Click here to Skip to main content
15,867,330 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Private Sub ToolStripMenuItemMajuorTrial_Click(sender As Object, e As EventArgs) Handles ToolStripMenuItemMajuroTrail.Click

Dim basestring As String = _
" SELECT mamt, macd, minpdth, mopendt, mmat, mamtpd," & _
" mlactdt, mbranch, mufe, minsr, hname, msource, mfee, " & _
" mint, mpmt, mhold, mtype, mwatch, macct, mcbal, mfreq, " & _
" racct, ramt, rcbal, rdate, " & _
" rcode, rref, hhold " & _
" FROM Note INNER JOIN" & _
" Holder ON Note.mhold = Holder.hhold INNER JOIN " & _
" LTrans ON Note.macct = LTrans.racct " & _
" Where mwatch = 'O' and mopendt between '" & DateTimePicker1.Text & "' and '" & DateTimePicker2.Text & "' AND MCBAL > 0 " & _
" order by 'mopendt'"
Dim tsqlCommand As String = String.Format(basestring, DateTimePicker1.Text, DateTimePicker2.Text.ToString)
Dim ClipperConnectionstring As String = String.Format(basestring.ToString) = _
My.Settings.SQLConnection

Try
Using cn As New Data.SqlClient.SqlConnection(connectionString)
cn.Open()
Using cmd As New Data.SqlClient.SqlCommand(tsqlCommand, cn)
Dim rdr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader
If rdr.HasRows Then
'TITLE
Me.RichTextBoxMain.AppendText(" " & vbCrLf)
Me.RichTextBoxMain.AppendText(" " & vbCrLf)
Me.RichTextBoxMain.AppendText(" " & vbCrLf)
Me.RichTextBoxMain.AppendText("" & vbCrLf)
Me.RichTextBoxMain.AppendText("" & vbCrLf)


Me.RichTextBoxMain.AppendText("" & _
" " & "Holder" & _
" " & " Note" & _
" " & " BorrowerName" & _
" " & " Amount" & _
" " & " Balance" & _
" " & " OpenDate" & _
" " & " PmtDate" & _
" " & " Int" & _
" " & " Code" & _
" " & " Source" & _
" " & " Watch" & _
" " & " Type" & _
" " & " Freq" & _
" " & " Branch" & _
vbCrLf)
Me.RichTextBoxMain.AppendText("------------------------------------------------------------------------------------------------------------------------------------" & vbCrLf)
Dim reportline As String = "{0,3} {1,-5:000000} {2,-8} {3,-27} {4,12:C} {5,12:C} {6,12:d} {7,10:d} {8,5:C} {9,5} {10,5} {11,7} {12,7} {13,7} {14,7}" & vbCrLf
Dim desc As String = ""

Dim runbal As Double
Dim interestpaid As Double
Dim Amount As Double
Dim payment As Double
Dim recieved As Double = 0.0R
Dim paid As Double = 0.0R
Dim count As Integer = 1I

'loop
While rdr.Read

My.Application.DoEvents()
Me.Cursor = Cursors.Arrow
If rdr("rCode") = "1" Then 'New Loan (NL)
runbal = rdr("ramt")
desc = "NL"
ElseIf rdr("rCode") = "7" Then 'Principal Paid (PP)
runbal -= rdr("ramt")
desc = "PP"
ElseIf rdr("rCode") = "C" Then 'Principal Added (PA)
runbal += rdr("ramt")
desc = "PA"
ElseIf rdr("rCode") = "8" Then 'Late Fee Added
desc = "L+"
End If

If rdr("rCode") = "5" Then
interestpaid = interestpaid + rdr("ramt") 'Interest Paid
desc = "InPaid-"

ElseIf rdr("rCode") = "3" Then 'Payment
count = count + 1
desc = "PMT"
ElseIf rdr("rCode") = "2" Then 'Insurance Purchase
desc = "I+"
ElseIf rdr("rCode") = "4" Then 'Insurance Paid
desc = "I-"
ElseIf rdr("rCode") = "6" Then 'Late Fee Paid
desc = "L-"
ElseIf rdr("rCode") = "8" Then 'Late Fee Added
desc = "L+"
End If

payment = Amount - runbal
Me.RichTextBoxMain.AppendText( _
String.Format(reportline, _
rdr("mhold"), rdr("macct"), rdr("hname"), rdr("mamt"), rdr("mcbal"), rdr("mopendt"), rdr("mpmt"), rdr("mint"), rdr("rcode"), rdr("mwatch"), rdr("msource"), rdr("mtype"), rdr("mfreq"), rdr("mbranch"), _
vbCrLf))

End While

Me.RichTextBoxMain.AppendText("------------------------------------------------------------------------------------------------------------------------------------" & vbCrLf)
Me.RichTextBoxMain.AppendText("HISTORY SUMMARY:" & vbCrLf)
Me.RichTextBoxMain.AppendText("" & vbCrLf)
Me.RichTextBoxMain.AppendText(" Amount = " & Amount.ToString("C") & vbCrLf)
Me.RichTextBoxMain.AppendText("Payment Amount = " & paid.ToString("C") & vbCrLf)
Me.RichTextBoxMain.AppendText("Interest Amount = " & interestpaid.ToString("C") & vbCrLf)
Me.RichTextBoxMain.AppendText("Unpaid Amount = " & runbal.ToString("C") & vbCrLf)

End If

End Using
End Using
Catch ex As Exception
MsgBox("ERROR: " + ex.ToString)

End Try

End Sub
Posted

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900