<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//oos.juyouhx.com//DTD Mapper 3.0//EN" "http://oss.juyouhx.com/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.scpyun.platform.standard.jilinsscgsdp.keyPersonRatingHistory" name="评分历史">
    <select id="selectRatingHistoryList" parameterType="page" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPersonRatingHistory">
        SELECT
            history.id as id,
            history.rating_dict_type as ratingDictType,
            history.key_person_id as keyPersonId,
            history.is_history as isHistory,
            history.is_overdue as isOverdue,
            history.batch_id as batchId,
            history.create_by as createBy,
            history.create_time as createTime,
            history.update_by as updateBy,
            history.update_time as updateTime,
            summary_latest.rating_type as ratingType,
            (select sys_dict.label
                from sys_dict
                where sys_dict.type = 'rating_level'
                and sys_dict.del_flag = '0'
                and sys_dict.value = summary_latest.rating_type) as ratingTypeName,
            summary_latest.rating as rating,
            DATE_FORMAT(history.plan_start_data,'%Y-%m-%d') planStartData,
            DATE_FORMAT(history.plan_end_data,'%Y-%m-%d') planEndData,
            DATE_FORMAT(history.overdue_date,'%Y-%m-%d') overdueDate,
            DATE_FORMAT(case when summary_latest.rating_custom_time is not null then summary_latest.rating_custom_time else summary_latest.rating_time end,'%Y-%m-%d %T') ratingTime,
            case
                when history.id is null then 1
                when curdate() &gt;= history.plan_start_data and curdate()  &lt;= history.plan_end_data
                then 1
                else 0
            end as canClick,
            history.id as historyId,
            summary_latest.id as summaryId,
            case
                when history.do_status = '1' then 1
                when history.do_status = '2' then 2
                when history.do_status = '3' then 3
                else '0'
            end as doStatus,
            su.name as ratingPersonnel,
            so.name as ratingCompany,
            summary_latest.rating_users,
        <choose>
            <when test="params._scope.scope_grade == '3'.toString()">
                case when dualPerson.id is not null and keyPerson.city_id = #{params._scope.scope_area_id}
                and keyPerson.city_id != dualPerson.city_id then '1' else '0' end as dualMark
            </when>
            <when test="params._scope.scope_grade == '4'.toString()">
                case when dualPerson.id is not null and keyPerson.area_id = #{params._scope.scope_area_id}
                and keyPerson.area_id != dualPerson.area_id then '1' else '0' end as dualMark
            </when>
            <when test="params._scope.scope_grade == '5'.toString()">
                case when dualPerson.id is not null and keyPerson.street_id = #{params._scope.scope_area_id}
                and keyPerson.street_id != dualPerson.street_id then '1' else '0' end as dualMark
            </when>
            <when test="params._scope.scope_grade == '6'.toString()">
                case when dualPerson.id is not null and keyPerson.community_id = #{params._scope.scope_area_id}
                and keyPerson.community_id != dualPerson.community_id then '1' else '0' end as dualMark
            </when>
            <otherwise>
                '0' as dualMark
            </otherwise>
        </choose>
        FROM jl_key_person_rating_history history
        LEFT JOIN (
            SELECT
            s.key_person_id,
            s.rating_history_id,
            MAX(s.rating_time) as latest_rating_time
        FROM
            jl_key_person_rating_summary s
        WHERE
            s.rating_dict_type = #{params.ratingDictType}
        GROUP BY
        s.key_person_id, s.rating_history_id
        ) AS summary_group
        ON history.key_person_id = summary_group.key_person_id
        AND history.id = summary_group.rating_history_id
        LEFT JOIN jl_key_person_rating_summary summary_latest
        ON summary_group.key_person_id = summary_latest.key_person_id
        AND summary_group.rating_history_id = summary_latest.rating_history_id
        AND summary_group.latest_rating_time = summary_latest.rating_time
        LEFT JOIN sys_user su ON summary_latest.rating_personnel = su.id
        LEFT JOIN sys_office so ON summary_latest.rating_company = so.code
        LEFT JOIN (select id,city_id,area_id,street_id,community_id from jl_key_person
            union all select id,city_id,area_id,street_id,community_id from jl_key_person_minor) AS keyPerson ON keyPerson.id=history.key_person_id
        LEFT JOIN jl_key_person_rating_dual_person dualPerson on dualPerson.key_person_id=keyPerson.id
        <where> 1=1
            <if test="params.ratingDictType != null and params.ratingDictType != ''">
                and history.rating_dict_type like concat('%',#{params.ratingDictType},'%')
            </if>
            <if test="params.keyPersonId != null and params.keyPersonId != ''">
                and history.key_person_id = #{params.keyPersonId}
            </if>
        </where>
        ORDER BY history.create_time DESC
    </select>

<!--    <select id="personRatingHistoryCount" parameterType="map" resultType="map">-->
<!--        SELECT count(0) as count-->
<!--        FROM jl_key_person_rating_history-->
<!--        <where>-->
<!--            <if test="key_person_id != null and key_person_id != ''">-->
<!--                and key_person_id = #{key_person_id}-->
<!--            </if>-->
<!--            <if test="rating_dict_type != null and rating_dict_type != ''">-->
<!--                and rating_dict_type like concat('%',#{rating_dict_type},'%')-->
<!--            </if>-->
<!--        </where>-->
<!--    </select>-->

    <insert id="insertPersonRatingHistory" parameterType="map">
        INSERT INTO jl_key_person_rating_history(
            id,
            rating_dict_type,
            key_person_id,
            plan_start_data,
            plan_end_data,
            overdue_date,
            is_history,
            do_status,
            is_overdue,
            batch_id,
            create_by,
            create_time
        ) VALUES (
            #{id},
            #{rating_dict_type},
            #{key_person_id},
            #{plan_start_data},
            #{plan_end_data},
            #{overdue_date},
            #{is_history},
            #{do_status},
            #{is_overdue},
            #{batch_id},
            #{_user.id},
            NOW()
        )
    </insert>

    <!--批量导入重点人员信息-->
    <!--原表-->
    <select id="getAllPersons" parameterType="map" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPersonImp">
        SELECT
        id, name, sex, folk,
        cast(AES_DECRYPT(FROM_BASE64(card_no),'*#id#') as char) as cardNo,
        phone,
        registered_residence as registeredResidence,
        city_id as cityId, area_id as areaId, street_id as streetId, community_id as communityId,
        address,
        involving_conflicts_disputes as involvingConflictsDisputes,
        risk_level as riskLevel,
        responsible_company_by_assistance as responsibleCompanyByAssistance,
        responsible_person_by_assistance as responsiblePersonByAssistance,
        responsible_company_by_key_personnel as responsibleCompanyByKeyPersonnel,
        responsible_person_key_personnel as responsiblePersonKeyPersonnel,
        hazard_assessment_level as hazardAssessmentLevel
        FROM
        jl_key_person
        WHERE sys_type = #{sysType}
    </select>
    <select id="getAllPersons3" parameterType="map" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPerson3Imp">
        SELECT
        id, name, sex, folk,
        cast(AES_DECRYPT(FROM_BASE64(card_no),'*#id#') as char) as cardNo,
        phone,
        registered_residence as registeredResidence,
        city_id as cityId, area_id as areaId, street_id as streetId, community_id as communityId,
        address,
        involving_conflicts_disputes as involvingConflictsDisputes,
        risk_level as riskLevel,
        responsible_company_by_assistance as responsibleCompanyByAssistance,
        responsible_person_by_assistance as responsiblePersonByAssistance,
        responsible_company_by_key_personnel as responsibleCompanyByKeyPersonnel,
        responsible_person_key_personnel as responsiblePersonKeyPersonnel,
        hazard_assessment_level as hazardAssessmentLevel,
        related_person_relation as relatedPersonRelation,
        related_person_name as relatedPersonName,
        related_person_address as relatedPersonAddress,
        related_person_phone as relatedPersonPhone,
        control_company_supervisor as controlCompanySupervisor,
        control_company_supervisor_person as controlCompanySupervisorPerson
        FROM
        jl_key_person
        WHERE sys_type = #{sysType}
    </select>
    <insert id="keyPersonInsertBatch" parameterType="map">
        INSERT INTO jl_key_person(id, sys_type, name, sex, folk, card_no, phone,
        registered_residence, city_id, area_id, street_id, community_id, address,
        involving_conflicts_disputes, risk_level,
        responsible_company_by_assistance,
        responsible_person_by_assistance,
        responsible_company_by_key_personnel,
        responsible_person_key_personnel,
        responsible_company,
        responsible_person,
        hazard_assessment_level,
        related_person_relation,
        related_person_name,
        related_person_address,
        related_person_phone,
        control_company_supervisor,
        control_company_supervisor_person,
        create_user) VALUES
        <foreach collection="list" item="person" index="index" separator=",">
            (#{person.id}, #{person.sysType}, #{person.name}, #{person.sex}, #{person.folk},
            TO_BASE64(AES_ENCRYPT(#{person.cardNo},'*#id#')),
            #{person.phone},
            #{person.registeredResidence}, #{person.cityId}, #{person.areaId}, #{person.streetId}, #{person.communityId},
            #{person.address}, #{person.involvingConflictsDisputes}, #{person.riskLevel},
            #{person.responsibleCompanyByAssistance}, #{person.responsiblePersonByAssistance},
            #{person.responsibleCompanyByKeyPersonnel}, #{person.responsiblePersonKeyPersonnel},
            #{person.responsibleCompany}, #{person.responsiblePerson},
            #{person.hazardAssessmentLevel},
            #{person.relatedPersonRelation},
            #{person.relatedPersonName},
            #{person.relatedPersonAddress},
            #{person.relatedPersonPhone},
            #{person.controlCompanySupervisor},
            #{person.controlCompanySupervisorPerson},
            #{person.createUser})
        </foreach>
    </insert>
    <insert id="keyPersonHighlyInsertBatch" parameterType="map">
        INSERT INTO jl_key_person_highly_concerned_rel(id,
        key_person_id,
        card_no,
        highly_concerned_situation,
        create_date,
        update_date,
        create_user,
        update_user) VALUES
        <foreach collection="list" item="obj" index="index" separator=",">
            (#{obj.id}, #{obj.keyPersonId}, #{obj.cardNo}, #{obj.highly}, NOW(), NOW(), #{obj.userId}, #{obj.userId})
        </foreach>
    </insert>
    <update id="keyPersonUpdateBatch" parameterType="map">
        UPDATE jl_key_person
        <set>
            <if test="name != null and name != ''">
                name = #{name},
            </if>
            <if test="sex != null and sex != ''">
                sex = #{sex},
            </if>
            <if test="folk != null and folk != ''">
                folk = #{folk},
            </if>
            <if test="phone != null and phone != ''">
                phone = #{phone},
            </if>
            <if test="registeredResidence != null and registeredResidence != ''">
                registered_residence = #{registeredResidence},
            </if>
            <if test="cityId != null and cityId != ''">
                `city_id` = #{cityId},
            </if>
            <if test="areaId != null and areaId != ''">
                `area_id` = #{areaId},
            </if>
            <if test="streetId != null and streetId != ''">
                `street_id` = #{streetId},
            </if>
            <if test="communityId != null and communityId != ''">
                `community_id` = #{communityId},
            </if>
            <if test="address != null and address != ''">
                address = #{address},
            </if>
            <if test="involvingConflictsDisputes != null and involvingConflictsDisputes != ''">
                involving_conflicts_disputes = #{involvingConflictsDisputes},
            </if>
            <if test="riskLevel != null and riskLevel != ''">
                risk_level = #{riskLevel},
            </if>
            <if test="responsibleCompanyByAssistance != null and responsibleCompanyByAssistance != ''">
                responsible_company_by_assistance = #{responsibleCompanyByAssistance},
            </if>
            <if test="responsiblePersonByAssistance != null and responsiblePersonByAssistance != ''">
                responsible_person_by_assistance = #{responsiblePersonByAssistance},
            </if>
            <if test="responsibleCompanyByKeyPersonnel != null and responsibleCompanyByKeyPersonnel != ''">
                responsible_company_by_key_personnel = #{responsibleCompanyByKeyPersonnel},
            </if>
            <if test="responsiblePersonKeyPersonnel != null and responsiblePersonKeyPersonnel != ''">
                responsible_person_key_personnel = #{responsiblePersonKeyPersonnel},
            </if>
            <if test="responsibleCompany != null and responsibleCompany != ''">
                responsible_company = #{responsibleCompany},
            </if>
            <if test="responsiblePerson != null and responsiblePerson != ''">
                responsible_person = #{responsiblePerson},
            </if>
            <if test="hazardAssessmentLevel != null and hazardAssessmentLevel != ''">
                hazard_assessment_level = #{hazardAssessmentLevel},
            </if>
            <if test="relatedPersonRelation != null and relatedPersonRelation != ''">
                related_person_relation = #{relatedPersonRelation},
            </if>
            <if test="relatedPersonName != null and relatedPersonName != ''">
                related_person_name = #{relatedPersonName},
            </if>
            <if test="relatedPersonAddress != null and relatedPersonAddress != ''">
                related_person_address = #{relatedPersonAddress},
            </if>
            <if test="relatedPersonPhone != null and relatedPersonPhone != ''">
                related_person_phone = #{relatedPersonPhone},
            </if>
            <if test="controlCompanySupervisor != null and controlCompanySupervisor != ''">
                control_company_supervisor = #{controlCompanySupervisor},
            </if>
            <if test="controlCompanySupervisorPerson != null and controlCompanySupervisorPerson != ''">
                control_company_supervisor_person = #{controlCompanySupervisorPerson},
            </if>
            `update_user` = #{updateUser},
            `update_date` = NOW()
        </set>
        where card_no = TO_BASE64(AES_ENCRYPT(#{cardNo},'*#id#')) and sys_type = #{sysType}
    </update>
    <delete id="deletekeyPersonHighlyByIds" parameterType="map">
        DELETE FROM jl_key_person_highly_concerned_rel
        WHERE highly_concerned_situation in ('婚姻家庭不幸','有重大疾病','有暴力倾向','有弃世厌世倾向','扬言报复社会','重新犯罪','黑恶','恶性犯罪','邪教','三无人员','其他') and card_no IN
        <foreach collection="list" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
    <!--重点未成年人表-->
    <select id="getAllPersons_new" parameterType="map" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.KeyPersonImp">
        SELECT
        id, name, sex, folk,
        cast(AES_DECRYPT(FROM_BASE64(card_no),'*#id#') as char) as cardNo,
        phone,
        registered_residence as registeredResidence,
        city_id as cityId, area_id as areaId, street_id as streetId, community_id as communityId,
        address,
        involving_conflicts_disputes as involvingConflictsDisputes,
        risk_level as riskLevel,
        responsible_company_by_assistance as responsibleCompanyByAssistance,
        responsible_person_by_assistance as responsiblePersonByAssistance,
        responsible_company_by_key_personnel as responsibleCompanyByKeyPersonnel,
        responsible_person_key_personnel as responsiblePersonKeyPersonnel,
        hazard_assessment_level as hazardAssessmentLevel
        FROM
        jl_key_person_minor
        WHERE sys_type = #{sysType}
    </select>
    <insert id="keyPersonInsertBatch_new" parameterType="map">
        INSERT INTO jl_key_person_minor(id, sys_type, name, sex, folk, card_no, phone,
        registered_residence, city_id, area_id, street_id, community_id, address,
        involving_conflicts_disputes, risk_level,
        responsible_company_by_assistance,
        responsible_person_by_assistance,
        responsible_company_by_key_personnel,
        responsible_person_key_personnel,
        responsible_company,
        responsible_person,
        hazard_assessment_level,
        related_person_relation,
        related_person_name,
        related_person_address,
        related_person_phone,
        control_company_supervisor,
        control_company_supervisor_person,
        create_user, region) VALUES
        <foreach collection="list" item="person" index="index" separator=",">
            (#{person.id}, #{person.sysType}, #{person.name}, #{person.sex}, #{person.folk},
            TO_BASE64(AES_ENCRYPT(#{person.cardNo},'*#id#')),
            #{person.phone},
            #{person.registeredResidence}, #{person.cityId}, #{person.areaId}, #{person.streetId}, #{person.communityId},
            #{person.address}, #{person.involvingConflictsDisputes}, #{person.riskLevel},
            #{person.responsibleCompanyByAssistance}, #{person.responsiblePersonByAssistance},
            #{person.responsibleCompanyByKeyPersonnel}, #{person.responsiblePersonKeyPersonnel},
            #{person.responsibleCompany}, #{person.responsiblePerson},
            #{person.hazardAssessmentLevel},
            #{person.relatedPersonRelation},
            #{person.relatedPersonName},
            #{person.relatedPersonAddress},
            #{person.relatedPersonPhone},
            #{controlCompanySupervisor},
            #{controlCompanySupervisorPerson},
            #{person.createUser}, '1')
        </foreach>
    </insert>
    <insert id="keyPersonHighlyInsertBatch_new" parameterType="map">
        INSERT INTO jl_key_person_minor_highly_concerned_rel(id,
        key_person_id,
        card_no,
        highly_concerned_situation,
        create_date,
        update_date,
        create_user,
        update_user) VALUES
        <foreach collection="list" item="obj" index="index" separator=",">
            (#{obj.id}, #{obj.keyPersonId}, #{obj.cardNo}, #{obj.highly}, NOW(), NOW(), #{obj.userId}, #{obj.userId})
        </foreach>
    </insert>

    <update id="keyPersonUpdateBatch_new" parameterType="map">
        UPDATE jl_key_person_minor
        <set>
            <if test="name != null and name != ''">
                name = #{name},
            </if>
            <if test="sex != null and sex != ''">
                sex = #{sex},
            </if>
            <if test="folk != null and folk != ''">
                folk = #{folk},
            </if>
            <if test="phone != null and phone != ''">
                phone = #{phone},
            </if>
            <if test="registeredResidence != null and registeredResidence != ''">
                registered_residence = #{registeredResidence},
            </if>
            <if test="cityId != null and cityId != ''">
                `city_id` = #{cityId},
            </if>
            <if test="areaId != null and areaId != ''">
                `area_id` = #{areaId},
            </if>
            <if test="streetId != null and streetId != ''">
                `street_id` = #{streetId},
            </if>
            <if test="communityId != null and communityId != ''">
                `community_id` = #{communityId},
            </if>
            <if test="address != null and address != ''">
                address = #{address},
            </if>
            <if test="involvingConflictsDisputes != null and involvingConflictsDisputes != ''">
                involving_conflicts_disputes = #{involvingConflictsDisputes},
            </if>
            <if test="riskLevel != null and riskLevel != ''">
                risk_level = #{riskLevel},
            </if>
            <if test="responsibleCompanyByAssistance != null and responsibleCompanyByAssistance != ''">
                responsible_company_by_assistance = #{responsibleCompanyByAssistance},
            </if>
            <if test="responsiblePersonByAssistance != null and responsiblePersonByAssistance != ''">
                responsible_person_by_assistance = #{responsiblePersonByAssistance},
            </if>
            <if test="responsibleCompanyByKeyPersonnel != null and responsibleCompanyByKeyPersonnel != ''">
                responsible_company_by_key_personnel = #{responsibleCompanyByKeyPersonnel},
            </if>
            <if test="responsiblePersonKeyPersonnel != null and responsiblePersonKeyPersonnel != ''">
                responsible_person_key_personnel = #{responsiblePersonKeyPersonnel},
            </if>
            <if test="responsibleCompany != null and responsibleCompany != ''">
                responsible_company = #{responsibleCompany},
            </if>
            <if test="responsiblePerson != null and responsiblePerson != ''">
                responsible_person = #{responsiblePerson},
            </if>
            <if test="hazardAssessmentLevel != null and hazardAssessmentLevel != ''">
                hazard_assessment_level = #{hazardAssessmentLevel},
            </if>
            <if test="relatedPersonRelation != null and relatedPersonRelation != ''">
                related_person_relation = #{relatedPersonRelation},
            </if>
            <if test="relatedPersonName != null and relatedPersonName != ''">
                related_person_name = #{relatedPersonName},
            </if>
            <if test="relatedPersonAddress != null and relatedPersonAddress != ''">
                related_person_address = #{relatedPersonAddress},
            </if>
            <if test="relatedPersonPhone != null and relatedPersonPhone != ''">
                related_person_phone = #{relatedPersonPhone},
            </if>
            <if test="controlCompanySupervisor != null and controlCompanySupervisor != ''">
                control_company_supervisor = #{controlCompanySupervisor},
            </if>
            <if test="controlCompanySupervisorPerson != null and controlCompanySupervisorPerson != ''">
                control_company_supervisor_person = #{controlCompanySupervisorPerson},
            </if>
            `update_user` = #{updateUser},
            `update_date` = NOW()
        </set>
        where card_no = TO_BASE64(AES_ENCRYPT(#{cardNo},'*#id#')) and sys_type = #{sysType}
    </update>
    <!--<update id="keyPersonUpdateBatch_new" parameterType="map">
        <foreach collection="list" item="person" separator=";">
            UPDATE jl_key_person_minor
            SET `name` = #{person.name}, `sex` = #{person.sex}, `folk` = #{person.folk}, `phone` = #{person.phone},
            `registered_residence` = #{person.registeredResidence},
            `city_id` = #{person.cityId}, `area_id` = #{person.areaId}, `street_id` = #{person.streetId}, `community_id` = #{person.communityId},
            `address` = #{person.address}, `involving_conflicts_disputes` = #{person.involvingConflictsDisputes},
            `risk_level` = #{person.riskLevel},
            `responsible_company_by_assistance` = #{person.responsibleCompanyByAssistance},
            `responsible_person_by_assistance` = #{person.responsiblePersonByAssistance},
            `responsible_company_by_key_personnel` = #{person.responsibleCompanyByKeyPersonnel},
            `responsible_person_key_personnel` = #{person.responsiblePersonKeyPersonnel},
            hazard_assessment_level = #{person.hazardAssessmentLevel},
            `update_user` = #{person.updateUser},
            `update_date` = NOW()
            WHERE `id` = #{person.id}
        </foreach>
    </update>-->
    <delete id="deletekeyPersonHighlyByIds_new" parameterType="map">
        DELETE FROM jl_key_person_minor_highly_concerned_rel
        WHERE highly_concerned_situation in ('婚姻家庭不幸','有重大疾病','有暴力倾向','有弃世厌世倾向','扬言报复社会','重新犯罪','黑恶','恶性犯罪','邪教','三无人员','其他') and card_no IN
        <foreach collection="list" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

</mapper>
