<?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.srs.workstudy.mapper.QgzxWorkLogMapper">

    <resultMap type="QgzxWorkLog" id="QgzxWorkLogResult">
        <result property="id" column="id"/>
        <result property="stuPostId" column="stu_post_id"/>
        <result property="workTime" column="work_time"/>
        <result property="mainWork" column="main_work"/>
        <result property="workMaterial" column="work_material"/>
        <result property="workDate" column="work_date"/>
        <result property="applyTime" column="apply_time"/>
        <result property="applyStatus" column="apply_status"/>
        <result property="zdlsCmt" column="zdls_cmt"/>
        <result property="zdlsSign" column="zdls_sign"/>
        <result property="zdlsTime" column="zdls_time"/>
    </resultMap>

    <sql id="selectQgzxWorkLogVo">
        select id,
               stu_post_id,
               work_time,
               main_work,
               work_material,
               work_date,
               apply_time,
               apply_status,
               zdls_cmt,
               zdls_sign,
               zdls_time
        from qgzx_work_log
    </sql>

    <select id="selectQgzxWorkLogList" parameterType="QgzxWorkLog" resultMap="QgzxWorkLogResult">
        <include refid="selectQgzxWorkLogVo"/>
        <where>
            <if test="stuPostId != null ">and stu_post_id = #{stuPostId}</if>
            <if test="workTime != null ">and work_time = #{workTime}</if>
            <if test="mainWork != null  and mainWork != ''">and main_work = #{mainWork}</if>
            <if test="workMaterial != null  and workMaterial != ''">and work_material = #{workMaterial}</if>
            <if test="workDate != null ">and work_date = #{workDate}</if>
            <if test="applyTime != null ">and apply_time = #{applyTime}</if>
            <if test="applyStatus != null  and applyStatus != ''">and apply_status = #{applyStatus}</if>
            <if test="zdlsCmt != null  and zdlsCmt != ''">and zdls_cmt = #{zdlsCmt}</if>
            <if test="zdlsSign != null  and zdlsSign != ''">and zdls_sign = #{zdlsSign}</if>
        </where>
    </select>

    <select id="selectQgzxWorkLogById" parameterType="Long" resultMap="QgzxWorkLogResult">
        <include refid="selectQgzxWorkLogVo"/>
        where id = #{id}
    </select>

    <insert id="insertQgzxWorkLog" parameterType="QgzxWorkLog" useGeneratedKeys="true" keyProperty="id">
        insert into qgzx_work_log
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="stuPostId != null">stu_post_id,</if>
            <if test="workTime != null">work_time,</if>
            <if test="mainWork != null">main_work,</if>
            <if test="workMaterial != null">work_material,</if>
            <if test="workDate != null">work_date,</if>
            <if test="applyTime != null">apply_time,</if>
            <if test="applyStatus != null">apply_status,</if>
            <if test="zdlsCmt != null">zdls_cmt,</if>
            <if test="zdlsSign != null">zdls_sign,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="stuPostId != null">#{stuPostId},</if>
            <if test="workTime != null">#{workTime},</if>
            <if test="mainWork != null">#{mainWork},</if>
            <if test="workMaterial != null">#{workMaterial},</if>
            <if test="workDate != null">#{workDate},</if>
            <if test="applyTime != null">#{applyTime},</if>
            <if test="applyStatus != null">#{applyStatus},</if>
            <if test="zdlsCmt != null">#{zdlsCmt},</if>
            <if test="zdlsSign != null">#{zdlsSign},</if>
        </trim>
    </insert>

    <update id="updateQgzxWorkLog" parameterType="QgzxWorkLog">
        update qgzx_work_log
        <trim prefix="SET" suffixOverrides=",">
            <if test="stuPostId != null">stu_post_id = #{stuPostId},</if>
            <if test="workTime != null">work_time = #{workTime},</if>
            <if test="mainWork != null">main_work = #{mainWork},</if>
            <if test="workMaterial != null">work_material = #{workMaterial},</if>
            <if test="workDate != null">work_date = #{workDate},</if>
            <if test="applyTime != null">apply_time = #{applyTime},</if>
            <if test="applyStatus != null">apply_status = #{applyStatus},</if>
            <if test="zdlsCmt != null">zdls_cmt = #{zdlsCmt},</if>
            <if test="zdlsSign != null">zdls_sign = #{zdlsSign},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteQgzxWorkLogById" parameterType="Long">
        delete
        from qgzx_work_log
        where id = #{id}
    </delete>

    <delete id="deleteQgzxWorkLogByIds" parameterType="String">
        delete from qgzx_work_log where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <select id="listView" parameterType="QgzxWorkLog" resultType="QgzxWorkLog">
        select a.*,c.post_name,c.dept_code,b.stu_no,d.stu_name,d.dept_name as stu_dept,e.dept_name,c.zdls_name,c.post_type
        from qgzx_work_log as a
        left join qgzx_stu_post as b on a.stu_post_id = b.id
        left join qgzx_post as c on b.post_id = c.id
        left join view_stu_info as d on b.stu_no = d.stu_no
        left join sys_dept as e on c.dept_code = e.dept_code
        <where>
            <if test="stuPostId != null ">and a.stu_post_id = #{stuPostId}</if>
            <if test="workDate != null ">and a.work_date = #{workDate}</if>
            <if test="workMonth != null ">and date_format(a.work_date, '%Y-%m') = date_format(#{workMonth}, '%Y-%m')</if>
            <if test="applyStatus != null  and applyStatus != ''">and a.apply_status = #{applyStatus}</if>
            <if test="stuNo != null  and stuNo != ''">and b.stu_no = #{stuNo}</if>
            <if test="stuName != null  and stuName != ''">and d.stu_name like concat('%',#{stuName},'%')</if>
            <if test="stuDept != null  and stuDept != ''">and d.dept_name like concat('%',#{stuDept},'%')</if>
            <if test="zdlsNo != null  and zdlsNo != ''">and b.zdls_no = #{zdlsNo}</if>
        </where>
        order by a.id desc
    </select>

    <select id="computeMonthTime" parameterType="QgzxWorkLog" resultType="QgzxWorkLog">
        select sum(a.work_time) as work_time
        from qgzx_work_log as a
                 left join qgzx_stu_post as b on a.stu_post_id = b.id
        WHERE date_format(a.work_date, '%Y-%m') = date_format(now(), '%Y-%m')
          and b.stu_no = #{stuNo}
          and a.apply_status not in (0, 10)
    </select>

    <select id="listWorkLogMonthTotal" resultType="HashMap" parameterType="QgzxWorkLog">
        select
            t1.*,t2.stu_no,t4.stu_name,t3.post_name,t3.post_type,t5.dept_name,t4.dept_name as stu_dept
        from (
                 select a.stu_post_id, date_format(a.work_date, '%Y-%m') as work_month,sum(a.work_time) as total_time
                 from qgzx_work_log as a
                 where a.apply_status = 2
                 GROUP BY a.stu_post_id, date_format(a.work_date, '%Y-%m')
             ) as t1
                 left join qgzx_stu_post as t2 on t1.stu_post_id = t2.id
                 left join qgzx_post as t3 on t2.post_id = t3.id
                 left join view_stu_info as t4 on t2.stu_no = t4.stu_no
                 left join sys_dept as t5 on t3.dept_code = t5.dept_code
        <where>
            <if test="stuPostId != null ">and t1.stu_post_id = #{stuPostId}</if>
            <if test="workMonth != null ">and t1.work_month =  date_format(#{workMonth}, '%Y-%m')</if>
            <if test="stuNo != null  and stuNo != ''">and t4.stu_no = #{stuNo}</if>
            <if test="stuName != null  and stuName != ''">and t4.stu_name like concat('%',#{stuName},'%')</if>
            <if test="stuDept != null  and stuDept != ''">and t4.dept_name like concat('%',#{stuDept},'%')</if>
            <if test="deptName != null  and deptName != ''">and t5.dept_name like concat('%',#{deptName},'%')</if>
            <if test="postName != null  and postName != ''">and t3.post_name like concat('%',#{postName},'%')</if>
        </where>
        order by t1.stu_post_id asc,t1.work_month desc

    </select>


</mapper>
