Option Strict Off
Imports System.Data.Odbc
Imports CrystalDecisions.CrystalReports.Engine

Public Class FrmLapImportir
    Dim cNama, cNip, cJabatan As String
    Dim Awal As Boolean = False
    Dim jmlKriteria As Integer = 0
    Dim rd2 As OdbcDataReader
    Dim strkode_Kriteria() As String
    Private Sub FrmLapPemohon_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Call TampilPeriode()
        btnCetak.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 TampilGrid()
        da = New OdbcDataAdapter("Select no_importir,nama_importir,asal_negara from tbimportir where " & _
                                 " periode = '" & cboPeriode.Text & "' order by no_importir", Conn)
        ds = New DataSet
        ds.Clear()
        da.Fill(ds, "tbimportir")
        DGV.DataSource = (ds.Tables("tbimportir"))
        DGV.ReadOnly = True
        DGV.AllowUserToAddRows = False
        DGV.Columns(0).Width = 90
        DGV.Columns(1).Width = 170
        DGV.Columns(2).Width = 160


        DGV.GridColor = Color.Blue
        DGV.DefaultCellStyle.ForeColor = Color.Blue
        DGV.RowHeadersDefaultCellStyle.BackColor = Color.DeepPink
        DGV.AlternatingRowsDefaultCellStyle.BackColor = Color.Orange
        DGV.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        DGV.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        DGV.Columns(0).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        DGV.Columns(0).HeaderText = "No. Importir"
        DGV.Columns(1).HeaderText = "Nama Importir"
        DGV.Columns(2).HeaderText = "Asal Negara"

    End Sub
    Sub TampilNilaiKriteria()
        DGV.ReadOnly = True
        DGV.AllowUserToAddRows = False
        DGV.Columns.Clear()
        DGV.Rows.Clear()
        DGV.Columns.Add("kode", "No Importir")
        DGV.Columns.Add("kode", "Nama Importir")
        DGV.Columns.Add("kode", "Asal Negara")
        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
            DGV.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
            DGV.Rows.Add()
            DGV.Item(0, i).Value = rd(0)
            DGV.Item(1, i).Value = rd(1)
            DGV.Item(2, i).Value = rd(2)
            i += 1
        End While
        rd.Close()

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


        DGV.Columns(0).Width = 120
        DGV.Columns(1).Width = 170
        DGV.Columns(2).Width = 150

        DGV.GridColor = Color.Blue
        DGV.DefaultCellStyle.ForeColor = Color.Blue
        DGV.RowHeadersDefaultCellStyle.BackColor = Color.DeepPink
        DGV.AlternatingRowsDefaultCellStyle.BackColor = Color.Yellow
        DGV.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        DGV.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        DGV.Columns(0).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        For k As Integer = 3 To jmlKriteria + 2
            DGV.Columns(k).Width = 70
            DGV.Columns(k).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        Next

    End Sub
    Sub TampilHasilPenilaian()
        Dim lstkode_Kriteria As New List(Of String)
        Dim cNilai As String = ""
        DGV.ReadOnly = True
        DGV.AllowUserToAddRows = False
        DGV.Columns.Clear()
        DGV.Rows.Clear()
        DGV.Columns.Add("kode", "No.Importir")
        DGV.Columns.Add("kode", "Nama Importir")
        DGV.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
            DGV.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 a.no_importir "
        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)
            DGV.Item(2, i).Value = rd(2)
            i += 1
        End While
        rd.Close()

        sql = "select * from tbnilaialternatif where periode = '" & cboPeriode.Text & "' order by no_importir"
        cmd = New OdbcCommand(sql, Conn)
        rd = cmd.ExecuteReader
        If rd.HasRows Then
            rd.Close()
            For j As Integer = 0 To DGV.RowCount - 1
                sql = "select kode_sub from tbnilaialternatif where no_importir='" & DGV.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
                    DGV.Item(i + 3, j).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.Columns(0).Width = 90
        DGV.Columns(1).Width = 170
        DGV.Columns(2).Width = 150
        DGV.GridColor = Color.Blue
        DGV.DefaultCellStyle.ForeColor = Color.Blue
        DGV.RowHeadersDefaultCellStyle.BackColor = Color.DeepPink
        DGV.AlternatingRowsDefaultCellStyle.BackColor = Color.Yellow
        DGV.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        DGV.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        DGV.Columns(0).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        For k As Integer = 3 To jmlKriteria + 2
            DGV.Columns(k).Width = 70
            DGV.Columns(k).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        Next

    End Sub

    Sub CekPenilai()
        cmd = New OdbcCommand("select * from tbpejabat where status='Aktif'", Conn)
        rd = cmd.ExecuteReader
        rd.Read()
        If rd.HasRows = True Then
            cNama = rd.Item(1)
            cJabatan = rd.Item(2)
            cNip = "NIP. " & rd.Item(0)
        Else
            cNama = ""
            cNip = ""
        End If
    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 nudPeriode_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs)
        Awal = True
    End Sub

    Sub CekData()

        cmd = New OdbcCommand("select * from tbnilaialternatif where  periode = '" & cboPeriode.Text & "' ", Conn)
        rd = cmd.ExecuteReader
        rd.Read()
        If rd.HasRows = True Then
            Call TampilHasilPenilaian()
            btnCetak.Enabled = True
        Else
            If Awal = True Then
                DGV.Columns.Clear()
                MsgBox("Maaf, Penilaian Importir  pada Periode tsb, belum ada", , "Perhatian...!")
                btnCetak.Enabled = False
                Exit Sub
            End If
        End If
    End Sub
    Private Sub btnCetak_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCetak.Click
        Dim cetakdata As CrystalDecisions.CrystalReports.Engine.ReportDocument
        Dim frmPenilaian As New FrmLaporan
        cetakdata = New rptPenilaian
        Call CekPenilai()
        frmPenilaian.CRV.ReportSource = Nothing
        frmPenilaian.CRV.SelectionFormula = "{tbnilaialternatif1.periode}='" & cboPeriode.Text & "'"
        cetakdata.DataDefinition.FormulaFields(0).Text = "'" & cNama & "'"
        cetakdata.DataDefinition.FormulaFields(1).Text = "'" & cNip & "'"
        cetakdata.DataDefinition.FormulaFields(2).Text = "'" & cJabatan & "'"

        frmPenilaian.CRV.ReportSource = cetakdata
        ' frmPenilaian.CRV.DisplayRectangle = False
        frmPenilaian.CRV.Refresh()
        frmPenilaian.CRV.RefreshReport()
        frmPenilaian.Show(Me)
    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 rbKategori_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbKategori.CheckedChanged
        Call TampilHasilPenilaian()
    End Sub

    Private Sub rbNilai_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbNilai.CheckedChanged
        Call TampilNilaiKriteria()
    End Sub

    Private Sub cboPeriode_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboPeriode.SelectedIndexChanged
        Call CekData()
        rbKategori.Checked = True
    End Sub
End Class