Public Class Login Form in Vb

Public Class loginForm

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles okButton2.Click

Try

mappDB.UserName = usernameTextBox.Text : mappDB.Password = passwordTextBox.Text

Call mappDB.connect()

MainForm.Show()

Catch ex As Exception

Call showError(ex.Message)

End Try

End Sub

Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

If MessageBox.Show("Do you really want to quit now?", strApplicationName, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then Application.Exit()

End Sub

Private Sub loginForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Me.Text = strApplicationName & " Login"

passwordTextBox.Text = String.Empty

'todo remove when launch

usernameTextBox.Text = "admin"

passwordTextBox.Text = "7151epeN"

End Sub

End Class

Imports System.Data.OleDb

Imports System.Configuration

Public Class MainForm

Structure _gridFilter

Dim _sql As String

Dim _criteria As String

Dim _orderBy As String

End Structure

Dim gridFilter As New _gridFilter

Dim cmdLocal As OleDbCommand

Dim blIsActiveLoan As Boolean

Dim bsGLSummary As New BindingSource

Private Sub exitToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

If MessageBox.Show("Quit application now?", strApplicationName, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then Application.Exit()

End Sub

Private Sub MainForm_Activated(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Activated

loginForm.Visible = False

End Sub

Private Sub MainForm_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing

Call mappDB.close() : Call mappDB.dispose()

mappDB = Nothing

End Sub

Private Sub NewLoanToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NewLoanToolStripMenuItem.Click

borrowForm.ShowDialog()

End Sub

Private Sub BorrowerToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BorrowerToolStripMenuItem.Click

borrowerForm.ShowDialog()

End Sub

Private Sub MainForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Me.Text = strApplicationName & " for " & ConfigurationManager.AppSettings("client").ToString

'dateLabel.Text = System.DateTime.Now.ToString("MMM dd yyyy")

loginUserNameLabel.Text = mappDB.UserName

loginsinceLabel.Text = System.DateTime.Now.ToString("MMM dd yyyy hh:mm tt")

filtercriteriaComboBox.SelectedIndex = 1

With glSumTotalDataGridView

.ReadOnly = True

.Rows.Add() : .Rows.Add() : .Rows.Add()

.Item(0, 0).Value = "Total Cr"

.Item(0, 1).Value = "Total Dr"

.Item(0, 2).Value = "Balance "

.Columns(0).Width = 550

.Columns(1).Width = 150

.Cursor = Cursors.Hand

.SelectionMode = DataGridViewSelectionMode.FullRowSelect

End With

End Sub

Private Sub searchfilterButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles searchfilterButton.Click

Me.Cursor = Cursors.WaitCursor

gridFilter._sql = "SELECT pk_borrowerID,lastname as [Last name],firstname as [First Name] FROM tblborrower"

gridFilter._orderBy = " ORDER BY lastname"

Select Case filtercriteriaComboBox.SelectedIndex

Case 0 'firstname

gridFilter._criteria = " WHERE firstname like '" & filtervalueTextBox.Text & "%'"

Case 1 'lastname

gridFilter._criteria = " WHERE lastname like '" & filtervalueTextBox.Text & "%'"

Case 2 'company

gridFilter._criteria = " WHERE company like '" & filtervalueTextBox.Text & "%'"

End Select

Call fillGrid(gridFilter._sql, gridFilter._criteria, gridFilter._orderBy, searchBrDataGridView, True)

Me.Cursor = Cursors.Default

End Sub

Private Sub refreshGLSumToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles glsum_refreshToolStripButton.Click

Me.Cursor = Cursors.WaitCursor

gridFilter._sql = " SELECT fk_borrowerid,fullname as [Borrower Name],sumofcr as [Sum of Cr], sumofdr as [Sum of Dr], Balance FROM qGLsummary "

If Not incZeroBalToolStripButton.Checked Then

gridFilter._criteria = ""

Else

gridFilter._criteria = " WHERE balance > 0 "

End If

gridFilter._orderBy = " ORDER BY Fullname "

Call fillGrid(gridFilter._sql, gridFilter._criteria, gridFilter._orderBy, glSumDataGridView, True, bsGLSummary)

glsumRowCountToolStripLabel.Text = "of " & glSumDataGridView.Rows.Count - 1

Call firstToolStripButton_Click(sender, e)

Call glsum_totals()

Me.Cursor = Cursors.Default

'Call TotalOfGLSum()

End Sub

Private Sub Label9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles drtotalal_glsumLabel.Click

End Sub

Private Sub viewGLSummaryToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles glsum_openToolStripButton.Click

Try

Me.Cursor = Cursors.WaitCursor

Call TabControl1.SelectTab(1)

gridFilter._sql = " Select pk_mloanid , pk_loanref as [Loan Ref],isactive as [Active], Dated FROM tblloanmaster "

gridFilter._orderBy = " ORDER BY dated DESC "

gridFilter._criteria = " WHERE fk_borrowerid=" & glSumDataGridView(0, glSumDataGridView.CurrentRow.Index).Value.ToString

Call fillGrid(gridFilter._sql, gridFilter._criteria, gridFilter._orderBy, brloanlistDataGridView, True)

brloanlistDataGridView.Columns(2).Width = 40

brloanlistDataGridView.Columns(3).Width = 40

brloanlistDataGridView.Columns(3).DefaultCellStyle.Format = "MMM dd yyyy"

brNameToolStripLabel.Text = glSumDataGridView(1, glSumDataGridView.CurrentRow.Index).Value.ToString

'generalledgerDataGridView.DataSource = Nothing

Catch ex As Exception

Call showError(ex.Message.ToString)

Finally

Me.Cursor = Cursors.Default

End Try

End Sub

Private Sub borrowerDataGridView_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles searchBrDataGridView.CellContentClick

End Sub

Private Sub borrowerDataGridView_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles searchBrDataGridView.CellDoubleClick

gridFilter._sql = " SELECT fk_borrowerid,fullname as [Borrower Name],sumofcr as [Sum of Cr], sumofdr as [Sum of Dr], Balance FROM qGLsummary "

gridFilter._criteria = " WHERE fk_borrowerid =" & searchBrDataGridView(0, searchBrDataGridView.CurrentRow.Index).Value.ToString

gridFilter._orderBy = " ORDER BY Fullname "

Call fillGrid(gridFilter._sql, gridFilter._criteria, gridFilter._orderBy, glSumDataGridView, True)

'Call TotalOfGLSum()

Call glsum_totals()

End Sub

Private Sub borrowerloanDataGridView_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles brloanlistDataGridView.CellContentDoubleClick

End Sub

Private Sub generalledgersummaryDataGridView_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles glSumDataGridView.CellDoubleClick

Call viewGLSummaryToolStripButton1_Click(sender, e)

End Sub

Private Sub upd_newToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles upd_newToolStripButton.Click

Try

upd_datedDateTimePicker.Value = Now

upd_ToComboBox.SelectedIndex = 1

upd_isactiveCheckBox.Checked = True

Call combolist("SELECT pk_accntID,pk_code FROM tblaccount ORDER BY description", "pk_accntid", "pk_code", upd_AccntComboBox)

Call upd_AccntComboBox_SelectionChangeCommitted(sender, e)

'upd_AmtNumericUpDown.Value = 0

upd_nextduedateDateTimePicker.Value = DateAdd(DateInterval.Month, 1, CType(duedateTextBox.Text.ToString, Date))

'upd_statusComboBox.SelectedIndex = 0

Call toggleOnUpdate()

Catch ex As Exception

Call showError(ex.ToString)

End Try

End Sub

Private Sub upd_editToolStripButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles upd_editToolStripButton.Click

Try

Dim a As Integer = brloanDetailsDataGridView.CurrentRow.Index

Call combolist("SELECT pk_accntID,pk_code FROM tblaccount ORDER BY description", "pk_accntid", "pk_code", upd_AccntComboBox)

Call toggleOnUpdate()

With brloanDetailsDataGridView

upd_recidLabel.Text = .Item(0, a).Value.ToString

upd_AccntComboBox.Text = .Item(1, a).Value.ToString

upd_datedDateTimePicker.Value = CType(.Item(2, a).Value, Date)

If CType(.Item(3, a).Value, Double) > 0 Then 'entry is Cr

upd_ToComboBox.Text = "Cr"

upd_AmtNumericUpDown.Value = CType(.Item(3, a).Value, Decimal)

Else

upd_ToComboBox.Text = "Dr"

upd_AmtNumericUpDown.Value = CType(.Item(4, a).Value, Decimal)

End If

upd_nextduedateDateTimePicker.Value = CType(duedateTextBox.Text.ToString, Date)

upd_isactiveCheckBox.Checked = isactiveCheckBox.Checked

End With

Catch ex As Exception

If Err.Number = 91 Then

Call showError("Please click entry in General Ledger Details and click edit.")

Else

Call showError(Err.Number & " " & ex.Message)

End If

End Try

End Sub

Private Sub upd_applyToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles upd_applyToolStripButton.Click

If Not IsNumeric(upd_recidLabel.Text) Then

MessageBox.Show("Please select entry from GL Details. Click OK to continue.", strApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Information)

Exit Sub

End If

If upd_AmtNumericUpDown.Value = 0 Then

Call showError("Enter amount value.")

Exit Sub

End If

Dim i As Integer, _PostAmountTo As String = String.Empty

If MessageBox.Show("Are all entries correct?", strApplicationName, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then

Try

If upd_ToComboBox.Text.ToUpper = "DR" Then _PostAmountTo = "dr" Else _PostAmountTo = "Cr"

If upd_recidLabel.Text = "0" Then

cmdLocal = New OleDbCommand("INSERT INTO tblloandetails(fk_mloanid,fk_accntid,dated," & _PostAmountTo & ") VALUES(@fk_mloanid,@fk_accntid,@dated,@amt)", mappDB.conn)

Else

cmdLocal = New OleDbCommand("UPDATE tblloandetails SET [email protected]/* */_mloanid,[email protected]/* */_accntid,[email protected]/* */," & _PostAmountTo & "[email protected]/* */ WHERE pk_dloanid=" & upd_recidLabel.Text.ToString, mappDB.conn)

End If

With cmdLocal.Parameters

.Add("@fk_mloanid", OleDbType.Integer, 0, "fk_mloanid").Value = recIDLabel.Text

.Add("@fk_accntid", OleDbType.Integer, 0, "fk_accntid").Value = upd_AccntComboBox.SelectedValue

.Add("@dated", OleDbType.Date, 0, "dated").Value = upd_datedDateTimePicker.Value.ToString("MMM/dd/yyyy")

.Add("@amt", OleDbType.Decimal, 0, _PostAmountTo).Value = upd_AmtNumericUpDown.Value

End With

i = cmdLocal.ExecuteNonQuery

gridFilter._sql = " SELECT pk_dloanid,pk_code as Particular,Dated,Cr,Dr FROM qloandetails "

gridFilter._criteria = " WHERE fk_mloanid =" & brloanlistDataGridView(0, brloanlistDataGridView.CurrentRow.Index).Value.ToString

gridFilter._orderBy = " ORDER BY dated,pk_dloanid "

Call fillGrid(gridFilter._sql, gridFilter._criteria, gridFilter._orderBy, brloanDetailsDataGridView, True)

Call gldetails_totals()

blIsActiveLoan = False

For i = 0 To brLoanDetailsSumDataGridView.Rows.Count - 1

If CType(brLoanDetailsSumDataGridView.Item(4, i).Value, Double) > 0 Then

blIsActiveLoan = True

Exit For

End If

Next

cmdLocal = New OleDbCommand("UPDATE tblloanmaster SET [email protected]/* */ WHERE pk_mloanid=" & recIDLabel.Text.ToString, mappDB.conn)

With cmdLocal.Parameters

.Add("@isactive", OleDbType.Boolean, 0, "isactive").Value = blIsActiveLoan

End With

cmdLocal.ExecuteNonQuery()

brloanlistDataGridView.Item(2, brloanlistDataGridView.CurrentRow.Index).Value = blIsActiveLoan

'end status check

If blIsActiveLoan And MessageBox.Show("Update due date?", strApplicationName, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then

'update loan due date

cmdLocal = New OleDbCommand("UPDATE tblloanmaster SET [email protected]/* */ WHERE pk_mloanid=" & recIDLabel.Text.ToString, mappDB.conn)

With cmdLocal.Parameters

.Add("@duedate", OleDbType.Date, 0, "duedate").Value = upd_nextduedateDateTimePicker.Value.ToString("MMM dd yyyy")

End With

cmdLocal.ExecuteNonQuery()

End If

Call toggleOnUpdate()

MessageBox.Show(brNameToolStripLabel.Text.ToUpper & " general ledger update success. Click OK to continue.", strApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Information)

Catch ex As Exception

Call showError(ex.ToString)

Finally

i = Nothing

cmdLocal = Nothing

mappDB.close()

End Try

End If

End Sub

Private Sub gldetails_totals()

With brloanDetailsDataGridView

.Columns(2).DefaultCellStyle.Format = "MMM dd yyyy"

.Columns(3).Width = 80

.Columns(3).DefaultCellStyle.Format = "0,0.00"

.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

.Columns(4).Width = 80

.Columns(4).DefaultCellStyle.Format = "0,0.00"

.Columns(4).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

End With

gridFilter._sql = " SELECT fk_mloanid,pk_code as Particular, sumofcr as Cr, sumofDr as Dr,Balance FROM qloandetailssum "

gridFilter._criteria = " WHERE fk_mloanid =" & brloanlistDataGridView(0, brloanlistDataGridView.CurrentRow.Index).Value.ToString

gridFilter._orderBy = " "

Call fillGrid(gridFilter._sql, gridFilter._criteria, gridFilter._orderBy, brLoanDetailsSumDataGridView, True)

With brLoanDetailsSumDataGridView

'.Columns(2).DefaultCellStyle.Format = "MMM dd yyyy"

For i As Integer = 2 To 4

.Columns(i).Width = 80

.Columns(i).DefaultCellStyle.Format = "0,0.00"

.Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

Next

End With

End Sub

Private Sub glsum_totals()

Dim _totalCr As Double = 0

Dim _totalDr As Double = 0

Try

For i As Integer = 0 To glSumDataGridView.Rows.Count - 1

_totalCr += CType(glSumDataGridView.Item(2, i).Value, Double)

_totalDr += CType(glSumDataGridView.Item(3, i).Value, Double)

Next

Catch

End Try

Try

'add grid formatting

With glSumDataGridView

.Columns(1).Width = 350

.Columns(2).DefaultCellStyle.Format = "0,0.00"

.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

.Columns(3).DefaultCellStyle.Format = "0,0.00"

.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

.Columns(4).DefaultCellStyle.Format = "0,0.00"

.Columns(4).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

End With

With glSumTotalDataGridView

.Item(1, 0).Value = FormatNumber(_totalCr, 2)

.Item(1, 1).Value = FormatNumber(_totalDr, 2)

.Item(1, 2).Value = FormatNumber(_totalCr - _totalDr, 2)

.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

End With

Catch ex As Exception

Call showError(ex.Message)

End Try

End Sub

Private Sub upd_calcToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles upd_calcToolStripButton.Click

Try

System.Diagnostics.Process.Start("calc")

Catch ex As Exception

Call showError(ex.ToString)

End Try

End Sub

Sub haltKeys(ByVal sender As Object, ByVal e As KeyPressEventArgs) Handles datedTextBox.KeyPress, duedateTextBox.KeyPress, _

comakerTextBox.KeyPress, remarkTextBox.KeyPress, payableTextBox.KeyPress, monthlyintTextBox.KeyPress, monthlypenaltyTextBox.KeyPress, _

principalTextBox.KeyPress, monthlyintTextBox.KeyPress, monthlypenaltyTextBox.KeyPress

e.Handled = True

End Sub

Private Sub export_glsumToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles glsum_exportToolStripButton.Click

If (glSumDataGridView.Rows.Count - 1) < 0 Then Exit Sub

Dim h As Integer = 0, r As Integer = 4

excelApp = New Excel.Application

excelWB = excelApp.Workbooks.Open(Application.StartupPath & "templatesglsummary.xlt")

excelWS = CType(excelWB.Sheets(1), Excel.Worksheet)

With excelWS

For h = 0 To glSumDataGridView.Rows.Count - 1

r = h + 4 '4 is the base row

.Cells(r, 1) = h + 1 'sn

.Cells(r, 2) = glSumDataGridView.Item(1, h).Value.ToString

.Cells(r, 3) = glSumDataGridView.Item(2, h).Value.ToString

.Cells(r, 4) = glSumDataGridView.Item(3, h).Value.ToString

.Cells(r, 5) = glSumDataGridView.Item(4, h).Value.ToString

Next

'balance

'.Cells(6, ExcelColumns.colE) = "=C" & r & "-D" & r

r += 1

.Cells(r, ExcelColumns.colC) = "=SUM(C4:C" & r - 1 & ")" 'Cr total

.Cells(r, ExcelColumns.colD) = "=SUM(D4:D" & r - 1 & ")" 'Dr Total

.Cells(r, ExcelColumns.colE) = "=C" & r & "-D" & r 'Balance Total

.Cells(r, ExcelColumns.colA) = "Total"

.Range("A" & r & ":B" & r).Merge()

.Range("A" & r).HorizontalAlignment = -4131

End With

Call drawBorder("A4:E" & r)

excelApp.Visible = True

'clean up variables

h = Nothing

excelWS = Nothing

excelWB = Nothing

excelApp = Nothing

End Sub

Private Sub gl_viewToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles gldetails_viewToolStripButton.Click

If (brloanDetailsDataGridView.Rows.Count - 1) < 0 Then Exit Sub

Dim h As Integer = 0, r As Integer = 0

'Dim s As String = String.Empty

excelApp = New Excel.Application

excelWB = excelApp.Workbooks.Open(Application.StartupPath & "templatesgldetails.xlt")

excelWS = CType(excelWB.Sheets(1), Excel.Worksheet)

With excelWS

.Cells(3, ExcelColumns.colC) = brNameToolStripLabel.Text.ToString

.Cells(4, ExcelColumns.colC) = loanRefLabel.Text.ToString

.Cells(5, ExcelColumns.colC) = datedTextBox.Text.ToString

.Cells(3, ExcelColumns.colG) = principalTextBox.Text.ToString

.Cells(4, ExcelColumns.colG) = monthlyintTextBox.Text.ToString

.Cells(5, ExcelColumns.colG) = monthlypenaltyTextBox.Text.ToString

For h = 0 To brloanDetailsDataGridView.Rows.Count - 1

r = h + 9 'is the base row

.Cells(r, ExcelColumns.colA) = h + 1 'sn

.Cells(r, ExcelColumns.colB) = brloanDetailsDataGridView.Item(1, h).Value.ToString

.Cells(r, ExcelColumns.colD) = CType(brloanDetailsDataGridView.Item(2, h).Value.ToString, Date).ToString("MMM dd yyyy")

.Cells(r, ExcelColumns.colE) = brloanDetailsDataGridView.Item(3, h).Value.ToString 'cr

.Cells(r, ExcelColumns.colF) = brloanDetailsDataGridView.Item(4, h).Value.ToString 'dr

'balance col

If r > 10 Then .Cells(r, ExcelColumns.colG) = "=G" & (r - 1) & "+E" & r & "-F" & r

Next

r += 1

'balance

.Cells(6, ExcelColumns.colG) = "=E" & r & "-F" & r

.Cells(r, ExcelColumns.colE) = "=SUM(E8:E" & r - 1 & ")"

.Cells(r, ExcelColumns.colF) = "=SUM(F8:F" & r - 1 & ")"

.Cells(r, ExcelColumns.colG) = "=E" & r & "-F" & r

.Cells(r, ExcelColumns.colA) = "Total"

.Range("A" & r & ":D" & r).Merge()

.Range("A" & r).HorizontalAlignment = -4131

End With

Call drawBorder("A10:G" & r)

excelApp.Visible = True

h = Nothing

excelWS = Nothing

excelWB = Nothing

excelApp = Nothing

End Sub

Private Sub what_accntButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles what_accntButton.Click

Try

cmdLocal = New OleDbCommand("SELECT description from tblaccount WHERE pk_accntID=" & upd_AccntComboBox.SelectedValue.ToString, mappDB.conn)

Dim rd As OleDbDataReader = cmdLocal.ExecuteReader

rd.Read()

If rd.HasRows Then MessageBox.Show(upd_AccntComboBox.Text & " = " & rd(0).ToString & ". Click OK to continue.", strApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Information)

rd = Nothing

cmdLocal = Nothing

Catch

End Try

End Sub

Private Sub AccountToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AccountToolStripMenuItem.Click

rptAccountForm.ShowDialog()

End Sub

Sub toggleOnUpdate()

brloanlistDataGridView.Enabled = Not brloanlistDataGridView.Enabled

brloanDetailsDataGridView.Enabled = Not brloanDetailsDataGridView.Enabled

upd_newToolStripButton.Enabled = Not upd_newToolStripButton.Enabled

upd_editToolStripButton.Enabled = Not upd_editToolStripButton.Enabled

upd_applyToolStripButton.Enabled = Not upd_applyToolStripButton.Enabled

upd_cancelToolStripButton.Enabled = Not upd_cancelToolStripButton.Enabled

End Sub

Private Sub upd_cancelToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles upd_cancelToolStripButton.Click

If MessageBox.Show("Cancel update?", strApplicationName, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then

upd_recidLabel.Text = "0"

Call toggleOnUpdate()

End If

End Sub

Private Sub borrowerloanDataGridView_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles brloanlistDataGridView.CellContentClick

End Sub

Private Sub TabControl1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles TabControl1.Click

If upd_applyToolStripButton.Enabled = True Then

MessageBox.Show("Please complete updating/cancel GL of " & brNameToolStripLabel.Text.ToUpper & ". Click OK to continue.", strApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Information)

TabControl1.SelectedIndex = 1

End If

End Sub

Private Sub DueDateToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DueDateToolStripMenuItem.Click

rptDueDateForm.ShowDialog()

End Sub

Private Sub rptBorrowerToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rptBorrowerToolStripMenuItem.Click

Me.Cursor = Cursors.WaitCursor

Dim r As Integer = 4

excelApp = New Excel.Application

excelWB = excelApp.Workbooks.Open(Application.StartupPath & "templatesborrower.xlt")

excelWS = CType(excelWB.Sheets(1), Excel.Worksheet)

cmdLocal = New OleDbCommand("SELECT * from tblborrower ORDER BY lastname", mappDB.conn)

Dim rd As OleDbDataReader = cmdLocal.ExecuteReader

While rd.Read

With excelWS

.Cells(r, ExcelColumns.colA) = r - 3

.Cells(r, ExcelColumns.colB) = rd("lastname").ToString

.Cells(r, ExcelColumns.colC) = rd("firstname").ToString

.Cells(r, ExcelColumns.colD) = rd("jobtitle").ToString & " - " & rd("company").ToString

.Cells(r, ExcelColumns.colE) = rd("contactnos").ToString

.Cells(r, ExcelColumns.colF) = rd("address").ToString

r += 1

End With

End While

If r > 4 Then Call drawBorder("A5:F" & r - 1)

excelApp.Visible = True

mappDB.close()

rd = Nothing

cmdLocal = Nothing

r = Nothing

excelWS = Nothing

excelWB = Nothing

excelApp = Nothing

Me.Cursor = Cursors.Default

End Sub

Private Sub AccountToolStripMenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AccountToolStripMenuItem1.Click

accountForm.ShowDialog()

End Sub

Private Sub LogOffToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LogOffToolStripMenuItem.Click

Me.Visible = False

loginForm.Show()

End Sub

Private Sub borrowerloanDataGridView_CellEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles brloanlistDataGridView.CellEnter

Dim rd As OleDbDataReader

recIDLabel.Text = "0"

Try

gridFilter._sql = " SELECT pk_dloanid,pk_code as Particular,Dated,Cr,Dr FROM qloandetails "

gridFilter._criteria = " WHERE fk_mloanid =" & brloanlistDataGridView(0, brloanlistDataGridView.CurrentRow.Index).Value.ToString

gridFilter._orderBy = " ORDER BY dated,pk_dloanid "

Call fillGrid(gridFilter._sql, gridFilter._criteria, gridFilter._orderBy, brloanDetailsDataGridView, True)

cmdLocal = New OleDbCommand("SELECT * FROM tblloanmaster WHERE pk_mloanid=" & brloanlistDataGridView(0, brloanlistDataGridView.CurrentRow.Index).Value.ToString, mappDB.conn)

rd = cmdLocal.ExecuteReader

rd.Read()

If rd.HasRows Then

recIDLabel.Text = rd("pk_mloanid").ToString

loanRefLabel.Text = rd("pk_loanref").ToString

datedTextBox.Text = CType(rd("dated").ToString, Date).ToString("MMM dd yyyy")

duedateTextBox.Text = CType(rd("duedate").ToString, Date).ToString("MMM dd yyyy")

comakerTextBox.Text = rd("comaker").ToString

remarkTextBox.Text = rd("remark").ToString

principalTextBox.Text = FormatNumber(rd("principal").ToString, 2).ToString

monthlyintTextBox.Text = FormatNumber(rd("interest").ToString, 2).ToString

monthlypenaltyTextBox.Text = FormatNumber(rd("penalty").ToString, 2).ToString

isactiveCheckBox.Checked = CType(rd("isactive"), Boolean)

'monthInterestNumericUpDown.Value = CType(rd("interest").ToString, Decimal)

payableTextBox.Text = FormatNumber(CType(principalTextBox.Text, Double) * (CType(monthlyintTextBox.Text, Double) / 100), 2).ToString

'monthPenaltyNumericUpDown.Value = CType(rd("penalty").ToString, Decimal)

End If

Call gldetails_totals()

Catch ex As Exception

Call showError(ex.Message)

Finally

mappDB.close()

rd = Nothing

End Try

End Sub

Private Sub quitToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles quitToolStripMenuItem.Click

If MessageBox.Show("Do you really want to quit now?", strApplicationName, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then Application.Exit()

End Sub

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick

nowToolStripLabel.Text = System.DateTime.Now.ToString("MMM dd yyyy HH:mm:ss tt")

End Sub

Private Sub previousToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles previousToolStripButton.Click

bsGLSummary.MovePrevious()

End Sub

Private Sub generalledgersummaryDataGridView_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles glSumDataGridView.CellContentClick

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

brloanDetailsDataGridView.Rows(1).Selected = True

End Sub

Private Sub BackupDatabaseToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackupDatabaseToolStripMenuItem.Click

End Sub

Private Sub ToolStripLabel8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

End Sub

Private Sub upd_isactiveCheckBox_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles upd_isactiveCheckBox.CheckedChanged

End Sub

Private Sub upd_isactiveCheckBox_CheckStateChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles upd_isactiveCheckBox.CheckStateChanged

End Sub

Private Sub upd_isactiveCheckBox_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles upd_isactiveCheckBox.Click

If upd_ToComboBox.Text.ToUpper = "DR" Then

upd_isactiveCheckBox.Checked = True

GoTo close

End If

Dim x As Double = 0

For i As Integer = 0 To brLoanDetailsSumDataGridView.Rows.Count - 1

x += CType(brLoanDetailsSumDataGridView.Item(2, i).Value, Double)

Next

If x - upd_AmtNumericUpDown.Value > 0 Then

upd_isactiveCheckBox.Checked = True

GoTo close

End If

Exit Sub

close:

MessageBox.Show("Zero balance loan cannot be close.", strApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Information)

End Sub

Private Sub upd_AccntComboBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles upd_AccntComboBox.SelectedIndexChanged

End Sub

Private Sub upd_AccntComboBox_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles upd_AccntComboBox.SelectionChangeCommitted

'retrieve balance of selected account

For i As Integer = 0 To brLoanDetailsSumDataGridView.Rows.Count - 1

If brLoanDetailsSumDataGridView.Item(1, i).Value.ToString = upd_AccntComboBox.Text.ToString Then

upd_AmtNumericUpDown.Value = CType(brLoanDetailsSumDataGridView.Item(4, i).Value, Decimal)

Exit For

End If

Next

End Sub

Private Sub generalledgersummaryDataGridView_CellEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles glSumDataGridView.CellEnter

Try

selectedrowindexToolStripTextBox.Text = CStr(e.RowIndex + 1)

Catch

End Try

End Sub

Private Sub filtervalueTextBox_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles filtervalueTextBox.KeyDown

If e.KeyCode = Keys.Enter Then Call searchfilterButton_Click(sender, e)

End Sub

Private Sub nextToolStripButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles nextToolStripButton.Click

bsGLSummary.MoveNext()

End Sub

Private Sub firstToolStripButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles firstToolStripButton.Click

bsGLSummary.MoveFirst()

End Sub

Private Sub lastToolStripButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lastToolStripButton.Click

bsGLSummary.MoveLast()

End Sub

Private Sub incZeroBalToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles incZeroBalToolStripButton.Click

incZeroBalToolStripButton.Checked = Not incZeroBalToolStripButton.Checked

End Sub

Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click

Dim s As String = strApplicationName

s += vbCrLf & ConfigurationManager.AppSettings("client").ToString

s += vbCrLf & vbCrLf & "Dedicated To:"

s += vbCrLf & "Ethan,Shane,Althea and Khristine"

s += vbCrLf & vbCrLf & "Developed By:" & vbCrLf & "SoftIndex Solutions 2009"

s += vbCrLf & "Jonathan Bantang"

MessageBox.Show(s, strApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Information)

End Sub

End Class

Imports System.Data.OleDb

Public Class accountForm

Dim oadmainLocal As OleDbDataAdapter

Dim dtmaindatatable As DataTable

Dim bs As New BindingSource

Private Sub accountForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Call fillGrid("SELECT pk_accntid, pk_code as Code, Description FROM tblaccount", "")

End Sub

Private Sub mainDataGridView_CellEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles mainDataGridView.CellEnter

Try

selectedrowindexToolStripTextBox.Text = CStr(e.RowIndex + 1)

Catch

End Try

End Sub

Private Sub closeToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles closeToolStripButton.Click

Me.Close()

End Sub

Private Sub saveToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles saveToolStripButton.Click

Dim cmdblder As OleDbCommandBuilder = New OleDbCommandBuilder(oadmainLocal)

Dim _msg As String = String.Empty

Try

oadmainLocal.Update(dtmaindatatable)

_msg = "Update success. Click OK to continue"

Catch ex As Exception

_msg = "ERROR : " & ex.Message

Finally

MessageBox.Show(_msg, strApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Information)

cmdblder = Nothing

_msg = Nothing

End Try

End Sub

Private Sub fillGrid(ByVal _sql As String, ByVal _criteria As String)

oadmainLocal = New OleDbDataAdapter(_sql + _criteria + " ORDER BY pk_code", mappDB.conn)

dtmaindatatable = New DataTable

Try

oadmainLocal.Fill(dtmaindatatable)

bs = New BindingSource

bs.DataSource = dtmaindatatable

With mainDataGridView

.DataSource = Nothing

'.DataSource = dtmaindatatable

.DataSource = bs

'format grid

.Columns(0).Visible = False

.Columns(1).Width = 150

.Columns(2).Width = 320

.SelectionMode = DataGridViewSelectionMode.FullRowSelect

.Cursor = Cursors.Hand

'TODO word wrap desc columns

selectedrowindexToolStripTextBox.Text = "1"

totalrowsToolStripLabel.Text = "of " & .RowCount - 1

End With

'bs = Nothing

Catch ex As Exception

MessageBox.Show("query error : " & ex.Message)

Finally

mappDB.close()

End Try

End Sub

Private Sub removeToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles removeToolStripButton.Click

Try

mainDataGridView.Rows.Remove(mainDataGridView.CurrentRow)

Catch ex As Exception

Call showError(ex.ToString)

End Try

End Sub

Private Sub previewToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles previewToolStripButton.Click

End Sub

Private Sub nextrowToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles nextrowToolStripButton.Click

bs.MoveNext()

End Sub

Private Sub firstrowToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles firstrowToolStripButton.Click

bs.MoveFirst()

End Sub

Private Sub lastrowToolStripButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lastrowToolStripButton.Click

bs.MoveLast()

End Sub

Private Sub previousrowToolStripButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles previousrowToolStripButton.Click

bs.MovePrevious()

End Sub

Private Sub addToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles addToolStripButton.Click

Call lastrowToolStripButton_Click(sender, e)

End Sub

Private Sub refreshToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles refreshToolStripButton.Click

End Sub

End Class

Imports System.Data.OleDb

Public Class borrowerForm

Dim oadmainLocal As OleDbDataAdapter

Dim dtmaindatatable As DataTable

Dim bs As New BindingSource

Dim strAllTableColumns As String = "SELECT pk_borrowerid,lastname as [Last Name], firstname as [First Name],jobtitle as [Job Title], contactnos as [Contact Nos],Address,Company FROM tblborrower"

Private Sub borrowerForm_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing

oadmainLocal = Nothing

dtmaindatatable = Nothing

End Sub

Private Sub borrowerForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Call fillGrid(strAllTableColumns, "")

filteroptionToolStripButton.SelectedIndex = 0

End Sub

Private Sub mainDataGridView_CellEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles mainDataGridView.CellEnter

Try

currentRowToolStripTextBox.Text = CStr(e.RowIndex + 1)

Catch

End Try

End Sub

'local fillgrid. perform table update

Private Sub fillGrid(ByVal _sql As String, ByVal _criteria As String)

oadmainLocal = New OleDbDataAdapter(_sql + _criteria + " ORDER BY lastname", mappDB.conn)

dtmaindatatable = New DataTable

Try

oadmainLocal.Fill(dtmaindatatable)

bs = New BindingSource

bs.DataSource = dtmaindatatable

With mainDataGridView

.DataSource = Nothing

.DataSource = bs

'format grid

.Columns(0).Visible = False

.Columns(1).Width = 120

.Columns(2).Width = 120

.Columns(3).Width = 150

.Columns(4).Width = 120

.Columns(5).Width = 150

.Columns(6).Width = 150

.SelectionMode = DataGridViewSelectionMode.FullRowSelect

.Cursor = Cursors.Hand

'TODO word wrap desc columns

'currentRowToolStripTextBox.Text = "1"

rowCountToolStripLabel.Text = "of " & .RowCount - 1

End With

Catch ex As Exception

MessageBox.Show("query error : " & ex.Message)

Finally

mappDB.close()

End Try

End Sub

Private Sub closeToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles closeToolStripButton.Click

Me.Close()

End Sub

Private Sub saveToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles saveToolStripButton.Click

Dim cmdblder As OleDbCommandBuilder = New OleDbCommandBuilder(oadmainLocal)

Dim _msg As String = String.Empty

Try

oadmainLocal.Update(dtmaindatatable)

_msg = "Update success. Click OK to continue"

Catch ex As Exception

_msg = "ERROR : " & ex.Message

Finally

MessageBox.Show(_msg, strApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Information)

cmdblder = Nothing

_msg = Nothing

End Try

End Sub

Private Sub searchmainfilterToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles searchmainfilterToolStripButton.Click

Select Case filteroptionToolStripButton.SelectedIndex

Case 0 'all

Call fillGrid(strAllTableColumns, "")

Case 1 'firstname

Call fillGrid(strAllTableColumns, " WHERE firstname LIKE '" & filtervalueToolStripTextBox.Text.ToString & "%'")

Case 2 'lastname

Call fillGrid(strAllTableColumns, " WHERE lastname LIKE '" & filtervalueToolStripTextBox.Text.ToString & "%'")

Case 3 'company

Call fillGrid(strAllTableColumns, " WHERE company LIKE '" & filtervalueToolStripTextBox.Text.ToString & "%'")

End Select

End Sub

Private Sub removefilterToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles removefilterToolStripButton.Click

filteroptionToolStripButton.SelectedIndex = 0

Call searchmainfilterToolStripButton_Click(sender, e)

End Sub

Private Sub removeToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles removeToolStripButton.Click

Try

mainDataGridView.Rows.Remove(mainDataGridView.CurrentRow)

Catch ex As Exception

Call showError(ex.ToString)

End Try

End Sub

Private Sub nextToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles nextToolStripButton.Click

bs.MoveNext()

End Sub

Private Sub firstToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles firstToolStripButton.Click

bs.MoveFirst()

End Sub

Private Sub lastToolStripButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lastToolStripButton.Click

bs.MoveLast()

End Sub

Private Sub previousToolStripButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles previousToolStripButton3.Click

bs.MovePrevious()

End Sub

Private Sub mainDataGridView_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles mainDataGridView.CellContentClick

End Sub

Private Sub addToolStripButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles addToolStripButton.Click

Call lastToolStripButton_Click(sender, e)

'bs.AddNew()

End Sub

End Class

Imports System.Data.OleDb

Public Class borrowForm

Dim cmdLocal As OleDbCommand

Private Sub closeToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles closeToolStripButton.Click

Me.Close()

End Sub

Private Sub applyToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles applyToolStripButton.Click

If Not IsNumeric(recIDLabel.Text) Then

MessageBox.Show("Click New if you want to enter loan. Click OK to continue.", strApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Information)

Exit Sub

End If

If principalNumericUpDown.Value <= 0 Then

MessageBox.Show("Principal cannot be zero value. Click OK to continue", strApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Information)

Exit Sub

End If

If MessageBox.Show("Are all entries correct?", strApplicationName, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then Exit Sub

Call PayableTotal(sender, e)

Dim i As Integer = 0

Select Case CType(recIDLabel.Text.ToString, Integer)

Case 0

cmdLocal = New OleDbCommand("INSERT INTO tblloanmaster(pk_loanref,fk_borrowerid,dated,duedate,comaker,remark,principal,interest,penalty) values(@pk_loanref,@fk_borrowerid,@dated,duedate,comaker,remark,@principal,@interest,@penalty)", mappDB.conn)

Case Else

cmdLocal = New OleDbCommand("UPDATE tblloanmaster SET [email protected]/* */_borrowerid,[email protected]/* */,[email protected]/* */,[email protected]/* */,[email protected]/* */,[email protected]/* */ WHERE pk_mloanid=" & recIDLabel.Text, mappDB.conn)

GoTo update_only

End Select

With cmdLocal.Parameters

.Clear()

.Add("@pk_loanref", OleDbType.VarChar, 50, "pk_loanref").Value = loanRefLabel.Text

.Add("@fk_borrowerID", OleDbType.Integer, 0, "fk_borrowerID").Value = borrowerComboBox.SelectedValue

.Add("@dated", OleDbType.Date, 0, "dated").Value = loanDateTimePicker.Value.ToString("MMM/dd/yyyy")

.Add("@duedate", OleDbType.Date, 0, "duedate").Value = duedateDateTimePicker.Value.ToString("MMM/dd/yyyy")

' .Add("@nextduedate", OleDbType.Date, 0, "nextduedate").Value = duedateDateTimePicker.Value.ToString("MMM/dd/yyyy")

.Add("@comaker", OleDbType.VarChar, 50, "comaker").Value = comakerTextBox.Text

.Add("@remark", OleDbType.VarChar, 50, "remark").Value = remarkTextBox.Text

.Add("@principal", OleDbType.Decimal, 0, "principal").Value = principalNumericUpDown.Value

.Add("@interest", OleDbType.Decimal, 0, "interest").Value = monthInterestNumericUpDown.Value

.Add("@penalty", OleDbType.Decimal, 0, "penalty").Value = monthPenaltyNumericUpDown.Value

End With

i = cmdLocal.ExecuteNonQuery()

If i > 0 Then

cmdLocal = New OleDbCommand("SELECT pk_mloanid FROM tblloanmaster WHERE pk_loanref='" & loanRefLabel.Text.ToString & "'", mappDB.conn)

Dim rd As OleDbDataReader = cmdLocal.ExecuteReader

rd.Read()

recIDLabel.Text = rd(0).ToString

rd = Nothing

If recIDLabel.Text <> "0" Then 'open the last rowid

'post to principal+due date to GL

cmdLocal = New OleDbCommand("INSERT INTO tblloandetails(fk_mloanid,fk_accntid,dated,cr) VALUES(@fk_mloanid,@fk_accntid,@dated,@cr)", mappDB.conn)

With cmdLocal.Parameters

.Add("@fk_mloanid", OleDbType.Integer, 0, "fk_mloanid").Value = recIDLabel.Text

.Add("@fk_accntid", OleDbType.Integer, 0, "fk_accntid").Value = principalComboBox.SelectedValue

.Add("@dated", OleDbType.Date, 0, "dated").Value = loanDateTimePicker.Value

.Add("@Cr", OleDbType.Decimal, 0, "Cr").Value = principalNumericUpDown.Value

End With

cmdLocal.ExecuteNonQuery()

'post to principal interest+due date to GL

cmdLocal = New OleDbCommand("INSERT INTO tblloandetails(fk_mloanid,fk_accntid,dated,cr) VALUES(@fk_mloanid,@fk_accntid,@dated,@cr)", mappDB.conn)

With cmdLocal.Parameters

.Add("@fk_mloanid", OleDbType.Integer, 0, "fk_mloanid").Value = recIDLabel.Text

.Add("@fk_accntid", OleDbType.Integer, 0, "fk_accntid").Value = interestComboBox.SelectedValue

.Add("@dated", OleDbType.Date, 0, "dated").Value = loanDateTimePicker.Value

.Add("@Cr", OleDbType.Decimal, 0, "Cr").Value = CType(payableTextBox.Text, Double)

End With

cmdLocal.ExecuteNonQuery()

End If

cmdLocal = Nothing

End If

update_only:

mappDB.close()

If MessageBox.Show(borrowerComboBox.Text.ToUpper & " loan is now saved. Create New Loan?", strApplicationName, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then Call newToolStripButton_Click(sender, e)

End Sub

Private Sub newLoanForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Call combolist("SELECT * from qborrower", "pk_borrowerid", "fullname", borrowerComboBox)

End Sub

Function loanID() As String

cmdLocal = New OleDbCommand("SELECT COUNT(pk_mloanid) as x from tblloanmaster", mappDB.conn)

Dim rd As OleDbDataReader = cmdLocal.ExecuteReader

rd.Read()

Return Date.Today.ToString("MMdd") & "-" & CStr(Format(CType(rd(0), Integer) + 1, "0000"))

rd = Nothing

cmdLocal = Nothing

mappDB.close()

End Function

Private Sub newToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles newToolStripButton.Click

loanRefLabel.Text = loanID()

recIDLabel.Text = "0"

loanDateTimePicker.Value = Now

duedateDateTimePicker.Value = DateAdd(DateInterval.Month, 1, Now)

payableTextBox.Text = "0.00"

principalNumericUpDown.Value = 0

remarkTextBox.Text = "Loan release date <date>."

monthInterestNumericUpDown.Value = 10

monthPenaltyNumericUpDown.Value = 3

Call combolist("SELECT pk_accntID,pk_code FROM tblaccount ORDER BY description", "pk_accntid", "pk_code", principalComboBox)

Call combolist("SELECT pk_accntID,pk_code FROM tblaccount ORDER BY description", "pk_accntid", "pk_code", interestComboBox)

End Sub

Sub PayableTotal(ByVal sender As Object, ByVal e As EventArgs) Handles principalNumericUpDown.ValueChanged, monthInterestNumericUpDown.ValueChanged

payableTextBox.Text = CStr(CType(principalNumericUpDown.Value, Double) * (CType(monthInterestNumericUpDown.Value, Double) / 100))

End Sub

Private Sub payableTextBox_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles payableTextBox.KeyPress

e.Handled = True

End Sub

Private Sub payableTextBox_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles payableTextBox.TextChanged

End Sub

End Class

Imports System.data.OleDb

Public Class rptAccountForm

Dim cmdlocal As OleDbCommand

Dim rd As OleDbDataReader

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Call combolist("SELECT pk_accntid,pk_code from tblaccount", "pk_accntid", "pk_code", AccntComboBox)

End Sub

Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click

Me.Cursor = Cursors.WaitCursor

Dim strCriteria As String = String.Empty

Dim i As Integer = 1

Dim r As Integer = 5

If AccntComboBox.Text.ToUpper <> "ALL" Then strCriteria = " AND pk_accntid=" & AccntComboBox.SelectedValue.ToString

excelApp = New Excel.Application

excelWB = excelApp.Workbooks.Open(Application.StartupPath & "templatesaccount.xlt")

excelWS = CType(excelWB.Sheets(1), Excel.Worksheet)

excelWS.Cells(1, ExcelColumns.colF) = AccntComboBox.Text.ToString

excelWS.Cells(2, ExcelColumns.colF) = fromDateTimePicker.Value.ToString("MMM dd yyyy") & Chr(10) & toDateTimePicker.Value.ToString("MMM dd yyyy")

cmdlocal = New OleDbCommand("SELECT * from qrptaccount WHERE dated BETWEEN #" & fromDateTimePicker.Value.ToString("MMM dd yyyy") & "# AND #" & toDateTimePicker.Value.ToString("MMM dd yyyy") & "# " & strCriteria, mappDB.conn)

rd = cmdlocal.ExecuteReader

While rd.Read

With excelWS

.Cells(r, ExcelColumns.colA) = r - 4

.Cells(r, ExcelColumns.colB) = rd("pk_code").ToString

.Cells(r, ExcelColumns.colC) = rd("fullname").ToString

.Cells(r, ExcelColumns.colD) = CType(rd("dated").ToString, Date).ToString("MMM dd yyyy")

.Cells(r, ExcelColumns.colE) = rd("cr").ToString

.Cells(r, ExcelColumns.colF) = rd("dr").ToString

r += 1

End With

End While

'add cr and dr sum

excelWS.Cells(r, ExcelColumns.colE) = "=SUM(E5:E" & r - 1 & ")"

excelWS.Cells(r, ExcelColumns.colF) = "=SUM(F5:F" & r - 1 & ")"

excelWS.Cells(r, ExcelColumns.colA) = "Total"

excelWS.Range("A" & r & ":D" & r).Merge()

excelWS.Range("A" & r).HorizontalAlignment = -4131

If r > 6 Then Call drawBorder("A6:F" & r)

excelApp.Visible = True

'clean up variables

mappDB.close()

rd = Nothing

cmdlocal = Nothing

r = Nothing

excelWS = Nothing

excelWB = Nothing

excelApp = Nothing

Me.Cursor = Cursors.Default

End Sub

Private Sub ToolStripButton2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ToolStripButton2.Click

Me.Close()

End Sub

Private Sub viewAllToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles viewAllToolStripButton.Click

End Sub

End Class

Imports System.Data.OleDb

Public Class rptDueDateForm

Dim cmdlocal As OleDbCommand

Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click

Me.Cursor = Cursors.WaitCursor

Dim r As Integer = 4

excelApp = New Excel.Application

excelWB = excelApp.Workbooks.Open(Application.StartupPath & "templatesduedate.xlt")

excelWS = CType(excelWB.Sheets(1), Excel.Worksheet)

cmdlocal = New OleDbCommand("SELECT * from qrptduedate WHERE duedate BETWEEN #" & fromDateTimePicker.Value.ToString("MMM dd yyyy") & "# AND #" & toDateTimePicker.Value.ToString("MMM dd yyyy") & "# ORDER BY duedate", mappDB.conn)

Dim rd As OleDbDataReader = cmdlocal.ExecuteReader

While rd.Read

With excelWS

.Cells(r, ExcelColumns.colA) = r - 3

.Cells(r, ExcelColumns.colB) = rd("fullname").ToString

.Cells(r, ExcelColumns.colC) = rd("pk_loanref").ToString

.Cells(r, ExcelColumns.colD) = CType(rd("duedate").ToString, Date).ToString("MMM dd yyyy")

.Cells(r, ExcelColumns.colE) = rd("sumofcr").ToString

.Cells(r, ExcelColumns.colF) = rd("sumofdr").ToString

.Cells(r, ExcelColumns.colG) = rd("balance").ToString

r += 1

End With

End While

'add cr and dr sum

excelWS.Cells(r, ExcelColumns.colE) = "=SUM(E4:E" & r - 1 & ")"

excelWS.Cells(r, ExcelColumns.colF) = "=SUM(F4:F" & r - 1 & ")"

excelWS.Cells(r, ExcelColumns.colG) = "=SUM(G4:G" & r - 1 & ")"

excelWS.Cells(r, ExcelColumns.colA) = "Total"

excelWS.Range("A" & r & ":D" & r).Merge()

excelWS.Range("A" & r).HorizontalAlignment = -4131

'If r > 5 Then

'End If

Call drawBorder("A4:G" & r)

excelApp.Visible = True

'clean up variables

mappDB.close()

rd = Nothing

cmdlocal = Nothing

r = Nothing

excelWS = Nothing

excelWB = Nothing

excelApp = Nothing

Me.Cursor = Cursors.Default

End Sub

Private Sub ToolStripButton2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ToolStripButton2.Click

Me.Close()

End Sub

End Class

Cite this page