<?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.springblade.modules.jwtdSpglDhjl.mapper.JwtdSpglDhjlMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="jwtdSpglDhjlResultMap" type="org.springblade.modules.jwtdSpglDhjl.pojo.entity.JwtdSpglDhjlEntity">
        <result column="ddbh" property="ddbh"/>
        <result column="user_id" property="userId"/>
        <result column="sp_id" property="spId"/>
        <result column="xhjf" property="xhjf"/>
        <result column="lqzt" property="lqzt"/>
        <result column="update_dept" property="updateDept"/>
    </resultMap>

    <select id="selectJwtdSpglDhjlPage" resultMap="jwtdSpglDhjlResultMap">
        SELECT
            jsd.ddbh,
            bu.name,
            bu.sfzh,
            bu.phone,
            bu.dz,
            SUM ( jsd.spsl ) AS spsl,
            SUM ( jsd.xhjf ) AS xhjf,
            jsd.lqzt,
            jsd.create_time,
            jsd.update_time,
            fh.name as fhrname,
            bd.dept_name as fhdept
        FROM
            jwtd_spgl_dhjl jsd
        LEFT JOIN blade_user bu ON bu.id = jsd.user_id
        LEFT JOIN blade_user fh ON fh.id = jsd.fhr
        LEFT JOIN blade_dept bd ON bd.id = jsd.update_dept
        WHERE jsd.is_deleted = 0
        <if test="jwtdSpglDhjl.ddbh != null and jwtdSpglDhjl.ddbh != ''">
            and jsd.ddbh = #{jwtdSpglDhjl.ddbh}
        </if>
        <if test="jwtdSpglDhjl.name != null and jwtdSpglDhjl.name != ''">
            and bu.name = #{jwtdSpglDhjl.name}
        </if>
        <if test="jwtdSpglDhjl.sfzh != null and jwtdSpglDhjl.sfzh != ''">
            and bu.sfzh = #{jwtdSpglDhjl.sfzh}
        </if>
        GROUP BY
            jsd.ddbh,
            bu.name,
            bu.sfzh,
            bu.phone,
            bu.dz,
            jsd.lqzt,
            jsd.create_time,
            jsd.update_time,
            fh.name,
            bd.dept_name
    </select>

    <select id="exportJwtdSpglDhjl" resultType="org.springblade.modules.jwtdSpglDhjl.excel.JwtdSpglDhjlExcel">
        SELECT * FROM jwtd_spgl_dhjl ${ew.customSqlSegment}
    </select>

    <select id="detailByDdbh" resultMap="jwtdSpglDhjlResultMap">
        SELECT
            jsd.ddbh,
            jsd.spzt,
            jsd.create_time,
            jsd.update_time,
            bu.name,
            bu.sfzh,
            bu.phone,
            bu.dz,
            bd.dept_name,
            bu1.name as fhName
        FROM jwtd_spgl_dhjl jsd
        LEFT JOIN blade_user bu ON bu.id = jsd.user_id
        LEFT JOIN blade_user bu1 ON bu1.id = jsd.fhr
        LEFT JOIN blade_dept bd ON bd.id = jsd.update_dept
        WHERE jsd.is_deleted = 0 AND jsd.ddbh = #{ddbh} order by create_time limit 1
    </select>

    <select id="detailListByDdbh" resultMap="jwtdSpglDhjlResultMap">
        SELECT
            jsd.sp_id,
            js.spmc,
            js.spms,
            jsd.spsl,
            jsd.xhjf
        FROM jwtd_spgl_dhjl jsd
        LEFT JOIN jwtd_spgl js ON js.ID = jsd.sp_id
        WHERE jsd.is_deleted = 0 AND jsd.ddbh = #{ddbh}
    </select>

    <update id="updateLqzt">
        update jwtd_spgl_dhjl set lqzt = 2, update_dept = #{deptId}, fhr = #{userId} where ddbh = #{ddbh}
    </update>

    <select id="getDdList" resultMap="jwtdSpglDhjlResultMap">
        select
            jsd.sp_id,
            js.spmc,
            jsd.xhjf,
            js.lqdd,
            jsd.create_time,
            jsd.lqzt,
            jsd.ddbh
        from jwtd_spgl_dhjl jsd
        left join jwtd_spgl js on js.id = jsd.sp_id
        where jsd.user_id = #{userId}
        <if test="type == '待领取'">
            and jsd.lqzt = '2'
        </if>
        <if test="type == '历史'">
            and (jsd.lqzt = '3' or jsd.lqzt = '4')
        </if>
        <if test="spmc != null and spmc != ''">
            and jsd.spmc like '%' || #{spmc} || '%'
        </if>
    </select>

    <update id="updateLqsp">
        update jwtd_spgl_dhjl set lqzt = 3, update_time = now() where ddbh = #{ddbh}
    </update>

    <insert id="saveDhjl">
        insert into jwtd_spgl_dhjl(id, ddbh, user_id, sp_id, spsl, xhjf, lqzt, create_user, create_dept, create_time, update_user, update_time)
        values (#{id}, #{ddbh}, #{userId}, #{spId}, #{xhjf}, #{lqzt}, #{createUser}, #{createDept}, #{createTime}, #{updateUser}, #{updateTime})
    </insert>
</mapper>
