USE [Ladaer2]
GO
/****** Object:  StoredProcedure [dbo].[sp_GetSaleRevenueByCustomer]    Script Date: 20/01/2026 08:52:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      LADA System
-- Create date: 2026-01-19
-- Description: Lấy thống kê doanh số theo khách hàng
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[sp_GetSaleRevenueByCustomer]
    @StartDate DATETIMEOFFSET = NULL,
    @EndDate DATETIMEOFFSET = NULL,
    @CustomerId INT = NULL,
    @CustomerIds NVARCHAR(MAX) = NULL,
    @SaleId INT = NULL,
    @PageNumber INT = 1,
    @PageSize INT = 25
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Set giá trị mặc định cho StartDate và EndDate nếu null
    -- StartDate: Đầu tháng hiện tại (00:00:00)
    -- EndDate: Thời điểm hiện tại
    IF @StartDate IS NULL
    BEGIN
        SET @StartDate = DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1);
    END
    
    IF @EndDate IS NULL
    BEGIN
        SET @EndDate = GETDATE();
    END
    
    -- Tạo bảng tạm để chứa danh sách CustomerIds nếu có
    DECLARE @CustomerIdTable TABLE (CustomerId INT);
    
    IF @CustomerIds IS NOT NULL
    BEGIN
        INSERT INTO @CustomerIdTable (CustomerId)
        SELECT CAST(value AS INT)
        FROM STRING_SPLIT(@CustomerIds, ',')
        WHERE RTRIM(value) <> '';
    END
    
    -- CTE chứa dữ liệu khách hàng
    ;WITH CustomerData AS (
        SELECT 
            c.Id AS CustomerId,
            c.UserName AS CustomerCode,
            c.FullName AS CustomerName,
            c.PhoneNumber AS CustomerPhone,
            c.ManagerId,
            u.FullName AS ManagerName,
            
            -- Tổng hợp
			SUM(CASE WHEN oops.[Status] <> 8 THEN 1 ELSE 0 END) AS TotalOrders,
            ISNULL(SUM(CASE WHEN oops.[Status] <> 8 THEN ofin.Fee + ofin.PickupSubFee + ofin.DeliverySubFee + ofin.CodFee + ofin.InsuranceFee + ofin.ReturnFee - ofin.Discount ELSE 0 END), 0) AS TotalRevenue,
            ISNULL(SUM(CASE WHEN oops.[Status] <> 8 THEN ofin.CollectAmount ELSE 0 END), 0) AS TotalCollectAmount,


            --COUNT(o.Id) AS TotalOrders,
            --ISNULL(SUM(ofin.Fee + ofin.PickupSubFee + ofin.DeliverySubFee + ofin.CodFee + ofin.InsuranceFee + ofin.ReturnFee - ofin.Discount), 0) AS TotalRevenue,
            --ISNULL(SUM(ofin.CollectAmount), 0) AS TotalCollectAmount,
            
            -- Chờ lấy hàng (PhysicalStatus = 1)
            SUM(CASE WHEN oops.PhysicalStatus = 1 THEN 1 ELSE 0 END) AS WaitPickup,
            ISNULL(SUM(CASE WHEN oops.PhysicalStatus = 1 THEN ofin.Fee + ofin.PickupSubFee + ofin.DeliverySubFee + ofin.CodFee + ofin.InsuranceFee + ofin.ReturnFee - ofin.Discount ELSE 0 END), 0) AS WaitPickupRevenue,
            ISNULL(SUM(CASE WHEN oops.PhysicalStatus = 1 THEN ofin.CollectAmount ELSE 0 END), 0) AS WaitPickupCollectAmount,
            
            -- Đang lấy hàng (PhysicalStatus = 2)
            SUM(CASE WHEN oops.PhysicalStatus IN (11,12) THEN 1 ELSE 0 END) AS Picking,
            ISNULL(SUM(CASE WHEN oops.PhysicalStatus IN (11,12) THEN ofin.Fee + ofin.PickupSubFee + ofin.DeliverySubFee + ofin.CodFee + ofin.InsuranceFee + ofin.ReturnFee - ofin.Discount ELSE 0 END), 0) AS PickingRevenue,
            ISNULL(SUM(CASE WHEN oops.PhysicalStatus IN (11,12) THEN ofin.CollectAmount ELSE 0 END), 0) AS PickingCollectAmount,
            
            -- Lấy hàng thất bại (PhysicalStatus = 3)
            SUM(CASE WHEN oops.[Status] = 11 THEN 1 ELSE 0 END) AS PickupFailed,
            ISNULL(SUM(CASE WHEN oops.[Status] = 11 THEN ofin.Fee + ofin.PickupSubFee + ofin.DeliverySubFee + ofin.CodFee + ofin.InsuranceFee + ofin.ReturnFee - ofin.Discount ELSE 0 END), 0) AS PickupFailedRevenue,
            ISNULL(SUM(CASE WHEN oops.[Status] = 11 THEN ofin.CollectAmount ELSE 0 END), 0) AS PickupFailedCollectAmount,
            
            -- Đang trung chuyển (PhysicalStatus = 4)
            SUM(CASE WHEN oops.PhysicalStatus IN (13,20,30) THEN 1 ELSE 0 END) AS Transporting,
            ISNULL(SUM(CASE WHEN oops.PhysicalStatus IN (13,20,30) THEN ofin.Fee + ofin.PickupSubFee + ofin.DeliverySubFee + ofin.CodFee + ofin.InsuranceFee + ofin.ReturnFee - ofin.Discount ELSE 0 END), 0) AS TransportingRevenue,
            ISNULL(SUM(CASE WHEN oops.PhysicalStatus IN (13,20,30) THEN ofin.CollectAmount ELSE 0 END), 0) AS TransportingCollectAmount,
            
            -- Đang giao hàng (PhysicalStatus = 5)
            SUM(CASE WHEN oops.PhysicalStatus IN (51,53) THEN 1 ELSE 0 END) AS Delivering,
            ISNULL(SUM(CASE WHEN oops.PhysicalStatus IN (51,53) THEN ofin.Fee + ofin.PickupSubFee + ofin.DeliverySubFee + ofin.CodFee + ofin.InsuranceFee + ofin.ReturnFee - ofin.Discount ELSE 0 END), 0) AS DeliveringRevenue,
            ISNULL(SUM(CASE WHEN oops.PhysicalStatus IN (51,53) THEN ofin.CollectAmount ELSE 0 END), 0) AS DeliveringCollectAmount,
            
            -- Giao hàng thất bại (PhysicalStatus = 6)
            SUM(CASE WHEN oops.[Status] = 51 THEN 1 ELSE 0 END) AS DeliveryFailed,
            ISNULL(SUM(CASE WHEN oops.[Status] = 51 THEN ofin.Fee + ofin.PickupSubFee + ofin.DeliverySubFee + ofin.CodFee + ofin.InsuranceFee + ofin.ReturnFee - ofin.Discount ELSE 0 END), 0) AS DeliveryFailedRevenue,
            ISNULL(SUM(CASE WHEN oops.[Status] = 51 THEN ofin.CollectAmount ELSE 0 END), 0) AS DeliveryFailedCollectAmount,
            
            -- Giao hàng thành công (PhysicalStatus = 7)
            SUM(CASE WHEN oops.PhysicalStatus = 55 THEN 1 ELSE 0 END) AS DeliverySuccess,
            ISNULL(SUM(CASE WHEN oops.PhysicalStatus = 55 THEN ofin.Fee + ofin.PickupSubFee + ofin.DeliverySubFee + ofin.CodFee + ofin.InsuranceFee + ofin.ReturnFee - ofin.Discount ELSE 0 END), 0) AS DeliverySuccessRevenue,
            ISNULL(SUM(CASE WHEN oops.PhysicalStatus = 55 THEN ofin.CollectAmount ELSE 0 END), 0) AS DeliverySuccessCollectAmount,
            
            -- Đã hủy (Status = 8)
            SUM(CASE WHEN oops.[Status] = 8 THEN 1 ELSE 0 END) AS Cancelled,
            ISNULL(SUM(CASE WHEN oops.[Status] = 8 THEN ofin.Fee + ofin.PickupSubFee + ofin.DeliverySubFee + ofin.CodFee + ofin.InsuranceFee + ofin.ReturnFee - ofin.Discount ELSE 0 END), 0) AS CancelledRevenue,
            ISNULL(SUM(CASE WHEN oops.[Status] = 8 THEN ofin.CollectAmount ELSE 0 END), 0) AS CancelledCollectAmount
            
        FROM Customer c
        INNER JOIN [Order] o ON c.Id = o.CustomerId
        INNER JOIN OrderFin ofin ON o.Id = ofin.Id
        INNER JOIN OrderOps oops ON o.Id = oops.Id
        LEFT JOIN [User] u ON c.ManagerId = u.Id
        
        WHERE o.IsDeleted = 0
            -- Lọc theo khoảng thời gian
            AND o.CreatedTime >= @StartDate
            AND o.CreatedTime <= @EndDate
            
            -- Lọc theo CustomerId
            AND (@CustomerId IS NULL OR c.Id = @CustomerId)
            
            -- Lọc theo danh sách CustomerIds
            AND (@CustomerIds IS NULL OR c.Id IN (SELECT CustomerId FROM @CustomerIdTable))
            
            -- Lọc theo SaleId
            AND (@SaleId IS NULL OR o.SaleId = @SaleId)
        
        GROUP BY c.Id, c.UserName, c.FullName, c.PhoneNumber, c.ManagerId, u.FullName
    ),
    -- CTE chứa dòng tổng cộng
    TotalRow AS (
        SELECT 
            0 AS CustomerId,
            N'TỔNG CỘNG' AS CustomerCode,
            N'TỔNG CỘNG' AS CustomerName,
            N'' AS CustomerPhone,
            NULL AS ManagerId,
            N'' AS ManagerName,
            
            -- Tổng hợp
            COUNT(o.Id) AS TotalOrders,
            ISNULL(SUM(ofin.Fee + ofin.PickupSubFee + ofin.DeliverySubFee + ofin.CodFee + ofin.InsuranceFee + ofin.ReturnFee - ofin.Discount), 0) AS TotalRevenue,
            ISNULL(SUM(ofin.CollectAmount), 0) AS TotalCollectAmount,
            
            -- Chờ lấy hàng
            SUM(CASE WHEN oops.PhysicalStatus = 1 THEN 1 ELSE 0 END) AS WaitPickup,
            ISNULL(SUM(CASE WHEN oops.PhysicalStatus = 1 THEN ofin.Fee + ofin.PickupSubFee + ofin.DeliverySubFee + ofin.CodFee + ofin.InsuranceFee + ofin.ReturnFee - ofin.Discount ELSE 0 END), 0) AS WaitPickupRevenue,
            ISNULL(SUM(CASE WHEN oops.PhysicalStatus = 1 THEN ofin.CollectAmount ELSE 0 END), 0) AS WaitPickupCollectAmount,
            
            -- Đang lấy hàng
            SUM(CASE WHEN oops.PhysicalStatus IN (11,12) THEN 1 ELSE 0 END) AS Picking,
            ISNULL(SUM(CASE WHEN oops.PhysicalStatus IN (11,12) THEN ofin.Fee + ofin.PickupSubFee + ofin.DeliverySubFee + ofin.CodFee + ofin.InsuranceFee + ofin.ReturnFee - ofin.Discount ELSE 0 END), 0) AS PickingRevenue,
            ISNULL(SUM(CASE WHEN oops.PhysicalStatus IN (11,12) THEN ofin.CollectAmount ELSE 0 END), 0) AS PickingCollectAmount,
            
            -- Lấy hàng thất bại
            SUM(CASE WHEN oops.[Status] = 11 THEN 1 ELSE 0 END) AS PickupFailed,
            ISNULL(SUM(CASE WHEN oops.[Status] = 11 THEN ofin.Fee + ofin.PickupSubFee + ofin.DeliverySubFee + ofin.CodFee + ofin.InsuranceFee + ofin.ReturnFee - ofin.Discount ELSE 0 END), 0) AS PickupFailedRevenue,
            ISNULL(SUM(CASE WHEN oops.[Status] = 11 THEN ofin.CollectAmount ELSE 0 END), 0) AS PickupFailedCollectAmount,
            
            -- Đang trung chuyển
            SUM(CASE WHEN oops.PhysicalStatus IN (13,20,30) THEN 1 ELSE 0 END) AS Transporting,
            ISNULL(SUM(CASE WHEN oops.PhysicalStatus IN (13,20,30) THEN ofin.Fee + ofin.PickupSubFee + ofin.DeliverySubFee + ofin.CodFee + ofin.InsuranceFee + ofin.ReturnFee - ofin.Discount ELSE 0 END), 0) AS TransportingRevenue,
            ISNULL(SUM(CASE WHEN oops.PhysicalStatus IN (13,20,30) THEN ofin.CollectAmount ELSE 0 END), 0) AS TransportingCollectAmount,
            
            -- Đang giao hàng
            SUM(CASE WHEN oops.PhysicalStatus IN (51,53) THEN 1 ELSE 0 END) AS Delivering,
            ISNULL(SUM(CASE WHEN oops.PhysicalStatus IN (51,53) THEN ofin.Fee + ofin.PickupSubFee + ofin.DeliverySubFee + ofin.CodFee + ofin.InsuranceFee + ofin.ReturnFee - ofin.Discount ELSE 0 END), 0) AS DeliveringRevenue,
            ISNULL(SUM(CASE WHEN oops.PhysicalStatus IN (51,53) THEN ofin.CollectAmount ELSE 0 END), 0) AS DeliveringCollectAmount,
            
            -- Giao hàng thất bại
            SUM(CASE WHEN oops.[Status] = 51 THEN 1 ELSE 0 END) AS DeliveryFailed,
            ISNULL(SUM(CASE WHEN oops.[Status] = 51 THEN ofin.Fee + ofin.PickupSubFee + ofin.DeliverySubFee + ofin.CodFee + ofin.InsuranceFee + ofin.ReturnFee - ofin.Discount ELSE 0 END), 0) AS DeliveryFailedRevenue,
            ISNULL(SUM(CASE WHEN oops.[Status] = 51 THEN ofin.CollectAmount ELSE 0 END), 0) AS DeliveryFailedCollectAmount,
            
            -- Giao hàng thành công
            SUM(CASE WHEN oops.PhysicalStatus = 55 THEN 1 ELSE 0 END) AS DeliverySuccess,
            ISNULL(SUM(CASE WHEN oops.PhysicalStatus = 55 THEN ofin.Fee + ofin.PickupSubFee + ofin.DeliverySubFee + ofin.CodFee + ofin.InsuranceFee + ofin.ReturnFee - ofin.Discount ELSE 0 END), 0) AS DeliverySuccessRevenue,
            ISNULL(SUM(CASE WHEN oops.PhysicalStatus = 55 THEN ofin.CollectAmount ELSE 0 END), 0) AS DeliverySuccessCollectAmount,
            
            -- Đã hủy
            SUM(CASE WHEN oops.[Status] = 8 THEN 1 ELSE 0 END) AS Cancelled,
            ISNULL(SUM(CASE WHEN oops.[Status] = 8 THEN ofin.Fee + ofin.PickupSubFee + ofin.DeliverySubFee + ofin.CodFee + ofin.InsuranceFee + ofin.ReturnFee - ofin.Discount ELSE 0 END), 0) AS CancelledRevenue,
            ISNULL(SUM(CASE WHEN oops.[Status] = 8 THEN ofin.CollectAmount ELSE 0 END), 0) AS CancelledCollectAmount
            
        FROM Customer c
        INNER JOIN [Order] o ON c.Id = o.CustomerId
        INNER JOIN OrderFin ofin ON o.Id = ofin.Id
        INNER JOIN OrderOps oops ON o.Id = oops.Id
        
        WHERE o.IsDeleted = 0
            AND o.CreatedTime >= @StartDate
            AND o.CreatedTime <= @EndDate
            AND (@CustomerId IS NULL OR c.Id = @CustomerId)
            AND (@CustomerIds IS NULL OR c.Id IN (SELECT CustomerId FROM @CustomerIdTable))
            AND (@SaleId IS NULL OR o.SaleId = @SaleId)
    )
    -- Kết hợp và phân trang
    SELECT 
        *,
        COUNT(*) OVER() AS TotalRecords
    FROM (
        SELECT * FROM CustomerData
        UNION ALL
        SELECT * FROM TotalRow
    ) AS Combined
    ORDER BY 
        CASE WHEN CustomerId = 0 THEN 1 ELSE 0 END,  -- Dòng tổng cộng luôn ở cuối
        TotalRevenue DESC,  -- Sắp xếp theo tổng doanh số giảm dần
        CustomerId
    OFFSET (@PageNumber - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END
GO
