Option Strict Off
Imports System.Data.Odbc
Public Class FrmPenilaian
    Dim rd2 As OdbcDataReader
    Dim cKode_Kls, cBltNilai, cBobot As String
    Dim baris, jmlKriteria As Byte
    Dim strkode_Kriteria() As String
    Sub databaru()
        txtNama.Text = ""
        txtAsal.Text = ""
        txtBarang.Text = ""
        txtNama.Focus()
    End Sub
    Sub kosongkan()
        Call tidaksiapisi()
        txtNo_Importir.Text = ""
        txtNama.Text = ""
        txtAsal.Text = ""
        txtBarang.Text = ""
        txtNo_Importir.Enabled = True
        txtNo_Importir.Focus()
    End Sub
    Sub siapisi()
        txtNama.Enabled = True
        txtBarang.Enabled = True
        txtAsal.Enabled = True
        btnHapus.Enabled = False
        btnSimpan.Enabled = True
    End Sub
    Sub tidaksiapisi()
        txtNama.Enabled = False
        txtAsal.Enabled = False
        txtBarang.Enabled = False
        btnSimpan.Enabled = False
        btnHapus.Enabled = False
    End Sub


    Sub TampilPeriode()
        cboPeriode.Items.Clear()
        cmd = New OdbcCommand("select distinct Periode from tbnilaialternatif order by periode desc", Conn)
        rd = cmd.ExecuteReader
        While rd.Read
            cboPeriode.Items.Add(rd.Item(0))
        End While
    End Sub
    Sub CekData()
        cmd = New OdbcCommand("select * from tbimportir where no_importir='" & txtNo_Importir.Text & "' ", Conn)
        rd = cmd.ExecuteReader
        rd.Read()
        If rd.HasRows = True Then
            txtNama.Text = rd.Item("Nama_Importir")
            txtAsal.Text = rd.Item("Asal_Negara")
            Call tidaksiapisi()
            btnHapus.Enabled = False
            txtNo_Importir.Enabled = False
            Call TampilPenilaian()
            btnSimpan.Enabled = True
            txtBarang.Enabled = True
            cmd = New OdbcCommand("Select nama_barang from tbhasil where periode='" & cboPeriode.Text & "' and no_importir='" & txtNo_Importir.Text & "'", Conn)
            rd = cmd.ExecuteReader
            rd.Read()
            If rd.HasRows Then
                txtBarang.Text = rd.Item("Nama_barang")
            End If
        Else
            MsgBox("Maaf, No.Importir tsb Belum ada", , "Perhatian...!")
            txtNo_Importir.Focus()
        End If
    End Sub

    Private Sub FrmPenilaian_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call databaru()
        Call tidaksiapisi()
        Call kosongkan()
        Call TampilPeriode()
    End Sub

    Private Sub cboKelas_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs)
        e.Handled = True
    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 txtNo_Importir_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtNo_Importir.KeyPress
        If e.KeyChar = Chr(13) Then
            Call CekData()
        End If
    End Sub


    Private Sub btnCari_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCari.Click
        Dim tabel As New frmBantuan
        cKodeBtn = ""
        cNamaBtn = ""
        sqlCari = "Select no_importir ,nama_importir  from tbimportir where nama_importir like '%" & txtNama.Text & "%' order by no_importir"
        sda = "tbimportir"
        tabel.ShowDialog()
        txtNama.Text = cNamaBtn
        txtNo_Importir.Text = cKodeBtn
        Call CekData()
    End Sub

    Private Sub btnBaru_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBaru.Click
        Call kosongkan()
        DGV.Columns.Clear()
    End Sub

    Private Sub btnSimpan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSimpan.Click
        Call SimpanPenilaian()
        btnHapus.Enabled = True
    End Sub

    Private Sub DGV2_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DGV2.CellDoubleClick
        txtNo_Importir.Text = DGV2.SelectedRows.Item(0).Cells(0).Value()
        Call CekData()
        If txtNo_Importir.Text <> "" Then btnHapus.Enabled = True
    End Sub

    Private Sub btnHapus_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnHapus.Click
        cmd = New OdbcCommand("select * from tbhasil where no_importir='" & txtNo_Importir.Text & "'" & _
                            "and periode = '" & cboPeriode.Text & "'", Conn)
        rd = cmd.ExecuteReader
        rd.Read()
        If rd.HasRows Then

            MsgBox("Maaf, Penilaian untuk Importir tsb, tidak dapat dihapus", , "Perhatian...!")
        Else

            If MessageBox.Show("Yakin akan dihapus..?", "Hapus Data", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.Yes Then
                cmd = New OdbcCommand("Delete  from tbnilaialternatif where no_importir='" & txtNo_Importir.Text & "' " & _
                                      " and periode = '" & cboPeriode.Text & "'", Conn)
                cmd.ExecuteNonQuery()

            End If
        End If

        Call kosongkan()
        Call TampilHasilPenilaian()
        DGV.Columns.Clear()
        btnHapus.Enabled = False
    End Sub

    Sub TampilHasilPenilaian()

        Dim lstkode_Kriteria As New List(Of String)
        Dim cNilai As String = ""
        DGV2.ReadOnly = True
        DGV2.AllowUserToAddRows = False
        DGV2.Columns.Clear()
        DGV2.Rows.Clear()
        DGV2.Columns.Add("kode", "No.Importir")
        DGV2.Columns.Add("kode", "Nama Importir")
        DGV2.Columns.Add("kode", "Asal_Negara")
        Dim sql As String = "Select nama_kriteria,kode_kriteria from tbkriteria  order by kode_kriteria"
        Dim cmd As New OdbcCommand(sql, Conn)
        Dim rd As OdbcDataReader = cmd.ExecuteReader
        While rd.Read
            DGV2.Columns.Add(rd(0), rd(0))
            lstkode_Kriteria.Add(rd(1))
        End While
        strkode_Kriteria = lstkode_Kriteria.ToArray
        jmlKriteria = lstkode_Kriteria.Count
        rd.Close()

        sql = "select a.no_importir,b.nama_importir,b.Asal_Negara from tbnilaialternatif a, tbimportir b " & _
            " where a.no_importir=b.no_importir and a. periode = '" & cboPeriode.Text & "' " & _
            " group by no_importir order by no_importir "
        cmd = New OdbcCommand(sql, Conn)
        rd = cmd.ExecuteReader
        Dim i As Integer = 0

        While rd.Read
            DGV2.Rows.Add()
            DGV2.Item(0, i).Value = rd(0)
            DGV2.Item(1, i).Value = rd(1)
            DGV2.Item(2, i).Value = rd(2)
            i += 1
        End While
        rd.Close()

        sql = "select * from tbnilaialternatif where periode = '" & cboPeriode.Text & "' "
        cmd = New OdbcCommand(sql, Conn)
        rd = cmd.ExecuteReader
        If rd.HasRows Then
            rd.Close()
            For j As Integer = 0 To DGV2.RowCount - 1
                sql = "select kode_sub from tbnilaialternatif where no_importir='" & DGV2.Item(0, j).Value & "' and periode = '" & cboPeriode.Text & "' "
                cmd = New OdbcCommand(sql, Conn)
                rd = cmd.ExecuteReader
                i = 0
                While rd.Read
                    cmd = New OdbcCommand("select sub_kriteria from tbsub_kriteria where kode_sub='" & rd(0) & "' ", Conn)
                    rd2 = cmd.ExecuteReader
                    rd2.Read()
                    If rd2.HasRows = True Then
                        cNilai = rd2.Item(0)
                    End If
                    DGV2.Item(i + 3, j).Value = cNilai
                    i += 1
                End While
                rd.Close()
            Next
        Else
            rd.Close()
        End If

        For Each col As DataGridViewColumn In DGV2.Columns
            col.SortMode = DataGridViewColumnSortMode.Programmatic
        Next

        DGV2.Columns(0).Width = 90
        DGV2.Columns(1).Width = 170
        DGV2.Columns(2).Width = 150
        DGV2.GridColor = Color.Blue
        DGV2.DefaultCellStyle.ForeColor = Color.Blue
        DGV2.RowHeadersDefaultCellStyle.BackColor = Color.DeepPink
        DGV2.AlternatingRowsDefaultCellStyle.BackColor = Color.Yellow
        DGV2.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        DGV2.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        DGV2.Columns(0).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        For k As Integer = 3 To jmlKriteria + 2
            DGV2.Columns(k).Width = 70
            DGV2.Columns(k).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        Next
    End Sub
    Sub TampilPenilaian()

        DGV.Columns.Clear()
        DGV.Columns.Add("kode", "Kode Kriteria")
        DGV.Columns.Add("kode", "Nama Kriteria")
        DGV.Columns.Add("kode", "Penilaian")
        Dim lstkode_Kriteria As New List(Of String)
        Dim cNilai As String = ""
        Dim sql As String = "Select nama_kriteria,kode_kriteria from tbkriteria  order by kode_kriteria"
        Dim cmd As New OdbcCommand(sql, Conn)
        Dim rd As OdbcDataReader = cmd.ExecuteReader
        While rd.Read
            lstkode_Kriteria.Add(rd(1))
        End While
        strkode_Kriteria = lstkode_Kriteria.ToArray
        rd.Close()

        sql = "select Kode_kriteria,nama_kriteria from tbkriteria  order by kode_kriteria "
        cmd = New OdbcCommand(sql, Conn)
        rd = cmd.ExecuteReader
        Dim i As Integer = 0
        While rd.Read
            DGV.Rows.Add()
            DGV.Item(0, i).Value = rd(0)
            DGV.Item(1, i).Value = rd(1)

            i += 1
        End While
        rd.Close()

        sql = "select * from tbnilaialternatif where no_importir='" & txtNo_Importir.Text & "' and periode = '" & cboPeriode.Text & "' "
        cmd = New OdbcCommand(sql, Conn)
        rd = cmd.ExecuteReader
        If rd.HasRows Then
            rd.Close()
            For j As Integer = 0 To DGV.RowCount - 2
                sql = "select `kode_sub` from tbnilaialternatif where no_importir='" & txtNo_Importir.Text & "' and periode = '" & cboPeriode.Text & "' "
                cmd = New OdbcCommand(sql, Conn)
                rd = cmd.ExecuteReader
                i = 0
                While rd.Read
                    cmd = New OdbcCommand("select sub_kriteria from tbsub_kriteria where kode_sub='" & rd(0) & "' ", Conn)
                    rd2 = cmd.ExecuteReader
                    rd2.Read()
                    If rd2.HasRows = True Then
                        cNilai = rd2.Item(0)
                    End If
                    DGV.Item(2, i).Value = cNilai
                    i += 1
                End While
                rd.Close()
            Next
        Else
            rd.Close()
        End If

        For Each col As DataGridViewColumn In DGV.Columns
            col.SortMode = DataGridViewColumnSortMode.Programmatic
        Next
        DGV.AllowUserToAddRows = False
        DGV.Columns(0).Width = 60
        DGV.Columns(1).Width = 190
        DGV.Columns(2).Width = 90
        DGV.Columns(0).ReadOnly = True
        DGV.Columns(1).ReadOnly = True
        DGV.GridColor = Color.Blue
        DGV.DefaultCellStyle.ForeColor = Color.Blue
        DGV.RowHeadersDefaultCellStyle.BackColor = Color.DeepPink
        DGV.AlternatingRowsDefaultCellStyle.BackColor = Color.Yellow
        DGV.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        DGV.Columns(0).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        DGV.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        DGV.Focus()
        DGV.CurrentCell = DGV.Rows(0).Cells(2)
    End Sub

    Private Sub DGV_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DGV.CellClick
        ' On Error Resume Next
        If e.ColumnIndex > 1 Then
            If e.RowIndex < DGV.RowCount Then
                Dim cmb As New DataGridViewComboBoxCell
                cmb.Items.Clear()
                Dim sql As String = "select `sub_kriteria` from `tbsub_kriteria` where `kode_kriteria`='" & strkode_Kriteria(e.RowIndex) & "' order by `kode_kriteria` asc"
                Dim cmd As New OdbcCommand(sql, Conn)
                Dim rd As OdbcDataReader = cmd.ExecuteReader
                While rd.Read
                    cmb.Items.Add(rd(0))
                End While
                rd.Close()
                DGV.Item(e.ColumnIndex, e.RowIndex) = cmb
            End If
        End If
    End Sub
    Sub SimpanPenilaian()
        Dim terisi As Boolean = True
        Dim nNilai As Single
        Dim cKode_Sub, cKet, cKode_max, cBulat As String
        cKode_Sub = ""
        cKet = ""
        cKode_max = ""
        cBulat = ""
        For j As Integer = 0 To DGV.RowCount - 1
            If DGV.Item(2, j).Value = "" Then
                terisi = False
            End If
        Next

        If terisi Then
            Dim sql As String = "delete from tbnilaialternatif where no_importir='" & txtNo_Importir.Text & "' and periode = '" & cboPeriode.Text & "' "
            Dim cmd As New OdbcCommand(sql, Conn)
            cmd.ExecuteNonQuery()


            For j As Integer = 0 To DGV.RowCount - 1
                cmd = New OdbcCommand("select kode_sub from tbsub_kriteria where sub_kriteria='" & DGV.Item(2, j).Value & "' " & _
                         "and kode_kriteria =  '" & DGV.Rows(j).Cells(0).Value & "'", Conn)
                rd2 = cmd.ExecuteReader
                rd2.Read()
                If rd2.HasRows = True Then
                    cKode_Sub = rd2.Item(0)
                    ' cBobot = rd2.Item(1)
                End If
                cmd = New OdbcCommand("select hasil from tbprioritas where kode_sub='" & cKode_Sub & "' " & _
                           "and kode_kriteria =  '" & DGV.Rows(j).Cells(0).Value & "'", Conn)
                rd2 = cmd.ExecuteReader
                rd2.Read()
                If rd2.HasRows = True Then
                    nNilai = rd2.Item(0)
                    cBulat = Replace(nNilai, ",", ".")
                End If
                rd2.Close()
                'Cari Nilai Kriteria Maximal
                cmd = New OdbcCommand("select nilai_max from tbkriteria where  kode_kriteria =  '" & DGV.Rows(j).Cells(0).Value & "'", Conn)
                rd2 = cmd.ExecuteReader
                rd2.Read()
                If rd2.HasRows = True Then
                    cKode_max = rd2.Item(0)
                End If
                'Menentukan Layak atau tidak
                If cKode_Sub = cKode_max Then
                    cKet = "Tidak Layak"
                Else
                    cKet = "Layak"
                End If
                rd2.Close()
                Dim sqlsimpan As String = "Insert into tbnilaialternatif (periode,no_importir,kode_kriteria,Kode_Sub,nilai,ket) values " & _
            "('" & cboPeriode.Text & "','" & txtNo_Importir.Text & "','" & DGV.Rows(j).Cells(0).Value & "','" & cKode_Sub & "','" & cBulat & "','" & cKet & "')"
                cmd = New OdbcCommand(sqlsimpan, Conn)
                cmd.ExecuteNonQuery()
            Next
            Call simpanBarang()
            Call TampilHasilPenilaian()
            '  MsgBox("Data Tersimpan")
            btnSimpan.Enabled = False
        Else
            MsgBox("Data belum terisi semua!")
            btnSimpan.Enabled = True
        End If
    End Sub
    Sub simpanBarang()
        cmd = New OdbcCommand("Select * from tbhasil where periode='" & cboPeriode.Text & "' and no_importir='" & txtNo_Importir.Text & "'", Conn)
        rd = cmd.ExecuteReader
        rd.Read()
        If Not rd.HasRows Then
            Dim sqltambah As String = "Insert into tbhasil (periode,no_importir,nama_barang) values " & _
            "('" & cboPeriode.Text & "','" & txtNo_Importir.Text & "','" & txtBarang.Text & "')"
            cmd = New OdbcCommand(sqltambah, Conn)

        Else
            Dim sqledit As String = "Update tbhasil set " & _
            "nama_barang='" & txtBarang.Text & "' " & _
            " where periode='" & cboPeriode.Text & "' and no_importir='" & txtNo_Importir.Text & "'"
            cmd = New OdbcCommand(sqledit, Conn)

        End If
        cmd.ExecuteNonQuery()
    End Sub
    Private Sub DGV_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DGV.CellContentClick

    End Sub

    Private Sub cboPeriode_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboPeriode.SelectedIndexChanged
        Call TampilHasilPenilaian()
    End Sub

    Private Sub txtNo_Importir_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtNo_Importir.TextChanged

    End Sub
End Class