<?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.youlai.boot.system.mapper.MineDemandMapper">

    <select id="getMineDemandPage" resultType="com.youlai.boot.system.model.vo.MineDemandVO">
        SELECT
        id,
        area,
        mine_name,
        demand_name,
        submitter,
        operation_staff,
        ventilation_tech_staff,
        demand_description,
        submit_time,
        planned_complete_time,
        actual_start_time,
        actual_complete_time,
        frontend_staff,
        frontend_version,
        backend_staff,
        backend_version,
        frontend_progress,
        backend_progress,
        operation_progress,
        (
        (area IS NULL OR area = '') +
        (mine_name IS NULL OR mine_name = '') +
        (demand_name IS NULL OR demand_name = '') +
        (submitter IS NULL OR submitter = '') +
        (operation_staff IS NULL OR operation_staff = '') +
        (ventilation_tech_staff IS NULL OR ventilation_tech_staff = '') +
        (demand_description IS NULL OR demand_description = '') +
        (submit_time IS NULL) +
        (planned_complete_time IS NULL) +
        (actual_start_time IS NULL) +
        (actual_complete_time IS NULL) +
        (frontend_staff IS NULL OR frontend_staff = '') +
        (frontend_version IS NULL OR frontend_version = '') +
        (backend_staff IS NULL OR backend_staff = '')
        ) AS empty_field_count
        FROM
        mine_demand
        <where>
            <if test="queryParams.subIds != null and queryParams.subIds.size() > 0">
                AND folder_id IN
                <foreach collection="queryParams.subIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>

            <if test="queryParams.area != null and queryParams.area != ''">
                AND area LIKE CONCAT('%', #{queryParams.area}, '%')
            </if>

            <if test="queryParams.mineName != null and queryParams.mineName != ''">
                AND mine_name LIKE CONCAT('%', #{queryParams.mineName}, '%')
            </if>

            <if test="queryParams.demandName != null and queryParams.demandName != ''">
                AND demand_name LIKE CONCAT('%', #{queryParams.demandName}, '%')
            </if>
            <if test="queryParams.submitter != null and queryParams.submitter != ''">
                AND submitter LIKE CONCAT('%', #{queryParams.submitter}, '%')
            </if>
            <if test="queryParams.ventilationTechStaff != null and queryParams.ventilationTechStaff != ''">
                AND ventilation_tech_staff LIKE CONCAT('%', #{queryParams.ventilationTechStaff}, '%')
            </if>
            <if test="queryParams.demandDescription != null and queryParams.demandDescription != ''">
                AND demand_description LIKE CONCAT('%', #{queryParams.demandDescription}, '%')
            </if>
            <if test="queryParams.operationStaff != null and queryParams.operationStaff != ''">
                AND operation_staff LIKE CONCAT('%', #{queryParams.operationStaff}, '%')
            </if>
            <if test="queryParams.frontendStaff != null and queryParams.frontendStaff != ''">
                AND frontend_staff LIKE CONCAT('%', #{queryParams.frontendStaff}, '%')
            </if>
            <if test="queryParams.frontendVersion != null and queryParams.frontendVersion != ''">
                AND frontend_version LIKE CONCAT('%', #{queryParams.frontendVersion}, '%')
            </if>
            <if test="queryParams.backendStaff != null and queryParams.backendStaff != ''">
                AND backend_staff LIKE CONCAT('%', #{queryParams.backendStaff}, '%')
            </if>
            <if test="queryParams.backendVersion != null and queryParams.backendVersion != ''">
                AND backend_version LIKE CONCAT('%', #{queryParams.backendVersion}, '%')
            </if>
            <if test="queryParams.submitTime != null and queryParams.submitTime.size() >= 2">
                AND submit_time >= #{queryParams.submitTime[0]}
                AND submit_time &lt;= #{queryParams.submitTime[1]}
            </if>
            <if test="queryParams.plannedCompleteTime != null and queryParams.plannedCompleteTime.size() >= 2">
                AND planned_complete_time >= #{queryParams.plannedCompleteTime[0]}
                AND planned_complete_time &lt;= #{queryParams.plannedCompleteTime[1]}
            </if>
            <if test="queryParams.actualStartTime != null and queryParams.actualStartTime.size() >= 2">
                AND actual_start_time >= #{queryParams.actualStartTime[0]}
                AND actual_start_time &lt;= #{queryParams.actualStartTime[1]}
            </if>
            <if test="queryParams.actualCompleteTime != null and queryParams.actualCompleteTime.size() >= 2">
                AND actual_complete_time >= #{queryParams.actualCompleteTime[0]}
                AND actual_complete_time &lt;= #{queryParams.actualCompleteTime[1]}
            </if>
            <if test="queryParams.completeStatus != null">
                <!-- 1：已完成（提交时间、计划完成时间、实际开始时间、实际完成时间均有值） -->
                <if test="queryParams.completeStatus == 1">
                    AND submit_time IS NOT NULL
                    AND planned_complete_time IS NOT NULL
                    AND actual_start_time IS NOT NULL
                    AND actual_complete_time IS NOT NULL
                </if>
                <!-- 2：未开始 -->
                <if test="queryParams.completeStatus == 2">
                    AND actual_start_time IS NULL
                </if>
                <!-- 3：未完成 -->
                <if test="queryParams.completeStatus == 3">
                    AND actual_start_time IS NOT NULL
                    AND actual_complete_time IS NULL
                </if>
                <!-- 0：默认不添加条件（筛选全部） -->
            </if>
        </where>
        ORDER BY
        -- id=1的放在最前面
        CASE WHEN id = 1 THEN 0 ELSE 1 END,
        -- 未填写项越多越靠前
        submit_time DESC,
        -- 全部填写完成的按提交时间由近到远排列
        empty_field_count DESC;
    </select>
</mapper>
