20130710
20100325
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 [dbo].[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,	--연기기간
	@NEXT_JOIN		VARCHAR(30) OUTPUT,	--다음가입기간
	@LOCKER_END     VARCHAR(8)
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,
		  @NEXT_JOIN		=(SELECT TOP 1 DBO.STOD(YMD_BEGIN) + '~'+DBO.STOD(YMD_FINISH) FROM SL_MEMBER_JOIN WHERE CD_MEMBER=A.CD_MEMBER AND YMD_BEGIN > 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.DS_STATUS		= '0'
	      AND A.CD_MEMBER	=@CD_MEMBER
	      AND A.YMD_BEGIN <= @TODATE 
                   AND A.YMD_FINISH >= @TODATE

	--정상상태의 가입내역이 없으면
	IF LEN(@YMD_BEGIN) <> 8
	BEGIN

		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,
			  @NEXT_JOIN		=(SELECT TOP 1 DBO.STOD(YMD_BEGIN) + '~'+DBO.STOD(YMD_FINISH) FROM SL_MEMBER_JOIN WHERE CD_MEMBER=A.CD_MEMBER AND YMD_BEGIN > 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.DS_STATUS		<> '0'
		      AND A.CD_MEMBER	=@CD_MEMBER
		      AND A.YMD_BEGIN <= @TODATE 
	                   AND A.YMD_FINISH >= @TODATE

		IF LEN(@YMD_BEGIN) <> 8
		BEGIN
	
			SELECT TOP 1 @NO_RCP = NO_RCP,
                                                                  @DS_STATUS = DS_STATUS 
                                           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 IF @DS_STATUS = '3'
                        BEGIN
				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 ) ,
					  @NEXT_JOIN	=(SELECT TOP 1 DBO.STOD(A.YMD_BEGIN) +' ~ ' + DBO.STOD(A.YMD_FINISH) FROM SL_MEMBER_JOIN WHERE CD_MEMBER = A.CD_MEMBER AND YMD_BEGIN > @TODATE) 
				   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
                        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 ) ,
					  @NEXT_JOIN	=(SELECT TOP 1 DBO.STOD(A.YMD_BEGIN) +' ~ ' + DBO.STOD(A.YMD_FINISH) FROM SL_MEMBER_JOIN WHERE CD_MEMBER = A.CD_MEMBER AND YMD_BEGIN > @TODATE) 
				   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
	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) +' )',
	             @LOCKER_END  = YMD_END 
	   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 2 @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,'')
	SET @NEXT_JOIN 	= ISNULL(@NEXT_JOIN,'')
	SET @LOCKER_END 	= ISNULL(@LOCKER_END,'')	

END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

