<?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.ningdatech.carapi.scheduler.mapper.VehicleOnlineStatusDayMapper">
    <select id="countTable" resultType="java.lang.Integer">
        SELECT count(0) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=#{tableName};
    </select>

    <select id="selectOneByTable" resultType="com.ningdatech.carapi.scheduler.entity.VehicleOnlineStatusDay">
        select
            id,create_on,create_by,update_on,
            update_by,status,car_plate,year,
            month,day,company_id,region_id
        from
            ${tableName}
        where car_plate = #{statusDay.carPlate} and
            year = #{statusDay.year} and
            month = #{statusDay.month} and
            day = #{statusDay.day}
            limit 1
    </select>

    <select id="listByTable" resultType="com.ningdatech.carapi.scheduler.entity.VehicleOnlineStatusDay">
        select
            id,create_on,create_by,update_on,
            update_by,status,car_plate,year,
            month,day,company_id,region_id
        from
            ${tableName}
        where
            year = #{statusDay.year} and
            month = #{statusDay.month} and
            day = #{statusDay.day}
    </select>

    <select id="selectOnlineCarPlatesInMonthTable" resultType="java.lang.String">
        select
           car_plate
        from
            ${tableName}
        where create_on >= #{date}
        group by car_plate
    </select>

    <update id="createTable">
        CREATE TABLE ${tableName} (
              `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
              `create_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
              `create_by` bigint(20) DEFAULT NULL COMMENT '创建人',
              `update_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
              `update_by` bigint(20) DEFAULT NULL COMMENT '修改人',
              `status` tinyint(2) DEFAULT NULL COMMENT '0离线 1在线 2异常',
              `car_plate` varchar(32) DEFAULT NULL COMMENT '车牌号',
              `year` varchar(16) DEFAULT NULL COMMENT '年',
              `month` varchar(16) DEFAULT NULL COMMENT '月',
              `day` varchar(16) DEFAULT NULL COMMENT '日',
              `company_id` bigint(20) DEFAULT NULL COMMENT '公司ID',
              `region_id` bigint(20) DEFAULT NULL COMMENT '区域ID',
              `deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0未删除1已删除',
              `online_time` datetime DEFAULT NULL COMMENT '车辆在线时间',
              `online_time_interval` int(11) DEFAULT NULL COMMENT '距离上次在线时间间隔',
              PRIMARY KEY (`id`) USING BTREE,
              UNIQUE KEY `uk_car_plate_day` (`deleted`,`year`,`month`,`day`,`car_plate`) USING BTREE
        ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='统计车辆上线状态 按天 月份表';
    </update>

    <insert id="saveByReqInMonthTable">
        insert into ${tableName}
        (status,car_plate,year,
         month,day,company_id,region_id)
        values
            (#{statusDay.status},#{statusDay.carPlate},#{statusDay.year},
             #{statusDay.month},#{statusDay.day},
             #{statusDay.companyId},#{statusDay.regionId})
    </insert>

    <update id="updateByReqInMonthTable">
        update ${tableName}
        set status = #{statusDay.status}
        where id = #{statusDay.id}
    </update>

    <insert id="batchSave" parameterType="java.util.List">
        INSERT INTO ${tableName}
        (status,car_plate,year,
        month,day,company_id,region_id,online_time,online_time_interval)
        values
        <foreach collection="statusDays" item="statusDay" separator="," index="index">
            (#{statusDay.status},#{statusDay.carPlate},#{statusDay.year},
            #{statusDay.month},#{statusDay.day},
            #{statusDay.companyId},#{statusDay.regionId},
             #{statusDay.onlineTime},#{statusDay.onlineTimeInterval})
        </foreach>
    </insert>
</mapper>
