GO
/****** Object:  StoredProcedure [dbo].[sp_SalesDetails]    Script Date: 19-04-2025 21:23:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_SalesDetails]    
    @FromDate datetime,
    @ToDate datetime,
    @SalesMan varchar(500),
    @Custcode nvarchar(500),
    @InvoiceType nvarchar(1000),
    @SalesType nvarchar(1000) ,
	@Itemcode nvarchar(1000),
	@CodeCustomer nvarchar(1000),
	@NameSalesman nvarchar(1000),
	@BranchCode nvarchar(1000)
AS    
BEGIN    
    DECLARE @SalesData TABLE (
        EntryDate nvarchar(500),
        InvNo 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),
		SR nvarchar(500)
    );
	 --SET @Custcode = NULLIF(LTRIM(RTRIM(@Custcode)), '');
    IF @SalesType IN ('1', '3')
    BEGIN
        INSERT INTO @SalesData
        SELECT 
         FORMAT(i.InvoiceDate, 'dd/MM/yyyy') AS EntryDate,
            ISNULL(id.InvNo, '') AS InvNo,
            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,
			i.SR as SR
        FROM 
            InvoiceItem id 
        LEFT JOIN 
            Invoice i ON i.Id = id.SubId
      
        WHERE  
            CONVERT(date, i.InvoiceDate) BETWEEN @FromDate AND @ToDate 
    
	
	 AND (id.ItemCode = @Itemcode OR @Itemcode IS NULL OR @Itemcode = '')  
           
            AND (@InvoiceType = 'all' OR i.InvType = @InvoiceType)
			 and isnull(i.CustomerCode,'')=isnull(@CodeCustomer,isnull(i.CustomerCode,''))
	
	 and (i.Attended = @NameSalesman OR @NameSalesman IS NULL OR @NameSalesman = '')  
	 and(i.BranchCode = @BranchCode OR @BranchCode IS NULL OR @BranchCode = '' OR @BranchCode = '1')  
    END;

    IF @SalesType IN ('2', '3')
    BEGIN
        INSERT INTO @SalesData
        SELECT 
           FORMAT(ir.InvoiceDate, 'dd/MM/yyyy') AS EntryDate,
            ISNULL(ird.InvoiceRetNo, '') 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,
			ir.SR as SR
        FROM 
            InvoiceReturnItem ird 
        LEFT JOIN 
            InvoiceReturn 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 isnull(ir.CustomerCode,'')=isnull(@CodeCustomer,isnull(ir.CustomerCode,''))
	
	 and (ir.Attended = @NameSalesman OR @NameSalesman IS NULL OR @NameSalesman = '')  
            AND (@InvoiceType = 'all' OR ir.InvType = @InvoiceType)
			 	 and(ir.BranchCode = @BranchCode OR @BranchCode IS NULL OR @BranchCode = '' OR @BranchCode = '1')  ;
    END;

    SELECT 
        EntryDate,
        InvNo,
        ItemCode,
        ItemName,
        Qty,
        Unit,
        Rate,
        Amount,
        VATPERCTAGE,
        VATAMT,
		SR
    FROM @SalesData
    ORDER BY SR asc,EntryDate, InvNo;
END;