Imports System.Data.OleDb



Public Class MasterSiswa



    Sub NomorOtomatis()

        CMD = New OleDbCommand("select id_siswa from tblsiswa order by id_siswa desc", Conn)

        DR = CMD.ExecuteReader

        DR.Read()

        If Not DR.HasRows Then

            txtIDSiswa.Text = "00001"

        Else

            txtIDSiswa.Text = Format(Microsoft.VisualBasic.Right(DR.Item("id_siswa"), 5) + 1, "00000")

        End If



    End Sub



    Sub Kosongkan()

        txtIDSiswa.Enabled = False

        Call NomorOtomatis()

        txtNIS.Clear()

        txtNISN.Clear()

        txtNama.Clear()

        TextBox6.Clear()

        cmbPendidikan.Text = ""

        cmbTahunAjaran.Text = ""

        cmbJurusan.Text = ""

        cmbKelas.Text = ""

        cmbSemester.Text = ""

        Call Tampilangkatan()

        Call TampilJurusan()

        Call Tampilkelas()

        Call TampilGrid()

        txtNIS.Focus()

    End Sub



    Sub DataBaru()

        txtNIS.Clear()

        txtNISN.Clear()

        txtNama.Clear()

        TextBox6.Clear()

        cmbPendidikan.Text = ""

        cmbTahunAjaran.Text = ""

        cmbJurusan.Text = ""

        cmbKelas.Text = ""

        cmbSemester.Text = ""

        Call Tampilangkatan()

        Call TampilJurusan()

        Call Tampilkelas()

        Call TampilGrid()

        txtNIS.Focus()

    End Sub



    Sub Ketemu()

        On Error Resume Next

        txtIDSiswa.Enabled = False

        txtNIS.Text = DR.Item("NIS")

        txtNISN.Text = DR.Item("NISN")

        txtNama.Text = DR.Item("nama_siswa")

        cmbPendidikan.Text = DR.Item("id_biaya") ': SendKeys.Send("{tab}")

        cmbTahunAjaran.Text = DR.Item("Tahun_ajaran")

        cmbJurusan.Text = DR.Item("Jurusan")

        cmbKelas.Text = DR.Item("kelas")

        cmbSemester.Text = DR.Item("semester")

        txtNIS.Focus()

    End Sub



    Sub TampilGrid()

        DA = New OleDbDataAdapter("select * from tblsiswa", Conn)

        DS = New DataSet

        DA.Fill(DS)

        DGV.DataSource = DS.Tables(0)

        DGV.ReadOnly = True

    End Sub



    Sub TampilPendidikan()

        CMD = New OleDbCommand("select * from tblLEVELPENDIDIKAN", Conn)

        DR = CMD.ExecuteReader

        cmbPendidikan.Items.Clear()

        Do While DR.Read

            cmbPendidikan.Items.Add(DR.Item(0) & Space(2) & DR(1))

        Loop

    End Sub



    Sub Tampilangkatan()

        CMD = New OleDbCommand("select DISTINCT TAHUN_AJARAN from tblSISWA", Conn)

        DR = CMD.ExecuteReader

        cmbTahunAjaran.Items.Clear()

        Do While DR.Read

            cmbTahunAjaran.Items.Add(DR.Item(0))

        Loop

    End Sub



    Sub TampilJurusan()

        CMD = New OleDbCommand("select DISTINCT JURUSAN  from tblSISWA", Conn)

        DR = CMD.ExecuteReader

        cmbJurusan.Items.Clear()

        Do While DR.Read

            cmbJurusan.Items.Add(DR.Item(0))

        Loop

    End Sub



    Sub Tampilkelas()

        CMD = New OleDbCommand("select DISTINCT KELAS from tblSISWA", Conn)

        DR = CMD.ExecuteReader

        cmbKelas.Items.Clear()

        Do While DR.Read

            cmbKelas.Items.Add(DR.Item(0))

        Loop

    End Sub



    Sub Kondisiawal()

        Call Kosongkan()

        Call TampilPendidikan()

        Call Tampilangkatan()

        Call TampilGrid()

        Call TampilJurusan()

        Call Tampilkelas()

    End Sub



    Sub Carisiswa()

        CMD = New OleDbCommand("select * from tblsiswa where id_siswa='" & txtIDSiswa.Text & "'", Conn)

        DR = CMD.ExecuteReader

        DR.Read()

    End Sub



    Private Sub MasterSiswa_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Call Koneksi()

        Call Kondisiawal()

        LtempoPertama.Text = DateValue("10/07/" & Year(Today) & "")

    End Sub



    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

        Call Kondisiawal()

        Call TampilGrid()

        Call Kosongkan()

    End Sub



    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

        Me.Close()

    End Sub



    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click



        Call Carisiswa()

        Try

            If Not DR.HasRows Then

                'Call Koneksi()

                Dim simpan As String = "insert into tblsiswa values ('" & txtIDSiswa.Text & "','" & txtNIS.Text & "','" & txtNISN.Text & "','" & txtNama.Text & "','" & Microsoft.VisualBasic.Left(cmbPendidikan.Text, 2) & "','" & Microsoft.VisualBasic.Left(cmbPendidikan.Text, 2) & "','" & cmbTahunAjaran.Text & "','" & cmbJurusan.Text & "','" & cmbKelas.Text & "','" & cmbSemester.Text & "','" & MasterMenu.panellembaga.Text & "')"

                CMD = New OleDbCommand(simpan, Conn)

                CMD.ExecuteNonQuery()

                For i As Integer = 1 To 12

                    Dim TempO As Date = DateAdd(DateInterval.Month, i - 1, DateValue(LtempoPertama.Text))

                    Call Koneksi()

                    Dim hasil As String = txtIDSiswa.Text

                    Dim simpandetail2 As String = "insert into TBLSPP values ('" & txtIDSiswa.Text & i & "',0,'" & TempO & "','" & Format(TempO, "MMMM") + " " + Format(TempO, "yyyy") & "','" & txtIDSiswa.Text & "',0,'-','-')"

                    CMD = New OleDbCommand(simpandetail2, Conn)

                    CMD.ExecuteNonQuery()

                Next



                CMD = New OleDbCommand("select * from TBLSPP order by 2,4", Conn)

                DR = CMD.ExecuteReader

                DR.Read()

                Call Kosongkan()

                Call TampilGrid()

            Else

                'Call Koneksi()

                Dim edit As String = "update tblsiswa set nis='" & txtNIS.Text & "',nisn='" & txtNISN.Text & "',nama_siswa='" & txtNama.Text & "',id_biaya='" & Microsoft.VisualBasic.Left(cmbPendidikan.Text, 2) & "',ID_PENDIDIKAN='" & Microsoft.VisualBasic.Left(cmbPendidikan.Text, 2) & "',Tahun_Ajaran='" & cmbTahunAjaran.Text & "',Jurusan='" & cmbJurusan.Text & "',Kelas='" & cmbKelas.Text & "',semester='" & cmbSemester.Text & "',ID_LEMBAGA='" & MasterMenu.panellembaga.Text & "'  where id_siswa='" & txtIDSiswa.Text & "'"

                CMD = New OleDbCommand(edit, Conn)

                CMD.ExecuteNonQuery()



                Call Kosongkan()

                Call TampilGrid()

            End If

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

    End Sub



    Private Sub DGV_CellMouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DGV.CellMouseClick

        'On Error Resume Next

        txtIDSiswa.Enabled = False

        txtIDSiswa.Text = DGV.Rows(e.RowIndex).Cells(0).Value



        Call Carisiswa()

        If DR.HasRows Then

            Call Ketemu()

        End If

    End Sub



    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        'If txtIDSiswa.Text = "" Then

        '    MsgBox("ID siswa harus diisi")

        '    txtIDSiswa.Focus()

        '    Exit Sub

        'End If



        'Call Carisiswa()

        'If Not DR.HasRows Then

        '    MsgBox("id siswa tidak terdaftar")

        '    txtIDSiswa.Focus()

        '    Exit Sub

        'End If



        'If MessageBox.Show("yakin akan dihapus..?", "", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.Yes Then

        '    'Call Koneksi()

        '    Dim hapus As String = "delete  from tblsiswa where id_siswa='" & txtIDSiswa.Text & "'"

        '    CMD = New OleDbCommand(hapus, Conn)

        '    CMD.ExecuteNonQuery()

        '    Call Kosongkan()

        '    Call TampilGrid()

        'Else

        '    Call Kosongkan()



        'End If

    End Sub



    Private Sub TextBox1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtIDSiswa.KeyPress

        txtIDSiswa.MaxLength = 5

        If e.KeyChar = Chr(13) Then

            CMD = New OleDbCommand("select * from tblsiswa where id_siswa='" & txtIDSiswa.Text & "'", Conn)

            DR = CMD.ExecuteReader

            DR.Read()

            If Not DR.HasRows Then

                Call DataBaru()

            Else

                Call Ketemu()

            End If

        End If

    End Sub



    Private Sub TextBox2_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtNIS.KeyPress

        'TextBox1.MaxLength = 5

        If e.KeyChar = Chr(13) Then

            txtNISN.Focus()

        End If

    End Sub



    Private Sub TextBox3_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtNISN.KeyPress

        'TextBox1.MaxLength = 5

        If e.KeyChar = Chr(13) Then

            txtNama.Focus()

        End If

    End Sub



    Private Sub TextBox4_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtNama.KeyPress

        'TextBox1.MaxLength = 5

        If e.KeyChar = Chr(13) Then

            cmbPendidikan.Focus()

        End If

    End Sub



    Private Sub combobox1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs)

        'TextBox1.MaxLength = 5

        If e.KeyChar = Chr(13) Then

            cmbTahunAjaran.Focus()

        End If

    End Sub



    Private Sub combobox2_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs)

        'TextBox1.MaxLength = 5

        If e.KeyChar = Chr(13) Then

            cmbJurusan.Focus()

        End If

    End Sub



    Private Sub combobox3_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs)

        'TextBox1.MaxLength = 5

        If e.KeyChar = Chr(13) Then

            Button1.Focus()

        End If

    End Sub



    Private Sub TextBox6_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox6.TextChanged

        'Call Koneksi()

        DA = New OleDbDataAdapter("select * from tblsiswa where id_siswa like '%" & TextBox6.Text & "%' or nama_siswa like '%" & TextBox6.Text & "%'", Conn)

        DS = New DataSet

        DA.Fill(DS)

        DGV.DataSource = DS.Tables(0)

    End Sub



    Private Sub cmbPendidikan_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbPendidikan.SelectedIndexChanged

        CMD = New OleDbCommand("select * from tblBIAYA where id_BIAYA='" & Microsoft.VisualBasic.Left(cmbPendidikan.Text, 2) & "'", Conn)

        DR = CMD.ExecuteReader

        DR.Read()

        If DR.HasRows Then

            LblTotalBiaya.Text = DR("TOTAL_BIAYA")

        Else

            MsgBox("ID BIAYA SALAH")

        End If

    End Sub

End Class