USE [Ladaer2]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER PROCEDURE [dbo].[sp_GetOrdersBothPayWithoutCashier]
    @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.DestFee,
        (ofin.Fee + ofin.PickupSubFee + ofin.DeliverySubFee + ofin.CodFee + ofin.ReturnFee + ofin.InsuranceFee - ofin.Discount) AS TotalFee,
        ofin.SourceFeeCashierId,
        ofin.SourceFeeCashierUsername,
        ofin.SourceFeeStage,
        ofin.DestFeeCashierId,
        ofin.DestFeeCashierUsername,
        ofin.DestFeeStage
    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 = 3  -- BothPay
        AND ops.Status = 55      -- Status = Delivered
        AND ops.PhysicalStatus = 55  -- PhysicalStatus = Delivered
        AND (
            (ofin.SourceFeeCashierId = 0 OR ofin.SourceFeeCashierId IS NULL)
            OR (ofin.DestFeeCashierId = 0 OR ofin.DestFeeCashierId 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.DestFee,
        owr.TotalFee,
        owr.SourceFeeCashierId,
        owr.SourceFeeCashierUsername,
        owr.SourceFeeStage,
        owr.DestFeeCashierId,
        owr.DestFeeCashierUsername,
        owr.DestFeeStage,
        -- 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,
        -- Thông tin người nhận
        ISNULL(receiver.Name, '') as ReceiverName,
        ISNULL(receiver.PhoneNumber1, '') as ReceiverPhone,
        ISNULL(receiver.Address1, '') as ReceiverAddress
    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
    LEFT JOIN [dbo].[OrderAddress] receiver ON owr.Id = receiver.OrderId AND receiver.AddressType = 2
    ORDER BY owr.CreatedTime DESC
    OFFSET (@PageNumber - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;

    -- Cleanup
    DROP TABLE #OrdersWithRisk;
END
GO
