<?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.aucma.dms.mapper.DmsBillsInspectInstanceMapper">

    <resultMap type="DmsBillsInspectInstance" id="DmsBillsInspectInstanceResult">
        <result property="inspectInstanceId"    column="inspect_instance_id"    />
        <result property="planInspectId"    column="plan_inspect_id"    />
        <!-- 已废弃审批流功能 -->
        <!-- <result property="wfProcessId"    column="wf_process_id"    /> -->
        <result property="inspectType"    column="inspect_type"    />
        <result property="billsInspectCode"    column="bills_inspect_code"    />
        <result property="planBeginTime"    column="plan_begin_time"    />
        <result property="planEndTime"    column="plan_end_time"    />
        <result property="realBeginTime"    column="real_begin_time"    />
        <result property="realEndTime"    column="real_end_time"    />
        <result property="inspectStatus"    column="inspect_status"    />
        <result property="isFlag"    column="is_flag"    />
        <result property="remark"    column="remark"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="inspectRouteId"    column="inspect_route_id"    />
        <result property="routeName"    column="route_name"    />
        <result property="planInspectCode"    column="plan_inspect_code"    />
    </resultMap>

    <sql id="selectDmsBillsInspectInstanceVo">
        select
            a.inspect_instance_id,
            a.plan_inspect_id,
            -- a.wf_process_id, -- 已废弃审批流
            a.inspect_type,
            a.bills_inspect_code,
            a.plan_begin_time,
            a.plan_end_time,
            a.real_begin_time,
            a.real_end_time,
            a.inspect_status,
            a.is_flag,
            b.remark,
            b.create_by,
            a.create_time,
            a.update_by,
            a.update_time,
            b.plan_inspect_code,
            b.performer
        from
            dms_bills_inspect_instance a left join dms_plan_inspect b on a.plan_inspect_id = b.plan_inspect_id
    </sql>

    <select id="selectDmsBillsInspectInstanceList" parameterType="DmsBillsInspectInstance" resultMap="DmsBillsInspectInstanceResult">
        <include refid="selectDmsBillsInspectInstanceVo"/>
        <where>
            <if test="planInspectId != null "> and a.plan_inspect_id = #{planInspectId}</if>
            <if test="inspectInstanceId != null "> and a.inspect_instance_id = #{inspectInstanceId}</if>
            <!-- 已废弃审批流 -->
            <!-- <if test="wfProcessId != null "> and a.wf_process_id = #{wfProcessId}</if> -->
            <if test="inspectType != null  and inspectType != ''"> and a.inspect_type = #{inspectType}</if>
            <if test="billsInspectCode != null  and billsInspectCode != ''"> and a.bills_inspect_code = #{billsInspectCode}</if>
            <if test="planBeginTime != null "> and a.plan_begin_time > #{planBeginTime}</if>
            <if test="planEndTime != null "> and  #{planEndTime}>a.plan_end_time </if>
            <if test="realBeginTime != null "> and a.real_begin_time > #{realBeginTime}</if>
            <if test="realEndTime != null "> and   #{realEndTime}>a.real_end_time</if>
            <if test="inspectStatus != null  and inspectStatus != ''"> and a.inspect_status = #{inspectStatus}</if>
            <if test="isFlag != null  and isFlag != ''"> and a.is_flag = #{isFlag}</if>
        </where>
        order by a.create_time desc
    </select>
    <select id="selectPoint" parameterType="DmsBillsInspectInstance" resultMap="DmsBillsInspectInstanceResult">
        <include refid="selectDmsBillsInspectInstanceVo"/>
        where a.inspect_type = 2
    </select>
    <select id="selectInspection" parameterType="DmsBillsInspectInstance" resultMap="DmsBillsInspectInstanceResult">
        <include refid="selectDmsBillsInspectInstanceVo"/>
        where a.inspect_type = 1
    </select>

    <select id="selectDmsBillsInspectInstanceByInspectInstanceId" parameterType="Long" resultMap="DmsBillsInspectInstanceResult">
        <include refid="selectDmsBillsInspectInstanceVo"/>
        where inspect_instance_id = #{inspectInstanceId}
    </select>

    <insert id="insertDmsBillsInspectInstance" parameterType="DmsBillsInspectInstance">
        <selectKey keyProperty="inspectInstanceId" resultType="long" order="BEFORE">
            SELECT HAIWEI.SEQ_DMS_BILLS_INSPECT_INSTANCE.NEXTVAL FROM DUAL
        </selectKey>
        insert into dms_bills_inspect_instance
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="inspectInstanceId != null">inspect_instance_id,</if>
            <if test="planInspectId != null">plan_inspect_id,</if>
            <!-- 已废弃审批流 -->
            <!-- <if test="wfProcessId != null">wf_process_id,</if> -->
            <if test="inspectType != null and inspectType != ''">inspect_type,</if>
            <if test="inspectRouteId != null and inspectRouteId != ''">inspect_route_id,</if>
            <if test="billsInspectCode != null">bills_inspect_code,</if>
            <if test="planBeginTime != null">plan_begin_time,</if>
            <if test="planEndTime != null">plan_end_time,</if>
            <if test="realBeginTime != null">real_begin_time,</if>
            <if test="realEndTime != null">real_end_time,</if>
            <if test="inspectStatus != null">inspect_status,</if>
            <if test="isFlag != null and isFlag != ''">is_flag,</if>
            <if test="remark != null">remark,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="inspectInstanceId != null">#{inspectInstanceId},</if>
            <if test="planInspectId != null">#{planInspectId},</if>
            <!-- 已废弃审批流 -->
            <!-- <if test="wfProcessId != null">#{wfProcessId},</if> -->
            <if test="inspectType != null and inspectType != ''">#{inspectType},</if>
            <if test="inspectRouteId != null and inspectRouteId != ''">#{inspectRouteId},</if>
            <if test="billsInspectCode != null">#{billsInspectCode},</if>
            <if test="planBeginTime != null">#{planBeginTime},</if>
            <if test="planEndTime != null">#{planEndTime},</if>
            <if test="realBeginTime != null">#{realBeginTime},</if>
            <if test="realEndTime != null">#{realEndTime},</if>
            <if test="inspectStatus != null">#{inspectStatus},</if>
            <if test="isFlag != null and isFlag != ''">#{isFlag},</if>
            <if test="remark != null">#{remark},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
         </trim>
    </insert>
    <insert id="insertRecord" parameterType="com.aucma.dms.domain.DmsRecordInspect">
        insert into dms_record_inspect
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="planInspectId != null">plan_inspect_id,</if>
            <if test="billsInspectCode != null">bills_inspect_code,</if>
            <if test="planBeginTime != null">plan_begin_time,</if>
            <if test="planEndTime != null">plan_end_time,</if>
            <if test="realBeginTime != null">real_begin_time,</if>
            <if test="realEndTime != null">real_end_time,</if>
            <if test="inspectStatus != null">inspect_status,</if>
            <if test="inspectType != null and inspectType != ''">inspect_type,</if>
            <if test="inspectRouteId != null and inspectRouteId != ''">inspect_route_id,</if>
            <if test="deviceAmount != null and deviceAmount != ''">device_amount,</if>
            <if test="performer != null and performer != ''">performer,</if>
            <if test="remark != null">remark,</if>
            <if test="inspectDuration != null">inspect_duration,</if>
            <if test="isFlag != null and isFlag != ''">is_flag,</if>
            <if test="inspectInstanceId != null and inspectInstanceId != ''">inspect_instance_id,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="planInspectId != null">#{planInspectId},</if>
            <if test="billsInspectCode != null">#{billsInspectCode},</if>
            <if test="planBeginTime != null">#{planBeginTime},</if>
            <if test="planEndTime != null">#{planEndTime},</if>
            <if test="realBeginTime != null">#{realBeginTime},</if>
            <if test="realEndTime != null">#{realEndTime},</if>
            <if test="inspectStatus != null">#{inspectStatus},</if>
            <if test="inspectType != null and inspectType != ''">#{inspectType},</if>
            <if test="inspectRouteId != null and inspectRouteId != ''">#{inspectRouteId},</if>
            <if test="deviceAmount != null and deviceAmount != ''">#{deviceAmount},</if>
            <if test="performer != null and performer != ''">#{performer},</if>
            <if test="remark != null">#{remark},</if>
            <if test="inspectDuration != null">#{inspectDuration},</if>
            <if test="isFlag != null and isFlag != ''">#{isFlag},</if>
            <if test="inspectInstanceId != null and inspectInstanceId != ''">#{inspectInstanceId},</if>
        </trim>
    </insert>

    <update id="updateDmsBillsInspectInstance" parameterType="DmsBillsInspectInstance">
        update dms_bills_inspect_instance
        <trim prefix="SET" suffixOverrides=",">
            <if test="planInspectId != null">plan_inspect_id = #{planInspectId},</if>
            <!-- 已废弃审批流 -->
            <!-- <if test="wfProcessId != null">wf_process_id = #{wfProcessId},</if> -->
            <if test="inspectType != null and inspectType != ''">inspect_type = #{inspectType},</if>
            <if test="billsInspectCode != null">bills_inspect_code = #{billsInspectCode},</if>
            <if test="planBeginTime != null">plan_begin_time = #{planBeginTime},</if>
            <if test="planEndTime != null">plan_end_time = #{planEndTime},</if>
            <if test="realBeginTime != null">real_begin_time = #{realBeginTime},</if>
            <if test="realEndTime != null">real_end_time = #{realEndTime},</if>
            <if test="inspectStatus != null">inspect_status = #{inspectStatus},</if>
            <if test="isFlag != null and isFlag != ''">is_flag = #{isFlag},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
        </trim>
        where inspect_instance_id = #{inspectInstanceId}
    </update>

    <delete id="deleteDmsBillsInspectInstanceByInspectInstanceId" parameterType="Long">
        delete from dms_bills_inspect_instance where inspect_instance_id = #{inspectInstanceId}
    </delete>

    
    <delete id="deleteDmsBillsInspectInstanceByInspectInstanceIds" parameterType="String">
        delete from dms_bills_inspect_instance where inspect_instance_id in
        <foreach item="inspectInstanceId" collection="array" open="(" separator="," close=")">
            #{inspectInstanceId}
        </foreach>
    </delete>


    <select id="selectNewestDmsBillsInspectInstance" parameterType="DmsBillsInspectInstance" resultMap="DmsBillsInspectInstanceResult">
        SELECT * FROM (
            select
            dbii.inspect_instance_id,
            dbii.plan_inspect_id,
            dbii.inspect_type,
            dbii.bills_inspect_code,
            dbii.plan_begin_time,
            dbii.plan_end_time,
            dbii.real_begin_time,
            dbii.real_end_time,
            dbii.inspect_status,
            dbii.create_time,
            dbir.route_name,a.plan_inspect_code,
            a.remark inspectDesc
            from dms_bills_inspect_instance dbii
            left join dms_base_inspect_route dbir on
            dbii.inspect_route_id = dbir.inspect_route_id left join dms_plan_inspect a on dbii.plan_inspect_id = a.plan_inspect_id

            <where>
                <if test="inspectRouteId != null "> and dbii.inspect_route_id = #{inspectRouteId}</if>
                <!-- 已废弃审批流 -->
                <!-- <if test="wfProcessId != null "> and dbii.wf_process_id = #{wfProcessId}</if> -->
                <if test="inspectType != null  and inspectType != ''"> and dbii.inspect_type = #{inspectType}</if>
                <if test="billsInspectCode != null  and billsInspectCode != ''"> and dbii.bills_inspect_code = #{billsInspectCode}</if>
                <if test="inspectStatus != null  and inspectStatus != ''"> and dbii.inspect_status = #{inspectStatus}</if>
                <if test="inspectStatusStr != null  and inspectStatusStr != ''"> and instr(',' || #{inspectStatusStr} || ',', ',' || dbii.inspect_status || ',') &gt; 0</if>
                <if test="isFlag != null  and isFlag != ''"> and dbii.is_flag = #{isFlag}</if>
            </where>
            order by dbii.create_time desc
        ) where rownum = 1
    </select>
    <select id="selectRecord" resultType="com.aucma.dms.domain.DmsRecordInspect" parameterType="java.lang.Long">
        select
            x.inspect_instance_id,
            c.plan_inspect_code bills_inspect_code,
            c.inspect_type ,
            c.device_amount ,
            a.plan_begin_time ,
            a.plan_end_time ,
            a.real_begin_time ,
            a.real_end_time ,
            b.inspect_route_id inspect_route_id ,
            c.performer ,
            x.instance_detail_status inspect_status,
            c.remark
        from
            dms_inspect_instance_detail x
                left join dms_bills_inspect_instance a on
                x.inspect_instance_id = a.inspect_instance_id
                left join dms_base_inspect_route b on
                a.inspect_route_id = b.inspect_route_id
                left join dms_plan_inspect c on
                a.plan_inspect_id = c.plan_inspect_id
        where
            x.instance_detail_id = #{instanceDetailId}
    </select>
    <select id="selectRecordByInstainceId" resultType="com.aucma.dms.domain.DmsRecordInspect"
            parameterType="java.lang.Long">
        select * from dms_record_inspect where inspect_instance_id = #{inspectInstanceId} and inspect_status != 3
    </select>
    <select id="selectUnCompletedInstance" resultType="java.lang.Integer">
        select
            count(1)
        from
            dms_inspect_instance_detail x
        where
            x.inspect_instance_id = #{inspectInstanceId}
          and instance_detail_status != 3
    </select>
    <select id="selectAllInstance" resultType="java.lang.Integer" parameterType="java.lang.Long">
        select
            count(1)
        from
            dms_inspect_instance_detail x
        where
            x.inspect_instance_id = #{inspectInstanceId}
    </select>

    <!-- 检查指定计划今天是否已生成工单 -->
    <select id="countByPlanIdAndToday" resultType="java.lang.Integer">
        SELECT COUNT(1)
        FROM dms_bills_inspect_instance
        WHERE plan_inspect_id = #{planInspectId}
          -- 使用 [当天00:00, 次日00:00) 范围，避免 TRUNC(create_time) 带来的索引失效风险
          AND create_time &gt;= TRUNC(SYSDATE)
          AND create_time &lt; TRUNC(SYSDATE) + 1
    </select>

    <!-- 统一从 DB 读取当前时间，避免应用与数据库时钟差异 -->
    <select id="selectDbNow" resultType="java.util.Date">
        SELECT SYSDATE FROM DUAL
    </select>

</mapper>
