Results 1 to 15 of 15

Thread: Installment system with invoice dividing in VB.NET

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2021
    Posts
    7

    Unhappy Installment system with invoice dividing in VB.NET

    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.

    Name:  installments.jpg
Views: 182
Size:  62.0 KB

    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:
    Code:
      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!

  2. #2
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,795

    Re: Installment system with invoice dividing in VB.NET

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

    Code:
    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
    Name:  Balance.jpg
Views: 106
Size:  53.0 KB
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  3. #3

    Thread Starter
    New Member
    Join Date
    May 2021
    Posts
    7

    Re: Installment system with invoice dividing in VB.NET

    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:

    Name:  x13QA.jpg
Views: 87
Size:  16.5 KB
    Name:  Snaa7.jpg
Views: 90
Size:  24.5 KB

    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!

  4. #4
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,795

    Re: Installment system with invoice dividing in VB.NET

    I can hardly see those images, in what other forum did you post? perhaps
    the images are better to see there
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  5. #5

    Thread Starter
    New Member
    Join Date
    May 2021
    Posts
    7

    Re: Installment system with invoice dividing in VB.NET

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

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

  6. #6
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,795

    Re: Installment system with invoice dividing in VB.NET

    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.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  7. #7
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,795

    Re: Installment system with invoice dividing in VB.NET

    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)
    Code:
    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
    Name:  Paymentplan.jpg
Views: 63
Size:  22.8 KB
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  8. #8

    Thread Starter
    New Member
    Join Date
    May 2021
    Posts
    7

    Re: Installment system with invoice dividing in VB.NET

    I use PostgreSQL. I even tried to cross join function on two first tables, but without common data column it looks like this:
    Name:  postgres.jpg
Views: 52
Size:  34.8 KB
    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.

  9. #9
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,795

    Re: Installment system with invoice dividing in VB.NET

    with PostgreSQL you can create Pivot table
    see here
    https://blog.devart.com/pivot-tables-in-postgresql.html
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  10. #10

    Thread Starter
    New Member
    Join Date
    May 2021
    Posts
    7

    Re: Installment system with invoice dividing in VB.NET

    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.

  11. #11
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,795

    Re: Installment system with invoice dividing in VB.NET

    Quote Originally Posted by linuxuser85 View Post
    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...
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  12. #12

    Thread Starter
    New Member
    Join Date
    May 2021
    Posts
    7

    Re: Installment system with invoice dividing in VB.NET

    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):

    Name:  app screen.jpg
Views: 46
Size:  34.1 KB


    In first step, app user searches a debtor/customer and after that his invoices are loaded to 1st DGV:
    Name:  1.jpg
Views: 43
Size:  14.4 KB
    Second step - user chooses between autoschedule (above) and manual schedule (below):
    Name:  2.jpg
Views: 44
Size:  36.7 KB
    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):
    Name:  4.jpg
Views: 43
Size:  26.7 KB

    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!
    Attached Images Attached Images  

  13. #13
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,795

    Re: Installment system with invoice dividing in VB.NET

    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...
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  14. #14

    Thread Starter
    New Member
    Join Date
    May 2021
    Posts
    7

    Lightbulb Re: Installment system with invoice dividing in VB.NET

    Done!

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

  15. #15
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,795

    Re: Installment system with invoice dividing in VB.NET

    Quote Originally Posted by linuxuser85 View Post
    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!
    Last edited by ChrisE; May 7th, 2021 at 10:32 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width