· 9 years ago · Oct 11, 2016, 06:56 AM
1/*
2SQLyog Ultimate v11.5 (64 bit)
3MySQL - 5.6.14 : Database - k5927314_dnd_penjualan
4*********************************************************************
5*/
6
7
8/*!40101 SET NAMES utf8 */;
9
10/*!40101 SET SQL_MODE=''*/;
11
12/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
13/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
14/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
15/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
16CREATE DATABASE /*!32312 IF NOT EXISTS*/`xxxxxx_penjualan_dnd` /*!40100 DEFAULT CHARACTER SET latin1 */;
17
18USE `xxxxxxx_penjualan_dnd`;
19
20/*Table structure for table `may_barang` */
21
22DROP TABLE IF EXISTS `may_barang`;
23
24CREATE TABLE `may_barang` (
25 `kdbarang` varchar(5) NOT NULL,
26 `nama_barang` varchar(100) NOT NULL,
27 `harga_beli` decimal(10,2) NOT NULL,
28 `harga_jual` decimal(10,2) NOT NULL,
29 `stok` smallint(3) NOT NULL,
30 `lokasi` varchar(20) NOT NULL,
31 `gambar` varchar(100) NOT NULL,
32 `idkat` int(3) NOT NULL,
33 `ids` tinyint(1) NOT NULL,
34 PRIMARY KEY (`kdbarang`)
35) ENGINE=InnoDB DEFAULT CHARSET=latin1;
36
37/*Data for the table `may_barang` */
38
39insert into `may_barang`(`kdbarang`,`nama_barang`,`harga_beli`,`harga_jual`,`stok`,`lokasi`,`gambar`,`idkat`,`ids`) values ('B0001','Back cover samsung note 2 - Wood','10000.00','15000.00',10,'A1','',2,1),('B0002','Pulsa Telkomsel 100','88000.00','98000.00',99,'-','',5,1);
40
41/*Table structure for table `may_grouplvlmdl` */
42
43DROP TABLE IF EXISTS `may_grouplvlmdl`;
44
45CREATE TABLE `may_grouplvlmdl` (
46 `idGroupLM` int(8) unsigned NOT NULL AUTO_INCREMENT,
47 `idlevel` tinyint(2) DEFAULT NULL,
48 `modulID` tinyint(3) DEFAULT NULL,
49 `r` enum('Y','N') NOT NULL DEFAULT 'N',
50 `c` varchar(1) NOT NULL DEFAULT 'N',
51 `e` varchar(1) NOT NULL DEFAULT 'N',
52 `d` varchar(1) NOT NULL DEFAULT 'N',
53 `userID` varchar(15) NOT NULL,
54 PRIMARY KEY (`idGroupLM`),
55 KEY `idlevel` (`idlevel`),
56 KEY `modulID` (`modulID`),
57 CONSTRAINT `may_grouplvlmdl_ibfk_1` FOREIGN KEY (`idlevel`) REFERENCES `may_level` (`idlevel`) ON DELETE CASCADE ON UPDATE CASCADE,
58 CONSTRAINT `may_grouplvlmdl_ibfk_2` FOREIGN KEY (`modulID`) REFERENCES `may_modul` (`modulID`) ON DELETE CASCADE ON UPDATE CASCADE
59) ENGINE=InnoDB AUTO_INCREMENT=164 DEFAULT CHARSET=latin1;
60
61/*Data for the table `may_grouplvlmdl` */
62
63insert into `may_grouplvlmdl`(`idGroupLM`,`idlevel`,`modulID`,`r`,`c`,`e`,`d`,`userID`) values (1,1,0,'Y','Y','Y','Y','yuda'),(2,1,1,'Y','Y','Y','Y','CED'),(3,1,2,'Y','Y','Y','Y','CED'),(4,1,3,'Y','Y','Y','Y','CED'),(5,1,4,'Y','Y','Y','Y','CED'),(6,1,5,'Y','Y','Y','Y','CED'),(7,1,6,'Y','Y','Y','Y','CED'),(8,1,7,'Y','Y','Y','Y','CED'),(9,1,8,'Y','Y','Y','Y','CED'),(10,1,15,'Y','Y','Y','Y','CED'),(153,15,1,'N','N','N','N','yuda'),(154,15,2,'N','N','N','N','yuda'),(155,15,3,'N','N','N','N','yuda'),(156,15,4,'N','N','N','N','yuda'),(157,15,5,'Y','Y','Y','N','yuda'),(158,15,6,'Y','Y','Y','N','yuda'),(159,15,7,'N','N','N','N','yuda'),(160,15,8,'Y','Y','Y','Y','yuda'),(161,15,15,'N','N','N','N','yuda');
64
65/*Table structure for table `may_kategori` */
66
67DROP TABLE IF EXISTS `may_kategori`;
68
69CREATE TABLE `may_kategori` (
70 `idkat` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
71 `ids` tinyint(1) unsigned NOT NULL,
72 `nama_kat` varchar(50) NOT NULL,
73 PRIMARY KEY (`idkat`)
74) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
75
76/*Data for the table `may_kategori` */
77
78insert into `may_kategori`(`idkat`,`ids`,`nama_kat`) values (2,1,'Aksesoris HP'),(3,1,'Jasa'),(4,1,'Jewellery'),(5,1,'Voucher'),(6,1,'Food');
79
80/*Table structure for table `may_level` */
81
82DROP TABLE IF EXISTS `may_level`;
83
84CREATE TABLE `may_level` (
85 `idlevel` tinyint(2) NOT NULL AUTO_INCREMENT,
86 `lvl` varchar(35) NOT NULL,
87 `aktif` enum('Y','N') NOT NULL,
88 `CreatedBy` varchar(15) NOT NULL,
89 PRIMARY KEY (`idlevel`)
90) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;
91
92/*Data for the table `may_level` */
93
94insert into `may_level`(`idlevel`,`lvl`,`aktif`,`CreatedBy`) values (1,'Super Administrator','Y','yuda'),(15,'Kasir','Y','yuda');
95
96/*Table structure for table `may_lgnhistories` */
97
98DROP TABLE IF EXISTS `may_lgnhistories`;
99
100CREATE TABLE `may_lgnhistories` (
101 `idLgn` int(8) unsigned NOT NULL AUTO_INCREMENT,
102 `username` varchar(15) NOT NULL,
103 `ip` varchar(20) NOT NULL,
104 `jam` datetime DEFAULT NULL,
105 `hostname` varchar(100) NOT NULL,
106 `browser` varchar(17) NOT NULL,
107 `ket` enum('IN','OUT') NOT NULL,
108 PRIMARY KEY (`idLgn`)
109) ENGINE=InnoDB DEFAULT CHARSET=latin1;
110
111/*Data for the table `may_lgnhistories` */
112
113/*Table structure for table `may_menu` */
114
115DROP TABLE IF EXISTS `may_menu`;
116
117CREATE TABLE `may_menu` (
118 `menuID` tinyint(3) NOT NULL AUTO_INCREMENT,
119 `menu` varchar(30) NOT NULL,
120 `aktif` enum('Y','N') DEFAULT NULL,
121 `sort` tinyint(2) NOT NULL,
122 PRIMARY KEY (`menuID`)
123) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
124
125/*Data for the table `may_menu` */
126
127insert into `may_menu`(`menuID`,`menu`,`aktif`,`sort`) values (1,'Data Master','Y',2),(2,'Transaksi','Y',3),(3,'Laporan','Y',4),(5,'My Account','Y',1),(6,'Settings','Y',5);
128
129/*Table structure for table `may_modul` */
130
131DROP TABLE IF EXISTS `may_modul`;
132
133CREATE TABLE `may_modul` (
134 `modulID` tinyint(3) NOT NULL AUTO_INCREMENT,
135 `nama_modul` varchar(30) NOT NULL,
136 `modul_folder` varchar(50) DEFAULT NULL,
137 `aktif` enum('Y','N') NOT NULL DEFAULT 'N',
138 `createdBy` varchar(15) NOT NULL,
139 `menuID` tinyint(3) DEFAULT NULL,
140 PRIMARY KEY (`modulID`)
141) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;
142
143/*Data for the table `may_modul` */
144
145insert into `may_modul`(`modulID`,`nama_modul`,`modul_folder`,`aktif`,`createdBy`,`menuID`) values (0,'system','system','Y','yuda',NULL),(1,'kategori','kategori','Y','yuda',1),(2,'suplier','suplier','Y','yuda',1),(3,'barang','barang','Y','yuda',1),(4,'user','user','Y','yuda',1),(5,'penjualan','trx_penjualan','Y','yuda',2),(6,'pembelian','trx_pembelian','Y','yuda',2),(7,'all reports','laporan','Y','yuda',3),(8,'profile','profile','Y','yuda',5),(15,'general','settings','Y','yuda',6);
146
147/*Table structure for table `may_paramchild` */
148
149DROP TABLE IF EXISTS `may_paramchild`;
150
151CREATE TABLE `may_paramchild` (
152 `idpc` tinyint(5) NOT NULL AUTO_INCREMENT,
153 `idpm` int(3) NOT NULL,
154 `child_name` varchar(50) NOT NULL,
155 `ket` text,
156 `aktif` varchar(1) DEFAULT 'Y',
157 PRIMARY KEY (`idpc`)
158) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
159
160/*Data for the table `may_paramchild` */
161
162insert into `may_paramchild`(`idpc`,`idpm`,`child_name`,`ket`,`aktif`) values (1,1,'Y','Ya','Y'),(2,1,'N','Tidak','Y'),(3,2,'Pembelian','report/pembelian/rpt_pembelian','Y'),(4,2,'Penjualan','report/penjualan/rpt_penjualan','Y'),(5,2,'Stok Barang','report/stok/rpt_stok','Y'),(6,2,'abc','abc','N');
163
164/*Table structure for table `may_parameter` */
165
166DROP TABLE IF EXISTS `may_parameter`;
167
168CREATE TABLE `may_parameter` (
169 `idpm` int(3) NOT NULL AUTO_INCREMENT,
170 `nama_param` varchar(50) NOT NULL,
171 `ket` text,
172 `userid` varchar(15) NOT NULL,
173 PRIMARY KEY (`idpm`)
174) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
175
176/*Data for the table `may_parameter` */
177
178insert into `may_parameter`(`idpm`,`nama_param`,`ket`,`userid`) values (1,'stdChoices','Pilihan Y/N',''),(2,'RptParam','Parameter untuk laporan','yuda'),(3,'TestParam','cuma tester','yuda');
179
180/*Table structure for table `may_pembelian` */
181
182DROP TABLE IF EXISTS `may_pembelian`;
183
184CREATE TABLE `may_pembelian` (
185 `kdPembelian` varchar(20) NOT NULL,
186 `no_nota` varchar(20) NOT NULL,
187 `total_pembelian` decimal(10,2) NOT NULL,
188 `kdsup` smallint(1) NOT NULL,
189 `userID` varchar(15) NOT NULL,
190 `tgl_input` datetime NULL ,
191 `tgl_beli` date NOT NULL,
192 `ids` tinyint(1) unsigned NOT NULL,
193 PRIMARY KEY (`kdPembelian`)
194) ENGINE=InnoDB DEFAULT CHARSET=latin1;
195
196/*Data for the table `may_pembelian` */
197
198/*Table structure for table `may_pembeliandtl` */
199
200DROP TABLE IF EXISTS `may_pembeliandtl`;
201
202CREATE TABLE `may_pembeliandtl` (
203 `idDtlPembelian` int(5) NOT NULL AUTO_INCREMENT,
204 `kdPembelian` varchar(20) NOT NULL,
205 `kd_barang` varchar(5) NOT NULL,
206 `qty_beli` smallint(3) NOT NULL,
207 `harga_beli` decimal(10,2) NOT NULL,
208 `total` decimal(10,2) NOT NULL,
209 PRIMARY KEY (`idDtlPembelian`)
210) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=latin1;
211
212/*Data for the table `may_pembeliandtl` */
213
214insert into `may_pembeliandtl`(`idDtlPembelian`,`kdPembelian`,`kd_barang`,`qty_beli`,`harga_beli`,`total`) values (1,'P-HYG201504040000001','B0012',2,'52400.00','104800.00'),(2,'P-HYG201504040000001','B0013',3,'52400.00','157200.00'),(3,'P-HYG201504090000002','B0018',2,'26000.00','52000.00'),(4,'P-HYG201504090000002','B0019',1,'14000.00','14000.00'),(5,'P-HYG201504090000003','B0018',1,'26000.00','26000.00'),(6,'P-HYG201504090000003','B0019',2,'14000.00','28000.00'),(7,'P-HYG201504090000004','B0019',2,'14000.00','28000.00'),(8,'P-HYG201504090000004','B0018',1,'26000.00','26000.00'),(9,'P-HYG201504090000005','B0019',2,'14000.00','28000.00'),(10,'P-HYG201504090000005','B0018',1,'26000.00','26000.00'),(11,'P-HYG201504090000006','B0018',2,'26000.00','52000.00'),(12,'P-HYG201504090000006','B0019',1,'14000.00','14000.00'),(13,'P-HYG201504190000007','B0011',1,'38900.00','38900.00'),(14,'P-HYG201504190000008','B0003',2,'46900.00','93800.00'),(15,'P-HYG201504190000008','B0002',1,'46900.00','46900.00'),(16,'P-HYG201504190000008','B0021',2,'46900.00','93800.00'),(17,'P-HYG201504190000008','B0022',1,'46900.00','46900.00'),(18,'P-HYG201504190000009','B0024',3,'56300.00','168900.00'),(19,'P-HYG201504190000009','B0025',1,'56300.00','56300.00'),(20,'P-HYG201504190000009','B0026',2,'56300.00','112600.00'),(21,'P-HYG201504190000010','B0027',3,'48300.00','144900.00'),(22,'P-HYG201504190000010','B0028',3,'36233.33','108700.00'),(23,'P-HYG201504190000010','B0029',1,'48181.00','48181.00'),(24,'P-HYG201504190000010','B0030',1,'47792.00','47792.00'),(25,'P-HYG201504190000011','B0031',2,'51500.00','103000.00'),(26,'P-HYG201504190000012','B0019',4,'19000.00','76000.00'),(27,'P-HYG201504190000013','B0003',3,'47532.33','142597.00'),(28,'P-HYG201504190000013','B0032',3,'47402.33','142207.00');
215
216/*Table structure for table `may_penjualan` */
217
218DROP TABLE IF EXISTS `may_penjualan`;
219
220CREATE TABLE `may_penjualan` (
221 `kd_penjualan` varchar(20) NOT NULL,
222 `ids` tinyint(1) unsigned NOT NULL,
223 `customer` varchar(25) NOT NULL DEFAULT 'Guest',
224 `tgl_input` datetime DEFAULT NULL,
225 `tgl_jual` date DEFAULT NULL,
226 `subtotal` decimal(10,2) NOT NULL,
227 `diskon` decimal(8,2) NOT NULL,
228 `grand_total` decimal(10,2) NOT NULL,
229 `uang_bayar` decimal(10,2) NOT NULL,
230 `uang_kembali` decimal(10,2) NOT NULL,
231 `keterangan` text,
232 `userID` varchar(15) NOT NULL,
233 `date_lastUpdate` datetime DEFAULT NULL,
234 `user_lastUpdate` varchar(15) DEFAULT NULL,
235 PRIMARY KEY (`kd_penjualan`)
236) ENGINE=InnoDB DEFAULT CHARSET=latin1;
237
238/*Data for the table `may_penjualan` */
239
240insert into `may_penjualan`(`kd_penjualan`,`ids`,`customer`,`tgl_input`,`tgl_jual`,`subtotal`,`diskon`,`grand_total`,`uang_bayar`,`uang_kembali`,`keterangan`,`userID`,`date_lastUpdate`,`user_lastUpdate`) values ('INV-2015052400000001',1,'Yuda','2015-05-24 13:17:37','2015-05-24','98000.00','0.00','98000.00','100000.00','2000.00','0813111111189','remoxp',NULL,NULL);
241
242/*Table structure for table `may_penjualandtl` */
243
244DROP TABLE IF EXISTS `may_penjualandtl`;
245
246CREATE TABLE `may_penjualandtl` (
247 `idDtlPenjualan` int(8) unsigned NOT NULL AUTO_INCREMENT,
248 `kd_penjualan` varchar(20) NOT NULL,
249 `kd_barang` varchar(5) NOT NULL,
250 `harga_jual` decimal(10,2) NOT NULL,
251 `diskon` decimal(10,2) NOT NULL,
252 `qty` smallint(3) NOT NULL,
253 `harga_beli` decimal(10,2) NOT NULL DEFAULT '0.00',
254 `total_harga` decimal(10,2) NOT NULL,
255 `userID` varchar(15) NOT NULL,
256 `tgl_input` datetime DEFAULT NULL,
257 `date_lastUpdate` datetime DEFAULT NULL,
258 `user_lastUpdate` varchar(15) DEFAULT NULL,
259 PRIMARY KEY (`idDtlPenjualan`),
260 KEY `kd_penjualan` (`kd_penjualan`),
261 CONSTRAINT `may_penjualandtl_ibfk_1` FOREIGN KEY (`kd_penjualan`) REFERENCES `may_penjualan` (`kd_penjualan`) ON DELETE CASCADE ON UPDATE CASCADE
262) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=latin1;
263
264/*Data for the table `may_penjualandtl` */
265
266insert into `may_penjualandtl`(`idDtlPenjualan`,`kd_penjualan`,`kd_barang`,`harga_jual`,`diskon`,`qty`,`harga_beli`,`total_harga`,`userID`,`tgl_input`,`date_lastUpdate`,`user_lastUpdate`) values (36,'INV-2015052400000001','B0002','98000.00','0.00',1,'88000.00','98000.00','yuda','2015-05-24 13:17:37','2015-05-24 13:17:37','yuda');
267
268/*Table structure for table `may_rptpriv` */
269
270DROP TABLE IF EXISTS `may_rptpriv`;
271
272CREATE TABLE `may_rptpriv` (
273 `idparam` tinyint(5) NOT NULL,
274 `idlevel` tinyint(2) NOT NULL,
275 `userid` varchar(15) NOT NULL,
276 `akses` enum('Y','N') NOT NULL,
277 KEY `idlevel` (`idlevel`),
278 CONSTRAINT `may_rptpriv_ibfk_1` FOREIGN KEY (`idlevel`) REFERENCES `may_level` (`idlevel`) ON DELETE CASCADE ON UPDATE CASCADE
279) ENGINE=InnoDB DEFAULT CHARSET=latin1;
280
281/*Data for the table `may_rptpriv` */
282
283insert into `may_rptpriv`(`idparam`,`idlevel`,`userid`,`akses`) values (3,1,'yuda','Y'),(4,1,'yuda','Y'),(5,1,'yuda','Y'),(0,1,'yuda','N');
284
285/*Table structure for table `may_settings` */
286
287DROP TABLE IF EXISTS `may_settings`;
288
289CREATE TABLE `may_settings` (
290 `ids` tinyint(1) unsigned NOT NULL AUTO_INCREMENT,
291 `kdSET` varchar(3) NOT NULL,
292 `nama_toko` varchar(100) NOT NULL,
293 `alamat` text,
294 `keckab` varchar(200) DEFAULT NULL,
295 `tlp` varchar(20) DEFAULT NULL,
296 `printer` varchar(100) NOT NULL,
297 `url_web` varchar(200) NOT NULL,
298 `logo_toko` varchar(100) NOT NULL,
299 `last_update` varchar(20) NOT NULL,
300 `folder_modul` varchar(20) NOT NULL,
301 `limit_page` tinyint(1) unsigned DEFAULT NULL,
302 PRIMARY KEY (`ids`)
303) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
304
305/*Data for the table `may_settings` */
306
307insert into `may_settings`(`ids`,`kdSET`,`nama_toko`,`alamat`,`keckab`,`tlp`,`printer`,`url_web`,`logo_toko`,`last_update`,`folder_modul`,`limit_page`) values (1,'YPS','ySTORE','www.remo-xp.com','Curug - Tangerang','089651224881','Canon MP230 series Printer','http://localhost/mylabs/may/','','remoxp','modul',15);
308
309/*Table structure for table `may_suplier` */
310
311DROP TABLE IF EXISTS `may_suplier`;
312
313CREATE TABLE `may_suplier` (
314 `kdsup` smallint(1) unsigned NOT NULL AUTO_INCREMENT,
315 `ids` tinyint(1) unsigned NOT NULL,
316 `nama_sup` varchar(100) DEFAULT NULL,
317 `tlp` varchar(20) NOT NULL,
318 `alamat` text,
319 `date_create` date DEFAULT NULL,
320 PRIMARY KEY (`kdsup`)
321) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
322
323/*Data for the table `may_suplier` */
324
325insert into `may_suplier`(`kdsup`,`ids`,`nama_sup`,`tlp`,`alamat`,`date_create`) values (1,1,'CV. ABC','081283741777','Mangga dua, Jakarta','2015-05-24');
326
327/*Table structure for table `may_users` */
328
329DROP TABLE IF EXISTS `may_users`;
330
331CREATE TABLE `may_users` (
332 `username` varchar(15) NOT NULL,
333 `nama_lengkap` varchar(40) NOT NULL,
334 `pass` varchar(35) NOT NULL,
335 `hp` varchar(15) NOT NULL,
336 `sessionID` varchar(100) NOT NULL DEFAULT '0',
337 `online` enum('Y','N') NOT NULL DEFAULT 'N',
338 `aktif` enum('Y','N') NOT NULL DEFAULT 'Y',
339 `level` tinyint(2) NOT NULL,
340 `ids` tinyint(1) unsigned NOT NULL,
341 `last_seen` datetime NOT NULL,
342 PRIMARY KEY (`username`),
343 KEY `may_users_ibfk_1` (`level`),
344 CONSTRAINT `may_users_ibfk_1` FOREIGN KEY (`level`) REFERENCES `may_level` (`idlevel`) ON DELETE CASCADE ON UPDATE CASCADE
345) ENGINE=InnoDB DEFAULT CHARSET=latin1;
346
347/*Data for the table `may_users` */
348
349insert into `may_users`(`username`,`nama_lengkap`,`pass`,`hp`,`sessionID`,`online`,`aktif`,`level`,`ids`,`last_seen`) values ('demo','may Demo','fe01ce2a7fbac8fafaed7c982a04e229','1234567890','','N','Y',15,1,'2015-05-24 02:40:27'),('remoxp','Yuda','dae2df6ff118b1df840223714eb1830a','081311000512','','N','Y',1,1,'2015-05-24 13:56:22');
350
351/* Trigger structure for table `may_pembeliandtl` */
352
353DELIMITER $$
354
355/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `T_UpdStok_AI_Pembelian` */$$
356
357/*!50003 CREATE */ /*!50017 DEFINER = 'k8261583_umay28'@'localhost' */ /*!50003 TRIGGER `T_UpdStok_AI_Pembelian` AFTER INSERT ON `may_pembeliandtl` FOR EACH ROW BEGIN
358
359
360 UPDATE may_barang SET stok=stok + NEW.qty_beli WHERE kdbarang=NEW.kd_barang;
361
362
363 END */$$
364
365
366DELIMITER ;
367
368/* Trigger structure for table `may_pembeliandtl` */
369
370DELIMITER $$
371
372/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `T_UpdStok_AU_Pembelian` */$$
373
374/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `T_UpdStok_AU_Pembelian` AFTER UPDATE ON `may_pembeliandtl` FOR EACH ROW BEGIN
375
376
377 UPDATE may_barang SET stok=stok - old.qty_beli WHERE kdbarang=old.kd_barang;
378
379
380 UPDATE may_barang SET stok=stok + new.qty_beli WHERE kdbarang=new.kd_barang;
381
382
383 END */$$
384
385
386DELIMITER ;
387
388/* Trigger structure for table `may_pembeliandtl` */
389
390DELIMITER $$
391
392/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `T_UpdStok_AD_Pembelian` */$$
393
394/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `T_UpdStok_AD_Pembelian` AFTER DELETE ON `may_pembeliandtl` FOR EACH ROW BEGIN
395
396
397 UPDATE may_barang SET stok=stok - OLD.qty_beli WHERE kdbarang=OLD.kd_barang;
398
399
400 END */$$
401
402
403DELIMITER ;
404
405/* Trigger structure for table `may_penjualandtl` */
406
407DELIMITER $$
408
409/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `T_UpdStok_AI_Penjualan` */$$
410
411/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `T_UpdStok_AI_Penjualan` AFTER INSERT ON `may_penjualandtl` FOR EACH ROW BEGIN
412
413
414 UPDATE may_barang SET stok=stok - NEW.qty WHERE kdbarang=NEW.kd_barang;
415
416
417 END */$$
418
419
420DELIMITER ;
421
422/* Trigger structure for table `may_penjualandtl` */
423
424DELIMITER $$
425
426/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `T_UpdStok_AU_Penjualan` */$$
427
428/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `T_UpdStok_AU_Penjualan` AFTER UPDATE ON `may_penjualandtl` FOR EACH ROW BEGIN
429
430
431 UPDATE may_barang SET stok=stok - old.qty WHERE kdbarang=old.kd_barang;
432
433
434 UPDATE may_barang SET stok=stok + new.qty WHERE kdbarang=new.kd_barang;
435
436
437 END */$$
438
439
440DELIMITER ;
441
442/* Trigger structure for table `may_penjualandtl` */
443
444DELIMITER $$
445
446/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `T_UpdStok_AD_Penjualan` */$$
447
448/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `T_UpdStok_AD_Penjualan` AFTER DELETE ON `may_penjualandtl` FOR EACH ROW BEGIN
449
450
451 UPDATE may_barang SET stok=stok + OLD.qty WHERE kdbarang=OLD.kd_barang;
452
453
454 END */$$
455
456
457DELIMITER ;
458
459/* Procedure structure for procedure `may_AddLvl` */
460
461/*!50003 DROP PROCEDURE IF EXISTS `may_AddLvl` */;
462
463DELIMITER $$
464
465/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `may_AddLvl`(
466
467in lname varchar(35),
468
469in userid varchar(15),
470
471out newID tinyint(3),
472
473OUT error VARCHAR(4)
474
475)
476BEGIN
477
478DECLARE proces INT DEFAULT 0;
479
480DECLARE last_id TINYINT(2);
481
482declare modID tinyint(3);
483
484DECLARE c_getModID CURSOR FOR SELECT modulID FROM may_modul WHERE modulID != 0 && 1=1;
485
486DECLARE CONTINUE HANDLER FOR NOT FOUND SET proces = 1;
487
488DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' BEGIN END ;
489
490IF EXISTS (SELECT 'x' FROM may_level WHERE lvl=lname) THEN
491
492 BEGIN
493
494 SET error = '1000' ;
495
496 END ;
497
498 ELSE -- jika tidak ada, eksekusi
499
500 BEGIN
501
502 INSERT INTO may_level SET lvl=lname, aktif='Y', createdBy=userid;
503
504 SET last_id = LAST_INSERT_ID();
505
506 OPEN c_getModID;
507
508 REPEAT
509
510 FETCH c_getModID INTO modID;
511
512 IF NOT proces THEN
513
514 INSERT INTO may_grouplvlmdl SET idlevel=last_id, modulID=modID, r='Y', userID=userid;
515
516 END IF;
517
518 UNTIL proces END REPEAT;
519
520 CLOSE c_getModID;
521
522 SET error='';
523
524 select last_id as newID;
525
526 END;
527
528END IF; -- end cek available data
529
530SELECT error;
531
532END */$$
533DELIMITER ;
534
535/* Procedure structure for procedure `may_modManag` */
536
537/*!50003 DROP PROCEDURE IF EXISTS `may_modManag` */;
538
539DELIMITER $$
540
541/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `may_modManag`(
542
543IN idlvl tinyint(2),
544
545IN modName varchar(30),
546
547in folder varchar(50),
548
549in aktif varchar(1),
550
551IN users varchar(15),
552
553in menuid tinyint(3),
554
555OUT error varchar(4)
556
557)
558BEGIN
559
560DECLARE proces INT DEFAULT 0;
561
562DECLARE j_mods TINYINT(5);
563
564DECLARE mods TINYINT(5);
565
566declare t_lvl tinyint(2);
567
568declare last_id tinyint(3);
569
570DECLARE c_getlvl CURSOR FOR select idlevel from may_level where 1=1;
571
572DECLARE CONTINUE HANDLER FOR NOT FOUND SET proces = 1;
573
574DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' BEGIN END ;
575
576if exists (select 'x' from may_modul where nama_modul=modName) then
577
578BEGIN
579
580 SET error = '1000' ;
581
582END ;
583
584ELSE -- jika tidak ada, eksekusi
585
586BEGIN
587
588INSERT into may_modul SET nama_modul=modName, modul_folder=folder, aktif=aktif, createdBy=users, menuID=menuId;
589
590set last_id = LAST_INSERT_ID();
591
592 open c_getlvl;
593
594 repeat
595
596 fetch c_getlvl into t_lvl;
597
598 if not proces then
599
600 INSERT INTO may_grouplvlmdl SET idlevel=t_lvl, modulID=last_id, userID=users;
601
602 end if;
603
604 until proces end repeat;
605
606 close c_getlvl;
607
608SET error='';
609
610END;
611
612END IF; -- end cek available data
613
614select error;
615
616END */$$
617DELIMITER ;
618
619/* Procedure structure for procedure `may_trxPembelianDtl` */
620
621/*!50003 DROP PROCEDURE IF EXISTS `may_trxPembelianDtl` */;
622
623DELIMITER $$
624
625/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `may_trxPembelianDtl`(
626
627
628 IN kdp VARCHAR (20),
629
630
631 IN nota VARCHAR (20),
632
633
634 IN kdsup SMALLINT (1),
635
636
637 IN userid VARCHAR (15),
638
639
640 IN tgl DATE,
641
642
643 IN kdb VARCHAR (5),
644
645
646 IN q_beli SMALLINT (3),
647
648
649 IN h_brg DECIMAL (10, 2),
650
651
652 IN ttl DECIMAL (10, 2),
653
654
655 OUT error VARCHAR (4)
656
657
658)
659BEGIN
660
661
662 DECLARE ttl_beli DECIMAL (10, 2) ;
663
664
665 DECLARE h_beli DECIMAL (10, 2) ;
666
667
668 DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
669
670
671 BEGIN
672
673
674 END ;
675
676
677 -- cek inputan baru atau bukan, jika bukan do it
678
679
680 IF EXISTS (SELECT 'x' FROM may_pembelian WHERE kdPembelian = kdp) THEN
681
682
683 BEGIN
684
685
686 IF EXISTS (SELECT 'x' FROM may_pembeliandtl WHERE kdPembelian = kdp && kd_barang = kdb) THEN -- cek barang yg di input, sudah ada apa blm di keranjang
687
688
689 BEGIN
690
691
692 SET error = '1001' ;
693
694
695 END ;
696
697
698 ELSE
699
700
701 BEGIN
702
703
704 INSERT INTO may_pembeliandtl SET kdPembelian = kdp,
705
706
707 kd_barang = kdb,
708
709
710 qty_beli = q_beli,
711
712
713 harga_beli = (ttl / q_beli),
714
715
716 total = ttl ;
717
718
719 SET ttl_beli = (SELECT DISTINCT SUM(total) AS t_harga FROM may_pembeliandtl WHERE kdPembelian = kdp) ;
720
721
722 SET h_brg = (SELECT harga_beli FROM may_barang WHERE kdbarang = kdb) ; -- cek harga barang satuan saat ini
723
724
725 UPDATE may_pembelian SET total_pembelian = ttl_beli WHERE kdPembelian = kdp ; -- update total pembelian
726
727
728
729
730
731 IF h_brg != (ttl / q_beli) THEN
732
733
734 BEGIN
735
736
737 UPDATE may_barang SET harga_beli = (ttl / q_beli) WHERE kdbarang = kdb ;
738
739
740 END ;
741
742
743 END IF ; -- end if cek harga barang dan update harga satuan
744
745
746 SET error = '' ;
747
748
749 END;
750
751
752 END IF; -- end cek item ada/nggak
753
754
755 END;
756
757
758 ELSE
759
760
761 BEGIN
762
763
764 INSERT INTO may_pembelian SET kdPembelian = kdp,
765
766
767 no_nota = nota,
768
769
770 kdsup = kdsup,
771
772
773 userID = userid,
774
775
776 tgl_input = NOW(),
777
778
779 tgl_beli = tgl,
780
781
782 ids = 1 ;
783
784
785 INSERT INTO may_pembeliandtl SET kdPembelian = kdp,
786
787
788 kd_barang = kdb,
789
790
791 qty_beli = q_beli,
792
793
794 harga_beli = (ttl / q_beli),
795
796
797 total = ttl ;
798
799
800 SET ttl_beli = (SELECT DISTINCT SUM(total) AS t_harga FROM may_pembeliandtl WHERE kdPembelian = kdp) ;
801
802
803 SET h_brg = (SELECT harga_beli FROM may_barang WHERE kdbarang = kdb) ;
804
805
806
807
808
809 -- cek harga barang satuan saat ini
810
811
812 UPDATE may_pembelian SET total_pembelian = ttl_beli WHERE kdPembelian = kdp ;
813
814
815 -- update total pembelian
816
817
818 IF h_brg != (ttl / q_beli) THEN
819
820
821 BEGIN
822
823
824 UPDATE may_barang SET harga_beli = (ttl / q_beli) WHERE kdbarang = kdb;
825
826
827
828 END ;
829
830
831 END IF ; -- end if cek harga barang
832
833
834 SET error = '' ;
835
836
837 END;
838
839
840 END IF;
841
842
843SELECT error;
844
845
846END */$$
847DELIMITER ;
848
849/* Procedure structure for procedure `may_trxPembelianDtl_delProd` */
850
851/*!50003 DROP PROCEDURE IF EXISTS `may_trxPembelianDtl_delProd` */;
852
853DELIMITER $$
854
855/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `may_trxPembelianDtl_delProd`(
856
857
858 in idp int(5),
859
860
861 IN kdp VARCHAR (20),
862
863
864 OUT error VARCHAR (4)
865
866
867)
868BEGIN
869
870
871 DECLARE ttl_beli DECIMAL (10, 2) ;
872
873
874 DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
875
876
877 BEGIN
878
879
880 END ;
881
882
883
884
885
886 delete from may_pembeliandtl where idDtlPembelian=idp && kdPembelian=kdp;
887
888
889 SET ttl_beli = (SELECT DISTINCT SUM(total) AS t_harga FROM may_pembeliandtl WHERE kdPembelian = kdp) ;
890
891
892 UPDATE may_pembelian SET total_pembelian = ttl_beli WHERE kdPembelian = kdp ; -- update total pembelian
893
894
895 SET error = '' ;
896
897
898SELECT error;
899
900
901END */$$
902DELIMITER ;
903
904/* Procedure structure for procedure `may_trxPenjualanDtl` */
905
906/*!50003 DROP PROCEDURE IF EXISTS `may_trxPenjualanDtl` */;
907
908DELIMITER $$
909
910/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `may_trxPenjualanDtl`(
911
912
913 IN kdp VARCHAR (20),
914
915
916 IN cust VARCHAR (25),
917
918
919 IN tgl DATE,
920
921
922 IN kdb VARCHAR (5),
923
924
925 IN h_jual DECIMAL (10, 2),
926
927
928 IN q_jual SMALLINT (3),
929
930
931 IN userid VARCHAR (15),
932
933
934 OUT error VARCHAR (4)
935
936
937)
938BEGIN
939
940
941 DECLARE ttl_h_brg DECIMAL (10, 2) ;
942
943
944 DECLARE brg_diskon DECIMAL (9, 2) ;
945
946
947 DECLARE h_beli DECIMAL (10, 2) ;
948
949
950 DECLARE ttl_harga_jual DECIMAL (10, 2) ;
951
952
953 DECLARE stok SMALLINT (3) ;
954
955
956 DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' BEGIN END ;
957
958
959 SET ttl_h_brg = (h_jual * q_jual) ;
960
961
962 SET stok =
963
964
965 (SELECT
966
967
968 a.stok
969
970
971 FROM
972
973
974 may_barang a
975
976
977 WHERE a.kdbarang = kdb);
978
979
980 -- select stok;
981
982
983 IF q_jual <= stok
984
985
986 THEN -- cek stok yg ada dengan qty jual
987
988
989 BEGIN
990
991
992 IF EXISTS
993
994
995 (SELECT
996
997
998 'x'
999
1000
1001 FROM
1002
1003
1004 may_penjualan
1005
1006
1007 WHERE kd_penjualan = kdp)
1008
1009
1010 THEN -- cek inputan baru apa bukan, jika buka do it now
1011
1012
1013 BEGIN
1014
1015
1016 IF EXISTS
1017
1018
1019 (SELECT
1020
1021
1022 'x'
1023
1024
1025 FROM
1026
1027
1028 may_penjualandtl
1029
1030
1031 WHERE kd_penjualan = kdp && kd_barang = kdb)
1032
1033
1034 THEN -- cek barang yg di input, sudah ada apa blm di keranjang
1035
1036
1037 BEGIN
1038
1039
1040 SET error = '1001' ;
1041
1042
1043 END ;
1044
1045
1046 ELSE -- jika tidak ada, eksekusi
1047
1048
1049 BEGIN
1050
1051
1052 SET brg_diskon =
1053
1054
1055 (SELECT DISTINCT
1056
1057
1058 SUM((harga_jual) - h_jual)
1059
1060
1061 FROM
1062
1063
1064 may_barang
1065
1066
1067 WHERE kdbarang = kdb) ;
1068
1069
1070 SET h_beli =
1071
1072
1073 (SELECT
1074
1075
1076 harga_beli
1077
1078
1079 FROM
1080
1081
1082 may_barang
1083
1084
1085 WHERE kdbarang = kdb) ;
1086
1087
1088 INSERT INTO may_penjualandtl SET kd_penjualan = kdp,
1089
1090
1091 kd_barang = kdb,
1092
1093
1094 harga_jual = h_jual,
1095
1096
1097 diskon = brg_diskon,
1098
1099
1100 qty = q_jual,
1101
1102
1103 harga_beli = h_beli,
1104
1105
1106 total_harga = ttl_h_brg,
1107
1108
1109 userID = userid,
1110
1111
1112 date_lastUpdate = NOW(),
1113
1114
1115 user_lastUpdate = userid ;
1116
1117
1118 SET ttl_harga_jual =
1119
1120
1121 (SELECT DISTINCT
1122
1123
1124 SUM(total_harga)
1125
1126
1127 FROM
1128
1129
1130 may_penjualandtl
1131
1132
1133 WHERE kd_penjualan = kdp) ;
1134
1135
1136 UPDATE
1137
1138
1139 may_penjualan
1140
1141
1142 SET
1143
1144
1145 subtotal = ttl_harga_jual
1146
1147
1148 WHERE kd_penjualan = kdp ;
1149
1150
1151 SET error = '' ;
1152
1153
1154 END ;
1155
1156
1157 END IF ;
1158
1159
1160 -- end cek kd_penjualan dan kd_barang
1161
1162
1163 END ;
1164
1165
1166 ELSE
1167
1168
1169 BEGIN
1170
1171
1172 -- jika data baru
1173
1174
1175 SET brg_diskon =
1176
1177
1178 (SELECT DISTINCT
1179
1180
1181 SUM((harga_jual) - h_jual)
1182
1183
1184 FROM
1185
1186
1187 may_barang
1188
1189
1190 WHERE kdbarang = kdb) ;
1191
1192
1193 SET h_beli =
1194
1195
1196 (SELECT
1197
1198
1199 harga_beli
1200
1201
1202 FROM
1203
1204
1205 may_barang
1206
1207
1208 WHERE kdbarang = kdb) ;
1209
1210
1211 INSERT INTO may_penjualan SET kd_penjualan = kdp,
1212
1213
1214 ids = 1,
1215
1216
1217 customer = cust,
1218
1219
1220 tgl_input = NOW(),
1221
1222
1223 tgl_jual = tgl,
1224
1225
1226 userID = userid ;
1227
1228
1229 INSERT INTO may_penjualandtl SET kd_penjualan = kdp,
1230
1231
1232 kd_barang = kdb,
1233
1234
1235 harga_jual = h_jual,
1236
1237
1238 diskon = brg_diskon,
1239
1240
1241 qty = q_jual,
1242
1243
1244 harga_beli = h_beli,
1245
1246
1247 total_harga = ttl_h_brg,
1248
1249
1250 userID = userid,
1251
1252
1253 date_lastUpdate = NOW(),
1254
1255
1256 user_lastUpdate = userid ;
1257
1258
1259 -- Dapetin total harga pembelian
1260
1261
1262 SET ttl_harga_jual =
1263
1264
1265 (SELECT DISTINCT
1266
1267
1268 SUM(total_harga)
1269
1270
1271 FROM
1272
1273
1274 may_penjualandtl
1275
1276
1277 WHERE kd_penjualan = kdp) ;
1278
1279
1280 UPDATE
1281
1282
1283 may_penjualan
1284
1285
1286 SET
1287
1288
1289 subtotal = ttl_harga_jual
1290
1291
1292 WHERE kd_penjualan = kdp ;
1293
1294
1295 SET error = '' ;
1296
1297
1298 END ;
1299
1300
1301 END IF ;
1302
1303
1304 -- end cek input baru atau bukan
1305
1306
1307 END ;
1308
1309
1310 ELSE
1311
1312
1313 BEGIN
1314
1315
1316 SET error = '1002' ;
1317
1318
1319 END ;
1320
1321
1322 END IF ;
1323
1324
1325 -- end cek stok
1326
1327
1328 SELECT error;
1329
1330
1331END */$$
1332DELIMITER ;
1333
1334/* Procedure structure for procedure `may_trxPenjualanDtl_update` */
1335
1336/*!50003 DROP PROCEDURE IF EXISTS `may_trxPenjualanDtl_update` */;
1337
1338DELIMITER $$
1339
1340/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `may_trxPenjualanDtl_update`(
1341
1342
1343 IN idp INT (8),
1344
1345
1346 IN kdp VARCHAR (20),
1347
1348
1349 IN q_jual SMALLINT (3),
1350
1351
1352 IN h_jual DECIMAL (10, 2),
1353
1354
1355 IN t_harga DECIMAL (10, 2),
1356
1357
1358 OUT error VARCHAR (4)
1359
1360
1361)
1362BEGIN
1363
1364
1365DECLARE stok SMALLINT (3) ;
1366
1367
1368DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' BEGIN END ;
1369
1370
1371SET stok = (SELECT
1372
1373
1374 a.stok
1375
1376
1377 FROM
1378
1379
1380 may_barang a, may_penjualandtl b
1381
1382
1383 WHERE idDtlPenjualan=idp && a.kdbarang = b.kd_barang);
1384
1385
1386 -- select stok;
1387
1388
1389
1390
1391
1392 IF q_jual <= stok THEN -- cek stok yg ada dengan qty jual
1393
1394
1395 BEGIN
1396
1397
1398 UPDATE
1399
1400
1401 may_penjualandtl
1402
1403
1404 SET
1405
1406
1407 qty = q_jual,
1408
1409
1410 harga_jual = h_jual,
1411
1412
1413 total_harga = t_harga
1414
1415
1416 WHERE idDtlPenjualan = idp && kd_penjualan = kdp ;
1417
1418
1419 UPDATE
1420
1421
1422 may_penjualan
1423
1424
1425 SET
1426
1427
1428 subtotal =
1429
1430
1431 (SELECT DISTINCT
1432
1433
1434 SUM(total_harga) AS t_harga
1435
1436
1437 FROM
1438
1439
1440 may_penjualandtl
1441
1442
1443 WHERE may_penjualandtl.kd_penjualan = kdp)
1444
1445
1446 WHERE kd_penjualan = kdp ;
1447
1448
1449 SET error = '' ;
1450
1451
1452 END;
1453
1454
1455 ELSE
1456
1457
1458 BEGIN
1459
1460
1461 SET error = '1002' ;
1462
1463
1464 END;
1465
1466
1467 END IF;
1468
1469
1470 SELECT error ;
1471
1472
1473END */$$
1474DELIMITER ;
1475
1476/*Table structure for table `may_vpembeliandtl` */
1477
1478DROP TABLE IF EXISTS `may_vpembeliandtl`;
1479
1480/*!50001 DROP VIEW IF EXISTS `may_vpembeliandtl` */;
1481/*!50001 DROP TABLE IF EXISTS `may_vpembeliandtl` */;
1482
1483/*!50001 CREATE TABLE `may_vpembeliandtl`(
1484 `kdbarang` varchar(5) ,
1485 `nama_barang` varchar(100) ,
1486 `harga_pokok_beli` decimal(10,2) ,
1487 `harga_pokok_jual` decimal(10,2) ,
1488 `kdsup` smallint(1) unsigned ,
1489 `nama_sup` varchar(100) ,
1490 `no_nota` varchar(20) ,
1491 `total_pembelian` decimal(10,2) ,
1492 `userID` varchar(15) ,
1493 `tgl_beli` date ,
1494 `idDtlPembelian` int(5) ,
1495 `kdPembelian` varchar(20) ,
1496 `qty_beli` smallint(3) ,
1497 `harga_beli_satBaru` decimal(10,2) ,
1498 `total_beli` decimal(10,2)
1499)*/;
1500
1501/*Table structure for table `may_vpenjualandtl` */
1502
1503DROP TABLE IF EXISTS `may_vpenjualandtl`;
1504
1505/*!50001 DROP VIEW IF EXISTS `may_vpenjualandtl` */;
1506/*!50001 DROP TABLE IF EXISTS `may_vpenjualandtl` */;
1507
1508/*!50001 CREATE TABLE `may_vpenjualandtl`(
1509 `kdbarang` varchar(5) ,
1510 `nama_barang` varchar(100) ,
1511 `harga_beli` decimal(10,2) ,
1512 `harga_jualstd` decimal(10,2) ,
1513 `idDtlPen` int(8) unsigned ,
1514 `kd_penjualan` varchar(20) ,
1515 `th_beli` decimal(37,2) ,
1516 `harga_jualreal` decimal(10,2) ,
1517 `qty` smallint(3) ,
1518 `diskon_produk` decimal(33,2) ,
1519 `pendapatan` decimal(39,2) ,
1520 `th_jual` decimal(10,2) ,
1521 `customer` varchar(25) ,
1522 `tgl_jual` date ,
1523 `diskon_final` decimal(8,2) ,
1524 `userID` varchar(15)
1525)*/;
1526
1527/*View structure for view may_vpembeliandtl */
1528
1529/*!50001 DROP TABLE IF EXISTS `may_vpembeliandtl` */;
1530/*!50001 DROP VIEW IF EXISTS `may_vpembeliandtl` */;
1531
1532/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `may_vpembeliandtl` AS select `a`.`kdbarang` AS `kdbarang`,`a`.`nama_barang` AS `nama_barang`,`a`.`harga_beli` AS `harga_pokok_beli`,`a`.`harga_jual` AS `harga_pokok_jual`,`may_suplier`.`kdsup` AS `kdsup`,`may_suplier`.`nama_sup` AS `nama_sup`,`b`.`no_nota` AS `no_nota`,`b`.`total_pembelian` AS `total_pembelian`,`b`.`userID` AS `userID`,`b`.`tgl_beli` AS `tgl_beli`,`c`.`idDtlPembelian` AS `idDtlPembelian`,`c`.`kdPembelian` AS `kdPembelian`,`c`.`qty_beli` AS `qty_beli`,`c`.`harga_beli` AS `harga_beli_satBaru`,`c`.`total` AS `total_beli` from (((`may_barang` `a` join `may_suplier`) join `may_pembelian` `b`) join `may_pembeliandtl` `c`) where ((`a`.`kdbarang` = `c`.`kd_barang`) and (`b`.`kdsup` = `may_suplier`.`kdsup`) and (`b`.`kdPembelian` = `c`.`kdPembelian`)) */;
1533
1534/*View structure for view may_vpenjualandtl */
1535
1536/*!50001 DROP TABLE IF EXISTS `may_vpenjualandtl` */;
1537/*!50001 DROP VIEW IF EXISTS `may_vpenjualandtl` */;
1538
1539/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `may_vpenjualandtl` AS select `a`.`kdbarang` AS `kdbarang`,`a`.`nama_barang` AS `nama_barang`,`a`.`harga_beli` AS `harga_beli`,`a`.`harga_jual` AS `harga_jualstd`,`b`.`idDtlPenjualan` AS `idDtlPen`,`b`.`kd_penjualan` AS `kd_penjualan`,sum((`a`.`harga_beli` * `b`.`qty`)) AS `th_beli`,`b`.`harga_jual` AS `harga_jualreal`,`b`.`qty` AS `qty`,(case when (`a`.`harga_jual` >= `b`.`harga_jual`) then sum((`a`.`harga_jual` - `b`.`harga_jual`)) when (`a`.`harga_jual` <= `b`.`harga_jual`) then 0 end) AS `diskon_produk`,sum(((`b`.`total_harga` - (`a`.`harga_beli` * `b`.`qty`)) - `c`.`diskon`)) AS `pendapatan`,`b`.`total_harga` AS `th_jual`,`c`.`customer` AS `customer`,`c`.`tgl_jual` AS `tgl_jual`,`c`.`diskon` AS `diskon_final`,`c`.`userID` AS `userID` from ((`may_barang` `a` join `may_penjualandtl` `b`) join `may_penjualan` `c`) where ((`a`.`kdbarang` = `b`.`kd_barang`) and (`b`.`kd_penjualan` = `c`.`kd_penjualan`)) group by `b`.`idDtlPenjualan` */;
1540
1541/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
1542/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
1543/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
1544/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;