Imports System.Data.SqlClient
Imports MetroFramework
Imports MetroFramework.Forms

Public Class frmPayment_Withdrawal
    Private Sub auto()
        Try
            Dim Num As Integer = 0
            con = New SqlConnection(cs)
            con.Open()
            Dim sql As String = ("SELECT MAX(ID) FROM Payment_Withdraw")
            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()
        txtR_W.Text = ""
        txtAccountName.Text = ""
        cmbAccountNo.SelectedIndex = -1
        cmbPaymentMode.SelectedIndex = 0
        txtContactNo.Text = ""
        txtBalanceAmount.Text = ""
        txtAmount.Text = ""
        dtpDate.Value = Today
        txtNotes.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
            Getdata()
            GroupBox4.Enabled = True
            groupBox5.Enabled = True
        Else
            DataGridView1.Rows.Clear()
            GroupBox4.Enabled = False
            groupBox5.Enabled = False
        End If
        btnDelete.Enabled = False
        btnPrint.Enabled = False
        txtR_W.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 txtR_W.Text = "" Then
            MetroMessageBox.Show(Me, "Please enter receiver/withdrawer name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            txtR_W.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.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            cmbAccountNo.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
        Try
            auto()
            con = New SqlConnection(cs)
            con.Open()
            Dim cb As String = "insert into Payment_Withdraw( id, AccountFrom, PaymentMode, Date, Amount, ReceiverName, PhoneNo, Notes) VALUES (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8)"
            cmd = New SqlCommand(cb)
            cmd.Connection = con
            cmd.Parameters.AddWithValue("@d1", Val(txtID.Text))
            cmd.Parameters.AddWithValue("@d2", cmbAccountNo.Text)
            cmd.Parameters.AddWithValue("@d3", cmbPaymentMode.Text)
            cmd.Parameters.AddWithValue("@d4", dtpDate.Value.Date)
            cmd.Parameters.AddWithValue("@d5", Val(txtAmount.Text))
            cmd.Parameters.AddWithValue("@d6", txtR_W.Text)
            cmd.Parameters.AddWithValue("@d7", txtContactNo.Text)
            cmd.Parameters.AddWithValue("@d8", txtNotes.Text)
            cmd.ExecuteNonQuery()
            con.Close()
            Dim st As String = "added the new Payment/Withdrawal from account no. '" & cmbAccountNo.Text & "' having Transaction ID '" & txtID.Text & "'"
            LogFunc(lblUser.Text, st)
            BankAccountLedgerSave(System.DateTime.Now, cmbAccountNo.Text, Val(txtID.Text), "Payment/Withdrawal", Val(txtAmount.Text), 0)
            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 Payment_Withdraw.Id, RTRIM(ReceiverName),RTRIM(PhoneNo), Amount, Payment_Withdraw.Date, RTRIM(PaymentMode),RTRIM(Payment_Withdraw.Notes),RTRIM(AccountFrom) from Payment_Withdraw 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), rdr(7))
            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 Payment_Withdraw 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 Payment/Withdrawal record having account no. '" & cmbAccountNo.Text & "' and Transaction ID '" & txtID.Text & "'"
                LogFunc(lblUser.Text, st)
                BankAccountLedgerDelete(Val(txtID.Text), "Payment/Withdrawal")
                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()
            For Each drow As DataRow In dtable.Rows
                cmbAccountNo.Items.Add(drow(0).ToString())
            Next
        Catch ex As Exception
            MetroMessageBox.Show(Me, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Sub frmPayment_Withdraw_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()
                txtR_W.Text = dr.Cells(1).Value.ToString()
                txtContactNo.Text = dr.Cells(2).Value.ToString()
                txtAmount.Text = dr.Cells(3).Value.ToString()
                dtpDate.Text = dr.Cells(4).Value.ToString()
                cmbPaymentMode.Text = dr.Cells(5).Value.ToString()
                txtNotes.Text = dr.Cells(6).Value.ToString()
                cmbAccountNo.Text = dr.Cells(7).Value.ToString()
                btnSave.Enabled = False
                If IsDeleteAllowed(lblUser.Text, "Banking") = True Then
                    btnDelete.Enabled = True
                Else
                    btnDelete.Enabled = False
                End If
                btnPrint.Enabled = True
            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 Payment_Withdraw.Id, RTRIM(ReceiverName),RTRIM(PhoneNo), Amount, Payment_Withdraw.Date, RTRIM(PaymentMode),RTRIM(Payment_Withdraw.Notes),RTRIM(AccountFrom) from Payment_Withdraw where AccountFrom 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), rdr(7))
            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 Payment_Withdraw.Id, RTRIM(ReceiverName),RTRIM(PhoneNo), Amount, Payment_Withdraw.Date, RTRIM(PaymentMode),RTRIM(Payment_Withdraw.Notes),RTRIM(AccountFrom) from Payment_Withdraw 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), rdr(7))
            End While
            con.Close()
        Catch ex As Exception
            MetroMessageBox.Show(Me, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Sub btnPrint_Click(sender As System.Object, e As System.EventArgs) Handles btnPrint.Click
        Try

            Cursor = Cursors.WaitCursor
            Timer2.Enabled = True
            Dim cmd, cmd1 As SqlCommand
            Dim ds As DataSet
            Dim adp, adp1 As SqlDataAdapter
            Dim dtable, dtable1 As DataTable
            con = New SqlConnection(cs)
            con.Open()
            cmd = New SqlCommand("SELECT * from Hotel", con)
            adp = New SqlDataAdapter(cmd)
            cmd1 = New SqlCommand("SELECT Payment_Withdraw.Id, ReceiverName,PhoneNo, Amount, Payment_Withdraw.Date,PaymentMode,Payment_Withdraw.Notes,AccountFrom from Payment_Withdraw where Payment_Withdraw.ID=" & Val(txtID.Text) & "", con)
            adp1 = New SqlDataAdapter(cmd1)
            dtable = New DataTable()
            dtable1 = New DataTable()
            adp.Fill(dtable)
            adp1.Fill(dtable1)
            ds = New DataSet()
            ds.Tables.Add(dtable)
            ds.Tables.Add(dtable1)
            ds.WriteXmlSchema("Payment_WithdrawerReceipt.xml")
            Dim rpt As New rptPayment_WithdrawalReceipt
            rpt.SetDataSource(ds)
            frmReport.CrystalReportViewer1.ReportSource = rpt
            frmReport.ShowDialog()
            rpt.Close()
            rpt.Dispose()
        Catch ex As Exception
            MetroMessageBox.Show(Me, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Sub Timer2_Tick(sender As System.Object, e As System.EventArgs) Handles Timer2.Tick
        Cursor = Cursors.Default
        Timer2.Enabled = False
    End Sub
End Class
