w4Xzt6R2

1-- phpMyAdmin SQL Dump
2-- version 4.8.1
3-- https://www.phpmyadmin.net/
4--
5-- Host: 127.0.0.1
6-- Generation Time: Mar 29, 2019 at 03:56 PM
7-- Server version: 10.1.33-MariaDB
8-- PHP Version: 7.2.6
9
10SET FOREIGN_KEY_CHECKS=0;
11SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
12SET AUTOCOMMIT = 0;
13START TRANSACTION;
14SET time_zone = "+00:00";
15
16
17/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
18/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
19/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
20/*!40101 SET NAMES utf8mb4 */;
21
22--
23-- Database: `sarana_prasarana`
24--
25
26-- --------------------------------------------------------
27
28--
29-- Table structure for table `keluar_barang`
30--
31
32DROP TABLE IF EXISTS `keluar_barang`;
33CREATE TABLE `keluar_barang` (
34  `id_brg_keluar` int(11) NOT NULL,
35  `kode_barang` varchar(11) NOT NULL,
36  `nama_barang` varchar(30) NOT NULL,
37  `tgl_keluar` date NOT NULL,
38  `penerima` varchar(35) NOT NULL,
39  `jml_brg_keluar` int(11) NOT NULL,
40  `keperluan` text NOT NULL,
41  `status` enum('aktif','pending','nonaktif','') NOT NULL
42) ENGINE=InnoDB DEFAULT CHARSET=latin1;
43
44--
45-- Triggers `keluar_barang`
46--
47DROP TRIGGER IF EXISTS `keluar_barang_tambah_kurang`;
48DELIMITER $$
49CREATE TRIGGER `keluar_barang_tambah_kurang` AFTER INSERT ON `keluar_barang` FOR EACH ROW UPDATE stok SET total_barang=total_barang-new.jml_brg_keluar, jml_keluar=jml_keluar+new.jml_brg_keluar WHERE kode_barang = new.kode_barang
50$$
51DELIMITER ;
52
53-- --------------------------------------------------------
54
55--
56-- Table structure for table `kembali_barang`
57--
58
59DROP TABLE IF EXISTS `kembali_barang`;
60CREATE TABLE `kembali_barang` (
61  `id` int(11) NOT NULL,
62  `no_pinjam` int(11) NOT NULL,
63  `tgl_kembali` date NOT NULL,
64  `kode_barang` varchar(11) NOT NULL,
65  `total_barang` int(11) NOT NULL,
66  `kondisi` enum('baik','kurang_baik','','') NOT NULL,
67  `status` enum('diterima','pending','gagal','') NOT NULL DEFAULT 'pending'
68) ENGINE=InnoDB DEFAULT CHARSET=latin1;
69
70--
71-- Dumping data for table `kembali_barang`
72--
73
74INSERT INTO `kembali_barang` (`id`, `no_pinjam`, `tgl_kembali`, `kode_barang`, `total_barang`, `kondisi`, `status`) VALUES
75(1, 9, '2019-03-29', 'KDBRG066916', 1, 'baik', 'diterima'),
76(2, 1, '2019-03-29', 'KDBRG708430', 10, 'baik', 'diterima');
77
78--
79-- Triggers `kembali_barang`
80--
81DROP TRIGGER IF EXISTS `kembali_barang`;
82DELIMITER $$
83CREATE TRIGGER `kembali_barang` AFTER INSERT ON `kembali_barang` FOR EACH ROW BEGIN
84
85UPDATE stok SET total_barang=total_barang+new.total_barang, jml_keluar=jml_keluar-new.total_barang WHERE kode_barang = new.kode_barang;
86
87UPDATE pinjam_barang SET status="dikembalikan" WHERE no_pinjam = new.no_pinjam;
88
89END
90$$
91DELIMITER ;
92
93-- --------------------------------------------------------
94
95--
96-- Table structure for table `masuk_barang`
97--
98
99DROP TABLE IF EXISTS `masuk_barang`;
100CREATE TABLE `masuk_barang` (
101  `id_brg_masuk` int(11) NOT NULL,
102  `kode_barang` varchar(11) NOT NULL,
103  `nama_brg` varchar(30) NOT NULL,
104  `id_masuk` varchar(8) DEFAULT NULL,
105  `jml_masuk` int(11) NOT NULL,
106  `kode_sp` int(11) NOT NULL
107) ENGINE=InnoDB DEFAULT CHARSET=latin1;
108
109--
110-- Triggers `masuk_barang`
111--
112DROP TRIGGER IF EXISTS `tambah_stok`;
113DELIMITER $$
114CREATE TRIGGER `tambah_stok` AFTER INSERT ON `masuk_barang` FOR EACH ROW UPDATE stok SET total_barang=total_barang+new.jml_masuk WHERE kode_barang = new.kode_barang
115$$
116DELIMITER ;
117
118-- --------------------------------------------------------
119
120--
121-- Table structure for table `pinjam_barang`
122--
123
124DROP TABLE IF EXISTS `pinjam_barang`;
125CREATE TABLE `pinjam_barang` (
126  `no_pinjam` int(11) NOT NULL,
127  `tgl_pinjam` date NOT NULL,
128  `kode_barang` varchar(11) NOT NULL,
129  `jml_pinjam` int(11) NOT NULL,
130  `peminjam` varchar(35) NOT NULL,
131  `keterangan` text NOT NULL,
132  `status` enum('aktif','pending','nonaktif','dikembalikan') NOT NULL DEFAULT 'pending'
133) ENGINE=InnoDB DEFAULT CHARSET=latin1;
134
135--
136-- Dumping data for table `pinjam_barang`
137--
138
139INSERT INTO `pinjam_barang` (`no_pinjam`, `tgl_pinjam`, `kode_barang`, `jml_pinjam`, `peminjam`, `keterangan`, `status`) VALUES
140(1, '2019-03-20', 'KDBRG708430', 10, 'aldycavalera', 'No', 'nonaktif'),
141(9, '2019-03-29', 'KDBRG066916', 1, 'Aldy Cavalera', 'Gaada keterangan', 'pending'),
142(10, '2019-03-29', 'KDBRG434826', 1, 'Dedi Rasnadi', 'Untuk belajar di kelas', 'pending');
143
144--
145-- Triggers `pinjam_barang`
146--
147DROP TRIGGER IF EXISTS `pinjam_barang`;
148DELIMITER $$
149CREATE TRIGGER `pinjam_barang` AFTER INSERT ON `pinjam_barang` FOR EACH ROW UPDATE stok SET total_barang=total_barang-new.jml_pinjam, jml_keluar=jml_keluar+new.jml_pinjam WHERE kode_barang = new.kode_barang
150$$
151DELIMITER ;
152
153-- --------------------------------------------------------
154
155--
156-- Table structure for table `stok`
157--
158
159DROP TABLE IF EXISTS `stok`;
160CREATE TABLE `stok` (
161  `kode_barang` varchar(11) NOT NULL,
162  `jml_masuk` int(11) NOT NULL,
163  `jml_keluar` int(11) NOT NULL,
164  `total_barang` int(11) NOT NULL
165) ENGINE=InnoDB DEFAULT CHARSET=latin1;
166
167--
168-- Dumping data for table `stok`
169--
170
171INSERT INTO `stok` (`kode_barang`, `jml_masuk`, `jml_keluar`, `total_barang`) VALUES
172('KDBRG325067', 100, 0, 100),
173('KDBRG649668', 50, 0, 50),
174('KDBRG754122', 30, 0, 30),
175('KDBRG892247', 250, 0, 250),
176('KDBRG755224', 3, 0, 3),
177('KDBRG674348', 25, 0, 25),
178('KDBRG708430', 250, 0, 250),
179('KDBRG416166', 300, 0, 300),
180('KDBRG193250', 30, 0, 30),
181('KDBRG761798', 15, 0, 15),
182('KDBRG066916', 28, 0, 28),
183('KDBRG434826', 2, 1, 1);
184
185-- --------------------------------------------------------
186
187--
188-- Table structure for table `tbl_barang`
189--
190
191DROP TABLE IF EXISTS `tbl_barang`;
192CREATE TABLE `tbl_barang` (
193  `kode_barang` varchar(11) NOT NULL,
194  `nama_barang` varchar(35) NOT NULL,
195  `kategori` varchar(25) NOT NULL,
196  `jml_barang` int(11) NOT NULL,
197  `kondisi` varchar(20) NOT NULL,
198  `jenis_barang` varchar(35) NOT NULL,
199  `spesifikasi` text,
200  `dibuat_pada` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
201) ENGINE=InnoDB DEFAULT CHARSET=latin1;
202
203--
204-- Dumping data for table `tbl_barang`
205--
206
207INSERT INTO `tbl_barang` (`kode_barang`, `nama_barang`, `kategori`, `jml_barang`, `kondisi`, `jenis_barang`, `spesifikasi`, `dibuat_pada`) VALUES
208('KDBRG066916', 'pancasila', 'Properti kelas', 28, 'Baik', 'Barang tidak habis pakai', '   2', '2019-03-22 02:46:39'),
209('KDBRG193250', 'Buku Bahasa Sunda', 'Buku', 30, 'Baru', 'Barang tidak habis pakai', '    Buku Bahasa Sunda Kang Endang', '2019-03-20 10:15:02'),
210('KDBRG325067', 'Kursi', 'Fasilitas sekolah', 100, 'Baru', 'Barang tidak habis pakai', ' Kursi Berlengan Kayu', '2019-03-20 10:15:40'),
211('KDBRG416166', 'Penggaris', 'Alat tulis', 300, 'Baru', 'Barang habis pakai', ' Penggaris Mika', '2019-03-20 10:15:50'),
212('KDBRG434826', 'Pulpen', 'Alat tulis', 2, 'Baik', 'Barang tidak habis pakai', '10 per box', '2019-03-22 02:50:37'),
213('KDBRG649668', 'Buku Bahasa Indonesia', 'Buku', 50, 'Baru', 'Barang tid habis pakai', 'Buku erlangga edisi 2019', '2019-03-13 05:28:53'),
214('KDBRG674348', 'Sapu Lidi', 'Peralatan kelas', 25, 'Bekas', 'Barang habis pakai', 'Untuk penggunaan kelas', '2019-03-17 03:00:13'),
215('KDBRG708430', 'Bola pingpong', 'Peralatan olahraga', 250, 'Baru', 'Barang tid habis pakai', 'Bola pingpong', '2019-03-17 03:03:53'),
216('KDBRG754122', 'Taplak', 'Properti kelas', 30, 'Baru', 'Barang habis pakai', 'Taplak kain', '2019-03-17 02:49:56'),
217('KDBRG755224', 'Bola futsal', 'Peralatan olahraga', 3, 'Baru', 'Barang tid habis pakai', 'Bola futsal ukuran standar', '2019-03-17 02:59:38'),
218('KDBRG761798', 'Jam dinding', 'Properti kelas', 15, 'Baru', 'Barang tid habis pakai', 'Jam analog', '2019-03-20 08:10:40'),
219('KDBRG892247', 'Pulpen', 'Alat tulis', 250, 'Baru', 'Barang habis pakai', 'Tinta hitam', '2019-03-17 02:58:43');
220
221--
222-- Triggers `tbl_barang`
223--
224DROP TRIGGER IF EXISTS `barang_auto_tambah_stok`;
225DELIMITER $$
226CREATE TRIGGER `barang_auto_tambah_stok` AFTER INSERT ON `tbl_barang` FOR EACH ROW INSERT INTO stok VALUES (new.kode_barang, new.jml_barang, 0, new.jml_barang)
227$$
228DELIMITER ;
229
230-- --------------------------------------------------------
231
232--
233-- Table structure for table `tbl_supplier`
234--
235
236DROP TABLE IF EXISTS `tbl_supplier`;
237CREATE TABLE `tbl_supplier` (
238  `kode_sp` int(11) NOT NULL,
239  `nama_sp` varchar(35) NOT NULL,
240  `alamat_sp` varchar(100) NOT NULL,
241  `telp_sp` varchar(15) NOT NULL,
242  `kota_sp` varchar(20) NOT NULL
243) ENGINE=InnoDB DEFAULT CHARSET=latin1;
244
245--
246-- Dumping data for table `tbl_supplier`
247--
248
249INSERT INTO `tbl_supplier` (`kode_sp`, `nama_sp`, `alamat_sp`, `telp_sp`, `kota_sp`) VALUES
250(1, 'Kijing Inovastion', 'Jl. Prabu Geusan Ulun, Sumedang', '085219362312', 'Sumedang');
251
252-- --------------------------------------------------------
253
254--
255-- Table structure for table `users`
256--
257
258DROP TABLE IF EXISTS `users`;
259CREATE TABLE `users` (
260  `id` int(11) NOT NULL,
261  `nama` varchar(35) NOT NULL,
262  `username` varchar(35) NOT NULL,
263  `password` varchar(255) NOT NULL,
264  `role` enum('admin','user','tamu','') NOT NULL
265) ENGINE=InnoDB DEFAULT CHARSET=latin1;
266
267--
268-- Dumping data for table `users`
269--
270
271INSERT INTO `users` (`id`, `nama`, `username`, `password`, `role`) VALUES
272(1, 'Aldy Cavalera', 'aldycavalera', '$2y$10$0KG7bX0/x/G.fFEM6Jely.2ncl15atrUgwQOg8fuLkS/M.qqOvwu2', 'admin'),
273(2, 'Tita Amelia', 'titaamel', '$2y$10$gx.XWenXHrG4mixSyPNLYer/lkQx1b8eVrFnUxE8JaP9sp8MufrAK', 'user'),
274(3, 'Monika', 'monika', '$2y$10$bMPCdWGo3/.dIz3N3FI6xeqf6ED3ENIcX9G4pSbDLD/jJza0MvaaK', 'user');
275
276--
277-- Indexes for dumped tables
278--
279
280--
281-- Indexes for table `keluar_barang`
282--
283ALTER TABLE `keluar_barang`
284  ADD PRIMARY KEY (`id_brg_keluar`),
285  ADD KEY `kdbrg_keluarbrg` (`kode_barang`);
286
287--
288-- Indexes for table `kembali_barang`
289--
290ALTER TABLE `kembali_barang`
291  ADD PRIMARY KEY (`id`),
292  ADD KEY `kdbrg_stok` (`kode_barang`),
293  ADD KEY `tbl_brg_kmbli_brg` (`no_pinjam`);
294
295--
296-- Indexes for table `masuk_barang`
297--
298ALTER TABLE `masuk_barang`
299  ADD PRIMARY KEY (`id_brg_masuk`),
300  ADD KEY `msk_brg_tbl_brg` (`kode_barang`),
301  ADD KEY `kdsp` (`kode_sp`);
302
303--
304-- Indexes for table `pinjam_barang`
305--
306ALTER TABLE `pinjam_barang`
307  ADD PRIMARY KEY (`no_pinjam`),
308  ADD KEY `kdbrg_pinjam_tbl_brg` (`kode_barang`),
309  ADD KEY `users_ibfk_1` (`peminjam`);
310
311--
312-- Indexes for table `stok`
313--
314ALTER TABLE `stok`
315  ADD KEY `kode_barang` (`kode_barang`);
316
317--
318-- Indexes for table `tbl_barang`
319--
320ALTER TABLE `tbl_barang`
321  ADD PRIMARY KEY (`kode_barang`);
322
323--
324-- Indexes for table `tbl_supplier`
325--
326ALTER TABLE `tbl_supplier`
327  ADD PRIMARY KEY (`kode_sp`);
328
329--
330-- Indexes for table `users`
331--
332ALTER TABLE `users`
333  ADD PRIMARY KEY (`id`),
334  ADD KEY `username` (`username`);
335
336--
337-- AUTO_INCREMENT for dumped tables
338--
339
340--
341-- AUTO_INCREMENT for table `keluar_barang`
342--
343ALTER TABLE `keluar_barang`
344  MODIFY `id_brg_keluar` int(11) NOT NULL AUTO_INCREMENT;
345
346--
347-- AUTO_INCREMENT for table `kembali_barang`
348--
349ALTER TABLE `kembali_barang`
350  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
351
352--
353-- AUTO_INCREMENT for table `masuk_barang`
354--
355ALTER TABLE `masuk_barang`
356  MODIFY `id_brg_masuk` int(11) NOT NULL AUTO_INCREMENT;
357
358--
359-- AUTO_INCREMENT for table `pinjam_barang`
360--
361ALTER TABLE `pinjam_barang`
362  MODIFY `no_pinjam` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
363
364--
365-- AUTO_INCREMENT for table `tbl_supplier`
366--
367ALTER TABLE `tbl_supplier`
368  MODIFY `kode_sp` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
369
370--
371-- AUTO_INCREMENT for table `users`
372--
373ALTER TABLE `users`
374  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
375
376--
377-- Constraints for dumped tables
378--
379
380--
381-- Constraints for table `keluar_barang`
382--
383ALTER TABLE `keluar_barang`
384  ADD CONSTRAINT `keluar_barang_ibfk_1` FOREIGN KEY (`kode_barang`) REFERENCES `stok` (`kode_barang`);
385
386--
387-- Constraints for table `kembali_barang`
388--
389ALTER TABLE `kembali_barang`
390  ADD CONSTRAINT `kembali_barang_ibfk_1` FOREIGN KEY (`no_pinjam`) REFERENCES `pinjam_barang` (`no_pinjam`) ON DELETE CASCADE ON UPDATE CASCADE,
391  ADD CONSTRAINT `kembali_barang_ibfk_2` FOREIGN KEY (`kode_barang`) REFERENCES `stok` (`kode_barang`) ON DELETE CASCADE ON UPDATE CASCADE;
392
393--
394-- Constraints for table `masuk_barang`
395--
396ALTER TABLE `masuk_barang`
397  ADD CONSTRAINT `masuk_barang_ibfk_1` FOREIGN KEY (`kode_sp`) REFERENCES `tbl_supplier` (`kode_sp`),
398  ADD CONSTRAINT `masuk_barang_ibfk_2` FOREIGN KEY (`kode_barang`) REFERENCES `stok` (`kode_barang`) ON DELETE CASCADE ON UPDATE CASCADE;
399
400--
401-- Constraints for table `pinjam_barang`
402--
403ALTER TABLE `pinjam_barang`
404  ADD CONSTRAINT `pinjam_barang_ibfk_1` FOREIGN KEY (`kode_barang`) REFERENCES `stok` (`kode_barang`) ON DELETE CASCADE ON UPDATE CASCADE;
405
406--
407-- Constraints for table `stok`
408--
409ALTER TABLE `stok`
410  ADD CONSTRAINT `stok_ibfk_1` FOREIGN KEY (`kode_barang`) REFERENCES `tbl_barang` (`kode_barang`) ON DELETE CASCADE ON UPDATE CASCADE;
411SET FOREIGN_KEY_CHECKS=1;
412COMMIT;
413
414/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
415/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
416/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;