Imports System.Data.Odbc
Imports System.Data.OleDb
Public Class frmPrediksi
    Dim A, nJmlData As Byte
    Dim nJmlSebelumnya, nNilaix, nHasil As Integer
    Dim cDataset_AWal, cDataset_Akhir, cTahunAwal, cId_Dataset As String
    Dim NilaiA, NilaiB As Single
    Sub Kosongkan()
        dg2.Columns.Clear()
        txtNilaiA.Text = ""
        txtNilaiB.Text = ""
        txtNilaiX.Text = ""
        txtHasil.Text = ""
        cboBulan.Text = ""
        nTahun.Text = ""
        lblY.Text = ""
        nTahun.Enabled = False
        cboBulan.Enabled = False
        txtNilaiX.Enabled = False
        btnPrediksi.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
            cDataset_AWal = rd.Item("dataset_awal")
            cDataset_Akhir = rd.Item("dataset_akhir")
            cTahunAwal = Val(cDataset_AWal) - 1
        Else
            Call Kosongkan()
            dg1.Columns.Clear()
            MsgBox("Maaf, Setting Dataset  tsb Belum Diatur...", , "Perhatian...!")

            Exit Sub
        End If
    End Sub


   
    Sub TampilkandataSet()
        dg1.Columns.Clear()
        dg1.Columns.Add("kode", "Tahun")
        dg1.Columns.Add("kode", "Bulan")
        dg1.Columns.Add("kode", "Jml Pendapatan")


        Dim sql As String = "Select tahun,id_bulan,jml_pendapatan " & _
                                 " from tbdataset where  " & _
                                 " no_indeks >='" & cTahunAwal & "' and no_indeks <='" & cDataset_Akhir & "' order by tahun,id_bulan"

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

            i += 1
        End While

        rd.Close()
        dg1.ReadOnly = True
        dg1.AllowUserToAddRows = False
        dg1.Columns(0).Width = 60
        dg1.Columns(1).Width = 90
        dg1.Columns(2).Width = 90

        dg1.GridColor = Color.Blue
        dg1.DefaultCellStyle.ForeColor = Color.Blue
        dg1.RowHeadersDefaultCellStyle.BackColor = Color.DeepPink
        dg1.AlternatingRowsDefaultCellStyle.BackColor = Color.Yellow
        dg1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        dg1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        dg1.Columns(0).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        dg1.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

        dg1.Columns(2).DefaultCellStyle.Format = "#,##0"

        If dg1.RowCount > 1 Then
            btnHitungPersamaan.Enabled = True
        Else
            btnHitungPersamaan.Enabled = False
        End If
    End Sub

    Sub ProsesLinierRegresi()
        Dim nBaris, nData As Integer
        Dim TotX, TotY, TotXY, TotXP2 As Single
        dg2.Columns.Clear()
        dg2.Columns.Add("kode", "Tahun")
        dg2.Columns.Add("kode", "Bulan")
        dg2.Columns.Add("kode", "Pendapatan Bulan-1 (X)")
        dg2.Columns.Add("kode", "Pendapatan Bulan Ini (Y)")
        dg2.Columns.Add("kode", "XY")
        dg2.Columns.Add("kode", "X^2")
        Dim brs As Integer = 0
        Dim brsdg1 As Integer = 1

        For brs = 1 To dg1.RowCount - 1
            dg2.Rows.Add(dg1.Item(0, brs).Value)
        Next

        For brs = 0 To dg1.RowCount - 2
            dg2.Item(1, brs).Value = dg1.Item(1, brsdg1).Value
            dg2.Item(2, brs).Value = dg1.Item(2, brsdg1 - 1).Value 'X
            dg2.Item(3, brs).Value = dg1.Item(2, brsdg1).Value 'Y
            dg2.Item(4, brs).Value = dg2.Item(2, brs).Value * dg2.Item(3, brs).Value 'X * Y
            dg2.Item(5, brs).Value = dg2.Item(2, brs).Value ^ 2 'X ^ 2
            TotX = TotX + dg2.Item(2, brs).Value
            TotY = TotY + dg2.Item(3, brs).Value
            TotXY = TotXY + dg2.Item(4, brs).Value
            TotXP2 = TotXP2 + dg2.Item(5, brs).Value
            brsdg1 = brsdg1 + 1
        Next

        nBaris = brs
        rd.Close()
        nData = dg2.RowCount - 1
        dg2.Rows.Add("")
        dg2.Rows.Add("Total")
        dg2.Item(1, nBaris + 1).Value = "n = " + Microsoft.VisualBasic.Str(nData)
        dg2.Item(2, nBaris + 1).Value = TotX
        dg2.Item(3, nBaris + 1).Value = TotY
        dg2.Item(4, nBaris + 1).Value = TotXY
        dg2.Item(5, nBaris + 1).Value = TotXP2
        NilaiA = (TotY * TotXP2 - TotX * TotXY) / (nData * TotXP2 - TotX ^ 2) 'rumus a
        NilaiB = (nData * TotXY - TotX * TotY) / (nData * TotXP2 - TotX ^ 2) 'rumus b
        txtNilaiA.Text = NilaiA
        txtNilaiB.Text = NilaiB
        lblY.Text = "Y = " + txtNilaiA.Text + " + " + txtNilaiB.Text + " X" 'persamaan Y



        For Each col As DataGridViewColumn In dg2.Columns
            col.SortMode = DataGridViewColumnSortMode.Programmatic
        Next
        dg2.ReadOnly = True
        dg2.Columns(0).Width = 75
        dg2.Columns(1).Width = 60
        dg2.Columns(2).Width = 80
        dg2.Columns(3).Width = 85
        dg2.Columns(4).Width = 100
        dg2.Columns(5).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(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        dg2.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        dg2.Columns(4).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        dg2.Columns(5).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        dg2.Columns(2).DefaultCellStyle.Format = "###,##0"
        dg2.Columns(3).DefaultCellStyle.Format = "###,##0"
        dg2.Columns(4).DefaultCellStyle.Format = "###,###"
        dg2.Columns(5).DefaultCellStyle.Format = "###,###"

    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 btnPrediksi_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnHitungPersamaan.Click
        Call ProsesLinierRegresi()

        nTahun.Enabled = True
        cboBulan.Enabled = True
        txtNilaiX.Enabled = True
        cboBulan.Focus()
        btnPrediksi.Enabled = True
        Call TampilBulan()
        Call TampilThnPrediksi()
        nTahun.Text = dg1.Item(0, dg1.RowCount - 1).Value
        cmd = New OdbcCommand("Select * from tbsetting_dataset  ", Conn)
        rd = cmd.ExecuteReader
        rd.Read()
        If rd.HasRows Then
            Dim sqledit As String = "Update tbsetting_dataset set " & _
            "nilai_a='" & NilaiA & "', " & _
            "nilai_b='" & NilaiB & "' "
            cmd = New OdbcCommand(sqledit, Conn)
            cmd.ExecuteNonQuery()
        End If

    End Sub
    Sub cekHasilPrediksi()
        cmd = New OdbcCommand("Select * from tbprediksi where   " & _
                              " tahun = '" & nTahun.Text & "' and  id_bulan ='" & cId_Bulan & "'", Conn)
        rd = cmd.ExecuteReader
        rd.Read()
        If rd.HasRows Then
            txtNilaiX.Text = rd.Item("nilai_x")
            txtHasil.Text = FormatNumber(rd.Item("prediksi_y"), 0)
            btnPrediksi.Focus()
        Else
            txtNilaiX.Text = ""
            txtHasil.Text = ""
            txtNilaiX.Enabled = True
            txtNilaiX.Focus()
        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 btnPrediksi_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrediksi.Click
        Dim cKet As String
        Dim Hasil As Single
        cKet = ""
        If nTahun.Text = "" Then
            MsgBox("Maaf, Tahun harus diisi dulu...", , "Perhatian...!")
            nTahun.Focus()
            Exit Sub
        End If
        If cboBulan.Text = "" Then
            MsgBox("Maaf, Bulan harus diisi 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)
        'isi Tabel Prediksi
        Cek_IdDataset()
        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   id_bulan ='" & cId_Bulan & "' and tahun = '" & nTahun.Text & "'"
            cmd = New OdbcCommand(sqledit, Conn)

        End If
        cmd.ExecuteNonQuery()

    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 btnHasil_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnHasil.Click
        frmLapHasilPrediksi.Show(Me)
    End Sub

    Private Sub cboTahun_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
        If cboBulan.Text <> "" Then
            cekHasilPrediksi()
        End If
    End Sub

    Private Sub frmPrediksi_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Call CekSettingDataset()
        Call TampilkandataSet()
        Call Kosongkan()
    End Sub

    Private Sub cboBulan_SelectedIndexChanged(sender As System.Object, 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(sender As System.Object, e As System.EventArgs) Handles nTahun.ValueChanged
        If cboBulan.Text <> "" Then
            Call cekHasilPrediksi()
        End If
    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 dg2_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dg2.CellContentClick

    End Sub

    Private Sub dg1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dg1.CellContentClick

    End Sub
End Class