<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//oos.juyouhx.com//DTD Mapper 3.0//EN" "http://oss.juyouhx.com/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.scpyun.platform.standard.jilinsscgsdp.eventflow" name="流程">

    <select id="getEventFlowSetting" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo" resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo">
        SELECT
            id,
            version
        FROM
            jl_event_flow_setting
        WHERE
            hierarchy = #{hierarchy}
        AND type = #{eventFlowSettingType}
        <if test="version == null or version == ''">
            AND is_history = 0
        </if>
        <if test="version != null and version != ''">
            AND version = #{version}
        </if>
        ORDER BY order_no DESC
        LIMIT 1
    </select>

    <select id="getEventFlowSettingByID" parameterType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlEventFlowSetting" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.JlEventFlowSetting">
        SELECT
            id,
            version,
            hierarchy,
            order_no orderNo,
            type
        FROM
            jl_event_flow_setting
        WHERE
            id = #{id}
    </select>

    <select id="getFlowLogCount" parameterType="map" resultType="Integer">
        SELECT
            COUNT(1)
        FROM
            jl_event_flow_log
        WHERE
            event_id = #{event_id}
        AND `status` != 3
        AND office_id = #{_user.company_id}
        AND del_flag = '0'
        ORDER BY create_time DESC
        LIMIT 1
    </select>

    <select id="getFlowLogOfficeIdTimeAsc" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        SELECT
            create_by createBy,
            office_id officeId,
            IFNULL(IF(grade_user_id != '',1,0),0) gradeUserId,
            flow_id flowId,
            flow_version flowVersion
        FROM
            jl_event_flow_log
        WHERE
            event_id = #{eventId}
        AND del_flag = '0'
        ORDER BY create_time ASC
        LIMIT 1
    </select>

    <select id="getFlowLogOfficeIdTime" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo" resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo">
        SELECT
            office_id officeId,
            log_type logType,
            IFNULL(grade_user_id,'-1') gradeUserId
        FROM
            jl_event_flow_log
        WHERE
            flow_id = #{id}
        AND event_id = #{eventId}
        AND del_flag = '0'
        ORDER BY create_time DESC
        LIMIT 1
    </select>

    <!--新增数据-->
    <insert id="insertEventFlowLog" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo">
        INSERT INTO jl_event_flow_log(
            id,
            event_id,
            flow_version,
            flow_id,
            office_id,
            grade_user_id,
            status,
            log_type,
            assignment_type,
            risk_level,
            term_time,
            log_result,
            event_check,
            resolve_name,
            resolve_unit,
            resolve_hierarchy,
            resolve_time,
            is_satisfaction,
            resolve_process,
            is_resolve,
            warning_level,
            contacts,
<!--            <if test="log_type == '3'.toString()">-->
<!--                assign_time,-->
<!--            </if>-->
            <if test="resolveWay != null and resolveWay != ''">
                resolve_way,
            </if>
            <if test="assignResult != null and assignResult != ''">
                assign_result,
            </if>
            <if test="is_dispatch_office != null and is_dispatch_office != ''">
                is_dispatch_office,
            </if>
            attachment,
            deal_warn_time,
            deal_end_time,
            create_by,
            create_name,
            create_time,
            scanning_time
        ) VALUES (
            UUID(),
            #{eventId},
            #{flowVersion},
            #{flowId},
            #{officeId},
            #{gradeUserId},
            #{status},
            #{logType},
            #{assignmentType},
            #{riskLevel},
            #{termTime},
            #{logResult},
            #{eventCheck},
            #{resolveName},
            #{resolveUnit},
            #{resolveHierarchy},
            #{resolveTime},
            #{isSatisfaction},
            #{resolveProcess},
            #{isResolve},
            #{warningLevel},
            #{contacts},
<!--            <if test="log_type == '3'.toString()">-->
<!--                NOW(),-->
<!--            </if>-->
            <if test="resolveWay != null and resolveWay != ''">
                #{resolveWay},
            </if>
            <if test="assignResult != null and assignResult != ''">
                #{assignResult},
            </if>
            <if test="is_dispatch_office != null and is_dispatch_office != ''">
                #{is_dispatch_office},
            </if>
            #{attachment},
            #{dealWarnTime},
            #{dealEndTime},
            #{_user.id},
            #{_user.name},
            NOW(),
            NOW()
        )
    </insert>

    <select id="selectEventFlowByEventId" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        SELECT
            id
        FROM
            jl_event_flow_log
        WHERE
            event_id = #{id}
        AND office_id = #{_user.company_id}
        ORDER BY create_time DESC
        LIMIT 1
    </select>

    <update id="updateEventFlowLogStatusByID" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" name="更改受理状态" desc="更改受理状态" expose="true">
        UPDATE
            jl_event_flow_log
        SET
            status = '2',
            <if test="assignResult != null and assign_result != ''">
                assign_result = #{assignResult},
            </if>
            receive_time = NOW(),
            update_by = #{_user.id},
            update_time = NOW(),
            scanning_time = NOW(),
            is_scanned = '0'
        WHERE
            id = #{id}
    </update>

    <update id="updateEventFlowLogStatus" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        UPDATE
            jl_event_flow_log
        SET
            status = #{status},
            risk_level = #{riskLevel},
            term_time = #{termTime},
            complete_time = NOW(),
            <!--<if test="logType!=null and logType!=''">
                log_type=#{logType},
            </if>-->
            <if test="logType!=null and logType!=''">
                deal_log_type=#{logType},
            </if>
            <if test="logResult!=null and logResult!=''">
                log_result=#{logResult},
            </if>
            <if test="logType == '4'.toString()">
                back_time = NOW(),
            </if>
            <if test="logType == '3'.toString()">
                assign_time = NOW(),
            </if>
            <if test="logType == '3'.toString()">
                <if test="assignResult!=null and assignResult!=''">
                    assign_result=#{assignResult},
                </if>
            </if>
            <if test="isResolve != null and isResolve != ''">
                is_resolve = #{isResolve},
            </if>
            <!--<if test="isResolve == '1'.toString()">
                resolve_time = NOW(),
            </if>-->
            <if test="resolveTime !=null and resolveTime!=''">
                resolve_time = #{resolveTime},
            </if>
            <if test="resolveName!=null and resolveName!=''">
                resolve_name=#{resolveName},
            </if>
            <if test="resolveUnit!=null and resolveUnit!=''">
                resolve_unit=#{resolveUnit},
            </if>
            <if test="resolveHierarchy!=null and resolveHierarchy!=''">
                resolve_hierarchy=#{resolveHierarchy},
            </if>
            <if test="resolveWay!=null and resolveWay!=''">
                resolve_way=#{resolveWay},
            </if>
            <if test="isSatisfaction!=null and isSatisfaction!=''">
                is_satisfaction=#{isSatisfaction},
            </if>
            <if test="resolveProcess!=null and resolveProcess!=''">
                resolve_process=#{resolveProcess},
            </if>
            update_by = #{_user.id},
            update_time = NOW(),
            scanning_time = NOW(),
            is_scanned = '0'
        WHERE
            id = #{id}
    </update>

    <update id="updateEventFlowLogStatusEnd" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        UPDATE
            jl_event_flow_log
        SET
            status = '3',
            <if test="eventCheck != null and eventCheck!= ''">
                event_check = #{eventCheck},
            </if>
            <if test="logType != null and logType!= ''">
                deal_log_type = #{logType},
            </if>
            <!--<if test="eventCheck != null and eventCheck!= ''">
                log_result = #{eventCheck},
            </if>-->
            <if test="isResolve != null and isResolve != ''">
                is_resolve = #{isResolve},
            </if>
            <if test="isResolve == '1'.toString()">
                resolve_time = NOW(),
            </if>
            <if test="resolveName!=null and resolveName!=''">
                resolve_name=#{resolveName},
            </if>
            <if test="resolveUnit!=null and resolveUnit!=''">
                resolve_unit=#{resolveUnit},
            </if>
            <if test="resolveHierarchy!=null and resolveHierarchy!=''">
                resolve_hierarchy=#{resolveHierarchy},
            </if>
            <if test="resolveWay!=null and resolveWay!=''">
                resolve_way=#{resolveWay},
            </if>
            <if test="isSatisfaction!=null and isSatisfaction!=''">
                is_satisfaction=#{isSatisfaction},
            </if>
            <if test="resolveProcess!=null and resolveProcess!=''">
                resolve_process=#{resolveProcess},
            </if>
            complete_time = NOW(),
            update_by = #{_user.id},
            update_time = NOW(),
            scanning_time = NOW(),
            is_scanned = '0'
        WHERE
            id = #{id}
    </update>


    <!--网格员新增数据-->
    <insert id="insertFirstEventFlowLog" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo">
        INSERT INTO jl_event_flow_log(
            id,
            event_id,
            flow_version,
            flow_id,
            office_id,
            grade_user_id,
            status,
            log_type,
            deal_log_type,
            assignment_type,
            risk_level,
            term_time,
            log_result,
            resolve_name,
            resolve_unit,
            resolve_hierarchy,
            resolve_time,
            is_satisfaction,
            resolve_process,
            is_resolve,
            warning_level,
            contacts,
            complete_time,
            <if test="logType == '3'.toString()">
                assign_time,
            </if>
            receive_time,
            attachment,
            deal_warn_time,
            deal_end_time,
            create_by,
            create_name,
            create_time,
            <if test="assignResult!=null and assignResult!=''">
                assign_result,
            </if>
            scanning_time
        ) VALUES (
            UUID(),
            #{eventId},
            #{flowVersion},
            #{flowId},
            #{_user.company_id},
            #{gradeUserId},
            #{status},
            #{logType},
            #{logType},
            #{assignmentType},
            #{riskLevel},
            #{termTime},
            #{logResult},
            #{resolveName},
            #{resolveUnit},
            #{resolveHierarchy},
            #{resolveTime},
            #{isSatisfaction},
            #{resolveProcess},
            '2',
            #{warningLevel},
            #{contacts},
            NOW(),
            <if test="logType == '3'.toString()">
                NOW(),
            </if>
            NOW(),
            #{attachment},
            #{dealWarnTime},
            #{dealEndTime},
            #{_user.id},
            #{_user.name},
            NOW(),
            <if test="assignResult!=null and assignResult!=''">
                #{assignResult},
            </if>
            NOW()
        )
    </insert>

    <!-- 更新数据 -->
    <update id="updateEventFlowLog" parameterType="map">
        UPDATE
            jl_event_flow_log
        SET
            <if test="log_result != null and log_result != ''">
                log_result = #{log_result},
            </if>
            <if test="resolve_name != null and resolve_name != ''">
                resolve_name = #{resolve_name},
            </if>
            <if test="resolve_unit != null and resolve_unit != ''">
                resolve_unit = #{resolve_unit},
            </if>
            <if test="resolve_hierarchy != null and resolve_hierarchy != ''">
                resolve_hierarchy = #{resolve_hierarchy},
            </if>
            <if test="resolve_time != null and resolve_time != ''">
                resolve_time = #{resolve_time},
            </if>
            <if test="is_satisfaction != null and is_satisfaction != ''">
                is_satisfaction = #{is_satisfaction},
            </if>
            <if test="resolve_process != null and resolve_process != ''">
                resolve_process = #{resolve_process},
            </if>
            <if test="is_resolve != null and is_resolve != ''">
                is_resolve = #{is_resolve},
            </if>
            <if test="warning_level != null and warning_level != ''">
                warning_level = #{warning_level},
            </if>
            <if test="contacts != null and contacts != ''">
                contacts = #{contacts},
            </if>
            <if test="attachment != null and attachment != ''">
                attachment = #{attachment},
            </if>
            <if test="risk_level != null and risk_level != ''">
                risk_level = #{risk_level},
            </if>
                update_by = #{_user.id},
                update_time = NOW(),
                scanning_time = NOW(),
                is_scanned = '0'
        WHERE
            id = #{id}
    </update>

    <select id="getCountLogAssignment" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" resultType="Integer">
        SELECT
            COUNT(id)
        FROM
            jl_event_flow_log
        WHERE
            event_id = #{eventId}
        AND log_type = '3'
        AND assignment_type = '3'
        AND del_flag = '0'
    </select>


    <select id="findFlowLogProcessList" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        SELECT
            jefl.id,
            jefl.event_id eventId,
            jefl.flow_version flowVersion,
            jefl.flow_id flowId,
            jefl.office_id officeId,
            jefl.status,
            jefl.log_type logType,
            jefl.assign_result assignResult,
            jefl.assignment_type assignmentType,
            jefl.log_result logResult,
            jefl.grade_user_id gradeUserId,
            jefl.event_check eventCheck,
            DATE_FORMAT(jefl.complete_time,'%Y-%m-%d %T') completeTime,
            DATE_FORMAT(jefl.back_time,'%Y-%m-%d %T') backTime,
            DATE_FORMAT(jefl.assign_time,'%Y-%m-%d %T') assignTime,
            DATE_FORMAT(jefl.receive_time,'%Y-%m-%d %T') receiveTime,
            DATE_FORMAT(jefl.create_time,'%Y-%m-%d %T') createTime,
            hierarchy,
            IF(hierarchy = '1', CONCAT('网格员：', su.`name`),CONCAT(so.`name`,IF(suc.id IS NULL, '', CONCAT('：', suc.`name`)))) hierarchyName,
            (select event_code from jl_event where id=jefl.event_id and del_flag='0') as eventCode,
            (select status from jl_event where id=jefl.event_id and del_flag='0') as eventStatus,
            jefl.is_dispatch_office,
            jefl.is_resolve isResolve,
            jefl.resolve_name as resolveName,
            (select status from jl_event where id=#{eventId}) as eventStatus,
            jefl.deal_log_type
        FROM
            jl_event_flow_log jefl
        LEFT JOIN jl_event_flow_setting jefs ON jefl.flow_id = jefs.id
        LEFT JOIN sys_office so ON jefl.office_id = so.id
        LEFT JOIN sys_user su ON jefl.grade_user_id = su.id
        LEFT JOIN sys_user suc ON jefl.update_by = suc.id
        WHERE
            jefl.event_id = #{eventId}
        AND jefl.del_flag = '0'
        ORDER BY jefl.create_time DESC
    </select>

    <select id="findFlowLogProcessInfo" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        SELECT
            jefl.id,
            jefl.is_resolve isResolve,
            jefl.resolve_name resolveName,
            jefl.resolve_unit resolveUnit,
            jefl.resolve_hierarchy resolveHierarchy,
            jefl.resolve_way resolveWay,
            DATE_FORMAT(jefl.resolve_time,'%Y-%m-%d %T') resolveTime,
            jefl.is_satisfaction isSatisfaction,
            jefl.resolve_process resolveProcess
        FROM
            jl_event_flow_log jefl
        WHERE
            jefl.id=#{id} AND jefl.del_flag = '0'
    </select>

    <select id="selectIdByEventIdList" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqWarningTaskVo" resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqWarningTaskVo">
        SELECT
            id,
            office_id deptId
        FROM
            jl_event_flow_log
        WHERE
            event_id = #{eventId}
        AND `status` != 3
        AND del_flag = '0'
    </select>

    <update id="updateEarlyEndTime" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqWarningTaskVo">
        UPDATE
            jl_event_flow_log
        SET
            is_early_warning = #{isEarlyWarning},
            scanning_time = NOW(),
            <if test="isEarlyWarning == '2'.toString()">
                deal_warn_time = NOW(),
            </if>
            <if test="isEarlyWarning == '3'.toString()">
                deal_end_time = NOW(),
            </if>
            is_scanned = '0'
        WHERE
            id = #{id}
    </update>

    <update id="updateEventFlowDel" parameterType="map">
        UPDATE
            jl_event_flow_log
        SET
            del_flag = '1',
            update_by = #{_user.id},
            update_time = NOW(),
            scanning_time = NOW(),
            is_scanned = '0'
        WHERE
            event_id = #{event_id}
    </update>

    <insert id="insertFirstEventFlowLogEnd" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo">
        INSERT INTO jl_event_flow_log(
            id,
            event_id,
            flow_version,
            flow_id,
            office_id,
            grade_user_id,
            status,
            log_type,
            assignment_type,
            risk_level,
            term_time,
            log_result,
            resolve_name,
            resolve_unit,
            resolve_hierarchy,
            resolve_time,
            is_satisfaction,
            resolve_process,
            is_resolve,
            warning_level,
            contacts,
            complete_time,
            <if test="logType == '3'.toString()">
                assign_time,
            </if>
            attachment,
            resolve_way,
            deal_warn_time,
            deal_end_time,
            create_by,
            create_name,
            create_time,
            scanning_time,
            receive_time
        ) VALUES (
            UUID(),
            #{eventId},
            #{flowVersion},
            #{flowId},
            #{_user.company_id},
            #{gradeUserId},
            #{status},
            #{logType},
            #{assignmentType},
            #{riskLevel},
            #{termTime},
            #{logResult},
            #{resolveName},
            #{resolveUnit},
            #{resolveHierarchy},
            #{resolveTime},
            #{isSatisfaction},
            #{resolveProcess},
            #{isResolve},
            #{warningLevel},
            #{contacts},
            NOW(),
            <if test="logType == '3'.toString()">
                NOW(),
            </if>
            #{attachment},
            #{resolveWay},
            #{dealWarnTime},
            #{dealEndTime},
            #{_user.id},
            #{_user.name},
            NOW(),
            NOW(),
            NOW()
        )
    </insert>

    <select id="selectEventNumList" parameterType="map" resultType="map">
        SELECT
            id,
            log_type,
            status
        FROM
            jl_event_flow_log
        WHERE
            event_id = #{event_id}
        AND grade_user_id = #{_user.id}
    </select>

    <select id="selectRiskLevelList" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" expose="true" name="查询风险历史" desc="查询风险历史">
        SELECT
            create_name,
            risk_level
        FROM
            jl_event_flow_log
        WHERE
            event_id = #{event_id}
        AND `status` = '3'
        ORDER BY create_time
    </select>

    <select id="getUnitCompanyByEventId" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" resultType="Integer">
        SELECT
            COUNT(1)
        FROM
            jl_event_flow_log
        WHERE
            event_id = #{eventId}
        AND `status` != '3'
        <!--AND (log_type = '5' or log_type = '3')-->
        and is_dispatch_office='1'
    </select>

    <select id="getHasResolveCount" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" resultType="Integer">
        SELECT
            COUNT(1)
        FROM
            jl_event_flow_log
        WHERE
            event_id = #{eventId}
        AND status='3' and is_resolve='1'
        and del_flag='0'
    </select>

    <select id="getHasNoResolveCount" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" resultType="Integer">
        SELECT
            COUNT(1)
        FROM
            jl_event_flow_log
        WHERE
            event_id = #{eventId}
        AND status!='3'
        and del_flag='0' and is_dispatch_office='1'
    </select>

    <select id="getHasBackCount" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" resultType="Integer">
        SELECT
            COUNT(1)
        FROM
            jl_event_flow_log
        WHERE
            event_id = #{eventId}
        AND status='3' and deal_log_type='4'
        and del_flag='0' and is_dispatch_office='1'
    </select>

    <select id="getLatestFlowLog" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo" resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        select log_type as logType,create_by as createBy
        from jl_event_flow_log
        where del_flag='0' and event_id=#{eventId}
        order by create_time desc limit 1
    </select>

    <select id="getOfficeIdById" parameterType="String" resultType="String">
        select office_id as officeId from  sys_user where id=#{id}
    </select>

    <select id="getLatestLogType" parameterType="map" resultType="String">
        select log_type from jl_event_flow_log where event_id=#{id} and del_flag='0' order by create_time desc limit 1
    </select>

</mapper>
