<?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" >

  <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>

      `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`,
      `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
    )
    values(
            #{id},
            #{vehicle},
            #{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}
          );
  </insert>

  <select id="getByPage4Month" parameterType="java.util.Map" resultType="com.xxfc.platform.vehicle.entity.VehicleBookRecord">
    select
      `id`,
      `vehicle`,
      `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
    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`,
            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,
            v.`code` as vehicleCode,
            v.number_plate as numberPlate,
            v.subordinate_branch,
            bc.name as subBranchName,
            vbf.booked_date as bookedDate
        from
            ${tbName} vbr left join
            vehicle v on vbr.vehicle = 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 and vbf.year_month = #{selectedMonth}
        where
            1=1
        <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="getById" parameterType="java.util.Map" resultType="com.xxfc.platform.vehicle.entity.VehicleBookRecord" >
        select
            vbr.`id`,
            vbr.`vehicle`,
            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
         from ${tbName} vbr
        where id = #{id}

    </select>



  <insert id="createTbIfNotExists" parameterType="java.lang.String">
    CREATE TABLE IF NOT EXISTS ${_parameter} (
     `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
     `vehicle` 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 '提车审核人姓名',
     `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`) 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>