<?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="cn.com.ctop.finance.payment.mapper.FinancePaymentRechargeApplicationMapper">

    <select id="queryAuditProcess" resultType="com.alibaba.fastjson.JSONObject">
        SELECT *
        FROM (
                 SELECT t1.audit_status as 'auditStatus', t1.recharge_id as 'userId', t2.realname as 'userName', t4.role_name as 'roleName', t1.approval_content as 'approvalContent', t1.files as 'files', t1.create_time as 'time'
                 FROM finance_payment_recharge_application_record t1
                          LEFT JOIN sys_user t2 ON t1.revieweder_id = t2.id
                          LEFT JOIN sys_user_role t3 ON t2.id = t3.user_id
                          LEFT JOIN sys_role t4 ON t3.role_id = t4.id
                 WHERE t1.recharge_id = #{id}
                 UNION ALL
                 SELECT t1.audit_status,
                        IFNULL(t1.revieweder_id, '-'),
                        IFNULL(t1.revieweder, '-'),
                        CASE
                            WHEN revieweder_id like'financ%' THEN '财务审核'
                            WHEN revieweder_id ='meidaManager' THEN '媒介'
                            ELSE
                                t4.role_name
                            END,
                        t1.approval_content,
                        t1.files,
                        t1.update_time
                 FROM finance_payment_recharge_application t1
                          LEFT JOIN sys_user_role t3 ON t1.revieweder_id = t3.user_id
                          LEFT JOIN sys_role t4 ON t3.role_id = t4.id
                 WHERE t1.id = #{id}
             ) t
        ORDER BY t.time
    </select>


    <select id="queryPageList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t1.id,
        t1.advertiser_id as 'advertiserId',
        t1.advertiser_name as 'advertiserName',
        t1.product_id as 'productId',
        GROUP_CONCAT(DISTINCT t1.product_id) as 'productIds',
        GROUP_CONCAT(DISTINCT t1.product_name) as 'productName',
        t1.project_id as 'projectId',
        t1.project_name as 'projectName',
        GROUP_CONCAT(t2.account_id) as 'accountIds',
        SUM(t2.cash_amount) as 'cashAmounts',
        SUM(t2.received_amount) as 'receivedAmounts',
        t1.applicanter_id as 'applicanterId',
        t1.applicanter as 'applicanter',
        t1.create_time as 'createTime',
        t1.files as 'files',
        t1.approval_content as 'approvalContent',
        t1.audit_status as 'auditStatus',
        t1.payment_media as 'paymentMedia',
        t1.recharge_type as 'rechargeType',
        t2.rebate_rate as 'rebateRate',
        t1.revieweder_id as 'reviewederId',
        t1.revieweder as 'revieweder',
        t1.amends,
        t1.remarks,
        t1.payment_port as 'paymentPort',
        CASE t1.payment_port
        WHEN 1 THEN '汇创'
        WHEN 2 THEN '骄阳'
        WHEN 3 THEN '江西五七'
        WHEN 4 THEN '北京五六七'
        WHEN 5 THEN '骄阳&amp;汇创'
        end as 'portName'
        FROM finance_payment_recharge_application t1
        LEFT JOIN finance_payment_recharge_account t2 ON t1.id = t2.recharge_id
        <where>
            <if test="advertiserName != null and advertiserName != '' ">
                and t1.advertiser_name like concat('%',#{advertiserName},'%')
            </if>
            <if test="productName != null and productName != '' ">
                and t1.product_name like concat('%',#{productName},'%')
            </if>
            <if test="projectName != null and projectName != '' ">
                and t1.project_name like concat('%',#{projectName},'%')
            </if>
            <if test="auditStatus != null and auditStatus != '' ">
                and t1.audit_status=#{auditStatus}
            </if>
            <if test="applicanterId != null and applicanterId != '' ">
                and t1.applicanter_id=#{applicanterId}
            </if>
            <if test="userIds != null and userIds.size()>0">
                and t1.applicanter_id IN
                <foreach collection="userIds" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test="paymentMedia != null and paymentMedia != '' ">
                and t1.payment_media=#{paymentMedia}
            </if>
            <if test="rechargeType != null and rechargeType != '' ">
                and t1.recharge_type=#{rechargeType}
            </if>
            <if test="amends != null and amends != '' ">
                and t1.amends=#{amends}
            </if>
            <if test="accountId != null and accountId != '' ">
                and t2.account_id=#{accountId}
            </if>
            <if test="startDate != null and startDate != '' ">
                and t1.create_time >= #{startDate}
                and t1.create_time &lt;= concat(#{endDate},' 23:59:59')
            </if>
        </where>
        GROUP BY t1.id
        order by t1.create_time desc
    </select>

    <select id="queryAccountList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t1.id,
        t1.advertiser_id as 'advertiserId',
        t1.advertiser_name as 'advertiserName',
        t2.product_id as 'productId',
        t2.product_name as 'productName',
        IFNULL(t2.project_id,'-') AS 'projectId',
        IFNULL(t5.project_name,'-') AS 'projectName',
        t2.account_id as 'accountId',
        t2.account_name as 'accountName',
        t2.cash_amount as 'cashAmounts',
        t2.received_amount as 'receivedAmounts',
        t2.rebate_type as 'rebateType',
        t1.applicanter_id as 'applicanterId',
        t1.applicanter as 'applicanter',
        DATE_FORMAT(t1.create_time,'%Y-%m-%d %H:%i:%s') as 'createTime',
        t1.files as 'files',
        t1.approval_content as 'approvalContent',
        t1.audit_status as 'auditStatus',
        t1.payment_media as 'paymentMedia',
        t1.recharge_type as 'rechargeType',
        t2.rebate_rate as 'rebateRate',
        t1.revieweder_id as 'reviewederId',
        t1.revieweder as 'revieweder',
        t1.amends,
        t1.remarks,
        t4.agent_name as 'agentName'
        FROM finance_payment_recharge_application t1
        LEFT JOIN finance_payment_recharge_account t2 ON t1.id = t2.recharge_id
        LEFT JOIN ctop_oauth_token t3 ON t2.account_id = t3.account_id
        LEFT JOIN ctop_oauth_config t4 ON t3.app_id = t4.app_id
        LEFT JOIN ctop_project t5 ON t2.project_id = t5.id
        <where>
            <if test="advertiserName != null and advertiserName != '' ">
                and t1.advertiser_name like concat('%',#{advertiserName},'%')
            </if>
            <if test="productName != null and productName != '' ">
                and t1.product_name like concat('%',#{productName},'%')
            </if>
            <if test="projectName != null and projectName != '' ">
                and t1.project_name like concat('%',#{projectName},'%')
            </if>
            <if test="auditStatus != null and auditStatus != '' ">
                and t1.audit_status=#{auditStatus}
            </if>
            <if test="applicanterId != null and applicanterId != '' ">
                and t1.applicanter_id=#{applicanterId}
            </if>
            <if test="userIds != null and userIds.size()>0">
                and t1.applicanter_id IN
                <foreach collection="userIds" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test="paymentMedia != null and paymentMedia != '' ">
                and t1.payment_media=#{paymentMedia}
            </if>
            <if test="rechargeType != null and rechargeType != '' ">
                and t1.recharge_type=#{rechargeType}
            </if>
            <if test="amends != null and amends != '' ">
                and t1.amends=#{amends}
            </if>
            <if test="accountId != null and accountId != '' ">
                and t2.account_id=#{accountId}
            </if>
            <if test="startDate != null and startDate != '' ">
                and t1.create_time >= #{startDate}
                and t1.create_time &lt;= concat(#{endDate},' 23:59:59')
            </if>
        </where>
        GROUP BY t2.id
        order by t1.create_time desc
    </select>

</mapper>