<?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="cn.com.ty.lift.batch.applet.dao.mapper.AnnualInspectionDataMapper" >

    <!-- 获取时间段内的年检数据 -->
    <select id="getAnnualInspectionDataList" parameterType="cn.com.ty.lift.batch.applet.dao.model.request.AnnualInspectionDataRequest"
            resultType="cn.com.ty.lift.batch.applet.dao.model.AnnualInspectionDataModel">
        select distinct
            ai.plan_date as planDate,
            ai.finish_time as finishTime,
            ai.mt_company_id as mtCompanyId,
            ai.status as status,
            ai.step_status as stepStatus,
            p.project_name as projectName
        from
            annual_inspection ai
        left join
            project_lift_relevance plr
        on
            ai.lift_id = plr.lift_id
        left join
            project p
        on
            plr.project_id = p.id
        where
            ai.mt_company_id = #{mtCompanyId}
        and
            ai.plan_date <![CDATA[ >= ]]> #{startTimeDateStr}
        and
            ai.plan_date <![CDATA[ <= ]]> #{endTimeDateStr}
    </select>

    <sql id = "findAnnualProjectSql">
        from
            project p
        left join
            project_lift_relevance plr
        on
            p.id = plr.project_id
        left join
            annual_inspection ai
        on
            plr.lift_id = ai.lift_id
        where
            p.project_status <![CDATA[ != ]]> '3'
        and
            p.mt_company_id = #{mtCompanyId}
        and
            ai.plan_date <![CDATA[ >= ]]> #{startTimeDateStr}
        and
            ai.plan_date <![CDATA[ <= ]]> #{endTimeDateStr}
        <if test="projectName != null and projectName != ''">
            and p.project_name like #{projectName}
        </if>
    </sql>

    <!-- 获取年检项目数据 -->
    <select id="getProjectAnnualCount" parameterType="cn.com.ty.lift.batch.applet.dao.model.request.AnnualInspectionDataRequest"
            resultType="java.lang.Long">
        select
            count(1)
        <include refid="findAnnualProjectSql"/>
    </select>

    <!-- 获取年检项目数据 -->
    <select id="getProjectAnnual" parameterType="cn.com.ty.lift.batch.applet.dao.model.request.AnnualInspectionDataRequest"
            resultType="cn.com.ty.lift.batch.applet.dao.model.AnnualInspectionDataModel">
        select
            distinct (p.id) as projectId,
            p.project_name as projectName
        <include refid="findAnnualProjectSql"/>
        limit #{pageNum}, #{pageSize}
    </select>

    <!-- 获取项目下的电梯年检状态 -->
    <select id="getProjectLiftAnnualInfo" parameterType="cn.com.ty.lift.batch.applet.dao.model.request.AnnualInspectionDataRequest"
            resultType="cn.com.ty.lift.batch.applet.dao.model.AnnualInspectionDataModel">
        select
            l.registration_code as registrationCode,
            l.device_position as devicePosition,
            ai.step_status as stepName,
            ai.plan_date as planDate,
            ai.status as status
        from
            annual_inspection ai
        left join
            lift l
        on
            ai.lift_id = l.id
        left join
            project_lift_relevance plr
        on
            ai.lift_id = plr.lift_id
        where
            ai.mt_company_id = #{mtCompanyId}
        and
            plr.project_id = #{projectId}
        and
            ai.plan_date <![CDATA[ >= ]]> #{startTimeDateStr}
        and
            ai.plan_date <![CDATA[ <= ]]> #{endTimeDateStr}
    </select>

    <!-- 获取项目关联的电梯 -->
    <select id="projectLiftNum" parameterType="cn.com.ty.lift.batch.applet.dao.model.request.AnnualInspectionDataRequest"
            resultType="cn.com.ty.lift.batch.applet.dao.model.AnnualInspectionDataModel">
        select
            plr.project_id as projectId
        from
            project_lift_relevance plr
        where
            plr.mt_company_id = #{mtCompanyId}
    </select>

    <!-- 获取项目已经开始年检的电梯 -->
    <select id="projectAnnualLiftNum" parameterType="cn.com.ty.lift.batch.applet.dao.model.request.AnnualInspectionDataRequest"
            resultType="cn.com.ty.lift.batch.applet.dao.model.AnnualInspectionDataModel">
        select
            plr.project_id as projectId
        from
            project_lift_relevance plr
        where
            plr.mt_company_id = #{mtCompanyId}
        and
            plr.lift_id in (select lift_id from annual_inspection)
    </select>


</mapper>