<?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.keyPersonRating" name="重点人员评分">
    <select id="selectRatingDictTypeList" parameterType="page" resultType="map">
        SELECT
            id,
            label name,
            value rating_dict_type
        FROM
            sys_dict
        WHERE
            type = 'rating_dict_type'
        ORDER BY sort ASC
    </select>

    <select id="selectRatingDictList" parameterType="map" resultType="map">
        SELECT
            id,
            rating_dict_type,
            evaluate_type,
            scoring_project,
            rating_description,
            min_num,
            max_num,
            sort,
            is_history
        FROM
            jl_key_person_rating_dict
        WHERE
            rating_dict_type = #{rating_dict_type}
        ORDER BY sort,create_time DESC
    </select>

    <insert id="insertRatingDict" parameterType="map">
        INSERT INTO jl_key_person_rating_dict (
            id,
            rating_dict_type,
            evaluate_type,
            scoring_project,
            rating_description,
            min_num,
            max_num,
            sort,
            is_history,
            create_by,
            create_time
        ) VALUES (
            UUID(),
            #{rating_dict_type},
            #{evaluate_type},
            #{scoring_project},
            #{rating_description},
            #{min_num},
            #{max_num},
            #{sort},
            #{is_history},
            #{_user.id},
            NOW()
        )
    </insert>

    <update id="updateRatingDict" parameterType="map">
        UPDATE
            jl_key_person_rating_dict
        SET
            rating_dict_type = #{rating_dict_type},
            evaluate_type = #{evaluate_type},
            scoring_project = #{scoring_project},
            rating_description = #{rating_description},
            min_num = #{min_num},
            max_num = #{max_num},
            sort = #{sort},
            is_history = #{is_history},
            update_by = #{_user.id},
            update_time = NOW()
        WHERE
            id = #{id}
    </update>

    <delete id="deleteRatingDict" parameterType="map">
        DELETE FROM jl_key_person_rating_dict WHERE
        <if test="id != null and id != ''">
            id = #{id}
        </if>
        <if test="rating_dict_type != null and rating_dict_type != ''">
            rating_dict_type = #{rating_dict_type}
        </if>
    </delete>

    <select id="selectRatingDetailsList" parameterType="map" resultType="map">
        SELECT
            id,
            key_person_id,
            rating_dict_type,
            evaluate_type,
            scoring_project,
            is_whether,
            details,
            company_code,
            DATE_FORMAT(create_time, '%Y-%m-%d') create_time
        FROM
            jl_key_person_rating_details
        WHERE
            key_person_id = #{id}
        AND company_code = #{_user.company_code}
        ORDER BY create_time DESC
        LIMIT 1
    </select>

    <select id="selectRatingDetailsByPersonIdList" parameterType="map" resultType="map">
        SELECT
            id,
            key_person_id,
            rating_dict_type,
            evaluate_type,
            scoring_project,
            is_whether,
            details,
            company_code
        FROM
            jl_key_person_rating_details
        WHERE
            key_person_id = #{id}
        AND DATE_FORMAT(create_time, '%Y-%m-%d') = #{create_time}
        AND company_code = #{_user.company_code}
        ORDER BY create_time DESC
    </select>

    <insert id="insertRatingDetails" parameterType="map">
        INSERT INTO jl_key_person_rating_details(
            id,
            rating_dict_type,
            rating_history_id,
            key_person_id,
            key_person_rating_summary_id,
            evaluate_type,
            scoring_project,
            is_whether,
            details,
            sort,
            rating_description,
            company_code,
            create_by,
            create_time,
            rating_remarks
        ) VALUES (
            #{id},
            #{rating_dict_type},
            #{rating_history_id},
            #{key_person_id},
            #{key_person_rating_summary_id},
            #{evaluate_type},
            #{scoring_project},
            #{is_whether},
            #{details},
            #{sort},
            #{rating_description},
            #{_user.company_code},
            #{_user.id},
            NOW(),
            #{remarks}
        )
    </insert>

    <update id="updateRatingDetails" parameterType="map">
        UPDATE
            jl_key_person_rating_details
        SET
            evaluate_type = #{evaluate_type},
            scoring_project = #{scoring_project},
            is_whether = #{is_whether},
            details = #{details},
            update_by = #{_user.id},
            update_time = NOW()
        WHERE
            id = #{id}
    </update>

    <insert id="insertPersonRatingSummary" parameterType="map">
        INSERT INTO jl_key_person_rating_summary(
            id,
            rating_dict_type,
            rating_history_id,
            key_person_id,
            rating_personnel,
            rating_company,
            rating_time,
            rating_type,
            rating,
            is_history,
            create_by,
            create_time,
            rating_users,
            rating_custom_time,
            rating_img
        ) VALUES (
            #{id},
            #{rating_dict_type},
            #{rating_history_id},
            #{key_person_id},
            #{_user.id},
            #{_user.company_code},
            NOW(),
            #{rating_type},
            #{rating},
            #{is_history},
            #{_user.id},
            NOW(),
            #{rating_users},
            STR_TO_DATE(#{rating_custom_time}, '%Y-%m-%d %H:%i:%s'),
            #{rating_img}
        )
        <!--<selectKey keyProperty="count" resultType="int" order="BEFORE">
            SELECT COUNT(1) FROM jl_key_person_rating_summary WHERE id = #{key_person_rating_summary_id}
        </selectKey>
        <if test="count == 0">

        </if>
        <if test="count > 0">
            UPDATE
                jl_key_person_rating_summary
            SET
                rating_personnel = #{_user.id},
                rating_company = #{_user.company_code},
                rating_time = NOW(),
                rating_type = #{rating_type},
                rating = #{rating}
            WHERE
                id = #{key_person_rating_summary_id}
        </if>-->
    </insert>

    <select id="getPersonById" parameterType="map" resultType="map">
        SELECT
            id,
            name,
            sys_type,
            card_no,
            sex,
            folk,
            phone
        FROM
            jl_key_person
        WHERE
            id = #{id}
    </select>

    <select id="getRatingDetailsTime" parameterType="map" resultType="String">
        SELECT
        DATE_FORMAT(create_time, '%Y-%m-%d') create_time,
        FROM
        jl_key_person_rating_details
        WHERE
        key_person_id = #{id}
        ORDER BY create_time DESC
        LIMIT 1
    </select>

    <select id="selectRatingDetailsByPersonIdTimeList" parameterType="map" resultType="map">
        SELECT
            id,
            rating_dict_type,
            key_person_id,
            key_person_rating_summary_id,
            evaluate_type,
            scoring_project,
            is_whether,
            details,
            company_code
        FROM
            jl_key_person_rating_details
        WHERE
            key_person_id = #{id}
        AND DATE_FORMAT(create_time, '%Y-%m-%d') = #{create_time}
        ORDER BY rating_dict_type DESC
    </select>

    <select id="selectRatingSummaryByPersonIdList" parameterType="page" resultType="map">
        SELECT
            kprs.id,
            kprs.rating_dict_type,
            kprs.key_person_id,
            su.name rating_personnel,
            so.name rating_company,
            DATE_FORMAT(case when kprs.rating_custom_time is not null then kprs.rating_custom_time else kprs.rating_time end,'%Y-%m-%d %T') rating_time,
            kprs.rating_type,
            kprs.rating,
            DATE_FORMAT(kprs.rating_custom_time,'%Y-%m-%d %T') rating_custom_time,
            kprs.rating_users,
            kprs.rating_img
        from
            jl_key_person_rating_summary kprs
        LEFT JOIN sys_user su ON kprs.rating_personnel = su.id
        LEFT JOIN sys_office so ON kprs.rating_company = so.code
        <where>
            key_person_id = #{params.id}
            <if test="params.rating_history_id != null and params.rating_history_id != ''">
                and rating_history_id = #{params.rating_history_id}
            </if>
        </where>
        ORDER BY rating_time DESC
    </select>

    <select id="selectSummaryRatingDetailsList" parameterType="map" resultType="map">
        SELECT
            id,
            rating_dict_type,
            key_person_id,
            key_person_rating_summary_id,
            evaluate_type,
            scoring_project,
            IF(is_whether = '1','有','无') is_whether,
            details,
            company_code,
            rating_remarks
        FROM
            jl_key_person_rating_details
        WHERE
            key_person_rating_summary_id = #{key_person_rating_summary_id}
        ORDER BY sort,rating_dict_type DESC
    </select>

    <select id="getKeyPersonUser" parameterType="map" resultType="map">
        SELECT
            kp.id,
            kp.name,
            sys_type,CONCAT(LEFT(kp.phone,5), '********' ) phone,
            CONCAT(LEFT(cast(AES_DECRYPT(FROM_BASE64 (kp.card_no),'*#id#') AS CHAR),3), '****' ,RIGHT(cast(AES_DECRYPT(FROM_BASE64 (kp.card_no),'*#id#') AS CHAR),4)) card_no,
            IF(kp.sex = '1','男','女') sex,
            kp.address,
            gmc.mzname folk
        FROM
            jl_key_person kp
        LEFT JOIN jl_grid_mz_code gmc ON kp.folk = gmc.mzcode
        WHERE
            kp.id = #{id}
    </select>

    <select id="getPersonMinor" parameterType="map" resultType="map">
        SELECT
        kp.id,
        kp.name,
        sys_type,CONCAT(LEFT(kp.phone,5), '********' ) phone,
        CONCAT(LEFT(cast(AES_DECRYPT(FROM_BASE64 (kp.card_no),'*#id#') AS CHAR),3), '****' ,RIGHT(cast(AES_DECRYPT(FROM_BASE64 (kp.card_no),'*#id#') AS CHAR),4)) card_no,
        IF(kp.sex = '1','男','女') sex,
        kp.address,
        gmc.mzname folk
        FROM
        jl_key_person_minor kp
        LEFT JOIN jl_grid_mz_code gmc ON kp.folk = gmc.mzcode
        WHERE
        kp.id = #{id}
    </select>

    <update id="updateIsNew" parameterType="map">
        UPDATE
            jl_key_person_rating_summary
        SET
            is_history = '0',
            update_by = #{_user.id},
            update_time = NOW()
        WHERE
            key_person_id = #{key_person_id}
            AND rating_dict_type = #{rating_dict_type}
        AND is_history = '1'
    </update>

    <update id="updateHistory" parameterType="map">
        UPDATE
        jl_key_person_rating_history
        SET
        do_status = #{do_status},
        update_by = #{_user.id},
        update_time = NOW()
        WHERE
        key_person_id = #{key_person_id}
        AND rating_dict_type = #{rating_dict_type}
        AND id = #{id}
    </update>

    <select id="selectRatingHistoryById" parameterType="map" resultType="map">
        SELECT
        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,
        update_by,
        update_time
        FROM
        jl_key_person_rating_history
        WHERE
        id = #{id}
    </select>


    <select id="findKeyPersonMinorList" parameterType="page" resultType="map">
        SELECT
            id,
            name,
            card_no,
            registered_residence,
            responsible_person,
            responsible_company,
            sex,
            address,
            folk,
            risk_level,
            involving_conflicts_disputes,
            create_date,
            update_date,
            create_user,
            update_user,
            region,
            city_id,
            area_id,
            street_id,
            community_id,
            remarks
        FROM
            jl_key_person_minor
        <where>
            <if test="params.name != null and params.name != ''">
                and jl_key_person.name like concat('%',#{params.name},'%')
            </if>
            <if test="params.cardNo != null and params.cardNo != ''">
                and card_no like concat('%',TO_BASE64(AES_ENCRYPT(#{params.cardNo},'*#id#')),'%')
            </if>
            <if test="params._user.company_grade != '2'.toString()">
                and (
                create_user=#{params._user.id}
                or create_user in (
                select id from sys_user where company_id in (
                select id from sys_office where parent_ids LIKE CONCAT('%', #{params._user.company_id}, '%') and del_flag='0'
                ) and del_flag='0'
                )
                )
            </if>
            <if test="params.cityId!=null and params.cityId!=''">
                AND city_id = #{params.cityId}
            </if>
            <if test="params.areaId!=null and params.areaId!=''">
                AND area_id = #{params.areaId}
            </if>
            <if test="params.streetId!=null and params.streetId!=''">
                AND street_id = #{params.streetId}
            </if>
            <if test="params.communityId!=null and params.communityId!=''">
                AND community_id = #{params.communityId}
            </if>
        </where>
    </select>

    <insert id="insertKeyPersonMinor" parameterType="map">
        INSERT INTO jl_key_person_minor(
            id,
            name,
            card_no,
            registered_residence,
            responsible_person,
            responsible_company,
            sex,
            address,
            folk,
            risk_level,
            involving_conflicts_disputes,
            create_date,
            update_date,
            create_user,
            update_user,
            region,
            city_id,
            area_id,
            street_id,
            community_id,
            remarks
        ) VALUES (
            UUID(),
            #{name},
            TO_BASE64(AES_ENCRYPT(#{cardNo,jdbcType=VARCHAR},'*#id#')),
            #{registeredResidence},
            #{responsiblePerson},
            #{responsibleCompany},
            #{sex},
            #{address},
            #{folk},
            #{riskLevel},
            #{involvingConflictsDisputes},
            now(),
            now(),
            #{_user.id},
            #{_user.id},
            #{region},
            #{cityId},
            #{areaId},
            #{streetId},
            #{communityId},
            #{remarks}
        )
    </insert>

    <update id="updateKeyPersonMinor" parameterType="map">
        UPDATE
            jl_key_person_minor
        SET
            name = #{name},
            card_no = TO_BASE64(AES_ENCRYPT(#{cardNo,jdbcType=VARCHAR},'*#id#')),
            registered_residence = #{registeredResidence},
            responsible_person = #{responsiblePerson},
            responsible_company = #{responsibleCompany},
            sex = #{sex},
            address = #{address},
            folk = #{folk},
            risk_level = #{riskLevel},
            involving_conflicts_disputes = #{involvingConflictsDisputes},
            update_date = now(),
            update_user = #{_user.id},
            region = #{region},
            city_id = #{cityId},
            area_id = #{areaId},
            street_id = #{streetId},
            community_id = #{communityId},
            remarks = #{remarks}
        WHERE
            id = #{id}
    </update>

    <delete id="deleteKeyPersonMinor" parameterType="map">
        DELETE FROM jl_key_person_minor WHERE id = #{id}
    </delete>
</mapper>
