70
Lampiran 1
71
Lampiran 2
72
Lampiran 3 Relasi Tabel 1
Tb_Stok Pk
Tb_penjualan
n
Kode_barang
No_nota
Nama_barang Jumlah
Tanggal
n Fk
Kode_barang
Satuan
Nama_barang
Harga_jual
Jumlah
Tanggal_update
Satuan
Jumlah_minimal
Harga Total Pk
Id_jual
73
Relasi Tabel 2
Tb_pembelian
Tb_stok
n
No_urut
PK
No_nota Tanggal
Nama_barang Jumlah
n
FK Kode_barang
Satuan
Nama_barang
Harga_jual
Jumlah
Tanggal_update
Harga Satuan
Kode_barang
Jumlah_minimal n
FK Kode_supplier Nama_supplier Total PK Id_beli Tb_supplier PK 1
Kode_supplier Nama_supplier Alamat No_telp Note
74
Relasi Tabel 3
Tb_ABC PK
Kode_barang
FK
Volume_bulanan
FK
Biaya_unit Vol_rup_bul Persentase_vol_rup_bul
Tb_penjualan No_nota n Tanggal Kode_barang n
Nama_barang n
Jumlah
Kelas
Satuan Harga Total PK
Id_jual Tb_stok
PK Kode_barang Nama_barang Jumlah Satuan n Harga_jual Tanggal_update Jumlah_minimal
75
Relasi Tabel 4
Tb_laba_kotor PK
Tb_ABC
Kode_barang
PK
n FK
Kelas
Volume_bulanan
Jumlah_stok
Biaya_unit
Stok_awal
Vol_rup_bul
Pembelian
Persentase_vol_rup_bul
Barang_tersedia_dijual Stok_akhir HPP Total_penjualan Laba_kotor
Kode_barang
1
Kelas
76
Relasi Tabel 5
Tb_pembelian
Tb_laba_kotor PK
Kode_barang
No_urut
Kelas
No_nota n
FK
Jumlah_stok
Tanggal
Stok_awal
Kode_barang n
FK
Pembelian
Nama_barang 1
Barang_tersedia_dijual
Jumlah
Stok_akhir
Harga
HPP
Satuan
Total_penjualan
Kode_supplier
Laba_kotor
Nama_supplier 1
Total PK
Id_beli
77
Relasi Tabel 6
Tb_penjualan
Tb_laba_kotor PK
PK
Kode_barang Kelas
Tanggal
Jumlah_stok
Kode_barang
Stok_awal
Nama_barang
Pembelian
Jumlah
Barang_tersedia_dijual
Satuan
Stok_akhir
Harga n
FK
HPP
Total
Total_penjualan
Id_jual
Laba_kotor
No_nota
n
78
Relasi Tabel 7
Tb_logfile
Tb_laba_kotor PK
PK
Kode_barang
Id
Kelas
No_nota
Jumlah_stok
Tanggal n
FK
Kode_barang
Stok_awal 1
Pembelian
Transaksi
Barang_tersedia_dijual
Kode_supplier
Stok_akhir
Jumlah
HPP
Harga
Total_penjualan
Total
Laba_kotor
Jumlah_stok
79
Lampiran 4 Coding Login Private Sub cmdCancel_Click() Unload Me End Sub Private Sub cmdOK_Click() If txtUserName.Text = "" Or txtPassword.Text = "" Then MsgBox "Username dan Password harus diisi.!", vbCritical, "xx" Exit Sub Else Adodc1.RecordSource = "select * from tb_user where username = '" + txtUserName + "'" Adodc1.Refresh If Adodc1.Recordset.EOF Then MsgBox "Username salah.!", vbCritical, "xx" Exit Sub ElseIf Not Adodc1.Recordset!Password = txtPassword Then MsgBox "Password salah.!", vbCritical, "xx" Exit Sub Else xuser = Adodc1.Recordset!UserName xstat = Adodc1.Recordset!Status MsgBox "Selamat datang.!" & vbCrLf & _ "Anda telah login sebagai " + xstat + " .", vbInformation, "vv" menuutama.Lbuser.Caption = xuser menuutama.Lbstatus.Caption = xstat menuutama.mndata.Enabled = True Unload Me End If End If End Sub
Coding Form Stok Barang Dim pesan, proses As String Sub TXT(a As Boolean, b As Boolean) Txtkode.Enabled = a Txtnama.Enabled = a Txtjumlah.Enabled = b Cbosatuan.Enabled = a Txtharga.Enabled = a Txtmin.Enabled = a End Sub Sub CMD(h As Boolean, j As Boolean) Cmdupdate.Enabled = h Cmddelete.Enabled = h Cmdsave.Enabled = j End Sub
80
Sub KOSONG() Txtkode.Text = "" Txtnama.Text = "" Txtjumlah.Text = "" Cbosatuan.Text = "" Txtharga.Text = "" Txtmin.Text = "" End Sub Private Sub Cmdcetak_Click() On Error Resume Next 'Adodc1.RecordSource = "select * from tb_stok" 'Adodc1.Refresh With Adodc1.Recordset Printer.FontSize = 14 Printer.FontBold = True Printer.Print "LAPORAN STOK BARANG" Printer.FontSize = 10 Printer.Print Tab(1); "Periode waktu : "; Printer.Print Tab(25); Format(Date) Printer.FontBold = False Printer.Print "---------------------------------------------------------------------------------------------" & _ "---------------------------------------------------------------------------------------------------------------" Printer.Print Tab(1); "Kode Barang"; Printer.Print Tab(20); "Nama Barang"; Printer.Print Tab(50); "Saldo"; Printer.Print Tab(65); "Min Saldo"; Printer.Print Tab(80); "Satuan"; Printer.Print Tab(95); "Tanggal Update" Printer.Print "---------------------------------------------------------------------------------------------" & _ "---------------------------------------------------------------------------------------------------------------" Do While Not .EOF Printer.Print Tab(1); !kode_barang; Printer.Print Tab(20); !nama_barang; Printer.Print Tab(50); !jumlah; Printer.Print Tab(65); !jumlah_minimal; Printer.Print Tab(80); !satuan; Printer.Print Tab(95); !tanggal_update .MoveNext Loop .MoveFirst Printer.Print "---------------------------------------------------------------------------------------------" & _ "---------------------------------------------------------------------------------------------------------------" End With End Sub Private Sub Cmddelete_Click() proses = "delete" Call UPDEL End Sub Private Sub cmdexit_Click()
81
Unload Me End Sub Private Sub Cmdinsert_Click() If Cmdinsert.Caption = "Baru" Then proses = "insert" Cmdinsert.Caption = "Batal" Call CMD(False, True) Call TXT(True, True) Txtkode.SetFocus ElseIf Cmdinsert.Caption = "Batal" Then Cmdinsert.Caption = "Baru" Call KOSONG Call CMD(True, False) Call TXT(False, False) Adodc1.RecordSource = "select * from tb_stok" Adodc1.Refresh End If End Sub Private Sub Cmdsave_Click() If Txtkode.Text = "" Or Txtnama.Text = "" Or Txtjumlah.Text = "" Or Cbosatuan.Text = "" Or Txtharga.Text = "" Or Txtmin.Text = "" Then MsgBox "Data belum lengkap.!", vbCritical, "xx" Exit Sub Else If proses = "insert" Then Adodc1.RecordSource = "select * from tb_stok where kode_barang = '" + Txtkode + "'" Adodc1.Refresh If Not Adodc1.Recordset.EOF Then MsgBox "Data sudah ada.!", vbCritical, "xx" Exit Sub Else Adodc1.RecordSource = "select * from tb_stok" Adodc1.Refresh Adodc1.Recordset.AddNew End If ElseIf proses = "update" Then Adodc1.RecordSource = "select * from tb_stok where kode_barang = '" + Txtkode + "'" Adodc1.Refresh End If With Adodc1.Recordset !kode_barang = Txtkode !nama_barang = Txtnama !jumlah = Val(Txtjumlah) !satuan = Cbosatuan !harga_jual = Val(Txtharga) !tanggal_update = Dtanggal.Value !jumlah_minimal = Val(Txtmin) .Update End With MsgBox "Data telah disimpan.!", vbInformation, "vv" End If Call LOGFILE Adodc1.RecordSource = "select * from tb_stok"
82
Adodc1.Refresh Call KOSONG Cmdinsert.Caption = "Baru" Call CMD(True, False) Call TXT(False, False) End Sub Private Sub Cmdupdate_Click() proses = "update" Call UPDEL End Sub Private Sub Form_Load() Dtanggal.Value = Format(Date) Call TXT(False, False) Call CMD(True, False) If menuutama.Lbuser.Caption = "admin" Then Frlock.Visible = False Else Frlock.Visible = True End If End Sub Private Sub Form_Unload(Cancel As Integer) menuutama.mndata.Enabled = True menuutama.mnlogout.Enabled = True End Sub Sub UPDEL() pesan = InputBox("Masukkan kode barang untuk melakukan proses.!") If pesan = "" Then Exit Sub Else Adodc1.RecordSource = "select * from tb_stok where kode_barang = '" + pesan + "'" Adodc1.Refresh If Not Adodc1.Recordset.EOF Then With Adodc1.Recordset Txtkode.Text = !kode_barang Txtnama.Text = !nama_barang Txtjumlah.Text = !jumlah Cbosatuan.Text = !satuan Txtharga.Text = !harga_jual Txtmin.Text = !jumlah_minimal End With If proses = "update" Then Call TXT(True, False) Call CMD(False, True) Cmdinsert.Caption = "Batal" Txtkode.SetFocus ElseIf proses = "delete" Then If MsgBox("Anda yakin ingin menghapus data barang tersebut.?", vbYesNo + vbQuestion, "hmm") = vbYes Then Adodc1.Recordset.Delete MsgBox "Data barang telah dihapus.!", vbInformation, "vv" Call KOSONG Adodc1.RecordSource = "select * from tb_stok"
83
Adodc1.Refresh End If End If Else MsgBox "Data tidak ditemukan.!", vbCritical, "xx" Adodc1.RecordSource = "select * from tb_stok" Adodc1.Refresh End If End If End Sub Sub LOGFILE() Dim awalx As String If proses = "insert" Then awalx = "Stok Awal" Else awalx = "-" End If adoLog.RecordSource = "select * from tb_logfile" adoLog.Refresh adoLog.Recordset.AddNew With adoLog.Recordset !no_nota = "-" !tanggal = Dtanggal.Value !kode_barang = Txtkode !transaksi = awalx !kode_supplier = "-" !jumlah = Val(Txtjumlah) !harga = Val(Txtharga) !total = Val(Txtjumlah) * Val(Txtharga) !jumlah_stok = Val(Txtjumlah) .Update End With End Sub Private Sub Txtjumlah_KeyPress(KeyAscii As Integer) If Not KeyAscii < 48 And KeyAscii > 57 Then MsgBox "Input salah.", vbCritical, "xx" KeyAscii = 0 End If End Sub
Coding Analisis ABC Dim VolBul, TotVolBul, VolRupBul, TotVolRupBul, SenVolRupBul, totkel As Double Dim KodBar, Kel As String Private Sub Command1_Click() On Error GoTo salahtanggal 'utk mencari volume bulanan, total volume bulanan, volume rupiah bulanan, dan total volume bulanan TotVolBul = 0 TotVolRupBul = 0 'hapus record yg ada Adodc3.RecordSource = "select * from tb_abc"
84
Adodc3.Refresh Do While Not Adodc3.Recordset.EOF Adodc3.Recordset.Delete Adodc3.Recordset.MoveNext Loop Adodc1.RecordSource = "select * from tb_stok" Adodc1.Refresh Do While Not Adodc1.Recordset.EOF VolBul = 0 VolRupBul = 0 KodBar = Adodc1.Recordset!kode_barang Adodc2.RecordSource = "select * from tb_penjualan where tanggal >= '" + Format(Dt1, "yyyy-mm-dd") + "' and tanggal <= '" + Format(Dt2, "yyyy-mm-dd") + "'" Adodc2.Refresh If Format(Dt1, "yyyy-mm-dd") > Format(Dt2, "yyyy-mm-dd") Then MsgBox "Input tanggal awal dan akhir salah.!", vbCritical, "xx" Exit Sub End If Adodc2.Recordset.Filter = "kode_barang like '*" + KodBar + "*'" Do While Not Adodc2.Recordset.EOF VolBul = VolBul + Val(Adodc2.Recordset!jumlah) TotVolBul = TotVolBul + VolBul Adodc2.Recordset.MoveNext Loop VolRupBul = Val(VolBul) * Val(Adodc1.Recordset!harga_jual) TotVolRupBul = TotVolRupBul + VolRupBul Adodc3.RecordSource = "select * from tb_abc" Adodc3.Refresh Adodc3.Recordset.AddNew With Adodc3.Recordset !kode_barang = Adodc1.Recordset!kode_barang !volume_bulanan = VolBul !biaya_unit = Adodc1.Recordset!harga_jual !vol_rup_bul = VolRupBul .Update End With Adodc1.Recordset.MoveNext Loop Adodc3.RecordSource = "select * from tb_abc" Adodc3.Refresh Do While Not Adodc3.Recordset.EOF SenVolRupBul = Format(Val(Adodc3.Recordset!vol_rup_bul) / TotVolRupBul, "0.000") If SenVolRupBul >= 0.2 Then Kel = "A" ElseIf SenVolRupBul > 0.05 And SenVolRupBul < 0.2 Then Kel = "B" Else Kel = "C" End If With Adodc3.Recordset
85
!persentase_vol_rup_bul = Format(Val(SenVolRupBul) * 100, "0.00") !kelas = Kel .Update End With Adodc3.Recordset.MoveNext Loop Adodc3.RecordSource = "select * from tb_abc where kelas = '" + "A" + "'" Adodc3.Refresh With Adodc3.Recordset totkel = 0 Do While Not .EOF totkel = totkel + Format(Val(!persentase_vol_rup_bul) / 100, "0.00") .MoveNext Loop grA.Height = 4455 * totkel grA.Top = 2400 + 4440 - grA.Height End With Adodc3.RecordSource = "select * from tb_abc where kelas = '" + "B" + "'" Adodc3.Refresh With Adodc3.Recordset totkel = 0 Do While Not .EOF totkel = totkel + Format(Val(!persentase_vol_rup_bul) / 100, "0.00") .MoveNext Loop grB.Height = 4455 * totkel grB.Top = 2400 + 4440 - grB.Height End With Adodc3.RecordSource = "select * from tb_abc where kelas = '" + "C" + "'" Adodc3.Refresh With Adodc3.Recordset totkel = 0 Do While Not .EOF totkel = totkel + Format(Val(!persentase_vol_rup_bul) / 100, "0.00") .MoveNext Loop grC.Height = 4455 * totkel grC.Top = 2400 + 4440 - grC.Height End With Adodc3.RecordSource = "select * from tb_abc" Adodc3.Refresh Exit Sub salahtanggal: MsgBox "Tanggal Transaksi Salah", vbCritical, "xx" Exit Sub End Sub Private Sub Command2_Click()
86
Unload Me End Sub Private Sub Form_Load() Dt1.Value = Format(Date) Dt2.Value = Format(Date) End Sub Private Sub Form_Unload(Cancel As Integer) menuutama.mndata.Enabled = True menuutama.mnlogout.Enabled = True End Sub
Coding Perhitungan Laba Kotor Dim kdbar As String Dim HPP, sediaawal, sediajual, jumBeli, totJual, totBeli, Labakotor, rtBeli, sediaakhir, xstok, Totalx As Double Private Sub Command1_Click() If cboABC.Text = "" Then MsgBox "Kelas Belum Dipilih", vbCritical, "xx" Exit Sub End If If Format(Dt1, "yyyy-mm-dd") > Format(Dt2, "yyyy-mm-dd") Then MsgBox "Input tanggal awal dan akhir salah.!", vbCritical, "xx" Exit Sub End If Adodc5.RecordSource = "select * from tb_laba_kotor" Adodc5.Refresh Do While Not Adodc5.Recordset.EOF Adodc5.Recordset.Delete Adodc5.Recordset.MoveNext Loop Totalx = 0 Adodc3.RecordSource = "select * from tb_abc where kelas = '" + cboABC + "'" Adodc3.Refresh Do While Not Adodc3.Recordset.EOF kdbar = Adodc3.Recordset!kode_barang sediaawal = 0 sediajual = 0 jumBeli = 0 totBeli = 0 rtBeli = 0 sediaakhir = 0 totJual = 0 Labakotor = 0 Adodc6.RecordSource = "select * from tb_logfile where kode_barang = '" + kdbar + "' and transaksi = '" + "Stok Awal" + "'" Adodc6.Refresh sediaawal = Val(Adodc6.Recordset!jumlah) * Val(Adodc6.Recordset!harga)
87
Adodc2.RecordSource = "select * from tb_pembelian where tanggal >= '" + Format(Dt1, "yyyy-mm-dd") + "' and tanggal <= '" + Format(Dt2, "yyyy-mm-dd") + "' and kode_barang = '" + kdbar + "'" Adodc2.Refresh If Format(Dt1, "yyyy-mm-dd") > Format(Dt2, "yyyy-mm-dd") Then MsgBox "Input tanggal awal dan akhir salah.!", vbCritical, "xx" Exit Sub End If Do While Not Adodc2.Recordset.EOF jumBeli = jumBeli + Val(Adodc2.Recordset!jumlah) 'jumlah pembelian barang totBeli = totBeli + Val(Adodc2.Recordset!total) 'total harga pmbelian barang rtBeli = Format(Val(totBeli) / Val(jumBeli), "0.00") Adodc4.RecordSource = "select * from tb_stok where kode_barang = '" + kdbar + "'" Adodc4.Refresh xstok = Adodc4.Recordset!jumlah 'megetahui stok barang yg ada sediaakhir = Format(Val(xstok) * Val(rtBeli), "0.00") Adodc2.Recordset.MoveNext Loop If Adodc2.Recordset.EOF Then Adodc4.RecordSource = "select * from tb_stok where kode_barang = '" + kdbar + "'" Adodc4.Refresh xstok = Adodc4.Recordset!jumlah End If sediajual = Val(sediaawal) + Val(totBeli) HPP = Val(sediajual) - Val(sediaakhir) Adodc1.RecordSource = "select * from tb_penjualan where tanggal >= '" + Format(Dt1, "yyyy-mm-dd") + "' and tanggal <= '" + Format(Dt2, "yyyy-mm-dd") + "' and kode_barang = '" + kdbar + "'" Adodc1.Refresh Do While Not Adodc1.Recordset.EOF totJual = Val(totJual) + Val(Adodc1.Recordset!total) Adodc1.Recordset.MoveNext Loop Labakotor = Val(totJual) - Val(HPP) Adodc5.Recordset.AddNew With Adodc5.Recordset !kode_barang = kdbar !kelas = cboABC.Text !jumlah_stok = xstok !stok_awal = sediaawal !pembelian = totBeli !barang_tersedia_dijual = sediajual !stok_akhir = sediaakhir !HPP = HPP !total_penjualan = totJual !laba_kotor = Labakotor .Update End With Totalx = Val(Totalx) + Val(Labakotor) Adodc3.Recordset.MoveNext
88
Loop lbKotor.Caption = Totalx Adodc5.RecordSource = "select * from tb_laba_kotor" Adodc5.Refresh Exit Sub End Sub Private Sub Command2_Click() Unload Me End Sub Private Sub Command3_Click() On Error Resume Next With Adodc5.Recordset Printer.FontSize = 14 Printer.FontBold = True Printer.Print "PERHITUNGAN LABA KOTOR" Printer.FontSize = 10 Printer.Print Tab(1); "Periode waktu : "; Printer.Print Tab(25); Dt1.Value Printer.Print Tab(25); Dt2.Value ' Printer.FontBold = False Printer.Print "---------------------------------------------------------------------------------------------" & _ "---------------------------------------------------------------------------------------------------------------" Do While Not .EOF Printer.FontBold = False Printer.Print Tab(1); "KODE BARANG :"; Printer.Print Tab(31); !kode_barang Printer.Print Tab(1); "NAMA BARANG :"; Adodc4.RecordSource = "select * from tb_stok where kode_barang = '" + Adodc5.Recordset!kode_barang + "'" Adodc4.Refresh Printer.Print Tab(31); Adodc4.Recordset!nama_barang Printer.Print Tab(1); "Stok Awal"; Printer.Print Tab(31); !stok_awal Printer.Print Tab(1); "Pembelian"; Printer.Print Tab(31); !pembelian Printer.Print Tab(31); "------------ (+)" Printer.Print Tab(1); "Barang Tersedia Dijual"; Printer.Print Tab(31); !barang_tersedia_dijual Printer.Print Tab(1); "Stok Akhir"; Printer.Print Tab(31); !stok; akhir Printer.Print Tab(31); "------------ (-)" Printer.Print Tab(1); "H P P"; Printer.Print Tab(31); !HPP Printer.Print Tab(100); "v"; Printer.Print Tab(1); "Total Penjualan"; Printer.Print Tab(31); !total_penjualan Printer.Print Tab(1); "H P P"; Printer.Print Tab(31); !HPP Printer.Print Tab(31); "------------ (-)" Printer.Print Tab(1); "Laba Kotor"; Printer.Print Tab(31); !laba_kotor Printer.Print "---------------------------------------------------------------------------------------------" & _
89
"---------------------------------------------------------------------------------------------------------------" .MoveNext Loop .MoveFirst Printer.Print "---------------------------------------------------------------------------------------------" & _ "---------------------------------------------------------------------------------------------------------------" Printer.Print Tab(1); "Total Laba Kotor :"; Printer.Print Tab(31); Totalx End With End Sub Private Sub Form_Load() Dt1.Value = Format(Date) Dt2.Value = Format(Date) End Sub Private Sub Form_Unload(Cancel As Integer) menuutama.mndata.Enabled = True menuutama.mnlogout.Enabled = True End Sub
