<?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.QgzxMoneyMapper">

    <resultMap type="QgzxMoney" id="QgzxMoneyResult">
        <result property="id" column="id"/>
        <result property="stuPostId" column="stu_post_id"/>
        <result property="totalTime" column="total_time"/>
        <result property="applySign" column="apply_sign"/>
        <result property="applyNo" column="apply_no"/>
        <result property="applyTime" column="apply_time"/>
        <result property="applyStatus" column="apply_status"/>
        <result property="deptSign" column="dept_sign"/>
        <result property="deptCmt" column="dept_cmt"/>
        <result property="deptNo" column="dept_no"/>
        <result property="highNo" column="high_no"/>
        <result property="highCmt" column="high_cmt"/>
        <result property="highSign" column="high_sign"/>
        <result property="finalNo" column="final_no"/>
        <result property="finalCmt" column="final_cmt"/>
        <result property="finalSign" column="final_sign"/>
        <result property="auditRemark" column="audit_remark"/>
    </resultMap>

    <sql id="selectQgzxMoneyVo">
        select id,
               stu_post_id,
               total_time,
               apply_sign,
               apply_no,
               apply_time,
               apply_status,
               dept_sign,
               dept_cmt,
               dept_no,
               high_no,
               high_cmt,
               high_sign,
               final_no,
               final_cmt,
               final_sign,
               audit_remark
        from qgzx_money
    </sql>

    <select id="selectQgzxMoneyList" parameterType="QgzxMoney" resultMap="QgzxMoneyResult">
        <include refid="selectQgzxMoneyVo"/>
        <where>
            <if test="stuPostId != null ">and stu_post_id = #{stuPostId}</if>
            <if test="totalTime != null ">and total_time = #{totalTime}</if>
            <if test="applySign != null  and applySign != ''">and apply_sign = #{applySign}</if>
            <if test="applyNo != null  and applyNo != ''">and apply_no = #{applyNo}</if>
            <if test="applyTime != null ">and apply_time = #{applyTime}</if>
            <if test="applyStatus != null  and applyStatus != ''">and apply_status = #{applyStatus}</if>
            <if test="deptSign != null  and deptSign != ''">and dept_sign = #{deptSign}</if>
            <if test="deptCmt != null  and deptCmt != ''">and dept_cmt = #{deptCmt}</if>
            <if test="deptNo != null  and deptNo != ''">and dept_no = #{deptNo}</if>
            <if test="highNo != null  and highNo != ''">and high_no = #{highNo}</if>
            <if test="highCmt != null  and highCmt != ''">and high_cmt = #{highCmt}</if>
            <if test="highSign != null  and highSign != ''">and high_sign = #{highSign}</if>
            <if test="finalNo != null  and finalNo != ''">and final_no = #{finalNo}</if>
            <if test="finalCmt != null  and finalCmt != ''">and final_cmt = #{finalCmt}</if>
            <if test="finalSign != null  and finalSign != ''">and final_sign = #{finalSign}</if>
            <if test="auditRemark != null  and auditRemark != ''">and audit_remark = #{auditRemark}</if>
        </where>
    </select>

    <select id="selectQgzxMoneyById" parameterType="Long" resultMap="QgzxMoneyResult">
        <include refid="selectQgzxMoneyVo"/>
        where id = #{id}
    </select>

    <insert id="insertQgzxMoney" parameterType="QgzxMoney" useGeneratedKeys="true" keyProperty="id">
        insert into qgzx_money
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="stuPostId != null">stu_post_id,</if>
            <if test="totalTime != null">total_time,</if>
            <if test="applySign != null">apply_sign,</if>
            <if test="applyNo != null">apply_no,</if>
            <if test="applyTime != null">apply_time,</if>
            <if test="applyStatus != null">apply_status,</if>
            <if test="deptSign != null">dept_sign,</if>
            <if test="deptCmt != null">dept_cmt,</if>
            <if test="deptNo != null">dept_no,</if>
            <if test="highNo != null">high_no,</if>
            <if test="highCmt != null">high_cmt,</if>
            <if test="highSign != null">high_sign,</if>
            <if test="finalNo != null">final_no,</if>
            <if test="finalCmt != null">final_cmt,</if>
            <if test="finalSign != null">final_sign,</if>
            <if test="auditRemark != null">audit_remark,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="stuPostId != null">#{stuPostId},</if>
            <if test="totalTime != null">#{totalTime},</if>
            <if test="applySign != null">#{applySign},</if>
            <if test="applyNo != null">#{applyNo},</if>
            <if test="applyTime != null">#{applyTime},</if>
            <if test="applyStatus != null">#{applyStatus},</if>
            <if test="deptSign != null">#{deptSign},</if>
            <if test="deptCmt != null">#{deptCmt},</if>
            <if test="deptNo != null">#{deptNo},</if>
            <if test="highNo != null">#{highNo},</if>
            <if test="highCmt != null">#{highCmt},</if>
            <if test="highSign != null">#{highSign},</if>
            <if test="finalNo != null">#{finalNo},</if>
            <if test="finalCmt != null">#{finalCmt},</if>
            <if test="finalSign != null">#{finalSign},</if>
            <if test="auditRemark != null">#{auditRemark},</if>
        </trim>
    </insert>

    <update id="updateQgzxMoney" parameterType="QgzxMoney">
        update qgzx_money
        <trim prefix="SET" suffixOverrides=",">
            <if test="stuPostId != null">stu_post_id = #{stuPostId},</if>
            <if test="totalTime != null">total_time = #{totalTime},</if>
            <if test="applySign != null">apply_sign = #{applySign},</if>
            <if test="applyNo != null">apply_no = #{applyNo},</if>
            <if test="applyTime != null">apply_time = #{applyTime},</if>
            <if test="applyStatus != null">apply_status = #{applyStatus},</if>
            <if test="deptSign != null">dept_sign = #{deptSign},</if>
            <if test="deptCmt != null">dept_cmt = #{deptCmt},</if>
            <if test="deptNo != null">dept_no = #{deptNo},</if>
            <if test="highNo != null">high_no = #{highNo},</if>
            <if test="highCmt != null">high_cmt = #{highCmt},</if>
            <if test="highSign != null">high_sign = #{highSign},</if>
            <if test="finalNo != null">final_no = #{finalNo},</if>
            <if test="finalCmt != null">final_cmt = #{finalCmt},</if>
            <if test="finalSign != null">final_sign = #{finalSign},</if>
            <if test="auditRemark != null">audit_remark = #{auditRemark},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteQgzxMoneyById" parameterType="Long">
        delete
        from qgzx_money
        where id = #{id}
    </delete>

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

    <select id="countUnMoney" parameterType="QgzxMoney" resultType="map">
        select (select count(a.id)
                from qgzx_stu_post as a
                         left join qgzx_post as b on a.post_id = b.id
                where a.work_status = '是'
                  and b.zdls_no = #{applyNo})         as all_stu_post,

               (select count(a.id)
                from qgzx_money 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
                where a.apply_time like concat(date_format(#{applyTime}, '%Y-%m'), '%')
                  and c.zdls_no = #{applyNo}
                  and a.apply_status in (1, 2, 3, 6)) as money_stu_post
    </select>

    <select id="listAuditByDept" parameterType="QgzxMoney" resultType="DeptPostCount">
        select
        t1.*,t2.dept_name
        from
        (
        select count(a.id) as number,c.dept_code
        from qgzx_money 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
        <where>
            <if test="applyStatus != null  and applyStatus != ''">and a.apply_status = #{applyStatus}</if>
        </where>
        GROUP BY c.dept_code
        ) as t1
        left join sys_dept as t2 on t1.dept_code = t2.dept_code
    </select>

    <select id="listOld"  parameterType="QgzxMoney" resultType="QgzxMoney">
        select a.*,b.stu_no,c.post_name,d.dept_name,e.dept_name as stu_dept,
        e.stu_name,e.class_name,e.grade_name,f.name as dept_no_name,
        g.name as high_name,h.name as final_name,
        c.work_need,b.bank_card,d.dept_id,b.family_condition,c.post_type,b.id_card,b.post_id,
        case a.apply_status
        when 0
        then "保存"
        when 1
        then "已提交待审核"
        when 2
        then "部门审核通过"
        when 3
        then "资助办审核通过"
        when 6
        then "学工审核通过"
        when 10
        then "拒绝"
        else "无" end as apply_status_ex
        from qgzx_money 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 sys_dept as d on c.dept_code = d.dept_code
        left join view_stu_info as e on b.stu_no = e.stu_no
        left join qgzx_teacher as f on a.dept_no = f.employee_id
        left join qgzx_teacher as g on a.high_no = g.employee_id
        left join qgzx_teacher as h on a.final_no = h.employee_id

        <where>
            <if test="stuPostId != null ">and a.stu_post_id = #{stuPostId}</if>
            <if test="deptId != null ">and d.dept_id = #{deptId}</if>
            <if test="applyNo != null  and applyNo != ''">and a.apply_no = #{applyNo}</if>
            <if test="stuNo != null  and stuNo != ''">and b.stu_no = #{stuNo}</if>
            <if test="applyTime != null">and a.apply_time like concat( date_format(#{applyTime}, '%Y-%m'),'%')</if>
            <if test="applyStatus != null  and applyStatus != ''">and a.apply_status = #{applyStatus}</if>
            <if test="deptName != null  and deptName != ''">and d.dept_name like concat('%',#{deptName},'%')</if>
            <if test="stuDept != null  and stuDept != ''">and e.dept_name like concat('%',#{stuDept},'%')</if>
            <if test="postName != null  and postName != ''">and c.post_name like concat('%',#{postName},'%')</if>
            <if test="stuName != null  and stuName != ''">and e.stu_name like concat('%',#{stuName},'%')</if>
        </where>
        order by a.apply_time DESC
    </select>

    <select id="listView" parameterType="QgzxMoney" resultType="QgzxMoney">
        select * from (
        select t2.* from (
        select a.*,b.stu_no,c.post_name,d.dept_name,e.dept_name as stu_dept,
        e.stu_name,e.class_name,e.grade_name,f.name as dept_no_name,
        g.name as high_name,h.name as final_name,
        c.work_need,b.bank_card,d.dept_id,b.family_condition,c.post_type,b.id_card,
        case a.apply_status
        when 0
        then "保存"
        when 1
        then "已提交待审核"
        when 2
        then "部门审核通过"
        when 3
        then "资助办审核通过"
        when 6
        then "学工审核通过"
        when 10
        then "拒绝"
        else "无" end as apply_status_ex
        from qgzx_money 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 sys_dept as d on c.dept_code = d.dept_code
        left join view_stu_info as e on b.stu_no = e.stu_no
        left join qgzx_teacher as f on a.dept_no = f.employee_id
        left join qgzx_teacher as g on a.high_no = g.employee_id
        left join qgzx_teacher as h on a.final_no = h.employee_id

        <where>
            <if test="stuPostId != null ">and a.stu_post_id = #{stuPostId}</if>
            <if test="deptId != null ">and d.dept_id = #{deptId}</if>
            <if test="applyNo != null  and applyNo != ''">and a.apply_no = #{applyNo}</if>
            <if test="stuNo != null  and stuNo != ''">and b.stu_no = #{stuNo}</if>
            <if test="applyTime != null">and a.apply_time like concat( date_format(#{applyTime}, '%Y-%m'),'%')</if>
            <if test="applyStatus != null  and applyStatus != ''">and a.apply_status = #{applyStatus}</if>
            <if test="deptName != null  and deptName != ''">and d.dept_name like concat('%',#{deptName},'%')</if>
            <if test="stuDept != null  and stuDept != ''">and e.dept_name like concat('%',#{stuDept},'%')</if>
            <if test="postName != null  and postName != ''">and c.post_name like concat('%',#{postName},'%')</if>
            <if test="stuName != null  and stuName != ''">and e.stu_name like concat('%',#{stuName},'%')</if>
        </where>
        order by a.id DESC
        ) as t2
        union
        select t1.* from
        (
        select a.id,null as stu_post_id,a.total_time,a.final_sign as apply_sign,a.final_no as
        apply_no,a.apply_time,a.apply_status,
        null as dept_sign,null as dept_cmt,null as dept_time,null as dept_no,null as high_time,null as high_no,null as
        high_cmt,null as high_sign,
        a.final_no,a.final_time,"同意" as final_cmt,a.final_sign,a.audit_remark,null as
        money,a.stu_no,a.post_name,b.dept_name,c.dept_name as stu_dept,
        c.stu_name,c.class_name,c.grade_name,null as dept_no_name,null as high_name,e.`name` as
        final_name,a.work_need,a.bank_card,e.dept_id,
        a.family_condition,a.post_type,c.id_card,null as apply_status_ex
        from qgzx_money_extra as a
        left join sys_dept as b on a.dept_code = b.dept_code
        left join view_stu_info as c on a.stu_no = c.stu_no
        left join qgzx_teacher as d on a.zdls_no = d.employee_id
        left join qgzx_teacher as e on a.final_no = e.employee_id

        <where>
            <if test="deptId != null ">and b.dept_id = #{deptId}</if>
            <if test="stuNo != null  and stuNo != ''">and a.stu_no = #{stuNo}</if>
            <if test="applyTime != null">and a.apply_time like concat( date_format(#{applyTime}, '%Y-%m'),'%')</if>
            <if test="applyStatus != null  and applyStatus != ''">and a.apply_status = #{applyStatus}</if>
            <if test="deptName != null  and deptName != ''">and b.dept_name like concat('%',#{deptName},'%')</if>
            <if test="stuDept != null  and stuDept != ''">and c.dept_name like concat('%',#{stuDept},'%')</if>
            <if test="postName != null  and postName != ''">and a.post_name like concat('%',#{postName},'%')</if>
            <if test="stuName != null  and stuName != ''">and c.stu_name like concat('%',#{stuName},'%')</if>
        </where>
            order by a.id DESC
            ) as t1
        ) as t3
        ORDER BY
        t3.id DESC
    </select>


    <select id="listCount" resultType="DeptPostCount">
        SELECT t2.dept_name,
               t1.*
        from (select a1.*,
                     b1.stu_post_count,
                     c1.month_time_count
              from (select a.dept_code, count(a.id) as post_count
                    from qgzx_post as a
                    where a.apply_status = 3 and post_type = "固定岗位B"
                    GROUP BY a.dept_code) as a1
                       left join
                   (select count(a.id) as stu_post_count, b.dept_code
                    from qgzx_stu_post as a
                             left join qgzx_post as b on a.post_id = b.id
                    where a.apply_status = "2"
                      and a.work_status = "是"
                    GROUP BY b.dept_code) as b1 on a1.dept_code = b1.dept_code
                       left join
                   (select c.dept_code, sum(a.total_time) as month_time_count
                    from qgzx_money 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
                    where a.apply_status = 6
                              and MONTH(a.apply_time) = MONTH(NOW())
              GROUP BY c.dept_code) as c1 on a1.dept_code = c1.dept_code) as t1
                 left join sys_dept as t2 on t1.dept_code = t2.dept_code
    </select>


</mapper>