Installment system with invoice dividing in VB.NET

[linuxuser85]

Hello!
I'm fighting with something for a couple of weeks and I really need help from You because it's too difficult for me. There are many debtor customers of the company I work in. Sometimes they cannot pay off all debt so they would like to pay off their arrears in installment/loan system. I'm working on it using VB.NET. I have to prepare the shedule which will divide every installment for individual invoices. I could easily split invoices to installments with payment deadlines for each one, but after that, in the second step, I have to divide each installment on invoices depending on amounts. There is an example of this accounting made manually in Excel for 2 exemplary invoices below.



Invoice table comes from PostgreSQL query, but the simple shedule (2nd table) is generated automaticly where total amount of debt is known. The shedule can be also created manually by user, where the user can add installment by installment with various amounts and payment deadlines. Everything is calculated on grids.

I know that loops used in the code should have 2 "if" conditions: Condition1: Installment amonunt < Invoice amount and Condition2: Installment amonunt >= Invoice amount, but app user (employee of the debt collection department) can generate mixed shedule (various amounts of each installment) where both of condition can be met. Additionally the simplification is that the program will not charge interest at all (higher level[! decision). I'll be very thankful for any help or any clue to solve this problem. Maybe some of You have had such a problem before.

I would like not to use database to fill 3rd table. I'd like to ask You for help, how to write a loop (probably 'while' loop) to fill the 3'rd datagridview based on data in two first grids. I think that I should use something like this:

  Dim i As Integer = 0
            Dim j As Integer = 0

            While sheduleGrid.Rows(j).Cells("installmentnumber").Value <= sheduleGrid.Rows(sheduleGrid.Rows.Count - 1).Cells("installmentnumber").Value And invoicesGrid.Rows(i).Cells("invoicenumber").Value <= invoicesGrid.Rows(invoicesGrid.Rows.Count - 1).Cells("invoicenumber").Value
'some conditions
            End While

Could You help me to write a loop which will fill 3rd grid based on invoices grid and shedule grid without using SQL? Insert-to-PostgreSQL-table query in my DB will be used when the settlement grid is complete. Thanks in advance!

[ChrisE]

why not use SQL ?
but here you go, here a way to calculate the values in a Datagridview

Public Class Form6

    Private Sub Form6_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        'Data
        With DataGridView1
            .Columns.Add("InvoiceId", "InvoiceId")
            .Columns.Add("Customer", "Customer")
            .Columns.Add("Invoice Amount", "Invoice Amount")
            .Columns.Add("Pais", "Paid")
            .Rows.Add(1, "A", 145.45, 0.0)
            .Rows.Add(2, "B", 132.16, 0.0)
            .Rows.Add(1, "A", 145.45, 5.25)
            .Rows.Add(3, "C", 123.51, 0.0)
            .Rows.Add(2, "B", 132.16, 4.45)
            .Rows.Add(3, "C", 123.78, 64.12)
            .Rows.Add(3, "C", 123.78, 8.78)
        End With
        'for Payments:
        With DataGridView2
            .Columns.Add("InvoiceId", "InvoiceId")
            .Columns.Add("Customer", "Customer")
            .Columns.Add("Invoice Amount", "Invoice Amount")
            .Columns.Add("Paid", "Paid")
            .Columns.Add("Balance", "Balance")
        End With
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim names = From row As DataGridViewRow In _
              DataGridView1.Rows.Cast(Of DataGridViewRow)() _
                  Where Not row.IsNewRow _
                      Select (CInt(row.Cells(0).Value)) Distinct

        For Each n In names
            Dim nArtikel As String = String.Empty
            Dim nEingang As Double = 0.0
            Dim nAusgang As Double = 0.0
            Dim nBestand As Double = 0.0
            For i As Integer = 0 To DataGridView1.Rows.Count - 1
                If CInt(DataGridView1.Rows(i).Cells(0).Value) = n Then
                    If DataGridView1.Rows(i).Cells(2).Value IsNot DBNull.Value Then
                        nArtikel = CStr(DataGridView1.Rows(i).Cells(1).Value)
                        nEingang = CDbl(DataGridView1.Rows(i).Cells(2).Value)
                        nAusgang += CDbl(DataGridView1.Rows(i).Cells(3).Value)
                    End If
                End If
                nBestand = nEingang - nAusgang
            Next
            DataGridView2.Rows.Add(n, nArtikel.ToString, nEingang.ToString("c"), nAusgang.ToString("c"), nBestand.ToString("c"))
        Next
    End Sub
End Class

Image of the Datagridviews

[linuxuser85]

Hello!
Thanks for quick reply, but this is not exactly what I need. I asked this question in another forum and got the following result:




This works correctly but written with SQL usage and adding special moduls etce - too difficult for me.

Let me explain it again. I think my previous explanation could be imprecise, so there is another example below:

1. The debtor has two invoices to pay. Invoice A - amount: 100,00; Invoice B - amount: 50,00; TOTAL: 150,00
2. My app user prepares the shedule: 3 installments of 50,00
3. When we have invoice grid (taken from DB) and the shedule grid (generated by app user), we have to account each installment to invoice using this shedule.
4. Third table should contain following rows:
Invoice A with installment 1 - value 50,00 (invA>inst1)
Invoice A with installment 2 - value 50,00 (remainder 50,00 from InvA)
Invoice B with installment 3 - value 50,00 (whole Invoice B)

This is when Invoice Amount< Installment

When we rise installment to be greater than invoice, for Example 120,00 (INST1 - 120,00, INST2 - 30,00), third table should contain following rows:
Invoice A with part of installment 1 - value 100,00 (invA<inst1)
Invoice B with remainder of installment 1 - value 20,00
Invoice B with installment 2 - value 30,00

In other words, I have to loop over invoice grid and shedule grid and account each invoice using installment shedule with order like in the example above.

I don't know if this explanation and screens from another forum are clear to understand, but I cannot describe it any other way. Thank You in advance again!

[ChrisE]

I can hardly see those images, in what other forum did you post? perhaps
the images are better to see there

[linuxuser85]

Hi there!
There's the link to my post on this forum I wrote above:

https://stackoverflow.com/questions/...ding-in-vb-net

[ChrisE]

well you are working with a Database, so would it not be time to work with SQL ?
you have a working sample and the User gave instructions in the other forum to extend that.

so I don't know how I could help, what have you tried and done sofar setting up your grids like you discribe here...

In other words, I have to loop over invoice grid and shedule grid and account each invoice using installment shedule with order like in the example above.

[ChrisE]

I did look at the code you got from the other forum, and it does look a bit complicated.

see if this is more clear.
first Step:
it will create a Paymentplan and insert that to a Table in Access (don't know what Database you have)

Imports System.Data.OleDb

Public Class Form1

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        'AddInstallmentplan parameters:
        '1) how many Installments = 8
        '2) the Invoice amount = 2785.12
        '3) Date of first payment = 01.08.2021
        '4) the Invoicenumber = 567

        AddInstallmentplan(8, 2785.12, CDate("01.08.2021"), 567)
    End Sub

    Public Sub AddInstallmentplan(ByVal nPayments As Integer, _
                       ByVal InvoiceAmount As Double, _
                       ByVal PaymentDate As Date, _
                       ByVal InvoiceNum As Integer)

        Dim sDB As String = "D:\TestCheck.mdb"
        Dim sCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                "Data Source=" & sDB & ";"

        Dim InstallPayments As Double = FormatNumber(InvoiceAmount / nPayments, 2)
        Dim Con As OleDb.OleDbConnection = New OleDb.OleDbConnection(sCon)
        Con.Open()

        'Create the command and Insert Paymentplan to Database
        For iCounter = 1 To nPayments Step 1
            Dim sql = <sql>
                Insert Into tbl_PaymentPlan
                (PaymentNr,PaymentAmount,PaymentDate,InvoiceNumber) Values (?,?,?,?)
        </sql>.Value

            Dim cmd As New OleDb.OleDbCommand(sql, Con)
            cmd.Parameters.AddWithValue("PaymentNr", iCounter)
            cmd.Parameters.AddWithValue("PaymentAmount", InstallPayments)
            cmd.Parameters.AddWithValue("PaymentDate", PaymentDate.AddMonths(iCounter - 1))
            cmd.Parameters.AddWithValue("InvoiceNumber", InvoiceNum)
            cmd.ExecuteNonQuery()
        Next
        Con.Close()
    End Sub
End Class

this is what the Access table looks like

[linuxuser85]

I use PostgreSQL. I even tried to cross join function on two first tables, but without common data column it looks like this:

Completely useless Cross Join Function only shows all data from those tables, but cannot prepare the settlement.
I'd like to insert only final/calculated table to SQL. Two first tables (invoice table and installment tables) are temporary prepared directly to DGV's. I would like to loop those two DGV's to get result in 3rd table and then the 3rd table should be uploaded to the DB. I'm looking for the solution (loop like 'while loop') that will manipulate those two DGV's to get the settlement.

[ChrisE]

with PostgreSQL you can create Pivot table
see here
https://blog.devart.com/pivot-tables-in-postgresql.html

[linuxuser85]

Yes, I know this function of Postgres and use it in other modules of my app but pivot table cannot create settlement. I saw this article You sent me but pivot table will work if there is a common column. There's no that column in first two tables of my module. Neither invoice table nor installment table have common data. It must be 'While loop' in VB.NET code.

[ChrisE]

Yes, I know this function of Postgres and use it in other modules of my app but pivot table cannot create settlement. I saw this article You sent me but pivot table will work if there is a common column. There's no that column in first two tables of my module. Neither invoice table nor installment table have common data. It must be 'While loop' in VB.NET code.

you need to add the Invoicenumber to the other Table(Paymentplan)

see my Example in Post#7
you need a reference to Join Tables :
Master Table = the Data with Invoice Number, Date of Invoice etc...
Detail Table = the Paymentplan with the amount of Payments etc...

Neither invoice table nor installment table have common data

you have to change that, or you will get nowhere!
they both need a Invoicenumber to perform a Loop, a Sql Join, for use in DataSet ..etc...

[linuxuser85]

you need to add the Invoicenumber to the other Table(Paymentplan)

It's not easy, because I have to know how many invoices are included in one installment or how many installments are included in one invoice.

Let me show You my app using screenshot below (It's in Polish, so I translated some part of it to English for You):




In first step, app user searches a debtor/customer and after that his invoices are loaded to 1st DGV:

Second step - user chooses between autoschedule (above) and manual schedule (below):

After filling amount textbox and date of first installment (1st option), he clicks the "clock button" which calculate 2nd DGV as shown on the screen (without database using):


I would like to have a code which will loop on 1st and 2nd DGV and create settlement like on my first post in this thread.

I think most likely I would have to use a couple of variables based on original invoice amount and installment amount but those variables would have to be calculated (sum/substract) depending on some conditions to be used as cell in dgv3.Rows.Add(X,Y,Z) formula. The problem is that I'm unable to write a loop (probably 'while loop') which will correctly manipulate those variables and insert rows to 3rd table.

Is there any chance that I would ask you to write that loop? Thank You in advance!

[ChrisE]

this is where your Database design is wrong...

It's not easy, because I have to know how many invoices are included in one installment or how many installments are included in one invoice.

each Invoice not fully paid should have one Paymentplan say the Invoice is 500,00 and 400,00 is paid.
a Paymentplane mixed up with 5 Invoices doesn't make sense and who ever does the Book's will go crazy.
...does this Payment belong to Invoice 1234 or 1235...

[linuxuser85]

Done!

Two while loops with invoice id and installment id incrementation made it clear! Here is the screenshot:
Attachment 181345

[ChrisE]

Done!

Two while loops with invoice id and installment id incrementation made it clear! Here is the screenshot:
Attachment 181345

well if you solved it, you should show your solution, and then mark this thread as Resolved with the Thread Tools.

your attachment doesn't work!