<?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.activity.mapper.XhpcClearingHistoryOrderMapper">
    <resultMap id="BaseResultMap" type="com.xhpc.activity.domain.XhpcClearingHistoryOrderDomain">
        <id column="clearing_order_id" jdbcType="BIGINT" property="clearingOrderId"/>
        <result column="charging_station_id" jdbcType="BIGINT" property="chargingStationId"/>
        <result column="charge_order_id" jdbcType="BIGINT" property="chargeOrderId"/>
        <result column="user_id" jdbcType="BIGINT" property="userId"/>
        <result column="terminal_id" jdbcType="BIGINT" property="terminalId"/>
        <result column="serial_number" jdbcType="VARCHAR" property="serialNumber"/>
        <result column="internet_serial_number" jdbcType="VARCHAR" property="internetSerialNumber"/>
        <result column="power_price_total" jdbcType="DECIMAL" property="powerPriceTotal"/>
        <result column="service_price_total" jdbcType="DECIMAL" property="servicePriceTotal"/>
        <result column="total_price" jdbcType="DECIMAL" property="totalPrice"/>
        <result column="activity_power_price_total" jdbcType="DECIMAL" property="activityPowerPriceTotal"/>
        <result column="activity_service_price_total" jdbcType="DECIMAL" property="activityServicePriceTotal"/>
        <result column="activity_total_price" jdbcType="DECIMAL" property="activityTotalPrice"/>
        <result column="promotion_discount" jdbcType="DECIMAL" property="promotionDiscount"/>
        <result column="act_price" jdbcType="DECIMAL" property="actPrice"/>
        <result column="act_power_price" jdbcType="DECIMAL" property="actPowerPrice"/>
        <result column="act_service_price" jdbcType="DECIMAL" property="actServicePrice"/>
        <result column="internet_commission" jdbcType="DECIMAL" property="internetCommission"/>
        <result column="internet_svc_commission" jdbcType="DECIMAL" property="internetSvcCommission"/>
        <result column="platform_commission" jdbcType="DECIMAL" property="platformCommission"/>
        <result column="platform_svc_commisssion" jdbcType="DECIMAL" property="platformSvcCommisssion"/>
        <result column="operation_commission" jdbcType="DECIMAL" property="operationCommission"/>
        <result column="operation_svc_commission" jdbcType="DECIMAL" property="operationSvcCommission"/>
        <result column="start_soc" jdbcType="VARCHAR" property="startSoc"/>
        <result column="end_soc" jdbcType="VARCHAR" property="endSoc"/>
        <result column="reconciliation_status" jdbcType="INTEGER" property="reconciliationStatus"/>
        <result column="sorting_status" jdbcType="INTEGER" property="sortingStatus"/>
        <result column="type" jdbcType="TINYINT" property="type"/>
        <result column="status" jdbcType="INTEGER" property="status"/>
        <result column="del_flag" jdbcType="INTEGER" property="delFlag"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="create_by" jdbcType="VARCHAR" property="createBy"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
        <result column="update_by" jdbcType="VARCHAR" property="updateBy"/>
        <result column="remark" jdbcType="VARCHAR" property="remark"/>
        <result column="state" jdbcType="INTEGER" property="state"/>
        <result column="vin_normal" jdbcType="VARCHAR" property="vinNormal"/>
        <result column="search_value" jdbcType="VARCHAR" property="searchValue"/>
        <result column="operator_id_evcs" jdbcType="VARCHAR" property="operatorIdEvcs"/>
        <result column="charge_model_evcs" jdbcType="INTEGER" property="chargeModelEvcs"/>
        <result column="connector_power_evcs" jdbcType="DOUBLE" property="connectorPowerEvcs"/>
        <result column="meter_value_end_evcs" jdbcType="DOUBLE" property="meterValueEndEvcs"/>
        <result column="meter_value_start_evcs" jdbcType="DOUBLE" property="meterValueStartEvcs"/>
        <result column="operator_id3rdpty_evcs" jdbcType="VARCHAR" property="operatorId3rdptyEvcs"/>
        <result column="start_time" jdbcType="TIMESTAMP" property="startTime"/>
        <result column="end_time" jdbcType="TIMESTAMP" property="endTime"/>
        <result column="stop_reason_evcs" jdbcType="VARCHAR" property="stopReasonEvcs"/>
        <result column="total_power" jdbcType="DOUBLE" property="totalPower"/>
        <result column="user_name_evcs" jdbcType="VARCHAR" property="userNameEvcs"/>
        <result column="phone" jdbcType="VARCHAR" property="phone"/>
        <result column="evcs_order_no" jdbcType="VARCHAR" property="evcsOrderNo"/>
        <result column="confirm_Result" jdbcType="INTEGER" property="confirmResult"/>
        <result column="rate_model_id" jdbcType="BIGINT" property="rateModelId"/>
        <result column="charging_mode" jdbcType="VARCHAR" property="chargingMode"/>
        <result column="internet_degree_commission" jdbcType="DECIMAL" property="internetDegreeCommission"/>
        <result column="source" jdbcType="INTEGER" property="source"/>
        <result column="tenant_id" jdbcType="VARCHAR" property="tenantId"/>
        <result column="operator_id" jdbcType="BIGINT" property="operatorId"/>
        <result column="operator_name" jdbcType="VARCHAR" property="operatorName"/>
        <result column="clearing_checkout_id" jdbcType="BIGINT" property="clearingCheckoutId"/>
        <result column="check_status" jdbcType="INTEGER" property="checkStatus"/>
        <result column="check_time" jdbcType="TIMESTAMP" property="checkTime"/>
        <result column="check_by" jdbcType="VARCHAR" property="checkBy"/>
    </resultMap>
    <sql id="Base_Column_List">
        ho.clearing_order_id, ho.charging_station_id, ho.charge_order_id, ho.user_id, ho.terminal_id, ho.serial_number,
        ho.internet_serial_number,
        ifnull(ho.power_price_total, 0) as 'power_price_total',
        ifnull(ho.service_price_total, 0) as 'service_price_total',
        ifnull(ho.total_price, 0) as 'total_price',
        ifnull(ho.promotion_discount, 0) as 'promotion_discount',
        ifnull(ho.act_price, 0) as 'act_price',
        ifnull(ho.act_power_price, 0) as 'act_power_price',
        ifnull(ho.act_service_price, 0) as 'act_service_price',
        ifnull(ho.internet_commission, 0) as 'internet_commission',
        ifnull( ho.internet_svc_commission, 0) as 'internet_svc_commission',
        ifnull(ho.platform_commission, 0) as 'platform_commission',
        ifnull(ho.platform_svc_commisssion, 0) as 'platform_svc_commisssion',
        ifnull(ho.operation_commission, 0) as 'operation_commission',
        ifnull(ho.operation_svc_commission, 0) as 'operation_svc_commission',
        ho.start_soc, ho.end_soc, ho.reconciliation_status, ho.sorting_status, ho.`type`, ho.`status`, ho.del_flag,
        ho.create_time, ho.create_by, ho.update_time, ho.update_by, ho.remark, ho.`state`, ho.vin_normal, ho.search_value,
        ho.operator_id_evcs, ho.charge_model_evcs, ho.connector_power_evcs, ho.meter_value_end_evcs,
        ho.meter_value_start_evcs, ho.operator_id3rdpty_evcs, ho.start_time, ho.end_time, ho.stop_reason_evcs,
        ho.total_power, ho.user_name_evcs, ho.phone, ho.evcs_order_no, ho.confirm_Result, ho.rate_model_id,
        ho.charging_mode,
        ifnull(ho.internet_degree_commission, 0) as 'internet_degree_commission',
        ho.`source`, ho.tenant_id, ho.operator_id, ho.operator_name,
        ho.clearing_checkout_id, ho.check_status, ho.check_by, ho.check_time,
        ifnull(ho.activity_power_price_total, 0) as 'activity_power_price_total',
        ifnull(ho.activity_service_price_total, 0) as 'activity_service_price_total',
        ifnull(ho.activity_total_price, 0) as 'activity_total_price'
    </sql>



    <update id="updateStatusBatchByOrderIds">
        update xhpc_clearing_history_order set check_status=#{status}, check_by=#{checkBy}, check_time=sysdate()
        where find_in_set(clearing_order_id, #{orderIds})
    </update>


    <select id="selectCheckedAmount" resultType="java.math.BigDecimal">
        select
            ifnull(sum(ho.total_price), 0)
        from xhpc_clearing_history_order as ho
        where ho.check_status=2 and ho.del_flag=0 and ho.operator_id=#{operatorId}
    </select>


    <select id="selectCashAmount" resultType="map">
        select
            ifnull(sum(ho.act_power_price + ho.act_service_price), 0) as amount,
            ifnull(sum(ho.act_service_price), 0) as serviceAmount,
            ifnull(sum(ho.act_power_price), 0) as powerAmount,
               group_concat(clearing_order_id) as clearingOrderIds
        from xhpc_clearing_history_order as ho
        where ho.check_status=2 and ho.del_flag=0 and ho.clearing_checkout_id is null and ho.operator_id=#{operatorId}
    </select>


    <select id="selectOrderListByOperator"
            resultType="com.xhpc.activity.domain.XhpcClearingHistoryOrderDomain">
        select
            <include refid="Base_Column_List"/>
        from xhpc_clearing_history_order as ho
        where ho.del_flag=0 and ho.check_status=2 and ho.clearing_checkout_id is null and ho.operator_id=#{operatorId}
    </select>


    <select id="selectOrderPage" resultType="map">
        select
            ho.serial_number as 'serialNumber',
            ct.name as 'stationName',
        ifnull(sum(ho.act_power_price + ho.act_service_price), 0) as 'totalPrice',
            ho.start_time as 'startTime',
            ho.end_time as 'endTime',
                inu.phone as internetUserPhone,
            apu.phone as appUserPhone,
            cop.account as communityAccount,
            cup.account as customersAccount,
        CASE WHEN cc.STATUS = 1  THEN '提现中'
        WHEN cc.STATUS = 6 THEN '已提现'
        ELSE
            case ho.check_status WHEN 1 THEN '审核失败'
            when 2 THEN '可提现'
            ELSE '待清分' end
        END AS 'clearingStatus',
            case  when ho.source=0 then 'C端用户'
                      when ho.source=1 then '流量方用户'
                      when ho.source=2 then '社区用户'
                      else 'B端客户'
                    end as 'sourceName',
            case  when ho.charging_mode="1" then '快电'
                  when ho.charging_mode="2" then '恒大'
                  when ho.charging_mode="3" then '新电途'
                  when ho.charging_mode="4" then '小桔'
                  when ho.charging_mode="微信" then '微信'
                  when ho.charging_mode="支付宝" then '支付宝'
                  else '刷卡'
                end as 'chargingModeName'
        from xhpc_clearing_history_order ho
        left join xhpc_charging_station ct on ct.charging_station_id = ho.charging_station_id
        left join xhpc_clearing_checkout cc on ho.clearing_checkout_id=cc.clearing_checkout_id
        left join xhpc_internet_user as inu on inu.internet_user_id = ho.user_id and ho.internet_serial_number is not null and ho.source=1
        left join xhpc_app_user as apu on apu.app_user_id = ho.user_id and ho.internet_serial_number is null and ho.source=0
        left join xhpc_community_personnel as cop on cop.community_personnel_id = ho.user_id and ho.internet_serial_number is null and ho.source=2
        left join xhpc_customers_personnel as cup on cup.customers_personnel_id = ho.user_id and ho.internet_serial_number is null and ho.source=3
        where ho.del_flag=0
        <if test="params.tenantId!=null and params.tenantId!=''">
            and ct.tenant_id=#{params.tenantId}
        </if>
        <if test="params.operatorId!=null and params.operatorId!=''">
            and ct.operator_id=#{params.operatorId}
        </if>
        <if test="params.orderNo!=null and params.orderNo!=''">
            and ho.serial_number like concat('%',#{params.orderNo},'%')
        </if>
          <choose>
                <when test="params.source != null and params.source!='' and params.source =='刷卡'">
                    and ho.charging_mode is null
                </when>
              <when test="params.source!=null and params.source!='' and params.source !='刷卡'">
                  and ho.charging_mode =#{params.source}
              </when>
              <otherwise>

              </otherwise>
          </choose>

        <if test="params.stationName!=null and params.stationName!=''">
            and ct.name =concat('%', #{params.stationName}, '%')
        </if>
        <if test="params.stationId!=null and params.stationId!=''">
            and ct.charging_station_id =#{params.stationId}
        </if>
        <if test="params.startTime!=null and params.startTime!=''">
            and ho.end_time <![CDATA[ >=  ]]> #{params.startTime}
        </if>
        <if test="params.endTime!= null and params.endTime!=''">
            and ho.end_time <![CDATA[ <=  ]]> #{params.endTime}
        </if>
        <choose>
            <when test="params.status!=null and params.status !='' and params.status == '待审核'">
                and ho.check_status=0 and ho.clearing_checkout_id is null
            </when>
            <when test="params.status!=null and params.status !='' and params.status == '审核失败'">
                and ho.check_status=1 and ho.clearing_checkout_id is null
            </when>
            <when test="params.status!=null and params.status !='' and params.status == '审核成功'">
                and ho.check_status=2 and ho.clearing_checkout_id is null
            </when>
            <when test="params.status!=null and params.status !='' and params.status == '待清分'">
                and ho.check_status not in (1, 2) and ho.clearing_checkout_id is null
            </when>
            <when test="params.status!=null and params.status !='' and params.status == '提现中'">
                and ho.check_status=2 and cc.STATUS = 1
            </when>
            <when test="params.status!=null and params.status !='' and params.status == '已提现'">
                and ho.check_status=2 and cc.STATUS = 6
            </when>
        </choose>

        GROUP BY ho.`clearing_order_id`
        ORDER BY FIELD(cc.STATUS, NULL,1,2) ASC, FIELD(ho.check_status,NULL,0,2, 1) asc
    </select>


    <select id="getListPage" resultType="com.xhpc.activity.domain.XhpcClearingHistoryOrderDomain">
        select
        <include refid="Base_Column_List" />,
        ct.name as 'station_name',
        op.name as 'operator_name',
               xt.name as 'terminal_name',
        case  when co.source=0 then "C端用户"
        when co.source=1 then "流量方用户"
        when co.source=2 then "社区用户"
        else "B端客户"
        end as 'source_name',
        case  when ho.charging_mode="1" then "快电"
        when ho.charging_mode="2" then "恒大"
        when ho.charging_mode="3" then "新电途"
        when ho.charging_mode="4" then "小桔"
        else "--"
        end as 'charging_mode_name'
        from xhpc_clearing_history_order as ho
        left join xhpc_charging_station as ct on ct.charging_station_id = ho.charging_station_id
        left join xhpc_operator as op on op.operator_id = ct.operator_id
        left join xhpc_charge_order co on co.charge_order_id = ho.charge_order_id
        left join xhpc_terminal xt on xt.terminal_id=ho.terminal_id
        where ho.status =0 and ho.del_flag=0 and ho.check_status=0 and ho.state <![CDATA[ <>  ]]> 3
        <if test="params.startTime!=null and params.startTime!=''">
            and ho.end_time <![CDATA[ >=  ]]> #{params.startTime}
        </if>
        <if test="params.endTime!= null and params.endTime!=''">
            and ho.end_time <![CDATA[ <=  ]]> #{params.endTime}
        </if>
        <if test="params.tenantId!=null and params.tenantId !=''">
            and ct.tenant_id=#{params.tenantId}
        </if>
        <if test="params.orderNo!=null and params.orderNo!=''">
            and ho.serial_number like concat('%', #{params.orderNo}, '%')
        </if>
        <if test="params.userType!=null and params.userType!=''">
            and co.source=#{params.userType}
        </if>
        <if test="params.internetName!=null and params.internetName!=''">
            and ho.charging_mode=#{params.internetName}
        </if>
        <if test="params.operatorId!=null and params.operatorId!=''">
            and ct.operator_id=#{params.operatorId}
        </if>
        <if test="params.stationId!=null and params.stationId!=''">
            and ho.charging_station_id=#{params.stationId}
        </if>
        <if test="params.checkoutIds!=null and params.checkoutIds!='' and params.checkoutIds!='-1'">
            and find_in_set(ho.clearing_checkout_id, #{params.checkoutIds})
        </if>

        <if test="params.operatorId !=null and params.operatorId !=''">
            and ho.operator_id =#{params.operatorId}
        </if>
        <if test="params.chargingStationId !=null and params.chargingStationId !=''">
            and ho.charging_station_id =#{params.chargingStationId}
        </if>
        <if test="params.chargingPileId !=null and params.chargingPileId !=''">
            and ho.terminal_id in (select terminal_id from xhpc_terminal where charging_pile_id=#{params.chargingPileId} and del_flag =0)
        </if>
        <if test="params.terminalId !=null and params.terminalId !=''">
            and ho.terminal_id#{params.terminalId}
        </if>
        <if test="params.type==1">
            and ho.charging_station_id in (select charging_station_id from xhpc_charging_station where operator_id=#{params.logOperatorId})
        </if>
        <if test="params.type==2">
            and ho.charging_station_id in (select charging_station_id from xhpc_user_privilege where user_id=#{params.logOperatorId})
        </if>
        <if test="params.orderIds != null and params.orderIds!='' and params.orderIds !='-1'">
            and find_in_set(ho.clearing_order_id, #{params.orderIds})
        </if>
    </select>


    <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from xhpc_clearing_history_order ho
        where ho.clearing_order_id = #{clearingOrderId}
    </select>


    <update id="deleteLogicByPrimaryKey" parameterType="java.lang.Long">
        update xhpc_clearing_history_order
        set del_flag=2
        where clearing_order_id = #{clearingOrderId}
    </update>


    <delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
        delete
        from xhpc_clearing_history_order
        where clearing_order_id = #{clearingOrderId}
    </delete>


    <insert id="insertBatch">
        insert into xhpc_clearing_history_order (clearing_order_id, charging_station_id, charge_order_id, user_id,
        terminal_id, serial_number, internet_serial_number,
        power_price_total, service_price_total, total_price,
        promotion_discount, act_price, act_power_price,
        act_service_price, internet_commission, internet_svc_commission,
        platform_commission, platform_svc_commisssion,
        operation_commission, operation_svc_commission,
        start_soc, end_soc, reconciliation_status,
        sorting_status, `type`, `status`,
        del_flag, create_time, create_by,
        update_time, update_by, remark,
        activity_power_price_total,
        activity_service_price_total, activity_total_price,
        `state`, vin_normal, search_value,
        operator_id_evcs, charge_model_evcs, connector_power_evcs,
        meter_value_end_evcs, meter_value_start_evcs, operator_id3rdpty_evcs,
        start_time, end_time, stop_reason_evcs,
        total_power, user_name_evcs, phone,
        evcs_order_no, confirm_Result, rate_model_id,
        charging_mode, internet_degree_commission,
        `source`, tenant_id, operator_id,
        operator_name, clearing_checkout_id, check_status)
        values
        <foreach collection="domainList" index="index" item="domain" separator=",">
            (#{domain.clearingOrderId}, #{domain.chargingStationId}, #{domain.chargeOrderId},
            #{domain.userId},
            #{domain.terminalId}, #{domain.serialNumber},
            #{domain.internetSerialNumber},
            #{domain.powerPriceTotal}, #{domain.servicePriceTotal},
            #{domain.totalPrice},
            #{domain.promotionDiscount}, #{domain.actPrice},
            #{domain.actPowerPrice},
            #{domain.actServicePrice}, #{domain.internetCommission},
            #{domain.internetSvcCommission},
            #{domain.platformCommission}, #{domain.platformSvcCommisssion},
            #{domain.operationCommission}, #{domain.operationSvcCommission},
            #{domain.startSoc}, #{domain.endSoc},
            #{domain.reconciliationStatus},
            #{domain.sortingStatus}, #{domain.type},
            #{domain.status},
            #{domain.delFlag}, #{domain.createTime},
            #{domain.createBy},
            #{domain.updateTime}, #{domain.updateBy},
            #{domain.remark},
             #{domain.activityPowerPriceTotal}, #{domain.activityServicePriceTotal}, #{domain.activityTotalPrice},
            #{domain.state}, #{domain.vinNormal},
            #{domain.searchValue},
            #{domain.operatorIdEvcs}, #{domain.chargeModelEvcs},
            #{domain.connectorPowerEvcs},
            #{domain.meterValueEndEvcs}, #{domain.meterValueStartEvcs},
            #{domain.operatorId3rdptyEvcs},
            #{domain.startTime}, #{domain.endTime},
            #{domain.stopReasonEvcs},
            #{domain.totalPower}, #{domain.userNameEvcs},
            #{domain.phone},
            #{domain.evcsOrderNo}, #{domain.confirmResult},
            #{domain.rateModelId},
            #{domain.chargingMode}, #{domain.internetDegreeCommission},
            #{domain.source}, #{domain.tenantId},
            #{domain.operatorId},
            #{domain.operatorName}, #{domain.clearingCheckoutId}, #{domain.checkStatus})
        </foreach>
    </insert>


    <insert id="insert" keyColumn="clearing_order_id" keyProperty="clearingOrderId"
            parameterType="com.xhpc.activity.domain.XhpcClearingHistoryOrderDomain" useGeneratedKeys="true">
        insert into xhpc_clearing_history_order (charging_station_id, charge_order_id, user_id,
                                                 terminal_id, serial_number, internet_serial_number,
                                                 power_price_total, service_price_total, total_price,
                                                 promotion_discount, act_price, act_power_price,
                                                 act_service_price, internet_commission, internet_svc_commission,
                                                 platform_commission, platform_svc_commisssion,
                                                 operation_commission, operation_svc_commission,
                                                 start_soc, end_soc, reconciliation_status,
                                                 sorting_status, `type`, `status`,
                                                 del_flag, create_time, create_by,
                                                 update_time, update_by, remark,
                                                 activity_power_price_total,
                                                 activity_service_price_total, activity_total_price,
                                                 `state`, vin_normal, search_value,
                                                 operator_id_evcs, charge_model_evcs, connector_power_evcs,
                                                 meter_value_end_evcs, meter_value_start_evcs, operator_id3rdpty_evcs,
                                                 start_time, end_time, stop_reason_evcs,
                                                 total_power, user_name_evcs, phone,
                                                 evcs_order_no, confirm_Result, rate_model_id,
                                                 charging_mode, internet_degree_commission,
                                                 `source`, tenant_id, operator_id,
                                                 operator_name, clearing_checkout_id, check_status)
        values (#{chargingStationId}, #{chargeOrderId}, #{userId},
                #{terminalId}, #{serialNumber},
                #{internetSerialNumber},
                #{powerPriceTotal}, #{servicePriceTotal},
                #{totalPrice},
                #{promotionDiscount}, #{actPrice}, #{actPowerPrice},
                #{actServicePrice}, #{internetCommission},
                #{internetSvcCommission},
                #{platformCommission}, #{platformSvcCommisssion},
                #{operationCommission}, #{operationSvcCommission},
                #{startSoc}, #{endSoc}, #{reconciliationStatus},
                #{sortingStatus}, #{type}, #{status},
                #{delFlag}, #{createTime}, #{createBy},
                #{updateTime}, #{updateBy}, #{remark},
                #{activityPowerPriceTotal}, #{activityServicePriceTotal}, #{activityTotalPrice},
                #{state}, #{vinNormal}, #{searchValue},
                #{operatorIdEvcs}, #{chargeModelEvcs},
                #{connectorPowerEvcs},
                #{meterValueEndEvcs}, #{meterValueStartEvcs},
                #{operatorId3rdptyEvcs},
                #{startTime}, #{endTime}, #{stopReasonEvcs},
                #{totalPower}, #{userNameEvcs}, #{phone},
                #{evcsOrderNo}, #{confirmResult}, #{rateModelId},
                #{chargingMode}, #{internetDegreeCommission},
                #{source}, #{tenantId}, #{operatorId},
                #{operatorName}, #{clearingCheckoutId}, #{checkStatus})
    </insert>


    <update id="updateByPrimaryKey" parameterType="com.xhpc.activity.domain.XhpcClearingHistoryOrderDomain">
        update xhpc_clearing_history_order
        set charging_station_id        = #{chargingStationId},
            charge_order_id            = #{chargeOrderId},
            user_id                    = #{userId},
            terminal_id                = #{terminalId},
            serial_number              = #{serialNumber},
            internet_serial_number     = #{internetSerialNumber},
            power_price_total          = #{powerPriceTotal},
            service_price_total        = #{servicePriceTotal},
            total_price                = #{totalPrice},
            activity_power_price_total=#{activityPowerPriceTotal},
            activity_service_price_total=#{activityServicePriceTotal},
            activity_total_price = #{activityTotalPrice},
            promotion_discount         = #{promotionDiscount},
            act_price                  = #{actPrice},
            act_power_price            = #{actPowerPrice},
            act_service_price          = #{actServicePrice},
            internet_commission        = #{internetCommission},
            internet_svc_commission    = #{internetSvcCommission},
            platform_commission        = #{platformCommission},
            platform_svc_commisssion   = #{platformSvcCommisssion},
            operation_commission       = #{operationCommission},
            operation_svc_commission   = #{operationSvcCommission},
            start_soc                  = #{startSoc},
            end_soc                    = #{endSoc},
            reconciliation_status      = #{reconciliationStatus},
            sorting_status             = #{sortingStatus},
            `type`                     = #{type},
            `status`                   = #{status},
            del_flag                   = #{delFlag},
            create_time                = #{createTime},
            create_by                  = #{createBy},
            update_time                = #{updateTime},
            update_by                  = #{updateBy},
            remark                     = #{remark},
            `state`                    = #{state},
            vin_normal                 = #{vinNormal},
            search_value               = #{searchValue},
            operator_id_evcs           = #{operatorIdEvcs},
            charge_model_evcs          = #{chargeModelEvcs},
            connector_power_evcs       = #{connectorPowerEvcs},
            meter_value_end_evcs       = #{meterValueEndEvcs},
            meter_value_start_evcs     = #{meterValueStartEvcs},
            operator_id3rdpty_evcs     = #{operatorId3rdptyEvcs},
            start_time                 = #{startTime},
            end_time                   = #{endTime},
            stop_reason_evcs           = #{stopReasonEvcs},
            total_power                = #{totalPower},
            user_name_evcs             = #{userNameEvcs},
            phone                      = #{phone},
            evcs_order_no              = #{evcsOrderNo},
            confirm_Result             = #{confirmResult},
            rate_model_id              = #{rateModelId},
            charging_mode              = #{chargingMode},
            internet_degree_commission = #{internetDegreeCommission},
            `source`                   = #{source},
            tenant_id                  = #{tenantId},
            operator_id                = #{operatorId},
            operator_name              = #{operatorName},
            clearing_checkout_id       = #{clearingCheckoutId}
        where clearing_order_id = #{clearingOrderId}
    </update>
    <update id="updateCheckoutByOperator">
        update xhpc_clearing_history_order set clearing_checkout_id=#{checkoutId}
        where del_flag=0 and check_status=2 and clearing_checkout_id is null and operator_id=#{operatorId}
    </update>


    <update id="updateCheckoutByOrderId">
        update xhpc_clearing_history_order set clearing_checkout_id=#{checkoutId}
        where find_in_set(clearing_order_id, #{orderIds})
    </update>


    <select id="getLandUser" resultType="map">
        select user_id as userId,user_type as userType,operator_id as operatorId  from sys_user where user_id =#{userId}
    </select>



    <select id="getOrderListPage" resultType="map">
        select
        ho.clearing_order_id as clearingOrderId,
        co.charge_order_id as chargeOrderId,
        ho.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,
        ho.type as orderType,
        ho.start_soc as startSoc,
        ho.end_soc as endSoc,
        ho.vin_normal as vinCode,
        ho.internet_serial_number as internetSerialNumber,
        ho.evcs_order_no as evcsOrderNo,
        ho.total_price as totalPrice,
        ho.act_price as actPrice,
        ho.confirm_Result as confirmResult,
        co.charging_time as chargingTime,
        co.charging_degree as chargingDegree,
        co.start_time as startTime,
        co.end_time as endTime,
        co.update_time as updateTime,
        co.source as source,
        co.Plate_num as plateNum,
        dispute_order_status as disputeOrderStatus,
        inu.phone as internetUserPhone,
        inu.name as internetName,
        apu.phone as appUserPhone,
        ho.power_price_total as powerPriceTotal,
        ho.service_price_total as servicePriceTotal,
        ho.promotion_discount as promotionDiscount,
        ho.act_power_price as actPowerPrice,
        ho.act_service_price as actServicePrice,
        (ho.act_power_price+ho.act_service_price) as actSumPrice,
        ho.internet_commission as internetCommission,
        ho.internet_svc_commission as internetSvcCommission,
        ho.internet_degree_commission as internetDegreeCommission,
        ho.platform_commission as platformCommission,
        ho.platform_svc_commisssion as platformSvcCommisssion,
        ho.operation_commission as operationCommission,
        ho.operation_svc_commission as operationSvcCommission,
        cop.account as communityAccount,
        cup.account as customersAccount,
        case  when co.source=0 then "C端用户"
        when co.source=1 then "流量方用户"
        when co.source=2 then "社区用户"
        else "B端客户!"
        end sourceName,
        case  when ho.charging_mode="1" then "快电"
        when ho.charging_mode="2" then "恒大"
        when ho.charging_mode="3" then "新电途"
        when ho.charging_mode="4" then "小桔"
        when ho.charging_mode="微信" then "微信"
        when ho.charging_mode="支付宝" then "支付宝"
        else "刷卡"
        end chargingModeName
        from xhpc_clearing_history_order as ho
        left join xhpc_charging_station as ct on ct.charging_station_id = ho.charging_station_id
        left join xhpc_operator as op on op.operator_id = ct.operator_id
        left join xhpc_charge_order co on co.charge_order_id = ho.charge_order_id
        left join xhpc_terminal as ter on ter.terminal_id = ho.terminal_id
        left join xhpc_charging_pile as cp on cp.charging_pile_id = ter.charging_pile_id
        left join et_dispute_orders as ed on ed.start_charge_seq = ho.internet_serial_number and ed.dispute_order_status !=2
        left join xhpc_internet_user as inu on inu.internet_user_id = ho.user_id and ho.internet_serial_number is not null and ho.source=1
        left join xhpc_app_user as apu on apu.app_user_id = ho.user_id and ho.internet_serial_number is null and ho.source=0
        left join xhpc_community_personnel as cop on cop.community_personnel_id = ho.user_id and ho.internet_serial_number is null and ho.source=2
        left join xhpc_customers_personnel as cup on cup.customers_personnel_id = ho.user_id and ho.internet_serial_number is null and ho.source=3
        <if test="number==1">
            inner join (
            select hio.clearing_order_id as clearing_order_id from xhpc_clearing_history_order as hio INNER JOIN xhpc_app_user as xau on xau.app_user_id = hio.user_id where hio.source=0 and xau.phone LIKE concat('%',#{phone}, '%')
            union
            select hio.clearing_order_id as clearing_order_id from xhpc_clearing_history_order as hio INNER JOIN xhpc_internet_user as xiu on xiu.internet_user_id = hio.user_id where hio.source=1 and xiu.phone LIKE concat('%',#{phone}, '%')
            union
            select hio.clearing_order_id as clearing_order_id from xhpc_clearing_history_order as hio INNER JOIN xhpc_community_personnel as xcp on xcp.community_personnel_id = hio.user_id where hio.source=2 and xcp.account LIKE concat('%',#{phone}, '%')
            union
            select hio.clearing_order_id as clearing_order_id from xhpc_clearing_history_order as hio INNER JOIN xhpc_customers_personnel as xcup on xcup.customers_personnel_id = hio.user_id where hio.source=3 and xcup.account LIKE concat('%',#{phone}, '%')
            ) ut on ut.clearing_order_id = ho.clearing_order_id
        </if>
        where ho.status =0 and ho.del_flag=0 and ho.clearing_checkout_id = #{checkoutId}
        <if test="confirmResult !=null">
            and ho.confirm_Result =#{confirmResult}
        </if>
        <if test="type !=null">
            and ho.type=#{type}
        </if>
        <if test="source !=null">
            and ho.source=#{source}
        </if>
        <if test="plateNum !=null and plateNum !=''">
            and co.Plate_num like concat('%', #{plateNum}, '%')
        </if>
        <if test="internetId !=null">
            and inu.internet_user_id=#{internetId}
        </if>
        <if test="internetSerialNumber !=null and internetSerialNumber !=''">
            and ho.internet_serial_number like concat('%', #{internetSerialNumber}, '%')
        </if>
        <if test="terminalName !=null and terminalName !=''">
            and ter.name like concat(#{terminalName}, '%')
        </if>
        <if test="vinCode !=null and vinCode !=''">
            and ho.vin_normal like concat('%', #{vinCode}, '%')
        </if>
        <if test="evcsOrderNo !=null and evcsOrderNo !=''">
            and co.evcs_order_no  like concat('%', #{evcsOrderNo}, '%')
        </if>
        <if test="beginStartTime !=null and beginStartTime !=''">
            and ho.start_time &gt;= #{beginStartTime}
        </if>
        <if test="beginEndTime !=null and beginEndTime !=''">
            and ho.start_time &lt;= #{beginEndTime}
        </if>
        <if test="overStartTime !=null and overStartTime !=''">
            and ho.end_time &gt;= #{overStartTime}
        </if>
        <if test="overEndTime !=null and overEndTime !=''">
            and ho.end_time &lt;= #{overEndTime}
        </if>
        <if test="transactionNumber !=null and transactionNumber !=''">
            and ho.serial_number like concat('%', #{transactionNumber}, '%')
        </if>
        <if test="chargingStationName !=null and chargingStationName !=''">
            and ct.name like concat('%', #{chargingStationName}, '%')
        </if>
        <if test="operatorId !=null">
            and op.operator_id =#{operatorId}
        </if>

        <if test="number==2">
            <if test="source ==0">
                and ho.clearing_order_id in (select hio.clearing_order_id as clearing_order_id from xhpc_clearing_history_order as hio INNER JOIN xhpc_app_user as xau on xau.app_user_id = hio.user_id where hio.source=0)
            </if>
            <if test="source ==1">
                and ho.clearing_order_id in(select hio.clearing_order_id as clearing_order_id from xhpc_clearing_history_order as hio INNER JOIN xhpc_internet_user as xiu on xiu.internet_user_id = hio.user_id where hio.source=1)
            </if>
            <if test="source ==2">
                and ho.clearing_order_id in (select hio.clearing_order_id as clearing_order_id from xhpc_clearing_history_order as hio INNER JOIN xhpc_community_personnel as xcp on xcp.community_personnel_id = hio.user_id where hio.source=2)
            </if>
            <if test="source ==3">
                and ho.clearing_order_id in (select hio.clearing_order_id as clearing_order_id from xhpc_clearing_history_order as hio INNER JOIN xhpc_customers_personnel as xcup on xcup.customers_personnel_id = hio.user_id where hio.source=3)
            </if>
        </if>
        <if test="number==3">
            <if test="source ==0">
                and ho.clearing_order_id in(select hio.clearing_order_id as clearing_order_id from xhpc_clearing_history_order as hio INNER JOIN xhpc_app_user as xau on xau.app_user_id = hio.user_id where hio.source=0 and xau.phone LIKE concat('%',#{phone}, '%'))
            </if>
            <if test="source ==1">
                and ho.clearing_order_id in(select hio.clearing_order_id as clearing_order_id from xhpc_clearing_history_order as hio INNER JOIN xhpc_internet_user as xiu on xiu.internet_user_id = hio.user_id where hio.source=1 and xiu.phone LIKE concat('%',#{phone}, '%'))
            </if>
            <if test="source ==2">
                and ho.clearing_order_id in(select hio.clearing_order_id as clearing_order_id from xhpc_clearing_history_order as hio INNER JOIN xhpc_community_personnel as xcp on xcp.community_personnel_id = hio.user_id where hio.source=2 and xcp.account LIKE concat('%',#{phone}, '%'))
            </if>
            <if test="source ==3">
                and ho.clearing_order_id in(select hio.clearing_order_id as clearing_order_id from xhpc_clearing_history_order as hio INNER JOIN xhpc_customers_personnel as xcup on xcup.customers_personnel_id = hio.user_id where hio.source=3 and xcup.account LIKE concat('%',#{phone}, '%'))
            </if>
        </if>
        <if test="status==1">
            and co.charging_station_id in (select charging_station_id from xhpc_charging_station where operator_id=#{userId})
        </if>
        <if test="status==2">
            and co.charging_station_id in (select charging_station_id from xhpc_user_privilege where user_id=#{userId})
        </if>
        <if test="personnelId !=null">
            and ho.user_id=#{personnelId}
        </if>
        <if test="tenantId !=null and '' !=tenantId">
            and ho.tenant_id=#{tenantId}
        </if>
        <if test="isNotNull==1">
            and ho.total_price &gt;=0
        </if>
        order by ho.create_time desc
    </select>
</mapper>