<?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.njzscloud.dispose.finance.mapper.BillMapper">

    <resultMap id="BillStatisticsResultMap" type="com.njzscloud.dispose.finance.pojo.result.BillStatisticsResult" autoMapping="true">
        <result column="user_id" property="userId"/>
        <result column="customer_id" property="customerId"/>
        <result column="org_id" property="orgId"/>
        <result column="account_type" property="accountType"/>
        <result column="order_count" property="orderCount"/>
        <result column="car_count" property="carCount"/>
        <result column="total_weight" property="totalWeight"/>
        <result column="total_money" property="totalMoney"/>
        <result column="discount_money" property="discountMoney"/>
    </resultMap>

    <!-- 统计对账单数据 -->
    <select id="statisticsBillData" resultMap="BillStatisticsResultMap">
        SELECT
            co.user_id AS user_id,
            co.customer_id AS customer_id,
            co.trans_org_id AS org_id,
            fma.account_type AS account_type,
            -- 订单数：统计已完成订单的数量
            COUNT(DISTINCT copr.order_id) AS order_count,
            -- 总车数：统计运输状态为已完成的运输信息条数
            COUNT(DISTINCT cot.id) AS car_count,
            -- 总质量：统计运输信息的净重，单位吨（千克除以1000转换为吨）
            COALESCE(SUM(cot.settle_weight) / 1000, 0) AS total_weight,
            -- 总金额：付费记录的总金额
            COALESCE(SUM(copr.total_money), 0) AS total_money,
            -- 优惠金额：付费记录的优惠金额
            COALESCE(SUM(copr.discount_money), 0) AS discount_money
        FROM cst_order_payment_record copr
        -- 关联付费项表
        INNER JOIN cst_order_expense_items coei ON coei.id = copr.expense_item_id
        -- 关联订单表
        INNER JOIN cst_order co ON co.id = copr.order_id
        -- 关联运输信息表
        LEFT JOIN cst_order_trans cot ON cot.id = copr.trans_id
        -- 关联资金账户表获取账户类型
        LEFT JOIN fin_money_account fma ON fma.customer_id = co.customer_id AND fma.deleted = 0
        -- 筛选条件
        WHERE co.deleted = 0
          AND co.order_status = 'YiWanCheng'
          AND co.finish_time BETWEEN #{startTime} AND #{endTime}
          -- 运输状态为已完成才计入车数和净重
          AND (cot.trans_status = 'YiWanCheng')
        -- 按源付费项ID分组
        GROUP BY coei.origin_expense_items_id
    </select>

</mapper>
