Imports System.Data.SqlClient
Public Class Data_Instansi
    Dim Conn As SqlConnection
    Dim Da As SqlDataAdapter
    Dim Cmd As SqlCommand
    Dim Rd As SqlDataReader
    Dim Ds As DataSet
    Dim MyDB As String

    ' Fungsi untuk melakukan koneksi ke database
    Sub koneksi()
        MyDB = "Data Source=DESKTOP-JIL4I4Q;initial catalog=ptbbm;integrated security=true"
        Conn = New SqlConnection(MyDB)
        If Conn.State = ConnectionState.Closed Then
            Conn.Open()
        End If
    End Sub

    ' Fungsi untuk mengosongkan data pada form
    Sub Cleardata()
        DateTimePicker1.Value = Now
        TextBox3.Text = ""
        TextBox4.Text = ""
        TextBox5.Text = ""
        ComboBox1.Text = ""
        TextBox6.Text = ""
        ComboBox3.Text = ""
        ComboBox2.Text = ""
    End Sub

    ' Fungsi untuk kondisi awal saat form diload
    Sub kondisiAwal()
        Call koneksi()
        Da = New SqlDataAdapter("SELECT * FROM datapelanggan", Conn)
        Ds = New DataSet
        Ds.Clear()
        DataGridView1.Columns.Clear()
        Da.Fill(Ds, "datapelanggan")
        DataGridView1.DataSource = (Ds.Tables("datapelanggan"))

        ' Menambahkan kolom "Delete" ke DataGridView
        Dim col As New DataGridViewButtonColumn
        col.DataPropertyName = "PropertyName"
        col.HeaderText = "Delete"
        col.Name = "delete"
        DataGridView1.Columns.Add(col)
        For i = 0 To DataGridView1.RowCount - 1
            DataGridView1.Rows(i).Cells("delete").Value = "Delete"
        Next

        ' Menambahkan kolom "Edit" ke DataGridView
        Dim coledit As New DataGridViewButtonColumn
        coledit.DataPropertyName = "PropertyName"
        coledit.HeaderText = "Edit"
        coledit.Name = "edit"
        DataGridView1.Columns.Add(coledit)
        For i = 0 To DataGridView1.RowCount - 1
            DataGridView1.Rows(i).Cells("edit").Value = "Edit"
        Next
    End Sub

    ' Fungsi untuk menampilkan nama jenis instansi dari database ke ComboBox
    Sub MunculNamaJenisInstansi()
        Call koneksi()
        ComboBox1.Items.Clear()
        Try
            Dim Ds As New DataSet
            Dim CB As New SqlDataAdapter("SELECT * FROM jenisinstansi", Conn)
            CB.Fill(Ds, "jenisinstansi")
            Me.ComboBox1.DataSource = Ds.Tables("jenisinstansi")
            Me.ComboBox1.ValueMember = "kode_jenis_instansi"
            Me.ComboBox1.DisplayMember = "nama_jenisinstansi"
        Catch ex As Exception
            ' Tangani eksepsi/kesalahan sesuai kebutuhan Anda
        End Try
    End Sub

    ' Fungsi untuk menampilkan nama provinsi dari database ke ComboBox
    Sub MunculNamaProvinsi()
        Call koneksi()
        ComboBox3.Items.Clear()
        Try
            Dim Ds As New DataSet
            Dim CB As New SqlDataAdapter("SELECT * FROM provinsi", Conn)
            CB.Fill(Ds, "provinsi")
            Me.ComboBox3.DataSource = Ds.Tables("provinsi")
            Me.ComboBox3.ValueMember = "kode_provinsi"
            Me.ComboBox3.DisplayMember = "nama_provinsi"
        Catch ex As Exception
            ' Tangani eksepsi/kesalahan sesuai kebutuhan Anda
        End Try
    End Sub

    Private Sub Data_Instansi_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' Memanggil fungsi kondisiAwal saat form diload
        Call kondisiAwal()
        ' Memanggil fungsi MunculNamaJenisInstansi untuk menampilkan nama jenis instansi di ComboBox
        Call MunculNamaJenisInstansi()
        ' Memanggil fungsi MunculNamaProvinsi untuk menampilkan nama provinsi di ComboBox
        Call MunculNamaProvinsi()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            Call koneksi()
            If TextBox4.Text.Trim() <> "" Then
                ' Menjalankan query untuk insert data ke database
                Dim InputData As String = "INSERT INTO datapelanggan (tanggal_daftar, no_pelanggan, nama_instansi, nama_instansi_disingkat, kode_jenis_instansi, alamat_instansi, kode_kota_kabupaten) VALUES ('" & DateTimePicker1.Value & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" & TextBox5.Text & "','" & ComboBox1.SelectedValue & "','" & TextBox6.Text & "','" & ComboBox2.SelectedValue & "')"
                Cmd = New SqlCommand(InputData, Conn)
                Cmd.ExecuteNonQuery()
                MsgBox("Data Berhasil Diinput")
                ' Memanggil fungsi kondisiAwal untuk refresh DataGridView
                Call kondisiAwal()
                ' Memanggil fungsi Cleardata untuk mengosongkan data pada form
                Call Cleardata()
            Else
                MsgBox("Textbox tidak boleh kosong. Silakan masukkan data terlebih dahulu.")
            End If
        Catch ex As Exception
            MsgBox("Terjadi kesalahan saat memasukkan data: " & ex.Message)
        Finally
            Conn.Close()
        End Try
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Try
            Call koneksi()
            If TextBox4.Text.Trim() <> "" Then
                ' Menjalankan query untuk update data di database
                Dim EditData As String = "UPDATE datapelanggan SET no_pelanggan='" & TextBox3.Text & "', nama_instansi='" & TextBox4.Text & "', nama_instansi_disingkat='" & TextBox5.Text & "', kode_jenis_instansi='" & ComboBox1.SelectedValue & "', alamat_instansi='" & TextBox6.Text & "', tanggal_daftar='" & DateTimePicker1.Value & "' WHERE no_pelanggan='" & TextBox3.Text & "'"
                Cmd = New SqlCommand(EditData, Conn)
                Cmd.ExecuteNonQuery()
                MsgBox("Data Berhasil DiEdit")
                ' Memanggil fungsi kondisiAwal untuk refresh DataGridView
                Call kondisiAwal()
            Else
                MsgBox("Isi terlebih dahulu. Agar bisa merubah data.")
            End If
        Catch ex As Exception
            MsgBox("Terjadi kesalahan saat mengedit data: " & ex.Message)
        End Try
    End Sub

    Private Sub ComboBox3_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox3.SelectedIndexChanged
        Try
            Da = New SqlDataAdapter("SELECT * FROM Kotakabupaten WHERE kode_provinsi=" & ComboBox3.SelectedValue, Conn)
            Ds = New DataSet
            Ds.Clear()
            Da.Fill(Ds, "Kotakabupaten")

            Me.ComboBox2.DataSource = Ds.Tables("Kotakabupaten")
            Me.ComboBox2.ValueMember = "kode_kota_kabupaten"
            Me.ComboBox2.DisplayMember = "nama_kota_kabupaten"
        Catch ex As Exception
        End Try
    End Sub

    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
        Select Case e.ColumnIndex
            Case 4
                ' Jika tombol "Delete" di-klik, tampilkan dialog konfirmasi dan hapus data jika user mengonfirmasi
                If e.RowIndex >= 0 AndAlso e.RowIndex < DataGridView1.RowCount Then
                    If e.ColumnIndex = DataGridView1.Columns("delete").Index Then
                        Dim kodePelanggan As String = DataGridView1.Rows(e.RowIndex).Cells("no_pelanggan").Value.ToString()
                        If MessageBox.Show("Apakah Anda yakin ingin menghapus data ini?", "Konfirmasi Hapus", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.Yes Then
                            Try
                                Call koneksi()
                                Dim hapusData As String = "DELETE FROM datapelanggan WHERE no_pelanggan='" & kodePelanggan & "'"
                                Cmd = New SqlCommand(hapusData, Conn)
                                Cmd.ExecuteNonQuery()
                                MsgBox("Data Berhasil Dihapus")
                                Call kondisiAwal()
                            Catch ex As Exception
                                MsgBox("Error menghapus data: " & ex.Message)
                            Finally
                                Conn.Close()
                            End Try
                        End If
                    End If
                End If
            Case 3
                ' Jika tombol "Edit" di-klik, tampilkan data dari row yang di-klik ke TextBox dan ComboBox
                If e.RowIndex >= 0 AndAlso e.RowIndex < DataGridView1.RowCount Then
                    If e.ColumnIndex = DataGridView1.Columns("edit").Index Then
                        TextBox3.Text = DataGridView1.Rows(e.RowIndex).Cells("no_pelanggan").Value.ToString()
                        TextBox4.Text = DataGridView1.Rows(e.RowIndex).Cells("nama_instansi").Value.ToString()
                        TextBox5.Text = DataGridView1.Rows(e.RowIndex).Cells("nama_instansi_disingkat").Value.ToString()
                        ComboBox1.SelectedValue = DataGridView1.Rows(e.RowIndex).Cells("kode_jenis_instansi").Value.ToString()
                        TextBox6.Text = DataGridView1.Rows(e.RowIndex).Cells("alamat_instansi").Value.ToString()
                        DateTimePicker1.Value = CDate(DataGridView1.Rows(e.RowIndex).Cells("tanggal_daftar").Value)
                    End If
                End If
        End Select
    End Sub
End Class