20080917
20080721
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StoDW]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[StoDW]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE FUNCTION StoDW
(@date varchar(12) )
RETURNS varchar(19) AS  
BEGIN 
	declare @return   varchar(19)
	
	set @return = substring(@date,1,4)+'-'+substring(@date,5,2)+'-'+substring(@date,7,2)

	if len(@date ) = 12 
	begin
		set @return = substring(@date,1,4)+'-'+substring(@date,5,2)+'-'+substring(@date,7,2) +  ' ' + substring(@date,9,2) + ':' + substring(@date,11,2)
	end

	set @return = @return+ 	case  DATEPART(DW, substring(@date,1,8)) 	when 2 then '(월)'
										when 3 then '(화)'
										when 4 then '(수)'
										when 5 then '(목)'
										when 6 then '(금)'
										when 7 then '(토)'
										when 1 then '(일)'
				end


    return ( @return )
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE VIEW dbo.SL_POINT
AS

SELECT CD_MEMBER, SUM(PNT_OCCUR) AS PNT_OCCUR, SUM(PNT_USE) AS PNT_USE, SUM(PNT_OCCUR-PNT_USE) AS PNT_TOTAL
FROM
(
	SELECT  CD_MEMBER,  SUM(PNT_CASH+PNT_CARD) AS PNT_OCCUR, 0 AS PNT_USE
	   FROM SL_PNT_OCCUR
	GROUP BY CD_MEMBER
	UNION ALL
	SELECT CD_MEMBER,  0, SUM(PNT_USE) AS PNT_USE
	   FROM SL_PNT_PAY
	GROUP BY  CD_MEMBER
) A
GROUP BY CD_MEMBER




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



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

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE  PROCEDURE [dbo].[POS_SAVE_SALE_H]

	@WORK_KIND			VARCHAR(1),
	@CD_STORE 	    		VARCHAR(4),
	@YMD_SALE			VARCHAR(8),
	@NO_POS			VARCHAR(2),
	@NO_RCP			VARCHAR(4),
	@DS_SALE			VARCHAR(1),
	@AMT_SALE			INT,
	@AMT_DUTY			INT,
	@AMT_TAX			INT,
	@AMT_DUTYFREE		INT,
	@AMT_CASH			INT,
	@AMT_CARD			INT,
	@AMT_CHECK			INT,
	@AMT_TRUST			INT,
	@AMT_GIFT			INT,
	@AMT_POINT			INT,
	@AMT_LOSS			INT,
	@DC_MENU			INT,
	@DC_RECEIPT			INT,
	@DC_ENURI			INT,
	@DC_CODE			INT,
	@DC_COUPON			INT,
	@CD_CODE			VARCHAR(3),
	@DC_MEMBER			INT,	
	@DC_CUT			INT,
	@DC_VAT			INT,
	@AMT_CASHRCP		INT,
	@DS_LOCK			VARCHAR(3),
	@NO_LOCK			VARCHAR(4),
	@CD_SAWON			VARCHAR(6),	
	@CD_MEMBER			VARCHAR(10),
	@YMD_SALE_ORG		VARCHAR(8),
	@NO_RCP_ORG		VARCHAR(4),
	@CD_MENU			VARCHAR(14),
	@AMT_EXISTPOINT		INT,
	@PNT_OCCUR			INT OUT,
	@PNT_USE			INT,
	@PNT_TOTAL			INT OUT
AS
BEGIN
 
	--VOID 또는 입금변경시 사용하기위함
	DECLARE @PNT_OCCUR_ORG 	INT
	DECLARE @PNT_USE_ORG 		INT
	DECLARE @AMT_TRUST_ORG 		INT
	DECLARE @CD_MEMBER_ORG	VARCHAR(10)
	DECLARE @NO_TICKET_REF		VARCHAR(14)

	SELECT @PNT_OCCUR_ORG = PNT_OCCUR,
		  @PNT_USE_ORG	=PNT_USE,
		  @AMT_TRUST_ORG	=AMT_TRUST,
		  @CD_MEMBER_ORG	=CD_MEMBER
	   FROM SL_SALE_H
	WHERE CD_STORE	=@CD_STORE
	      AND YMD_SALE	=@YMD_SALE
	      AND NO_POS	=@NO_POS
	      AND NO_RCP	=@NO_RCP

	/**************************************************************************************************************************************************************************************/
	/*                                               					포인트 적용   시작 								   */
	/**************************************************************************************************************************************************************************************/

	--결제변경일 경우에는 무조건 이전의 적립 및 사용내역을 삭제한다
	IF @WORK_KIND IN('C','V')
	BEGIN

		DELETE 
		   FROM SL_PNT_OCCUR
		WHERE LINK		=@YMD_SALE+@NO_POS+@NO_RCP

		DELETE 
		   FROM SL_PNT_PAY
		WHERE  LINK		=@YMD_SALE+@NO_POS+@NO_RCP	

		UPDATE MS_MEMBER SET 	AMT_SALE = AMT_SALE - @AMT_SALE
		 WHERE CD_MEMBER	=@CD_MEMBER

		SELECT @PNT_TOTAL = PNT_TOTAL
		   FROM SL_POINT
		 WHERE CD_MEMBER	=@CD_MEMBER
		
	END
	ELSE SET @PNT_USE = @AMT_POINT

	IF (@CD_MEMBER <> '' AND @WORK_KIND <> 'V')
	BEGIN
		DECLARE @CASHAMT_STD	INT
		DECLARE @CARDAMT_STD	INT
		DECLARE @CASHPNT_STD	INT
		DECLARE @CARDPNT_STD	INT

		DECLARE @CASHAMT		INT
		DECLARE @CARDAMT		INT
		DECLARE @PNT_CASH	INT
		DECLARE @PNT_CARD	INT


		--회원이면 회원별로 포인트가 설정되어져있는지 체크한다
		IF @CD_MEMBER <> ''
		BEGIN
			
			SELECT @CASHAMT_STD = CONVERT(INT, ISNULL(A.NM_CODE5,'0')),
				  @CASHPNT_STD = CONVERT(INT, ISNULL(A.NM_CODE6,'0')),
				  @CARDAMT_STD = CONVERT(INT, ISNULL(A.NM_CODE7,'0')),
				  @CARDPNT_STD = CONVERT(INT, ISNULL(A.NM_CODE8,'0'))
			    FROM MS_CODE A, MS_MEMBER  B
			 WHERE A.CD_KIND    		= '05'
	                                  AND A.CD_CODE   		=B.DS_MEMBER
			       AND A.CD_STORE 		=@CD_STORE
			       AND B.CD_MEMBER	=@CD_MEMBER

			SET @CASHAMT_STD = ISNULL(@CASHAMT_STD,0)
			SET @CARDAMT_STD = ISNULL(@CARDAMT_STD,0)
			SET @CASHPNT_STD  = ISNULL(@CASHPNT_STD,0)
			SET @CARDPNT_STD = ISNULL(@CARDPNT_STD,0)

		END

		
		IF (@CASHAMT_STD = 0) OR (@CARDAMT_STD = 0)
		BEGIN
			SELECT @CASHAMT_STD = CASHAMT_STD,
				  @CASHPNT_STD = CASH_POINT,
				  @CARDAMT_STD = CARDAMT_STD,
				  @CARDPNT_STD = CARD_POINT
			    FROM MS_STORE
			 WHERE CD_STORE =@CD_STORE
		END

	
		SET @CASHAMT =ISNULL(@AMT_CASH,0)+ISNULL(@AMT_CHECK,0)+ISNULL(@AMT_GIFT,0)
		SET @CARDAMT = @AMT_CARD

		--카드금액이 없으면 포인트제외 금액을 현금에서 뺀다
		IF @CARDAMT = 0 
			SET @CASHAMT = @CASHAMT - @AMT_EXISTPOINT

		--카드금액이 있으면 포인트제외 금액을 카드금에서 뺀다(카드금액이 포인트 제외금액보다 작을때는 귀찮아서 처리안함)
		IF @CARDAMT > 0
			SET @CARDAMT = @CARDAMT - @AMT_EXISTPOINT

		IF @CASHPNT_STD > 0
			SET	@PNT_CASH = @CASHAMT / @CASHAMT_STD * @CASHPNT_STD
		ELSE
			SET	@PNT_CASH = 0

		IF @CARDPNT_STD > 0
			SET	@PNT_CARD = @CARDAMT / @CARDAMT_STD * @CARDPNT_STD
		ELSE
			SET	@PNT_CARD = 0

		SET 	@PNT_OCCUR = @PNT_CASH + @PNT_CARD

		--포인트를 사용한 영수증은 포인트를 적립하지 않는다
		IF (@PNT_USE <> 0)
			SET @PNT_OCCUR = 0

		--VOID일때는 발생포인트에 *-1을 곱한다
		IF @WORK_KIND = 'V'
		BEGIN
			SET @PNT_OCCUR = @PNT_OCCUR * -1
		END

		SELECT @PNT_TOTAL = PNT_TOTAL FROM SL_POINT WHERE CD_MEMBER = @CD_MEMBER
	
		--총포인트
		SELECT @PNT_TOTAL = @PNT_OCCUR + @PNT_TOTAL - @PNT_USE
	
		DECLARE @PNT_SEQ INT

		--적립에 대해 적용
		IF (@PNT_OCCUR <> 0)
		BEGIN
			SELECT @PNT_SEQ = ISNULL(MAX(SEQ),0) +1
			   FROM SL_PNT_OCCUR
			WHERE YMD_OCCUR = @YMD_SALE
		
			INSERT INTO SL_PNT_OCCUR( 
							CD_MEMBER,
							YMD_OCCUR,
							SEQ,
							CD_CODE,
							AMT_CASH,
							AMT_CARD,
							PNT_CASH,
							PNT_CARD,
							LINK
							)
					VALUES(
							@CD_MEMBER,
							@YMD_SALE,
							@PNT_SEQ,
							'000',
							@AMT_CASH,
							@AMT_CARD,
							@PNT_CASH,
							@PNT_CARD,
							@YMD_SALE+@NO_POS+@NO_RCP	
						)
		END
		ELSE IF @PNT_USE <> 0
		BEGIN
			SELECT @PNT_SEQ = ISNULL(MAX(SEQ),0) +1
			   FROM SL_PNT_PAY
			WHERE  YMD_PAY = @YMD_SALE
		
			INSERT INTO SL_PNT_PAY( 
							CD_MEMBER,
							YMD_PAY,
							SEQ,
							CD_CODE,
							PNT_USE,
							LINK
							)
					VALUES(
							@CD_MEMBER,
							@YMD_SALE,
							@PNT_SEQ,
							'000',
							@PNT_USE,
							@YMD_SALE+@NO_POS+@NO_RCP	
						)
		END


	END

	/**************************************************************************************************************************************************************************************/
	/*                                               					포인트 적용    END 								   */
	/**************************************************************************************************************************************************************************************/

	/**************************************************************************************************************************************************************************************/
	/*                                               					회원 외상 적용  시작 								   */
	/**************************************************************************************************************************************************************************************/



	IF @WORK_KIND IN ('S','B')
	BEGIN
	
		DECLARE @COME_TIME	DATETIME

		IF @WORK_KIND = 'S'
		BEGIN
			SELECT @COME_TIME 	=COME_TIME
			   FROM SL_ORDER_H
			WHERE CD_STORE	=@CD_STORE
			      AND DS_LOCK	=@DS_LOCK
			      AND NO_LOCK	=@NO_LOCK

			SET @COME_TIME = ISNULL(@COME_TIME, GETDATE())
			EXEC SAVE_LOCKER_CANCEL @CD_STORE, @YMD_SALE, @DS_LOCK, @NO_LOCK

			IF (@CD_MEMBER <> '') AND (@CD_MENU <> '')
			BEGIN
				INSERT INTO SL_MEMBER_USE (YMD_SALE, NO_RCP, CD_MEMBER, CD_MENU)
							VALUES   (CONVERT(VARCHAR(8), GETDATE(), 112), @CD_STORE+@YMD_SALE+@NO_POS+@NO_RCP, @CD_MEMBER, @CD_MENU)
			END

			--입장권번호
			SELECT @NO_TICKET_REF = YMD_SALE+NO_POS+NO_RCP 
			   FROM SL_LOCK_ALLOT
			WHERE CD_STORE	=@CD_STORE
			      AND DS_LOCK	=@DS_LOCK
			      AND NO_LOCK	=@NO_LOCK
			      AND OUT_TIME	IS NULL
		END
		--반품이면 회원 이용내역을 삭제한다
		ELSE IF @WORK_KIND = 'B'		
		BEGIN
			SET @COME_TIME = GETDATE()
			IF @CD_MEMBER <> ''
			BEGIN

				DELETE FROM SL_MEMBER_USE WHERE NO_RCP = @CD_STORE+@YMD_SALE+@NO_POS+@NO_RCP
			END
		END

		INSERT INTO SL_SALE_H(
						CD_STORE,
						YMD_SALE,
						NO_POS,
						NO_RCP,
						DS_SALE,
						AMT_SALE,
						AMT_DUTY,
						AMT_TAX,
						AMT_DUTYFREE,
						AMT_CASH,
						AMT_CARD,
						AMT_CHECK,
						AMT_TRUST,
						AMT_GIFT,
						AMT_POINT,
						AMT_LOSS,
						DC_MENU,
						DC_RECEIPT,
						DC_ENURI,
						DC_CODE,
						CD_CODE,
						DC_MEMBER,
						DC_COUPON,
						DC_CUT,
						DC_VAT,
						AMT_CASHRCP,
						DS_LOCK,
						NO_LOCK,
						COME_TIME,
						DT_SALE,
						CD_SAWON,
						CD_MEMBER,
						NO_RCP_ORG,
						PNT_OCCUR,
						PNT_USE,
						NO_ACCT_REF,
						NO_TICKET_REF
						)
					VALUES
						(
						@CD_STORE,
						@YMD_SALE,
						@NO_POS,
						@NO_RCP,
						@DS_SALE,
						@AMT_SALE,
						@AMT_DUTY,
						@AMT_TAX,
						@AMT_DUTYFREE,
						@AMT_CASH,
						@AMT_CARD,
						@AMT_CHECK,
						@AMT_TRUST,
						@AMT_GIFT,
						@AMT_POINT,
						@AMT_LOSS,
						@DC_MENU,
						@DC_RECEIPT,
						@DC_ENURI,
						@DC_CODE,
						@CD_CODE,
						@DC_MEMBER,
						@DC_COUPON,
						@DC_CUT,
						@DC_VAT,
						@AMT_CASHRCP,
						@DS_LOCK,
						@NO_LOCK,
						@COME_TIME,
						GETDATE(),
						@CD_SAWON,
						@CD_MEMBER,
						@YMD_SALE_ORG +  @NO_POS + @NO_RCP_ORG,
						@PNT_OCCUR,
						@PNT_USE,
						'',
						@NO_TICKET_REF	
						)

						

		IF @CD_MEMBER <> ''
		BEGIN
			UPDATE MS_MEMBER SET 	YMD_VISIT = CONVERT(VARCHAR(8), GETDATE(), 112),
							AMT_SALE = AMT_SALE + @AMT_SALE,
							CNT_VISIT = CNT_VISIT + 1
			 WHERE CD_MEMBER	=@CD_MEMBER
		END

		--쿠폰사용내역 환원
		UPDATE MS_COUPON SET CNT_USE = CNT_USE - T1.QTY_SALE
		  FROM SL_SALE_D T1
		 WHERE MS_COUPON.CD_STORE	= T1.CD_STORE
		   AND MS_COUPON.NO_COUPON	= T1.NO_COUPON
		   AND T1.CD_STORE			= @CD_STORE
		   AND T1.YMD_SALE			= @YMD_SALE
		   AND T1.NO_POS			= @NO_POS
		   AND T1.NO_RCP			= @NO_RCP
		   AND T1.DS_SALE			= 'C'

		--반품일때
		IF @WORK_KIND = 'B'
		BEGIN
			INSERT INTO SL_SALE_D(
							CD_STORE,
							YMD_SALE,
							NO_POS,
							NO_RCP,
							DS_SALE,
							CD_MENU,
							SEQ,
							QTY_SALE,	
							PR_SALE,
							AMT_SALE,
							DC_MENU,
							DC_RECEIPT,
							DC_MEMBER,
							AMT_VAT,
							DS_TAX
							)
						
					SELECT	CD_STORE,
							@YMD_SALE,
							NO_POS,
							@NO_RCP,
							DS_SALE,
							CD_MENU,
							SEQ,
							QTY_SALE *-1,
							PR_SALE,
							AMT_SALE *-1,
							DC_MENU * -1,
							DC_RECEIPT * -1,
							DC_MEMBER*-1,
							AMT_VAT * -1,
							DS_TAX
			
					FROM		SL_SALE_D
					WHERE	CD_STORE	=@CD_STORE
					      AND 	YMD_SALE	=@YMD_SALE_ORG
					      AND 	NO_POS	=@NO_POS
					      AND 	NO_RCP	=@NO_RCP_ORG

						
		END

					
	END
	--현금영수증만 발행
	ELSE IF @WORK_KIND = 'H'
	BEGIN
		UPDATE SL_SALE_H SET
						AMT_CASHRCP=@AMT_CASHRCP,
						DT_CHANGE  	=GETDATE()

		WHERE CD_STORE	=@CD_STORE
		      AND YMD_SALE	=@YMD_SALE
		      AND NO_POS	=@NO_POS
		      AND NO_RCP	=@NO_RCP

	END
	--결제변경
	ELSE IF @WORK_KIND = 'C' 
	BEGIN
		UPDATE SL_SALE_H SET	AMT_SALE		=@AMT_SALE,
						AMT_CASH		=@AMT_CASH,
						AMT_CARD		=@AMT_CARD,
						AMT_CHECK		=@AMT_CHECK,
						AMT_TRUST		=@AMT_TRUST,
						AMT_GIFT		=@AMT_GIFT,
						AMT_POINT		=@AMT_POINT,
						AMT_LOSS		=@AMT_LOSS,
						AMT_CASHRCP	=@AMT_CASHRCP,
						DC_MENU		=@DC_MENU,
						CD_MEMBER		=@CD_MEMBER,
						DC_MEMBER		=@DC_MEMBER,
						DC_RECEIPT		=@DC_RECEIPT,
						DC_ENURI		=@DC_ENURI,
						DC_CODE		=@DC_CODE,
						DC_COUPON		=@DC_COUPON,
						DC_CUT		=@DC_CUT,
						DC_VAT		=@DC_VAT,
						CD_SAWON		=@CD_SAWON,
						AMT_DUTY		=@AMT_DUTY,
						AMT_TAX		=@AMT_TAX,
						PNT_OCCUR		=@PNT_OCCUR,
						PNT_USE		=@PNT_USE,
						DT_CHANGE  		=GETDATE()

		WHERE CD_STORE	=@CD_STORE
		      AND YMD_SALE	=@YMD_SALE
		      AND NO_POS	=@NO_POS
		      AND NO_RCP	=@NO_RCP

		DELETE 
		   FROM SL_SALE_D
		WHERE CD_STORE	=@CD_STORE
		      AND YMD_SALE	=@YMD_SALE
		      AND NO_POS	=@NO_POS
		      AND NO_RCP	=@NO_RCP

	END
	-- VOID 처리
	ELSE IF @WORK_KIND = 'V'
	BEGIN

		UPDATE SL_SALE_H SET
						DS_SALE	='V',
						DT_CHANGE  	=GETDATE()

		WHERE CD_STORE	=@CD_STORE
		      AND YMD_SALE	=@YMD_SALE
		      AND NO_POS	=@NO_POS
		      AND NO_RCP	=@NO_RCP


		--회원이용내역을 삭제한다
		IF @CD_MEMBER <> ''
		BEGIN
			DELETE FROM SL_MEMBER_USE WHERE NO_RCP = @CD_STORE+@YMD_SALE+@NO_POS+@NO_RCP
		END

		--쿠폰사용내역 환원
		UPDATE MS_COUPON SET CNT_USE = CNT_USE - T1.QTY_SALE
		  FROM SL_SALE_D T1
		 WHERE MS_COUPON.CD_STORE	= T1.CD_STORE
		   AND MS_COUPON.NO_COUPON	= T1.NO_COUPON
		   AND T1.CD_STORE			= @CD_STORE
		   AND T1.YMD_SALE			= @YMD_SALE
		   AND T1.NO_POS			= @NO_POS
		   AND T1.NO_RCP			= @NO_RCP
		   AND T1.DS_SALE			= 'C'
	

	END
	ELSE IF @WORK_KIND = 'P'
	BEGIN
		UPDATE SL_SALE_H SET
						CD_MEMBER=@CD_MEMBER,
						PNT_OCCUR = @PNT_OCCUR,
						DT_CHANGE  	=GETDATE()

		WHERE CD_STORE	=@CD_STORE
		      AND YMD_SALE	=@YMD_SALE
		      AND NO_POS	=@NO_POS
		      AND NO_RCP	=@NO_RCP

		IF @CD_MEMBER <> ''
		BEGIN
			UPDATE MS_MEMBER SET 	YMD_VISIT = CONVERT(VARCHAR(8), GETDATE(), 112),
							AMT_SALE = AMT_SALE + @AMT_SALE,
							CNT_VISIT = CNT_VISIT + 1
			 WHERE CD_MEMBER	=@CD_MEMBER
		END

	END


	SELECT @PNT_OCCUR = ISNULL(@PNT_OCCUR, 0)
	SELECT @PNT_TOTAL = ISNULL(@PNT_TOTAL, 0)


END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO



CREATE    PROCEDURE SAVE_SL_POINT                                                                                   
                                                   
	 @YMD_SALE	 	VARCHAR(8),    	-- 매출일자          
	 @SEQ			INT,                                                   
	 @CD_MEMBER	VARCHAR(10),	-- 회원코드       
	 @CD_CODE		VARCHAR(3),	-- 코드        
	 @POINT  	 	INT,  		-- 포인트      
	 @REMARK		VARCHAR(100), 
	 @CD_SAWON_CHG	VARCHAR(6),
	 @GUBUN		VARCHAR(1),	--0:사용, 1:지급
	 @WORK_KIND	VARCHAR(1)	--작업구분(I:추가, U:수정, D:삭제)                                                    
AS       
begin                                     
	DECLARE	@CNT		INT
	DECLARE	@BEF_POINT	INT

	IF @GUBUN = '0'
	BEGIN
		--수정전 포인트를 구한다
		SELECT @BEF_POINT = PNT_USE
		   FROM SL_PNT_PAY
		 WHERE YMD_PAY	=@YMD_SALE
		       AND SEQ		=@SEQ  

		IF @WORK_KIND = 'I'
		BEGIN
			SELECT @CNT = ISNULL(MAX(SEQ),0) + 1 FROM SL_PNT_PAY	WHERE YMD_PAY = @YMD_SALE

			INSERT INTO SL_PNT_PAY (YMD_PAY, SEQ, CD_MEMBER, CD_CODE, PNT_USE, REMARK, CD_SAWON_CHG)
					VALUES       (@YMD_SALE, @CNT, @CD_MEMBER, @CD_CODE, @POINT, @REMARK, @CD_SAWON_CHG)

	
	
		END
		ELSE IF @WORK_KIND = 'U'
		BEGIN
			UPDATE SL_PNT_PAY	SET PNT_USE = @POINT,
 						        REMARK  = @REMARK,
						        CD_SAWON_CHG = @CD_SAWON_CHG,
						        DT_CHANGE 	=GETDATE()
			 WHERE  YMD_PAY	=@YMD_SALE
			       AND SEQ		=@SEQ

		END
		ELSE IF @WORK_KIND = 'D'
		BEGIN

			DELETE SL_PNT_PAY
			 WHERE YMD_PAY	=@YMD_SALE
			       AND SEQ		=@SEQ
			
		END
	END   
	ELSE IF @GUBUN = '1'  	
	BEGIN
		--수정전 포인트를 구한다
		SELECT @BEF_POINT = PNT_CASH
		   FROM SL_PNT_OCCUR
		 WHERE YMD_OCCUR	=@YMD_SALE
		       AND SEQ		=@SEQ

		IF @WORK_KIND = 'I'
		BEGIN
			SELECT @CNT = ISNULL(MAX(SEQ),0) + 1 FROM SL_PNT_OCCUR	WHERE YMD_OCCUR = @YMD_SALE

			INSERT INTO SL_PNT_OCCUR ( YMD_OCCUR, SEQ, CD_MEMBER, CD_CODE, PNT_CASH, REMARK, CD_SAWON_CHG)
					VALUES       (@YMD_SALE, @CNT, @CD_MEMBER, @CD_CODE, @POINT, @REMARK, @CD_SAWON_CHG)

	
		END
		ELSE IF @WORK_KIND = 'U'
		BEGIN
			UPDATE SL_PNT_OCCUR	SET PNT_CASH = @POINT,
	 						        REMARK  = @REMARK,
							        CD_SAWON_CHG = @CD_SAWON_CHG,
							        DT_CHANGE 	=GETDATE()
			 WHERE YMD_OCCUR	=@YMD_SALE
			       AND SEQ		=@SEQ

		END
		ELSE IF @WORK_KIND = 'D'
		BEGIN

			DELETE SL_PNT_OCCUR
			 WHERE YMD_OCCUR	=@YMD_SALE
			       AND SEQ		=@SEQ
			
		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_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_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

