USE [Ladaer2]
GO
/****** Object:  StoredProcedure [dbo].[sp_GetReconciliableOrdersByCustomer]    Script Date: 07/12/2025 10:30:00 CH ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      LADA System
-- Create date: 2025-12-07
-- Description: Lấy danh sách đơn hàng cần đối soát của một khách hàng cụ thể
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[sp_GetReconciliableOrdersByCustomer]
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT 
        o.Id AS OrderId,
        o.Code AS OrderCode,
        o.PartnerCode,
        o.Note,
        
        -- Thông tin khách hàng
        c.UserName AS CustomerCode,
        
        -- Thông tin tài chính
        ofin.CollectAmount AS AppliedCodAmount,
        ofin.Fee + ofin.PickupSubFee + ofin.DeliverySubFee + ofin.CodFee + ofin.InsuranceFee + ofin.ReturnFee - ofin.Discount AS AppliedFeeAmount,
        ofin.CollectAmount - (ofin.Fee + ofin.PickupSubFee + ofin.DeliverySubFee + ofin.CodFee + ofin.InsuranceFee + ofin.ReturnFee - ofin.Discount) AS NetAmount,
        ofin.ChargeWeight,
        ofin.Fee AS ShippingFee,
        ofin.CodCollectedTime,
        
        -- Thông tin thời gian
        ott.DeliveryDate AS DeliveredDate,
        
        -- Thông tin trạng thái
        oops.[Status] AS OrderStatus,
        oops.PhysicalStatus AS PhysicalStatus,
        
        -- Thông tin địa chỉ người gửi
        CONCAT(
            srcAddr.Address1,
            CASE WHEN srcWard.[Name] IS NOT NULL THEN CONCAT(N', ', srcWard.[Name]) ELSE N'' END,
            CASE WHEN srcDistrict.[Name] IS NOT NULL THEN CONCAT(N', ', srcDistrict.[Name]) ELSE N'' END,
            CASE WHEN srcCity.[Name] IS NOT NULL THEN CONCAT(N', ', srcCity.[Name]) ELSE N'' END
        ) AS SenderAddress,
        srcAddr.[Name] AS SenderName,
        srcAddr.PhoneNumber1 AS SenderPhone,
        
        -- Thông tin địa chỉ người nhận
        CONCAT(
            destAddr.Address1,
            CASE WHEN destWard.[Name] IS NOT NULL THEN CONCAT(N', ', destWard.[Name]) ELSE N'' END,
            CASE WHEN destDistrict.[Name] IS NOT NULL THEN CONCAT(N', ', destDistrict.[Name]) ELSE N'' END,
            CASE WHEN destCity.[Name] IS NOT NULL THEN CONCAT(N', ', destCity.[Name]) ELSE N'' END
        ) AS DeliveryAddress,
        destAddr.[Name] AS ReceiverName,
        destAddr.PhoneNumber1 AS ReceiverPhone,
        
        -- Thông tin sản phẩm (aggregate từ OrderPackage)
        (
            SELECT STRING_AGG(
                CONCAT( op.Name, N' - ', op.Quantity, N' kiện ', N' - ', CAST(op.Weight / 1000.0 AS DECIMAL(10,2)), N'kg'), 
                CHAR(10)  -- newline character
            ) WITHIN GROUP (ORDER BY op.Id)
            FROM OrderPackage op
            WHERE op.OrderId = o.Id AND op.IsDeleted = 0
        ) AS ProductInfo
        
    FROM [Order] o
    INNER JOIN Customer c ON o.CustomerId = c.Id
    INNER JOIN OrderFin ofin ON o.Id = ofin.Id
    INNER JOIN OrderOps oops ON o.Id = oops.Id
    LEFT JOIN OrderTimeTrack ott ON o.Id = ott.Id
    LEFT JOIN OrderAddress srcAddr ON o.Id = srcAddr.OrderId AND srcAddr.AddressType = 1 -- AddressType.Source = 1
    LEFT JOIN OrderAddress destAddr ON o.Id = destAddr.OrderId AND destAddr.AddressType = 2 -- AddressType.Dest = 2
    -- JOIN để lấy tên tỉnh/quận/phường cho địa chỉ gửi (từ bảng Location)
    LEFT JOIN [Location] srcCity ON srcAddr.CityId = srcCity.Id AND srcCity.Level = 1 -- City
    LEFT JOIN [Location] srcDistrict ON srcAddr.DistrictId = srcDistrict.Id AND srcDistrict.Level = 2 -- District
    LEFT JOIN [Location] srcWard ON srcAddr.WardId = srcWard.Id AND srcWard.Level = 3 -- Ward
    -- JOIN để lấy tên tỉnh/quận/phường cho địa chỉ nhận (từ bảng Location)
    LEFT JOIN [Location] destCity ON destAddr.CityId = destCity.Id AND destCity.Level = 1 -- City
    LEFT JOIN [Location] destDistrict ON destAddr.DistrictId = destDistrict.Id AND destDistrict.Level = 2 -- District
    LEFT JOIN [Location] destWard ON destAddr.WardId = destWard.Id AND destWard.Level = 3 -- Ward
    
    WHERE o.CustomerId = @CustomerId
        AND o.IsDeleted = 0
        
        -- Loại trừ các đơn hủy ra
        AND (oops.[Status] NOT IN (8)) 
        
        -- Chỉ lấy các đơn đã được công ty thu tiền
        AND (
            -- COD orders at Head stage (công ty đã thu được tiền COD)
            (ofin.CollectAmount > 0 AND ofin.CodStage = 9)
            OR
            -- BankTransfer orders collected by Head (công ty đã thu được tiền chuyển khoản)
            (ofin.PayFeeType = 3 AND ofin.TransferFeeStage = 1)
        )
        
        -- Loại trừ đơn đã nằm trong phiếu đối soát khách hàng (trừ phiếu bị từ chối)
        AND NOT EXISTS (
            SELECT 1 FROM CustomerReconciliationSlipDetails crd
            INNER JOIN CustomerReconciliationSlips crs ON crd.CustomerReconciliationSlipId = crs.Id
            WHERE crd.OrderId = o.Id
                AND crs.[Status] != 8 -- Rejected = 8
                AND crs.IsDeleted = 0
        )
        
        -- Loại trừ đơn đã nằm trong phiếu đối soát đối tác (trừ phiếu bị từ chối hoặc đã hủy)
        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) -- Cancelled = 4, Rejected = 8
                AND r.IsDeleted = 0
        )
        
        -- Loại trừ đơn đã nằm trong phiếu chuyển tiền (trừ phiếu bị từ chối hoặc đã hủy)
        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) -- Cancelled = 3, Rejected = 8
                AND tpb.IsDeleted = 0
        )
    
    ORDER BY o.CreatedTime DESC;
END
