Imports System.Data.SqlClient
Imports System.IO
Imports MetroFramework
Imports MetroFramework.Forms
Public Class frmBankAccountRegistration
    Private Sub auto()
        Try
            Dim Num As Integer = 0
            con = New SqlConnection(cs)
            con.Open()
            Dim sql As String = ("SELECT MAX(ID) FROM BankAccountRegistration")
            cmd = New SqlCommand(sql)
            cmd.Connection = con
            If (IsDBNull(cmd.ExecuteScalar)) Then
                Num = 1
                txtID.Text = Num.ToString
            Else
                Num = cmd.ExecuteScalar + 1
                txtID.Text = Num.ToString
            End If
            cmd.Dispose()
            con.Close()
            con.Dispose()
        Catch ex As Exception
            MetroMessageBox.Show(Me, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Sub Reset()
        txtAddress.Text = ""
        txtAccountName.Text = ""
        txtAccountNo.Text = ""
        cmbAccountType.SelectedIndex = -1
        cmbActive.SelectedIndex = 0
        txtBalanceAmount.Text = ""
        dtpOpeningDate.Value = Today
        txtContactNo.Text = ""
        txtIFSCCode.Text = ""
        txtSwiftCode.Text = ""
        txtBranchName.Text = ""
        txtBank.Text = ""
        If IsSaveAllowed(lblUser.Text, "Banking") = True Then
            btnSave.Enabled = True
        Else
            btnSave.Enabled = False
        End If
        If IsViewAllowed(lblUser.Text, "Banking") = True Then
            Getdata1()
        Else
            DataGridView1.Rows.Clear()
        End If

        btnUpdate.Enabled = False
        btnDelete.Enabled = False
        txtAccountNo.Focus()
        auto()
    End Sub
    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        If txtAccountNo.Text = "" Then
            MetroMessageBox.Show(Me, "Please enter account no.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            txtAccountNo.Focus()
            Return
        End If
        If txtAccountName.Text = "" Then
            MetroMessageBox.Show(Me, "Please enter account name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            txtAccountName.Focus()
            Return
        End If
        If cmbAccountType.Text = "" Then
            MetroMessageBox.Show(Me, "Please select account type", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            cmbAccountType.Focus()
            Return
        End If
        If txtBalanceAmount.Text = "" Then
            MetroMessageBox.Show(Me, "Please enter balance amount", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            txtBalanceAmount.Focus()
            Return
        End If
        Try
            con = New SqlConnection(cs)
            con.Open()
            Dim ct As String = "select AccountNo from BankAccountRegistration where AccountNo=@d1"
            cmd = New SqlCommand(ct)
            cmd.Parameters.AddWithValue("@d1", txtAccountNo.Text)
            cmd.Connection = con
            rdr = cmd.ExecuteReader()

            If rdr.Read() Then
                MetroMessageBox.Show(Me, "Account No. Already Exists", "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
                txtAccountNo.Text = ""
                txtAccountNo.Focus()
                If (rdr IsNot Nothing) Then
                    rdr.Close()
                End If
                Return
            End If
            auto()
            con = New SqlConnection(cs)
            con.Open()
            Dim cb As String = "insert into BankAccountRegistration(AccountNo, AccountName, AccountType, OpeningDate, BalanceAmount, Active, BranchID,ID) VALUES (@d2,@d3,@d4,@d5,@d6,@d7,@d8,@d9)"
            cmd = New SqlCommand(cb)
            cmd.Connection = con
            cmd.Parameters.AddWithValue("@d2", txtAccountNo.Text)
            cmd.Parameters.AddWithValue("@d3", txtAccountName.Text)
            cmd.Parameters.AddWithValue("@d4", cmbAccountType.Text)
            cmd.Parameters.AddWithValue("@d5", dtpOpeningDate.Value.Date)
            cmd.Parameters.AddWithValue("@d6", Val(txtBalanceAmount.Text))
            cmd.Parameters.AddWithValue("@d7", cmbActive.Text)
            cmd.Parameters.AddWithValue("@d8", Val(txtBranchID.Text))
            cmd.Parameters.AddWithValue("@d9", Val(txtID.Text))
            cmd.ExecuteNonQuery()
            con.Close()
            Dim st As String = "added the new Bank account having account no. '" & txtAccountNo.Text & "' and Bank Name '" & txtBank.Text & "'"
            LogFunc(lblUser.Text, st)
            If Val(txtBalanceAmount.Text) > 0 Then
                BankAccountLedgerSave(System.DateTime.Now, txtAccountNo.Text, Val(txtID.Text), "Opening Balance", 0, Val(txtBalanceAmount.Text))
            End If
            MetroMessageBox.Show(Me, "Successfully saved", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Reset()
        Catch ex As Exception
            MetroMessageBox.Show(Me, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
        End Try
    End Sub


    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        If txtAccountNo.Text = "" Then
            MetroMessageBox.Show(Me, "Please enter account no.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            txtAccountNo.Focus()
            Return
        End If
        If txtAccountName.Text = "" Then
            MetroMessageBox.Show(Me, "Please enter account name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            txtAccountName.Focus()
            Return
        End If
        If cmbAccountType.Text = "" Then
            MetroMessageBox.Show(Me, "Please select account type", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            cmbAccountType.Focus()
            Return
        End If
        If txtBalanceAmount.Text = "" Then
            MetroMessageBox.Show(Me, "Please enter balance amount", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            txtBalanceAmount.Focus()
            Return
        End If

        Try
            If txtAccountNo.Text <> txtAccNo.Text Then
                con = New SqlConnection(cs)
                con.Open()
                Dim ct As String = "select AccountNo from BankAccountRegistration where AccountNo=@d1"
                cmd = New SqlCommand(ct)
                cmd.Parameters.AddWithValue("@d1", txtAccountNo.Text)
                cmd.Connection = con
                rdr = cmd.ExecuteReader()
                If rdr.Read() Then
                    MetroMessageBox.Show(Me, "Account No. Already Exists", "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
                    txtAccountNo.Text = ""
                    txtAccountNo.Focus()
                    If (rdr IsNot Nothing) Then
                        rdr.Close()
                    End If
                    Return
                End If
            End If
            con = New SqlConnection(cs)
            con.Open()
            Dim cb1 As String = "update FundTransfer set AccountTransfrom=@d1 where AccountTransfrom=@d2"
            cmd = New SqlCommand(cb1)
            cmd.Parameters.AddWithValue("@d1", txtAccountNo.Text)
            cmd.Parameters.AddWithValue("@d2", txtAccNo.Text)
            cmd.Connection = con
            cmd.ExecuteNonQuery()
            con.Close()
            con = New SqlConnection(cs)
            con.Open()
            Dim cb2 As String = "update FundTransfer set AccountTransTo=@d1 where AccountTransTo=@d2"
            cmd = New SqlCommand(cb2)
            cmd.Parameters.AddWithValue("@d1", txtAccountNo.Text)
            cmd.Parameters.AddWithValue("@d2", txtAccNo.Text)
            cmd.Connection = con
            cmd.ExecuteNonQuery()
            con.Close()
            con = New SqlConnection(cs)
            con.Open()
            Dim cb As String = "Update BankAccountRegistration set  AccountNo=@d2, AccountName=@d3, AccountType=@d4, OpeningDate=@d5, BalanceAmount=@d6, Active=@d7, BranchID=@d8 where AccountNo=@d1"
            cmd = New SqlCommand(cb)
            cmd.Connection = con
            cmd.Parameters.AddWithValue("@d1", txtAccNo.Text)
            cmd.Parameters.AddWithValue("@d2", txtAccountNo.Text)
            cmd.Parameters.AddWithValue("@d3", txtAccountName.Text)
            cmd.Parameters.AddWithValue("@d4", cmbAccountType.Text)
            cmd.Parameters.AddWithValue("@d5", dtpOpeningDate.Value.Date)
            cmd.Parameters.AddWithValue("@d6", Val(txtBalanceAmount.Text))
            cmd.Parameters.AddWithValue("@d7", cmbActive.Text)
            cmd.Parameters.AddWithValue("@d8", Val(txtBranchID.Text))
            cmd.ExecuteNonQuery()
            con.Close()
            Dim st As String = "updated the Bank account registration details having account no. '" & txtAccountNo.Text & "' and Bank Name '" & txtBank.Text & "'"
            LogFunc(lblUser.Text, st)
            BankAccountLedgerUpdate(System.DateTime.Now, txtAccountNo.Text, 0, Val(txtBalanceAmount.Text), Val(txtID.Text), "Opening Balance")
            MetroMessageBox.Show(Me, "Successfully updated", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Reset()
        Catch ex As Exception
            MetroMessageBox.Show(Me, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
        End Try
    End Sub
    Public Sub Getdata1()
        Try
            con = New SqlConnection(cs)
            con.Open()
            cmd = New SqlCommand("SELECT RTRIM(AccountNo),RTRIM(AccountName),RTRIM(AccountType),Openingdate,BalanceAmount,RTRIM(Active),BranchID,RTRIM(Bank.BankName), RTRIM(BranchName), RTRIM(Address), RTRIM(ContactNo), RTRIM(SwiftCode), RTRIM(IFSCCode),BankAccountRegistration.ID from BankBranch,Bank,BankAccountRegistration where Bank.BankName=BankBranch.BankName and BankBranch.ID=BankAccountRegistration.BranchID order by Bank.BankName", con)
            rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            DataGridView1.Rows.Clear()
            While (rdr.Read() = True)
                DataGridView1.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11), rdr(12), rdr(13))
            End While
            con.Close()
        Catch ex As Exception
            MetroMessageBox.Show(Me, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub
    Public Sub Getdata()
        Try
            con = New SqlConnection(cs)
            con.Open()
            cmd = New SqlCommand("SELECT RTRIM(ID),RTRIM(BankName), RTRIM(BranchName), RTRIM(Address), RTRIM(ContactNo), RTRIM(SwiftCode), RTRIM(IFSCCode) from BankBranch order by BranchName", con)
            rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            dgw.Rows.Clear()
            While (rdr.Read() = True)
                dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6))
            End While
            con.Close()
        Catch ex As Exception
            MetroMessageBox.Show(Me, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub
    Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
        Reset()
    End Sub


    Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
        Dim strRowNumber As String = (e.RowIndex + 1).ToString()
        Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
        If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
            dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
        End If
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))

    End Sub

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

    Private Sub dgw_MouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
        Try
            If dgw.Rows.Count > 0 Then
                Dim dr As DataGridViewRow = dgw.SelectedRows(0)
                txtBranchID.Text = dr.Cells(0).Value.ToString()
                txtBank.Text = dr.Cells(1).Value.ToString()
                txtBranchName.Text = dr.Cells(2).Value.ToString()
                txtAddress.Text = dr.Cells(3).Value.ToString()
                txtContactNo.Text = dr.Cells(4).Value.ToString()
                txtSwiftCode.Text = dr.Cells(5).Value.ToString()
                txtIFSCCode.Text = dr.Cells(6).Value.ToString()
            End If
        Catch ex As Exception
            MetroMessageBox.Show(Me, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub


    Private Sub btnDelete_Click(sender As System.Object, e As System.EventArgs) Handles btnDelete.Click
        Try
            If MetroMessageBox.Show(Me, "Do you really want to delete this record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
                DeleteRecord()
            End If
        Catch ex As Exception
            MetroMessageBox.Show(Me, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub
    Private Sub DeleteRecord()

        Try
            Dim RowsAffected As Integer = 0
            con = New SqlConnection(cs)
            con.Open()
            Dim cl As String = "select AccountNo from FundDeposit,BankAccountRegistration where FundDeposit.AccNo=BankAccountRegistration.AccountNo and AccountNo=@d1"
            cmd = New SqlCommand(cl)
            cmd.Connection = con
            cmd.Parameters.AddWithValue("@d1", txtAccNo.Text)
            rdr = cmd.ExecuteReader()
            If rdr.Read Then
                MetroMessageBox.Show(Me, "Unable to delete..Already in use in Fund Deposit", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                If Not rdr Is Nothing Then
                    rdr.Close()
                End If
                Exit Sub
            End If
            con.Close()
            con = New SqlConnection(cs)
            con.Open()
            Dim cl1 As String = "select AccountNo from Payment_Withdraw,BankAccountRegistration where Payment_Withdraw.AccountFrom=BankAccountRegistration.AccountNo and AccountNo=@d1"
            cmd = New SqlCommand(cl1)
            cmd.Connection = con
            cmd.Parameters.AddWithValue("@d1", txtAccNo.Text)
            rdr = cmd.ExecuteReader()
            If rdr.Read Then
                MetroMessageBox.Show(Me, "Unable to delete..Already in use in Payment/Withdrawer", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                If Not rdr Is Nothing Then
                    rdr.Close()
                End If
                Exit Sub
            End If
            con.Close()
            con = New SqlConnection(cs)
            con.Open()
            Dim cl2 As String = "select AccountNo from FundTransfer,BankAccountRegistration where FundTransfer.AccountTransFrom=BankAccountRegistration.AccountNo and AccountNo=@d1"
            cmd = New SqlCommand(cl2)
            cmd.Connection = con
            cmd.Parameters.AddWithValue("@d1", txtAccNo.Text)
            rdr = cmd.ExecuteReader()
            If rdr.Read Then
                MetroMessageBox.Show(Me, "Unable to delete..Already in use in Fund Transfer", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                If Not rdr Is Nothing Then
                    rdr.Close()
                End If
                Exit Sub
            End If
            con.Close()
            con = New SqlConnection(cs)
            con.Open()
            Dim cl3 As String = "select AccountNo from FundTransfer,BankAccountRegistration where FundTransfer.AccountTransTo=BankAccountRegistration.AccountNo and AccountNo=@d1"
            cmd = New SqlCommand(cl3)
            cmd.Connection = con
            cmd.Parameters.AddWithValue("@d1", txtAccNo.Text)
            rdr = cmd.ExecuteReader()
            If rdr.Read Then
                MetroMessageBox.Show(Me, "Unable to delete..Already in use in Fund Transfer", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                If Not rdr Is Nothing Then
                    rdr.Close()
                End If
                Exit Sub
            End If
            con.Close()
            con = New SqlConnection(cs)
            con.Open()
            Dim cq As String = "delete from BankAccountRegistration where AccountNo=@d1"
            cmd = New SqlCommand(cq)
            cmd.Parameters.AddWithValue("@d1", txtAccNo.Text)
            cmd.Connection = con
            RowsAffected = cmd.ExecuteNonQuery()
            If RowsAffected > 0 Then
                Dim st As String = "deleted the Bank account registration having account no. '" & txtAccountNo.Text & "' and Bank Name '" & txtBank.Text & "'"
                LogFunc(lblUser.Text, st)
                BankAccountLedgerDelete(Val(txtID.Text), "Opening Balance")
                MetroMessageBox.Show(Me, "Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Getdata1()
                Reset()
            Else
                MetroMessageBox.Show(Me, "No Record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Reset()
            End If
            If con.State = ConnectionState.Open Then
                con.Close()

            End If
        Catch ex As Exception
            MetroMessageBox.Show(Me, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
        End Try
    End Sub

    Private Sub txtBalanceAmount_KeyPress(sender As System.Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txtBalanceAmount.KeyPress
        Dim keyChar = e.KeyChar

        If Char.IsControl(keyChar) Then
            'Allow all control characters.
        ElseIf Char.IsDigit(keyChar) OrElse keyChar = "."c Then
            Dim text = Me.txtBalanceAmount.Text
            Dim selectionStart = Me.txtBalanceAmount.SelectionStart
            Dim selectionLength = Me.txtBalanceAmount.SelectionLength

            text = text.Substring(0, selectionStart) & keyChar & text.Substring(selectionStart + selectionLength)

            If Integer.TryParse(text, New Integer) AndAlso text.Length > 16 Then
                'Reject an integer that is longer than 16 digits.
                e.Handled = True
            ElseIf Double.TryParse(text, New Double) AndAlso text.IndexOf("."c) < text.Length - 3 Then
                'Reject a real number with two many decimal places.
                e.Handled = False
            End If
        Else
            'Reject all other characters.
            e.Handled = True
        End If
    End Sub

    Private Sub DataGridView1_MouseClick(sender As Object, e As System.Windows.Forms.MouseEventArgs) Handles DataGridView1.MouseClick
        Try
            If DataGridView1.Rows.Count > 0 Then
                Dim dr As DataGridViewRow = DataGridView1.SelectedRows(0)
                txtAccNo.Text = dr.Cells(0).Value.ToString()
                txtAccountNo.Text = dr.Cells(0).Value.ToString()
                txtAccountName.Text = dr.Cells(1).Value.ToString()
                cmbAccountType.Text = dr.Cells(2).Value.ToString()
                dtpOpeningDate.Text = dr.Cells(3).Value.ToString()
                txtBalanceAmount.Text = dr.Cells(4).Value.ToString()
                cmbActive.Text = dr.Cells(5).Value.ToString()
                txtBranchID.Text = dr.Cells(6).Value.ToString()
                txtBank.Text = dr.Cells(7).Value.ToString()
                txtBranchName.Text = dr.Cells(8).Value.ToString()
                txtAddress.Text = dr.Cells(9).Value.ToString()
                txtContactNo.Text = dr.Cells(10).Value.ToString()
                txtSwiftCode.Text = dr.Cells(11).Value.ToString()
                txtIFSCCode.Text = dr.Cells(12).Value.ToString()
                txtID.Text = dr.Cells(13).Value.ToString()
                If IsDeleteAllowed(lblUser.Text, "Banking") = True Then
                    btnDelete.Enabled = True
                Else
                    btnDelete.Enabled = False
                End If
                If IsUpdateAllowed(lblUser.Text, "Banking") = True Then
                    btnUpdate.Enabled = True
                Else
                    btnUpdate.Enabled = False
                End If
                btnSave.Enabled = False

            End If
        Catch ex As Exception
            MetroMessageBox.Show(Me, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Sub DataGridView1_RowPostPaint(sender As Object, e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles DataGridView1.RowPostPaint
        Dim strRowNumber As String = (e.RowIndex + 1).ToString()
        Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
        If DataGridView1.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
            DataGridView1.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
        End If
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))

    End Sub
End Class
