DROP FUNCTION IF EXISTS FN_IS_GOODS_COUPON;
DELIMITER $$
create FUNCTION FN_IS_GOODS_COUPON
	(I_FRONT_GB       VARCHAR(20)
	,I_GOODS_CD       VARCHAR(20)
	,I_CURR_PRICE     int
	,I_USABLE_CUST_GB VARCHAR(20)
	) RETURNS CHAR(1)
/******************************************************************************
/*
/* Module       : 상품쿠폰 존재여부
/* Program Name : FN_IS_GOODS_COUPON
/* Description  : 상품상세에서 다운받을수 있는 쿠폰이 존재하는지 확인한다.
/*                  
/*                Input  : i_front_gb => 프론트구분(P:PC, M:모바일,A:APP )
/*                         i_goods_cd => 상품코드
/*                         i_curr_price => 현재판매가
/*                         i_usable_cust_gb => 고객구분
/*                OutPut : 쿠폰정보
/*
/* Program History
/*
/*----------------------------------------------------------------------------
/*   Date        CSR NO.             Name      Description
/*----------------------------------------------------------------------------
/* 2021-02-24                        eskim    Initial Release
/******************************************************************************/
BEGIN
    DECLARE O_RET_VALUE VARCHAR(1);

    SELECT CASE WHEN (
                      SELECT COUNT(*)
                      FROM   TB_GOODS G
                           , TB_COUPON CP
                      WHERE  CP.SITE_CD = 'G000_10'   -- 전시사이트
                      AND    G.GOODS_CD  = I_GOODS_CD
                      AND    CP.CPN_STAT = 'G232_11'   -- 쿠폰인 진행중인 쿠폰만
                      AND    CP.DOWN_ABL_YN = 'Y'    -- 상품상세 다운로드 가능여부 
                      AND    NOW() <= IF (CP.PD_GB = 'D', DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 DAY), '%Y-%m-%d %H:%i:%S'), CP.AVAIL_EDDT)
                      AND    NOW() BETWEEN CP.DOWN_STDT AND CP.DOWN_EDDT
                      AND    CP.CPN_TYPE  IN ('G230_11', 'G230_20', 'G230_30') /*  상품쿠폰, 주문서 쿠폰, 배송비 쿠폰*/
                      AND    (SELECT COUNT(1)
                              FROM TB_COUPON_CUST_GBN
                              WHERE CPN_ID = CP.CPN_ID
                              AND USABLE_CUST_GB IN (i_usable_cust_gb) /*사용가능고객구분 일반 : G100_10, 임직원 : G100_20*/
                             ) >= 1
                     /* AND    (SELECT COUNT(1) 
                              FROM TB_COUPON_CUST_GRADE
                              WHERE CPN_ID = CP.CPN_ID
                              AND USABLE_CUST_GRADE IN ('G110_10', 'G110_20', 'G110_30', 'G110_40', 'G110_50' ) 
                              ) = 5 */
                      AND    CP.BUY_LIMIT_AMT <= I_CURR_PRICE  /*최소주문금액*/
                      AND    (CASE WHEN I_FRONT_GB = 'P' THEN DC_PVAL
                                   WHEN I_FRONT_GB = 'M' THEN DC_MVAL
                                   ELSE DC_AVAL END) > 0       
                      AND    IF (CP.TOT_PUB_LIMIT_QTY = 0, 9999999999,CP.TOT_PUB_LIMIT_QTY) > (SELECT COUNT(1) FROM TB_CUST_COUPON WHERE CPN_ID = CP.CPN_ID) /*총발행제한수*/
                      AND   (
                              (CP.APPLY_SCOPE = 'A' ) 
                              OR  /* 적용대상:상품*/
                              (CP.APPLY_SCOPE = 'I' AND (SELECT COUNT(1) FROM TB_COUPON_REFVAL
                                                         WHERE CPN_ID = CP.CPN_ID
                                                         AND CPN_TARGET = 'G260_10'
                                                         AND DEL_YN = 'N'
                                                         AND REF_VAL = G.GOODS_CD
                                                         ) > 0
                              ) 
                              OR /* 적용대상:카테고리*/
                              (CP.APPLY_SCOPE = 'I' AND (SELECT COUNT(1) FROM TB_COUPON_REFVAL
                                                         WHERE CPN_ID = CP.CPN_ID
                                                         AND CPN_TARGET = 'G260_11' 
                                                         AND DEL_YN = 'N'
                                                         AND IFNULL(REF_FORMAL_GB,G.FORMAL_GB) = G.FORMAL_GB
                                                         AND IFNULL(REF_BRAND_CD,G.BRAND_CD) = G.BRAND_CD
                                                         AND REF_VAL IN (SELECT DISTINCT X.CATE_NO 
                                                                         FROM 
                                                                              (SELECT LEAF_CATE_NO 
                                                                                    , CATE_LVL 
                                                                                    , CATE_NO
                                                                               FROM (
                                                                                     SELECT LEAF_CATE_NO ,
                                                                                            CASE WHEN X = 1 THEN 'CATE1_NO'  -- 컬럼과 매핑(컬럼명)
                                                                                                 WHEN X = 2 THEN 'CATE2_NO'
                                                                                                 WHEN X = 3 THEN 'CATE3_NO'
                                                                                                 WHEN X = 4 THEN 'CATE4_NO'
                                                                                                 ELSE  'CATE5_NO' 
                                                                                            END CATE_LVL,
                                                                                            CASE WHEN X = 1 THEN CATE1_NO  -- 컬럼과 매핑(컬럼 데이터)
                                                                                                 WHEN X = 2 THEN CATE2_NO
                                                                                                 WHEN X = 3 THEN CATE3_NO
                                                                                                 WHEN X = 4 THEN CATE4_NO
                                                                                                 ELSE  CATE5_NO 
                                                                                            END CATE_NO
                                                                                     FROM (
                                                                                            ( SELECT LEAF_CATE_NO
                                                                                                   , CATE1_NO
                                                                                                   , CATE2_NO
                                                                                                   , CATE3_NO
                                                                                                   , CATE4_NO
                                                                                                   , CATE5_NO
                                                                                              FROM  TB_CATE_4SRCH
                                                                                              WHERE SITE_CD = 'G000_10'
                                                                                              AND CATE_TYPE = 'G031_10') A, -- 상품타입
                                                                                            (SELECT 1 AS X                -- UNPIVOT 컬럼 수 만큼 선언
                                                                                             UNION ALL SELECT 2 AS X
                                                                                             UNION ALL SELECT 3 AS X
                                                                                             UNION ALL SELECT 4 AS X
                                                                                             UNION ALL SELECT 5 AS X
                                                                                           ) B
                                                                                          )  
                                                                                     ORDER BY LEAF_CATE_NO, CATE_LVL
                                                                                    ) K
                                                                                    WHERE CATE_NO IS NOT NULL
                                                                              ) X, 
                                                                              (
                                                                               SELECT A.CATE_NO, A.GOODS_CD
                                                                               FROM TB_CATE_GOODS A
                                                                               WHERE GOODS_CD = G.GOODS_CD
                                                                              ) Y 
                                                                              WHERE X.LEAF_CATE_NO = Y.CATE_NO
                                                                        )
                                                        ) >0
                              ) 
                              OR /* 적용대상:브랜드*/
                              (CP.APPLY_SCOPE = 'I' AND (SELECT COUNT(1) FROM TB_COUPON_REFVAL
                                                         WHERE CPN_ID = CP.CPN_ID
                                                         AND CPN_TARGET = 'G260_12' 
                                                         AND DEL_YN = 'N'
                                                         AND REF_VAL = G.BRAND_CD
                                                         AND IFNULL(REF_FORMAL_GB,G.FORMAL_GB) = G.FORMAL_GB
                                                        ) >0
                              ) 
                              OR /* 적용대상:공급업체*/
                              (CP.APPLY_SCOPE = 'I' AND (SELECT COUNT(1) FROM TB_COUPON_REFVAL
                                                         WHERE CPN_ID = CP.CPN_ID
                                                         AND CPN_TARGET = 'G260_13' 
                                                         AND DEL_YN = 'N'
                                                         AND REF_VAL = G.SUPPLY_COMP_CD
                                                         /*AND IFNULL(REF_FORMAL_GB,G.FORMAL_GB) = G.FORMAL_GB*/
                                                        ) >0
                              )
                            )
                      AND NOT EXISTS (
                                      SELECT 1
                                      FROM   TB_COUPON_REFVAL SCPR
                                      WHERE  SCPR.CPN_ID = CP.CPN_ID
                                      AND    SCPR.REF_VAL = G.GOODS_CD
                                      AND    SCPR.DEL_YN = 'N' /*삭제안된넘*/
                                      AND    SCPR.CPN_TARGET = '40' /* 제외상품*/
                                     )
                      AND NOT EXISTS (
                                       SELECT 1
                                      FROM TB_COUPON_BAN_GOODS
                                      WHERE GOODS_CD = G.GOODS_CD
                                      AND DEL_YN = 'N'
                                     )
                      ) > 0
                      THEN 'Y' ELSE 'N' END AS COUPON_YN
      INTO   O_RET_VALUE
      ;

	RETURN O_RET_VALUE;
END	$$
DELIMITER ;