<?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.yd.dal.mapper.commission.CommissionEGoldenMapper">
    <resultMap id="base_result_mapegolden" type="com.yd.dal.entity.commission.CommissionEGolden">
        <result column="SAL_MST_MOBILE" property="login"/>
        <result column="SAL_MST_NAME" property="name"/>
        <result column="INS_MST_ID" property="policyNo"/>
        <result column="INS_MST_TOTAL_PREIUM" property="premium"/>
        <result column="INS001_FYCTOTAL" property="fyc"/>
        <result column="INS_MST_SALES" property="handBy"/>
        <result column="INS_MST_SEND_DATE" property="policyIssueDate"/>
        <result column="INS_BEN_NAME" property="policyHolder"/>
    </resultMap>
    <select id="listSalesCommissionfromEGolden" parameterType="java.lang.String" resultMap="base_result_mapegolden">
        SELECT DISTINCT
        sales.SAL_MST_MOBILE  -- 业务员手机
        ,sales.SAL_MST_NAME  -- 业务员
        ,policy.INS_MST_ID -- 保单号
        ,policy.INS_MST_TOTAL_PREIUM -- 保费
        ,policy.INS001_FYCTOTAL -- FYC
        ,policy.INS_MST_SALES -- 经手人
        ,policy.INS_MST_SEND_DATE -- 出单日
        ,p_insed.INS_BEN_NAME -- 被保人受益人
        FROM sal001 sales LEFT JOIN ins003 p_sales on sales.SAL_MST_ID = p_sales.FK_SAL_MST_ID
        LEFT JOIN ins001 policy on policy.INS_MST_ID = p_sales.FK_INS_MST_ID
        LEFT JOIN ins017 p_insed on policy.INS_MST_ID = p_insed.FK_INS_MST_ID
        WHERE policy.INS_MST_REPEAL != 1 AND sales.SAL_MST_MOBILE = #{login}
    </select>

    <select id="getStatementDataByPage" resultType="com.yd.api.agms.vo.statistics.statementData">
        select if(#{type,jdbcType=INTEGER} = 1,receive.mon024_302,receive.MON024_301) as inCommissionTime, -- 预计来佣年月
        if(#{type,jdbcType=INTEGER} = 1,pay.MON025_302,pay.mon025_301) as outCommissionTime,
        policy.INS_MST_POLICY_NUM as policyNo, -- 保单号
        branch.SPC_AREA_NAME as insurerBranch, -- 分公司
        dept.SPC_BUS_NAME as dept, -- 营业部
        divl.SPC_DIV_NAME as subordinate, -- 体系id
        practitioner.SAL_MST_NAME as practitioner, --
        practitioner.SAL_MST_EPID as practitionerIdEG, -- 业务员
        practitioner.SAL_MST_OFFICIAL_TITLE as title, -- 职阶
        pay.MON025_403 as titleRate, -- 职阶率
        if((pay.MON025_108 = "S02" or pay.MON025_108 = "S01"),convert(policy.INS_MST_TOTAL_PREIUM,decimal(10,2)),0) as premium, -- 保费
        if((receive.MON024_303=1 and (pay.MON025_108 = "S02" or pay.MON025_108 = "S01")),convert(policy.INS001_FYCTOTAL,decimal(10,2)),0) as FYC, -- fyc
        if((receive.MON024_303 != 1 and (pay.MON025_108 = "S02" or pay.MON025_108 = "S01")),convert((policy.INS_MST_TOTAL_PREIUM * pay.MON025_402)/100,decimal(10,2)) ,0) as RYC, -- fyc
        if((pay.MON025_108 = "S02" or pay.MON025_108 = "S01"),convert(receive.incommission,decimal(10,2)),0) as rightReceiveCommission, -- 来佣金额
        pay.paycommission as rightPayCommission, -- 应发佣金
        commissionType.SRA_BS_NAME as commissionType-- 佣奖类型
        from ins001 policy right join (select MON024_002,MON024_301,MON024_302,MON024_303,sum(MON024_404) as incommission from MON024 where mon024_104 != "3" and mon024_104 != "4"  group by MON024_002 ,MON024_301) receive on policy.INS_MST_ID = receive.MON024_002
        right join (select MON025_002,MON025_301,MON025_302,MON025_303,MON025_005,MON025_006,MON025_008,MON025_007,MON025_403,MON025_402,sum(MON025_401) as premium,sum(MON025_405) as paycommission,MON025_108 from MON025 where mon025_109 != "5" and mon025_109 != "6" group by mon025_002,mon025_301,MON025_007) pay on policy.INS_MST_ID = pay.MON025_002 and receive.MON024_303 = pay.MON025_303
        left join sal001 practitioner on practitioner.SAL_MST_ID = pay.MON025_007
        left join spc002 dept on dept.SPC_BUS_ID = practitioner.FK_SPC_BUS_ID
        left join spc001 branch on branch.SPC_AREA_ID = dept.FK_AREA_ID
        left join spc004 divl on divl.SPC_DIV_ID = practitioner.FK_SPC_DIV_ID
        left join sra009 commissionType on commissionType.SRA_BS_CODE = pay.MON025_108
        where 1=1
<!--        <if test="isStatement == 0">-->
            <choose>
                <when test="type == 1">
                    <choose>
                        <when test="inCommissionStartTime == null and inCommissionEndTime == null and outCommissionStartTime == null and outCommissionEndTime == null">
                            and receive.mon024_302 is not null or pay.MON025_302 is not null
                        </when>
                        <otherwise>
                            <if test="inCommissionStartTime != null">
                                and CONVERT(receive.mon024_302,signed) &gt;= CONVERT(#{inCommissionStartTime,jdbcType=VARCHAR},signed)
                            </if>
                            <if test="inCommissionEndTime != null">
                                and CONVERT(receive.mon024_302,signed) &lt;= CONVERT(#{inCommissionEndTime,jdbcType=VARCHAR},signed)
                            </if>
                            <if test="outCommissionStartTime != null">
                                and CONVERT(pay.MON025_302,signed) &gt;= CONVERT(#{outCommissionStartTime,jdbcType=VARCHAR},signed)
                            </if>
                            <if test="outCommissionEndTime != null">
                                and CONVERT(pay.MON025_302,signed) &lt;= CONVERT(#{outCommissionEndTime,jdbcType=VARCHAR},signed)
                            </if>
                        </otherwise>
                    </choose>
                </when>
                <otherwise>
                    <if test="inCommissionStartTime != null">
                        and CONVERT(receive.mon024_301,signed) &gt;= CONVERT(#{inCommissionStartTime,jdbcType=VARCHAR},signed)
                    </if>
                    <if test="inCommissionEndTime != null">
                        and CONVERT(receive.mon024_301,signed) &lt;= CONVERT(#{inCommissionEndTime,jdbcType=VARCHAR},signed)
                    </if>
                    <if test="outCommissionStartTime != null">
                        and CONVERT(pay.MON025_301,signed) &gt;= CONVERT(#{outCommissionStartTime,jdbcType=VARCHAR},signed)
                    </if>
                    <if test="outCommissionEndTime != null">
                        and CONVERT(pay.MON025_301,signed) &lt;= CONVERT(#{outCommissionEndTime,jdbcType=VARCHAR},signed)
                    </if>
                </otherwise>
            </choose>
            <choose>
                <when test="subordinateId != null">
                    and pay.MON025_006 = #{subordinateId,jdbcType=VARCHAR}
                </when>
                <when test="deptId != null ">
                    and pay.MON025_005 = #{deptId,jdbcType=VARCHAR}
                </when>
                <when test="insurerBranchId != null ">
                    and branch.SPC_AREA_ID = #{insurerBranchId,jdbcType=VARCHAR}
                </when>
                <otherwise>

                </otherwise>
            </choose>
        order by policy.INS_MST_POLICY_NUM,receive.mon024_301 asc
<!--        </if>-->
    </select>
</mapper>