<?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.yeejoin.amos.boot.module.common.api.mapper.FailureDetailsMapper">

    <select id="selectAllPage" resultType="com.yeejoin.amos.boot.module.common.api.entity.FailureDetails">
        SELECT
        sequence_nbr,
        current_status,
        failure_equipment_id,
        failure_equipment,
        fault_time,
        fault_phenomenon,
        submission_name,
        submission_pid,
        biz_code,
        submission_time,
        submission_branch,
        submission_branch_id
        FROM
        cb_failure_details
        <where>
            <if test="currentStatus!= null ">
                and current_status = #{currentStatus}
            </if>
            <if test="startTime!= null and endTime != null">
                and submission_time between #{startTime} and #{endTime}
            </if>
            <if test="submissionPid!= null ">
                and submission_pid = #{submissionPid}
            </if>
        </where>
        order by submission_time DESC limit #{current},#{size};
    </select>

    <select id="selectWebPage" resultType="com.yeejoin.amos.boot.module.common.api.dto.FailureDetailsDto">
        SELECT
        sequence_nbr,
        current_status,
        failure_equipment_id,
        failure_equipment,
        fault_time,
        fault_phenomenon,
        submission_name,
        submission_pid,
        biz_code,
        submission_time,
        submission_branch,
        submission_branch_id
        FROM
        cb_failure_details
        <where>
            <if test="currentStatus!= null ">
                and current_status = #{currentStatus}
            </if>
            <if test="startTime!= null and endTime != null">
                and submission_time between #{startTime} and #{endTime}
            </if>
            <if test="submissionName != null ">
                and submission_name = #{submissionName}
            </if>
            <if test="submissionBranchId!= null ">
                and submission_branch_id = #{submissionBranchId}
            </if>
            <if test="sequenceNbr!= null ">
                and  sequence_nbr = #{ sequenceNbr}
            </if>
        </where>
        order by submission_time DESC limit #{current},#{size}
    </select>




    <select id="selectISubPage" resultType="com.yeejoin.amos.boot.module.common.api.entity.FailureDetails">
        SELECT sequence_nbr,
        current_status,
        failure_equipment_id,
        failure_equipment,
        fault_time,
        fault_phenomenon,
        submission_name,
        submission_pid,
        biz_code,
        submission_time,
        submission_branch,
        submission_branch_id
        FROM cb_failure_details
        <where>
            <if test="currentStatus!= null ">
                and current_status = #{currentStatus}
            </if>
            <if test="startTime!= null and endTime != null">
                and submission_time between #{startTime} and #{endTime}
            </if>
            <if test="submissionPid!= null ">
                and submission_pid = #{submissionPid}
            </if>
        </where>
        order by submission_time DESC
        limit #{current},#{size}
    </select>


    <select id="selectInProcessing" resultType="com.yeejoin.amos.boot.module.common.api.entity.FailureDetails">
        SELECT sequence_nbr,
        current_status,
        failure_equipment_id,
        failure_equipment,
        fault_time,
        fault_phenomenon,
        submission_name,
        submission_pid,
        biz_code,
        submission_time,
        submission_branch,
        submission_branch_id
        FROM cb_failure_details
        <where>
            <if test="currentStatus == null ">
                and current_status in (0,3)
            </if>
            <if test="currentStatus != null ">
                current_status = #{currentStatus}
            </if>
            <if test="startTime!= null and endTime != null">
                and submission_time between #{startTime} and #{endTime}
            </if>
            <if test="submissionPid!= null ">
                and submission_pid = #{submissionPid}
            </if>
        </where>
        order by submission_time DESC
        limit #{current},#{size}
    </select>

    <select id="selectStatusWaitTj" resultType="com.yeejoin.amos.boot.module.common.api.entity.FailureDetails">
        SELECT sequence_nbr,
        current_status,
        failure_equipment_id,
        failure_equipment,
        fault_time,
        fault_phenomenon,
        submission_name,
        submission_pid,
        biz_code,
        submission_time,
        submission_branch,
        submission_branch_id
        FROM cb_failure_details
        <where>
            <if test="currentStatus != null ">
                and current_status = #{currentStatus}
            </if>
            <if test="startTime!= null and endTime != null">
                and submission_time between #{startTime} and #{endTime}
            </if>
            <if test="submissionPid!= null ">
                and submission_pid = #{submissionPid}
            </if>
        </where>
        order by submission_time DESC
        limit #{current},#{size}
    </select>

    <select id="selectStatusWaitWx" resultType="com.yeejoin.amos.boot.module.common.api.entity.FailureDetails">
        SELECT sequence_nbr,
        current_status,
        failure_equipment_id,
        failure_equipment,
        fault_time,
        fault_phenomenon,
        submission_name,
        submission_pid,
        biz_code,
        submission_time,
        submission_branch,
        submission_branch_id
        FROM cb_failure_details
        <where>
            <if test="currentStatus == null ">
                current_status IN (2,6)
            </if>
            <if test="currentStatus != null ">
                current_status = #{currentStatus}
            </if>
            <if test="startTime!= null and endTime != null">
                and submission_time between #{startTime} and #{endTime}
            </if>
            <if test="submissionPid!= null ">
                and submission_pid = #{submissionPid}
            </if>
        </where>
        order by submission_time DESC
        limit #{current},#{size}
    </select>

    <select id="selectStatusCount" resultType="com.yeejoin.amos.boot.module.common.api.dto.StatusDto">
        SELECT cb_failure_details.current_status,
        count(cb_failure_details.current_status)
        AS currentStatusCount
        FROM cb_failure_details
        <where>
            <if test="currentStatus!= null ">
                and current_status = #{currentStatus}
            </if>
            <if test="startTime!= null and endTime != null">
                and submission_time between #{startTime} and #{endTime}
            </if>
            <if test="submissionPid!= null ">
                and submission_pid = #{submissionPid}
            </if>
        </where>
        GROUP BY cb_failure_details.current_status
    </select>

    <select id="selectStatusWx" resultType="com.yeejoin.amos.boot.module.common.api.dto.StatusDto">
        SELECT cb_failure_details.current_status,
        count(cb_failure_details.current_status)
        AS currentStatusCount
        FROM cb_failure_details
        <where>
            <if test="currentStatus == null ">
                and current_status in (2,6)
            </if>
            <if test="currentStatus != null ">
                and current_status #{currentStatus}
            </if>
            <if test="startTime!= null and endTime != null">
                and submission_time between #{startTime} and #{endTime}
            </if>
            <if test="submissionPid!= null ">
                and submission_pid = #{submissionPid}
            </if>
        </where>
        GROUP BY cb_failure_details.current_status
    </select>

    <select id="selectStatusFq" resultType="com.yeejoin.amos.boot.module.common.api.dto.StatusDto">
        SELECT cb_failure_details.current_status,
        count(cb_failure_details.current_status)
        AS currentStatusCount
        FROM cb_failure_details
        <where>
            <if test="currentStatus == null ">
                and current_status = 1
            </if>
            <if test="startTime!= null and endTime != null">
                and submission_time between #{startTime} and #{endTime}
            </if>
            <if test="submissionPid!= null ">
                and submission_pid = #{submissionPid}
            </if>
        </where>

        GROUP BY cb_failure_details.current_status
    </select>

    <select id="selectStatusLeader" resultType="com.yeejoin.amos.boot.module.common.api.dto.StatusDto">
        SELECT cb_failure_details.current_status,
        count(cb_failure_details.current_status)
        AS currentStatusCount
        FROM cb_failure_details
        <where>
            <if test="currentStatus == null ">
                and current_status in (0,3)
            </if>
            <if test="startTime!= null and endTime != null">
                and submission_time between #{startTime} and #{endTime}
            </if>
            <if test="submissionPid!= null ">
                and submission_pid = #{submissionPid}
            </if>
        </where>
        GROUP BY cb_failure_details.current_status
    </select>

    <select id="selectStatusFqp" parameterType="integer"
            resultType="com.yeejoin.amos.boot.module.common.api.dto.StatusDto">
        SELECT cb_failure_details.current_status,
        count(cb_failure_details.current_status)
        AS currentStatusCount
        FROM cb_failure_details
        <where>
            <if test="currentStatus!= null ">
                and current_status = #{currentStatus}
            </if>
            <if test="startTime!= null and endTime != null">
                and submission_time between #{startTime} and #{endTime}
            </if>
            <if test="submissionPid!= null ">
                and submission_pid = #{submissionPid}
            </if>
        </where>
        GROUP BY cb_failure_details.current_status
    </select>

    <select id="selectAudit" resultType="com.yeejoin.amos.boot.module.common.api.dto.StatusDto">
        SELECT
            current_status,
            count(current_status)
                AS currentStatusCount
        FROM
            cb_failure_details d

        <where>
            d.sequence_nbr  in
            (SELECT
            fault_id
            FROM
            cb_failure_audit
            where
            rec_user_id = #{userId}
            )
            <if test="currentStatus!= null ">
                and current_status = #{currentStatus}
            </if>
            <if test="startTime!= null and endTime != null">
                and submission_time between #{startTime} and #{endTime}
            </if>
            <if test="submissionPid!= null ">
                and submission_pid = #{submissionPid}
            </if>
        </where>
        GROUP BY current_status;
    </select>

    <select id="selectMaintain" resultType="com.yeejoin.amos.boot.module.common.api.dto.StatusDto">
        SELECT
            current_status,
            count(current_status)
                AS currentStatusCount
        FROM
            cb_failure_details d
        <where>
            d.sequence_nbr  in
            (SELECT
            fault_id
            FROM
            cb_failure_maintain
            where
            rec_user_id = #{userId}
            )
            <if test="currentStatus!= null ">
                and current_status = #{currentStatus}
            </if>
            <if test="startTime!= null and endTime != null">
                and submission_time between #{startTime} and #{endTime}
            </if>
            <if test="submissionPid!= null ">
                and submission_pid = #{submissionPid}
            </if>
        </where>
        GROUP BY current_status;
    </select>

    <select id="selectUserId" resultType="com.yeejoin.amos.boot.module.common.api.dto.StatusDto">
        SELECT
            current_status,
            count(current_status)
                AS currentStatusCount
        FROM
            cb_failure_details d
              <where>
                  d.sequence_nbr  in
                  (SELECT
                  fault_id
                  FROM
                  cb_failure_audit
                  where
                  audit_result = 2
                  )
                  <if test="currentStatus!= null ">
                      and current_status = #{currentStatus}
                  </if>
                  <if test="startTime!= null and endTime != null">
                      and submission_time between #{startTime} and #{endTime}
                  </if>
                  <if test="userId != null ">
                      and submission_pid = #{userId}
                  </if>
              </where>
        GROUP BY current_status;
    </select>

    <select id="selectYJ" resultType="com.yeejoin.amos.boot.module.common.api.entity.FailureDetails">
        SELECT
            *
        FROM
            cb_failure_details d
        <where>
            d.sequence_nbr  in
            (SELECT
            fault_id
            FROM
            cb_failure_audit
            where
            audit_result = 2
            )
            <if test="currentStatus != null ">
                and current_status = #{currentStatus}
            </if>
            <if test="startTime!= null and endTime != null">
                and submission_time between #{startTime} and #{endTime}
            </if>
            <if test="userId != null ">
                and submission_pid = #{userId}
            </if>
        </where>
        order by submission_time DESC
                limit #{current},#{size};
    </select>

    <select id="selectForAudit" resultType="com.yeejoin.amos.boot.module.common.api.entity.FailureDetails">
        SELECT
            *
        FROM
            cb_failure_details d
        <where>
            d.sequence_nbr  in
            (SELECT
            fault_id
            FROM
            cb_failure_audit
            where
            rec_user_id = #{userId}
            )
            <if test="currentStatus != null ">
                and current_status = #{currentStatus}
            </if>
            <if test="startTime!= null and endTime != null">
                and submission_time between #{startTime} and #{endTime}
            </if>
            <if test="submissionPid!= null ">
                and submission_pid = #{submissionPid}
            </if>
        </where>
        order by submission_time DESC
            limit #{current},#{size};
    </select>

    <select id="selectForMaintain" resultType="com.yeejoin.amos.boot.module.common.api.entity.FailureDetails">
        SELECT
        *
        FROM
        cb_failure_details d
        <where>
            d.sequence_nbr  in
            (SELECT
            fault_id
            FROM
            cb_failure_maintain
            where
            rec_user_id = #{userId}
            )
            <if test="currentStatus != null ">
                and current_status = #{currentStatus}
            </if>
            <if test="startTime!= null and endTime != null">
                and submission_time between #{startTime} and #{endTime}
            </if>
            <if test="submissionPid!= null ">
                and submission_pid = #{submissionPid}
            </if>
        </where>
        order by submission_time DESC
        limit #{current},#{size};
    </select>


</mapper>
