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

    <resultMap type="com.xhpc.order.domain.XhpcRealTimeOrder" id="XhpcRealTimeOrderMap">
        <result column="real_time_order_id" property="realTimeOrderId"/>
        <result column="charging_order_id" property="chargingOrderId"/>
        <result column="transaction_number" property="transactionNumber"/>
        <result column="pile_number" property="pileNumber"/>
        <result column="gun_number" property="gunNumber"/>
        <result column="pile_gun_status" property="pileGunStatus"/>
        <result column="vehicle_gun_status" property="vehicleGunStatus"/>
        <result column="voltage" property="voltage"/>
        <result column="electric_current" property="electricCurrent"/>
        <result column="gun_line_temperature" property="gunLineTemperature"/>
        <result column="gun_line_number" property="gunLineNumber"/>
        <result column="soc" property="soc"/>
        <result column="max_temperature" property="maxTemperature"/>
        <result column="charging_time" property="chargingTime"/>
        <result column="charging_time_number" property="chargingTimeNumber"/>
        <result column="remaining_time" property="remainingTime"/>
        <result column="charging_degree" property="chargingDegree"/>
        <result column="loss_charging_degree" property="lossChargingDegree"/>
        <result column="amount_charged" property="amountCharged"/>
        <result column="hardware_fault" property="hardwareFault"/>
        <result column="status" property="status"/>
        <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="user_id" property="userId"/>
        <result column="charging_station_id" property="chargingStationId"/>
    </resultMap>

    <insert id="insertXhpcRealTimeOrder" parameterType="com.xhpc.order.domain.XhpcRealTimeOrder" useGeneratedKeys="true"
            keyProperty="realTimeOrderId">
        insert into xhpc_real_time_order
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="null != chargingOrderId ">
                charging_order_id,
            </if>
            <if test="null != transactionNumber ">
                transaction_number,
            </if>
            <if test="null != pileNumber ">
                pile_number,
            </if>
            <if test="null != gunNumber ">
                gun_number,
            </if>
            <if test="null != pileGunStatus ">
                pile_gun_status,
            </if>
            <if test="null != vehicleGunStatus ">
                vehicle_gun_status,
            </if>
            <if test="null != voltage ">
                voltage,
            </if>
            <if test="null != electricCurrent ">
                electric_current,
            </if>
            <if test="null != gunLineTemperature ">
                gun_line_temperature,
            </if>
            <if test="null != gunLineNumber ">
                gun_line_number,
            </if>
            <if test="null != soc ">
                soc,
            </if>
            <if test="null != maxTemperature ">
                max_temperature,
            </if>
            <if test="null != chargingTime ">
                charging_time,
            </if>
            <if test="null != remainingTime ">
                remaining_time,
            </if>
            <if test="null != chargingDegree ">
                charging_degree,
            </if>
            <if test="null != lossChargingDegree ">
                loss_charging_degree,
            </if>
            <if test="null != amountCharged ">
                amount_charged,
            </if>
            <if test="null != hardwareFault ">
                hardware_fault,
            </if>
            <if test="null != status ">
                status,
            </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 != userId ">
                user_id,
            </if>
            <if test="null != chargingStationId ">
                charging_station_id,
            </if>
            <if test="null != chargingTimeNumber ">
                charging_time_number,
            </if>
            <if test="null != source ">
                source
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="null != chargingOrderId ">
                #{chargingOrderId},
            </if>
            <if test="null != transactionNumber ">
                #{transactionNumber},
            </if>
            <if test="null != pileNumber ">
                #{pileNumber},
            </if>
            <if test="null != gunNumber ">
                #{gunNumber},
            </if>
            <if test="null != pileGunStatus ">
                #{pileGunStatus},
            </if>
            <if test="null != vehicleGunStatus ">
                #{vehicleGunStatus},
            </if>
            <if test="null != voltage ">
                #{voltage},
            </if>
            <if test="null != electricCurrent ">
                #{electricCurrent},
            </if>
            <if test="null != gunLineTemperature ">
                #{gunLineTemperature},
            </if>
            <if test="null != gunLineNumber ">
                #{gunLineNumber},
            </if>
            <if test="null != soc ">
                #{soc},
            </if>
            <if test="null != maxTemperature ">
                #{maxTemperature},
            </if>
            <if test="null != chargingTime ">
                #{chargingTime},
            </if>
            <if test="null != remainingTime ">
                #{remainingTime},
            </if>
            <if test="null != chargingDegree ">
                #{chargingDegree},
            </if>
            <if test="null != lossChargingDegree ">
                #{lossChargingDegree},
            </if>
            <if test="null != amountCharged ">
                #{amountCharged},
            </if>
            <if test="null != hardwareFault ">
                #{hardwareFault},
            </if>
            <if test="null != status ">
                #{status},
            </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 != userId ">
                #{userId},
            </if>
            <if test="null != chargingStationId ">
                #{chargingStationId},
            </if>
            <if test="null != chargingTimeNumber ">
                #{chargingTimeNumber},
            </if>
            <if test="null != source ">
                #{source}
            </if>
        </trim>
    </insert>

    <insert id="insertSOC" parameterType="com.xhpc.order.domain.XhpcChargeOrderSoc" useGeneratedKeys="true"
            keyProperty="chargeOrderSocId">
        insert into xhpc_charge_order_soc
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="null != chargeOrderId ">
                charge_order_id,
            </if>
            <if test="null != soc ">
                soc,
            </if>
            <if test="null != status ">
                status,
            </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>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="null != chargeOrderId ">
                #{chargeOrderId},
            </if>
            <if test="null != soc ">
                #{soc},
            </if>
            <if test="null != status ">
                #{status},
            </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>
        </trim>
    </insert>

    <insert id="insertCurrent" parameterType="com.xhpc.order.domain.XhpcChargeOrderCurrent" useGeneratedKeys="true"
            keyProperty="chargeOrderCurrentId">
        insert into xhpc_charge_order_current
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="null != chargeOrderId ">
                charge_order_id,
            </if>
            <if test="null != current ">
                current,
            </if>
            <if test="null != status ">
                status,
            </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>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="null != chargeOrderId ">
                #{chargeOrderId},
            </if>
            <if test="null != current ">
                #{current},
            </if>
            <if test="null != status ">
                #{status},
            </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>
        </trim>
    </insert>

    <insert id="insertVoltage" parameterType="com.xhpc.order.domain.XhpcChargeOrderVoltage" useGeneratedKeys="true"
            keyProperty="chargeOrderVoltageId">
        insert into xhpc_charge_order_voltage
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="null != chargeOrderId ">
                charge_order_id,
            </if>
            <if test="null != voltage ">
                voltage,
            </if>
            <if test="null != status ">
                status,
            </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>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="null != chargeOrderId ">
                #{chargeOrderId},
            </if>
            <if test="null != voltage ">
                #{voltage},
            </if>
            <if test="null != status ">
                #{status},
            </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>
        </trim>
    </insert>

    <select id="list" resultType="map">
        select
        co.charge_order_id as chargeOrderId,
        co.serial_number as serialNumber,
        ct.name as chargingStationName,
        ter.pile_serial_number as pileSerialNumber,
        ter.name as terminalName,
        op.operator_id as operatorId,
        op.name as operatorName,
        cp.power as power,
        cp.type as type,
        co.status as status,
        co.erro_remark as erroRemark,
        co.create_time as createTime,
        co.start_time as startTime,
        ro.real_time_order_id as realTimeOrderId,
        ro.soc as soc,
        ro.charging_degree as chargingDegree,
        ro.charging_time as chargingTime,
        co.source as source,
        co.internet_serial_number as internetSerialNumber,
        co.Plate_num as plateNum,
        inu.name as internetName,
        inu.phone as internetUserPhone,
        cup.name as  customersName,
        cup.account as  customersAccount,
        cop.name as  communityName,
        cop.account as  communityAccount,
        apu.phone as appUserPhone
        from xhpc_charge_order co
        left join xhpc_charging_station as ct on ct.charging_station_id = co.charging_station_id
        left join xhpc_operator as op on op.operator_id = ct.operator_id
        left join xhpc_terminal as ter on ter.terminal_id = co.terminal_id
        left join xhpc_charging_pile as cp on cp.charging_pile_id = ter.charging_pile_id
        left join xhpc_internet_user as inu on inu.internet_user_id = co.user_id and co.source =1
        left join xhpc_app_user as apu on apu.app_user_id = co.user_id and co.source =0
        left join xhpc_community_personnel as cop on cop.community_personnel_id = co.user_id and co.source =2
        left join xhpc_customers_personnel as cup on cup.customers_personnel_id = co.user_id and co.source =3
        LEFT JOIN (
        SELECT charging_order_id,real_time_order_id,soc,charging_degree,charging_time FROM xhpc_real_time_order
        WHERE real_time_order_id IN ( SELECT max( real_time_order_id ) FROM xhpc_real_time_order GROUP BY charging_order_id )
        ) ro ON ro.charging_order_id = co.charge_order_id
        <if test="number==1">
            inner join (
            select xco.charge_order_id as charge_order_id from xhpc_charge_order as xco INNER JOIN xhpc_app_user as xau on xau.app_user_id = xco.user_id where xco.source=0 and xau.phone LIKE concat('%',#{phone}, '%')
            union
            select xco.charge_order_id as charge_order_id from xhpc_charge_order as xco INNER JOIN xhpc_internet_user as xiu on xiu.internet_user_id = xco.user_id where xco.source=1 and xiu.phone LIKE concat('%',#{phone}, '%')
            union
            select xco.charge_order_id as charge_order_id from xhpc_charge_order as xco INNER JOIN xhpc_community_personnel as xcp on xcp.community_personnel_id = xco.user_id where xco.source=2 and xcp.account LIKE concat('%',#{phone}, '%')
            union
            select xco.charge_order_id as charge_order_id from xhpc_charge_order as xco INNER JOIN xhpc_customers_personnel as xcup on xcup.customers_personnel_id = xco.user_id where xco.source=3 and xcup.account LIKE concat('%',#{phone}, '%')
            ) ut on ut.charge_order_id = co.charge_order_id
        </if>
        where co.status=#{status} and co.del_flag=0
        <if test="internetId !=null">
            and co.source =1
            and inu.internet_user_id=#{internetId}
        </if>
        <if test="chargingStationId!=null">
            and co.charging_station_id=#{chargingStationId}
        </if>
        <if test="chargingPileId!=null">
            and co.terminal_id in (select terminal_id from xhpc_terminal where charging_pile_id =#{chargingPileId} and del_flag =0)
        </if>
        <if test="terminalId!=null">
            and co.terminal_id=#{terminalId}
        </if>
        <if test="internetSerialNumber !=null and internetSerialNumber !=''">
            and co.source =1
            and inu.name like concat('%', #{internetSerialNumber}, '%')
        </if>
        <if test="plateNum !=null and plateNum !=''">
            and co.Plate_num like concat('%', #{plateNum}, '%')
        </if>
        <if test="tenantId !=null and tenantId !=''">
            and co.tenant_id =#{tenantId}
        </if>
        <if test="source !=null">
            and co.source =#{source}
        </if>
        <if test="startTime !=null and startTime !=''">
            and co.start_time &gt;= #{startTime}
        </if>
        <if test="endTime !=null and endTime !=''">
            and co.start_time &lt;= #{endTime}
        </if>
        <if test="transactionNumber !=null and transactionNumber !=''">
            and co.serial_number like concat('%', #{transactionNumber}, '%')
        </if>
        <if test="chargingStationName !=null and chargingStationName !=''">
            and ct.name like concat('%', #{chargingStationName}, '%')
        </if>
        <if test="terminalName !=null and terminalName !=''">
            and ter.name like concat('%', #{terminalName}, '%')
        </if>
        <if test="operatorId !=null">
            and op.operator_id =#{operatorId}
        </if>
        <if test="number==2">
            <if test="source !=null and source ==0">
                and co.charge_order_id in (select xco.charge_order_id as charge_order_id from xhpc_charge_order as xco INNER JOIN xhpc_app_user as xau on xau.app_user_id = xco.user_id where xco.source=0 and xau.phone LIKE concat('%',#{phone}, '%'))
            </if>
            <if test="source !=null and source ==1">
                and co.charge_order_id in (select xco.charge_order_id as charge_order_id from xhpc_charge_order as xco INNER JOIN xhpc_internet_user as xiu on xiu.internet_user_id = xco.user_id where xco.source=1 and xiu.phone LIKE concat('%',#{phone}, '%'))
            </if>
        </if>
        <if test="number==3">
            <if test="source !=null and source ==0">
                and co.charge_order_id in (select xco.charge_order_id as charge_order_id from xhpc_charge_order as xco INNER JOIN xhpc_app_user as xau on xau.app_user_id = xco.user_id where xco.source=0 and xau.phone LIKE concat('%',#{phone}, '%'))
            </if>
            <if test="source !=null and source ==1">
                a and co.charge_order_id in (select xco.charge_order_id as charge_order_id from xhpc_charge_order as xco INNER JOIN xhpc_internet_user as xiu on xiu.internet_user_id = xco.user_id where xco.source=1 and xiu.phone LIKE concat('%',#{phone}, '%'))
            </if>
        </if>
        <if test="type==1">
            and co.charging_station_id in (select charging_station_id from xhpc_charging_station where operator_id=#{userId})
        </if>
        <if test="type==2">
            and co.charging_station_id in (select charging_station_id from xhpc_user_privilege where user_id=#{userId})
        </if>
        order by co.create_time desc
    </select>

    <select id="getMessage" resultType="map">
        select
            ro.real_time_order_id as realTimeOrderId,
            co.charge_order_id as chargeOrderId,
            co.serial_number as serialNumber,
            ct.name as chargingStationName,
            ter.pile_serial_number as pileSerialNumber,
            ter.name as terminalName,
            op.operator_id as operatorId,
            op.name as operatorName,
            cp.serial_number as pileSerialNumber,
            cp.power as power,
            cp.type as type,
            ro.soc as soc,
            ro.voltage as voltage,
            ro.electric_current as electricCurrent,
            co.status as status,
            co.create_time as createTime,
            ro.charging_degree as chargingDegree,
            ro.charging_time as chargingTime,
            co.source as source,
            co.internet_serial_number as internetSerialNumber,
            co.erro_remark as erroRemark,
            ro.amount_charged as amountCharged,
            co.Plate_num as plateNum,
            (SELECT phone FROM xhpc_app_user where app_user_id = co.user_id) as appUserPhone,
            (SELECT phone from xhpc_internet_user where internet_user_id = co.user_id) as internetUserPhone,
            (SELECT name from xhpc_internet_user where internet_user_id = co.user_id and co.source=1) as internetName,
            (SELECT account from xhpc_community_personnel where community_personnel_id = co.user_id and co.source=2) as communityAccount,
            (SELECT account from xhpc_customers_personnel where customers_personnel_id = co.user_id and co.source=3) as customersAccount
        from xhpc_real_time_order ro
        Left join xhpc_charge_order co on co.serial_number = ro.transaction_number
        Left join xhpc_charging_station as ct on ct.charging_station_id = ro.charging_station_id
        left join xhpc_operator as op on op.operator_id = ct.operator_id
        left join xhpc_terminal as ter on ter.terminal_id = co.terminal_id
        left join xhpc_charging_pile as cp on cp.charging_pile_id = ter.charging_pile_id
        where ro.real_time_order_id=#{realTimeOrderId}
    </select>

    <select id="getMessageChargeOrderId" resultType="map">
        select
            co.charge_order_id as chargeOrderId,
            co.serial_number as serialNumber,
            ct.name as chargingStationName,
            ter.pile_serial_number as pileSerialNumber,
            ter.name as terminalName,
            op.operator_id as operatorId,
            op.name as operatorName,
            cp.serial_number as pileSerialNumber,
            cp.power as power,
            cp.type as type,
            concat(0) as soc,
            concat(0) as voltage,
            concat(0) as electricCurrent,
            co.status as status,
            co.create_time as createTime,
            concat(0) as chargingDegree,
            concat(0) as chargingTime,
            co.source as source,
            co.internet_serial_number as internetSerialNumber,
            co.erro_remark as erroRemark,
            concat(0) as amountCharged,
            co.Plate_num as plateNum,
            (SELECT phone FROM xhpc_app_user where app_user_id = co.user_id) as appUserPhone,
            (SELECT phone from xhpc_internet_user where internet_user_id = co.user_id) as internetUserPhone,
            (SELECT name from xhpc_internet_user where internet_user_id = co.user_id and co.source=1) as internetName,
            (SELECT account from xhpc_community_personnel where community_personnel_id = co.user_id and co.source=2) as communityAccount,
            (SELECT account from xhpc_customers_personnel where customers_personnel_id = co.user_id and co.source=3) as customersAccount
         from xhpc_charge_order co
                 Left join xhpc_charging_station as ct on ct.charging_station_id = co.charging_station_id
                 left join xhpc_operator as op on op.operator_id = ct.operator_id
                 left join xhpc_terminal as ter on ter.terminal_id = co.terminal_id
                 left join xhpc_charging_pile as cp on cp.charging_pile_id = ter.charging_pile_id
        where co.charge_order_id=#{chargeOrderId}
    </select>




    <select id="timeList" resultType="map">
         select
             create_time as createTime,
             voltage,
             electric_current as electricCurrent,
             soc,
             charging_degree as chargingDegree,
             loss_charging_degree as lossChargingDegree,
             gun_line_temperature as gunLineTemperature,
             charging_time as chargingTime,
             remaining_time as remainingTime,
             vehicle_gun_status as vehicleGunStatus,
             status
         from xhpc_real_time_order
        where charging_order_id=#{chargingOrderId}
        order by create_time desc
    </select>

    <select id="timeChartSOCList" resultType="map">
        select
            group_concat(soc) as soc,
            group_concat(DATE_FORMAT(create_time,'%H:%i:%s')) createTime
        from xhpc_charge_order_soc
        where charge_order_id=#{chargingOrderId}
    </select>

    <select id="timeChartVoltageList" resultType="map">
        select
            group_concat(voltage) as voltage,
            group_concat(DATE_FORMAT(create_time,'%H:%i:%s')) createTime
        from xhpc_charge_order_voltage
        where charge_order_id=#{chargingOrderId}
    </select>

    <select id="timeChartCurrentList" resultType="map">
        select
            group_concat(current) as current,
            group_concat(DATE_FORMAT(create_time,'%H:%i:%s')) createTime
        from xhpc_charge_order_current
        where charge_order_id=#{chargingOrderId}
    </select>

    <delete id="deleteRealTimeOrder">
        delete from xhpc_real_time_order where charging_order_id=#{chargingOrderId}
    </delete>

    <delete id="deleteChargeVoltage">
        delete from xhpc_charge_order_current where charge_order_id=#{chargingOrderId}
    </delete>

    <delete id="deleteChargeOrderSoc">
        delete from xhpc_charge_order_soc where charge_order_id=#{chargingOrderId}
    </delete>

    <delete id="deleteChargeOrderCurrent">
        delete from xhpc_charge_order_voltage where charge_order_id=#{chargingOrderId}
    </delete>

    <select id="getChargingOrderId" resultMap="XhpcRealTimeOrderMap">
        select
               *
        from xhpc_real_time_order
        where charging_order_id=#{chargingOrderId}
        and soc>0 and charging_degree>0
        <if test="type ==1">
             order by create_time asc
         </if>
         <if test="type==2">
             order by create_time desc
         </if>
          limit 1
    </select>

    <select id="getRealTimeOrderTime" resultType="double">
       select IFNULL((
           (SELECT
        max(charging_degree)
        FROM
        xhpc_real_time_order
        WHERE
        charging_order_id =#{chargingOrderId}
        and create_time &lt;=#{endTime} )-(SELECT
        min(charging_degree)
        FROM
        xhpc_real_time_order
        WHERE
        charging_order_id =#{chargingOrderId}
        and create_time &gt;=#{startTime})),0) number from xhpc_real_time_order WHERE
        charging_order_id =#{chargingOrderId}
        limit 1
    </select>

    <select id="getDifferenceTime" resultType="int">
        SELECT IFNULL(TIMESTAMPDIFF(SECOND,
        (SELECT
        max(create_time)
        FROM
        xhpc_real_time_order
        WHERE
        charging_order_id =#{chargingOrderId}
        and create_time &lt;=#{time} ),
        #{time}
        ),0) time from xhpc_real_time_order WHERE charging_order_id =#{chargingOrderId} limit 1
    </select>

    <select id="getChargingDegreeTime" resultType="double">
        SELECT IFNULL((SELECT
        min(charging_degree)
        FROM
        xhpc_real_time_order
        WHERE
        charging_order_id =#{chargingOrderId}
        and create_time &gt;=#{time} )-
        (SELECT
        max(charging_degree)
        FROM
        xhpc_real_time_order
        WHERE
        charging_order_id =#{chargingOrderId}
        and create_time &lt;=#{time} ),0)time from xhpc_real_time_order WHERE charging_order_id =#{chargingOrderId} limit 1
    </select>

    <select id="getChargeOrderById" resultType="map">
        select
            co.serial_number as serialNumber,
            ct.name as chargingStationName,
            ter.pile_serial_number as pileSerialNumber,
            ter.name as terminalName,
            op.operator_id as operatorId,
            op.name as operatorName,
            cp.serial_number as pileSerialNumber,
            cp.power as power,
            cp.type as type,
            ro.soc as soc,
            co.status as status,
            co.create_time as createTime,
            ro.charging_degree as chargingDegree,
            ro.charging_time as chargingTime,
            co.source as source,
            co.internet_serial_number as internetSerialNumber,
            co.erro_remark as erroRemark,
            ro.amount_charged as amountCharged,
            (SELECT phone FROM xhpc_app_user where app_user_id = co.user_id) as appUserPhone,
            (SELECT phone from xhpc_internet_user where internet_user_id = co.user_id) as internetUserPhone
        from xhpc_charge_order as co
        Left join xhpc_real_time_order ro on co.serial_number = ro.transaction_number
        Left join xhpc_charging_station as ct on ct.charging_station_id = ro.charging_station_id
        left join xhpc_operator as op on op.operator_id = ct.operator_id
        left join xhpc_terminal as ter on ter.terminal_id = co.terminal_id
        left join xhpc_charging_pile as cp on cp.charging_pile_id = ter.charging_pile_id
        where ro.real_time_order_id=#{realTimeOrderId}
    </select>

    <select id="getOperatorIdEvcs" resultType="map">
        select
         internet_user_id as internetUserId,
         operator_id_evcs as operatorIdEvcs,
         commission_type as commissionType,
         commission_rate as commissionRate
        from xhpc_internet_user
        where del_flag =0 and operator_id_evcs=#{operatorIdEvcs} limit 1
    </select>

    <select id="getActivityFormulaTime" resultType="com.xhpc.order.dto.XhpcActivityFormulaDomainDto">
        select
           activity_formula_id as activityFormulaId,
           activity_id as activityId,
           start_time as startTime,
           end_time as endTime,
           service_price as servicePrice,
           power_price as powerPrice,
           compute_formula as computeFormula
        from xhpc_activity_formula
        where
        activity_id = #{activityId}
        and activity_formula_id in (SELECT activity_formula_id FROM xhpc_activity_formula WHERE  start_time &lt;= #{startTime} AND end_time &gt;= #{startTime} and activity_id = #{activityId})
        or activity_formula_id in (SELECT activity_formula_id FROM xhpc_activity_formula WHERE  start_time &lt;= #{endTime} AND end_time &gt;= #{endTime} and activity_id = #{activityId})
        order by activity_formula_id
    </select>

    <select id="getActivityStationTime" resultType="com.xhpc.order.dto.XhpcActivityInternetDomainDto" >
        select
            activity_id as activityId,
            activity_name as activityName,
            internet_name as internetName,
            compute_type as computeType,
            service_price as servicePrice,
            compute_formula as computeFormula,
            start_time as startTime,
            end_time as endTime,
            station_list as stationList,
            tenant_id as tenantId,
            `status`,
            del_flag as delFlag
        from xhpc_activity_internet
        where tenant_id =#{tenantId}
        and find_in_set(#{chargingStationId}, station_list)
        and internet_name = (select name from xhpc_internet_user where internet_user_id =#{internetUserId} and del_flag =0)
        and status =3 and del_flag=0
        and start_time &lt;= #{startTime} AND end_time &gt;= #{startTime}
        order by  end_time
    </select>

    <select id="getActivityDiscountTime" resultType="com.xhpc.order.dto.XhpcActivityDiscountDto">
        select
            xad.activity_id as activityId,
            xad.activity_name as activityName,
            xad.template_id as templateId,
            xad.app_user_list as appUserList,
            xad.com_user_list as comUserList,
            xad.cus_user_list as cusUserList,
            xad.station_list as stationList,
            xad.tenant_id as tenantId,
            xad.start_time as startTime,
            xad.end_time as endTime,
            xad.status as status,
            xad.del_flag as delFlag,
            xad.create_by as createBy,
            xad.create_time as createTime,
            xad.update_by as updateBy,
            xad.update_time as updateTime,
            xad.check_by as checkBy,
            xad.check_time as checkTime,
            xat.discount_type discountType,
            xat.discount_rate discountRate
        from xhpc_activity_discount as xad
        join xhpc_activity_template as xat on xat.activity_template_id = xad.template_id and xat.del_flag =0 and xat.status=1
        where xad.tenant_id=#{tenantId} and xad.status=3 and xad.del_flag =0
          and find_in_set(#{chargingStationId}, xad.station_list)
        <if test="userType ==0">
            and (find_in_set(#{internetUserId}, xad.app_user_list) or xad.app_user_list='-1')
        </if>
        <if test="userType ==2">
            and (find_in_set(#{internetUserId}, xad.com_user_list) or xad.com_user_list='-1')
        </if>
        <if test="userType ==3">
            and (find_in_set(#{internetUserId}, xad.cus_user_list) or xad.cus_user_list='-1')
        </if>
        and xad.start_time &lt;=#{startTime}   and xad.end_time &gt; #{startTime}
        order by xad.start_time asc
    </select>


    <select id="getActivityFormulaById" resultType="com.xhpc.order.dto.XhpcActivityFormulaDomainDto">
        select
            activity_formula_id as activityFormulaId,
            activity_id as activityId,
            start_time as startTime,
            end_time as endTime,
            service_price as servicePrice,
            power_price as powerPrice,
            compute_formula as computeFormula
        from xhpc_activity_formula
        where activity_id = #{activityId}
    </select>

    <select id="getRateTimeList" resultType="com.xhpc.order.dto.RateTime">
        select
            start_time as startTime,
            replace(end_time, '00:00:00', '24:00:00') as endTime,
            power_fee as powerPrice,
            service_fee as servicePrice
        from  xhpc_rate_time as xrt
        left join xhpc_rate as xr on xrt.rate_id = xr.rate_id
        where xrt.charging_station_id=#{chargingStationId} and xrt.rate_model_id=#{rateModelId} and xrt.del_flag=0
        <if test="rateValue !=null and rateValue !=''">
            and xrt.rate_value =#{rateValue}
        </if>
    </select>

    <select id="getActivityFormulaTimeById" resultType="com.xhpc.order.dto.XhpcActivityFormulaDomainDto">
        select
            activity_formula_id as activityFormulaId,
            activity_id as activityId,
            start_time as startTime,
            end_time as endTime,
            service_price as servicePrice,
            power_price as powerPrice,
            compute_formula as computeFormula
        from xhpc_activity_formula
        where activity_id = #{activityId}
            and start_time &gt;= #{startTime} AND end_time &lt;= #{endTime}
    </select>

</mapper>