vb.net - VB.NET中带发票分割的分期付款系统

Question

我和某事争吵了几天，我真的需要你的帮助，因为这对我来说太难了。我工作的公司有很多债务人客户。有时他们无法还清所有债务，所以他们想用分期付款/贷款系统偿还欠款。我正在使用 VB.NET 进行处理。我必须准备将每个分期付款的单张发票。我可以轻松地将发票拆分为分期付款，每个分期付款截止日期，但在那之后，在第二步中，我必须在发票上分期付款。下面是一个在 Excel 中为以下 2 个示例发票手动制作的会计示例。我知道代码中使用的循环应该有2个“if”条件：条件1：分期付款<发票金额和条件2：分期付款> =发票金额，但应用程序用户（收债部门的员工）可以生成满足这两个条件的混合表（每期的不同金额）。此外，简化是该程序根本不会收取利息（更高级别[！决定）。我将非常感谢任何帮助或解决此问题的任何线索。也许你们中的一些人之前遇到过这样的问题提前谢谢！

标签： vb.netinvoiceaccounting

Answer 1

这是数据库图和一些数据示例

这是程序界面

我将输入我想要安排的分期付款数量和分期付款前缀以生成分期付款的交易编号。

这是代码

Imports System.Data.SqlClient

Public Class frmInstalment

    Private Sub btnGenerateInstalment_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGenerateInstalment.Click

        Dim TotalOustanding = Modws.GetDecimalFromQuery("Select Sum(amtunmatched) from Invoice")
        Dim InstNo As Integer = Val(txtInstalmentNo.Text)
        Dim Balance = TotalOustanding

        Dim i As Integer = 1

        Do Until i = InstNo + 1

            Dim InstalAmount = Math.Ceiling(TotalOustanding / InstNo)
            Balance = Balance - InstalAmount

            Dim Amount As Decimal

            If Balance > 0 Then
                Amount = InstalAmount
            Else
                Amount = InstalAmount + Balance
            End If


            Dim InstalNo As String = txtInsalPre.Text & i.ToString("00")

            Modws.UpdateItem("Insert into Instalment(TransNo,Instalment,Amount) Values('" & InstalNo & "'," & i & ", " & Amount & ")")

            i = i + 1
        Loop

        Modws.DisplayDataGrid(dgvInstalment, "Select TransNo,Instalment,Amount from Instalment Order by TransNo")

        For x = 0 To dgvInstalment.Rows.Count - 1
            AddMatching(dgvInstalment("TransNo", x).Value, Val(dgvInstalment("Amount", x).Value))
        Next

        Modws.DisplayDataGrid(dgvResult, "Select * from Matching Order by Instalment")

    End Sub


    Private Sub AddMatching(InstalmentNo As String, InstAmt As Decimal)

        Dim StrConn As String = My.Settings.ImportLinkCS
        Dim CN = New SqlConnection(StrConn)


        CN.Open()

        Dim StrSql As String = "Select * from Invoice Where AMTUNMATCHED <> 0 order by transdate, TransNo"

        Dim cmdReader As SqlCommand = New SqlCommand(StrSql, CN)
        cmdReader.CommandType = CommandType.Text

        Dim SdrReader As SqlDataReader = cmdReader.ExecuteReader(CommandBehavior.CloseConnection)
        'SdrReader = cmdReader.ExecuteReader

        Try
            With SdrReader
                If .HasRows Then
                    While .Read

                        If .Item("Amtunmatched") > InstAmt Then
                            Modws.UpdateItem("Update Invoice set amtmatched = amtmatched + " & InstAmt & ",amtunmatched =  amtunmatched - " & InstAmt & " Where TransNo = '" & .Item("TransNo") & "'")
                            Modws.UpdateItem("Insert into Matching(Invoice,Instalment,Amtmached,InvOutstanding) " + _
                                             "Values('" & .Item("TransNo") & "','" & InstalmentNo & "', " & InstAmt & "," & (.Item("Amtunmatched") - InstAmt) & " )")

                            Exit Sub
                        Else

                            Modws.UpdateItem("Update Invoice set amtmatched = Total,amtunmatched = 0 Where TransNo = '" & .Item("TransNo") & "'")
                            Modws.UpdateItem("Insert into Matching(Invoice,Instalment,Amtmached,InvOutstanding) " + _
                                             "Values('" & .Item("TransNo") & "','" & InstalmentNo & "'," & .Item("AMTUNMATCHED") & ",0 )")

                            InstAmt = InstAmt - .Item("Amtunmatched")



                        End If

                    End While
                End If
            End With
        Catch ex As System.Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
        End Try



    End Sub

    Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click

        Modws.DeleteItem("Delete from Instalment")
        Modws.DeleteItem("Delete from Matching")
        Modws.UpdateItem("Update Invoice set amtmatched = 0, amtunmatched = Total")
    End Sub
End Class

您还需要在程序中添加一个模块类

Imports System.Data
Imports System.Data.SqlClient
Imports ImportLink.GridPrintPreviewLib

Module Modws

  Public Function GetDecimalFromQuery(ByVal SQLQuery As String) As Decimal
        Dim StrConn As String = My.Settings.ImportLinkCS
        Dim CN = New SqlConnection(StrConn)

        GetDecimalFromQuery = 0

        CN.Open()

        Dim StrSql As String = SQLQuery

        Dim cmdReader As SqlCommand = New SqlCommand(StrSql, CN)
        cmdReader.CommandType = CommandType.Text

        Dim SdrReader As SqlDataReader = cmdReader.ExecuteReader(CommandBehavior.CloseConnection)
        'SdrReader = cmdReader.ExecuteReader
        'GetDoubleFromQuery = 0
        Try
            With SdrReader
                If .HasRows Then
                    While .Read
                        If .GetValue(0) Is DBNull.Value Then
                            'GetDoubleFromQuery = 20
                            'MsgBox("Null Value")
                        Else
                            If IsDBNull(.GetValue(0).ToString) Then
                                'GetDoubleFromQuery = 50
                                'MsgBox("Null Value")
                            Else
                                GetDecimalFromQuery = .GetValue(0).ToString
                            End If
                        End If
                    End While
                Else
                    'MsgBox("No Row")
                End If
            End With
            CN.Close()

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error")
        End Try
    End Function

 Public Sub UpdateItem(ByVal SqlQuery As String)

        Dim StrConn As String = My.Settings.ImportLinkCS
        Dim CN = New SqlConnection(StrConn)
        Try
            CN.Open()

            Dim StrSql As String = SqlQuery

            Dim cmdupdate As New SqlCommand(StrSql, CN)
            cmdupdate.CommandText = StrSql
            cmdupdate.ExecuteNonQuery()

        Catch ex As Exception
            MsgBox(ex.Message & SqlQuery, MsgBoxStyle.Exclamation, "Error-UpdateItem")
        End Try

        CN.Close()
        CN = Nothing
    End Sub

Public Sub DisplayDataGrid(ByVal dgv As DataGridView, ByVal SQLSTR As String)
        dgv.DataSource = Nothing
        Try
            Dim dbBindSource As New BindingSource
            Dim strCon As String = My.Settings.ImportLinkCS
            Dim strSQL As String = SQLSTR

            Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter(strSQL, strCon)
            Dim commandBuilder As SqlCommandBuilder = New SqlCommandBuilder(dataAdapter)

            'Populate a new data table and bind it to the BindingSource.
            Dim table As DataTable = New DataTable()
            table.Locale = System.Globalization.CultureInfo.InvariantCulture
            dataAdapter.Fill(table)
            dbBindSource.DataSource = table


            'Resize the DataGridView columns to fit the newly loaded content.
            dgv.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader)
            dgv.DataSource = dbBindSource

            dgv.Columns(2).Width = 200
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "error")
        End Try
    End Sub

End Module

这就是我得到的结果

我已经尝试过了，它工作正常。此外，您只需添加客户参数，以便根据客户进行处理。