USE [Ladaer2]
GO
/****** Object:  StoredProcedure [dbo].[sp_GetOrderRevenueStatisticsBySale_Optimized]    Script Date: 19/01/2026 5:42:46 CH ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER   PROCEDURE [dbo].[sp_GetOrderRevenueStatisticsBySale]
    @StartDate DATE = NULL,
    @EndDate DATE = NULL,
    @SaleId INT = NULL
AS
BEGIN
    SET NOCOUNT ON;

    -- Set default date range if not provided
    IF @StartDate IS NULL
        SET @StartDate = '1900-01-01';

    IF @EndDate IS NULL
        SET @EndDate = '9999-12-30';
    -- Tránh overflow khi DATEADD(DAY, 1)

    -- CTE to pre-calculate revenue, collection date and group data
    WITH
        OrderData
        AS
        (
            SELECT
                o.SaleId,
                ops.Status,
                -- Calculate revenue once
                ISNULL(fin.Fee, 0) + 
            ISNULL(fin.CodFee, 0) + 
            ISNULL(fin.PickupSubFee, 0) + 
            ISNULL(fin.DeliverySubFee, 0) - 
            ISNULL(fin.Discount, 0) AS Revenue,

                -- Financial fields
                fin.PayFeeType,
                fin.SourceFeeStage,
                fin.DestFeeStage,
                fin.TransferFeeStage,
                fin.DeductCodFeeStage,

                -- Calculate CollectionTime based on PayFeeType
                CASE fin.PayFeeType
                    WHEN 1 THEN track.HeadSourceFeeTime        -- SenderPay
                    WHEN 2 THEN track.HeadDestFeeTime          -- ConsigneePay
                    WHEN 3 THEN track.HeadTransferFeeTime      -- BankTransfer
                    WHEN 4 THEN                                -- SplitPay - lấy thời gian sớm nhất
                        CASE 
                            WHEN track.HeadSourceFeeTime IS NOT NULL AND track.HeadDestFeeTime IS NOT NULL 
                                THEN (CASE WHEN track.HeadSourceFeeTime < track.HeadDestFeeTime 
                                          THEN track.HeadSourceFeeTime 
                                          ELSE track.HeadDestFeeTime END)
                            WHEN track.HeadSourceFeeTime IS NOT NULL THEN track.HeadSourceFeeTime
                            WHEN track.HeadDestFeeTime IS NOT NULL THEN track.HeadDestFeeTime
                            ELSE NULL
                        END
                    WHEN 5 THEN track.HeadDeductCodFeeTime     -- COD
                    ELSE NULL
                END AS CollectionTime,

                -- Pre-calculate financial status flags to avoid redundant logic
                -- IsUnpaid: Chưa thanh toán
                CASE
                    WHEN fin.PayFeeType = 1 AND fin.SourceFeeStage IN (0, 1) THEN 1  -- SenderPay
                    WHEN fin.PayFeeType = 2 AND fin.DestFeeStage IN (0, 1) THEN 1     -- ConsigneePay
                    WHEN fin.PayFeeType = 3 AND fin.TransferFeeStage IN (0, 1) THEN 1 -- BankTransfer
                    WHEN fin.PayFeeType = 4 AND (fin.SourceFeeStage IN (0, 1) OR fin.DestFeeStage IN (0, 1)) THEN 1 -- SplitPay
                    WHEN fin.PayFeeType = 5 AND fin.DeductCodFeeStage IN (0, 1) THEN 1 -- COD
                    ELSE 0
                END AS IsUnpaid,

                -- IsWaitingConfirmation: Chờ xác nhận
                CASE
                    WHEN fin.PayFeeType = 1 AND fin.SourceFeeStage IN (2, 3) THEN 1  -- SenderPay
                    WHEN fin.PayFeeType = 2 AND fin.DestFeeStage IN (2, 3) THEN 1     -- ConsigneePay
                    WHEN fin.PayFeeType = 3 AND fin.TransferFeeStage = 4 THEN 1       -- BankTransfer
                    WHEN fin.PayFeeType = 4 AND (fin.SourceFeeStage IN (2, 3) OR fin.DestFeeStage IN (2, 3)) THEN 1 -- SplitPay
                    WHEN fin.PayFeeType = 5 AND fin.DeductCodFeeStage = 2 THEN 1      -- COD
                    ELSE 0
                END AS IsWaitingConfirmation,

                -- IsPaid: Đã thanh toán
                CASE
                    WHEN fin.PayFeeType = 1 AND fin.SourceFeeStage = 9 THEN 1  -- SenderPay
                    WHEN fin.PayFeeType = 2 AND fin.DestFeeStage = 9 THEN 1     -- ConsigneePay
                    WHEN fin.PayFeeType = 3 AND fin.TransferFeeStage = 9 THEN 1 -- BankTransfer
                    WHEN fin.PayFeeType = 4 AND (fin.SourceFeeStage = 9 OR fin.DestFeeStage = 9) THEN 1 -- SplitPay
                    WHEN fin.PayFeeType = 5 AND fin.DeductCodFeeStage = 9 THEN 1 -- COD
                    ELSE 0
                END AS IsPaid
            FROM [Order] o WITH (NOLOCK)
                INNER JOIN OrderOps ops WITH (NOLOCK) ON o.Id = ops.Id
                INNER JOIN OrderFin fin WITH (NOLOCK) ON o.Id = fin.Id
                INNER JOIN OrderTimeTrack track WITH (NOLOCK) ON o.Id = track.Id
            WHERE 
            o.IsDeleted = 0
                -- Filter by SaleId
                AND (@SaleId IS NULL OR o.SaleId = @SaleId)
        ),
        -- Second CTE to filter by date
        FilteredOrderData
        AS
        (
            SELECT
                SaleId,
                Status,
                Revenue,
                IsUnpaid,
                IsWaitingConfirmation,
                IsPaid
            FROM OrderData
            WHERE 
                CollectionTime IS NOT NULL -- Chỉ tính đơn đã có thời gian nhận tiền
                AND CollectionTime >= CAST(@StartDate AS DATETIMEOFFSET)
                AND CollectionTime < CAST(DATEADD(DAY, 1, @EndDate) AS DATETIMEOFFSET)
        )
    -- Main aggregation query - THỐNG KÊ TÀI CHÍNH THEO SALE
    SELECT
        fod.SaleId,
        u.FullName AS SaleName,
        u.UserName AS SaleUserName,

        -- Tổng số đơn và cước (không bao gồm đơn hủy)
        COUNT(CASE WHEN fod.Status <> 8 THEN 1 END) AS TotalOrders,
        SUM(CASE WHEN fod.Status <> 8 THEN fod.Revenue ELSE 0 END) AS TotalRevenue,

        -- Chưa thanh toán
        COUNT(CASE WHEN fod.Status <> 8 AND fod.IsUnpaid = 1 THEN 1 END) AS UnpaidOrders,
        SUM(CASE WHEN fod.Status <> 8 AND fod.IsUnpaid = 1 THEN fod.Revenue ELSE 0 END) AS UnpaidRevenue,

        -- Chờ xác nhận
        COUNT(CASE WHEN fod.Status <> 8 AND fod.IsWaitingConfirmation = 1 THEN 1 END) AS WaitingConfirmationOrders,
        SUM(CASE WHEN fod.Status <> 8 AND fod.IsWaitingConfirmation = 1 THEN fod.Revenue ELSE 0 END) AS WaitingConfirmationRevenue,

        -- Đã thanh toán
        COUNT(CASE WHEN fod.Status <> 8 AND fod.IsPaid = 1 THEN 1 END) AS PaidOrders,
        SUM(CASE WHEN fod.Status <> 8 AND fod.IsPaid = 1 THEN fod.Revenue ELSE 0 END) AS PaidRevenue,

        -- Doanh số ghi nhận (Đã thanh toán - 8% VAT)
        SUM(CASE WHEN fod.Status <> 8 AND fod.IsPaid = 1 THEN fod.Revenue / 1.08 ELSE 0 END) AS RecognizedRevenue

    FROM FilteredOrderData fod
        LEFT JOIN [User] u WITH (NOLOCK) ON fod.SaleId = u.Id
    GROUP BY fod.SaleId, u.FullName, u.UserName
    ORDER BY TotalRevenue DESC;
-- Sắp xếp theo doanh thu cao nhất
END
