<?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="org.jeecg.ctop.finance.policy.mapper.CwjsPolicyInfoMapper">


    <!-- 查看政策列表 -->
    <select id="selectPolicyInfos" resultType="org.jeecg.ctop.finance.policy.entity.CwjsPolicyInfo">
        select t1.policy_product_name as productName,t2.* ,
               (select u.realname from sys_user u where u.id = t2.sale_id) as saleName,
                sub.company_subject_name CompanySubjectName,
               sub.area_info as areaInfo,
        sub.company_id as companyId
        from ctop_cwjs_policy_product t1
            LEFT JOIN ctop_cwjs_policy_info t2 on t1.policy_id = t2.id
        LEFT JOIN ctop_cwjs_company_subject sub on sub.id = t2.company_subject_id
        where t1.del_flag = 0 and t2.del_flag = 0
        <if test="companyName != null and companyName != ''">and t2.company_name like concat(concat('%',#{companyName}),'%')</if>
        <if test="advertiserName != null and advertiserName != ''">and t2.advertiser_name like concat(concat('%',#{advertiserName}),'%')</if>
        <if test="productName != null and productName != ''">and t1.policy_product_name like concat(concat('%',#{productName}),'%')</if>
        <if test="dianPayCompanyName != null and dianPayCompanyName != ''">and t2.dian_pay_company_name like concat(concat('%',#{dianPayCompanyName}),'%')</if>
        <if test="mediaType != null">and t2.media_type = #{mediaType}</if>
        <if test="rebateType != null">and t2.rebate_type = #{rebateType}</if>
        <if test="advancePay != null">and t2.advance_pay = #{advancePay}</if>
        <if test="customerFlag != null">and t2.customer_flag = #{customerFlag}</if>
        <if test="saleIds != null and saleIds.size() > 0">
            and  (t2.sale_id in
            <foreach item="item" index="index" collection="saleIds" open="(" separator="," close=")">
                #{item}
            </foreach>
            or  t2.create_user_id in
            <foreach item="item" index="index" collection="saleIds" open="(" separator="," close=")">
                #{item}
            </foreach>
                )
        </if>

        <if test="approvedProgress != null and approvedProgress != ''">
          and t2.approved_progress = #{approvedProgress}
        </if>

         <if test="saleId != null and saleId != ''">
          and t2.sale_id = #{saleId}
        </if>

        <if test="companySubjectIds != null and companySubjectIds.size() > 0">
            and (t2.company_subject_id in
            <foreach item="item" index="index" collection="companySubjectIds" open="(" separator="," close=")">
                #{item}
            </foreach>
                )
        </if>


        <if test="promotionType != null">and t2.promotion_type = #{promotionType}</if>

        <if test="statusList != null and statusList.size() > 0">
            and t2.approved_status in
            <foreach item="item" index="index" collection="statusList" open="(" separator="," close=")">
                #{item}
            </foreach>

        </if>


        <if test="startTime != null and startTime != ''">and t2.create_time &gt;= #{startTime}</if>
        <if test="endTime != null and endTime != ''">and t2.create_time &lt;= concat(#{endTime},' 23:59:59')</if>



        <if test="policyStartDate != null and policyStartDate != '' and policyEndDate != null and policyEndDate != ''">
        and ((#{policyStartDate} &lt;= t2.policy_start_date and #{policyEndDate} &gt;= t2.policy_start_date)
        or (#{policyStartDate} &gt;= t2.policy_start_date and #{policyStartDate} &lt;= t2.policy_end_date))
        </if>
        order by t2.create_time DESC, t2.id DESC
    </select>

    <select id="getAllSale" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t1.user_id AS 'userId',
	LEFT (t1.company_name, 2) AS 'companyName',
	t3.role_name AS 'ruleName',
	t1.user_name AS 'userName'
        FROM
            user_company t1
            LEFT JOIN sys_user_role t2
        ON t1.user_id = t2.user_id
            LEFT JOIN sys_role t3 ON t2.role_id = t3.id
        WHERE
            t3.role_code IN (
            'externalSale'
            , 'saleDirector'
            , 'sale'
            , 'saleAssistant'
            , 'salesSupportManager'
            , 'saleAm'
            ,'admin'
            )

    </select>

    <!-- 查询所有销售和 媒介 -->
    <select id="getAllSaleAndMeidaManager" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t1.user_id AS 'id',
	LEFT (t1.company_name, 2) AS 'companyName',
	t3.role_name AS 'ruleName',
	t1.user_name AS 'realname'
        FROM
            user_company t1
            LEFT JOIN sys_user_role t2
        ON t1.user_id = t2.user_id
            LEFT JOIN sys_role t3 ON t2.role_id = t3.id
        WHERE
            t3.role_code IN (
            'externalSale'
            , 'saleDirector'
            , 'sale'
            , 'saleAssistant'
            , 'salesSupportManager'
            , 'saleAm'
            ,'admin'
            ,'meidaManager'
            )

    </select>

    <!-- 查询所有运营 -->
    <select id="getAllOperationUser" resultType="java.util.LinkedHashMap">

        SELECT
            t1.id AS 'id',
            t3.role_name AS 'roleName',
            t1.realname AS 'realName',
            t3.role_code AS roleCode
        FROM
            sys_user t1,
            sys_user_role t2,
            sys_role t3
        WHERE
            t1.id = t2.user_id
          AND t2.role_id = t3.id
          AND t1.STATUS = 1
          AND t1.del_flag = 0
          AND t3.role_name LIKE '%运营%'

    </select>

    <select id="getAllSaleByUserId" resultType="com.alibaba.fastjson.JSONObject">
        SELECT distinct t1.create_user_id AS 'userId',
        (SELECT su.realname from sys_user su where su.id = t1.create_user_id) AS 'userName'
        FROM
            ctop_cwjs_policy_info t1
        WHERE
            t1.approved_progress = #{userId} or t1.create_user_id = #{userId}
    </select>

    <update id="updatePolicyInfoById">
        update ctop_cwjs_policy_info set
            company_name = #{companyName},
             advertiser_name = #{advertiserName},
             media_type = #{mediaType},
             company_subject_id = #{companySubjectId},
             group_abbreviation = #{groupAbbreviation},
             approved_progress = #{approvedProgress},
             advance_pay = #{advancePay},
             customer_flag = #{customerFlag},
             accounting_period = #{accountingPeriod},
             rebate_type = #{rebateType},
             rebate_rate = #{rebateRate},
             is_dian_pay = #{isDianPay},
             dian_pay_company_name = #{dianPayCompanyName},
             dian_pay_company_rebate_rate = #{dianPayCompanyRebateRate},
             promotion_type = #{promotionType},
             policy_start_date = #{policyStartDate},
             policy_end_date = #{policyEndDate},
             remarks = #{remarks},
             sale_id = #{saleId},
             create_user_id = #{createUserId},
             create_user_name = #{createUserName},
             estimated_profit = #{estimatedProfit}
        where id = #{id}
    </update>









    <select id="getPolicyInfo" resultType="org.jeecg.ctop.finance.policy.entity.CwjsPolicyInfo">
        SELECT pi.*
        FROM
            ctop_cwjs_policy_info pi
          where pi.del_flag = 0
        <if test="companyName != null and companyName != ''">
            and pi.company_name = #{companyName}
        </if>
        <if test="advertiserName != null and advertiserName != ''">
            and pi.advertiser_name = #{advertiserName}
        </if>
        <if test="mediaType != null and mediaType != ''">
            and pi.media_type = #{mediaType}
        </if>

        <if test="companySubjectId != null">
            and pi.company_subject_id = #{companySubjectId}
        </if>

        <if test="groupAbbreviation != null and groupAbbreviation != ''">
            and pi.group_abbreviation = #{groupAbbreviation}
        </if>

        <if test="advancePay != null">
            and pi.advance_pay = #{advancePay}
        </if>
        <if test="accountingPeriod != null and accountingPeriod != ''">
            and pi.accounting_period = #{accountingPeriod}
        </if>

        <if test="rebateType != null">
            and pi.rebate_type = #{rebateType}
        </if>

        <if test="rebateRate != null and rebateRate != ''">
            and pi.rebate_rate = #{rebateRate}
        </if>

        <if test="isDianPay != null">
            and pi.is_dian_pay = #{isDianPay}
        </if>

        <if test="dianPayCompanyName != null and dianPayCompanyName != ''">
            and pi.dian_pay_company_name = #{dianPayCompanyName}
        </if>

        <if test="dianPayCompanyRebateRate != null and dianPayCompanyRebateRate != ''">
            and pi.dian_pay_company_rebate_rate = #{dianPayCompanyRebateRate}
        </if>

        <if test="promotionType != null">
            and pi.promotion_type = #{promotionType}
        </if>

        <if test="policyStartDate != null and policyStartDate != ''">
            and pi.policy_start_date = #{policyStartDate}
        </if>

        <if test="policyEndDate != null and policyEndDate != ''">
            and pi.policy_end_date = #{policyEndDate}
        </if>

        <if test="createUserId != null and createUserId != ''">
            and pi.create_user_id = #{createUserId}
        </if>
        limit 1


    </select>




    <!-- 查询用户所属公司及区域 以及 上级领导 和 分公司负责人 -->
    <select id="getUserLeaderAndDirectorByRoleName" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            a.company_id companyId,
            a.company_name companyName,
            a.nick_name areaName,
            a.leader_id leaderId,
            a.leader_name leaderName,
            b.realname directorName,
            b.role_code directorRoleCode,
            b.user_id directorId,
            a.depart_name departName
        FROM
            (
                SELECT
                    c.company_id,
                    c.company_name,
                    c.nick_name,
                    u.leader_id,
                    u.leader_name,
                    d.depart_name
                FROM
                    `user_company` c
                        LEFT JOIN sys_user u on c.user_id = u.id
                        LEFT JOIN sys_depart d on d.org_code = u.org_code
                WHERE
                    user_id = #{userId}
            ) a LEFT JOIN
            (
                SELECT
                    sr.role_name,
                    sr.role_code,
                    sur.user_id ,
                    su.realname,
                    uc.company_id,
                    uc.company_name,
                    uc.nick_name
                FROM
                    sys_user_role sur
                        LEFT JOIN sys_role sr ON sur.role_id = sr.id
                        LEFT JOIN user_company uc on uc.user_id = sur.user_id
                        LEFT JOIN sys_user su on su.id = uc.user_id
                WHERE
                    sr.role_name = #{roleName}
            )b
            on a.company_id = b.company_id
            limit 1
    </select>

    <!-- 查询用户上级 以及所在部门 -->
    <select id="getUserLeaderByUserId" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            u.leader_id leaderId,
            u.leader_name leaderName,
            d.depart_name  departName
        FROM
          sys_user u,
          sys_depart d
          where u.org_code= d.org_code
          and u.id = #{userId}
            limit 1
    </select>



    <!-- 查询 自动转审条件 -->
    <select id="selAutoConveryInfo" resultType="com.alibaba.fastjson.JSONObject">
        SELECT * FROM `ctop_cwjs_auto_convery_condition`
        WHERE 1=1
        and del_flag = 0
        and (accounting_period &lt;= #{accountingPeriod})
        or (
        media_type = #{mediaType}
        and advance_pay = #{advancePay}
        and rebate_type = #{rebateType}
        and rebate_rate &lt; #{rebateRate}
        )
    </select>




    <!-- 查询 政策审核记录 -->
    <select id="getPolicyApprovalLogByPolicyId" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            t.*,
            IFNULL( IF ( t.userid = 'legalAffairs', '法务人员', u.realname ), t.create_user_name ) userName,
            IF
                ( t.userId = 'legalAffairs', '法务', r.role_name ) roleName,
            IF(t.createTime is NULL or t.createTime ='' ,1,10) sort
        FROM
            (
                (SELECT
                    l.id,
                    l.policy_id policyId,
                    l.approval_status approvalStatus,
                    CASE
                        l.approval_status
                        WHEN 1 THEN
                            '审核拒绝'
                        WHEN 2 THEN
                            '转审领导'
                        WHEN 3 THEN
                            '老板审核' ELSE l.approval_content
                        END AS approvalContent,
                    l.create_user_id userId,
                    p.create_user_name,
                    l.create_time createTime
                FROM
                    `jeecg-boot`.`ctop_cwjs_policy_approval_log` l
                        LEFT JOIN `jeecg-boot`.ctop_cwjs_policy_info p ON l.policy_id = p.id
                WHERE
                    l.policy_id = #{policyId})
                UNION
                (SELECT
                    p.id,
                    p.id policyId,
                    '0' approvalStatus,
                    CASE
                        p.approved_status
                        WHEN 1 THEN
                            '待审核'
                        WHEN 2 THEN
                            '待审核'
                        WHEN 3 THEN
                            '审核完成'
                        WHEN 4 THEN
                            '待提交'
                        WHEN 5 THEN
                            '作废'
                        ELSE '-'
                        END AS approvalContent,
                    IF(p.approved_status = 4 or p.approved_status= 5, p.create_user_id, p.approved_progress ) userId,
                    p.create_user_name,
                    '' createTime
                FROM
                    ctop_cwjs_policy_info p
                WHERE
                    p.id = #{policyId})
            ) t
                LEFT JOIN `jeecg-boot`.sys_user u ON t.userid = u.id
                LEFT JOIN `jeecg-boot`.sys_user_role ur ON ur.user_id = u.id
                LEFT JOIN `jeecg-boot`.sys_role r ON r.id = ur.role_id
        ORDER BY
                 sort DESC,createTime

    </select>





    <!-- 查询 用户所属公司主题id -->
    <select id="getCompanySubjectByUserId" resultType="java.lang.Integer">
        SELECT
            s.id subjectId
        FROM
            user_company c
                LEFT JOIN ctop_cwjs_company_subject s ON c.company_id = s.company_id
                AND s.del_flag = 0
        WHERE
            user_id = #{userId}

    </select>


    <!-- 查询 法务人员 -->
    <select id="getLegalAffairsUsers" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            r.role_name,
            r.role_code,
            ur.user_id userId,
            u.realname
        FROM
            sys_role r
                LEFT JOIN sys_user_role ur ON r.id = ur.role_id
                LEFT JOIN sys_user u ON u.id = ur.user_id
        WHERE
            r.role_name = '法务'
          AND u.`status` = 1
          AND u.del_flag = 0
    </select>






    <!-- 政策作废 修改状态-->
    <update id="updatePolicyToVOidById">
        update ctop_cwjs_policy_info set
            approved_status = 5,
            del_reason = #{delReason}
        where id = #{policyId}
    </update>




    <!-- 查询 结算单 没有上传账户图片的账户信息  -->
    <select id="getSettlementNoPictureAccountList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            u.account_id accountId,
            u.auth_name accountName,
            u.user_name operateUserName
        FROM
            ctop_user_allocation u
        WHERE
            u.project_id = #{projectId}
          AND u.account_status = 0
        <if test="mediaIds != null and mediaIds.size() > 0">
            AND u.media_id in
            <foreach item="item" index="index" collection="mediaIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
          AND u.account_id NOT IN (
            SELECT
                c.account_id
            FROM
                `ctop_cwjs_settlement_file_info` c
            WHERE
                c.del_flag = 0
              AND c.product_id = #{productId}
            <if test="mediaIds != null and mediaIds.size() > 0">
                AND c.media_id in
                <foreach item="item" index="index" collection="mediaIds" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
              AND c.upload_years = #{uploadYears}
            GROUP BY
                c.account_id
        )
        ORDER BY
            u.auth_name
    </select>
    <!-- 查询 结算单 没有上传账户图片的账户信息  过滤 消耗为0的账户-->
    <select id="getSettlementNoPictureAccountAgentList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            u.account_id accountId,
            u.auth_name accountName,
            u.user_name operateUserName
        FROM
            ctop_user_allocation u
        WHERE
            u.project_id = #{projectId}
        <if test="accountIds != null and accountIds.size() > 0">
            AND u.account_id in
            <foreach item="item" index="index" collection="accountIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>

          AND u.account_id NOT IN (
            SELECT
                c.account_id
            FROM
                `ctop_cwjs_settlement_file_info` c
            WHERE
                c.del_flag = 0
              AND c.product_id = #{productId}
              AND c.upload_years = DATE_FORMAT(#{endTime}, '%Y-%m')
            GROUP BY
                c.account_id
        )
        ORDER BY
            u.auth_name
    </select>

     <select id="queryInfo" 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>











</mapper>