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

Public Class frmBankAccountStatements

    Dim a, b, c, d, f, g, h, i As Decimal

    Sub Reset()
        dtpDateFrom.Text = Today
        dtpDateTo.Text = Today
        DateTimePicker1.Value = Today
        DateTimePicker2.Value = Today
        cmbAccountNo.SelectedIndex = -1
    End Sub
    Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
        Reset()
    End Sub


    Private Sub btnClose_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

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


    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Try
            con = New SqlConnection(cs)
            con.Open()
            Dim ct As String = "select * from BankAccountLedger where Date >=@d1 and Date < @d2"
            cmd = New SqlCommand(ct)
            cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
            cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date.AddDays(1)
            cmd.Connection = con
            rdr = cmd.ExecuteReader()
            If Not rdr.Read() Then
                MetroMessageBox.Show(Me, "Sorry...No record found", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
                If (rdr IsNot Nothing) Then
                    rdr.Close()
                End If
                Return
            End If
            Cursor = Cursors.WaitCursor
            Timer1.Enabled = True
            con = New SqlConnection(cs)
            con.Open()
            cmd = New SqlCommand("Select  Id, Date, AccNo, LedgerNo, Label, Debit, Credit from BankAccountLedger where Date >=@d1 and Date < @d2 order by ID,Date,LedgerNo", con)
            cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
            cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date.AddDays(1)
            adp = New SqlDataAdapter(cmd)
            dtable = New DataTable()
            adp.Fill(dtable)
            con.Close()
            ds = New DataSet()
            ds.Tables.Add(dtable)
            ds.WriteXmlSchema("BankAccountStatements1.xml")
            Dim rpt As New rptBankAccountStatements
            rpt.SetDataSource(ds)
            rpt.SetParameterValue("p1", dtpDateFrom.Value.Date)
            rpt.SetParameterValue("p2", dtpDateTo.Value.Date)
            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
    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 frmBankAccountStatements_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        fillAccountNo()
    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        Try
            If cmbAccountNo.Text = "" Then
                MetroMessageBox.Show(Me, "Please select account no.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                cmbAccountNo.Focus()
                Return
            End If
            con = New SqlConnection(cs)
            con.Open()
            Dim ct As String = "select * from BankAccountLedger where Date >=@d1 and Date < @d2 and AccNo=@d3"
            cmd = New SqlCommand(ct)
            cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = DateTimePicker2.Value.Date
            cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = DateTimePicker1.Value.Date.AddDays(1)
            cmd.Parameters.AddWithValue("@d3", cmbAccountNo.Text)
            cmd.Connection = con
            rdr = cmd.ExecuteReader()
            If Not rdr.Read() Then
                MetroMessageBox.Show(Me, "Sorry...No record found", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
                If (rdr IsNot Nothing) Then
                    rdr.Close()
                End If
                Return
            End If
            Cursor = Cursors.WaitCursor
            Timer1.Enabled = True
            con = New SqlConnection(cs)
            con.Open()
            cmd = New SqlCommand("SELECT Credit,Debit, BankAccountLedger.Id, BankAccountLedger.AccNo, BankAccountLedger.Date, BankAccountLedger.LedgerNo, BankAccountLedger.Label, BankAccountLedger.Debit, BankBranch.BranchName, BankBranch.SwiftCode, BankBranch.IFSCCode, BankBranch.BankName, BankAccountRegistration.AccountName FROM Bank INNER JOIN BankBranch ON Bank.BankName = BankBranch.BankName INNER JOIN BankAccountRegistration ON BankBranch.Id = BankAccountRegistration.BranchID INNER JOIN BankAccountLedger ON BankAccountRegistration.AccountNo = BankAccountLedger.AccNo where Date >=@d1 and Date < @d2 and AccNo=@d3 order by ID,Date,LedgerNo", con)
            cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = DateTimePicker2.Value.Date
            cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = DateTimePicker1.Value.Date.AddDays(1)
            cmd.Parameters.AddWithValue("@d3", cmbAccountNo.Text)
            adp = New SqlDataAdapter(cmd)
            dtable = New DataTable()
            adp.Fill(dtable)
            con.Close()
            ds = New DataSet()
            ds.Tables.Add(dtable)
            ds.WriteXmlSchema("BankAccountStatements3.xml")
            Dim rpt As New rptBankAccountStatements1
            rpt.SetDataSource(ds)
            rpt.SetParameterValue("p1", DateTimePicker2.Value.Date)
            rpt.SetParameterValue("p2", DateTimePicker1.Value.Date)
            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
End Class
