USE [Ladaer2]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_GetCashierBalance]
    @PageNumber INT = 1,
    @PageSize INT = 10,
    @Keyword NVARCHAR(100) = NULL,
    @CodCurrentStage INT = NULL,
    @SourceFeeCurrentStage INT = NULL,
    @DestFeeCurrentStage INT = NULL
AS
BEGIN
    SET NOCOUNT ON;

    -- Tạo temp table để lưu kết quả tính toán
    CREATE TABLE #UserSummary (
        Id INT,
        FullName NVARCHAR(255),
        UserName NVARCHAR(255),
        CodAmount DECIMAL(18, 2),
        SourceFeeAmount DECIMAL(18, 2),
        DestFeeAmount DECIMAL(18, 2),
        ExpectedCodAmount DECIMAL(18, 2),
        ExpectedSourceFeeAmount DECIMAL(18, 2),
        ExpectedDestFeeAmount DECIMAL(18, 2)
    );

    -- Tính toán và insert vào temp table
    INSERT INTO #UserSummary
    SELECT
        u.Id,
        u.FullName,
        u.UserName,
        -- TỔNG TIỀN ĐANG CẦM (HELD)
        SUM(CASE 
            WHEN fin.IsDeleted = 0 
                AND fin.CollectAmount > 0 
                AND fin.CodCashierId = u.Id 
                AND (@CodCurrentStage IS NULL OR fin.CodStage < @CodCurrentStage)
            THEN fin.CollectAmount 
            ELSE 0 
        END) as CodAmount,
        SUM(CASE 
            WHEN fin.IsDeleted = 0 
                AND fin.SourceFee > 0 
                AND fin.SourceFeeCashierId = u.Id 
                AND (@SourceFeeCurrentStage IS NULL OR fin.SourceFeeStage < @SourceFeeCurrentStage)
            THEN fin.SourceFee 
            ELSE 0 
        END) as SourceFeeAmount,
        SUM(CASE 
            WHEN fin.IsDeleted = 0 
                AND fin.DestFee > 0 
                AND fin.DestFeeCashierId = u.Id 
                AND (@DestFeeCurrentStage IS NULL OR fin.DestFeeStage < @DestFeeCurrentStage)
            THEN fin.DestFee 
            ELSE 0 
        END) as DestFeeAmount,
        -- TỔNG TIỀN DỰ KIẾN THU (EXPECTED)
        SUM(CASE 
            WHEN fin.IsDeleted = 0 
                AND ops.PicId = u.Id 
                AND fin.CollectAmount > 0 
                AND fin.CodStage = 1 
                AND ops.PhysicalStatus IN (51, 53)
            THEN fin.CollectAmount 
            ELSE 0 
        END) as ExpectedCodAmount,
        SUM(CASE 
            WHEN fin.IsDeleted = 0 
                AND ops.PicId = u.Id 
                AND fin.SourceFee > 0 
                AND fin.SourceFeeStage = 1 
                AND ops.PhysicalStatus IN (11, 12)
            THEN fin.SourceFee 
            ELSE 0 
        END) as ExpectedSourceFeeAmount,
        SUM(CASE 
            WHEN fin.IsDeleted = 0 
                AND ops.PicId = u.Id 
                AND fin.DestFee > 0 
                AND fin.DestFeeStage = 1 
                AND ops.PhysicalStatus IN (51, 53)
            THEN fin.DestFee 
            ELSE 0 
        END) as ExpectedDestFeeAmount
    FROM [dbo].[User] u
    LEFT JOIN [dbo].[OrderFin] fin ON (
        (fin.IsDeleted = 0 AND fin.CollectAmount > 0 AND fin.CodCashierId = u.Id AND (@CodCurrentStage IS NULL OR fin.CodStage < @CodCurrentStage))
        OR (fin.IsDeleted = 0 AND fin.SourceFee > 0 AND fin.SourceFeeCashierId = u.Id AND (@SourceFeeCurrentStage IS NULL OR fin.SourceFeeStage < @SourceFeeCurrentStage))
        OR (fin.IsDeleted = 0 AND fin.DestFee > 0 AND fin.DestFeeCashierId = u.Id AND (@DestFeeCurrentStage IS NULL OR fin.DestFeeStage < @DestFeeCurrentStage))
    )
    LEFT JOIN [dbo].[OrderOps] ops ON fin.Id = ops.Id AND (
        (ops.PicId = u.Id AND fin.CollectAmount > 0 AND fin.CodStage = 1 AND ops.PhysicalStatus IN (51, 53))
        OR (ops.PicId = u.Id AND fin.SourceFee > 0 AND fin.SourceFeeStage = 1 AND ops.PhysicalStatus IN (11, 12))
        OR (ops.PicId = u.Id AND fin.DestFee > 0 AND fin.DestFeeStage = 1 AND ops.PhysicalStatus IN (51, 53))
    )
    WHERE u.Id NOT IN (37, 19)
        AND (@Keyword IS NULL
            OR u.UserName LIKE N'%' + @Keyword + N'%'
            OR u.FullName LIKE N'%' + @Keyword + N'%')
    GROUP BY u.Id, u.FullName, u.UserName;

    -- Result set 1: Trả về tổng số bản ghi
    SELECT COUNT(*) AS TotalRecords
    FROM #UserSummary
    WHERE (CodAmount > 0 OR SourceFeeAmount > 0 OR DestFeeAmount > 0 
        OR ExpectedCodAmount > 0 OR ExpectedSourceFeeAmount > 0 OR ExpectedDestFeeAmount > 0);

    -- Result set 2: Trả về danh sách dữ liệu với phân trang
    SELECT
        us.Id,
        us.FullName,
        us.UserName,
        us.CodAmount,
        us.SourceFeeAmount,
        us.DestFeeAmount,
        us.ExpectedCodAmount,
        us.ExpectedSourceFeeAmount,
        us.ExpectedDestFeeAmount,
        -- Danh sách đơn hàng dạng JSON
        (
            SELECT
                o.Id,
                o.Code,
                ops_sub.PhysicalStatus,
                -- Thông tin người gửi
                ISNULL(sender.Name, '') as SenderName,
                ISNULL(sender.PhoneNumber1, '') as SenderPhone,
                ISNULL(sender.Address1, '') as SenderAddress,
                -- Thông tin người nhận
                ISNULL(receiver.Name, '') as RecipientName,
                ISNULL(receiver.PhoneNumber1, '') as RecipientPhone,
                ISNULL(receiver.Address1, '') as RecipientAddress,
                -- Thông tin tiền ĐANG CẦM (Held)
                CASE WHEN fin2.IsDeleted=0 AND fin2.CodCashierId = us.Id AND (@CodCurrentStage IS NULL OR fin2.CodStage < @CodCurrentStage) THEN fin2.CollectAmount ELSE 0 END as CodAmount,
                CASE WHEN fin2.IsDeleted=0 AND fin2.SourceFeeCashierId = us.Id AND (@SourceFeeCurrentStage IS NULL OR fin2.SourceFeeStage < @SourceFeeCurrentStage) THEN fin2.SourceFee ELSE 0 END as SourceFee,
                CASE WHEN fin2.IsDeleted=0 AND fin2.DestFeeCashierId = us.Id AND (@DestFeeCurrentStage IS NULL OR fin2.DestFeeStage < @DestFeeCurrentStage) THEN fin2.DestFee ELSE 0 END as DestFee,
                -- Thông tin tiền DỰ KIẾN THU (Expected)
                CASE WHEN fin2.IsDeleted=0 AND ops_sub.PicId = us.Id AND fin2.CollectAmount > 0 AND fin2.CodStage = 1 AND ops_sub.PhysicalStatus IN (51, 53) THEN fin2.CollectAmount ELSE 0 END as ExpectedCod,
                CASE WHEN fin2.IsDeleted=0 AND ops_sub.PicId = us.Id AND fin2.SourceFee > 0 AND fin2.SourceFeeStage = 1 AND ops_sub.PhysicalStatus IN (11, 12) THEN fin2.SourceFee ELSE 0 END as ExpectedSourceFee,
                CASE WHEN fin2.IsDeleted=0 AND ops_sub.PicId = us.Id AND fin2.DestFee > 0 AND fin2.DestFeeStage = 1 AND ops_sub.PhysicalStatus IN (51, 53) THEN fin2.DestFee ELSE 0 END as ExpectedDestFee
            FROM [dbo].[Order] o
            INNER JOIN [dbo].[OrderFin] fin2 ON o.Id = fin2.Id
            INNER JOIN [dbo].[OrderOps] ops_sub ON o.Id = ops_sub.Id
            LEFT JOIN [dbo].[OrderAddress] sender ON o.Id = sender.OrderId AND sender.AddressType = 1
            LEFT JOIN [dbo].[OrderAddress] receiver ON o.Id = receiver.OrderId AND receiver.AddressType = 2
            WHERE fin2.IsDeleted=0 AND (
                (fin2.CodCashierId = us.Id AND fin2.CollectAmount > 0 AND (@CodCurrentStage IS NULL OR fin2.CodStage < @CodCurrentStage))
                OR (fin2.SourceFeeCashierId = us.Id AND fin2.SourceFee > 0 AND (@SourceFeeCurrentStage IS NULL OR fin2.SourceFeeStage < @SourceFeeCurrentStage))
                OR (fin2.DestFeeCashierId = us.Id AND fin2.DestFee > 0 AND (@DestFeeCurrentStage IS NULL OR fin2.DestFeeStage < @DestFeeCurrentStage))
                OR (ops_sub.PicId = us.Id AND fin2.CollectAmount > 0 AND fin2.CodStage = 1 AND ops_sub.PhysicalStatus IN (51, 53))
                OR (ops_sub.PicId = us.Id AND fin2.SourceFee > 0 AND fin2.SourceFeeStage = 1 AND ops_sub.PhysicalStatus IN (11, 12))
                OR (ops_sub.PicId = us.Id AND fin2.DestFee > 0 AND fin2.DestFeeStage = 1 AND ops_sub.PhysicalStatus IN (51, 53))
            )
            FOR JSON PATH
        ) as Orders
    FROM #UserSummary us
    WHERE (us.CodAmount > 0 OR us.SourceFeeAmount > 0 OR us.DestFeeAmount > 0 
        OR us.ExpectedCodAmount > 0 OR us.ExpectedSourceFeeAmount > 0 OR us.ExpectedDestFeeAmount > 0)
    ORDER BY us.FullName
    OFFSET (@PageNumber - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;

    -- Cleanup temp table
    DROP TABLE #UserSummary;
END