<?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.iamberry.wechat.service.mapper.ChannelRebateMapper">
   <resultMap  id="BaseResultMap" type="ChannelRebate" >
        <result    column="rebate_id"    property="rebateId" />
        <result    column="rebate_sales_order_id"    property="rebateSalesOrderId" />
        <result    column="reback_from_open_id"    property="rebackFromOpenId" />
        <result    column="reback_channel_id"    property="rebackChannelId" />
        <result    column="reback_amount"    property="rebackAmount" />
        <result    column="reback_status"    property="rebackStatus" />
        <result    column="reback_time"    property="rebackTime" />
        <result    column="reback_create_time"    property="rebackCreateTime" />
        <result    column="reback_update_time"    property="rebackUpdateTime" />
    </resultMap>
    <sql    id="Base_List" >
        t.rebate_id,
        t.rebate_sales_order_id,
        t.reback_from_open_id,
        t.reback_channel_id,
        t.reback_amount,
        t.reback_status,
        t.reback_time,
        t.reback_create_time,
        t.reback_update_time
    </sql>
   <select id="getChannelRebateList" resultType="ChannelRebate" parameterType="ChannelRebate" >
        select
        <include refid="Base_List" />,
           so.sales_orderid,
           so.sales_amount,
           so.sales_yet_amount,
           so.sales_transaction_date,
           so.sales_transportation_type,
           so.sales_address_name,
           so.sales_address_tel,
           pc.color_name,
           oi.item_num
       from tb_iamberry_channel_rebate t
       LEFT JOIN tb_iamberry_shop_sales_order so on t.rebate_sales_order_id = so.sales_orderid
       LEFT JOIN tb_iamberry_shop_order_item oi on oi.item_id = t.rebate_item_id
       LEFT JOIN tb_iamberry_product_color pc on pc.color_id = oi.item_color_id
        <where>
            so.sales_status IN (2,3,5,6,7,8,9,10,11,12)
            <if test="rebateId != null   and rebateId != '' ">
                AND t.rebate_id = #{rebateId}
            </if >
            <if test="rebateSalesOrderId != null and rebateSalesOrderId != ''">
                AND t.rebate_sales_order_id = #{rebateSalesOrderId}
            </if >
            <if test="rebackChannelId != null   and rebackChannelId != ''">
                AND t.reback_channel_id = #{rebackChannelId}
            </if >
            <if test="rebackStatus != null and rebackStatus != ''">
                AND t.reback_status = #{rebackStatus}
            </if >
            <if test="salesOrderid != null  and salesOrderid != ''">
                AND so.sales_orderid = #{salesOrderid}
            </if >
            <if test="stateDate != null  and stateDate != ''">
                AND t.reback_create_time &gt; #{stateDate}
            </if >
            <if test="endDate != null and endDate != ''">
                AND t.reback_create_time &lt; #{endDate}
            </if >
            <if test="rebackChannelId != null  and rebackChannelId != ''">
                AND t.reback_channel_id = #{rebackChannelId}
            </if >
            <if test="rebackChannelType != null  and rebackChannelType != ''">
                AND t.reback_channel_type = #{rebackChannelType}
            </if >
        </where>
       ORDER BY  t.rebate_id DESC
    </select>
    <select id="getChannelRebateById" resultMap="BaseResultMap" parameterType="Integer" >
        select
        <include refid="Base_List" />
        from tb_iamberry_channel_rebate t
        where t.rebate_id= #{rebateId}
    </select>
    <insert id="save" parameterType="ChannelRebate" >
        insert into
        tb_iamberry_channel_rebate
        (
        rebate_sales_order_id,
        rebate_item_id,
        reback_from_open_id,
        reback_channel_id,
        reback_channel_type,
        reback_amount,
        reback_status,
        reback_time
        )
        values
        (
        #{rebateSalesOrderId},
        #{rebateItemId},
        #{rebackFromOpenId},
        #{rebackChannelId},
        #{rebackChannelType},
        #{rebackAmount},
        #{rebackStatus},
        #{rebackTime}
        )
    </insert>
    <update id="update" parameterType="ChannelRebate" >
        update
        tb_iamberry_channel_rebate
        <set >
            <if test="rebateSalesOrderId != null and rebateSalesOrderId != ''">
                rebate_sales_order_id = #{rebateSalesOrderId},
            </if >
            <if test="rebackFromOpenId != null and rebackFromOpenId != ''">
                reback_from_open_id = #{rebackFromOpenId},
            </if >
            <if test="rebackChannelId != null ">
                reback_channel_id = #{rebackChannelId},
            </if >
            <if test="rebateItemId != null ">
                rebate_item_id = #{rebateItemId},
            </if >
            <if test="rebackAmount != null ">
                reback_amount = #{rebackAmount},
            </if >
            <if test="rebackStatus != null ">
                reback_status = #{rebackStatus},
            </if >
            <if test="rebackTime != null and rebackTime != ''">
                reback_time = #{rebackTime},
            </if >
        </set >
        where rebate_id= #{rebateId}
    </update>

    <update id="updateRebackStatus" parameterType="ChannelRebate" >
        update
        tb_iamberry_channel_rebate t
        SET
            t.reback_status = 3,
            t.reback_time = now()

        where t.rebate_id IN(
        <foreach collection="rebates" item="item" separator=",">
            ${item}
        </foreach>)
    </update>

    <select id="getChannelRebateSum" resultType="Integer" parameterType="ChannelRebate" >
         select
        (CASE WHEN SUM(t.reback_amount) is NULL THEN 0 ELSE SUM(t.reback_amount) END) rebackAmount
        from tb_iamberry_channel_rebate t
        where t.rebate_id IN(
        <foreach collection="rebates" item="item" separator=",">
            ${item}
        </foreach>)
    </select>
</mapper>