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

    <select id="myMajorIncome" parameterType="com.dayou.vo.UserCommissionProdVO" resultType="com.dayou.vo.UserCommissionProdVO">
        SELECT
            mpa.id,
            fc.claim_amount as realAmount,
            mp.report_no,
            IF
            ( fc.claim_datetime > mp.delivery_date, fc.claim_datetime, mp.delivery_date ) AS settleDate,
            mp.NAME,
            mp.created,
            dd.NAME AS businessCate,
            bcr.min_ratio AS ratio,
            mpa.user_id,
            mpa.ratio AS allotRatio,
            ( fc.claim_amount * ( bcr.min_ratio / 100 )* mpa.ratio ) AS commissionAmount,
            ( fc.claim_amount * ( bcr.min_ratio / 100 ) ) AS totalAmount,
            u.name as userName,
            (case mpa.user_type when 'EVALUATE' then '评估人员' else '市场人员' end) as  userType
        FROM
            (
                SELECT
                    claim_amount,
                    order_fund_id,
                    production_fund_id,
                    claim_datetime
                FROM
                    finance_claim
                WHERE
                    deleted = 0
                  AND production_fund_id IS NOT NULL

            ) fc
                INNER JOIN ( SELECT id,order_fund_id, business_id, production_no FROM production_fund WHERE business_type = 'MAJOR_BUSINESS' AND deleted = 0 ) pf ON ( pf.id = fc.production_fund_id AND pf.order_fund_id = fc.order_fund_id )
                INNER JOIN (
                SELECT
                    id,
                    major_id,
                    NAME,
                    report_no,
                    production,
                    evaluate_amount,
                    delivery_date,
                    fund.real_amount,
                    created
                FROM
                    major_production mpr
                        INNER JOIN ( SELECT production_no, real_amount, production_type FROM production_fund WHERE deleted = 0 AND business_type = 'MAJOR_BUSINESS' ) fund ON ( fund.production_no = mpr.report_no AND fund.production_type = mpr.production )
                WHERE
                    fund.real_amount IS NOT NULL
                  AND mpr.delivery_date IS NOT NULL
            ) mp ON mp.report_no = pf.production_no
                LEFT JOIN major m ON m.id = mp.major_id
                INNER JOIN ( SELECT id,business_id, production_id, business_type ,commission_rate_id FROM commission_declare WHERE  deleted = 0 AND declare_result = '审核通过' ) cd ON (
                cd.business_id = m.id
                    AND ( cd.production_id = mp.id ))
                LEFT JOIN business_commission_rate bcr ON bcr.id = cd.commission_rate_id
                LEFT JOIN dict_data dd ON dd.id = bcr.business_cate_id
                LEFT JOIN ( SELECT id,major_id,user_type, major_production_id, user_id, ratio ,declare_id FROM major_production_allot WHERE deleted = 0 ) mpa
                          ON ( mpa.major_id = m.id and mpa.major_production_id = mp.id
                              and  if(mpa.user_type='MARKET','COMMISSION_DECLARE_MAJOR_MARKET','COMMISSION_DECLARE_MAJOR_EVALUATE') = cd.business_type
                              )

                left join user u on u.id = mpa.user_id

        WHERE
            u.id= #{vo.userId}
        <if test="vo!=null and vo.startDate!=null and vo.startDate!='' ">
            AND (select IF
            ( fc.claim_datetime > mp.delivery_date, fc.claim_datetime, mp.delivery_date )) &gt;= #{vo.startDate}
        </if>
        <if test="vo!=null and vo.endDate!=null and vo.endDate!='' ">
            AND (select IF
            ( fc.claim_datetime > mp.delivery_date, fc.claim_datetime, mp.delivery_date )) &lt;= #{vo.endDate}
        </if>
        order by settleDate DESC
    </select>

    <!--资产我的提成-->
    <sql id="myAssetsIncomeSql">
        SELECT bpd.id,
               a.name                                                                           AS name,
               fc.claim_amount                                                                  AS realAmount,
               ap.production_no                                                                 AS reportNo,
               IF(fc.claim_datetime > ap.save_file_date, fc.claim_datetime, ap.save_file_date)  AS settleDate,
               ap.created,
               dd.name                                                                          AS businessCate,
               IF(cd.ratio IS NULL, bcr.min_ratio, cd.ratio)                                    AS ratio,
               bpd.member_id,
               bpd.performance_distribution                                                     AS allotRatio,
               IF(bpd.user_type = '评估人员',
                  IF(cd.defined_low_limit_amount &lt; fc.claim_amount,
                     fc.claim_amount *  (cd.ratio / 100) * (bpd.performance_distribution / 100),
                     cd.defined_low_limit_amount * (cd.ratio / 100) * (bpd.performance_distribution / 100)
                  )
                   ,fc.claim_amount *  (bcr.min_ratio / 100) * (bpd.performance_distribution / 100)
               ) AS commissionAmount,
               IF(bpd.user_type = '评估人员',
                  IF(cd.defined_low_limit_amount &lt; fc.claim_amount,
                     fc.claim_amount *  (cd.ratio / 100),
                     cd.defined_low_limit_amount * (cd.ratio / 100)
                  )
                   ,fc.claim_amount *  (bcr.min_ratio / 100)
               ) AS totalAmount,
               u.name                                                                           AS userName,
               bpd.user_type                                                                    AS userType
        FROM (SELECT claim_amount,
                     order_fund_id,
                     production_fund_id,
                     claim_datetime
              FROM finance_claim
              WHERE deleted = 0
                AND production_fund_id IS NOT NULL
                AND claim_datetime &gt;= #{vo.startDate}
                AND claim_datetime &lt;= #{vo.endDate}
              UNION ALL
              SELECT allot_amount AS claim_amount,
                     order_fund_id,
                     production_fund_id,
                     created      AS claim_datetime
              FROM order_fund_allot
              WHERE deleted = 0
                AND created &gt;= #{vo.startDate}
                AND created &lt;= #{vo.endDate}) fc
                 INNER JOIN (SELECT id, order_fund_id, business_id, production_no
                             FROM production_fund
                             WHERE business_type = 'ASSET_BUSINESS'
                               AND deleted = 0) pf
                            ON (pf.id = fc.production_fund_id AND pf.order_fund_id = fc.order_fund_id)
                 INNER JOIN (SELECT id, business_id, production_no, production_type, save_file_date, delivery, created
                             FROM assets_production
                             WHERE deleted = 0) ap ON ap.production_no = pf.production_no
                 LEFT JOIN assets a ON a.id = ap.business_id
                 INNER JOIN (SELECT id, business_id, production_id, business_type, commission_rate_id, ratio, defined_low_limit_amount
                             FROM commission_declare
                             WHERE deleted = 0
                               AND declare_result = '审核通过') cd ON (
            cd.business_id = a.id
                AND (cd.production_id IS NULL OR cd.production_id = ap.id))
                 LEFT JOIN business_commission_rate bcr ON bcr.id = cd.commission_rate_id
                 LEFT JOIN dict_data dd ON dd.id = bcr.business_cate_id
                 LEFT JOIN (SELECT id,
                                   business_id,
                                   user_type,
                                   report_no,
                                   member_id,
                                   performance_distribution,
                                   declare_id
                            FROM business_performance_distribution
                            WHERE deleted = 0) bpd
                           ON (bpd.business_id = a.id AND (bpd.report_no IS NULL OR bpd.report_no = ap.production_no)
                               AND IF(bpd.user_type = '市场人员',
                                      'COMMISSION_DECLARE_ASSET_MARKET',
                                      'COMMISSION_DECLARE_ASSET_EVALUATE'
                                   ) = cd.business_type
                                  )
                               AND IF(bpd.user_type = '市场人员', ap.delivery = TRUE, ap.save_file_date IS NOT NULL)
                 LEFT JOIN user u ON u.id = bpd.member_id
        WHERE u.id = #{vo.userId}
        ORDER BY settleDate DESC
    </sql>

    <!--资产业务我的提成-->
    <select id="myAssetsIncome" resultType="com.dayou.vo.UserCommissionProdVO">
        <include refid="myAssetsIncomeSql" />
    </select>

    <!--资产业务我的提成导出excel-->
    <select id="myAssetsIncomeExport" resultType="com.dayou.vo.UserCommissionProdVO">
        <include refid="myAssetsIncomeSql" />
    </select>

</mapper>
