Imports System.Data.Odbc
Imports System.Data.OleDb
Public Class frmDataSet
    Dim StopWatch As New Diagnostics.Stopwatch
    Dim nJmlData As Byte
    Dim nJumlah As Single
    Dim cId_Dataset As String
    Dim nNilaix As Integer
    Sub TampilBulan()
        cboBulan.Items.Clear()
        For A = 0 To 11
            cboBulan.Items.Add(cBulan(A))
        Next
    End Sub
    Sub databaru()
        txtJumlah.Text = ""
        nTahun.Focus()
    End Sub
    Sub kosongkan()
        Call tidaksiapisi()
        txtJumlah.Text = ""
        nTahun.Enabled = True
        cboBulan.Enabled = True

        btnHapus.Enabled = False
        btnSimpan.Enabled = False
        nTahun.Focus()
    End Sub
    Sub siapisi()
        txtJumlah.Enabled = True
        btnHapus.Enabled = False
        btnSimpan.Enabled = True
        nTahun.Enabled = True
        txtJumlah.Focus()
    End Sub
    Sub tidaksiapisi()
        txtJumlah.Enabled = False
        btnSimpan.Enabled = False
        btnHapus.Enabled = False
        btnimport.Enabled = False
    End Sub
   
    Sub CekData()
        cmd = New OdbcCommand("select * from tbdataset where tahun = '" & nTahun.Text & "' and " & _
                              "id_bulan= '" & cId_Bulan & "' ", Conn)
        rd = cmd.ExecuteReader
        rd.Read()
        If rd.HasRows = True Then
            txtJumlah.Text = rd.Item("jml_pendapatan")
            '   Call tidaksiapisi()
            btnHapus.Enabled = True
            '  cboBulan.Enabled = False
            ' nTahun.Enabled = False
            txtJumlah.Enabled = True
            btnSimpan.Enabled = True
        Else
            Call databaru()
            Call siapisi()
        End If
    End Sub

    Sub Tampilkandata()
        dg2.Columns.Clear()
        dg2.Columns.Add("kode", "Tahun")
        dg2.Columns.Add("kode", "Bulan")
        dg2.Columns.Add("kode", "Jml pendapatan")

        Dim sql As String = "Select tahun,id_bulan,jml_pendapatan " & _
                                 " from tbdataset  order by tahun,id_bulan"

        cmd = New OdbcCommand(sql, Conn)
        rd = cmd.ExecuteReader
        Dim i As Integer = 0
        While rd.Read
            dg2.Rows.Add()
            dg2.Item(0, i).Value = rd(0)
            dg2.Item(1, i).Value = NamaBulan(rd(1) - 1)
            dg2.Item(2, i).Value = rd(2)
            i += 1
        End While

        rd.Close()
        dg2.ReadOnly = True
        dg2.AllowUserToAddRows = False
        dg2.Columns(0).Width = 80
        dg2.Columns(1).Width = 110
        dg2.Columns(2).Width = 120
        dg2.GridColor = Color.Blue
        dg2.DefaultCellStyle.ForeColor = Color.Blue
        dg2.RowHeadersDefaultCellStyle.BackColor = Color.DeepPink
        dg2.AlternatingRowsDefaultCellStyle.BackColor = Color.Yellow
        dg2.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        dg2.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        dg2.Columns(0).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        dg2.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        dg2.Columns(2).DefaultCellStyle.Format = "#,###"


    End Sub
    Private Sub btnHapus_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnHapus.Click

        If MessageBox.Show("Yakin akan dihapus..?", "Hapus Data", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.Yes Then
            cmd = New OdbcCommand("Delete  from tbdataset where  tahun = '" & nTahun.Text & "' and id_bulan= '" & cId_Bulan & "' ", Conn)
            cmd.ExecuteNonQuery()
        End If

        Call Tampilkandata()
        Call kosongkan()
    End Sub

    Private Sub btnTutup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTutup.Click
        Me.Dispose()
    End Sub

    Private Sub frmDataTraining_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles Me.KeyPress
        If Asc(e.KeyChar) = Keys.Enter Then
            SendKeys.Send("{tab}")
        End If
    End Sub


    Private Sub frmDataSet_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Koneksi()

        nTahun.Value = Year(Now)
        Call Tampilkandata()
        Call kosongkan()
        Call TampilBulan()

    End Sub

    Private Sub DG2_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dg2.CellDoubleClick
        nTahun.Text = dg2.Rows(e.RowIndex).Cells(0).Value
        cboBulan.Text = dg2.Rows(e.RowIndex).Cells(1).Value
        Call CekData()
    End Sub



    Private Sub btnSimpan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSimpan.Click
        If txtJumlah.Text = "" Then
            MsgBox("Maaf, Data belum lengkap...", , "Perhatian...!")
            txtJumlah.Focus()
            Exit Sub
        End If
        Cek_IdDataset()
        cmd = New OdbcCommand("Select * from tbdataset where tahun = '" & nTahun.Text & "' and id_bulan= '" & cId_Bulan & "' ", Conn)
        rd = cmd.ExecuteReader
        rd.Read()
        If Not rd.HasRows Then
            Dim sqltambah As String = "Insert into tbdataset (id_dataset,tahun,id_bulan,jml_pendapatan,user_id) values " & _
            "('" & cId_Dataset & "','" & nTahun.Text & "', '" & cId_Bulan & "','" & nNilaix & "','" & cUserId & "')"
            cmd = New OdbcCommand(sqltambah, Conn)

        Else
            Dim sqledit As String = "Update tbdataset set " & _
                "jml_pendapatan='" & nNilaix & "', " & _
                "user_id='" & cUserId & "' " & _
                " where  tahun = '" & nTahun.Text & "' and id_bulan= '" & cId_Bulan & "' "
            cmd = New OdbcCommand(sqledit, Conn)

        End If
        cmd.ExecuteNonQuery()
        Call kosongkan()
        Call Tampilkandata()

    End Sub

    Private Sub btnBatal_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBatal.Click
        Call kosongkan()
        Call Tampilkandata()
        nTahun.Focus()
    End Sub

    Private Sub cmdimport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnimport.Click
        Dim conn_xls As OleDbConnection
        Dim cmd_xls As OleDbCommand
        Dim rd_xls As OleDbDataReader
        Dim nJmlDataExcel As Integer
        Me.StopWatch.Reset()

        If btnimport.Text = "&Import" Then
            If txtNamaFile.Text = "" Then
                MsgBox("Maaf, File Excel belum dipilih !!!", 0 + 16, "Konfirmasi")
                '  cmdCari_Click()
                Exit Sub
            End If
            conn_xls = New OleDbConnection("provider=Microsoft.ace.OLEDB.12.0;data source=" & txtNamaFile.Text & ";Extended Properties=Excel 12.0;")
            conn_xls.Open()
            cmd_xls = New OleDbCommand("SELECT COUNT(*) AS jml_rec FROM [sheet1$]", conn_xls)
            rd_xls = cmd_xls.ExecuteReader
            rd_xls.Read()
            nJmlDataExcel = rd_xls.Item(0)
            cmd_xls = New OleDbCommand("SELECT * FROM [sheet1$]", conn_xls)
            rd_xls = cmd_xls.ExecuteReader
            btnimport.Visible = False
            Me.Refresh()

            Timer1.Enabled = True
            nData = 0
            pBar.Maximum = 100
            Timer1.Start()
            Me.StopWatch.Start()
            While rd_xls.Read
                nTahun.Text = rd_xls.Item(0)
                cId_Bulan = NilaiBulan(Trim(rd_xls.Item(1)))
                Cek_IdDataset()
                If cId_Bulan <> 0 Then
                    cmd = New OdbcCommand("Select * from tbdataset where tahun='" & nTahun.Text & "' and id_bulan= '" & cId_Bulan & "' ", Conn)
                    rd = cmd.ExecuteReader
                    rd.Read()
                    If Not rd.HasRows Then
                        Dim sqltambah As String = "Insert into tbdataset (id_dataset,tahun,id_bulan,jml_pendapatan,user_id) values " & _
                        "('" & cId_Dataset & "','" & nTahun.Text & "','" & cId_Bulan & "'," & rd_xls.Item(2) & ",'" & cUserId & "')"
                        cmd = New OdbcCommand(sqltambah, Conn)

                    Else
                        Dim sqledit As String = "Update tbdataset set " & _
                          "jml_pendapatan=" & rd_xls.Item(2) & ", " & _
                          "user_id='" & cUserId & "' " & _
                          " where tahun='" & nTahun.Text & "' and id_bulan ='" & cId_Bulan & "' "
                        cmd = New OdbcCommand(sqledit, Conn)

                    End If

                    cmd.ExecuteNonQuery()
                End If
                nData = nData + 1
                pBar.PerformStep()
                'Hitung persentase kinerja pemrosesan data dan tampilkan dalam status bar
                labelPersen.Text = ((nData / nJmlDataExcel) * 100).ToString("N2") & "% Completing..."
                pBar.Value = Math.Round(nData / nJmlDataExcel * 100)
                Application.DoEvents()
            End While
            Timer1.Stop()
            Me.StopWatch.Stop()
            labelPersen.Text = "100% Completed"
            conn_xls.Close()
            btnimport.Visible = True
            MsgBox("Import Data Sukses.", vbExclamation, "Information")
            btnimport.Enabled = False
            pBar.Value = 0
            labelPersen.Text = ""
            Call Tampilkandata()
        End If
    End Sub

    Private Sub cmdCari_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCari.Click

        Dim FileExcel As String
        cDialog.Title = "Excel File"
        cDialog.Filter = "Excel (*.xlsx)|*.xlsx"
        cDialog.InitialDirectory = Application.ExecutablePath
        cDialog.ShowDialog()
        FileExcel = cDialog.FileName
        txtNamaFile.Text = FileExcel
        If txtNamaFile.Text <> "" Then
            btnimport.Enabled = True
            stsBar.Visible = True
        Else
            btnimport.Enabled = False
        End If
    End Sub

    Private Sub txtJumlah_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtJumlah.KeyPress
        If Not ((e.KeyChar >= "0" And e.KeyChar <= "9") Or e.KeyChar = vbBack) Then e.Handled() = True
        ' If Not ((e.KeyChar >= "0" And e.KeyChar <= "9") Or e.KeyChar = vbBack Or e.KeyChar = ".") Then e.Handled() = True
    End Sub



    Private Sub cboBulan_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboBulan.SelectedIndexChanged
        If cboBulan.Text = "" Then Exit Sub
        cId_Bulan = NilaiBulan(Trim(cboBulan.Text))
        If cId_Bulan = 0 Then
            MsgBox("Maaf, Nama Bulan tidak dikenal atau salah isi...", , "Perhatian...!")
            cboBulan.Text = ""
            cboBulan.Focus()
            Exit Sub
        End If
        Call CekData()
    End Sub


    Private Sub cboBulan_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboBulan.Validated
        cboBulan_SelectedIndexChanged(sender, e)
    End Sub

    Private Sub nTahun_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles nTahun.ValueChanged
        If cboBulan.Text <> "" Then
            Call CekData()
        End If
    End Sub
    Sub Cek_IdDataset()
        Dim cBln As String
        If cId_Bulan < 10 Then
            cBln = "0" + Trim(Microsoft.VisualBasic.Str(cId_Bulan))
        Else
            cBln = Trim(Microsoft.VisualBasic.Str(cId_Bulan))
        End If
        cId_Dataset = nTahun.Text + cBln
    End Sub

    Private Sub txtJumlah_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtJumlah.TextChanged
        With Me.txtJumlah
            If .Text = "" Then .Text = 0
            .Text = FormatNumber(.Text, 0)
            .SelectionStart = .Text.Length
        End With
        nNilaix = txtJumlah.Text
    End Sub
End Class