USE [Ladaer2]
GO
/****** Object:  StoredProcedure [dbo].[sp_SearchCustomersForReconciliation]    Script Date: 07/12/2025 10:12:09 SA ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      LADA System
-- Create date: 2025-11-22
-- Description: Tìm kiếm khách hàng có đơn hàng cần đối soát
-- =============================================
CREATE OR ALTER   PROCEDURE [dbo].[sp_SearchCustomersForReconciliation]
    @SearchKeyword NVARCHAR(256) = NULL,
    @FromDate DATETIMEOFFSET = NULL,
    @ToDate DATETIMEOFFSET = NULL,
    @ManagerId INT = NULL,
    @PageNumber INT = 1,
    @PageSize INT = 200
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Mặc định lấy 30 ngày gần nhất nếu không truyền
    IF @FromDate IS NULL
    BEGIN
        SET @FromDate = DATEADD(DAY, -30, SYSDATETIMEOFFSET());
    END
    
    IF @ToDate IS NULL
    BEGIN
        SET @ToDate = SYSDATETIMEOFFSET();
    END
    
    ;WITH CustomerOrders AS (
        SELECT 
            c.Id AS CustomerId,
            c.FullName,
            c.PhoneNumber,
            c.Email,
            c.UserName,
            
            -- Đếm số đơn COD/DeductCod chưa đối soát
            COUNT(CASE 
                WHEN ofin.CollectAmount > 0 
                    AND ofin.CodStage = 9 
                THEN 1 
            END) AS CodOrderCount,
            
            -- Đếm số đơn BankTransfer chưa đối soát  
            COUNT(CASE 
                WHEN ofin.PayFeeType = 3 
                    AND ofin.TransferFeeStage = 1 
                THEN 1 
            END) AS BankTransferOrderCount,
            
            -- Tổng số tiền COD chưa đối soát
            ISNULL(SUM(CASE 
                WHEN ofin.CollectAmount > 0 
                    AND ofin.CodStage = 9 
                THEN ofin.CollectAmount 
                ELSE 0 
            END), 0) AS TotalCodAmount,
            
            -- Tổng cước của các đơn COD chưa đối soát
            ISNULL(SUM(CASE 
                WHEN ofin.CollectAmount > 0 
                    AND ofin.CodStage = 9 
                THEN ofin.Fee + ofin.PickupSubFee + ofin.DeliverySubFee + ofin.CodFee + ofin.InsuranceFee + ofin.ReturnFee - ofin.Discount 
                ELSE 0 
            END), 0) AS TotalCodFee,
            
            -- Tổng số tiền TransferFee chưa đối soát
            ISNULL(SUM(CASE 
                WHEN ofin.PayFeeType = 3 
                    AND ofin.TransferFeeStage = 1 
                THEN ofin.TransferFee 
                ELSE 0 
            END), 0) AS TotalTransferFee,
            
            -- Ngày đơn cũ nhất
            MIN(o.CreatedTime) AS OldestOrderDate,
            
            -- Ngày đơn mới nhất
            MAX(o.CreatedTime) AS LatestOrderDate
            
        FROM Customer c
        INNER JOIN [Order] o ON c.Id = o.CustomerId
        INNER JOIN OrderFin ofin ON o.Id = ofin.Id
        WHERE o.CreatedTime >= @FromDate
            AND o.CreatedTime <= @ToDate
            AND o.IsDeleted = 0
            AND c.IsDeleted = 0
            AND c.IsDefaultCustomer = 0
            AND (
                -- COD orders at Head stage
                (ofin.CollectAmount > 0 AND ofin.CodStage = 9)
                OR
                -- BankTransfer orders not collected
                (ofin.PayFeeType = 3 AND ofin.TransferFeeStage = 1)
            )
            AND NOT EXISTS (
                SELECT 1 FROM CustomerReconciliationSlipDetails cro
                INNER JOIN CustomerReconciliationSlips cr ON cro.CustomerReconciliationSlipId = cr.Id
                WHERE cro.OrderId = o.Id
                    AND cr.[Status] != 8
                    AND cr.IsDeleted = 0
            )
            AND NOT EXISTS (
                SELECT 1 FROM ReconciliationOrders ro
                INNER JOIN Reconciliations r ON ro.ReconciliationId = r.Id
                WHERE ro.OrderId = o.Id
                    AND r.[Status] NOT IN (4, 8)
                    AND r.IsDeleted = 0
            )
            AND NOT EXISTS (
                SELECT 1 FROM TransferPaymentBillOrders tpbo
                INNER JOIN TransferPaymentBills tpb ON tpbo.TransferPaymentBillId = tpb.Id
                WHERE tpbo.OrderId = o.Id
                    AND tpb.[Status] NOT IN (3, 8)
                    AND tpb.IsDeleted = 0
            )
            AND (
                @ManagerId IS NULL OR c.ManagerId = @ManagerId
            )
            AND (
                @SearchKeyword IS NULL
                OR c.FullName LIKE N'%' + @SearchKeyword + '%'
                OR c.PhoneNumber LIKE '%' + @SearchKeyword + '%'
                OR c.Email LIKE '%' + @SearchKeyword + '%'
                OR c.UserName LIKE '%' + @SearchKeyword + '%'
            )
        GROUP BY c.Id, c.FullName, c.PhoneNumber, c.Email, c.UserName
        HAVING COUNT(*) > 0 -- Ít nhất 1 đơn chưa đối soát
    )
    SELECT 
        CustomerId,
        FullName,
        PhoneNumber,
        Email,
        UserName,
        CodOrderCount,
        BankTransferOrderCount,
        CodOrderCount + BankTransferOrderCount AS TotalOrderCount,
        TotalCodAmount,
        TotalCodFee,
        TotalTransferFee,
        TotalCodAmount - TotalCodFee - TotalTransferFee AS EstimatedNetAmount,
        OldestOrderDate,
        LatestOrderDate,
        
        -- Tổng số bản ghi để phân trang
        COUNT(*) OVER() AS TotalRecords
        
    FROM CustomerOrders
    ORDER BY LatestOrderDate DESC
    OFFSET (@PageNumber - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END
