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