20110907
20100325
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
			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
	--마감취소
	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

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

CREATE TABLE [dbo].[MS_POST_DORO] (
	[SEQ] [int] NOT NULL ,
	[NO_POST] [varchar] (6) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[NM_CITYDO] [varchar] (100) COLLATE Korean_Wansung_CI_AS NULL ,
	[NM_CITYGU] [varchar] (80) COLLATE Korean_Wansung_CI_AS NULL ,
	[NM_TOWN] [varchar] (100) COLLATE Korean_Wansung_CI_AS NULL ,
	[NM_DORO] [varchar] (80) COLLATE Korean_Wansung_CI_AS NULL ,
	[NM_BUILDING] [varchar] (100) COLLATE Korean_Wansung_CI_AS NULL ,
	[NM_DONG] [varchar] (100) COLLATE Korean_Wansung_CI_AS NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MS_POST_DORO] WITH NOCHECK ADD 
	CONSTRAINT [PK_MS_POST_DORO] PRIMARY KEY  CLUSTERED 
	(
		[SEQ]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [IX_MS_POST_DORO] ON [dbo].[MS_POST_DORO]([NM_DORO], [NM_DONG]) ON [PRIMARY]
GO

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

CREATE TABLE [dbo].[SL_MEMBER_NEWS_TEMP] (
	[cd_member] [varchar] (10) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[seq] [int]   NOT NULL ,
	[ymd_news] [varchar] (8) COLLATE Korean_Wansung_CI_AS NULL ,
	[news_txt] [varchar] (500) COLLATE Korean_Wansung_CI_AS NULL ,
	[ds_status] [varchar] (1) COLLATE Korean_Wansung_CI_AS NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SL_MEMBER_NEWS_TEMP] WITH NOCHECK ADD 
	CONSTRAINT [DF_SL_MEMBER_NEWS_TEMP_ds_status] DEFAULT (0) FOR [ds_status],
	CONSTRAINT [PK_SSL_MEMBER_NEWS_TEMP] PRIMARY KEY  CLUSTERED 
	(
		[cd_member],
		[seq]
	)  ON [PRIMARY] 
GO


insert into SL_MEMBER_NEWS_TEMP (cd_member, seq, ymd_news, news_txt, ds_status)
select cd_member, convert(int, seq), ymd_news, news_txt, ds_status
  from SL_MEMBER_NEWS
  
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SL_MEMBER_NEWS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SL_MEMBER_NEWS]
GO

CREATE TABLE [dbo].[SL_MEMBER_NEWS] (
	[cd_member] [varchar] (10) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[seq] [int]   NOT NULL ,
	[ymd_news] [varchar] (8) COLLATE Korean_Wansung_CI_AS NULL ,
	[news_txt] [varchar] (500) COLLATE Korean_Wansung_CI_AS NULL ,
	[ds_status] [varchar] (1) COLLATE Korean_Wansung_CI_AS NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SL_MEMBER_NEWS] WITH NOCHECK ADD 
	CONSTRAINT [DF_SL_MEMBER_NEWS_ds_status] DEFAULT (0) FOR [ds_status],
	CONSTRAINT [PK_SSL_MEMBER_NEWS] PRIMARY KEY  CLUSTERED 
	(
		[cd_member],
		[seq]
	)  ON [PRIMARY] 
GO
  
insert into SL_MEMBER_NEWS(cd_member, seq, ymd_news, news_txt, ds_status)
select cd_member, convert(int, seq), ymd_news, news_txt, ds_status
  from SL_MEMBER_NEWS_TEMP 
GO

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