20080310
20080318
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	--연기기간
AS
BEGIN
	DECLARE	@NO_RCP	VARCHAR(4)
	DECLARE	@TODATE	DATETIME
	DECLARE	@YMD_SALE	VARCHAR(8)

	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
	   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
--	      AND NOT ( (ISNULL(A.DELAY_BEGIN,@TODATE) <= @TODATE) AND (ISNULL(A.DELAY_TO,@TODATE) <= @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 ) 
			   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


	SELECT @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


	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,'')

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO






CREATE  PROCEDURE SAVE_MEMBER_DELAY

	@YMD_SALE			VARCHAR(8),
	@NO_RCP			VARCHAR(4),
	@YMD_DELAY			VARCHAR(8),
	@CD_DELAY			VARCHAR(3),
	@YMD_FROM			VARCHAR(8),
	@YMD_TO			VARCHAR(8),
	@CD_SAWON_CHG		VARCHAR(6),
	@WORK_KIND			INT		--0:신규, 1:수정
AS
BEGIN

	DECLARE	@CD_MEMBER		VARCHAR(10)
	DECLARE	@DS_STATUS		VARCHAR(1)

	IF (CONVERT(VARCHAR(8), GETDATE(), 112) >= @YMD_FROM) AND (CONVERT(VARCHAR(8), GETDATE(), 112) <= @YMD_TO)
		SET @DS_STATUS = '1'
	ELSE
		SET @DS_STATUS = '0'

	SELECT @CD_MEMBER = CD_MEMBER FROM SL_MEMBER_JOIN WHERE YMD_SALE =@YMD_SALE AND NO_RCP = @NO_RCP


	IF @WORK_KIND = 0
	BEGIN
		INSERT INTO  SL_MEMBER_DELAY (	
							YMD_SALE,
							NO_RCP,
							YMD_DELAY,
							CD_DELAY,
							CD_MEMBER,
							YMD_FROM,
							YMD_TO,
							CD_SAWON_CHG
						)
	
	               		 	VALUES(	
							@YMD_SALE,
							@NO_RCP,
							@YMD_DELAY,
							@CD_DELAY,
							@CD_MEMBER,
							@YMD_FROM,
							@YMD_TO,
							@CD_SAWON_CHG
						)		
	END
	ELSE
	BEGIN
		UPDATE SL_MEMBER_DELAY SET CD_DELAY	=@CD_DELAY,
						       YMD_TO	=@YMD_TO
		 WHERE YMD_SALE	=@YMD_SALE
		       AND NO_RCP	=@NO_RCP
		       AND YMD_DELAY	=@YMD_DELAY
	END



	UPDATE  SL_MEMBER_JOIN     SET 
					         DELAY_BEGIN	= @YMD_FROM,
					        DELAY_TO	= @YMD_TO,
					        DAY_DELAY = DATEDIFF( DAY, CONVERT(DATETIME, @YMD_FROM), CONVERT(DATETIME, @YMD_TO)) + 1,
					         YMD_FINISH	=  CONVERT(VARCHAR(8), DATEADD(DAY, DATEDIFF( DAY, CONVERT(DATETIME, @YMD_FROM), CONVERT(DATETIME, @YMD_TO)), CONVERT(DATETIME, YMD_FINISH))+1  , 112),
					        DS_STATUS          = @DS_STATUS
			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_ORDER_C]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[POS_SAVE_ORDER_C]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

-- 주문취소내역 저장
CREATE  PROCEDURE POS_SAVE_ORDER_C

	@CD_STORE 	    		VARCHAR(4),
	@YMD_SALE			VARCHAR(8),
	@NO_LOCK			VARCHAR(4),
	@DS_LOCK			VARCHAR(3),
	@YN_ORDER			VARCHAR(1),
	@CD_MENU			VARCHAR(14),
	@QTY_CANCEL		INT,
	@PR_SALE			INT,
	@AMT_SALE			INT,
	@NO_POS			VARCHAR(2),
	@CD_SAWON			VARCHAR(6),
	@CD_DAMDANG		VARCHAR(6)

AS
BEGIN

	DECLARE @SEQ INT
	DECLARE @CNT INT

	SELECT @CNT = COUNT(*) FROM SL_ORDER_H WHERE CD_STORE =@CD_STORE AND DS_LOCK =@DS_LOCK AND NO_LOCK = @NO_LOCK


	IF @CNT = 1
	BEGIN

		IF @YN_ORDER = 'Y'
		BEGIN
			SELECT @SEQ = ISNULL( MAX(SEQ), 0 ) + 1
			   FROM SL_ORDER_D
			WHERE CD_STORE	=@CD_STORE
			      AND DS_LOCK	=@DS_LOCK
			      AND NO_LOCK	=@NO_LOCK
		
			IF @SEQ IS NULL SET @SEQ = 1
	
			DECLARE	@DT_ORDER			DATETIME
	
			SELECT TOP 1 @DT_ORDER = DT_CREATE 
			   FROM SL_ORDER_D 
			WHERE CD_STORE	= @CD_STORE 
			      AND DS_LOCK 	=@DS_LOCK 
			      AND NO_LOCK 	= @NO_LOCK 
			      AND CD_MENU 	= @CD_MENU 
			      AND CD_DAMDANG= @CD_DAMDANG
			      AND QTY_SALE	> 0
			ORDER BY ORDERSEQ
		
			INSERT INTO SL_ORDER_D(
							CD_STORE,
							DS_LOCK,
							NO_LOCK,
							CD_MENU,
							SEQ,	
							QTY_SALE,
							PR_SALE,
							AMT_SALE,
							NO_POS,
							CD_DAMDANG
						         )
					VALUES
						        (
							@CD_STORE,
							@DS_LOCK,
							@NO_LOCK,
							@CD_MENU,
							@SEQ,
							@QTY_CANCEL*-1,
							@PR_SALE,
							@AMT_SALE,
							@NO_POS,
							@CD_DAMDANG
						        )
	
			UPDATE SL_ORDER_H SET AMT_SALE = AMT_SALE - (@QTY_CANCEL * @PR_SALE)
			WHERE CD_STORE	=@CD_STORE
			      AND DS_LOCK	=@DS_LOCK
			      AND NO_LOCK	=@NO_LOCK
		END

		SELECT @SEQ = ISNULL( MAX(SEQ), 0 ) + 1
		   FROM SL_SALE_C
		WHERE CD_STORE	=@CD_STORE
		      AND YMD_SALE	=@YMD_SALE
	
		IF @SEQ IS NULL SET @SEQ = 1

		IF @YN_ORDER = 'N'
			SET @DT_ORDER = NULL
	
		INSERT INTO SL_SALE_C(
						CD_STORE,
						YMD_SALE,
						SEQ,
						DS_LOCK,
						NO_LOCK,
						CD_MENU,
						QTY_CANCEL,
						NO_POS,
						CD_SAWON,
						DT_ORDER
					         )
				VALUES
					        (
						@CD_STORE,
						@YMD_SALE,
						@SEQ,
						@DS_LOCK,
						@NO_LOCK,
						@CD_MENU,
						@QTY_CANCEL,
						@NO_POS,
						@CD_SAWON,
						@DT_ORDER
					        )

	END
	ELSE IF LEN(@YMD_SALE) = 8
	BEGIN
		SELECT @SEQ = ISNULL( MAX(SEQ), 0 ) + 1
		   FROM SL_SALE_C
		WHERE CD_STORE	=@CD_STORE
		      AND YMD_SALE	=@YMD_SALE
	
		IF @SEQ IS NULL SET @SEQ = 1
	
		INSERT INTO SL_SALE_C(
						CD_STORE,
						YMD_SALE,
						SEQ,
						DS_LOCK,
						NO_LOCK,
						CD_MENU,
						QTY_CANCEL,
						NO_POS,
						CD_SAWON
					         )
				VALUES
					        (
						@CD_STORE,
						@YMD_SALE,
						@SEQ,
						@DS_LOCK,
						@NO_LOCK,
						@CD_MENU,
						@QTY_CANCEL,
						@NO_POS,
						@CD_SAWON
					        )
	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].[SAVE_MEMBER_DELAY]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SAVE_MEMBER_DELAY]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



CREATE  PROCEDURE [dbo].[SAVE_MEMBER_DELAY]

	@YMD_SALE			VARCHAR(8),
	@NO_RCP			VARCHAR(4),
	@YMD_DELAY			VARCHAR(8),
	@CD_DELAY			VARCHAR(3),
	@YMD_FROM			VARCHAR(8),
	@YMD_TO			VARCHAR(8),
	@CD_SAWON_CHG		VARCHAR(6),
	@WORK_KIND			INT		--0:신규, 1:수정
AS
BEGIN

	DECLARE @DELAYDAY		INT

	DECLARE	@CD_MEMBER		VARCHAR(10)
	DECLARE	@DS_STATUS		VARCHAR(1)

	IF (CONVERT(VARCHAR(8), GETDATE(), 112) >= @YMD_FROM) AND (CONVERT(VARCHAR(8), GETDATE(), 112) <= @YMD_TO)
		SET @DS_STATUS = '1'
	ELSE
		SET @DS_STATUS = '0'

	SELECT @CD_MEMBER = CD_MEMBER FROM SL_MEMBER_JOIN WHERE YMD_SALE =@YMD_SALE AND NO_RCP = @NO_RCP


	IF @WORK_KIND = 0
	BEGIN
		SET @DELAYDAY = 0
		INSERT INTO  SL_MEMBER_DELAY (	
							YMD_SALE,
							NO_RCP,
							YMD_DELAY,
							CD_DELAY,
							CD_MEMBER,
							YMD_FROM,
							YMD_TO,
							CD_SAWON_CHG
						)
	
	               		 	VALUES(	
							@YMD_SALE,
							@NO_RCP,
							@YMD_DELAY,
							@CD_DELAY,
							@CD_MEMBER,
							@YMD_FROM,
							@YMD_TO,
							@CD_SAWON_CHG
						)		
	END
	ELSE
	BEGIN
		SELECT @DELAYDAY	= ISNULL(DAY_DELAY,0)
		  FROM SL_MEMBER_JOIN	
		 WHERE YMD_SALE	=@YMD_SALE
		   AND NO_RCP	=@NO_RCP	

		UPDATE SL_MEMBER_DELAY SET CD_DELAY	=@CD_DELAY,
						       YMD_TO	=@YMD_TO
		 WHERE YMD_SALE	=@YMD_SALE
		       AND NO_RCP	=@NO_RCP
		       AND YMD_DELAY	=@YMD_DELAY
	END



	UPDATE  SL_MEMBER_JOIN     SET 
					         DELAY_BEGIN	= @YMD_FROM,
					        DELAY_TO	= @YMD_TO,
					        DAY_DELAY = DATEDIFF( DAY, CONVERT(DATETIME, @YMD_FROM), CONVERT(DATETIME, @YMD_TO)) + 1,
					        YMD_FINISH	=  CONVERT(VARCHAR(8), DATEADD(DAY, DATEDIFF( DAY, CONVERT(DATETIME, @YMD_FROM), CONVERT(DATETIME, @YMD_TO)), CONVERT(DATETIME, YMD_FINISH))+1-@DELAYDAY , 112),
					        DS_STATUS          = @DS_STATUS
			WHERE    YMD_SALE 		= @YMD_SALE
			      AND    NO_RCP		= @NO_RCP
	
	
	

	
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO







CREATE  PROCEDURE SAVE_MS_MENU

	@CD_STORE 	    		VARCHAR(4),
	@CD_MENU      		VARCHAR(14),
	@NM_MENU		    	VARCHAR(50),
	@NM_MENU_SHORT	    	VARCHAR(50),
	@DS_MENU_TYPE    		VARCHAR(1),
	@DS_GENDER			VARCHAR(1),
	@CD_UPJANG			VARCHAR(4),
	@PR_TIP			INTEGER,
	@PR_SALE     			INTEGER,
	@PR_SALE1			INTEGER,
	@DS_TAX 	    		VARCHAR(1),
	@YN_DC			VARCHAR(1),
	@YN_POINT			VARCHAR(1),
	@CD_CLASS 	    		VARCHAR(8),
	@YN_USE			VARCHAR(1),
	@DS_STOCK			VARCHAR(1),
	@STOCK_CLASS		VARCHAR(3),
	@NM_UNIT			VARCHAR(30),
	@CD_TRDPL			VARCHAR(6),
	@PR_BUY			INT,
	@QTY_SAFETY		INT,
	@CD_SAWON_CHG   		VARCHAR(6),
	@YN_DAMDANG		VARCHAR(1),
	@I_SW           			CHAR(1)
AS
BEGIN
    DECLARE @CNT INT

    IF @I_SW = 0
    BEGIN
		INSERT INTO  MS_MENU (	CD_STORE, 	 
						CD_MENU,     
						CD_CLASS, 	
						CD_UPJANG, 
						NM_MENU,	
						NM_MENU_SHORT,	 
						DS_MENU_TYPE,
						DS_GENDER,
						PR_TIP,	
						PR_SALE,     
						PR_SALE1,
						DS_TAX, 	
						YN_DC,    
						YN_POINT,
						DS_STOCK,
						STOCK_CLASS,
						NM_UNIT,
						CD_TRDPL,
						PR_BUY,
						QTY_SAFETY,
						YN_USE,		
						CD_SAWON_CHG,
						YN_DAMDANG
						)

                       		 	VALUES(	@CD_STORE, 	 
						@CD_MENU,     
						@CD_CLASS, 	 
						@CD_UPJANG,
						@NM_MENU,		
						@NM_MENU_SHORT, 
						@DS_MENU_TYPE,
						@DS_GENDER,
						@PR_TIP,	
						@PR_SALE,     
						@PR_SALE1,
						@DS_TAX, 	   
						@YN_DC, 
						@YN_POINT,
						@DS_STOCK,
						@STOCK_CLASS,
						@NM_UNIT,
						@CD_TRDPL,
						@PR_BUY,
						@QTY_SAFETY,
						@YN_USE,		
						@CD_SAWON_CHG,
						@YN_DAMDANG
						)			

    END
    ELSE 
    BEGIN
		UPDATE  MS_MENU     SET 
						CD_CLASS		=@CD_CLASS, 	 
						CD_UPJANG		=@CD_UPJANG,
						NM_MENU		=@NM_MENU, 
						NM_MENU_SHORT        =@NM_MENU_SHORT,						
						DS_MENU_TYPE	=@DS_MENU_TYPE,
						DS_GENDER		=@DS_GENDER,
						PR_TIP		=@PR_TIP,
						PR_SALE		=@PR_SALE,     
						PR_SALE1		=@PR_SALE1,
						DS_TAX 		=@DS_TAX,    
						YN_DC			=@YN_DC,
						YN_POINT		=@YN_POINT,
						DS_STOCK		=@DS_STOCK,
						STOCK_CLASS		=@STOCK_CLASS,
						NM_UNIT		=@NM_UNIT,
						CD_TRDPL		=@CD_TRDPL,
						PR_BUY		=@PR_BUY,
						QTY_SAFETY		=@QTY_SAFETY,
						YN_USE		=@YN_USE,
						CD_SAWON_CHG	=@CD_SAWON_CHG,
						YN_DAMDANG		=@YN_DAMDANG,
						DT_CHANGE          	=GETDATE()
				WHERE    CD_STORE 		= @CD_STORE 
				      AND    CD_MENU 		= @CD_MENU


    END
    

	
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

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

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

if not exists(select * from information_schema.columns where TABLE_NAME='ms_store' and COLUMN_NAME='nighttime_end')
BEGIN
ALTER TABLE ms_store
ADD nighttime_end varchar(5)
END
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 OFF 
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_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,
	@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)


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_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,
					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,
					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].[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,3,2)
	set @day_end		= substring(@day_end,1,2)+':'+substring(@day_end,3,2)
	set @night_begin	= substring(@night_begin,1,2)+':'+substring(@night_begin,3,2)
	set @night_end		= substring(@night_end,1,2)+':'+substring(@night_end,3,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



