<?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.AlarmJobMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.casic.missiles.modular.system.model.AlarmJob">
        <id column="ID" property="id"/>
        <result column="JOBCODE" property="jobcode"/>
        <result column="JOG_TYPE" property="jogType"/>
        <result column="WELL_CODE" property="wellCode"/>
        <result column="DEVCODE" property="devcode"/>
        <result column="CREATE_TIME" property="createTime"/>
        <result column="JOB_STATUS" property="jobStatus"/>
        <result column="GET_JOB_PERSON" property="getJobPerson"/>
        <result column="GET_JOB_TIME" property="getJobTime"/>
        <result column="FIRST_STATE" property="firstState"/>
        <result column="FIRST_STATE_PHOTOS" property="firstStatePhotos"/>
        <result column="CONFIRM_JOB_PERSON" property="confirmJobPerson"/>
        <result column="CONFRIM_JOB_TIME" property="confrimJobTime"/>
        <result column="HANDLE_JOB_Person" property="handleJobPerson"/>
        <result column="HANDLE_JOB_TIME" property="handleJobTime"/>
        <result column="HANDLE_MESSAGE" property="handleMessage"/>
        <result column="HANDLE_PHOTOS" property="handlePhotos"/>
        <result column="FLOW" property="flow"/>
    </resultMap>

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

    <sql id="date_toChar" databaseId="pgsql">
       TO_CHAR(${paramTime},'yyyy-mm-dd hh24:mi:ss')
    </sql>

    <sql id="date_toChar" databaseId="oracle">
       TO_CHAR(${paramTime},'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="maxId_sum" databaseId="mysql">
        ifnull(MAX(ar.ID),0)
    </sql>

    <sql id="maxId_sum" databaseId="pgsql">
        coalesce(MAX(ar.ID),0)
    </sql>

    <sql id="maxId_sum" databaseId="oracle">
       coalesce(MAX(ar.ID),0)
    </sql>

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

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

    <sql id="char_toDate" databaseId="oracle">
        TO_DATE(${paramStr},'yyyy-mm-dd hh24:mi')
    </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('day',now()-aj.create_time)   <![CDATA[< ]]>  ${paramDay}
    </sql>

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


    <sql id="keywords_sql" databaseId="oracle">
        '%'||#{keywords}||'%'
    </sql>
    <sql id="keywords_sql" databaseId="mysql">
        CONCAT('%',#{keywords},'%')
    </sql>
    <sql id="keywords_sql" databaseId="pgsql">
        CONCAT('%',#{keywords},'%')
    </sql>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        ID AS id, JOBCODE AS "jobcode", JOG_TYPE AS "jogType",
        WELL_CODE AS "wellCode", DEVCODE AS "devcode",
        <include refid="date_toChar">
            <property name="paramTime" value="CREATE_TIME"/>
        </include>
        AS "createTime",
        JOB_STATUS AS "jobStatus", GET_JOB_PERSON AS "getJobPerson",
        <include refid="date_toChar">
            <property name="paramTime" value="GET_JOB_TIME"/>
        </include>
        AS "getJobTime", FIRST_STATE AS "firstState",
        FIRST_STATE_PHOTOS AS "firstStatePhotos", CONFIRM_JOB_PERSON AS "confirmJobPerson",
        <include refid="date_toChar">
            <property name="paramTime" value="CONFRIM_JOB_TIME"/>
        </include>
        AS "confrimJobTime", HANDLE_JOB_Person AS "handleJobPerson",
        <include refid="date_toChar">
            <property name="paramTime" value="HANDLE_JOB_TIME"/>
        </include>
        AS "handleJobTime", HANDLE_MESSAGE AS "handleMessage",
        HANDLE_PHOTOS AS "handlePhotos", FLOW AS flow
    </sql>

    <!-- 暂时废弃 -->
    <select id="countByJobStatus" 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
        FROM
        (
        SELECT
        <include refid="maxId_sum"/>
        AS alarmId,aj.job_status AS "job_status"
        FROM alarm_job aj LEFT JOIN alarm_records ar
        ON aj.ID = ar.job_id
        WHERE aj.well_code in
        (
        SELECT bwi.WELL_CODE
        FROM bus_well_info bwi
        WHERE bwi.responsible_dept = #{deptId}
        ) and ar.alarm_type =1
        and (( aj.job_status=3 and aj.job_belong_to IS NOT NULL ) or aj.job_status in (0,1,2))
        <choose>
            <when test="spanDays gte 0">
                <include refid="data_diff">
                    <property name="paramDay" value="#{spanDays}"/>
                </include>
            </when>
            <otherwise>
                <include refid="data_diff">
                    <property name="paramDay" value="30"/>
                </include>
            </otherwise>
        </choose>
        GROUP BY aj.id
        ) c
    </select>

    <select id="countMyJob" 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
        FROM
        (
        SELECT
        <include refid="maxId_sum"/>
        AS alarmId,aj.job_status AS job_status
        FROM alarm_job aj LEFT JOIN alarm_records ar
        ON aj.ID = ar.job_id
        WHERE aj.well_code in
        (
        SELECT bwi.WELL_CODE
        FROM bus_well_info bwi
        WHERE bwi.responsible_dept = #{deptId} AND bwi.VALID = '1'
        ) and ar.alarm_type =1 AND ( aj.job_belong_to IS NULL OR aj.job_belong_to = #{userId} )
        and (( aj.job_status=3 and aj.job_belong_to IS NOT NULL ) or aj.job_status in (0,1,2))
        <choose>
            <when test="spanDays gte 0">
                <include refid="data_diff">
                    <property name="paramDay" value="#{spanDays}"/>
                </include>
            </when>
            <otherwise>
                <include refid="data_diff">
                    <property name="paramDay" value="30"/>
                </include>
            </otherwise>
        </choose>
        GROUP BY aj.id
        ) c
    </select>

    <select id="jobListExport" resultType="map">
        SELECT
        <include refid="job_list_column"/>
        FROM alarm_job aj LEFT JOIN alarm_records ar ON aj.ID = ar.JOB_ID
        LEFT JOIN bus_well_info bwi ON aj.well_code = bwi.well_code
        WHERE bwi.VALID = '1'
        <if test="jobStatus!='' and jobStatus!=null">
            AND aj.JOB_STATUS = #{jobStatus}
        </if>
        <if test="beginTime!='' and beginTime!=null">
            AND aj.CREATE_TIME <![CDATA[ >= ]]>
            <include refid="char_toDate">
                <property name="paramStr" value="#{beginTime}"/>
            </include>
        </if>
        <if test="endTime!='' and endTime!=null">
            AND aj.CREATE_TIME <![CDATA[ <= ]]>
            <include refid="char_toDate">
                <property name="paramStr" value="#{endTime}"/>
            </include>
        </if>
        <if test="keywords!='' and keywords!=null">
            and aj.WELL_CODE like
            <include refid="keywords_sql"/>
        </if>
        <if test="alarmType!='' and alarmType!=null">
            AND ar.ALARM_TYPE = #{alarmType}
        </if>
        <if test="alarmContent!='' and alarmContent!=null">
            AND ar.ALARM_CONTENT = #{alarmContent}
        </if>
        group by aj.id
    </select>

    <!-- 工单统计 -->
    <select id="countByDataScope" resultType="map">
        SELECT MAX( aj.ID ) AS id, bwi.RESPONSIBLE_DEPT AS "deptId"
        FROM alarm_job aj
        LEFT JOIN alarm_records ar ON ar.JOB_ID = aj.ID
        LEFT JOIN bus_well_info bwi ON aj.WELL_CODE = bwi.WELL_CODE
        <where>
            bwi.VALID = '1'
            AND ar.JOB_ID > 0
            <if test="alarmType != null ">
                AND ar.ALARM_TYPE = #{alarmType}
            </if>
            <if test="jobStatus != null">
                AND aj.JOB_STATUS = #{jobStatus}
            </if>
            <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>
        GROUP BY aj.id,bwi.RESPONSIBLE_DEPT
    </select>

    <select id="countByResponse" resultType="java.lang.Integer">
        SELECT COUNT( DISTINCT aj.ID )
        FROM alarm_job aj
        LEFT JOIN alarm_records ar ON ar.JOB_ID = aj.ID
        LEFT JOIN bus_well_info bwi ON aj.WELL_CODE = bwi.WELL_CODE
        <where>
            bwi.VALID = '1'
            <if test="deptId!='' and deptId!=null">
                AND bwi.RESPONSIBLE_DEPT = #{deptId}
            </if>
            <if test="alarmType!='' and alarmType!=null">
                AND ar.ALARM_TYPE = #{alarmType}
            </if>
            <if test="jobStatus!='' and jobStatus!=null">
                AND aj.JOB_STATUS = #{jobStatus}
            </if>
            <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="countByUser" resultType="java.lang.Integer">
        SELECT COUNT( DISTINCT aj.ID )
        FROM alarm_job aj
        LEFT JOIN alarm_records ar ON ar.JOB_ID = aj.ID
        LEFT JOIN bus_well_info bwi ON aj.WELL_CODE = bwi.WELL_CODE
        <where>
            bwi.VALID = '1'
            <if test="userId!='' and userId!=null">
                AND aj.JOB_BELONG_TO = #{userId}
            </if>
            <if test="alarmType!='' and alarmType!=null">
                AND ar.ALARM_TYPE = #{alarmType}
            </if>
            <if test="jobStatus!='' and jobStatus!=null">
                AND aj.JOB_STATUS = #{jobStatus}
            </if>
            <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>

    <!-- 工单列表查询 -->
    <sql id="job_list_column" databaseId="mysql">
        ar.id AS alarmId,
        aj.DEVCODE AS devcode,
        aj.WELL_CODE AS wellCode,
        ar.ALARM_TYPE AS alarmType,
        ar.ALARM_CONTENT AS alarmContentName,
        ar.STATUS AS alarmStatus,
        aj.ID AS id,
        aj.ID AS jobId,
        aj.JOG_TYPE AS jobType,
        aj.JOBCODE AS jobCode,
        aj.JOB_STATUS AS jobStatus,
        aj.JOB_BELONG_TO AS jobBelongTo,
        ar.ALARM_VALUE AS alarmValue,
        ar.ALARM_LEVEL AS alarmLevel,
        ar.ALARM_MSG AS alarmMessage,
        <include refid="date_toChar">
            <property name="paramTime" value="CREATE_TIME"/>
        </include>
        AS createTime,
        <include refid="date_toChar">
            <property name="paramTime" value="ar.ALARM_TIME"/>
        </include>
        AS alarmTime,
        aj.GET_JOB_PERSON AS getJobPerson,
        <include refid="date_toChar">
            <property name="paramTime" value="aj.SHOULD_GET_TIME"/>
        </include>
        AS shouldGetTime,
        <include refid="date_toChar">
            <property name="paramTime" value="aj.GET_JOB_TIME"/>
        </include>
        AS getJobTime,
        aj.CONFIRM_JOB_PERSON AS confirmJobPerson,
        <include refid="date_toChar">
            <property name="paramTime" value="aj.CONFRIM_JOB_TIME"/>
        </include>
        AS confirmJobTime,
        aj.HANDLE_JOB_Person AS handleJobPerson,
        <include refid="date_toChar">
            <property name="paramTime" value="aj.SHOULD_HANDLE_TIME"/>
        </include>
        AS shouldHandleTime,
        <include refid="date_toChar">
            <property name="paramTime" value="aj.HANDLE_JOB_TIME"/>
        </include>
        AS handleJobTime,
        aj.JOB_FLAG_1 AS jobFlag,
        bwi.RESPONSIBLE_DEPT AS deptid
    </sql>

    <!-- 工单列表查询 -->
    <sql id="job_list_column" databaseId="oracle">
        ar.id AS alarmId,
        aj.DEVCODE AS devcode,
        aj.WELL_CODE AS wellCode,
        ar.ALARM_TYPE AS alarmType,
        ar.ALARM_CONTENT AS alarmContentName,
        ar.STATUS AS alarmStatus,
        aj.ID AS id,
        aj.ID AS jobId,
        aj.JOG_TYPE AS jobType,
        aj.JOBCODE AS jobCode,
        aj.JOB_STATUS AS jobStatus,
        aj.JOB_BELONG_TO AS jobBelongTo,
        ar.ALARM_VALUE AS alarmValue,
        ar.ALARM_LEVEL AS alarmLevel,
        ar.ALARM_MSG AS alarmMessage,
        <include refid="date_toChar">
            <property name="paramTime" value="CREATE_TIME"/>
        </include>
        AS createTime,
        <include refid="date_toChar">
            <property name="paramTime" value="ar.ALARM_TIME"/>
        </include>
        AS alarmTime,
        aj.GET_JOB_PERSON AS getJobPerson,
        <include refid="date_toChar">
            <property name="paramTime" value="aj.SHOULD_GET_TIME"/>
        </include>
        AS shouldGetTime,
        <include refid="date_toChar">
            <property name="paramTime" value="aj.GET_JOB_TIME"/>
        </include>
        AS getJobTime,
        aj.CONFIRM_JOB_PERSON AS confirmJobPerson,
        <include refid="date_toChar">
            <property name="paramTime" value="aj.CONFRIM_JOB_TIME"/>
        </include>
        AS confirmJobTime,
        aj.HANDLE_JOB_Person AS handleJobPerson,
        <include refid="date_toChar">
            <property name="paramTime" value="aj.SHOULD_HANDLE_TIME"/>
        </include>
        AS shouldHandleTime,
        <include refid="date_toChar">
            <property name="paramTime" value="aj.HANDLE_JOB_TIME"/>
        </include>
        AS handleJobTime,
        aj.JOB_FLAG_1 AS jobFlag,
        bwi.RESPONSIBLE_DEPT AS deptid
    </sql>

    <!-- 工单列表查询 -->
    <sql id="job_list_column" databaseId="pgsql">
        ar.id AS "alarmId",
        aj.DEVCODE AS devcode,
        aj.WELL_CODE AS "wellCode",
        ar.ALARM_TYPE AS "alarmType",
        ar.ALARM_CONTENT AS "alarmContentName",
        ar.STATUS AS "alarmStatus",
        aj.ID AS id,
        aj.ID AS "jobId",
        aj.JOG_TYPE AS "jobType",
        aj.JOBCODE AS "jobCode",
        aj.JOB_STATUS AS "jobStatus",
        aj.JOB_BELONG_TO AS "jobBelongTo",
        ar.ALARM_VALUE AS "alarmValue",
        ar.ALARM_LEVEL AS "alarmLevel",
        ar.ALARM_MSG AS "alarmMessage",
        <include refid="date_toChar">
            <property name="paramTime" value="CREATE_TIME"/>
        </include>
        AS "createTime",
        <include refid="date_toChar">
            <property name="paramTime" value="ar.ALARM_TIME"/>
        </include>
        AS "alarmTime",
        aj.GET_JOB_PERSON AS "getJobPerson",
        <include refid="date_toChar">
            <property name="paramTime" value="aj.SHOULD_GET_TIME"/>
        </include>
        AS "shouldGetTime",
        <include refid="date_toChar">
            <property name="paramTime" value="aj.GET_JOB_TIME"/>
        </include>
        AS "getJobTime",
        aj.CONFIRM_JOB_PERSON AS "confirmJobPerson",
        <include refid="date_toChar">
            <property name="paramTime" value="aj.CONFRIM_JOB_TIME"/>
        </include>
        AS "confirmJobTime",
        aj.HANDLE_JOB_Person AS "handleJobPerson",
        <include refid="date_toChar">
            <property name="paramTime" value="aj.SHOULD_HANDLE_TIME"/>
        </include>
        AS "shouldHandleTime",
        <include refid="date_toChar">
            <property name="paramTime" value="aj.HANDLE_JOB_TIME"/>
        </include>
        AS "handleJobTime",
        aj.JOB_FLAG_1 AS "jobFlag",
        bwi.RESPONSIBLE_DEPT AS deptid
    </sql>


    <select id="jobList" resultType="map">
        SELECT
        <include refid="job_list_column"/>
        FROM alarm_job aj
        LEFT JOIN alarm_records ar ON (aj.ID = ar.JOB_ID AND ar.ID IN ( SELECT MAX(ID) FROM alarm_records GROUP BY
        JOB_ID))
        LEFT JOIN bus_well_info bwi ON aj.well_code = bwi.well_code
        WHERE ar.JOB_ID > 0 AND bwi.VALID = '1'
        <if test="jobStatus!='' and jobStatus!=null">
            AND aj.JOB_STATUS = #{jobStatus}
        </if>
        <if test="keywords!='' and keywords!=null">
            and aj.WELL_CODE like
            <include refid="keywords_sql"/>
        </if>
        <if test="alarmType!='' and alarmType!=null">
            AND ar.ALARM_TYPE = #{alarmType}
        </if>
        <if test="alarmContent!='' and alarmContent!=null">
            AND ar.ALARM_CONTENT = #{alarmContent}
        </if>
        <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>
    </select>

    <select id="jobListApp" resultType="map">
        SELECT<include refid="job_list_column"/>,
        (JOB_BELONG_TO = #{userId} OR JOB_BELONG_TO IS NULL) AS editable
        FROM alarm_job aj
        LEFT JOIN alarm_records ar ON (aj.ID = ar.JOB_ID AND ar.ID IN ( SELECT MAX(ID) FROM alarm_records GROUP BY
        JOB_ID))
        LEFT JOIN bus_well_info bwi ON aj.well_code = bwi.well_code
        WHERE ar.JOB_ID > 0
        AND ar.ALARM_TYPE = '1'
        AND bwi.responsible_dept = #{deptId}
        AND bwi.VALID = '1'
        <if test="keywords!='' and keywords!=null">
            AND aj.WELL_CODE like
            <include refid="keywords_sql"/>
        </if>
        <if test="alarmType!='' and alarmType!=null">
            AND ar.ALARM_TYPE = #{alarmType}
        </if>
        <if test="alarmContent!='' and alarmContent!=null">
            AND ar.ALARM_CONTENT = #{alarmContent}
        </if>
        <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>
        <choose>
            <when test="leaderId gt 0">
                <if test="jobStatus!='' and jobStatus!=null">
                    AND aj.JOB_STATUS = #{jobStatus}
                </if>
                <!-- GROUP BY aj.id -->
                <if test="jobStatus==1 or jobStatus==2 or jobStatus==3">
                    ORDER BY instr(#{leaderId},job_belong_to) desc,aj.CREATE_TIME desc
                </if>
            </when>
            <otherwise>
                <if test="jobStatus!='' and jobStatus!=null">
                    AND aj.JOB_STATUS = #{jobStatus}
                    <if test="jobStatus gt 0">
                        AND job_belong_to = #{userId}
                    </if>
                </if>
                <if test="jobStatus == '' or jobStatus == null ">
                    AND (job_belong_to = #{userId} OR JOB_STATUS = '0')
                </if>
                <!-- GROUP BY aj.id -->
            </otherwise>
        </choose>
    </select>

    <select id="jobListDelayRe" resultType="map">
        SELECT
        <include refid="job_list_column"/>
        FROM alarm_job aj
        LEFT JOIN alarm_records ar ON (aj.ID = ar.JOB_ID AND ar.ID IN ( SELECT MAX(ID) FROM alarm_records GROUP BY
        JOB_ID))
        LEFT JOIN bus_well_info bwi ON aj.well_code = bwi.well_code
        WHERE ar.JOB_ID > 0 AND bwi.VALID = '1'
        AND aj.JOB_STATUS = '0'
        AND aj.JOB_FLAG_DELAY = '1'
        <if test="keywords!='' and keywords!=null">
            and aj.WELL_CODE like
            <include refid="keywords_sql"/>
        </if>
        <if test="alarmType!='' and alarmType!=null">
            AND ar.ALARM_TYPE = #{alarmType}
        </if>
        <if test="alarmContent!='' and alarmContent!=null">
            AND ar.ALARM_CONTENT = #{alarmContent}
        </if>
        <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>
    </select>

    <select id="jobListDelayReApp" resultType="map">
        SELECT<include refid="job_list_column"/>,
        (JOB_BELONG_TO = #{userId} OR JOB_BELONG_TO IS NULL) AS editable
        FROM alarm_job aj
        LEFT JOIN alarm_records ar ON (aj.ID = ar.JOB_ID AND ar.ID IN ( SELECT MAX(ID) FROM alarm_records GROUP BY
        JOB_ID))
        LEFT JOIN bus_well_info bwi ON aj.well_code = bwi.well_code
        WHERE ar.JOB_ID > 0 AND bwi.VALID = '1'
        AND aj.JOB_STATUS = '0'
        AND aj.JOB_FLAG_DELAY = '1'
        AND ar.ALARM_TYPE = '1'
        AND bwi.responsible_dept = #{deptId}
        <if test="keywords!='' and keywords!=null">
            and aj.WELL_CODE like
            <include refid="keywords_sql"/>
        </if>
        <if test="alarmType!='' and alarmType!=null">
            AND ar.ALARM_TYPE = #{alarmType}
        </if>
        <if test="alarmContent!='' and alarmContent!=null">
            AND ar.ALARM_CONTENT = #{alarmContent}
        </if>
        <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>
    </select>

    <select id="jobListDelayPro" resultType="map">
        SELECT
        <include refid="job_list_column"/>
        FROM alarm_job aj
        LEFT JOIN alarm_records ar ON (aj.ID = ar.JOB_ID AND ar.ID IN ( SELECT MAX(ID) FROM alarm_records GROUP BY
        JOB_ID))
        LEFT JOIN bus_well_info bwi ON aj.well_code = bwi.well_code
        WHERE ar.JOB_ID > 0 AND bwi.VALID = '1'
        AND ( aj.JOB_STATUS = '1' OR aj.JOB_STATUS = '2')
        AND aj.JOB_FLAG_DELAY = '2'
        <if test="beginTime!='' and beginTime!=null">
            AND aj.CREATE_TIME <![CDATA[ >= ]]>
            <include refid="char_toDate">
                <property name="paramStr" value="#{beginTime}"/>
            </include>
        </if>
        <if test="endTime!='' and endTime!=null">
            AND aj.CREATE_TIME <![CDATA[ <= ]]>
            <include refid="char_toDate">
                <property name="paramStr" value="#{endTime}"/>
            </include>
        </if>
        <if test="keywords!='' and keywords!=null">
            and aj.WELL_CODE like
            <include refid="keywords_sql"/>
        </if>
        <if test="alarmType!='' and alarmType!=null">
            AND ar.ALARM_TYPE = #{alarmType}
        </if>
        <if test="alarmContent!='' and alarmContent!=null">
            AND ar.ALARM_CONTENT = #{alarmContent}
        </if>
    </select>

    <select id="jobListDelayProApp" resultType="map">
        SELECT<include refid="job_list_column"/>,
        (JOB_BELONG_TO = #{userId} OR JOB_BELONG_TO IS NULL) AS editable
        FROM alarm_job aj
        LEFT JOIN alarm_records ar ON (aj.ID = ar.JOB_ID AND ar.ID IN ( SELECT MAX(ID) FROM alarm_records GROUP BY
        JOB_ID))
        LEFT JOIN bus_well_info bwi ON aj.well_code = bwi.well_code
        WHERE ar.JOB_ID > 0 AND bwi.VALID = '1'
        AND ( aj.JOB_STATUS = '1' OR aj.JOB_STATUS = '2')
        AND aj.JOB_FLAG_DELAY = '2'
        AND ar.ALARM_TYPE = '1'
        AND bwi.responsible_dept = #{deptId}
        <if test="beginTime!='' and beginTime!=null">
            AND aj.CREATE_TIME <![CDATA[ >= ]]>
            <include refid="char_toDate">
                <property name="char_toDate" value="#{beginTime}"/>
            </include>
        </if>
        <if test="endTime!='' and endTime!=null">
            AND aj.CREATE_TIME <![CDATA[ <= ]]>
            <include refid="char_toDate">
                <property name="char_toDate" value="#{endTime}"/>
            </include>
        </if>
        <if test="keywords!='' and keywords!=null">
            AND aj.WELL_CODE like
            <include refid="keywords_sql"/>
        </if>
        <if test="alarmType!='' and alarmType!=null">
            AND ar.ALARM_TYPE = #{alarmType}
        </if>
        <if test="alarmContent!='' and alarmContent!=null">
            AND ar.ALARM_CONTENT = #{alarmContent}
        </if>
        <choose>
            <when test="leaderId gt 0">
                <!-- GROUP BY aj.id -->
                ORDER BY instr(#{leaderId},job_belong_to) desc,aj.CREATE_TIME desc
            </when>
            <otherwise>
                AND job_belong_to = #{userId}
                <!-- GROUP BY aj.id -->
            </otherwise>
        </choose>
    </select>

    <select id="jobListSearch" resultType="map">
        SELECT<include refid="job_list_column"/>,
        (JOB_BELONG_TO = #{userId} OR JOB_BELONG_TO IS NULL) AS editable
        FROM alarm_job aj
        LEFT JOIN alarm_records ar ON (aj.ID = ar.JOB_ID AND ar.ID IN ( SELECT MAX(ID) FROM alarm_records GROUP BY
        JOB_ID))
        LEFT JOIN bus_well_info bwi ON aj.well_code = bwi.well_code
        <where>
            bwi.VALID = '1'
            AND ar.ALARM_TYPE = '1'
            AND bwi.VALID = '1'
            <if test="jobListParam.keywords!='' and jobListParam.keywords!=null">
                AND aj.WELL_CODE like
                <include refid="keywords_sql"/>
            </if>
            <if test="jobListParam.alarmLevel!='' and jobListParam.alarmLevel!=null">
                AND ar.ALARM_LEVEL = #{jobListParam.alarmLevel}
            </if>
            <if test="jobListParam.alarmContent!='' and jobListParam.alarmContent!=null">
                AND ar.ALARM_CONTENT = #{jobListParam.alarmContent}
            </if>
            <if test="jobListParam.jobStatus!='' and jobListParam.jobStatus!=null">
                AND aj.JOB_STATUS = #{jobListParam.jobStatus}
            </if>
            <if test="jobListParam.beginTime!='' and jobListParam.beginTime!=null">
                AND aj.CREATE_TIME <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{jobListParam.beginTime}"/>
                </include>
            </if>
            <if test="jobListParam.endTime!='' and jobListParam.endTime!=null">
                AND aj.CREATE_TIME <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{jobListParam.endTime}"/>
                </include>
            </if>
            <if test="jobListParam.getJobBeginTime!='' and jobListParam.getJobBeginTime!=null">
                AND aj.GET_JOB_TIME <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{jobListParam.getJobBeginTime}"/>
                </include>
            </if>
            <if test="jobListParam.getJobEndTime!='' and jobListParam.getJobEndTime!=null">
                AND aj.GET_JOB_TIME <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{jobListParam.getJobEndTime}"/>
                </include>
            </if>
            <if test="jobListParam.shouldGetBeginTime!='' and jobListParam.shouldGetBeginTime!=null">
                AND aj.SHOULD_GET_TIME <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{jobListParam.shouldGetBeginTime}"/>
                </include>
            </if>
            <if test="jobListParam.shouldGetEndTime!='' and jobListParam.shouldGetEndTime!=null">
                AND aj.SHOULD_GET_TIME <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{jobListParam.shouldGetEndTime}"/>
                </include>
            </if>
            <if test="jobListParam.handleJobBeginTime!='' and jobListParam.handleJobBeginTime!=null">
                AND aj.HANDLE_JOB_TIME <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{jobListParam.handleJobBeginTime}"/>
                </include>
            </if>
            <if test="jobListParam.handleJobEndTime!='' and jobListParam.handleJobEndTime!=null">
                AND aj.HANDLE_JOB_TIME <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{jobListParam.handleJobEndTime}"/>
                </include>
            </if>
            <if test="jobListParam.shouldHandleBeginTime!='' and jobListParam.shouldHandleBeginTime!=null">
                AND aj.SHOULD_HANDLE_TIME <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{jobListParam.shouldHandleBeginTime}"/>
                </include>
            </if>
            <if test="jobListParam.shouldHandleEndTime!='' and jobListParam.shouldHandleEndTime!=null">
                AND aj.SHOULD_HANDLE_TIME <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{jobListParam.shouldHandleEndTime}"/>
                </include>
            </if>
        </where>
    </select>

    <select id="jobListSearchApp" resultType="map">
        SELECT<include refid="job_list_column"/>,
        (JOB_BELONG_TO = #{userId} OR JOB_BELONG_TO IS NULL) AS editable
        FROM alarm_job aj
        LEFT JOIN alarm_records ar ON (aj.ID = ar.JOB_ID AND ar.ID IN ( SELECT MAX(ID) FROM alarm_records GROUP BY
        JOB_ID))
        LEFT JOIN bus_well_info bwi ON aj.well_code = bwi.well_code
        <where>
            bwi.VALID = '1'
            AND ar.ALARM_TYPE = '1'
            AND bwi.responsible_dept = #{deptId}
            AND bwi.VALID = '1'
            <if test="keywords!='' and keywords!=null">
                AND aj.WELL_CODE like
                <include refid="keywords_sql"/>
            </if>
            <if test="alarmLevel!='' and alarmLevel!=null">
                AND ar.ALARM_LEVEL = #{alarmLevel}
            </if>
            <if test="alarmContent!='' and alarmContent!=null">
                AND ar.ALARM_CONTENT = #{alarmContent}
            </if>
            <if test="beginTime!='' and beginTime!=null">
                AND aj.CREATE_TIME <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{beginTime}"/>
                </include>
            </if>
            <if test="endTime!='' and endTime!=null">
                AND aj.CREATE_TIME <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{endTime}"/>
                </include>
            </if>
            <if test="getJobBeginTime!='' and getJobBeginTime!=null">
                AND aj.GET_JOB_TIME <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{getJobBeginTime}"/>
                </include>
            </if>
            <if test="getJobEndTime!='' and getJobEndTime!=null">
                <include refid="char_toDate">
                    <property name="paramStr" value="#{getJobEndTime}"/>
                </include>
            </if>
            <if test="shouldGetBeginTime!='' and shouldGetBeginTime!=null">
                AND aj.SHOULD_GET_TIME <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{shouldGetBeginTime}"/>
                </include>
            </if>
            <if test="shouldGetEndTime!='' and shouldGetEndTime!=null">
                AND aj.SHOULD_GET_TIME <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{shouldGetEndTime}"/>
                </include>
            </if>
            <if test="handleJobBeginTime!='' and handleJobBeginTime!=null">
                AND aj.HANDLE_JOB_TIME <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{handleJobBeginTime}"/>
                </include>
            </if>
            <if test="handleJobEndTime!='' and handleJobEndTime!=null">
                AND aj.HANDLE_JOB_TIME <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{handleJobEndTime}"/>
                </include>
            </if>
            <if test="shouldHandleBeginTime!='' and shouldHandleBeginTime!=null">
                AND aj.SHOULD_HANDLE_TIME <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{shouldHandleBeginTime}"/>
                </include>
            </if>
            <if test="shouldHandleEndTime!='' and shouldHandleEndTime!=null">
                AND aj.SHOULD_HANDLE_TIME <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{shouldHandleEndTime}"/>
                </include>
            </if>
            <choose>
                <when test="leaderId gt 0">
                    <if test="jobStatus!='' and jobStatus!=null">
                        AND aj.JOB_STATUS = #{jobStatus}
                    </if>
                    <!-- GROUP BY aj.id -->
                    <if test="jobStatus==1 or jobStatus==2 or jobStatus==3">
                        ORDER BY instr(#{leaderId},job_belong_to) desc,aj.CREATE_TIME desc
                    </if>
                </when>
                <otherwise>
                    <if test="jobStatus!='' and jobStatus!=null">
                        AND aj.JOB_STATUS = #{jobStatus}
                        <if test="jobStatus gt 0">
                            AND job_belong_to = #{userId}
                        </if>
                    </if>
                    <if test="jobStatus == '' or jobStatus == null ">
                        AND (job_belong_to = #{userId} OR JOB_STATUS = '0')
                    </if>
                    <!-- GROUP BY aj.id -->
                </otherwise>
            </choose>
        </where>
    </select>

    <!-- 工单详情 -->
    <select id="jobInfo" resultType="map">
        SELECT
        ar.id AS "alarmId",
        aj.DEVCODE AS "devcode",
        aj.WELL_CODE AS "wellCode",
        ar.ALARM_TYPE AS "alarmType",
        ar.ALARM_CONTENT AS "alarmContentName",
        ar.STATUS AS "alarmStatus",
        aj.ID AS id,
        ar.JOB_ID AS "jobId",
        aj.JOG_TYPE AS "jobType",
        aj.JOBCODE AS "jobCode",
        aj.JOB_STATUS AS "jobStatus",
        aj.JOB_BELONG_TO AS "jobBelongTo",
        ar.ALARM_VALUE AS "alarmValue",
        ar.ALARM_LEVEL AS "alarmLevel",
        ar.ALARM_MSG AS "alarmMessage",
        <include refid="date_toChar">
            <property name="paramTime" value="aj.CREATE_TIME"/>
        </include>
        AS "createTime",
        <include refid="date_toChar">
            <property name="paramTime" value="ar.ALARM_TIME"/>
        </include>
        AS "alarmTime",
        aj.FIRST_STATE AS "firstState",
        aj.FIRST_STATE_PHOTOS AS "firstStatePhotos",
        aj.GET_JOB_PERSON AS "getJobPerson",
        <include refid="date_toChar">
            <property name="paramTime" value="aj.GET_JOB_TIME"/>
        </include>
        AS "getJobTime",
        <include refid="date_toChar">
            <property name="paramTime" value="aj.SHOULD_GET_TIME"/>
        </include>
        AS "shouldGetTime",
        aj.CONFIRM_JOB_PERSON AS "confirmJobPerson",
        <include refid="date_toChar">
            <property name="paramTime" value="aj.CONFRIM_JOB_TIME"/>
        </include>
        AS "confirmJobTime",
        aj.HANDLE_JOB_Person AS "handleJobPerson",
        <include refid="date_toChar">
            <property name="paramTime" value="aj.HANDLE_JOB_TIME"/>
        </include>
        AS "handleJobTime",
        <include refid="date_toChar">
            <property name="paramTime" value="aj.SHOULD_HANDLE_TIME"/>
        </include>
        AS "shouldHandleTime",
        aj.HANDLE_MESSAGE AS "handleMessage",
        aj.HANDLE_PHOTOS AS "handlePhotos",
        aj.JOB_FLAG_1 AS "jobFlag",
        coalesce(aj.FLOW,'') AS flow,
        bwi.RESPONSIBLE_DEPT AS deptid
        FROM alarm_job aj
        LEFT JOIN alarm_records ar ON aj.ID = ar.JOB_ID
        LEFT JOIN bus_well_info bwi ON aj.well_code = bwi.well_code
        WHERE aj.ID = ar.JOB_ID and aj.id = #{id}
        ORDER BY ar.ID DESC LIMIT 1
    </select>

    <!-- 工单处理 -->
    <!--<update id="getJob" flushCache="true">-->
    <!--update alarm_job set job_status = 1,-->
    <!--job_belong_to = #{personId},&lt;!&ndASh;add 0605&ndASh;&gt;-->
    <!--get_job_person = #{personId},get_job_time = now()-->
    <!--where id = #{id}-->
    <!--</update>-->
    <update id="getJob" flushCache="true">
        update alarm_job set job_status = 1,
        JOB_BELONG_TO = #{personId},<!--add 0605-->
        GET_JOB_PERSON = #{personId},
        GET_JOB_TIME = #{getTime},
        SHOULD_HANDLE_TIME = #{shouldHandleTime}
        where id = #{id}
    </update>

    <update id="confirmJob" flushCache="true">
        update alarm_job set confirm_job_person = #{personId},
        <!--job_belong_to = #{personId},add 0605-->
        confrim_job_time = now(),
        <!--        first_state = concat( ifnull(first_state,''),concat('|',#{firstState})),
                first_state_photos =concat( ifnull(first_state_photos,''),concat('|',#{firstStatePhotos})),-->
        first_state = #{firstState},
        first_state_photos =#{firstStatePhotos},
        job_status = (case when #{needHandle}='1' then 2 else 3 end )
        where id = #{id}
    </update>

    <update id="transferJob" flushCache="true">
        update alarm_job set
        job_belong_to = #{transferPerson},<!--add 0605-->
        flow = concat(coalesce(flow,''),',',#{flowRec})
        where id = #{id}
    </update>

    <update id="saveJob" flushCache="true">
        update alarm_job
        set handle_message = #{handleMessage},
        handle_photos = #{handlePhotos}
        where id = #{id}
    </update>

    <update id="overJob" flushCache="true">
        update alarm_job
        set job_status = 3,
        handle_job_person = #{personId},
        handle_job_time = now(),
        handle_message = #{handleMessage},
        handle_photos = #{handlePhotos}
        where id = #{id}
    </update>

    <update id="overJobAlarmRecrds" flushCache="true">
        UPDATE alarm_records
        set   status = 2
        WHERE job_id=#{id}
    </update>

    <update id="confirmOverJobAndAlarm" flushCache="true">
        <!--   call overJob(#{id},#{personId},#{handleMessage},#{handlePhotos}) -->
        UPDATE alarm_job
        SET job_status = 3 ,handle_job_person = #{personId},
        first_state = #{firstState},
        first_state_photos =#{firstStatePhotos},
        confirm_job_person = #{personId},confrim_job_time = now(),
        handle_job_time = now()
        WHERE alarm_job.ID = #{id}
    </update>

    <update id="confirmOverAlarmRecords" flushCache="true">
     UPDATE alarm_records
     SET     status = 2
     WHERE job_id = #{id}
    </update>

    <update id="overAlarm" flushCache="true">
        update alarm_records set status = 2
        where job_id = #{jobId}
    </update>

    <update id="handleJob" flushCache="true">
        update alarm_job set job_status = 2,
        confirm_job_person = #{personId},
        job_belong_to = #{personId},
        confrim_job_time = now()
        where id = #{id}
    </update>

    <!-- 根据设备查询权属单位人员id-->
    <select id="selectUserByWellCode" resultType="com.casic.missiles.modular.system.dto.UserClientView">
     SELECT DISTINCT u.id AS id,c.clientid AS clientid
     FROM bus_well_info w
     LEFT JOIN sys_dept s ON w.DEPTID=s.ID
     LEFT JOIN sys_user u ON u.deptid=s.id
     LEFT JOIN sys_user_client c ON u.id=c.userid
     WHERE  u.status=1 AND w.valid=1
     AND w.well_code=#{wellCode}
    </select>

    <!-- sys_user_client APP推送用-->
    <select id="selectClientIdByUser" resultType="String">
        SELECT CLIENTID
        FROM sys_user_client
        WHERE USERID = #{userId}
    </select>
    <select id="selectRoles" resultType="com.casic.missiles.modular.system.dto.SelectDto">
     SELECT id,sr.dataScope AS name
     FROM sys_role sr
    </select>
    <select id="selectUseIds" resultType="Long">

        SELECT DISTINCT(sur.`USERID`) FROM sys_user_role_relation sur
        WHERE sur.`ROLEID` IN
        <foreach collection="roleIds" item="id" index="index" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </select>

</mapper>
