<?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.xhpc.order.mapper.XhpcChargeOrderMapper">

    <resultMap type="com.xhpc.order.domain.XhpcChargeOrder" id="XhpcChargeOrderResult">
        <result column="charge_order_id" property="chargeOrderId"/>
        <result column="charging_station_id" property="chargingStationId"/>
        <result column="user_id" property="userId"/>
        <result column="terminal_id" property="terminalId"/>
        <result column="internet_serial_number" property="internetSerialNumber"/>
        <result column="serial_number" property="serialNumber"/>
        <result column="start_soc" property="startSoc"/>
        <result column="end_soc" property="endSoc"/>
        <result column="source" property="source"/>
        <result column="status" property="status"/>
        <result column="del_flag" property="delFlag"/>
        <result column="create_time" property="createTime"/>
        <result column="create_by" property="createBy"/>
        <result column="update_time" property="updateTime"/>
        <result column="update_by" property="updateBy"/>
        <result column="remark" property="remark"/>
        <result column="rate_model_id" property="rateModelId"/>
        <result column="charging_mode" property="chargingMode"/>
        <result column="start_time" property="startTime"/>
        <result column="end_time" property="endTime"/>
        <result column="charging_time" property="chargingTime"/>
        <result column="charging_degree" property="chargingDegree"/>
        <result column="type" property="type"/>
        <result column="erro_remark" property="erroRemark"/>
        <result column="amount_charged" property="amountCharged"/>
        <result column="power" property="power"/>
        <result column="charging_time_number" property="chargingTimeNumber"/>
        <result column="tenant_id" property="tenantId"/>
    </resultMap>

    <resultMap id="BaseResultMap" type="com.xhpc.common.domain.XhpcTerminal">
        <result property="terminalId" column="terminal_id"/>
        <result property="chargingPileId" column="charging_pile_id"/>
        <result property="chargingStationId" column="charging_station_id"/>
        <result property="name" column="name"/>
        <result property="serialNumber" column="serial_number"/>
        <result property="pileSerialNumber" column="pile_serial_number"/>
        <result property="gunStatus" column="gun_status"/>
        <result property="workStatus" column="work_status"/>
        <result property="status" column="status"/>
        <result property="delFlag" column="del_flag"/>
        <result property="createTime" column="create_time"/>
        <result property="createBy" column="create_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="remark" column="remark"/>
        <result property="rateModelId" column="rate_model_id"/>
    </resultMap>

    <resultMap id="XhpcRateMap" type="com.xhpc.common.domain.XhpcRate">
        <result property="rateId" column="rate_id"/>
        <result property="chargingStationId" column="charging_station_id"/>
        <result property="name" column="name"/>
        <result property="powerFee" column="power_fee"/>
        <result property="serviceFee" column="service_fee"/>
        <result property="status" column="status"/>
        <result property="delFlag" column="del_flag"/>
        <result property="createTime" column="create_time"/>
        <result property="createBy" column="create_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="remark" column="remark"/>
        <result property="rateValue" column="rate_value"/>
    </resultMap>


    <select id="getHistotyChargeOrderMessage" resultType="int">
         select
             count(charge_order_id)
         from xhpc_charge_order
         where user_id = #{userId}
           and status=0
           and del_flag =0
           <if test="tenantId !=null and tenantId !=''">
               and tenant_id=#{tenantId}
           </if>
           and source =#{userType}
    </select>

    <select id="getMessage" resultType="map">
        select
            rto.real_time_order_id as realTimeOrderId,
            rto.charging_order_id as chargingOrderId,
            rto.voltage as voltage,
            rto.charging_time_number as chargingTimeNumber,
            concat(rto.pile_number,"",rto.gun_number)as serialNumber,
            rto.electric_current as electricCurrent,
            (select power from xhpc_charging_pile where charging_pile_id=
            (select charging_pile_id from xhpc_charging_pile where serial_number=rto.pile_number and del_flag=0 LIMIT 1))power,
            rto.soc as soc,
            rto.gun_number as gunNumber,
            rto.charging_degree as chargingDegree,
            rto.charging_time as chargingTime,
            rto.remaining_time as remainingTime,
            rto.amount_charged as amountCharged,
            IFNULL(cp.type,'1') as type,
            au.balance as balance
        from xhpc_real_time_order as rto
        LEFT JOIN xhpc_app_user as au on au.app_user_id = rto.user_id
        left join xhpc_charging_pile as cp on cp.serial_number = rto.pile_number and cp.status =0 and cp.del_flag=0
        where rto.charging_order_id =(select charge_order_id from xhpc_charge_order where status =0 and source = 0 and user_id=#{userId} ORDER BY create_time desc LIMIT 1)
        and rto.user_id=#{userId}
		ORDER BY rto.create_time DESC limit 1
    </select>

    <select id="getUserMessage" resultType="map">
        select
            app_user_id as appUserId,
            balance as balance,
            phone as phone,
            soc as soc,
            is_refund as isRefund,
            weixin_open_id as weixinOpenId,
            alipay_open_id as alipayOpenId,
            is_refund_application as isRefundApplication
        from xhpc_app_user
        where del_flag=0 and app_user_id=#{userId}
    </select>

    <update id="updateUserBalance">
        update  xhpc_app_user set balance=#{balance} where app_user_id=#{userId}
    </update>
    <select id="countXhpcRealTimeOrder" resultType="String">
        select
               charge_order_id as chargeOrderId
        from xhpc_charge_order
        where
              user_id =#{userId} and status=0 and del_flag =0 and source =0
        <if test="source !=null and source !=''">
            and source=#{source}
        </if>
        <if test="tenantId !=null and tenantId !=''">
            and tenant_id=#{tenantId}
        </if>
              limit 1
    </select>

    <select id="countXhpcChargeOrder" resultType="int">
        select count(charge_order_id)
        from xhpc_charge_order
        where user_id =#{userId}
          and status =2 and source =0 and del_flag =0
        <if test="source !=null and source !=''">
            and source=#{source}
        </if>
        <if test="tenantId !=null and tenantId !=''">
            and tenant_id=#{tenantId}
        </if>
    </select>

    <select id="getXhpcTerminalSerialNumber" resultMap="BaseResultMap">
        select * from  xhpc_terminal where serial_number=#{serialNumber} and tenant_id=#{tenantId} and del_flag=0 limit 1
    </select>

    <insert id="addXhpcChargeOrder" parameterType="com.xhpc.order.domain.XhpcChargeOrder" useGeneratedKeys="true"
            keyProperty="chargeOrderId">
        insert into xhpc_charge_order
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="null != chargingStationId ">
                charging_station_id,
            </if>
            <if test="null != userId ">
                user_id,
            </if>
            <if test="null != terminalId ">
                terminal_id,
            </if>
            <if test="null != internetSerialNumber ">
                internet_serial_number,
            </if>
            <if test="null != serialNumber ">
                serial_number,
            </if>
            <if test="null != startSoc ">
                start_soc,
            </if>
            <if test="null != endSoc ">
                end_soc,
            </if>
            <if test="null != source ">
                source,
            </if>
            <if test="null != status ">
                status,
            </if>
            <if test="null != delFlag ">
                del_flag,
            </if>
            <if test="null != rateModelId ">
                rate_model_id,
            </if>
            <if test="null != chargingMode ">
                charging_mode,
            </if>
            <if test="null != startTime ">
                start_time,
            </if>
            <if test="null != endTime ">
                end_time,
            </if>
            <if test="null != chargingTime ">
                charging_time,
            </if>
            <if test="null != chargingDegree ">
                charging_degree,
            </if>
            <if test="null != type ">
                type,
            </if>
            <if test="null != erroRemark ">
                erro_remark,
            </if>
            <if test="null != amountCharged ">
                amount_charged,
            </if>
            <if test="null != power ">
                power,
            </if>
            <if test="null != createTime ">
                create_time,
            </if>
            <if test="null != tenantId and tenantId !=''">
                tenant_id,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="null != chargingStationId ">
                #{chargingStationId},
            </if>
            <if test="null != userId ">
                #{userId},
            </if>
            <if test="null != terminalId ">
                #{terminalId},
            </if>
            <if test="null != internetSerialNumber ">
                #{internetSerialNumber},
            </if>
            <if test="null != serialNumber ">
                #{serialNumber},
            </if>
            <if test="null != startSoc ">
                #{startSoc},
            </if>
            <if test="null != endSoc ">
                #{endSoc},
            </if>
            <if test="null != source ">
                #{source},
            </if>
            <if test="null != status ">
                #{status},
            </if>
            <if test="null != delFlag ">
                #{delFlag},
            </if>
            <if test="null != rateModelId ">
                #{rateModelId},
            </if>
            <if test="null != chargingMode ">
                #{chargingMode},
            </if>
            <if test="null != startTime ">
                #{startTime},
            </if>
            <if test="null != endTime ">
                #{endTime},
            </if>
            <if test="null != chargingTime ">
                #{chargingTime},
            </if>
            <if test="null != chargingDegree ">
                #{chargingDegree},
            </if>
            <if test="null != type ">
                #{type},
            </if>
            <if test="null != erroRemark ">
                #{erroRemark},
            </if>
            <if test="null != amountCharged ">
                #{amountCharged},
            </if>
            <if test="null != power ">
                #{power},
            </if>
            <if test="null != createTime ">
                #{createTime},
            </if>
            <if test="null != tenantId and tenantId !=''">
                #{tenantId},
            </if>
        </trim>
    </insert>

    <update id="updateXhpcChargeOrder" parameterType="com.xhpc.order.domain.XhpcChargeOrder">
        update xhpc_charge_order
        <set>
            <if test="startSoc != null">start_soc = #{startSoc},</if>
            <if test="endSoc != null">end_soc = #{endSoc},</if>
            <if test="status != null">status=#{status},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="startTime != null">start_time = #{startTime},</if>
            <if test="endTime != null">end_time = #{endTime},</if>
            <if test="chargingTime != null">charging_time = #{chargingTime},</if>
            <if test="chargingDegree != null">charging_degree = #{chargingDegree},</if>
            <if test="type != null">type = #{type},</if>
            <if test="power != null">power = #{power},</if>
            <if test="erroRemark != null">erro_remark = #{erroRemark},</if>
            <if test="amountCharged != null">amount_charged = #{amountCharged},</if>
            <if test="chargingTimeNumber != null">charging_time_number = #{chargingTimeNumber}</if>
        </set>
        where charge_order_id = #{chargeOrderId}
    </update>

    <select id="getHistotyChargeOrderStatusList" resultType="map">
        select
            cor.charge_order_id as chargeOrderId,
            cor.create_time as createTime,
            cor.serial_number as serialNumber,
            cs.name as chargingStationName,
            ter.name as terminalName,
            cor.charging_time as chargingTime,
            cor.charging_degree as chargingDegree,
            cor.status as status
        from xhpc_charge_order as cor
        left join xhpc_charging_station as cs on cs.charging_station_id = cor.charging_station_id
        left join xhpc_terminal as ter on ter.terminal_id=cor.terminal_id
        where cor.status =2
          and cor.del_flag =0
          and cor.user_id=#{userId}
        <if test="tenantId !=null and tenantId !=''">
            and cor.tenant_id =#{tenantId}
        </if>
        <if test="userType !=null">
            and cor.source =#{userType}
        </if>
        order by cor.update_time desc
    </select>

    <select id="getSerialNumberMessage" resultMap="XhpcChargeOrderResult">
        select *
        from xhpc_charge_order
        where serial_number = #{serialNumber}
        limit 1
    </select>

    <select id="getCount" resultType="int">
        select count(charge_order_id) from xhpc_charge_order where user_id=#{userId} and del_flag =0 and (status !=1 or status !=3)
        <if test="chargeOrderId !=null">
            and charge_order_id=#{chargeOrderId}
        </if>
        <if test="tenantId !=null and tenantId !=''">
            and tenant_id=#{tenantId}
        </if>
        <if test="source !=null">
            and source=#{source}
        </if>
    </select>

    <select id="getPromotion" resultType="map">
        select discount,state from xhpc_promotion where del_flag=0 and status=0 and type=0 and start_time &lt;= now() and  end_time &gt;=now() order by update_time desc
    </select>

    <select id="getChargingOrderId" resultMap="XhpcChargeOrderResult">
        select *
        from xhpc_charge_order
        where charge_order_id = #{chargingOrderId}
    </select>

    <select id="getOperatorMessage" resultType="map">
        select
         operator_id as operatorId,
         commission_type as commissionType,
         operator_id_evcs as operatorIdEvcs,
         soc as soc,
         platform_commission_rate as platformCommissionRate,
         maintenance_commission_rate as maintenanceCommissionRate
        from xhpc_operator where operator_id=(select operator_id from xhpc_charging_station where charging_station_id=#{chargingStationId})
    </select>

    <select id="getXhpcChargingPileById" resultType="map">
        select
        cp.charging_pile_id as chargingPileId,
        cs.operator_id as operatorId,
        cp.communication_protocol_version as communicationProtocolVersion,
        cp.power as power
        from xhpc_charging_pile as cp
        left join xhpc_charging_station as cs on cs.charging_station_id = cp.charging_station_id
        where cp.charging_pile_id=#{chargingPileid}
        <if test="tenantId !=null and tenantId !=''">
            and cp.tenant_id=#{tenantId}
        </if>
    </select>

    <insert id="addXhpcOrderRedisRecord" parameterType="com.xhpc.order.domain.XhpcOrderRedisRecord" useGeneratedKeys="true"
            keyProperty="orderRedisRecordId">
        insert into xhpc_order_redis_record
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="null != chargeOrderId ">
                charge_order_id,
            </if>
            <if test="null != historyOrderOd ">
                history_order_id,
            </if>
            <if test="null != endSoc ">
                end_soc,
            </if>
            <if test="null != status ">
                status,
            </if>
            <if test="null != balance ">
                balance,
            </if>
            <if test="null != tel ">
                tel,
            </if>
            <if test="null != startSoc ">
                start_soc,
            </if>
            <if test="null != startResult ">
                start_result,
            </if>
            <if test="null != realtimeDataList ">
                realtime_data_list,
            </if>
            <if test="null != remainingTime ">
                remaining_time,
            </if>
            <if test="null != rateModelId ">
                rate_model_id,
            </if>
            <if test="null != delFlag ">
                del_flag,
            </if>
            <if test="null != createTime ">
                create_time,
            </if>
            <if test="null != createBy ">
                create_by,
            </if>
            <if test="null != updateTime ">
                update_time,
            </if>
            <if test="null != updateBy ">
                update_by,
            </if>
            <if test="null != remark ">
                remark,
            </if>
            <if test="null != orderData ">
                order_data
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="null != chargeOrderId ">
                #{chargeOrderId},
            </if>
            <if test="null != historyOrderOd ">
                #{historyOrderOd},
            </if>
            <if test="null != endSoc ">
                #{endSoc},
            </if>
            <if test="null != status ">
                #{status},
            </if>
            <if test="null != balance ">
                #{balance},
            </if>
            <if test="null != tel ">
                #{tel},
            </if>
            <if test="null != startSoc ">
                #{startSoc},
            </if>
            <if test="null != startResult ">
                #{startResult},
            </if>
            <if test="null != realtimeDataList ">
                #{realtimeDataList},
            </if>
            <if test="null != remainingTime ">
                #{remainingTime},
            </if>
            <if test="null != rateModelId ">
                #{rateModelId},
            </if>
            <if test="null != delFlag ">
                #{delFlag},
            </if>
            <if test="null != createTime ">
                #{createTime},
            </if>
            <if test="null != createBy ">
                #{createBy},
            </if>
            <if test="null != updateTime ">
                #{updateTime},
            </if>
            <if test="null != updateBy ">
                #{updateBy},
            </if>
            <if test="null != remark ">
                #{remark},
            </if>
            <if test="null != orderData ">
                #{orderData}
            </if>
        </trim>
    </insert>

    <insert id="insertUserAccountStatement">
        INSERT INTO xhpc_user_account_statement(user_id, amount, remaining_sum, charge_order_id, type, create_time)
        values (#{userId}, #{amount}, #{remainingSum}, #{chargeOrderId}, #{type}, #{date})
    </insert>

    <select id="getRateModelId" resultMap="XhpcRateMap">
        select *
        from xhpc_rate
        where rate_model_id = #{rateModelId}
    </select>

    <select id="getXhpcChargingPile" resultType="map">
        select charging_pile_id  as chargingPileId,
               IFNULL(type, '1') as type
        from xhpc_charging_pile
        where charging_pile_id = (select charging_pile_id from xhpc_terminal where terminal_id = #{terminalId})
    </select>

    <select id="getXhpcChargeOrderStatus" resultMap="XhpcChargeOrderResult">
        select *
        from xhpc_charge_order
        where del_flag = 0
          and status = #{status}
          and source = #{source}
          and datediff(now(), create_time) > 1
    </select>


    <insert id="addBy3rd">
        insert into xhpc_charge_order
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="null != internetSerialNumber ">
                internet_serial_number,
            </if>
            <if test="null != serialNum ">
                serial_number,
            </if>
            <if test="null != driverId ">
                driver_id,
            </if>
            <if test="null != chargingAmt ">
                charging_amt,
            </if>
            <if test="null != plateNum ">
                plate_num,
            </if>
            <if test="null != status ">
                status,
            </if>
            <if test="null != createTime ">
                create_time,
            </if>
            <if test="null != rateModelId ">
                rate_model_id,
            </if>
            <if test="null != terminalId ">
                terminal_id,
            </if>
            <if test="null != power ">
                power,
            </if>
            <if test="null != chargingStationId ">
                charging_station_id,
            </if>
            <if test="null != chargingMode ">
                charging_mode,
            </if>
            <if test="null != source ">
                source,
            </if>
            <if test="null != userId ">
                user_id
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="null != internetSerialNumber ">
                #{internetSerialNumber},
            </if>
            <if test="null != serialNum ">
                #{serialNum},
            </if>
            <if test="null != driverId ">
                #{driverId},
            </if>
            <if test="null != chargingAmt ">
                #{chargingAmt},
            </if>
            <if test="null != plateNum ">
                #{plateNum},
            </if>
            <if test="null != status ">
                #{status},
            </if>
            <if test="null != createTime ">
                #{createTime},
            </if>
            <if test="null != rateModelId ">
                #{rateModelId},
            </if>
            <if test="null != terminalId ">
                #{terminalId},
            </if>
            <if test="null != power ">
                #{power},
            </if>
            <if test="null != chargingStationId ">
                #{chargingStationId},
            </if>
            <if test="null != chargingMode ">
                #{chargingMode},
            </if>
            <if test="null != source ">
                #{source},
            </if>
            <if test="null != userId ">
                #{userId}
            </if>
        </trim>
    </insert>

    <select id="selectDate3rdNeedBy" resultType="map">

        select xt.charging_station_id as chargingStationId,
               xt.terminal_id         as terminalId,
               cs.rate_model_id       as rateModelId,
               power
        from xhpc_terminal as xt
        left join xhpc_charging_pile as xcp on xcp.serial_number = substring(#{serialNumber}, 1, 14)
        left join xhpc_charging_station as cs on xt.charging_station_id = cs.charging_station_id
        where xt.serial_number = #{serialNumber} and xt.del_falg = 0
    </select>

    <select id="select3rdNameBy" resultType="map">

        select name, internet_user_id as userId
        from xhpc_internet_user
        where operator_id_evcs = #{operatorIdEvcs}
    </select>

    <update id="updateStatus">
      update xhpc_charge_order set status=#{status} where  status=0 and charge_order_id = #{chargeOrderId}
    </update>

    <select id="getFourTimsStatus" resultMap="XhpcChargeOrderResult">
        select * from xhpc_charge_order  where now() >DATE_ADD(create_time,interval 4 hour) and status=0
    </select>

    <select id="getSerialNumberLike" resultType="int">
        select count(charge_order_id) from xhpc_charge_order where serial_number like concat('%', #{serialNumber}, '%') and charge_order_id &gt; #{chargeOrderId} and status=1
    </select>
</mapper>
