20080225
20080304
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 @NO_RCP = MAX(NO_RCP) FROM SL_MEMBER_JOIN WHERE CD_MEMBER	= @CD_MEMBER	

		IF @NO_RCP = NULL
		BEGIN
			SET @DS_STATUS	= '5'
		END
		ELSE
		BEGIN
			SET	@DS_STATUS = '4'
			
			SELECT  @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
			      AND A.NO_RCP		=@NO_RCP
		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].[POS_SELECT_MULTIUPJANG_SALE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[POS_SELECT_MULTIUPJANG_SALE]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE POS_SELECT_MULTIUPJANG_SALE
    @cd_store 	varchar(4),
    @ymd_sale	varchar(8),
    @cd_upjang	varchar(4),
	@work_kind	int
AS
BEGIN

	declare	@upjang	varchar(100)
	declare	@temp		varchar(4)

	select @upjang	= multi_upjang from ms_upjang where cd_store =@cd_store	and cd_upjang =@cd_upjang

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

	CREATE TABLE UpjangDummyTable
	(
		IDX 		INT IDENTITY
		, UPJANG 	varchar(4)
	)

	while (@upjang <> '')
	begin
		set @temp = substring(@upjang, 1, 4)
		insert into UpjangDummyTable(UPJANG) values(@temp)

		if (charindex(',',@upjang) > 0)
			set @upjang = substring(@upjang, 6, len(@upjang)-5)
		else
			set @upjang = ''
	end

	if @work_kind = 0
	begin

		SELECT C.NM_UPJANG, 
			   B.NM_MENU, 
			   A.QTY_SALE, 
			   A.AMT_SALE, 
			   DBO.GETCOMMONNAME(A.CD_STORE, '02', A.CD_DAMDANG) AS DAMDANG,
			   DBO.GETCOMMONNAME(A.CD_STORE, '60', A.DS_LOCK)+'-'+A.NO_LOCK AS LOCK , CONVERT(VARCHAR(16), A.DT_CHANGE,120) AS DT_ORDER 
		  FROM SL_ORDER_D A, MS_MENU B, MS_UPJANG C                                                                                                                                         
		 WHERE A.CD_STORE	= B.CD_STORE                                                                                                                                        
		   AND A.CD_MENU  	= B.CD_MENU    
		   AND A.CD_STORE	= C.CD_STORE
		   AND B.CD_UPJANG  = C.CD_UPJANG                                                                                                                                     
		   AND A.CD_STORE	=@CD_STORE                                                                                                                                                
		   AND B.CD_UPJANG	IN (SELECT UPJANG FROM UpjangDummyTable)    
		ORDER BY C.CD_UPJANG, B.CD_MENU                                                                                                                                           


	end
	else
	begin
		SELECT D.NM_UPJANG,
			   C.NM_MENU,                                       
			   SUM(A.QTY_SALE) AS QTY_SALE,                     
			   SUM(A.AMT_SALE) AS AMT_SALE,                     
			   SUM(A.DC_TOT) AS AMT_DC,                         
			   SUM(A.AMT_SALE-A.DC_TOT) AS AMT_SOON,
			   DBO.GETCOMMONNAME(A.CD_STORE, '02', A.CD_DAMDANG) AS DAMDANG             
		  FROM SL_SALE_D A, SL_SALE_H B, MS_MENU C, MS_UPJANG D
		 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 A.CD_STORE  = C.CD_STORE                         
		   AND A.CD_MENU   = C.CD_MENU    
		   AND C.CD_UPJANG IN (SELECT UPJANG FROM UpjangDummyTable)        
		   AND A.CD_STORE  = D.CD_STORE
		   AND C.CD_UPJANG = D.CD_UPJANG               
		   AND B.DS_SALE <> 'V'                               
		   AND A.CD_STORE = @CD_STORE                                 
		   AND A.YMD_SALE = @YMD_SALE                                 
		 GROUP BY A.CD_STORE, C.CD_UPJANG, D.NM_UPJANG, C.NM_MENU, C.CD_MENU, A.CD_DAMDANG                        
		 ORDER BY C.CD_UPJANG, C.CD_MENU, C.NM_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='sl_sale_c' and COLUMN_NAME='dt_order')
BEGIN
ALTER TABLE sl_sale_c
ADD dt_order datetime
END
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

		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

		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 not exists(select * from information_schema.columns where TABLE_NAME='sl_lock_allot' and COLUMN_NAME='acct_lock')
BEGIN
ALTER TABLE sl_lock_allot
ADD acct_lock varchar
END
GO

update sl_lock_allot set acct_lock = ds_lock+no_lock
GO

