<?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.system.homepage.dao.mapper.CommonDataMapper">
    <!-- 公司id -> 数量sql -->
    <sql id="companyIdToCountSql">
        mt_company_id as mtCompanyId, count(mt_company_id) as dataCount
    </sql>
    <!-- 公司id sql设置 -->
    <sql id="setCompanyId">
        <if test="companyIdList != null">
            and mt_company_id in
            <foreach item="item" index="index" collection="companyIdList" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
    </sql>

    <!-- 电梯信息 sql -->
    <sql id="liftInfoSql">
        l.id as liftId,
        l.registration_code as registrationCode,
        l.lift_code as liftCode,
        p.project_name as projectName,
        ui.name as workerName,
    </sql>

    <!-- 电梯信息关联字段 sql -->
    <sql id="liftInfoJoinSql">
        left join
            project_lift_relevance plr
        on
            l.id = plr.lift_id
        left join
            project p
        on
            plr.project_id = p.id
        left join
            user_info ui
        on
            plr.worker_id = ui.user_id
    </sql>

    <!-- 获取维保计划数据 -->
    <select id="getMtPlanData" parameterType="cn.com.ty.lift.system.homepage.dao.dto.request.LiftDataRequest"
            resultType="cn.com.ty.lift.system.homepage.dao.model.MtPlanDataModel">
        select
            mp.lift_id as liftId,
            mp.status as status
        from
            maintenance_plan mp
        where
            mp.mt_company_id = #{mtCompanyId}
        and
            mp.plan_date <![CDATA[ >= ]]> #{startDateStr}
        and
            mp.plan_date <![CDATA[ <= ]]> #{endDateStr}
    </select>

    <!-- 获取急修记录数据 -->
    <select id="getEmergencyRecordData" parameterType="cn.com.ty.lift.system.homepage.dao.dto.request.LiftDataRequest"
            resultType="cn.com.ty.lift.system.homepage.dao.model.EmergencyRecordDataModel">
        select
            er.lift_id as liftId,
            er.status as status
        from
            emergency_repair er
        where
            er.mt_company_id = #{mtCompanyId}
        and
            er.caller_date <![CDATA[ >= ]]> #{startDateStr}
        and
            er.caller_date <![CDATA[ <= ]]> #{endDateStr}
    </select>

    <!-- 获取电梯维保记录 -->
    <select id="getMtRecordData" parameterType="cn.com.ty.lift.system.homepage.dao.dto.request.LiftDataRequest"
            resultType="cn.com.ty.lift.system.homepage.dao.model.MtRecordDataModel">
        select
            mr.lift_id as liftId,
            mr.status as stats
        from
            maintenance_record mr
        where
            mr.mt_company_id = #{mtCompanyId}
        and
            mr.work_date <![CDATA[ >= ]]> #{startDateStr}
        and
            mr.work_date <![CDATA[ <= ]]> #{endDateStr}
    </select>

    <!-- 获取公司项目数 -->
    <select id="getMtCompanyIdToProjectNum"
            parameterType="cn.com.ty.lift.system.settings.dao.entity.model.MaintenanceCompanyRequest"
            resultType="cn.com.ty.lift.system.homepage.dao.model.CountDataModel">
        select
        <include refid="companyIdToCountSql"/>
        from
        project
        group by
        mt_company_id
        having
        1=1
        <!-- 判断公司id是否在集合中 -->
        <include refid="setCompanyId"/>

    </select>

    <!-- 获取公司维保记录 -->
    <select id="getMtCompanyIdToMtRecord"
            parameterType="cn.com.ty.lift.system.settings.dao.entity.model.MaintenanceCompanyRequest"
            resultType="cn.com.ty.lift.system.homepage.dao.model.CountDataModel">
        select
        <include refid="companyIdToCountSql"/>
        from
        maintenance_record
        group by
        mt_company_id
        having
        1=1
        <!-- 判断公司id是否在集合中 -->
        <include refid="setCompanyId"/>
    </select>

    <!-- 获取公司急修记录 -->
    <select id="getMtCompanyIdToEmergencyRecord"
            parameterType="cn.com.ty.lift.system.settings.dao.entity.model.MaintenanceCompanyRequest"
            resultType="cn.com.ty.lift.system.homepage.dao.model.CountDataModel">
        select
        <include refid="companyIdToCountSql"/>
        from
        emergency_repair
        group by
        mt_company_id
        having
        1=1
        <!-- 判断公司id是否在集合中 -->
        <include refid="setCompanyId"/>
    </select>

    <!--################################################################################-->
    <!--#############################电梯日历数据库代码####################################-->
    <!--################################################################################-->

    <!-- 获取当前时间到月末时间段内的公司维保计划 -->
    <select id="getMonthNowToEndMtPlan"
            parameterType="cn.com.ty.lift.system.homepage.dao.dto.request.PlatformCalendarRequest"
            resultType="cn.com.ty.lift.system.homepage.dao.model.MtPlanDataModel">
        select
        <include refid="liftInfoSql"/>
        mp.plan_date as planDate,
        mp.status as status
        from
        maintenance_plan mp
        left join
        lift l
        on
        mp.lift_id = l.id

        <include refid="liftInfoJoinSql"/>
        where
        mp.mt_company_id = #{mtCompanyId}
        and
        mp.plan_date <![CDATA[ >= ]]> #{monthBeginStr}
        and
        mp.plan_date <![CDATA[ <= ]]> #{monthEndStr}

    </select>

    <!-- 获取月初到当前时间段内公司的维保记录信息 -->
    <select id="getMonthBeginToNowMtRecord"
            parameterType="cn.com.ty.lift.system.homepage.dao.dto.request.PlatformCalendarRequest"
            resultType="cn.com.ty.lift.system.homepage.dao.model.MtRecordDataModel">
        select
        <include refid="liftInfoSql"/>
        mr.work_date as workDate,
        mr.status as status
        from
        maintenance_record mr
        left join
        lift l
        on
        mr.lift_id = l.id
        <include refid="liftInfoJoinSql"/>
        where
        mr.mt_company_id = #{mtCompanyId}
        and
        mr.work_date <![CDATA[ >= ]]> #{monthBeginStr}
        and
        mr.work_date <![CDATA[ <= ]]> #{monthEndStr}

    </select>

    <!-- 获取月初到当前时间段内公司的急修记录信息 -->
    <select id="getMonthBeginToNowEmergencyRecord"
            parameterType="cn.com.ty.lift.system.homepage.dao.dto.request.PlatformCalendarRequest"
            resultType="cn.com.ty.lift.system.homepage.dao.model.EmergencyRecordDataModel">
        select
        <include refid="liftInfoSql"/>
        er.caller_date as callerDate,
        er.recovery_date as recoveryDate,
        er.status as status
        from
        emergency_repair er
        left join
        lift l
        on
        er.lift_id = l.id
        <include refid="liftInfoJoinSql"/>
        where
        er.mt_company_id = #{mtCompanyId}
        and
        er.caller_date <![CDATA[ >= ]]> #{monthBeginStr}
        and
        er.caller_date <![CDATA[ <= ]]> #{monthEndStr}

    </select>

    <!-- 获取本月时间段内的年检记录信息 -->
    <select id="getMonthBeginToEndAnnualInspectionRecord"
            parameterType="cn.com.ty.lift.system.homepage.dao.dto.request.PlatformCalendarRequest"
            resultType="cn.com.ty.lift.system.homepage.dao.model.AnnualInspectionDataModel">
        select
        <include refid="liftInfoSql"/>
        ai.finish_time as finishTime,
        ai.status as status
        from
        annual_inspection ai
        left join
        lift l
        on
        ai.lift_id = l.id
        <include refid="liftInfoJoinSql"/>
        where
        ai.mt_company_id = #{mtCompanyId}
        and
        ai.finish_time <![CDATA[ >= ]]> #{monthBeginStr}
        and
        ai.finish_time <![CDATA[ <= ]]> #{monthEndStr}
    </select>

    <!-- 获取 年份-项目总数 -->
    <select id="getEveryYearProjectCount"
            parameterType="cn.com.ty.lift.system.homepage.dao.dto.request.DataStatisticsRequest"
            resultType="cn.com.ty.lift.system.homepage.dao.model.CountDataModel">
        select
            SUBSTR(create_date, 1, 4) as year,
            count(create_date) as dataCount
        from project
        where
            mt_company_id = #{companyId}
        and
            create_date <![CDATA[ >= ]]> #{beforeDateStr}
        and
            create_date <![CDATA[ <= ]]> #{nowDateStr}
        group by
            SUBSTR(create_date, 1, 4)

    </select>

    <!--获取 年份-维保次数 -->
    <select id="getEveryYearMaintenanceCount"
            parameterType="cn.com.ty.lift.system.homepage.dao.dto.request.DataStatisticsRequest"
            resultType="cn.com.ty.lift.system.homepage.dao.model.CountDataModel">
        select
            SUBSTR(create_date, 1, 4) as year,
            count(create_date) as dataCount
        from maintenance_record
        where
            mt_company_id = #{companyId}
        and
            create_date <![CDATA[ >= ]]> #{beforeDateStr}
        and
            create_date <![CDATA[ <= ]]> #{nowDateStr}
        group by
            SUBSTR(create_date, 1, 4)

    </select>
    <!--获取 年份-急修次数 -->
    <select id="getEveryYearEmergencyCount"
            parameterType="cn.com.ty.lift.system.homepage.dao.dto.request.DataStatisticsRequest"
            resultType="cn.com.ty.lift.system.homepage.dao.model.CountDataModel">
        select
            SUBSTR(create_date, 1, 4) as year,
            count(create_date) as dataCount
        from emergency_repair
        where
            mt_company_id = #{companyId}
        and
            create_date <![CDATA[ >= ]]> #{beforeDateStr}
        and
            create_date <![CDATA[ <= ]]> #{nowDateStr}
        group by
            SUBSTR(create_date, 1, 4)

    </select>
    <!--获取 年份-年检次数 -->
    <select id="getEveryYearAnnualCount"
            parameterType="cn.com.ty.lift.system.homepage.dao.dto.request.DataStatisticsRequest"
            resultType="cn.com.ty.lift.system.homepage.dao.model.CountDataModel">
        select
            SUBSTR(create_date, 1, 4) as year,
            count(create_date) as dataCount
        from annual_inspection
        where
            mt_company_id = #{companyId}
        and
            create_date <![CDATA[ >= ]]> #{beforeDateStr}
        and
            create_date <![CDATA[ <= ]]> #{nowDateStr}
        group by
            SUBSTR(create_date, 1, 4)

    </select>
    <!--获取 年份-大修次数 -->
    <select id="getEveryYearCapitalCount"
            parameterType="cn.com.ty.lift.system.homepage.dao.dto.request.DataStatisticsRequest"
            resultType="cn.com.ty.lift.system.homepage.dao.model.CountDataModel">
        select
            SUBSTR(create_date, 1, 4) as year,
            count(create_date) as dataCount
        from capital_repair
        where
            mt_company_id = #{companyId}
        and
            create_date <![CDATA[ >= ]]> #{beforeDateStr}
        and
            create_date <![CDATA[ <= ]]> #{nowDateStr}
        group by
            SUBSTR(create_date, 1, 4)
    </select>

    <!-- 获取企业的最近活跃时间 -->
    <select id="getCompanyLastActiveTime" resultType="cn.com.ty.lift.system.settings.dao.entity.model.CompanyActiveVo">
        select
            mr.mt_company_id as companyId,
            max(mr.create_date) as activeTime
        from
            maintenance_record mr
        group by
            mr.mt_company_id;
    </select>

</mapper>