<?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="Base_Column_List">
        ID AS id, JOBCODE AS jobcode, JOG_TYPE AS jogType,
         WELL_CODE AS wellCode, DEVCODE AS devcode, DATE_FORMAT(CREATE_TIME,'%Y-%m-%d %H:%i:%s') AS createTime,
          JOB_STATUS AS jobStatus, GET_JOB_PERSON AS getJobPerson,
          DATE_FORMAT(GET_JOB_TIME,'%Y-%m-%d %H:%i:%s') AS getJobTime, FIRST_STATE AS firstState,
          FIRST_STATE_PHOTOS AS firstStatePhotos, CONFIRM_JOB_PERSON AS confirmJobPerson,
           DATE_FORMAT(CONFRIM_JOB_TIME,'%Y-%m-%d %H:%i:%s') AS confrimJobTime, HANDLE_JOB_Person AS handleJobPerson,
            DATE_FORMAT(HANDLE_JOB_TIME,'%Y-%m-%d %H:%i:%s') AS handleJobTime, HANDLE_MESSAGE AS handleMessage,
             HANDLE_PHOTOS AS handlePhotos, FLOW AS flow
    </sql>

    <sql id="Base_Job_List">
        ar.id  AS alarmId,
        aj.DEVCODE AS devcode,
        aj.WELL_CODE AS wellCode,
        ar.ALARM_TYPE AS alarmType,
        ar.ALARM_CONTENT AS alarmContentName,
        aj.ID AS id,
        aj.JOG_TYPE AS jobType,
        aj.JOBCODE AS jobCode,
        aj.JOB_STATUS AS jobStatus,
        aj.JOB_BELONG_TO AS jobBelongTo,
        aj.GET_JOB_PERSON AS getJobPerson,
        ar.ALARM_VALUE AS alarmValue,
        DATE_FORMAT( aj.CREATE_TIME, '%Y-%m-%d %H:%i:%s' ) AS createTime,
        DATE_FORMAT( ar.ALARM_TIME, '%Y-%m-%d %H:%i:%s' ) AS alarmTime,
        DATE_FORMAT( aj.GET_JOB_TIME, '%Y-%m-%d %H:%i:%s' ) AS getJobTime,
        aj.CONFIRM_JOB_PERSON AS confirmJobPerson,
        DATE_FORMAT( aj.CONFRIM_JOB_TIME, '%Y-%m-%d %H:%i:%s' ) AS confirmJobTime,
        aj.HANDLE_JOB_Person AS handleJobPerson,
        DATE_FORMAT( aj.HANDLE_JOB_TIME, '%Y-%m-%d %H:%i:%s' ) AS handleJobTime
    </sql>

    <select id="countByJobStatus" resultType="map">
        SELECT ifnull(SUM(IF(job_status='0',1,0)),0) AS beforeGet,
        ifnull(SUM(IF(job_status='1',1,0)),0) AS beforeConfirm,
        ifnull(SUM(IF(job_status='2',1,0)),0) AS inHandle,
        ifnull(SUM(IF(job_status='3',1,0)),0) AS over,
        count(1) as total
        FROM
        (
            SELECT     IFNULL(MAX(ar.ID),0) 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} or bwi.deptid = #{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">
                <![CDATA[ and datediff(now(),aj.create_time) <= #{spanDays} ]]>
            </when>
            <otherwise>
                <![CDATA[ and datediff(now(),aj.create_time) < 30 ]]>
            </otherwise>
        </choose>
        GROUP BY aj.id
        ) c
    </select>


    <select id="countMyJob" resultType="map">
        SELECT ifnull(SUM(IF(job_status='0',1,0)),0) AS beforeGet,
        ifnull(SUM(IF(job_status='1',1,0)),0) AS beforeConfirm,
        ifnull(SUM(IF(job_status='2',1,0)),0) AS inHandle,
        ifnull(SUM(IF(job_status='3',1,0)),0) AS over,
        count(1) as total
        FROM
        (
            SELECT     IFNULL(MAX(ar.ID),0) 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.deptid in ( ${deptIds} )
<!--            WHERE  bwi.responsible_dept = #{deptId}  or bwi.deptid = #{deptId} -->
            ) 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">
                <![CDATA[ and datediff(now(),aj.create_time) <= #{spanDays} ]]>
            </when>
            <otherwise>
                <![CDATA[ and datediff(now(),aj.create_time) < 30 ]]>
            </otherwise>
        </choose>
        GROUP BY aj.id
        ) c
    </select>

    <select id="countJobGroup" resultType="java.lang.Integer">
        SELECT count(*)
        FROM alarm_job aj
        LEFT JOIN alarm_records ar ON aj.ID = ar.JOB_ID
        WHERE ar.ID = (SELECT MAX(ID) FROM alarm_records tmp WHERE tmp.JOB_ID = ar.JOB_ID)
        AND  aj.WELL_CODE IN (
            SELECT WELL_CODE
            FROM bus_well_info bwi
            INNER JOIN bus_group_well bgw ON bgw.WELL_ID = bwi.ID
            INNER JOIN bus_group bg ON bg.ID = bgw.GROUP_ID
            INNER JOIN bus_group_user bgu ON ( bgu.GROUP_ID = bg.ID AND bgu.USER_ID = #{userId} )
        )
        AND aj.JOB_STATUS = '0'
        AND ar.ALARM_TYPE = '1'
    </select>

    <select id="countJobUser" resultType="java.lang.Integer">
        SELECT count(*)
        FROM alarm_job aj
        LEFT JOIN alarm_records ar ON aj.ID = ar.JOB_ID
        WHERE ar.ID = (SELECT MAX(ID) FROM alarm_records tmp WHERE tmp.JOB_ID = ar.JOB_ID)
        AND  aj.HANDLE_JOB_PERSON = #{userId}
        AND aj.JOB_STATUS = '3'
        AND ar.ALARM_TYPE = '1'
    </select>


    <select id="jobList" resultType="map">
          SELECT max(ar.id) as alarmId,aj.DEVCODE as devcode,aj.WELL_CODE as wellCode,ar.ALARM_TYPE as alarmType,
          ar.ALARM_CONTENT as alarmContentName,aj.ID as id,
          aj.JOG_TYPE as jobType,aj.JOBCODE as jobCode,
          aj.JOB_STATUS as jobStatus,aj.JOB_BELONG_TO as jobBelongTo,
          aj.GET_JOB_PERSON as getJobPerson,ar.ALARM_VALUE AS alarmValue,
        DATE_FORMAT(aj.CREATE_TIME,'%Y-%m-%d %H:%i:%s') as createTime,
        DATE_FORMAT(ar.ALARM_TIME,'%Y-%m-%d %H:%i:%s') as alarmTime,
        DATE_FORMAT(aj.GET_JOB_TIME,'%Y-%m-%d %H:%i:%s') as getJobTime,
<!--           aj.FIRST_STATE as firstState,
          aj.FIRST_STATE_PHOTOS as firstStatePhotos,-->
          aj.CONFIRM_JOB_PERSON as confirmJobPerson,
        DATE_FORMAT(aj.CONFRIM_JOB_TIME,'%Y-%m-%d %H:%i:%s') as confirmJobTime,aj.HANDLE_JOB_Person as handleJobPerson,
        DATE_FORMAT(aj.HANDLE_JOB_TIME,'%Y-%m-%d %H:%i:%s') as handleJobTime,bwi.DEPTID AS deptid
        <!--         aj.HANDLE_MESSAGE as handleMessage
                ,aj.HANDLE_PHOTOS as handlePhotos,aj.FLOW as flow
        -->
        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 ar.JOB_ID > 0
        <if test="jobStatus!='' and jobStatus!=null">
            AND aj.JOB_STATUS = #{jobStatus}
        </if>
        <if test="beginTime!='' and beginTime!=null">
            <![CDATA[ and aj.CREATE_TIME >= #{beginTime}]]>
        </if>
        <if test="endTime!='' and endTime!=null">
            <![CDATA[ and aj.CREATE_TIME <= #{endTime}]]>
        </if>
        <if test="keywords!='' and keywords!=null">
            and aj.WELL_CODE like concat('%',CONCAT(#{keywords},'%'))
        </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="jobListApp" resultType="map">
        SELECT max(ar.id) as alarmId,aj.DEVCODE as devcode,aj.WELL_CODE as wellCode,ar.ALARM_TYPE as alarmType,
        ar.ALARM_CONTENT as alarmContentName,aj.ID as id,
        aj.JOG_TYPE as jobType,aj.JOBCODE as jobCode,
        aj.JOB_STATUS as jobStatus,aj.JOB_BELONG_TO as jobBelongTo,
        aj.GET_JOB_PERSON as getJobPerson,ar.ALARM_VALUE AS alarmValue,
        DATE_FORMAT(aj.CREATE_TIME,'%Y-%m-%d %H:%i:%s') as createTime,
        DATE_FORMAT(ar.ALARM_TIME,'%Y-%m-%d %H:%i:%s') as alarmTime,
        DATE_FORMAT(aj.GET_JOB_TIME,'%Y-%m-%d %H:%i:%s') as getJobTime,
        <!--           aj.FIRST_STATE as firstState,
                  aj.FIRST_STATE_PHOTOS as firstStatePhotos,-->
        aj.CONFIRM_JOB_PERSON as confirmJobPerson,
        DATE_FORMAT(aj.CONFRIM_JOB_TIME,'%Y-%m-%d %H:%i:%s') as confirmJobTime,aj.HANDLE_JOB_Person as handleJobPerson,
        DATE_FORMAT(aj.HANDLE_JOB_TIME,'%Y-%m-%d %H:%i:%s') as handleJobTime,bwi.DEPTID AS deptid
        <!--         aj.HANDLE_MESSAGE as handleMessage
                ,aj.HANDLE_PHOTOS as handlePhotos,aj.FLOW as flow
        -->
        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 ar.JOB_ID > 0
        AND ar.ALARM_TYPE = '1'
<!--        AND bwi.responsible_dept = #{deptId} 20190825 remove responsible_dept -->
        <if test="jobStatus!='' and jobStatus!=null">
            AND aj.JOB_STATUS = #{jobStatus}
        </if>
        <if test="beginTime!='' and beginTime!=null">
            <![CDATA[ and aj.CREATE_TIME >= #{beginTime}]]>
        </if>
        <if test="endTime!='' and endTime!=null">
            <![CDATA[ and aj.CREATE_TIME <= #{endTime}]]>
        </if>
        <if test="keywords!='' and keywords!=null">
            and aj.WELL_CODE like concat('%',CONCAT(#{keywords},'%'))
        </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 and jobStatus==3 ">
                group by aj.id order by instr(#{leaderId},job_belong_to) desc,aj.CREATE_TIME desc
            </when>
            <otherwise>
                AND ( job_belong_to IS NULL OR job_belong_to = #{userId} )
                group by aj.id
            </otherwise>
        </choose> -->
        group by aj.id
    </select>

    <select id="jobListScope" resultType="map">
        SELECT
          <include refid="Base_Job_List"/>,
          bwi.DEPTID 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 ar.ID = (SELECT MAX(ID) FROM alarm_records tmp WHERE tmp.JOB_ID = ar.JOB_ID)
        <if test="jobStatus!='' and jobStatus!=null">
            AND aj.JOB_STATUS = #{jobStatus}
        </if>
        <if test="beginTime!='' and beginTime!=null">
            <![CDATA[ and aj.CREATE_TIME >= #{beginTime}]]>
        </if>
        <if test="endTime!='' and endTime!=null">
            <![CDATA[ and aj.CREATE_TIME <= #{endTime}]]>
        </if>
        <if test="keywords!='' and keywords!=null">
            and aj.WELL_CODE like concat('%',CONCAT(#{keywords},'%'))
        </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="jobListGroup" resultType="map">
        SELECT <include refid="Base_Job_List"/>
        FROM alarm_job aj
        LEFT JOIN alarm_records ar ON aj.ID = ar.JOB_ID
        WHERE ar.ID = (SELECT MAX(ID) FROM alarm_records tmp WHERE tmp.JOB_ID = ar.JOB_ID)
        AND  aj.WELL_CODE IN (
            SELECT WELL_CODE
            FROM bus_well_info bwi
            INNER JOIN bus_group_well bgw ON bgw.WELL_ID = bwi.ID
            INNER JOIN bus_group bg ON bg.ID = bgw.GROUP_ID
            INNER JOIN bus_group_user bgu ON ( bgu.GROUP_ID = bg.ID AND bgu.USER_ID = #{userId} )
        )
        AND aj.JOB_STATUS = '0'
        AND ar.ALARM_TYPE = '1'
        <if test="beginTime!='' and beginTime!=null">
            <![CDATA[ AND aj.CREATE_TIME >= #{beginTime}]]>
        </if>
        <if test="endTime!='' and endTime!=null">
            <![CDATA[ AND aj.CREATE_TIME <= #{endTime}]]>
        </if>
        <if test="keywords!='' and keywords!=null">
            AND aj.WELL_CODE LIKE concat('%',CONCAT(#{keywords},'%'))
        </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="jobListUser" resultType="map">
        SELECT <include refid="Base_Job_List"/>
        FROM alarm_job aj
        LEFT JOIN alarm_records ar ON aj.ID = ar.JOB_ID
        WHERE ar.ID = (SELECT MAX(ID) FROM alarm_records tmp WHERE tmp.JOB_ID = ar.JOB_ID)
        AND  aj.HANDLE_JOB_PERSON = #{userId}
        AND aj.JOB_STATUS = '3'
        AND ar.ALARM_TYPE = '1'
        <if test="beginTime!='' and beginTime!=null">
            <![CDATA[ AND aj.CREATE_TIME >= #{beginTime}]]>
        </if>
        <if test="endTime!='' and endTime!=null">
            <![CDATA[ AND aj.CREATE_TIME <= #{endTime}]]>
        </if>
        <if test="keywords!='' and keywords!=null">
            AND aj.WELL_CODE LIKE concat('%',CONCAT(#{keywords},'%'))
        </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="jobListAll" resultType="map">
        SELECT *
        FROM (
            SELECT <include refid="Base_Job_List"/>
            FROM alarm_job aj
            LEFT JOIN alarm_records ar ON aj.ID = ar.JOB_ID
            WHERE ar.ID = (SELECT MAX(ID) FROM alarm_records tmp WHERE tmp.JOB_ID = ar.JOB_ID)
            AND  aj.WELL_CODE IN (
            SELECT WELL_CODE
            FROM bus_well_info bwi
            INNER JOIN bus_group_well bgw ON bgw.WELL_ID = bwi.ID
            INNER JOIN bus_group bg ON bg.ID = bgw.GROUP_ID
            INNER JOIN bus_group_user bgu ON ( bgu.GROUP_ID = bg.ID AND bgu.USER_ID = #{userId} )
            )
            AND aj.JOB_STATUS = '0'
            AND ar.ALARM_TYPE = '1'

            UNION DISTINCT

            SELECT <include refid="Base_Job_List"/>
            FROM alarm_job aj
            LEFT JOIN alarm_records ar ON aj.ID = ar.JOB_ID
            WHERE ar.ID = (SELECT MAX(ID) FROM alarm_records tmp WHERE tmp.JOB_ID = ar.JOB_ID)
            AND  aj.HANDLE_JOB_PERSON = #{userId}
            AND aj.JOB_STATUS = '3'
            AND ar.ALARM_TYPE = '1'
        ) uni
        WHERE 1=1
        <if test="jobStatus!='' and jobStatus!=null">
            AND jobStatus = #{jobStatus}
        </if>
        <if test="beginTime!='' and beginTime!=null">
            <![CDATA[ and createTime >= #{beginTime}]]>
        </if>
        <if test="endTime!='' and endTime!=null">
            <![CDATA[ and createTime <= #{endTime}]]>
        </if>
        <if test="keywords!='' and keywords!=null">
            and wellCode like concat('%',CONCAT(#{keywords},'%'))
        </if>
        <if test="alarmType!='' and alarmType!=null">
            AND alarmType = #{alarmType}
        </if>
        <if test="alarmContent!='' and alarmContent!=null">
            AND alarmContentName = #{alarmContent}
        </if>
    </select>



    <select id="jobListExport" resultType="map">
          SELECT max(ar.id) as alarmId,aj.DEVCODE as devcode,aj.WELL_CODE as wellCode,ar.ALARM_TYPE as alarmType,
          ar.ALARM_CONTENT as alarmContentName,aj.ID as id,
          aj.JOG_TYPE as jobType,aj.JOBCODE as jobCode,
          aj.JOB_STATUS as jobStatus,aj.JOB_BELONG_TO as jobBelongTo,
          aj.GET_JOB_PERSON as getJobPerson,ar.ALARM_VALUE AS alarmValue,
        DATE_FORMAT(aj.CREATE_TIME,'%Y-%m-%d %H:%i:%s') as createTime,
        DATE_FORMAT(ar.ALARM_TIME,'%Y-%m-%d %H:%i:%s') as alarmTime,
        DATE_FORMAT(aj.GET_JOB_TIME,'%Y-%m-%d %H:%i:%s') as getJobTime,
<!--           aj.FIRST_STATE as firstState,
          aj.FIRST_STATE_PHOTOS as firstStatePhotos,-->
          aj.CONFIRM_JOB_PERSON as confirmJobPerson,
        DATE_FORMAT(aj.CONFRIM_JOB_TIME,'%Y-%m-%d %H:%i:%s') as confirmJobTime,aj.HANDLE_JOB_Person as handleJobPerson,
        DATE_FORMAT(aj.HANDLE_JOB_TIME,'%Y-%m-%d %H:%i:%s') as handleJobTime,bwi.DEPTID AS deptid
        <!--         aj.HANDLE_MESSAGE as handleMessage
                ,aj.HANDLE_PHOTOS as handlePhotos,aj.FLOW as flow
        -->
        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
        <if test="jobStatus!='' and jobStatus!=null">
            AND aj.JOB_STATUS = #{jobStatus}
        </if>
        <if test="beginTime!='' and beginTime!=null">
            <![CDATA[ and aj.CREATE_TIME >= #{beginTime}]]>
        </if>
        <if test="endTime!='' and endTime!=null">
            <![CDATA[ and aj.CREATE_TIME <= #{endTime}]]>
        </if>
        <if test="keywords!='' and keywords!=null">
            and aj.WELL_CODE like concat('%',CONCAT(#{keywords},'%'))
        </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="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,aj.ID as id,
        aj.JOG_TYPE as jobType,aj.JOBCODE as jobCode,
        aj.JOB_STATUS as jobStatus,aj.JOB_BELONG_TO as jobBelongTo,
        aj.GET_JOB_PERSON as getJobPerson,ar.ALARM_VALUE AS alarmValue,
        DATE_FORMAT(aj.CREATE_TIME,'%Y-%m-%d %H:%i:%s') as createTime,
        DATE_FORMAT(ar.ALARM_TIME,'%Y-%m-%d %H:%i:%s') as alarmTime,
        DATE_FORMAT(aj.GET_JOB_TIME,'%Y-%m-%d %H:%i:%s') as getJobTime,
        aj.FIRST_STATE as firstState,
        aj.FIRST_STATE_PHOTOS as firstStatePhotos,aj.CONFIRM_JOB_PERSON as confirmJobPerson,
        DATE_FORMAT(aj.CONFRIM_JOB_TIME,'%Y-%m-%d %H:%i:%s') as confirmJobTime,aj.HANDLE_JOB_Person as handleJobPerson,
        DATE_FORMAT(aj.HANDLE_JOB_TIME,'%Y-%m-%d %H:%i:%s') as handleJobTime,
        aj.HANDLE_MESSAGE as handleMessage,aj.HANDLE_PHOTOS as handlePhotos,ifnull(aj.FLOW,'') as flow,
        bwi.deptid 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},<!--add 0605-->
        get_job_person = #{personId},get_job_time = now()
        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(concat(ifnull(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 = concat(ifnull(handle_message,''),concat('|',#{handleMessage})),
        handle_photos = concat(ifnull(handle_photos,''),concat('|',#{handlePhotos}))-->
        handle_message = #{handleMessage},
        handle_photos = #{handlePhotos}
        where id = #{id}
    </update>

    <update id="overJobAndAlarm" flushCache="true"  >
     <!--   call overJob(#{id},#{personId},#{handleMessage},#{handlePhotos}) -->
        UPDATE alarm_job  aj, alarm_records ar
        set aj.job_status = 3 ,aj.handle_job_person = #{personId},
        aj.handle_job_time = now(),aj.handle_message = #{handleMessage},
        aj.handle_photos = #{handlePhotos},ar.status = 2
        WHERE aj.ID = ar.job_id
        AND aj.ID = #{id}
    </update>

    <update id="overJobAndAlarmByDevCode" flushCache="true"  >
        UPDATE alarm_job  aj, alarm_records ar
        set aj.job_status = 3 ,
            aj.handle_job_time = now(),
            aj.handle_message = #{handleMessage},
            aj.handle_photos = #{handlePhotos},
            ar.status = 2
        WHERE aj.ID = ar.job_id
        AND aj.DEVCODE = #{devCode}
        AND aj.job_status = 0
    </update>

    <update id="batchCancel" flushCache="true"  >
        UPDATE alarm_job  aj, alarm_records ar
        set aj.job_status = 3 ,aj.handle_job_person = #{personId},
        aj.handle_job_time = now(),ar.status = 2
        WHERE aj.ID = ar.job_id
<!--        AND aj.ID = #{id}-->
        and aj.job_status = 0
        and aj.well_code in
        (
        SELECT bwi.WELL_CODE
        FROM bus_well_info bwi
        WHERE  bwi.deptid in ( ${deptIds} )
        )
    </update>

    <update id="confirmOverJobAndAlarm" flushCache="true"  >
     <!--   call overJob(#{id},#{personId},#{handleMessage},#{handlePhotos}) -->
        UPDATE alarm_job  aj, alarm_records ar
        set aj.job_status = 3 ,aj.handle_job_person = #{personId},
        confirm_job_person = #{personId},confrim_job_time = now(),
        aj.handle_job_time = now(),ar.status = 2
        WHERE aj.ID = ar.job_id
        AND aj.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>

    <insert id="insWellCover" flushCache="true">
        insert into data_wellcover (devcode,descn,status,logtime)
        select devcode,'状态正常','0',now() from alarm_job
        where id = #{id}
    </insert>

    <insert id="insWellCoverByDevCode" flushCache="true">
        insert into data_wellcover (devcode,descn,status,logtime)
        value (#{devCode},'状态正常','0',now())
    </insert>

    <select id="getDevCodeByJobId" resultType="string">
        select devcode from alarm_records where job_id = #{id} limit 1
    </select>
    <select id="getDevCodesByDeptIds" resultType="map">
        select devcode from alarm_job where job_status = 0
        and well_code in
        (
            SELECT bwi.WELL_CODE
            FROM bus_well_info bwi
            WHERE  bwi.deptid in ( ${deptIds} )
        )
    </select>

</mapper>
