20090312
20090312
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[POS_SAVE_DAYOPEN]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[POS_SAVE_DAYOPEN]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO






--         포스 일마감 체크 --
CREATE PROCEDURE POS_SAVE_DAYOPEN
    @CD_STORE	VARCHAR(4),
    @YMD_OPEN	VARCHAR(8),
    @NO_POS		VARCHAR(2),
    @RESULT		VARCHAR(12) OUTPUT
AS
BEGIN

	DECLARE	@CONF	VARCHAR(1)

	SELECT @CONF = SUBSTRING(CONF,10,1) FROM MS_STORE WHERE CD_STORE =@CD_STORE
		
	SET @RESULT = ''
	SELECT @RESULT = DS_STATUS
	   FROM SL_POSCLOSE
	WHERE CD_STORE	=@CD_STORE
	      AND YMD_CLOSE	=@YMD_OPEN
	      AND NO_POS	=@NO_POS

	--마감이 안됐을때
	IF @RESULT <> 'C' 
	BEGIN
		
		--관리프로그램 개점처리
		DECLARE @TEMP1	VARCHAR(1)
		DECLARE @POSNO	VARCHAR(2)

		SELECT @TEMP1 = SUBSTRING(CONF,43,1),
			 @POSNO = NO_POS
		   FROM MS_STORE
		WHERE CD_STORE	=@CD_STORE

		IF (@TEMP1 = '1') AND (@NO_POS = @POSNO)
		BEGIN
			DECLARE @CNT	INT
			
			SELECT @CNT = COUNT(*) 
			   FROM SL_DAY_CLOSE
			WHERE CD_STORE = @CD_STORE
			      AND YMD_CLOSE = @YMD_OPEN

			IF (@CNT = 0)
			BEGIN
				INSERT INTO SL_DAY_CLOSE(CD_STORE, YMD_CLOSE, YN_CLOSE)
						VALUES(@CD_STORE, @YMD_OPEN, 'N')
			END
			

		END

		DECLARE @TEMP VARCHAR(12)

		--전일자에 마감이 안된 일자가 있는지 체크
		SELECT TOP 1 @TEMP =  DBO.STOD(YMD_CLOSE)+NO_POS
		   FROM SL_POSCLOSE
		WHERE CD_STORE	= @CD_STORE
		      AND YMD_CLOSE	< @YMD_OPEN
		      AND DS_STATUS	='O'
		      
		IF @TEMP IS NULL SET @RESULT = ''
		ELSE SET @RESULT = @TEMP
	
		IF (@RESULT = '') OR (@CONF = '1')
		BEGIN
			INSERT INTO SL_POSCLOSE(	CD_STORE,
							YMD_CLOSE,
							NO_POS,
							DS_STATUS
						          )
					VALUES 
						         (   @CD_STORE,
							@YMD_OPEN,
							@NO_POS,
							'O'
						         )
			UPDATE SL_MEMBER_JOIN SET DS_STATUS = '1'
			FROM SL_MEMBER_JOIN
			WHERE  DS_STATUS    = '0'
			     AND delay_begin <= CONVERT(VARCHAR(8), GETDATE(), 112)
			     and delay_to >= CONVERT(VARCHAR(8), GETDATE(), 112)
							
			SET @RESULT = ''
		END
	END


END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DELETE_DATA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DELETE_DATA]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






/*************************************************************************************/
/*                                           데이터삭제			             */
/*************************************************************************************/
CREATE        PROCEDURE [dbo].[DELETE_DATA]
(
         @CD_STORE      	VARCHAR(4) ,      	-- 점코드                                     
         @YMD_FROM      	VARCHAR(8) ,      	-- 삭제기간                                   
         @YMD_TO        	VARCHAR(8) ,      	                        
         @WORKKIND    	INT
	
)

AS 

BEGIN
	
	IF @WORKKIND= 0 -- 설정기간
	BEGIN

		DELETE SL_LOCK_ALLOT	WHERE CD_STORE =@CD_STORE AND YMD_SALE BETWEEN @YMD_FROM AND @YMD_TO

		DELETE SL_CARD     		WHERE CD_STORE =@CD_STORE AND YMD_SALE BETWEEN @YMD_FROM AND @YMD_TO
		DELETE SL_CASH    		WHERE CD_STORE =@CD_STORE AND YMD_SALE BETWEEN @YMD_FROM AND @YMD_TO
		DELETE SL_CASHBAG 		WHERE CD_STORE =@CD_STORE AND YMD_SALE BETWEEN @YMD_FROM AND @YMD_TO
		DELETE SL_SALE_C 		WHERE CD_STORE =@CD_STORE AND YMD_SALE BETWEEN @YMD_FROM AND @YMD_TO
		DELETE SL_SALE_D 		WHERE CD_STORE =@CD_STORE AND YMD_SALE BETWEEN @YMD_FROM AND @YMD_TO
		DELETE SL_SALE_H 		WHERE CD_STORE =@CD_STORE AND YMD_SALE BETWEEN @YMD_FROM AND @YMD_TO
		DELETE SL_CASHIER_MGM 	WHERE CD_STORE =@CD_STORE AND YMD_CLOSE BETWEEN @YMD_FROM AND @YMD_TO
		DELETE SL_POSCLOSE 	WHERE CD_STORE =@CD_STORE AND YMD_CLOSE BETWEEN @YMD_FROM AND @YMD_TO
		DELETE SL_DAY_CLOSE	WHERE CD_STORE =@CD_STORE AND YMD_CLOSE BETWEEN @YMD_FROM AND @YMD_TO

	END

	IF @WORKKIND= 1 --기간에 상관없이
	BEGIN
		DELETE SL_ORDER_D 	WHERE CD_STORE =@CD_STORE 
		DELETE SL_ORDER_H 	WHERE CD_STORE =@CD_STORE 
		DELETE SL_ORDER_C	 	WHERE CD_STORE =@CD_STORE 
		DELETE SL_CARD     		WHERE CD_STORE =@CD_STORE 
		DELETE SL_CASH    		WHERE CD_STORE =@CD_STORE 
		DELETE SL_CASHBAG 		WHERE CD_STORE =@CD_STORE 
		DELETE SL_SALE_C 		WHERE CD_STORE =@CD_STORE 
		DELETE SL_SALE_D 		WHERE CD_STORE =@CD_STORE 
		DELETE SL_SALE_H 		WHERE CD_STORE =@CD_STORE
		DELETE SL_CASHIER_MGM 	WHERE CD_STORE =@CD_STORE
		DELETE SL_POSCLOSE 	WHERE CD_STORE =@CD_STORE
		DELETE SL_DAY_CLOSE 	WHERE CD_STORE =@CD_STORE

		DELETE SL_LOCK_ALLOT	WHERE CD_STORE =@CD_STORE
		DELETE SL_MEMBER_USE	

		DELETE SL_PNT_OCCUR	
		DELETE SL_PNT_PAY		
		DELETE SL_ACCT		WHERE CD_STORE =@CD_STORE
		DELETE SL_PAY		WHERE CD_STORE =@CD_STORE 
		DELETE SL_SILSA		WHERE CD_STORE =@CD_STORE 
		DELETE SL_STOCK_MONTH	WHERE CD_STORE =@CD_STORE 

	END

	IF @WORKKIND= 2 -- 초기화
	BEGIN
		DELETE SL_ORDER_D 	WHERE CD_STORE =@CD_STORE 
		DELETE SL_ORDER_H 	WHERE CD_STORE =@CD_STORE 
		DELETE SL_ORDER_C	 	WHERE CD_STORE =@CD_STORE 
		DELETE SL_CARD     		WHERE CD_STORE =@CD_STORE 
		DELETE SL_CASH    		WHERE CD_STORE =@CD_STORE 
		DELETE SL_CASHBAG 		WHERE CD_STORE =@CD_STORE 
		DELETE SL_SALE_C 		WHERE CD_STORE =@CD_STORE 
		DELETE SL_SALE_D 		WHERE CD_STORE =@CD_STORE 
		DELETE SL_SALE_H 		WHERE CD_STORE =@CD_STORE
		DELETE SL_BUY_H 		WHERE CD_STORE =@CD_STORE
		DELETE SL_BUY_D 		WHERE CD_STORE =@CD_STORE
		DELETE SL_CASHIER_MGM 	WHERE CD_STORE =@CD_STORE
		DELETE SL_LOCKER	 	WHERE CD_STORE =@CD_STORE
		DELETE SL_LOCKER_LOG	WHERE CD_STORE =@CD_STORE
		DELETE SL_LOCK_ALLOT	WHERE CD_STORE =@CD_STORE
		DELETE SL_MEMBER_JOIN	
		DELETE SL_MEMBER_USE	

		DELETE SL_MEMBER_REFUND
		DELETE SL_MEMBER_DELAY	
		DELETE MS_MEMBER_CARD	
		DELETE SL_MEMBER_JOIN_LOG

		DELETE SL_MEMBER_NEWS
		DELETE SL_POSCLOSE 	WHERE CD_STORE =@CD_STORE
		DELETE SL_DAY_CLOSE	WHERE CD_STORE =@CD_STORE
		DELETE SL_PNT_OCCUR		
		DELETE SL_PNT_PAY		
		DELETE SL_ACCT			WHERE CD_STORE =@CD_STORE
		DELETE SL_SAWONWORK		WHERE CD_STORE =@CD_STORE
		DELETE SL_ACCT			WHERE CD_STORE =@CD_STORE
		DELETE SL_PAY			WHERE CD_STORE =@CD_STORE 
		DELETE SL_SILSA			WHERE CD_STORE =@CD_STORE 
		DELETE SL_STOCK_MONTH	WHERE CD_STORE =@CD_STORE 

		DELETE MS_CODE			WHERE CD_STORE =@CD_STORE AND CD_KIND NOT IN ('15','80','60')
		INSERT INTO MS_CODE (CD_STORE, CD_KIND, CD_CODE, NM_CODE1, NM_CODE2, NM_CODE3, NM_CODE4, NM_CODE7, DS_STATUS)
				VALUES ('0001','01','001','01','127.0.0.1','0','1','0','0')
		INSERT INTO MS_CODE (CD_STORE, CD_KIND, CD_CODE, NM_CODE1, NM_CODE2, DS_STATUS)
				VALUES ('0001','11','100','거래처 외상매입금 지급','1','0')
		INSERT INTO MS_CODE (CD_STORE, CD_KIND, CD_CODE, NM_CODE1, NM_CODE2, DS_STATUS)
				VALUES ('0001','11','200','회원 외상입금','0','0')
		INSERT INTO MS_CODE (CD_STORE, CD_KIND, CD_CODE, NM_CODE1, DS_STATUS)
				VALUES ('0001','20','001','관리자','0')


		DELETE MS_MENU		WHERE CD_STORE =@CD_STORE 
		DELETE MS_MENU_DC	WHERE CD_STORE =@CD_STORE 
		DELETE MS_GOODS		WHERE CD_STORE =@CD_STORE 
		DELETE MS_LOCKER		WHERE CD_STORE =@CD_STORE 
		DELETE MS_UPJANG		WHERE CD_STORE =@CD_STORE 
		DELETE MS_LOCK		WHERE CD_STORE =@CD_STORE 
		DELETE MS_SAWON		WHERE CD_STORE =@CD_STORE AND CD_SAWON NOT IN ('master')
		INSERT INTO MS_SAWON(CD_STORE, CD_SAWON, NM_SAWON, NO_PASSWORD, CD_GRADE, CD_TIME_PAY, EMP_WORK, YN_USE)
				VALUES    ('0001','999999','초기사용자','3B','001','000','1111111111', 'Y')
		DELETE MS_LARGE		WHERE CD_STORE =@CD_STORE 
		DELETE MS_SMALL		WHERE CD_STORE =@CD_STORE 
		DELETE MS_SPC_H		WHERE CD_STORE =@CD_STORE 
		DELETE MS_SPC_D		WHERE CD_STORE =@CD_STORE 
		DELETE MS_TRD		WHERE CD_STORE =@CD_STORE 
		DELETE MS_MEMBER	
	END


END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

