Imports System.Data.SqlClient
Imports System.IO
Imports MetroFramework
Imports MetroFramework.Forms
Public Class frmBranchMaster_Bank
    Private Sub auto()
        Try
            Dim Num As Integer = 0
            con = New SqlConnection(cs)
            con.Open()
            Dim sql As String = ("SELECT MAX(ID) FROM BankBranch")
            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 fillCombo()
        Try
            con = New SqlConnection(cs)
            con.Open()
            adp = New SqlDataAdapter()
            adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(BankName) FROM Bank", con)
            ds = New DataSet("ds")
            adp.Fill(ds)
            dtable = ds.Tables(0)
            cmbBank.Items.Clear()
            For Each drow As DataRow In dtable.Rows
                cmbBank.Items.Add(drow(0).ToString())
            Next
        Catch ex As Exception
            MetroMessageBox.Show(Me, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub
    Sub Reset()
        txtAddress.Text = ""
        txtContactNo.Text = ""
        txtIFSCCode.Text = ""
        txtSwiftCode.Text = ""
        txtBranchName.Text = ""
        cmbBank.SelectedIndex = -1
        If IsSaveAllowed(lblUser.Text, "Banking") = True Then
            btnSave.Enabled = True
        Else
            btnSave.Enabled = False
        End If
        If IsViewAllowed(lblUser.Text, "Banking") = True Then
            Getdata()
        Else
            dgw.Rows.Clear()
        End If

        btnUpdate.Enabled = False
        btnDelete.Enabled = False
        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 cmbBank.Text = "" Then
            MetroMessageBox.Show(Me, "Please select bank", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            cmbBank.Focus()
            Return
        End If
        If txtBranchName.Text = "" Then
            MetroMessageBox.Show(Me, "Please enter branch name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            txtBranchName.Focus()
            Return
        End If
        If txtAddress.Text = "" Then
            MetroMessageBox.Show(Me, "Please enter address", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            txtAddress.Focus()
            Return
        End If
        If txtContactNo.Text = "" Then
            MetroMessageBox.Show(Me, "Please enter contact no.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            txtContactNo.Focus()
            Return
        End If


        Try
            con = New SqlConnection(cs)
            con.Open()
            Dim ct As String = "select BranchName from BankBranch where BranchName=@d1"
            cmd = New SqlCommand(ct)
            cmd.Parameters.AddWithValue("@d1", txtBranchName.Text)
            cmd.Connection = con
            rdr = cmd.ExecuteReader()

            If rdr.Read() Then
                MetroMessageBox.Show(Me, "Branch Name Already Exists", "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
                txtBranchName.Text = ""
                txtBranchName.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 BankBranch(ID,BranchName,Address,ContactNo,SwiftCode,IFSCCode,BankName) VALUES (@d1,@d2,@d3,@d4,@d5,@d6,@d7)"
            cmd = New SqlCommand(cb)
            cmd.Connection = con
            cmd.Parameters.AddWithValue("@d1", Val(txtID.Text))
            cmd.Parameters.AddWithValue("@d2", txtBranchName.Text)
            cmd.Parameters.AddWithValue("@d3", txtAddress.Text)
            cmd.Parameters.AddWithValue("@d4", txtContactNo.Text)
            cmd.Parameters.AddWithValue("@d5", txtSwiftCode.Text)
            cmd.Parameters.AddWithValue("@d6", txtIFSCCode.Text)
            cmd.Parameters.AddWithValue("@d7", cmbBank.Text)
            cmd.ExecuteNonQuery()
            con.Close()
            Dim st As String = "added the new bank's branch '" & txtBranchName.Text & "' info"
            LogFunc(lblUser.Text, st)
            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 cmbBank.Text = "" Then
            MetroMessageBox.Show(Me, "Please select bank", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            cmbBank.Focus()
            Return
        End If
        If txtBranchName.Text = "" Then
            MetroMessageBox.Show(Me, "Please enter branch name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            txtBranchName.Focus()
            Return
        End If
        If txtAddress.Text = "" Then
            MetroMessageBox.Show(Me, "Please enter address", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            txtAddress.Focus()
            Return
        End If
        If txtContactNo.Text = "" Then
            MetroMessageBox.Show(Me, "Please enter contact no.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            txtContactNo.Focus()
            Return
        End If


        Try

            con = New SqlConnection(cs)
            con.Open()
            Dim cb As String = "Update BankBranch set BranchName=@d2,Address=@d3,ContactNo=@d4,SwiftCode=@d5,IFSCCode=@d6,BankName=@d7 where ID=@d1"
            cmd = New SqlCommand(cb)
            cmd.Connection = con
            cmd.Parameters.AddWithValue("@d1", Val(txtID.Text))
            cmd.Parameters.AddWithValue("@d2", txtBranchName.Text)
            cmd.Parameters.AddWithValue("@d3", txtAddress.Text)
            cmd.Parameters.AddWithValue("@d4", txtContactNo.Text)
            cmd.Parameters.AddWithValue("@d5", txtSwiftCode.Text)
            cmd.Parameters.AddWithValue("@d6", txtIFSCCode.Text)
            cmd.Parameters.AddWithValue("@d7", cmbBank.Text)
            cmd.ExecuteNonQuery()
            con.Close()
            Dim st As String = "updated the bank's branch '" & txtBranchName.Text & "' info"
            LogFunc(lblUser.Text, st)
            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 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
        fillCombo()
    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)
                txtID.Text = dr.Cells(0).Value.ToString()
                cmbBank.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()

                btnSave.Enabled = False
                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
            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 BankBranch.ID from BankBranch,BankAccountRegistration where BankBranch.ID=BankAccountRegistration.BranchID and BankBranch.ID=@d1"
            cmd = New SqlCommand(cl)
            cmd.Connection = con
            cmd.Parameters.AddWithValue("@d1", Val(txtID.Text))
            rdr = cmd.ExecuteReader()
            If rdr.Read Then
                MetroMessageBox.Show(Me, "Unable to delete..Already in use in Bank Accounts Registration", "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 BankBranch where ID=" & Val(txtID.Text) & ""
            cmd = New SqlCommand(cq)
            cmd.Connection = con
            RowsAffected = cmd.ExecuteNonQuery()
            If RowsAffected > 0 Then
                Dim st As String = "deleted the Bank's branch '" & txtBranchName.Text & "' record"
                LogFunc(lblUser.Text, st)
                MetroMessageBox.Show(Me, "Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
                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 btnAddCategory_Click(sender As System.Object, e As System.EventArgs) Handles btnAddCategory.Click
        frmBank.lblUser.Text = lblUser.Text
        frmBank.lblSet.Text = "Branch"
        frmBank.Reset()
        frmBank.ShowDialog()
    End Sub
End Class
