<?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.xxfc.platform.vehicle.mapper.VehicleBookRecordMapper">

    <resultMap id="getVehicleMap" type="com.xxfc.platform.vehicle.pojo.VehicleRecordInfoVo">
        <association property="vehicle" column="vehicle_id"
                     select="com.xxfc.platform.vehicle.mapper.VehicleMapper.selectById"/>
    </resultMap>

    <resultMap id="searchBookRecord" type="com.xxfc.platform.vehicle.pojo.VehicleBookRecordVo">
        <result column="id" property="id" jdbcType="BIGINT" javaType="java.lang.Long"/>
        <association property="vehicleDepartureLogVo" column="id"
                     select="com.xxfc.platform.vehicle.mapper.VehicleDepartureLogMapper.selectByBookRecordId"/>
    </resultMap>

    <resultMap id="getVehicleHourMap" type="com.xxfc.platform.vehicle.pojo.VehicleBookRecordVo">
        <association property="vehicleBookHourInfo" column="id"
                     select="com.xxfc.platform.vehicle.mapper.VehicleBookHourInfoMapper.selectByBookRecordId"/>
    </resultMap>

    <update id="changeRecordStatus" parameterType="java.util.Map">
        update vehicle_book_record set
        <if test="reviewerApply != null ">
            reviewer_apply =#{reviewerApply},
        </if>
        <if test="reviewerNameApply != null and reviewerNameApply !=''">
            reviewer_name_apply =#{reviewerNameApply},
        </if>
        <if test="reviewerReturn != null ">
            reviewer_return =#{reviewerReturn},
        </if>
        <if test="reviewerNameReturn != null and reviewerNameReturn !=''">
            reviewer_name_return =#{reviewerNameReturn},
        </if>
        <if test="reviewerCancel != null">
            reviewer_cancel =#{reviewerCancel},
        </if>
        <if test="reviewerNameCancel != null and reviewerNameCancel !=''">
            reviewer_name_cancel =#{reviewerNameCancel},
        </if>
        <if test="mileageLift != null">
            mileage_lift =#{mileageLift},
        </if>
        <if test="mileageRet != null ">
            mileage_ret =#{mileageRet},
        </if>
        <if test="haveViolation != null">
            have_violation =#{haveViolation},
        </if>
        <if test="rejectRemark != null">
            reject_remark = #{rejectRemark},
        </if>
        `status` = #{status}
        where id = #{id} and `status` = #{statusCondition}
    </update>

    <delete id="del4YearMoth" parameterType="java.util.Map">
    delete  from vehicle_book_record where `book_end_date` between #{startDate} and #{endDate} limit #{batchSize}
  </delete>

    <select id="getIdInYearMothLimit" parameterType="java.util.Map" resultType="java.lang.Long">
        select id  from vehicle_book_record where `book_end_date` between #{startDate} and #{endDate} limit #{batchSize}
    </select>

    <update id="liftOrRet" parameterType="java.util.Map">
        update vehicle_book_record
        set `status` = #{targetStatus},
        <if test="reviewerLift != null">
            `reviewer_lift` = #{reviewerLift},
            `reviewer_name_lift` = #{reviewerNameLift},
        </if>
        <if test="reviewerReturn != null">
            `reviewer_return` = #{reviewerReturn},
            `reviewer_name_return` = #{reviewerNameReturn},
        </if>
        <if test="liftRemark != null and liftRemark != ''">
            `lift_remark` = #{liftRemark},
        </if>
        <if test="retRemark != null and retRemark != ''">
            `ret_remark` = #{retRemark},
        </if>
        <if test="retCompany != null">
            `ret_company` = #{retCompany},
        </if>
        <if test="liftCompany != null">
            `lift_company` = #{liftCompany},
        </if>
        <if test="mileageLift != null">
            mileage_lift =#{mileageLift},
        </if>
        <if test="mileageRet != null ">
            mileage_ret =#{mileageRet},
        </if>
        <if test="haveViolation != null">
            have_violation =#{haveViolation},
        </if>
        <choose>
            <when test="targetStatus == 7">`actual_start_date` = now()</when>
            <when test="targetStatus == 3">`actual_end_date` = now()</when>
        </choose>
        where id = #{id} and `status` = #{conditionStatus}
    </update>

    <insert id="insertHis" parameterType="java.util.Map">
    insert into ${tbName} (
      `id`,
      `vehicle_id`,
      `status`,
      `book_type`,
      `book_user`,
      `book_user_name`,
      `contact_info`,
      `book_start_date`,
      `book_end_date`,
      `lift_location`,
      `lift_addr`,
      `remark`,
      `destination`,
      `reviewer_apply`,
      `reviewer_name_apply`,
      `reviewer_return`,
      `reviewer_name_return`,
      `reviewer_cancel`,
      `reviewer_name_cancel`,
      `actual_start_date`,
      `actual_end_date`,
      `reviewer_lift`,
      `reviewer_name_lift`,
      lift_company,
      lift_remark,
      ret_company,
      mileage_lift,
      mileage_ret,
      have_violation,
      ret_remark,
      order_no,
      vehicle_username,
      vehicle_user_phone,
      upkeep_ids
    )
    values(
            #{id},
            #{vehicleId},
            #{status},
            #{bookType},
            #{bookUser},
            #{bookUserName},
            #{contactInfo},
            #{bookStartDate},
            #{bookEndDate},
            #{liftLocation},
            #{liftAddr},
            #{remark},
            #{destination},
            #{reviewerApply},
            #{reviewerNameApply},
            #{reviewerReturn},
            #{reviewerNameReturn},
            #{reviewerCancel},
            #{reviewerNameCancel},
            #{actualStartDate},
            #{actualEndDate},
            #{reviewerLift},
            #{reviewerNameLift},
            #{liftCompany},
            #{liftRemark},
            #{retCompany},
            #{mileageLift},
            #{mileageRet},
            #{haveViolation},
            #{retRemark},
            #{orderNo},
            #{vehicleUsername},
            #{vehicleUserPhone},
            #{upkeepIds}
          );
  </insert>

    <select id="getByPage4Month" parameterType="java.util.Map"
            resultType="com.xxfc.platform.vehicle.entity.VehicleBookRecord">
    select
      `id`,
      `vehicle_id`,
      `status`,
      `book_type`,
      `book_user`,
      `book_user_name`,
      `contact_info`,
      `book_start_date`,
      `book_end_date`,
      `lift_location`,
      `lift_addr`,
      `remark`,
      `create_time`,
      `update_time`,
      `destination`,
      `reviewer_apply`,
      `reviewer_name_apply`,
      `reviewer_return`,
      `reviewer_name_return`,
      `reviewer_cancel`,
      `reviewer_name_cancel`,
      `actual_start_date`,
      `actual_end_date`,
      `reviewer_lift`,
      `reviewer_name_lift`,
      lift_company,
      lift_remark,
      ret_company,
      mileage_lift,
      mileage_ret,
      have_violation,
      ret_remark,
      order_no,
       vehicle_username,
      vehicle_user_phone,
      upkeep_ids
    from
         vehicle_book_record
    where
          `book_end_date` between #{startDate} and #{endDate}
    order by id
    limit #{pageStart},#{pageSize}
  </select>

    <select id="getByPage" parameterType="java.util.Map"
            resultType="com.xxfc.platform.vehicle.pojo.QueryVehicleBookRecordVo">
        select
        vbr.`id`,
        vbr.`vehicle_id`,
        vbr.`status`,
        vbr.`book_type`,
        vbr.`book_user`,
        vbr.`book_user_name`,
        vbr.`contact_info`,
        vbr.`book_start_date`,
        vbr.`book_end_date`,
        vbr.`lift_location`,
        vbr.`lift_addr`,
        vbr.`remark`,
        vbr.`create_time`,
        vbr.`update_time`,
        vbr.`destination`,
        vbr.`reviewer_apply`,
        vbr.`reviewer_name_apply`,
        vbr.`reviewer_return`,
        vbr.`reviewer_name_return`,
        vbr.`reviewer_cancel`,
        vbr.`reviewer_name_cancel`,
        vbr.`actual_start_date`,
        vbr.`actual_end_date`,
        vbr.`reviewer_lift`,
        vbr.`reviewer_name_lift`,
        vbr.lift_company,
        bcl.name as liftCompanyName ,
        vbr.lift_remark,
        vbr.ret_company,
        bcr.name as retCompanyName ,
        vbr.mileage_lift,
        vbr.mileage_ret,
        vbr.have_violation,
        vbr.ret_remark,
        vbr.order_no,
        vbr.vehicle_username,
        vbr.vehicle_user_phone,
        vbr.upkeep_ids,
        v.`code` as vehicleCode,
        v.number_plate as numberPlate,
        v.subordinate_branch,
        bc.name as subBranchName,
        vbf.booked_date as bookedDate,
        vbr.vehicle_username,
        vbr.vehicle_user_phone
        from
        ${tbName} vbr left join
        vehicle v on vbr.vehicle_id = v.id left join
        branch_company bc on bc.id = v.subordinate_branch left join
        branch_company bcl on bcl.id = vbr.lift_company left join
        branch_company bcr on bcr.id = vbr.ret_company left join
        vehicle_book_info vbf on vbf.vehicle = vbr.vehicle_id and vbf.year_month = #{selectedMonth}
        where
        1=1
        <if test="companyList != null and companyList.size() > 0">
            and ( vbr.lift_company in (
            <trim suffixOverrides=",">
                <foreach collection="companyList" item="companyId">
                    #{companyId},
                </foreach>
            </trim>
            )
            or vbr.ret_company in (
            <trim suffixOverrides=",">
                <foreach collection="companyList" item="companyId">
                    #{companyId},
                </foreach>
            </trim>
            )
            )
        </if>
        <if test="liftCompany !=null">
            and vbr.lift_company = #{liftCompany}
        </if>
        <if test="retCompany !=null">
            and vbr.ret_company = #{retCompany}
        </if>
        <if test="numberPlate !=null and numberPlate !=''">
            and v.number_plate = #{numberPlate}
        </if>
        <if test="vehicleCode !=null and vehicleCode !=''">
            and v.code = #{vehicleCode}
        </if>
        <if test="status !=null ">
            and vbr.status = #{status}
        </if>
        <if test="subordinateBranch !=null">
            and v.subordinate_branch = #{subordinateBranch}
        </if>
        <if test="bookedStartDate !=null">
            and vbr.book_start_date &gt;= #{bookedStartDate}
        </if>
        <if test="bookedEndDate !=null">
            and vbr.book_end_date &lt;= #{bookedEndDate}
        </if>
        order by vbr.id desc
    </select>

    <select id="selectByVehicleIdAndTime" parameterType="java.util.Map" resultMap="searchBookRecord">
        select v1.* from vehicle_book_record v1
         where v1.vehicle_id = #{vehicleId} and v1.book_end_date &gt; #{time} and v1.status BETWEEN 1 and 2
    </select>

    <select id="selectZeroHourRecord" resultMap="searchBookRecord" parameterType="java.util.Map">
        select v1.* from vehicle_book_record v1
         where v1.vehicle_id = #{vehicleId}
         and (v1.book_end_date = #{bookedEndDate} or v1.book_start_date = #{bookedEndDate})
         and v1.status BETWEEN 1 and 2
    </select>

    <select id="getById" parameterType="java.util.Map" resultType="com.xxfc.platform.vehicle.entity.VehicleBookRecord">
        select
            vbr.`id`,
            vbr.`vehicle_id`,
            vbr.`status`,
            vbr.`book_type`,
            vbr.`book_user`,
            vbr.`book_user_name`,
            vbr.`contact_info`,
            vbr.`book_start_date`,
            vbr.`book_end_date`,
            vbr.`lift_location`,
            vbr.`lift_addr`,
            vbr.`remark`,
            vbr.`create_time`,
            vbr.`update_time`,
            vbr.`destination`,
            vbr.`reviewer_apply`,
            vbr.`reviewer_name_apply`,
            vbr.`reviewer_return`,
            vbr.`reviewer_name_return`,
            vbr.`reviewer_cancel`,
            vbr.`reviewer_name_cancel`,
            vbr.`actual_start_date`,
            vbr.`actual_end_date`,
            vbr.`reviewer_lift`,
            vbr.`reviewer_name_lift`,
            vbr.lift_company,
            vbr.lift_remark,
            vbr.ret_company,
            vbr.mileage_lift,
            vbr.mileage_ret,
            vbr.have_violation,
            vbr.ret_remark,
            vbr.order_no,
            vbr.vehicle_username,
            vbr.vehicle_user_phone,
            vbr.upkeep_ids,
         from ${tbName} vbr
        where id = #{id}

    </select>
    <select id="selectByVehicleId" parameterType="java.lang.String"
            resultMap="searchBookRecord">
       SELECT v1.*,bc2.name retCompanyName from vehicle_book_record v1
               LEFT JOIN branch_company bc2 on v1.ret_company = bc2.id
        where v1.vehicle_id = #{vehicleId} and v1.status BETWEEN 1 and 2
    </select>
    <select id="getByParam" parameterType="java.util.Map" resultMap="getVehicleMap">
        select v1.* from vehicle_book_record v1
        left join vehicle v2 on v2.id = v1.vehicle_id
        <where>
        <if test="startTime != null">
            and v1.create_time between #{startTime} and #{endTime}
        </if>
        <if test="numberPlate != null || numberPlate != ''">
            and v2.number_plate = #{numberPlate}
        </if>
        <if test="status != null">
            and v2.status = #{status}
        </if>
        <if test="subordinateBranch != null">
            and v2.subordinate_branch = #{subordinateBranch}
        </if>
        <if test="parkBranchCompanyId != null">
            and v2.park_branch_company_id = #{parkBranchCompanyId}
        </if>
    </where>
        order By v1.create_time DESC
    </select>

    <select id="getBookRecord" resultMap="searchBookRecord"
            parameterType="com.xxfc.platform.vehicle.pojo.VehicleBookRecordQueryVo">
        select bc3.name parkCompanyName,bc4.name subordinateBranchName, conv(v4.booked_hour,10,2)
        startHour,conv(v5.booked_hour,10,2) endHour, bc1.`name` lift_company_name, bc2.`name` ret_company_name,
        v3.number_plate,v1.*,v3.code
        from vehicle_book_record v1
        LEFT JOIN vehicle_book_hour_info v4 on v4.book_record_id = v1.id and YEAR(v4.year_month_day) =
        YEAR(v1.book_start_date) AND MONTH(v4.year_month_day) = MONTH(v1.book_start_date) AND DAY(v4.year_month_day)
        =DAY(v1.book_start_date)
        LEFT JOIN vehicle_book_hour_info v5 on v5.book_record_id = v1.id and YEAR(v5.year_month_day) =
        YEAR(v1.book_end_date) AND MONTH(v5.year_month_day) = MONTH(v1.book_end_date) AND DAY(v5.year_month_day)
        =DAY(v1.book_end_date)
        LEFT JOIN branch_company bc1 ON v1.lift_company = bc1.id
        LEFT JOIN branch_company bc2 on v1.ret_company = bc2.id
        LEFT JOIN vehicle v3 on v3.id = v1.vehicle_id
        LEFT JOIN branch_company bc3 on v3.park_branch_company_id = bc3.id
        LEFT JOIN branch_company bc4 on v3.subordinate_branch = bc4.id
        <where>
            <if test="selectedMonth != null">
                and (v1.book_start_date like CONCAT(#{selectedMonth}, "%") or v1.book_end_date like
                CONCAT(#{selectedMonth}, "%"))
            </if>
            <if test="numberPlate != null">
                and v3.number_plate = #{numberPlate}
            </if>
            <if test="code != null">
                and v3.code = #{code}
            </if>
            <if test="bookType != null">
                and v1.book_type = #{bookType}
            </if>
            <if test="companyIds != null and companyIds.size > 0">
                and (v1.lift_company in
                <foreach collection="companyIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
                or v1.ret_company in
                <foreach collection="companyIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>)
            </if>
            and v1.status between 1 and 2 and v1.book_user != -2
        </where>

        order by create_time DESC
    </select>


    <select id="getBookRecordInfo" resultMap="searchBookRecord" parameterType="java.util.Map">
        select  (CASE v1.lift_company WHEN #{userCompany} THEN 1 ELSE 0 end) liftStatus,(CASE v1.ret_company WHEN
        #{userCompany} THEN 1 ELSE 0 end) retStatus,bc4.name subordinateBranchName, bc1.`name` lift_company_name,
        bc2.`name` ret_company_name, v3.number_plate,v1.*,v3.code
        from vehicle_book_record v1
        LEFT JOIN branch_company bc1 ON v1.lift_company = bc1.id
        LEFT JOIN branch_company bc2 on v1.ret_company = bc2.id
        LEFT JOIN vehicle v3 on v3.id = v1.vehicle_id
        LEFT JOIN branch_company bc3 on v3.park_branch_company_id = bc3.id
        LEFT JOIN branch_company bc4 on v3.subordinate_branch = bc4.id
        LEFT JOIN vehicle_departure_log v4 on v4.book_record_id = v1.id
        <where>
            <if test="selectedMonth != null">
                and (v1.book_start_date like CONCAT(#{selectedMonth}, "%") or v1.book_end_date like
                CONCAT(#{selectedMonth}, "%"))
            </if>
            <if test="numberPlate != null">
                and v3.number_plate = #{numberPlate}
            </if>
            <if test="bookType != null">
                and v1.book_type = #{bookType}
            </if>
            <if test="keywords != null and keywords != ''">
                and (v3.number_plate like concat('%', #{keywords}, '%') or v3.code like concat('%', #{keywords}, '%'))
            </if>
            <if test="companyIds != null and companyIds.size > 0">
                and (v1.lift_company in
                <foreach collection="companyIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
                or v1.ret_company in
                <foreach collection="companyIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>)
            </if>
            <if test="status != null and status != 0 and status != -1 and status != -2">
                and v1.status = #{status}
            </if>
            <if test="status != null and status == 0 ">
                and v4.state = 0
            </if>
            <if test="status != null and status == -1 ">
                and v4.state = 1
            </if>
            <if test="code != null">
                and v3.code = #{code}
            </if>
            <!--增加时间限制，未审核的小于当前时间的不显示-->
            <if test="flag == true and status == 1">
                and book_end_date &gt;= now()
            </if>
            <!--增加时间限制，已审核的未出车小于当前时间的不显示， 已出车的结束时间延后两天小于当前时间的不显示-->
            <if test="flag == true and status == 2">
                and ( v4.id IS NOT NULL  OR (v4.id IS NULL AND v1.book_end_date &gt;= now()))
            </if>
            and v1.book_user != -2
        </where>
        group by v1.id
        order by create_time DESC
    </select>


    <insert id="createTbIfNotExists" parameterType="java.lang.String">
    CREATE TABLE IF NOT EXISTS ${_parameter} (
     `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
     `vehicle_id` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '车辆id',
     `status` int(255) NULL DEFAULT NULL COMMENT '申请状态：1-申请中 2-已通过 3-已归还 4-拒绝 5-逾期归还 6-已取消',
     `book_type` int(11) NOT NULL COMMENT '预定类型，1-用户租赁、2-内部员工申请 3-维修',
     `book_user` int(11) NOT NULL COMMENT '预定用户id',
     `book_user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '申请人姓名',
     `contact_info` varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '联系信息，比如电话、联系人姓名等(json)',
     `book_start_date` datetime(0) NOT NULL COMMENT '申请开始日期',
     `book_end_date` datetime(0) NOT NULL COMMENT '申请结束日期',
     `lift_location` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '提车地点(经纬度)',
     `lift_addr` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '提车地址',
     `remark` varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
     `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
     `update_time` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0),
     `destination` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '目的地',
     `reviewer_apply` int(10) NULL DEFAULT NULL COMMENT '申请审核人，-1代表系统',
     `reviewer_name_apply` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '申请审核人姓名',
     `reviewer_return` int(10) NULL DEFAULT NULL COMMENT '归还审核人，-1代表系统',
     `reviewer_name_return` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '归还审核人姓名',
     `reviewer_cancel` int(10) NULL DEFAULT NULL COMMENT '取消人，-1代表系统',
     `reviewer_name_cancel` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '取消预定人姓名',
     `actual_start_date` datetime(0) NULL DEFAULT NULL COMMENT '实际开始日期(提车时间)',
     `actual_end_date` datetime(0) NULL DEFAULT NULL COMMENT '实际结束日期（还车时间）',
     `lift_company` int(10) NULL DEFAULT NULL COMMENT '提车地点（公司）',
     `lift_remark` varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '提车备注',
     `ret_company` int(10) NULL DEFAULT NULL COMMENT '还车地点（公司）',
     `ret_remark` varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '还车备注',
     `reviewer_lift` int(10) NULL DEFAULT NULL COMMENT '提车审核人',
     `reviewer_name_lift` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '提车审核人姓名',
     `order_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '订单号',
     `mileage_lift` int(10) NULL DEFAULT NULL COMMENT '提车里程数',
     `mileage_ret` int(10) NULL DEFAULT NULL COMMENT '还车里程数',
     `have_violation` tinyint(4) NULL DEFAULT NULL COMMENT '是否违章',
     PRIMARY KEY (`id`) USING BTREE,
     INDEX `i_vehicle`(`vehicle_id`) USING BTREE,
     INDEX `i_book_user`(`book_user`) USING BTREE,
     INDEX `i_book_end_date`(`book_end_date`) USING BTREE COMMENT '迁移至历史表时查询用到该索引（暂时）'
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '汽车预定记录历史表，按年分表' ROW_FORMAT = Dynamic;

  </insert>


</mapper>