<?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.bonus.gs.sub.evaluate.evaluate.dao.OutsourcerEvaluateDao">
    <insert id="insertEvaluateData" useGeneratedKeys="true" keyProperty="id">
        insert into pj_evaluate_details(evaluate_id,sub_evaluate_id,post_id,person_id,json,title_list,is_project,is_active)
        values(#{evaluateId},#{templateId},#{deptId},#{userId},#{jsonData},#{titleFiled},#{isProject},'1')
    </insert>
    <update id="updateEvaluateData">
        update pj_evaluate_details set json = #{jsonData},title_list = #{titleFiled},is_project = #{isProject}
        where evaluate_id = #{evaluateId} and sub_evaluate_id = #{templateId} and post_id = #{deptId}
    </update>
    <delete id="deleteEvaluateData">
        delete from pj_evaluate_details_child where evaluate_id = #{evaluateId} and template_id = #{templateId} and
            post_id = #{deptId}
        <if test="deptId == 0 or deptId == '0'">
            and person_id = #{userId}
        </if>
    </delete>
    <insert id="saveEvaluateData">
        insert into pj_evaluate_details_child(details_id,template_id,evaluate_id,post_id,person_id,sub_id,
                                              sub_name,pro_id,pro_name,title_id,score)values
        <foreach collection="list" item="item" separator=",">
            (#{item.parentId},#{item.templateId},#{item.evaluateId},#{item.deptId},#{item.userId},
            #{item.subId},#{item.subName},#{item.proId},#{item.proName},#{item.titleId},#{item.score})
        </foreach>
    </insert>
    <insert id="insertEvaluateChildData">
        insert into
            pj_evaluate_result(details_id,evaluate_id,sub_evaluate_id,sub_id,pro_id,dept_id,person_id,json,title_list,is_project,
                            check_one,check_person_one,is_active)
        values(#{parentId},#{evaluateId},#{templateId},#{subId},#{proId},#{deptId},#{userId},#{jsonData},#{titleFiled},#{isProject},'0','0','1')
    </insert>
    <update id="audit">
        <if test="evaluateType == 1 ">
            update pj_evaluate_result set check_one = #{type},reject_one = #{rejectReason},check_person_one = #{userId}
            where id = #{id}
        </if>

        <if test="evaluateType == 2 ">
            update pj_evaluate_result set check_two = #{type},reject_two = #{rejectReason},check_person_two = #{userId},
            check_one = #{type}
            where id = #{id}
        </if>
        <if test="evaluateType == 3 ">
            update pj_evaluate_result set check_three = #{type},reject_three = #{rejectReason},check_person_three = #{userId},
            check_one = #{type},check_two = #{type}
            where id = #{id}
        </if>
    </update>

    <update id="auditByThreeParams" >
        <if test="evaluateType == 1 ">
            update pj_evaluate_result set check_one = #{type},reject_one = #{rejectReason},check_person_one = #{userId}
            where evaluate_id = #{evaluateId} and sub_evaluate_id = #{templateId} and dept_id = #{deptId}
        </if>

        <if test="evaluateType == 2 ">
            update pj_evaluate_result set check_two = #{type},reject_two = #{rejectReason},check_person_two = #{userId},
            check_one = #{type}
            where evaluate_id = #{evaluateId} and sub_evaluate_id = #{templateId} and dept_id = #{deptId}
        </if>
        <if test="evaluateType == 3 ">
            update pj_evaluate_result set check_three = #{type},reject_three = #{rejectReason},check_person_three = #{userId},
            check_two = #{type},check_one = #{type}
            where evaluate_id = #{evaluateId} and sub_evaluate_id = #{templateId} and dept_id = #{deptId}
        </if>
    </update>


    <update id="updateEvaluateChildData">
        update pj_evaluate_result set json = #{jsonData},title_list = #{titleFiled},is_project =
            #{isProject},check_one = '0',check_person_one = '0',reject_one = '',check_two = '0',check_person_two =
            '0',reject_two = '',person_id = #{userId}
        where id = #{id}
    </update>

    <update id="updatepjEvaluateDetailsStatus">
        <if test="evaluateType == 1 ">
            update pj_evaluate_details set check_one = #{isApprove} where details_id = #{detailsId}
        </if>

        <if test=" evaluateType == 2 ">
            update pj_evaluate_details set check_two = #{isApprove},check_one = #{isApprove} where details_id = #{detailsId}
        </if>
        <if test=" evaluateType == 3 ">
            update pj_evaluate_details set check_three = #{isApprove}, check_two = #{isApprove},check_one = #{isApprove} where details_id = #{detailsId}
        </if>
    </update>

    <update id="updatePjEvaluateDetailsStatusByThreeParams">
        <if test="evaluateType == 1 ">
            update pj_evaluate_details set check_one = #{isApprove} where evaluate_id = #{evaluateId} and post_id = #{deptId} and sub_evaluate_id = #{templateId}
        </if>

        <if test=" evaluateType == 2  ">
            update pj_evaluate_details set check_two = #{isApprove},check_one = #{isApprove} where evaluate_id = #{evaluateId} and post_id = #{deptId} and sub_evaluate_id = #{templateId}
        </if>
        <if test=" evaluateType == 3  ">
            update pj_evaluate_details set check_three = #{isApprove},check_two = #{isApprove},check_one = #{isApprove} where evaluate_id = #{evaluateId} and post_id = #{deptId} and sub_evaluate_id = #{templateId}
        </if>
    </update>

    <update id="updateJsonData">
        update pj_evaluate_record set json = #{jsonData} where evaluate_id = #{evaluateId}
    </update>
    <update id="updateCheckTwoStatus">
        update pj_evaluate_details set check_two = #{isApprove} where evaluate_id = #{evaluateId} and sub_evaluate_id =
            #{templateId} and post_id = #{deptId}
    </update>
    <update id="fullApproval">
        update pj_evaluate_result set check_two = #{type},reject_two = #{rejectReason},check_person_two = #{userId}
         where evaluate_id = #{evaluateId} and sub_evaluate_id =
                                               #{templateId} and dept_id = #{deptId}
    </update>
    <update id="updateRecordStatus">
        update pj_evaluate_record set task_status = #{isApprove}
         where evaluate_id = #{evaluateId} and template_id = #{templateId} and dept_id = #{businessDivisionId}
    </update>


    <select id="getEvaluatSubListById" parameterType="com.bonus.gs.sub.evaluate.evaluate.beans.EvaluateSubBean" resultType="com.bonus.gs.sub.evaluate.evaluate.beans.EvaluateSubBean">
        select  sub_evaluate_id as subEvaluateId, sub_id as subId,sub_name as subName,pro_id as proId,pro_name  as proName from pj_evaluate_sub
        where sub_evaluate_id = 1
    </select>

    <select id="getTemplateById" resultType="com.bonus.gs.sub.evaluate.evaluate.beans.SetTemplateBean" >
        SELECT
        config_id as id,
        config_name as name,
        create_user_name as createUser,
        create_time as createTime,
        version as version,
        json as jsonTxt
        FROM
        pj_template_config
        WHERE
        is_active = '1'
        and config_id = #{id}
    </select>

    <select id="getTeamEvaluateList" parameterType="com.bonus.gs.sub.evaluate.evaluate.beans.OutSourceBean" resultType="com.bonus.gs.sub.evaluate.evaluate.beans.OutSourceBean">
        select
        per.evaluate_id as id,
        ped.details_id as detailsId,
        per.template_id as templateId,
        per.version as version,
        poi.name as evaluateDept,
        per.evaluate_name as evaluateTitle,
        per.year_and_month as evaluateMonth,
        per.create_time as createTime,
        r.proNum as projectNum,
        r.subNum as outsourcerNum,
        ifnull(GROUP_CONCAT(ped.post_id),'') as deptId,
        IF(ped.check_one is null, 0,IF( ped.check_one > 0, '0', '1' )) as isApprove,
        ped.isReject as isReject,
        IFNULL(c.evaluateNum,0) as evaluateNum,
        IF(c.rejectReason is null or c.rejectReason = '',c.rejectReasonTwo,c.rejectReason) as rejectReason,
        task.postId as taskPostId
        from pj_evaluate_record per
        left join (
        select
        evaluate_id,
        GROUP_CONCAT(pro_id) as pro_id,
        count(distinct sub_id) as subNum,
        count(distinct pro_id) as proNum
        from pj_evaluate_sub pes where pes.is_active = 1
        group by evaluate_id
        )r on per.evaluate_id = r.evaluate_id
        left join pj_template_config pt on pt.config_id = per.template_id and pt.is_active = 1
        left join pm_org_info poi on per.dept_id = poi.id and poi.status = 1
        left join pm_org_info poi1 on FIND_IN_SET(poi1.id, r.pro_id) and poi.status = 1
        LEFT JOIN (
        SELECT
        post_id,
        details_id,
        evaluate_id,
        count( IF ( check_one = '0' OR check_one = '2', 1, NULL )) AS check_one,
        count(IF( check_one = '2', 1, NULL )) AS isReject
        FROM
        pj_evaluate_details
        WHERE
        post_id = #{deptId}
        <if test="deptId == 0 or deptId == '0'">
           and person_id = #{userId}
        </if>
        GROUP BY evaluate_id
        ) ped ON ped.evaluate_id = per.evaluate_id
        LEFT JOIN (
        SELECT evaluate_id as evaluateId,COUNT(id) as evaluateNum,
        GROUP_CONCAT(DISTINCT IF(reject_one is null or reject_one = '',null,reject_one)) as rejectReason,
        GROUP_CONCAT(DISTINCT IF(reject_two is null or reject_two = '',null,reject_two)) as rejectReasonTwo
        from pj_evaluate_result where dept_id = #{deptId}
        <if test="deptId == 0 or deptId == '0'">
            and person_id = #{userId}
        </if>
        GROUP BY evaluate_id
        )c on c.evaluateId = per.evaluate_id
        left join (
        select group_concat(post_id) as postId,evaluate_id from pj_task_evaluated GROUP BY evaluate_id
        )task on task.evaluate_id = per.evaluate_id
        where per.is_active = 1 and FIND_IN_SET(#{deptId},task.postId)
        <if test="evaluateDeptId != null and evaluateDeptId != ''">
            and per.dept_id = #{evaluateDeptId}
        </if>
        <if test="deptId == 0 or deptId == '0'">
            and poi1.user_id = #{userId}
        </if>
        <if test="startDate != null and startDate != ''">
            and per.year_and_month = #{startDate}
        </if>
        <if test="keyWord != null and keyWord != ''">
            and per.evaluate_name like concat('%', #{keyWord}, '%')
        </if>
        group by per.evaluate_id
        <if test="evStatus != null and evStatus != '' and (auditStatus == null or auditStatus == '')">
            <choose>
                <when test="evStatus == 1">
                    having evaluateNum = 0
                </when>
                <when test="evStatus == 2">
                    having evaluateNum > 0
                </when>
                <when test="evStatus == 3">
                    having rejectReason is not null and rejectReason != ''
                </when>
            </choose>
        </if>
        <if test="(evStatus == null or evStatus == '') and auditStatus != null and auditStatus != ''">
            <choose>
                <when test="auditStatus == 1">
                    having evaluateNum = 0
                </when>
                <when test="auditStatus == 2">
                    having evaluateNum > 0 and isApprove = 0
                </when>
                <when test="auditStatus == 3">
                    having evaluateNum > 0 and isApprove = 1
                </when>
                <when test="auditStatus == 4">
                    having rejectReason is not null and rejectReason != ''
                </when>
            </choose>
        </if>
        <if test="evStatus != null and auditStatus != null and evStatus != '' and auditStatus != ''">
            <choose>
                <when test="evStatus == 1 and auditStatus == 1">
                    having evaluateNum = 0
                </when>
                <when test="evStatus == 1 and auditStatus == 2">
                    having evaluateNum = 0 and isApprove = 0
                </when>
                <when test="evStatus == 1 and auditStatus == 3">
                    having evaluateNum = 0 and isApprove = 1
                </when>
                <when test="evStatus == 1 and auditStatus == 4">
                    having evaluateNum = 0 and rejectReason is not null and rejectReason != ''
                </when>
                <when test="evStatus == 2 and auditStatus == 1">
                    having evaluateNum > 0 and evaluateNum = 0
                </when>
                <when test="evStatus == 2 and auditStatus == 2">
                    having evaluateNum > 0 and isApprove = 0
                </when>
                <when test="evStatus == 2 and auditStatus == 3">
                    having evaluateNum > 0 and isApprove = 1
                </when>
                <when test="evStatus == 2 and auditStatus == 4">
                    having evaluateNum > 0 and rejectReason is not null and rejectReason != ''
                </when>
                <when test="evStatus == 3 and auditStatus == 1">
                    having rejectReason is not null and rejectReason != '' and evaluateNum > 0
                </when>
                <when test="evStatus == 3 and auditStatus == 2">
                    having rejectReason is not null and rejectReason != '' and evaluateNum > 0 and isApprove = 0
                </when>
                <when test="evStatus == 3 and auditStatus == 3">
                    having rejectReason is not null and rejectReason != '' and evaluateNum > 0 and isApprove = 1
                </when>
                <when test="evStatus == 3 and auditStatus == 4">
                    having rejectReason is not null and rejectReason != ''
                </when>
            </choose>
        </if>
        order by MAX(per.create_time) desc
    </select>

    <select id="getSubAndProjectByEvaluateId" parameterType="string" resultType="com.bonus.gs.sub.evaluate.evaluate.beans.EvaluateSubBean">
        select sub_evaluate_id as subEvaluateId,sub_id as subId,sub_name as subName,pro_id as proId,pro_name as proName
        from pj_evaluate_sub
        where evaluate_id = #{id}
        <if test="type == '项目'">
            and pro_id in (select id from pm_org_info where user_id = #{userId} and table_source = 'pm_dept' and status
            = 1)
        </if>
        order by sub_id,pro_id
    </select>
    <select id="getDeptNameById" resultType="java.lang.String">
        select name from pm_org_info where id = #{deptId}
    </select>
    <select id="getTemplateDeatailsList" resultType="com.bonus.gs.sub.evaluate.evaluate.beans.SetTemplateBean">
        SELECT
        template_id as id,
        config_id as templateId,
        level,
        child_num as childNum,
        if(level = '2',concat(target_name,'（',target_score,'）'),target_name) as indexName,
        target_score as standardScore,
        count_type as countType,
        ifnull(parent_id,'0') as parentId,
        duty_dept as responsibleDepartment,
        method,
        standard
        FROM
        pj_template
        WHERE
        is_active = '1' and config_id = #{templateId} and version = (select version from pj_evaluate_record where evaluate_id =
                                                                                                         #{evaluateId})
        order by template_id asc
    </select>
    <select id="getEvaluateCount" resultType="java.lang.Integer">
        select
            count(1)
        from
            pj_evaluate_details
        where
            evaluate_id = #{evaluateId} and sub_evaluate_id = #{templateId} and post_id = #{deptId}
    </select>
    <select id="getCollectData" resultType="com.bonus.gs.sub.evaluate.evaluate.beans.EvaluateDataBean">
        select
            evaluate_id as evaluateId,
            sub_evaluate_id as templateId,
            post_id as deptId,
            person_id as userId,
            json as jsonData,
            title_list as titleFiled,
            is_project as isProject
        from pj_evaluate_details
        where evaluate_id = #{evaluateId}
    </select>
    <select id="getCollectChildData"
            resultType="com.bonus.gs.sub.evaluate.evaluate.beans.EvaluateDataDetailsBean">
        select
            details_id as parentId,
            template_id as templateId,
            evaluate_id as evaluateId,
            post_id as deptId,
            person_id as userId,
            sub_id as subId,
            sub_name as subName,
            pro_id as proId,
            pro_name as proName,
            title_id as titleId,
            score
        from pj_evaluate_details_child
        where  evaluate_id = #{evaluateId}
    </select>
    <select id="getEvaluateAuditList" resultType="com.bonus.gs.sub.evaluate.evaluate.beans.OutSourceBean">
        SELECT
        per.evaluate_id AS id,
        ped.details_id AS detailsId,
        per.template_id AS templateId,
        per.version AS version,
        poi.NAME AS evaluateDept,
        per.evaluate_name AS evaluateTitle,
        per.year_and_month AS evaluateMonth,
        per.create_time AS createTime,
        r.proNum AS projectNum,
        r.subNum AS outsourcerNum,
        ifnull( GROUP_CONCAT( ped.post_id ), '' ) AS deptId,
        IF(ped.check_one is null, 0,IF( ped.check_one > 0, '0', '1' )) as isApprove,
        ped.isReject as isReject,
        IFNULL( c.evaluateNum, 0 ) AS evaluateNum,
        if(c.rejectReason is null,c.rejectReasonTwo,c.rejectReason)AS rejectReason,
        ifnull(pjTaskExamine.num,0) as isAuditTask
        FROM
        pj_evaluate_record per
        LEFT JOIN (
        SELECT
        evaluate_id,
        GROUP_CONCAT( pro_id ) AS pro_id,
        count( DISTINCT sub_id ) AS subNum,
        count( DISTINCT pro_id ) AS proNum
        FROM
        pj_evaluate_sub pes
        WHERE
        pes.is_active = 1
        GROUP BY
        evaluate_id
        ) r ON per.evaluate_id = r.evaluate_id
        LEFT JOIN pj_template_config pt ON pt.config_id = per.template_id
        AND pt.is_active = 1
        LEFT JOIN pm_org_info poi ON per.dept_id = poi.id
        AND poi.STATUS = 1
        LEFT JOIN pm_org_info poi1 ON FIND_IN_SET( poi1.id, r.pro_id )
        AND poi.STATUS = 1
        LEFT JOIN (
        SELECT
        post_id,
        details_id,
        evaluate_id,
        count( IF ( check_one = '0' OR check_one = '2', 1, NULL )) AS check_one,
        count(IF( check_one = '2', 1, NULL )) AS isReject
        FROM
        pj_evaluate_details
        WHERE
        post_id =  #{deptId}
        <if test="isBusinessDivision == 1 or isBusinessDivision == '1'">
            or post_id = '0'
        </if>
        GROUP BY
        evaluate_id
        ) ped ON ped.evaluate_id = per.evaluate_id
        LEFT JOIN (
        SELECT
        evaluate_id AS evaluateId,
        COUNT( id ) AS evaluateNum,
        GROUP_CONCAT(DISTINCT IF(reject_one is null or reject_one = '',null,reject_one)) as rejectReason,
        GROUP_CONCAT(DISTINCT IF(reject_two is null or reject_two = '',null,reject_two)) as rejectReasonTwo
        FROM
        pj_evaluate_result
        WHERE
        dept_id = #{deptId}
        <if test="isBusinessDivision == 1 or isBusinessDivision == '1'">
            or dept_id = '0'
        </if>
        GROUP BY
        evaluate_id
        ) c ON c.evaluateId = per.evaluate_id
        left join (
        SELECT
        count(1) as num,
        GROUP_CONCAT(user_list) as user_list,
        evaluate_id
        from pj_task_examine
        group by evaluate_id) pjTaskExamine on pjTaskExamine.evaluate_id  = per.evaluate_id
        WHERE
        per.is_active = 1 and FIND_IN_SET(#{userId},pjTaskExamine.user_list)
        <if test="isBusinessDivision == 0 or isBusinessDivision == '0'">
            AND ped.post_id = #{deptId}
        </if>
        <if test="isBusinessDivision == 1 or isBusinessDivision == '1'">
            AND (ped.post_id = #{deptId} or ped.post_id = '0')
        </if>
        <if test="evaluateDeptId != null and evaluateDeptId != ''">
            and per.dept_id = #{evaluateDeptId}
        </if>
        <if test="startDate != null and startDate != ''">
            and per.year_and_month = #{startDate}
        </if>
        <if test="keyWord != null and keyWord != ''">
            and per.evaluate_name like concat('%', #{keyWord}, '%')
        </if>
        group by
            per.evaluate_id
        <if test="evStatus != null and evStatus != '' and (auditStatus == null or auditStatus == '')">
            <choose>
                <when test="evStatus == 1">
                    having evaluateNum = 0
                </when>
                <when test="evStatus == 2">
                    having evaluateNum > 0
                </when>
                <when test="evStatus == 3">
                    having rejectReason is not null and rejectReason != ''
                </when>
            </choose>
        </if>
        <if test="(evStatus == null or evStatus == '') and auditStatus != null and auditStatus != ''">
            <choose>
                <when test="auditStatus == 1">
                    having evaluateNum = 0
                </when>
                <when test="auditStatus == 2">
                    having evaluateNum > 0 and isApprove = 0
                </when>
                <when test="auditStatus == 3">
                    having evaluateNum > 0 and isApprove = 1
                </when>
                <when test="auditStatus == 4">
                    having rejectReason is not null and rejectReason != ''
                </when>
            </choose>
        </if>
        <if test="evStatus != null and auditStatus != null and evStatus != '' and auditStatus != ''">
            <choose>
                <when test="evStatus == 1 and auditStatus == 1">
                    having evaluateNum = 0
                </when>
                <when test="evStatus == 1 and auditStatus == 2">
                    having evaluateNum = 0 and isApprove = 0
                </when>
                <when test="evStatus == 1 and auditStatus == 3">
                    having evaluateNum = 0 and isApprove = 1
                </when>
                <when test="evStatus == 1 and auditStatus == 4">
                    having evaluateNum = 0 and rejectReason is not null and rejectReason != ''
                </when>
                <when test="evStatus == 2 and auditStatus == 1">
                    having evaluateNum > 0 and evaluateNum = 0
                </when>
                <when test="evStatus == 2 and auditStatus == 2">
                    having evaluateNum > 0 and isApprove = 0
                </when>
                <when test="evStatus == 2 and auditStatus == 3">
                    having evaluateNum > 0 and isApprove = 1
                </when>
                <when test="evStatus == 2 and auditStatus == 4">
                    having evaluateNum > 0 and rejectReason is not null and rejectReason != ''
                </when>
                <when test="evStatus == 3 and auditStatus == 1">
                    having rejectReason is not null and rejectReason != '' and evaluateNum > 0
                </when>
                <when test="evStatus == 3 and auditStatus == 2">
                    having rejectReason is not null and rejectReason != '' and evaluateNum > 0 and isApprove = 0
                </when>
                <when test="evStatus == 3 and auditStatus == 3">
                    having rejectReason is not null and rejectReason != '' and evaluateNum > 0 and isApprove = 1
                </when>
                <when test="evStatus == 3 and auditStatus == 4">
                    having rejectReason is not null and rejectReason != ''
                </when>
            </choose>
        </if>
        order by
            MAX(per.create_time) desc
    </select>
    <select id="getAuditJson" resultType="com.bonus.gs.sub.evaluate.evaluate.beans.EvaluateDataBean">
        select
            details_id as id,
            evaluate_id as evaluateId,
            sub_evaluate_id as templateId,
            post_id as deptId,
            json as jsonData,
            su.username as userName,
            ped.person_id as userId
        from pj_evaluate_details ped
        left join sys_user su on ped.person_id = su.id
        where
            evaluate_id = #{evaluateId} and sub_evaluate_id = #{templateId} and post_id = #{deptId}
        order by
            create_time desc
        limit 1
    </select>
    <select id="getChildData" resultType="com.bonus.gs.sub.evaluate.evaluate.beans.EvaluateDataBean">
        select
            per.id as id,
            details_id as parentId,
            evaluate_id as evaluateId,
            sub_evaluate_id as templateId,
            dept_id as deptId,
            json as jsonData,
            su.username as userName,
            per.person_id as userId,
            ifnull(per.check_one,'0') as isApprove,
            ifnull(per.check_two,'0') as isTwoApprove,
            ifnull(per.check_three,'0') as isThreeApprove
          from pj_evaluate_result per
                   left join sys_user su on per.person_id = su.id
         where
             evaluate_id = #{evaluateId} and sub_evaluate_id = #{templateId} and dept_id = #{deptId}
        <if test="userId !=null and userId !='null' and userId !=''">
            and per.person_id = #{userId}
        </if>
<!--        <if test="type =='evaluate'">-->
<!--            and per.check_one = '2'-->
<!--        </if>-->
         order by
             create_time desc
    </select>
    <select id="getCurrentIsApprove" resultType="java.lang.Integer">
        select
            count(1)
        from
            pj_evaluate_result
        where
            details_id = #{detailsId}
        <if test="evaluateType == 1 ">
            and check_one != '1'
        </if>
        <if test="evaluateType == 2 ">
            and check_two != '1'
        </if>
        <if test="evaluateType == 3 ">
            and check_three != '1'
        </if>

    </select>
    <select id="getIsBusinessDivision" resultType="java.lang.String">
        select
            org_type
        from
            pm_org_info
        where
            id = #{deptId}
    </select>
    <select id="getDeptName" resultType="com.bonus.gs.sub.evaluate.evaluate.beans.EvaluatePersonBean">
        select
            poi.name as deptName,
            per.dept_id as deptId
        from pj_evaluate_record per
        left join pm_org_info poi on per.dept_id = poi.id and poi.status = 1
        where evaluate_id = #{evaluateId}
    </select>
    <select id="getAuditBtn" resultType="com.bonus.gs.sub.evaluate.evaluate.beans.EvaluateDataBean">
        select
            duty_dept as deptId
        from
            pj_template
        where
            config_id = #{templateId} and version = (select version from pj_template_config where config_id = #{templateId})
        having deptId is not null
    </select>
    <select id="getSummaryJsonData" resultType="com.bonus.gs.sub.evaluate.evaluate.beans.EvaluateDataBean">
        select
            json as jsonData
        from
            pj_evaluate_record
        where
            evaluate_id = #{evaluateId}
    </select>
    <select id="getIndexNameById" resultType="java.lang.String">
        select
            target_name
        from pj_template
        where
            template_id = #{id}
    </select>
    <select id="getCountTypeById" resultType="java.lang.String">
        select
            collect_type
        from pj_template
        where
            template_id = #{id}
    </select>
    <select id="getStandardScoreById" resultType="java.lang.String">
        select
            target_score
        from pj_template
        where
            template_id = #{id}
    </select>

    <select id="getEvaluatedNum" resultType="int" parameterType="string">
        SELECT COUNT(a.person_id)  as  evaluated from (
        select person_id from pj_evaluate_result where evaluate_id = #{evaluateId}
        GROUP BY  person_id
        )a
    </select>

    <select id="getUnEvaluateNum" resultType="int" parameterType="string">
        SELECT
        LENGTH(user_list) - LENGTH(REPLACE(user_list, ',', '')) + 1 AS count
        FROM
        pj_task_evaluated where evaluate_id = #{evaluateId} and post_id =0
    </select>

    <select id="getProEvaluateNum" resultType="int" parameterType="string">
        select ifnull(count(evaluat_task_id),0) as num from pj_task_evaluated where evaluate_id = #{evaluateId} and post_id = -1
    </select>


    <insert id="addExaminePerson"  parameterType="com.bonus.gs.sub.evaluate.evaluate.beans.ExamineBean">
        insert into pj_task_examine(evaluat_task_id,evaluate_id,post_id,user_list,evaluate_type,evaluate_node)
        values (#{evaluatTaskId},#{evaluateId},#{postId},#{userList},#{evaluateType},#{evaluateNode})
    </insert>
    <insert id="addMonthRank">
        insert into
            pj_summary_compare(business_id,evaluate_id,sub_id,sub_name,leader_name,evaluate_date,year,month,title_name,score,user_id,user_name) values
        <foreach collection="list" item="item" separator=",">
            (#{bean.deptId}, #{bean.evaluateId}, #{item.subId}, #{item.subName}, #{item.leaderName},
            #{item.evaluateDate}, #{item.year}, #{item.month},
                #{item.titleName}, #{item.totalScore}, #{bean.userId}, #{bean.userName})
        </foreach>
    </insert>

    <select id="getExaminePersonByDeptAndRoleName" parameterType="string" resultType="string">
        select userId from sys_role_user where userId in (
            select id from sys_user where org_id = #{orgId}
        ) and roleId = (select id from sys_role where `name` = #{roleName})
    </select>


    <select id="getThisTemplateByEvaluateId" resultType="com.bonus.gs.sub.evaluate.evaluate.beans.SetTemplateBean" parameterType="string">
        SELECT
        a.config_id as templateId,a.duty_dept as dutyDept,a.version
        FROM
        pj_template a
        where
        a.config_id  = (select template_id from pj_evaluate_record where evaluate_id = #{evaluateId})
        and a.version = (select version from pj_evaluate_record where evaluate_id = #{evaluateId})
        and duty_dept is not null
    </select>

    <select id="getExamineNumByEvaluateId" resultType="int">
        select count(1) from pj_task_examine where evaluate_id = #{evaluateId} and evaluate_node = #{evaluateNode}
    </select>

    <select id="getOrgIddByEvaluateId" resultType="java.lang.String">
        select dept_id from pj_evaluate_record where evaluate_id = #{evaluateId}
    </select>

    <select id="startCollectExamineByEvaluateId" resultType="int" parameterType="com.bonus.gs.sub.evaluate.evaluate.beans.EvaluateDataBean">
        select count(id) from pj_evaluate_result where evaluate_id =  #{evaluateId}
        <if test="evaluateType == 1 ">
            and check_one ='0'
        </if>
        <if test="evaluateType == 2 ">
            and check_two ='0'
        </if>
        <if test="evaluateType == 3 ">
            and check_three ='0'
        </if>
    </select>
    <select id="getCurrentTwoIsApprove" resultType="java.lang.Integer">
        select
            count(1)
          from
              pj_evaluate_result
         where
             evaluate_id = #{evaluateId} and sub_evaluate_id = #{templateId} and check_two != '1'
    </select>
    <select id="getSummaryCompareList"
            resultType="com.bonus.gs.sub.evaluate.evaluate.beans.SummaryCompareBean">
        SELECT
            id,
            sub_id as subId,
            sub_name as subName,
            leader_name as leaderName,
            year,
            month,
            evaluate_date as evaluateDate,
            title_name as titleName,
            score
          from pj_summary_compare
            where
                business_id = #{bean.businessId}
            and evaluate_date in
        <foreach collection="arr" item="month" open="(" close=")" separator=",">
            #{month}
        </foreach>

    </select>
    <select id="getIsApprovalByDeptId" resultType="java.lang.Integer">
        SELECT
            count( 1 )
          FROM
              pj_evaluate_result
         WHERE
             evaluate_id = #{bean.evaluateId} and sub_evaluate_id = #{bean.templateId} and  dept_id =
                                                                                            #{dataBean.id} and
             (check_two
                                                                                                                != '1'
             or check_two is null or check_two = '')
    </select>
    <select id="getIsRejectionByDeptId" resultType="java.lang.Integer">
        SELECT
            count( 1 )
          FROM
              pj_evaluate_result
         WHERE
             evaluate_id = #{bean.evaluateId} and sub_evaluate_id = #{bean.templateId} and  dept_id =
                                                                                            #{dataBean.id} and
             (  check_two = '2')
    </select>
    <select id="isCheckOneIsAudit" resultType="java.lang.Integer">
        select count(1) from pj_evaluate_details where evaluate_id = #{evaluateId} and post_id = #{deptId} and
            check_one = '1' and sub_evaluate_id = #{templateId}
    </select>
    <select id="getEvaluateSubInfo" resultType="com.bonus.gs.sub.evaluate.evaluate.beans.EvaluateDataBean">
        select
            pes.sub_id as subId,
            pes.sub_name as subName,
            pes.pro_id as proId,
            pes.pro_name as proName,
            pa.corporateName as leaderName,
            per.year_and_month as evaluateMonth,
            DATE_FORMAT(CONCAT(per.year_and_month,"-",'01'),'%Y年%c月') as evaluateDate,
            DATE_FORMAT(CONCAT(per.year_and_month,"-",'01'),'%Y') as year,
            DATE_FORMAT(CONCAT(per.year_and_month,"-",'01'),'%m') as month,
            per.dept_id as deptId
          from
              pj_evaluate_sub pes
                  left join pm_org_info poi on pes.sub_id = poi.id
                  left join project_assignment pa on pa.id = poi.lk_id
                  left join pj_evaluate_record per on pes.evaluate_id = per.evaluate_id
         where
             pes.evaluate_id = #{evaluateId}
        group by
            pes.sub_id
    </select>
    <select id="isCheckTwoIsAudit" resultType="java.lang.Integer">

        select count(1) from pj_evaluate_details where evaluate_id = #{evaluateId} and post_id = #{deptId} and
            check_two = '1' and sub_evaluate_id = #{templateId}
    </select>

    <update id="updateEvaluateRecordByEvaluateId">
        update pj_evaluate_record set task_status = #{taskStatus} where evaluate_id = #{evaluateId}
    </update>


</mapper>
