LAMPIRAN. Aplikasi pada PT. Kreasindo Sukses Mandiri. Layar Login. Layar Menu Utama

LI

LAMPIRAN

Aplikasi pada PT. Kreasindo Sukses Mandiri

Layar Login

Layar Menu Utama

LI

LII

Layar Entry Data Penjualan

LIII

Layar Detail Barang

LIV

Layar Lihat Sales Order

LV

Layar Edit Sales Order

LVI

Layar Pembuatan Faktur

LVII

Layar Lihat Faktur

LVIII

Layar Entry Surat Jalan

LIX

Layar Lihat Surat Jalan

LX

Layar Lihat Barang

LXI

Layar Tambah Barang

Layar Edit Barang

Layar Update Harga

LXII

Layar Lihat Stock

LXIII

Layar Entry Purchase Order

LXIV

Layar View Purchase Order

LXV

Layar Pembelian Barang

LXVI

Layar Entry Retur Pembelian

LXVII

Layar View Retur Pembelian

LXVIII

Layar Pembayaran Pembelian

Layar View Pembayaran Pembelian

LXIX

Layar View Karyawan

LXX

Layar Entry Karyawan

LXXI

Layar Lihat Data Customer

LXXII

Layar Entry Data Customer

Layar Edit Customer

LXXIII

Layar View Supplier

LXXIV

Layar Edit Data Supplier

Layar Entry Data Supplier

LXXV

Laporan daftar Pelanggan

Laporan Jumlah Stock

LXXVI

Laporan Penjualan Bulanan

LXXVII

Laporan Pembelian Bulanan

Laporan Daftar Karyawan

LXXVIII Query View

create view laporanbulanan as SELECT trpenjualan_0.KdPelanggan, mspelanggan_0.NamaPelanggan, msbarang_0.KdBarang, msbarang_0.NamaBarang, msbarang_0.Satuan, trsalesorderdetail_0.Jumlah, trsalesorderdetail_0.HargaJual, month(trpenjualan_0.Tgl) as bulan, year(trpenjualan_0.Tgl) as tahun FROM basisdata.msbarang msbarang_0, basisdata.mspelanggan mspelanggan_0, basisdata.trpenjualan trpenjualan_0, basisdata.trsalesorderdetail trsalesorderdetail_0, basisdata.trsalesorderheader trsalesorderheader_0 WHERE trsalesorderdetail_0.IdSalesOrder = trpenjualan_0.IdSalesOrder AND trsalesorderdetail_0.IdSalesOrder = trsalesorderheader_0.IdSalesOrder AND mspelanggan_0.KdPelanggan = trpenjualan_0.KdPelanggan AND mspelanggan_0.KdPelanggan = trsalesorderheader_0.KdPelanggan AND msbarang_0.KdBarang = trsalesorderdetail_0.KdBarang

create view daftarpelanggan as select namakota,kdpelanggan, namapelanggan, alamat, telp, fax from mspelanggan p, mskota k where k.kdkota=p.kdkota

create view daftarkaryawan as select namakota,kdkaryawan, namakaryawan, namajabatan, namadepartemen, alamat, telp

from

msjabatan

j,msdepartemen

d,

mskaryawan

k,

mskota

kt

where

kt.kdkota=k.kdkota and k.kddepartemen=d.kddepartemen and k.kdjabatan=j.kdjabatan

create view laporanjumlahstock as select k.kdbarang, namabarang, satuan, jumlah from trstockbulanan r, msbarang k where k.kdbarang = r.kdbarang

LXXIX create view lihatfaktur as SELECT p.kdpelanggan, p.namapelanggan, p.alamat, p.telp, p.fax, j.tgl, j.tgljatuhtempo, kr.kdkaryawan, kr.namakaryawan, j.nofakturpenjualan, d.idsalesorder, d.kdbarang, namabarang, satuan, jumlah, hargajual FROM mspelanggan p, mskaryawan kr, trpenjualan j, trsalesorderdetail d, msbarang h WHERE d.kdbarang = h.kdbarang AND d.idsalesorder = j.idsalesorder AND kr.kdkaryawan = j.kdkaryawan AND p.kdpelanggan = j.kdpelanggan

create view lihatsuratjalan as select

kdsuratjalan,

j.nofakturpenjualan,

j.tgl,

j.kdkaryawan,

k.namakaryawan,kdpelanggan,namapelanggan,l.alamat,l.telp,fax,kdbarang,namabarang,s atuan,jumlah

from

lihatfaktur

l,

mskaryawan

k,

trsuratjalan

j

where

j.kdkaryawan=k.kdkaryawan and j.nofakturpenjualan=l.nofakturpenjualan

create view lihatreturpenjualan as select l.nofakturpenjualan,h.noreturpenjualan,h.tglretur ,kdpelanggan, namapelanggan, l.alamat,l.telp,fax, d.kdbarang, l.satuan , namabarang, d.jumlah, hargajual from lihatfaktur l, mskaryawan k, trreturpenjualanheader h, trreturpenjualandetail d where d.noreturpenjualan=h.noreturpenjualan and h.nofakturpenjualan=l.nofakturpenjualan and d.kdbarang=l.kdbarang and k.kdkaryawan = l.kdkaryawan

create view lihatsalesorder as select

h.idsalesorder,

k.kdkaryawan,

namakaryawan,

p.alamat,

p.kdpelanggan,

p.namapelanggan, p.telp, p.fax, d.kdbarang, namabarang, satuan, jumlah, hargajual, tgl from mskaryawan k, trsalesorderheader h, trsalesorderdetail d, mspelanggan p, msbarang br where

k.kdkaryawan=h.kdkaryawan

and

p.kdpelanggan=h.kdpelanggan

h.idsalesorder = d.idsalesorder and d.kdbarang = br.kdbarang

and

LXXX create view lihatpenerimaan as select

l.kdpelanggan,l.namapelanggan,

l.alamat,

l.telp,

l.fax,

l.nofakturpenjualan,

k.kdkaryawan, k.namakaryawan, jumlahbayar, pn.tgl from lihatfaktur l, mskaryawan k, trpenerimaan pn where pn.nofakturpenjualan = l.nofakturpenjualan and k.kdkaryawan=pn.kdkaryawan

create view lihatpurchaseorder as select h.IdPurchaseOrder, h.kdkaryawan, namakaryawan, s.kdsupplier, s.namasupplier, s.alamat, s.telp, h.tgl, s.fax, d.kdbarang, namabarang, d.jumlah from trPurchaseOrderheader h, trPurchaseOrderdetail d, mskaryawan k, mssupplier s, msbarang b where

h.idpurchaseorder=d.idpurchaseorder

and

s.kdsupplier=h.kdsupplier

and

h.kdkaryawan=k.kdkaryawan and b.kdbarang=d.kdbarang

create view lihatpembelian as select h.nofakturpembelian, h.idpurchaseorder, h.tgl, h.tgljatuhtempo, s.kdsupplier, s.namasupplier, s.alamat, s.telp, s.fax, d.kdbarang, namabarang, d.jumlah, d.harga, k.kdkaryawan, k.namakaryawan from trpembeliandetail d, trpembelianheader h, mssupplier s, mskaryawan k, msbarang b where d.nofakturpembelian=h.nofakturpembelian and s.kdsupplier=h.kdsupplier and k.kdkaryawan=h.kdkaryawan and d.kdbarang=b.kdbarang

create view lihatreturpembelian as select

h.noreturpembelian,

k.namakaryawan,

h.nofakturpembelian,

tglretur,

l.kdsupplier,

k.kdkaryawan, l.namasupplier,

l.alamat,l.telp,l.fax,d.kdbarang,l.namabarang,d.jumlah,l.harga from lihatpembelian l, mskaryawan k, trreturpembeliandetail d, trreturpembelianheader h where h.noreturpembelian=d.noreturpembelian and k.kdkaryawan=h.kdkaryawan and l.nofakturpembelian=h.nofakturpembelian and d.kdbarang=l.kdbarang

LXXXI create view lihatpembayaran as select p.kdpembayaran, p.tgl, l.nofakturpembelian, k.kdkaryawan, k.namakaryawan, l.kdsupplier, l.namasupplier, l.telp, l.fax, l.alamat, jumlahbayar from trpembayaran p, mskaryawan k, lihatpembelian l where l.nofakturpembelian=p.nofakturpembelian and k.kdkaryawan=p.kdkaryawan

LXXXII Database Design Syntax create table MsKota( KdKota char(4), NamaKota varchar(20), primary key (KdKota) )type=MyISAM;

create table MsBarang( KdBarang char(6), NamaBarang varchar(20), Satuan char(3), primary key (KdBarang) )type=MyISAM;

create table MsJabatan( KdJabatan char(4), NamaJabatan varchar(20), primary key (KdJabatan) )type=MyISAM;

create table MsPelanggan( KdPelanggan char(4), NamaPelanggan varchar(20), Alamat varchar(50), Telp varchar(20), Fax varchar(20), KdKota char(4), memo varchar(100), primary key(KdPelanggan), Foreign key (KdKota) References MsKota(KdKota) )type=MyISAM;

LXXXIII create table MsSupplier( KdSupplier char(4), NamaSupplier varchar(20), Alamat varchar(50), Telp varchar(20), Fax varchar(20), KdKota char(4), memo varchar(100), primary key (KdSupplier), foreign key (KdKota) references MsKota(KdKota) )type=MyISAM;

create table MsKaryawan( KdKaryawan char(12), NamaKaryawan varchar(20), Gender binary, Password varchar(12), KdJabatan char(4), KdDepartemen char(4), TglMasuk Date, Alamat varchar(50), KdKota char(4), Telp varchar(20), primary key (KdKaryawan), foreign key (KdKota) references MsKota(KdKota), foreign key (KdJabatan) references MsJabatan(KdJabatan), foreign key (KdDepartemen) references MsDepartemen(KdDepartemen) )type=MyISAM;

LXXXIV create table hakAkses( aksesID char(4) primary key, karyawan enum('Y','N') not null default 'N', supplier enum('Y','N') not null default 'N', customer enum('Y','N') not null default 'N', buatRetur enum('Y','N') not null default 'N', lihatRetur enum('Y','N') not null default 'N', buatSuratJalan enum('Y','N') not null default 'N', lihatSuratJalan enum('Y','N') not null default 'N', entrySalesOrder enum('Y','N') not null default 'N', lihatSalesOrder enum('Y','N') not null default 'N', entryFaktur enum('Y','N') not null default 'N', lihatFaktur enum('Y','N') not null default 'N', lihatPembayaran enum('Y','N') not null default 'N', lihatBarang enum('Y','N') not null default 'N', lihatStock enum('Y','N') not null default 'N', entryPurchaseOrder enum('Y','N') not null default 'N', lihatPurchaseOrder enum('Y','N') not null default 'N', entryPembelian enum('Y','N') not null default 'N', lihatPembelian enum('Y','N') not null default 'N', returPembelian enum('Y','N') not null default 'N', lihatReturPembelian enum('Y','N') not null default 'N', pembayaranPembelian enum('Y','N') not null default 'N', lihatPembayaranPembelian enum('Y','N') not null default 'N', laporanJumlahStock enum('Y','N') not null default 'N', laporanPenjualan enum('Y','N') not null default 'N', laporanPembelian enum('Y','N') not null default 'N', laporanDaftarKaryawan enum('Y','N') not null default 'N', laporanDaftarPelanggan enum('Y','N') not null default 'N' );

LXXXV create table Msdepartemen( KdDepartemen char(4), NamaDepartemen varchar(20), aksesID char(4), Primary key (KdDepartemen), foreign key (aksesID) references hakAkses(aksesID) );

create table TrPenjualan( IdSalesOrder char(11), NoFakturPenjualan char(11), Tgl Date, TglJatuhTempo Date, KdKaryawan char(12), KdPelanggan char(4), StatusPembayaran int, primary key (NoFakturPenjualan), foreign key (IdSalesOrder) references TrSalesOrderHeader(IdSalesOrder), foreign key (KdKaryawan) references MsKaryawan(KdKaryawan), foreign key (KdPelanggan) references MsPelanggan(KdPelanggan) )type=MyISAM;

create table TrPerubahanHarga( KdPerubahanHarga bigint auto_increment, KdBarang char(6), Harga bigint, TglPerubahan Date, primary key (KdPerubahanHarga), foreign key (KdBarang) references MsBarang(KdBarang) )type=MyISAM;

LXXXVI create table TrStockBulanan( KdStock INT NOT NULL AUTO_INCREMENT, KdBarang char(4), Jumlah int, bulan int, tahun int, primary key (KdStock), Foreign key (KdBarang) References MsBarang(KdBarang) )type=MyISAM;

create table TrReturPenjualanHeader( NoReturPenjualan char(11), TglRetur Date, NoFakturPenjualan char(11), KdKaryawan char(12), primary key (NoReturPenjualan), Foreign key (NoFakturPenjualan) References TrPenjualan(NoFakturPenjualan), Foreign key (KdKaryawan) References MsKaryawan(KdKaryawan) )type=MyISAM;

create table TrReturPenjualanDetail( NoReturPenjualan char(11), KdBarang char(6), Jumlah int, primary key (NoReturPenjualan, KdBarang), Foreign key (NoReturPenjualan) References TrReturPenjualanHeader(NoReturPenjualan), Foreign key (KdBarang) References MsBarang(KdBarang) )type=MyISAM;

LXXXVII create table TrPenerimaan( KdPenerimaan char(11), Tgl Date, NoFakturPenjualan char(11), kdkaryawan char(12), JumlahBayar bigint, memo varchar(300), primary key (KdPenerimaan), foreign key (kdKaryawan) references mskaryawan(kdkaryawan), Foreign key (NoFakturPenjualan) References TrPenjualan(NoFakturPenjualan) )type=MyISAM;

create table TrSalesOrderHeader( IdSalesOrder char(11), Tgl Date, KdKaryawan char(12), KdPelanggan char(4), foreign key (KdKaryawan) references MsKaryawan(KdKaryawan), foreign key (KdPelanggan) references MsPelanggan(KdPelanggan) )type=MyISAM;

create table TrSalesOrderDetail( IdSalesOrder char(11), KdBarang char(4), Jumlah int, HargaJual bigint, Primary key (IdSalesOrder, KdBarang), foreign key (IdSalesOrder) references TrPenjualanHeader(IdSalesOrder), foreign key (KdBarang) references MsBarang(KdBarang) )type=MyISAM;

LXXXVIII create table trsuratjalan( Kdsuratjalan char(11), NoFakturPenjualan char(11), tgl date, kdkaryawan char(12), Primary key (Kdsuratjalan), foreign key (kdkaryawan) references mskaryawan(kdkaryawan), foreign key (Nofakturpenjualan) references TrPenjualan(NoFakturPenjualan) )type=MyISAM;

create table trpembelianheader( NoFakturPembelian char(11), Tgl Date, TglJatuhTempo Date, IdPurchaseOrder char(11), KdKaryawan char(12), KdSupplier char(4), StatusPembayaran int default 0, primary key (NoFakturPembelian), foreign key (IdPurchaseOrder) references trPurchaseOrderHeader(IdPurchaseOrder), Foreign key (KdKaryawan) References MsKaryawan(KdKaryawan), Foreign key (KdSupplier) References MsSupplier(KdSupplier) );

create table trpembeliandetail( NoFakturPembelian char(11), KdBarang char(6), Jumlah int, Harga bigint, Primary key (NoFakturPembelian, KdBarang),

LXXXIX Foreign key (NoFakturPembelian) References TrPembelianHeader(NoFakturPembelian), Foreign key (KdBarang) References MsBarang(KdBarang) );

create table TrReturPembelianHeader( NoReturPembelian char(11), TglRetur Date, KdKaryawan char(12), NoFakturPembelian char(11), primary key (NoReturPembelian), Foreign key (KdKaryawan) References MsKaryawan(KdKaryawan), Foreign key (NoFakturPembelian) References TrPembelianHeader(NoFakturPembelian) );

create table TrReturPembelianDetail( NoReturPembelian char(11), KdBarang char(6), Jumlah int, primary key (NoReturPembelian,KdBarang), Foreign key (NoReturPembelian) References TrReturPembelianHeader(NoReturPembelian), Foreign key (KdBarang) References MsBarang(KdBarang) );

create table TrPembayaran( KdPembayaran char(11), Tgl Date, NoFakturPembelian char(11), KdKaryawan char(12),

XC JumlahBayar bigint, Memo varchar(300), primary key (KdPembayaran), Foreign key (KdKaryawan) References MsKaryawan(KdKaryawan), Foreign key (NoFakturPembelian) References TrPembelianHeader(NoFakturPembelian) )type=MyISAM;

create table trPurchaseOrderHeader( IdPurchaseOrder char(11), Tgl Date, KdKaryawan char(12), KdSupplier char(4), primary key (IdPurchaseOrder), Foreign key (KdKaryawan) References MsKaryawan(KdKaryawan), Foreign key (KdSupplier) References MsSupplier(KdSupplier) ); create table trPurchaseOrderDetail( IdPurchaseOrder char(11), KdBarang char(6), jumlah int, primary key (IdPurchaseOrder, KdBarang), Foreign key (IdPurchaseOrder) References trPurchaseOrderHeader(IdPurchaseOrder), Foreign key (KdBarang) References MsBarang(KdBarang) );

XCI Storage Procedure

create procedure input_faktur(in nofaktur char(11), in idso char(11), in t date, in tjatuh date, in kdkary char(4), in kdpel char(4), in status int) insert trpenjualan set nofakturpenjualan = nofaktur, idsalesorder = idso, tgl = t, tgljatuhtempo = tjatuh, kdkaryawan = kdkary, kdpelanggan=kdpel, statuspembayaran = status;

create procedure update_stock(in kd char(4), in n int) update trstockbulanan set jumlah = n where kdbarang = kd

create procedure input_pelanggan(in kd char(4), in Nama varchar(20), in alm varchar(50), in ntelp varchar(20), in nfax varchar(20), in kdkt char(4), in mem varchar(100) ) insert mspelanggan set kdpelanggan = kd, namapelanggan = nama, alamat = alm, telp = ntelp, fax = nfax, kdkota = kdkt, memo = mem;

create procedure edit_pelanggan(in kd char(4), in Nama varchar(20), in alm varchar(50), in ntelp varchar(20), in nfax varchar(20), in kdkt char(4), in mem varchar(100) ) update mspelanggan set namapelanggan = nama, alamat = alm, telp = ntelp, fax = nfax, kdkota = kdkt, memo = mem where kdpelanggan = kd ;

create procedure hapus_pelanggan(in kd char(4)) delete from mspelanggan where kdpelanggan = kd

create procedure input_supplier(in kd char(4), in Nama varchar(20), in alm varchar(50), in ntelp varchar(20), in nfax varchar(20), in kdkt char(4), in mem varchar(100) ) insert mssupplier set kdsupplier = kd, namasupplier = nama, alamat = alm, telp = ntelp, fax = nfax, kdkota = kdkt, memo = mem;

XCII create procedure edit_supplier(in kd char(4), in Nama varchar(20), in alm varchar(50), in ntelp varchar(20), in nfax varchar(20), in kdkt char(4), in mem varchar(100) ) update mssupplier set namasupplier = nama, alamat = alm, telp = ntelp, fax = nfax, kdkota = kdkt, memo = mem where kdsupplier = kd ;

create procedure hapus_supplier(in kd char(4)) delete from mssupplier where kdsupplier = kd

create procedure hapus_faktur(in kd char(11)) delete from trpenjualan where nofakturpenjualan = kd

create procedure input_barang(in kd char(4),in nama varchar(20), in sat char(3)) insert msbarang set kdbarang = kd , namabarang = nama, satuan = sat;

create procedure input_hargabarang(in kd char(4), in hg bigint, in tg date) insert trperubahanharga set kdbarang = kd, harga = hg, tglperubahan =tg

create procedure input_stockawal(in kd char(4), in jml int, in bln int, in thn int) insert trstockbulanan set kdbarang = kd, jumlah = jml, bulan = bln, tahun = thn

create view histori_harga as select m.kdbarang , namabarang , satuan , harga, tglperubahan from msbarang m ,trperubahanharga t where m.kdbarang=t.kdbarang order by KdPerubahanHarga desc

create procedure input_soheader( in idso char(11), in t date, in kdkary char(12), in kdpel char(4)) insert trsalesorderheader set idsalesorder = idso, tgl = t, kdkaryawan = kdkary, kdpelanggan = kdpel

create procedure input_departemen( in kd char(4), in nm varchar(20)) insert msdepartemen set kddepartemen = kd, namadepartemen = nm

XCIII create procedure input_jabatan( in kd char(4), in nm varchar(20)) insert msjabatan set kdjabatan = kd, namajabatan = nm

create procedure input_karyawan(in kd char(12), in nm varchar(20), in g binary, in pass varchar(20),in kdjbt char(4), in kddpt char(4), in tgl date, in alm varchar(50), in kdkt char(4), in tlp varchar(20)) insert mskaryawan set kdkaryawan = kd, namakaryawan = nm, gender = g, password = pass, kdjabatan = kdjbt, kddepartemen = kddpt, tglmasuk = tgl, alamat = alm, kdkota = kdkt, telp = tlp

create procedure rubah_password(in kd char(12), in pass varchar(20)) update mskaryawan set password = pass where kdkaryawan = kd

create procedure rubah_karyawan(in kdb char(12),in kdl char(12), in nm varchar(20), in g binary, in pass varchar(20),in kdjbt char(4), in kddpt char(4), in alm varchar(50), in kdkt char(4), in tlp varchar(20)) update mskaryawan set kdkaryawan = kdb, namakaryawan = nm, gender = g, password = pass, kdjabatan = kdjbt, kddepartemen = kddpt, alamat = alm, kdkota = kdkt, telp = tlp where kdkaryawan = kdl

create procedure hapus_karyawan(in kd char(12)) delete from mskaryawan where kdkaryawan = kd

create procedure hapus_sodetail(in kd char(11)) delete from trsalesorderdetail where idsalesorder = kd

create procedure edit_soheader( in idso char(11), in t date, in kdkary char(4), in kdpel char(4)) update trsalesorderheader set tgl = t, kdkaryawan = kdkary, kdpelanggan = kdpel where idsalesorder = idso

XCIV create procedure input_suratjalan(in kd char(11), in nof char(11), in tg date, in kdkary char(12)) insert into trsuratjalan values( kd, nof, tgl, kdkary)

create procedure rubah_suratjalan(in kd char(11), in nof char(11), in tg date, in kdkary char(12)) update trsuratjalan set nofakturpenjualan = nof, tgl = tg, kdkaryawan = kdkary where kdsuratjalan = kd

create procedure hapus_suratjalan(in kd char(11)) delete from trsuratjalan where kdsuratjalan = kd

create procedure insert_returpenjualanheader(in kd char(11), in tgl date, in nof char(11), in kdkary char(12)) insert trreturpenjualanheader values ( kd, tgl, nof, kdkary)

create procedure delete_returpenjualanheader(in kd char(11)) delete from trreturpenjualanheader where noreturpenjualan = kd

create procedure hapus_returpenjualandetail(in kd char(11)) delete from trreturpenjualandetail where noreturpenjualan = kd

create procedure insert_penerimaan(in kd char(11),in tg date,in nof char(11),in kdkary char(12),in jml bigint, in mm varchar(300)) insert into trpenerimaan values( kd,tg,nof,kdkary,jml,mm)

create procedure hapus_penerimaan(in kd char(11)) delete from trpenerimaan where kdpenerimaan = kd

create procedure update_penerimaan(in kd char(11),in jml bigint, in mm varchar(300)) update trpenerimaan set jumlahbayar = jml, memo = mm where kdpenerimaan = kd

XCV

create procedure lunas(in kd char(11)) update trpenjualan set statuspembayaran = 1 where nofakturpenjualan = kd

create procedure tidaklunas( in kd char(11)) update trpenjualan set statuspembayaran = 0 where nofakturpenjualan = k