<?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.bonus.canteen.core.account.mapper.AccTradeMapper">
    <resultMap type="com.bonus.canteen.core.account.domain.AccountTrade" id="AccTradeResult">
        <result property="tradeId"    column="trade_id"    />
        <result property="tradeTime"    column="trade_time"    />
        <result property="userId"    column="user_id"    />
        <result property="deptId"    column="dept_id"    />
        <result property="canteenId"    column="canteen_id"    />
        <result property="tradeType"    column="trade_type"    />
        <result property="actualAmount"    column="actual_amount"    />
        <result property="amount"    column="amount"    />
        <result property="walletBalTotal"    column="wallet_bal_total"    />
        <result property="accountAllBal"    column="account_all_bal"    />
        <result property="payChannel"    column="pay_channel"    />
        <result property="payType"    column="pay_type"    />
        <result property="payState"    column="pay_state"    />
        <result property="tradeState"    column="trade_state"    />
        <result property="thirdTradeNo"    column="third_trade_no"    />
        <result property="machineSn"    column="machine_sn"    />
        <result property="batchNum"    column="batch_num"    />
        <result property="orderNo"    column="order_no"    />
        <result property="originTradeId"    column="origin_trade_id"    />
        <result property="subTimeRuleId"    column="sub_time_rule_id"    />
        <result property="manageCost"    column="manage_cost"    />
        <result property="withdrawSource"    column="withdraw_source"    />
        <result property="rechargeSource"    column="recharge_source"    />
        <result property="machineType"    column="machine_type"    />
        <result property="failReason"    column="fail_reason"    />
        <result property="rechargeOperate"    column="recharge_operate"    />
        <result property="psnType"    column="psn_type"    />
        <result property="operateSource"    column="operate_source"    />
        <result property="batchImportId"    column="batch_import_id"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
    </resultMap>

    <sql id="selectAccTradeVo">
        select trade_id, trade_time, user_id, dept_id, trade_type, actual_amount, amount, wallet_bal_total, 
               account_all_bal, pay_channel, pay_type, pay_state, trade_state, third_trade_no, machine_sn,
               batch_num, order_no, origin_trade_id, sub_time_rule_id, manage_cost, withdraw_source, 
               recharge_source, canteen_id, machine_type, fail_reason, recharge_operate, psn_type, 
               operate_source, batch_import_id, create_by, create_time, update_by, update_time 
        from account_trade
    </sql>

    <select id="selectAccTradeList" parameterType="com.bonus.canteen.core.account.domain.AccountTrade" resultMap="AccTradeResult">
        <include refid="selectAccTradeVo"/>
        <where>  
            <if test="tradeTime != null "> and trade_time = #{tradeTime}</if>
            <if test="userId != null "> and user_id = #{userId}</if>
            <if test="deptId != null "> and dept_id = #{deptId}</if>
            <if test="tradeType != null "> and trade_type = #{tradeType}</if>
            <if test="actualAmount != null "> and actual_amount = #{actualAmount}</if>
            <if test="amount != null "> and amount = #{amount}</if>
            <if test="walletBalTotal != null "> and wallet_bal_total = #{walletBalTotal}</if>
            <if test="accountAllBal != null "> and account_all_bal = #{accountAllBal}</if>
            <if test="payChannel != null "> and pay_channel = #{payChannel}</if>
            <if test="payType != null "> and pay_type = #{payType}</if>
            <if test="payState != null "> and pay_state = #{payState}</if>
            <if test="tradeState != null "> and trade_state = #{tradeState}</if>
            <if test="thirdTradeNo != null  and thirdTradeNo != ''"> and third_trade_no = #{thirdTradeNo}</if>
            <if test="machineSn != null  and machineSn != ''"> and machine_sn = #{machineSn}</if>
            <if test="batchNum != null  and batchNum != ''"> and batch_num = #{batchNum}</if>
            <if test="orderNo != null "> and order_no = #{orderNo}</if>
            <if test="originTradeId != null "> and origin_trade_id = #{originTradeId}</if>
            <if test="subTimeRuleId != null "> and sub_time_rule_id = #{subTimeRuleId}</if>
            <if test="manageCost != null "> and manage_cost = #{manageCost}</if>
            <if test="withdrawSource != null "> and withdraw_source = #{withdrawSource}</if>
            <if test="rechargeSource != null "> and recharge_source = #{rechargeSource}</if>
            <if test="canteenId != null "> and canteen_id = #{canteenId}</if>
            <if test="machineType != null "> and machine_type = #{machineType}</if>
            <if test="failReason != null  and failReason != ''"> and fail_reason = #{failReason}</if>
            <if test="rechargeOperate != null "> and recharge_operate = #{rechargeOperate}</if>
            <if test="psnType != null "> and psn_type = #{psnType}</if>
            <if test="operateSource != null "> and operate_source = #{operateSource}</if>
            <if test="batchImportId != null "> and batch_import_id = #{batchImportId}</if>
        </where>
    </select>
    
    <select id="selectAccTradeByTradeId" parameterType="Long" resultMap="AccTradeResult">
        <include refid="selectAccTradeVo"/>
        where trade_id = #{tradeId}
    </select>

    <select id="selectAccTradeByOrderNo" parameterType="String" resultMap="AccTradeResult">
        <include refid="selectAccTradeVo"/>
        where order_no = #{orderNo}
    </select>
        
    <insert id="insertAccTrade" parameterType="com.bonus.canteen.core.account.domain.AccountTrade" keyProperty="tradeId" useGeneratedKeys="true">
        insert into account_trade
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="tradeId != null">trade_id,</if>
            <if test="tradeTime != null">trade_time,</if>
            <if test="userId != null">user_id,</if>
            <if test="deptId != null">dept_id,</if>
            <if test="tradeType != null">trade_type,</if>
            <if test="actualAmount != null">actual_amount,</if>
            <if test="amount != null">amount,</if>
            <if test="walletBalTotal != null">wallet_bal_total,</if>
            <if test="accountAllBal != null">account_all_bal,</if>
            <if test="payChannel != null">pay_channel,</if>
            <if test="payType != null">pay_type,</if>
            <if test="payState != null">pay_state,</if>
            <if test="tradeState != null">trade_state,</if>
            <if test="thirdTradeNo != null">third_trade_no,</if>
            <if test="machineSn != null">machine_sn,</if>
            <if test="batchNum != null">batch_num,</if>
            <if test="orderNo != null">order_no,</if>
            <if test="originTradeId != null">origin_trade_id,</if>
            <if test="subTimeRuleId != null">sub_time_rule_id,</if>
            <if test="manageCost != null">manage_cost,</if>
            <if test="withdrawSource != null">withdraw_source,</if>
            <if test="rechargeSource != null">recharge_source,</if>
            <if test="canteenId != null">canteen_id,</if>
            <if test="machineType != null">machine_type,</if>
            <if test="failReason != null">fail_reason,</if>
            <if test="rechargeOperate != null">recharge_operate,</if>
            <if test="psnType != null">psn_type,</if>
            <if test="operateSource != null">operate_source,</if>
            <if test="batchImportId != null">batch_import_id,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="tradeId != null">#{tradeId},</if>
            <if test="tradeTime != null">#{tradeTime},</if>
            <if test="userId != null">#{userId},</if>
            <if test="deptId != null">#{deptId},</if>
            <if test="tradeType != null">#{tradeType},</if>
            <if test="actualAmount != null">#{actualAmount},</if>
            <if test="amount != null">#{amount},</if>
            <if test="walletBalTotal != null">#{walletBalTotal},</if>
            <if test="accountAllBal != null">#{accountAllBal},</if>
            <if test="payChannel != null">#{payChannel},</if>
            <if test="payType != null">#{payType},</if>
            <if test="payState != null">#{payState},</if>
            <if test="tradeState != null">#{tradeState},</if>
            <if test="thirdTradeNo != null">#{thirdTradeNo},</if>
            <if test="machineSn != null">#{machineSn},</if>
            <if test="batchNum != null">#{batchNum},</if>
            <if test="orderNo != null">#{orderNo},</if>
            <if test="originTradeId != null">#{originTradeId},</if>
            <if test="subTimeRuleId != null">#{subTimeRuleId},</if>
            <if test="manageCost != null">#{manageCost},</if>
            <if test="withdrawSource != null">#{withdrawSource},</if>
            <if test="rechargeSource != null">#{rechargeSource},</if>
            <if test="canteenId != null">#{canteenId},</if>
            <if test="machineType != null">#{machineType},</if>
            <if test="failReason != null">#{failReason},</if>
            <if test="rechargeOperate != null">#{rechargeOperate},</if>
            <if test="psnType != null">#{psnType},</if>
            <if test="operateSource != null">#{operateSource},</if>
            <if test="batchImportId != null">#{batchImportId},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
         </trim>
    </insert>

    <update id="updateAccTrade" parameterType="com.bonus.canteen.core.account.domain.AccountTrade">
        update account_trade
        <trim prefix="SET" suffixOverrides=",">
            <if test="tradeTime != null">trade_time = #{tradeTime},</if>
            <if test="userId != null">user_id = #{userId},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="tradeType != null">trade_type = #{tradeType},</if>
            <if test="actualAmount != null">actual_amount = #{actualAmount},</if>
            <if test="amount != null">amount = #{amount},</if>
            <if test="walletBalTotal != null">wallet_bal_total = #{walletBalTotal},</if>
            <if test="accountAllBal != null">account_all_bal = #{accountAllBal},</if>
            <if test="payChannel != null">pay_channel = #{payChannel},</if>
            <if test="payType != null">pay_type = #{payType},</if>
            <if test="payState != null">pay_state = #{payState},</if>
            <if test="tradeState != null">trade_state = #{tradeState},</if>
            <if test="thirdTradeNo != null">third_trade_no = #{thirdTradeNo},</if>
            <if test="machineSn != null">machine_sn = #{machineSn},</if>
            <if test="batchNum != null">batch_num = #{batchNum},</if>
            <if test="orderNo != null">order_no = #{orderNo},</if>
            <if test="originTradeId != null">origin_trade_id = #{originTradeId},</if>
            <if test="subTimeRuleId != null">sub_time_rule_id = #{subTimeRuleId},</if>
            <if test="manageCost != null">manage_cost = #{manageCost},</if>
            <if test="withdrawSource != null">withdraw_source = #{withdrawSource},</if>
            <if test="rechargeSource != null">recharge_source = #{rechargeSource},</if>
            <if test="canteenId != null">canteen_id = #{canteenId},</if>
            <if test="machineType != null">machine_type = #{machineType},</if>
            <if test="failReason != null">fail_reason = #{failReason},</if>
            <if test="rechargeOperate != null">recharge_operate = #{rechargeOperate},</if>
            <if test="psnType != null">psn_type = #{psnType},</if>
            <if test="operateSource != null">operate_source = #{operateSource},</if>
            <if test="batchImportId != null">batch_import_id = #{batchImportId},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
        </trim>
        where trade_id = #{tradeId}
    </update>

    <update id="updateAccTradeVoByOrderNo" parameterType="com.bonus.canteen.core.account.domain.vo.AccTradeVo">
        update account_trade
        set
            third_trade_no = #{thirdTradeNo},pay_state = #{payState},
            trade_state = #{tradeState},fail_reason = #{failReason},update_time = #{updateTime}
        where order_no = #{orderNo}
    </update>

    <delete id="deleteAccTradeByTradeId" parameterType="Long">
        delete from account_trade where trade_id = #{tradeId}
    </delete>

    <delete id="deleteAccTradeByTradeIds" parameterType="String">
        delete from account_trade where trade_id in
        <foreach item="tradeId" collection="array" open="(" separator="," close=")">
            #{tradeId}
        </foreach>
    </delete>

    <select id="queryAccOperationList" resultType="com.bonus.canteen.core.account.domain.vo.AccOperationListVO">
        SELECT ate.trade_time,
               ate.user_id,
               su.nick_name,
               su.phonenumber as phoneNumber,
               sd.dept_name,
               su.user_type,
               ate.create_by,
               ate.trade_id,
               ate.trade_state,
               ate.pay_state,
               ate.amount,
               ate.fail_reason,
               atwd.wallet_bal
            FROM
        account_trade ate
                LEFT JOIN sys_user su ON su.user_id = ate.user_id
                LEFT JOIN sys_dept sd ON sd.dept_id = su.dept_id
                LEFT JOIN account_trade_wallet_detail atwd ON ate.trade_id = atwd.trade_id
        <where>
            <if test="param.tradeType != null">
                ate.trade_type = #{param.tradeType}
            </if>
            <if test="param.payState != null">
                and ate.pay_state = #{param.payState}
            </if>
            <if test="param.walletType != null">
                and atwd.wallet_type = #{param.walletType}
            </if>
            <if test="param.startDateTime != null">
                and ate.trade_time <![CDATA[ >= ]]> #{param.startDateTime}
            </if>
            <if test="param.endDateTime != null">
                and ate.trade_time <![CDATA[ <= ]]> #{param.endDateTime}
            </if>
            <if test="param.createBy != null and param.createBy != ''">
                and ate.create_by like  concat('%', #{param.createBy}, '%')
            </if>
            <if test="param.deptIds != null and param.deptIds.size() > 0">
                and ate.dept_id in
                <foreach collection="param.deptIds" item="deptId" separator="," open="(" close=")">
                    #{deptId}
                </foreach>
            </if>
            <if test="param.searchValue != null and param.searchValue != ''">
                and (su.nick_name like CONCAT('%',#{param.searchValue},'%')
                or su.phonenumber = #{encryptedSearchValue}
                or su.user_id like CONCAT('%',#{param.searchValue},'%')
                )
            </if>
            <if test="param.userId != null">
                and ate.user_id = #{param.userId}
            </if>
        </where>
        order by ate.trade_id desc
    </select>

    <select id="queryTradeAndWallerInfoByOrderNo" resultType="com.bonus.canteen.core.account.domain.bo.TradeAndWalletInfo">
        select
            ate.trade_id,
            ate.actual_amount,
            ate.amount,
            ate.pay_state,
            atwd.wallet_type

        from
            account_trade ate
                inner join account_trade_wallet_detail atwd on
                ate.trade_id = atwd.trade_id
        where
            ate.pay_state = 3
            and ate.order_no = #{orderNo}
            and ate.trade_type = #{accTradeType}
    </select>

    <select id="queryAccConsumeDetail" resultType="com.bonus.canteen.core.account.domain.vo.AccConsumeDetailVO">
        select
            at2.trade_id,
            su.user_id,
            su.nick_name,
            sd.dept_full_name,
            su.user_type,
            at2.trade_time as pay_time,
            oi.order_time,
            at2.trade_type,
            atwd.wallet_type,
            atwd.amount,
            atwd.wallet_bal,
            oi.source_type
        from
            order_info oi
                inner join account_trade at2 on
                oi.order_id = at2.order_no
                inner join account_trade_wallet_detail atwd on
                atwd.trade_id = at2.trade_id
                inner join sys_user su on
                oi.user_id = su.user_id
                inner join sys_dept sd on
                su.dept_id = sd.dept_id
        where
            atwd.trade_type in (110, 120, 130)
          and oi.order_state in (1, 2, 3)
          and oi.pay_state = 3
        <if test="param.tradeType != null">
            and at2.trade_type = #{param.tradeType}
        </if>
        <if test="param.walletType != null">
            and atwd.wallet_type = #{param.walletType}
        </if>
        <if test="param.userType != null">
            and su.user_type = #{param.userType}
        </if>
        <if test="param.deptIdList != null and param.deptIdList.size() > 0">
            and sd.dept_id in
            <foreach collection="param.deptIdList" item="deptId" separator="," open="(" close=")">
                #{deptId}
            </foreach>
        </if>
        <if test="param.startDateTime != null">
            and at2.trade_time <![CDATA[ >= ]]> #{param.startDateTime}
        </if>
        <if test="param.endDateTime != null">
            and at2.trade_time <![CDATA[ <= ]]> #{param.endDateTime}
        </if>
        <if test="param.searchValue != null and param.searchValue != ''">
            and (su.nick_name like CONCAT('%',#{param.searchValue},'%')
            or su.phonenumber = #{encryptedSearchValue}
            or su.user_id like CONCAT('%',#{param.searchValue},'%')
            )
        </if>
        order by
        at2.trade_id desc
    </select>

</mapper>