GO
/****** Object:  StoredProcedure [dbo].[sp_MatTransferDetailsReport]    Script Date: 05-01-2025 12:15:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_MatTransferDetailsReport]
@FromDate datetime,
@ToDate varchar(50),
@BranchFrom varchar(50),
@BranchTo varchar(50) 
AS  
BEGIN 
select  MaterialTrans.voucherNo as InvNo,MaterialTrans.EntryDate,MaterialTrans.PONo,MaterialTrans.RefNo,MaterialTrans.fromstore as StoreName,MaterialTrans.tostore as StoreName1,MaterialTrans.Attended,MaterialTrans.NetAmount,
MaterialTrans.Remarks,MaterialTransDetails.SlNo,MaterialTransDetails.ItemName,MaterialTransDetails.Qty,MaterialTransDetails.Rate,MaterialTransDetails.Unit,MaterialTransDetails.Amount,MaterialTransDetails.ItemCode,
MaterialTransDetails.ItemName,MaterialTransDetails.Qty,MaterialTransDetails.Rate,
MaterialTransDetails.SalesRate
from  MaterialTrans inner join MaterialTransDetails on MaterialTrans.voucherNo=MaterialTransDetails.DocumentNo  
 where  MaterialTrans.EntryDate between @FromDate and @ToDate and (MaterialTrans.fromstore = @BranchFrom OR @BranchFrom IS NULL OR @BranchFrom = '' OR @BranchFrom='All')   and(MaterialTrans.tostore = @BranchTo OR @BranchTo IS NULL OR @BranchTo = '' OR @BranchTo='All')  
END  



GO
/****** Object:  StoredProcedure [dbo].[sp_MatTransferDetailsOutReport]    Script Date: 05-01-2025 12:15:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_MatTransferDetailsOutReport]
@FromDate datetime,
@ToDate varchar(50),
@BranchFrom varchar(50),
@BranchTo varchar(50) 
AS  
BEGIN 
select  MaterialTransOut.voucherNo as InvNo,MaterialTransOut.EntryDate,MaterialTransOut.PONo,MaterialTransOut.RefNo,MaterialTransOut.fromstore as StoreName,MaterialTransOut.tostore as StoreName1,MaterialTransOut.Attended,MaterialTransOut.NetAmount,
MaterialTransOut.Remarks,MaterialTransOutDetails.SlNo,MaterialTransOutDetails.ItemName,MaterialTransOutDetails.Qty,MaterialTransOutDetails.Rate,MaterialTransOutDetails.Unit,MaterialTransOutDetails.Amount,MaterialTransOutDetails.ItemCode,
MaterialTransOutDetails.ItemName,MaterialTransOutDetails.Qty,MaterialTransOutDetails.Rate

from  MaterialTransOut inner join MaterialTransOutDetails on MaterialTransOut.voucherNo=MaterialTransOutDetails.DocumentNo  
 where  MaterialTransOut.EntryDate between @FromDate and @ToDate and (MaterialTransOut.fromstore = @BranchFrom OR @BranchFrom IS NULL OR @BranchFrom = '' OR @BranchFrom='All')   and(MaterialTransOut.tostore = @BranchTo OR @BranchTo IS NULL OR @BranchTo = '' OR @BranchTo='All')  
END  
go



GO
/****** Object:  StoredProcedure [dbo].[Sp_GetCustomerReceiptDetailEdit]    Script Date: 09-01-2025 19:53:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[Sp_GetCustomerReceiptDetailEdit]
    @VoucherNo NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    -- First SELECT statement
    SELECT  
        CustomerReceiptDetail.InvoiceNo,
        CustomerReceiptDetail.Discount,
        CustomerReceiptDetail.ReceivedAmount,
        ISNULL(CustomerReceiptDetail.Narration, '') AS Narration,
        Invoice.NetAmount AS InvoiceAmount,
        ISNULL((SELECT SUM(ReceivedAmount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = Invoice.InvNo), 0) AS ReceivedAmt,
        FORMAT(InvDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        ISNULL((SELECT SUM(Discount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = Invoice.InvNo), 0) AS DiscountAmt,
        CASE 
            WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
            THEN Invoice.NetAmount
            ELSE (SELECT (Invoice.NetAmount - COALESCE(SUM(ReceivedAmount), 0) - COALESCE(SUM(Discount), 0)) 
                  FROM CustomerReceiptDetail AS CRD2 
                  WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
                    AND CRD2.VoucherNo != @VoucherNo 
                  GROUP BY InvoiceNo)
        END AS TotalReceivedAmount,
        CASE 
            WHEN EXISTS (SELECT 1 FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo AND CRD2.PaidDate > CustomerReceiptDetail.PaidDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        CASE 
            WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
            THEN 0
            ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
                  FROM CustomerReceiptDetail AS CRD2 
                  WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
                    AND CRD2.VoucherNo != @VoucherNo 
                  GROUP BY InvoiceNo)
        END AS ReceivedWithoutInv,
        CASE 
            WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
            THEN 0
            ELSE (SELECT COALESCE(SUM(Discount), 0) 
                  FROM CustomerReceiptDetail AS CRD2 
                  WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
                    AND CRD2.VoucherNo != @VoucherNo 
                  GROUP BY InvoiceNo)
        END AS DiscountWithoutInv
    FROM   
        CustomerReceiptDetail 
    LEFT JOIN  
        Invoice ON CustomerReceiptDetail.InvoiceNo = Invoice.InvNo 
    WHERE  
        CustomerReceiptDetail.VoucherNo = @VoucherNo AND Invoice.NetAmount IS NOT NULL

    UNION ALL

    -- Second SELECT statement
    SELECT  
        CustomerReceiptDetail.InvoiceNo,
        CustomerReceiptDetail.Discount,
        CustomerReceiptDetail.ReceivedAmount,
        ISNULL(CustomerReceiptDetail.Narration, '') AS Narration,
        CustomerOPEntryUpdated.InvoiceAmt AS InvoiceAmount,
        ISNULL((SELECT SUM(ReceivedAmount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo), 0) AS ReceivedAmt,
        FORMAT(InvDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        ISNULL((SELECT SUM(Discount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo), 0) AS DiscountAmt,
        CASE 
            WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
            THEN CustomerOPEntryUpdated.InvoiceAmt
            ELSE (SELECT (CustomerOPEntryUpdated.InvoiceAmt - COALESCE(SUM(ReceivedAmount), 0) - COALESCE(SUM(Discount), 0)) 
                  FROM CustomerReceiptDetail AS CRD2 
                  WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
                    AND CRD2.VoucherNo != @VoucherNo 
                  GROUP BY InvoiceNo)
        END AS TotalReceivedAmount,
        CASE 
            WHEN EXISTS (SELECT 1 FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo AND CRD2.PaidDate > CustomerReceiptDetail.PaidDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        CASE 
            WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
            THEN 0
            ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
                  FROM CustomerReceiptDetail AS CRD2 
                  WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
                    AND CRD2.VoucherNo != @VoucherNo 
                  GROUP BY InvoiceNo)
        END AS ReceivedWithoutInv,
        CASE 
            WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
            THEN 0
            ELSE (SELECT COALESCE(SUM(Discount), 0) 
                  FROM CustomerReceiptDetail AS CRD2 
                  WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
                    AND CRD2.VoucherNo != @VoucherNo 
                  GROUP BY InvoiceNo)
        END AS DiscountWithoutInv
    FROM   
        CustomerReceiptDetail 
    LEFT JOIN  
        CustomerOPEntryUpdated ON CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo 
    WHERE  
        CustomerReceiptDetail.VoucherNo = @VoucherNo AND CustomerOPEntryUpdated.InvoiceAmt IS NOT NULL
END






GO
/****** Object:  StoredProcedure [dbo].[Sp_GetInvoiceDetailForReceipt]    Script Date: 09-01-2025 19:54:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[Sp_GetInvoiceDetailForReceipt]
    @CustomerId NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    -- First SELECT statement
    SELECT 
        Invoice.InvNo AS InvoiceNo, 
        (Invoice.NetAmount - COALESCE(InvoiceReturn.NetAmount, 0) - ISNULL(SUM(CustomerReceiptDetail.ReceivedAmount), 0) - ISNULL(SUM(CustomerReceiptDetail.Discount), 0)) AS BalanceAmount,
        ISNULL(SUM(CustomerReceiptDetail.Discount), 0) AS DiscountAmt, 
        ISNULL(SUM(CustomerReceiptDetail.ReceivedAmount), 0) AS ReceivedAmt,
        Invoice.NetAmount AS InvoiceAmount, 
        FORMAT(Invoice.InvoiceDate, 'dd-MM-yyyy') AS EntryDatefrmtd
    FROM 
        Invoice
        LEFT JOIN CustomerReceiptDetail 
            ON CustomerReceiptDetail.InvoiceNo = Invoice.InvNo
        LEFT JOIN InvoiceReturn 
            ON InvoiceReturn.InvNo = Invoice.InvNo 
            AND InvoiceReturn.InvType = 'Credit'
    WHERE 
        Invoice.InvType = 'Credit' 
        AND Invoice.CustomerCode = @CustomerId
    GROUP BY 
        Invoice.InvNo, 
        Invoice.NetAmount, 
        Invoice.InvoiceDate, 
        InvoiceReturn.NetAmount,
        InvoiceReturn.InvType
    HAVING 
        (Invoice.NetAmount - COALESCE(InvoiceReturn.NetAmount, 0) - ISNULL(SUM(CustomerReceiptDetail.ReceivedAmount), 0) - ISNULL(SUM(CustomerReceiptDetail.Discount), 0)) > 0
    
    UNION ALL

    -- Second SELECT statement
    SELECT 
        InvoiceNo AS InvoiceNo,
        Balance AS BalanceAmount,
        Discount AS DiscountAmt,
        ReceivedAmt AS ReceivedAmt,
        InvoiceAmt AS InvoiceAmount,
        FORMAT(InvoiceDate, 'dd-MM-yyyy') AS EntryDatefrmtd
    FROM 
        CustomerOPEntryUpdated
    WHERE 
        CustomerCode = @CustomerId

    ORDER BY EntryDatefrmtd ASC;
END








GO
/****** Object:  StoredProcedure [dbo].[SP_GetPurchaseDetailsForSuppPayment]    Script Date: 09-01-2025 19:55:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[SP_GetPurchaseDetailsForSuppPayment]
    @id NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        Purchase.DocumentNo AS PurchaseNo, 
        (Purchase.NetAmount 
         - COALESCE(PurchaseReturn.NetAmount, 0) 
         - ISNULL(SUM(SupplierPaymentDetails.ReceivedAmount), 0) 
         - ISNULL(SUM(SupplierPaymentDetails.Discount), 0)) AS BalanceAmount,
        ISNULL(SUM(SupplierPaymentDetails.Discount), 0) AS DiscountAmt,
        ISNULL(SUM(SupplierPaymentDetails.ReceivedAmount), 0) AS ReceivedAmt,
        Purchase.NetAmount AS PurchaseAmount, 
        FORMAT(Purchase.EntryDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        'P' AS type
    FROM Purchase
    LEFT JOIN SupplierPaymentDetails
        ON SupplierPaymentDetails.PurchaseNo = Purchase.DocumentNo AND type = 'P'
    LEFT JOIN PurchaseReturn
        ON PurchaseReturn.PurchaseNo = Purchase.DocumentNo AND PurchaseReturn.BillType = 'Credit'
    WHERE Purchase.BillType = 'Credit' AND Purchase.SupplierCode = @id
    GROUP BY Purchase.DocumentNo, Purchase.NetAmount, Purchase.EntryDate, PurchaseReturn.NetAmount, PurchaseReturn.BillType
    HAVING (Purchase.NetAmount 
            - COALESCE(PurchaseReturn.NetAmount, 0) 
            - ISNULL(SUM(SupplierPaymentDetails.ReceivedAmount), 0) 
            - ISNULL(SUM(SupplierPaymentDetails.Discount), 0)) > 0

    UNION ALL

    SELECT 
        PurchaseExpenseDetails.purexpno AS PurchaseNo, 
        (PurchaseExpenseDetails.RATE 
         - ISNULL(SUM(SupplierPaymentDetails.ReceivedAmount), 0) 
         - ISNULL(SUM(SupplierPaymentDetails.Discount), 0)) AS BalanceAmount,
        ISNULL(SUM(SupplierPaymentDetails.Discount), 0) AS DiscountAmt,
        ISNULL(SUM(SupplierPaymentDetails.ReceivedAmount), 0) AS ReceivedAmt,
        PurchaseExpenseDetails.RATE AS PurchaseAmount,  
        FORMAT(PurchaseExpenseDetails.EXPDATE, 'dd-MM-yyyy') AS EntryDatefrmtd,
        'E' AS type
    FROM PurchaseExpenseDetails
    LEFT JOIN SupplierPaymentDetails
        ON SupplierPaymentDetails.PurchaseNo = PurchaseExpenseDetails.purexpno AND type = 'E'

    WHERE PurchaseExpenseDetails.EXTYPE = 'Credit' AND PurchaseExpenseDetails.suppcode = @id
    GROUP BY PurchaseExpenseDetails.purexpno, PurchaseExpenseDetails.RATE, PurchaseExpenseDetails.EXPDATE, PurchaseExpenseDetails.EXTYPE
    HAVING (PurchaseExpenseDetails.RATE 
            - ISNULL(SUM(SupplierPaymentDetails.ReceivedAmount), 0)) > 0

    UNION ALL

    SELECT 
        SupplierOPEntryUpdated.PurchaseNo AS PurchaseNo, 
		
        (ISNULL(SupplierOPEntryUpdated.Balance, 0)-ISNULL(SUM(SupplierPaymentDetails.ReceivedAmount), 0) - ISNULL(SUM(SupplierPaymentDetails.Discount), 0)) AS BalanceAmount,
        ISNULL(SupplierOPEntryUpdated.Discount, 0) AS DiscountAmt,
        ISNULL(SupplierOPEntryUpdated.ReceivedAmt, 0) AS ReceivedAmt,
        SupplierOPEntryUpdated.PurchaseAmt AS PurchaseAmount, 
        FORMAT(SupplierOPEntryUpdated.EntryDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        '' AS type
    FROM SupplierOPEntryUpdated
	 LEFT JOIN SupplierPaymentDetails
        ON SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo 
    WHERE SupplierOPEntryUpdated.SupplierCode = @id
	group by
	SupplierOPEntryUpdated.PurchaseNo, 
        SupplierOPEntryUpdated.PurchaseAmt, 
        SupplierOPEntryUpdated.EntryDate,
        SupplierOPEntryUpdated.Balance,
		SupplierOPEntryUpdated.Discount,
		SupplierOPEntryUpdated.ReceivedAmt
    ORDER BY EntryDatefrmtd ASC;
END






GO
/****** Object:  StoredProcedure [dbo].[SP_GetSupplierPaymentDetailEdit]    Script Date: 09-01-2025 19:55:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[SP_GetSupplierPaymentDetailEdit]
    @id NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    -- Query to fetch payment details for purchases
    SELECT  
        PurchaseNo,
        SupplierPaymentDetails.ReceivedAmount,
        ISNULL(SupplierPaymentDetails.Narration, '') AS Narration,
        Purchase.NetAmount AS PurchaseAmount,
        ISNULL((SELECT SUM(Discount) 
                FROM SupplierPaymentDetails  
                WHERE SupplierPaymentDetails.PurchaseNo = Purchase.DocumentNo), 0) AS DiscountAmt,
        ISNULL((SELECT SUM(ReceivedAmount) 
                FROM SupplierPaymentDetails  
                WHERE SupplierPaymentDetails.PurchaseNo = Purchase.DocumentNo), 0) AS ReceivedAmt,
        FORMAT(SupplierPaymentDetails.PurchaseDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        CASE 
            WHEN (SELECT COUNT(*)   
                  FROM SupplierPaymentDetails AS CRD2   
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo   
                  GROUP BY PurchaseNo) = 1  
            THEN (Purchase.NetAmount)
            ELSE (SELECT (Purchase.NetAmount -  
                          COALESCE(SUM(ReceivedAmount), 0) - 
                          COALESCE(SUM(Discount), 0)) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS TotalReceivedAmount,
        CASE  
            WHEN EXISTS (SELECT 1  
                         FROM SupplierPaymentDetails AS CRD2  
                         WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo  
                           AND CRD2.EntryDate > SupplierPaymentDetails.EntryDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        CASE 
            WHEN (SELECT COUNT(*) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                  GROUP BY PurchaseNo) = 1
            THEN '0'
            ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS ReceivedWithoutInv,
        CASE 
            WHEN (SELECT COUNT(*) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                  GROUP BY PurchaseNo) = 1
            THEN '0'
            ELSE (SELECT COALESCE(SUM(Discount), 0) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS DiscountWithoutInv,
        'P' AS Type
    FROM SupplierPaymentDetails 
    LEFT JOIN Purchase ON SupplierPaymentDetails.PurchaseNo = Purchase.DocumentNo 
    WHERE SupplierPaymentDetails.VoucherNo = @id AND Type = 'P'

    UNION ALL

    -- Query to fetch payment details for purchase expenses
    SELECT  
        PurchaseNo,
        ReceivedAmount,
        ISNULL(SupplierPaymentDetails.Narration, '') AS Narration,
        PurchaseExpenseDetails.RATE AS PurchaseAmount,
        ISNULL((SELECT SUM(Discount) 
                FROM SupplierPaymentDetails  
                WHERE SupplierPaymentDetails.PurchaseNo = PurchaseExpenseDetails.DOCNO), 0) AS DiscountAmt,
        ISNULL((SELECT SUM(ReceivedAmount) 
                FROM SupplierPaymentDetails  
                WHERE SupplierPaymentDetails.PurchaseNo = PurchaseExpenseDetails.DOCNO), 0) AS ReceivedAmt,
        FORMAT(SupplierPaymentDetails.PurchaseDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        CASE 
            WHEN (SELECT COUNT(*)   
                  FROM SupplierPaymentDetails AS CRD2   
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo   
                  GROUP BY PurchaseNo) = 1  
            THEN (PurchaseExpenseDetails.RATE)
            ELSE (SELECT (PurchaseExpenseDetails.RATE -  
                          COALESCE(SUM(ReceivedAmount), 0) - 
                          COALESCE(SUM(Discount), 0)) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS TotalReceivedAmount,
        CASE  
            WHEN EXISTS (SELECT 1  
                         FROM SupplierPaymentDetails AS CRD2  
                         WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo  
                           AND CRD2.EntryDate > SupplierPaymentDetails.EntryDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        CASE 
            WHEN (SELECT COUNT(*) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                  GROUP BY PurchaseNo) = 1
            THEN '0'
            ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS ReceivedWithoutInv,
        CASE 
            WHEN (SELECT COUNT(*) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                  GROUP BY PurchaseNo) = 1
            THEN '0'
            ELSE (SELECT COALESCE(SUM(Discount), 0) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS DiscountWithoutInv,
        'E' AS Type
    FROM SupplierPaymentDetails 
    LEFT JOIN PurchaseExpenseDetails ON SupplierPaymentDetails.PurchaseNo = PurchaseExpenseDetails.purexpno 
    WHERE SupplierPaymentDetails.VoucherNo = @id AND Type = 'E'

    UNION ALL

    -- Query to fetch supplier opening balance payment details
    SELECT  
        SupplierOPEntryUpdated.PurchaseNo,
        SupplierPaymentDetails.ReceivedAmount,
        ISNULL(SupplierPaymentDetails.Narration, '') AS Narration,
        SupplierOPEntryUpdated.PurchaseAmt AS PurchaseAmount,
        ISNULL((SELECT SUM(Discount) 
                FROM SupplierPaymentDetails  
                WHERE SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo), 0) AS DiscountAmt,
        ISNULL((SELECT SUM(ReceivedAmount) 
                FROM SupplierPaymentDetails  
                WHERE SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo), 0) AS ReceivedAmt,
        FORMAT(SupplierPaymentDetails.PurchaseDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        CASE 
            WHEN (SELECT COUNT(*)   
                  FROM SupplierPaymentDetails AS CRD2   
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo   
                  GROUP BY PurchaseNo) = 1  
            THEN (SupplierOPEntryUpdated.PurchaseAmt)
            ELSE (SELECT (SupplierOPEntryUpdated.PurchaseAmt -  
                          COALESCE(SUM(ReceivedAmt), 0) - 
                          COALESCE(SUM(Discount), 0)) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS TotalReceivedAmount,
        CASE  
            WHEN EXISTS (SELECT 1  
                         FROM SupplierPaymentDetails AS CRD2  
                         WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo  
                           AND CRD2.EntryDate > SupplierPaymentDetails.EntryDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        CASE 
            WHEN (SELECT COUNT(*) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                  GROUP BY PurchaseNo) = 1
            THEN '0'
            ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS ReceivedWithoutInv,
        CASE 
            WHEN (SELECT COUNT(*) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                  GROUP BY PurchaseNo) = 1
            THEN '0'
            ELSE (SELECT COALESCE(SUM(Discount), 0) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS DiscountWithoutInv,
        '' AS Type
    FROM SupplierPaymentDetails 
    LEFT JOIN SupplierOPEntryUpdated ON SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo 
    WHERE SupplierPaymentDetails.VoucherNo = @id AND Type IS NULL group by SupplierOPEntryUpdated.PurchaseNo,SupplierOPEntryUpdated.ReceivedAmt,SupplierPaymentDetails.Narration,SupplierOPEntryUpdated.PurchaseAmt,SupplierPaymentDetails.PurchaseDate,
SupplierPaymentDetails.PurchaseNo,SupplierPaymentDetails.EntryDate,SupplierPaymentDetails.ReceivedAmount;
END


GO
/****** Object:  StoredProcedure [dbo].[sp_CustomerReceiptReport]    Script Date: 11-01-2025 11:37:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_CustomerReceiptReport]        
@FromDate datetime,        
@ToDate DATETIME,  
@CustomerCode VARCHAR(100),  
@PayMode VARCHAR(50), 
@attended VARCHAR(100) ,
@Flag VARCHAR(10)  ,
@BranchCode VARCHAR(100) 
AS   
begin        
IF @Flag ='F' 
SELECT distinct CustomerReceipt.VoucherNo,CustomerReceipt.PaidDate,CustomerReceipt.Narration,CustomerReceipt.PayMode,dbo.CustomerReceipt.ChequeNo,
dbo.CustomerReceipt.ChequeDate,CustomerReceipt.TotAmount AS TotAmount,CustomerReceipt.CustomerCode,CustomerReceipt.CustomerName,ISNULL(invoice.attended,'') as Salesman     
FROM  CustomerReceipt LEFT JOIN CustomerReceiptDetail ON CustomerReceipt.VoucherNo = CustomerReceiptDetail.VoucherNo left join invoice on invoice.invno=CustomerReceiptDetail.invoiceno 
and invoice.sr='sales' where CustomerReceipt.PaidDate between  @FromDate AND @ToDate AND ISNULL(CustomerReceipt.CustomerCode,'')=ISNULL(@CustomerCode,ISNULL(CustomerReceipt.CustomerCode,''))
and isnull(CustomerReceipt.deleted,'N')='N' and ISNULL(CustomerReceipt.PayMode,'')=ISNULL(@PayMode,ISNULL(CustomerReceipt.PayMode,''))  
AND (@BranchCode IS NULL OR @BranchCode = '' OR @BranchCode = '1' OR ISNULL(CustomerReceipt.BranchCode, '') = @BranchCode)
AND ISNULL(invoice.attended,'')=ISNULL(@attended,ISNULL(invoice.attended,'')) order by CustomerReceipt.voucherno 
ELSE IF @Flag ='T' 
SELECT distinct CustomerReceipt.VoucherNo,CustomerReceipt.PaidDate,CustomerReceipt.Narration,CustomerReceipt.PayMode,dbo.CustomerReceipt.ChequeNo,
dbo.CustomerReceipt.ChequeDate,CustomerReceipt.TotAmount AS TotAmount,CustomerReceipt.CustomerCode,CustomerReceipt.CustomerName,ISNULL(invoice.attended,'') as Salesman     
FROM  CustomerReceipt LEFT JOIN CustomerReceiptDetail ON CustomerReceipt.VoucherNo = CustomerReceiptDetail.VoucherNo left join invoice on invoice.invno=CustomerReceiptDetail.invoiceno 
and invoice.sr='sales' where CustomerReceipt.PaidDate between  @FromDate AND @ToDate  AND (@CustomerCode IS NULL OR @CustomerCode = '' OR ISNULL(CustomerReceipt.CustomerCode, '') = @CustomerCode)
 and isnull(CustomerReceipt.deleted,'N')='N' AND (@PayMode IS NULL OR @PayMode = '' OR @paymode = '4' OR ISNULL(CustomerReceipt.PayMode, '') = @PayMode)
AND (@attended IS NULL OR @attended = '' OR ISNULL(invoice.attended, '') = @attended)
AND (@BranchCode IS NULL OR @BranchCode = '' OR @BranchCode = '1' OR ISNULL(CustomerReceipt.BranchCode, '') = @BranchCode) order by CustomerReceipt.voucherno 
select InvoiceNo as InvNo,PaidDate,InvoiceAmount,ReceivedAmount,VoucherNo from CustomerReceiptDetail where PaidDate between  @FromDate AND   @ToDate 
and isnull(deleted,'N')='N' order by voucherno
--SELECT distinct CustomerReceipt.VoucherNo,CustomerReceipt.PaidDate,CustomerReceipt.Narration,CustomerReceipt.PayMode,dbo.CustomerReceipt.ChequeNo,
--dbo.CustomerReceipt.ChequeDate,CustomerReceipt.TotAmount,CustomerReceipt.CustomerCode,CustomerReceipt.CustomerName     
--FROM  CustomerReceipt  AND (@attended IS NULL OR @attended = '' OR ISNULL(invoice.attended, '') = @attended)
--LEFT JOIN CustomerReceiptDetail ON CustomerReceipt.VoucherNo = CustomerReceiptDetail.VoucherNo                 
--where CustomerReceipt.PaidDate between  @FromDate AND   @ToDate   
--AND ISNULL(CustomerReceipt.CustomerCode,'')=ISNULL(@CustomerCode,CustomerReceipt.CustomerCode)
-- and isnull(CustomerReceipt.deleted,'N')='N'
--and ISNULL(CustomerReceipt.PayMode,'')=ISNULL(@PayMode,CustomerReceipt.PayMode)   
end 

go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[udf_GetReceivedAmountCustRecpt] (
    @InvoiceNo NVARCHAR(50),   -- Invoice number for which the received amount is needed
    @CustomerCode NVARCHAR(50) -- Customer code to filter the data
)
RETURNS DECIMAL(18, 2) -- Returns the total received amount as a decimal value
AS
BEGIN
    DECLARE @TotalReceivedAmount DECIMAL(18, 2);

    SELECT 
        @TotalReceivedAmount = ISNULL(SUM(ReceivedAmount), 0)
    FROM 
        CustomerReceiptDetail
    INNER JOIN 
        CustomerReceipt 
        ON CustomerReceiptDetail.voucherno = CustomerReceipt.voucherno   
    INNER JOIN 
        CustomerOPEntryUpdated 
        ON CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo   
    WHERE 
        CustomerReceiptDetail.InvoiceNo = @InvoiceNo
        AND CustomerOPEntryUpdated.CustomerCode = @CustomerCode
        AND CustomerReceipt.CustomerCode = @CustomerCode;

    RETURN @TotalReceivedAmount;
END;




GO
/****** Object:  UserDefinedFunction [dbo].[udf_GetDiscountAmountCustRecpt]    Script Date: 13-01-2025 20:13:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[udf_GetDiscountAmountCustRecpt] (
    @InvoiceNo NVARCHAR(50),   -- Invoice number for which the received amount is needed
    @CustomerCode NVARCHAR(50) -- Customer code to filter the data
)
RETURNS DECIMAL(18, 2) -- Returns the total received amount as a decimal value
AS
BEGIN
    DECLARE @TotalReceivedAmount DECIMAL(18, 2);

    SELECT 
        @TotalReceivedAmount = ISNULL(SUM(CustomerReceiptDetail.Discount), 0)
    FROM 
        CustomerReceiptDetail
    INNER JOIN 
        CustomerReceipt 
        ON CustomerReceiptDetail.voucherno = CustomerReceipt.voucherno   
    INNER JOIN 
        CustomerOPEntryUpdated 
        ON CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo   
    WHERE 
        CustomerReceiptDetail.InvoiceNo = @InvoiceNo
        AND CustomerOPEntryUpdated.CustomerCode = @CustomerCode
        AND CustomerReceipt.CustomerCode = @CustomerCode;

    RETURN @TotalReceivedAmount;
END;





GO
/****** Object:  UserDefinedFunction [dbo].[udf_GetTotalReceivedAmountforcustrecpt]    Script Date: 13-01-2025 20:15:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[udf_GetTotalReceivedAmountforcustrecpt]
(
    @InvNo NVARCHAR(500),
    @custcode NVARCHAR(500),
    @voucherno NVARCHAR(500),
    @InvoiceAmt FLOAT
)
RETURNS FLOAT
AS
BEGIN
    DECLARE @TotalReceivedAmount FLOAT;

   IF (
        SELECT COUNT(*)
        FROM CustomerReceiptDetail AS CRD2
        INNER JOIN CustomerReceipt AS CR ON CR.VoucherNo = CRD2.VoucherNo
        WHERE CRD2.InvoiceNo = @InvNo
          AND CR.CustomerCode = @custcode
        GROUP BY CRD2.InvoiceNo
    ) = 1
    BEGIN
        -- If there is only one receipt for the invoice
        SET @TotalReceivedAmount = @InvoiceAmt;
    END
    ELSE
    BEGIN
        -- Calculate the remaining amount for multiple receipts
        SELECT 
            @TotalReceivedAmount = @InvoiceAmt 
                                    - COALESCE(SUM(CRD2.ReceivedAmount), 0) 
                                    - COALESCE(SUM(CRD2.Discount), 0)
        FROM 
            CustomerReceiptDetail AS CRD2
        INNER JOIN 
            CustomerReceipt AS CR ON CR.VoucherNo = CRD2.VoucherNo
        WHERE 
            CRD2.InvoiceNo = @InvNo
            AND CR.CustomerCode = @custcode
            AND CRD2.VoucherNo <> @voucherno
        GROUP BY 
            CRD2.InvoiceNo;
    END

    RETURN ISNULL(@TotalReceivedAmount, 0);
END



GO
/****** Object:  UserDefinedFunction [dbo].[udf_SelectReceivedAmountOfCustomerforinvoice]    Script Date: 13-01-2025 20:16:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[udf_SelectReceivedAmountOfCustomerforinvoice] ( @InvNo nvarchar(500),@custcode nvarchar(500),@voucherno nvarchar(500))
RETURNS float
AS
BEGIN

RETURN (select isnull(sum(ReceivedAmount),0) as ReceivedAmount FROM CustomerReceiptDetail inner join CustomerReceipt 
 on CustomerReceipt.voucherno=CustomerReceiptDetail.VoucherNo 
where CustomerReceiptDetail.InvoiceNo=@InvNo  and CustomerReceipt.CustomerCode =@custcode and CustomerReceipt.voucherno<>@voucherno )

END



GO
/****** Object:  UserDefinedFunction [dbo].[udf_SelectDiscountAmountOfCustomerforinvoice]    Script Date: 13-01-2025 20:17:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[udf_SelectDiscountAmountOfCustomerforinvoice] ( @InvNo nvarchar(500),@custcode nvarchar(500),@voucherno nvarchar(500))
RETURNS float
AS
BEGIN

RETURN (select isnull(sum(Discount),0) as Discount FROM CustomerReceiptDetail inner join CustomerReceipt 
 on CustomerReceipt.voucherno=CustomerReceiptDetail.VoucherNo 
where CustomerReceiptDetail.InvoiceNo=@InvNo  and CustomerReceipt.CustomerCode =@custcode and CustomerReceipt.voucherno<>@voucherno )

END





GO
/****** Object:  StoredProcedure [dbo].[Sp_GetCustomerReceiptDetailEdit]    Script Date: 13-01-2025 20:11:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[Sp_GetCustomerReceiptDetailEdit]
    @VoucherNo NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    -- First SELECT statement
    SELECT  
        CustomerReceiptDetail.InvoiceNo,
        CustomerReceiptDetail.Discount,
        CustomerReceiptDetail.ReceivedAmount,
        ISNULL(CustomerReceiptDetail.Narration, '') AS Narration,
        Invoice.NetAmount AS InvoiceAmount,
        ISNULL((SELECT SUM(ReceivedAmount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = Invoice.InvNo), 0) AS ReceivedAmt,
		
        FORMAT(InvDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        ISNULL((SELECT SUM(Discount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = Invoice.InvNo), 0) AS DiscountAmt,

        CASE 
            WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
            THEN Invoice.NetAmount
            ELSE (SELECT (Invoice.NetAmount - COALESCE(SUM(ReceivedAmount), 0) - COALESCE(SUM(Discount), 0)) 
                  FROM CustomerReceiptDetail AS CRD2 
                  WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
                    AND CRD2.VoucherNo != @VoucherNo 
                  GROUP BY InvoiceNo)
        END AS TotalReceivedAmount,
        CASE 
            WHEN EXISTS (SELECT 1 FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo AND CRD2.PaidDate > CustomerReceiptDetail.PaidDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        CASE 
            WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
            THEN 0
            ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
                  FROM CustomerReceiptDetail AS CRD2 
                  WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
                    AND CRD2.VoucherNo != @VoucherNo 
                  GROUP BY InvoiceNo)
        END AS ReceivedWithoutInv,
        CASE 
            WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
            THEN 0
            ELSE (SELECT COALESCE(SUM(Discount), 0) 
                  FROM CustomerReceiptDetail AS CRD2 
                  WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
                    AND CRD2.VoucherNo != @VoucherNo 
                  GROUP BY InvoiceNo)
        END AS DiscountWithoutInv
    FROM   
        CustomerReceiptDetail 
    LEFT JOIN  
        Invoice ON CustomerReceiptDetail.InvoiceNo = Invoice.InvNo 
    WHERE  
        CustomerReceiptDetail.VoucherNo = @VoucherNo AND Invoice.NetAmount IS NOT NULL

    UNION ALL

    -- Second SELECT statement
    SELECT  
        CustomerReceiptDetail.InvoiceNo,
        CustomerReceiptDetail.Discount,
        CustomerReceiptDetail.ReceivedAmount,
        ISNULL(CustomerReceiptDetail.Narration, '') AS Narration,
        CustomerOPEntryUpdated.InvoiceAmt AS InvoiceAmount,
       
	   --isnull([dbo].[udf_SelectReceivedAmountOfCustomerforinvoice](CustomerReceiptDetail.invoiceno,customerreceipt.customercode),0)AS ReceivedAmt,
	   ISNULL((SELECT SUM(ReceivedAmount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo and CustomerOPEntryUpdated.CustomerCode=CustomerReceipt.CustomerCode), 0) AS ReceivedAmt,
        
		FORMAT(InvDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        ISNULL((SELECT SUM(Discount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo), 0) AS DiscountAmt,
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN CustomerOPEntryUpdated.InvoiceAmt
        --    ELSE (SELECT (CustomerOPEntryUpdated.InvoiceAmt - COALESCE(SUM(ReceivedAmount), 0) - COALESCE(SUM(Discount), 0)) 
        --          FROM CustomerReceiptDetail AS CRD2 
        --          WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo  and   CustomerOPEntryUpdated.CustomerCode=CustomerReceipt.CustomerCode
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS TotalReceivedAmount,
		 isnull([dbo].[udf_GetTotalReceivedAmountforcustrecpt](CustomerReceiptDetail.invoiceno,customerreceipt.customercode,customerreceipt.voucherno,CustomerOPEntryUpdated.InvoiceAmt),0)AS TotalReceivedAmount,
        CASE 
            WHEN EXISTS (SELECT 1 FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo AND CRD2.PaidDate > CustomerReceiptDetail.PaidDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN 0
        --    ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
        --          FROM CustomerReceiptDetail AS CRD2 
        --          WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS ReceivedWithoutInv,
		  isnull([dbo].[udf_SelectReceivedAmountOfCustomerforinvoice](CustomerReceiptDetail.invoiceno,customerreceipt.customercode,customerreceipt.voucherno),0)AS ReceivedWithoutInv,
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN 0
        --    ELSE (SELECT COALESCE(SUM(Discount), 0) 
        --          FROM CustomerReceiptDetail AS CRD2 
        --          WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS DiscountWithoutInv
		  isnull([dbo].[udf_SelectDiscountAmountOfCustomerforinvoice](CustomerReceiptDetail.invoiceno,customerreceipt.customercode,customerreceipt.voucherno),0)AS DiscountWithoutInv
    FROM   
        CustomerReceiptDetail 
		inner join CustomerReceipt  on CustomerReceipt.voucherno=CustomerReceiptDetail.VoucherNo
    LEFT JOIN  
        CustomerOPEntryUpdated ON CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo  and CustomerOPEntryUpdated.CustomerCode=CustomerReceipt.CustomerCode
    WHERE  
        CustomerReceiptDetail.VoucherNo = @VoucherNo AND CustomerOPEntryUpdated.InvoiceAmt IS NOT NULL
END








GO
/****** Object:  StoredProcedure [dbo].[Sp_GetInvoiceDetailForReceipt]    Script Date: 13-01-2025 20:11:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[Sp_GetInvoiceDetailForReceipt]
    @CustomerId NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    -- First SELECT statement
    SELECT 
        Invoice.InvNo AS InvoiceNo, 
        (Invoice.NetAmount - COALESCE(InvoiceReturn.NetAmount, 0) - ISNULL(SUM(CustomerReceiptDetail.ReceivedAmount), 0) - ISNULL(SUM(CustomerReceiptDetail.Discount), 0)) AS BalanceAmount,
        ISNULL(SUM(CustomerReceiptDetail.Discount), 0) AS DiscountAmt, 
        ISNULL(SUM(CustomerReceiptDetail.ReceivedAmount), 0) AS ReceivedAmt,
        Invoice.NetAmount AS InvoiceAmount, 
        FORMAT(Invoice.InvoiceDate, 'dd-MM-yyyy') AS EntryDatefrmtd
    FROM 
        Invoice
        LEFT JOIN CustomerReceiptDetail 
            ON CustomerReceiptDetail.InvoiceNo = Invoice.InvNo
        LEFT JOIN InvoiceReturn 
            ON InvoiceReturn.InvNo = Invoice.InvNo 
            AND InvoiceReturn.InvType = 'Credit'
    WHERE 
        Invoice.InvType = 'Credit' 
        AND Invoice.CustomerCode = @CustomerId
    GROUP BY 
        Invoice.InvNo, 
        Invoice.NetAmount, 
        Invoice.InvoiceDate, 
        InvoiceReturn.NetAmount,
        InvoiceReturn.InvType
    HAVING 
        (Invoice.NetAmount - COALESCE(InvoiceReturn.NetAmount, 0) - ISNULL(SUM(CustomerReceiptDetail.ReceivedAmount), 0) - ISNULL(SUM(CustomerReceiptDetail.Discount), 0)) > 0
    
    UNION ALL

    -- Second SELECT statement
    SELECT 
        CustomerOPEntryUpdated.InvoiceNo AS InvoiceNo,
	
        (Balance-ISNULL([dbo].[udf_GetReceivedAmountCustRecpt](CustomerReceiptDetail.invoiceno,@CustomerId), 0) - ISNULL([dbo].[udf_GetDiscountAmountCustRecpt](CustomerReceiptDetail.invoiceno,@CustomerId), 0)) AS BalanceAmount,
        CustomerOPEntryUpdated.Discount AS DiscountAmt,
        ReceivedAmt AS ReceivedAmt,
        InvoiceAmt AS InvoiceAmount,
        FORMAT(InvoiceDate, 'dd-MM-yyyy') AS EntryDatefrmtd
    FROM 
        CustomerOPEntryUpdated
		LEFT JOIN CustomerReceiptDetail 
            ON CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo  
			LEFT JOIN 
        CustomerReceipt 
        ON CustomerReceiptDetail.voucherno = CustomerReceipt.voucherno    AND (CustomerReceipt.CustomerCode = @CustomerId OR CustomerReceipt.CustomerCode IS NULL)
    WHERE 
        CustomerOPEntryUpdated.CustomerCode = @CustomerId
		-- AND CustomerReceipt.CustomerCode = @CustomerId
		
		GROUP BY 
        CustomerOPEntryUpdated.InvoiceNo, 
        CustomerOPEntryUpdated.InvoiceAmt, 
        CustomerOPEntryUpdated.InvoiceDate,
        CustomerOPEntryUpdated.Balance,
		CustomerOPEntryUpdated.Discount,
		CustomerOPEntryUpdated.ReceivedAmt,
		CustomerReceiptDetail.invoiceno
      
    ORDER BY EntryDatefrmtd ASC;
END
  





  SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[udf_GetReceivedAmountSuppPaymnt] (
    @PurchaseNo NVARCHAR(50),   -- Invoice number for which the received amount is needed
    @SupplierCode NVARCHAR(50) -- Customer code to filter the data
)
RETURNS DECIMAL(18, 2) -- Returns the total received amount as a decimal value
AS
BEGIN
    DECLARE @TotalReceivedAmount DECIMAL(18, 2);

    SELECT 
        @TotalReceivedAmount = ISNULL(SUM(ReceivedAmount), 0)
    FROM 
        SupplierPaymentDetails
    INNER JOIN 
        SupplierPayment 
        ON SupplierPaymentDetails.voucherno = SupplierPayment.voucherno   
    INNER JOIN 
        SupplierOPEntryUpdated 
        ON SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo   
    WHERE 
        SupplierPaymentDetails.PurchaseNo = @PurchaseNo
        AND SupplierOPEntryUpdated.SupplierCode = @SupplierCode
        AND SupplierPayment.SupplierCode = @SupplierCode;

    RETURN @TotalReceivedAmount;
END;



GO
/****** Object:  UserDefinedFunction [dbo].[udf_GetDiscountAmountSuppPaymnt]    Script Date: 13-01-2025 20:20:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[udf_GetDiscountAmountSuppPaymnt] (
    @PurchaseNo NVARCHAR(50),   -- Invoice number for which the received amount is needed
    @SupplierCode NVARCHAR(50) -- Customer code to filter the data
)
RETURNS DECIMAL(18, 2) -- Returns the total received amount as a decimal value
AS
BEGIN
    DECLARE @TotalReceivedAmount DECIMAL(18, 2);

    SELECT 
        @TotalReceivedAmount = ISNULL(SUM(SupplierPaymentDetails.Discount), 0)
    FROM 
        SupplierPaymentDetails
    INNER JOIN 
        SupplierPayment 
        ON SupplierPaymentDetails.voucherno = SupplierPayment.voucherno   
    INNER JOIN 
        SupplierOPEntryUpdated 
        ON SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo   
    WHERE 
        SupplierPaymentDetails.PurchaseNo = @PurchaseNo
        AND SupplierOPEntryUpdated.SupplierCode = @SupplierCode
        AND SupplierPayment.SupplierCode = @SupplierCode;

    RETURN @TotalReceivedAmount;
END;




GO
/****** Object:  StoredProcedure [dbo].[SP_GetPurchaseDetailsForSuppPayment]    Script Date: 13-01-2025 20:19:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[SP_GetPurchaseDetailsForSuppPayment]
    @id NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        Purchase.DocumentNo AS PurchaseNo, 
        (Purchase.NetAmount 
         - COALESCE(PurchaseReturn.NetAmount, 0) 
         - ISNULL(SUM(SupplierPaymentDetails.ReceivedAmount), 0) 
         - ISNULL(SUM(SupplierPaymentDetails.Discount), 0)) AS BalanceAmount,
        ISNULL(SUM(SupplierPaymentDetails.Discount), 0) AS DiscountAmt,
        ISNULL(SUM(SupplierPaymentDetails.ReceivedAmount), 0) AS ReceivedAmt,
        Purchase.NetAmount AS PurchaseAmount, 
        FORMAT(Purchase.EntryDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        'P' AS type
    FROM Purchase
    LEFT JOIN SupplierPaymentDetails
        ON SupplierPaymentDetails.PurchaseNo = Purchase.DocumentNo AND type = 'P'
    LEFT JOIN PurchaseReturn
        ON PurchaseReturn.PurchaseNo = Purchase.DocumentNo AND PurchaseReturn.BillType = 'Credit'
    WHERE Purchase.BillType = 'Credit' AND Purchase.SupplierCode = @id
    GROUP BY Purchase.DocumentNo, Purchase.NetAmount, Purchase.EntryDate, PurchaseReturn.NetAmount, PurchaseReturn.BillType
    HAVING (Purchase.NetAmount 
            - COALESCE(PurchaseReturn.NetAmount, 0) 
            - ISNULL(SUM(SupplierPaymentDetails.ReceivedAmount), 0) 
            - ISNULL(SUM(SupplierPaymentDetails.Discount), 0)) > 0

    UNION ALL

    SELECT 
        PurchaseExpenseDetails.purexpno AS PurchaseNo, 
        (PurchaseExpenseDetails.RATE 
         - ISNULL(SUM(SupplierPaymentDetails.ReceivedAmount), 0) 
         - ISNULL(SUM(SupplierPaymentDetails.Discount), 0)) AS BalanceAmount,
        ISNULL(SUM(SupplierPaymentDetails.Discount), 0) AS DiscountAmt,
        ISNULL(SUM(SupplierPaymentDetails.ReceivedAmount), 0) AS ReceivedAmt,
        PurchaseExpenseDetails.RATE AS PurchaseAmount,  
        FORMAT(PurchaseExpenseDetails.EXPDATE, 'dd-MM-yyyy') AS EntryDatefrmtd,
        'E' AS type
    FROM PurchaseExpenseDetails
    LEFT JOIN SupplierPaymentDetails
        ON SupplierPaymentDetails.PurchaseNo = PurchaseExpenseDetails.purexpno AND type = 'E'

    WHERE PurchaseExpenseDetails.EXTYPE = 'Credit' AND PurchaseExpenseDetails.suppcode = @id
    GROUP BY PurchaseExpenseDetails.purexpno, PurchaseExpenseDetails.RATE, PurchaseExpenseDetails.EXPDATE, PurchaseExpenseDetails.EXTYPE
    HAVING (PurchaseExpenseDetails.RATE 
            - ISNULL(SUM(SupplierPaymentDetails.ReceivedAmount), 0)) > 0

    UNION ALL

    SELECT 
        SupplierOPEntryUpdated.PurchaseNo AS PurchaseNo, 
		(ISNULL(SupplierOPEntryUpdated.Balance, 0)-ISNULL([dbo].[udf_GetReceivedAmountSuppPaymnt](SupplierPaymentDetails.PurchaseNo,@id), 0) - ISNULL([dbo].[udf_GetDiscountAmountSuppPaymnt](SupplierPaymentDetails.PurchaseNo,@id), 0)) AS BalanceAmount,
       
        ISNULL(SupplierOPEntryUpdated.Discount, 0) AS DiscountAmt,
        ISNULL(SupplierOPEntryUpdated.ReceivedAmt, 0) AS ReceivedAmt,
        SupplierOPEntryUpdated.PurchaseAmt AS PurchaseAmount, 
        FORMAT(SupplierOPEntryUpdated.EntryDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        '' AS type
    FROM SupplierOPEntryUpdated
	 LEFT JOIN SupplierPaymentDetails
        ON SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo 
		left JOIN 
        SupplierPayment 
        ON SupplierPaymentDetails.voucherno = SupplierPayment.voucherno   AND (SupplierPayment.SupplierCode = @id OR SupplierPayment.SupplierCode IS NULL)
    WHERE SupplierOPEntryUpdated.SupplierCode = @id  
	group by
	SupplierOPEntryUpdated.PurchaseNo, 
        SupplierOPEntryUpdated.PurchaseAmt, 
        SupplierOPEntryUpdated.EntryDate,
        SupplierOPEntryUpdated.Balance,
		SupplierOPEntryUpdated.Discount,
		SupplierOPEntryUpdated.ReceivedAmt,
		SupplierPaymentDetails.PurchaseNo
    ORDER BY EntryDatefrmtd ASC;
END



GO
/****** Object:  UserDefinedFunction [dbo].[udf_GetTotalReceivedAmountforSupppayment]    Script Date: 13-01-2025 20:22:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[udf_GetTotalReceivedAmountforSupppayment]
(
    @PurchaseNo NVARCHAR(500),
    @suppcode NVARCHAR(500),
    @voucherno NVARCHAR(500),
    @PurchaseAmt FLOAT
)
RETURNS FLOAT
AS
BEGIN
    DECLARE @TotalReceivedAmount FLOAT;

   IF (
        SELECT COUNT(*)
        FROM SupplierPaymentDetails AS CRD2
        INNER JOIN SupplierPayment AS CR ON CR.VoucherNo = CRD2.VoucherNo
        WHERE CRD2.PurchaseNo = @PurchaseNo
          AND CR.SupplierCode = @suppcode
        GROUP BY CRD2.PurchaseNo
    ) = 1
    BEGIN
        -- If there is only one receipt for the invoice
        SET @TotalReceivedAmount = @PurchaseAmt;
    END
    ELSE
    BEGIN
        -- Calculate the remaining amount for multiple receipts
        SELECT 
            @TotalReceivedAmount = @PurchaseAmt 
                                    - COALESCE(SUM(CRD2.ReceivedAmount), 0) 
                                    - COALESCE(SUM(CRD2.Discount), 0)
        FROM 
            SupplierPaymentDetails AS CRD2
        INNER JOIN 
            SupplierPayment AS CR ON CR.VoucherNo = CRD2.VoucherNo
        WHERE 
            CRD2.PurchaseNo = @PurchaseNo
            AND CR.SupplierCode = @suppcode
            AND CRD2.VoucherNo <> @voucherno
        GROUP BY 
            CRD2.PurchaseNo;
    END

    RETURN ISNULL(@TotalReceivedAmount, 0);
END



GO
/****** Object:  UserDefinedFunction [dbo].[udf_SelectReceivedAmountOfSupplierforPurchase]    Script Date: 13-01-2025 20:23:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[udf_SelectReceivedAmountOfSupplierforPurchase] ( @purchaseNo nvarchar(500),@suppcode nvarchar(500),@voucherno nvarchar(500))
RETURNS float
AS
BEGIN

RETURN (select isnull(sum(ReceivedAmount),0) as Discount FROM SupplierPaymentDetails inner join SupplierPayment 
 on SupplierPayment.voucherno=SupplierPaymentDetails.VoucherNo 
where SupplierPaymentDetails.PurchaseNo=@purchaseNo  and SupplierPayment.SupplierCode =@suppcode and SupplierPayment.voucherno<>@voucherno )

END


GO
/****** Object:  UserDefinedFunction [dbo].[udf_SelectDiscountAmountOfSupplierforPurchase]    Script Date: 13-01-2025 20:24:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[udf_SelectDiscountAmountOfSupplierforPurchase] ( @purchaseNo nvarchar(500),@suppcode nvarchar(500),@voucherno nvarchar(500))
RETURNS float
AS
BEGIN

RETURN (select isnull(sum(Discount),0) as Discount FROM SupplierPaymentDetails inner join SupplierPayment 
 on SupplierPayment.voucherno=SupplierPaymentDetails.VoucherNo 
where SupplierPaymentDetails.PurchaseNo=@purchaseNo  and SupplierPayment.SupplierCode =@suppcode and SupplierPayment.voucherno=@voucherno )

END









GO
/****** Object:  StoredProcedure [dbo].[SP_GetSupplierPaymentDetailEdit]    Script Date: 13-01-2025 20:21:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[SP_GetSupplierPaymentDetailEdit]
    @id NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    -- Query to fetch payment details for purchases
    SELECT  
        PurchaseNo,
        SupplierPaymentDetails.ReceivedAmount,
        ISNULL(SupplierPaymentDetails.Narration, '') AS Narration,
        Purchase.NetAmount AS PurchaseAmount,
        ISNULL((SELECT SUM(Discount) 
                FROM SupplierPaymentDetails  
                WHERE SupplierPaymentDetails.PurchaseNo = Purchase.DocumentNo), 0) AS DiscountAmt,
        ISNULL((SELECT SUM(ReceivedAmount) 
                FROM SupplierPaymentDetails  
                WHERE SupplierPaymentDetails.PurchaseNo = Purchase.DocumentNo), 0) AS ReceivedAmt,
        FORMAT(SupplierPaymentDetails.PurchaseDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        CASE 
            WHEN (SELECT COUNT(*)   
                  FROM SupplierPaymentDetails AS CRD2   
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo   
                  GROUP BY PurchaseNo) = 1  
            THEN (Purchase.NetAmount)
            ELSE (SELECT (Purchase.NetAmount -  
                          COALESCE(SUM(ReceivedAmount), 0) - 
                          COALESCE(SUM(Discount), 0)) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS TotalReceivedAmount,
        CASE  
            WHEN EXISTS (SELECT 1  
                         FROM SupplierPaymentDetails AS CRD2  
                         WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo  
                           AND CRD2.EntryDate > SupplierPaymentDetails.EntryDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        CASE 
            WHEN (SELECT COUNT(*) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                  GROUP BY PurchaseNo) = 1
            THEN '0'
            ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS ReceivedWithoutInv,
        CASE 
            WHEN (SELECT COUNT(*) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                  GROUP BY PurchaseNo) = 1
            THEN '0'
            ELSE (SELECT COALESCE(SUM(Discount), 0) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS DiscountWithoutInv,
        'P' AS Type
    FROM SupplierPaymentDetails 
    LEFT JOIN Purchase ON SupplierPaymentDetails.PurchaseNo = Purchase.DocumentNo 
    WHERE SupplierPaymentDetails.VoucherNo = @id AND Type = 'P'

    UNION ALL

    -- Query to fetch payment details for purchase expenses
    SELECT  
        PurchaseNo,
        ReceivedAmount,
        ISNULL(SupplierPaymentDetails.Narration, '') AS Narration,
        PurchaseExpenseDetails.RATE AS PurchaseAmount,
        ISNULL((SELECT SUM(Discount) 
                FROM SupplierPaymentDetails  
                WHERE SupplierPaymentDetails.PurchaseNo = PurchaseExpenseDetails.DOCNO), 0) AS DiscountAmt,
        ISNULL((SELECT SUM(ReceivedAmount) 
                FROM SupplierPaymentDetails  
                WHERE SupplierPaymentDetails.PurchaseNo = PurchaseExpenseDetails.DOCNO), 0) AS ReceivedAmt,
        FORMAT(SupplierPaymentDetails.PurchaseDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        CASE 
            WHEN (SELECT COUNT(*)   
                  FROM SupplierPaymentDetails AS CRD2   
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo   
                  GROUP BY PurchaseNo) = 1  
            THEN (PurchaseExpenseDetails.RATE)
            ELSE (SELECT (PurchaseExpenseDetails.RATE -  
                          COALESCE(SUM(ReceivedAmount), 0) - 
                          COALESCE(SUM(Discount), 0)) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS TotalReceivedAmount,
        CASE  
            WHEN EXISTS (SELECT 1  
                         FROM SupplierPaymentDetails AS CRD2  
                         WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo  
                           AND CRD2.EntryDate > SupplierPaymentDetails.EntryDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        CASE 
            WHEN (SELECT COUNT(*) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                  GROUP BY PurchaseNo) = 1
            THEN '0'
            ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS ReceivedWithoutInv,
        CASE 
            WHEN (SELECT COUNT(*) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                  GROUP BY PurchaseNo) = 1
            THEN '0'
            ELSE (SELECT COALESCE(SUM(Discount), 0) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS DiscountWithoutInv,
        'E' AS Type
    FROM SupplierPaymentDetails 
    LEFT JOIN PurchaseExpenseDetails ON SupplierPaymentDetails.PurchaseNo = PurchaseExpenseDetails.purexpno 
    WHERE SupplierPaymentDetails.VoucherNo = @id AND Type = 'E'

    UNION ALL

    -- Query to fetch supplier opening balance payment details
     SELECT  
        SupplierPaymentDetails.PurchaseNo,
        
        SupplierPaymentDetails.ReceivedAmount,
        ISNULL(SupplierPaymentDetails.Narration, '') AS Narration,
        SupplierOPEntryUpdated.PurchaseAmt AS PurchaseAmount,
        ISNULL((SELECT SUM(Discount) FROM SupplierPaymentDetails WHERE SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo), 0) AS DiscountAmt,
	   --isnull([dbo].[udf_SelectReceivedAmountOfCustomerforinvoice](SupplierPaymentDetails.invoiceno,customerreceipt.customercode),0)AS ReceivedAmt,
	   ISNULL((SELECT SUM(ReceivedAmount) FROM SupplierPaymentDetails WHERE SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo and SupplierOPEntryUpdated.SupplierCode=SupplierPayment.SupplierCode), 0) AS ReceivedAmt,
        
		FORMAT(SupplierPaymentDetails.EntryDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
       
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM SupplierPaymentDetails AS CRD2 WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN SupplierOPEntryUpdated.InvoiceAmt
        --    ELSE (SELECT (SupplierOPEntryUpdated.InvoiceAmt - COALESCE(SUM(ReceivedAmount), 0) - COALESCE(SUM(Discount), 0)) 
        --          FROM SupplierPaymentDetails AS CRD2 
        --          WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo  and   SupplierOPEntryUpdated.CustomerCode=CustomerReceipt.CustomerCode
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS TotalReceivedAmount,
		 isnull([dbo].[udf_GetTotalReceivedAmountforSupppayment](SupplierPaymentDetails.purchaseno,supplierpayment.suppliercode,supplierpayment.voucherno,SupplierOPEntryUpdated.PurchaseAmt),0)AS TotalReceivedAmount,
        CASE 
            WHEN EXISTS (SELECT 1 FROM SupplierPaymentDetails AS CRD2 WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo AND CRD2.EntryDate > SupplierPaymentDetails.EntryDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM SupplierPaymentDetails AS CRD2 WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN 0
        --    ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
        --          FROM SupplierPaymentDetails AS CRD2 
        --          WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo 
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS ReceivedWithoutInv,
		  isnull([dbo].[udf_SelectReceivedAmountOfSupplierforPurchase](SupplierPaymentDetails.purchaseno,supplierpayment.suppliercode,supplierpayment.voucherno),0)AS ReceivedWithoutInv,
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM SupplierPaymentDetails AS CRD2 WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN 0
        --    ELSE (SELECT COALESCE(SUM(Discount), 0) 
        --          FROM SupplierPaymentDetails AS CRD2 
        --          WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo 
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS DiscountWithoutInv
		 isnull([dbo].[udf_SelectDiscountAmountOfSupplierforPurchase](SupplierPaymentDetails.purchaseno,supplierpayment.suppliercode,supplierpayment.voucherno),0)AS DiscountWithoutInv,'' as Type 
    FROM   
        SupplierPaymentDetails 
		inner join SupplierPayment  on SupplierPayment.voucherno=SupplierPaymentDetails.VoucherNo
    LEFT JOIN  
        SupplierOPEntryUpdated ON SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo  and SupplierOPEntryUpdated.SupplierCode=SupplierPayment.SupplierCode
    WHERE  
        SupplierPaymentDetails.VoucherNo = @id AND SupplierOPEntryUpdated.PurchaseAmt IS NOT NULL
END

go


GO
/****** Object:  StoredProcedure [dbo].[SP_GetSupplierPaymentDetailEdit]    Script Date: 16-01-2025 14:49:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_GetSupplierPaymentDetailEdit]
    @id NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    -- Query to fetch payment details for purchases
    SELECT  
        SupplierPaymentDetails.PurchaseNo,
        SupplierPaymentDetails.ReceivedAmount,
        ISNULL(SupplierPaymentDetails.Narration, '') AS Narration,
        Purchase.NetAmount AS PurchaseAmount,
        ISNULL((SELECT SUM(Discount) 
                FROM SupplierPaymentDetails  
                WHERE SupplierPaymentDetails.PurchaseNo = Purchase.DocumentNo), 0) AS DiscountAmt,
        ISNULL((SELECT SUM(ReceivedAmount) 
                FROM SupplierPaymentDetails  
                WHERE SupplierPaymentDetails.PurchaseNo = Purchase.DocumentNo), 0) AS ReceivedAmt,
        FORMAT(SupplierPaymentDetails.PurchaseDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        CASE 
            WHEN (SELECT COUNT(*)   
                  FROM SupplierPaymentDetails AS CRD2   
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo   
                  GROUP BY PurchaseNo) = 1  
            THEN (Purchase.NetAmount)
            ELSE (SELECT (Purchase.NetAmount -  
                          COALESCE(SUM(ReceivedAmount), 0) - 
                          COALESCE(SUM(Discount), 0)) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS TotalReceivedAmount,
        CASE  
            WHEN EXISTS (SELECT 1  
                         FROM SupplierPaymentDetails AS CRD2  
                         WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo  
                           AND CRD2.EntryDate > SupplierPaymentDetails.EntryDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        --CASE 
        --    WHEN (SELECT COUNT(*) 
        --          FROM SupplierPaymentDetails AS CRD2 
        --          WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
        --          GROUP BY PurchaseNo) = 1
        --    THEN '0'
        --    ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
        --          FROM SupplierPaymentDetails AS CRD2 
        --          WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
        --            AND CRD2.VoucherNo != @id 
        --          GROUP BY PurchaseNo) 
        --END AS ReceivedWithoutInv,
		isnull([dbo].[udf_SelectReceivedAmountOfSupplierforPurchase](SupplierPaymentDetails.purchaseno,supplierpayment.suppliercode,supplierpayment.voucherno),0)AS ReceivedWithoutInv,
        CASE 
            WHEN (SELECT COUNT(*) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                  GROUP BY PurchaseNo) = 1
            THEN '0'
            ELSE (SELECT COALESCE(SUM(Discount), 0) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS DiscountWithoutInv,
        'P' AS Type
    FROM SupplierPaymentDetails 
    LEFT JOIN Purchase ON SupplierPaymentDetails.PurchaseNo = Purchase.DocumentNo 
	LEFT JOIN SupplierPayment ON SupplierPayment.VoucherNo = SupplierPaymentDetails.VoucherNo 
    WHERE SupplierPaymentDetails.VoucherNo = @id AND SupplierPaymentDetails.Type = 'P'

    UNION ALL

    -- Query to fetch payment details for purchase expenses
    SELECT  
        PurchaseNo,
        ReceivedAmount,
        ISNULL(SupplierPaymentDetails.Narration, '') AS Narration,
        PurchaseExpenseDetails.RATE AS PurchaseAmount,
        ISNULL((SELECT SUM(Discount) 
                FROM SupplierPaymentDetails  
                WHERE SupplierPaymentDetails.PurchaseNo = PurchaseExpenseDetails.DOCNO), 0) AS DiscountAmt,
        ISNULL((SELECT SUM(ReceivedAmount) 
                FROM SupplierPaymentDetails  
                WHERE SupplierPaymentDetails.PurchaseNo = PurchaseExpenseDetails.DOCNO), 0) AS ReceivedAmt,
        FORMAT(SupplierPaymentDetails.PurchaseDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        CASE 
            WHEN (SELECT COUNT(*)   
                  FROM SupplierPaymentDetails AS CRD2   
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo   
                  GROUP BY PurchaseNo) = 1  
            THEN (PurchaseExpenseDetails.RATE)
            ELSE (SELECT (PurchaseExpenseDetails.RATE -  
                          COALESCE(SUM(ReceivedAmount), 0) - 
                          COALESCE(SUM(Discount), 0)) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS TotalReceivedAmount,
        CASE  
            WHEN EXISTS (SELECT 1  
                         FROM SupplierPaymentDetails AS CRD2  
                         WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo  
                           AND CRD2.EntryDate > SupplierPaymentDetails.EntryDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        CASE 
            WHEN (SELECT COUNT(*) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                  GROUP BY PurchaseNo) = 1
            THEN '0'
            ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS ReceivedWithoutInv,
        CASE 
            WHEN (SELECT COUNT(*) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                  GROUP BY PurchaseNo) = 1
            THEN '0'
            ELSE (SELECT COALESCE(SUM(Discount), 0) 
                  FROM SupplierPaymentDetails AS CRD2 
                  WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo 
                    AND CRD2.VoucherNo != @id 
                  GROUP BY PurchaseNo) 
        END AS DiscountWithoutInv,
        'E' AS Type
    FROM SupplierPaymentDetails 
    LEFT JOIN PurchaseExpenseDetails ON SupplierPaymentDetails.PurchaseNo = PurchaseExpenseDetails.purexpno 
    WHERE SupplierPaymentDetails.VoucherNo = @id AND Type = 'E'

    UNION ALL

    -- Query to fetch supplier opening balance payment details
     SELECT  
        SupplierPaymentDetails.PurchaseNo,
        
        SupplierPaymentDetails.ReceivedAmount,
        ISNULL(SupplierPaymentDetails.Narration, '') AS Narration,
        SupplierOPEntryUpdated.PurchaseAmt AS PurchaseAmount,
        ISNULL((SELECT SUM(Discount) FROM SupplierPaymentDetails WHERE SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo), 0) AS DiscountAmt,
	   --isnull([dbo].[udf_SelectReceivedAmountOfCustomerforinvoice](SupplierPaymentDetails.invoiceno,customerreceipt.customercode),0)AS ReceivedAmt,
	   ISNULL((SELECT SUM(ReceivedAmount) FROM SupplierPaymentDetails WHERE SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo and SupplierOPEntryUpdated.SupplierCode=SupplierPayment.SupplierCode), 0) AS ReceivedAmt,
        
		FORMAT(SupplierPaymentDetails.EntryDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
       
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM SupplierPaymentDetails AS CRD2 WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN SupplierOPEntryUpdated.InvoiceAmt
        --    ELSE (SELECT (SupplierOPEntryUpdated.InvoiceAmt - COALESCE(SUM(ReceivedAmount), 0) - COALESCE(SUM(Discount), 0)) 
        --          FROM SupplierPaymentDetails AS CRD2 
        --          WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo  and   SupplierOPEntryUpdated.CustomerCode=CustomerReceipt.CustomerCode
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS TotalReceivedAmount,
		 isnull([dbo].[udf_GetTotalReceivedAmountforSupppayment](SupplierPaymentDetails.purchaseno,supplierpayment.suppliercode,supplierpayment.voucherno,SupplierOPEntryUpdated.PurchaseAmt),0)AS TotalReceivedAmount,
        CASE 
            WHEN EXISTS (SELECT 1 FROM SupplierPaymentDetails AS CRD2 WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo AND CRD2.EntryDate > SupplierPaymentDetails.EntryDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM SupplierPaymentDetails AS CRD2 WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN 0
        --    ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
        --          FROM SupplierPaymentDetails AS CRD2 
        --          WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo 
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS ReceivedWithoutInv,
		  isnull([dbo].[udf_SelectReceivedAmountOfSupplierforPurchase](SupplierPaymentDetails.purchaseno,supplierpayment.suppliercode,supplierpayment.voucherno),0)AS ReceivedWithoutInv,
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM SupplierPaymentDetails AS CRD2 WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN 0
        --    ELSE (SELECT COALESCE(SUM(Discount), 0) 
        --          FROM SupplierPaymentDetails AS CRD2 
        --          WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo 
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS DiscountWithoutInv
		 isnull([dbo].[udf_SelectDiscountAmountOfSupplierforPurchase](SupplierPaymentDetails.purchaseno,supplierpayment.suppliercode,supplierpayment.voucherno),0)AS DiscountWithoutInv,'' as Type 
    FROM   
        SupplierPaymentDetails 
		inner join SupplierPayment  on SupplierPayment.voucherno=SupplierPaymentDetails.VoucherNo
    LEFT JOIN  
        SupplierOPEntryUpdated ON SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo  and SupplierOPEntryUpdated.SupplierCode=SupplierPayment.SupplierCode
    WHERE  
        SupplierPaymentDetails.VoucherNo = @id AND SupplierOPEntryUpdated.PurchaseAmt IS NOT NULL



		union all 

		 SELECT  
        SupplierPaymentDetails.PurchaseNo,
        
        SupplierPaymentDetails.ReceivedAmount,
        ISNULL(SupplierPaymentDetails.Narration, '') AS Narration,
        0 AS PurchaseAmount,
        ISNULL((SELECT SUM(Discount) FROM SupplierPaymentDetails WHERE SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo), 0) AS DiscountAmt,
	   --isnull([dbo].[udf_SelectReceivedAmountOfCustomerforinvoice](SupplierPaymentDetails.invoiceno,customerreceipt.customercode),0)AS ReceivedAmt,
	   ISNULL((SELECT SUM(ReceivedAmount) FROM SupplierPaymentDetails WHERE SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo and SupplierOPEntryUpdated.SupplierCode=SupplierPayment.SupplierCode), 0) AS ReceivedAmt,
        
		FORMAT(SupplierPaymentDetails.EntryDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
       
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM SupplierPaymentDetails AS CRD2 WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN SupplierOPEntryUpdated.InvoiceAmt
        --    ELSE (SELECT (SupplierOPEntryUpdated.InvoiceAmt - COALESCE(SUM(ReceivedAmount), 0) - COALESCE(SUM(Discount), 0)) 
        --          FROM SupplierPaymentDetails AS CRD2 
        --          WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo  and   SupplierOPEntryUpdated.CustomerCode=CustomerReceipt.CustomerCode
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS TotalReceivedAmount,
		 isnull([dbo].[udf_GetTotalReceivedAmountforSupppayment](SupplierPaymentDetails.purchaseno,supplierpayment.suppliercode,supplierpayment.voucherno,SupplierOPEntryUpdated.PurchaseAmt),0)AS TotalReceivedAmount,
        CASE 
            WHEN EXISTS (SELECT 1 FROM SupplierPaymentDetails AS CRD2 WHERE CRD2.PurchaseNo = SupplierPaymentDetails.PurchaseNo AND CRD2.EntryDate > SupplierPaymentDetails.EntryDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM SupplierPaymentDetails AS CRD2 WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN 0
        --    ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
        --          FROM SupplierPaymentDetails AS CRD2 
        --          WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo 
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS ReceivedWithoutInv,
		  isnull([dbo].[udf_SelectReceivedAmountOfSupplierforPurchase](SupplierPaymentDetails.purchaseno,supplierpayment.suppliercode,supplierpayment.voucherno),0)AS ReceivedWithoutInv,
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM SupplierPaymentDetails AS CRD2 WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN 0
        --    ELSE (SELECT COALESCE(SUM(Discount), 0) 
        --          FROM SupplierPaymentDetails AS CRD2 
        --          WHERE CRD2.InvoiceNo = SupplierPaymentDetails.InvoiceNo 
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS DiscountWithoutInv
		 isnull([dbo].[udf_SelectDiscountAmountOfSupplierforPurchase](SupplierPaymentDetails.purchaseno,supplierpayment.suppliercode,supplierpayment.voucherno),0)AS DiscountWithoutInv,'' as Type 
    FROM   
        SupplierPaymentDetails 
		left join SupplierPayment  on SupplierPayment.voucherno=SupplierPaymentDetails.VoucherNo
    LEFT JOIN  
        SupplierOPEntryUpdated ON SupplierPaymentDetails.PurchaseNo = SupplierOPEntryUpdated.PurchaseNo  and SupplierOPEntryUpdated.SupplierCode=SupplierPayment.SupplierCode
	left join	Purchase ON SupplierPaymentDetails.PurchaseNo = Purchase.DocumentNo 
    WHERE  
        SupplierPaymentDetails.VoucherNo = @id AND SupplierOPEntryUpdated.PurchaseAmt is NULL and purchase.NetAmount is null and SupplierPayment.Advance in(1,3) and SupplierPaymentDetails.Type is null
END







GO
/****** Object:  StoredProcedure [dbo].[Sp_GetCustomerReceiptDetailEdit]    Script Date: 16-01-2025 11:05:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Sp_GetCustomerReceiptDetailEdit]
    @VoucherNo NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    -- First SELECT statement
    SELECT  
        CustomerReceiptDetail.InvoiceNo,
        CustomerReceiptDetail.Discount,
        CustomerReceiptDetail.ReceivedAmount,
        ISNULL(CustomerReceiptDetail.Narration, '') AS Narration,
        ISNULL(Invoice.NetAmount,0) AS InvoiceAmount,
        ISNULL((SELECT SUM(ReceivedAmount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = Invoice.InvNo), 0) AS ReceivedAmt,
		
        FORMAT(InvDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        ISNULL((SELECT SUM(Discount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = Invoice.InvNo), 0) AS DiscountAmt,

        CASE 
            WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
            THEN COALESCE(Invoice.NetAmount, 0)
            ELSE COALESCE((SELECT (Invoice.NetAmount - COALESCE(SUM(ReceivedAmount), 0) - COALESCE(SUM(Discount), 0)) 
                  FROM CustomerReceiptDetail AS CRD2 
                  WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
                    AND CRD2.VoucherNo != @VoucherNo 
                  GROUP BY InvoiceNo),0)
        END AS TotalReceivedAmount,
        CASE 
            WHEN EXISTS (SELECT 1 FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo AND CRD2.PaidDate > CustomerReceiptDetail.PaidDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN 0
        --    ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
        --          FROM CustomerReceiptDetail AS CRD2 
        --          WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS ReceivedWithoutInv,
		isnull([dbo].[udf_SelectReceivedAmountOfCustomerforinvoice](CustomerReceiptDetail.invoiceno,customerreceipt.customercode,customerreceipt.voucherno),0)AS ReceivedWithoutInv,
        CASE 
            WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
            THEN 0
            ELSE (SELECT COALESCE(SUM(Discount), 0) 
                  FROM CustomerReceiptDetail AS CRD2 
                  WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
                    AND CRD2.VoucherNo != @VoucherNo 
                  GROUP BY InvoiceNo)
        END AS DiscountWithoutInv
    FROM   
        CustomerReceiptDetail 
		inner join CustomerReceipt  on CustomerReceipt.voucherno=CustomerReceiptDetail.VoucherNo
    LEFT JOIN  
        Invoice ON CustomerReceiptDetail.InvoiceNo = Invoice.InvNo 
    WHERE  
        CustomerReceiptDetail.VoucherNo = @VoucherNo AND Invoice.NetAmount IS NOT NULL

    UNION ALL

    -- Second SELECT statement
    SELECT  
        CustomerReceiptDetail.InvoiceNo,
        CustomerReceiptDetail.Discount,
        CustomerReceiptDetail.ReceivedAmount,
        ISNULL(CustomerReceiptDetail.Narration, '') AS Narration,
        CustomerOPEntryUpdated.InvoiceAmt AS InvoiceAmount,
       
	   --isnull([dbo].[udf_SelectReceivedAmountOfCustomerforinvoice](CustomerReceiptDetail.invoiceno,customerreceipt.customercode),0)AS ReceivedAmt,
	   ISNULL((SELECT SUM(ReceivedAmount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo and CustomerOPEntryUpdated.CustomerCode=CustomerReceipt.CustomerCode), 0) AS ReceivedAmt,
        
		FORMAT(InvDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        ISNULL((SELECT SUM(Discount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo), 0) AS DiscountAmt,
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN CustomerOPEntryUpdated.InvoiceAmt
        --    ELSE (SELECT (CustomerOPEntryUpdated.InvoiceAmt - COALESCE(SUM(ReceivedAmount), 0) - COALESCE(SUM(Discount), 0)) 
        --          FROM CustomerReceiptDetail AS CRD2 
        --          WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo  and   CustomerOPEntryUpdated.CustomerCode=CustomerReceipt.CustomerCode
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS TotalReceivedAmount,
		 isnull([dbo].[udf_GetTotalReceivedAmountforcustrecpt](CustomerReceiptDetail.invoiceno,customerreceipt.customercode,customerreceipt.voucherno,CustomerOPEntryUpdated.InvoiceAmt),0)AS TotalReceivedAmount,
        CASE 
            WHEN EXISTS (SELECT 1 FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo AND CRD2.PaidDate > CustomerReceiptDetail.PaidDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN 0
        --    ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
        --          FROM CustomerReceiptDetail AS CRD2 
        --          WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS ReceivedWithoutInv,
		  isnull([dbo].[udf_SelectReceivedAmountOfCustomerforinvoice](CustomerReceiptDetail.invoiceno,customerreceipt.customercode,customerreceipt.voucherno),0)AS ReceivedWithoutInv,
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN 0
        --    ELSE (SELECT COALESCE(SUM(Discount), 0) 
        --          FROM CustomerReceiptDetail AS CRD2 
        --          WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS DiscountWithoutInv
		  isnull([dbo].[udf_SelectDiscountAmountOfCustomerforinvoice](CustomerReceiptDetail.invoiceno,customerreceipt.customercode,customerreceipt.voucherno),0)AS DiscountWithoutInv
    FROM   
        CustomerReceiptDetail 
		inner join CustomerReceipt  on CustomerReceipt.voucherno=CustomerReceiptDetail.VoucherNo
    LEFT JOIN  
        CustomerOPEntryUpdated ON CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo  and CustomerOPEntryUpdated.CustomerCode=CustomerReceipt.CustomerCode
    WHERE  
        CustomerReceiptDetail.VoucherNo = @VoucherNo AND CustomerOPEntryUpdated.InvoiceAmt IS NOT NULL

		union all 

		 SELECT  
        CustomerReceiptDetail.InvoiceNo,
        CustomerReceiptDetail.Discount,
        CustomerReceiptDetail.ReceivedAmount,
        ISNULL(CustomerReceiptDetail.Narration, '') AS Narration,
        ISNULL(Invoice.NetAmount,0) AS InvoiceAmount,
        ISNULL((SELECT SUM(ReceivedAmount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = Invoice.InvNo), 0) AS ReceivedAmt,
		
        FORMAT(InvDate, 'dd-MM-yyyy') AS EntryDatefrmtd,
        ISNULL((SELECT SUM(Discount) FROM CustomerReceiptDetail WHERE CustomerReceiptDetail.InvoiceNo = Invoice.InvNo), 0) AS DiscountAmt,

        CASE 
            WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
            THEN COALESCE(Invoice.NetAmount, 0)
            ELSE COALESCE((SELECT (Invoice.NetAmount - COALESCE(SUM(ReceivedAmount), 0) - COALESCE(SUM(Discount), 0)) 
                  FROM CustomerReceiptDetail AS CRD2 
                  WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
                    AND CRD2.VoucherNo != @VoucherNo 
                  GROUP BY InvoiceNo),0)
        END AS TotalReceivedAmount,
        CASE 
            WHEN EXISTS (SELECT 1 FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo AND CRD2.PaidDate > CustomerReceiptDetail.PaidDate) 
            THEN 'True' 
            ELSE 'False' 
        END AS EntryAfterPaidDate,
        --CASE 
        --    WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
        --    THEN 0
        --    ELSE (SELECT COALESCE(SUM(ReceivedAmount), 0) 
        --          FROM CustomerReceiptDetail AS CRD2 
        --          WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
        --            AND CRD2.VoucherNo != @VoucherNo 
        --          GROUP BY InvoiceNo)
        --END AS ReceivedWithoutInv,
		isnull([dbo].[udf_SelectReceivedAmountOfCustomerforinvoice](CustomerReceiptDetail.invoiceno,customerreceipt.customercode,customerreceipt.voucherno),0)AS ReceivedWithoutInv,
        CASE 
            WHEN (SELECT COUNT(*) FROM CustomerReceiptDetail AS CRD2 WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo GROUP BY InvoiceNo) = 1
            THEN 0
            ELSE (SELECT COALESCE(SUM(Discount), 0) 
                  FROM CustomerReceiptDetail AS CRD2 
                  WHERE CRD2.InvoiceNo = CustomerReceiptDetail.InvoiceNo 
                    AND CRD2.VoucherNo != @VoucherNo 
                  GROUP BY InvoiceNo)
        END AS DiscountWithoutInv
    FROM   
        CustomerReceiptDetail 
		inner join CustomerReceipt  on CustomerReceipt.voucherno=CustomerReceiptDetail.VoucherNo

    LEFT JOIN  
        Invoice ON CustomerReceiptDetail.InvoiceNo = Invoice.InvNo 
		LEFT JOIN  
        CustomerOPEntryUpdated ON CustomerReceiptDetail.InvoiceNo = CustomerOPEntryUpdated.InvoiceNo  and CustomerOPEntryUpdated.CustomerCode=CustomerReceipt.CustomerCode
    WHERE  
        CustomerReceiptDetail.VoucherNo = @VoucherNo AND Invoice.NetAmount IS NULL AND CustomerOPEntryUpdated.InvoiceAmt IS NULL and CustomerReceipt.Advance in(1,3)
END

go

ALTER TABLE ProformaInvoiceItem
ADD BranchName varchar(50), 
    BranchCode varchar(50);

ALTER TABLE ProformaInvoice
ADD BranchName varchar(50), 
    BranchCode varchar(50);


ALTER TABLE ProformaInvoice
ALTER COLUMN hijridate NVARCHAR(50);

ALTER TABLE ProformaInvoiceItem
ADD SubId INT;


ALTER TABLE ProformaInvoiceItem
ADD NetDiscPer FLOAT,
    NetDiscAmt FLOAT,
    NetVatAmt FLOAT;

ALTER TABLE ProformaInvoice
ADD DiscountType NVARCHAR(250);


alter table ProformaInvoice
Add TotalIncDiscAmt nvarchar(500);

alter table ProformaInvoiceItem
add LineTotal float;



insert into setup(code,SValue) values ('JobOrderno','1000');

alter table item
add ModelNo nvarchar(500);

alter table permanentsettings add Replacement varchar(50);

alter table permanentsettings add Billtype varchar(50);

alter table permanentsettings add CashAccount varchar(250);

alter table permanentsettings add BankAccount varchar(250);

alter table permanentsettings add CashAccountCode varchar(250);

alter table permanentsettings add BankAccountCode varchar(250);


go

create PROCEDURE sp_ProfInvoiceDetails  
    @fromdate DATE,  
    @todate DATE,  
    @CustCode NVARCHAR(50),  
    @BranchCode NVARCHAR(50),  
    @ItemCode NVARCHAR(50)  
AS  
BEGIN  
    SELECT 
        PI.Id,  
        PI.Branch,  
        PI.EntryDate,  
        PI.InvNo,  
        PI.ReturnInvoiceNo,  
        PI.BillType,  
        PI.RefNo,  
        PI.PONo,  
        PI.CustomerCode,  
        PI.CustomerName,  
        PI.Address,  
        PI.Phone,  
        PI.Fax,  
        PI.Attended,  
        PI.GrossAmount,  
        PI.LessPerc,  
        PI.LessAmount,  
        PI.NetAmount,  
        PI.SR,  
        PI.AddLess,  
        PI.Remarks,  
        PI.Received,  
        PI.DnNo,  
        PI.QuotNo,  
        PI.Flag,  
        PI.CreditPeriod,  
        PI.ReceivedAmount,  
        PI.UserName,  
        PI.InvoiceDate,  
        PI.Narration,  
        PI.voucherNo,  
        PI.Commision,  
        PI.Status,  
        PI.Bank1,  
        PI.Bank2,  
        PI.Bank3,  
        PI.Bank4,  
        PI.PODate,  
        PI.Freight,  
        PI.Retention,  
        PI.DelDate,  
        PI.ProjectNo,  
        PI.Deleted,  
        PI.AccountCode,  
        PI.AccountName,  
        PI.hijridate,  
        PI.Mobile,  
        PI.vattotal,  
        PI.Vatno,  
        PI.crdays,  
        PI.invdueDate,  
        PI.BranchName,  
        PI.BranchCode,  
        PI.TotalIncDiscAmt,  
        PII.ItemCode,  
        PII.ItemName,  
        PII.Damage,  
        PII.Unit,  
        PII.Qty,  
        PII.Rate,  
        PII.Amount,  
        PII.SR AS ItemSR,  
        PII.Cost,  
        PII.InvoiceDate AS ItemInvoiceDate,  
        PII.DocumentNo,  
        PII.InvNo AS ItemInvNo,  
        PII.SQTY,  
        PII.Sales,  
        PII.QtyPerUnit,  
        PII.TOTQty,  
        PII.PartNo,  
        PII.stdcost,  
        PII.RateAfterDisc,  
        PII.RetQty,  
        PII.DNNo,  
        PII.VATPER,  
        PII.VATAMT,  
        PII.DiscPer,  
        PII.DiscAmt,  
        PII.LineTotal  
    FROM 
        ProformaInvoice PI  
    LEFT JOIN 
        ProformaInvoiceItem PII ON PI.InvNo = PII.InvNo  
    WHERE  
        PI.EntryDate BETWEEN @fromdate AND @todate  
        AND (@CustCode IS NULL OR @CustCode = '' OR PI.CustomerCode = @CustCode)  
    AND (@BranchCode IS NULL OR @BranchCode = '' OR PI.BranchCode = @BranchCode)  
    AND (@ItemCode IS NULL OR @ItemCode = '' OR PII.ItemCode = @ItemCode);
END;

go

create PROCEDURE sp_ProfInvoiceRegister  
    @fromdate DATE,  
    @todate DATE,  
    @CustCode NVARCHAR(50),  
    @BranchCode NVARCHAR(50),  
    @BillType NVARCHAR(50)  
AS  
BEGIN  
    SELECT 
        PI.Id,  
        PI.Branch,  
        PI.EntryDate,  
        PI.InvNo,  
        PI.ReturnInvoiceNo,  
        PI.BillType,  
        PI.RefNo,  
        PI.PONo,  
        PI.CustomerCode,  
        PI.CustomerName,  
        PI.Address,  
        PI.Phone,  
        PI.Fax,  
        PI.Attended,  
        PI.GrossAmount,  
        PI.LessPerc,  
        PI.LessAmount,  
        PI.NetAmount,  
        PI.SR,  
        PI.AddLess,  
        PI.Remarks,  
        PI.Received,  
        PI.DnNo,  
        PI.QuotNo,  
        PI.Flag,  
        PI.CreditPeriod,  
        PI.ReceivedAmount,  
        PI.UserName,  
        PI.InvoiceDate,  
        PI.Narration,  
        PI.voucherNo,  
        PI.Commision,  
        PI.Status,  
        PI.Bank1,  
        PI.Bank2,  
        PI.Bank3,  
        PI.Bank4,  
        PI.PODate,  
        PI.Freight,  
        PI.Retention,  
        PI.DelDate,  
        PI.ProjectNo,  
        PI.Deleted,  
        PI.AccountCode,  
        PI.AccountName,  
        PI.hijridate,  
        PI.Mobile,  
        PI.vattotal,  
        PI.Vatno,  
        PI.crdays,  
        PI.invdueDate,  
        PI.BranchName,  
        PI.BranchCode,  
        PI.TotalIncDiscAmt,  
        PII.ItemCode,  
        PII.ItemName,  
        PII.Damage,  
        PII.Unit,  
        PII.Qty,  
        PII.Rate,  
        PII.Amount,  
        PII.SR AS ItemSR,  
        PII.Cost,  
        PII.InvoiceDate AS ItemInvoiceDate,  
        PII.DocumentNo,  
        PII.InvNo AS ItemInvNo,  
        PII.SQTY,  
        PII.Sales,  
        PII.QtyPerUnit,  
        PII.TOTQty,  
        PII.PartNo,  
        PII.stdcost,  
        PII.RateAfterDisc,  
        PII.RetQty,  
        PII.DNNo,  
        PII.VATPER,  
        PII.VATAMT,  
        PII.DiscPer,  
        PII.DiscAmt,  
        PII.LineTotal  
    FROM 
        ProformaInvoice PI  
    LEFT JOIN 
        ProformaInvoiceItem PII ON PI.InvNo = PII.InvNo  
    WHERE  
        PI.EntryDate BETWEEN @fromdate AND @todate  
        AND  (@CustCode IS NULL OR @CustCode = '' OR PI.CustomerCode = @CustCode)  
    AND (@BranchCode IS NULL OR @BranchCode = '' OR PI.BranchCode = @BranchCode)  
    AND (@BillType IS NULL OR @BillType = '' OR PI.BillType = @BillType);
END;

go 

create PROCEDURE [dbo].[sp_SaveProformaInvoice]  
 @InvNo varchar(50)  
  
AS  
begin  
SELECT ProformaInvoice.DELDate,ProformaInvoice.Branch,ProformaInvoice.CreditPeriod,ProformaInvoice.Bank1,ProformaInvoice.Bank2,ProformaInvoice.Bank3,ProformaInvoice.Bank4,  
ProformaInvoice.Del1,ProformaInvoice.Del2,ProformaInvoice.Del3,ProformaInvoice.Del4,ProformaInvoice.PODate,ProformaInvoice.Freight,ProformaInvoice.Retention,ProformaInvoice.BillType,  
ProformaInvoice.InvNo,ProformaInvoice.EntryDate,ProformaInvoice.PONo,ProformaInvoice.DnNo,ProformaInvoice.RefNo,ProformaInvoice.CustomerCode,ProformaInvoice.CustomerName,  
ProformaInvoice.Address,ProformaInvoice.Phone,ProformaInvoice.Fax,ProformaInvoice.Attended,ProformaInvoice.Narration,ProformaInvoice.GrossAmount,ProformaInvoice.NetAmount,  
ProformaInvoice.LessAmount + dbo.ProformaInvoice.AddLess AS less,ProformaInvoiceItem.SlNo,ProformaInvoiceItem.ItemCode,ProformaInvoiceItem.ItemName,  
ProformaInvoiceItem.PartNo,ProformaInvoiceItem.Rate,ProformaInvoiceItem.Unit,ProformaInvoiceItem.Qty,ProformaInvoiceItem.Amount,  
CONVERT(nchar, isnull(HijriDate,'01/01/1900'), 131) as HijriDate,isnull(ProformaInvoice.QuotNo,'') as QuotNo  
,ISNULL(ProformaInvoiceITEM.VATPER,0) AS VATPER,ISNULL(ProformaInvoiceITEM.VATAMT,0)AS VATAMT,ISNULL(ProformaInvoice.VATTOTAL,0)AS VATTOTAL  
,ISNULL(ProformaInvoice.Vatno,'')AS Vatno,isnull(ProformaInvoiceitem.rateafterdisc,0) as Rateafterdisc,ISNULL(ProformaInvoice.REMARKS1,'') AS REMARKSS,ProformaInvoice.MOBILE  
,ISNULL(CONVERT(VARCHAR(25),D.ENTRYDATE,103),CONVERT(VARCHAR(25),ProformaInvoice.ENTRYDATE,103)) AS DNDATE,ISNULL(C.namear,'') as Arabicname,isnull(C.address1ar,'') as ArabicAddress   
 ,CONVERT(VARCHAR(25),ProformaInvoice.EntryDate,103) 'DATE' ,  
 dbo.currency_conversion(ProformaInvoice.NetAmount) as ARABICNETAMOUNT  
 FROM ProformaInvoice INNER JOIN ProformaInvoiceItem ON ProformaInvoice.InvNo = ProformaInvoiceItem.InvNo AND ProformaInvoice.SR = 'Sales'and  ProformaInvoiceItem.SR = 'Sales'   
LEFT JOIN DELIVERYNOTE D ON D.DNNO=ProformaInvoiceITEM.DNNO LEFT JOIN CUSTOMER C ON C.CODE=ProformaInvoice.CUSTOMERCODE where ProformaInvoice.InvNo=@InvNo  
  
  
end  

go

DISABLE TRIGGER ALL ON ProformaInvoice;

alter table invoiceitem add ModelNo varchar(500);

alter table QuotationItem add ModelNo varchar(500);

alter table DeliveryNoteItem add ModelNo varchar(500);

CREATE TABLE [dbo].[Replacement](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[itemcode] [varchar](500) NULL,
	[itemname] [varchar](500) NULL,
	[replacementcode] [varchar](500) NULL,
	[replacementname] [varchar](500) NULL,
	[remarks] [varchar](500) NULL,
	[docno] [varchar](500) NULL,
	[EntryDate] [datetime] NULL,
	[BranchCode] [varchar](250) NULL,
	[BranchName] [varchar](250) NULL,
PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

USE [Web_Infogate]
GO

/****** Object:  Table [dbo].[Repair]    Script Date: 22-01-2025 13:23:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Repair](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Code] [nvarchar](50) NOT NULL,
	[Name] [nvarchar](500) NOT NULL,
	[Remarks] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

USE [Web_Infogate]
GO

/****** Object:  Table [dbo].[RepairDetails]    Script Date: 22-01-2025 13:24:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[RepairDetails](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[EntryDate] [datetime] NOT NULL,
	[JobOrderNo] [nvarchar](50) NOT NULL,
	[RepairCode] [nvarchar](500) NULL,
	[RepairName] [nvarchar](500) NULL,
	[TechnicianCode] [nvarchar](500) NULL,
	[TechnicianName] [nvarchar](500) NULL,
	[Qty] [int] NULL,
	[Rate] [decimal](18, 2) NULL,
	[Total] [decimal](18, 2) NULL,
	[VatPer] [decimal](5, 2) NULL,
	[VatAmt] [decimal](18, 2) NULL,
	[LineTotal] [decimal](18, 2) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

USE [Web_Infogate]
GO

/****** Object:  Table [dbo].[Technician]    Script Date: 22-01-2025 13:24:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Technician](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Code] [nvarchar](50) NOT NULL,
	[Name] [nvarchar](500) NOT NULL,
	[Position] [nvarchar](500) NULL,
	[Mobile] [nvarchar](150) NULL,
	[Remarks] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

USE [Web_Infogate]
GO

/****** Object:  Table [dbo].[VehicleType]    Script Date: 22-01-2025 13:24:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[VehicleType](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Code] [nvarchar](50) NOT NULL,
	[Name] [nvarchar](500) NOT NULL,
	[Remarks] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO