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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE SAVE_STOCK_CLOSE
	@CD_STORE 		VARCHAR(4),		--점포코드
	@NEXT_CLOSE	VARCHAR(6), 		--마감다음월
	@CURR_CLOSE	VARCHAR(6)		--마감월

AS
BEGIN
	--임시테이블에 월 기초재고를 옮겨놓는다
	delete sl_stock_tmp 

	insert into sl_stock_tmp (cd_store, ym_stock, cd_menu, qty_base)
	  select cd_store, ym_stock, cd_menu, qty_base from sl_stock_month where cd_store =@cd_store and ym_stock =@curr_close


	delete sl_stock_month where cd_store =@cd_store and ym_stock = '000000'

	insert into sl_stock_month ( cd_store, ym_stock, yn_close, cd_menu, qty_base, qty_in, qty_out, qty_silsa, qty_adj)
	select  a.cd_store, '000000', 'Y', a.cd_menu, isnull(e.qty_base,0), isnull(b.qty_buy,0), isnull(c.qty_sale,0), d.qty_silsa, isnull(d.qty_settle,0)
	   from ms_menu a,
	          (select cd_store, cd_goods as cd_menu, sum(isnull(qty_buy,0)) as qty_buy
	              from sl_buy_d
	           where substring(ymd_buy,1,6) = @curr_close
	            group by cd_store, cd_goods
	          ) b,
	          (select a.cd_store, a.cd_menu, sum(a.qty_sale) as qty_sale
		   from sl_sale_d a, sl_sale_h b
		where a.cd_store = b.cd_store
		    and a.ymd_sale = b.ymd_sale
		    and a.no_pos    = b.no_pos
		    and a.no_rcp    = b.no_rcp
		    and b.ds_sale   <> 'V'
		    and substring(a.ymd_sale,1,6) = @curr_close
		group by a.cd_store, a.cd_menu
	          ) c,
	          (select cd_store, cd_menu, qty_silsa, isnull(qty_settle,0) qty_settle
	              from sl_silsa
	           where ym_silsa = @curr_close
	          ) d,
	          sl_stock_tmp e
	where a.cd_store *= b.cd_store
	    and a.cd_menu *= b.cd_menu
	    and a.cd_store *= c.cd_store
	    and a.cd_menu *= c.cd_menu
	    and a.cd_store *= c.cd_store
	    and a.cd_menu *= d.cd_menu
                 and a.cd_menu *= e.cd_menu
	    and a.ds_stock  = '1'
                       and a.yn_use    = 'Y'
	    and a.cd_store  =@cd_store

	delete sl_stock_month where cd_store =@cd_store and ym_stock = @curr_close

	update sl_stock_month set ym_stock = @curr_close where cd_store =@cd_store and ym_stock = '000000'

	--다음월의 기초재고를 입력한다
	delete sl_stock_month where cd_store =@cd_store and ym_stock = @next_close

	insert into sl_stock_month ( cd_store, ym_stock, cd_menu, qty_base)
	select  a.cd_store, @next_close,  a.cd_menu, b.qty_stock
	   from ms_menu a,
	          sl_stock_month b
	where a.cd_store *= b.cd_store
	    and a.cd_menu *= b.cd_menu
	    and b.ym_stock = @curr_close
	    and a.ds_stock  = '1'
	    and a.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_SILSA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SAVE_SL_SILSA]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO



CREATE PROCEDURE SAVE_SL_SILSA
	
 	@CD_STORE		VARCHAR(4),		--점포코드
	@YM_SILSA	 	VARCHAR(8),		--실사월
	@CD_MENU		VARCHAR(14),
	@QTY_STOCK		INT,
	@QTY_SILSA		INT,
	@QTY_SETTLE	INT,
	@REMARK		VARCHAR(100),
	@CD_SAWON_CHG	VARCHAR(6),
	@WORK_KIND		VARCHAR(1)		--(I:INSERT, U:UPDATE, D:DELETE)

AS
BEGIN
	DECLARE	@CNT		INT

	SELECT @CNT = COUNT(*) 
	  FROM SL_SILSA
	WHERE CD_STORE	=@CD_STORE
	      AND YM_SILSA	=@YM_SILSA
	      AND CD_MENU	=@CD_MENU

	SET @CNT = ISNULL(@CNT, 0)
	IF @CNT = 0  SET @WORK_KIND = 'I'

	IF @WORK_KIND = 'I'
	BEGIN
		INSERT INTO SL_SILSA(CD_STORE, YM_SILSA, CD_MENU, QTY_STOCK, QTY_SILSA, QTY_SETTLE, REMARK, CD_SAWON_CHG)
				VALUES(@CD_STORE, @YM_SILSA, @CD_MENU, @QTY_STOCK, @QTY_SILSA, @QTY_SETTLE, @REMARK, @CD_SAWON_CHG)
	END
	ELSE IF @WORK_KIND = 'U'
	BEGIN
		UPDATE SL_SILSA SET	QTY_STOCK	=@QTY_STOCK,
						QTY_SILSA	=@QTY_SILSA,
						QTY_SETTLE	=@QTY_SETTLE,
						REMARK	=@REMARK
		WHERE CD_STORE	=@CD_STORE
		      AND YM_SILSA	=@YM_SILSA
		      AND CD_MENU	=@CD_MENU
	END
	ELSE IF @WORK_KIND = 'D'
	BEGIN
		DELETE SL_SILSA
		WHERE CD_STORE	=@CD_STORE
		      AND YM_SILSA	=@YM_SILSA
		      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='backup_dir')
BEGIN
ALTER TABLE ms_store
ADD backup_dir varchar(100)
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),
	@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_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,
					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].[MS_COUPON_TEMP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MS_COUPON_TEMP]
GO

CREATE TABLE [dbo].[MS_COUPON_TEMP] (
	[cd_store] [varchar] (4) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[no_coupon] [varchar] (20) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[ds_coupon] [varchar] (3) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[cnt_available] [int] NULL ,
	[ymd_begin] [varchar] (8) COLLATE Korean_Wansung_CI_AS NULL ,
	[ymd_end] [varchar] (8) COLLATE Korean_Wansung_CI_AS NULL ,
	[cnt_use] [int] NULL ,
	[ymd_sale] [varchar] (8) COLLATE Korean_Wansung_CI_AS NULL ,
	[amt_sale] [int] NULL ,
	[ds_status] [varchar] (1) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[cd_sawon_chg] [varchar] (6) COLLATE Korean_Wansung_CI_AS NULL ,
	[dt_change] [dt_change] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MS_COUPON_TEMP] ADD 
	CONSTRAINT [DF_MS_COUPON_TEMP_cnt_available] DEFAULT (0) FOR [cnt_available],
	CONSTRAINT [DF_MS_COUPON_TEMP_cnt_use] DEFAULT (0) FOR [cnt_use],
	CONSTRAINT [DF_MS_COUPON_TEMP_amt_sale] DEFAULT (0) FOR [amt_sale],
	CONSTRAINT [DF_MS_COUPON_TEMP_ds_status] DEFAULT (0) FOR [ds_status],
	CONSTRAINT [PK_MS_COUPON_TEMP] PRIMARY KEY  CLUSTERED 
	(
		[cd_store],
		[no_coupon]
	)  ON [PRIMARY] 
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[getdate]', N'[MS_COUPON_TEMP].[dt_change]'
GO

setuser
GO

INSERT INTO MS_COUPON_TEMP ( cd_store, no_coupon, ds_coupon,cnt_available, ymd_begin, ymd_end, cnt_use, ymd_sale, amt_sale, ds_status, cd_sawon_chg, dt_change)
SELECT cd_store, no_coupon, ds_coupon,cnt_available, ymd_begin, ymd_end, cnt_use, ymd_sale, amt_sale, ds_status, cd_sawon_chg, dt_change FROM MS_COUPON
GO

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

CREATE TABLE [dbo].[MS_COUPON] (
	[cd_store] [varchar] (4) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[no_coupon] [varchar] (20) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[no_seq] [varchar] (2) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[ds_coupon] [varchar] (3) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[cnt_available] [int] NULL ,
	[ymd_begin] [varchar] (8) COLLATE Korean_Wansung_CI_AS NULL ,
	[ymd_end] [varchar] (8) COLLATE Korean_Wansung_CI_AS NULL ,
	[cnt_use] [int] NULL ,
	[ymd_sale] [varchar] (8) COLLATE Korean_Wansung_CI_AS NULL ,
	[amt_sale] [int] NULL ,
	[ds_status] [varchar] (1) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[cd_sawon_chg] [varchar] (6) COLLATE Korean_Wansung_CI_AS NULL ,
	[dt_change] [dt_change] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MS_COUPON] ADD 
	CONSTRAINT [DF_MS_COUPON_cnt_available] DEFAULT (0) FOR [cnt_available],
	CONSTRAINT [DF_MS_COUPON_cnt_use] DEFAULT (0) FOR [cnt_use],
	CONSTRAINT [DF_MS_COUPON_amt_sale] DEFAULT (0) FOR [amt_sale],
	CONSTRAINT [DF_MS_COUPON_ds_status] DEFAULT (0) FOR [ds_status],
	CONSTRAINT [PK_MS_COUPON] PRIMARY KEY  CLUSTERED 
	(
		[cd_store],
		[no_coupon],
		[no_seq]
	)  ON [PRIMARY] 
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[getdate]', N'[MS_COUPON].[dt_change]'
GO

setuser
GO



INSERT INTO MS_COUPON ( cd_store, no_coupon, no_seq,  ds_coupon,cnt_available, ymd_begin, ymd_end, cnt_use, ymd_sale, amt_sale, ds_status, cd_sawon_chg, dt_change)
SELECT cd_store, no_coupon, '01', ds_coupon,cnt_available, ymd_begin, ymd_end, cnt_use, ymd_sale, amt_sale, ds_status, cd_sawon_chg, dt_change FROM MS_COUPON_TEMP
GO
 

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

CREATE TABLE [dbo].[MS_COUPON_MENU_TEMP] (
	[cd_store] [varchar] (4) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[no_coupon] [varchar] (20) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[cd_menu] [varchar] (14) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[seq] [int] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MS_COUPON_MENU_TEMP] ADD 
	CONSTRAINT [PK_MS_COUPON_MENU_TEMP] PRIMARY KEY  CLUSTERED 
	(
		[cd_store],
		[no_coupon],
		[cd_menu]
	)  ON [PRIMARY] 
GO

INSERT INTO MS_COUPON_MENU_TEMP(CD_STORE, NO_COUPON, CD_MENU, SEQ)
SELECT CD_STORE, NO_COUPON, CD_MENU, SEQ FROM MS_COUPON_MENU


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

CREATE TABLE [dbo].[MS_COUPON_MENU] (
	[cd_store] [varchar] (4) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[no_coupon] [varchar] (20) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[no_seq] [varchar] (2) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[cd_menu] [varchar] (14) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[seq] [int] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MS_COUPON_MENU] ADD 
	CONSTRAINT [PK_MS_COUPON_MENU] PRIMARY KEY  CLUSTERED 
	(
		[cd_store],
		[no_coupon],
		[no_seq],
		[cd_menu]
	)  ON [PRIMARY] 
GO


INSERT INTO MS_COUPON_MENU(CD_STORE, NO_COUPON, NO_SEQ, CD_MENU, SEQ)
SELECT CD_STORE, NO_COUPON, '01',  CD_MENU, SEQ FROM MS_COUPON_MENU_TEMP
GO

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


CREATE   PROCEDURE SAVE_MS_COUPON
	@CD_STORE       		VARCHAR(4),    		-- 점포코드
	@NO_COUPON			VARCHAR(20),
	@NO_SEQ			VARCHAR(2),
	@DS_COUPON 		VARCHAR(3),    		
	@YMD_BEGIN         		VARCHAR(8),   		
	@YMD_END        		VARCHAR(8),  		
	@CNT_AVAILABLE		INT,    		 
	@YMD_SALE			VARCHAR(8),           
	@AMT_SALE			INT,                       
	@CD_SAWON_CHG   		VARCHAR(6),    		-- 변경사원                                                             
	@WORK_KIND     		VARCHAR(1)    		-- 작업구분(N:신규, U:수정)
AS
BEGIN                                                                                                       
                                                                                                        
	 
	IF @WORK_KIND = 'I' 
	BEGIN

		INSERT INTO MS_COUPON(
						  CD_STORE,                                                                       
				             	             	   NO_COUPON,     
						   NO_SEQ,                                                                   
			  		             	   DS_COUPON,   
						   YMD_BEGIN,
			  	            		   YMD_END,
				  	             	   CNT_AVAILABLE,
						   YMD_SALE,
						   AMT_SALE,
						   CD_SAWON_CHG )                                                                     
				VALUES  (
						  @CD_STORE,                                                                       
				             	             	  @NO_COUPON,           
						  @NO_SEQ,                                                             
			  		             	  @DS_COUPON,   
						  @YMD_BEGIN,
			  	            		  @YMD_END,
				  	             	  @CNT_AVAILABLE,
						  @YMD_SALE,
						  @AMT_SALE,
						  @CD_SAWON_CHG )                                                                     
		


	END
	ELSE IF @WORK_KIND = 'U'
	BEGIN
		UPDATE MS_COUPON SET 	YMD_BEGIN 		= @YMD_BEGIN,
						YMD_END 		= @YMD_END, 
						AMT_SALE		= @AMT_SALE,
						CNT_AVAILABLE 	= @CNT_AVAILABLE,
						CD_SAWON_CHG	= @CD_SAWON_CHG,
						DT_CHANGE		= GETDATE()
		 WHERE CD_STORE 	= @CD_STORE 
		       AND NO_COUPON 	= @NO_COUPON
		       AND NO_SEQ	= @NO_SEQ

	END

 END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

ALTER TABLE SL_SALE_D
ALTER COLUMN NO_COUPON VARCHAR(22)
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[POS_SAVE_SALE_D]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[POS_SAVE_SALE_D]
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_D]

	@CD_STORE 	    		VARCHAR(4),
	@YMD_SALE			VARCHAR(8),
	@NO_POS			VARCHAR(2),
	@NO_RCP			VARCHAR(4),
	@DS_SALE			VARCHAR(1),
	@CD_MENU			VARCHAR(14),
	@SEQ				INT,
	@QTY_SALE			INT,
	@PR_SALE			INT,
	@AMT_SALE			INT,
	@DC_MENU			INT,
	@DC_RECEIPT			INT,
	@DC_MEMBER			INT,
	@NO_COUPON			VARCHAR(22),
	@DC_COUPON			INT,
	@AMT_VAT			INT,
	@DS_TAX			VARCHAR(1),
	@CD_DAMDANG		VARCHAR(6)
AS
BEGIN

	SELECT @SEQ = ISNULL(MAX(SEQ),0) + 1 FROM SL_SALE_D 
	WHERE CD_STORE 	=@CD_STORE 
	      AND YMD_SALE	=@YMD_SALE
	      AND NO_POS	=@NO_POS
	      AND NO_RCP	=@NO_RCP

	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,
					NO_COUPON,
					DC_COUPON,
					AMT_VAT,		
					DS_TAX,
					CD_DAMDANG
					)
				VALUES
					(
					@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,
					@NO_COUPON,
					@DC_COUPON,
					@AMT_VAT,
					@DS_TAX,
					@CD_DAMDANG
					)

	IF @NO_COUPON <> ''
	BEGIN
		UPDATE MS_COUPON SET CNT_USE = CNT_USE + @QTY_SALE WHERE CD_STORE = @CD_STORE AND NO_COUPON+NO_SEQ = @NO_COUPON
	END
	

END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
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')
	BEGIN
		DECLARE @CASHAMT_STD	INT
		DECLARE @CARDAMT_STD	INT
		DECLARE @CASHPNT_STD	INT
		DECLARE @CARDPNT_STD	INT

		DECLARE @CASHAMT		INT
		DECLARE @CARDAMT		INT
		DECLARE @PNT_CASH	INT
		DECLARE @PNT_CARD	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'))
			    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 @CASHPNT_STD  = ISNULL(@CASHPNT_STD,0)
			SET @CARDPNT_STD = ISNULL(@CARDPNT_STD,0)

		END

		
		IF (@CASHAMT_STD = 0) OR (@CARDAMT_STD = 0)
		BEGIN
			SELECT @CASHAMT_STD = CASHAMT_STD,
				  @CASHPNT_STD = CASH_POINT,
				  @CARDAMT_STD = CARDAMT_STD,
				  @CARDPNT_STD = CARD_POINT
			    FROM MS_STORE
			 WHERE CD_STORE =@CD_STORE
		END

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

		--카드금액이 없으면 포인트제외 금액을 현금에서 뺀다
		IF @CARDAMT = 0 
			SET @CASHAMT = @CASHAMT - @AMT_EXISTPOINT

		--카드금액이 있으면 포인트제외 금액을 카드금에서 뺀다(카드금액이 포인트 제외금액보다 작을때는 귀찮아서 처리안함)
		IF @CARDAMT > 0
			SET @CARDAMT = @CARDAMT - @AMT_EXISTPOINT

		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

		SET 	@PNT_OCCUR = @PNT_CASH + @PNT_CARD

		--포인트를 사용한 영수증은 포인트를 적립하지 않는다
		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 = PNT_TOTAL FROM SL_POINT WHERE CD_MEMBER = @CD_MEMBER
	
		--총포인트
		SELECT @PNT_TOTAL = @PNT_OCCUR + @PNT_TOTAL - @PNT_USE
	
		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,
							PNT_CASH,
							PNT_CARD,
							LINK
							)
					VALUES(
							@CD_MEMBER,
							@YMD_SALE,
							@PNT_SEQ,
							'000',
							@AMT_CASH,
							@AMT_CARD,
							@PNT_CASH,
							@PNT_CARD,
							@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,
						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,
						'',
						@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].[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 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,
						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'

			UPDATE MS_COUPON SET DS_STATUS = '1'
			WHERE CD_STORE =@CD_STORE
			     AND (CNT_AVAILABLE - CNT_USE) = 0 
		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

UPDATE SL_SALE_D SET NO_COUPON = NO_COUPON + '01' WHERE LEN(NO_COUPON ) > 0
GO

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

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE POS_SAVE_SALE_CASH
    @CD_STORE 		VARCHAR(4),
    @YMD_SALE 		VARCHAR(8),
    @NO_POS 			VARCHAR(2),
    @NO_RCP 			VARCHAR(4),
    @NO_SEQ 			INT,
    @DS_TRD 			VARCHAR(1),
    @DS_KIND			VARCHAR(1),
    @DS_TYPE			VARCHAR(1),
    @DS_INPUT			VARCHAR(1),
    @NO_CARD 			VARCHAR(20),
    @NO_APPROVAL 		VARCHAR(12),
    @AMT_APPROVAL 		INT,
    @TRD_DATE 		VARCHAR(8),
    @TRD_DATE_ORG		VARCHAR(8),
    @NO_APPROVAL_ORG	VARCHAR(12),
    @NO_VAN			INT
    
AS
BEGIN
	DECLARE @V_SEQ INT

	SELECT @V_SEQ = ISNULL(MAX(NO_SEQ), 0) + 1
                 FROM SL_CASH
	WHERE CD_STORE	=@CD_STORE
	      AND YMD_SALE	=@YMD_SALE
	      AND NO_POS	=@NO_POS
	      AND NO_RCP	=@NO_RCP

    INSERT  INTO SL_CASH( CD_STORE, 
			YMD_SALE,
			NO_POS, 
			NO_RCP,
			NO_SEQ, 
			DS_TRD,
			DS_KIND,
			DS_TYPE,
			DS_INPUT,
			NO_CARD,
			NO_APPROVAL,
			AMT_APPROVAL,
			TRD_DATE,
			TRD_DATE_ORG,
			NO_APPROVAL_ORG,
			NO_VAN)
                    VALUES(@CD_STORE,
			@YMD_SALE,
			@NO_POS,
			@NO_RCP, 
			@V_SEQ, 
			@DS_TRD,
			@DS_KIND, 
			@DS_TYPE,
			@DS_INPUT,
			@NO_CARD, 
			@NO_APPROVAL,
			@AMT_APPROVAL, 
			@TRD_DATE,
			@TRD_DATE_ORG,
			@NO_APPROVAL_ORG,
			@NO_VAN)
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


