<?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.FinancePaymentApplicationMapper">

    <select id="getAdvertisers" resultType="com.alibaba.fastjson.JSONObject">
        SELECT group_concat(DISTINCT t2.id) as 'advertiserIds', t2.name as 'advertiserName'
        FROM ctop_advertiser t2
        LEFT JOIN
        `ctop_cwjs_policy_info` t1 ON t1.advertiser_name = t2.name
        WHERE t2.id is not null
        <if test="mediaType != null and mediaType != '' ">
            AND media_type = #{mediaType}
        </if>
        GROUP BY t2.name
    </select>

    <select id="getProductByAdvertiserIds" resultType="com.alibaba.fastjson.JSONObject">
        SELECT id as 'productId', product_name as 'productName'
        FROM ctop_product
        WHERE advertiser_id in
        <foreach item="advertiserId" collection="advertiserIds" open="(" close=")" separator=",">
            #{advertiserId}
        </foreach>
    </select>

    <select id="queryPoolInfo" resultType="cn.com.ctop.finance.payment.entity.FinancePaymentApplication">
        SELECT id,
               advertiser_id,
               advertiser_name,
               product_id,
               product_name,
               payment_media,
               audit_status,
               payment_type,
               cash_amount,
               display_amount,
               rebate_rate,
               rebate_type,
               payment_port,
               applicanter_id,
               applicanter,
               revieweder_id,
               revieweder,
               approval_content,
               temporary_passage,
               temporary_advances,
               files
        FROM finance_payment_application
        WHERE advertiser_id = #{advertiserId}
          AND product_id = #{productId}
          AND payment_media = #{type}
        order by update_time desc
    </select>

    <select id="queryInfo" resultType="cn.com.ctop.finance.payment.entity.FinancePaymentApplication">
        SELECT id,
               advertiser_id,
               advertiser_name,
               product_id,
               product_name,
               payment_media,
               audit_status,
               payment_type,
               cash_amount,
               display_amount,
               rebate_rate,
               rebate_type,
               payment_port,
               applicanter_id,
               applicanter,
               revieweder_id,
               revieweder,
               approval_content,
               temporary_passage,
               temporary_advances,
               files
        FROM finance_payment_application
        WHERE advertiser_id = #{advertiserId}
          AND product_id = #{productId}
          AND payment_media = #{type}
          AND (audit_status = 3 or audit_status = 4)
        order by update_time desc Limit 1;
    </select>

    <select id="queryCwjsPolicyInfo" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t1.id as 'id', t1.advertiser_name as 'advertiserName', t1.media_type as 'mediaType', t2.policy_product_name as 'policyProductName', approved_status as 'approvedStatus', advance_pay as 'advancePay', rebate_type as 'rebateType', rebate_rate as 'rebateRate', policy_start_date as 'policyStartDate', policy_end_date as 'policyEndDate'
        FROM `ctop_cwjs_policy_info` t1
                 LEFT JOIN ctop_cwjs_policy_product t2 ON t1.id = t2.policy_id
        WHERE t1.advertiser_name = #{advertiserName}
          AND t1.media_type = #{type}
          AND t2.policy_product_name = #{productName}
        ORDER BY policy_start_date desc, policy_end_date desc LIMIT 1
    </select>

    <select id="queryAuditProcess" resultType="com.alibaba.fastjson.JSONObject">
        SELECT *
        FROM (
                 SELECT t1.audit_status as 'auditStatus', t1.revieweder_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_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.application_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_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>

</mapper>