· 7 years ago · Dec 24, 2018, 05:32 PM
1-- phpMyAdmin SQL Dump
2-- version 4.4.15.9
3-- https://www.phpmyadmin.net
4--
5-- Host: localhost
6-- Generation Time: Dec 24, 2018 at 05:28 PM
7-- Server version: 5.6.37
8-- PHP Version: 7.1.8
9
10SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
11SET time_zone = "+00:00";
12
13--
14-- Database: `fb_hardi`
15--
16
17-- --------------------------------------------------------
18
19--
20-- Table structure for table `t_admin`
21--
22
23CREATE TABLE IF NOT EXISTS `t_admin` (
24 `id_admin` tinyint(2) NOT NULL,
25 `username` varchar(35) NOT NULL,
26 `fullname` varchar(45) NOT NULL,
27 `password` varchar(255) NOT NULL,
28 `level` tinyint(1) NOT NULL,
29 `email` varchar(100) NOT NULL,
30 `reset` varchar(35) NOT NULL
31) ENGINE=InnoDB DEFAULT CHARSET=latin1;
32
33-- --------------------------------------------------------
34
35--
36-- Table structure for table `t_detail_order`
37--
38
39CREATE TABLE IF NOT EXISTS `t_detail_order` (
40 `id_row` int(11) NOT NULL,
41 `id_order` int(10) NOT NULL,
42 `id_item` int(7) NOT NULL,
43 `qty` int(4) NOT NULL,
44 `biaya` int(9) NOT NULL
45) ENGINE=InnoDB DEFAULT CHARSET=latin1;
46
47-- --------------------------------------------------------
48
49--
50-- Table structure for table `t_items`
51--
52
53CREATE TABLE IF NOT EXISTS `t_items` (
54 `id_item` int(7) NOT NULL,
55 `nama_item` varchar(255) NOT NULL,
56 `harga` int(10) NOT NULL,
57 `berat` int(5) NOT NULL,
58 `status` tinyint(1) NOT NULL,
59 `gambar` varchar(255) NOT NULL,
60 `deskripsi` text NOT NULL,
61 `stock` int(5) NOT NULL
62) ENGINE=InnoDB DEFAULT CHARSET=latin1;
63
64-- --------------------------------------------------------
65
66--
67-- Table structure for table `t_kategori`
68--
69
70CREATE TABLE IF NOT EXISTS `t_kategori` (
71 `id_kategori` smallint(6) NOT NULL,
72 `kategori` varchar(30) NOT NULL,
73 `url` varchar(30) NOT NULL
74) ENGINE=InnoDB DEFAULT CHARSET=latin1;
75
76-- --------------------------------------------------------
77
78--
79-- Table structure for table `t_konfirmasi`
80--
81
82CREATE TABLE IF NOT EXISTS `t_konfirmasi` (
83 `id_konfirm` int(10) NOT NULL,
84 `id_order` int(15) NOT NULL,
85 `bukti_tf` text NOT NULL
86) ENGINE=InnoDB DEFAULT CHARSET=latin1;
87
88-- --------------------------------------------------------
89
90--
91-- Table structure for table `t_order`
92--
93
94CREATE TABLE IF NOT EXISTS `t_order` (
95 `id_order` int(15) NOT NULL,
96 `id_user` int(7) NOT NULL,
97 `total` double NOT NULL,
98 `tujuan` varchar(255) NOT NULL,
99 `pos` int(5) NOT NULL,
100 `kota` varchar(25) NOT NULL,
101 `kurir` varchar(5) NOT NULL,
102 `service` varchar(50) NOT NULL,
103 `tgl_pesan` date NOT NULL,
104 `bts_bayar` date NOT NULL,
105 `status` enum('belum','proses','lain-lain','batal') NOT NULL
106) ENGINE=InnoDB DEFAULT CHARSET=latin1;
107
108--
109-- Triggers `t_order`
110--
111DELIMITER $$
112CREATE TRIGGER `detail_after_update` AFTER UPDATE ON `t_order`
113 FOR EACH ROW begin
114
115insert into z_history(action,id_reff,type) values(new.status,new.id_order,'order');
116
117if new.status = 'proses' then
118begin
119 update t_order_detail
120 set proses=1
121 where id_order=new.id_order;
122end;
123end if;
124
125end
126$$
127DELIMITER ;
128
129-- --------------------------------------------------------
130
131--
132-- Table structure for table `t_order_detail`
133--
134
135CREATE TABLE IF NOT EXISTS `t_order_detail` (
136 `id_row` int(11) NOT NULL,
137 `id_order` int(10) NOT NULL,
138 `id_item` int(7) NOT NULL,
139 `qty` int(4) NOT NULL,
140 `biaya` int(9) NOT NULL,
141 `proses` tinyint(4) NOT NULL DEFAULT '0'
142) ENGINE=InnoDB DEFAULT CHARSET=latin1;
143
144--
145-- Triggers `t_order_detail`
146--
147DELIMITER $$
148CREATE TRIGGER `update_stock_2` AFTER UPDATE ON `t_order_detail`
149 FOR EACH ROW begin
150
151insert into z_history(action,id_reff,type) values(new.proses,new.id_row,'order detail');
152
153if new.proses = 1 then
154begin
155 update t_items
156 set stock=stock-new.qty
157 where id_item=new.id_item;
158end;
159end if;
160
161END
162$$
163DELIMITER ;
164
165-- --------------------------------------------------------
166
167--
168-- Table structure for table `t_rkategori`
169--
170
171CREATE TABLE IF NOT EXISTS `t_rkategori` (
172 `id_row` int(5) NOT NULL,
173 `id_item` int(7) NOT NULL,
174 `id_kategori` smallint(6) NOT NULL
175) ENGINE=InnoDB DEFAULT CHARSET=latin1;
176
177-- --------------------------------------------------------
178
179--
180-- Table structure for table `t_users`
181--
182
183CREATE TABLE IF NOT EXISTS `t_users` (
184 `id_user` int(7) NOT NULL,
185 `username` varchar(35) NOT NULL,
186 `fullname` varchar(100) NOT NULL,
187 `email` varchar(100) NOT NULL,
188 `password` varchar(255) NOT NULL,
189 `jk` enum('L','P') NOT NULL,
190 `telp` varchar(20) NOT NULL,
191 `alamat` text NOT NULL,
192 `status` tinyint(1) NOT NULL,
193 `reset` varchar(35) NOT NULL
194) ENGINE=InnoDB DEFAULT CHARSET=latin1;
195
196-- --------------------------------------------------------
197
198--
199-- Table structure for table `z_history`
200--
201
202CREATE TABLE IF NOT EXISTS `z_history` (
203 `id` int(11) NOT NULL,
204 `id_reff` int(11) NOT NULL,
205 `action` varchar(30) NOT NULL,
206 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
207 `type` varchar(100) NOT NULL
208) ENGINE=MyISAM DEFAULT CHARSET=latin1;
209
210--
211-- Indexes for dumped tables
212--
213
214--
215-- Indexes for table `t_admin`
216--
217ALTER TABLE `t_admin`
218 ADD PRIMARY KEY (`id_admin`),
219 ADD UNIQUE KEY `username` (`username`),
220 ADD UNIQUE KEY `email` (`email`);
221
222--
223-- Indexes for table `t_detail_order`
224--
225ALTER TABLE `t_detail_order`
226 ADD PRIMARY KEY (`id_row`);
227
228--
229-- Indexes for table `t_items`
230--
231ALTER TABLE `t_items`
232 ADD PRIMARY KEY (`id_item`);
233
234--
235-- Indexes for table `t_kategori`
236--
237ALTER TABLE `t_kategori`
238 ADD PRIMARY KEY (`id_kategori`);
239
240--
241-- Indexes for table `t_konfirmasi`
242--
243ALTER TABLE `t_konfirmasi`
244 ADD PRIMARY KEY (`id_konfirm`);
245
246--
247-- Indexes for table `t_order`
248--
249ALTER TABLE `t_order`
250 ADD PRIMARY KEY (`id_order`);
251
252--
253-- Indexes for table `t_order_detail`
254--
255ALTER TABLE `t_order_detail`
256 ADD PRIMARY KEY (`id_row`),
257 ADD KEY `proses` (`proses`);
258
259--
260-- Indexes for table `t_rkategori`
261--
262ALTER TABLE `t_rkategori`
263 ADD PRIMARY KEY (`id_row`);
264
265--
266-- Indexes for table `t_users`
267--
268ALTER TABLE `t_users`
269 ADD PRIMARY KEY (`id_user`),
270 ADD UNIQUE KEY `username` (`username`),
271 ADD UNIQUE KEY `email` (`email`);
272
273--
274-- Indexes for table `z_history`
275--
276ALTER TABLE `z_history`
277 ADD PRIMARY KEY (`id`),
278 ADD KEY `id_reff` (`id_reff`),
279 ADD KEY `type` (`type`);
280
281--
282-- AUTO_INCREMENT for dumped tables
283--
284
285--
286-- AUTO_INCREMENT for table `t_admin`
287--
288ALTER TABLE `t_admin`
289 MODIFY `id_admin` tinyint(2) NOT NULL AUTO_INCREMENT;
290--
291-- AUTO_INCREMENT for table `t_detail_order`
292--
293ALTER TABLE `t_detail_order`
294 MODIFY `id_row` int(11) NOT NULL AUTO_INCREMENT;
295--
296-- AUTO_INCREMENT for table `t_items`
297--
298ALTER TABLE `t_items`
299 MODIFY `id_item` int(7) NOT NULL AUTO_INCREMENT;
300--
301-- AUTO_INCREMENT for table `t_kategori`
302--
303ALTER TABLE `t_kategori`
304 MODIFY `id_kategori` smallint(6) NOT NULL AUTO_INCREMENT;
305--
306-- AUTO_INCREMENT for table `t_konfirmasi`
307--
308ALTER TABLE `t_konfirmasi`
309 MODIFY `id_konfirm` int(10) NOT NULL AUTO_INCREMENT;
310--
311-- AUTO_INCREMENT for table `t_order`
312--
313ALTER TABLE `t_order`
314 MODIFY `id_order` int(15) NOT NULL AUTO_INCREMENT;
315--
316-- AUTO_INCREMENT for table `t_order_detail`
317--
318ALTER TABLE `t_order_detail`
319 MODIFY `id_row` int(11) NOT NULL AUTO_INCREMENT;
320--
321-- AUTO_INCREMENT for table `t_rkategori`
322--
323ALTER TABLE `t_rkategori`
324 MODIFY `id_row` int(5) NOT NULL AUTO_INCREMENT;
325--
326-- AUTO_INCREMENT for table `t_users`
327--
328ALTER TABLE `t_users`
329 MODIFY `id_user` int(7) NOT NULL AUTO_INCREMENT;
330--
331-- AUTO_INCREMENT for table `z_history`
332--
333ALTER TABLE `z_history`
334 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;