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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO





CREATE          PROCEDURE SAVE_SL_DAY_CLOSE

	@CD_STORE		VARCHAR(4),		--점포코드
	@YMD_CLOSE		VARCHAR(8)	--마감일자
AS
BEGIN	
	
	DECLARE 	@AMT_SALE_TOT	NUMERIC(11,0)		--총매출금액
	DECLARE 	@AMT_SALE		NUMERIC(11,0)		--매출금액
	DECLARE 	@AMT_DC		INT			--할인금액
	DECLARE 	@AMT_TAX		NUMERIC(11,0)		--부가세
	DECLARE 	@AMT_SOON		NUMERIC(11,0)		--순매출
	DECLARE 	@AMT_DUTY		NUMERIC(11,0)		--과세매출
	DECLARE 	@AMT_DUTYFREE	NUMERIC(11,0)		--면세매출
	DECLARE 	@CNT_SALE		INT			--매출건수
	DECLARE	@CNT_PERSON		INT			--고객수
	DECLARE 	@AMT_SALE_DANGA	NUMERIC(11,0)		--객단가(순매출/매출건수)
	DECLARE 	@AMT_GIVE_TOT		NUMERIC(11,0)		--현미수금잔액
	DECLARE 	@AMT_PAY_TOT		NUMERIC(11,0)		--미지급금잔액
	DECLARE 	@AMT_ACCT_IN		NUMERIC(11,0)		--출납(수입금액)
	DECLARE 	@AMT_ACCT_OUT	NUMERIC(11,0)		--출납(지출금액)
	DECLARE 	@AMT_CASH		INT			--현금매출
	DECLARE 	@CNT_CASH		INT			--현금매출
	DECLARE 	@AMT_CASHRCP	INT			--현금매출
	DECLARE 	@CNT_CASHRCP	INT			--현금매출
	DECLARE 	@AMT_CARD		INT			--카드승인(인터넷승인)
	DECLARE 	@CNT_CARD		INT			--카드승인건수(인터넷승인)
	DECLARE 	@AMT_TRUST		INT			--외상매출
	DECLARE 	@CNT_TRUST		INT			--외상매출
	DECLARE 	@AMT_POINT		INT			--포인트매출
	DECLARE 	@CNT_POINT		INT			--포인트매출
	DECLARE 	@AMT_LOSS		INT			--손실매출
	DECLARE 	@CNT_LOSS		INT			--손실매출
	DECLARE 	@AMT_OKC		INT			--OK캐쉬백매출
	DECLARE 	@CNT_OKC		INT			--OK캐쉬백매출
	DECLARE 	@AMT_LACK		INT			--과부족금


	--매출금액
	
	-- 현금매출,수표매출,상품권매출,외상매출,포인트매출,할인금액
	SELECT  @AMT_SALE_TOT = SUM(ISNULL(AMT_SALE+DC_TOT,0)),
		  @AMT_SALE = SUM(ISNULL(AMT_SALE,0)),
		  @AMT_CASH = ISNULL(SUM(ISNULL(AMT_CASH+AMT_CHECK-AMT_CASHRCP,0)),0), 
	               @AMT_CASHRCP = ISNULL(SUM(ISNULL(AMT_CASHRCP,0)),0),
	               @AMT_TRUST = ISNULL(SUM(ISNULL(AMT_TRUST,0)),0),
		  @AMT_POINT = ISNULL(SUM(ISNULL(AMT_POINT,0)),0),
		  @AMT_LOSS = ISNULL(SUM(ISNULL(AMT_LOSS,0)),0),
		  @AMT_OKC = ISNULL(SUM(ISNULL(AMT_OKC,0)),0),
		  @AMT_DC    = ISNULL(SUM(ISNULL(DC_TOT,0)),0)
	   FROM  SL_SALE_H
	WHERE CD_STORE = @CD_STORE
	      AND DS_SALE <> 'V'
	      AND YMD_SALE = @YMD_CLOSE
	

	--순매출금액
	SELECT @AMT_SOON = SUM(ISNULL(AMT_SALE,0))-SUM(ISNULL(AMT_TAX,0)) FROM SL_SALE_H WHERE CD_STORE = @CD_STORE AND DS_SALE <> 'V' AND YMD_SALE = @YMD_CLOSE

	--부가세금액
	SELECT @AMT_TAX = ISNULL(SUM(AMT_TAX),0) FROM SL_SALE_H WHERE CD_STORE = @CD_STORE AND YMD_SALE = @YMD_CLOSE AND  DS_SALE <> 'V'

	--과세매출금액
	SELECT @AMT_DUTY= ISNULL(SUM(AMT_DUTY),0)+SUM(ISNULL(AMT_TAX,0))-SUM(ISNULL(AMT_DUTYFREE,0)) FROM SL_SALE_H WHERE CD_STORE = @CD_STORE AND YMD_SALE = @YMD_CLOSE AND  DS_SALE <> 'V'
	--부가세금액
	SELECT @AMT_DUTYFREE = ISNULL(SUM(AMT_DUTYFREE),0) FROM SL_SALE_H WHERE CD_STORE = @CD_STORE AND YMD_SALE = @YMD_CLOSE AND  DS_SALE <> 'V'

	--매출건수
	SET @CNT_SALE = (SELECT COUNT(*) FROM SL_SALE_H WHERE CD_STORE = @CD_STORE AND DS_SALE <> 'V' AND YMD_SALE = @YMD_CLOSE)

	--고객수
	SELECT @CNT_PERSON = COUNT(*) FROM SL_SALE_H WHERE CD_STORE = @CD_STORE AND DS_SALE <> 'V' AND YMD_SALE = @YMD_CLOSE

	--매출원가
	IF @CNT_SALE = 0 
	BEGIN 	
   		--객단가
		SELECT @AMT_SALE_DANGA =  @AMT_SOON  / 1 
	   	   FROM SL_SALE_H
		WHERE CD_STORE = @CD_STORE AND DS_SALE <> 'V' AND YMD_SALE = @YMD_CLOSE
	END
	ELSE
	BEGIN
   		--객단가
		SELECT @AMT_SALE_DANGA =  @AMT_SOON  / CASE WHEN @CNT_PERSON = 0 THEN 1 ELSE @CNT_PERSON END
	   	   FROM SL_SALE_H
		WHERE CD_STORE = @CD_STORE AND DS_SALE <> 'V' AND YMD_SALE = @YMD_CLOSE
	END	
	
	-- 현금거래건수
	SELECT @CNT_CASH = COUNT(*) FROM SL_SALE_H
	WHERE CD_STORE = @CD_STORE AND YMD_SALE = @YMD_CLOSE AND (AMT_CASH <> 0 OR AMT_CHECK <> 0) AND (ABS(AMT_CASH+AMT_CHECK-AMT_CASHRCP) > 0 ) AND DS_SALE <> 'V'

	SELECT @CNT_CASHRCP = COUNT(*) FROM SL_SALE_H
	WHERE CD_STORE = @CD_STORE AND YMD_SALE = @YMD_CLOSE AND AMT_CASHRCP <> 0 AND DS_SALE <> 'V'

	-- 외상거래건수
	SELECT @CNT_TRUST = COUNT(*) FROM SL_SALE_H
	WHERE CD_STORE = @CD_STORE AND YMD_SALE = @YMD_CLOSE AND AMT_TRUST <> 0 AND DS_SALE <> 'V'

	-- 포인트거래건수
	SELECT @CNT_POINT = COUNT(*) FROM SL_SALE_H
	WHERE CD_STORE = @CD_STORE AND YMD_SALE = @YMD_CLOSE AND AMT_POINT <> 0 AND DS_SALE <> 'V'

	-- 손실거래건수
	SELECT @CNT_LOSS = COUNT(*) FROM SL_SALE_H
	WHERE CD_STORE = @CD_STORE AND YMD_SALE = @YMD_CLOSE AND AMT_LOSS <> 0 AND DS_SALE <> 'V'

	
	-- OK캐쉬백거래건수
	SELECT @CNT_OKC = COUNT(*) FROM SL_SALE_H
	WHERE CD_STORE = @CD_STORE AND YMD_SALE = @YMD_CLOSE AND AMT_OKC <> 0 AND DS_SALE <> 'V'

	-- 카드거래건수
	SELECT @CNT_CARD = COUNT(*) FROM SL_CARD
	WHERE CD_STORE = @CD_STORE AND YMD_SALE = @YMD_CLOSE 

	--카드승인취소(외부승인)
	SELECT @AMT_CARD =  SUM(CASE WHEN DS_TRD = '1' THEN AMT_APPROVAL ELSE AMT_APPROVAL*-1 END) 
	  FROM SL_CARD
	WHERE CD_STORE = @CD_STORE AND YMD_SALE = @YMD_CLOSE

	--현미수금잔액, 미지급금잔액
	SELECT @AMT_PAY_TOT  = SUM(ISNULL(AMT_TRUST,0))
	   FROM SL_BUY_H 
	WHERE CD_STORE	=@CD_STORE
	      AND YMD_BUY <= @YMD_CLOSE
	      AND NO_ACCT_REF = ''

	SELECT @AMT_GIVE_TOT = SUM(ISNULL(AMT_TRUST,0))
	   FROM SL_SALE_H
	WHERE CD_STORE	=@CD_STORE
	      AND YMD_SALE <= @YMD_CLOSE
	      AND CD_MEMBER 	<> ''
	      AND DS_SALE <> 'V'

	 SELECT @AMT_GIVE_TOT = @AMT_GIVE_TOT - SUM(ISNULL(AMT_PAYIN,0))
	   FROM SL_ACCT
	  WHERE CD_STORE = @CD_STORE
	    AND YMD_OCCUR <=@YMD_CLOSE 
	    AND ISNULL(CD_MEMBER,'') <> ''

	SELECT @AMT_ACCT_IN  	= SUM(ISNULL(AMT_PAYIN,0)),
		 @AMT_ACCT_OUT	= SUM(ISNULL(AMT_OUT,0))
	   FROM SL_ACCT 
	WHERE CD_STORE	=@CD_STORE
	      AND YMD_OCCUR   = @YMD_CLOSE

	
	DELETE SL_DAY_CLOSE WHERE CD_STORE =@CD_STORE AND YMD_CLOSE = @YMD_CLOSE

	INSERT INTO SL_DAY_CLOSE(
					CD_STORE,
					YMD_CLOSE,
					YN_CLOSE,
					AMT_GIVE_TOT,
					AMT_PAY_TOT,
					AMT_ACCT_IN,
					AMT_ACCT_OUT,
					AMT_CASH,
					CNT_CASH,
					AMT_CASHRCP,
					CNT_CASHRCP,
					AMT_CARD,
					CNT_CARD,
					AMT_TRUST,
					CNT_TRUST,
					AMT_POINT,
					CNT_POINT,
					AMT_LOSS,
					CNT_LOSS,
					AMT_OKC,
					CNT_OKC,
					AMT_DC,
					AMT_SALE_TOT,
					AMT_SALE,
					CNT_SALE,
					CNT_PERSON,
					AMT_SALE_DANGA,
					AMT_SOON,
					AMT_DUTY,
					AMT_DUTYFREE,
					AMT_TAX
					)
			VALUES	(
					@CD_STORE,
					@YMD_CLOSE,
					'Y',
					@AMT_GIVE_TOT,
					@AMT_PAY_TOT,
					@AMT_ACCT_IN,
					@AMT_ACCT_OUT,
					@AMT_CASH,
					@CNT_CASH,
					@AMT_CASHRCP,
					@CNT_CASHRCP,
					@AMT_CARD,
					@CNT_CARD,
					@AMT_TRUST,
					@CNT_TRUST,
					@AMT_POINT,
					@CNT_POINT,
					@AMT_LOSS,
					@CNT_LOSS,
					@AMT_OKC,
					@CNT_OKC,
					@AMT_DC,
					@AMT_SALE_TOT,
					@AMT_SALE,
					@CNT_SALE,
					@CNT_PERSON,
					@AMT_SALE_DANGA,
					@AMT_SOON,
					@AMT_DUTY,
					@AMT_DUTYFREE,
					@AMT_TAX
					)
					
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_ACCT_CARD]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[POS_SAVE_ACCT_CARD]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE POS_SAVE_ACCT_CARD
    @CD_STORE 		VARCHAR(4),
    @YMD_SALE 		VARCHAR(8),
    @NO_POS 			VARCHAR(2),
    @NO_ACCT 			VARCHAR(3),
    @SEQ 			INT,
    @DS_TRD 			VARCHAR(1),
    @NO_CARD 			VARCHAR(20),
    @APPROVAL_ORG 		VARCHAR(10),
    @NO_CHAINPL 		VARCHAR(15),
    @NO_APPROVAL 		VARCHAR(10),
    @NO_ORGUNIQUE		VARCHAR(12),
    @AMT_APPROVAL 		INT,
    @AMT_TIP			INT,
    @AMT_VAT			INT,
    @TERM_HALBU 		VARCHAR(2),
    @TERM_VALID 		VARCHAR(4),
    @TYPE_TRD  		VARCHAR(1),
    @TRD_TIME 		VARCHAR(4),
    @TRD_DATE 		VARCHAR(8),
    @TRD_DATE_ORG 		VARCHAR(8),
    @NM_CARDPL 		VARCHAR(20),
    @CD_CARD_BUY 		VARCHAR(4),
    @NM_CARD_BUY 		VARCHAR(20),
    @REALMODE		VARCHAR(5),
    @IMGFILE			VARCHAR(30),
    @SIGN_IMAGE		IMAGE,
    @NO_VAN			INT
AS
BEGIN

	DECLARE @V_SEQ INT

	SELECT @V_SEQ = ISNULL(MAX(SEQ), 0) + 1
                 FROM SL_ACCT_CARD
	WHERE CD_STORE	=@CD_STORE
	      AND YMD_SALE	=@YMD_SALE
	      AND NO_POS	=@NO_POS
	      AND NO_ACCT	=@NO_ACCT

    INSERT  INTO SL_ACCT_CARD( 
				CD_STORE, 
				YMD_SALE, 
				NO_POS, 
				NO_ACCT, 
				SEQ, 
				DS_TRD, 
				NO_CARD, 
				NO_APPROVAL_ORG, 
				NO_CHAINPL, 
				NO_APPROVAL, 
				NO_ORGUNIQUE, 
				AMT_APPROVAL,
				AMT_TIP,
				TERM_HALBU, 
				TERM_VALID, 
				TYPE_TRD, 
				TRD_TIME, 
				TRD_DATE, 
				TRD_DATE_ORG, 
				NM_CARDPL, 
				CD_CARD_BUY, 
				NM_CARD_BUY,
				REALMODE,
				IMGFILE,				
				SIGN_IMAGE,
				NO_VAN
				)
                    VALUES(
				@CD_STORE, 
				@YMD_SALE, 
				@NO_POS, 
				@NO_ACCT,  
				@V_SEQ, 
				@DS_TRD, 
				@NO_CARD, 
				@APPROVAL_ORG, 
				@NO_CHAINPL, 
				@NO_APPROVAL, 
				@NO_ORGUNIQUE, 
				@AMT_APPROVAL,
				@AMT_TIP,
				@TERM_HALBU,  
				@TERM_VALID, 
				@TYPE_TRD, 
				@TRD_TIME, 
				@TRD_DATE, 
				@TRD_DATE_ORG, 
				@NM_CARDPL, 
				@CD_CARD_BUY, 
				@NM_CARD_BUY,
				@REALMODE,
				@IMGFILE,
				@SIGN_IMAGE,
				@NO_VAN
			)
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

