-- G720_10	판매-결제완료
-- G720_20	판매-배송중
-- G720_30	환입-취소완료
-- G720_40	환입-품절취소
-- G720_50	환입_반품완료
-- G720_60	환입-교환완료

/* TsaSettle.getGoodsSettleList */
SELECT ODIH.ORD_NO                                                         /*주문번호*/
     , ODIH.ORD_DTL_NO                                                     /*주문상세번호*/
     , ODIH.MALL_GB                                                        /*몰구분*/
     , ODIH.MALL_GB_NM                                                     /*몰구분명*/
     , ODIH.EXTMALL_ORDER_ID                                               /*제휴몰주문번호*/
     , ODIH.EXTMALL_ID                                                     /*제휴몰ID*/
     , ODIH.EXTMALL_NM                                                     /*제휴몰명*/
     , ODIH.SUPPLY_COMP_CD                                                 /*공급업체코드*/
     , ODIH.SUPPLY_COMP_NM                                                 /*공급업체명*/
     , ODIH.DISTRIBUTION_GB                                                /*유통구분*/
     , ODIH.DISTRIBUTION_GB_NM                                             /*유통구분명*/
     , ODIH.SETTLE_DAY                                                     /*정산주기*/
     , ODIH.SETTLE_DAY_NM                                                  /*정산주기명*/
     , ODIH.ORD_DT                                                         /*주문일시*/
     , ODIH.SETTLE_GB                                                      /*정산구분*/
     , ODIH.SETTLE_GB_NM                                                   /*정산구분명*/
     , ODIH.OCCUR_DT                                                       /*발생일시*/
     , ODIH.GOODS_CD                                                       /*상품코드*/
     , ODIH.GOODS_NM                                                       /*상품명*/
     , ODIH.BRAND_CD                                                       /*브랜드코드*/
     , ODIH.BRAND_ENM                                                      /*브랜드명*/
     , ODIH.MD_NM                                                          /*담당MD*/
     , ODIH.ITEM_CD                                                        /*단품코드*/
     , ODIH.OPT_CD1                                                        /*옵션코드1*/
     , ODIH.OPT_CD2                                                        /*옵션코드2*/
     , ODIH.SELL_PRICE                                                     /*판매가*/
     , ODIH.SELL_QTY                                                       /*판매수량*/
     , ODIH.SELL_AMT                                                       /*판매금액(ORD_AMT에는 CPN1_DC_AMT가 빠진 금액이 들어가 있음)*/
     , ODIH.CPN1_DC_AMT                                                    /*즉시사용쿠폰금액*/
     , ODIH.GOODS_CPN_DC_AMT                                               /*상품쿠폰사용금액*/
     , ODIH.CART_CPN_DC_AMT                                                /*주문서쿠폰사용금액*/
     , ODIH.PNT_DC_AMT                                                     /*포인트사용금액*/
     , ODIH.TMTB_DC_AMT                                                    /*다다익선할인금액*/
     , ODIH.GFCD_USE_AMT                                                   /*상품권사용금액*/
     , ODIH.SELF_CPN_DC_AMT                                                /*자사쿠폰분담액*/
     , ODIH.SUPPLY_COMP_CPN_DC_AMT                                         /*입점쿠폰분담액*/
     , CASE WHEN ODIH.SELL_QTY = 0 THEN 0
            ELSE
                (ODIH.SELL_AMT
                 - (ODIH.CPN1_DC_AMT + ODIH.GOODS_CPN_DC_AMT + ODIH.CART_CPN_DC_AMT)
                 - ODIH.TMTB_DC_AMT
                ) / ODIH.SELL_QTY
       END                                       AS REAL_SELL_PRICE        /*실판매가*/
     , ODIH.SELL_AMT
       - (ODIH.CPN1_DC_AMT + ODIH.GOODS_CPN_DC_AMT + ODIH.CART_CPN_DC_AMT)
       - ODIH.TMTB_DC_AMT                        AS REAL_SELL_AMT          /*실판매금액(=상품총액. 쿠폰과 다다익선만 차감. 상품권과 포인트는 포함)*/
     , ODIH.SELL_FEE_RATE                                                  /*판매수수료율*/
     , ROUND((ODIH.SELL_AMT
              - (ODIH.CPN1_DC_AMT + ODIH.GOODS_CPN_DC_AMT + ODIH.CART_CPN_DC_AMT)
              - ODIH.TMTB_DC_AMT
             ) * (ODIH.SELL_FEE_RATE / 100),0)   AS SELL_FEE_AMT           /*수수료*/
     , (ODIH.SELL_AMT
        - (ODIH.CPN1_DC_AMT + ODIH.GOODS_CPN_DC_AMT + ODIH.CART_CPN_DC_AMT)
        - ODIH.TMTB_DC_AMT)
       - ROUND((ODIH.SELL_AMT
                - (ODIH.CPN1_DC_AMT + ODIH.GOODS_CPN_DC_AMT + ODIH.CART_CPN_DC_AMT)
                - ODIH.TMTB_DC_AMT
               ) * (ODIH.SELL_FEE_RATE / 100),0) AS SETTLE_AMT             /*정산대상액(실판매금액 - 수수료)*/
FROM   (
        SELECT ODIH.ORD_DTL_ITEM_HST_SQ                                                           /*주문상세단품이력일련번호*/
             , ODIH.ORD_NO                                                                        /*주문번호*/
             , ODIH.ORD_DTL_NO                                                                    /*주문상세번호*/
             , O.MALL_GB                                                                          /*몰구분*/
             , FN_GET_CODE_NM('G011',O.MALL_GB)                         AS MALL_GB_NM             /*몰구분명*/
             , OD.EXTMALL_ORDER_ID                                                                /*제휴몰주문번호*/
             , OD.EXTMALL_ID                                                                      /*제휴몰ID*/
             , E.EXTMALL_NM                                                                       /*제휴몰명*/
             , OD.SUPPLY_COMP_CD                                                                  /*공급업체코드*/
             , SC.SUPPLY_COMP_NM                                                                  /*공급업체명*/
             , SC.DISTRIBUTION_GB                                                                 /*유통구분*/
             , FN_GET_CODE_NM('G065',SC.DISTRIBUTION_GB)                AS DISTRIBUTION_GB_NM     /*유통구분명*/
             , SC.SETTLE_DAY                                                                      /*정산주기*/
             , FN_GET_CODE_NM('G075',SC.SETTLE_DAY)                     AS SETTLE_DAY_NM          /*정산주기명*/
             , DATE_FORMAT(O.ORD_DT,'%Y-%m-%d %H:%i:%S')                AS ORD_DT                 /*주문일시*/
             , ODIH.ORD_DTL_STAT                                        AS SETTLE_GB              /*정산구분*/
             , FN_GET_CODE_NM('G720',ODIH.ORD_DTL_STAT)                 AS SETTLE_GB_NM           /*정산구분명*/
             , DATE_FORMAT(ODIH.REG_DT,'%Y-%m-%d %H:%i:%S')             AS OCCUR_DT               /*발생일시*/
             , OD.GOODS_CD                                                                        /*상품코드*/
             , G.GOODS_NM                                                                         /*상품명*/
             , G.BRAND_CD                                                                         /*브랜드코드*/
             , B.BRAND_ENM                                                                        /*브랜드명*/
             , (SELECT U.USER_NM
                FROM   TB_BRAND_MD BM
                     , TB_USER U
                WHERE  BM.MD_NO = U.USER_NO
                AND    BM.BRAND_CD = G.BRAND_CD
                AND    BM.FORMAL_GB = G.FORMAL_GB
                LIMIT 1)                                                AS MD_NM                  /*담당MD*/
             , ODIH.ITEM_CD                                                                       /*단품코드*/
             , ODIH.OPT_CD1                                                                       /*옵션코드1*/
             , ODIH.OPT_CD2                                                                       /*옵션코드2*/
             , ODIH.ITEM_PRICE                                          AS SELL_PRICE             /*판매가*/
             , (CASE WHEN ODIH.ORD_DTL_STAT = 'G720_20' THEN 1 ELSE -1 END)
               *
               CAST(CASE WHEN ODIH.ORD_DTL_STAT = 'G720_20'/*판매-배송중*/ THEN
                             ODIH.ORD_QTY - ODIH.CNCL_RTN_QTY
                         ELSE /*환입-반품완료,환입-교환완료*/
                             ODIH.CNCL_RTN_QTY
                    END AS SIGNED INT)                                  AS SELL_QTY               /*판매수량*/
             , (CASE WHEN ODIH.ORD_DTL_STAT = 'G720_20' THEN 1 ELSE -1 END)
               *
               CAST(CASE WHEN ODIH.ORD_DTL_STAT = 'G720_20'/*판매-배송중*/ THEN
                             ODIH.ORD_AMT - ODIH.CNCL_RTN_AMT + ODIH.CPN1_DC_AMT
                         ELSE /*환입-반품완료,환입-교환완료*/
                             ODIH.CNCL_RTN_AMT + ODIH.CPN1_DC_AMT
                    END AS SIGNED INT)                                  AS SELL_AMT               /*판매금액(ORD_AMT에는 CPN1_DC_AMT가 빠진 금액이 들어가 있음)*/
             , ODIH.CPN1_DC_AMT                                                                   /*즉시사용쿠폰금액*/
             , ODIH.GOODS_CPN_DC_AMT                                                              /*상품쿠폰사용금액*/
             , ODIH.CART_CPN_DC_AMT                                                               /*주문서쿠폰사용금액*/
             , ODIH.PNT_DC_AMT                                                                    /*포인트사용금액*/
             , ODIH.TMTB1_DC_AMT + ODIH.TMTB2_DC_AMT                    AS TMTB_DC_AMT            /*다다익선할인금액*/
             , ODIH.GFCD_USE_AMT                                                                  /*상품권사용금액*/
             , ((100 - IFNULL(CB1.BURDEN_RATE,0)) / 100) * ODIH.CPN1_DC_AMT
               + ((100 - IFNULL(CB2.BURDEN_RATE,0)) / 100) * ODIH.GOODS_CPN_DC_AMT
               + ((100 - IFNULL(CB3.BURDEN_RATE,0)) / 100) * ODIH.CART_CPN_DC_AMT AS SELF_CPN_DC_AMT        /*자사쿠폰분담액*/
             , (IFNULL(CB1.BURDEN_RATE,0) / 100) * ODIH.CPN1_DC_AMT
               + (IFNULL(CB2.BURDEN_RATE,0) / 100) * ODIH.GOODS_CPN_DC_AMT
               + (IFNULL(CB3.BURDEN_RATE,0) / 100) * ODIH.CART_CPN_DC_AMT         AS SUPPLY_COMP_CPN_DC_AMT /*입점쿠폰분담액*/
             , CASE WHEN O.MALL_GB = 'G011_10' /*자사몰*/ THEN
                        SC.SELL_FEE_RATE
                    ELSE /*제휴몰*/
                        IFNULL(E.SELL_FEE_RATE,0)
               END                                                      AS SELL_FEE_RATE          /*판매수수료율*/
        FROM   TB_ORDER_DETAIL_ITEM_HST ODIH
        INNER JOIN TB_ORDER_DETAIL OD ON ODIH.ORD_DTL_NO = OD.ORD_DTL_NO
        INNER JOIN TB_ORDER O ON ODIH.ORD_NO = O.ORD_NO
        INNER JOIN TB_SUPPLY_COMPANY SC ON OD.SUPPLY_COMP_CD = SC.SUPPLY_COMP_CD
        INNER JOIN TB_GOODS G ON OD.GOODS_CD = G.GOODS_CD
        INNER JOIN TB_BRAND B ON G.BRAND_CD = B.BRAND_CD
        LEFT OUTER JOIN TB_EXTMALL E ON OD.EXTMALL_ID = E.EXTMALL_ID
        LEFT OUTER JOIN TB_COUPON_BURDEN CB1 ON OD.CPN1_CPN_SQ = CB1.CPN_ID
                                            AND OD.SUPPLY_COMP_CD = CB1.SUPPLY_COMP_CD
        LEFT OUTER JOIN TB_COUPON_BURDEN CB2 ON OD.GOODS_CPN_SQ = CB1.CPN_ID
                                            AND OD.SUPPLY_COMP_CD = CB1.SUPPLY_COMP_CD
        LEFT OUTER JOIN TB_COUPON_BURDEN CB3 ON OD.CART_CPN_SQ = CB1.CPN_ID
                                            AND OD.SUPPLY_COMP_CD = CB1.SUPPLY_COMP_CD
        WHERE  1 = 1
        AND    ODIH.REG_DT  >=  STR_TO_DATE( '2021-06-07' /**P*/,'%Y-%m-%d')
        AND    ODIH.REG_DT  <  DATE_ADD(STR_TO_DATE( '2021-07-06' /**P*/,'%Y-%m-%d'), INTERVAL 1 DAY)
        AND    ODIH.ORD_DTL_STAT IN ('G720_20','G720_50','G720_60') /*판매-배송중,환입-반품완료,환입-교환완료*/
       ) ODIH
ORDER  BY ODIH.ORD_DTL_ITEM_HST_SQ
