<?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.casic.missiles.modular.system.dao.StatisticMapper">

    <sql id="wellStatics_sql">
            A.wellCount ,
            A.deptid,
            SD.simplename as "deptName",
            SD.pid
    </sql>

    <select id="wellStaticsByDept" resultType="map">
        SELECT
            A.wellCount ,
            A.deptid,
            SD.simple_name as "deptName",
            SD.pid
        FROM
        ( SELECT COUNT( 1 ) AS wellCount, DEPTID AS deptid FROM bus_well_info WHERE VALID = '1' GROUP BY DEPTID ) A
        LEFT JOIN sys_dept SD ON A.DEPTID = SD.id
    </select>

    <select id="wellStaticsByType" resultType="map">
        SELECT
            COUNT( 1 ) AS "wellCount",
            WELL_TYPE AS "wellType"
        FROM bus_well_info
        WHERE VALID = '1' AND DEPTID IN (${deptIds})
        GROUP BY WELL_TYPE
    </select>

    <select id="deviceStaticsByType" resultType="map">
         SELECT
						 bdt.ID  AS  "id",
             bdt.TYPE_NAME AS "deviceType",
             COALESCE(A.devicecount ,0)  AS "deviceCount"
         FROM base_device_type bdt
         LEFT JOIN (
            SELECT
                COUNT( 1 ) AS devicecount,
                BD.DEVICE_TYPE AS deviceType
            FROM bus_device BD
            LEFT JOIN bus_device_well BDW ON BDW.DEVICE_ID = BD.ID
            LEFT JOIN bus_well_info BWI ON BDW.WELL_ID = BWI.ID
            WHERE BD.VALID = '1' AND BDW.VALID='1'
            GROUP BY BD.DEVICE_TYPE
          ) A ON bdt.ID = A.deviceType
    </select>

    <select id="deviceStaticsByDept" resultType="map">
        SELECT
        BWI.DEPTID AS deptid,
        COUNT( 1 ) AS "deviceCount"
        FROM
        bus_device BD
        LEFT JOIN bus_device_well BDW ON BDW.DEVICE_ID = BD.ID
        LEFT JOIN bus_well_info BWI ON BDW.WELL_ID = BWI.ID
        <where>
            BD.VALID = '1' AND BDW.VALID='1'
            <if test="deviceType != null and deviceType != ''">
                AND BD.DEVICE_TYPE = #{deviceType}
            </if>
        </where>
        GROUP BY
        BWI.DEPTID
    </select>

    <sql id="char_toDate" databaseId="mysql">
       str_to_date(${paramStr},'%Y-%m-%d %H:%i:%s')
    </sql>

    <sql id="char_toDate" databaseId="pgsql">
        TO_DATE(${paramStr},'yyyy-MM-dd HH24:mi:ss')
    </sql>

    <sql id="char_toDate" databaseId="oracle">
        TO_DATE(${paramStr},'yyyy-mm-dd hh24:mi:ss')
    </sql>


    <sql id="isNull_sum" databaseId="mysql">
       ifnull(SUM(IF(job_status=${numberStatus},1,0)),0)
    </sql>

    <sql id="isNull_sum" databaseId="pgsql">
        COALESCE(SUM(CASE WHEN job_status=${numberStatus} THEN 1 ELSE 0 END))
    </sql>

    <sql id="isNull_sum" databaseId="oracle">
        COALESCE(SUM(IF(job_status=${numberStatus},1,0)),0)
    </sql>

    <sql id="data_diff" databaseId="mysql">
         and datediff(now(),aj.create_time) <![CDATA[<  ]]>${paramDay}
    </sql>

    <sql id="data_diff" databaseId="pgsql">
         and date_part(now(),aj.create_time)<![CDATA[<  ]]>${paramDay}
    </sql>

    <sql id="data_diff" databaseId="oracle">
         and date_part(now(),aj.create_time)   <![CDATA[<  ]]> ${paramDay}
    </sql>


    <select id="deviceStaticsByStatus" resultType="map">
        SELECT
        COALESCE(SUM(CASE WHEN bds.ONLINE_STATE='1' OR  bds.ONLINE_STATE is NULL THEN 1 ELSE 0 END))
        AS online,
        COALESCE(SUM(CASE WHEN bds.ONLINE_STATE='0' THEN 1 ELSE 0 END))
        AS offline,
        count(1) AS total
        FROM
        bus_device BD
        LEFT JOIN bus_device_well BDW ON BDW.DEVICE_ID = BD.ID
        LEFT JOIN bus_well_info BWI ON BDW.WELL_ID = BWI.ID
        LEFT JOIN bus_device_status bds ON  bds.DEVICE_ID=BD.ID
        WHERE BD.VALID = '1' AND BDW.VALID='1' AND BWI.DEPTID IN (${deptIds})
    </select>

    <select id="deviceStaticsByStatusAndType" resultType="map">
        SELECT
        COALESCE(SUM(CASE WHEN bds.ONLINE_STATE='1' OR  bds.ONLINE_STATE is NULL THEN 1 ELSE 0 END))
        AS online,
        COALESCE(SUM(CASE WHEN bds.ONLINE_STATE='0' THEN 1 ELSE 0 END))
        AS offline,
        count(1) AS total,bdt.TYPE_NAME AS "typeName"
        FROM
        bus_device BD
        LEFT JOIN bus_device_status bds ON  bds.DEVICE_ID=BD.ID
        LEFT JOIN bus_device_well BDW ON BDW.DEVICE_ID = BD.ID
        LEFT JOIN bus_well_info BWI ON BDW.WELL_ID = BWI.ID,
        base_device_type bdt
        WHERE BD.VALID = '1' AND BDW.VALID='1' AND BWI.DEPTID IN (${deptIds})
        AND bdt.id=BD.DEVICE_TYPE
        GROUP BY bdt.id
    </select>

    <select id="jobsByDept" resultType="map">
        SELECT
        bwi.DEPTID AS deptid,
        <include refid="isNull_sum">
            <property name="numberStatus" value="'0'"/>
        </include>
        AS "beforeGet",
        <include refid="isNull_sum">
            <property name="numberStatus" value="'1'"/>
        </include>
        AS "beforeConfirm",
        <include refid="isNull_sum">
            <property name="numberStatus" value="'2'"/>
        </include>
        AS "inHandle",
        <include refid="isNull_sum">
            <property name="numberStatus" value="'3'"/>
        </include>
        AS "over",
        <include refid="isNull_sum">
            <property name="numberStatus" value="'4'"/>
        </include>
        AS cancel
        FROM alarm_job aj
        INNER JOIN bus_well_info bwi ON aj.WELL_CODE = bwi.WELL_CODE
        INNER JOIN bus_device bd ON aj.DEVCODE = bd.DEVCODE
        WHERE bwi.VALID = '1' AND bd.VALID = '1'
        AND aj.CREATE_TIME <![CDATA[ > ]]>
        <include refid="char_toDate">
            <property name="paramStr" value="#{beginTime}"/>
        </include>
        AND aj.CREATE_TIME <![CDATA[ <= ]]>
        <include refid="char_toDate">
            <property name="paramStr" value="#{endTime}"/>
        </include>
        <if test="deviceType != null and deviceType != ''">
            AND bd.DEVICE_TYPE = #{deviceType}
        </if>
        GROUP BY bwi.DEPTID
    </select>

    <select id="jobsByDeptId" resultType="map">
        SELECT
        <include refid="isNull_sum">
            <property name="numberStatus" value="'0'"/>
        </include>
        AS "beforeGet",
        <include refid="isNull_sum">
            <property name="numberStatus" value="'1'"/>
        </include>
        AS "beforeConfirm",
        <include refid="isNull_sum">
            <property name="numberStatus" value="'2'"/>
        </include>
        AS "inHandle",
        <include refid="isNull_sum">
            <property name="numberStatus" value="'3'"/>
        </include>
        AS "over",
        <include refid="isNull_sum">
            <property name="numberStatus" value="'4'"/>
        </include>
        AS cancel
        FROM alarm_job aj
        INNER JOIN bus_well_info bwi ON aj.WELL_CODE = bwi.WELL_CODE
        INNER JOIN bus_device bd ON aj.DEVCODE = bd.DEVCODE
        <where>
            bwi.VALID = '1' AND bd.VALID = '1'
            AND bwi.DEPTID IN (${deptIds})
            <choose>
                <when test="beginTime != null and beginTime != '' and endTime != null and endTime != ''">
                    AND aj.CREATE_TIME <![CDATA[ >= ]]>
                    <include refid="char_toDate">
                        <property name="paramStr" value="#{beginTime}"/>
                    </include>
                    AND aj.CREATE_TIME <![CDATA[ <=]]>
                    <include refid="char_toDate">
                        <property name="paramStr" value="#{endTime}"/>
                    </include>
                </when>
                <otherwise>
                    <include refid="data_diff">
                        <property name="paramDay" value="30"/>
                    </include>
                </otherwise>
            </choose>
        </where>
    </select>

    <select id="alarmWellListPage" resultType="MAP">
        SELECT ar.WELL_CODE AS "wellCode",COUNT(*) AS "alarmCount",bwi.WELL_NAME AS "wellName",DEPTID AS deptid,
        COUNT(ar.DEVCODE) AS count,POSITION AS "position",COORDINATE_X AS "coordinateX",COORDINATE_Y AS "coordinateY",
        LAT_BAIDU AS "latBaidu",LNG_BAIDU AS "lngBaidu",LNG_GAODE AS "latGaode",LNG_GAODE AS "lngGaode",
        bd.DEVICE_TYPE AS "deviceType"
        FROM alarm_records ar
        JOIN bus_well_info bwi ON bwi.Well_code=ar.WELL_CODE
        JOIN bus_device bd ON bd.DEVCODE=ar.DEVCODE
        WHERE ar.WELL_CODE is not null and ar.WELL_CODE != ''
        <if test="alarmContent!='' and alarmContent!= null">
            AND ALARM_CONTENT = #{alarmContent}
        </if>
        <if test="beginTime!='' and beginTime!= null">
            AND ALARM_TIME <![CDATA[ >= ]]>
            <include refid="char_toDate">
                <property name="paramStr" value="#{beginTime}"/>
            </include>
        </if>
        <if test="endTime!='' and endTime!= null">
            AND ALARM_TIME <![CDATA[ <=]]>
            <include refid="char_toDate">
                <property name="paramStr" value="#{endTime}"/>
            </include>
        </if>
        GROUP BY
        ar.WELL_CODE,bwi.WELL_NAME,bwi.deptid,POSITION,COORDINATE_X,COORDINATE_Y,LAT_BAIDU,LNG_BAIDU,LNG_GAODE,LNG_GAODE,bd.DEVICE_TYPE
        ORDER BY "alarmCount" DESC
    </select>

    <select id="getAlarmCountByType" resultType="MAP">
        SELECT ALARM_CONTENT AS "alarmContent",COUNT(*) AS "alarmCount",bdt.ID AS "deviceType"
        FROM alarm_records ar
        JOIN bus_well_info bwi ON bwi.WELL_CODE=ar.WELL_CODE
        JOIN bus_device bd ON bd.DEVCODE=ar.DEVCODE
        JOIN base_device_type bdt ON bdt.ID=bd.DEVICE_TYPE
        WHERE 1=1
        <if test="alarmType!='' and alarmType!= null">
            AND ALARM_TYPE = #{alarmType}
        </if>
        <if test="beginTime!='' and beginTime!= null">
            AND ALARM_TIME <![CDATA[ >= ]]>
            <include refid="char_toDate">
                <property name="paramStr" value="#{beginTime}"/>
            </include>
        </if>
        <if test="endTime!='' and endTime!= null">
            AND ALARM_TIME <![CDATA[ <=]]>
            <include refid="char_toDate">
                <property name="paramStr" value="#{endTime}"/>
            </include>
        </if>
        GROUP BY ALARM_CONTENT,bdt.ID
        ORDER BY "alarmCount" DESC
    </select>

    <select id="getAlarmByStatus" resultType="MAP">
        SELECT ALARM_CONTENT AS "alarmContent",COUNT(*) AS "totalAlarm",bdt.ID AS "deviceType",
        SUM(CASE WHEN ar.STATUS='1' THEN 1 ELSE 0 END) AS alarmings
        FROM alarm_records ar
        LEFT JOIN bus_well_info bwi ON bwi.WELL_CODE=ar.WELL_CODE
        JOIN bus_device bd ON bd.DEVCODE=ar.DEVCODE
        JOIN base_device_type bdt ON bdt.ID=bd.DEVICE_TYPE
        WHERE 1=1
        <if test="alarmType!='' and alarmType!= null">
            AND ALARM_TYPE = #{alarmType}
        </if>
        <if test="beginTime!='' and beginTime!= null">
            AND ALARM_TIME <![CDATA[ >= ]]>
            <include refid="char_toDate">
                <property name="paramStr" value="#{beginTime}"/>
            </include>
        </if>
        <if test="endTime!='' and endTime!= null">
            AND ALARM_TIME <![CDATA[ <=]]>
            <include refid="char_toDate">
                <property name="paramStr" value="#{endTime}"/>
            </include>
        </if>
        GROUP BY ALARM_CONTENT,deviceType
        ORDER BY "totalAlarm" DESC
    </select>

    <select id="getDeviceType" resultType="String">
        SELECT  DEVICE_TYPE
        FROM config_dept_permission
        WHERE  DEPTID=#{deptid}
    </select>

</mapper>