<?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.ChildChannelMapper">
    <resultMap  id="BaseResultMap" type="ChildChannel" >
        <result    column="child_channel_id"    property="childChannelId" />
        <result    column="child_channel_open_id"    property="childChannelOpenId" />
        <result    column="main_channel_id"    property="mainChannelId" />
        <result    column="child_channel_name"    property="childChannelName" />
        <result    column="child_channel_docking_name"    property="childChannelDockingName" />
        <result    column="child_channel_docking_tel"    property="childChannelDockingTel" />
        <result    column="child_channel_bank"    property="childChannelBank" />
        <result    column="child_channel_bank_account"    property="childChannelBankAccount" />
        <result    column="child_channel_bank_name"    property="childChannelBankName" />
        <result    column="child_channel_bank_branch"    property="childChannelBankBranch" />
        <result    column="child_channel_qrcode"    property="childChannelQrcode" />
        <result    column="child_channel_status"    property="childChannelStatus" />
        <result    column="child_channel_desc"    property="childChannelDesc" />
        <result    column="child_channel_create_time"    property="childChannelCreateTime" />
        <result    column="child_channel_update_time"    property="childChannelUpdateTime" />
    </resultMap>
    <sql    id="Base_List" >
        t.child_channel_id,
        t.child_channel_open_id,
        t.main_channel_id,
        t.child_channel_name,
        t.child_channel_docking_name,
        t.child_channel_docking_tel,
        t.child_channel_bank,
        t.child_channel_bank_account,
        t.child_channel_bank_name,
        t.child_channel_bank_branch,
        t.child_channel_qrcode,
        t.child_channel_status,
        t.child_channel_desc,
        t.child_channel_create_time,
        t.child_channel_update_time
    </sql>
    <select id="getChildChannelList" resultType="ChildChannel" parameterType="ChildChannel" >
        select
        t.child_channel_id,
        t.child_channel_open_id,
        t.main_channel_id,
        t.child_channel_name,
        t.child_channel_docking_name,
        t.child_channel_docking_tel,
        t.child_channel_bank,
        t.child_channel_bank_account,
        t.child_channel_bank_name,
        t.child_channel_bank_branch,
        t.child_channel_qrcode,
        t.child_channel_status,
        t.child_channel_desc,
        t.child_channel_create_time,
        t.child_channel_update_time,
        mt.main_channel_name,
        (CASE WHEN cr.rebackAmount is NULL THEN 0 ELSE cr.rebackAmount END) rebackAmount
        from tb_iamberry_child_channel t
        left JOIN tb_iamberry_main_channel mt
        ON t.main_channel_id = mt.main_channel_id
        left JOIN (
        SELECT
        c.reback_channel_id reback_channel_id,
        SUM(c.reback_amount) rebackAmount
        FROM
        tb_iamberry_channel_rebate c
        WHERE
        c.reback_channel_type = 2
        AND c.reback_status = 2
        ) cr
        on t.child_channel_id = cr.reback_channel_id
        <where>
            <if test="childChannelId != null ">
                AND t.child_channel_id = #{childChannelId}
            </if >
            <if test="childChannelOpenId != null and childChannelOpenId != ''">
                AND t.child_channel_open_id = #{childChannelOpenId}
            </if >
            <if test="mainChannelId != null ">
                AND t.main_channel_id = #{mainChannelId}
            </if >
            <if test="childChannelName != null and childChannelName != ''">
                AND t.child_channel_name  like  CONCAT ('%',#{childChannelName},'%')
            </if >
            <if test="childChannelDockingName != null and childChannelDockingName != ''">
                AND t.child_channel_docking_name  like  CONCAT ('%',#{childChannelDockingName},'%')
            </if >
            <if test="childChannelDockingTel != null and childChannelDockingTel != ''">
                AND t.child_channel_docking_tel  like  CONCAT ('%',#{childChannelDockingTel},'%')
            </if >
            <if test="childChannelBank != null and childChannelBank != ''">
                AND t.child_channel_bank = #{childChannelBank}
            </if >
            <if test="childChannelBankAccount != null and childChannelBankAccount != ''">
                AND t.child_channel_bank_account = #{childChannelBankAccount}
            </if >
            <if test="childChannelStatus != null ">
                AND t.child_channel_status = #{childChannelStatus}
            </if >
        </where>
        GROUP BY child_channel_id
        ORDER BY  t.child_channel_id DESC
    </select>
    <select id="getChildChannelById" resultMap="BaseResultMap" parameterType="Integer" >
        select
        <include refid="Base_List" />,
        (CASE WHEN cr.rebackAmount is NULL THEN 0 ELSE cr.rebackAmount END) rebackAmount
        from tb_iamberry_child_channel t
        LEFT JOIN (
        SELECT
        c.reback_channel_id reback_channel_id,
        SUM(c.reback_amount) rebackAmount
        FROM
        tb_iamberry_channel_rebate c
        WHERE
        c.reback_channel_type = 2
        AND c.reback_status = 2
        ) cr
        on t.child_channel_id = cr.reback_channel_id
        where
        t.child_channel_id= #{childChannelId}
        GROUP BY t.child_channel_id
    </select>
    <insert id="save" parameterType="ChildChannel" >
        insert into
        tb_iamberry_child_channel
        (
        child_channel_open_id,
        main_channel_id,
        child_channel_name,
        child_channel_docking_name,
        child_channel_docking_tel,
        child_channel_bank,
        child_channel_bank_account,
        child_channel_bank_name,
        child_channel_bank_branch,
        child_channel_qrcode,
        child_channel_status,
        child_channel_desc
        )
        values
        (
        #{childChannelOpenId},
        #{mainChannelId},
        #{childChannelName},
        #{childChannelDockingName},
        #{childChannelDockingTel},
        #{childChannelBank},
        #{childChannelBankAccount},
        #{childChannelBankName},
        #{childChannelBankBranch},
        #{childChannelQrcode},
        #{childChannelStatus},
        #{childChannelDesc}
        )
    </insert>
    <update id="update" parameterType="ChildChannel" >
        update
        tb_iamberry_child_channel
        <set >
            <if test="childChannelOpenId != null and childChannelOpenId != ''">
                child_channel_open_id = #{childChannelOpenId},
            </if >
            <if test="mainChannelId != null ">
                main_channel_id = #{mainChannelId},
            </if >
            <if test="childChannelName != null and childChannelName != ''">
                child_channel_name = #{childChannelName},
            </if >
            <if test="childChannelDockingName != null and childChannelDockingName != ''">
                child_channel_docking_name = #{childChannelDockingName},
            </if >
            <if test="childChannelDockingTel != null and childChannelDockingTel != ''">
                child_channel_docking_tel = #{childChannelDockingTel},
            </if >
            <if test="childChannelBank != null and childChannelBank != ''">
                child_channel_bank = #{childChannelBank},
            </if >
            <if test="childChannelBankAccount != null and childChannelBankAccount != ''">
                child_channel_bank_account = #{childChannelBankAccount},
            </if >
            <if test="childChannelBankName != null and childChannelBankName != ''">
                child_channel_bank_name = #{childChannelBankName},
            </if >
            <if test="childChannelBankBranch != null and childChannelBankBranch != ''">
                child_channel_bank_branch = #{childChannelBankBranch},
            </if >
            <if test="childChannelQrcode != null and childChannelQrcode != ''">
                child_channel_qrcode = #{childChannelQrcode},
            </if >
            <if test="childChannelStatus != null ">
                child_channel_status = #{childChannelStatus},
            </if >
            <if test="childChannelDesc != null and childChannelDesc != ''">
                child_channel_desc = #{childChannelDesc},
            </if >
        </set >
        where child_channel_id= #{childChannelId}
    </update>
    <delete id="delete" parameterType="Integer" >
        delete
        tb_iamberry_child_channel
        where child_channel_id=#{childChannelId}
    </delete>
</mapper>