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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/*******************************************************************
시간대별 판매단가계산
Result 
********************************************************************/

CREATE     FUNCTION [dbo].[GetSalePrice]
    (	@cd_store		varchar(4),
	@pr_sale		int,
	@pr_sale1		int,
	@now_time		varchar(5) )
		returns				int
AS
BEGIN

	declare @conf			varchar(1)
	declare @day_begin		varchar(5)
	declare @day_end		varchar(5)
	declare @night_begin		varchar(5)
	declare @night_end		varchar(5)


	select 	@conf		= substring(conf, 122,1),
		@day_begin	= daytime_begin,
		@day_end    	= daytime_end,
		@night_begin	=nighttime_begin,
		@night_end	=nighttime_end
	  from ms_store where cd_store = @cd_store

	set @day_begin		= substring(@day_begin,1,2)+':'+substring(@day_begin,4,2)
	set @day_end		= substring(@day_end,1,2)+':'+substring(@day_end,4,2)
	set @night_begin	= substring(@night_begin,1,2)+':'+substring(@night_begin,4,2)
	set @night_end		= substring(@night_end,1,2)+':'+substring(@night_end,4,2)


	if @conf = '0'
		return(@pr_sale)
	else
	begin
		if @day_begin > @day_end
		begin
			if ((@now_time >= @day_begin) and (@now_time <= '24:00')) or ((@now_time >= '00:00') and (@now_time <= @day_end)) 
				return(@pr_sale)
		end
		else if @night_begin > @night_end
		begin
     
			if ((@now_time >=@night_begin) and (@now_time <= '24:00')) or ((@now_time >= '00:00') and (@now_time <= @night_end)) 
				return(@pr_sale1)

		end
		if ((@now_time >= @day_begin) and (@now_time <= @day_end))  
				return(@pr_sale)

		if ((@now_time >= @night_begin) and (@now_time <= @night_end))  
				 return(@pr_sale1)
	
		
	end
	return(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].[SELECT_DAY_SALE_PR]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SELECT_DAY_SALE_PR]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE [dbo].[SELECT_DAY_SALE_PR]
 	@CD_STORE		VARCHAR(4),			--점포코드
	@YMD_CLOSE		VARCHAR(8),			--종료일
	@NM_UPJANG01	VARCHAR(20) OUTPUT,	--유형분류01
	@NM_UPJANG02	VARCHAR(20) OUTPUT,	--유형분류02
	@NM_UPJANG03	VARCHAR(20) OUTPUT,	--유형분류03
	@NM_UPJANG04	VARCHAR(20) OUTPUT,	--유형분류04
	@NM_UPJANG05	VARCHAR(20) OUTPUT,	--유형분류05
	@NM_UPJANG06	VARCHAR(20) OUTPUT,	--유형분류06
	@NM_UPJANG07	VARCHAR(20)OUTPUT		--유형분류07
	
AS
BEGIN
	
	-- 유형별 동적생성--
	DECLARE	@CNT				INT,		--카운터
			@CD_UPJANG			VARCHAR(4),	--유형
			@CD_UPJANG01		VARCHAR(4),	--유형분류01
			@CD_UPJANG02		VARCHAR(4),	--유형분류02
			@CD_UPJANG03		VARCHAR(4),	--유형분류03
			@CD_UPJANG04		VARCHAR(4),	--유형분류04
			@CD_UPJANG05		VARCHAR(4),	--유형분류05
			@CD_UPJANG06		VARCHAR(4),	--유형분류06
			@CD_UPJANG07		VARCHAR(4),	--유형분류07
			@NM_UPJANG			VARCHAR(20)	--유형

	SET NOCOUNT ON
	
	DECLARE  CUR_SALE CURSOR FAST_FORWARD
	FOR 
	  SELECT CD_UPJANG, NM_UPJANG FROM MS_UPJANG
	  WHERE CD_STORE = @CD_STORE
	  ORDER BY CD_UPJANG
	  
	  SET @CNT = 1		--커서초기값

	  OPEN CUR_SALE

	  FETCH NEXT FROM CUR_SALE INTO @CD_UPJANG,@NM_UPJANG
	  WHILE (@@FETCH_STATUS = 0)
	  BEGIN

	    IF @CNT = 1 
	    BEGIN
		SET @CD_UPJANG01 = @CD_UPJANG
		SET @NM_UPJANG01 = @NM_UPJANG
	    END
	    ELSE IF @CNT = 2 
	    BEGIN
		SET @CD_UPJANG02 = @CD_UPJANG
		SET @NM_UPJANG02 = @NM_UPJANG
   	    END
	    ELSE IF @CNT = 3 
	    BEGIN
		SET @CD_UPJANG03 = @CD_UPJANG
		SET @NM_UPJANG03 = @NM_UPJANG
	    END
	    ELSE IF @CNT = 4 
	    BEGIN
		SET @CD_UPJANG04 = @CD_UPJANG
		SET @NM_UPJANG04 = @NM_UPJANG
	    END
	    ELSE IF @CNT = 5
	    BEGIN
		SET @CD_UPJANG05 = @CD_UPJANG
		SET @NM_UPJANG05 = @NM_UPJANG
	    END
	    ELSE IF @CNT = 6 
	    BEGIN
		SET @CD_UPJANG06 = @CD_UPJANG
		SET @NM_UPJANG06 = @NM_UPJANG
	    END
	    ELSE IF @CNT = 7 
	    BEGIN
		SET @CD_UPJANG07 = @CD_UPJANG
		SET @NM_UPJANG07 = @NM_UPJANG
	    END

	    SET @CNT = @CNT + 1
	  FETCH NEXT FROM CUR_SALE INTO @CD_UPJANG,@NM_UPJANG

	  END
	CLOSE CUR_SALE
	DEALLOCATE CUR_SALE
-------------------------------------------------------------------------------------------------------------------	


SELECT 	TOP 1 
		 ISNULL(T1.AMT_SALE,0)
		+ ISNULL(T21.AMT_SALE,0)
		+ ISNULL(T5.AMT_SALE,0)
		+ ISNULL(T8.AMT_KEEP,0)
		+ ISNULL(T8.AMT_RENT,0)	
		+ ISNULL(T1.AMT_DC, 0)		AS AMT_SALE_DAY_TOT,

		 ISNULL(T2.AMT_SALE,0)
		+ ISNULL(T6.AMT_SALE,0)
		+ ISNULL(T22.AMT_SALE,0)
		+ ISNULL(T9.AMT_KEEP,0)
		+ ISNULL(T9.AMT_RENT,0)	
		+ ISNULL(T10.AMT_KEEP,0)
		+ ISNULL(T10.AMT_RENT,0)	
		+ ISNULL(T2.AMT_DC, 0)		AS AMT_SALE_DAY_TOT_M,

		 ISNULL(T1.AMT_SALE,0)
		+ ISNULL(T5.AMT_SALE,0)
		+ ISNULL(T8.AMT_KEEP,0)
		+ ISNULL(T8.AMT_RENT,0)		AS AMT_SALE_DAY,

		 ISNULL(T2.AMT_SALE,0)
		+ ISNULL(T6.AMT_SALE,0)
		+ ISNULL(T9.AMT_KEEP,0)
		+ ISNULL(T9.AMT_RENT,0)		
		+ ISNULL(T10.AMT_KEEP,0)
		+ ISNULL(T10.AMT_RENT,0)		AS AMT_SALE_DAY_M
,
		 ISNULL(T17.AMT_JIK,0)		AS AMT_JIK,		
		 ISNULL(T17.AMT_IM,0)			AS AMT_IM,		
		 ISNULL(T18.AMT_JIK,0)		AS AMT_JIK_M,		
		 ISNULL(T18.AMT_IM,0)			AS AMT_IM_M,		

		 ISNULL(T19.AMT_GAIN,0)		AS AMT_GAIN,		
		 ISNULL(T20.AMT_GAIN,0)		AS AMT_GAIN_T,		
		 ISNULL(T1.AMT_CASH,0)		AS AMT_CASH,
		 ISNULL(T1.CNT_CASH,0)		AS CNT_CASH,
		 ISNULL(T1.AMT_CASHRCP,0)		AS AMT_CASHRCP,
		 ISNULL(T1.CNT_CASHRCP,0)		AS CNT_CASHRCP,
		 ISNULL(T1.AMT_CARD,0)		AS AMT_CARD,
		 ISNULL(T1.CNT_CARD,0)		AS CNT_CARD,
		 ISNULL(T1.AMT_TRUST,0)		AS AMT_TRUST,
		 ISNULL(T1.CNT_TRUST,0)		AS CNT_TRUST,
		 ISNULL(T1.AMT_POINT,0)		AS AMT_POINT,
		 ISNULL(T1.CNT_POINT,0)		AS CNT_POINT,
		 ISNULL(T1.AMT_LOSS,0)		AS AMT_LOSS,
		 ISNULL(T1.CNT_LOSS,0)		AS CNT_LOSS,
		 ISNULL(T1.AMT_OKC,0)		AS AMT_OKC,
		 ISNULL(T1.CNT_OKC,0)		AS CNT_OKC,
		 ISNULL(T1.TOT_AMT,0)		AS TOT_AMT,
		 ISNULL(T1.AMT_ACCT_IN,0)		AS AMT_ACCT_IN,
		 ISNULL(T1.AMT_ACCT_OUT,0)		AS AMT_ACCT_OUT,
		 ISNULL(T1.AMT_ACCT,0)		AS AMT_ACCT,
		 ISNULL(T1.AMT_SALE_TOT,0)		AS AMT_SALE_TOT,
		 ISNULL(T1.AMT_SALE,0)		AS AMT_SALE,
		 ISNULL(T1.AMT_TAX, 0)		AS AMT_TAX,
		 ISNULL(T1.AMT_SOON, 0)		AS AMT_SOON,
		 ISNULL(T1.AMT_DUTY, 0)		AS AMT_DUTY,
		 ISNULL(T1.AMT_DUTYFREE, 0)	AS AMT_DUTYFREE,
		 ISNULL(T1.AMT_DC, 0)			AS AMT_DC,
		 ISNULL(T2.AMT_CASH,0)		AS AMT_CASH_M,
		 ISNULL(T2.CNT_CASH,0)		AS CNT_CASH_M,
		 ISNULL(T2.AMT_CASHRCP,0)		AS AMT_CASHRCP_M,
		 ISNULL(T2.CNT_CASHRCP,0)		AS CNT_CASHRCP_M,
		 ISNULL(T2.AMT_CARD,0)		AS AMT_CARD_M,
		 ISNULL(T2.CNT_CARD,0)		AS CNT_CARD_M,
		 ISNULL(T2.AMT_TRUST,0)		AS AMT_TRUST_M,
		 ISNULL(T2.CNT_TRUST,0)		AS CNT_TRUST_M,
		 ISNULL(T2.AMT_POINT,0)		AS AMT_POINT_M,
		 ISNULL(T2.CNT_POINT,0)		AS CNT_POINT_M,
		 ISNULL(T2.AMT_LOSS,0)		AS AMT_LOSS_M,
		 ISNULL(T2.CNT_LOSS,0)		AS CNT_LOSS_M,
		 ISNULL(T2.AMT_OKC,0)		AS AMT_OKC_M,
		 ISNULL(T2.CNT_OKC,0)		AS CNT_OKC_M,
		 ISNULL(T2.TOT_AMT,0)		AS TOT_AMT_M,
		 ISNULL(T2.AMT_ACCT_IN,0)		AS AMT_ACCT_IN_M,
		 ISNULL(T2.AMT_ACCT_OUT,0)		AS AMT_ACCT_OUT_M,
		 ISNULL(T2.AMT_ACCT,0)		AS AMT_ACCT_M,
		 ISNULL(T2.AMT_SALE_TOT,0)		AS AMT_SALE_TOT_M,
		 ISNULL(T2.AMT_SALE,0)		AS AMT_SALE_M,
		 ISNULL(T2.AMT_TAX, 0)		AS AMT_TAX_M,
		 ISNULL(T2.AMT_SOON, 0)		AS AMT_SOON_M,
		 ISNULL(T2.AMT_DUTY, 0)		AS AMT_DUTY_M,
		 ISNULL(T2.AMT_DUTYFREE, 0)	AS AMT_DUTYFREE_M,
		 ISNULL(T2.AMT_DC, 0)			AS AMT_DC_M,
		 ISNULL(T3.AMT_TOT01,0)		AS AMT_TOT01,
		 ISNULL(T3.AMT_TOT02,0)		AS AMT_TOT02,
		 ISNULL(T3.AMT_TOT03,0)		AS AMT_TOT03,
		 ISNULL(T3.AMT_TOT04,0)		AS AMT_TOT04,
		 ISNULL(T3.AMT_TOT05,0)		AS AMT_TOT05,
		 ISNULL(T3.AMT_TOT06,0)		AS AMT_TOT06,
		 ISNULL(T3.AMT_TOT07,0)		AS AMT_TOT07,
		 ISNULL(T3.AMT_TOT,0)		AS AMT_TOT,
		 ISNULL(T4.AMT_TOT01,0)		AS AMT_TOT01_M,
		 ISNULL(T4.AMT_TOT02,0)		AS AMT_TOT02_M,
		 ISNULL(T4.AMT_TOT03,0)		AS AMT_TOT03_M,
		 ISNULL(T4.AMT_TOT04,0)		AS AMT_TOT04_M,
		 ISNULL(T4.AMT_TOT05,0)		AS AMT_TOT05_M,
		 ISNULL(T4.AMT_TOT06,0)		AS AMT_TOT06_M,
		 ISNULL(T4.AMT_TOT07,0)		AS AMT_TOT07_M,
		 ISNULL(T4.AMT_TOT,0)		AS AMT_TOT_M,
		 ISNULL(T5.QTY_SALE,0)		AS QTY_SALE_MEM,
		 ISNULL(T5.AMT_SALE,0)		AS AMT_SALE_MEM,
		 ISNULL(T5.QTY_REFUND,0)		AS QTY_REFUND_MEM,
		 ISNULL(T5.AMT_REFUND,0)		AS AMT_REFUND_MEM,
		 ISNULL(T5.QTY_SALE,0)
		-ISNULL(T5.QTY_REFUND,0)		AS QTY_SALE_MEM_TOT,
		 ISNULL(T5.AMT_SALE,0)
		-ISNULL(T5.AMT_REFUND,0)		AS AMT_SALE_MEM_TOT,
		 ISNULL(T5.QTY_FINISH,0)		AS QTY_FINISH_MEM,
		 ISNULL(T5.QTY_DELAY,0)		AS QTY_DELAY_MEM,
		 ISNULL(T6.QTY_SALE,0)		AS QTY_SALE_MEM_M,
		 ISNULL(T6.AMT_SALE,0)		AS AMT_SALE_MEM_M,
		 ISNULL(T6.QTY_REFUND,0)		AS QTY_REFUND_MEM_M,
		 ISNULL(T6.AMT_REFUND,0)		AS AMT_REFUND_MEM_M,
		 ISNULL(T6.QTY_SALE,0)
		-ISNULL(T6.QTY_REFUND,0)		AS QTY_SALE_MEM_TOT_M,
		 ISNULL(T6.AMT_SALE,0)
		-ISNULL(T6.AMT_REFUND,0)		AS AMT_SALE_MEM_TOT_M,
		 ISNULL(T6.QTY_FINISH,0)		AS QTY_FINISH_MEM_M,
		 ISNULL(T7.QTY_SALE,0)		AS QTY_SALE_MEM_T,
		 ISNULL(T7.AMT_SALE,0)		AS AMT_SALE_MEM_T,
		 ISNULL(T7.QTY_REFUND,0)		AS QTY_REFUND_MEM_T,
		 ISNULL(T7.AMT_REFUND,0)		AS AMT_REFUND_MEM_T,
		 ISNULL(T7.QTY_SALE,0)
		-ISNULL(T7.QTY_REFUND,0)		AS QTY_SALE_MEM_TOT_T,
		 ISNULL(T7.AMT_SALE,0)
		-ISNULL(T7.AMT_REFUND,0)		AS AMT_SALE_MEM_TOT_T,

		 ISNULL(T7.QTY_FINISH,0)		AS QTY_FINISH_MEM_T,
		 ISNULL(T8.QTY_SALE,0)		AS QTY_SALE_LOCK,
		 ISNULL(T8.AMT_KEEP,0)		AS AMT_KEEP_LOCK,		
		 ISNULL(T8.AMT_RENT,0)		AS AMT_RENT_LOCK,		
		 ISNULL(T8.AMT_KEEP,0)	
		+ ISNULL(T8.AMT_RENT,0)		AS AMT_LOCK_TOT,		
		 ISNULL(T9.QTY_SALE,0)	
		+ ISNULL(T10.QTY_SALE,0)		AS QTY_SALE_LOCK_M,
		 ISNULL(T9.AMT_KEEP,0)		
		+ ISNULL(T10.AMT_KEEP,0)		AS AMT_KEEP_LOCK_M,		
		 ISNULL(T9.AMT_RENT,0)		
		+ ISNULL(T10.AMT_RENT,0)		AS AMT_RENT_LOCK_M,		
		 ISNULL(T9.AMT_KEEP,0)		
		+ ISNULL(T10.AMT_KEEP,0)	
		+ ISNULL(T9.AMT_RENT,0)		
		+ ISNULL(T10.AMT_RENT,0)		AS AMT_LOCK_TOT_M,
		 ISNULL(T11.QTY_SALE,0)		AS QTY_SALE_TICKET,
		 ISNULL(T11.QTY_MAN,0)		AS QTY_MAN_TICKET,
		 ISNULL(T11.QTY_WOMAN,0)		AS QTY_WOMAN_TICKET,
		 ISNULL(T11.AMT_SALE,0)		AS AMT_SALE_TICKET,	
		 ISNULL(T12.QTY_SALE,0)		AS QTY_SALE_TICKET_M,
		 ISNULL(T12.QTY_MAN,0)		AS QTY_MAN_TICKET_M,
		 ISNULL(T12.QTY_WOMAN,0)		AS QTY_WOMAN_TICKET_M,
		 ISNULL(T12.AMT_SALE,0)		AS AMT_SALE_TICKET_M,		
		 ISNULL(T13.CNT_MAN,0)		AS QTY_MAN,
		 ISNULL(T13.CNT_WOMAN,0)		AS QTY_WOMAN,
		 ISNULL(T13.CNT_MAN,0)
		+ISNULL(T13.CNT_WOMAN,0)		AS QTY_TICKET_TOT,
		 ISNULL(T14.CNT_MAN,0)		AS QTY_MAN_M,
		 ISNULL(T14.CNT_WOMAN,0)		AS QTY_WOMAN_M,
		 ISNULL(T14.CNT_MAN,0)
		+ISNULL(T14.CNT_WOMAN,0)		AS QTY_TICKET_TOT_M,
		 ISNULL(T15.AMT_TRUST,0)		AS AMT_TRUST_TOT,		
		 ISNULL(T16.AMT_BUY,0)		AS AMT_BUY_TOT,
		 ISNULL(T21.CNT_SALE,0)		AS CNT_COUPON,
		 ISNULL(T21.AMT_SALE,0)		AS AMT_COUPON,
		 ISNULL(T22.CNT_SALE,0)		AS CNT_COUPON_M,
		 ISNULL(T22.AMT_SALE,0)		AS AMT_COUPON_M		
FROM
	(
	SELECT
	             ISNULL(AMT_CASH,0) 			AS AMT_CASH,			--현금입금
	             ISNULL(CNT_CASH,0) 			AS CNT_CASH,			--현금입금
	             ISNULL(AMT_CASHRCP,0) 		AS AMT_CASHRCP,		--현금입금
	             ISNULL(CNT_CASHRCP,0) 		AS CNT_CASHRCP,		--현금입금
		ISNULL(AMT_CARD,0)			AS AMT_CARD,			--신용카드
		ISNULL(CNT_CARD,0)			AS CNT_CARD,		--신용단말기
		ISNULL(AMT_TRUST,0)			AS AMT_TRUST,			--외상
		ISNULL(CNT_TRUST,0)			AS CNT_TRUST,			--외상
		ISNULL(AMT_POINT,0)			AS AMT_POINT,			--외상
		ISNULL(CNT_POINT,0)			AS CNT_POINT,			--외상
		ISNULL(AMT_LOSS,0)			AS AMT_LOSS,			--외상
		ISNULL(CNT_LOSS,0)			AS CNT_LOSS,			--외상
		ISNULL(AMT_OKC,0)			AS AMT_OKC,			--외상
		ISNULL(CNT_OKC,0)			AS CNT_OKC,			--외상
     	            ( ISNULL(AMT_CASH , 0)
	            + ISNULL(AMT_CASHRCP, 0) 
	            + ISNULL(AMT_CARD, 0) 
       	            + ISNULL(AMT_TRUST, 0)
       	            + ISNULL(AMT_POINT, 0)
       	            + ISNULL(AMT_LOSS, 0)
	            + ISNULL(AMT_OKC , 0)) 		AS TOT_AMT,			--입금계
       		ISNULL(AMT_ACCT_IN, 0) 		AS AMT_ACCT_IN, 		--입금액 
		ISNULL(AMT_ACCT_OUT, 0	)	AS AMT_ACCT_OUT,		--출금액
		ISNULL(AMT_ACCT, 0)			AS AMT_ACCT,			--현출납액 
		ISNULL(AMT_SALE_TOT, 0)		AS AMT_SALE_TOT,		--매출액
		ISNULL(AMT_TAX, 0)			AS AMT_TAX,
		ISNULL(AMT_SALE, 0)			AS AMT_SALE,			--매출액
		ISNULL(AMT_SOON,0)			AS AMT_SOON,
		ISNULL(AMT_DUTY,0)			AS AMT_DUTY,
		ISNULL(AMT_DUTYFREE, 0)		AS AMT_DUTYFREE,
		ISNULL(AMT_DC,0)			AS AMT_DC

	   FROM SL_DAY_CLOSE
	 WHERE CD_STORE   = @CD_STORE
	      AND YMD_CLOSE = @YMD_CLOSE
	)T1,
	(
	 SELECT 
	             ISNULL(SUM(AMT_CASH),0)		AS AMT_CASH,		--현금입금
	             ISNULL(SUM(CNT_CASH),0)		AS CNT_CASH,		--현금입금
	             ISNULL(SUM(AMT_CASHRCP),0)	AS AMT_CASHRCP,	--현금입금
	             ISNULL(SUM(CNT_CASHRCP),0)	AS CNT_CASHRCP,	--현금입금
		ISNULL(SUM(AMT_CARD),0)		AS AMT_CARD,		--신용카드
	             ISNULL(SUM(CNT_CARD),0)		AS CNT_CARD,		--현금입금
		ISNULL(SUM(AMT_TRUST),0)		AS AMT_TRUST,	--외상
	             ISNULL(SUM(CNT_TRUST),0)		AS CNT_TRUST,		--현금입금
		ISNULL(SUM(AMT_POINT),0)		AS AMT_POINT,	--외상
	             ISNULL(SUM(CNT_POINT),0)		AS CNT_POINT,		--현금입금
		ISNULL(SUM(AMT_LOSS),0)		AS AMT_LOSS,	--외상
	             ISNULL(SUM(CNT_LOSS),0)		AS CNT_LOSS,		--현금입금
		ISNULL(SUM(AMT_OKC),0)		AS AMT_OKC,	--외상
	             ISNULL(SUM(CNT_OKC),0)		AS CNT_OKC,		--현금입금
     	            ( ISNULL(SUM(AMT_CASH) , 0) 
	            + ISNULL(SUM(AMT_CASHRCP), 0) 
	            + ISNULL(SUM(AMT_CARD), 0) 
       	            + ISNULL(SUM(AMT_TRUST), 0)
       	            + ISNULL(SUM(AMT_POINT), 0)
       	            + ISNULL(SUM(AMT_LOSS), 0)
	            + ISNULL(SUM(AMT_OKC) , 0)) 	AS TOT_AMT,		--입금계
       		ISNULL(SUM(AMT_ACCT_IN), 0) 	AS AMT_ACCT_IN, 	--입금액 
		ISNULL(SUM(AMT_ACCT_OUT), 0)	AS AMT_ACCT_OUT,	--출금액
		ISNULL(SUM(AMT_ACCT), 0)		AS AMT_ACCT,		--현출납액 
		ISNULL(SUM(AMT_SALE_TOT), 0)	AS AMT_SALE_TOT,	--매출액
		ISNULL(SUM(AMT_SALE), 0)		AS AMT_SALE,		--매출액
		ISNULL(SUM(AMT_TAX), 0)		AS AMT_TAX,
		ISNULL(SUM(AMT_SOON),0)		AS AMT_SOON,
		ISNULL(SUM(AMT_DUTY),0)		AS AMT_DUTY,
		ISNULL(SUM(AMT_DUTYFREE), 0)	AS AMT_DUTYFREE,
		ISNULL(SUM(AMT_DC), 0)		AS AMT_DC
	    FROM SL_DAY_CLOSE  
	  WHERE CD_STORE = @CD_STORE
	       AND YMD_CLOSE BETWEEN SUBSTRING(@YMD_CLOSE,1,6) +'00'
	       AND  @YMD_CLOSE
	)T2,
	(
	 SELECT SUM(ISNULL(GU.AMT_TOT01,0))	AS AMT_TOT01,		
		SUM(ISNULL(GU.AMT_TOT02,0))	AS AMT_TOT02,
		SUM(ISNULL(GU.AMT_TOT03,0))	AS AMT_TOT03,
		SUM(ISNULL(GU.AMT_TOT04,0))	AS AMT_TOT04,
		SUM(ISNULL(GU.AMT_TOT05,0))	AS AMT_TOT05,
		SUM(ISNULL(GU.AMT_TOT06,0))	AS AMT_TOT06,
		SUM(ISNULL(GU.AMT_TOT07,0))	AS AMT_TOT07,
		SUM(ISNULL(GU.AMT_TOT,0))		AS AMT_TOT
	   FROM 
		(
		  SELECT 
			 CASE B.CD_UPJANG WHEN @CD_UPJANG01 THEN SUM(ISNULL(A.AMT_SALE,0) )
			 END AMT_TOT01,
			 CASE B.CD_UPJANG WHEN @CD_UPJANG02 THEN SUM(ISNULL(A.AMT_SALE,0) )
			 END AMT_TOT02,
			 CASE B.CD_UPJANG WHEN @CD_UPJANG03 THEN SUM(ISNULL(A.AMT_SALE,0) )
			 END AMT_TOT03,
			 CASE B.CD_UPJANG WHEN @CD_UPJANG04 THEN SUM(ISNULL(A.AMT_SALE,0) )
			 END AMT_TOT04,
			 CASE B.CD_UPJANG WHEN @CD_UPJANG05 THEN SUM(ISNULL(A.AMT_SALE,0) )
			 END AMT_TOT05,
			 CASE B.CD_UPJANG WHEN @CD_UPJANG06 THEN SUM(ISNULL(A.AMT_SALE,0) )
			 END AMT_TOT06,
			 CASE B.CD_UPJANG WHEN @CD_UPJANG07 THEN SUM(ISNULL(A.AMT_SALE,0) )
			 END AMT_TOT07,
			 SUM(ISNULL(A.AMT_SALE,0) ) AS AMT_TOT
		    FROM SL_SALE_D A, MS_MENU B, SL_SALE_H C
		  WHERE A.CD_STORE	= @CD_STORE
		        AND A.YMD_SALE = @YMD_CLOSE
		        AND A.CD_STORE = B.CD_STORE
		        AND A.CD_MENU	= B.CD_MENU
		        AND A.CD_STORE = C.CD_STORE
                       	        AND A.YMD_SALE = C.YMD_SALE                                                 
                                   AND A.NO_POS   	= C.NO_POS
                                   AND A.NO_RCP   	= C.NO_RCP   
		        AND C.DS_SALE  <> 'V'
		  GROUP BY  B.CD_UPJANG
		)GU
   	)T3,							--유형별
	(
	 SELECT SUM(ISNULL(GU.AMT_TOT01,0))	AS AMT_TOT01,		
		SUM(ISNULL(GU.AMT_TOT02,0))	AS AMT_TOT02,
		SUM(ISNULL(GU.AMT_TOT03,0))	AS AMT_TOT03,
		SUM(ISNULL(GU.AMT_TOT04,0))	AS AMT_TOT04,
		SUM(ISNULL(GU.AMT_TOT05,0))	AS AMT_TOT05,
		SUM(ISNULL(GU.AMT_TOT06,0))	AS AMT_TOT06,
		SUM(ISNULL(GU.AMT_TOT07,0))	AS AMT_TOT07,
		SUM(ISNULL(GU.AMT_TOT,0))		AS AMT_TOT
	   FROM 
		(
		  SELECT
			 CASE B.CD_UPJANG WHEN @CD_UPJANG01 THEN SUM(ISNULL(A.AMT_SALE,0) )
			 END AMT_TOT01,
			 CASE B.CD_UPJANG WHEN @CD_UPJANG02 THEN SUM(ISNULL(A.AMT_SALE,0) )

			 END AMT_TOT02,
			 CASE B.CD_UPJANG WHEN @CD_UPJANG03 THEN SUM(ISNULL(A.AMT_SALE,0) )
			 END AMT_TOT03,
			 CASE B.CD_UPJANG WHEN @CD_UPJANG04 THEN SUM(ISNULL(A.AMT_SALE,0) )
			 END AMT_TOT04,
			 CASE B.CD_UPJANG WHEN @CD_UPJANG05 THEN SUM(ISNULL(A.AMT_SALE,0) )
			 END AMT_TOT05,
			 CASE B.CD_UPJANG WHEN @CD_UPJANG06 THEN SUM(ISNULL(A.AMT_SALE,0) )
			 END AMT_TOT06,
			 CASE B.CD_UPJANG WHEN @CD_UPJANG07 THEN SUM(ISNULL(A.AMT_SALE,0) )
			 END AMT_TOT07,
			 SUM( ISNULL(A.AMT_SALE,0)) AS AMT_TOT
		    FROM	 SL_SALE_D A, MS_MENU B, SL_SALE_H C
		  WHERE A.CD_STORE = @CD_STORE
	       	        AND A.YMD_SALE BETWEEN SUBSTRING(@YMD_CLOSE,1,6) +'00' AND @YMD_CLOSE
		        AND A.CD_STORE = B.CD_STORE
		        AND A.CD_MENU = B.CD_MENU
		       AND A.CD_STORE = C.CD_STORE
		       AND A.YMD_SALE = C.YMD_SALE                                                 
		       AND A.NO_POS   = C.NO_POS
		       AND A.NO_RCP   = C.NO_RCP   
		       AND C.DS_SALE <> 'V'
		  GROUP BY  B.CD_UPJANG
		)GU
   	) T4,									
	( 					--회원가입현황 ( 일 계 )
	 SELECT A.QTY_SALE,
		  A.AMT_SALE,
		  B.QTY_REFUND,
		  B.AMT_REFUND,
		  C.QTY_FINISH,
		  D.QTY_DELAY
	    FROM 
		(
		 SELECT COUNT(*) 		AS QTY_SALE,
			  SUM(AMT_SALE)	AS AMT_SALE
		    FROM SL_MEMBER_JOIN
	 	 WHERE YMD_SALE	=@YMD_CLOSE
		) A,
		(
		 SELECT COUNT(*) 		AS QTY_REFUND,
			  SUM(AMT_REFUND)	AS AMT_REFUND
		    FROM SL_MEMBER_REFUND
	 	 WHERE YMD_REFUND	=@YMD_CLOSE 
		) B,
		(
		 SELECT COUNT(*)	 AS QTY_FINISH
		    FROM SL_MEMBER_JOIN
	 	 WHERE YMD_FINISH	=@YMD_CLOSE
		       AND DS_STATUS	='2'
		) C,
		(
		 SELECT COUNT(*)	 AS QTY_DELAY
		    FROM SL_MEMBER_DELAY
	 	 WHERE YMD_DELAY	=@YMD_CLOSE
		) D
	) T5,
	( 					--회원가입현황 ( 월 계 )
	 SELECT A.QTY_SALE,
		  A.AMT_SALE,
		  B.QTY_REFUND,
		  B.AMT_REFUND,
		  C.QTY_FINISH
	    FROM 
		(
		 SELECT COUNT(*) 		AS QTY_SALE,
			  SUM(AMT_SALE)	AS AMT_SALE
		    FROM SL_MEMBER_JOIN
	 	 WHERE  YMD_SALE BETWEEN SUBSTRING(@YMD_CLOSE,1,6) +'00' AND @YMD_CLOSE
		) A,
		(
		 SELECT COUNT(*) 		AS QTY_REFUND,
			  SUM(AMT_REFUND)	AS AMT_REFUND
		    FROM SL_MEMBER_REFUND
	 	 WHERE YMD_REFUND	BETWEEN SUBSTRING(@YMD_CLOSE,1,6) +'00' AND @YMD_CLOSE
		) B,
		(
		 SELECT COUNT(*)	 AS QTY_FINISH
		    FROM SL_MEMBER_JOIN
	 	 WHERE YMD_FINISH	BETWEEN SUBSTRING(@YMD_CLOSE,1,6) +'00' AND @YMD_CLOSE
		       AND DS_STATUS	='2'
		) C
	) T6,
	( 					--회원가입현황 ( 월 계 )
	 SELECT A.QTY_SALE,
		  A.AMT_SALE,
		  B.QTY_REFUND,
		  B.AMT_REFUND,
		  C.QTY_FINISH
	    FROM 
		(
		 SELECT COUNT(*) 		AS QTY_SALE,
			  SUM(AMT_SALE)	AS AMT_SALE
		    FROM SL_MEMBER_JOIN
		) A,
		(
		 SELECT COUNT(*) 		AS QTY_REFUND,
			  SUM(AMT_REFUND)	AS AMT_REFUND
		    FROM SL_MEMBER_REFUND
		) B,
		(
		 SELECT COUNT(*)	 AS QTY_FINISH
		    FROM SL_MEMBER_JOIN
		 WHERE  DS_STATUS	='2'
		) C
	) T7,
	(
	 SELECT COUNT(*)		AS QTY_SALE,
		  SUM(AMT_KEEP)	AS AMT_KEEP,
		  SUM(AMT_RENT)	AS AMT_RENT
	    FROM SL_LOCKER
	 WHERE CD_STORE	=@CD_STORE
	       AND YMD_SALE	=@YMD_CLOSE
	) T8,
	(
	 SELECT COUNT(*)		AS QTY_SALE,
		  SUM(AMT_KEEP)	AS AMT_KEEP,
		  SUM(AMT_RENT)	AS AMT_RENT
	    FROM SL_LOCKER
	 WHERE CD_STORE	=@CD_STORE
	       AND YMD_SALE	BETWEEN SUBSTRING(@YMD_CLOSE,1,6) +'00' AND @YMD_CLOSE
	) T9,
	(
	 SELECT COUNT(*)		AS QTY_SALE,
		  SUM(AMT_KEEP)	AS AMT_KEEP,
		  SUM(AMT_RENT)	AS AMT_RENT
	    FROM SL_LOCKER_LOG
	 WHERE CD_STORE	=@CD_STORE
	       AND YMD_SALE	BETWEEN SUBSTRING(@YMD_CLOSE,1,6) +'00' AND @YMD_CLOSE
	) T10,
	(
	 SELECT SUM(A.QTY_SALE)	AS QTY_SALE,
		 SUM( CASE WHEN B.DS_GENDER ='0' THEN A.QTY_SALE ELSE 0 END ) AS QTY_MAN,
		 SUM( CASE WHEN B.DS_GENDER ='0' THEN 0 ELSE A.QTY_SALE END ) AS QTY_WOMAN,
		  SUM(A.AMT_SALE)	AS AMT_SALE
	    FROM SL_SALE_D A, MS_MENU B, SL_SALE_H C
	  WHERE A.CD_STORE	= @CD_STORE
	        AND A.YMD_SALE = @YMD_CLOSE
	        AND A.CD_STORE = B.CD_STORE
	        AND A.CD_MENU	= B.CD_MENU
	        AND A.CD_STORE = C.CD_STORE
                     AND A.YMD_SALE = C.YMD_SALE                                                 
                     AND A.NO_POS   	= C.NO_POS
                     AND A.NO_RCP   	= C.NO_RCP   
	        AND C.DS_SALE  <> 'V'
	        AND B.DS_MENU_TYPE = '0'
	) T11,
	(
	 SELECT SUM(A.QTY_SALE)	AS QTY_SALE,
		 SUM( CASE WHEN B.DS_GENDER ='0' THEN A.QTY_SALE ELSE 0 END ) AS QTY_MAN,
		 SUM( CASE WHEN B.DS_GENDER ='0' THEN 0 ELSE A.QTY_SALE END ) AS QTY_WOMAN,
		  SUM(A.AMT_SALE)	AS AMT_SALE
	    FROM SL_SALE_D A, MS_MENU B, SL_SALE_H C
	  WHERE A.CD_STORE	= @CD_STORE
	        AND A.CD_STORE = B.CD_STORE
	        AND A.CD_MENU	= B.CD_MENU
	        AND A.CD_STORE = C.CD_STORE
                     AND A.YMD_SALE = C.YMD_SALE                                                 
                     AND A.NO_POS   	= C.NO_POS
                     AND A.NO_RCP   	= C.NO_RCP   
	        AND C.DS_SALE  <> 'V'
	        AND B.DS_MENU_TYPE = '0'
                     AND A.YMD_SALE	BETWEEN SUBSTRING(@YMD_CLOSE,1,6) +'00' AND @YMD_CLOSE
	) T12,
	(
	SELECT SUM(A.CNT_MAN) 	AS	CNT_MAN,
		 SUM(A.CNT_WOMAN)	AS 	CNT_WOMAN
	  FROM
		(
		SELECT CASE DS_LOCK WHEN '001' THEN 1   END AS CNT_MAN,
			 CASE DS_LOCK WHEN '002' THEN 1   END AS CNT_WOMAN
		   FROM SL_LOCK_ALLOT
		WHERE CD_STORE  =@CD_STORE
		      AND YMD_SALE  = @YMD_CLOSE
		) A
	) T13,
	(
	SELECT SUM(A.CNT_MAN) 	AS	CNT_MAN,
		 SUM(A.CNT_WOMAN)	AS 	CNT_WOMAN
	  FROM
		(
		SELECT CASE DS_LOCK WHEN '001' THEN 1   END AS CNT_MAN,
			 CASE DS_LOCK WHEN '002' THEN 1   END AS CNT_WOMAN
		   FROM SL_LOCK_ALLOT
		WHERE CD_STORE  =@CD_STORE
		      AND YMD_SALE  BETWEEN SUBSTRING(@YMD_CLOSE,1,6) +'00' AND @YMD_CLOSE
		) A
	) T14,
	(
	 SELECT SUM(AMT_TRUST)	AS AMT_TRUST
	    FROM SL_SALE_H 
	WHERE CD_STORE	=@CD_STORE
	      AND YMD_SALE	=@YMD_CLOSE
	      AND DS_SALE	<> 'V'
	      AND NO_ACCT_REF = ''
	) T15,
	(
	 SELECT SUM(AMT_BUY)	AS AMT_BUY
	    FROM SL_BUY_H
	WHERE CD_STORE	=@CD_STORE
	      AND DS_BUY 	= '1'
	      AND NO_ACCT_REF = ''
	) T16,
	(
	 SELECT SUM(A.AMT_JIK)	AS AMT_JIK,
		  SUM(A.AMT_IM)		AS AMT_IM
	    FROM
		(
		 SELECT CASE D.DS_UPJANG WHEN '0'	THEN A.AMT_SALE END AS AMT_JIK,
			  CASE D.DS_UPJANG WHEN '1'          	THEN A.AMT_SALE END AS AMT_IM
		    FROM SL_SALE_D A, MS_MENU B, SL_SALE_H C, MS_UPJANG D
		  WHERE A.CD_STORE	= @CD_STORE
		        AND A.YMD_SALE = @YMD_CLOSE
		        AND A.CD_STORE = B.CD_STORE
		        AND A.CD_MENU	= B.CD_MENU
		        AND A.CD_STORE = C.CD_STORE
	                     AND A.YMD_SALE = C.YMD_SALE                                                 
	                     AND A.NO_POS   	= C.NO_POS
	                     AND A.NO_RCP   	= C.NO_RCP   
		       AND B.CD_STORE = D.CD_STORE
		       AND B.CD_UPJANG = D.CD_UPJANG
		        AND C.DS_SALE  <> 'V'
		) A
	) T17,
	(
	 SELECT SUM(A.AMT_JIK)	AS AMT_JIK,
		  SUM(A.AMT_IM)		AS AMT_IM
	    FROM
		(
		 SELECT CASE D.DS_UPJANG WHEN '0'	THEN A.AMT_SALE END AS AMT_JIK,
			  CASE D.DS_UPJANG WHEN '1'          	THEN A.AMT_SALE END AS AMT_IM
		    FROM SL_SALE_D A, MS_MENU B, SL_SALE_H C, MS_UPJANG D
		  WHERE A.CD_STORE	= @CD_STORE
		        AND A.YMD_SALE BETWEEN SUBSTRING(@YMD_CLOSE,1,6) +'00' AND @YMD_CLOSE
		        AND A.CD_STORE = B.CD_STORE
		        AND A.CD_MENU	= B.CD_MENU
		        AND A.CD_STORE = C.CD_STORE
	                     AND A.YMD_SALE = C.YMD_SALE                                                 
	                     AND A.NO_POS   	= C.NO_POS
	                     AND A.NO_RCP   	= C.NO_RCP   
		       AND B.CD_STORE = D.CD_STORE
		       AND B.CD_UPJANG = D.CD_UPJANG
		        AND C.DS_SALE  <> 'V'
		) A
	) T18,
	(
	SELECT
		ISNULL(T.AMT_CASH, 0) * (A.CASH_FEE / 100) 
	           +ISNULL(T.AMT_CARD, 0) * (A.CARD_FEE / 100) AS AMT_GAIN
	   FROM MS_UPJANG A,
		(
		SELECT T.CD_UPJANG,
			 SUM(T.AMT_CASH)	AS AMT_CASH,
			 SUM(T.AMT_CARD)	AS AMT_CARD
		  FROM 
			(
			SELECT T2.CD_UPJANG,
				T1.YMD_SALE,
				T1.NO_POS,
				T1.NO_RCP,
				( ((T1.AMT_SALE-T1.AMT_CARD) / DBO.ISZERO(T1.AMT_SALE) * 100 ) / 100 ) * T2.AMT_SALE AS AMT_CASH,
				T2.AMT_SALE 
				- (( ((T1.AMT_SALE-T1.AMT_CARD) / DBO.ISZERO(T1.AMT_SALE) * 100 ) / 100 ) * T2.AMT_SALE) AS AMT_CARD
			   FROM SL_SALE_H T1,
				(
				SELECT T1.YMD_SALE,
					T1.NO_POS,
					T1.NO_RCP,
					T2.CD_UPJANG,
					SUM(T1.AMT_SALE-T1.DC_TOT) AS AMT_SALE
				   FROM SL_SALE_D T1,
					 MS_MENU T2
				WHERE  T1.CD_STORE	=T2.CD_STORE
					AND T1.CD_MENU 	= T2.CD_MENU
					AND T1.CD_STORE = @CD_STORE
					AND T1.YMD_SALE = @YMD_CLOSE
				GROUP BY T1.YMD_SALE, T1.NO_POS, T1.NO_RCP, T2.CD_UPJANG
				) T2
			WHERE  T1.YMD_SALE      = T2.YMD_SALE
				AND T1.NO_POS    = T2.NO_POS
				AND T1.NO_RCP    = T2.NO_RCP
				AND T1.DS_SALE  <> 'V'
				AND T1.CD_STORE = @CD_STORE
				AND T1.YMD_SALE = @YMD_CLOSE
				AND T2.AMT_SALE <> 0
			) T
		GROUP BY T.CD_UPJANG		
		) T
	WHERE A.CD_STORE =  @CD_STORE
	) T19,
	(
	SELECT
		ISNULL(T.AMT_CASH, 0) * (A.CASH_FEE / 100) 
	           +ISNULL(T.AMT_CARD, 0) * (A.CARD_FEE / 100) AS AMT_GAIN
	   FROM MS_UPJANG A,
		(
		SELECT T.CD_UPJANG,
			 SUM(T.AMT_CASH)	AS AMT_CASH,
			 SUM(T.AMT_CARD)	AS AMT_CARD
		  FROM 
			(
			SELECT T2.CD_UPJANG,
				T1.YMD_SALE,
				T1.NO_POS,
				T1.NO_RCP,
				( ((T1.AMT_SALE-T1.AMT_CARD) / DBO.ISZERO(T1.AMT_SALE) * 100 ) / 100 ) * T2.AMT_SALE AS AMT_CASH,
				T2.AMT_SALE 
				- (( ((T1.AMT_SALE-T1.AMT_CARD) / DBO.ISZERO(T1.AMT_SALE) * 100 ) / 100 ) * T2.AMT_SALE) AS AMT_CARD
			   FROM SL_SALE_H T1,
				(
				SELECT T1.YMD_SALE,
					T1.NO_POS,
					T1.NO_RCP,
					T2.CD_UPJANG,
					SUM(T1.AMT_SALE-T1.DC_TOT) AS AMT_SALE
				   FROM SL_SALE_D T1,
					 MS_MENU T2
				WHERE  T1.CD_STORE	=T2.CD_STORE
					AND T1.CD_MENU 	= T2.CD_MENU
					AND T1.CD_STORE = @CD_STORE
					AND T1.YMD_SALE BETWEEN SUBSTRING(@YMD_CLOSE,1,6) +'00' AND @YMD_CLOSE
				GROUP BY T1.YMD_SALE, T1.NO_POS, T1.NO_RCP, T2.CD_UPJANG
				) T2
			WHERE  T1.YMD_SALE      = T2.YMD_SALE
				AND T1.NO_POS    = T2.NO_POS
				AND T1.NO_RCP    = T2.NO_RCP
				AND T1.DS_SALE  <> 'V'
				AND T1.CD_STORE = @CD_STORE
				AND T1.YMD_SALE BETWEEN SUBSTRING(@YMD_CLOSE,1,6) +'00' AND @YMD_CLOSE
				AND T2.AMT_SALE <> 0
			) T
		GROUP BY T.CD_UPJANG		
		) T
	WHERE A.CD_STORE =  @CD_STORE
	) T20,
	(
	 SELECT COUNT(*) 		AS CNT_SALE,
                             SUM(AMT_SALE)	AS AMT_SALE
	    FROM MS_COUPON
	 WHERE CD_STORE	=@CD_STORE
	       AND YMD_SALE	=@YMD_CLOSE
	) T21,
	(
	 SELECT COUNT(*) 		AS CNT_SALE,
                             SUM(AMT_SALE)	AS AMT_SALE
	    FROM MS_COUPON
	 WHERE CD_STORE	=@CD_STORE
	       AND YMD_SALE  BETWEEN SUBSTRING(@YMD_CLOSE,1,6) +'00' AND @YMD_CLOSE
	) T22
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_SELECT_MEMBER]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[POS_SELECT_MEMBER]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


--회원조회
CREATE PROCEDURE POS_SELECT_MEMBER
	@CD_STORE		VARCHAR(4),
	@CD_MEMBER		VARCHAR(10),
	@DS_STATUS		VARCHAR(1) OUTPUT,		
	@YMD_BEGIN		VARCHAR(10) OUTPUT,
	@YMD_END		VARCHAR(10) OUTPUT,
	@NM_GOODS		VARCHAR(50) OUTPUT,
	@CD_MENU		VARCHAR(14) OUTPUT,
	@CD_MENU2		VARCHAR(14) OUTPUT,
	@NM_MENU		VARCHAR(50) OUTPUT,
	@LOCKER		VARCHAR(50) OUTPUT,
	@ALLOW_TOTCNT	INT	OUTPUT,
	@ALLOW_DAYCNT	INT	OUTPUT,
	@USE_TOT		INT	OUTPUT,
	@DELAY_RANGE	VARCHAR(30) OUTPUT,	--연기기간
	@NEXT_JOIN		VARCHAR(30) OUTPUT		--다음가입기간
AS
BEGIN
	DECLARE	@NO_RCP	VARCHAR(4)
	DECLARE	@TODATE	DATETIME
	DECLARE	@YMD_SALE	VARCHAR(8)
	DECLARE	@TEMP_LOCKER	VARCHAR(25)
	DECLARE	@TEMP_LOCKER1	VARCHAR(25)

	SET @TODATE = convert(VARCHAR(8), GETDATE(), 112) 

	IF LEN(@CD_MEMBER) <> 10
	BEGIN
		SELECT TOP 1 @CD_MEMBER  = CD_MEMBER FROM MS_MEMBER_CARD WHERE DS_STATUS = '0' AND NO_CARD = @CD_MEMBER
	END


	SELECT A.CD_MEMBER,
		 A.NM_MEMBER,
                            A.DS_MEMBER AS CD_CLASS,
		 B.NM_CODE1	AS DS_MEMBER,
		 B.NM_CODE2	AS DC_RATE,
		 A.TEL_MOBIL,
		 ISNULL( (SELECT TOP 1 NO_CARD FROM MS_MEMBER_CARD WHERE CD_MEMBER = A.CD_MEMBER AND DS_STATUS = '0' ), '') AS NO_CARD,
		 A.YMD_VISIT,
		 A.CNT_VISIT,
		 A.YN_TRUST,
		 (SELECT ISNULL(PNT_TOTAL,0) FROM SL_POINT WHERE CD_MEMBER = A.CD_MEMBER ) AS PNT_TOTAL,
		 (SELECT COUNT(*) FROM SL_MEMBER_USE WHERE CD_MEMBER = A.CD_MEMBER AND  YMD_SALE  = CONVERT(VARCHAR(8), GETDATE(), 112)  )  AS USE_DAY,
		CASE A.DS_SEX WHEN '0' THEN '남자' WHEN '1' THEN '여자' END AS GENDER,
	             (SELECT TOP 1 NEWS_TXT FROM SL_MEMBER_NEWS WHERE CD_MEMBER =@CD_MEMBER AND DS_STATUS = '0' ORDER BY SEQ DESC) AS NEWS_TXT
	FROM MS_MEMBER A, MS_CODE B
          WHERE  A.DS_MEMBER	*= B.CD_CODE
	   AND  B.CD_KIND	= '05'
	   AND  B.CD_STORE	=@CD_STORE
	   AND  A.CD_MEMBER	=@CD_MEMBER

	--정상상태의 가입내역을 조회한다
	SELECT  @YMD_SALE		=A.YMD_SALE,
		  @YMD_BEGIN	=A.YMD_BEGIN, 
		  @YMD_END		=A.YMD_FINISH, 
		  @NM_GOODS		=B.NM_GOODS, 
		  @CD_MENU		=C.CD_MENU, 
		  @CD_MENU2		=B.CD_MENU2, 
		  @NM_MENU		=C.NM_MENU,
		  @ALLOW_TOTCNT	=B.ALLOW_TOTCNT,
		  @ALLOW_DAYCNT	=B.ALLOW_DAYCNT,
		  @USE_TOT		=(SELECT COUNT(*) FROM SL_MEMBER_USE WHERE CD_MEMBER = A.CD_MEMBER AND  YMD_SALE  BETWEEN A.YMD_BEGIN AND A.YMD_FINISH ) ,
		  @DELAY_RANGE	 = DBO.STOD(A.DELAY_BEGIN) +' ~ ' + DBO.STOD(A.DELAY_TO),
		  @DS_STATUS	=A.DS_STATUS,
		  @NO_RCP		=A.NO_RCP,
		  @NEXT_JOIN		=(SELECT TOP 1 DBO.STOD(YMD_BEGIN) + '~'+DBO.STOD(YMD_FINISH) FROM SL_MEMBER_JOIN WHERE CD_MEMBER=A.CD_MEMBER AND YMD_BEGIN > A.YMD_FINISH)
	   FROM SL_MEMBER_JOIN A, MS_GOODS B, MS_MENU C
	WHERE A.CD_GOODS		= B.CD_GOODS
	      AND B.CD_MENU		= C.CD_MENU
	      AND A.DS_STATUS		= '0'
	      AND A.CD_MEMBER	=@CD_MEMBER
	      AND A.YMD_BEGIN <= @TODATE 
                   AND A.YMD_FINISH >= @TODATE

	--정상상태의 가입내역이 없으면
	IF LEN(@YMD_BEGIN) <> 8
	BEGIN

		SELECT  @YMD_SALE		=A.YMD_SALE,
			  @YMD_BEGIN	=A.YMD_BEGIN, 
			  @YMD_END		=A.YMD_FINISH, 
			  @NM_GOODS		=B.NM_GOODS, 
			  @CD_MENU		=C.CD_MENU, 
			  @CD_MENU2		=B.CD_MENU2, 
			  @NM_MENU		=C.NM_MENU,
			  @ALLOW_TOTCNT	=B.ALLOW_TOTCNT,
			  @ALLOW_DAYCNT	=B.ALLOW_DAYCNT,
			  @USE_TOT		=(SELECT COUNT(*) FROM SL_MEMBER_USE WHERE CD_MEMBER = A.CD_MEMBER AND  YMD_SALE  BETWEEN A.YMD_BEGIN AND A.YMD_FINISH ) ,
			  @DELAY_RANGE	 = DBO.STOD(A.DELAY_BEGIN) +' ~ ' + DBO.STOD(A.DELAY_TO),
			  @DS_STATUS	=A.DS_STATUS,
			  @NO_RCP		=A.NO_RCP,
			  @NEXT_JOIN		=(SELECT TOP 1 DBO.STOD(YMD_BEGIN) + '~'+DBO.STOD(YMD_FINISH) FROM SL_MEMBER_JOIN WHERE CD_MEMBER=A.CD_MEMBER AND YMD_BEGIN > A.YMD_FINISH)
		   FROM SL_MEMBER_JOIN A, MS_GOODS B, MS_MENU C
		WHERE A.CD_GOODS		= B.CD_GOODS
		      AND B.CD_MENU		= C.CD_MENU
		      AND A.DS_STATUS		<> '0'
		      AND A.CD_MEMBER	=@CD_MEMBER
		      AND A.YMD_BEGIN <= @TODATE 
	                   AND A.YMD_FINISH >= @TODATE

		IF LEN(@YMD_BEGIN) <> 8
		BEGIN
	
			SELECT TOP 1 @NO_RCP = NO_RCP FROM SL_MEMBER_JOIN WHERE CD_MEMBER	= @CD_MEMBER ORDER BY YMD_SALE DESC, NO_RCP DESC
	
			IF @NO_RCP = NULL
			BEGIN	
				--가입상품없음
				SET @DS_STATUS	= '5'
			END
			ELSE
			BEGIN
				--기간만료
				SET	@DS_STATUS = '4'
				
				SELECT  TOP 1
					  @YMD_BEGIN	=A.YMD_BEGIN, 
					  @YMD_END		=A.YMD_FINISH, 
					  @NM_GOODS		=B.NM_GOODS, 
					  @CD_MENU		=C.CD_MENU, 
					  @CD_MENU2		=B.CD_MENU2, 
					  @NM_MENU		=C.NM_MENU,
					  @ALLOW_TOTCNT	=B.ALLOW_TOTCNT,
					  @ALLOW_DAYCNT	=B.ALLOW_DAYCNT,
					  @USE_TOT		=(SELECT COUNT(*) FROM SL_MEMBER_USE WHERE CD_MEMBER = A.CD_MEMBER AND  YMD_SALE  BETWEEN A.YMD_BEGIN AND A.YMD_FINISH ) ,
					  @NEXT_JOIN	=(SELECT TOP 1 DBO.STOD(A.YMD_BEGIN) +' ~ ' + DBO.STOD(A.YMD_FINISH) FROM SL_MEMBER_JOIN WHERE CD_MEMBER = A.CD_MEMBER AND YMD_BEGIN > @TODATE) 
				   FROM SL_MEMBER_JOIN A, MS_GOODS B, MS_MENU C
				WHERE A.CD_GOODS		= B.CD_GOODS
				      AND B.CD_MENU		= C.CD_MENU
				      AND A.CD_MEMBER	=@CD_MEMBER
				ORDER BY A.YMD_SALE DESC, NO_RCP DESC
			END
		
		END
		ELSE
		BEGIN
			--환불상태이면 환불일자를 가져온다
			IF @DS_STATUS = '2'
			BEGIN
				SELECT @DELAY_RANGE	= DBO.STOD(YMD_REFUND) FROM SL_MEMBER_REFUND WHERE YMD_SALE =@YMD_SALE AND NO_RCP = @NO_RCP
			END
		END
	END


	SELECT TOP 1 @TEMP_LOCKER =  DBO.GETCOMMONNAME(@CD_STORE, '03', A.CD_PLACE) +'-'+ A.NO_LOCKER + ' ( '+DBO.GETCOMMONNAME(@CD_STORE, '04', B.CD_LOCKER) +' )' 
	   FROM SL_LOCKER A, MS_LOCKER B
	WHERE A.CD_PLACE	= B.CD_PLACE
	      AND A.NO_LOCKER	= B.NO_LOCKER
	      AND A.CD_MEMBER = @CD_MEMBER

	SELECT TOP 2 @TEMP_LOCKER1 =  DBO.GETCOMMONNAME(@CD_STORE, '03', A.CD_PLACE) +'-'+ A.NO_LOCKER + ' ( '+DBO.GETCOMMONNAME(@CD_STORE, '04', B.CD_LOCKER) +' )' 
	   FROM SL_LOCKER A, MS_LOCKER B
	WHERE A.CD_PLACE	= B.CD_PLACE
	      AND A.NO_LOCKER	= B.NO_LOCKER
	      AND A.CD_MEMBER = @CD_MEMBER

	SET @LOCKER = ISNULL(@TEMP_LOCKER,'') +' '+  ISNULL(@TEMP_LOCKER1,'')


	SET @DS_STATUS 	= ISNULL(@DS_STATUS,'')
	SET @YMD_BEGIN 	= ISNULL(@YMD_BEGIN,'')
	SET @YMD_END 	= ISNULL(@YMD_END,'')
	SET @NM_GOODS 	= ISNULL(@NM_GOODS,'')
	SET @CD_MENU 	= ISNULL(@CD_MENU,'')
	SET @CD_MENU2 	= ISNULL(@CD_MENU2,'')
	SET @NM_MENU 	= ISNULL(@NM_MENU,'')
	SET @LOCKER 	= ISNULL(@LOCKER,'')
	SET @USE_TOT	= ISNULL(@USE_TOT,0)
	SET @ALLOW_TOTCNT	= ISNULL(@ALLOW_TOTCNT,0)
	SET @ALLOW_DAYCNT	= ISNULL(@ALLOW_DAYCNT,0)
	SET @DELAY_RANGE 	= ISNULL(@DELAY_RANGE,'')
	SET @NEXT_JOIN 	= ISNULL(@NEXT_JOIN,'')

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_MEMBER_LOCKER]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SAVE_MEMBER_LOCKER]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO





CREATE  PROCEDURE SAVE_MEMBER_LOCKER

	@CD_STORE 	    		VARCHAR(4),
	@CD_PLACE			VARCHAR(3),
	@NO_LOCKER			VARCHAR(4),
	@YMD_BEGIN			VARCHAR(8),
	@YMD_END			VARCHAR(8),
	@YMD_SALE			VARCHAR(8),
	@CD_MEMBER			VARCHAR(10),
	@AMT_KEEP			INT,
	@AMT_RENT			INT,
	@CD_SAWON_CHG		VARCHAR(6),
	@CD_PLACE_ORG		VARCHAR(3),
	@NO_LOCKER_ORG		VARCHAR(4),
	@SIGNDATA			IMAGE,
	@I_SW           			CHAR(1)
AS
BEGIN

	IF @I_SW = 0
	BEGIN
		INSERT INTO  SL_LOCKER	 (	CD_STORE, 
							CD_PLACE,
							NO_LOCKER,
							YMD_BEGIN,
							YMD_END,
							YMD_SALE,
							CD_MEMBER,
							AMT_KEEP,	
							AMT_RENT,
							SIGNDATA,
							CD_SAWON_CHG
						)
	
	               		 	VALUES(	@CD_STORE, 	
							@CD_PLACE,
							@NO_LOCKER,
							@YMD_BEGIN,
							@YMD_END,
							@YMD_SALE,
							@CD_MEMBER,
							@AMT_KEEP,
							@AMT_RENT,
							@SIGNDATA,
							@CD_SAWON_CHG
						)			
	
	END
	ELSE 
	BEGIN
		UPDATE  SL_LOCKER     SET 
						CD_PLACE		=@CD_PLACE,
						NO_LOCKER		=@NO_LOCKER,
						YMD_BEGIN		= @YMD_BEGIN,
						YMD_END		=@YMD_END,
						AMT_KEEP		=@AMT_KEEP,
						AMT_RENT		=@AMT_RENT,
						SIGNDATA		=@SIGNDATA,
						CD_SAWON_CHG	=@CD_SAWON_CHG,
						DT_CHANGE          	=GETDATE()
				WHERE    CD_STORE 		= @CD_STORE 
				      AND    CD_PLACE 		= @CD_PLACE_ORG
				      AND    NO_LOCKER		= @NO_LOCKER_ORG
				      AND    YMD_BEGIN		= @YMD_BEGIN
	
	
	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].[DATA_PERSENT_DELETE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DATA_PERSENT_DELETE]
GO

SET QUOTED_IDENTIFIER OFF 
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	
	

	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 NO_RCP 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

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE POS_SAVE_SAWONWORK

	@CD_STORE	 	VARCHAR(4),
	@CD_SAWON	 	VARCHAR(6),
	@TIME_IN		VARCHAR(8),
	@NO_POS		VARCHAR(2),
	@IP_ADDRESS		VARCHAR(15),
	@WORK_KIND		VARCHAR(1),
	@R_NM_SAWON	VARCHAR(10) 	OUTPUT,
	@R_TIME_IN		VARCHAR(5) 	OUTPUT,
	@R_TIME_OUT	VARCHAR(5) 	OUTPUT,
	@IMG_IN		IMAGE,
	@IMG_OUT		IMAGE,
	@RESULT		INT 		OUTPUT

AS
BEGIN
	DECLARE	@CD_TIME		VARCHAR(3)

	SELECT @CD_TIME	= CD_TIME_PAY
	   FROM MS_SAWON
	WHERE CD_STORE	=@CD_STORE
	      AND CD_SAWON	=@CD_SAWON


	IF @WORK_KIND = 'S'
	BEGIN
		DECLARE	@LAST_IN	DATETIME

		SELECT TOP 1 @LAST_IN = TIME_IN
		   FROM SL_SAWONWORK
		WHERE CD_STORE	=@CD_STORE
		      AND CD_SAWON	=@CD_SAWON
		      AND TIME_OUT	IS NULL
                                 AND SETTLE_OUT IS NULL 
                           ORDER BY SETTLE_IN

		SELECT @R_NM_SAWON = ''

		IF ISNULL(@LAST_IN,'') = ''
		BEGIN

			SELECT @R_NM_SAWON 	=B.NM_SAWON,                                                              
				 @R_TIME_IN		=ISNULL(CONVERT(VARCHAR(5), A.TIME_IN, 108), '') ,            
				 @R_TIME_OUT	=ISNULL(CONVERT(VARCHAR(5), A.TIME_OUT, 108), '')
			   FROM SL_SAWONWORK A,  MS_SAWON B
			WHERE A.CD_STORE 	= B.CD_STORE                                                 
			      AND A.CD_SAWON 	= B.CD_SAWON    
			      AND A.CD_STORE	=@CD_STORE
			      AND A.CD_SAWON	=@CD_SAWON
			      AND CONVERT(VARCHAR(8), A.SETTLE_IN, 112) = CONVERT(VARCHAR(8), GETDATE(), 112)

			IF ISNULL(@R_TIME_IN,'') <> ''
				RETURN

			SELECT @R_NM_SAWON 	= NM_SAWON,
				 @R_TIME_IN		= '',
				 @R_TIME_OUT	=''
			   FROM MS_SAWON                        
			WHERE CD_STORE 	=@CD_STORE                                                    
			      AND CD_SAWON	=@CD_SAWON    
		END
		ELSE
		BEGIN
			SELECT @R_NM_SAWON 	=A.NM_SAWON,                                                              
				 @R_TIME_IN		=ISNULL(CONVERT(VARCHAR(5), B.TIME_IN, 108), '') ,            
				 @R_TIME_OUT	=ISNULL(CONVERT(VARCHAR(5), B.TIME_OUT, 108), '')
			   FROM MS_SAWON A, SL_SAWONWORK B                         
			WHERE A.CD_STORE 	*= B.CD_STORE                                                 
			      AND A.CD_SAWON 	*= B.CD_SAWON                                                 
			      AND B.TIME_IN	=@LAST_IN
			      AND A.CD_STORE 	=@CD_STORE                                                    
			      AND A.CD_SAWON	=@CD_SAWON    
		END                                               
	END
	ELSE  IF @WORK_KIND = 'I'
	BEGIN

		INSERT INTO SL_SAWONWORK(	CD_STORE,
							CD_SAWON,
							TIME_IN,
							SETTLE_IN,
							NO_POS,
							IMG_IN,
							CD_TIME,
							IP_ADDRESS
						   )
					VALUES  (	
							@CD_STORE,
							@CD_SAWON,
							GETDATE(),
							GETDATE(),
							@NO_POS,
							@IMG_IN,
							@CD_TIME,
							@IP_ADDRESS
						   )
					
		IF @@ERROR >0 GOTO ERROR_RETURN

	END
	ELSE IF @WORK_KIND = 'O'
	BEGIN

		DECLARE	@TEMP		INT
		DECLARE 	@TEMP_TIME		DATETIME

		SELECT TOP 1 @TEMP_TIME = TIME_IN,
                                                      @TEMP = DATEDIFF(n, TIME_IN, GETDATE() )
		   FROM SL_SAWONWORK
		WHERE CD_STORE	=@CD_STORE
		      AND CD_SAWON	=@CD_SAWON		
		      AND TIME_OUT	IS NULL
                                 AND SETTLE_OUT IS NULL 
                           ORDER BY SETTLE_IN DESC


		IF @TEMP < 30 GOTO ERROR_RETURN

		UPDATE SL_SAWONWORK SET TIME_OUT	=GETDATE(),
						   SETTLE_OUT	=GETDATE(),
						   IMG_OUT	=@IMG_OUT,
						   CD_TIME	=@CD_TIME
		WHERE CD_STORE	=@CD_STORE
		      AND CD_SAWON	=@CD_SAWON
		      AND TIME_IN	=@TEMP_TIME

		IF @@ERROR >0 GOTO ERROR_RETURN

	END


-- 프로시저 정상 완료
	SELECT @RESULT =0
	RETURN

-- 실행도중 에러 발생
ERROR_RETURN:
	SELECT @RESULT = -1
	RETURN
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


