<?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.mapper.business.BusinessLabExecutiveInfoMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.casic.missiles.model.business.BusinessLabExecutiveInfo">
        <id column="id" property="id"/>
        <result column="order_id" property="orderId"/>
        <result column="sample_id" property="sampleId"/>
        <result column="measure_dept_id" property="measureDeptId"/>
        <result column="measure_person_id" property="measurePersonId"/>
        <result column="measure_item" property="measureItem"/>
        <result column="measure_sequence" property="measureSequence"/>
        <result column="execute_sequence" property="executeSequence"/>
        <result column="measure_status" property="measureStatus"/>
        <result column="is_auto" property="isAuto"/>
        <result column="require_certifications" property="requireCertifications"/>
        <result column="operate_time" property="operateTime"/>
        <result column="start_time" property="startTime"/>
        <result column="end_time" property="endTime"/>
        <result column="remark" property="remark"/>
        <result column="create_user" property="createUser"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, order_id, sample_id, measure_dept_id, measure_person_id, measure_item, measure_sequence, execute_sequence, measure_status, is_auto, require_certifications, operate_time, start_time, end_time, remark, is_del, create_user, create_time, update_time
    </sql>

    <!-- 各状态列表查询结果列 -->
    <sql id="Status_Column_List">
        blei.id AS id, csi.id AS sample_id, csi.sample_no, csi.sample_name, csi.sample_model, csi.manufacturing_no, bo.order_code AS orderNo, bo.id AS orderId,
        bo.customer_id, bo.customer_no, bo.customer_name, bo.deliverer, bo.is_urgent, csi.sample_belong, bo.require_over_time, blei.require_certifications,
        blei.measure_status, blei.measure_person_id
    </sql>

    <select id="selectListByInMeasureStatus" resultType="com.casic.missiles.dto.business.labExecutive.HandOutLabExecutiveDTO">
        SELECT  blei.order_id, blei.sample_id, blei.measure_dept_id, sd.SIMPLE_NAME AS deptName, blei.measure_status,
        blei.require_certifications
        FROM business_lab_executive_info blei
        JOIN sys_dept sd ON blei.measure_dept_id = sd.ID
        WHERE blei.order_id = #{orderId}
        AND blei.sample_id = #{sampleId}
        AND (blei.measure_status = 1 OR blei.measure_status = 2 OR blei.measure_status = 3)<!--待分配or待检测or检测中均属于分发中的检测中-->
    </select>

    <select id="getMeasureNumberStatus" resultType="Integer">
        SELECT require_certifications
        FROM (SELECT  *
             FROM  business_lab_executive_info  blei
             WHERE blei.order_id = #{orderId}
             AND blei.sample_id = #{sampleId}
        ) blei
        JOIN sys_dept sd ON blei.measure_dept_id = sd.ID
        limit 1
    </select>

    <select id="selectExecutiveList" resultType="com.casic.missiles.dto.business.labExecutive.MeasureProcessDTO">
        SELECT blei.id, blei.measure_dept_id AS measureSegmentId, sd.SIMPLE_NAME AS measureSegment, blei.measure_person_id, ms.name AS measurePerson,
            blei.measure_status, blei.require_certifications, blei.update_time, blei.measure_sequence
        FROM business_lab_executive_info blei
        JOIN sys_dept sd ON blei.measure_dept_id = sd.ID
        LEFT JOIN meter_staff ms ON blei.measure_person_id = ms.id
        WHERE blei.order_id = #{orderId}
        AND blei.sample_id = #{sampleId}
        ORDER BY blei.measure_sequence ASC
    </select>

    <select id="selectListByDeptIdAndStatus" resultType="com.casic.missiles.dto.business.labExecutive.SampleLabExecutiveListResponse">
        SELECT <include refid="Status_Column_List"/>
        FROM business_lab_executive_info blei
        JOIN customer_sample_info csi ON csi.id = blei.sample_id
        JOIN business_order bo ON bo.id = blei.order_id
        WHERE blei.measure_dept_id = #{deptId}
        <if test="status != null and status != ''">
            AND blei.measure_status = #{status}
        </if>
        <if test="statusList != null">
            AND blei.measure_status IN
            <foreach collection="statusList" item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="request.sampleNo != null and request.sampleNo !=''">
            AND csi.sample_no LIKE concat('%',#{request.sampleNo},'%')
        </if>
        <if test="request.sampleName != null and request.sampleName !=''">
            AND csi.sample_name LIKE concat('%',#{request.sampleName},'%')
        </if>
        <if test="request.orderNo != null and request.orderNo !=''">
            AND bo.order_code LIKE concat('%',#{request.orderNo},'%')
        </if>
        <if test="request.customerNo != null and request.customerNo !=''">
            AND bo.customer_no LIKE concat('%',#{request.customerNo},'%')
        </if>
        <if test="request.customerName != null and request.customerName !=''">
            AND bo.customer_name LIKE concat('%',#{request.customerName},'%')
        </if>
        <if test="request.startTime != null and request.startTime !=''">
            AND bo.require_over_time &gt;= #{request.startTime}
        </if>
        <if test="request.endTime != null and request.endTime !=''">
            AND bo.require_over_time &lt;= #{request.endTime}
        </if>
        <if test="request.isUrgent != null and request.isUrgent !=''">
            AND bo.is_urgent = #{request.isUrgent}
        </if>
        <if test="request.sampleBelong != null and request.sampleBelong !=''">
            AND csi.sample_belong = #{request.sampleBelong}
        </if>
        ORDER BY blei.update_time DESC
    </select>

    <select id="selectListByUserIdAndStatus" resultType="com.casic.missiles.dto.business.labExecutive.SampleLabExecutiveListResponse">
        SELECT <include refid="Status_Column_List"/>, sd.ID AS measureDeptId, sd.SIMPLE_NAME AS measureDeptName
        FROM business_lab_executive_info blei
        JOIN customer_sample_info csi ON csi.id = blei.sample_id
        JOIN business_order bo ON bo.id = blei.order_id
        LEFT JOIN sys_dept sd ON blei.measure_dept_id = sd.ID
        WHERE blei.measure_person_id = #{userId}
        AND blei.measure_status = #{status}
        <if test="request.sampleNo != null and request.sampleNo !=''">
            AND csi.sample_no LIKE concat('%',#{request.sampleNo},'%')
        </if>
        <if test="request.sampleName != null and request.sampleName !=''">
            AND csi.sample_name LIKE concat('%',#{request.sampleName},'%')
        </if>
        <if test="request.orderNo != null and request.orderNo !=''">
            AND bo.order_code LIKE concat('%',#{request.orderNo},'%')
        </if>
        <if test="request.customerNo != null and request.customerNo !=''">
            AND bo.customer_no LIKE concat('%',#{request.customerNo},'%')
        </if>
        <if test="request.customerName != null and request.customerName !=''">
            AND bo.customer_name LIKE concat('%',#{request.customerName},'%')
        </if>
        <if test="request.startTime != null and request.startTime !=''">
            AND bo.require_over_time &gt;= #{request.startTime}
        </if>
        <if test="request.endTime != null and request.endTime !=''">
            AND bo.require_over_time &lt;= #{request.endTime}
        </if>
        <if test="request.isUrgent != null and request.isUrgent !=''">
            AND bo.is_urgent = #{request.isUrgent}
        </if>
        <if test="request.sampleBelong != null and request.sampleBelong !=''">
            AND csi.sample_belong = #{request.sampleBelong}
        </if>
        ORDER BY blei.update_time DESC
    </select>

    <update id="updateForSampleInMeasureOperate">
        UPDATE business_lab_executive_info
        SET measure_status = #{status}, start_time = NOW()
        WHERE measure_person_id = #{userId}
        AND (order_id, sample_id) IN
        <foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
            (#{item.orderId}, #{item.sampleId})
        </foreach>
    </update>

    <update id="updateForSampleMeasureCompleteOperate">
        UPDATE business_lab_executive_info
        SET measure_status = #{status}, end_time = NOW()
        WHERE measure_person_id = #{userId}
        AND (order_id, sample_id) IN
        <foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
            (#{item.orderId}, #{item.sampleId})
        </foreach>
    </update>

    <select id="selectListForSampleOperate" resultType="com.casic.missiles.dto.business.labExecutive.SampleOperateRequest">
        SELECT order_id, sample_id
        FROM business_lab_executive_info
        WHERE measure_person_id = #{userId}
        AND measure_status = #{status}
        AND (order_id, sample_id) IN
        <foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
            (#{item.orderId}, #{item.sampleId})
        </foreach>
    </select>

    <update id="updateForSendBack">
        UPDATE business_lab_executive_info
        SET measure_sequence = measure_sequence - 1
        WHERE (measure_status IS NULL OR measure_status = "")
        AND order_id = #{orderId}
        AND sample_id = #{sampleId}
    </update>

    <select id="selectMinSeq" resultType="java.lang.Long">
        SELECT id FROM business_lab_executive_info
        WHERE measure_sequence = (
            SELECT MIN(measure_sequence)
            FROM business_lab_executive_info
            WHERE (measure_status IS NULL OR measure_status = "")
            AND order_id = #{orderId}
            AND sample_id = #{sampleId}
        )
        AND (measure_status IS NULL OR measure_status = "")
		AND order_id = #{orderId}
        AND sample_id = #{sampleId}
    </select>

    <select id="selectListByDeptId" resultType="com.casic.missiles.dto.business.workbench.SampleMeasureResponse">
        SELECT csi.id AS sample_id, csi.sample_name, bo.customer_id, bo.customer_name, blei.measure_status
        FROM business_lab_executive_info blei
        JOIN customer_sample_info csi ON csi.id = blei.sample_id
        JOIN business_order bo ON bo.id = blei.order_id
        WHERE blei.measure_dept_id = #{deptId} AND blei.measure_status IS NOT NULL
        AND DATE_FORMAT(blei.create_time, '%Y%m') = DATE_FORMAT(CURDATE() ,'%Y%m')
        ORDER BY blei.update_time DESC
    </select>

    <select id="selectListByUserId" resultType="com.casic.missiles.dto.business.workbench.SampleMeasureResponse">
        SELECT csi.id AS sample_id, csi.sample_name, bo.customer_id, bo.customer_name, blei.measure_status
        FROM business_lab_executive_info blei
        JOIN customer_sample_info csi ON csi.id = blei.sample_id
        JOIN business_order bo ON bo.id = blei.order_id
        WHERE blei.measure_person_id = #{userId} AND blei.measure_status IS NOT NULL
        AND DATE_FORMAT(blei.create_time, '%Y%m') = DATE_FORMAT(CURDATE() ,'%Y%m')
        ORDER BY blei.update_time DESC
    </select>

    <select id="selectAllList" resultType="com.casic.missiles.dto.business.workbench.SampleMeasureResponse">
        SELECT csi.id AS sample_id, csi.sample_name, bo.customer_id, bo.customer_name, blei.measure_status
        FROM business_lab_executive_info blei
        JOIN customer_sample_info csi ON csi.id = blei.sample_id
        JOIN business_order bo ON bo.id = blei.order_id
        WHERE blei.measure_status IS NOT NULL
        AND DATE_FORMAT(blei.create_time, '%Y%m') = DATE_FORMAT(CURDATE() ,'%Y%m')
        ORDER BY blei.update_time DESC
    </select>

    <select id="selectListGroupByStatus" resultType="com.casic.missiles.dto.business.workbench.SampleMeasureChartResponse">
        SELECT blei.measure_status, COUNT(blei.id) AS measureQuantity
        FROM business_lab_executive_info blei
        JOIN customer_sample_info csi ON csi.id = blei.sample_id
        JOIN business_order bo ON bo.id = blei.order_id
        WHERE blei.measure_status IS NOT NULL
        AND blei.measure_status IN (2,3,4)
        AND DATE_FORMAT(blei.create_time, '%Y%m') = DATE_FORMAT(CURDATE() ,'%Y%m')
        GROUP BY blei.measure_status
    </select>
</mapper>
