<?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.ruoyi.project.benyi.mapper.ByDayflowassessmentMapper">

    <resultMap type="ByDayflowassessment" id="ByDayflowassessmentResult">
        <result property="id" column="id"/>
        <result property="planid" column="planid"/>
        <result property="deptId" column="dept_id"/>
        <result property="classid" column="classid"/>
        <result property="bzbh" column="bzbh"/>
        <result property="bzxm" column="bzxm"/>
        <result property="pbbh" column="pbbh"/>
        <result property="pbxm" column="pbxm"/>
        <result property="zlbh" column="zlbh"/>
        <result property="zlxm" column="zlxm"/>
        <result property="xnxq" column="xnxq"/>
        <result property="bzid" column="bzid"/>
        <result property="bzmf" column="bzmf"/>
        <result property="zzdf" column="zzdf"/>
        <result property="kfz" column="kfz"/>
        <result property="classdf" column="classdf"/>
        <result property="items" column="items"/>
        <result property="values" column="values"/>
        <result property="kfcs" column="kfcs"/>
        <result property="pgdx" column="pgdx"/>
        <result property="pgdxxm" column="pgdxxm"/>
        <result property="createUserid" column="create_userid"/>
        <result property="createTime" column="create_time"/>
        <result property="remark" column="remark"/>
        <result property="status"    column="status"    />
        <result property="bjpjf" column="bjpjf"/>
        <result property="zjjdpjf" column="zjjdpjf"/>
        <result property="ycpjf" column="ycpjf"/>
        <result property="zjzqpjf" column="zjzqpjf"/>
        <result property="fzjxpjf" column="fzjxpjf"/>
        <result property="dxsjpjf" column="dxsjpjf"/>
        <result property="rcxsyspjf" column="rcxsyspjf"/>
        <result property="hdgdpjf" column="hdgdpjf"/>
        <result property="hwhdpjf" column="hwhdpjf"/>
        <result property="wspjf" column="wspjf"/>
        <result property="lyzjpjf" column="lyzjpjf"/>
        <result property="aqpjf" column="aqpjf"/>
        <result property="zyhdpjf" column="zyhdpjf"/>
        <result property="gzyjlyspjf" column="gzyjlyspjf"/>
        <result property="wxkcpjf" column="wxkcpjf"/>
        <result property="qkcpjf" column="qkcpjf"/>
        <association property="byClass" column="classid" javaType="ByClass" resultMap="ByClassResult"/>
    </resultMap>

    <resultMap type="ByClass" id="ByClassResult">
        <result property="bjbh" column="bjbh"/>
        <result property="deptId" column="dept_id"/>
        <result property="bjtype" column="bjtype"/>
        <result property="bhxh" column="bhxh"/>
        <result property="xn" column="xn"/>
        <result property="bjmc" column="bjmc"/>
        <result property="bjrych" column="bjrych"/>
        <result property="jbny" column="jbny"/>
        <result property="zbjs" column="zbjs"/>
        <result property="zbjsxm" column="zbjsxm"/>
        <result property="pbjs" column="pbjs"/>
        <result property="pbjsxm" column="pbjsxm"/>
        <result property="zljs" column="zljs"/>
        <result property="zljsxm" column="zljsxm"/>
        <result property="isdel" column="isdel"/>
        <result property="createtime" column="createtime"/>
    </resultMap>

    <sql id="selectByDayflowassessmentVo">
        select d.id, d.planid, d.dept_id, d.classid, e.bjmc, d.bzbh, d.bzxm, d.pbbh, d.pbxm, d.zlbh, d.zlxm, d.xnxq, d.bzid, d.bzmf, d.kfz, d.kfcs, d.zzdf, d.classdf, d.items, d.values, d.pgdx, f.nick_name as pgdxxm, d.create_userid, d.create_time, d.status,
         (select sum(b.value) from by_dayflowassessmentitem b where d.id=b.pid and b.item in (select id from by_day_flow where name='早间接待')) as zjjdpjf,
(select sum(b.value) from by_dayflowassessmentitem b where d.id=b.pid and b.item in (select id from by_day_flow where name='用餐')) as ycpjf,
(select sum(b.value) from by_dayflowassessmentitem b where d.id=b.pid and b.item in (select id from by_day_flow where name='早间坐圈')) as zjzqpjf,
(select sum(b.value) from by_dayflowassessmentitem b where d.id=b.pid and b.item in (select id from by_day_flow where name='分组教学')) as fzjxpjf,
(select sum(b.value) from by_dayflowassessmentitem b where d.id=b.pid and b.item in (select id from by_day_flow where name='点心时间')) as dxsjpjf,
(select sum(b.value) from by_dayflowassessmentitem b where d.id=b.pid and b.item in (select id from by_day_flow where name='如厕洗手饮水')) as rcxsyspjf,
(select sum(b.value) from by_dayflowassessmentitem b where d.id=b.pid and b.item in (select id from by_day_flow where name='活动过渡')) as hdgdpjf,
(select sum(b.value) from by_dayflowassessmentitem b where d.id=b.pid and b.item in (select id from by_day_flow where name='户外活动')) as hwhdpjf,
(select sum(b.value) from by_dayflowassessmentitem b where d.id=b.pid and b.item in (select id from by_day_flow where name='午睡')) as wspjf,
(select sum(b.value) from by_dayflowassessmentitem b where d.id=b.pid and b.item in (select id from by_day_flow where name='离园再见')) as lyzjpjf,
(select sum(b.value) from by_dayflowassessmentitem b where d.id=b.pid and b.item in (select id from by_day_flow where name='安全')) as aqpjf,
(select sum(b.value) from by_dayflowassessmentitem b where d.id=b.pid and b.item in (select id from by_day_flow where name='自由活动')) as zyhdpjf,
(select sum(b.value) from by_dayflowassessmentitem b where d.id=b.pid and b.item in (select id from by_day_flow where name='规则与纪律约束')) as gzyjlyspjf,
(select sum(b.value) from by_dayflowassessmentitem b where d.id=b.pid and b.item in (select id from by_day_flow where name='微型课程')) as wxkcpjf,
(select sum(b.value) from by_dayflowassessmentitem b where d.id=b.pid and b.item in (select id from by_day_flow where name='潜课程')) as qkcpjf
         from by_dayflowassessment d left join by_class e on d.classid=e.bjbh left join sys_user f on d.pgdx=f.user_id
    </sql>

    <select id="selectByDayflowassessmentList" parameterType="ByDayflowassessment"
            resultMap="ByDayflowassessmentResult">
        <include refid="selectByDayflowassessmentVo"/>
        <where>
            <if test="planid != null ">and d.planid = #{planid}</if>
            <if test="classid != null  and classid != ''">and d.classid = #{classid}</if>
            <if test="bzbh != null ">and d.bzbh = #{bzbh}</if>
            <if test="bzxm != null  and bzxm != ''">and d.bzxm = #{bzxm}</if>
            <if test="pbbh != null ">and d.pbbh = #{pbbh}</if>
            <if test="pbxm != null  and pbxm != ''">and d.pbxm = #{pbxm}</if>
            <if test="zlbh != null ">and d.zlbh = #{zlbh}</if>
            <if test="zlxm != null  and zlxm != ''">and d.zlxm = #{zlxm}</if>
            <if test="xnxq != null  and xnxq != ''">and d.xnxq = #{xnxq}</if>
            <if test="bzid != null ">and d.bzid = #{bzid}</if>
            <if test="kfz != null ">and d.kfz = #{kfz}</if>
            <if test="classdf != null ">and d.classdf = #{classdf}</if>
            <if test="bzmf != null ">and d.bzmf = #{bzmf}</if>
            <if test="zzdf != null ">and d.zzdf = #{zzdf}</if>
            <if test="kfcs != null ">and d.kfcs = #{kfcs}</if>
            <if test="pgdx != null ">and d.pgdx = #{pgdx}</if>
            <if test="createUserid != null ">and d.create_userid = #{createUserid}</if>
            <if test="beginTime != null and beginTime != ''"><!-- 开始时间检索 -->
                AND date_format(d.create_time,'%y%m%d') &gt;= date_format(#{beginTime},'%y%m%d')
            </if>
            <if test="endTime != null and endTime != ''"><!-- 结束时间检索 -->
                AND date_format(d.create_time,'%y%m%d') &lt;= date_format(#{endTime},'%y%m%d')
            </if>
            <if test="remark != null  and remark != ''">and d.remark = #{remark}</if>
            <!-- 数据范围过滤 -->
            ${dataScope}
            order by d.create_time desc
        </where>
    </select>

    <select id="selectByDayflowassessmentById" parameterType="Long" resultMap="ByDayflowassessmentResult">
        <include refid="selectByDayflowassessmentVo"/>
        where d.id = #{id}
    </select>

    <select id="selectByDayflowassessmentPjf" parameterType="ByDayflowassessment" resultMap="ByDayflowassessmentResult">
        select t.classid,e.bjmc,t.ny,avg(t.zzdf) as bjpjf,avg(t.zjjd) as zjjdpjf,avg(t.yc) as ycpjf,avg(t.zjzq) as zjzqpjf,avg(t.fzjx) as fzjxpjf,avg(t.dxsj) as dxsjpjf,avg(t.rcxsys) as rcxsyspjf,avg(t.hdgd) as hdgdpjf,avg(t.hwhd) as hwhdpjf,avg(t.ws) as wspjf,avg(t.lyzj) as lyzjpjf,avg(t.aq) as aqpjf,avg(t.zyhd) as zyhdpjf,avg(t.gzyjlys) as gzyjlyspjf,avg(t.wxkc) as wxkcpjf,avg(t.qkc) as qkcpjf from (
select a.classid,a.zzdf,a.create_time,date_format(a.create_time,'%Y-%m') as 'ny',
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='早间接待')) as zjjd,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='用餐')) as yc,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='早间坐圈')) as zjzq,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='分组教学')) as fzjx,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='点心时间')) as dxsj,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='如厕洗手饮水')) as rcxsys,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='活动过渡')) as hdgd,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='户外活动')) as hwhd,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='午睡')) as ws,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='离园再见')) as lyzj,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='安全')) as aq,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='自由活动')) as zyhd,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='规则与纪律约束')) as gzyjlys,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='微型课程')) as wxkc,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='潜课程')) as qkc
from by_dayflowassessment a
where a.dept_id=#{deptId}
        <if test="createTime != null ">and date_format(create_time,'%Y-%m') = date_format(#{createTime},'%Y-%m')</if>

) t left join by_class e on e.bjbh=t.classid
group by t.classid,t.ny
    </select>

    <select id="selectByDayflowassessmentTeacherPjf" parameterType="ByDayflowassessment" resultMap="ByDayflowassessmentResult">
        select t.pgdx,e.nick_name as pgdxxm,avg(t.zzdf) as bjpjf,avg(t.zjjd) as zjjdpjf,avg(t.yc) as ycpjf,avg(t.zjzq) as zjzqpjf,avg(t.fzjx) as fzjxpjf,avg(t.dxsj) as dxsjpjf,avg(t.rcxsys) as rcxsyspjf,avg(t.hdgd) as hdgdpjf,avg(t.hwhd) as hwhdpjf,avg(t.ws) as wspjf,avg(t.lyzj) as lyzjpjf,avg(t.aq) as aqpjf,avg(t.zyhd) as zyhdpjf,avg(t.gzyjlys) as gzyjlyspjf,avg(t.wxkc) as wxkcpjf,avg(t.qkc) as qkcpjf from (
select a.pgdx,a.zzdf,a.create_time,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='早间接待')) as zjjd,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='用餐')) as yc,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='早间坐圈')) as zjzq,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='分组教学')) as fzjx,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='点心时间')) as dxsj,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='如厕洗手饮水')) as rcxsys,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='活动过渡')) as hdgd,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='户外活动')) as hwhd,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='午睡')) as ws,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='离园再见')) as lyzj,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='安全')) as aq,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='自由活动')) as zyhd,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='规则与纪律约束')) as gzyjlys,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='微型课程')) as wxkc,
(select sum(b.value) from by_dayflowassessmentitem b where a.id=b.pid and b.item in (select id from by_day_flow where name='潜课程')) as qkc
from by_dayflowassessment a
where a.dept_id=#{deptId}
        <if test="pgdx != null ">and a.pgdx = #{pgdx}</if>
        <if test="beginTime != null and beginTime != ''"><!-- 开始时间检索 -->
            AND date_format(a.create_time,'%y%m%d') &gt;= date_format(#{beginTime},'%y%m%d')
        </if>
        <if test="endTime != null and endTime != ''"><!-- 结束时间检索 -->
            AND date_format(a.create_time,'%y%m%d') &lt;= date_format(#{endTime},'%y%m%d')
        </if>
) t left join sys_user e on e.user_id=t.pgdx
group by t.pgdx
    </select>

    <insert id="insertByDayflowassessment" parameterType="ByDayflowassessment" useGeneratedKeys="true" keyProperty="id">
        insert into by_dayflowassessment
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="planid != null ">planid,</if>
            <if test="deptId != null ">dept_id,</if>
            <if test="classid != null  and classid != ''">classid,</if>
            <if test="bzbh != null ">bzbh,</if>
            <if test="bzxm != null  and bzxm != ''">bzxm,</if>
            <if test="pbbh != null ">pbbh,</if>
            <if test="pbxm != null  and pbxm != ''">pbxm,</if>
            <if test="zlbh != null ">zlbh,</if>
            <if test="zlxm != null  and zlxm != ''">zlxm,</if>
            <if test="xnxq != null  and xnxq != ''">xnxq,</if>
            <if test="bzid != null ">bzid,</if>
            <if test="kfz != null ">kfz,</if>
            <if test="classdf != null ">classdf,</if>
            <if test="items != null  and items != ''">items,</if>
            <if test="values != null  and values != ''">values,</if>
            <if test="bzmf != null ">bzmf,</if>
            <if test="zzdf != null ">zzdf,</if>
            <if test="kfcs != null ">kfcs,</if>
            <if test="pgdx != null ">pgdx,</if>
            <if test="pgdxxm != null ">pgdxxm,</if>
            <if test="createUserid != null ">create_userid,</if>
            <if test="createTime != null ">create_time,</if>
            <if test="remark != null  and remark != ''">remark,</if>
            <if test="status != null  and status != ''">status,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="planid != null ">#{planid},</if>
            <if test="deptId != null ">#{deptId},</if>
            <if test="classid != null  and classid != ''">#{classid},</if>
            <if test="bzbh != null ">#{bzbh},</if>
            <if test="bzxm != null  and bzxm != ''">#{bzxm},</if>
            <if test="pbbh != null ">#{pbbh},</if>
            <if test="pbxm != null  and pbxm != ''">#{pbxm},</if>
            <if test="zlbh != null ">#{zlbh},</if>
            <if test="zlxm != null  and zlxm != ''">#{zlxm},</if>
            <if test="xnxq != null  and xnxq != ''">#{xnxq},</if>
            <if test="bzid != null ">#{bzid},</if>
            <if test="kfz != null ">#{kfz},</if>
            <if test="classdf != null ">#{classdf},</if>
            <if test="items != null  and items != ''">#{items},</if>
            <if test="values != null  and values != ''">#{values},</if>
            <if test="bzmf != null ">#{bzmf},</if>
            <if test="zzdf != null ">#{zzdf},</if>
            <if test="kfcs != null ">#{kfcs},</if>
            <if test="pgdx != null ">#{pgdx},</if>
            <if test="pgdxxm != null ">#{pgdxxm},</if>
            <if test="createUserid != null ">#{createUserid},</if>
            <if test="createTime != null ">#{createTime},</if>
            <if test="remark != null  and remark != ''">#{remark},</if>
            <if test="status != null  and status != ''">#{status},</if>
        </trim>
    </insert>

    <update id="updateByDayflowassessment" parameterType="ByDayflowassessment">
        update by_dayflowassessment
        <trim prefix="SET" suffixOverrides=",">
            <if test="planid != null ">planid = #{planid},</if>
            <if test="deptId != null ">dept_id = #{deptId},</if>
            <if test="classid != null  and classid != ''">classid = #{classid},</if>
            <if test="bzbh != null ">bzbh = #{bzbh},</if>
            <if test="bzxm != null  and bzxm != ''">bzxm = #{bzxm},</if>
            <if test="pbbh != null ">pbbh = #{pbbh},</if>
            <if test="pbxm != null  and pbxm != ''">pbxm = #{pbxm},</if>
            <if test="zlbh != null ">zlbh = #{zlbh},</if>
            <if test="zlxm != null  and zlxm != ''">zlxm = #{zlxm},</if>
            <if test="xnxq != null  and xnxq != ''">xnxq = #{xnxq},</if>
            <if test="bzid != null ">bzid = #{bzid},</if>
            <if test="kfz != null ">kfz = #{kfz},</if>
            <if test="classdf != null ">classdf = #{classdf},</if>
            <if test="items != null  and items != ''">items = #{items},</if>
            <if test="values != null  and values != ''">values = #{values},</if>
            <if test="bzmf != null ">bzmf = #{bzmf},</if>
            <if test="zzdf != null ">zzdf = #{zzdf},</if>
            <if test="kfcs != null ">kfcs = #{kfcs},</if>
            <if test="pgdx != null ">pgdx = #{pgdx},</if>
            <if test="pgdxxm != null ">pgdxxm = #{pgdxxm},</if>
            <if test="createUserid != null ">create_userid = #{createUserid},</if>
            <if test="createTime != null ">create_time = #{createTime},</if>
            <if test="remark != null  and remark != ''">remark = #{remark},</if>
            <if test="status != null  and status != ''">status = #{status},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteByDayflowassessmentById" parameterType="Long">
        delete from by_dayflowassessment where id = #{id}
    </delete>

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

</mapper>