APLIKASI RENTAL VCD - VB.NET

 

Tools :

  1. VB.Net 2008 (bisa upgrade ke VB.net 2010, 2012, 2013)
  2. Database access 2003 (bisa diubah ke sql server, mysql)

 

Tahapan Pembuatan Aplikasi :

 

  1. Membuat project dengan nama "Aplikasi Rental VCD"
  2. Membuat database dengan nama "Rental" dan tabel-tabel sepert gambar dibawah ini

 

 

  1. Membuat Relasi Tabel

 

  1. Membuat Module Koneksi

 

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 cryRpt As New ReportDocument

    Public crtableLogoninfos As New TableLogOnInfos

    Public crtableLogoninfo As New TableLogOnInfo

    Public crConnectionInfo As New ConnectionInfo

    Public CrTables As Tables

 

    Public Sub seting_laporan()

        With crConnectionInfo

            .ServerName = (Application.StartupPath.ToString & "\rental.mdb")

            .DatabaseName = (Application.StartupPath.ToString & "\rental.mdb")

            .UserID = ""

            .Password = ""

        End With

 

        CrTables = cryRpt.Database.Tables

        For Each CrTable In CrTables

            crtableLogoninfo = CrTable.LogOnInfo

            crtableLogoninfo.ConnectionInfo = crConnectionInfo

            CrTable.ApplyLogOnInfo(crtableLogoninfo)

        Next

    End Sub

 

    Public Sub Konekdb()

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

        conn.Open()

    End Sub

 

End Module

 

  1. Membuat Form Login

 

 

 

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

        cmd = New OleDbCommand("select * from petugas where nama_petugas='" & txtnama.Text & "' and pwd='" & txtpassword.Text & "'", conn)

        dr = cmd.ExecuteReader

        dr.Read()

        If dr.HasRows Then

            If txtpassword.Text <> dr.Item("pwd") Then

                MsgBox("password salah")

                txtpassword.Clear()

                txtpassword.Focus()

                Exit Sub

            End If

 

            Me.Visible = False

            MNUtama.Show()

 

            MNUtama.PanelKode.Text = dr.Item("id_petugas")

            MNUtama.PanelNama.Text = dr.Item("nama_petugas")

            MNUtama.PanelHakAkses.Text = UCase(dr.Item("hak_akses"))

 

            If MNUtama.PanelHakAkses.Text <> "ADMIN" Then

                MNUtama.Button1.Visible = False

                MNUtama.Button4.Visible = False

            End If

 

            cmd = New OleDbCommand("select * from pengaturan", conn)

            dr = cmd.ExecuteReader

            dr.Read()

            If dr.HasRows Then

                MNUtama.BatasFilm1.Text = dr.Item("batas_Film")

                MNUtama.BatasHari1.Text = dr.Item("batas_hari")

                MNUtama.DendaPerhari1.Text = dr.Item("denda_perhari")

                MNUtama.DendaRusak1.Text = dr.Item("denda_rusak")

            Else

                MsgBox("Segera isi pengaturan")

                Pengaturan.Show()

            End If

 

            cmd = New OleDbCommand("select * from lembaga", conn)

            dr = cmd.ExecuteReader

            dr.Read()

            If dr.HasRows Then

                MNUtama.IDLembaga.Text = dr.Item("id_lembaga")

            Else

                MsgBox("Segera isi profil lembaga")

                Lembaga.Show()

            End If

        Else

            MsgBox("Login gagal")

            txtnama.Clear()

            txtpassword.Clear()

            txtnama.Focus()

 

            Hitung = Hitung + 1

            If Hitung > 2 Then

                End

            End If

        End If

    End Sub

 

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

        Me.Close()

    End Sub

End Class

 

 

  1. Membuat Menu Utama

 

 

  1. Membuat Form Petugas

 

 

  1. Membuat Form Anggota

 

 

  1. Membuat Form Film

 

 

Imports System.Data.OleDb

 

Public Class Film

 

    Sub IDFilmOtomatis()

        cmd = New OleDbCommand("select id_Film from Film order by id_Film desc", conn)

        dr = cmd.ExecuteReader

        dr.Read()

        If Not dr.HasRows Then

            TextBox1.Text = "F0001"

        Else

            TextBox1.Text = "F" + Format(Microsoft.VisualBasic.Right(dr.Item("id_Film"), 4) + 1, "0000")

        End If

        TextBox1.Enabled = False

    End Sub

 

    Sub Kosongkan()

        On Error Resume Next

        TextBox2.Clear()

        ComboBox3.Text = ""

        TextBox3.Clear()

        ComboBox5.Text = ""

        TextBox4.Clear()

        TextBox5.Clear()

        TextBox5.Enabled = False

        PictureBox1.Load(TextBox5.Text)

        PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage

        TextBox6.Clear()

        TextBox2.Focus()

    End Sub

 

 

    Sub DataBaru()

        On Error Resume Next

        TextBox2.Clear()

        ComboBox3.Text = ""

        TextBox3.Clear()

        ComboBox5.Text = ""

        TextBox4.Clear()

        TextBox5.Clear()

        PictureBox1.Load(TextBox5.Text)

        PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage

        TextBox6.Clear()

        TextBox2.Focus()

    End Sub

 

    Sub Ketemu()

        On Error Resume Next

        TextBox2.Text = dr.Item(1)

        ComboBox3.Text = dr.Item(2)

        TextBox3.Text = dr.Item(3)

        ComboBox5.Text = dr.Item(4)

        DateTimePicker1.Text = dr.Item(5)

        TextBox4.Text = dr.Item(6)

        TextBox5.Text = dr.Item(7)

        PictureBox1.Load(TextBox5.Text)

        PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage

        TextBox6.Clear()

        TextBox2.Focus()

    End Sub

 

    Sub TampilKategori()

        cmd = New OleDbCommand("select distinct kategori from Film", conn)

        dr = cmd.ExecuteReader

        ComboBox3.Items.Clear()

        Do While dr.Read

            ComboBox3.Items.Add(dr.Item("kategori"))

        Loop

 

    End Sub

 

    Sub TampilLokasirak()

        cmd = New OleDbCommand("select distinct lokasi from Film", conn)

        dr = cmd.ExecuteReader

        ComboBox5.Items.Clear()

        Do While dr.Read

            ComboBox5.Items.Add(dr.Item("lokasi"))

        Loop

    End Sub

 

    Sub TampilGrid()

        da = New OleDbDataAdapter("select * from Film", conn)

        ds = New DataSet

        da.Fill(ds)

        DGV.DataSource = ds.Tables(0)

        DGV.ReadOnly = True

    End Sub

 

    Sub CariIdFilm()

        cmd = New OleDbCommand("select * from Film where id_Film='" & TextBox1.Text & "'", conn)

        dr = cmd.ExecuteReader

        dr.Read()

    End Sub

 

    Sub Awal()

        Call IDFilmOtomatis()

        Call Kosongkan()

        Call TampilGrid()

        Call TampilKategori()

        Call TampilLokasirak()

    End Sub

 

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

        Call Konekdb()

        Call Awal()

    End Sub

 

    Private Sub PictureBox1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PictureBox1.Click

        On Error Resume Next

        OpenFileDialog1.ShowDialog()

        TextBox5.Text = OpenFileDialog1.FileName

        PictureBox1.Load(TextBox5.Text)

        PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage

    End Sub

 

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

 

        Try

            Call CariIdFilm()

            If Not dr.HasRows Then

                Dim simpan As String = "insert into Film values('" & TextBox1.Text & "','" & TextBox2.Text & "','" & ComboBox3.Text & "','" & TextBox3.Text & "','" & ComboBox5.Text & "','" & DateTimePicker1.Text & "','" & TextBox4.Text & "','" & TextBox5.Text & "')"

                cmd = New OleDbCommand(simpan, conn)

                cmd.ExecuteNonQuery()

            Else

                Dim edit As String = "update Film set judul='" & TextBox2.Text & "',kategori='" & ComboBox3.Text & "',stok='" & TextBox3.Text & "',lokasi='" & ComboBox5.Text & "',Tanggal_masuk='" & DateTimePicker1.Text & "',harga_Film='" & TextBox4.Text & "',gambar='" & TextBox5.Text & "' where id_Film='" & TextBox1.Text & "'"

                cmd = New OleDbCommand(edit, conn)

                cmd.ExecuteNonQuery()

            End If

            Call Awal()

 

        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.Text = DGV.Rows(e.RowIndex).Cells(0).Value

        Call CariIdFilm()

        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

        Call CariIdFilm()

        If Not dr.HasRows Then

            MsgBox("ID Film tidak terdaftar")

            Exit Sub

        End If

 

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

            Dim hapus As String = "delete * from Film  where id_Film='" & TextBox1.Text & "'"

            cmd = New OleDbCommand(hapus, conn)

            cmd.ExecuteNonQuery()

            Call Awal()

        Else

            Call Kosongkan()

        End If

    End Sub

 

 

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

        Call Kosongkan()

        Call IDFilmOtomatis()

    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 TextBox6_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox6.TextChanged

        da = New OleDbDataAdapter("select * from Film where judul like '%" & TextBox6.Text & "%'", conn)

        ds = New DataSet

        da.Fill(ds)

        DGV.DataSource = ds.Tables(0)

        DGV.ReadOnly = True

    End Sub

 

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

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

            e.Handled = True

        End If

    End Sub

 

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

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

            e.Handled = True

        End If

    End Sub

 

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

        On Error Resume Next

        OpenFileDialog1.ShowDialog()

        TextBox5.Text = OpenFileDialog1.FileName

        PictureBox1.Load(TextBox5.Text)

        PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage

    End Sub

End Class

 

  1. Membuat Form Lembaga

 

 

  1. Membuat Form Pengaturan

 

 

Imports System.Data.OleDb

 

Public Class Pengaturan

 

    Sub IDPengaturanOtomatis()

        cmd = New OleDbCommand("select id_Pengaturan from Pengaturan order by id_Pengaturan desc", conn)

        dr = cmd.ExecuteReader

        dr.Read()

        If Not dr.HasRows Then

            TextBox1.Text = "1"

        Else

            TextBox1.Text = "1"

        End If

        TextBox1.Enabled = False

    End Sub

 

    Sub Kosongkan()

        On Error Resume Next

        TextBox2.Clear()

        TextBox3.Clear()

        TextBox4.Clear()

        TextBox5.Clear()

        TextBox2.Focus()

    End Sub

 

 

    Sub DataBaru()

        On Error Resume Next

        TextBox2.Clear()

        TextBox3.Clear()

        TextBox4.Clear()

        TextBox5.Clear()

        TextBox2.Focus()

    End Sub

 

    Sub Ketemu()

        On Error Resume Next

        TextBox2.Text = dr.Item(1)

        TextBox3.Text = dr.Item(2)

        TextBox4.Text = dr.Item(3)

        TextBox5.Text = dr.Item(4)

        TextBox2.Focus()

    End Sub

   

    Sub CariIdPengaturan()

        cmd = New OleDbCommand("select * from Pengaturan where id_Pengaturan='" & TextBox1.Text & "'", conn)

        dr = cmd.ExecuteReader

        dr.Read()

    End Sub

 

    Sub Awal()

        Call IDPengaturanOtomatis()

        Call Kosongkan()

    End Sub

 

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

        Call Konekdb()

        Call Awal()

        Call CariIdPengaturan()

        If dr.HasRows Then

            Call Ketemu()

        End If

    End Sub

 

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

 

        Try

            Call CariIdPengaturan()

            If Not dr.HasRows Then

                Dim simpan As String = "insert into Pengaturan values('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" & TextBox5.Text & "','" & MNUtama.PanelKode.Text & "')"

                cmd = New OleDbCommand(simpan, conn)

                cmd.ExecuteNonQuery()

            Else

 

                Dim edit As String = "update Pengaturan set BATAS_Film='" & TextBox2.Text & "',batas_hari='" & TextBox3.Text & "',denda_perhari='" & TextBox4.Text & "',denda_rusak='" & TextBox5.Text & "',id_petugas='" & MNUtama.PanelKode.Text & "' where id_Pengaturan='" & TextBox1.Text & "'"

                cmd = New OleDbCommand(edit, conn)

                cmd.ExecuteNonQuery()

            End If

            Call Awal()

 

            cmd = New OleDbCommand("select * from pengaturan", conn)

            dr = cmd.ExecuteReader

            dr.Read()

            If dr.HasRows Then

                MNUtama.BatasFilm1.Text = dr.Item("batas_Film")

                MNUtama.BatasHari1.Text = dr.Item("batas_hari")

                MNUtama.DendaPerhari1.Text = dr.Item("denda_perhari")

                MNUtama.DendaRusak1.Text = dr.Item("denda_rusak")

 

            End If

        Catch ex As Exception

            MsgBox(ex.Message)

            MsgBox("Harus login dulu")

            Login.Show()

        End Try

    End Sub

 

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

        Call Kosongkan()

        Call IDPengaturanOtomatis()

    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_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox2.KeyPress

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

            e.Handled = True

        End If

    End Sub

 

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

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

            e.Handled = True

        End If

    End Sub

 

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

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

            e.Handled = True

        End If

    End Sub

 

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

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

            e.Handled = True

        End If

    End Sub

End Class

 

  1. Membuat Form Peminjaman

 

 

Imports System.Data.OleDb

 

Public Class Peminjaman

 

    'id pinjaman otomatis dengan pola thn-bln-tgl+ nomor urut

    Sub IDPinjamOtomatis()

        cmd = New OleDbCommand("select ID_pinjam from pinjam order by id_pinjam desc", conn)

        dr = cmd.ExecuteReader

        dr.Read()

        If Not dr.HasRows Then

            LBLIdPinjaman.Text = "PJ" + Format(Today, "yyMMdd") + "01"

        Else

            If Microsoft.VisualBasic.Mid(dr.Item("id_pinjam"), 3, 6) = Format(Today, "yyMMdd") Then

                LBLIdPinjaman.Text = "PJ" + Format(Microsoft.VisualBasic.Right(dr.Item("id_pinjam"), 8) + 1, "00")

            Else

                LBLIdPinjaman.Text = "PJ" + Format(Today, "yyMMdd") + "01"

            End If

        End If

    End Sub

 

    'menampilkan id anggota di combobox

    Sub TampilAnggota()

        cmd = New OleDbCommand("select ID_anggota from anggota", conn)

        dr = cmd.ExecuteReader

        Do While dr.Read

            ComboBox1.Items.Add(dr.Item("ID_anggota"))

        Loop

    End Sub

 

    'menampilkan data Film di sebelah kanan

    Sub TampilFilm()

        da = New OleDbDataAdapter("select * from Film where stok>0", conn)

        ds = New DataSet

        da.Fill(ds)

        DGV3.DataSource = ds.Tables(0)

        DGV3.ReadOnly = True

    End Sub

 

    Sub Kosongkan()

        On Error Resume Next

        DGV3.Enabled = True

        ComboBox1.Text = ""

        LBLNama.Text = ""

        LBLPinjamSekarang.Text = 0

        LBLPernahPinjam.Text = 0

        LBLTotalPinjam.Text = 0

        DGV1.Rows.Clear()

        DGV2.Columns.Clear()

        TextBox1.Clear()

 

        PictureBox2.Load(TextBox1.Text)

        PictureBox2.SizeMode = PictureBoxSizeMode.StretchImage

    End Sub

 

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

        Call Kosongkan()

        Call Konekdb()

        Call TampilFilm()

        Call IDPinjamOtomatis()

        LBLtanggal.Text = Format(Today, "dd/MMMM/yyyy")

        Call TampilAnggota()

        DGV1.ReadOnly = True

    End Sub

 

    Private Sub TextBox2_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox2.TextChanged

 

        'mencari judul Film di bawah kanan

        da = New OleDbDataAdapter("select * from Film where judul like '%" & TextBox2.Text & "%' and stok>0", conn)

        ds = New DataSet

        da.Fill(ds)

        DGV3.DataSource = ds.Tables(0)

        DGV3.ReadOnly = True

    End Sub

 

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

        If ComboBox1.Text = "" Then

            MsgBox("pilih atau Isi id anggota terlebih dahulu...!")

            Exit Sub

        End If

        TextBox2.Clear()

        Dim baris1 As Integer = DGV1.RowCount - 1

        'Dim baris3 As Integer = DGV3.RowCount - 1

 

        Dim kode As String = DGV3.Rows(e.RowIndex).Cells(0).Value

 

        'mencegah pinjaman Film2x ketika masih dipinjam

        cmd = New OleDbCommand("select id_Film from pinjamdetail,pinjam, anggota where pinjam.id_pinjam=pinjamdetail.id_pinjam and pinjam.id_anggota=anggota.id_anggota and pinjamdetail.id_Film='" & kode & "' and pinjam.id_anggota='" & ComboBox1.Text & "' and pinjamdetail.keterangan='Dipinjam'", conn)

        dr = cmd.ExecuteReader

        dr.Read()

        If dr.HasRows Then

            MsgBox("Film sedang dipinjam")

            Exit Sub

        End If

 

        'menampilkan id Film, judul dan pengarang yang dipinjam dari grid sebelah kanan

        cmd = New OleDbCommand("select Judul from Film where id_Film='" & DGV3.Rows(e.RowIndex).Cells(0).Value & "'", conn)

        dr = cmd.ExecuteReader

        dr.Read()

        If dr.HasRows Then

            DGV1.Rows.Add(DGV3.Rows(e.RowIndex).Cells(0).Value, DGV3.Rows(e.RowIndex).Cells(1).Value)

            'mencegah peminjaman Film yang sama

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

                For barisbawah As Integer = barisatas + 1 To DGV1.RowCount - 1

                    If DGV1.Rows(barisbawah).Cells(0).Value = DGV1.Rows(barisatas).Cells(0).Value Then

                        MsgBox("Film sudah dalam transaksi peminjaman")

                        DGV1.Rows.RemoveAt(barisbawah)

                        Exit Sub

                    End If

                Next

            Next

        End If

 

        'menampilkan gambar Film dari lokasi hardisk

        cmd = New OleDbCommand("select gambar from Film where id_Film='" & DGV1.Rows(baris1).Cells(0).Value & "'", conn)

        dr = cmd.ExecuteReader

        dr.Read()

        If dr.HasRows Then

            TextBox1.Text = dr.Item("gambar")

            PictureBox2.Load(TextBox1.Text)

            PictureBox2.SizeMode = PictureBoxSizeMode.Zoom

        End If

        'menghitung jumlah Film yang dipinjam dari grid atas kanan

        LBLPinjamSekarang.Text = DGV1.RowCount - 1

        'menghitung total pinjaman sekarang dan yang lalu

        LBLTotalPinjam.Text = Val(LBLPernahPinjam.Text) + Val(LBLPinjamSekarang.Text)

 

        'menampilkan batas jumlah pinjaman

        If LBLTotalPinjam.Text >= MNUtama.BatasFilm1.Text Then

            MsgBox("pinjaman sudah maksimal")

            DGV3.Enabled = False

        End If

 

    End Sub

 

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

        DGV3.Enabled = True

        cmd = New OleDbCommand("select Nama_anggota from anggota where id_anggota='" & ComboBox1.Text & "'", conn)

        dr = cmd.ExecuteReader

        dr.Read()

        If dr.HasRows Then

            LBLNama.Text = dr.Item("nama_anggota")

        End If

 

        'menampilkan data Film yang pernah dan sedang dipinjam oleh anggota

        da = New OleDbDataAdapter("select pinjamdetail.ID_Pinjam,pinjamdetail.ID_Film,Film.Judul from pinjamdetail,pinjam,Film where pinjamdetail.id_pinjam=pinjam.id_pinjam and Film.id_Film=pinjamdetail.id_Film and pinjam.id_anggota='" & ComboBox1.Text & "' and pinjamdetail.keterangan='Dipinjam'", conn)

 

        ds = New DataSet

        da.Fill(ds)

        DGV2.DataSource = ds.Tables(0)

        DGV2.ReadOnly = True

        DGV2.Columns(0).HeaderText = "ID Pinjam"

        DGV2.Columns(1).HeaderText = "ID Film"

        DGV2.Columns(2).HeaderText = "Judul"

        LBLPernahPinjam.Text = DGV2.RowCount - 1

 

        'menampilkan batas jumlah pinjaman

        If LBLPernahPinjam.Text >= MNUtama.BatasFilm1.Text Then

            MsgBox("pinjaman sudah maksimal")

            DGV3.Enabled = False

        End If

    End Sub

 

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

        Call Kosongkan()

 

    End Sub

 

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

        Me.Close()

    End Sub

 

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

        'menghapus data dengan tombol delete atau escape

        On Error Resume Next

        If e.KeyCode = Keys.Delete Or e.KeyCode = Keys.Escape Then

            DGV1.Rows.Remove(DGV1.CurrentRow)

            LBLPinjamSekarang.Text = DGV1.RowCount - 1

            LBLTotalPinjam.Text = Val(LBLPernahPinjam.Text) + Val(LBLPinjamSekarang.Text)

            DGV3.Enabled = True

        End If

    End Sub

End Class

 

  1. Membuat Form Pengembalian

 

 

Imports System.Data.OleDb

 

Public Class Pengembalian

 

    Sub Kosongkan()

        On Error Resume Next

        ComboBox1.Text = ""

        LBLIDAnggota.Text = ""

        LBLNama.Text = ""

        LBLTotalPinjam.Text = 0

        LBLTotalDenda.Text = 0

        txtdibayar.Text = 0

        LBLKembali.Text = 0

        DGV1.Rows.Clear()

        DGV2.Columns.Clear()

        TextBox1.Clear()

        PictureBox1.Load(TextBox1.Text)

        PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage

    End Sub

 

 

    Sub IDKembaliOtomatis()

        cmd = New OleDbCommand("select ID_kembali from kembali order by id_kembali desc", conn)

        dr = cmd.ExecuteReader

        dr.Read()

        If Not dr.HasRows Then

            LBLIDKembali.Text = "KB" + Format(Today, "yyMMdd") + "01"

        Else

            If Microsoft.VisualBasic.Mid(dr.Item("id_kembali"), 3, 6) = Format(Today, "yyMMdd") Then

                LBLIDKembali.Text = "KB" + Format(Microsoft.VisualBasic.Right(dr.Item("id_kembali"), 8) + 1, "00")

            Else

                LBLIDKembali.Text = "KB" + Format(Today, "yyMMdd") + "01"

            End If

        End If

    End Sub

 

    'menampilkan id pinjam yang keterangannya "Dipinjam'

    Sub TampilIDPinjam()

        cmd = New OleDbCommand("select distinct ID_pinjam from pinjamdetail where keterangan='Dipinjam'", conn)

        dr = cmd.ExecuteReader

        ComboBox1.Items.Clear()

        Do While dr.Read

            ComboBox1.Items.Add(dr.Item("ID_Pinjam"))

        Loop

    End Sub

 

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

        Call Konekdb()

        Call IDKembaliOtomatis()

        LBLtanggal.Text = Format(Today, "dd/MMM/yyyy")

        Call TampilIDPinjam()

        Call Kosongkan()

 

        DGV1.Columns(0).ReadOnly = True

        DGV1.Columns(1).ReadOnly = True

        DGV1.Columns(2).ReadOnly = True

        DGV1.Columns(3).ReadOnly = True

        DGV1.Columns(4).ReadOnly = True

        DGV1.Columns(7).ReadOnly = True

    End Sub

 

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

        'bersihkan grid transaksi dan cari identitas anggota

        DGV1.Rows.Clear()

        'Call Pilihan()

        cmd = New OleDbCommand("select anggota.id_anggota,anggota.Nama_anggota from anggota,pinjam,pinjamdetail where pinjam.id_anggota=anggota.id_anggota and pinjam.id_pinjam='" & ComboBox1.Text & "'", conn)

        dr = cmd.ExecuteReader

        dr.Read()

        If dr.HasRows Then

            LBLIDAnggota.Text = dr.Item("id_anggota")

            LBLNama.Text = dr.Item("nama_anggota")

        End If

 

        'menampilkan data Film yang akan dikembalikan

        da = New OleDbDataAdapter("select pinjamdetail.id_Film,Film.judul from pinjamdetail,pinjam,Film where pinjamdetail.id_pinjam=pinjam.id_pinjam and Film.id_Film=pinjamdetail.id_Film and pinjam.id_pinjam='" & ComboBox1.Text & "'  and pinjamdetail.keterangan='Dipinjam'", conn)

 

        ds = New DataSet

        da.Fill(ds)

        DGV2.DataSource = ds.Tables(0)

        DGV2.ReadOnly = True

        DGV2.Columns(0).HeaderText = "ID Film"

        DGV2.Columns(1).HeaderText = "Judul"

        LBLTotalPinjam.Text = DGV2.RowCount - 1

    End Sub

 

    Sub HitungDenda()

        Dim hitung As Double

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

            hitung = hitung + DGV1.Rows(baris).Cells(7).Value

        Next

        LBLTotalDenda.Text = hitung

 

        If LBLTotalDenda.Text = 0 Then

            LBLTotalDenda.Text = 0

            txtdibayar.Text = 0

            LBLKembali.Text = 0

        Else

 

        End If

    End Sub

 

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

        'denda rusak

        If e.ColumnIndex = 5 Then

            If DGV1.Rows(e.RowIndex).Cells(5).Value = "Ya" Then

                DGV1.Rows(e.RowIndex).Cells(6).Value = "Tidak"

                DGV1.Rows(e.RowIndex).Cells(7).Value = Val(MNUtama.DendaPerhari1.Text) * (DGV1.Rows(e.RowIndex).Cells(4).Value) + (MNUtama.DendaRusak1.Text)

            Else

                DGV1.Rows(e.RowIndex).Cells(7).Value = 0

            End If

        End If

 

        'denda hilang

        If e.ColumnIndex = 6 Then

            If DGV1.Rows(e.RowIndex).Cells(6).Value = "Ya" Then

                DGV1.Rows(e.RowIndex).Cells(5).Value = "Tidak"

                cmd = New OleDbCommand("select * from Film where id_Film='" & DGV1.Rows(e.RowIndex).Cells(0).Value & "'", conn)

                dr = cmd.ExecuteReader

                dr.Read()

                If dr.HasRows Then

                    DGV1.Rows(e.RowIndex).Cells(7).Value = Val(MNUtama.DendaPerhari1.Text) * (DGV1.Rows(e.RowIndex).Cells(4).Value) + dr.Item("harga_Film")

                End If

            Else

                DGV1.Rows(e.RowIndex).Cells(7).Value = 0

            End If

        End If

 

 

        Call HitungDenda()

    End Sub

 

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

 

        Dim baris As Integer = DGV1.RowCount - 1

        cmd = New OleDbCommand("select tanggal_pinjam from pinjam,pinjamdetail where pinjam.id_pinjam='" & ComboBox1.Text & "' and pinjamdetail.id_Film='" & DGV2.Rows(e.RowIndex).Cells(0).Value & "'", conn)

        dr = cmd.ExecuteReader

        dr.Read()

        If dr.HasRows Then

            'masukkan data Film ke dalam grid pengembalian

            DGV1.Rows.Add(DGV2.Rows(e.RowIndex).Cells(0).Value, DGV2.Rows(e.RowIndex).Cells(1).Value, Format(DateValue(dr.Item("tanggal_pinjam")), "dd/MMM/yyyy"), "", "", "Tidak", "Tidak")

 

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

                For barisbawah As Integer = barisatas + 1 To DGV1.RowCount - 1

                    If DGV1.Rows(barisbawah).Cells(0).Value = DGV1.Rows(barisatas).Cells(0).Value Then

                        MsgBox("Film sedang dalam proses pengembalian")

                        DGV1.Rows.RemoveAt(barisbawah)

                        Exit Sub

                    End If

                Next

            Next

            'hitug lama pinjam

            If DGV1.Rows(baris).Cells(2).Value = Today Then

                DGV1.Rows(baris).Cells(3).Value = 1

            Else

                DGV1.Rows(baris).Cells(3).Value = DateDiff(DateInterval.Day, DGV1.Rows(baris).Cells(2).Value, Today())

            End If

 

            'batas pinjam misal 7 hari. jika lama pinjam lebih dari 7 hari maka....

            'jika lama pinjam lebih besar dari 7 - 7 maka ditemukan lama keterlambatannya

            'jika kurang dari 7 maka terlambat =0 dan denda =0

            If DGV1.Rows(baris).Cells(3).Value <= Val(MNUtama.BatasFilm1.Text) Then

                DGV1.Rows(baris).Cells(4).Value = 0

                DGV1.Rows(baris).Cells(7).Value = 0

            Else

                DGV1.Rows(baris).Cells(4).Value = DGV1.Rows(baris).Cells(3).Value - Val(MNUtama.BatasHari1.Text)

                DGV1.Rows(baris).Cells(7).Value = Val(MNUtama.DendaPerhari1.Text) * DGV1.Rows(baris).Cells(4).Value

            End If

        Else

            MsgBox("Film sedang dalam proses pengembalian")

        End If

 

        'cari gambar Film yang dikembalikan

        cmd = New OleDbCommand("select gambar from Film where id_Film='" & DGV1.Rows(baris).Cells(0).Value & "'", conn)

        dr = cmd.ExecuteReader

        dr.Read()

        If dr.HasRows Then

            On Error Resume Next

            TextBox1.Text = dr.Item("gambar")

            PictureBox1.Load(TextBox1.Text)

            PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage

        End If

        Call HitungDenda()

    End Sub

 

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

        Me.Close()

    End Sub

 

 

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

        Call Kosongkan()

    End Sub

 

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

        'proses pembayaran denda

        If e.KeyCode = Keys.Enter Then

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

                MsgBox("pembayaran kurang")

                Exit Sub

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

                LBLKembali.Text = Val(txtdibayar.Text) - Val(LBLTotalDenda.Text)

                BTNSimpan.Focus()

            End If

        End If

    End Sub

  

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

        'hapus data jika ingin dibatalkan dengan esc atau delete

        On Error Resume Next

        If e.KeyCode = Keys.Delete Or e.KeyCode = Keys.Escape Then

            DGV1.Rows.Remove(DGV1.CurrentRow)

            LBLTotalPinjam.Text = DGV1.RowCount - 1

            Call HitungDenda()

            txtdibayar.Text = 0

            LBLKembali.Text = 0

        End If

    End Sub

End Class

 

  1. Membuat Form Histori

 

 

Imports System.Data.OleDb

 

Public Class Histori

 

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

        If e.KeyCode = Keys.Escape Then

            Me.Close()

        End If

    End Sub

 

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

        If e.KeyCode = Keys.Escape Then

            Me.Close()

        End If

    End Sub

 

    Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged

        Call Konekdb()

        da = New OleDbDataAdapter("select DISTINCT pinjam.ID_Pinjam,pinjam.Tanggal_Pinjam, Film.Judul,Pinjamdetail.Keterangan from pinjam,Film,pinjamdetail,ANGGOTA WHERE PINJAM.ID_PINJAM=PINJAMDETAIL.ID_PINJAM AND Film.ID_Film=PINJAMDETAIL.ID_Film AND ANGGOTA.ID_ANGGOTA=PINJAM.ID_ANGGOTA AND ANGGOTA.NAMA_ANGGOTA like '%" & TextBox1.Text & "%'", conn)

 

        ds = New DataSet

        da.Fill(ds)

        DGVPinjam.DataSource = ds.Tables(0)

        DGVPinjam.ReadOnly = True

 

        da = New OleDbDataAdapter("select DISTINCT Kembali.ID_Kembali,Kembali.Tanggal_Kembali, Film.Judul,Kembalidetail.Lama_Pinjam,kembalidetail.Terlambat,kembalidetail.Rusak,kembalidetail.Hilang,kembalidetail.Denda from anggota,Kembali,Film,Kembalidetail,pinjam WHERE Kembali.ID_Kembali=KembaliDETAIL.ID_Kembali AND Film.ID_Film=KembaliDETAIL.ID_Film AND ANGGOTA.ID_ANGGOTA=pinjam.ID_ANGGOTA AND pinjam.id_pinjam=kembali.id_pinjam and ANGGOTA.NAMA_ANGGOTA like '%" & TextBox1.Text & "%'", conn)

        ds = New DataSet

        da.Fill(ds)

        DGVKembali.DataSource = ds.Tables(0)

        DGVKembali.ReadOnly = True

    End Sub

 

    Private Sub TextBox2_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox2.TextChanged

        Call Konekdb()

        da = New OleDbDataAdapter("select DISTINCT pinjam.ID_Pinjam,pinjam.Tanggal_Pinjam, Film.Judul,pinjamdetail.Keterangan from pinjam,Film,pinjamdetail,ANGGOTA WHERE PINJAM.ID_PINJAM=PINJAMDETAIL.ID_PINJAM AND Film.ID_Film=PINJAMDETAIL.ID_Film AND ANGGOTA.ID_ANGGOTA=PINJAM.ID_ANGGOTA AND pinjam.id_pinjam like '%" & TextBox2.Text & "%'", conn)

 

        ds = New DataSet

        da.Fill(ds)

        DGVPinjam.DataSource = ds.Tables(0)

        DGVPinjam.ReadOnly = True

 

        da = New OleDbDataAdapter("select DISTINCT Kembali.ID_Kembali,Kembali.Tanggal_Kembali, Film.Judul,Kembalidetail.Lama_Pinjam,kembalidetail.Terlambat,kembalidetail.Rusak,kembalidetail.Hilang,kembalidetail.Denda from anggota,Kembali,Film,Kembalidetail,pinjam WHERE Kembali.ID_Kembali=KembaliDETAIL.ID_Kembali AND Film.ID_Film=KembaliDETAIL.ID_Film AND ANGGOTA.ID_ANGGOTA=pinjam.ID_ANGGOTA AND pinjam.id_pinjam=kembali.id_pinjam and pinjam.id_pinjam like '%" & TextBox2.Text & "%'", conn)

        ds = New DataSet

        da.Fill(ds)

        DGVKembali.DataSource = ds.Tables(0)

        DGVKembali.ReadOnly = True

    End Sub

End Class

 

  1. Membuat Laporan Peminjaman

a.      Harian

b.      Mingguan

c.       Bulanan grafik

 

 

  1. Membuat Form Pengembalian

a.      Harian

b.      Mingguan

c.       Bulanan

d.      Grafik