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

    <resultMap type="com.xhpc.order.domain.XhpcHistoryOrderSortingStatus" id="XhpcHistoryOrderSortingStatusResult">
        <result column="history_order_sorting_status_id" property="historyOrderSortingStatusId"/>
        <result column="history_order_id" property="historyOrderId"/>
        <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"/>
    </resultMap>

    <insert id="insert" parameterType="com.xhpc.order.domain.XhpcHistoryOrderSortingStatus" useGeneratedKeys="true"
            keyProperty="historyOrderSortingStatusId">
        INSERT INTO xhpc_history_order_sorting_status
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="null != historyOrderId and '' != historyOrderId">
                history_order_id,
            </if>
            <if test="null != status and '' != status">
                status,
            </if>
            <if test="null != delFlag and '' != delFlag">
                del_flag,
            </if>
            <if test="null != createTime ">
                create_time,
            </if>
            <if test="null != createBy and '' != createBy">
                create_by,
            </if>
            <if test="null != updateTime">
                update_time,
            </if>
            <if test="null != updateBy and '' != updateBy">
                update_by,
            </if>
            <if test="null != remark and '' != remark">
                remark
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="null != historyOrderId and '' != historyOrderId">
                #{historyOrderId},
            </if>
            <if test="null != status and '' != status">
                #{status},
            </if>
            <if test="null != delFlag and '' != delFlag">
                #{delFlag},
            </if>
            <if test="null != createTime ">
                #{createTime},
            </if>
            <if test="null != createBy and '' != createBy">
                #{createBy},
            </if>
            <if test="null != updateTime">
                #{updateTime},
            </if>
            <if test="null != updateBy and '' != updateBy">
                #{updateBy},
            </if>
            <if test="null != remark and '' != remark">
                #{remark}
            </if>
        </trim>
    </insert>

    <update id="update" parameterType="com.xhpc.order.domain.XhpcHistoryOrderReconciliationStatus">
        UPDATE xhpc_history_order_reconciliation_status
        <set>
            <if test="null != historyOrderId and '' != historyOrderId">history_order_id = #{historyOrderId},</if>
            <if test="null != status">status = #{status},</if>
            <if test="null != delFlag and '' != delFlag">del_flag = #{delFlag},</if>
            <if test="null != createTime ">create_time = #{createTime},</if>
            <if test="null != createBy and '' != createBy">create_by = #{createBy},</if>
            <if test="null != updateTime">update_time = #{updateTime},</if>
            <if test="null != updateBy and '' != updateBy">update_by = #{updateBy},</if>
            <if test="null != remark and '' != remark">remark = #{remark}</if>
        </set>
        WHERE history_order_sorting_status_id = #{historyOrderSortingStatusId}
    </update>

    <select id="operatorUser" parameterType="java.lang.Long" resultType="java.util.Map">
        select xo.operator_id operatorId,xo.`name`,xo.open_bank openbank,xo.card_number
        cardNumber,a.waitSorting,b.sortingOnTheWay,c.withdrawnCash
        from sys_user su
        LEFT JOIN (select sum(xho.act_power_price+xho.act_service_price) waitSorting,xo.operator_id
        from xhpc_history_order xho
        LEFT JOIN xhpc_charging_station xcs on xcs.charging_station_id = xho.charging_station_id
        LEFT JOIN xhpc_operator xo on xo.operator_id = xcs.operator_id
        <if test="chargingStation != null and chargingStation  != ''">
            LEFT JOIN xhpc_user_privilege xup on xup.charging_station_id = xcs.charging_station_id
        </if>
        where xho.del_flag = 0 and xho.sorting_status = 0
        <if test="operatorId != null and operatorId != ''">
            and xo.operator_id = #{operatorId }
        </if>
        <if test="chargingStation != null and chargingStation  != ''">
            and xho.user_id = #{userId}
        </if>
        GROUP BY xo.operator_id) a on su.operator_id = a.operator_id
        LEFT JOIN
        (select sum(xho.act_power_price+xho.act_service_price) sortingOnTheWay ,xo.operator_id
        from xhpc_history_order xho
        LEFT JOIN xhpc_charging_station xcs on xcs.charging_station_id = xho.charging_station_id
        LEFT JOIN xhpc_operator xo on xo.operator_id = xcs.operator_id
        <if test="chargingStation != null and chargingStation  != ''">
            LEFT JOIN xhpc_user_privilege xup on xup.charging_station_id = xcs.charging_station_id
        </if>
        where xho.del_flag = 0 and xho.sorting_status = 1
        <if test="operatorId != null and operatorId != ''">
            and xo.operator_id = #{operatorId }
        </if>
        <if test="chargingStation != null and chargingStation  != ''">
            and xho.user_id = #{userId}
        </if>
        GROUP BY xo.operator_id) b on su.operator_id = b.operator_id
        LEFT JOIN
        (select sum(xho.act_power_price+xho.act_service_price) withdrawnCash ,xo.operator_id
        from xhpc_history_order xho
        LEFT JOIN xhpc_charging_station xcs on xcs.charging_station_id = xho.charging_station_id
        LEFT JOIN xhpc_operator xo on xo.operator_id = xcs.operator_id
        <if test="chargingStation != null and chargingStation  != ''">
            LEFT JOIN xhpc_user_privilege xup on xup.charging_station_id = xcs.charging_station_id
        </if>
        where xho.del_flag = 0 and xho.sorting_status = 2
        <if test="operatorId != null and operatorId != ''">
            and xo.operator_id = #{operatorId }
        </if>
        <if test="chargingStation != null and chargingStation  != ''">
            and xho.user_id = #{userId}
        </if>
        GROUP BY xo.operator_id) c on su.operator_id = c.operator_id
        LEFT JOIN xhpc_operator xo on xo.operator_id = su.operator_id
        where su.user_id = #{userId} LIMIT 1
    </select>

    <select id="page" parameterType="java.lang.Long" resultType="java.util.Map">
        select xho.act_power_price+xho.act_service_price
        expenditure,xho.history_order_id historyOrderId,
        xho.user_id userId,xho.serial_number serialNumber,xco.create_time createTime,
        xcs.`name` chargingStationName,xho.`sorting_status` sortingStatus,sdd.dict_label sortingStatusName,
        xco.source,
        CASE WHEN xco.source = 0 THEN '平台' else xiu.`name` end sourceName
        from xhpc_history_order xho
        LEFT JOIN xhpc_charge_order xco on xco.charge_order_id = xho.charge_order_id
        LEFT JOIN xhpc_charging_station xcs on xcs.charging_station_id = xho.charging_station_id
        LEFT JOIN xhpc_operator xo on xo.operator_id = xcs.operator_id
        LEFT JOIN sys_user su on xo.operator_id = su.operator_id
        LEFT JOIN sys_dict_data sdd on sdd.`dict_type` = 'sorting_status' and sdd.dict_value = xho.`sorting_status`
        <if test="chargingStation != null and chargingStation != ''">
            LEFT JOIN xhpc_user_privilege xup on xup.charging_station_id = xcs.charging_station_id
        </if>
        LEFT JOIN xhpc_internet_user xiu on xiu.internet_user_id = su.internet_user_id
        where xho.del_flag = 0
        <if test="userId != null and userId != ''">
            and su.user_id = #{userId}
        </if>
        <if test="serialNumber != null and serialNumber != ''">
            and xho.serial_number like concat(concat('%', #{serialNumber}), '%')
        </if>
        <if test="createTimeStart != null and createTimeStart != ''"><!-- 开始时间检索 -->
            AND xco.create_time &gt;= #{createTimeStart}
        </if>
        <if test="createTimeEnd != null and createTimeEnd != ''"><!-- 结束时间检索 -->
            AND xco.create_time &lt;= #{createTimeEnd}
        </if>
        <if test="chargingStationName != null and chargingStationName != ''">
            and xcs.`name`like concat(concat('%', #{chargingStationName}), '%')
        </if>
        <if test="status != null and status != ''">
            and xho.sorting_status = #{status}
        </if>
        <if test="operatorId != null and operatorId != ''">
            and xo.operator_id = #{operatorId }
        </if>
        <if test="chargingStation != null and chargingStation != ''">
            and xup.user_id = #{userId}
        </if>
        ORDER BY xho.create_time DESC
    </select>

    <select id="info" parameterType="java.lang.Long" resultType="java.util.Map">
        select sum(xho.act_power_price+xho.act_service_price)
        expenditure,xho.history_order_id historyOrderId,
        xho.user_id userId,xho.serial_number serialNumber,xco.create_time createTime,
        xcs.`name` chargingStationName,xho.`sorting_status` sortingStatus,sdd.dict_label sortingStatusName,
        xco.source,
        CASE WHEN xco.source = 0 THEN '平台' else xiu.`name` end sourceName,
        xho.end_soc endSoc
        from xhpc_history_order xho
        LEFT JOIN xhpc_charge_order xco on xco.charge_order_id = xho.charge_order_id
        LEFT JOIN xhpc_charging_station xcs on xcs.charging_station_id = xho.charging_station_id
        LEFT JOIN xhpc_operator xo on xo.operator_id = xcs.operator_id
        LEFT JOIN sys_user su on xo.operator_id = su.operator_id
        LEFT JOIN sys_dict_data sdd on sdd.`dict_type` = 'sorting_status' and sdd.dict_value = xho.`sorting_status`
        LEFT JOIN xhpc_internet_user xiu on xiu.internet_user_id = su.internet_user_id
        where xho.del_flag = 0 and xho.history_order_id = #{historyOrderId}
    </select>
</mapper> 