<?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.kxs.stat.biz.mapper.KxsUserTradeMapper">

    <resultMap type="com.kxs.stat.api.model.KxsUserTrade" id="KxsUserTradeMap">
        <result column="id" property="id"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <result column="del_flag" property="delFlag"/>
        <result column="version" property="version"/>
        <result column="user_id" property="userId"/>
        <result column="trade_date" property="tradeDate"/>
        <result column="trade_month" property="tradeMonth"/>
        <result column="brand_id" property="brandId"/>
        <result column="help_direct_trade_amt" property="helpDirectTradeAmt"/>
        <result column="help_debit_trade_amt" property="helpDebitTradeAmt"/>
        <result column="pro_direct_trade_amt" property="proDirectTradeAmt"/>
        <result column="pro_debit_trade_amt" property="proDebitTradeAmt"/>
        <result column="pro_a_direct_trade_amt" property="proADirectTradeAmt"/>
        <result column="pro_a_debit_trade_amt" property="proADebitTradeAmt"/>
        <result column="trade_type" property="tradeType"/>
        <result column="help_debit_cap_trade_amt" property="helpDebitCapTradeAmt"/>
        <result column="help_debit_cap_num" property="helpDebitCapNum"/>
        <result column="pro_debit_cap_trade_amt" property="proDebitCapTradeAmt"/>
        <result column="pro_debit_cap_num" property="proDebitCapNum"/>
        <result column="pro_a_debit_cap_trade_amt" property="proADebitCapTradeAmt"/>
        <result column="pro_a_debit_cap_num" property="proADebitCapNum"/>
        <result column="total_type" property="totalType"/>
        <result column="pro_b_debit_trade_amt" property="proBDebitTradeAmt"/>
        <result column="pro_b_direct_trade_amt" property="proBDirectTradeAmt"/>
        <result column="pro_b_debit_cap_trade_amt" property="proBDebitCapTradeAmt"/>
        <result column="pro_b_debit_cap_num" property="proBDebitCapNum"/>

    </resultMap>

    <update id="automaticTableCreationTasks" parameterType="String">
        CREATE TABLE IF NOT EXISTS `${tableName}` LIKE kxs_user_trade;
    </update>

    <select id="getUserTradeList" resultType="com.kxs.stat.api.vo.ShdTradeAmtVO" fetchSize="1000">
        select
        sum(pro_b_direct_trade_amt + pro_b_debit_trade_amt + pro_a_debit_trade_amt + pro_a_direct_trade_amt +
        pro_debit_trade_amt + pro_direct_trade_amt + help_debit_trade_amt + help_direct_trade_amt) as pos_amt,
        a.user_id
        from ${tableName} a
        <where>
            and a.total_type = 1
        </where>
        group by a.user_id
    </select>

    <!--    临时表操作 下面增删改查-->
    <update id="createTemporaryShdTable">
        CREATE TEMPORARY TABLE IF NOT EXISTS `${tableName}` (
            user_id INT PRIMARY KEY,
            pos_amt NUMERIC(18,2) default 0,
            lkb_amt NUMERIC(18,2) default 0,
            lkb_act_amt NUMERIC(18,2) default 0,
            gd_amt NUMERIC(18,2) default 0
        );
        TRUNCATE TABLE `${tableName}`;
    </update>
    <select id="getTempShdTableData" resultType="com.kxs.stat.api.vo.ShdTradeAmtVO">
        select * from `${tableName}`
        <where>
            and user_id = #{userId}
        </where>
    </select>
    <select id="getTempShdTableList" resultType="com.kxs.stat.api.vo.ShdTradeAmtVO" fetchSize="1000">
        select * from `${tableName}`
    </select>
    <select id="getStatAmount" resultType="java.math.BigDecimal">
        select sum(pro_debit_trade_amt + pro_direct_trade_amt) from `${tableName}` where user_id = 1 and del_flag != 1
    </select>
    <select id="getUserTradeMonthTotal" resultType="com.kxs.stat.api.vo.myUser.PercentVO">
        select sum(pro_b_direct_trade_amt + pro_b_debit_trade_amt + pro_a_debit_trade_amt + pro_a_direct_trade_amt +
        pro_debit_trade_amt + pro_direct_trade_amt + help_debit_trade_amt + help_direct_trade_amt) as tradeAmt, brand_id
        from `${tableName}`
        <where>
            and user_id = #{userId}
            and total_type = #{totalType}
        </where>
        group by brand_id
    </select>
    <select id="getUserTradeDayTotal" resultType="com.kxs.stat.api.vo.myUser.PercentVO">
        select sum(pro_b_direct_trade_amt + pro_b_debit_trade_amt + pro_a_debit_trade_amt + pro_a_direct_trade_amt +
        pro_debit_trade_amt + pro_direct_trade_amt + help_debit_trade_amt + help_direct_trade_amt) as tradeAmt, brand_id
        from `${tableName}`
        <where>
            and user_id = #{userId}
            and total_type = #{totalType}
            and trade_date = #{tradeDate}
        </where>
        group by brand_id
    </select>

    <select id="getPosTrade" resultType="com.kxs.stat.api.vo.TotalTradeAmtVO">
        select
        sum(pro_b_direct_trade_amt + pro_b_debit_trade_amt + pro_a_debit_trade_amt + pro_a_direct_trade_amt +
        pro_debit_trade_amt + pro_direct_trade_amt + help_debit_trade_amt + help_direct_trade_amt) as posAmt
        from ${tableName}
        <where>
            and user_id = #{userId}
            and total_type = #{totalType}
        <if test="date != null and date.toString().length() == 6">
            and trade_month = #{date}
        </if>
        <if test="date != null and date.toString().length() == 8">
            and trade_date = #{date}
         </if>
        </where>
    </select>
    <select id="getMyPosOrCloudTrade" resultType="com.kxs.stat.api.model.KxsUserTrade">
        select
        sum(pro_b_direct_trade_amt) as pro_b_direct_trade_amt,
        sum(pro_b_debit_trade_amt) as pro_b_debit_trade_amt,
        sum(pro_a_debit_trade_amt) as pro_a_debit_trade_amt,
        sum(pro_a_direct_trade_amt) as pro_a_direct_trade_amt,
        sum(pro_debit_trade_amt) as pro_debit_trade_amt,
        sum(pro_direct_trade_amt) as pro_direct_trade_amt,
        sum(help_debit_trade_amt) as help_debit_trade_amt,
        sum(help_direct_trade_amt) as help_direct_trade_amt
        from ${tableName}
        <where>
            and total_type = #{param.type}
            and user_id = #{userId}
            and trade_type = #{tradeType}
            <if test="date != null and date != '' ">
                and trade_date = #{date}
            </if>
        </where>
    </select>
    <select id="getAllUserTradeSum" resultType="com.kxs.stat.api.model.KxsUserTrade">
        select
        sum(pro_b_debit_cap_num) as pro_b_debit_cap_num,
        sum(pro_b_debit_cap_trade_amt) as pro_b_debit_cap_trade_amt,
        sum(pro_b_direct_trade_amt) as pro_b_direct_trade_amt,
        sum(pro_b_debit_trade_amt) as pro_b_debit_trade_amt,
        sum(pro_a_debit_cap_num) as pro_a_debit_cap_num,
        sum(pro_a_debit_cap_trade_amt) as pro_a_debit_cap_trade_amt,
        sum(pro_debit_cap_num) as pro_debit_cap_num,
        sum(pro_debit_cap_trade_amt) as pro_debit_cap_trade_amt,
        sum(help_debit_cap_num) as help_debit_cap_num,
        sum(help_debit_cap_trade_amt) as help_debit_cap_trade_amt,
        sum(pro_a_debit_trade_amt) as pro_a_debit_trade_amt,
        sum(pro_a_direct_trade_amt) as pro_a_direct_trade_amt,
        sum(pro_debit_trade_amt) as pro_debit_trade_amt,
        sum(pro_direct_trade_amt) as pro_direct_trade_amt,
        sum(help_debit_trade_amt) as help_debit_trade_amt,
        sum(help_direct_trade_amt) as help_direct_trade_amt
        from ${tableName}
        <where>
            and total_type = #{totalType}
            and user_id = #{userId}
            and trade_type = #{tradeType}
            and brand_id = #{brandId}
            <if test="getType == 1">
                and trade_date = #{date}
            </if>
            <if test="getType == 2">
                and trade_month = #{date}
            </if>
        </where>
    </select>
    <select id="getStatUserBrandTradeVOS" resultType="com.kxs.stat.api.vo.statUser.StatUserBrandTradeVO">
        select
            sum(pro_b_direct_trade_amt + pro_b_debit_trade_amt + pro_a_debit_trade_amt + pro_a_direct_trade_amt +
                pro_debit_trade_amt + pro_direct_trade_amt + help_debit_trade_amt + help_direct_trade_amt) as totalAmt,brand_id
        from ${tableName}
        <where>
            and total_type = #{totalType}
            and user_id = #{userId}
            <if test="date != '' and date != null">
                and trade_date = #{date}
            </if>
        </where>
        group by
            brand_id
    </select>
    <select id="getUserTradeMonthTrend" resultType="com.kxs.stat.api.vo.statUser.TrendTimesVO">
        select
        sum(pro_b_direct_trade_amt + pro_b_debit_trade_amt + pro_a_debit_trade_amt + pro_a_direct_trade_amt +
        pro_debit_trade_amt + pro_direct_trade_amt + help_debit_trade_amt + help_direct_trade_amt) as amount, brand_id, trade_month as `time`
        from ${tableName}
        <where>
            and total_type = #{totalType}
            and user_id = #{userId}
        </where>
        group by brand_id, trade_month
    </select>
    <select id="getUserTradeDayTrend" resultType="com.kxs.stat.api.vo.statUser.TrendTimesVO">
        select
        sum(pro_b_direct_trade_amt + pro_b_debit_trade_amt + pro_a_debit_trade_amt + pro_a_direct_trade_amt +
        pro_debit_trade_amt + pro_direct_trade_amt + help_debit_trade_amt + help_direct_trade_amt) as amount, brand_id, trade_date as `time`
        from ${tableName}
        <where>
            and total_type = #{totalType}
            and user_id = #{userId}
            and trade_date = #{tradeDate}
        </where>
        group by brand_id, trade_date
    </select>


    <insert id="insertTempShdTableData">
        INSERT INTO `${tableName}`
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="data.userId != null">
                user_id,
            </if>
            <if test="data.posAmt != null">
                pos_amt,
            </if>
            <if test="data.lkbAmt != null">
                lkb_amt,
            </if>
            <if test="data.lkbActAmt != null">
                lkb_act_amt,
            </if>
            <if test="data.gdAmt != null">
                gd_amt,
            </if>
        </trim>
        <trim prefix="values(" suffix=")" suffixOverrides=",">
            <if test="data.userId != null">
                #{data.userId},
            </if>
            <if test="data.posAmt != null">
                #{data.posAmt},
            </if>
            <if test="data.lkbAmt != null">
                #{data.lkbAmt},
            </if>
            <if test="data.lkbActAmt != null">
                #{data.lkbActAmt},
            </if>
            <if test="data.gdAmt != null">
                #{data.gdAmt},
            </if>
        </trim>
    </insert>
    <update id="updateTempShdTableData">
        UPDATE `${tableName}`
        <set>
            <if test="data.posAmt != null">
                pos_amt = #{data.posAmt},
            </if>
            <if test="data.lkbAmt != null">
                lkb_amt = #{data.lkbAmt},
            </if>
            <if test="data.lkbActAmt != null">
                lkb_act_amt = #{data.lkbActAmt},
            </if>
            <if test="data.gdAmt != null">
                gd_amt = #{data.gdAmt},
            </if>
        </set>
        where user_id = #{data.userId}
    </update>
    <update id="removeTempTable">
        DROP TEMPORARY TABLE IF EXISTS `${tableName}`;
    </update>


</mapper>
