Imports System.Data.Odbc
Public Class frmPrediksiUser
    Dim NilaiA, NilaiB, nNilaiX As Single
    Dim cDataset_AWal, cDataset_Akhir, cId_Dataset As String
    Sub Tampilkandata()
        dg2.Columns.Clear()
        dg2.Columns.Add("kode", "Tahun")
        dg2.Columns.Add("kode", "Bulan")
        dg2.Columns.Add("kode", "Jml Pendapatan Bulan-1 (X) Ton")
        dg2.Columns.Add("kode", "Prediksi Bulan Ini (Y) Ton")

        Dim sql As String = "Select tahun,id_bulan,nilai_X,prediksi_y " & _
                                 " from tbprediksi  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)
            dg2.Item(3, i).Value = rd(3)
            i += 1
        End While

        rd.Close()
        dg2.ReadOnly = True
        dg2.AllowUserToAddRows = False
        dg2.Columns(0).Width = 70
        dg2.Columns(1).Width = 80
        dg2.Columns(2).Width = 90
        dg2.Columns(3).Width = 90
        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(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        dg2.Columns(2).DefaultCellStyle.Format = "#,##0"
        dg2.Columns(3).DefaultCellStyle.Format = "#,##0"
        If dg2.RowCount > 0 Then
            btnCetak.Enabled = True
        Else
            btnCetak.Enabled = False
        End If
    End Sub

    Sub Kosongkan()
        dg2.Columns.Clear()
        txtNilaiX.Text = ""
        txtHasil.Text = ""
        cboBulan.Text = ""
        lblY.Text = ""
        btnPrediksi.Enabled = False
        txtNilaiX.Enabled = False
        nTahun.Enabled = False
        cboBulan.Enabled = False
    End Sub
    Sub TampilBulan()
        cboBulan.Items.Clear()
        For X = 0 To 11
            cboBulan.Items.Add(cBulan(X))
        Next
    End Sub
    Sub TampilThnPrediksi()
        cmd = New OdbcCommand("select * from tbdataset  order by tahun", Conn)
        rd = cmd.ExecuteReader
        rd.Read()
        If rd.HasRows Then
            cmd = New OdbcCommand("select min(tahun) from tbdataset  order by tahun", Conn)
            rd = cmd.ExecuteReader
            rd.Read()
            nTahun.Minimum = rd.Item(0)
            nTahun.Value = Year(Now)

        End If
    End Sub
    
    Sub CekSettingDataset()
        cmd = New OdbcCommand("Select * from tbsetting_dataset ", Conn)
        rd = cmd.ExecuteReader
        rd.Read()
        If rd.HasRows Then

            NilaiA = rd.Item("nilai_a")
            NilaiB = rd.Item("nilai_b")
            lblY.Text = "Y = " + Microsoft.VisualBasic.Str(NilaiA) + " + " + Microsoft.VisualBasic.Str(NilaiB) + " X" 'persamaan Y
            If (NilaiA = 0) Or (NilaiB = 0) Then
                Call Kosongkan()
                dg2.Columns.Clear()
                MsgBox("Maaf, Persamaan Linier Belum ada...", , "Perhatian...!")

                Exit Sub
            Else
                Call TampilBulan()
                Call TampilThnPrediksi()
                nTahun.Enabled = True
                cboBulan.Enabled = True
                txtNilaiX.Enabled = True
                cboBulan.Focus()
                btnPrediksi.Enabled = True
            End If
        Else
            dg2.Columns.Clear()
            Call Kosongkan()
            MsgBox("Maaf, Setting Dataset Diatur...", , "Perhatian...!")

            Exit Sub
        End If
    End Sub

    Private Sub frmPrediksiUser_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 frmPrediksiUser_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call Kosongkan()
        Call CekSettingDataset()
        Call TampilThnPrediksi()
    End Sub



    Private Sub btnTutup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTutup.Click
        Me.Dispose()
    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 btnPrediksi_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrediksi.Click
        Dim Hasil As Single
        If cboBulan.Text = "" Then
            MsgBox("Maaf, Bulan Prediksi harus dipilih dulu...", , "Perhatian...!")
            cboBulan.Focus()
            Exit Sub
        End If
        If txtNilaiX.Text = "" Then
            MsgBox("Maaf, Nilai X harus diisi dulu...", , "Perhatian...!")
            txtNilaiX.Focus()
            Exit Sub
        End If

        Hasil = NilaiA + NilaiB * nNilaiX
        txtHasil.Text = FormatNumber(Hasil, 0)


        cmd = New OdbcCommand("Select * from tbprediksi 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 tbprediksi (id_dataset,tahun,id_bulan,nilai_x,prediksi_y) values " & _
            "('" & cId_Dataset & "','" & nTahun.Text & "','" & cId_Bulan & "','" & nNilaiX & "','" & Hasil & "')"
            cmd = New OdbcCommand(sqltambah, Conn)

        Else
            Dim sqledit As String = "Update tbprediksi set " & _
            "nilai_x='" & nNilaiX & "', " & _
            "prediksi_y='" & Hasil & "' " & _
            "where  tahun = '" & nTahun.Text & "' and id_bulan ='" & cId_Bulan & "' "
            cmd = New OdbcCommand(sqledit, Conn)

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

    Sub cekHasilPrediksi()
        cmd = New OdbcCommand("Select * from tbprediksi where id_bulan='" & cId_Bulan & "'  and tahun = '" & nTahun.Text & "' ", Conn)
        rd = cmd.ExecuteReader
        rd.Read()
        If rd.HasRows Then
            txtNilaiX.Text = rd.Item("nilai_x")
            txtHasil.Text = rd.Item("prediksi_y")
            btnPrediksi.Focus()
        Else
            txtNilaiX.Text = ""
            txtHasil.Text = ""
            txtNilaiX.Enabled = True
            txtNilaiX.Focus()
        End If
    End Sub

    Private Sub txtNilaiX_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtNilaiX.KeyPress
        If Not ((e.KeyChar >= "0" And e.KeyChar <= "9") Or e.KeyChar = vbBack) 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
        Else
            btnPrediksi.Enabled = True
            Call cekHasilPrediksi()
        End If
    End Sub

    Private Sub nTahun_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles nTahun.ValueChanged
        If cboBulan.Text <> "" Then
            Call cekHasilPrediksi()
        End If
    End Sub

   

    Private Sub btnCetak_Click(sender As System.Object, e As System.EventArgs) Handles btnCetak.Click
        Dim frmCetakHasil As New frmLaporan
        Dim Cetakdata As CrystalDecisions.CrystalReports.Engine.ReportDocument
        Cetakdata = New rptHasilPrediksi
        frmCetakHasil.CRV.ReportSource = Nothing
        frmCetakHasil.CRV.ReportSource = Cetakdata
        frmCetakHasil.CRV.Refresh()
        frmCetakHasil.CRV.RefreshReport()
        frmCetakHasil.Show(Me)
    End Sub

    Private Sub txtNilaiX_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtNilaiX.TextChanged
        With Me.txtNilaiX
            If .Text = "" Then .Text = 0
            .Text = FormatNumber(.Text, 0)
            .SelectionStart = .Text.Length
        End With
        nNilaiX = txtNilaiX.Text
    End Sub

    Private Sub Label7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label7.Click

    End Sub
End Class