<?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.equipment.EquipmentStandardEquipmentMapper">

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        mse.*
    </sql>

    <sql id="Common_condition">
        <if test="request.standardNo != null and request.standardNo != ''">
            and mse.standard_no like concat('%', #{request.standardNo}, '%')
        </if>
        <if test="request.standardName != null and request.standardName !=''">
            and mse.standard_name like concat('%', #{request.standardName}, '%')
        </if>
        <if test="request.category != null">
            and mse.category = #{request.category}
        </if>
        <if test="request.managerState != null">
            and mse.manager_state = #{request.managerState}
        </if>
        <if test="request.standardLaboratory != null">
            and mse.standard_laboratory = #{request.standardLaboratory}
        </if>
        <if test="request.preparationStartDate != null and request.preparationStartDate != ''">
            and DATE_FORMAT(mse.preparation_date,'%Y-%m-%d') <![CDATA[ >= ]]> #{request.preparationStartDate}
        </if>
        <if test="request.preparationEndDate != null and request.preparationEndDate != ''">
            and DATE_FORMAT(mse.preparation_date,'%Y-%m-%d') <![CDATA[ <= ]]> #{request.preparationEndDate}
        </if>
        <if test="request.id != null and request.id != ''">
            and mse.id = #{request.id}
        </if>
        <if test="request.ids != null and request.ids.size() > 0 ">
            and mse.id in
            <foreach collection='request.ids' item='id' open='(' separator=',' close=')'>
                #{id}
            </foreach>
        </if>
    </sql>

    <select id="selectMaxNo" resultType="java.lang.Long">
        SELECT IFNULL(max(RIGHT(standard_no, 12)), 0) from equipment_standard_equipment
    </select>

    <select id="listPage" resultType="com.casic.missiles.model.equipment.EquipmentStandardEquipment">
        SELECT <include refid="Base_Column_List"/>,
            ms.NAME AS standardOwnerName,
            mo.organize_name AS standardLaboratoryName,
            mo.director AS laboratoryOwnerName
        FROM
            equipment_standard_equipment mse
        LEFT JOIN meter_staff ms ON ms.id = mse.standard_owner
        LEFT JOIN meter_organize mo ON mo.dept_id = mse.standard_laboratory
        WHERE
        mse.is_del = 0
        <include refid="Common_condition"/>
        ORDER BY mse.create_time DESC
    </select>

    <!-- todo 查询有效日期(select字段，查询条件)，注意证书有效期只到日    -->
    <select id="remindListPage" resultType="com.casic.missiles.model.equipment.EquipmentStandardEquipment">
        SELECT <include refid="Base_Column_List"/>,
        su.name as standardOwnerName, mo.organize_name as standardLaboratoryName, mo.director as laboratoryOwnerName
        FROM equipment_standard_equipment mse
        LEFT JOIN sys_user su ON su.id = mse.standard_owner
        LEFT JOIN meter_organize mo on mo.dept_id = mse.standard_laboratory
        WHERE mse.is_del = 0
        AND CURRENT_TIMESTAMP >= mse.remind_time
        <include refid="Common_condition"/>
        <if test="request.validStartTime != null and request.validStartTime != ''">
            AND DATE_FORMAT( mse.remind_time, '%Y-%m-%d' ) &gt;= #{request.validStartTime}
        </if>
        <if test="request.validEndTime != null and request.validEndTime != ''">
            AND DATE_FORMAT( mse.remind_time, '%Y-%m-%d' ) &lt;= #{request.validEndTime}
        </if>
        ORDER BY mse.remind_time DESC
    </select>

    <select id="selectRemindQuantity" resultType="java.lang.Integer">
        SELECT IFNULL(COUNT(mse.id),0)
        FROM equipment_standard_equipment mse
        LEFT JOIN sys_user su ON su.id = mse.standard_owner
        LEFT JOIN meter_organize mo on mo.dept_id = mse.standard_laboratory
        WHERE mse.is_del = 0
        AND CURRENT_TIMESTAMP >= mse.remind_time
    </select>

    <select id="selectExpireEquipmentStatistics"
            resultType="com.casic.missiles.dto.cockpit.ExpireEquipmentStatisticsResponse">
        SELECT DATE_FORMAT(remind_time,'%Y-%m') AS month, COUNT(id) AS expiredQuantity
        FROM equipment_standard_equipment
        WHERE is_del = 0
        AND DATE_FORMAT(remind_time,'%Y') = DATE_FORMAT(CURDATE(),'%Y')
        GROUP BY DATE_FORMAT(remind_time,'%Y-%m')
    </select>
</mapper>
