20080721
20080721

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 IF @WORK_KIND = 1
	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
	ELSE IF @WORK_KIND = 2
	BEGIN

		UPDATE  SL_MEMBER_JOIN     SET 
						         DELAY_BEGIN	= '',
						        DELAY_TO	= '',
						        DAY_DELAY 	=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))+1)*-1, CONVERT(DATETIME, YMD_FINISH) ), 112),
						        DS_STATUS          = '0'
				WHERE    YMD_SALE 		= @YMD_SALE
				      AND    NO_RCP		= @NO_RCP

		DELETE FROM SL_MEMBER_DELAY
		 WHERE YMD_SALE	=@YMD_SALE
		       AND NO_RCP	=@NO_RCP
		       AND YMD_DELAY	=@YMD_DELAY
	
		
	END


	 IF @WORK_KIND IN (0, 1)
	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 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)
	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
	   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 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 1 @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,'')

END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE   PROCEDURE SAVE_MS_SAWON
	@cd_store 		varchar(4),
	@cd_sawon_chg 	varchar(8),
	@i_sw     	 	char(1),
	@cd_sawon 		varchar(6),
	@nm_sawon 		varchar(10),
	@no_jumin		varchar(14),
	@no_password		varchar(50),
	@ymd_entrance 	varchar(8),
	@tel_home       		varchar(13),
	@tel_mobil      		varchar(13),
	@no_post        		varchar(7),
	@addr1          		varchar(40),
	@addr2          		varchar(40),
	@nm_email       		varchar(40),
	@ymd_leave 		varchar(8),
	@cd_time_pay		varchar(3),
	@cd_grade         	varchar(3),
	@nm_rank           	varchar(10),
	@yn_use           		varchar(1),
	@emp_work		varchar(10)
AS
begin
	if @i_sw = 0 
             begin
	insert into  MS_SAWON ( cd_store,cd_sawon,nm_sawon,no_jumin, no_password,ymd_entrance,tel_home,tel_mobil,no_post,addr1,addr2,nm_email,ymd_leave,cd_time_pay, cd_grade,nm_rank,yn_use, emp_work,cd_sawon_chg)
                        		values(@cd_store,@cd_sawon,@nm_sawon,@no_jumin, @no_password,@ymd_entrance,@tel_home,@tel_mobil,@no_post,@addr1,@addr2,@nm_email,@ymd_leave,@cd_time_pay, @cd_grade,@nm_rank,@yn_use,@emp_work,@cd_sawon_chg)
            end
            else
            begin
		update  MS_SAWON set nm_sawon=@nm_sawon,
					no_jumin = @no_jumin,
					ymd_entrance=@ymd_entrance,
					tel_home = @tel_home,
					tel_mobil=@tel_mobil,
					no_post=@no_post,
					addr1=@addr1,
					addr2=@addr2,
					nm_email=@nm_email,
					ymd_leave=@ymd_leave,
					cd_time_pay=@cd_time_pay,
					cd_grade=@cd_grade,
					nm_rank=@nm_rank,
					yn_use=@yn_use,
					cd_sawon_chg=@cd_sawon_chg,
					dt_change = getdate(), 
					emp_work = @emp_work
                                      where cd_sawon = @cd_sawon and cd_store=@cd_store
           end
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

