20121017
20100325

ALTER  PROCEDURE [dbo].[SELECT_SALE_PROMPT]
 	@CD_STORE		VARCHAR(4),		--점포코드
	@YMD_SALE		VARCHAR(8),		--조회일자
	@CD_CLASS		VARCHAR(8),		--분류코드
	@KIND			INT	
AS
BEGIN
	IF @KIND = 1 
	BEGIN
		SELECT Isnull(T2.CD_CODE,'') as CD_CODE, 
		       Isnull(T2.NM_CODE1,'미지정') as NM_CODE1,
		       ISNULL(T1.AMT_SALE,0) AS AMT_SALE,
		       ISNULL(T1.CNT_SALE,0) AS CNT_SALE
		  FROM  (SELECT A.CD_STORE,
	                        B.CD_CLASS,
	                        SUM(ISNULL(A.AMT_SALE,0))		AS AMT_SALE,
	                        SUM(A.QTY_SALE)					AS CNT_SALE
		           FROM SL_SALE_D A left outer join 
			        MS_MENU   B on a.CD_STORE = b.CD_STORE and a.CD_MENU = b.CD_MENU inner join
			        SL_SALE_H C on 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'
		          WHERE A.CD_STORE =@CD_STORE
		            AND A.YMD_SALE =@YMD_SALE
			  GROUP BY A.CD_STORE, B.CD_CLASS ) T1 left outer join
			  MS_CODE T2 on T1.CD_STORE = t2.CD_STORE and T2.CD_CODE = T1.CD_CLASS AND T2.CD_KIND = '17'
		ORDER BY T2.CD_CODE
	END
	ELSE
	BEGIN
		SELECT A.CD_MENU,
		       B.NM_MENU,
		       SUM(A.QTY_SALE),
		       SUM(A.AMT_SALE-A.DC_TOT) as AMT_SALE
		  FROM SL_SALE_D A left outer join 
		       MS_MENU   B on a.CD_STORE = b.CD_STORE and a.CD_MENU = b.CD_MENU inner join
		       SL_SALE_H C on 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'
		 WHERE A.CD_STORE =@CD_STORE
		   AND A.YMD_SALE =@YMD_SALE
		 GROUP BY A.CD_MENU, B.NM_MENU
		 ORDER BY A.CD_MENU
	END
END

GO

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO





CREATE  PROCEDURE SAVE_MEMBER_REFUND

	@YMD_SALE			VARCHAR(8),
	@NO_RCP			VARCHAR(4),
	@YMD_REFUND		VARCHAR(8),
	@CD_REFUND			VARCHAR(3),
	@AMT_SALE			INT,
	@AMT_USE			INT,
	@AMT_FEE			INT,
	@AMT_REFUND		INT,
	@CD_SAWON_CHG		VARCHAR(6)
AS
BEGIN

		DECLARE	@CD_MEMBER		VARCHAR(10)
		SELECT @CD_MEMBER = CD_MEMBER FROM SL_MEMBER_JOIN WHERE YMD_SALE =@YMD_SALE AND NO_RCP = @NO_RCP
	
		INSERT INTO  SL_MEMBER_REFUND (	
							YMD_SALE,
							NO_RCP,
							CD_MEMBER,
							YMD_REFUND,
							CD_REFUND,
							AMT_SALE,
							AMT_USE,
							AMT_FEE,
							AMT_REFUND,
							CD_SAWON_CHG
						)
	
	               		 	VALUES(	
							@YMD_SALE,
							@NO_RCP,
							@CD_MEMBER,
							@YMD_REFUND,
							@CD_REFUND,
							@AMT_SALE,
							@AMT_USE,
							@AMT_FEE,
							@AMT_REFUND,
							@CD_SAWON_CHG
						)		



	
		UPDATE  SL_MEMBER_JOIN     SET DS_STATUS = 3
				WHERE    YMD_SALE 		= @YMD_SALE
				      AND    NO_RCP		= @NO_RCP
	
	
	

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

SET QUOTED_IDENTIFIER OFF 
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_OKC		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_OKC		=SUM(AMT_OKC	),
			 @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_OKC		=@AMT_OKC,
						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
                                        IF CONVERT(VARCHAR(5), GETDATE(), 108) > '23:30'
                                        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
                                       ELSE
                                        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

	
	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

