<?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.dayou.mapper.FinanceClaimMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.dayou.entity.FinanceClaim">
        <result column="id" property="id" />
        <result column="created" property="created" />
        <result column="deleted" property="deleted" />
        <result column="modified" property="modified" />
        <result column="real_fund_id" property="realFundId" />
        <result column="production_fund_id" property="productionFundId" />
        <result column="claim_id" property="claimId" />
        <result column="claim_datetime" property="claimDatetime" />
        <result column="claim_amount" property="claimAmount" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id,
        created,
        deleted,
        modified,
        real_fund_id, production_fund_id, claim_id, claim_datetime, claim_amount
    </sql>

    <select id="getList" parameterType="com.dayou.entity.FinanceClaim" resultType="com.dayou.vo.FinanceClaimVO">
        SELECT
            fc.id,
            fc.real_fund_id,
            ( CASE orf.business_type WHEN 'MAJOR_BUSINESS' THEN '大中型业务' WHEN 'PERSONAL_BUSINESS' THEN '个贷业务' ELSE '-' END ) AS businessType,
            orf.should_amount,
            fc.claim_amount,
            u.NAME AS claimName,
            fc.created,
            orf.order_id,
            orf.order_name,
            orf.remark
        FROM
            finance_claim fc
                LEFT JOIN order_fund orf ON fc.order_fund_id = orf.id
                LEFT JOIN user u ON u.id = fc.claim_id
        WHERE
            fc.real_fund_id = #{realFundId}
          AND fc.deleted = 0
        ORDER BY
            fc.created DESC
    </select>

    <select id="getTotalClaimAmountByProductionFundId" parameterType="java.lang.Long" resultType="java.math.BigDecimal">
        select ifnull(sum(claim_amount),0) from finance_claim where production_fund_id = #{productionFundId} and deleted = 0
    </select>

    <select id="residueAmount" parameterType="java.lang.Long" resultType="java.math.BigDecimal">
        SELECT amount -( SELECT IFNULL( sum( claim_amount ), 0 ) FROM `finance_claim` WHERE deleted = 0 AND real_fund_id = #{financeFundId} ) AS residueAmount
        FROM
            finance_real_fund
        WHERE
            id = #{financeFundId} and deleted = 0
    </select>

    <sql id="majorStatQuery">
        SELECT
        f.*,
        mp.NAME,
        m.financial,
        m.members,
        mp.if_save_file,
        mp.evaluate_amount,
        u.NAME AS clientManager,
        u1.NAME AS principal,
        cc1.NAME AS clientName,
        cc2.NAME AS clientSubName,
        m.bailor,
        frf.pay_datetime,
        frf.payer,
        u2.NAME AS claimUser,
        d.name as evaluateDepartment,
        (select GROUP_CONCAT(name SEPARATOR '、') from department where id in (select department_id from post where id in (select post_id from user_post where user_id = u.id))) as marketDepartment
        FROM
        (
        SELECT
        of.order_id,
        pf.real_amount AS claimAmount,
        pf.production_no as reportNo,
        pf.production_type,
        pf.created as claimDatetime,
        pf.claim_id,
        of.business_id
        FROM
        ( SELECT id, real_amount, production_no, production_type, order_fund_id, claim_id, created FROM production_fund WHERE business_type = "MAJOR_BUSINESS" AND deleted = 0 ) pf
        LEFT JOIN order_fund of ON pf.order_fund_id = of.id
        WHERE
        of.business_type = 'MAJOR_BUSINESS'
        AND of.deleted = 0
        AND pf.real_amount != 0
        ) f
        LEFT JOIN major m ON f.business_id = m.id
        LEFT JOIN USER u ON u.id = m.client_manager_id
        LEFT JOIN USER u1 ON u1.id = m.principal_id
        LEFT JOIN department d ON d.id = m.department_id
        LEFT JOIN customer_company cc1 ON cc1.id = m.clientele_id
        LEFT JOIN customer_company cc2 ON cc2.id = m.clientele_sub_id
        LEFT JOIN customer_linkman cl1 ON cl1.id = m.clientele_contact_id
        LEFT JOIN finance_claim fc ON fc.id = f.claim_id
        LEFT JOIN finance_real_fund frf ON frf.id = fc.real_fund_id
        LEFT JOIN ( SELECT major_id, report_no, evaluate_amount, if_save_file, NAME FROM major_production WHERE deleted = 0 ) mp ON ( mp.major_id = m.id AND mp.report_no = f.reportNo )
        LEFT JOIN USER u2 ON u2.id = fc.claim_id
        WHERE
        m.deleted = 0
        <if test="majorStat!=null and majorStat.orderId!=null and majorStat.orderId!='' ">
            and f.order_id like concat ('%',#{majorStat.orderId},'%')
        </if>
        <if test="majorStat!=null and majorStat.reportNo!=null and majorStat.reportNo!='' ">
            and f.reportNo like concat ('%',#{majorStat.reportNo},'%')
        </if>
        <if test="majorStat!=null and majorStat.startDate!=null and majorStat.startDate!='' ">
            and f.claimDatetime &gt;= #{majorStat.startDate}
        </if>
        <if test="majorStat!=null and majorStat.endDate!=null and majorStat.endDate!='' ">
            and f.claimDatetime &lt;= #{majorStat.endDate}
        </if>
        <if test="majorStat!=null and majorStat.principal!=null and majorStat.principal!='' ">
            and u1.NAME like concat ('%',#{majorStat.principal},'%')
        </if>
        <if test="majorStat!=null and majorStat.claimAmount!=null and majorStat.claimAmount!='' ">
            and f.claimAmount = #{majorStat.claimAmount}
        </if>
        <if test="majorStat!=null and majorStat.ifSaveFile!=null ">
            and mp.if_save_file = #{majorStat.ifSaveFile}
        </if>
        <if test="majorStat!=null and majorStat.financial!=null ">
            and m.financial = #{majorStat.financial}
        </if>
        <if test="majorStat!=null and majorStat.claimUser!=null and majorStat.claimUser!='' ">
            and u2.NAME like concat ('%',#{majorStat.claimUser},'%')
        </if>
        ORDER BY
        f.claimDatetime DESC
    </sql>
    <select id="majorStat" parameterType="com.dayou.vo.RealFundMajorStatVO" resultType="com.dayou.vo.RealFundMajorStatVO">
        <include refid="majorStatQuery" />
    </select>

    <select id="majorStatExport" parameterType="com.dayou.vo.RealFundMajorStatVO" resultType="com.dayou.vo.RealFundMajorStatVO">
        <include refid="majorStatQuery" />
    </select>
</mapper>
