USE [Ladaer2]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER PROCEDURE [dbo].[sp_GetOrdersWithoutSourceCashier]
    @FromDate DATETIME = NULL,
    @ToDate DATETIME = NULL,
    @PageNumber INT = 1,
    @PageSize INT = 50
AS
BEGIN
    SET NOCOUNT ON;

    -- Tạo temp table để lưu kết quả
    SELECT
        o.Id,
        o.Code,
        o.CreatedTime,
        o.CustomerId,
        ofin.PayFeeType,
        ops.Status,
        ops.PhysicalStatus,
        ofin.SourceFee,
        (ofin.Fee + ofin.PickupSubFee + ofin.DeliverySubFee + ofin.CodFee + ofin.ReturnFee + ofin.InsuranceFee - ofin.Discount) AS TotalFee,
        ofin.SourceFeeCashierId,
        ofin.SourceFeeCashierUsername,
        ofin.SourceFeeStage
    INTO #OrdersWithRisk
    FROM [dbo].[Order] o
    INNER JOIN [dbo].[OrderFin] ofin ON o.Id = ofin.Id
    INNER JOIN [dbo].[OrderOps] ops ON o.Id = ops.Id
    WHERE ofin.PayFeeType = 1  -- SenderPay
        AND ops.Status > 1       -- Status > New
        AND ops.Status <> 8      -- Status <> Cancelled
        AND (ofin.SourceFeeCashierId = 0 OR ofin.SourceFeeCashierId IS NULL)
        AND o.IsDeleted = 0
        AND (@FromDate IS NULL OR o.CreatedTime >= @FromDate)
        AND (@ToDate IS NULL OR o.CreatedTime <= @ToDate);

    -- Result set 1: Tổng số bản ghi
    SELECT COUNT(*) AS TotalRecords
    FROM #OrdersWithRisk;

    -- Result set 2: Danh sách có phân trang
    SELECT
        owr.Id,
        owr.Code,
        owr.CreatedTime,
        owr.CustomerId,
        owr.PayFeeType,
        owr.Status,
        owr.PhysicalStatus,
        owr.SourceFee,
        owr.TotalFee,
        owr.SourceFeeCashierId,
        owr.SourceFeeCashierUsername,
        owr.SourceFeeStage,
        -- Thông tin Customer
        c.FullName as CustomerName,
        c.PhoneNumber as CustomerPhone,
        -- Thông tin người gửi
        ISNULL(sender.Name, '') as SenderName,
        ISNULL(sender.PhoneNumber1, '') as SenderPhone,
        ISNULL(sender.Address1, '') as SenderAddress
    FROM #OrdersWithRisk owr
    LEFT JOIN [dbo].[Customer] c ON owr.CustomerId = c.Id
    LEFT JOIN [dbo].[OrderAddress] sender ON owr.Id = sender.OrderId AND sender.AddressType = 1
    ORDER BY owr.CreatedTime DESC
    OFFSET (@PageNumber - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;

    -- Cleanup
    DROP TABLE #OrdersWithRisk;
END
GO
