GO
/****** Object:  StoredProcedure [dbo].[sp_PurchaseDetails]    Script Date: 26-02-2025 18:18:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_PurchaseDetails] 
	@FromDate datetime,
    @ToDate datetime,
    @purchaser varchar(150),
    @Suppcode nvarchar(500),
    @InvoiceType nvarchar(100),
    @SalesType nvarchar(100) ,
	@Itemcode nvarchar(100),
	@CodeCustomer nvarchar(1000),
	@NameSalesman nvarchar(1000),
	@BranchCode nvarchar(1000)
AS    
BEGIN    
    DECLARE @SalesData TABLE (
        EntryDate date,
        DocumentNo nvarchar(500),
        ItemCode nvarchar(500),
        ItemName nvarchar(500),
        Qty nvarchar(500),
        Unit nvarchar(500),
        Rate decimal(18, 2),
        Amount decimal(18, 2),
        VATPERCTAGE decimal(18, 2),
        VATAMT decimal(18, 2),
		pr nvarchar(500)
    );
	 --SET @Custcode = NULLIF(LTRIM(RTRIM(@Custcode)), '');
    IF @SalesType IN ('1', '3')
    BEGIN
        INSERT INTO @SalesData
        SELECT 
            CONVERT(date, i.EntryDate) AS EntryDate,
            ISNULL(id.DocumentNo, '') AS DocumentNo,
            ISNULL(id.ItemCode, '') AS ItemCode,
            ISNULL(id.ItemName, '') AS ItemName,
            ISNULL(id.Qty, '') AS Qty,
            ISNULL(id.Unit, '') AS Unit,
            ISNULL(id.Rate, 0) AS Rate,
            ISNULL(id.Amount, 0) AS Amount,
            ISNULL(id.VATPER, 0) AS VATPERCTAGE,
            ISNULL(id.VATAMT, 0) AS VATAMT,
			   ISNULL(i.pr, 'Purchase') AS pr
        FROM 
            PurchaseItem id 
        LEFT JOIN 
            Purchase i ON i.Id = id.SubId
      
        WHERE  
            CONVERT(date, i.EntryDate) BETWEEN @FromDate AND @ToDate 
    
	
	 AND (id.ItemCode = @Itemcode OR @Itemcode IS NULL OR @Itemcode = '')  
           
            AND (@InvoiceType = 'all' OR i.BillType = @InvoiceType)
			and (i.SupplierCode = @Suppcode OR @Suppcode IS NULL OR @Suppcode = '')
			 and (i.BranchCode = @BranchCode OR @BranchCode IS NULL OR @BranchCode = '' or @BranchCode='1')  
	
	 and (i.Purchaser = @NameSalesman OR @NameSalesman IS NULL OR @NameSalesman = '') 
	 and i.isDeleted='N';
	 
    END;

    IF @SalesType IN ('2', '3')
    BEGIN
        INSERT INTO @SalesData
        SELECT 
            CONVERT(date, ir.EntryDate) AS EntryDate,
			
            ISNULL(ird.DocumentNo, '') AS InvNo,
            ISNULL(ird.ItemCode, '') AS ItemCode,
            ISNULL(ird.ItemName, '') AS ItemName,
            ISNULL(ird.Qty, '') AS Qty,
            ISNULL(ird.Unit, '') AS Unit,
            ISNULL(ird.Rate, 0) AS Rate,
            ISNULL(ird.Amount, 0) AS Amount,
            ISNULL(ird.VATPER, 0) AS VATPERCTAGE,
            ISNULL(ird.VATAMT, 0) AS VATAMT,
			  ISNULL(ir.pr, 'Returns') AS pr
        FROM 
            PurchaseReturnItem ird 
        LEFT JOIN 
            PurchaseReturn ir ON ird.SubId= ir.id
    
        WHERE  
            CONVERT(date, ir.EntryDate) BETWEEN @FromDate AND @ToDate 
			AND (ird.ItemCode = @Itemcode OR @Itemcode IS NULL OR @Itemcode = '')  
           and (ir.SupplierCode = @Suppcode OR @Suppcode IS NULL OR @Suppcode = '')
	
		 and (ir.Purchaser = @purchaser OR @purchaser IS NULL OR @purchaser = '')  
		  and (ir.BranchCode = @BranchCode OR @BranchCode IS NULL OR @BranchCode = '' or @BranchCode='1')  
            AND (@InvoiceType = 'all' OR ir.BillType = @InvoiceType);
			
    END;

    SELECT 
        EntryDate,
        DocumentNo,
        ItemCode,
        ItemName,
        Qty,
        Unit,
        Rate,
        Amount,
        VATPERCTAGE,
        VATAMT,
		pr
    FROM @SalesData
    ORDER BY EntryDate, DocumentNo;
END;