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

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.dayou.entity.CommissionDeclare">
        <result column="id" property="id" />
        <result column="deleted" property="deleted" />
        <result column="created" property="created" />
        <result column="modified" property="modified" />
        <result column="business_type" property="businessType" />
        <result column="commission_rate_id" property="commissionRateId" />
        <result column="business_id" property="businessId" />
        <result column="production_id" property="productionId" />
        <result column="declare_user_id" property="declareUserId" />
        <result column="declare_result" property="declareResult" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id,
        deleted,
        created,
        modified,
        business_type, commission_rate_id, business_id, production_id, declare_user_id, declare_result
    </sql>

    <select id="majorMarketDeclarePage" parameterType="com.dayou.vo.CommissionDeclareVO" resultType="com.dayou.vo.CommissionDeclareVO">
        select bus.*,dd.name as commissionType from (SELECT
                                       IFNULL(cd.id,(SELECT id from commission_declare where deleted = 0 and business_id= major.businessId and production_id is null) ) id,
                                       IFNULL(cd.commission_rate_id,(SELECT commission_rate_id from commission_declare where deleted = 0 and business_id= major.businessId and production_id is null) ) commission_rate_id,
                                        IFNULL(cd.declare_result,(SELECT declare_result from commission_declare where deleted = 0 and business_id= major.businessId and production_id is null) ) declare_result,
                                       major.*
                                   FROM
                                       (
                                           SELECT
                                               m.id AS businessId,
                                               mp.id AS productionId,
                                               ifnull( mp.id, m.id ) AS mId,
                                               m.NAME AS orderName,
                                               m.order_id,
                                               m.business_object_type,
                                               m.bailor_contact_name,
                                               m.bailor_contact_tel,
                                               m.bailor,
                                               m.created AS orderCreated,
                                               u.NAME AS clientManager,
                                               u1.NAME AS principal,
                                               cc1.NAME AS clienteleName,
                                               cc2.NAME AS clienteleSubName,
                                               mp.NAME,
                                               mp.report_no ,
                                               mp.created
                                           FROM
                                               major m
                                                   LEFT JOIN ( SELECT id, NAME, major_id, report_no,created FROM major_production WHERE deleted = 0 ) mp ON m.id = mp.major_id
                                                   LEFT JOIN USER u ON u.id = m.client_manager_id
                                                   LEFT JOIN USER u1 ON u1.id = m.principal_id
                                                   LEFT JOIN customer_company cc1 ON cc1.id = m.clientele_id
                                                   LEFT JOIN customer_company cc2 ON cc2.id = m.clientele_sub_id
                                           WHERE
                                               m.deleted = 0
                                             AND m.client_manager_id = #{marketUserId}
                                       ) major
                                           LEFT JOIN ( SELECT id, ifnull( production_id, business_id ) AS xId, commission_rate_id,declare_result FROM commission_declare WHERE deleted = 0 ) cd ON major.mId = cd.xId) bus left join business_commission_rate bcr on
            bus.commission_rate_id = bcr.id left join dict_data dd on dd.id = bcr.business_cate_id
                            <where>
                                <if test="declare!=null and declare.orderId!=null and declare.orderId!=''">
                                        and bus.order_id like concat ('%',#{declare.orderId},'%')
                                </if>
                                <if test="declare!=null and declare.name!=null and declare.name!=''">
                                    and (bus.orderName like concat ('%',#{declare.name},'%') or bus.name like concat ('%',#{declare.name},'%') )
                                </if>
                                <if test="declare!=null and declare.reportNo!=null and declare.reportNo!=''">
                                    and bus.report_no like concat ('%',#{declare.reportNo},'%')
                                </if>
                                <if test="declare!=null and declare.commissionRateId!=null ">
                                    and bus.commission_rate_id = #{declare.commissionRateId}
                                </if>
                                <if test="declare!=null and declare.declareType!=null and declare.declareType==true">
                                    and bus.productionId is not null
                                </if>
                                <if test="declare!=null and declare.declareType!=null and declare.declareType==false">
                                    and bus.productionId is null
                                </if>
                                <if test="declare!=null and declare.declareResult!=null and declare.declareResult=='待申报'">
                                    and bus.declare_result is null
                                </if>
                                <if test="declare!=null and declare.declareResult!=null and declare.declareResult=='审核中'">
                                    and bus.declare_result = #{declare.declareResult}
                                </if>
                                <if test="declare!=null and declare.declareResult!=null and declare.declareResult=='审核通过'">
                                    and bus.declare_result = #{declare.declareResult}
                                </if>
                                <if test="declare!=null and declare.declareResult!=null and declare.declareResult=='审核拒绝'">
                                    and bus.declare_result = #{declare.declareResult}
                                </if>
                            </where>
        order by bus.created DESC ,bus.orderCreated DESC
    </select>


    <select id="majorEvaluateDeclarePage" parameterType="com.dayou.vo.CommissionDeclareVO" resultType="com.dayou.vo.CommissionDeclareVO">
        select bus.*,dd.name as commissionType from (SELECT
        IFNULL(cd.id,(SELECT id from commission_declare where deleted = 0 and business_id= major.businessId and production_id is null) ) id,
        IFNULL(cd.commission_rate_id,(SELECT commission_rate_id from commission_declare where deleted = 0 and business_id= major.businessId and production_id is null) ) commission_rate_id,
        IFNULL(cd.declare_result,(SELECT declare_result from commission_declare where deleted = 0 and business_id= major.businessId and production_id is null) ) declare_result,
        major.*
        FROM
        (
        SELECT
        m.id AS businessId,
        mp.id AS productionId,
        ifnull( mp.id, m.id ) AS mId,
        m.NAME AS orderName,
        m.order_id,
        m.business_object_type,
        m.bailor_contact_name,
        m.bailor_contact_tel,
        m.bailor,
        m.created AS orderCreated,
        u.NAME AS clientManager,
        u1.NAME AS principal,
        cc1.NAME AS clienteleName,
        cc2.NAME AS clienteleSubName,
        mp.NAME,
        mp.report_no ,
        mp.created
        FROM
        major m
        LEFT JOIN ( SELECT id, NAME, major_id, report_no,created FROM major_production WHERE deleted = 0 ) mp ON m.id = mp.major_id
        LEFT JOIN USER u ON u.id = m.client_manager_id
        LEFT JOIN USER u1 ON u1.id = m.principal_id
        LEFT JOIN customer_company cc1 ON cc1.id = m.clientele_id
        LEFT JOIN customer_company cc2 ON cc2.id = m.clientele_sub_id
        WHERE
        m.deleted = 0
        AND m.principal_id = #{evaluateUserId}
        ) major
        LEFT JOIN ( SELECT id, ifnull( production_id, business_id ) AS xId, commission_rate_id,declare_result FROM commission_declare WHERE deleted = 0 ) cd ON major.mId = cd.xId) bus left join business_commission_rate bcr on
        bus.commission_rate_id = bcr.id left join dict_data dd on dd.id = bcr.business_cate_id
        <where>
            <if test="declare!=null and declare.orderId!=null and declare.orderId!=''">
                and bus.order_id like concat ('%',#{declare.orderId},'%')
            </if>
            <if test="declare!=null and declare.name!=null and declare.name!=''">
                and (bus.orderName like concat ('%',#{declare.name},'%') or bus.name like concat ('%',#{declare.name},'%') )
            </if>
            <if test="declare!=null and declare.reportNo!=null and declare.reportNo!=''">
                and bus.report_no like concat ('%',#{declare.reportNo},'%')
            </if>
            <if test="declare!=null and declare.commissionRateId!=null ">
                and bus.commission_rate_id = #{declare.commissionRateId}
            </if>
            <if test="declare!=null and declare.declareType!=null and declare.declareType==true">
                and bus.productionId is not null
            </if>
            <if test="declare!=null and declare.declareType!=null and declare.declareType==false">
                and bus.productionId is null
            </if>
            <if test="declare!=null and declare.declareResult!=null and declare.declareResult=='待申报'">
                and bus.declare_result is null
            </if>
            <if test="declare!=null and declare.declareResult!=null and declare.declareResult=='审核中'">
                and bus.declare_result = #{declare.declareResult}
            </if>
            <if test="declare!=null and declare.declareResult!=null and declare.declareResult=='审核通过'">
                and bus.declare_result = #{declare.declareResult}
            </if>
            <if test="declare!=null and declare.declareResult!=null and declare.declareResult=='审核拒绝'">
                and bus.declare_result = #{declare.declareResult}
            </if>
        </where>
        order by bus.created DESC ,bus.orderCreated DESC
    </select>

    <select id="detail" parameterType="java.lang.Long" resultType="com.dayou.vo.CommissionDeclareVO">
        SELECT
            cd.id,
            cd.commission_rate_id,
            cd.business_id,
            cd.production_id,
            cd.created,
            cd.declare_result,
            dd.NAME AS businessCate,
            u.NAME AS declareUser,
            bcr.business_cate_id,
            bcr.business_type,
            cd.created
        FROM
            commission_declare cd
                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 user u ON u.id = cd.declare_user_id
            where cd.deleted = 0 and bcr.deleted = 0 and cd.id =#{id}
    </select>
</mapper>
