Imports System.Data.Odbc

Public Class frmSettingDataset
    Dim cKode_Kec As String
    Sub KosongkanData()
        nudDatasetAwal.Value = 0
        nudDatasetAkhir.Value = 0
        nudAkurasiAWal.Value = 0
        nudAkurasiAkhir.Value = 0
        btnSimpan.Enabled = False
    End Sub
  

    Sub CekData()
        cmd = New OdbcCommand("Select * from tbsetting_dataset  ", Conn)
        rd = cmd.ExecuteReader
        rd.Read()
        If rd.HasRows Then
            nudDatasetAwal.Value = rd.Item("dataset_awal")
            nudDatasetAkhir.Value = rd.Item("dataset_akhir")
            nudAkurasiAWal.Value = rd.Item("akurasi_awal")
            nudAkurasiAkhir.Value = rd.Item("akurasi_akhir")

        End If
    End Sub
    Sub Tampilkandata()

        dg2.Columns.Clear()
        dg2.Columns.Add("kode", "No. Record")
        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 = i + 1
            dg2.Item(1, i).Value = rd(0)
            dg2.Item(2, i).Value = NamaBulan(rd(1) - 1)
            dg2.Item(3, i).Value = rd(2)
            i += 1
        End While

        rd.Close()
        dg2.ReadOnly = True
        dg2.AllowUserToAddRows = False
        dg2.Columns(0).Width = 60
        dg2.Columns(1).Width = 80
        dg2.Columns(2).Width = 90
        dg2.Columns(3).Width = 100

        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(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        dg2.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        dg2.Columns(3).DefaultCellStyle.Format = "#,###.#0"

        If dg2.RowCount > 1 Then
            btnSimpan.Enabled = True
            nudDatasetAwal.Minimum = 2
            nudDatasetAwal.Maximum = dg2.RowCount
            nudDatasetAkhir.Minimum = 2
            nudDatasetAkhir.Maximum = dg2.RowCount
            nudAkurasiAWal.Minimum = 1
            nudAkurasiAWal.Maximum = dg2.RowCount
            nudAkurasiAkhir.Minimum = 1
            nudAkurasiAkhir.Maximum = dg2.RowCount

            nudDatasetAwal.Value = nudDatasetAwal.Minimum
            nudDatasetAkhir.Value = nudDatasetAkhir.Maximum
            nudAkurasiAWal.Value = nudAkurasiAWal.Minimum
            nudAkurasiAkhir.Value = nudAkurasiAkhir.Maximum
        Else
            btnSimpan.Enabled = False
            nudDatasetAwal.Minimum = 0
            nudDatasetAwal.Maximum = 999
            nudDatasetAkhir.Minimum = 0
            nudDatasetAkhir.Maximum = 999
            nudAkurasiAWal.Minimum = 0
            nudAkurasiAWal.Maximum = 999
            nudAkurasiAkhir.Minimum = 0
            nudAkurasiAkhir.Maximum = 999
            Call KosongkanData()
        End If
    End Sub



    Private Sub frmSettingDataset_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call Tampilkandata()
        Call CekData()

    End Sub

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

    Private Sub btnSimpan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSimpan.Click
        If nudDatasetAwal.Value = 0 Then
            MsgBox("Maaf, Datset Awal masih kosong...", , "Perhatian...!")
            nudDatasetAwal.Focus()
            Exit Sub
        End If
        If nudDatasetAkhir.Value = 0 Then
            MsgBox("Maaf, Datset Akhir masih kosong...", , "Perhatian...!")
            nudDatasetAkhir.Focus()
            Exit Sub
        End If
        If nudAkurasiAWal.Value = 0 Then
            MsgBox("Maaf, Akurasi Awal masih kosong...", , "Perhatian...!")
            nudAkurasiAWal.Focus()
            Exit Sub
        End If
        If nudAkurasiAkhir.Value = 0 Then
            MsgBox("Maaf, Akurasi Akhir masih kosong...", , "Perhatian...!")
            nudAkurasiAkhir.Focus()
            Exit Sub
        End If
        cmd = New OdbcCommand("Select * from tbsetting_dataset  ", Conn)
        rd = cmd.ExecuteReader
        rd.Read()
        If Not rd.HasRows Then
            Dim sqltambah As String = "Insert into tbsetting_dataset (dataset_awal,dataset_akhir,akurasi_awal,akurasi_akhir) values " & _
            "('" & nudDatasetAwal.Value & "','" & nudDatasetAkhir.Value & "','" & nudAkurasiAWal.Value & "','" & nudAkurasiAkhir.Value & "')"
            cmd = New OdbcCommand(sqltambah, Conn)

        Else
            Dim sqledit As String = "Update tbsetting_dataset set " & _
            "dataset_awal='" & nudDatasetAwal.Value & "', " & _
            "dataset_akhir='" & nudDatasetAkhir.Value & "', " & _
             "akurasi_awal='" & nudAkurasiAWal.Value & "', " & _
            "akurasi_akhir='" & nudAkurasiAkhir.Value & "' "
            cmd = New OdbcCommand(sqledit, Conn)

        End If
        cmd.ExecuteNonQuery()
        For baris As Integer = 0 To dg2.Rows.Count - 1
            cId_Bulan = NilaiBulan(Trim(dg2.Rows(baris).Cells(2).Value))
            cmd = New OdbcCommand("Select * from tbdataset where tahun = '" & dg2.Rows(baris).Cells(1).Value & "' and id_bulan= '" & cId_Bulan & "' ", Conn)
            rd = cmd.ExecuteReader
            rd.Read()
            If rd.HasRows Then
                Dim sqledit As String = "Update tbdataset set " & _
                "no_indeks='" & dg2.Rows(baris).Cells(0).Value & "' " & _
               " where  tahun = '" & dg2.Rows(baris).Cells(1).Value & "' and id_bulan= '" & cId_Bulan & "' "
                cmd = New OdbcCommand(sqledit, Conn)
                cmd.ExecuteNonQuery()
            End If

        Next baris
        rd.Close()

        MsgBox("Data Setting berhasil disimpan...", , "Simpan Data Setting...!")

    End Sub


End Class