<?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.InitiateEvaluateDao">
    <insert id="insertInitiateEvaluate" useGeneratedKeys="true" keyProperty="id">
        insert into pj_evaluate_record
        (dept_id, evaluate_name, template_id, version, year_and_month, task_status, is_active)
        values (#{orgId}, #{templateTitle}, #{templateId}, #{version}, #{evaluateDate}, 0, 1)
    </insert>
    <insert id="insertInitiateEvaluateLink">
        insert into pj_evaluate_sub
        (evaluate_id, pro_id, sub_id, sub_name,pro_name,is_active)
        values
        <foreach collection="arr" item="item" separator=",">
            (#{id}, #{item.proId}, #{item.id},#{item.subName},#{item.proName}, 1)
        </foreach>
    </insert>
    <insert id="insertPjTaskEvaluate">
        insert into pj_task_evaluated
        (evaluate_id, post_id, user_list, evaluate_type, is_wait, evaluate_method)
        values (#{id}, #{deptId}, #{ids}, '0', #{isWait}, 1)
    </insert>
    <update id="updateInitiateEvaluate">
        update pj_evaluate_record
           set dept_id        = #{orgId},
               evaluate_name  = #{templateTitle},
               template_id    = #{templateId},
               year_and_month = #{evaluateDate},
               version        = #{version}
         where evaluate_id = #{id}
    </update>
    <delete id="deleteInitiateEvaluateById">
        update pj_evaluate_record
           set is_active = 0
         where evaluate_id = #{id}
    </delete>
    <delete id="deleteInitiateEvaluateLink">
        update pj_evaluate_sub
           set is_active = 0
         where evaluate_id = #{id}
    </delete>
    <delete id="deleteInitiateLink">
        delete
          from pj_evaluate_sub
         where evaluate_id = #{id}
    </delete>
    <delete id="deletePjTaskEvaluate">
        delete
          from pj_task_evaluated
         where evaluate_id = #{id}
    </delete>
    <select id="getInitiateEvaluateList"
            resultType="com.bonus.gs.sub.evaluate.evaluate.beans.InitiateEvaluateBean">
        select
        per.evaluate_id as id,
        poi.name as orgName,
        per.evaluate_name as templateTitle,
        pt.config_name as templateName,
        per.year_and_month as evaluateDate,
        per.create_time as createTime,
        r.proNum as proNum,
        r.subNum as subNum,
        if(per.task_status = 0, '未评价', if(per.task_status = 1 , '评价中','已评价')) as status
        from pj_evaluate_record per
        left join (
        select
        evaluate_id,
        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 poi1.id = r.pro_id
        where per.is_active = 1
<!--          <if test="deptId == null or deptId == ''">-->
<!--              and poi1.user_id = #{userId}-->
<!--          </if>-->
        <if test="orgId != null and orgId != ''">
            and per.dept_id = #{orgId}
        </if>
        <if test="startDate != null and startDate != ''">
            and DATE_FORMAT(per.create_time, '%Y-%m-%d') between #{startDate} and #{endDate}
        </if>
        <if test="keyWord != null and keyWord != ''">
            and (
            per.evaluate_name like concat('%', #{keyWord}, '%') or
            pt.config_name like concat('%', #{keyWord},
            '%'))
        </if>
        order by per.create_time desc
    </select>
    <select id="getOrgSelect" resultType="com.bonus.gs.sub.evaluate.evaluate.beans.InitiateEvaluateBean">
        select id,
               name
          from pm_org_info poi
         where poi.status = 1
           and poi.level = '2'
           and poi.org_type = '1'
    </select>
    <select id="getTemplateSelect" resultType="com.bonus.gs.sub.evaluate.evaluate.beans.InitiateEvaluateBean">
        select config_id   as id,
               config_name as name,
               version     as version
          from pj_template_config
         where is_active = 1 and is_enable  = 0
    </select>
    <select id="getOrgTree" resultType="com.bonus.gs.sub.evaluate.evaluate.beans.NodeBean">
        SELECT id,
               name                  as title,
               name,
               level,
               parent_id             AS parentId,
               IFNULL(org_type, '1') as org_type
          FROM pm_org_info
         where level &lt; 4
        having org_type != 2
    </select>
    <select id="getSubListByOrgId" resultType="com.bonus.gs.sub.evaluate.evaluate.beans.InitiateEvaluateBean">
        SELECT
        poi.lk_id as id,
        poi1.id as proId,
        poi.name as subName,
        poi1.name as proName
        from pm_org_info poi
        left join pm_org_info poi1 on poi1.id = poi.parent_id and poi1.`status` = '1'
        where poi.parent_id in
        <foreach collection="arr" item="proId" open="(" separator="," close=")">
            #{proId}
        </foreach>
    </select>
    <select id="checkIsExistEvaluate"
            resultType="java.lang.Integer">
        select
        count(1) as count
        from pj_evaluate_record per
        where per.dept_id = #{orgId}
        and per.year_and_month = #{evaluateDate} and is_active = '1'
    </select>
    <select id="getInitiateEvaluateDetails"
            resultType="com.bonus.gs.sub.evaluate.evaluate.beans.InitiateEvaluateBean">
        select per.evaluate_id    as id,
               poi.name           as orgName,
               per.dept_id        as orgId,
               per.evaluate_name  as templateTitle,
               pt.config_name     as templateName,
               per.template_id    as templateId,
               per.year_and_month as evaluateDate,
               per.create_time    as createTime
          from pj_evaluate_record per
                   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
         where per.is_active = 1
           and per.evaluate_id = #{id}
    </select>
    <select id="getSubInfo" resultType="com.bonus.gs.sub.evaluate.evaluate.beans.SubBean">
        select sub_id   as subId,
               sub_id   as id,
               sub_name as subName,
               pro_id   as proId,
               pro_name as proName
          from pj_evaluate_sub
         where evaluate_id = #{id}
           and is_active = 1
    </select>
    <select id="getTemplationJson" resultType="java.lang.String">
        select json
          from pj_template_config
         where config_id = #{id}
    </select>
    <select id="getProUserIdByDeptId" resultType="com.bonus.gs.sub.evaluate.manager.model.SysUser">
        select
        user_id as id,
        user_name as username
        from pm_org_info
        where id in
        <foreach collection="arr" item="item" open="(" separator="," close=")">
            #{item.proId}
        </foreach>
    </select>
    <select id="getUserIdByDeptId" resultType="com.bonus.gs.sub.evaluate.manager.model.SysUser">
        select su.id       as id,
               su.username as username
          from sys_user su
                   left join sys_role_user sru
                             on su.id = sru.userId
                   left join sys_role sr
                             on sru.roleId = sr.id
         where su.org_id = #{deptId}
           and sr.id = '3'
    </select>
    <select id="getBusinessDivisionUserIdByDeptId"
            resultType="com.bonus.gs.sub.evaluate.manager.model.SysUser">
        select su.id       as id,
               su.username as username
          from sys_user su
                   left join sys_role_user sru
                             on su.id = sru.userId
                   left join sys_role sr
                             on sru.roleId = sr.id
         where su.org_id = #{orgId}
           and sr.id = '3'
    </select>
</mapper>
