Aplikasi Pembayaran Biaya Sekolah Dan SPP (konsultasivb.com)

 

Langkah-langkah membuat aplikasi pembayaran biaya sekolah dan SPP adalah sebagai berikut :

 

  1. Membuat database
  2. Membuat tabel-tabel
  3. Membuat relasi tabel
  4. Membuat project
  5. Membuat module koneksi
  6. Membuat form login
  7. Membuat menu utama
  8. Membuat form-form master
  9. Membuat form transaksi pembayaran
  10. Membuat form transaksi SPP
  11. Membuat laporan
  12. Dan lain-lain

 

Coding dan gambar-gambar form dan laporan dapat dilihat pada ilustrasi di bawah ini :

 

 

 

 

 

 

Module koneksi database

 

Imports System.Data.OleDb

Imports CrystalDecisions.CrystalReports.Engine

Imports CrystalDecisions.Shared

 

Module Module1

 

    Public Conn As OleDbConnection

    Public DA As OleDbDataAdapter

    Public DS As DataSet

    Public CMD As OleDbCommand

    Public DR As OleDbDataReader

 

 

    Public laporan As New ReportDocument

    Public TabelLogon As CrystalDecisions.Shared.TableLogOnInfo

 

    Public Sub SetingLaporan()

        Dim BacaTabel As CrystalDecisions.CrystalReports.Engine.Table

        For Each BacaTabel In laporan.Database.Tables

            TabelLogon = BacaTabel.LogOnInfo

            With TabelLogon.ConnectionInfo

                .ServerName = Application.StartupPath & "\dbbiayasekolah.mdb"

                .UserID = ""

                .Password = ""

                .DatabaseName = ""

            End With

            BacaTabel.ApplyLogOnInfo(TabelLogon)

        Next BacaTabel

    End Sub

 

 

    Public Sub Koneksi()

        Try

            Conn = New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=dbbiayasekolah.mdb")

            Conn.Open()

        Catch ex As Exception

            MsgBox(ex.Message)

            End

        End Try

    End Sub

End Module

 

 

 

Imports System.Data.OleDb

 

Public Class Login

 

    Dim hitung As Integer

 

    Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click

        Call Koneksi()

        CMD = New OleDbCommand("select * from tbluser where nama_user='" & txtnama.Text & "' and pwd_user='" & txtpassword.Text & "'", Conn)

        DR = CMD.ExecuteReader

        DR.Read()

        If DR.HasRows Then

            Me.Visible = False

            MasterMenu.Show()

 

            MasterMenu.panelkode.Text = DR.Item(0)

            MasterMenu.panelnama.Text = DR.Item(1)

            MasterMenu.panelstatus.Text = UCase(DR.Item(3))

 

        Else

            MsgBox("Login gagal")

            txtnama.Clear()

            txtpassword.Clear()

            txtnama.Focus()

 

            hitung = hitung + 1

            If hitung = 3 Then

                MsgBox("Login sudah 3x salah")

                End

            End If

        End If

 

        Call Koneksi()

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

        DR = CMD.ExecuteReader

        DR.Read()

        If DR.HasRows Then

            MasterMenu.panellembaga.Text = DR.Item(0)

        Else

            MsgBox("Profil lembaga harus segera diisi")

            MasterLembaga.Show()

        End If

    End Sub

 

    Private Sub Cancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Cancel.Click

        End

    End Sub

 

    Private Sub txtnama_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtnama.KeyDown

        If e.KeyCode = Keys.Enter Then

            txtpassword.Focus()

        End If

    End Sub

 

    Private Sub txtpassword_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtpassword.KeyDown

        If e.KeyCode = Keys.Enter Then

            OK.Focus()

        End If

    End Sub

End Class

 

 

 

 

 

Imports System.Data.OleDb

 

Public Class MasterLembaga

 

    Sub Ketemu()

        TextBox2.Text = DR.Item("Nama_lembaga")

        TextBox3.Text = DR.Item("alamat")

        TextBox4.Text = Microsoft.VisualBasic.Mid(DR.Item("telepon"), 9, 20)

        TextBox5.Text = Microsoft.VisualBasic.Mid(DR.Item("fax"), 5, 20)

        TextBox7.Text = Microsoft.VisualBasic.Mid(DR.Item("email"), 7, 43)

        TextBox8.Text = Microsoft.VisualBasic.Mid(DR.Item("website"), 9, 41)

        TextBox2.Focus()

    End Sub

 

    Sub TampilPerusahaan()

        Call Koneksi()

        CMD = New OleDbCommand("select * from TBLLEMBAGA WHERE ID_LEMBAGA='01'", Conn)

        DR = CMD.ExecuteReader

        DR.Read()

        If DR.HasRows Then

            TextBox2.Text = DR.Item("nama_LEMBAGA")

            TextBox3.Text = DR.Item("alamat")

            TextBox4.Text = Microsoft.VisualBasic.Mid(DR.Item("telp"), 9, 20)

            TextBox5.Text = Microsoft.VisualBasic.Mid(DR.Item("fax"), 5, 20)

            TextBox7.Text = Microsoft.VisualBasic.Mid(DR.Item("email"), 7, 50)

            TextBox8.Text = Microsoft.VisualBasic.Mid(DR.Item("website"), 9, 50)

        End If

    End Sub

 

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

        Call Koneksi()

        Call TampilPerusahaan()

    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 Koneksi()

        CMD = New OleDbCommand("select * from TBLLEMBAGA where id_lembaga='01'", Conn)

        DR = CMD.ExecuteReader

        DR.Read()

        Try

            If Not DR.HasRows Then

                Call Koneksi()

                Dim simpan As String = "insert into TBLLEMBAGA values ('01','" & TextBox2.Text & "','" & TextBox3.Text & "','" & Label4.Text & Space(1) & TextBox4.Text & "','" & Label5.Text & Space(1) & TextBox5.Text & "','" & Label6.Text & Space(1) & TextBox7.Text & "','" & Label7.Text & Space(1) & TextBox8.Text & "')"

                CMD = New OleDbCommand(simpan, Conn)

                CMD.ExecuteNonQuery()

                MsgBox("Data berhasil disimpan")

                Me.Close()

            Else

                Call Koneksi()

                Dim edit As String = "update TBLLEMBAGA set Nama_lembaga='" & TextBox2.Text & "',alamat='" & TextBox3.Text & "',telp='" & Label4.Text & Space(1) & TextBox4.Text & "',fax='" & Label5.Text & Space(1) & TextBox5.Text & "',email='" & Label6.Text & Space(1) & TextBox7.Text & "',website='" & Label7.Text & Space(1) & TextBox8.Text & "' where ID_LEMBAGA='01'"

                CMD = New OleDbCommand(edit, Conn)

                CMD.ExecuteNonQuery()

                MsgBox("Data berhasil diedit")

                Me.Close()

            End If

 

            Call Koneksi()

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

            DR = CMD.ExecuteReader

            DR.Read()

            If DR.HasRows Then

                MasterMenu.panellembaga.Text = DR.Item(0)

            End If

 

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

    End Sub

 

    Private Sub TextBox2_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox2.LostFocus

        Call Koneksi()

        CMD = New OleDbCommand("select * from TBLLEMBAGA where ID_LEMBAGA='01", Conn)

        DR = CMD.ExecuteReader

        DR.Read()

        If Not DR.HasRows Then

            TextBox3.Clear()

            TextBox4.Clear()

            TextBox5.Clear()

            TextBox7.Clear()

            TextBox8.Clear()

            TextBox3.Focus()

        Else

            Call Ketemu()

        End If

    End Sub

End Class

 

 

 

 

Imports System.Data.OleDb

 

Public Class MasterUser

 

    Sub Kosongkan()

        TextBox1.Enabled = True

        TextBox1.Clear()

        TextBox2.Clear()

        ComboBox1.Text = ""

        TextBox3.Clear()

        TextBox6.Clear()

        TextBox1.Focus()

        Call Tampillevel_user()

        Call TampilGrid()

    End Sub

 

    Sub DataBaru()

        TextBox2.Clear()

        ComboBox1.Text = ""

        TextBox3.Clear()

        TextBox6.Clear()

        TextBox2.Focus()

    End Sub

 

    Sub Ketemu()

        TextBox1.Enabled = False

        TextBox2.Text = DR.Item("nama_User")

        TextBox3.Text = DR.Item("pwd_User")

        ComboBox1.Text = DR.Item("level_user")

        TextBox2.Focus()

    End Sub

 

    Sub TampilGrid()

        'Call Koneksi()

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

        DS = New DataSet

        DA.Fill(DS)

        DGV.DataSource = DS.Tables(0)

        DGV.ReadOnly = True

    End Sub

 

    Sub Tampillevel_user()

        'Call Koneksi()

        CMD = New OleDbCommand("select distinct level_user from tblUser", Conn)

        DR = CMD.ExecuteReader

        ComboBox1.Items.Clear()

        Do While DR.Read

            ComboBox1.Items.Add(DR.Item("level_user"))

        Loop

    End Sub

 

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

        Me.StartPosition = FormStartPosition.CenterScreen

        Call Koneksi()

        Call Kosongkan()

    End Sub

 

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

        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 ComboBox1_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.LostFocus

        ComboBox1.Text = UCase(ComboBox1.Text)

    End Sub

 

 

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

        CMD = New OleDbCommand("select * from tblUser where id_user='" & TextBox1.Text & "'", Conn)

        DR = CMD.ExecuteReader

        DR.Read()

        Try

            If Not DR.HasRows Then

                Dim simpan As String = "insert into tblUser values ('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & ComboBox1.Text & "')"

                CMD = New OleDbCommand(simpan, Conn)

                CMD.ExecuteNonQuery()

            Else

                Dim edit As String = "update tblUser set nama_User='" & TextBox2.Text & "',level_user='" & ComboBox1.Text & "',pwd_User='" & TextBox3.Text & "' where id_user='" & TextBox1.Text & "'"

                CMD = New OleDbCommand(edit, Conn)

                CMD.ExecuteNonQuery()

            End If

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

        Call Kosongkan()

    End Sub

 

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

        On Error Resume Next

        TextBox1.Enabled = False

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

        TextBox2.Text = DGV.Rows(e.RowIndex).Cells(1).Value

        TextBox3.Text = DGV.Rows(e.RowIndex).Cells(2).Value

        ComboBox1.Text = DGV.Rows(e.RowIndex).Cells(3).Value

    End Sub

 

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

        'If TextBox1.Text = "" Then

        '    MsgBox("Kode User harus diisi")

        '    TextBox1.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 tblUser where id_user='" & TextBox1.Text & "'"

        '    CMD = New OleDbCommand(hapus, Conn)

        '    CMD.ExecuteNonQuery()

        '    Call Kosongkan()

        'Else

        '    Call Kosongkan()

        'End If

    End Sub

 

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

        TextBox1.MaxLength = 3

        If e.KeyChar = Chr(13) Then

            CMD = New OleDbCommand("select * from tblUser where id_user='" & TextBox1.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 TextBox2.KeyPress

        'TextBox1.MaxLength = 5

        If e.KeyChar = Chr(13) Then

            TextBox3.Focus()

        End If

    End Sub

 

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

        'TextBox1.MaxLength = 5

        If e.KeyChar = Chr(13) Then

            ComboBox1.Focus()

        End If

    End Sub

 

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

        'TextBox1.MaxLength = 5

        If e.KeyChar = Chr(13) Then

            Button1.Focus()

        End If

    End Sub

 

    Private Sub TextBox1_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.LostFocus

        'Call Koneksi()

    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 tblUser where nama_User like '%" & TextBox6.Text & "%'", Conn)

        DS = New DataSet

        DA.Fill(DS)

        DGV.DataSource = DS.Tables(0)

    End Sub

End Class

 

 

 

Imports System.Data.OleDb

 

Public Class MasterLevelendidikan

 

    Sub NomorOtomatis()

        CMD = New OleDbCommand("select id_pendidikan from tbllevelpendidikan order by id_pendidikan desc", Conn)

        DR = CMD.ExecuteReader

        DR.Read()

        If Not DR.HasRows Then

            TextBox1.Text = "01"

        Else

            TextBox1.Text = Format(Microsoft.VisualBasic.Right(DR.Item("id_pendidikan"), 2) + 1, "00")

        End If

 

    End Sub

 

    Sub Kosongkan()

        TextBox1.Enabled = False

        Call NomorOtomatis()

        TextBox2.Clear()

        TextBox6.Clear()

        Call TampilGrid()

        TextBox2.Focus()

    End Sub

 

    Sub DataBaru()

        TextBox2.Clear()

        TextBox6.Clear()

        Call TampilGrid()

        TextBox2.Focus()

    End Sub

 

    Sub Ketemu()

        TextBox1.Enabled = False

        TextBox2.Text = DR.Item("level_pendidikan")

        TextBox2.Focus()

    End Sub

 

    Sub TampilGrid()

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

        DS = New DataSet

        DA.Fill(DS)

        DGV.DataSource = DS.Tables(0)

        DGV.ReadOnly = True

    End Sub

 

    Sub Kondisiawal()

        Call Kosongkan()

    End Sub

 

    Sub CariID()

        CMD = New OleDbCommand("select * from tbllevelpendidikan where id_pendidikan='" & TextBox1.Text & "'", Conn)

        DR = CMD.ExecuteReader

        DR.Read()

    End Sub

 

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

        Call Koneksi()

        Call Kondisiawal()

        'Call NomorOtomatis()

    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 CariID()

        Try

            If Not DR.HasRows Then

                'Call Koneksi()

                Dim simpan As String = "insert into tbllevelpendidikan values ('" & TextBox1.Text & "','" & TextBox1.Text + "-" + TextBox2.Text & "')"

                CMD = New OleDbCommand(simpan, Conn)

                CMD.ExecuteNonQuery()

                Call Kosongkan()

                Call TampilGrid()

            Else

                'Call Koneksi()

                Dim edit As String = "update tbllevelpendidikan set level_pendidikan='" & TextBox2.Text & "'  where id_pendidikan='" & TextBox1.Text & "'"

                CMD = New OleDbCommand(edit, Conn)

                CMD.ExecuteNonQuery()

                'Call Kondisiawal()

                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

        TextBox1.Enabled = False

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

        Call CariID()

        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 TextBox1.Text = "" Then

        '    MsgBox("id level harus diisi")

        '    TextBox1.Focus()

        '    Exit Sub

        'End If

 

        'Call CariID()

        'If Not DR.HasRows Then

        '    MsgBox("id level tidak terdaftar")

        '    TextBox1.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 tbllevelpendidikan where id_pendidikan='" & TextBox1.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 TextBox1.KeyPress

        TextBox1.MaxLength = 5

        If e.KeyChar = Chr(13) Then

            CMD = New OleDbCommand("select * from tbllevelpendidikan where id_pendidikan='" & TextBox1.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 TextBox2.KeyPress

        '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 tbllevelpendidikan where id_pendidikan like '%" & TextBox6.Text & "%' or level_pendidikan like '%" & TextBox6.Text & "%'", Conn)

        DS = New DataSet

        DA.Fill(DS)

        DGV.DataSource = DS.Tables(0)

    End Sub

End Class

 

 

Imports System.Data.OleDb

 

Public Class MasterBiaya2

 

    Sub Bersihkan()

        ComboBox1.Text = ""

        IDPendidikan.Text = ""

        txtuangspp.Clear()

        txtkondisidata.Clear()

        txtidbiaya.Clear()

        DGV.Columns.Clear()

    End Sub

 

    Sub TampilLevelpendidikan()

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

        DR = CMD.ExecuteReader

        ComboBox1.Items.Clear()

        Do While DR.Read

            ComboBox1.Items.Add(DR.Item(1))

        Loop

    End Sub

 

    Sub TotalBiaya()

        On Error Resume Next

        Dim hitung As Integer

        For baris As Integer = 0 To DGV.RowCount - 1

            hitung = hitung + DGV.Rows(baris).Cells(2).Value

        Next

        txttotalbiaya.Text = FormatNumber(hitung, 0)

    End Sub

 

    Private Sub DGV_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DGV.CellEndEdit

 

        'On Error Resume Next

        Dim baris As Integer = DGV.RowCount - 1

        If e.ColumnIndex = 1 Then

            DGV.Rows(e.RowIndex).Cells(1).Value = UCase(DGV.Rows(e.RowIndex).Cells(1).Value)

 

            CMD = New OleDbCommand("select * from tblbiayadetail where id_biaya='" & DGV.Rows(e.RowIndex).Cells(0).Value & "'", Conn)

            DR = CMD.ExecuteReader

            DR.Read()

            If Not DR.HasRows Then

                If baris < 10 Then

                    DGV.Rows(e.RowIndex).Cells(0).Value = IDPendidikan.Text & "0" & baris

                    DGV.CurrentCell = DGV(2, DGV.CurrentCell.RowIndex)

                    SendKeys.Send("{UP}")

                Else

                    DGV.Rows(e.RowIndex).Cells(0).Value = IDPendidikan.Text & baris

                    DGV.CurrentCell = DGV(2, DGV.CurrentCell.RowIndex)

                    SendKeys.Send("{UP}")

                End If

            Else

                If baris < 10 Then

                    DGV.CurrentCell = DGV(2, DGV.CurrentCell.RowIndex)

                    SendKeys.Send("{UP}")

                Else

                    DGV.CurrentCell = DGV(2, DGV.CurrentCell.RowIndex)

                    SendKeys.Send("{UP}")

                End If

            End If

           

        End If

 

        If e.ColumnIndex = 2 Then

            Try

                DGV.CurrentCell = DGV(1, DGV.CurrentCell.RowIndex)

            Catch ex As Exception

                MsgBox("Harus angka")

            End Try

 

        End If

        Call TotalBiaya()

    End Sub

 

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

        Me.Close()

    End Sub

 

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

 

        If ComboBox1.Text = "" Or txttotalbiaya.Text = "" Or txtuangspp.Text = "" Then

            MsgBox("data belum lengkap")

            Exit Sub

        End If

 

        For baris As Integer = 0 To DGV.RowCount - 2

            CMD = New OleDbCommand("select * from tblbiayadetail where id_biaya='" & DGV.Rows(baris).Cells(0).Value & "'", Conn)

            DR = CMD.ExecuteReader

            DR.Read()

            If Not DR.HasRows Then

                Dim simpan As String = "insert into tblbiayadetail values('" & DGV.Rows(baris).Cells(0).Value & "','" & DGV.Rows(baris).Cells(1).Value & "','" & DGV.Rows(baris).Cells(2).Value & "')"

                CMD = New OleDbCommand(simpan, Conn)

                CMD.ExecuteNonQuery()

            Else

                Dim edit As String = "update tblbiayadetail set nama_biaya='" & DGV.Rows(baris).Cells(1).Value.ToString & "',jumlah_biaya='" & DGV.Rows(baris).Cells(2).Value.ToString & "' where id_biaya='" & DGV.Rows(baris).Cells(0).Value.ToString & "'"

                CMD = New OleDbCommand(edit, Conn)

                CMD.ExecuteNonQuery()

            End If

        Next

 

        CMD = New OleDbCommand("select * from TBLBiaya where id_biaya='" & IDPendidikan.Text & "'", Conn)

        DR = CMD.ExecuteReader

        DR.Read()

        If Not DR.HasRows Then

            Dim simpan As String = "insert into TBLBiaya values('" & IDPendidikan.Text & "','" & txttotalbiaya.Text & "','" & DTPJatuhTempo.Text & "','" & txtuangspp.Text & "')"

            CMD = New OleDbCommand(simpan, Conn)

            CMD.ExecuteNonQuery()

        Else

            Dim edit As String = "update TBLBiaya set total_biaya='" & txttotalbiaya.Text & "', jatuh_tempo='" & DTPJatuhTempo.Text & "',uang_spp='" & txtuangspp.Text & "' where id_biaya='" & IDPendidikan.Text & "'"

            CMD = New OleDbCommand(edit, Conn)

            CMD.ExecuteNonQuery()

        End If

        Call Bersihkan()

        Call TampilLevelpendidikan()

    End Sub

 

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

        Call Koneksi()

        Call TampilLevelpendidikan()

        'jatuh tempo 3 bulan kedepan

        DTPJatuhTempo.Text = DateAdd(DateInterval.Month, 3, Today)

    End Sub

 

    Private Sub ComboBox1_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles ComboBox1.KeyDown

        If e.KeyCode = Keys.Enter Then

            ComboBox1.Text = UCase(ComboBox1.Text)

            CMD = New OleDbCommand("select * from tblbiayadetail where LEFT(id_biaya,2) = '" & IDPendidikan.Text & "'", Conn)

            DR = CMD.ExecuteReader

            DR.Read()

            If DR.HasRows Then

                DA = New OleDbDataAdapter("select * from tblbiayadetail where LEFT(id_biaya,2) = '" & IDPendidikan.Text & "'", Conn)

                DS = New DataSet

                DA.Fill(DS)

                DGV.DataSource = DS.Tables(0)

                DGV.Columns(1).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill

                txtkondisidata.Text = "DATA LAMA"

                Call TotalBiaya()

            Else

                DA = New OleDbDataAdapter("select * from tblbiayadetail where LEFT(id_biaya,2) = '" & IDPendidikan.Text & "'", Conn)

                DS = New DataSet

                DA.Fill(DS)

                DGV.DataSource = DS.Tables(0)

                DGV.Columns(1).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill

                txtkondisidata.Text = "DATA BARU"

                Call TotalBiaya()

            End If

            DGV.Columns(0).ReadOnly = True

        End If

    End Sub

 

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged

        On Error Resume Next

 

        CMD = New OleDbCommand("select * from tbllevelpendidikan where level_pendidikan ='" & ComboBox1.Text & "'", Conn)

        DR = CMD.ExecuteReader

        DR.Read()

        If DR.HasRows Then

            IDPendidikan.Text = DR("ID_PENDIDIKAN")

        End If

 

        CMD = New OleDbCommand("select * from tblbiaya where id_biaya = '" & IDPendidikan.Text & "'", Conn)

        DR = CMD.ExecuteReader

        DR.Read()

        If DR.HasRows Then

            DTPJatuhTempo.Text = DR("jatuh_tempo")

            txtuangspp.Text = DR("uang_spp")

        Else

            txtuangspp.Clear()

        End If

 

        CMD = New OleDbCommand("select * from tblbiayadetail where LEFT(id_biaya,2) = '" & IDPendidikan.Text & "'", Conn)

        DR = CMD.ExecuteReader

        DR.Read()

        If DR.HasRows Then

            DA = New OleDbDataAdapter("select * from tblbiayadetail where LEFT(id_biaya,2) = '" & IDPendidikan.Text & "'", Conn)

            DS = New DataSet

            DA.Fill(DS)

            DGV.DataSource = DS.Tables(0)

            DGV.Columns(1).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill

            DGV.Columns(2).DefaultCellStyle.Format = "#,###"

            DGV.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

            txtkondisidata.Text = "DATA LAMA"

            Call TotalBiaya()

        Else

            DA = New OleDbDataAdapter("select * from tblbiayadetail where LEFT(id_biaya,2) = '" & IDPendidikan.Text & "'", Conn)

            DS = New DataSet

            DA.Fill(DS)

            DGV.DataSource = DS.Tables(0)

            DGV.Columns(1).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill

            txtkondisidata.Text = "DATA BARU"

            Call TotalBiaya()

        End If

 

        DGV.Columns(0).ReadOnly = True

 

    End Sub

 

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

        Call Bersihkan()

    End Sub

 

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

        On Error Resume Next

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

    End Sub

 

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

        If txtidbiaya.Text = "" Then

            MsgBox("Pilih data yang akan dihapus")

            Exit Sub

        End If

 

        CMD = New OleDbCommand("select * from tblbiayadetail where id_biaya='" & txtidbiaya.Text & "'", Conn)

        DR = CMD.ExecuteReader

        DR.Read()

        If Not DR.HasRows Then

            MsgBox("data belum terdaftar, hapus dengan menekan Escape...")

            Exit Sub

        End If

 

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

            Dim hapus As String = "delete * from tblbiayadetail where id_biaya='" & txtidbiaya.Text & "'"

            CMD = New OleDbCommand(hapus, Conn)

            CMD.ExecuteNonQuery()

           

            CMD = New OleDbCommand("select * from tblbiayadetail where id_biaya like '%" & IDPendidikan.Text & "%'", Conn)

            DR = CMD.ExecuteReader

            DR.Read()

            If DR.HasRows Then

                DA = New OleDbDataAdapter("select * from tblbiayadetail where id_biaya like '%" & IDPendidikan.Text & "%'", Conn)

                DS = New DataSet

                DA.Fill(DS)

                DGV.DataSource = DS.Tables(0)

                DGV.Columns(1).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill

                txtkondisidata.Text = "DATA LAMA"

                Call TotalBiaya()

            Else

                DA = New OleDbDataAdapter("select * from tblbiayadetail where id_biaya like '%" & IDPendidikan.Text & "%'", Conn)

                DS = New DataSet

                DA.Fill(DS)

                DGV.DataSource = DS.Tables(0)

                DGV.Columns(1).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill

                txtkondisidata.Text = "DATA BARU"

                Call TotalBiaya()

            End If

            txtidbiaya.Clear()

            DGV.Columns(0).ReadOnly = True

        End If

 

        Dim editbiaya As String = "update tblbiaya set total_biaya='" & txttotalbiaya.Text & "' where id_biaya='" & IDPendidikan.Text & "'"

        CMD = New OleDbCommand(editbiaya, Conn)

        CMD.ExecuteNonQuery()

    End Sub

 

    Private Sub DGV_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles DGV.KeyDown

        On Error Resume Next

        Dim baris As Integer = DGV.RowCount - 2

        If e.KeyCode = Keys.Escape Then

            CMD = New OleDbCommand("select * from tblbiayadetail where id_biaya='" & DGV.Rows(baris).Cells(0).Value & "'", Conn)

            DR = CMD.ExecuteReader

            DR.Read()

            If Not DR.HasRows Then

                DGV.Rows.Remove(DGV.CurrentRow)

                Call TotalBiaya()

            Else

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

                    Dim hapus As String = "delete * from tblbiayadetail where id_biaya='" & DGV.Rows(baris).Cells(0).Value & "'"

                    CMD = New OleDbCommand(hapus, Conn)

                    CMD.ExecuteNonQuery()

                    DA = New OleDbDataAdapter("select * from tblbiayadetail where id_biaya like '%" & IDPendidikan.Text & "%'", Conn)

                    DS = New DataSet

                    DA.Fill(DS)

                    DGV.DataSource = DS.Tables(0)

                    DGV.Columns(1).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill

                    txtkondisidata.Text = "DATA LAMA"

                    Call TotalBiaya()

                End If

            End If

        End If

    End Sub

End Class

 

 

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

 

 

 

Imports System.Data.OleDb

 

Public Class TRPembayaran

 

    Sub Nomorotomat()

        CMD = New OleDbCommand("select id_pembayaran from tblpembayaran order by id_pembayaran desc", Conn)

        DR = CMD.ExecuteReader

        DR.Read()

        If Not DR.HasRows Then

            txtnomor.Text = "00001"

        Else

            txtnomor.Text = Format(Microsoft.VisualBasic.Left(DR.Item("id_pembayaran"), 5) + 1, "00000")

        End If

        txtnomor.Enabled = False

    End Sub

 

    Sub Kosongkan()

        txtIDSiswa.Clear()

        txtnama.Clear()

        txttotal.Clear()

        txtdibayar.Clear()

        txtkembali.Clear()

        txtsisa.Clear()

        txtIDpendidikan.Clear()

        txtLevel.Clear()

        txtsemester.Text = ""

        txtjatuhtempo.Clear()

        DGV.Columns.Clear()

    End Sub

 

 

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

        Call Koneksi()

        Call Kosongkan()

        Call Nomorotomat()

    End Sub

 

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

        If e.KeyChar = Chr(13) Then

            txtIDSiswa.Focus()

        End If

    End Sub

 

    Private Sub TextBox3_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtnama.TextChanged

        CMD = New OleDbCommand("select * from tblsiswa where nama_siswa like '%" & txtnama.Text & "%'", Conn)

        DR = CMD.ExecuteReader

        DR.Read()

        If DR.HasRows Then

            txtIDSiswa.Text = DR.Item(0)

        Else

            txtIDSiswa.Text = ""

        End If

    End Sub

 

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

        Me.Close()

    End Sub

 

    Sub TotalBayar()

        Dim hitung As Integer

        For baris As Integer = 0 To DGV.RowCount - 1

            hitung = hitung + DGV.Rows(baris).Cells(5).Value

        Next

        txttotal.Text = hitung

        txtdibayar.Text = hitung

        txtkembali.Text = Val(txttotal.Text) - Val(txtdibayar.Text)

    End Sub

 

    Sub TotalSisa()

        Dim hitung As Integer

        For baris As Integer = 0 To DGV.RowCount - 1

            hitung = hitung + DGV.Rows(baris).Cells(4).Value

        Next

        txtsisa.Text = hitung

    End Sub

 

    Private Sub DGV_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DGV.CellEndEdit

 

        Try

            If e.ColumnIndex = 5 Then

                If DateValue(TanggalBayar.Text) > DateValue(txtjatuhtempo.Text) Then

                    DGV.Rows(e.RowIndex).Cells(6).Value = "BAYAR TUNGGAKAN"

                Else

                    DGV.Rows(e.RowIndex).Cells(6).Value = "BAYAR CICILAN"

                End If

                DGV.Columns(6).AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells

                DGV.Rows(e.RowIndex).Cells(4).Value = DGV.Rows(e.RowIndex).Cells(2).Value - (DGV.Rows(e.RowIndex).Cells(3).Value + DGV.Rows(e.RowIndex).Cells(5).Value)

            End If

 

            Call TotalBayar()

            Call TotalSisa()

        Catch ex As Exception

            MsgBox("Harus angka")

            SendKeys.Send("{UP}")

            DGV.Rows(e.RowIndex).Cells(5).Value = ""

            DGV.Rows(e.RowIndex).Cells(6).Value = ""

        End Try

    End Sub

 

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

        Call Kosongkan()

        txtIDSiswa.Clear()

    End Sub

 

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

        If e.KeyChar = Chr(13) Then

            DGV.Columns.Clear()

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

            DR = CMD.ExecuteReader

            DR.Read()

            If DR.HasRows Then

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

                txtIDpendidikan.Text = DR.Item("ID_BIAYA")

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

 

                CMD = New OleDbCommand("select jatuh_tempo from tblbiaya where id_biaya='" & txtIDpendidikan.Text & "'", Conn)

                DR = CMD.ExecuteReader

                DR.Read()

                If DR.HasRows Then

                    txtjatuhtempo.Text = DR("jatuh_tempo")

                End If

 

                CMD = New OleDbCommand("select * from tbllevelpendidikan where id_pendidikan='" & txtIDpendidikan.Text & "'", Conn)

                DR = CMD.ExecuteReader

                DR.Read()

                If DR.HasRows Then

                    txtLevel.Text = DR("level_pendidikan")

                End If

 

                CMD = New OleDbCommand("select distinct tblpembayarandetail.id_biaya,tblbiayadetail.nama_biaya,jumlah_biaya,tblpembayarandetail.jumlah_bayar as [Sudah Bayar],Sisa from tblpembayarandetail,tblbiayadetail,tblpembayaran where tblpembayarandetail.id_biaya=tblbiayadetail.id_biaya and tblpembayaran.id_siswa='" & txtIDSiswa.Text & "' and left(tblpembayarandetail.id_biaya,2) = '" & txtIDpendidikan.Text & "' and tblpembayarandetail.id_pembayaran in (select max(id_pembayaran) from tblpembayarandetail where leFt(id_biaya,2) ='" & txtIDpendidikan.Text & "') group by tblpembayarandetail.id_biaya,tblbiayadetail.nama_biaya,tblbiayadetail.jumlah_biaya,tblpembayarandetail.jumlah_bayar,sisa order by 1", Conn)

 

                DR = CMD.ExecuteReader

                DR.Read()

                If DR.HasRows Then

                    DA = New OleDbDataAdapter("select distinct tblpembayarandetail.id_biaya AS [Id Biaya],tblbiayadetail.nama_biaya as [Nama Biaya],jumlah_biaya as [Jumlah Biaya],tblpembayarandetail.jumlah_bayar as [Sudah Bayar],Sisa from tblpembayarandetail,tblbiayadetail,tblpembayaran where tblpembayarandetail.id_biaya=tblbiayadetail.id_biaya and tblpembayaran.id_siswa='" & txtIDSiswa.Text & "' and left(tblpembayarandetail.id_biaya,2) = '" & txtIDpendidikan.Text & "' and tblpembayarandetail.id_pembayaran in (select max(id_pembayaran) from tblpembayarandetail where left(id_biaya,2) ='" & txtIDpendidikan.Text & "') group by tblpembayarandetail.id_biaya,tblbiayadetail.nama_biaya,tblbiayadetail.jumlah_biaya,tblpembayarandetail.jumlah_bayar,sisa order by 1", Conn)

 

                    DS = New DataSet

                    DA.Fill(DS)

                    DGV.DataSource = DS.Tables(0)

                    DGV.Columns.Add("bayar", "Bayar Sekarang")

                    DGV.Columns.Add("Keterangan", "Keterangan")

                    DGV.Columns(1).AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells

                    DGV.Columns(0).ReadOnly = True

                    DGV.Columns(1).ReadOnly = True

                    DGV.Columns(2).ReadOnly = True

                    DGV.Columns(3).ReadOnly = True

                    DGV.Columns(4).ReadOnly = True

                    For baris As Integer = 0 To DGV.RowCount - 2

                        DGV.Rows(baris).Cells(3).Value = DGV.Rows(baris).Cells(2).Value - DGV.Rows(baris).Cells(4).Value

                        DGV.Rows(baris).Cells(5).Value = 0

                        DGV.Rows(baris).Cells(6).Value = "-"

 

                        If DGV.Rows(baris).Cells(4).Value <= 0 Then

                            DGV.Rows(baris).Cells(5).ReadOnly = True

                        Else

                            DGV.Rows(baris).Cells(5).ReadOnly = False

                        End If

                    Next

 

                    For kolom As Integer = 2 To DGV.ColumnCount - 1

                        DGV.Columns(kolom).DefaultCellStyle.Format = "###,###,###"

                        DGV.Columns(kolom).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

                    Next

                    DGV.Columns(4).DefaultCellStyle.ForeColor = Color.Red

                    Call TotalSisa()

 

                    If txtsisa.Text = 0 Then

                        MsgBox("pembayaran sudah lunas")

                        Call Kosongkan()

                    End If

                Else

                    Call TampilGridBiaya()

                    DGV.Columns(0).ReadOnly = True

                    DGV.Columns(1).ReadOnly = True

                    DGV.Columns(2).ReadOnly = True

                    DGV.Columns(3).ReadOnly = True

                    DGV.Columns(4).ReadOnly = True

                End If

            Else

                txtnama.Clear()

                txtIDpendidikan.Clear()

                txtsemester.Text = ""

                DGV.Columns.Clear()

                MsgBox("Id siswa tidak terdaftar")

                DataSiswa.Show()

            End If

        End If

 

        If e.KeyChar = Chr(27) Then

            Me.Close()

        End If

    End Sub

 

    Sub TampilGridBiaya()

        DA = New OleDbDataAdapter("select * from tblbiayadetail where left(id_biaya,2) = '" & txtIDpendidikan.Text & "' order by 1", Conn)

        DS = New DataSet

        DA.Fill(DS)

        DGV.DataSource = DS.Tables(0)

        DGV.Columns(1).AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells

        DGV.Columns(2).DefaultCellStyle.Format = "###,###,###"

        DGV.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

 

        DGV.Columns(0).HeaderText = "Id Biaya"

        DGV.Columns(1).HeaderText = "Nama Biaya"

        DGV.Columns(2).HeaderText = "Jumlah Biaya"

 

        DGV.Columns.Add("sudah", "Sudah Bayar")

        DGV.Columns.Add("Sisa", "Sisa")

        DGV.Columns.Add("Dibayar", "Bayar Sekarang")

        DGV.Columns.Add("Keterangan", "Keterangan")

 

 

        For baris As Integer = 0 To DGV.RowCount - 2

            DGV.Rows(baris).Cells(3).Value = 0

            DGV.Rows(baris).Cells(4).Value = DGV.Rows(baris).Cells(2).Value

            DGV.Rows(baris).Cells(5).Value = 0

        Next

 

        For kolom As Integer = 2 To DGV.ColumnCount - 1

            DGV.Columns(kolom).DefaultCellStyle.Format = "###,###,###"

            DGV.Columns(kolom).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

 

        Next

 

        DGV.Columns(4).DefaultCellStyle.ForeColor = Color.Red

        DGV.Columns(1).AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells

        Call TotalSisa()

    End Sub

 

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

 

        If txtIDSiswa.Text = "" Or txttotal.Text = "" Or txtdibayar.Text = "" Or txtkembali.Text = "" Then

            MsgBox("data belum lengkap")

            Exit Sub

        End If

 

        Dim simpan As String = "insert into tblpembayaran values('" & txtnomor.Text & "','" & TanggalBayar.Text & "','" & txtIDSiswa.Text & "','" & txttotal.Text & "','" & txtdibayar.Text & "','" & txtkembali.Text & "','" & txtsisa.Text & "','" & txtsemester.Text & "','U01')"

        CMD = New OleDbCommand(simpan, Conn)

        CMD.ExecuteNonQuery()

 

        For baris As Integer = 0 To DGV.RowCount - 2

            Dim simpandetail As String = "insert into tblpembayarandetail values('" & txtnomor.Text & "','" & DGV.Rows(baris).Cells(0).Value & "','" & DGV.Rows(baris).Cells(5).Value & "','" & DGV.Rows(baris).Cells(4).Value & "','" & DGV.Rows(baris).Cells(6).Value & "')"

            CMD = New OleDbCommand(simpandetail, Conn)

            CMD.ExecuteNonQuery()

        Next

 

        If MessageBox.Show("cetak faktur...?", "", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.Yes Then

            Cetak.Show()

            laporan.Load("cetak pembayaran.rpt")

            Call SetingLaporan()

            Cetak.CRV.ReportSource = laporan

            Cetak.CRV.RefreshReport()

        End If

        Call Nomorotomat()

        Call Kosongkan()

    End Sub

 

    Private Sub txtdibayar_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtdibayar.KeyDown

        If e.KeyCode = Keys.Enter Then

            If Val(txtdibayar.Text) < Val(txttotal.Text) Then

                MsgBox("pembayaran kurang")

                Exit Sub

            ElseIf Val(txtdibayar.Text) >= Val(txttotal.Text) Then

                txtkembali.Text = Val(txtdibayar.Text) - Val(txttotal.Text)

                BTNsimpan.Focus()

            End If

        End If

 

        If Not (e.KeyCode >= "0" And e.KeyCode <= "9" Or e.KeyCode = Keys.Back) Then

            e.Handled = True

        End If

    End Sub

 

    Private Sub DGV_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles DGV.KeyDown

        On Error Resume Next

        If e.KeyCode = Keys.Escape Then

            DGV.Rows.Remove(DGV.CurrentRow)

            Call TotalBayar()

            Call TotalSisa()

        End If

    End Sub

End Class

 

 

Imports System.Data.OleDb

Public Class TRSPP

 

    Sub Bayarotomatis()

        'Call Koneksi()

        CMD = New OleDbCommand("select * from TBLSPP where No_bayar in(select max(No_Bayar) from TBLSPP)", Conn)

        DR = CMD.ExecuteReader

        DR.Read()

        If Not DR.HasRows Then

            TxtNomorBayar.Text = Format(Now, "yyMMdd") + "0001"

        Else

            If Microsoft.VisualBasic.Left(DR.Item("no_Bayar"), 6) = Format(Now, "yyMMdd") Then

                TxtNomorBayar.Text = DR.Item("no_Bayar") + 1

            Else

                TxtNomorBayar.Text = Format(Now, "yyMMdd") + "0001"

            End If

        End If

    End Sub

 

    Sub KondisiAwal()

        TxtIDSiswa.Clear()

        Label2.Text = ""

        LBLnamaSiswa.Text = ""

        LblKelas.Text = ""

        LBLtahunAjaran.Text = ""

        LBiayaSPP.Text = ""

        Label13.Text = ""

        Label15.Text = ""

        Label17.Text = ""

        Label18.Text = ""

        Button1.Enabled = False

        Button4.Enabled = False

        Button5.Enabled = False

        DGV.Columns.Clear()

        TxtIDSiswa.Focus()

    End Sub

 

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

        Call KondisiAwal()

        Call Koneksi()

        Call Bayarotomatis()

        Button1.Enabled = False

        Button4.Enabled = False

        Button5.Enabled = False

        Me.Top = 100

 

    End Sub

 

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

        TxtNomorBayar.MaxLength = 5

        If e.KeyChar = Chr(13) Then

            'Call Koneksi()

            CMD = New OleDbCommand("select * from tblSiswa where ID_Siswa='" & TxtIDSiswa.Text & "'", Conn)

            DR = CMD.ExecuteReader

            DR.Read()

            If DR.HasRows Then

                LBLnamaSiswa.Text = DR.Item("nama_Siswa")

                LblKelas.Text = DR.Item("Kelas")

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

 

                CMD = New OleDbCommand("select uang_spp from tblbiaya,tblsiswa where tblsiswa.id_biaya=tblbiaya.id_biaya and tblsiswa.ID_Siswa='" & TxtIDSiswa.Text & "'", Conn)

                DR = CMD.ExecuteReader

                DR.Read()

                LBiayaSPP.Text = DR.Item("uang_spp")

                LBiayaSPP.Text = FormatNumber(LBiayaSPP.Text, 0)

               

            Else

                MsgBox("ID_Siswa tidak terdaftar")

                Call KondisiAwal()

                DataSiswa.Show()

            End If

        End If

 

 

        'Call Koneksi()

        CMD = New OleDbCommand("select * from TBLSPP where ID_Siswa='" & TxtIDSiswa.Text & "'", Conn)

        DR = CMD.ExecuteReader

        DR.Read()

        If DR.HasRows Then

            'Call Koneksi()

            DA = New OleDbDataAdapter("select * from TBLSPP where ID_Siswa='" & TxtIDSiswa.Text & "' ORDER BY 3", Conn)

            DS = New DataSet

            DA.Fill(DS)

            DGV.DataSource = DS.Tables(0)

            DGV.ReadOnly = True

            DGV.Columns(5).DefaultCellStyle.Format = "###,###,###"

            DGV.Columns(5).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

            DGV.Columns(4).Visible = False

            DGV.Columns(7).Visible = False

            DGV.Columns(6).Width = 150

            Call TotalTelahDibayar()

            Call TotalBelumDibayar()

        End If

        If Not ((e.KeyChar >= "0" And e.KeyChar <= "9") Or e.KeyChar = vbBack) Then e.Handled = True

    End Sub

 

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

        Call KondisiAwal()

    End Sub

 

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

        Me.Close()

    End Sub

 

    Sub TotalTelahDibayar()

        Dim hitung As Integer = 0

        For baris As Integer = 0 To DGV.RowCount - 1

            hitung = hitung + DGV.Rows(baris).Cells(5).Value

            Label13.Text = Format(hitung, "###,###,###")

        Next

 

        'Call Koneksi()

        CMD = New OleDbCommand("select count(keterangan) from TBLSPP where ID_Siswa='" & TxtIDSiswa.Text & "' and keterangan<>'-'", Conn)

        DR = CMD.ExecuteReader

        DR.Read()

        Label17.Text = DR.Item(0) & " Bulan"

    End Sub

 

    Sub TotalBelumDibayar()

        'Call Koneksi()

        CMD = New OleDbCommand("select count(keterangan) from TBLSPP where ID_Siswa='" & TxtIDSiswa.Text & "' and keterangan='-'", Conn)

        DR = CMD.ExecuteReader

        DR.Read()

        Label18.Text = DR.Item(0) & " Bulan"

        Label15.Text = Val(Microsoft.VisualBasic.Str(LBiayaSPP.Text) * DR.Item(0))

        Label15.Text = FormatNumber(Label15.Text, 0)

    End Sub

 

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

        Try

            'Call Koneksi()

            Dim KondisiBayar As String

            If DGV.Rows(DGV.CurrentCell.RowIndex).Cells(2).Value > DateTimePicker1.Value Then

                KondisiBayar = "LUNAS"

            Else

                KondisiBayar = "LUNASI TUNGGAKAN"

            End If

 

            Dim bayar As String = "update TBLSPP set no_bayar='" & TxtNomorBayar.Text & "',tgl_bayar='" & DateTimePicker1.Text & "',jumlah='" & Microsoft.VisualBasic.Str(LBiayaSPP.Text) & "',keterangan='" & KondisiBayar & "',id_user='" & MasterMenu.panelkode.Text & "' where no_bayar='" & DGV.Rows(DGV.CurrentCell.RowIndex).Cells(0).Value & "'"

            CMD = New OleDbCommand(bayar, Conn)

            CMD.ExecuteNonQuery()

 

            'Call Koneksi()

            DA = New OleDbDataAdapter("select * from TBLSPP where ID_Siswa='" & TxtIDSiswa.Text & "' ORDER BY 3", Conn)

            DS = New DataSet

            DA.Fill(DS)

            DGV.DataSource = DS.Tables(0)

            DGV.ReadOnly = True

            DGV.Columns(5).DefaultCellStyle.Format = "###,###,###"

            DGV.Columns(5).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

            DGV.Columns(6).Width = 190

            Call TotalTelahDibayar()

            Call TotalBelumDibayar()

            Call Bayarotomatis()

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

    End Sub

 

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

 

        Cetak.Show()

        Cetak.CRV.SelectionFormula = "{TBLSPP.no_bayar}='" & TextBox2.Text & "'"

        laporan.Load("master spp.rpt")

        Call SetingLaporan()

        Cetak.CRV.ReportSource = laporan

        Cetak.CRV.RefreshReport()

    End Sub

 

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click

        Cetak.Show()

        Cetak.CRV.SelectionFormula = "{TBLSPP.ID_Siswa}='" & TxtIDSiswa.Text & "' and totext({TBLSPP.tgl_bayar})='" & DGV.Rows(DGV.CurrentCell.RowIndex).Cells(1).Value & "'"

        laporan.Load("master spp.rpt")

        Call SetingLaporan()

        Cetak.CRV.ReportSource = laporan

        Cetak.CRV.RefreshReport()

    End Sub

 

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click

        Cetak.Show()

        Cetak.CRV.SelectionFormula = "{TBLSPP.ID_Siswa}='" & TxtIDSiswa.Text & "' and {TBLSPP.jumlah}>0"

        laporan.Load("master spp.rpt")

        Call SetingLaporan()

        Cetak.CRV.ReportSource = laporan

        Cetak.CRV.RefreshReport()

 

    End Sub

 

    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click

        Cetak.Show()

        Cetak.CRV.SelectionFormula = "{TBLSPP.ID_Siswa}='" & TxtIDSiswa.Text & "' and {TBLSPP.jumlah}=0"

        laporan.Load("master spp.rpt")

        Call SetingLaporan()

        Cetak.CRV.ReportSource = laporan

        Cetak.CRV.RefreshReport()

    End Sub

 

    Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click

 

        Cetak.Show()

        Cetak.CRV.SelectionFormula = "{TBLSPP.ID_Siswa}='" & TxtIDSiswa.Text & "'"

        laporan.Load("master spp.rpt")

        Call SetingLaporan()

        Cetak.CRV.ReportSource = laporan

        Cetak.CRV.RefreshReport()

 

    End Sub

 

    Private Sub TxtIDSiswa_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TxtIDSiswa.TextChanged

        If TxtIDSiswa.Text = "" Then

            Call KondisiAwal()

        End If

    End Sub

 

    Private Sub DGV_RowEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DGV.RowEnter

        Label2.Text = DGV.Rows(e.RowIndex).Cells(6).Value

        If Label2.Text = "-" Then

            Button1.Enabled = True

            Button4.Enabled = False

            Button5.Enabled = False

        ElseIf Label2.Text = "LUNAS" Or Label2.Text = "LUNASI TUNGGAKAN" Then

            Button1.Enabled = False

            Button4.Enabled = True

            Button5.Enabled = True

        End If

    End Sub

 

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

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

    End Sub

End Class

 

 

Imports System.Data.OleDb

 

Public Class LaporanSPP

 

    Sub TampilKelas()

        'Call Koneksi()

        CMD = New OleDbCommand("select distinct kelas from tblsiswa", Conn)

        DR = CMD.ExecuteReader

        ComboBox1.Items.Clear()

        Do While DR.Read

            ComboBox1.Items.Add(DR.Item("kelas"))

            ComboBox2.Items.Add(DR.Item("kelas"))

        Loop

    End Sub

 

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

        Call Koneksi()

        Call TampilKelas()

    End Sub

 

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

        CRV.SelectionFormula = "totext({tblspp.tgl_bayar})='" & DateTimePicker1.Text & "' and {tblspp.keterangan}<>'-'"

        laporan.Load("laporan spp harian.rpt")

        Call SetingLaporan()

        CRV.ReportSource = laporan

        CRV.RefreshReport()

    End Sub

 

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

        CRV.SelectionFormula = "{tblspp.keterangan}<>'-' and month({tblspp.tgl_bayar})=(" & Month(DateTimePicker2.Text) & ") and year({tblspp.tgl_bayar}) =(" & Year(DateTimePicker2.Text) & ")"

        laporan.Load("laporan spp bulanan.rpt")

        Call SetingLaporan()

        CRV.ReportSource = laporan

        CRV.RefreshReport()

 

    End Sub

 

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

        CRV.SelectionFormula = "{tblsiswa.kelas}='" & ComboBox1.Text & "' and {tblspp.keterangan}<>'-'"

        laporan.Load("laporan spp per kelas.rpt")

        Call SetingLaporan()

        CRV.ReportSource = laporan

        CRV.RefreshReport()

 

    End Sub

 

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

        CRV.SelectionFormula = "{tblsiswa.kelas}='" & ComboBox2.Text & "' and {tblspp.keterangan}='-' and month({tblspp.jatuh_tempo})=(" & Month(DateTimePicker3.Text) & ") and year({tblspp.jatuh_tempo}) =(" & Year(DateTimePicker3.Text) & ")  "

        laporan.Load("laporan tunggakan.rpt")

        Call SetingLaporan()

        CRV.ReportSource = laporan

        CRV.RefreshReport()

 

    End Sub

 

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

        Me.Close()

    End Sub

End Class

 

 

 

 

Imports System.Data.OleDb

 

Public Class LaporanPembayaran

 

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

        If RadioButton1.Checked = True Then

            CRV.SelectionFormula = "{TBLpembayaran.tanggal} in date ('" & DateTimePicker1.Text & "') to date ('" & DateTimePicker1.Text & "') "

            laporan.Load("master pembayaran1.rpt")

            Call SetingLaporan()

            CRV.ReportSource = laporan

            CRV.RefreshReport()

        ElseIf RadioButton2.Checked = True Then

            CRV.SelectionFormula = "month({tblpembayaran.tanggal})=(" & Month(DateTimePicker1.Text) & ") and year({tblpembayaran.tanggal}) =(" & Year(DateTimePicker1.Text) & ")"

            laporan.Load("master pembayaran1.rpt")

            Call SetingLaporan()

            CRV.ReportSource = laporan

            CRV.RefreshReport()

        ElseIf RadioButton3.Checked = True Then

            CRV.SelectionFormula = "year({tblpembayaran.tanggal}) =(" & Year(DateTimePicker1.Text) & ")"

            laporan.Load("master pembayaran1.rpt")

            Call SetingLaporan()

            CRV.ReportSource = laporan

            CRV.RefreshReport()

        End If

    End Sub

 

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

        Call Koneksi()

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

        DR = CMD.ExecuteReader

        Do While DR.Read

            ListBox1.Items.Add(DR.Item(1))

        Loop

    End Sub

 

    Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged

        CRV.SelectionFormula = "{tbllevelpendidikan.level_pendidikan} ='" & ListBox1.Text & "'"

        laporan.Load("master pembayaran1.rpt")

        Call SetingLaporan()

        CRV.ReportSource = laporan

        CRV.RefreshReport()

    End Sub

 

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

 

        DA = New OleDbDataAdapter("select distinct tblpembayaran.id_siswa,tblsiswa.nama_siswa,tbllevelpendidikan.level_pendidikan,tblbiaya.total_biaya,sum(tblpembayarandetail.jumlah_bayar) as total_bayar, tblbiaya.total_biaya-sum(tblpembayarandetail.jumlah_bayar) as sisa_biaya from tblpembayaran,tblsiswa,tblpembayarandetail,tblbiaya,tbllevelpendidikan where tblpembayaran.id_siswa=tblsiswa.id_siswa and tblpembayaran.id_pembayaran=tblpembayarandetail.id_pembayaran and tblbiaya.id_biaya=left(tblpembayarandetail.id_biaya,2) and tblsiswa.id_pendidikan=tbllevelpendidikan.id_pendidikan group by  tblpembayaran.id_siswa,tblsiswa.nama_siswa,tblbiaya.total_biaya,tblsiswa.kelas,tbllevelpendidikan.level_pendidikan", Conn)

        DS = New DataSet

        DA.Fill(DS)

        DGV.DataSource = DS.Tables(0)

        DGV.ReadOnly = True

 

        For baris As Integer = 0 To DGV.RowCount - 2

            CMD = New OleDbCommand("select * from tblrekap where id_siswa='" & DGV.Rows(baris).Cells(0).Value & "'", Conn)

 

            DR = CMD.ExecuteReader

            DR.Read()

            If Not DR.HasRows Then

                Dim simpan As String = "insert into tblrekap values('" & DGV.Rows(baris).Cells(0).Value & "','" & DGV.Rows(baris).Cells(1).Value & "','" & DGV.Rows(baris).Cells(2).Value & "','" & DGV.Rows(baris).Cells(3).Value & "','" & DGV.Rows(baris).Cells(4).Value & "','" & DGV.Rows(baris).Cells(5).Value & "')"

                CMD = New OleDbCommand(simpan, Conn)

                CMD.ExecuteNonQuery()

            Else

                Dim edit As String = "update tblrekap set nama_siswa='" & DGV.Rows(baris).Cells(1).Value & "',pendidikan='" & DGV.Rows(baris).Cells(2).Value & "',biaya='" & DGV.Rows(baris).Cells(3).Value & "',telah_bayar='" & DGV.Rows(baris).Cells(4).Value & "',sisa='" & DGV.Rows(baris).Cells(5).Value & "' where id_siswa='" & DGV.Rows(baris).Cells(0).Value & "'"

                CMD = New OleDbCommand(edit, Conn)

                CMD.ExecuteNonQuery()

            End If

        Next

 

        laporan.Load("rekap pembayaran.rpt")

        Call SetingLaporan()

        CRV.ReportSource = laporan

        CRV.RefreshReport()

 

    End Sub

End Class

 

 

Imports System.Data.OleDb

 

Public Class CariPembayaran

 

 

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

        Call Koneksi()

    End Sub

 

    Private Sub TextBox1_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles TextBox1.KeyDown

        If e.KeyCode = Keys.Enter Then

 

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

            DR = CMD.ExecuteReader

            DR.Read()

            If DR.HasRows Then

                TextBox2.Text = DR("nama_siswa")

            End If

 

            DA = New OleDbDataAdapter("SELECT DISTINCT TBLSISWA.ID_SISWA,NAMA_SISWA,TBLSISWA.ID_BIAYA,TAHUN_AJARAN,JURUSAN,KELAS,TBLBiaya.TOTAL_BIAYA FROM TBLSISWA,TBLBiaya WHERE TBLSISWA.ID_BIAYA=TBLBiaya.ID_BIAYA AND ID_SISWA LIKE '%" & TextBox1.Text & "%'", Conn)

 

            DS = New DataSet

            DA.Fill(DS)

            DGV1.DataSource = DS.Tables(0)

            DGV1.ReadOnly = True

            DGV1.Columns(6).DefaultCellStyle.Format = "#,###"

            DGV1.Columns(6).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

 

 

            DA = New OleDbDataAdapter("SELECT TBLPEMBAYARAN.ID_PEMBAYARAN,TANGGAL,TOTAL_BAYAR,SISA_BIAYA FROM TBLPEMBAYARAN WHERE ID_SISWA LIKE '%" & TextBox1.Text & "%'", Conn)

 

            DS = New DataSet

            DA.Fill(DS)

            DGV2.DataSource = DS.Tables(0)

            DGV2.ReadOnly = True

            DGV2.Columns(2).DefaultCellStyle.Format = "#,###"

            DGV2.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

 

            DGV2.Columns(2).DefaultCellStyle.Format = "#,###"

            DGV2.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

 

            DGV2.Columns(3).DefaultCellStyle.Format = "#,###"

            DGV2.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

 

 

            DA = New OleDbDataAdapter("SELECT DISTINCT TBLPEMBAYARANDETAIL.ID_PEMBAYARAN,TBLBIAYADETAIL.NAMA_BIAYA,TBLPEMBAYARANDETAIL.JUMLAH_BAYAR,SISA FROM TBLPEMBAYARANDETAIL,TBLBiaya,TBLBIAYADETAIL,TBLPEMBAYARAN,TBLSISWA WHERE TBLPEMBAYARAN.ID_PEMBAYARAN=TBLPEMBAYARANDETAIL.ID_PEMBAYARAN AND TBLPEMBAYARANDETAIL.ID_BIAYA=TBLBIAYADETAIL.ID_BIAYA  AND TBLPEMBAYARAN.ID_SISWA LIKE '%" & TextBox1.Text & "%'", Conn)

 

            DS = New DataSet

            DA.Fill(DS)

            DGV3.DataSource = DS.Tables(0)

            DGV3.ReadOnly = True

 

            DGV3.Columns(2).DefaultCellStyle.Format = "#,###"

            DGV3.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

 

            DGV3.Columns(3).DefaultCellStyle.Format = "#,###"

            DGV3.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

 

        End If

 

        Call HitungTotal()

        If txttotalbiaya.Text = txttotalbayar.Text Then

            txtketerangan.Text = "LUNAS"

        Else

            txtketerangan.Text = "BELUM LUNAS"

        End If

        If e.KeyCode = Keys.Escape Then

            Me.Close()

        End If

    End Sub

 

    Sub HitungTotal()

 

        Dim hitungbiaya As Double

        For baris As Integer = 0 To DGV1.RowCount - 1

            hitungbiaya = hitungbiaya + DGV1.Rows(baris).Cells(6).Value

        Next

        txttotalbiaya.Text = FormatNumber(hitungbiaya, 0)

 

        Dim hitungbayar As Double

        For baris As Integer = 0 To DGV2.RowCount - 1

            hitungbayar = hitungbayar + DGV2.Rows(baris).Cells(2).Value

        Next

        txttotalbayar.Text = FormatNumber(hitungbayar, 0)

 

        txttotalsisa.Text = txttotalbiaya.Text - txttotalbayar.Text

        txttotalsisa.Text = FormatNumber(txttotalsisa.Text, 0)

    End Sub

 

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

        DGV1.Columns.Clear()

        DGV2.Columns.Clear()

        DGV3.Columns.Clear()

        TextBox1.Focus()

        TextBox1.Text = "Ketik ID Siswa"

        TextBox2.Text = "Ketik Nama Siswa"

    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 TextBox2_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles TextBox2.KeyDown

        If e.KeyCode = Keys.Enter Then

            CMD = New OleDbCommand("select * from tblsiswa where nama_siswa like '%" & TextBox2.Text & "%'", Conn)

            DR = CMD.ExecuteReader

            DR.Read()

            If DR.HasRows Then

                TextBox1.Text = DR.Item("id_siswa")

                TextBox1.Focus()

            End If

        End If

 

        If e.KeyCode = Keys.Escape Then

            Me.Close()

        End If

    End Sub

 

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click

        Dim baris As Integer = DGV2.RowCount - 2

 

        DA = New OleDbDataAdapter("SELECT DISTINCT TBLPEMBAYARANDETAIL.ID_PEMBAYARAN,TBLBIAYADETAIL.NAMA_BIAYA,TBLPEMBAYARANDETAIL.JUMLAH_BAYAR,SISA FROM TBLPEMBAYARANDETAIL,TBLBiaya,TBLBIAYADETAIL,TBLPEMBAYARAN,TBLSISWA WHERE TBLPEMBAYARAN.ID_PEMBAYARAN=TBLPEMBAYARANDETAIL.ID_PEMBAYARAN AND TBLPEMBAYARANDETAIL.ID_BIAYA=TBLBIAYADETAIL.ID_BIAYA  AND TBLPEMBAYARAN.ID_SISWA LIKE '%" & TextBox1.Text & "%' and TBLPEMBAYARAN.ID_PEMBAYARAN ='" & DGV2.Rows(baris).Cells(0).Value & "' and tblpembayarandetail.sisa>0", Conn)

 

        DS = New DataSet

        DA.Fill(DS)

        DGV3.DataSource = DS.Tables(0)

        DGV3.ReadOnly = True

 

        DGV3.Columns(2).DefaultCellStyle.Format = "#,###"

        DGV3.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

 

        DGV3.Columns(3).DefaultCellStyle.Format = "#,###"

        DGV3.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

 

    End Sub

 

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click

        DA = New OleDbDataAdapter("SELECT DISTINCT TBLPEMBAYARANDETAIL.ID_PEMBAYARAN,TBLBIAYADETAIL.NAMA_BIAYA,TBLPEMBAYARANDETAIL.JUMLAH_BAYAR,SISA FROM TBLPEMBAYARANDETAIL,TBLBiaya,TBLBIAYADETAIL,TBLPEMBAYARAN,TBLSISWA WHERE TBLPEMBAYARAN.ID_PEMBAYARAN=TBLPEMBAYARANDETAIL.ID_PEMBAYARAN AND TBLPEMBAYARANDETAIL.ID_BIAYA=TBLBIAYADETAIL.ID_BIAYA  AND TBLPEMBAYARAN.ID_SISWA LIKE '%" & TextBox1.Text & "%'", Conn)

 

        DS = New DataSet

        DA.Fill(DS)

        DGV3.DataSource = DS.Tables(0)

        DGV3.ReadOnly = True

 

        DGV3.Columns(2).DefaultCellStyle.Format = "#,###"

        DGV3.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

 

        DGV3.Columns(3).DefaultCellStyle.Format = "#,###"

        DGV3.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

    End Sub

 

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

        Me.Close()

    End Sub

 

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

        Dim baris As Integer = DGV2.RowCount - 2

        Cetak.Show()

        Cetak.CRV.SelectionFormula = "{tblpembayaran.id_pembayaran} ='" & DGV2.Rows(baris).Cells(0).Value & "'"

        laporan.Load("cetak sisa biaya.rpt")

        Call SetingLaporan()

        Cetak.CRV.ReportSource = laporan

        Cetak.CRV.RefreshReport()

    End Sub

End Class

 

 

 

 

Imports System.Data.OleDb

Public Class LaporanSiswa

 

    Sub TampilKelas()

        'Call Koneksi()

        CMD = New OleDbCommand("select distinct kelas from tblsiswa", Conn)

        DR = CMD.ExecuteReader

        ComboBox1.Items.Clear()

        ComboBox3.Items.Clear()

        Do While DR.Read

            ComboBox1.Items.Add(DR.Item("kelas"))

            ComboBox3.Items.Add(DR.Item("kelas"))

        Loop

    End Sub

 

    Sub TampilJurusan()

        'Call Koneksi()

        CMD = New OleDbCommand("select distinct jurusan from tblsiswa", Conn)

        DR = CMD.ExecuteReader

        ComboBox1.Items.Clear()

        ComboBox3.Items.Clear()

        Do While DR.Read

            ComboBox5.Items.Add(DR.Item("jurusan"))

        Loop

    End Sub

 

    Sub TampilTahun()

        'Call Koneksi()

        CMD = New OleDbCommand("select distinct tahun_ajaran from tblsiswa", Conn)

        DR = CMD.ExecuteReader

        ComboBox2.Items.Clear()

        ComboBox4.Items.Clear()

        Do While DR.Read

            ComboBox2.Items.Add(DR.Item("tahun_ajaran"))

            ComboBox4.Items.Add(DR.Item("tahun_ajaran"))

        Loop

    End Sub

 

 

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

        Call Koneksi()

        Call TampilKelas()

        Call TampilTahun()

        Call TampilJurusan()

    End Sub

 

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

        laporan.Load("laporan siswa.rpt")

        Call SetingLaporan()

        CRV.ReportSource = laporan

        CRV.RefreshReport()

    End Sub

 

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged

 

        CRV.SelectionFormula = "{tblSISWA.KELAS}='" & ComboBox1.Text & "'"

        laporan.Load("laporan siswa.rpt")

        Call SetingLaporan()

        CRV.ReportSource = laporan

        CRV.RefreshReport()

 

    End Sub

 

    Private Sub ComboBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged

 

        CRV.SelectionFormula = "{tblSISWA.TAHUN_AJARAN}='" & ComboBox2.Text & "'"

        laporan.Load("laporan siswa.rpt")

        Call SetingLaporan()

        CRV.ReportSource = laporan

        CRV.RefreshReport()

 

    End Sub

 

    Private Sub ComboBox4_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox4.SelectedIndexChanged

 

        CRV.SelectionFormula = "{tblSISWA.KELAS}='" & ComboBox3.Text & "' AND  {tblSISWA.tahun_ajaran}='" & ComboBox4.Text & "'"

        laporan.Load("laporan siswa.rpt")

        Call SetingLaporan()

        CRV.ReportSource = laporan

        CRV.RefreshReport()

 

    End Sub

 

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

        If e.KeyChar = Chr(13) Then

            If TextBox1.Text <> "" Then

 

                CRV.SelectionFormula = "{tblSISWA.NAMA_SISWA} like '*" & TextBox1.Text & "*'"

                laporan.Load("laporan siswa.rpt")

                Call SetingLaporan()

                CRV.ReportSource = laporan

                CRV.RefreshReport()

 

            Else

                laporan.Load("laporan siswa.rpt")

                Call SetingLaporan()

                CRV.ReportSource = laporan

                CRV.RefreshReport()

            End If

            TextBox1.Focus()

        End If

    End Sub

 

   

    Private Sub ComboBox5_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox5.SelectedIndexChanged

        CRV.SelectionFormula = "{tblSISWA.jurusan}='" & ComboBox5.Text & "'"

        laporan.Load("laporan siswa.rpt")

        Call SetingLaporan()

        CRV.ReportSource = laporan

        CRV.RefreshReport()

 

    End Sub

End Class