<?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.qianhe.zqcz.zsrw.mapper.ZqczZsrwMapper">
    <!--  返回实体  -->
    <resultMap id="ZqczZsrwResult" type="com.qianhe.zqcz.zsrw.domain.ZqczZsrwVo">
        <id property="zsrwid" column="zsrwid"/>
        <result property="rwmc" column="rwmc"/>
        <result property="deptId" column="dept_id"/>
        <result property="deptName" column="dept_name"/>
        <result property="rwkssj" column="rwkssj"/>
        <result property="rwjssj" column="rwjssj"/>
        <result property="glqsl" column="glqsl"/>
        <result property="cjglqsl" column="cjglqsl"/>
        <result property="zsrwjd" column="zsrwjd"/>
        <result property="remark" column="remark"/>
        <result property="jccjzt" column="jccjzt"/>
    </resultMap>
    <!--  通用查询  -->
    <sql id="selectCommon">
        SELECT
            zsrw.zsrwid,
            dept.dept_id,
            dept.dept_name,
            zsrw.rwmc,
            IFNULL(zsrw.cjglqsl, 0) AS cjglqsl,
            zsrw.rwkssj,
            zsrw.rwjssj,
            zsrw.remark,
            zsrw.jccjzt,
            IFNULL(jcdw_count.glqsl, 0) AS glqsl,
            IFNULL(jcrw_count.jcrwsl, 0) AS jcrwsl,
            IFNULL(jcrw_count.jcrwwcsl, 0) AS jcrwwcsl,
            CONCAT(IFNULL(jcrw_count.jcrwwcsl, 0), '/', IFNULL(jcrw_count.jcrwsl, 0)) AS zsrwjd
        FROM
            zqcz_zsrw zsrw
            LEFT JOIN sys_dept dept ON zsrw.dept_id = dept.dept_id
            LEFT JOIN (SELECT zsrwid, COUNT(*) AS glqsl FROM zqcz_jcdw GROUP BY zsrwid) jcdw_count ON zsrw.zsrwid = jcdw_count.zsrwid
            LEFT JOIN ( SELECT zsrwid, COUNT(jcrwid) AS jcrwsl, SUM(CASE WHEN jcrwzt = '7' THEN 1 ELSE 0 END) AS jcrwwcsl FROM zqcz_jcrw GROUP BY zsrwid) jcrw_count ON zsrw.zsrwid = jcrw_count.zsrwid
    </sql>
    <!--  条件查询  -->
    <select id="list" parameterType="com.qianhe.zqcz.zsrw.domain.ZqczZsrwQuery" resultMap="ZqczZsrwResult">
        <include refid="selectCommon"></include>
        <where>
            <if test="deptId != null and deptId != ''">AND zsrw.dept_id = #{deptId}</if>
            <if test="rwmc != null and rwmc != ''">AND zsrw.rwmc like concat('%', #{rwmc}, '%')</if>
            <if test="rwkssj != null">AND zsrw.rwkssj &gt;= #{rwkssj}</if>
            <if test="rwjssj != null">AND zsrw.rwjssj &lt;= #{rwjssj}</if>
            ${params.dataScope}
        </where>
        ORDER BY
            zsrw.rwkssj DESC
    </select>
    <!--  查询详情  -->
    <select id="info" parameterType="long" resultMap="ZqczZsrwResult">
        <include refid="selectCommon"></include> WHERE zsrw.zsrwid = #{zsrwid}
    </select>
    <!--  新增  -->
    <insert id="add" parameterType="com.qianhe.zqcz.zsrw.domain.ZqczZsrw">
        INSERT INTO zqcz_zsrw
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="rwmc != null and rwmc != ''">rwmc,</if>
            <if test="deptId != null and deptId != ''">dept_id,</if>
            <if test="rwkssj != null">rwkssj,</if>
            <if test="rwjssj != null">rwjssj,</if>
            <if test="glqsl != null">glqsl,</if>
            <if test="cjglqsl != null">cjglqsl,</if>
            <if test="jccjzt != null and jccjzt != ''">jccjzt,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="remark != null and remark != ''">remark,</if>
            <if test="yl1 != null and yl1 != ''">yl1,</if>
            <if test="yl2 != null and yl2 != ''">yl2,</if>
            <if test="yl3 != null and yl3 != ''">yl3,</if>
            <if test="yl4 != null and yl4 != ''">yl4,</if>
            <if test="yl5 != null and yl5 != ''">yl5,</if>
            update_time
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="rwmc != null and rwmc != ''">#{rwmc},</if>
            <if test="deptId != null and deptId != ''">#{deptId},</if>
            <if test="rwkssj != null">#{rwkssj},</if>
            <if test="rwjssj != null">#{rwjssj},</if>
            <if test="glqsl != null">#{glqsl},</if>
            <if test="cjglqsl != null">#{cjglqsl},</if>
            <if test="jccjzt != null and jccjzt != ''">#{jccjzt},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="remark != null and remark != ''">#{remark},</if>
            <if test="yl1 != null and yl1 != ''">#{yl1},</if>
            <if test="yl2 != null and yl2 != ''">#{yl2},</if>
            <if test="yl3 != null and yl3 != ''">#{yl3},</if>
            <if test="yl4 != null and yl4 != ''">#{yl4},</if>
            <if test="yl5 != null and yl5 != ''">#{yl5},</if>
            now()
        </trim>
    </insert>
    <!--  修改  -->
    <update id="edit" parameterType="com.qianhe.zqcz.zsrw.domain.ZqczZsrw">
        UPDATE zqcz_zsrw
        <set>
            <if test="rwmc != null and rwmc != ''">rwmc=#{rwmc},</if>
            <if test="deptId != null and deptId != ''">dept_id=#{deptId},</if>
            <if test="rwkssj != null">rwkssj=#{rwkssj},</if>
            <if test="rwjssj != null">rwjssj=#{rwjssj},</if>
            <if test="glqsl != null">glqsl=#{glqsl},</if>
            <if test="cjglqsl != null">cjglqsl=#{cjglqsl},</if>
            <if test="jccjzt != null and jccjzt != ''">jccjzt=#{jccjzt},</if>
            <if test="createBy != null">create_by=#{createBy},</if>
            <if test="createTime != null">create_time=#{createTime},</if>
            <if test="updateBy != null">update_by=#{updateBy},</if>
            <if test="remark != null and remark != ''">remark=#{remark},</if>
            <if test="yl1 != null and yl1 != ''">yl1=#{yl1},</if>
            <if test="yl2 != null and yl2 != ''">yl2=#{yl2},</if>
            <if test="yl3 != null and yl3 != ''">yl3=#{yl3},</if>
            <if test="yl4 != null and yl4 != ''">yl4=#{yl4},</if>
            <if test="yl5 != null and yl5 != ''">yl5=#{yl5},</if>
            update_time=now()
        </set>
        WHERE zsrwid=#{zsrwid}
    </update>
    <!--  删除  -->
    <delete id="delete" parameterType="long">
        DELETE FROM zqcz_zsrw WHERE zsrwid IN
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
    <!--  重新修改基层单位总数  -->
    <update id="computeGlqsl">
        UPDATE zqcz_zsrw SET glqsl = glqsl + #{count} WHERE zsrwid = #{zsrwid}
    </update>
</mapper>