<?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.picc.grab.mapper.CarinfoMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.picc.grab.domain.Carinfo">
        <id column="id" property="id"/>
        <result column="wtf_id" property="wtfId"/>
        <result column="jjbh" property="jjbh"/>
        <result column="fgs_id" property="fgsId"/>
        <result column="user_id" property="userId"/>
        <result column="cjh" property="cjh"/>
        <result column="cllx" property="cllx"/>
        <result column="csh" property="csh"/>
        <result column="pinpai" property="pinpai"/>
        <result column="chexi" property="chexi"/>
        <result column="chexing" property="chexing"/>
        <result column="cph" property="cph"/>
        <result column="fdjh" property="fdjh"/>
        <result column="djzrq" property="djzrq"/>
        <result column="ccrq" property="ccrq"/>
        <result column="cpxh" property="cpxh"/>
        <result column="bsxlx" property="bsxlx"/>
        <result column="rylx" property="rylx"/>
        <result column="syxz" property="syxz"/>
        <result column="syrxz" property="syrxz"/>
        <result column="pailia" property="pailia"/>
        <result column="pldw" property="pldw"/>
        <result column="bxlc" property="bxlc"/>
        <result column="jkfs" property="jkfs"/>
        <result column="sgyy" property="sgyy"/>
        <result column="cldfd_shen" property="cldfdShen"/>
        <result column="cldfd_shi" property="cldfdShi"/>
        <result column="cldfd_jtdz" property="cldfdJtdz"/>
        <result column="xjfs" property="xjfs"/>
        <result column="sfdy" property="sfdy"/>
        <result column="sfgz" property="sfgz"/>
        <result column="cjhsfbg" property="cjhsfbg"/>
        <result column="fdjhsfbg" property="fdjhsfbg"/>
        <result column="sfecsg" property="sfecsg"/>
        <result column="wtqfy" property="wtqfy"/>
        <result column="xjks" property="xjks"/>
        <result column="xjjs" property="xjjs"/>
        <result column="bjjs" property="bjjs"/>
        <result column="xjbz" property="xjbz"/>
        <result column="exbz" property="exbz"/>
        <result column="exzjbjid" property="exzjbjid"/>
        <result column="status" property="status"/>
        <result column="by1" property="by1"/>
        <result column="by2" property="by2"/>
        <result column="by3" property="by3"/>
        <result column="bbxrsfcz" property="bbxrsfcz"/>
        <result column="create_user_id" property="createUserId"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <result column="sffqmjbj" property="sffqmjbj"/>
    </resultMap>
    <!--查询最大的车辆id-->
    <select id="getMaxCarId" resultType="java.lang.Integer">
		SELECT CONVERT(id,SIGNED) FROM md_carinfo ORDER BY CONVERT(id,SIGNED) DESC LIMIT 0,1
	</select>
    <!--根据车架号查询车辆id-->
    <select id="findCaridByCjh" resultType="java.lang.String">
        SELECT id FROM md_carinfo WHERE cjh = #{cjh}
        <![CDATA[ AND STATUS <> 0

        ]]>
    </select>
    <!--根据车架号查询出价记录-->
    <select id="findCjjlByCjh" resultType="com.picc.grab.entity.grab.dto.CarCjjlDTO">
        SELECT cjsj,wtf,wtj,hsj FROM md_carcjjl WHERE cjh = #{cjh}
    </select>

    <!--查询报价截止时间还有15分未发送报价单的车辆-->
    <select id="findVehicleByTenMinute" resultType="java.lang.String">
		SELECT DISTINCT car.id FROM md_carInfo car  left join  md_wtf_Info wtf on wtf.id=car.wtf_id
		where car.status=4 and wtf.id=#{id} and car.fgs_id=#{fgsid}
	    and car.xjjs>now() and IFNULL(car.jjbh,'') !=''
	    <![CDATA[ and DATE_SUB(car.bjjs, INTERVAL 15 minute)<=now()
        ]]>
    </select>
    <!--查询车辆报价单金额-->
    <select id="findBjdjeByCarid" resultType="java.lang.String">
		SELECT bjd.bjdje FROM md_carbjd bjd WHERE bjd.carid = #{carid}  ORDER BY bjd.create_time DESC LIMIT 1
	</select>

    <!--维护车辆中标状态-->
    <select id="findVehicleByBid" resultType="java.util.Map">
	     SELECT   car.jjbh id,zw.zbgzstatus,car.id carid from  md_carinfo car
	     LEFT JOIN md_zbwt zw on zw.carid=car.id
         LEFT JOIN md_wtf_info wtf on wtf.id=car.wtf_id
         where car.fgs_id=#{fgsid} and wtf.yjwtf=#{wtfName} and zw.zbgzstatus=#{zbgzstatus} and
        TIMESTAMPDIFF(DAY,zw.create_time,NOW()) <![CDATA[ <=  ]]>31  and IFNULL(car.jjbh,'') !=''
    </select>

    <!--同步手续管理车辆信息-->
    <insert id="syncSxglcarData">
		INSERT INTO nj_pmhtjkdb.md_sxglcar (`id`, `wtj`,
		`wtf_id`, `wtf_name`, `fgs_id`, `fgs_name`,
		`user_id`, `user_name`, `cjh`, `cllx`, `csh`, `pinpai`, `chexi`, `chexing`, `cph`, `sxglzt`,
		`carzname`, `carzdh`, `dsyname`, `dsydh`, `by1`, `by2`, `by3`, `create_time`, `update_time`)

		SELECT car.id, (SELECT bjd.bjdje FROM nj_mdb.md_carbjd bjd WHERE carid = car.id ORDER BY bjd.create_time DESC LIMIT 1) wtj,
		car.wtf_id, wtf.yjwtf wtf_name, car.fgs_id, fgs.fgs_mc fgs_name,
		car.user_id, u.trueName user_name, car.cjh, car.cllx, car.csh, car.pinpai, car.chexi, car.chexing, car.cph, 1 sxglzt,
		carzxx.carzname, carzxx.carzdh, carzxx.dsyname, carzxx.dsydh,car.by1, car.by2, car.by3, now() create_time, now() update_time
		FROM md_carinfo car
		INNER JOIN md_carzxx carzxx ON carzxx.carid = car.id
		LEFT JOIN md_zbwt zbwt ON car.id = zbwt.carid
		LEFT JOIN md_wtf_info wtf ON car.wtf_id = wtf.id
		LEFT JOIN md_fgs_info fgs ON car.fgs_id = fgs.id
		LEFT JOIN md_user u ON car.user_id = u.id
		LEFT JOIN nj_pmhtjkdb.md_sxglcar wtcar ON car.id = wtcar.id
		WHERE car.status = 5 AND zbwt.zbgzstatus = 5 AND car.id = #{carid} AND wtcar.id IS NULL
	</insert>
</mapper>
