Imports System.Data.SqlClient
Imports System.IO
Imports MetroFramework
Imports MetroFramework.Forms
Public Class frmFundTransfer
    Private Sub auto()
        Try
            Dim Num As Integer = 0
            con = New SqlConnection(cs)
            con.Open()
            Dim sql As String = ("SELECT MAX(ID) FROM FundTransfer")
            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 Clear()
        DateFrom.Value = Today
        DateTo.Value = Today
        txtAccNo.Text = ""

        If IsViewAllowed(lblUser.Text, "Banking") = True Then
            Getdata()
            GroupBox4.Enabled = True
            groupBox5.Enabled = True
        Else
            DataGridView1.Rows.Clear()
            GroupBox4.Enabled = False
            groupBox5.Enabled = False
        End If
    End Sub
    Sub Reset()
        txtOperator.Text = ""
        txtAccountName.Text = ""
        txtAccountName1.Text = ""
        cmbAccountNo.SelectedIndex = -1
        cmbAccountNo1.SelectedIndex = -1
        txtAmount.Text = ""
        dtpDate.Value = Today
        txtNotes.Text = ""
        txtIFSCCode.Text = ""
        txtSwiftCode.Text = ""
        txtBranchName.Text = ""
        txtBank.Text = ""
        txtIFSCCode1.Text = ""
        txtSwiftCode1.Text = ""
        txtBranchName1.Text = ""
        txtBalanceAmount.Text = ""
        txtBank1.Text = ""
        If IsSaveAllowed(lblUser.Text, "Banking") = True Then
            btnSave.Enabled = True
        Else
            btnSave.Enabled = False
        End If
        If IsViewAllowed(lblUser.Text, "Banking") = True Then
            Getdata()
            GroupBox4.Enabled = True
            groupBox5.Enabled = True
        Else
            DataGridView1.Rows.Clear()
            GroupBox4.Enabled = False
            groupBox5.Enabled = False
        End If
        btnDelete.Enabled = False
        txtOperator.Text = lblUser.Text
        txtOperator.Focus()
        Clear()
        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 txtOperator.Text = "" Then
            MetroMessageBox.Show(Me, "Please enter operator", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            txtOperator.Focus()
            Return
        End If
        If txtAmount.Text = "" Then
            MetroMessageBox.Show(Me, "Please enter amount", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            txtAmount.Focus()
            Return
        End If
        If Val(txtAmount.Text) <= 0 Then
            MetroMessageBox.Show(Me, "Amount must be greater than zero", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            txtAmount.Focus()
            Return
        End If
        If cmbAccountNo.Text = "" Then
            MetroMessageBox.Show(Me, "Please select account no. from", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            cmbAccountNo.Focus()
            Return
        End If
        If cmbAccountNo1.Text = "" Then
            MetroMessageBox.Show(Me, "Please select account no. to", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            cmbAccountNo1.Focus()
            Return
        End If
        If Val(txtBalanceAmount.Text) < Val(txtAmount.Text) Then
            MetroMessageBox.Show(Me, "Transferred Amount must be less than or equal to balance amount", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtAmount.Focus()
            Return
        End If
        If cmbAccountNo.Text = cmbAccountNo1.Text Then
            MetroMessageBox.Show(Me, "Both the accounts must be different", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return
        End If
        Try
            auto()
            con = New SqlConnection(cs)
            con.Open()
            Dim cb As String = "insert into FundTransfer(Id, AccountTransFrom, AccountTransTo, Amount, Date, Operator, Notes) 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", cmbAccountNo.Text)
            cmd.Parameters.AddWithValue("@d3", cmbAccountNo1.Text)
            cmd.Parameters.AddWithValue("@d4", Val(txtAmount.Text))
            cmd.Parameters.AddWithValue("@d5", dtpDate.Value.Date)
            cmd.Parameters.AddWithValue("@d6", txtOperator.Text)
            cmd.Parameters.AddWithValue("@d7", txtNotes.Text)
            cmd.ExecuteNonQuery()
            con.Close()
            Dim st As String = "added the new fund Transfer from account no. '" & cmbAccountNo.Text & "' to account no. '" & cmbAccountNo.Text & "' having Transaction ID '" & txtID.Text & "'"
            LogFunc(lblUser.Text, st)
            BankAccountLedgerSave(System.DateTime.Now, cmbAccountNo.Text, Val(txtID.Text), "Fund Transfer", Val(txtAmount.Text), 0)
            BankAccountLedgerSave(System.DateTime.Now, cmbAccountNo1.Text, Val(txtID.Text), "Fund Transfer", 0, Val(txtAmount.Text))
            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
    Public Sub Getdata()
        Try
            con = New SqlConnection(cs)
            con.Open()
            cmd = New SqlCommand("SELECT FundTransfer.Id, RTRIM(Operator), Amount, FundTransfer.Date, RTRIM(FundTransfer.Notes),RTRIM(AccountTransFrom), RTRIM(AccountTransTo) from FundTransfer order by Date", 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))
            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 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 cq As String = "delete from FundTransfer where ID=@d1"
            cmd = New SqlCommand(cq)
            cmd.Parameters.AddWithValue("@d1", Val(txtID.Text))
            cmd.Connection = con
            RowsAffected = cmd.ExecuteNonQuery()
            If RowsAffected > 0 Then
                Dim st As String = "Deleted the fund Transfer record having Transaction ID '" & txtID.Text & "'"
                LogFunc(lblUser.Text, st)
                BankAccountLedgerDelete(Val(txtID.Text), "Fund Transfer")
                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 cmbAccountNo_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbAccountNo.SelectedIndexChanged
        Try
            con = New SqlConnection(cs)
            con.Open()
            cmd = con.CreateCommand()
            cmd.CommandText = "SELECT RTRIM(AccountName),RTRIM(BankName),RTRIM(BranchName),RTRIM(SwiftCode),RTRIM(IFSCCode),IsNull(Sum(Credit)-Sum(Debit),0) from BankAccountRegistration LEFT JOIN BankBranch ON BankAccountRegistration.BranchID = BankBranch.Id LEFT JOIN BankAccountLedger ON BankAccountRegistration.AccountNo = BankAccountLedger.AccNo where AccountNo=@d1 group by AccountName,BankName,BranchName,SwiftCode,IFSCCode"
            cmd.Parameters.AddWithValue("@d1", cmbAccountNo.Text)
            rdr = cmd.ExecuteReader()
            If rdr.Read() Then
                txtAccountName.Text = rdr.GetValue(0)
                txtBank.Text = rdr.GetValue(1)
                txtBranchName.Text = rdr.GetValue(2)
                txtSwiftCode.Text = rdr.GetValue(3)
                txtIFSCCode.Text = rdr.GetValue(4)
                txtBalanceAmount.Text = rdr.GetValue(5)
            End If
            If (rdr IsNot Nothing) Then
                rdr.Close()
            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
    Sub fillAccountNo()
        Try
            Dim CN As New SqlConnection(cs)
            CN.Open()
            adp = New SqlDataAdapter()
            adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(AccountNo) FROM BankAccountRegistration where Active='Yes' order by 1", CN)
            ds = New DataSet("ds")
            adp.Fill(ds)
            dtable = ds.Tables(0)
            cmbAccountNo.Items.Clear()
            cmbAccountNo1.Items.Clear()
            For Each drow As DataRow In dtable.Rows
                cmbAccountNo.Items.Add(drow(0).ToString())
                cmbAccountNo1.Items.Add(drow(0).ToString())
            Next
            CN.Close()
        Catch ex As Exception
            MetroMessageBox.Show(Me, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Sub frmFundTransfer_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        fillAccountNo()
    End Sub

    Private Sub txtAmount_KeyPress(sender As System.Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txtAmount.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.txtAmount.Text
            Dim selectionStart = Me.txtAmount.SelectionStart
            Dim selectionLength = Me.txtAmount.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_RowPostPaint(sender As System.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

    Private Sub DataGridView1_MouseClick(sender As System.Object, e As System.Windows.Forms.MouseEventArgs) Handles DataGridView1.MouseClick
        Try
            If DataGridView1.Rows.Count > 0 Then
                Dim dr As DataGridViewRow = DataGridView1.SelectedRows(0)
                txtID.Text = dr.Cells(0).Value.ToString()
                txtOperator.Text = dr.Cells(1).Value.ToString()
                txtAmount.Text = dr.Cells(2).Value.ToString()
                dtpDate.Text = dr.Cells(3).Value.ToString()
                txtNotes.Text = dr.Cells(4).Value.ToString()
                cmbAccountNo.Text = dr.Cells(5).Value.ToString()
                cmbAccountNo1.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
            End If
        Catch ex As Exception
            MetroMessageBox.Show(Me, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
        Clear()
    End Sub

    Private Sub btnExportExcel_Click(sender As System.Object, e As System.EventArgs) Handles btnExportExcel.Click
        ExportExcel(DataGridView1)
    End Sub

    Private Sub txtAccNo_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtAccNo.TextChanged
        Try
            con = New SqlConnection(cs)
            con.Open()
            cmd = New SqlCommand("SELECT FundTransfer.Id, RTRIM(Operator), Amount, FundTransfer.Date, RTRIM(FundTransfer.Notes),RTRIM(AccountTransFrom), RTRIM(AccountTransTo) from FundTransfer where AccountTransFrom like '%" & txtAccNo.Text & "%' or AccountTransTo like '%" & txtAccNo.Text & "%' order by Date", 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))
            End While
            con.Close()
        Catch ex As Exception
            MetroMessageBox.Show(Me, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Try
            con = New SqlConnection(cs)
            con.Open()
            cmd = New SqlCommand("SELECT FundTransfer.Id, RTRIM(Operator), Amount, FundTransfer.Date, RTRIM(FundTransfer.Notes),RTRIM(AccountTransFrom), RTRIM(AccountTransTo) from FundTransfer where Date between @d1 and @d2 order by Date", con)
            cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "DateIN").Value = DateFrom.Value.Date
            cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "DateIN").Value = DateTo.Value.Date
            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))
            End While
            con.Close()
        Catch ex As Exception
            MetroMessageBox.Show(Me, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Sub cmbAccountNo1_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbAccountNo1.SelectedIndexChanged
        Try
            con = New SqlConnection(cs)
            con.Open()
            cmd = con.CreateCommand()
            cmd.CommandText = "SELECT RTRIM(AccountName),RTRIM(BankName),RTRIM(BranchName),RTRIM(SwiftCode),RTRIM(IFSCCode) from BankBranch,BankAccountRegistration where BankBranch.ID=BankAccountRegistration.BranchID and AccountNo=@d1"
            cmd.Parameters.AddWithValue("@d1", cmbAccountNo1.Text)
            rdr = cmd.ExecuteReader()
            If rdr.Read() Then
                txtAccountName1.Text = rdr.GetValue(0)
                txtBank1.Text = rdr.GetValue(1)
                txtBranchName1.Text = rdr.GetValue(2)
                txtSwiftCode1.Text = rdr.GetValue(3)
                txtIFSCCode1.Text = rdr.GetValue(4)
            End If
            If (rdr IsNot Nothing) Then
                rdr.Close()
            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
End Class
