<?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.mmc.iuav.user.dao.wallet.PayWalletDao">
    <insert id="insertPayWalletInfo">
        insert into pay_wallet
        (user_account_id, cash_amt, cash_paid, cash_freeze, salary_amt,
         salary_paid, salary_freeze, salary_wdl)
        values (#{userAccountId}, #{cashAmt}, #{cashPaid}, #{cashFreeze}, #{salaryAmt}, #{salaryPaid}, #{salaryFreeze},
                #{salaryWdl})
    </insert>
    <insert id="insertPayLogDO">
        insert into pay_log (user_account_id,
                             pay_no,
                             pay_method,
                             cash_amt_paid,
                             salary_amt_paid,
                             pay_time,
                             remark,
                             operate_user)
        values (#{userAccountId}, #{payNo}, #{payMethod}, #{cashAmtPaid}, #{salaryAmtPaid}, NOW(), #{remark},
                #{operateUser})
    </insert>
    <insert id="insertWithdrawalLog">
        insert into withdrawal_log(user_account_id,
                                   xz_withdrawal_apply_id,
                                   pay_no,
                                   pay_method,
                                   pay_time,
                                   salary_freeze,
                                   salary_paid)
        values (#{userAccountId}, #{xzWithdrawalApplyId}, #{payNo}, #{payMethod}, #{payTime}, #{salaryFreeze},
                #{salaryPaid})
    </insert>
    <update id="updatePayWallet">
        update pay_wallet
        <set>
            <if test="pid != null">
                pid = #{pid} + 1,
            </if>
            <if test="cashAmt != null">
                cash_amt = #{cashAmt},
            </if>
            <if test="cashFreeze != null">
                cash_freeze = #{cashFreeze},
            </if>
            <if test="salaryAmt != null">
                salary_amt = #{salaryAmt},
            </if>
            <if test="salaryFreeze != null">
                salary_freeze = #{salaryFreeze},
            </if>
            <if test="wdlFreeze != null">
                wdl_freeze = #{wdlFreeze},
            </if>
            <if test="salaryWdl != null">
                salary_wdl =#{salaryWdl}
            </if>
        </set>
        where user_account_id = #{userAccountId} and pid = #{pid}
    </update>
    <update id="updateWithdrawalApply">
        update xz_withdrawal_apply
        <set>
            <if test="applyStatus != null">
                apply_status = #{applyStatus},
            </if>
            <if test="approveUser != null">
                approve_user = #{approveUser},
            </if>
            <if test="approveTime != null">
                approve_time = #{approveTime},
            </if>
            <if test="remark != null and remark != ''">
                remark = #{remark},
            </if>
            <if test="chargeAmount != null">
                charge_amount = #{chargeAmount},
            </if>
            <if test="tradeStatus != null">
                trade_status = #{tradeStatus},
            </if>
            <if test="tradeResult != null">
                trade_result = #{tradeResult},
            </if>
            <if test="remarks != null">
                remarks = #{remarks},
            </if>
            <if test="taxFundId != null">
                tax_fund_id = #{taxFundId},
            </if>
            <if test="wechatAppId !=null">
                wechat_app_id = #{wechatAppId},
            </if>
            <if test="tradeTime !=null">
                trade_time = #{tradeTime},
            </if>
            <if test="frontLogNo !=null">
                front_log_no = #{frontLogNo},
            </if>
            <if test="tradeFailCode !=null">
                trade_fail_code = #{tradeFailCode},
            </if>
            <if test="submitStatus !=null">
                submit_status = #{submitStatus},
            </if>
            <if test="errorCode != null">
                error_code = #{errorCode},
            </if>
            <if test="errorMessage != null">
                error_message =#{errorMessage}
            </if>
        </set>
        <where>
            <if test="id != null">
                id = #{id}
            </if>
            <if test="outerTradeNo != null">
                outer_trade_no = #{outerTradeNo}
            </if>
        </where>
    </update>

    <select id="getPayWalletByUser" resultType="com.mmc.iuav.user.entity.wallet.PayWalletDO">
        select id,
               pid,
               user_account_id,
               cash_amt,
               cash_paid,
               cash_freeze,
               salary_amt,
               salary_paid,
               salary_freeze,
               wdl_freeze,
               salary_wdl,
               create_time
        from pay_wallet
        where user_account_id = #{userAccountId}
    </select>
    <select id="getPayLog" resultType="com.mmc.iuav.user.entity.wallet.PayLogDO">
        SELECT pl.id,
        pl.user_account_id,
        pl.pay_no,
        pl.pay_method,
        pl.cash_amt_paid,
        pl.salary_amt_paid,
        pl.pay_time,
        pl.remark,
        pl.operate_user,
        pl.create_time,
        wd.`name`
        FROM pay_log pl
        INNER JOIN wallet_dictionary wd ON pl.pay_method = wd.`code`
        <where>
            pl.user_account_id = #{userAccountId}
            <if test="startTime != null and startTime != '' ">
                and pl.pay_time &gt;= STR_TO_DATE(#{startTime},'%Y-%m-%d
                %H:%i:%s')
            </if>
            <if test="endTime != null and endTime != '' ">
                and pl.pay_time &lt;= STR_TO_DATE(#{endTime},'%Y-%m-%d
                %H:%i:%s')
            </if>
        </where>
        ORDER BY pl.create_time DESC
        limit #{pageNo}
        , #{pageSize}
    </select>
    <select id="countPayLog" resultType="java.lang.Integer">
        select count(*)
        from pay_log pl
        <where>
            pl.user_account_id = #{userAccountId}
            <if test="startTime != null and startTime != '' ">
                and pl.pay_time &gt;= STR_TO_DATE(#{startTime},'%Y-%m-%d
                %H:%i:%s')
            </if>
            <if test="endTime != null and endTime != '' ">
                and pl.pay_time &lt;= STR_TO_DATE(#{endTime},'%Y-%m-%d
                %H:%i:%s')
            </if>
        </where>
    </select>
    <select id="getPayLogById" resultType="com.mmc.iuav.user.entity.wallet.PayLogDO">
        SELECT pl.id,
               pl.user_account_id,
               pl.pay_no,
               pl.pay_method,
               pl.cash_amt_paid,
               pl.salary_amt_paid,
               pl.pay_time,
               pl.remark,
               pl.operate_user,
               pl.create_time,
               wd.`name`
        FROM pay_log pl
                 INNER JOIN wallet_dictionary wd ON pl.pay_method = wd.`code`
        WHERE pl.id = #{id}
    </select>
    <select id="getWithdrawalListByUserAccountId"
            resultType="com.mmc.iuav.user.entity.XzWithdrawalApplyDO">
        select id,
        apply_status,
        user_account_id,
        bank_name,
        bank_remo,
        emp_num,
        emp_name,
        emp_phone,
        license_id,
        license_type,
        `month`,
        notify_url,
        outer_trade_no,
        pay_account,
        pay_amount,
        position_name,
        remarks,
        settle_type,
        tax_fund_id,
        wechat_app_id,
        trade_status,
        trade_time,
        front_log_no,
        trade_fail_code,
        trade_result,
        submit_status,
        error_code,
        error_message,
        charge_amount,
        pay_time,
        create_time
        from xz_withdrawal_apply
        <where>
            user_account_id = #{userAccountId}
            <if test="startTime != null and startTime != '' ">
                and create_time &gt;= STR_TO_DATE(#{startTime},'%Y-%m-%d %H:%i:%s')
            </if>
            <if test="endTime != null and endTime != '' ">
                and create_time &lt;= STR_TO_DATE(#{endTime},'%Y-%m-%d %H:%i:%s')
            </if>
        </where>
        order by create_time DESC
        limit #{pageNo},#{pageSize}
    </select>
    <select id="getWithdrawalApplyByIdOrOuterTradeNo" resultType="com.mmc.iuav.user.entity.XzWithdrawalApplyDO">
        select id,
        apply_status,
        user_account_id,
        bank_name,
        bank_remo,
        emp_num,
        emp_name,
        emp_phone,
        license_id,
        license_type,
        `month`,
        notify_url,
        outer_trade_no,
        pay_account,
        pay_amount,
        position_name,
        remarks,
        settle_type,
        tax_fund_id,
        wechat_app_id,
        trade_status,
        trade_time,
        front_log_no,
        trade_fail_code,
        trade_result,
        submit_status,
        error_code,
        error_message,
        charge_amount,
        pay_time,
        create_time
        from xz_withdrawal_apply
        <where>
            <if test="id != null">
                id = #{id}
            </if>
            <if test="outerTradeNo != null and outerTradeNo != ''">
                outer_trade_no = #{outerTradeNo}
            </if>
        </where>
    </select>
    <select id="listPayWithdrawalLog" resultType="com.mmc.iuav.user.entity.wallet.WithdrawalLogDO">
        select id,
               user_account_id,
               xz_withdrawal_apply_id,
               pay_no,
               pay_method,
               pay_time,
               salary_freeze,
               create_time
        from withdrawal_log
        where xz_withdrawal_apply_id = #{id}
        order by pay_time asc
    </select>
    <select id="countPayWithdrawalLog" resultType="java.lang.Integer">
        select count(*)
        from xz_withdrawal_apply
        <where>
            user_account_id = #{userAccountId}
            <if test="startTime != null and startTime != '' ">
                and create_time &gt;= STR_TO_DATE(#{startTime},'%Y-%m-%d
                %H:%i:%s')
            </if>
            <if test="endTime != null and endTime != '' ">
                and create_time &lt;= STR_TO_DATE(#{endTime},'%Y-%m-%d
                %H:%i:%s')
            </if>
        </where>
    </select>
    <select id="countWithdrawalApply" resultType="java.lang.Integer">
        select count(*)
        from xz_withdrawal_apply
        <where>
            1 = 1
            <if test="tradeStatus != null">
                and trade_status = #{tradeStatus}
            </if>
            <if test="startTime != null and startTime != '' ">
                and create_time &gt;=
                STR_TO_DATE(#{startTime},'%Y-%m-%d %H:%i:%s')
            </if>
            <if test="endTime != null and endTime != '' ">
                and create_time &lt;= STR_TO_DATE(#{endTime},'%Y-%m-%d
                %H:%i:%s')
            </if>
            <if test="settleType != null and settleType != '' ">
                and settle_type = #{settleType}
            </if>
            <if test="keyword != null and keyword != '' ">
                and (
                emp_name like CONCAT('%',#{keyword},'%')
                or
                emp_phone like CONCAT('%',#{keyword},'%')
                or pay_account like
                CONCAT('%',#{keyword},'%')
                or outer_trade_no like
                CONCAT('%',#{keyword},'%')
                )
            </if>
        </where>
    </select>
    <select id="listPageWithdrawalApply" resultType="com.mmc.iuav.user.entity.XzWithdrawalApplyDO">
        select id,
        user_account_id,
        apply_status,
        bank_name,
        bank_remo,
        emp_num,
        emp_name,
        emp_phone,
        license_id,
        license_type,
        `month`,
        notify_url,
        outer_trade_no,
        pay_account,
        pay_amount,
        position_name,
        remarks,
        settle_type,
        tax_fund_id,
        wechat_app_id,
        trade_status,
        trade_time,
        front_log_no,
        trade_fail_code,
        trade_result,
        submit_status,
        error_code,
        error_message,
        charge_amount,
        create_time,
        pay_time,
        remark
        from xz_withdrawal_apply
        <where>
            1 = 1
            <if test="tradeStatus != null">
                and trade_status = #{tradeStatus}
            </if>
            <if test="startTime != null and startTime != '' ">
                and create_time &gt;=
                STR_TO_DATE(#{startTime},'%Y-%m-%d %H:%i:%s')
            </if>
            <if test="endTime != null and endTime != '' ">
                and create_time &lt;= STR_TO_DATE(#{endTime},'%Y-%m-%d
                %H:%i:%s')
            </if>
            <if test="settleType != null and settleType != '' ">
                and settle_type = #{settleType}
            </if>
            <if test="keyword != null and keyword != '' ">
                and (
                emp_name like CONCAT('%',#{keyword},'%')
                or
                emp_phone like CONCAT('%',#{keyword},'%')
                or pay_account like
                CONCAT('%',#{keyword},'%')
                or outer_trade_no like
                CONCAT('%',#{keyword},'%')
                )
            </if>
        </where>
        order by create_time desc
        limit #{pageNo},#{pageSize}
    </select>
    <select id="getTotalWithdrawAmount" resultType="java.math.BigDecimal">
        select sum(salary_wdl)
        from pay_wallet
    </select>
</mapper>
