<?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.djhk.uav.equ.mapper.StatMapper">

    <!--按机构统计飞行次数、飞行时长数据-->
<!--    <select id="getStatForDept" resultType="java.util.Map">-->
<!--						select t.dept_id,t.count,d.dept_name,t.shichang,t.length from (-->
<!--            select f.dept_id,count(f.dept_id) count,SUM(TIME_TO_SEC(TIMEDIFF(f.end_time, f.beg_time))) / 3600 AS shichang ,ROUND(SUM(d.dist), 2) length-->
<!--            from t_fly f-->
<!--						left join (select orderid,max(dist) dist from t_fly_data GROUP BY orderid) d on f.orderid = d.orderid-->
<!--						 where 1=1-->
<!--						 ${sqlStr}-->
<!--						 GROUP BY dept_id-->
<!--        ) t LEFT JOIN sys_dept d on t.dept_id = d.dept_id-->
<!--    </select>-->

    <select id="getStatForDept" resultType="java.util.Map">
        select dept_id, parent_id, ancestors, dept_name, order_num, leader,
        phone, email, status, del_flag, create_by,admin_code, create_time,loc_lat,loc_lng
        from sys_dept where del_flag = '0' and dept_id = #{deptId} or parent_id = #{deptId} or  find_in_set(#{deptId},ancestors)
    </select>

    <select id="getUserTotal" resultType="java.util.Map">
        SELECT COUNT(user_name) as userTotal FROM uav_operator WHERE del_flag = '0' AND org_id = #{deptID}
    </select>

    <select id="getEquInfo" resultType="java.util.Map">
        SELECT COUNT(equ_id) as equTotal FROM equ_info WHERE del_flag = '0' AND organ_id = #{deptID} and type_id ='11'
    </select>

    <select id="getOtherTotal" resultType="java.util.Map">
        SELECT COUNT(equ_id) as otherTotal FROM equ_info WHERE del_flag = '0' AND organ_id = #{deptID} and type_id ='12'
    </select>

    <select id="getFlightTotal" resultType="java.util.Map">
        SELECT COUNT(equ_id) as flightTotal FROM equ_info WHERE del_flag = '0' AND organ_id = #{deptID} and type_id ='10'
    </select>

    <!--按设备类别统计飞行次数、飞行时长数据-->
    <select id="getStatForType" resultType="java.util.Map">
            SELECT a.type_id,a.type_name,count(type_id) as count,SUM(TIME_TO_SEC(TIMEDIFF(end_time, beg_time))) / 3600 AS shichang,ROUND(SUM(a.length), 2) length
         from (
            select t.*,e.type_id,type.type_name,d.length
            from t_fly t left join equ_info e on t.equ_sn = e.equ_sn
             left join equ_type type on e.type_id = type.type_id
              LEFT JOIN (select orderid,max(dist) length from t_fly_data GROUP BY orderid) d on t.orderid = d.orderid
              where 1=1 and e.del_flag = 0
              and type.type_id in ('10','11')
					 ${sqlStr}
            ) a where a.type_name != '' GROUP BY a.type_id
    </select>

    <!--按设备型号统计飞行次数、飞行时长数据-->
    <select id="getStatForModel" resultType="java.util.Map">
            SELECT a.model_id,a.model_name,count(model_id) as count,SUM(TIME_TO_SEC(TIMEDIFF(end_time, beg_time))) / 3600 AS shichang ,ROUND(SUM(a.length), 2) length
         from (
            select t.*,e.model_id,model.model_name,d.length from t_fly t
            left join equ_info e on t.equ_sn = e.equ_sn
            left join equ_model model on e.model_id = model.model_id
            LEFT JOIN (select orderid,max(dist) length from t_fly_data GROUP BY orderid) d on t.orderid = d.orderid
            where 1=1 and e.del_flag = 0
             ${sqlStr}
            ) a where a.model_id != '' GROUP BY a.model_id
    </select>

    <!--按设备统计飞行次数、飞行时长数据-->
    <select id="getStatForEqu" resultType="java.util.Map">
            SELECT a.equ_sn,a.equ_code,count(equ_sn) as count,SUM(TIME_TO_SEC(TIMEDIFF(end_time, beg_time))) / 3600 AS shichang ,ROUND(SUM(a.length), 2) length
         from (
            select t.*,e.equ_code,d.length from t_fly t
            left join equ_info e on t.equ_sn = e.equ_sn
            LEFT JOIN (select orderid,max(dist) length from t_fly_data GROUP BY orderid) d on t.orderid = d.orderid
            where 1=1 and e.del_flag = 0
            ${sqlStr}
            ) a where a.equ_code != '' GROUP BY a.equ_sn
    </select>

</mapper>
