20090105
20090312
if not exists(select * from information_schema.columns where TABLE_NAME='ms_store' and COLUMN_NAME='biznofix')
BEGIN
ALTER TABLE ms_store
ADD biznofix int
END
GO

update ms_store set biznofix = 0
GO

if not exists(select * from information_schema.columns where TABLE_NAME='sl_sale_h' and COLUMN_NAME='amt_existpoint')
BEGIN
ALTER TABLE sl_sale_h
ADD amt_existpoint int
END
GO

if not exists(select * from information_schema.columns where TABLE_NAME='sl_pnt_occur' and COLUMN_NAME='amt_exist')
BEGIN
ALTER TABLE sl_pnt_occur
ADD amt_exist int
END
GO


if not exists(select * from information_schema.columns where TABLE_NAME='ms_store' and COLUMN_NAME='cashrcpamt_std')
BEGIN
ALTER TABLE ms_store
ADD cashrcpamt_std int
END
GO

if not exists(select * from information_schema.columns where TABLE_NAME='ms_store' and COLUMN_NAME='cashrcp_point')
BEGIN
ALTER TABLE ms_store
ADD cashrcp_point int
END
GO

if not exists(select * from information_schema.columns where TABLE_NAME='sl_pnt_occur' and COLUMN_NAME='amt_cashrcp')
BEGIN
ALTER TABLE sl_pnt_occur
ADD amt_cashrcp int
END
GO

if not exists(select * from information_schema.columns where TABLE_NAME='sl_pnt_occur' and COLUMN_NAME='pnt_cashrcp')
BEGIN
ALTER TABLE sl_pnt_occur
ADD pnt_cashrcp int
END
GO

UPDATE MS_STORE SET CASHRCPAMT_STD =0, CASHRCP_POINT=0
GO

UPDATE MS_CODE SET NM_CODE9='0', NM_CODE10='0' WHERE CD_KIND = '05'
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+PNT_CASHRCP) 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_MS_STORE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SAVE_MS_STORE]
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'  AND @WORK_KIND <> 'H')
	BEGIN
		DECLARE @CASHAMT_STD		INT
		DECLARE @CARDAMT_STD		INT
		DECLARE @CASHRCPAMT_STD	INT
		DECLARE @CASHPNT_STD		INT
		DECLARE @CARDPNT_STD		INT
		DECLARE @CASHRCPPNT_STD	INT

		DECLARE @CASHAMT		INT
		DECLARE @CARDAMT		INT
		DECLARE @CASHRCPAMT	INT
		DECLARE @PNT_CASH	INT
		DECLARE @PNT_CARD	INT
		DECLARE @PNT_CASHRCP	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')),
				  @CASHRCPAMT_STD = CONVERT(INT, ISNULL(A.NM_CODE9,'0')),
				  @CASHRCPPNT_STD = CONVERT(INT, ISNULL(A.NM_CODE10,'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 @CASHRCPAMT_STD 	= ISNULL(@CASHRCPAMT_STD,0)

			SET @CASHPNT_STD  	= ISNULL(@CASHPNT_STD,0)
			SET @CARDPNT_STD 		= ISNULL(@CARDPNT_STD,0)
			SET @CASHRCPPNT_STD 	= ISNULL(@CASHRCPPNT_STD,0)
		END

		
		IF (@CASHAMT_STD = 0) AND (@CARDAMT_STD = 0) AND (@CASHRCPAMT_STD = 0)
		BEGIN
			SELECT @CASHAMT_STD = CASHAMT_STD,
				  @CASHPNT_STD = CASH_POINT,
				  @CARDAMT_STD = CARDAMT_STD,
				  @CARDPNT_STD = CARD_POINT,
				  @CASHRCPAMT_STD = CASHRCPAMT_STD,
				  @CASHRCPPNT_STD = CASHRCP_POINT
			    FROM MS_STORE
			 WHERE CD_STORE =@CD_STORE

		END

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

		SET @CASHAMT 	=ABS(@CASHAMT)
		SET @CARDAMT 	=ABS(@CARDAMT)
		SET @CASHRCPAMT 	=ABS(@CASHRCPAMT)
		SET @AMT_EXISTPOINT = ABS(@AMT_EXISTPOINT)


		DECLARE	@AMT_APPLY	INT
		DECLARE	@KIND		INT	--0:현금, 1:현금영수증, 2:신용카드
		SET 		@AMT_APPLY = 0
		SET 		@KIND = 0

		IF (@CASHAMT = 0) AND (@CARDAMT = 0) AND (@CASHRCPAMT = 0) 
			GOTO BEGIN_RETURN
	
		WHILE @AMT_APPLY< @AMT_EXISTPOINT
		BEGIN
			IF @KIND = 0 
			BEGIN
				IF @CASHAMT >= (@AMT_EXISTPOINT-@AMT_APPLY)
				BEGIN
					SET @CASHAMT 	= @CASHAMT - @AMT_EXISTPOINT
					SET @AMT_APPLY 	= @AMT_EXISTPOINT
				END
				ELSE IF @CASHAMT > 0
				BEGIN
					SET @AMT_APPLY 	=@CASHAMT
					SET @CASHAMT 	=@CASHAMT - (@AMT_EXISTPOINT -  (@AMT_EXISTPOINT- @CASHAMT))
				END
				SET 		@KIND = 1		
			END
			ELSE IF @KIND = 1
			BEGIN
				IF @CASHRCPAMT >= (@AMT_EXISTPOINT-@AMT_APPLY)
				BEGIN
					SET @CASHRCPAMT 	= @CASHRCPAMT - (@AMT_EXISTPOINT-@AMT_APPLY)
					SET @AMT_APPLY 	= @AMT_APPLY + (@AMT_EXISTPOINT-@AMT_APPLY)
				END
				ELSE IF @CASHRCPAMT > 0
				BEGIN
					SET @AMT_APPLY 	=@AMT_APPLY + @CASHRCPAMT
					IF (@AMT_EXISTPOINT>= @AMT_APPLY) 
						SET @CASHRCPAMT 	= 0
					ELSE
						SET @CASHRCPAMT =@AMT_EXISTPOINT-@AMT_APPLY
				END
				SET 		@KIND = 2		
			END
			ELSE IF @KIND = 2
			BEGIN
				IF @CARDAMT >= (@AMT_EXISTPOINT-@AMT_APPLY)
				BEGIN
					SET @CARDAMT 	= @CARDAMT - (@AMT_EXISTPOINT-@AMT_APPLY)
					SET @AMT_APPLY 	= @AMT_APPLY + @AMT_EXISTPOINT-@AMT_APPLY
				END
				ELSE IF @CARDAMT > 0
				BEGIN
					SET @AMT_APPLY 	=@AMT_APPLY + @CARDAMT
					IF (@AMT_EXISTPOINT>= @AMT_APPLY) 
						SET @CARDAMT 	= 0
					ELSE
						SET @CARDAMT 	=@AMT_EXISTPOINT-@AMT_APPLY
				END

			END

		END

BEGIN_RETURN:

		--반품일때는 포인트 제외상품금액에 더한다
		IF @WORK_KIND = 'B'
		BEGIN
			SET @CASHAMT 	=@CASHAMT		*-1
			SET @CARDAMT 	=@CARDAMT		*-1
			SET @CASHRCPAMT 	=@CASHRCPAMT	*-1
			SET @AMT_EXISTPOINT =@AMT_EXISTPOINT *-1
		END


		--현금 포인트
		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

		--현금영수증 포인트
		IF @CASHRCPPNT_STD > 0
			SET	@PNT_CASHRCP = @CASHRCPAMT / @CASHRCPAMT_STD * @CASHRCPPNT_STD
		ELSE
			SET	@PNT_CASHRCP = 0

		SET 	@PNT_OCCUR = @PNT_CASH + @PNT_CARD + @PNT_CASHRCP

		--포인트를 사용한 영수증은 포인트를 적립하지 않는다
		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 = ISNULL(PNT_TOTAL,0) FROM SL_POINT WHERE CD_MEMBER = @CD_MEMBER
	
		SET @PNT_TOTAL	= ISNULL(@PNT_TOTAL, 0)
		SET @PNT_OCCUR 	= ISNULL(@PNT_OCCUR, 0)
		SET @PNT_USE 	= ISNULL(@PNT_USE, 0)

		--총포인트
		SELECT @PNT_TOTAL = @PNT_OCCUR + @PNT_TOTAL - @PNT_USE
	
		SET @CASHAMT =ISNULL(@AMT_CASH,0)+ISNULL(@AMT_CHECK,0)+ISNULL(@AMT_GIFT,0) - ISNULL(@AMT_CASHRCP,0)
		SET @CARDAMT = @AMT_CARD
		SET @CASHRCPAMT = @AMT_CASHRCP

		SET @CASHAMT 	=ABS(@CASHAMT)
		SET @CARDAMT 	=ABS(@CARDAMT)
		SET @CASHRCPAMT 	=ABS(@CASHRCPAMT)
		SET @AMT_EXISTPOINT = ABS(@AMT_EXISTPOINT)	

		IF @WORK_KIND = 'B'
		BEGIN
			SET @CASHAMT 	=@CASHAMT		*-1
			SET @CARDAMT 	=@CARDAMT		*-1
			SET @CASHRCPAMT 	=@CASHRCPAMT	*-1
			SET @AMT_EXISTPOINT =@AMT_EXISTPOINT *-1
		END
	
		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,
							AMT_CASHRCP,
							PNT_CASH,
							PNT_CARD,

							PNT_CASHRCP,
							AMT_EXIST,
							LINK
							)
					VALUES(
							@CD_MEMBER,
							@YMD_SALE,
							@PNT_SEQ,
							'000',
							@AMT_CASH,
							@AMT_CARD,
							@AMT_CASHRCP,
							@PNT_CASH,
							@PNT_CARD,
							@PNT_CASHRCP,
							@AMT_EXISTPOINT,
							@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,
						AMT_EXISTPOINT,
						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,
						@AMT_EXISTPOINT,
						'',
						@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+MS_COUPON.NO_SEQ	= 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+MS_COUPON.NO_SEQ	= 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




CREATE   PROCEDURE SAVE_MS_STORE
	@CD_STORE 		VARCHAR(4),
	@CD_SAWON_CHG 	VARCHAR(6),
	@I_SW    	  	CHAR(1),
	@NM_STORE  	 	VARCHAR(30),
	@NM_BOSS   	 	VARCHAR(10),
	@NO_BIZER  	 	VARCHAR(12),
	@NM_UPTAE  	 	VARCHAR(30),
	@NM_UPJONG 	VARCHAR(30),
	@TEL_OFFICE 	 	VARCHAR(13),
	@TEL_MOBIL1		VARCHAR(13),
	@TEL_MOBIL2		VARCHAR(13),
	@TEL_FAX   		VARCHAR(13),
	@NO_POST  	 	VARCHAR(6),
	@ADDR1     	 	VARCHAR(40),
	@ADDR2    	 	VARCHAR(40),
	@PNT_MIN_USE 	INT,
	@RCP_TITLE1	 	VARCHAR(42),
	@RCP_TITLE2	 	VARCHAR(42),
	@RCP_TITLE3 	 	VARCHAR(42),
	@RCP_TITLE4	 	VARCHAR(42),
	@CARD_TITLE1	VARCHAR(42),
	@CARD_TITLE2	VARCHAR(42),
	@CARD_TITLE3 	VARCHAR(42),
	@CARD_TITLE4	VARCHAR(42),
	@RCP_END1 		VARCHAR(42),
	@RCP_END2 		VARCHAR(42),
	@RCP_END3 		VARCHAR(42),
	@TICKET_END1	VARCHAR(42),
	@TICKET_END2	VARCHAR(42),
	@TICKET_END3	VARCHAR(42),
	@TICKET_END4	VARCHAR(42),
	@TICKET_END5	VARCHAR(42),
	@CDP_ACCOUNT 	VARCHAR(40),
	@CDP_READY  	VARCHAR(40),
	@CONF        	 	VARCHAR(200),
	@USE_MENU		VARCHAR(50),
	@REMARK    		VARCHAR(100),
	@DC_UNIT		INT,
	@LEN_CARD		INT,
	@PREFIX		VARCHAR(20),
	@NO_POS		VARCHAR(2),
	@LEN_MENU		INT,
	@CASHAMT_STD	INT,
	@CASH_POINT		INT,
	@CARDAMT_STD	INT,
	@CARD_POINT	INT,
	@CASHRCPAMT_STD	INT,
	@CASHRCP_POINT	INT,
	@POINT_MIN		INT,
	@RT_ONEVAN		INT,
	@AMT_LIMIT		INT,
	@CUST_TITLE		VARCHAR(30),
	@RPT_PWD		VARCHAR(20),
	@SIMPLE_RCP		VARCHAR(50),
	@MIN_AFTER		INT,
	@MIN_EACH		INT,
	@AMT_ADD		INT,
	@ADD_MENU		VARCHAR(14),
	@CLSTEP1		INT,
	@CLSTEP2		INT,
	@CLSTEP3		INT,
	@CLCOLOR1		VARCHAR(30),
	@CLCOLOR2		VARCHAR(30),
	@CLCOLOR3		VARCHAR(30),
	@LOCK_X		INT,
	@LOCK_Y		INT,
	@LOCKPOS_X		INT,
	@LOCKPOS_Y		INT,
	@LOCKPOS_F		INT,
	@CLIPBOARD		VARCHAR(30),
	@TICKET_TITLE	VARCHAR(20),
	@LEN_COUPON	INT,
	@DAYTIME_BEGIN	VARCHAR(5),
	@DAYTIME_END	VARCHAR(5),
	@NIGHTTIME_BEGIN	VARCHAR(5),
	@NIGHTTIME_END	VARCHAR(5),
	@BACKUP_DIR		VARCHAR(100)


AS
BEGIN
	UPDATE MS_STORE SET 	NM_STORE		=@NM_STORE,
					NM_BOSS		=@NM_BOSS,
					NO_BIZER		=@NO_BIZER,
					NM_UPTAE		=@NM_UPTAE,
					NM_UPJONG 		=@NM_UPJONG,
			                  	TEL_OFFICE		=@TEL_OFFICE,
					TEL_MOBIL1		=@TEL_MOBIL1,
					TEL_MOBIL2		=@TEL_MOBIL2,
					TEL_FAX		=@TEL_FAX,
					NO_POST		=@NO_POST,
					ADDR1			=@ADDR1,
					ADDR2			=@ADDR2,
					PNT_MIN_USE		=@PNT_MIN_USE,
					RCP_TITLE1		=@RCP_TITLE1,
					RCP_TITLE2		=@RCP_TITLE2,
					RCP_TITLE3		=@RCP_TITLE3,
					RCP_TITLE4		=@RCP_TITLE4,
					CARD_TITLE1		=@CARD_TITLE1,
					CARD_TITLE2		=@CARD_TITLE2,
					CARD_TITLE3		=@CARD_TITLE3,
					CARD_TITLE4		=@CARD_TITLE4,
					RCP_END1		=@RCP_END1,
					RCP_END2		=@RCP_END2,
			                  	RCP_END3		=@RCP_END3,
					TICKET_END1		=@TICKET_END1,
					TICKET_END2		=@TICKET_END2,
			                  	TICKET_END3		=@TICKET_END3,
			                  	TICKET_END4		=@TICKET_END4,
			                  	TICKET_END5		=@TICKET_END5,
					CDP_ACCOUNT	=@CDP_ACCOUNT,
					CDP_READY		=@CDP_READY,
					CONF 			=@CONF,
					USE_MENU		=@USE_MENU,
					REMARK 		=@REMARK,
					DC_UNIT		=@DC_UNIT,
					LEN_CARD		=@LEN_CARD,
					PREFIX			=@PREFIX,
					NO_POS		=@NO_POS,
					LEN_MENU		=@LEN_MENU,
					CASHAMT_STD	=@CASHAMT_STD,
					CASH_POINT		=@CASH_POINT,
					CARDAMT_STD	=@CARDAMT_STD,
					CARD_POINT		=@CARD_POINT,
					CASHRCPAMT_STD	=@CASHRCPAMT_STD,
					CASHRCP_POINT	=@CASHRCP_POINT,
					POINT_MIN		=@POINT_MIN,
					RT_ONEVAN		=@RT_ONEVAN,
					CUST_TITLE		=@CUST_TITLE,
					POSRPT_PWD		=@RPT_PWD,
					SIMPLE_RCP		=@SIMPLE_RCP,
					AMT_LIMIT		=@AMT_LIMIT,
					MIN_AFTER		=@MIN_AFTER,
					MIN_EACH		=@MIN_EACH,
					AMT_ADD		=@AMT_ADD,
					ADD_MENU		=@ADD_MENU,
					CLSTEP1		=@CLSTEP1,
					CLSTEP2		=@CLSTEP2,
					CLSTEP3		=@CLSTEP3,
					CLCOLOR1		=@CLCOLOR1,
					CLCOLOR2		=@CLCOLOR2,
					CLCOLOR3		=@CLCOLOR3,
					LOCK_X		=@LOCK_X,
					LOCK_Y		=@LOCK_Y,
					LOCKPOS_X		=@LOCKPOS_X,
					LOCKPOS_Y		=@LOCKPOS_Y,
					LOCKPOS_F		=@LOCKPOS_F,
					CLIPBOARD		=@CLIPBOARD,
					TICKET_TITLE		=@TICKET_TITLE,
					LEN_COUPON		=@LEN_COUPON,
					DAYTIME_BEGIN	=@DAYTIME_BEGIN,
					DAYTIME_END		=@DAYTIME_END,
					NIGHTTIME_BEGIN	=@NIGHTTIME_BEGIN,
					NIGHTTIME_END	=@NIGHTTIME_END,
					BACKUP_DIR		=@BACKUP_DIR,
					DT_CHANGE		=GETDATE()
	                      WHERE CD_STORE=@CD_STORE

END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
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 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, AMT_CASH, PNT_CARD, AMT_CARD, PNT_CASHRCP, AMT_CASHRCP, REMARK, CD_SAWON_CHG)
					VALUES       (@YMD_SALE, @CNT, @CD_MEMBER, @CD_CODE, @POINT, 0, 0, 0, 0, 0, @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].[worktime_f]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[worktime_f]
GO

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




/*******************************************************************
근무시간 계산
Result 주간(2) + 야간(2) + 심야(2)
********************************************************************/

CREATE     FUNCTION dbo.worktime_f(
                @start_date    		datetime,
                @end_date		datetime,
   	  @sun_time		varchar(13),
   	  @over_time		varchar(13),
   	  @night_time		varchar(13)
                )


    returns varchar(12)
AS
BEGIN

	declare @sunfrom_time	varchar(5)
	declare @sunto_time	varchar(5)
	declare @overfrom_time	varchar(5)
	declare @overto_time	varchar(5)
	declare @nightfrom_time	varchar(5)
	declare @nightto_time	varchar(5)	

	declare	@resultdata	varchar(12)
	declare @start_time	varchar(5)
	declare @end_time	varchar(5)
	declare @temp	varchar(12)

	declare @result_sun	int
	declare @result_over	int
	declare @result_night	int

	select @temp   = '000000000000'

	select @result_sun   = 0
	select @result_over  = 0
	select @result_night = 0


	select @sunfrom_time = SUBSTRING(@sun_time, 1, 5)
	select @sunto_time     = SUBSTRING(@sun_time, 9, 5)
	select @overfrom_time = SUBSTRING(@over_time, 1, 5)
	select @overto_time     = SUBSTRING(@over_time, 9, 5)
	select @nightfrom_time = SUBSTRING(@night_time, 1, 5)
	select @nightto_time     = SUBSTRING(@night_time, 9, 5)

	if @sunfrom_time > @sunto_time
	begin
		select @sunfrom_time = replace(@sunfrom_time, '24','00')
		select @night_time = replace(@night_time, '24','00')
	end

	if @overfrom_time > @overto_time
	begin
		select @overfrom_time = replace(@overfrom_time, '24','00')
		select @over_time 	= replace(@over_time, '24','00')
	end

	if @nightfrom_time > @nightto_time
	begin
		select @nightfrom_time = replace(@nightfrom_time, '24','00')
		select @night_time 	= replace(@night_time, '24','00')
	end

	declare	@term	int
	declare	@i	int
	declare	@tempdate	datetime


	set	@term = DATEDIFF(DAY, @start_date, @end_date) + 1
	set 	@tempdate = @start_date
	set	@i	= 0
	
	WHILE @i < @term
	BEGIN

		if (convert(varchar(10), @tempdate, 112) = convert(varchar(10), @start_date, 112)) and  (convert(varchar(10), @tempdate, 112) = convert(varchar(10), @end_date, 112))
		begin

			select @temp =	 dbo.worktime_sub_f(@tempdate, @end_date, @sun_time, @over_time,	@night_time, 0)
	
	
			select @result_sun	 = @result_sun + convert(int, substring(@temp, 1,4))
			select @result_over	 = @result_over + convert(int, substring(@temp, 5,4))
			select @result_night	 = @result_night + convert(int, substring(@temp, 9,4))
		
			
		end
		else if (convert(varchar(10), @tempdate, 112) = convert(varchar(10), @start_date, 112)) and  (convert(varchar(10), @tempdate, 112) < convert(varchar(10), @end_date, 112))
		begin

			select @temp =	 dbo.worktime_sub_f(@tempdate, @end_date, @sun_time, @over_time,	@night_time, 1)
	
	
			select @result_sun	 = @result_sun + convert(int, substring(@temp, 1,4))
			select @result_over	 = @result_over + convert(int, substring(@temp, 5,4))
			select @result_night	 = @result_night + convert(int, substring(@temp, 9,4))

		end
		else if (convert(varchar(10), @tempdate, 112) > convert(varchar(10), @start_date, 112)) and  (convert(varchar(10), @tempdate, 112) = convert(varchar(10), @end_date, 112))
		begin

			select @temp =	 dbo.worktime_sub_f(@tempdate, @end_date, @sun_time, @over_time,	@night_time, 2)
	
	
			select @result_sun	 = @result_sun + convert(int, substring(@temp, 1,4))
			select @result_over	 = @result_over + convert(int, substring(@temp, 5,4))
			select @result_night	 = @result_night + convert(int, substring(@temp, 9,4))

		end
		else if (convert(varchar(10), @tempdate, 112) <> convert(varchar(10), @start_date, 112)) and  (convert(varchar(10), @tempdate, 112) <> convert(varchar(10), @end_date, 112))
		begin

			select @temp =	 dbo.worktime_sub_f(@tempdate, @end_date, @sun_time, @over_time,	@night_time, 3)
	
	
			select @result_sun	 = @result_sun + convert(int, substring(@temp, 1,4))
			select @result_over	 = @result_over + convert(int, substring(@temp, 5,4))
			select @result_night	 = @result_night + convert(int, substring(@temp, 9,4))

		end

		set	@tempdate = @tempdate + 1
		set 	@i	    = @i + 1

	END

	select @resultdata	= dbo.lpad(@result_sun, 4,'0') + dbo.lpad(@result_over, 4,'0') + dbo.lpad(@result_night, 4,'0')
       	Return @resultdata
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

/*******************************************************************
근무시간 계산
Result 주간(4) + 야간(4) + 심야(4)
********************************************************************/

CREATE      FUNCTION dbo.worktime_sub_f(
                @start_date    		datetime,
                @end_date		datetime,
   	  @sun_time		varchar(13),
   	  @over_time		varchar(13),
   	  @night_time		varchar(13),
	  @gubun		int	-- 0 : 출근과 퇴근이 있을때
					-- 1 : 출근만 있을때 ( 출근시간부터 24:00 까지 계산)
					-- 2 : 퇴근만 있을때 (00:01 부터 퇴근시간까지 계산)
					-- 3 : 출/퇴근이 없을때 ( 00:01 부터 23:59 까지 계산)
                )


    returns varchar(12)
AS
BEGIN

	declare @sunfrom_time	varchar(5)
	declare @sunto_time	varchar(5)
	declare @overfrom_time	varchar(5)
	declare @overto_time	varchar(5)
	declare @nightfrom_time	varchar(5)
	declare @nightto_time	varchar(5)	

	declare	@resultdata	varchar(12)
	declare @start_time	varchar(8)
	declare @end_time	varchar(8)
	declare @temp	varchar(12)

	declare @result_sun	int
	declare @result_over	int
	declare @result_night	int

	select @temp   = '000000000000'

	select @result_sun   = 0
	select @result_over  = 0
	select @result_night = 0

	select @sunfrom_time = SUBSTRING(@sun_time, 1, 5)
	select @sunto_time     = SUBSTRING(@sun_time, 9, 5)
	select @overfrom_time = SUBSTRING(@over_time, 1, 5)
	select @overto_time     = SUBSTRING(@over_time, 9, 5)
	select @nightfrom_time = SUBSTRING(@night_time, 1, 5)
	select @nightto_time     = SUBSTRING(@night_time, 9, 5)

	if (@gubun = 0)
	begin
		select @start_time = convert(varchar(8), @start_date, 114)
		select @end_time = convert(varchar(8), @end_date, 114)
	end
	else if (@gubun = 1)
	begin
		select @start_time = convert(varchar(8), @start_date, 114)
		select @end_time = '23:59:59'
	end
	else if (@gubun = 2)
	begin
		select @start_time = '00:00:00'
		select @end_time = convert(varchar(8), @end_date, 114)
	end
	else if (@gubun = 3)
	begin
		select @start_time = '00:00:00'
		select @end_time = '23:59:59'
	end


	if @gubun in (0,1)
	begin
		--출근시간이 주간근무시간일때
		if (@start_time <= @sunto_time) 
		begin
	
			if  (@end_time <= @sunto_time)
				select @result_sun = datediff(n, convert(smalldatetime, '2000-01-01 '+@start_time), convert(smalldatetime, '2000-01-01 '+@end_time))
			else if  (@end_time <= @overto_time)
			begin
				select @result_sun = datediff(n, convert(smalldatetime, '2000-01-01 '+@start_time), convert(smalldatetime, '2000-01-01 '+@sunto_time))
				select @result_over= datediff(n, convert(smalldatetime, '2000-01-01 '+@overfrom_time), convert(smalldatetime, '2000-01-01 '+@end_time))
			end
			else 
			begin
				select @result_sun = datediff(n, convert(smalldatetime, '2000-01-01 '+@start_time), convert(smalldatetime, '2000-01-01 '+@sunto_time))
				select @result_over= datediff(n, convert(smalldatetime, '2000-01-01 '+@overfrom_time), convert(smalldatetime, '2000-01-01 '+@overto_time))
				select @result_night= datediff(n, convert(smalldatetime, '2000-01-01 '+@nightfrom_time), convert(smalldatetime, '2000-01-01 '+@end_time))
			end
	
	
		end
		else if (@start_time <= @overto_time) 
		begin
	
			if  (@end_time <= @overto_time)
			begin
				select @result_over= datediff(n, convert(smalldatetime, '2000-01-01 '+@start_time), convert(smalldatetime, '2000-01-01 '+@end_time))
			end
			else  
			begin
				select @result_over=  datediff(n, convert(smalldatetime, '2000-01-01 '+@start_time), convert(smalldatetime, '2000-01-01 '+@overto_time))
				select @result_night= datediff(n, convert(smalldatetime, '2000-01-01 '+@nightfrom_time), convert(smalldatetime, '2000-01-01 '+@end_time))
			end
	
	
		end
		--출근시간이 야간근무시간일때
		else if (@start_time <= @nightto_time) 
		begin
	
			select @result_night = datediff(n, convert(smalldatetime, '2000-01-01 '+@nightfrom_time), convert(smalldatetime, '2000-01-01 23:59:59'))
	
		end
		else 
		begin
			select @result_night = datediff(n, convert(smalldatetime, '2000-01-01 '+@nightfrom_time), convert(smalldatetime, '2000-01-01 '+@end_time))

		end
	end
	else 
	begin
		--출근시간이 주간근무시간일때
		if (@end_time <= @nightto_time) 
		begin
	
			select @result_night= datediff(n, convert(smalldatetime, '2000-01-01 '+@start_time), convert(smalldatetime, '2000-01-01 '+@end_time))
		
		end
		else if (@end_time <= @sunto_time)
		begin
		
			select @result_night= datediff(n, convert(smalldatetime, '2000-01-01 '+@start_time), convert(smalldatetime, '2000-01-01 '+@nightto_time))
			select @result_sun= datediff(n, convert(smalldatetime, '2000-01-01 '+@sunfrom_time), convert(smalldatetime, '2000-01-01 '+@end_time))

		end
		else if (@end_time <= @overto_time)
		begin
		
			select @result_night= datediff(n, convert(smalldatetime, '2000-01-01 '+@start_time), convert(smalldatetime, '2000-01-01 '+@nightto_time))
			select @result_sun= datediff(n, convert(smalldatetime, '2000-01-01 '+@sunfrom_time), convert(smalldatetime, '2000-01-01 '+@sunto_time))
			select @result_over= datediff(n, convert(smalldatetime, '2000-01-01 '+@overfrom_time), convert(smalldatetime, '2000-01-01 '+@end_time))
		end
		else
		begin
		
			select @result_night= datediff(n, convert(smalldatetime, '2000-01-01 '+@start_time), convert(smalldatetime, '2000-01-01 '+@nightto_time))
			select @result_sun= datediff(n, convert(smalldatetime, '2000-01-01 '+@sunfrom_time), convert(smalldatetime, '2000-01-01 '+@sunto_time))
			select @result_over= datediff(n, convert(smalldatetime, '2000-01-01 '+@overfrom_time), convert(smalldatetime, '2000-01-01 '+@overto_time))
			if @gubun = 3
				select @result_night= @result_night + datediff(n, convert(smalldatetime, '2000-01-01 '+@nightfrom_time), convert(smalldatetime, '2000-01-01 '+@end_time))
		end

	end
	select @resultdata	= dbo.lpad(@result_sun, 4,'0') + dbo.lpad(@result_over, 4,'0') + dbo.lpad(@result_night, 4,'0')
       Return @resultdata
END

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_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, SL_MEMBER_DELAY B
			WHERE SL_MEMBER_JOIN.CD_MEMBER = B.CD_MEMBER
			     AND SL_MEMBER_JOIN.NO_RCP        = B.NO_RCP
			     AND SL_MEMBER_JOIN.DS_STATUS    = '0'
			     AND B.YMD_FROM <= 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_log]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[delete_log]
GO

CREATE TABLE [dbo].[delete_log] (
	[seq] [bigint] IDENTITY (1, 1) NOT NULL ,
	[delete_date] [datetime] NULL ,
	[ds_type] [int] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[delete_log] ADD 
	CONSTRAINT [PK_delete_log] PRIMARY KEY  CLUSTERED 
	(
		[seq]
	)  ON [PRIMARY] 
GO

if not exists(select * from information_schema.columns where TABLE_NAME='sl_posclose' and COLUMN_NAME='yn_datadelete')
BEGIN
ALTER TABLE sl_posclose
ADD yn_datadelete varchar(1)
END
GO

update sl_posclose set yn_datadelete = 'N'
GO

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE [dbo].[DELETE_RECEIPT]
	
 	@CD_STORE		VARCHAR(4),		--매장코드
	@YMD_SALE	 	VARCHAR(8),		--매출일자
	@NO_POS		VARCHAR(2),		--포스번호
	@NO_RCP		VARCHAR(4)		--영수증번호

AS
BEGIN
 
	DECLARE	@AMT_SALE	INT
	DECLARE	@CD_SAWON	VARCHAR(6)

	SELECT @AMT_SALE = AMT_SALE,
		 @CD_SAWON = CD_SAWON
	   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 @AMT_SALE IS NULL RETURN


	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

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


	UPDATE SL_SALE_D SET	NO_RCP = DBO.LPAD(CONVERT(INT, NO_RCP) - 1,4,'0')
 			WHERE CD_STORE	=@CD_STORE
			      AND YMD_SALE	=@YMD_SALE
			      AND NO_POS	=@NO_POS
			      AND NO_RCP	>@NO_RCP

	UPDATE SL_SALE_H SET	NO_RCP = DBO.LPAD(CONVERT(INT, NO_RCP) - 1,4,'0')
 			WHERE CD_STORE	=@CD_STORE
			      AND YMD_SALE	=@YMD_SALE
			      AND NO_POS	=@NO_POS
			      AND NO_RCP	>@NO_RCP

	UPDATE SL_CARD SET	NO_RCP = DBO.LPAD(CONVERT(INT, NO_RCP) - 1,4,'0')
 			WHERE CD_STORE	=@CD_STORE
			      AND YMD_SALE	=@YMD_SALE
			      AND NO_POS	=@NO_POS
			      AND NO_RCP	>@NO_RCP

	UPDATE SL_CASH SET	NO_RCP = DBO.LPAD(CONVERT(INT, NO_RCP) - 1,4,'0')
 			WHERE CD_STORE	=@CD_STORE
			      AND YMD_SALE	=@YMD_SALE
			      AND NO_POS	=@NO_POS
			      AND NO_RCP	>@NO_RCP

	--같은 판매원이 마감을 두번했을때는 두번의 마감내역에서 모두 차감된다
	UPDATE SL_CASHIER_MGM SET 	AMT_SALE = AMT_SALE - @AMT_SALE,
						AMT_CASH = AMT_CASH - @AMT_SALE
 			WHERE CD_STORE	=@CD_STORE
			      AND YMD_CLOSE	=@YMD_SALE
			      AND NO_POS	=@NO_POS
			      AND CD_SAWON	=@CD_SAWON

	UPDATE SL_POSCLOSE SET 	AMT_SALE = AMT_SALE - @AMT_SALE,
					AMT_CASH = AMT_CASH - @AMT_SALE
 			WHERE CD_STORE	=@CD_STORE
			      AND YMD_CLOSE	=@YMD_SALE
			      AND NO_POS	=@NO_POS




END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO



CREATE PROCEDURE DATA_PERSENT_DELETE
	@CD_STORE		VARCHAR(4),
	@YMD_SALE		VARCHAR(8),
	@NO_POS		VARCHAR(2),
	@DELETE_PER	INT,
	@AMT_APPLY		INT OUTPUT
AS
BEGIN

	DECLARE	@AMT_DELETE	INT	
	

	INSERT INTO DELETE_LOG(DELETE_DATE, DS_TYPE) VALUES(GETDATE(), 5)	

	SET NOCOUNT ON

	DECLARE	CUR_LOOP	CURSOR FAST_FORWARD	
	
	FOR 

		SELECT AMT_CASH-AMT_CASHRCP AS AMT_CASH
		   FROM SL_POSCLOSE
		 WHERE CD_STORE	=@CD_STORE
		      AND YMD_CLOSE	=@YMD_SALE
		      AND DS_STATUS 	='C'

		OPEN CUR_LOOP

		DECLARE @TOT_SALE		INT

		FETCH NEXT FROM CUR_LOOP	INTO  @TOT_SALE

		WHILE (@@FETCH_STATUS = 0)
		BEGIN
			SET 	@AMT_APPLY = 0
			SET	@AMT_DELETE = @TOT_SALE * (@DELETE_PER * 0.01)
	
			SET NOCOUNT ON
		
			DECLARE	CUR_DEL	CURSOR FAST_FORWARD
		
			FOR
		
				SELECT  NO_RCP,
					  AMT_SALE
				   FROM SL_SALE_H A
				 WHERE CD_STORE		=@CD_STORE
				      AND YMD_SALE		=@YMD_SALE
				      AND NO_POS		=@NO_POS
				      AND DS_SALE		='S'
				      AND AMT_SALE		=AMT_CASH
				      AND CD_MEMBER		=''
				      AND AMT_CASHRCP	=0
				      AND (SELECT COUNT(*) FROM SL_CARD WHERE CD_STORE = @CD_STORE AND YMD_SALE = A.YMD_SALE AND NO_POS = A.NO_POS AND NO_RCP  = A.NO_RCP)= 0
				      AND (SELECT COUNT(*) FROM SL_CASH WHERE CD_STORE = @CD_STORE AND YMD_SALE = A.YMD_SALE AND NO_POS = A.NO_POS AND NO_RCP  = A.NO_RCP)= 0
				 ORDER BY AMT_SALE DESC
					
				OPEN  CUR_DEL
			
				DECLARE @NO_RCP		VARCHAR(4)
				DECLARE @AMT_SALE		INT
		
				Fetch next from CUR_DEL into @NO_RCP, @AMT_SALE
				
				while (@@Fetch_status = 0) 
				begin
					IF ( (CONVERT(FLOAT, (@AMT_APPLY + @AMT_SALE)) / @AMT_DELETE * 100 ) <= 110)
					BEGIN
		
						EXEC DELETE_RECEIPT @CD_STORE, @YMD_SALE, @NO_POS, @NO_RCP
						SET @AMT_APPLY = @AMT_APPLY + @AMT_SALE
					END
		
					Fetch Next From CUR_DEL into @NO_RCP, @AMT_SALE
				end
				
			close CUR_DEL
		
			DEALLOCATE CUR_DEL  
	
			UPDATE SL_POSCLOSE SET YN_DATADELETE = 'Y' WHERE CD_STORE = @CD_STORE AND YMD_CLOSE = @YMD_SALE AND NO_POS = @NO_POS
	
			FETCH NEXT FROM CUR_LOOP	INTO @TOT_SALE


		END

	CLOSE CUR_LOOP

	DEALLOCATE CUR_LOOP

END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO











CREATE  PROCEDURE POS_SAVE_POS_MGM

	@CD_STORE 	    		VARCHAR(4),
	@YMD_CLOSE			VARCHAR(8),
	@NO_POS			VARCHAR(2),
	@CD_WEATHER		VARCHAR(3),
	@WORK_KIND			VARCHAR(1),
	@RESULT			VARCHAR(1) OUTPUT,
	@YMD_LAST			VARCHAR(8) OUTPUT
AS
BEGIN
	
	IF @WORK_KIND = 'C'
	BEGIN
	
		DECLARE  	@AMT_READY		INT,
				@AMT_MID		INT,
				@AMT_SALE		INT,
				@AMT_CASH          	INT,
				@AMT_CARD         	INT,
				@AMT_CHECK        	INT,
				@AMT_GIFT          	INT,
				@AMT_POINT		INT,
				@AMT_TRUST		INT,
				@AMT_LOSS		INT,
				@AMT_VOID		INT,
				@AMT_CASHRCP	INT,
				@DC_MENU		INT,
				@DC_MEMBER		INT,
				@DC_RECEIPT	      	INT,
				@DC_CODE		INT,
				@DC_SPC		INT,
				@DC_ENURI		INT,
				@DC_COUPON		INT,
				@DC_CUT		INT,
				@DC_VAT		INT,
				@CNT_CUSTOMER	INT,
				@CNT_CASHIER	INT,
				@AMT_LACK		INT
	
		SELECT @AMT_READY	=SUM(AMT_READY	),
			 @AMT_MID		=SUM(AMT_MID_CASH+AMT_MID_CHECK	),
			 @AMT_SALE		=SUM(AMT_SALE	),
			 @AMT_CASH		=SUM(AMT_CASH	),
			 @AMT_CARD		=SUM(AMT_CARD	),
			 @AMT_CHECK         	=SUM(AMT_CHECK	),
			 @AMT_GIFT          	=SUM(AMT_GIFT	),
			 @AMT_POINT		=SUM(AMT_POINT	),
			 @AMT_TRUST	=SUM(AMT_TRUST	),
			 @AMT_LOSS		=SUM(AMT_LOSS	),
			 @AMT_VOID		=SUM(AMT_VOID	),
			 @AMT_CASHRCP	=SUM(AMT_CASHRCP 	),
			 @DC_MENU		=SUM(DC_MENU	),
			 @DC_MEMBER	=SUM(DC_MEMBER	),
			 @DC_RECEIPT	=SUM(DC_RECEIPT	),
			 @DC_CODE		=SUM(DC_CODE	),
			 @DC_SPC		=SUM(DC_SPC		),
			 @DC_ENURI		=SUM(DC_ENURI	),
			 @DC_COUPON	=SUM(DC_COUPON	),
			 @DC_CUT		=SUM(DC_CUT		),
			 @DC_VAT		=SUM(DC_VAT		),
			 @CNT_CUSTOMER     	=SUM(CNT_CUSTOMER),
			 @CNT_CASHIER	=COUNT(*),
			 @AMT_LACK		=SUM(AMT_LACK	)
		   FROM  SL_CASHIER_MGM
		WHERE  CD_STORE  	=@CD_STORE
		      AND  YMD_CLOSE 	=@YMD_CLOSE
		      AND  NO_POS    	=@NO_POS
	
		UPDATE SL_POSCLOSE SET
						AMT_READY		=@AMT_READY,
						AMT_MID		=@AMT_MID,
	         					AMT_SALE 	  	=@AMT_SALE,
						AMT_CASH		=@AMT_CASH,
						AMT_CARD		=@AMT_CARD,
						AMT_CHECK		=@AMT_CHECK,
						AMT_GIFT		=@AMT_GIFT,
						AMT_POINT		=@AMT_POINT,
						AMT_TRUST		=@AMT_TRUST,
						AMT_LOSS		=@AMT_LOSS,
						AMT_VOID		=@AMT_VOID,
						AMT_CASHRCP	=@AMT_CASHRCP,
						DC_MENU		=@DC_MENU,
						DC_MEMBER		=@DC_MEMBER,
						DC_RECEIPT	  	=@DC_RECEIPT,
						DC_CODE		=@DC_CODE,
						DC_SPC		=@DC_SPC,
						DC_ENURI		=@DC_ENURI,
						DC_COUPON		=@DC_COUPON,
						DC_CUT		=@DC_CUT,
						DC_VAT		=@DC_VAT,
						CNT_CUSTOMER	=@CNT_CUSTOMER,
						CNT_CASHIER		=@CNT_CASHIER,
						AMT_LACK		=@AMT_LACK,
						YN_DATADELETE	='N',
						DT_CHANGE		=GETDATE(),
						DS_STATUS		='C'
		    WHERE CD_STORE	  	=@CD_STORE
		          AND YMD_CLOSE	=@YMD_CLOSE
		          AND NO_POS	    	=@NO_POS
	

		--기간이 지난 회원상품의 상태를 변경한다
		DECLARE @POS_NO VARCHAR(2)
		SELECT @POS_NO = NO_POS FROM MS_STORE WHERE CD_STORE = @CD_STORE

		IF @NO_POS = @POS_NO
		BEGIN
			UPDATE SL_MEMBER_JOIN SET DS_STATUS = '2'
			WHERE YMD_FINISH < CONVERT(VARCHAR(8), GETDATE(), 112) 
			      AND DS_STATUS = '0'

			UPDATE SL_MEMBER_JOIN SET DS_STATUS = '0'
			WHERE DELAY_TO < CONVERT(VARCHAR(8), GETDATE(), 112) 
			      AND DS_STATUS = '1'
		END

	
	END
	--마감취소
	ELSE IF @WORK_KIND = 'X'
	BEGIN

		DECLARE @TEMP INT

		--포스마감이 된 일자인지 체크
		SELECT @TEMP = COUNT(*)
		   FROM SL_POSCLOSE
		 WHERE CD_STORE 	=@CD_STORE
		       AND YMD_CLOSE	=@YMD_CLOSE
		       AND NO_POS	=@NO_POS
		       AND DS_STATUS	='C'

		IF @TEMP > 0
		BEGIN

			--취소할려는 일자보다 나중에 일자에 마감된 일자가 있는지체크
			SELECT @TEMP = COUNT(*)
			   FROM SL_POSCLOSE
			 WHERE CD_STORE 	=@CD_STORE
			       AND YMD_CLOSE	>@YMD_CLOSE

			IF @TEMP = 0
			BEGIN		
				UPDATE SL_POSCLOSE SET DS_STATUS = 'O'
				 WHERE CD_STORE 	=@CD_STORE
				       AND YMD_CLOSE	=@YMD_CLOSE
				       AND NO_POS	=@NO_POS
				
				SELECT @RESULT = ' '

			END
			ELSE SELECT @RESULT = 'M'
		END
		ELSE SELECT @RESULT = 'X'

	END

	SELECT @YMD_LAST = MAX(YMD_CLOSE)
	   FROM SL_POSCLOSE
	WHERE CD_STORE	=@CD_STORE
	      AND NO_POS	=@NO_POS
	      AND DS_STATUS	='C'

	IF @YMD_LAST IS NULL SET @YMD_LAST = ''



END
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

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'  AND @WORK_KIND <> 'H')
	BEGIN
		DECLARE @CASHAMT_STD		INT
		DECLARE @CARDAMT_STD		INT
		DECLARE @CASHRCPAMT_STD	INT
		DECLARE @CASHPNT_STD		INT
		DECLARE @CARDPNT_STD		INT
		DECLARE @CASHRCPPNT_STD	INT

		DECLARE @CASHAMT		INT
		DECLARE @CARDAMT		INT
		DECLARE @CASHRCPAMT	INT
		DECLARE @PNT_CASH	INT
		DECLARE @PNT_CARD	INT
		DECLARE @PNT_CASHRCP	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')),
				  @CASHRCPAMT_STD = CONVERT(INT, ISNULL(A.NM_CODE9,'0')),
				  @CASHRCPPNT_STD = CONVERT(INT, ISNULL(A.NM_CODE10,'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 @CASHRCPAMT_STD 	= ISNULL(@CASHRCPAMT_STD,0)

			SET @CASHPNT_STD  	= ISNULL(@CASHPNT_STD,0)
			SET @CARDPNT_STD 		= ISNULL(@CARDPNT_STD,0)
			SET @CASHRCPPNT_STD 	= ISNULL(@CASHRCPPNT_STD,0)
		END

		
		IF (@CASHAMT_STD = 0) AND (@CARDAMT_STD = 0) AND (@CASHRCPAMT_STD = 0)
		BEGIN
			SELECT @CASHAMT_STD = CASHAMT_STD,
				  @CASHPNT_STD = CASH_POINT,
				  @CARDAMT_STD = CARDAMT_STD,
				  @CARDPNT_STD = CARD_POINT,
				  @CASHRCPAMT_STD = CASHRCPAMT_STD,
				  @CASHRCPPNT_STD = CASHRCP_POINT
			    FROM MS_STORE
			 WHERE CD_STORE =@CD_STORE

		END

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

		SET @CASHAMT 	=ABS(@CASHAMT)
		SET @CARDAMT 	=ABS(@CARDAMT)
		SET @CASHRCPAMT 	=ABS(@CASHRCPAMT)
		SET @AMT_EXISTPOINT = ABS(@AMT_EXISTPOINT)


		DECLARE	@AMT_APPLY	INT
		DECLARE	@KIND		INT	--0:현금, 1:현금영수증, 2:신용카드
		SET 		@AMT_APPLY = 0
		SET 		@KIND = 0
	
		WHILE @AMT_APPLY< @AMT_EXISTPOINT
		BEGIN
			IF @KIND = 0 
			BEGIN
				IF @CASHAMT >= (@AMT_EXISTPOINT-@AMT_APPLY)
				BEGIN
					SET @CASHAMT 	= @CASHAMT - @AMT_EXISTPOINT
					SET @AMT_APPLY 	= @AMT_EXISTPOINT
				END
				ELSE IF @CASHAMT > 0
				BEGIN
					SET @AMT_APPLY 	=@CASHAMT
					SET @CASHAMT 	=@CASHAMT - (@AMT_EXISTPOINT -  (@AMT_EXISTPOINT- @CASHAMT))
				END
				SET 		@KIND = 1		
			END
			ELSE IF @KIND = 1
			BEGIN
				IF @CASHRCPAMT >= (@AMT_EXISTPOINT-@AMT_APPLY)
				BEGIN
					SET @CASHRCPAMT 	= @CASHRCPAMT - (@AMT_EXISTPOINT-@AMT_APPLY)
					SET @AMT_APPLY 	= @AMT_APPLY + (@AMT_EXISTPOINT-@AMT_APPLY)
				END
				ELSE IF @CASHRCPAMT > 0
				BEGIN
					SET @AMT_APPLY 	=@AMT_APPLY + @CASHRCPAMT
					IF (@AMT_EXISTPOINT>= @AMT_APPLY) 
						SET @CASHRCPAMT 	= 0
					ELSE
						SET @CASHRCPAMT =@AMT_EXISTPOINT-@AMT_APPLY
				END
				SET 		@KIND = 2		
			END
			ELSE IF @KIND = 2
			BEGIN
				IF @CARDAMT >= (@AMT_EXISTPOINT-@AMT_APPLY)
				BEGIN
					SET @CARDAMT 	= @CARDAMT - (@AMT_EXISTPOINT-@AMT_APPLY)
					SET @AMT_APPLY 	= @AMT_APPLY + @AMT_EXISTPOINT-@AMT_APPLY
				END
				ELSE IF @CARDAMT > 0
				BEGIN
					SET @AMT_APPLY 	=@AMT_APPLY + @CARDAMT
					IF (@AMT_EXISTPOINT>= @AMT_APPLY) 
						SET @CARDAMT 	= 0
					ELSE
						SET @CARDAMT 	=@AMT_EXISTPOINT-@AMT_APPLY
				END

			END

		END

		--반품일때는 포인트 제외상품금액에 더한다
		IF @WORK_KIND = 'B'
		BEGIN
			SET @CASHAMT 	=@CASHAMT		*-1
			SET @CARDAMT 	=@CARDAMT		*-1
			SET @CASHRCPAMT 	=@CASHRCPAMT	*-1
			SET @AMT_EXISTPOINT =@AMT_EXISTPOINT *-1
		END


		--현금 포인트
		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

		--현금영수증 포인트
		IF @CASHRCPPNT_STD > 0
			SET	@PNT_CASHRCP = @CASHRCPAMT / @CASHRCPAMT_STD * @CASHRCPPNT_STD
		ELSE
			SET	@PNT_CASHRCP = 0

		SET 	@PNT_OCCUR = @PNT_CASH + @PNT_CARD + @PNT_CASHRCP

		--포인트를 사용한 영수증은 포인트를 적립하지 않는다
		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 = ISNULL(PNT_TOTAL,0) FROM SL_POINT WHERE CD_MEMBER = @CD_MEMBER
	
		SET @PNT_TOTAL	= ISNULL(@PNT_TOTAL, 0)
		SET @PNT_OCCUR 	= ISNULL(@PNT_OCCUR, 0)
		SET @PNT_USE 	= ISNULL(@PNT_USE, 0)

		--총포인트
		SELECT @PNT_TOTAL = @PNT_OCCUR + @PNT_TOTAL - @PNT_USE
	
		SET @CASHAMT =ISNULL(@AMT_CASH,0)+ISNULL(@AMT_CHECK,0)+ISNULL(@AMT_GIFT,0) - ISNULL(@AMT_CASHRCP,0)
		SET @CARDAMT = @AMT_CARD
		SET @CASHRCPAMT = @AMT_CASHRCP

		SET @CASHAMT 	=ABS(@CASHAMT)
		SET @CARDAMT 	=ABS(@CARDAMT)
		SET @CASHRCPAMT 	=ABS(@CASHRCPAMT)
		SET @AMT_EXISTPOINT = ABS(@AMT_EXISTPOINT)	

		IF @WORK_KIND = 'B'
		BEGIN
			SET @CASHAMT 	=@CASHAMT		*-1
			SET @CARDAMT 	=@CARDAMT		*-1
			SET @CASHRCPAMT 	=@CASHRCPAMT	*-1
			SET @AMT_EXISTPOINT =@AMT_EXISTPOINT *-1
		END
	
		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,
							AMT_CASHRCP,
							PNT_CASH,
							PNT_CARD,
							PNT_CASHRCP,
							AMT_EXIST,
							LINK
							)
					VALUES(
							@CD_MEMBER,
							@YMD_SALE,
							@PNT_SEQ,
							'000',
							@AMT_CASH,
							@AMT_CARD,
							@AMT_CASHRCP,
							@PNT_CASH,
							@PNT_CARD,
							@PNT_CASHRCP,
							@AMT_EXISTPOINT,
							@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,
						AMT_EXISTPOINT,
						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,
						@AMT_EXISTPOINT,
						'',
						@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+MS_COUPON.NO_SEQ	= 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+MS_COUPON.NO_SEQ	= 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

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

