<?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.template.mapper.DriverStockMapper">

    <resultMap id="Tj" type="com.template.model.tongji.Tj">
        <result property="name" column="name"/>
        <result property="num" column="num"/>
    </resultMap>

    <resultMap id="TjF" type="com.template.model.tongji.TjFloat">
        <result property="name" column="name"/>
        <result property="meterName" column="meter_name"/>
        <result property="num" column="num"/>
    </resultMap>

    <resultMap id="MeterPage" type="com.template.model.tongji.MeterPage">
        <result property="build" column="build"/>
        <result property="meterName" column="meter_name"/>
        <result property="meterId" column="meter_id"/>
        <result property="num" column="num"/>
        <result property="address" column="address"/>
        <result property="meterNo" column="meter_no"/>
        <result property="createTime" column="create_time"/>
    </resultMap>

    <resultMap id="userPage" type="com.template.model.tongji.userPage">
        <result property="name" column="name"/>
        <result property="cardNo" column="card_no"/>
        <result property="departMent" column="depart_ment"/>
    </resultMap>

    <!--年级统计-->
    <select id="getClassTj" resultMap="Tj">
        select `name`, COUNT(*) as num from ( select CONCAT(IFNULL(b.`name`,''),i.`name` ) `name` from `smart_user` a
left join smart_identity i on i.deleted = 0 and i.id = a.identity_id
left join `smart_grade` b on b.deleted = 0 and a.`grade`=b.id
where a.deleted =0 and a.is_cancel = 0) c group by `name`
    </select>

    <!--访客统计-->
    <select id="getVisitorTj" resultMap="Tj">
        select case visitor_type when '1' then '家长' when '2' then '其他'
            end as `name`, COUNT(*) as num from `smart_visitor` WHERE visitor_time>#{start} and #{end}>visitor_time  and deleted=0 and statu=9  group by visitor_type
    </select>

    <!--能耗总量统计-->
    <select id="getEnergyTj" resultMap="TjF">
        select case meter_type when 1 then '用电量' when 0 then '用水量' end as `name`,
               SUM(meter_power) as num from `smart_meter_detail`
                   group by meter_type
    </select>

    <!--每栋楼电费月统计-->
    <select id="getEnergyMonth" resultMap="TjF">
        select b.name, IFNULL(a.num,0) num,#{date} as date from (
            select name,IFNULL(SUM(num),0) as num from `month_meter_detail`
            where date=#{date} and type=#{type}
            group by name ) a right join `smart_build` b on a.name=b.name
    </select>

    <!--近12天水电统计-->
    <select id="getEnergyDay" resultMap="TjF">
        select IFNULL(a.num,0) num,#{date} as name from (
            select IFNULL(SUM(num),0) as num from `day_meter_detail`
            where date=#{date} and type=#{type} ) a
    </select>

    <!--总设备数-->
    <select id="getTotalDevice" resultMap="TjF">
        select case meter_type when 1 then '电表' when 0 then '水表' end as `name`,
               COUNT(*) as num
        from `smart_build_meter` group by meter_type
    </select>

    <!--在线设备数-->
    <select id="getOnlineDevice" resultMap="TjF">
        select case meter_type when 1 then '电表' when 0 then '水表' end as `name`,
               COUNT(*) as num from (select * from `smart_meter_detail` where create_time
           like concat(#{date}, '%') group by meter_id ) a group by meter_type
    </select>

    <!--设备实时记录数据-->
    <select id="getMeterDetail" resultMap="MeterPage">
        select e.name meter_name,e.address,e.meter_no,c.meter_power num,d.`name` build,c.create_time from
            (select a.`meter_id`,a.`meter_power`,a.`create_time`,
                    b.`build_id` from `smart_meter_detail` a left join `smart_build_meter` b
                    on a.`meter_id`=b.`meter_id` where a.meter_type=#{meterType} and b.`meter_type`=#{meterType}
                    and a.`deleted`=0 and b.`deleted`=0) c
                left join `smart_build` d on c.`build_id`=d.`id`
                left join `smart_meter` e on c.meter_id=e.id
            where d.deleted=0 and e.`deleted`=0 and e.`meter_type`=#{meterType}
            order by create_time desc
    </select>

    <!--设备实时记录数据-->
    <select id="getUserComp" resultMap="Tj">
        select iname as name,COUNT(*) as num from (
             select a.*,b.name as iname from `smart_user` a left join `smart_identity` b
             on a.`identity_id`=b.id where a.deleted=0 ) c group by iname
    </select>

    <!--用户汇总学生-->
    <select id="getUserPage" resultMap="userPage">
        select a.id,a.sex_id as sexId,a.head_image as headImage, a.name,a.`card_no`,b.`name` as depart_ment from
        `smart_user` a
        left join `smart_department` b on a.`department_id`=b.id
        where a.`deleted`=0 and a.identity_id=#{identityId}
        <if test="key != null and key != ''">
            and (a.name like '%' #{key} '%' or a.card_no like '%' #{key} '%')
        </if>
    </select>

    <!--教学成果-->
    <select id="getScorePer" resultMap="Tj">
        select score as name,COUNT(*) as num from (
        select case when score <![CDATA[ < ]]> 60 then '60分以下'
        when score <![CDATA[ >= ]]> 60 and score <![CDATA[ <= ]]> 70 then '60-70'
        when score <![CDATA[ >= ]]> 71 and score <![CDATA[ <= ]]> 80 then '71-80'
        when score <![CDATA[ >= ]]> 81 and score <![CDATA[ <= ]]> 90 then '81-90'
        when score <![CDATA[ >= ]]> 91 and score <![CDATA[ <= ]]> 100 then '91-100'
        end score,CONCAT(semester,b.name) as exam_name,a.deleted=0,a.update_user from `smart_score` a
        left join `smart_examtype` b on a.exam_type=b.id ) a
        where a.exam_name=#{examName} and a.update_user=#{updateUser} group by score
    </select>

    <!--考试名称-->
    <select id="getExamName" resultMap="Tj">
        select distinct(CONCAT(semester,b.name)) as name from `smart_score` a
              left join `smart_examtype` b on a.exam_type=b.id where a.`deleted`=0
    </select>

    <!--教师评分-->
    <select id="getTeacherExam" resultMap="Tj">
        select evaluate_detail as name from `smart_evaluate_teacher` where card_no=#{cardNo} and deleted=0 order by id desc
    </select>
</mapper>
