<?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.dromara.zjk.mapper.ZjkExpertMapper">

    <select id="selectExistingIdCards" resultType="String">
        SELECT id_card
        FROM zjk_expert
        WHERE id_card IN
        <foreach item="id" collection="idCards" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>
    <select id="selectByIds" resultType="org.dromara.zjk.domain.ZjkExpert">
        select *
        from zjk_expert
        where delete_flag = 0
        and expert_id in
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>

    </select>

    <select id="expertInquiry" resultType="org.dromara.zjk.domain.vo.ZjkExpertVo">
        SELECT DISTINCT e.expert_id,
        e.expert_name,
        e.sex,
        e.birth_date,
        e.contact,
        e.work_unit,
        e.duty,
        e.politics,
        e.graduate_school,
        e.major,
        e.email,
        e.professional,
        e.professional_acquisition_date,
        e.work_professial,
        e.work_professial_year,
        e.photograph,
        e.perform_work_id,
        e.other_describe,
        e.work_experience,
        e.work_pluralistic,
        e.achievement,
        e.paperpath,
        e.patentpath,
        e.remark,
        e.delete_flag,
        e.tenant_id,
        e.create_dept,
        e.use_flag,
        e.status,
        e.is_recommend,
        e.user_id,
        e.create_by,
        e.create_time,
        e.update_by,
        e.update_time,
        e.approval_type,
        e.unit_type,
        e.id_card,
        e.origin
        FROM zjk_expert e
        LEFT JOIN zjk_expert_major em on em.expert_id = e.expert_id
        LEFT JOIN zjk_expert_professional ep on ep.expert_id = e.expert_id
        <where>
            e.delete_flag='0'
            <if test="bo.status== null ">
                AND e.status NOT IN (0, 4, 5)
            </if>
            <if test="bo.status!=null ">
                and e.status = #{bo.status}
            </if>
            <if test="bo.expertName != null and bo.expertName != ''">
                and e.expert_name like '%' || #{bo.expertName} || '%'
            </if>
            <if test="bo.contact != null and bo.contact !=''">
                AND e.contact like '%' || #{bo.contact} || '%'
            </if>
            <if test="bo.professional != null">
                AND EXISTS (
                SELECT 1
                FROM zjk_expert_professional ep
                WHERE ep.expert_id = e.expert_id
                AND ep.professional = #{bo.professional}
                )
            </if>
            <if test="bo.performWorkId != null and bo.performWorkId !=''">
                AND POSITION(',' || #{bo.performWorkId} || ',' IN ',' || e.perform_work_id || ',') > 0
            </if>
            <if test="bo.degree != null and bo.degree != ''">
                AND EXISTS (
                SELECT 1
                FROM zjk_expert_major em
                WHERE em.expert_id = e.expert_id
                AND em.degree = #{bo.degree}
                )
            </if>
            <if test="bo.workProfessialYear != null">
                AND e.work_professial_year = #{bo.workProfessialYear}
            </if>
            <if test="bo.unitType != null">
                and e.unit_type=#{bo.unitType}
            </if>
            order by e.update_time desc
        </where>
    </select>
    <select id="selectByUserIds" resultType="org.dromara.zjk.domain.vo.ZjkExpertVo">
        select su.nick_name user_name ,su.user_id user_id,dept_name ,ze.*,so.url as avatar
        from  sys_user su
        left join zjk_expert ze on ze.user_id = su.user_id
        left join sys_dept sd on su.dept_id = sd.dept_id
        left join sys_oss so on su.avatar = so.oss_id
        where su.user_id in
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>

    </select>

    <update id="updateStatusById">
        UPDATE zjk_expert
        <set>
            <if test="status != null">
                status = #{status},
            </if>
            <if test="approvalTime != null">
                approval_time= #{approvalTime},
            </if>
            <if test="remark != null">
                remark = #{remark},
            </if>
        </set>
        WHERE expert_id = #{expertId}
    </update>


    <select id="selectExpertAllList" resultType="org.dromara.zjk.domain.ZjkExpert">
        SELECT
            e.*,
            zcu.credit_count
        FROM
            zjk_expert e
                LEFT JOIN zjk_credit_user zcu ON zcu.expert_id = e.expert_id
        WHERE
            status = 2
          AND delete_flag = 0
    </select>

    <select id="selectDataWorkId" resultType="java.lang.String">
        select dict_label from sys_dict_data where dict_type = 'perform_work_type' and dict_value = #{value} and tenant_id = #{tandId}
    </select>

    <select id="expertList" resultType="org.dromara.zjk.domain.vo.ZjkExpertVo">
        SELECT DISTINCT e.expert_id,
        e.expert_name,
        e.sex,
        e.birth_date,
        e.contact,
        e.work_unit,
        e.duty,
        e.politics,
        e.graduate_school,
        e.major,
        e.email,
        e.professional,
        e.professional_acquisition_date,
        e.work_professial,
        e.work_professial_year,
        e.photograph,
        e.perform_work_id,
        e.other_describe,
        e.work_experience,
        e.work_pluralistic,
        e.achievement,
        e.paperpath,
        e.patentpath,
        e.remark,
        e.delete_flag,
        e.tenant_id,
        e.create_dept,
        e.use_flag,
        e.status,
        e.is_recommend,
        e.user_id,
        e.create_by,
        e.create_time,
        e.update_by,
        e.update_time,
        e.approval_type,
        e.unit_type,
        e.id_card,
        e.origin,
        zcr.reward_type,
        e.expert_sources,
        e.position_title_level
        FROM zjk_expert e
        left join zjk_credit_user zcu on zcu.expert_id = e.expert_id
        left join zjk_credit_reward zcr on zcr."ID" = zcu.reward_id
        <where>
            e.delete_flag='0'
            <if test="bo.status != null">
                and e.status = #{bo.status}
            </if>
            <if test="bo.expertName != null and bo.expertName != ''">
                and e.expert_name like '%' || #{bo.expertName} || '%'
            </if>
            <if test="bo.performWorkId != null and bo.performWorkId !=''">
                AND e.perform_work_id like '%' || #{bo.performWorkId} || '%'
            </if>
            <if test="bo.contact != null and bo.contact !=''">
                AND e.contact like '%' || #{bo.contact} || '%'
            </if>
            <if test="bo.workProfessialYear != null">
                AND e.work_professial_year = #{bo.workProfessialYear}
            </if>
            <if test="bo.unitType != null">
                and e.unit_type=#{bo.unitType}
            </if>
            order by e.update_time desc
        </where>
    </select>

    <select id="getExpertBaseInfo" resultType="org.dromara.zjk.domain.ExpertBaseInfo">
        SELECT
            ze.expert_name,
            ze.work_unit,
            ze.duty,
            ze.status,
            zpu.point_count,
            zcl.credit_name,
            ze.perform_work_id
        FROM
            zjk_expert ze
                LEFT JOIN zjk_point_user zpu on zpu.expert_id = ze.expert_id
                LEFT JOIN zjk_credit_user zcu on  zcu.expert_id = ze.expert_id
                LEFT JOIN zjk_credit_lv zcl on zcl."id" = zcu.lv_id
        WHERE ze.user_id = #{userId}
    </select>

    <select id="getExpertPerformWorkCount" resultType="org.dromara.zjk.domain.vo.ExpertPerformWorkCountVO">
        SELECT
            CAST(perform_work_id AS INTEGER) AS performWork,
            COUNT(*) AS COUNT
        FROM (
                 SELECT
                     tenant_id,
                     TRIM(VALUE) AS perform_work_id,
                     update_time
                 FROM
                     zjk_expert,
                     UNNEST(string_to_array(perform_work_id, ',')) AS VALUE
                     <where>
                         status = 2
                         <if test="startDate != null and endDate != null">
                            and update_time BETWEEN #{startDate} AND #{endDate}
                         </if>
                     </where>
             ) AS split_values
        GROUP BY
            perform_work_id
        ORDER BY
            performWork ASC;
    </select>

    <select id="getAgeRangeStatistics" resultType="java.util.Map">
        SELECT
            CASE
                WHEN age BETWEEN 20 AND 24 THEN '20-25'
                WHEN age BETWEEN 25 AND 29 THEN '25-30'
                WHEN age BETWEEN 30 AND 34 THEN '30-35'
                WHEN age BETWEEN 35 AND 39 THEN '35-40'
                WHEN age BETWEEN 40 AND 44 THEN '40-45'
                WHEN age BETWEEN 45 AND 49 THEN '45-50'
                WHEN age BETWEEN 50 AND 54 THEN '50-55'
                WHEN age BETWEEN 55 AND 59 THEN '55-60'
                WHEN age BETWEEN 60 AND 64 THEN '60-65'
                WHEN age BETWEEN 65 AND 70 THEN '65-70'
                ELSE '其他'
                END AS age_range,
            COUNT(*) AS count
        FROM (
            SELECT
            FLOOR(EXTRACT(YEAR FROM age(birth_date))) AS age
            FROM zjk_expert
            WHERE birth_date IS NOT NULL
            AND status = 2
            ) t
        GROUP BY age_range
        ORDER BY age_range;

    </select>
    <select id="getUnitCount" resultType="org.dromara.zjk.domain.vo.ExpertPerformWorkCountVO">
        SELECT
            ifnull(unit_type,0)as unit_type,
            COUNT(1) AS count,
            SUM(COUNT(1)) OVER () AS total_count -- 计算总和
        FROM
            zjk_expert
        where  status = 2
        GROUP BY
            unit_type;
    </select>

    <select id="getPoliticsCount" resultType="org.dromara.zjk.domain.vo.ExpertPerformWorkCountVO">
        SELECT
            politics,
            COUNT( 1 )
        FROM
            zjk_expert
        WHERE
            politics IS NOT NULL  AND status = 2
        GROUP BY
            politics
    </select>
    <select id="workProfessialYear" resultType="java.util.Map">
        SELECT ifnull(work_professial_year,0) as work_professial_year ,count(1)
        FROM zjk_expert
        WHERE status = 2
        GROUP BY work_professial_year
    </select>

    <select id="getApprovalTimeAnalysis" resultType="java.util.Map">
        SELECT
            TO_CHAR( approval_time, 'YYYY-MM-DD' ) AS approval_date,-- 格式化审批时间为日期
            COUNT( * ) AS approval_count -- 统计每天的记录数

        FROM
            zjk_expert
        <where>
            status = 2
            <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
                and approval_time BETWEEN #{startTime} AND #{endTime}
            </if>
        </where>
        GROUP BY
            TO_CHAR( approval_time, 'YYYY-MM-DD' ) -- 按天分组

        ORDER BY
            approval_date;-- 按日期排序
    </select>
    <select id="selectByUserId" resultType="org.dromara.zjk.domain.vo.ZjkExpertVo">
        select su.nick_name user_name ,su.user_id user_id,dept_name ,ze.*,so.url as avatar
        from  sys_user su
        left join zjk_expert ze on ze.user_id = su.user_id
        left join sys_dept sd on su.dept_id = sd.dept_id
        left join sys_oss so on su.avatar = so.oss_id
        where su.user_id =  #{id}
    </select>

    <select id="selectZjkExpertVo" resultType="org.dromara.zjk.domain.vo.ZjkExpertVo">
        SELECT
            ze.*,
            zemc.money_type as positionTitleLevelStr
        FROM
            zjk_expert ze
            LEFT JOIN zjk_expert_money_config zemc ON zemc.money_id = ze.position_title_level
        <where>
            ze.delete_flag = '0'
            <if test="userId != null">
                and ze.user_id = #{userId}
            </if>
            <if test="expertId != null">
                and ze.expert_id = #{expertId}
            </if>
        </where>
    </select>

    <select id="getApprovalTimeCountThisYear" resultType="java.lang.Long">
        SELECT
            COUNT(*)
        FROM
            "zjk_expert"
        WHERE
            EXTRACT(YEAR FROM "approval_time") = EXTRACT(YEAR FROM CURRENT_DATE);
    </select>
    <select id="getPhonesById" resultType="org.dromara.zjk.domain.ZjkExpert">
        select user_id,contact from zjk_expert where 1=1
        <if test="receiveUserIds != null and receiveUserIds.size() > 0">
            AND user_id  IN
            <foreach collection="receiveUserIds" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
    </select>
    <select id="getUserIdByPhone" resultType="java.lang.Long">
        select user_id from zjk_expert where contact=#{tel}
    </select>
    <select id="selectIdsByName" resultType="java.lang.Long">
        select user_id from zjk_expert where expert_name  like '%' || #{expertName} || '%'
    </select>

    <update id="updateHmac">
        UPDATE zjk_expert
        <set>
            <if test="hmac != null">
                hmac = #{hmac},
            </if>
        </set>
        WHERE expert_id = #{expertId}
    </update>
</mapper>
