<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.style24.admin.biz.dao.TsaSettleDao">

	<!-- 상품정산 목록 -->
	<select id="getGoodsSettleList" parameterType="GoodsSettle" resultType="GoodsSettle">
		/* 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.MALL_PRICE                                                            /*몰판매가*/
		     , 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.SELF_TMTB_DC_AMT                                                      /*자사다다익선분담액*/
		     , ODIH.SUPPLY_COMP_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)
		       + (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT) 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 MALL_PRICE              /*몰판매가*/
		             , CASE WHEN O.MALL_GB = 'G011_20' /*제휴몰*/ THEN
		                        IFNULL(EUP.USAC_PRICE,ODIH.ITEM_PRICE)
		                    ELSE
		                        ODIH.ITEM_PRICE
		               END                                                                  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_QTY - ODIH.CNCL_RTN_QTY) * (CASE WHEN O.MALL_GB = 'G011_20' /*제휴몰*/ THEN
		                                                                             IFNULL(EUP.USAC_PRICE,ODIH.ITEM_PRICE)
		                                                                         ELSE
		                                                                             ODIH.ITEM_PRICE
		                                                                    END))
		                             + ODIH.CPN1_DC_AMT
		                         ELSE /*환입-반품완료,환입-교환완료*/
		                             (ODIH.CNCL_RTN_QTY * (CASE WHEN O.MALL_GB = 'G011_20' /*제휴몰*/ THEN
		                                                            IFNULL(EUP.USAC_PRICE,ODIH.ITEM_PRICE)
		                                                        ELSE
		                                                            ODIH.ITEM_PRICE
		                                                   END))
		                             + 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             /*다다익선할인금액*/
		             , ((100 - IFNULL(TB1.TMTB_BURDEN_RATE,0)) / 100) * ODIH.TMTB1_DC_AMT
		               + ((100 - IFNULL(TB2.TMTB_BURDEN_RATE,0)) / 100) * ODIH.TMTB2_DC_AMT AS SELF_TMTB_DC_AMT        /*자사다다익선분담액*/
		             , (IFNULL(TB1.TMTB_BURDEN_RATE,0) / 100) * ODIH.TMTB1_DC_AMT
		               + (IFNULL(TB2.TMTB_BURDEN_RATE,0) / 100) * ODIH.TMTB2_DC_AMT         AS SUPPLY_COMP_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
		                        OD.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 = CB2.CPN_ID
		                                            AND OD.SUPPLY_COMP_CD = CB2.SUPPLY_COMP_CD
		        LEFT OUTER JOIN TB_COUPON_BURDEN CB3 ON OD.CART_CPN_SQ = CB3.CPN_ID
		                                            AND OD.SUPPLY_COMP_CD = CB3.SUPPLY_COMP_CD
		        LEFT OUTER JOIN TB_TMTB_BURDEN TB1 ON OD.TMTB1_SQ = TB1.TMTB_SQ
		                                          AND OD.SUPPLY_COMP_CD = TB1.TMTB_SUPPLY_CD
		        LEFT OUTER JOIN TB_TMTB_BURDEN TB2 ON OD.TMTB2_SQ = TB2.TMTB_SQ
		                                          AND OD.SUPPLY_COMP_CD = TB2.TMTB_SUPPLY_CD
		        LEFT OUTER JOIN TB_EXTMALL_USAC_PRICE EUP ON ODIH.ORD_DTL_ITEM_HST_SQ = EUP.ORD_DTL_ITEM_HST_SQ
		        WHERE  1 = 1
		        AND    ODIH.REG_DT <![CDATA[>=]]> STR_TO_DATE(#{salesStdt},'%Y-%m-%d')
		        AND    ODIH.REG_DT <![CDATA[<]]> DATE_ADD(STR_TO_DATE(#{salesEddt},'%Y-%m-%d'), INTERVAL 1 DAY)
		        AND    ODIH.ORD_DTL_STAT IN ('G720_20','G720_50','G720_60') /*판매-배송중,환입-반품완료,환입-교환완료*/
		        <if test='distributionGb != null and distributionGb != ""'>
		        AND    SC.DISTRIBUTION_GB = #{distributionGb}
		        </if>
		        <if test='mallGb != null and mallGb != ""'>
		        AND    O.MALL_GB = #{mallGb}
		        </if>
		        <if test='extmallId != null and extmallId != ""'>
		        AND    OD.EXTMALL_ID = #{extmallId}
		        </if>
		        <if test="multiSupplyComp != null and multiSupplyComp != ''">
		        AND    SC.SUPPLY_COMP_CD IN
		            <foreach collection="multiSupplyComp" item="item" index="index"  open="(" close=")" separator=",">
		            #{item}
		            </foreach>
		        </if>
		        <if test="multiBrand != null and multiBrand != ''">
		        AND    B.BRAND_CD IN
		            <foreach collection="multiBrand" item="item" index="index"  open="(" close=")" separator=",">
		            #{item}
		            </foreach>
		        </if>
		       ) ODIH
		ORDER  BY ODIH.ORD_DTL_ITEM_HST_SQ
	</select>
	
	<!-- 정산확정 데이터 삭제 -->
	<delete id="deleteSettleConfirm" parameterType="String">
		/* TsaSettle.deleteSettleConfirm */
		DELETE FROM TB_USAC
		WHERE  USAC_YM = REPLACE(#{settleYm},'-','')
	</delete>
	
	<!-- 정산확정 데이터 생성 -->
	<insert id="createSettleConfirm" parameterType="SettleConfirm">
		/* TsaSettle.createSettleConfirm */
		INSERT INTO TB_USAC (
		       USAC_YM
		     , SUPPLY_COMP_CD
		     , ECONTRACT_YN
		     , DISTRIBUTION_GB
		     , SETTLE_DAY
		     , SELL_QTY
		     , REAL_SELL_AMT
		     , SELL_FEE_AMT
		     , CPN_DC_AMT
		     , SELF_CPN_DC_AMT
		     , SUPPLY_COMP_CPN_DC_AMT
		     , TMTB_DC_AMT
		     , SELF_TMTB_DC_AMT
		     , SUPPLY_COMP_TMTB_DC_AMT
		     , BILL_AMT
		     , SUPPLY_AMT
		     , TAX_AMT
		     , DELV_FEE
		     , BANK_CD
		     , ACCOUNT_NO
		     , DEPOSITOR_NM
		     , BILL_EMAIL
		     , REG_NO
		     , REG_DT
		)
		SELECT ODIH.USAC_YM                                                       /*정산연월*/
		     , ODIH.SUPPLY_COMP_CD                                                /*공급업체코드*/
		     , SC.ECONTRACT_YN                                                    /*전자계약여부*/
		     , SC.DISTRIBUTION_GB                                                 /*유통구분*/
		     , SC.SETTLE_DAY                                                      /*정산주기*/
		     , SUM(ODIH.SELL_QTY)                      AS SELL_QTY                /*판매수량*/
		     , SUM(ODIH.REAL_SELL_AMT)                 AS REAL_SELL_AMT           /*실판매금액(=상품총액)*/
		     , SUM(ODIH.SELL_FEE_AMT)                  AS SELL_FEE_AMT            /*수수료*/
		     , SUM(ODIH.CPN_DC_AMT)                    AS CPN_DC_AMT              /*쿠폰할인금액*/
		     , SUM(ODIH.SELF_CPN_DC_AMT)               AS SELF_CPN_DC_AMT         /*자사쿠폰분담액*/
		     , SUM(ODIH.SUPPLY_COMP_CPN_DC_AMT)        AS SUPPLY_COMP_CPN_DC_AMT  /*입점쿠폰분담액*/
		     , SUM(ODIH.TMTB_DC_AMT)                   AS TMTB_DC_AMT             /*다다익선할인금액*/
		     , SUM(ODIH.SELF_TMTB_DC_AMT)              AS SELF_TMTB_DC_AMT        /*자사다다익선분담액*/
		     , SUM(ODIH.SUPPLY_COMP_TMTB_DC_AMT)       AS SUPPLY_COMP_TMTB_DC_AMT /*입점다다익선분담액*/
		     , SUM(CASE WHEN ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT) <![CDATA[<]]> 0 THEN 0
		                ELSE ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT)
		           END)                                AS BILL_AMT               /*계산서발행금액*/
		     , TRUNCATE(SUM(CASE WHEN ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT) <![CDATA[<]]> 0 THEN 0
		                         ELSE ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT)
		                    END) / 1.1,0)              AS SUPPLY_AMT             /*공급가액*/
		     , SUM(CASE WHEN ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT) <![CDATA[<]]> 0 THEN 0
		                ELSE ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT)
		           END) - (TRUNCATE(SUM(CASE WHEN ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT) <![CDATA[<]]> 0 THEN 0
		                                     ELSE ODIH.SELL_FEE_AMT - (ODIH.SELF_CPN_DC_AMT + ODIH.SELF_TMTB_DC_AMT)
		                                END) / 1.1,0)) AS TAX_AMT                /*세액*/
		     , SUM(ODIH.DELV_FEE)                      AS DELV_FEE               /*배송비*/
		     , SC.BANK_CD                                                        /*은행코드*/
		     , SC.ACCOUNT_NO                                                     /*계좌번호*/
		     , SC.DEPOSITOR_NM                                                   /*예금주명*/
		     , SC.BILL_EMAIL                                                     /*세금계산서이메일*/
		     , #{regNo}                                AS REG_NO
		     , NOW()                                   AS REG_DT
		FROM   (
		        SELECT DATE_FORMAT(ODIH.OCCUR_DT,'%Y%m')            AS USAC_YM                 /*정산연월*/
		             , ODIH.SUPPLY_COMP_CD                                                     /*공급업체코드*/
		             , SUM(ODIH.SELL_QTY)                           AS SELL_QTY                /*판매수량*/
		             , SUM(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 /*실판매금액(=상품총액. 쿠폰과 다다익선만 차감. 상품권과 포인트는 포함)*/
		             , ROUND(SUM((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            /*수수료*/
		             , SUM(ODIH.CPN1_DC_AMT +
		                   ODIH.GOODS_CPN_DC_AMT +
		                   ODIH.CART_CPN_DC_AMT)                    AS CPN_DC_AMT              /*쿠폰할인금액*/
		             , SUM(ODIH.SELF_CPN_DC_AMT)                    AS SELF_CPN_DC_AMT         /*자사쿠폰분담액*/
		             , SUM(ODIH.SUPPLY_COMP_CPN_DC_AMT)             AS SUPPLY_COMP_CPN_DC_AMT  /*입점쿠폰분담액*/
		             , SUM(ODIH.TMTB_DC_AMT)                        AS TMTB_DC_AMT             /*다다익선할인금액*/
		             , SUM(ODIH.SELF_TMTB_DC_AMT)                   AS SELF_TMTB_DC_AMT        /*자사다다익선분담액*/
		             , SUM(ODIH.SUPPLY_COMP_TMTB_DC_AMT)            AS SUPPLY_COMP_TMTB_DC_AMT /*입점다다익선분담액*/
		             , 0                                            AS DELV_FEE                /*배송비*/
		        FROM   (
		                SELECT ODIH.REG_DT                                                          AS OCCUR_DT                /*발생일시*/
		                     , OD.SUPPLY_COMP_CD                                                                               /*공급업체코드*/
		                     , (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_QTY - ODIH.CNCL_RTN_QTY) * (CASE WHEN O.MALL_GB = 'G011_20' /*제휴몰*/ THEN
		                                                                                     IFNULL(EUP.USAC_PRICE,ODIH.ITEM_PRICE)
		                                                                                 ELSE
		                                                                                     ODIH.ITEM_PRICE
		                                                                            END))
		                                     + ODIH.CPN1_DC_AMT
		                                 ELSE /*환입-반품완료,환입-교환완료*/
		                                     (ODIH.CNCL_RTN_QTY * (CASE WHEN O.MALL_GB = 'G011_20' /*제휴몰*/ THEN
		                                                                    IFNULL(EUP.USAC_PRICE,ODIH.ITEM_PRICE)
		                                                                ELSE
		                                                                    ODIH.ITEM_PRICE
		                                                           END))
		                                     + 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             /*다다익선할인금액*/
		                     , ((100 - IFNULL(TB1.TMTB_BURDEN_RATE,0)) / 100) * ODIH.TMTB1_DC_AMT
		                       + ((100 - IFNULL(TB2.TMTB_BURDEN_RATE,0)) / 100) * ODIH.TMTB2_DC_AMT AS SELF_TMTB_DC_AMT        /*자사다다익선분담액*/
		                     , (IFNULL(TB1.TMTB_BURDEN_RATE,0) / 100) * ODIH.TMTB1_DC_AMT
		                       + (IFNULL(TB2.TMTB_BURDEN_RATE,0) / 100) * ODIH.TMTB2_DC_AMT         AS SUPPLY_COMP_TMTB_DC_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
		                                OD.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
		                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 = CB2.CPN_ID
		                                                    AND OD.SUPPLY_COMP_CD = CB2.SUPPLY_COMP_CD
		                LEFT OUTER JOIN TB_COUPON_BURDEN CB3 ON OD.CART_CPN_SQ = CB3.CPN_ID
		                                                    AND OD.SUPPLY_COMP_CD = CB3.SUPPLY_COMP_CD
		                LEFT OUTER JOIN TB_TMTB_BURDEN TB1 ON OD.TMTB1_SQ = TB1.TMTB_SQ
		                                                  AND OD.SUPPLY_COMP_CD = TB1.TMTB_SUPPLY_CD
		                LEFT OUTER JOIN TB_TMTB_BURDEN TB2 ON OD.TMTB2_SQ = TB2.TMTB_SQ
		                                                  AND OD.SUPPLY_COMP_CD = TB2.TMTB_SUPPLY_CD
		                LEFT OUTER JOIN TB_EXTMALL_USAC_PRICE EUP ON ODIH.ORD_DTL_ITEM_HST_SQ = EUP.ORD_DTL_ITEM_HST_SQ
		                WHERE  1 = 1
		                AND    ODIH.REG_DT <![CDATA[>=]]> STR_TO_DATE(CONCAT(#{settleYm},'-01'),'%Y-%m-%d')
		                AND    ODIH.REG_DT <![CDATA[<]]> DATE_ADD(STR_TO_DATE(CONCAT(#{settleYm},'-01'),'%Y-%m-%d'), INTERVAL 1 MONTH)
		                AND    ODIH.ORD_DTL_STAT IN ('G720_20','G720_50','G720_60') /*판매-배송중,환입-반품완료,환입-교환완료*/
		               ) ODIH
		        GROUP  BY DATE_FORMAT(ODIH.OCCUR_DT,'%Y%m')
		                , ODIH.SUPPLY_COMP_CD
		        
		        UNION ALL
		        
		        /*업체별배송비*/
		        SELECT DATE_FORMAT(OD.OCCUR_DT,'%Y%m') AS USAC_YM                 /*정산연월*/
		             , OD.SUPPLY_COMP_CD                                          /*공급업체코드*/
		             , 0                               AS SELL_QTY                /*판매수량*/
		             , 0                               AS REAL_SELL_AMT           /*실판매금액(=상품총액. 쿠폰과 다다익선만 차감. 상품권과 포인트는 포함)*/
		             , 0                               AS SELL_FEE_AMT            /*수수료*/
		             , 0                               AS CPN_DC_AMT              /*쿠폰할인금액*/
		             , 0                               AS SELF_CPN_DC_AMT         /*자사쿠폰분담액*/
		             , 0                               AS SUPPLY_COMP_CPN_DC_AMT  /*입점쿠폰분담액*/
		             , 0                               AS TMTB_DC_AMT             /*다다익선할인금액*/
		             , 0                               AS SELF_TMTB_DC_AMT        /*자사다다익선분담액*/
		             , 0                               AS SUPPLY_COMP_TMTB_DC_AMT /*입점다다익선분담액*/
		             , SUM(DF.DELV_FEE)                AS DELV_FEE                /*배송비*/
		        FROM   (
		                SELECT DISTINCT
		                       ODIH.REG_DT       AS OCCUR_DT  /*발생일시*/
		                     , OD.SUPPLY_COMP_CD              /*공급업체코드*/
		                     , ODIH.ORD_NO                    /*주문번호*/
		                     , ODIH.ORD_DTL_STAT AS SETTLE_GB /*정산구분*/
		                FROM   TB_ORDER_DETAIL_ITEM_HST ODIH
		                     , TB_ORDER_DETAIL OD
		                     , TB_GOODS G
		                WHERE  ODIH.ORD_DTL_NO = OD.ORD_DTL_NO
		                AND    OD.GOODS_CD = G.GOODS_CD
		                AND    ODIH.REG_DT <![CDATA[>=]]> STR_TO_DATE(CONCAT(#{settleYm},'-01'),'%Y-%m-%d')
		                AND    ODIH.REG_DT <![CDATA[<]]> DATE_ADD(STR_TO_DATE(CONCAT(#{settleYm},'-01'),'%Y-%m-%d'), INTERVAL 1 MONTH)
		                AND    ODIH.ORD_DTL_STAT IN ('G720_20','G720_50','G720_60') /*판매-배송중,환입-반품완료,환입-교환완료*/
		               ) OD
		             , TB_DELIVERY_FEE DF
		        WHERE  OD.SUPPLY_COMP_CD = DF.SUPPLY_COMP_CD
		        AND    OD.ORD_NO = DF.ORD_NO
		        AND    OD.OCCUR_DT >= DF.REG_DT /*출고기준의 일자 보다 배송비 발생일자가 적은 넘만*/
		        GROUP  BY DATE_FORMAT(OD.OCCUR_DT,'%Y%m')
		                , OD.SUPPLY_COMP_CD
		       ) ODIH
		     , TB_SUPPLY_COMPANY SC
		WHERE  ODIH.SUPPLY_COMP_CD = SC.SUPPLY_COMP_CD
		GROUP  BY ODIH.USAC_YM
		     , ODIH.SUPPLY_COMP_CD
		     , SC.ECONTRACT_YN
		     , SC.DISTRIBUTION_GB
		     , SC.SETTLE_DAY
		     , SC.BANK_CD
		     , SC.ACCOUNT_NO
		     , SC.DEPOSITOR_NM
		     , SC.BILL_EMAIL
	</insert>
	
	<!-- 배송비정산 목록 -->
	<select id="getDeliveryFeeSettleList" parameterType="DelvFeeSettle" resultType="DelvFeeSettle">
		/* TsaSettle.getDeliveryFeeSettleList */
		SELECT DATE_FORMAT(OD.OCCUR_DT,'%Y-%m-%d')       AS OCCUR_DT           /*발생(출고/회수)일시*/
		     , OD.SUPPLY_COMP_CD                                               /*공급업체코드*/
		     , SC.SUPPLY_COMP_NM                                               /*공급업체명*/
		     , FN_GET_CODE_NM('G065',SC.DISTRIBUTION_GB) AS DISTRIBUTION_GB_NM /*유통구분명*/
		     , DF.DELV_FEE_GB                                                  /*배송비구분*/
		     , FN_GET_CODE_NM('G018',DF.DELV_FEE_GB)     AS DELV_FEE_GB_NM     /*배송비구분명*/
		     , OD.ORD_NO                                                       /*주문번호*/
		     , SUM(DF.DELV_FEE)                          AS DELV_FEE           /*배송비*/
		FROM   (
		        SELECT DISTINCT
		               ODIH.REG_DT       AS OCCUR_DT  /*발생일시*/
		             , OD.SUPPLY_COMP_CD              /*공급업체코드*/
		             , ODIH.ORD_NO                    /*주문번호*/
		             , ODIH.ORD_DTL_STAT AS SETTLE_GB /*정산구분*/
		        FROM   TB_ORDER_DETAIL_ITEM_HST ODIH
		             , TB_ORDER_DETAIL OD
		             , TB_GOODS G
		        WHERE  ODIH.ORD_DTL_NO = OD.ORD_DTL_NO
		        AND    OD.GOODS_CD = G.GOODS_CD
		        AND    ODIH.REG_DT <![CDATA[>=]]> STR_TO_DATE(#{salesStdt},'%Y-%m-%d')
		        AND    ODIH.REG_DT <![CDATA[<]]> DATE_ADD(STR_TO_DATE(#{salesEddt},'%Y-%m-%d'), INTERVAL 1 DAY)
		        AND    ODIH.ORD_DTL_STAT IN ('G720_20','G720_50','G720_60') /*판매-배송중,환입-반품완료,환입-교환완료*/
		        <if test="multiSupplyComp != null and multiSupplyComp != ''">
		        AND    OD.SUPPLY_COMP_CD IN
		            <foreach collection="multiSupplyComp" item="item" index="index"  open="(" close=")" separator=",">
		            #{item}
		            </foreach>
		        </if>
			    <if test="multiBrand != null and multiBrand != ''">
		        AND    G.BRAND_CD IN
		            <foreach collection="multiBrand" item="item" index="index"  open="(" close=")" separator=",">
		            #{item}
		            </foreach>
		        </if>
			   ) OD
			 , TB_DELIVERY_FEE DF
		     , TB_SUPPLY_COMPANY SC
		WHERE  OD.SUPPLY_COMP_CD = DF.SUPPLY_COMP_CD
		AND    OD.ORD_NO = DF.ORD_NO
		AND    OD.OCCUR_DT >= DF.REG_DT /*출고기준의 일자 보다 배송비 발생일자가 적은 넘만*/
		AND    OD.SUPPLY_COMP_CD = SC.SUPPLY_COMP_CD
		<if test='distributionGb != null and distributionGb != ""'>
		AND    SC.DISTRIBUTION_GB = #{distributionGb}
		</if>
		GROUP  BY DATE_FORMAT(OD.OCCUR_DT,'%Y-%m-%d')
		        , OD.SUPPLY_COMP_CD
		        , SC.SUPPLY_COMP_NM
		        , SC.DISTRIBUTION_GB
		        , DF.DELV_FEE_GB
		        , OD.ORD_NO
		ORDER  BY DATE_FORMAT(OD.OCCUR_DT,'%Y-%m-%d'), OD.SUPPLY_COMP_CD, DF.DELV_FEE_GB
	</select>
	
	<!-- 정산확정 목록 -->
	<select id="getSettleConfirmList" parameterType="SettleConfirm" resultType="SettleConfirm">
		/* TsaSettle.getSettleConfirmList */
		SELECT U.USAC_YM                                                                                                 /*정산연월*/
		     , U.SUPPLY_COMP_CD                                                                                          /*공급업체코드*/
		     , SC.SUPPLY_COMP_NM                                                                                         /*공급업체명*/
		     , U.ECONTRACT_YN                                                                                            /*전자계약여부*/
		     , SV.BIZ_NO                                                                                                 /*사업자번호*/
		     , U.SETTLE_DAY                                                                                              /*정산주기*/
		     , FN_GET_CODE_NM('G075',U.SETTLE_DAY)                                                     AS SETTLE_DAY_NM  /*정산주기명*/
		     , U.SELL_QTY                                                                                                /*판매수량*/
		     , U.REAL_SELL_AMT                                                                                           /*실판매금액(=상품총액)*/
		     , U.SELL_FEE_AMT                                                                                            /*수수료*/
		     , U.CPN_DC_AMT                                                                                              /*쿠폰할인금액*/
		     , U.SELF_CPN_DC_AMT                                                                                         /*자사쿠폰분담액*/
		     , U.SUPPLY_COMP_CPN_DC_AMT                                                                                  /*입점쿠폰분담액*/
		     , U.TMTB_DC_AMT                                                                                             /*다다익선할인금액*/
		     , U.SELF_TMTB_DC_AMT                                                                                        /*자사다다익선분담액*/
		     , U.SUPPLY_COMP_TMTB_DC_AMT                                                                                 /*입점다다익선분담액*/
		     , U.BILL_AMT                                                                                                /*계산서발행금액*/
		     , U.SUPPLY_AMT                                                                                              /*공급금액*/
		     , U.TAX_AMT                                                                                                 /*세액*/
		     , U.DELV_FEE                                                                                                /*배송비*/
		     , IFNULL(UR2.ETC_DEDUCT_AMT,0)                                                            AS ETC_DEDUCT_AMT /*기타차감*/
		     , U.BILL_AMT + U.DELV_FEE - IFNULL(UR2.ETC_DEDUCT_AMT,0)                                  AS GIVE_AMT       /*지급금액*/
		     , IFNULL(UR1.RECEIVABLE_AMT,0)                                                            AS RECEIVABLE_AMT /*미수금*/
		     , (U.BILL_AMT + U.DELV_FEE - IFNULL(UR2.ETC_DEDUCT_AMT,0)) - IFNULL(UR1.RECEIVABLE_AMT,0) AS TOT_GIVE_AMT   /*총지급금액*/
		     , CASE WHEN U.ECONTRACT_YN = 'N' THEN '보류'
		            ELSE
		                CASE WHEN (U.BILL_AMT + U.DELV_FEE - IFNULL(UR2.ETC_DEDUCT_AMT,0)) <![CDATA[<]]> 0 THEN
		                         '미수금'
		                     ELSE
		                         '정상'
		                END
		       END                                                                                     AS SETTLE_STAT    /*정산상태*/
		     , U.BANK_CD                                                                                                 /*은행코드*/
		     , FN_GET_CODE_NM('G940',U.BANK_CD)                                                        AS BANK_NM        /*은행명*/
		     , U.ACCOUNT_NO                                                                                              /*계좌번호*/
		     , U.DEPOSITOR_NM                                                                                            /*예금주명*/
		     , U.BILL_EMAIL                                                                                              /*계산서이메일*/
		FROM   TB_USAC U
		INNER JOIN TB_SUPPLY_COMPANY SC ON U.SUPPLY_COMP_CD = SC.SUPPLY_COMP_CD
		INNER JOIN TB_SUPPLY_VENDOR SV ON SC.SUPPLY_VENDOR_CD = SV.SUPPLY_VENDOR_CD
		LEFT OUTER JOIN TB_USAC_RECEIVE UR1 ON U.USAC_YM = UR1.USAC_YM
		                                   AND U.SUPPLY_COMP_CD = UR1.SUPPLY_COMP_CD
		LEFT OUTER JOIN (
		                 SELECT OCCUR_YM        AS USAC_YM
		                      , SUPPLY_COMP_CD
		                      , SUM(REWARD_AMT) AS ETC_DEDUCT_AMT
		                 FROM   TB_USAC_DEDUCT
		                 WHERE  OCCUR_YM = REPLACE(#{settleYm},'-','')
		                 GROUP  BY OCCUR_YM, SUPPLY_COMP_CD
		                ) UR2 ON U.USAC_YM = UR2.USAC_YM
		                     AND U.SUPPLY_COMP_CD = UR2.SUPPLY_COMP_CD
		WHERE  U.USAC_YM = REPLACE(#{settleYm},'-','')
		<if test="multiSupplyComp != null and multiSupplyComp != ''">
		AND    U.SUPPLY_COMP_CD IN
		    <foreach collection="multiSupplyComp" item="item" index="index"  open="(" close=")" separator=",">
		    #{item}
		    </foreach>
		</if>
	</select>
	
	<!-- 정산확정 미수금 생성 -->
	<insert id="createReceivableAmt" parameterType="SettleConfirm">
		/* TsaSettle.createReceivableAmt */
		INSERT INTO TB_USAC_RECEIVE (
		       USAC_YM
		     , SUPPLY_COMP_CD
		     , RECEIVABLE_AMT
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       #{usacYm}
		     , #{supplyCompCd}
		     , IFNULL(#{receivableAmt},0)
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       RECEIVABLE_AMT = IFNULL(#{receivableAmt},RECEIVABLE_AMT)
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
	</insert>
	
	<!-- 정산확정 기타차감금액 생성 -->
	<insert id="createEtcDeductAmt" parameterType="SettleConfirm">
		/* TsaSettle.createEtcDeductAmt */
		INSERT INTO TB_USAC_DEDUCT (
		       DEDUCT_SQ
		     , OCCUR_YM
		     , SUPPLY_COMP_CD
		     , SUPPLY_COMP_NM
		     , CUST_ID
		     , CUST_NM
		     , ORD_NO
		     , CATE_NM
		     , CHARGE_NM
		     , ORD_GOODS
		     , CURR_PRICE
		     , REWARD_AMT
		     , REASON
		     , REG_NO
		     , REG_DT
		)
		VALUES (
		       NULL
		     , REPLACE(#{occurYm},'-','')
		     , #{supplyCompCd}
		     , #{supplyCompNm}
		     , #{custId}
		     , #{custNm}
		     , #{ordNo}
		     , #{cateNm}
		     , #{chargeNm}
		     , #{ordGoods}
		     , IFNULL(REPLACE(#{currPrice},',',''),0)
		     , IFNULL(REPLACE(#{rewardAmt},',',''),0)
		     , #{reason}
		     , #{regNo}
		     , NOW()
		)
	</insert>
	
	<!-- 세금계산서용 정산 목록 -->
	<select id="getTaxBillSettleList" parameterType="SettleConfirm" resultType="SettleConfirm">
		/* TsaSettle.getTaxBillSettleList */
		SELECT U.USAC_YM                                /*정산연월*/
		     , '상품판매수수료'            AS BILL_TYPE /*계산서유형*/
		     , U.SUPPLY_AMT                             /*공급가액*/
		     , U.TAX_AMT                                /*세액*/
		     , U.BILL_AMT                  AS TRADE_AMT /*계산서발행금액*/
		     , SV.BIZ_NO                                /*사업자번호*/
		     , U.SUPPLY_COMP_CD                         /*공급업체코드*/
		     , SC.SUPPLY_COMP_NM                        /*공급업체명*/
		     , SV.OWNER_NM                              /*대표자명*/
		     , SV.BIZ_TYPE                              /*업태*/
		     , SV.BIZ_KIND                              /*업종*/
		     , SV.BIZ_BASE_ADDR                         /*사업장기본주소*/
		     , SV.BIZ_DTL_ADDR                          /*사업장상세주소*/
		     , SC.SETTLE_CHARGE_NM                      /*정산담당자명*/
		     , SC.SETTLE_CHARGE_TELNO                   /*정산담당자전화번호*/
		     , U.BILL_EMAIL                             /*계산서이메일*/
		     , U.BILL_AMT                               /*계산서발행금액*/
		FROM   TB_USAC U
		INNER JOIN TB_SUPPLY_COMPANY SC ON U.SUPPLY_COMP_CD = SC.SUPPLY_COMP_CD
		INNER JOIN TB_SUPPLY_VENDOR SV ON SC.SUPPLY_VENDOR_CD = SV.SUPPLY_VENDOR_CD
		WHERE  U.USAC_YM = REPLACE(#{settleYm},'-','')
	</select>
	
	<!-- 상품권현황 목록 -->
	<select id="getGiftcardStatusList" parameterType="GiftcardSettle" resultType="GiftcardSettle">
		/* TsaSettle.getGiftcardStatusList */
		SELECT CG.GFCD_NO                                                                          /*상품권번호*/
		     , DATE_FORMAT(CG.REG_DT,'%Y-%m-%d')                                   AS REG_DT       /*등록일*/
		     , DATE_FORMAT(CG.USE_EXP_DATE,'%Y-%m-%d')                             AS USE_EXP_DATE /*사용만료일*/
		     , CASE WHEN NOW() <![CDATA[<]]> CG.USE_EXP_DATE THEN 'Y' ELSE 'N' END AS AVAIL_YN     /*유효여부*/
		     , CG.CUST_NO                                                                          /*고객번호*/
		     , C.CUST_ID                                                                           /*고객ID*/
		     , CG.CHG_GFCD_AMT                                                                     /*전환금액(=최초등록금액)*/
		     , CG.US_GFCD_AMT                                                                      /*누적사용금액*/
		     , CG.RM_GFCD_AMT                                                                      /*잔여금액*/
		FROM   TB_CUST_GIFTCARD CG
		INNER JOIN TB_CUSTOMER C ON CG.CUST_NO = C.CUST_NO
		WHERE  1 = 1
		<choose>
		    <when test="termGb == 'EXPIRE_DT'"> <!-- 사용만료일 -->
		AND    CG.USE_EXP_DATE <![CDATA[>=]]> STR_TO_DATE(#{startDt},'%Y-%m-%d')
		AND    CG.USE_EXP_DATE <![CDATA[<]]> DATE_ADD(STR_TO_DATE(#{endDt},'%Y-%m-%d'), INTERVAL 1 DAY)
		    </when>
		    <otherwise> <!-- 등록일 -->
		AND    CG.REG_DT <![CDATA[>=]]> STR_TO_DATE(#{startDt},'%Y-%m-%d')
		AND    CG.REG_DT <![CDATA[<]]> DATE_ADD(STR_TO_DATE(#{endDt},'%Y-%m-%d'), INTERVAL 1 DAY)
		    </otherwise>
		</choose>
		<if test="availYn != null and availYn != ''">
		AND    (CASE WHEN NOW() <![CDATA[<]]> CG.USE_EXP_DATE THEN 'Y' ELSE 'N' END) = #{availYn}
		</if>
		ORDER  BY CG.GFCD_NO
	</select>
	
	<!-- 상품권정산 목록 -->
	<select id="getGiftcardSettleList" parameterType="GiftcardSettle" resultType="GiftcardSettle">
		/* TsaSettle.getGiftcardSettleList */
		SELECT CG.GFCD_NO                                                                              /*상품권번호*/
		     , DATE_FORMAT(CG.REG_DT,'%Y-%m-%d')                                   AS REG_DT           /*등록일*/
		     , DATE_FORMAT(CG.USE_EXP_DATE,'%Y-%m-%d')                             AS USE_EXP_DATE     /*사용만료일*/
		     , CASE WHEN NOW() <![CDATA[<]]> CG.USE_EXP_DATE THEN 'Y' ELSE 'N' END AS AVAIL_YN         /*유효여부*/
		     , CG.CUST_NO                                                                              /*고객번호*/
		     , C.CUST_ID                                                                               /*고객ID*/
		     , CG.CHG_GFCD_AMT                                                                         /*전환금액(=최초등록금액)*/
		     , IFNULL(CGH1.USE_GFCD_AMT,0)                                         AS USE_GFCD_AMT     /*정산월사용금액*/
		     , IFNULL(CGH1.CNCL_GFCD_AMT,0)                                        AS CNCL_GFCD_AMT    /*정산월취소금액*/
		     , CG.CHG_GFCD_AMT - IFNULL(CGH2.PREV_USE_GFCD_AMT,0)                  AS PREV_RM_GFCD_AMT /*전월기준잔액*/
		     , (CG.CHG_GFCD_AMT - IFNULL(CGH2.PREV_USE_GFCD_AMT,0))
		       - IFNULL(CGH1.USE_GFCD_AMT,0) + IFNULL(CGH1.CNCL_GFCD_AMT,0)        AS RM_GFCD_AMT      /*잔액(=최초등록금액 - 전월까지의누적사용금액 - 정산월사용금액 + 정산월취소금액)*/
		FROM   TB_CUST_GIFTCARD CG
		INNER JOIN TB_CUSTOMER C ON CG.CUST_NO = C.CUST_NO
		LEFT OUTER JOIN (
		                 /*정산월기준 사용금액과 취소금액*/
		                 SELECT CUST_NO
		                      , CUST_GFCD_SQ
		                      , -1 * SUM(CASE WHEN OCCUR_GB IN ('G074_12','G074_19') THEN GFCD_AMT ELSE 0 END) AS USE_GFCD_AMT  /*사용금액*/
		                      , SUM(CASE WHEN OCCUR_GB IN ('G074_13','G074_18') THEN GFCD_AMT ELSE 0 END)      AS CNCL_GFCD_AMT /*취소금액*/
		                 FROM   TB_CUST_GIFTCARD_HST
		                 WHERE  CUST_GFCD_SQ = 16
		                 AND    REG_DT <![CDATA[>=]]> STR_TO_DATE(CONCAT(#{settleYm},'-01'),'%Y-%m-%d')
		                 AND    REG_DT <![CDATA[<]]> DATE_ADD(STR_TO_DATE(CONCAT(#{settleYm},'-01'),'%Y-%m-%d'), INTERVAL 1 MONTH)
		                 GROUP  BY CUST_NO, CUST_GFCD_SQ
		                ) CGH1 ON CG.CUST_NO = CGH1.CUST_NO
		                      AND CG.CUST_GFCD_SQ = CGH1.CUST_GFCD_SQ
		LEFT OUTER JOIN (
		                 /*전월까지의 사용누적금액*/
		                 SELECT CUST_NO
		                      , CUST_GFCD_SQ
		                      , -1 * SUM(GFCD_AMT) AS PREV_USE_GFCD_AMT
		                 FROM   TB_CUST_GIFTCARD_HST
		                 WHERE  CUST_GFCD_SQ = 16
		                 AND    REG_DT <![CDATA[<]]> STR_TO_DATE(CONCAT(#{settleYm},'-01'),'%Y-%m-%d')
		                 GROUP  BY CUST_NO, CUST_GFCD_SQ
		                ) CGH2 ON CG.CUST_NO = CGH2.CUST_NO
		                      AND CG.CUST_GFCD_SQ = CGH2.CUST_GFCD_SQ
		WHERE  1 = 1
		<if test="availYn != null and availYn != ''">
		AND    (CASE WHEN NOW() <![CDATA[<]]> CG.USE_EXP_DATE THEN 'Y' ELSE 'N' END) = #{availYn}
		</if>
		ORDER  BY CG.GFCD_NO
	</select>
	
	<!-- 제휴채널정산 목록 -->
	<select id="getAfLinkFeeList" parameterType="AfLinkFee" resultType="AfLinkFee">
		/* TsaSettle.getAfLinkFeeList */
		SELECT AF_CHANNEL                                         /*상위채널코드*/
		     , FN_GET_CODE_NM('G053',AF_CHANNEL) AS AF_CHANNEL_NM /*상위채널명*/
		     , AF_LINK_CD                                         /*채널코드*/
		     , AF_LINK_NM                                         /*채널명*/
		     , OCCUR_DT                                           /*발생일시*/
		     , ORD_NO                                             /*주문번호*/
		     , ORD_DTL_STAT                                       /*주문상세상태*/
		     , SUM(SELL_AMT)                     AS SELL_AMT      /*판매금액(상품금액.배송비제외)*/
		     , SUM(CPN1_DC_AMT +
		           TMTB1_DC_AMT + TMTB2_DC_AMT +
		           GOODS_CPN_DC_AMT +
		           CART_CPN_DC_AMT)              AS CPN_DC_AMT     /*쿠폰할인금액*/
		     , SUM(PNT_DC_AMT + PRE_PNT_DC_AMT)  AS PNT_DC_AMT     /*포인트할인금액*/
		     , SUM(GFCD_USE_AMT)                 AS GFCD_USE_AMT   /*상품권사용금액*/
		     , SUM(SELL_AMT - (CPN1_DC_AMT + TMTB1_DC_AMT + TMTB2_DC_AMT + GOODS_CPN_DC_AMT + CART_CPN_DC_AMT + PG_CPN_AMT)
		                    - (PNT_DC_AMT + PRE_PNT_DC_AMT)
		                    - GFCD_USE_AMT)      AS PAY_AMT        /*결제금액(VAT포함)*/
		     , SUM(TRUNCATE((SELL_AMT - (CPN1_DC_AMT + TMTB1_DC_AMT + TMTB2_DC_AMT + GOODS_CPN_DC_AMT + CART_CPN_DC_AMT + PG_CPN_AMT)
		                              - (PNT_DC_AMT + PRE_PNT_DC_AMT)
		                              - GFCD_USE_AMT
		                    ) / 1.1,0))          AS SALES_AMT      /*매출금액(VAT제외). 건별로 1.1로 나누고 소숫점을 절사하여 합계를 낸다.*/
		FROM   (
		        SELECT AL.AF_CHANNEL
		             , ODH.AF_LINK_CD                                              /*제휴링크코드*/
		             , AL.AF_LINK_NM
		             , DATE_FORMAT(ODH.REG_DT,'%Y-%m-%d %m:%h:%s') AS OCCUR_DT     /*발생일자*/
		             , ODH.ORD_NO
		             , ODH.ORD_DTL_NO
		             , CASE WHEN ODH.ORD_DTL_STAT = 'G013_20' THEN
		                        FN_GET_CODE_NM('G013',ODH.ORD_DTL_STAT)
		                    ELSE
		                        IFNULL(FN_GET_CODE_NM('G685',OCD.CHG_STAT),'주문취소')
		               END                                         AS ORD_DTL_STAT /*주문상세상태*/
		             , (ODH.ORD_AMT - ODH.CNCL_RTN_AMT)            AS SELL_AMT     /*판매금액(상품금액.배송비제외)*/
		             , ODH.CPN1_DC_AMT                                             /*1차쿠폰(즉시할인쿠폰)할인금액*/
		             , ODH.TMTB1_DC_AMT                                            /*다다익선할인금액(수량)*/
		             , ODH.TMTB2_DC_AMT                                            /*다다익선할인금액(금액)*/
		             , ODH.GOODS_CPN_DC_AMT                                        /*상품쿠폰할인금액*/
		             , ODH.CART_CPN_DC_AMT                                         /*장바구니할인금액*/
		             , 0                                           AS PG_CPN_AMT   /*PG쿠폰금액.TODO:바로 아래 쿼리문으로 대체해야 함*/
		             -- , ODH.PG_CPN_AMT                                              /*PG쿠폰금액*/
		             , ODH.PNT_DC_AMT                                              /*포인트할인금액*/
		             , ODH.PRE_PNT_DC_AMT                                          /*선포인트할인금액*/
		             , ODH.GFCD_USE_AMT                                            /*상품권사용금액*/
		        FROM   TB_ORDER_DETAIL_HST ODH
		        INNER JOIN TB_AF_LINK AL
		                ON ODH.AF_LINK_CD = AL.AF_LINK_CD
		        LEFT OUTER JOIN TB_ORDER_CHANGE_DETAIL OCD
		                     ON ODH.ORD_DTL_NO = OCD.ORD_DTL_NO
		                    AND OCD.CHG_STAT IN ('G685_21','G685_32','G685_42')	/*취소완료,교환완료,반품완료*/
		        WHERE  ODH.ORD_DTL_STAT IN ('G013_20','G013_99') /*결제완료,주문취소*/
		        AND    ODH.REG_DT <![CDATA[>=]]> STR_TO_DATE(#{startDt},'%Y-%m-%d')
		        AND    ODH.REG_DT <![CDATA[<]]> DATE_ADD(STR_TO_DATE(#{endDt},'%Y-%m-%d'),INTERVAL 1 DAY)
		        <if test="ordNo != null and ordNo != ''">
		        AND    ODH.ORD_NO = #{ordNo}
		        </if>
		        <if test="afLinkCd != null and afLinkCd != ''">
		        AND    AL.AF_LINK_CD = #{afLinkCd}
		        </if>
		        <if test="afChannel != null and afChannel != ''">
		        AND    AL.AF_CHANNEL = #{afChannel}
		        </if>
		       ) U
		GROUP  BY AF_CHANNEL, AF_LINK_CD, AF_LINK_NM, OCCUR_DT, ORD_NO, ORD_DTL_STAT
		ORDER  BY AF_CHANNEL, AF_LINK_CD, AF_LINK_NM, OCCUR_DT, ORD_NO, ORD_DTL_STAT
	</select>

</mapper>