Tyb48ttz

1-- phpMyAdmin SQL Dump
2-- version 4.7.4
3-- https://www.phpmyadmin.net/
4--
5-- Host: 127.0.0.1
6-- Generation Time: Jun 25, 2018 at 07:05 AM
7-- Server version: 5.7.19
8-- PHP Version: 7.1.8
9
10SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
11SET AUTOCOMMIT = 0;
12START TRANSACTION;
13SET time_zone = "+00:00";
14
15--
16-- Database: `jukir_db`
17--
18
19-- --------------------------------------------------------
20
21--
22-- Table structure for table `bukti_transaksi`
23--
24
25CREATE TABLE `bukti_transaksi` (
26  `id` int(11) NOT NULL,
27  `tanggal` date NOT NULL,
28  `nik` varchar(50) NOT NULL,
29  `jumlah` int(11) NOT NULL
30) ENGINE=InnoDB DEFAULT CHARSET=latin1;
31
32-- --------------------------------------------------------
33
34--
35-- Table structure for table `migrations`
36--
37
38CREATE TABLE `migrations` (
39  `id` int(10) UNSIGNED NOT NULL,
40  `migration` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
41  `batch` int(11) NOT NULL
42) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
43
44-- --------------------------------------------------------
45
46--
47-- Table structure for table `password_resets`
48--
49
50CREATE TABLE `password_resets` (
51  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
52  `token` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
53  `created_at` timestamp NULL DEFAULT NULL
54) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
55
56-- --------------------------------------------------------
57
58--
59-- Table structure for table `roles`
60--
61
62CREATE TABLE `roles` (
63  `id` int(11) UNSIGNED NOT NULL,
64  `name` varchar(50) NOT NULL DEFAULT ''
65) ENGINE=InnoDB DEFAULT CHARSET=utf8;
66
67-- --------------------------------------------------------
68
69--
70-- Table structure for table `summary_transaction_detail`
71--
72
73CREATE TABLE `summary_transaction_detail` (
74  `id` int(11) NOT NULL,
75  `transaction_id` varchar(100) NOT NULL,
76  `jukir_id` varchar(50) NOT NULL,
77  `full_name` varchar(100) NOT NULL,
78  `nik` varchar(50) NOT NULL,
79  `location_id` int(11) NOT NULL,
80  `location_name` varchar(100) NOT NULL,
81  `city_id` text NOT NULL,
82  `vehicle_type` int(11) NOT NULL,
83  `vehicle_type_name` varchar(50) NOT NULL,
84  `police_number` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
85  `start_date` date NOT NULL,
86  `start_time` time NOT NULL,
87  `end_date` date NOT NULL,
88  `end_time` time NOT NULL,
89  `base_amout` double NOT NULL,
90  `promo` double NOT NULL,
91  `services_charges` double NOT NULL,
92  `total` double NOT NULL,
93  `total_jukir` double NOT NULL,
94  `total_tgr` double NOT NULL,
95  `transaction_type` int(11) NOT NULL,
96  `transaction_type_name` varchar(50) NOT NULL,
97  `payment_type` int(11) NOT NULL,
98  `payment_type_name` varchar(50) NOT NULL,
99  `note` varchar(200) NOT NULL,
100  `upd_by` varchar(50) DEFAULT NULL,
101  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
102  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
103  `is_active` int(11) NOT NULL
104) ENGINE=InnoDB DEFAULT CHARSET=latin1;
105
106-- --------------------------------------------------------
107
108--
109-- Table structure for table `summary_virtual`
110--
111
112CREATE TABLE `summary_virtual` (
113  `id` int(11) NOT NULL,
114  `transaction_id` varchar(100) NOT NULL,
115  `jukir_id` varchar(50) NOT NULL,
116  `nik` varchar(50) NOT NULL,
117  `full_name` varchar(100) NOT NULL,
118  `location_name` varchar(100) NOT NULL,
119  `location_id` int(11) NOT NULL,
120  `city_id` text NOT NULL,
121  `amount` double NOT NULL,
122  `balance_before` double NOT NULL,
123  `balance_after` double NOT NULL,
124  `vehicle_type` int(11) NOT NULL,
125  `vehicle_type_name` varchar(50) NOT NULL,
126  `transaction_type` int(11) NOT NULL,
127  `transaction_type_name` varchar(50) NOT NULL,
128  `payment_type` int(11) NOT NULL,
129  `payment_type_name` varchar(50) NOT NULL,
130  `police_number` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
131  `promo` double NOT NULL,
132  `services_charges` double NOT NULL,
133  `note` varchar(200) NOT NULL,
134  `base_amout` double NOT NULL,
135  `total` double NOT NULL,
136  `total_jukir` double NOT NULL,
137  `total_tgr` double NOT NULL,
138  `time_request` datetime NOT NULL,
139  `start_date` date NOT NULL,
140  `start_time` time NOT NULL,
141  `end_date` date NOT NULL,
142  `end_time` time NOT NULL,
143  `upd_by` varchar(50) DEFAULT NULL,
144  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
145  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
146  `is_active` int(11) NOT NULL
147) ENGINE=InnoDB DEFAULT CHARSET=latin1;
148
149-- --------------------------------------------------------
150
151--
152-- Table structure for table `summary_virtual_1`
153--
154
155CREATE TABLE `summary_virtual_1` (
156  `id` int(11) NOT NULL,
157  `id_transaction` varchar(150) NOT NULL,
158  `jukir_id` text,
159  `nik` varchar(150) NOT NULL,
160  `full_name` varchar(150) NOT NULL,
161  `location_name` varchar(150) NOT NULL,
162  `location_id` int(11) NOT NULL,
163  `city_id` int(11) NOT NULL,
164  `amount` double NOT NULL,
165  `balance_before` double NOT NULL,
166  `balance_after` double NOT NULL,
167  `payment_type` int(11) NOT NULL DEFAULT '1',
168  `status` int(11) NOT NULL,
169  `description` text NOT NULL,
170  `qrcode` text,
171  `time_request` datetime NOT NULL,
172  `time_response` datetime NOT NULL,
173  `is_active` int(11) NOT NULL DEFAULT '1',
174  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
175  `updated_at` timestamp NULL DEFAULT NULL
176) ENGINE=InnoDB DEFAULT CHARSET=latin1;
177
178-- --------------------------------------------------------
179
180--
181-- Table structure for table `summary_virtual_copy`
182--
183
184CREATE TABLE `summary_virtual_copy` (
185  `id` int(11) NOT NULL,
186  `transaction_id` varchar(100) NOT NULL,
187  `jukir_id` varchar(50) NOT NULL,
188  `nik` varchar(50) NOT NULL,
189  `full_name` varchar(100) NOT NULL,
190  `location_name` varchar(100) NOT NULL,
191  `location_id` int(11) NOT NULL,
192  `city_id` text NOT NULL,
193  `amount` double NOT NULL,
194  `balance_before` double NOT NULL,
195  `balance_after` double NOT NULL,
196  `vehicle_type` int(11) NOT NULL,
197  `vehicle_type_name` varchar(50) NOT NULL,
198  `transaction_type` int(11) NOT NULL,
199  `transaction_type_name` varchar(50) NOT NULL,
200  `payment_type` int(11) NOT NULL,
201  `payment_type_name` varchar(50) NOT NULL,
202  `police_number` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
203  `promo` double NOT NULL,
204  `services_charges` double NOT NULL,
205  `note` varchar(200) NOT NULL,
206  `base_amout` double NOT NULL,
207  `total` double NOT NULL,
208  `total_jukir` double NOT NULL,
209  `total_tgr` double NOT NULL,
210  `time_request` datetime NOT NULL,
211  `start_date` date NOT NULL,
212  `start_time` time NOT NULL,
213  `end_date` date NOT NULL,
214  `end_time` time NOT NULL,
215  `upd_by` varchar(50) DEFAULT NULL,
216  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
217  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
218  `is_active` int(11) NOT NULL
219) ENGINE=InnoDB DEFAULT CHARSET=latin1;
220
221-- --------------------------------------------------------
222
223--
224-- Table structure for table `sum_bc`
225--
226
227CREATE TABLE `sum_bc` (
228  `id` int(11) NOT NULL,
229  `transaction_id` varchar(100) NOT NULL,
230  `jukir_id` varchar(50) NOT NULL,
231  `full_name` varchar(100) NOT NULL,
232  `nik` varchar(50) NOT NULL,
233  `location_id` int(11) NOT NULL,
234  `location_name` varchar(100) NOT NULL,
235  `vehicle_type` int(11) NOT NULL,
236  `vehicle_type_name` varchar(50) NOT NULL,
237  `police_number` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
238  `start_date` date NOT NULL,
239  `start_time` time NOT NULL,
240  `end_date` date NOT NULL,
241  `end_time` time NOT NULL,
242  `base_amout` double NOT NULL,
243  `promo` double NOT NULL,
244  `services_charges` double NOT NULL,
245  `total` double NOT NULL,
246  `total_jukir` double NOT NULL,
247  `total_tgr` double NOT NULL,
248  `transaction_type` int(11) NOT NULL,
249  `transaction_type_name` varchar(50) NOT NULL,
250  `payment_type` int(11) NOT NULL,
251  `payment_type_name` varchar(50) NOT NULL,
252  `note` varchar(200) NOT NULL,
253  `upd_by` varchar(50) DEFAULT NULL,
254  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
255  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
256  `is_active` int(11) NOT NULL
257) ENGINE=InnoDB DEFAULT CHARSET=latin1;
258
259-- --------------------------------------------------------
260
261--
262-- Table structure for table `tb_3d_account`
263--
264
265CREATE TABLE `tb_3d_account` (
266  `id` int(11) NOT NULL,
267  `name` varchar(100) NOT NULL,
268  `service` varchar(50) NOT NULL,
269  `as` text NOT NULL,
270  `api` text NOT NULL,
271  `is_active` int(11) NOT NULL,
272  `created_at` timestamp NULL DEFAULT NULL,
273  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
274) ENGINE=InnoDB DEFAULT CHARSET=latin1;
275
276-- --------------------------------------------------------
277
278--
279-- Table structure for table `tb_admin_location`
280--
281
282CREATE TABLE `tb_admin_location` (
283  `id` int(11) NOT NULL,
284  `user_id` varchar(50) NOT NULL,
285  `location_id` int(11) NOT NULL,
286  `upd_by` varchar(50) NOT NULL,
287  `created_at` datetime NOT NULL,
288  `updated_at` datetime NOT NULL
289) ENGINE=MyISAM DEFAULT CHARSET=utf8;
290
291-- --------------------------------------------------------
292
293--
294-- Table structure for table `tb_apps_landing_page`
295--
296
297CREATE TABLE `tb_apps_landing_page` (
298  `id_landing` int(11) NOT NULL,
299  `title` varchar(150) NOT NULL,
300  `apps_id` tinyint(11) NOT NULL,
301  `url` varchar(150) NOT NULL,
302  `image` varchar(100) NOT NULL,
303  `is_active` tinyint(4) NOT NULL DEFAULT '0',
304  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
305  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
306) ENGINE=InnoDB DEFAULT CHARSET=utf8;
307
308-- --------------------------------------------------------
309
310--
311-- Table structure for table `tb_apps_splash_screen`
312--
313
314CREATE TABLE `tb_apps_splash_screen` (
315  `id_splash` int(11) NOT NULL,
316  `apps_id` tinyint(11) NOT NULL,
317  `title` varchar(150) NOT NULL,
318  `url` varchar(250) NOT NULL,
319  `image` varchar(100) NOT NULL,
320  `is_active` tinyint(4) NOT NULL DEFAULT '0',
321  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
322  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
323) ENGINE=InnoDB DEFAULT CHARSET=utf8;
324
325-- --------------------------------------------------------
326
327--
328-- Table structure for table `tb_apps_version`
329--
330
331CREATE TABLE `tb_apps_version` (
332  `id_version` int(11) NOT NULL,
333  `version` varchar(100) NOT NULL,
334  `os` int(11) NOT NULL,
335  `log` varchar(100) NOT NULL,
336  `is_active` tinyint(4) NOT NULL,
337  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
338  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
339) ENGINE=InnoDB DEFAULT CHARSET=utf8;
340
341-- --------------------------------------------------------
342
343--
344-- Table structure for table `tb_cashback`
345--
346
347CREATE TABLE `tb_cashback` (
348  `id` int(11) NOT NULL,
349  `nik` varchar(255) NOT NULL,
350  `tanggal` date NOT NULL,
351  `status_transfer` int(11) NOT NULL
352) ENGINE=InnoDB DEFAULT CHARSET=latin1;
353
354-- --------------------------------------------------------
355
356--
357-- Table structure for table `tb_category_nik`
358--
359
360CREATE TABLE `tb_category_nik` (
361  `id_category_nik` int(11) NOT NULL,
362  `nik` varchar(100) NOT NULL,
363  `name` varchar(100) NOT NULL
364) ENGINE=InnoDB DEFAULT CHARSET=utf8;
365
366-- --------------------------------------------------------
367
368--
369-- Table structure for table `tb_complain`
370--
371
372CREATE TABLE `tb_complain` (
373  `id` text NOT NULL,
374  `nik` int(11) NOT NULL,
375  `category` varchar(50) NOT NULL,
376  `message` text NOT NULL,
377  `status` int(11) DEFAULT NULL,
378  `is_active` int(11) NOT NULL,
379  `created_at` timestamp NULL DEFAULT NULL,
380  `updated_at` timestamp NOT NULL DEFAULT '2000-01-01 01:01:01' ON UPDATE CURRENT_TIMESTAMP
381) ENGINE=InnoDB DEFAULT CHARSET=latin1;
382
383-- --------------------------------------------------------
384
385--
386-- Table structure for table `tb_complain_category`
387--
388
389CREATE TABLE `tb_complain_category` (
390  `id` int(11) NOT NULL,
391  `name` varchar(50) NOT NULL,
392  `desc` text NOT NULL,
393  `is_active` int(11) NOT NULL,
394  `created_at` timestamp NULL DEFAULT NULL,
395  `updated_at` timestamp NOT NULL DEFAULT '2000-01-01 01:01:01' ON UPDATE CURRENT_TIMESTAMP
396) ENGINE=InnoDB DEFAULT CHARSET=latin1;
397
398-- --------------------------------------------------------
399
400--
401-- Table structure for table `tb_customer`
402--
403
404CREATE TABLE `tb_customer` (
405  `id` varchar(50) NOT NULL,
406  `name` varchar(100) NOT NULL,
407  `email` varchar(100) NOT NULL,
408  `phone_number` varchar(50) NOT NULL,
409  `password` varchar(100) NOT NULL,
410  `is_active` tinyint(1) NOT NULL DEFAULT '1',
411  `password_token` varchar(50) NOT NULL,
412  `reset_password_token` varchar(50) DEFAULT NULL,
413  `auth_token` text,
414  `auth_expiry` datetime DEFAULT NULL,
415  `last_lat` double NOT NULL DEFAULT '0',
416  `last_long` double NOT NULL DEFAULT '0',
417  `os_player_id` varchar(100) NOT NULL DEFAULT '-',
418  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
419  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
420) ENGINE=MyISAM DEFAULT CHARSET=utf8;
421
422-- --------------------------------------------------------
423
424--
425-- Table structure for table `tb_customer_vehicle`
426--
427
428CREATE TABLE `tb_customer_vehicle` (
429  `id` int(11) NOT NULL,
430  `customer_id` varchar(50) NOT NULL,
431  `vehicle_type` int(11) NOT NULL,
432  `vehicle_name` varchar(45) NOT NULL,
433  `vehicle_number` varchar(20) DEFAULT '-',
434  `created_at` timestamp NULL DEFAULT NULL,
435  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
436) ENGINE=MyISAM DEFAULT CHARSET=utf8;
437
438-- --------------------------------------------------------
439
440--
441-- Table structure for table `tb_jukir`
442--
443
444CREATE TABLE `tb_jukir` (
445  `id` varchar(50) NOT NULL,
446  `nik` varchar(50) NOT NULL,
447  `email` varchar(100) DEFAULT NULL,
448  `phone_number` varchar(50) NOT NULL,
449  `full_name` varchar(100) NOT NULL,
450  `username` varchar(45) NOT NULL DEFAULT '-',
451  `password` varchar(100) NOT NULL,
452  `password_token` varchar(50) DEFAULT NULL,
453  `last_lat` double DEFAULT '0',
454  `last_long` double DEFAULT '0',
455  `is_login` tinyint(1) DEFAULT '0',
456  `owner_id` int(11) NOT NULL,
457  `location_id` tinyint(5) NOT NULL DEFAULT '0',
458  `role_id` int(11) NOT NULL,
459  `titik_parkir_id` int(11) NOT NULL,
460  `auth_token` text,
461  `auth_remember` text,
462  `auth_expiry` datetime DEFAULT NULL,
463  `status` tinyint(4) NOT NULL,
464  `is_active` tinyint(1) NOT NULL DEFAULT '1',
465  `upd_by` varchar(50) DEFAULT NULL,
466  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
467  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
468  `picture` varchar(45) NOT NULL DEFAULT 'jukir_default.png'
469) ENGINE=MyISAM DEFAULT CHARSET=utf8;
470
471--
472-- Triggers `tb_jukir`
473--
474DELIMITER $$
475CREATE TRIGGER `tb_jukir_BEFORE_INSERT` BEFORE INSERT ON `tb_jukir` FOR EACH ROW BEGIN
476IF new.id IS NULL or new.id = '' or new.id = '-'
477  THEN
478    SET new.id = UPPER(CONCAT('JUKIR-',uuid()));
479  END IF;
480END
481$$
482DELIMITER ;
483
484-- --------------------------------------------------------
485
486--
487-- Table structure for table `tb_jukir_balance`
488--
489
490CREATE TABLE `tb_jukir_balance` (
491  `id_jukir` varchar(50) NOT NULL,
492  `key_access` text NOT NULL,
493  `balance` double NOT NULL DEFAULT '0',
494  `last_topup_balance` double NOT NULL DEFAULT '0',
495  `last_topup_datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
496  `is_active` int(11) NOT NULL DEFAULT '1',
497  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
498  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
499) ENGINE=InnoDB DEFAULT CHARSET=latin1;
500
501-- --------------------------------------------------------
502
503--
504-- Table structure for table `tb_jukir_profile`
505--
506
507CREATE TABLE `tb_jukir_profile` (
508  `id_jukir_profile` int(11) NOT NULL,
509  `id_jukir` varchar(100) NOT NULL,
510  `nama` varchar(150) NOT NULL,
511  `tempat_lahir` varchar(100) NOT NULL,
512  `tanggal_lahir` varchar(20) NOT NULL,
513  `jenis_kelamin` varchar(5) NOT NULL,
514  `no_ktp` varchar(100) NOT NULL,
515  `status_pernikahan` varchar(5) NOT NULL,
516  `krbt_nama` varchar(100) NOT NULL,
517  `krbt_no_hp` varchar(20) NOT NULL,
518  `krbt_hubungan` varchar(100) NOT NULL,
519  `alamat` text NOT NULL,
520  `pendidikan_terakhir` varchar(50) NOT NULL,
521  `referensi` varchar(150) NOT NULL,
522  `ijazah` varchar(150) NOT NULL,
523  `buku_nikah` varchar(150) NOT NULL,
524  `bpkb` varchar(150) NOT NULL
525) ENGINE=InnoDB DEFAULT CHARSET=utf8;
526
527-- --------------------------------------------------------
528
529--
530-- Table structure for table `tb_jukir_virtual_payment`
531--
532
533CREATE TABLE `tb_jukir_virtual_payment` (
534  `id_transaction` varchar(100) NOT NULL,
535  `jukir_id` text,
536  `amount` double NOT NULL,
537  `payment_type` int(11) NOT NULL DEFAULT '1',
538  `balance_before` double NOT NULL,
539  `balance_after` double NOT NULL,
540  `status` int(11) NOT NULL,
541  `description` text NOT NULL,
542  `qrcode` text,
543  `time_request` datetime NOT NULL,
544  `time_response` datetime DEFAULT NULL,
545  `is_active` int(11) NOT NULL DEFAULT '1',
546  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
547  `updated_at` timestamp NULL DEFAULT NULL
548) ENGINE=InnoDB DEFAULT CHARSET=latin1;
549
550-- --------------------------------------------------------
551
552--
553-- Table structure for table `tb_jukir_virtual_payment_status`
554--
555
556CREATE TABLE `tb_jukir_virtual_payment_status` (
557  `id` int(11) NOT NULL,
558  `name` varchar(50) NOT NULL,
559  `created_at` timestamp NULL DEFAULT NULL,
560  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
561) ENGINE=InnoDB DEFAULT CHARSET=latin1;
562
563-- --------------------------------------------------------
564
565--
566-- Table structure for table `tb_location_type`
567--
568
569CREATE TABLE `tb_location_type` (
570  `id` int(11) NOT NULL,
571  `name` varchar(50) NOT NULL,
572  `icon` varchar(50) DEFAULT NULL,
573  `upd_by` int(10) DEFAULT NULL,
574  `created_at` timestamp NULL DEFAULT '2001-01-01 01:01:01',
575  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
576  `is_active` tinyint(1) NOT NULL DEFAULT '1'
577) ENGINE=MyISAM DEFAULT CHARSET=utf8;
578
579-- --------------------------------------------------------
580
581--
582-- Table structure for table `tb_location_vehicle_fare`
583--
584
585CREATE TABLE `tb_location_vehicle_fare` (
586  `id` int(11) NOT NULL,
587  `location_id` int(11) NOT NULL,
588  `vehicle_type` int(11) NOT NULL,
589  `transaction_type_id` int(11) DEFAULT NULL,
590  `transaction_sub_type_id` int(11) DEFAULT NULL,
591  `amount` double NOT NULL DEFAULT '0',
592  `upd_by` varchar(50) DEFAULT NULL,
593  `created_at` timestamp NULL DEFAULT '2001-01-01 01:01:01',
594  `updated_at` timestamp NULL DEFAULT NULL
595) ENGINE=MyISAM DEFAULT CHARSET=utf8;
596
597-- --------------------------------------------------------
598
599--
600-- Table structure for table `tb_ms_city`
601--
602
603CREATE TABLE `tb_ms_city` (
604  `id` int(11) NOT NULL,
605  `province_id` int(10) DEFAULT NULL,
606  `city_id` int(10) DEFAULT NULL,
607  `name` varchar(100) DEFAULT NULL,
608  `created_at` timestamp NULL DEFAULT '2001-01-01 01:01:01',
609  `updated_at` timestamp NULL DEFAULT '2001-01-01 01:01:01' ON UPDATE CURRENT_TIMESTAMP
610) ENGINE=MyISAM DEFAULT CHARSET=utf8;
611
612-- --------------------------------------------------------
613
614--
615-- Table structure for table `tb_ms_location`
616--
617
618CREATE TABLE `tb_ms_location` (
619  `id` int(11) NOT NULL,
620  `location_name` varchar(100) NOT NULL,
621  `location_type` varchar(20) NOT NULL DEFAULT '-',
622  `latitude` double NOT NULL DEFAULT '0',
623  `longitude` double NOT NULL DEFAULT '0',
624  `province_id` int(5) NOT NULL,
625  `city_id` int(5) NOT NULL,
626  `sub_district_id` int(5) NOT NULL,
627  `address` text,
628  `parking_charging_type` int(11) NOT NULL DEFAULT '1',
629  `is_booking` tinyint(1) NOT NULL DEFAULT '0',
630  `profit_id` int(11) NOT NULL,
631  `description` text,
632  `terms_and_condition` text,
633  `picture` varchar(255) NOT NULL DEFAULT 'no_image.png',
634  `is_active` tinyint(1) NOT NULL DEFAULT '1',
635  `upd_by` varchar(50) DEFAULT NULL,
636  `created_at` timestamp NULL DEFAULT NULL,
637  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
638) ENGINE=MyISAM DEFAULT CHARSET=utf8;
639
640-- --------------------------------------------------------
641
642--
643-- Table structure for table `tb_ms_parking_charges`
644--
645
646CREATE TABLE `tb_ms_parking_charges` (
647  `id` int(11) NOT NULL,
648  `name` varchar(45) NOT NULL,
649  `upd_by` int(10) DEFAULT NULL,
650  `created_at` timestamp NULL DEFAULT '2001-01-01 01:01:01',
651  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
652) ENGINE=MyISAM DEFAULT CHARSET=utf8;
653
654-- --------------------------------------------------------
655
656--
657-- Table structure for table `tb_ms_province`
658--
659
660CREATE TABLE `tb_ms_province` (
661  `id` int(11) NOT NULL,
662  `province_id` int(10) DEFAULT NULL,
663  `name` varchar(100) DEFAULT NULL,
664  `created_at` timestamp NULL DEFAULT '2001-01-01 01:01:01',
665  `updated_at` timestamp NULL DEFAULT '2001-01-01 01:01:01' ON UPDATE CURRENT_TIMESTAMP
666) ENGINE=MyISAM DEFAULT CHARSET=utf8;
667
668-- --------------------------------------------------------
669
670--
671-- Table structure for table `tb_ms_sub_district`
672--
673
674CREATE TABLE `tb_ms_sub_district` (
675  `id` int(11) NOT NULL,
676  `city_id` int(10) DEFAULT NULL,
677  `name` varchar(100) DEFAULT NULL,
678  `created_at` timestamp NULL DEFAULT '2001-01-01 01:01:01',
679  `updated_at` timestamp NULL DEFAULT '2001-01-01 01:01:01' ON UPDATE CURRENT_TIMESTAMP
680) ENGINE=MyISAM DEFAULT CHARSET=utf8;
681
682-- --------------------------------------------------------
683
684--
685-- Table structure for table `tb_ms_user_type`
686--
687
688CREATE TABLE `tb_ms_user_type` (
689  `id` int(11) NOT NULL,
690  `access_name` varchar(40) DEFAULT NULL,
691  `created_at` timestamp NULL DEFAULT '2001-01-01 01:01:01',
692  `updated_at` timestamp NULL DEFAULT '2001-01-01 01:01:01' ON UPDATE CURRENT_TIMESTAMP
693) ENGINE=MyISAM DEFAULT CHARSET=utf8;
694
695-- --------------------------------------------------------
696
697--
698-- Table structure for table `tb_owner`
699--
700
701CREATE TABLE `tb_owner` (
702  `id` int(6) NOT NULL,
703  `code` varchar(5) DEFAULT NULL,
704  `owner_name` varchar(60) NOT NULL DEFAULT '',
705  `description` varchar(100) DEFAULT '',
706  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
707  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
708) ENGINE=InnoDB DEFAULT CHARSET=latin1;
709
710-- --------------------------------------------------------
711
712--
713-- Table structure for table `tb_payment_type`
714--
715
716CREATE TABLE `tb_payment_type` (
717  `id` int(11) NOT NULL,
718  `name` varchar(100) NOT NULL,
719  `upd_by` int(10) DEFAULT NULL,
720  `created_at` timestamp NULL DEFAULT '2001-01-01 01:01:01',
721  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
722  `is_active` tinyint(1) DEFAULT NULL
723) ENGINE=MyISAM DEFAULT CHARSET=utf8;
724
725-- --------------------------------------------------------
726
727--
728-- Table structure for table `tb_pendaftaran_mitra_jukir`
729--
730
731CREATE TABLE `tb_pendaftaran_mitra_jukir` (
732  `id_pendaftaran` varchar(150) NOT NULL,
733  `nama_depan` varchar(20) NOT NULL,
734  `nama_belakang` varchar(20) NOT NULL,
735  `email` varchar(50) NOT NULL,
736  `no_hp` varchar(20) NOT NULL,
737  `kota` varchar(50) NOT NULL,
738  `informasi` varchar(150) NOT NULL,
739  `pengalaman_parkir` varchar(5) NOT NULL,
740  `lokasi` varchar(50) NOT NULL,
741  `is_active` varchar(3) NOT NULL DEFAULT '0',
742  `created_at` datetime NOT NULL,
743  `updated_at` datetime NOT NULL
744) ENGINE=InnoDB DEFAULT CHARSET=latin1;
745
746-- --------------------------------------------------------
747
748--
749-- Table structure for table `tb_profit`
750--
751
752CREATE TABLE `tb_profit` (
753  `id` int(11) NOT NULL,
754  `persen` int(11) NOT NULL,
755  `nominal` double NOT NULL,
756  `type` int(11) NOT NULL,
757  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
758  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
759) ENGINE=InnoDB DEFAULT CHARSET=latin1;
760
761-- --------------------------------------------------------
762
763--
764-- Table structure for table `tb_progressive`
765--
766
767CREATE TABLE `tb_progressive` (
768  `id` text NOT NULL,
769  `user_id` text NOT NULL,
770  `in_out` int(11) NOT NULL,
771  `area_Id` int(11) NOT NULL,
772  `lat` varchar(255) NOT NULL,
773  `lng` varchar(255) NOT NULL,
774  `trans_type` int(11) NOT NULL,
775  `pay_type` int(11) NOT NULL,
776  `price` varchar(255) NOT NULL,
777  `tipe` int(11) NOT NULL,
778  `qr_code` varchar(100) NOT NULL,
779  `nomer_plat` text NOT NULL,
780  `status` int(11) NOT NULL DEFAULT '0',
781  `start_date` date NOT NULL,
782  `start_time` time NOT NULL,
783  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
784  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
785) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;
786
787-- --------------------------------------------------------
788
789--
790-- Table structure for table `tb_titik_parkir`
791--
792
793CREATE TABLE `tb_titik_parkir` (
794  `id` int(6) UNSIGNED NOT NULL,
795  `location_name` varchar(60) NOT NULL,
796  `area_id` int(11) NOT NULL,
797  `owner_id` int(11) DEFAULT NULL,
798  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
799  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
800) ENGINE=InnoDB DEFAULT CHARSET=latin1;
801
802-- --------------------------------------------------------
803
804--
805-- Table structure for table `tb_transaction`
806--
807
808CREATE TABLE `tb_transaction` (
809  `id` varchar(100) NOT NULL,
810  `jukir_id` varchar(50) DEFAULT NULL,
811  `location_id` int(11) DEFAULT NULL,
812  `date_transaction` date NOT NULL,
813  `time_transaction` time NOT NULL,
814  `amount` double NOT NULL,
815  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
816  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
817  `is_active` tinyint(1) NOT NULL DEFAULT '1'
818) ENGINE=MyISAM DEFAULT CHARSET=utf8;
819
820--
821-- Triggers `tb_transaction`
822--
823DELIMITER $$
824CREATE TRIGGER `tb_transaction_BEFORE_INSERT` BEFORE INSERT ON `tb_transaction` FOR EACH ROW BEGIN
825IF new.id IS NULL or new.id = '' or new.id = '-'
826  THEN
827    SET new.id = UPPER(CONCAT('TRX-',uuid()));
828  END IF;
829
830END
831$$
832DELIMITER ;
833
834-- --------------------------------------------------------
835
836--
837-- Table structure for table `tb_transaction_detail`
838--
839
840CREATE TABLE `tb_transaction_detail` (
841  `id` int(11) NOT NULL,
842  `transaction_id` varchar(100) NOT NULL,
843  `vehicle_type` int(11) NOT NULL,
844  `police_number` varchar(20) NOT NULL DEFAULT '-',
845  `start_date` date NOT NULL,
846  `start_time` time NOT NULL,
847  `end_date` date NOT NULL,
848  `end_time` time NOT NULL,
849  `base_amout` double NOT NULL DEFAULT '0',
850  `promo` double NOT NULL DEFAULT '0',
851  `services_charges` double NOT NULL DEFAULT '0',
852  `total` double NOT NULL DEFAULT '0',
853  `jukir` int(11) DEFAULT NULL,
854  `tgr` int(11) DEFAULT NULL,
855  `transaction_type` int(11) NOT NULL,
856  `payment_type` int(11) NOT NULL,
857  `note` varchar(200) NOT NULL DEFAULT '-',
858  `upd_by` varchar(50) DEFAULT NULL,
859  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
860  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
861) ENGINE=MyISAM DEFAULT CHARSET=utf8;
862
863-- --------------------------------------------------------
864
865--
866-- Table structure for table `tb_transaction_sub_type`
867--
868
869CREATE TABLE `tb_transaction_sub_type` (
870  `id` int(11) NOT NULL,
871  `transaction_type_id` int(11) NOT NULL,
872  `system_name` varchar(40) NOT NULL,
873  `name` varchar(50) NOT NULL,
874  `description` varchar(200) DEFAULT NULL,
875  `is_active` tinyint(1) NOT NULL DEFAULT '1',
876  `upd_by` varchar(50) DEFAULT NULL,
877  `created_at` timestamp NULL DEFAULT '2001-01-01 01:01:01',
878  `updated_at` timestamp NULL DEFAULT NULL
879) ENGINE=MyISAM DEFAULT CHARSET=utf8;
880
881-- --------------------------------------------------------
882
883--
884-- Table structure for table `tb_transaction_type`
885--
886
887CREATE TABLE `tb_transaction_type` (
888  `id` int(11) NOT NULL,
889  `name` varchar(50) DEFAULT NULL,
890  `is_active` tinyint(1) DEFAULT '1',
891  `upd_by` varchar(50) DEFAULT NULL,
892  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
893  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
894) ENGINE=MyISAM DEFAULT CHARSET=utf8;
895
896-- --------------------------------------------------------
897
898--
899-- Table structure for table `tb_user`
900--
901
902CREATE TABLE `tb_user` (
903  `id` int(11) NOT NULL,
904  `first_name` varchar(45) NOT NULL,
905  `last_name` varchar(45) DEFAULT NULL,
906  `email` varchar(100) NOT NULL,
907  `phone_number` varchar(45) DEFAULT NULL,
908  `picture` varchar(100) DEFAULT 'user.png',
909  `address` text,
910  `id_kota` varchar(50) NOT NULL,
911  `id_lokasi` varchar(50) NOT NULL,
912  `type_user` tinyint(4) DEFAULT NULL,
913  `username` varchar(50) NOT NULL,
914  `password` varchar(100) NOT NULL,
915  `remember_token` varchar(100) DEFAULT NULL,
916  `is_login` tinyint(2) DEFAULT '0',
917  `is_active` tinyint(1) DEFAULT '1',
918  `created_at` datetime NOT NULL,
919  `updated_at` datetime NOT NULL
920) ENGINE=MyISAM DEFAULT CHARSET=utf8;
921
922-- --------------------------------------------------------
923
924--
925-- Table structure for table `tb_user_booking`
926--
927
928CREATE TABLE `tb_user_booking` (
929  `id` varchar(100) NOT NULL,
930  `user_id` varchar(50) NOT NULL,
931  `location_id` int(11) NOT NULL,
932  `vehicle_type` int(11) NOT NULL,
933  `vehicle_number` varchar(20) NOT NULL,
934  `booking_date` date DEFAULT NULL,
935  `booking_time` time DEFAULT NULL,
936  `amount` double NOT NULL DEFAULT '0',
937  `booking_stat` tinyint(4) NOT NULL DEFAULT '0',
938  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
939  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
940  `upd_by` varchar(50) DEFAULT NULL,
941  `transaction_id` varchar(100) DEFAULT NULL
942) ENGINE=InnoDB DEFAULT CHARSET=utf8;
943
944-- --------------------------------------------------------
945
946--
947-- Table structure for table `tb_vehicle_type`
948--
949
950CREATE TABLE `tb_vehicle_type` (
951  `id` int(10) NOT NULL,
952  `name` varchar(50) NOT NULL,
953  `picture` varchar(50) NOT NULL DEFAULT 'no_image.png',
954  `is_active` tinyint(1) DEFAULT '1',
955  `created_at` timestamp NULL DEFAULT NULL,
956  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
957) ENGINE=MyISAM DEFAULT CHARSET=utf8;
958
959-- --------------------------------------------------------
960
961--
962-- Table structure for table `users`
963--
964
965CREATE TABLE `users` (
966  `id` int(10) UNSIGNED NOT NULL,
967  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
968  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
969  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
970  `remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
971  `created_at` timestamp NULL DEFAULT NULL,
972  `updated_at` timestamp NULL DEFAULT NULL
973) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
974
975-- --------------------------------------------------------
976
977--
978-- Stand-in structure for view `view_area_location`
979-- (See below for the actual view)
980--
981CREATE TABLE `view_area_location` (
982`id` int(11)
983,`location_name` varchar(100)
984,`location_type_id` varchar(20)
985,`location_type_name` varchar(50)
986,`latitude` double
987,`longitude` double
988,`province_id` int(5)
989,`province_name` varchar(100)
990,`city_id` int(5)
991,`city_name` varchar(100)
992,`sub_district_id` int(5)
993,`sub_district_name` varchar(100)
994,`address` text
995,`parking_charging_type` int(11)
996,`description` text
997,`terms_and_condition` text
998,`picture` varchar(255)
999,`is_booking` tinyint(1)
1000,`is_active` tinyint(1)
1001,`upd_by` varchar(50)
1002,`created_at` timestamp
1003,`updated_at` timestamp
1004,`charges_name` varchar(45)
1005);
1006
1007-- --------------------------------------------------------
1008
1009--
1010-- Stand-in structure for view `view_booking_detail`
1011-- (See below for the actual view)
1012--
1013CREATE TABLE `view_booking_detail` (
1014`id` varchar(100)
1015,`user_id` varchar(50)
1016,`vehicle_type` int(11)
1017,`vehicle_name` varchar(50)
1018,`vehicle_number` varchar(20)
1019,`location_id` int(11)
1020,`location_address` text
1021,`location_desction` text
1022,`location_terms_and_condition` text
1023,`location_picture` varchar(255)
1024,`location_name` varchar(100)
1025,`latitude` double
1026,`longitude` double
1027,`booking_date` date
1028,`booking_time` time
1029,`amount` double
1030,`booking_stat` tinyint(4)
1031);
1032
1033-- --------------------------------------------------------
1034
1035--
1036-- Stand-in structure for view `view_report_transaction`
1037-- (See below for the actual view)
1038--
1039CREATE TABLE `view_report_transaction` (
1040`jukir_id` varchar(50)
1041,`date` date
1042,`amount` double
1043);
1044
1045-- --------------------------------------------------------
1046
1047--
1048-- Stand-in structure for view `vw_base_fare`
1049-- (See below for the actual view)
1050--
1051CREATE TABLE `vw_base_fare` (
1052`id` int(11)
1053,`location_id` int(11)
1054,`vehicle_type` int(11)
1055,`vehicle_name` varchar(50)
1056,`picture` varchar(50)
1057,`transaction_type_id` int(11)
1058,`transaction_type_name` varchar(50)
1059,`transaction_sub_type_id` int(11)
1060,`transaction_sub_type_name` varchar(50)
1061,`amount` double
1062,`upd_by` varchar(50)
1063,`created_at` timestamp
1064,`updated_at` timestamp
1065);
1066
1067-- --------------------------------------------------------
1068
1069--
1070-- Stand-in structure for view `vw_cashback`
1071-- (See below for the actual view)
1072--
1073CREATE TABLE `vw_cashback` (
1074`nik` varchar(50)
1075,`nama` varchar(100)
1076,`tanggal_setor` date
1077,`total_global` double
1078,`total_jukir` double
1079,`total_tgr` double
1080,`total_cashback` double
1081,`status` bigint(10)
1082);
1083
1084-- --------------------------------------------------------
1085
1086--
1087-- Stand-in structure for view `vw_cashback2`
1088-- (See below for the actual view)
1089--
1090CREATE TABLE `vw_cashback2` (
1091`nik` varchar(50)
1092,`nama` varchar(100)
1093,`tanggal_setor` date
1094,`total_global` double
1095,`total_jukir` double
1096,`total_tgr` int(11)
1097,`total_cashback` decimal(14,4)
1098,`status` bigint(10)
1099);
1100
1101-- --------------------------------------------------------
1102
1103--
1104-- Stand-in structure for view `vw_cashback_2`
1105-- (See below for the actual view)
1106--
1107CREATE TABLE `vw_cashback_2` (
1108`status` bigint(10)
1109,`tanggal_setor` date
1110,`lokasi` varchar(100)
1111,`nik` varchar(50)
1112,`nama` varchar(100)
1113,`mobil` decimal(23,0)
1114,`motor` decimal(23,0)
1115,`truck` decimal(23,0)
1116,`speda` decimal(23,0)
1117,`total_global` double
1118,`total_jukir` double
1119,`total_tgr` double
1120,`total_cashback` double
1121);
1122
1123-- --------------------------------------------------------
1124
1125--
1126-- Stand-in structure for view `vw_cashback_tes`
1127-- (See below for the actual view)
1128--
1129CREATE TABLE `vw_cashback_tes` (
1130`active` tinyint(1)
1131,`nik` varchar(50)
1132,`nama` varchar(100)
1133,`lokasi` varchar(100)
1134,`tanggal_setor` date
1135,`mobil` decimal(23,0)
1136,`motor` decimal(23,0)
1137,`truck` decimal(23,0)
1138,`speda` decimal(23,0)
1139,`total_global` double
1140,`total_jukir` double
1141,`total_tgr` double
1142,`total_cashback` double
1143,`status` bigint(10)
1144);
1145
1146-- --------------------------------------------------------
1147
1148--
1149-- Stand-in structure for view `vw_mobil_transaction`
1150-- (See below for the actual view)
1151--
1152CREATE TABLE `vw_mobil_transaction` (
1153`jukir_id` varchar(50)
1154,`nik` varchar(50)
1155,`date` date
1156,`mobil` decimal(32,0)
1157);
1158
1159-- --------------------------------------------------------
1160
1161--
1162-- Stand-in structure for view `vw_motor_transaction`
1163-- (See below for the actual view)
1164--
1165CREATE TABLE `vw_motor_transaction` (
1166`jukir_id` varchar(50)
1167,`date` date
1168,`motor` decimal(32,0)
1169);
1170
1171-- --------------------------------------------------------
1172
1173--
1174-- Stand-in structure for view `vw_revenue_mj`
1175-- (See below for the actual view)
1176--
1177CREATE TABLE `vw_revenue_mj` (
1178`nik` varchar(50)
1179,`nama` varchar(100)
1180,`lokasi` varchar(100)
1181,`tanggal_setor` date
1182,`total_global` double
1183);
1184
1185-- --------------------------------------------------------
1186
1187--
1188-- Stand-in structure for view `vw_speda_transaction`
1189-- (See below for the actual view)
1190--
1191CREATE TABLE `vw_speda_transaction` (
1192`jukir_id` varchar(50)
1193,`date` date
1194,`mobil` decimal(32,0)
1195);
1196
1197-- --------------------------------------------------------
1198
1199--
1200-- Stand-in structure for view `vw_sum_report_transaction`
1201-- (See below for the actual view)
1202--
1203CREATE TABLE `vw_sum_report_transaction` (
1204`jukir_id` varchar(50)
1205,`date` date
1206,`amount` double
1207);
1208
1209-- --------------------------------------------------------
1210
1211--
1212-- Stand-in structure for view `vw_sum_report_transaction_detail`
1213-- (See below for the actual view)
1214--
1215CREATE TABLE `vw_sum_report_transaction_detail` (
1216`id` int(11)
1217,`transaction_id` varchar(100)
1218,`jukir_id` varchar(50)
1219,`date_transaction` date
1220,`location_id` int(11)
1221,`transaction_type` int(11)
1222,`payment_type` int(11)
1223,`sum(td.total)` double
1224);
1225
1226-- --------------------------------------------------------
1227
1228--
1229-- Stand-in structure for view `vw_transaction_detail`
1230-- (See below for the actual view)
1231--
1232CREATE TABLE `vw_transaction_detail` (
1233`id` int(11)
1234,`transaction_id` varchar(100)
1235,`jukir_id` varchar(50)
1236,`full_name` varchar(100)
1237,`nik` varchar(50)
1238,`location_id` int(11)
1239,`location_name` varchar(100)
1240,`vehicle_type` int(11)
1241,`vehicle_type_name` varchar(50)
1242,`police_number` varchar(20)
1243,`start_date` date
1244,`start_time` time
1245,`end_date` date
1246,`end_time` time
1247,`base_amout` double
1248,`promo` double
1249,`services_charges` double
1250,`total` double
1251,`total_jukir` double
1252,`total_tgr` double
1253,`transaction_type` int(11)
1254,`transaction_type_name` varchar(50)
1255,`payment_type` int(11)
1256,`payment_type_name` varchar(100)
1257,`note` varchar(200)
1258,`upd_by` varchar(50)
1259,`created_at` timestamp
1260,`updated_at` timestamp
1261,`is_active` tinyint(1)
1262);
1263
1264-- --------------------------------------------------------
1265
1266--
1267-- Stand-in structure for view `vw_transaction_detail2`
1268-- (See below for the actual view)
1269--
1270CREATE TABLE `vw_transaction_detail2` (
1271`id` int(11)
1272,`transaction_id` varchar(100)
1273,`jukir_id` varchar(50)
1274,`full_name` varchar(100)
1275,`nik` varchar(50)
1276,`location_id` int(11)
1277,`location_name` varchar(100)
1278,`vehicle_type` int(11)
1279,`vehicle_type_name` varchar(50)
1280,`police_number` varchar(20)
1281,`start_date` date
1282,`start_time` time
1283,`end_date` date
1284,`end_time` time
1285,`base_amout` double
1286,`promo` double
1287,`services_charges` double
1288,`total` double
1289,`total_jukir` double
1290,`total_tgr` double
1291,`transaction_type` int(11)
1292,`transaction_type_name` varchar(50)
1293,`payment_type` int(11)
1294,`payment_type_name` varchar(100)
1295,`note` varchar(200)
1296,`upd_by` varchar(50)
1297,`created_at` timestamp
1298,`updated_at` timestamp
1299,`is_active` tinyint(1)
1300);
1301
1302-- --------------------------------------------------------
1303
1304--
1305-- Stand-in structure for view `vw_truck_transaction`
1306-- (See below for the actual view)
1307--
1308CREATE TABLE `vw_truck_transaction` (
1309`jukir_id` varchar(50)
1310,`date` date
1311,`truck` decimal(32,0)
1312);
1313
1314-- --------------------------------------------------------
1315
1316--
1317-- Structure for view `view_area_location`
1318--
1319DROP TABLE IF EXISTS `view_area_location`;
1320
1321CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_area_location`  AS  select `loc`.`id` AS `id`,`loc`.`location_name` AS `location_name`,`loc`.`location_type` AS `location_type_id`,`loc_type`.`name` AS `location_type_name`,`loc`.`latitude` AS `latitude`,`loc`.`longitude` AS `longitude`,`loc`.`province_id` AS `province_id`,`p`.`name` AS `province_name`,`loc`.`city_id` AS `city_id`,`c`.`name` AS `city_name`,`loc`.`sub_district_id` AS `sub_district_id`,`sd`.`name` AS `sub_district_name`,`loc`.`address` AS `address`,`loc`.`parking_charging_type` AS `parking_charging_type`,`loc`.`description` AS `description`,`loc`.`terms_and_condition` AS `terms_and_condition`,`loc`.`picture` AS `picture`,`loc`.`is_booking` AS `is_booking`,`loc`.`is_active` AS `is_active`,`loc`.`upd_by` AS `upd_by`,`loc`.`created_at` AS `created_at`,`loc`.`updated_at` AS `updated_at`,`charges`.`name` AS `charges_name` from (((((`tb_ms_location` `loc` join `tb_location_type` `loc_type` on((`loc`.`location_type` = `loc_type`.`id`))) join `tb_ms_parking_charges` `charges` on((`loc`.`parking_charging_type` = `charges`.`id`))) join `tb_ms_province` `p` on((`p`.`id` = `loc`.`province_id`))) join `tb_ms_city` `c` on((`c`.`id` = `loc`.`city_id`))) join `tb_ms_sub_district` `sd` on((`sd`.`id` = `loc`.`sub_district_id`))) ;
1322
1323-- --------------------------------------------------------
1324
1325--
1326-- Structure for view `view_booking_detail`
1327--
1328DROP TABLE IF EXISTS `view_booking_detail`;
1329
1330CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_booking_detail`  AS  select `ub`.`id` AS `id`,`ub`.`user_id` AS `user_id`,`ub`.`vehicle_type` AS `vehicle_type`,`vt`.`name` AS `vehicle_name`,`ub`.`vehicle_number` AS `vehicle_number`,`ub`.`location_id` AS `location_id`,`l`.`address` AS `location_address`,`l`.`description` AS `location_desction`,`l`.`terms_and_condition` AS `location_terms_and_condition`,`l`.`picture` AS `location_picture`,`l`.`location_name` AS `location_name`,`l`.`latitude` AS `latitude`,`l`.`longitude` AS `longitude`,`ub`.`booking_date` AS `booking_date`,`ub`.`booking_time` AS `booking_time`,`ub`.`amount` AS `amount`,`ub`.`booking_stat` AS `booking_stat` from ((`tb_user_booking` `ub` join `tb_ms_location` `l` on((`ub`.`location_id` = `l`.`id`))) join `tb_vehicle_type` `vt` on((`vt`.`id` = `ub`.`vehicle_type`))) ;
1331
1332-- --------------------------------------------------------
1333
1334--
1335-- Structure for view `view_report_transaction`
1336--
1337DROP TABLE IF EXISTS `view_report_transaction`;
1338
1339CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_report_transaction`  AS  select `tb_transaction`.`jukir_id` AS `jukir_id`,cast(`tb_transaction`.`date_transaction` as date) AS `date`,sum(`tb_transaction`.`amount`) AS `amount` from `tb_transaction` group by `tb_transaction`.`date_transaction`,`tb_transaction`.`jukir_id` order by cast(`tb_transaction`.`date_transaction` as date) ;
1340
1341-- --------------------------------------------------------
1342
1343--
1344-- Structure for view `vw_base_fare`
1345--
1346DROP TABLE IF EXISTS `vw_base_fare`;
1347
1348CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_base_fare`  AS  select `vf`.`id` AS `id`,`vf`.`location_id` AS `location_id`,`vf`.`vehicle_type` AS `vehicle_type`,`vt`.`name` AS `vehicle_name`,`vt`.`picture` AS `picture`,`vf`.`transaction_type_id` AS `transaction_type_id`,`tt`.`name` AS `transaction_type_name`,`vf`.`transaction_sub_type_id` AS `transaction_sub_type_id`,`ts`.`name` AS `transaction_sub_type_name`,`vf`.`amount` AS `amount`,`vf`.`upd_by` AS `upd_by`,`vf`.`created_at` AS `created_at`,`vf`.`updated_at` AS `updated_at` from (((`tb_location_vehicle_fare` `vf` join `tb_vehicle_type` `vt` on((`vt`.`id` = `vf`.`vehicle_type`))) join `tb_transaction_type` `tt` on((`vf`.`transaction_type_id` = `tt`.`id`))) join `tb_transaction_sub_type` `ts` on((`vf`.`transaction_sub_type_id` = `ts`.`id`))) ;
1349
1350-- --------------------------------------------------------
1351
1352--
1353-- Structure for view `vw_cashback`
1354--
1355DROP TABLE IF EXISTS `vw_cashback`;
1356
1357CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_cashback`  AS  select `tb_jukir`.`nik` AS `nik`,`tb_jukir`.`full_name` AS `nama`,`tb_transaction`.`date_transaction` AS `tanggal_setor`,sum(`tb_transaction_detail`.`total`) AS `total_global`,(sum(`tb_transaction_detail`.`total`) * 0.8) AS `total_jukir`,(sum(`tb_transaction_detail`.`total`) * 0.2) AS `total_tgr`,((sum(`tb_transaction_detail`.`total`) * 0.2) / 2) AS `total_cashback`,(curdate() - cast(`tb_jukir`.`created_at` as date)) AS `status` from ((`tb_jukir` join `tb_transaction`) join `tb_transaction_detail`) where ((`tb_jukir`.`id` = `tb_transaction`.`jukir_id`) and (`tb_transaction`.`id` = `tb_transaction_detail`.`transaction_id`)) group by `tb_jukir`.`id`,`tb_transaction`.`date_transaction`,`tb_jukir`.`nik`,`tb_jukir`.`full_name`,`tb_jukir`.`created_at` ;
1358
1359-- --------------------------------------------------------
1360
1361--
1362-- Structure for view `vw_cashback2`
1363--
1364DROP TABLE IF EXISTS `vw_cashback2`;
1365
1366CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_cashback2`  AS  select `tb_jukir`.`nik` AS `nik`,`tb_jukir`.`full_name` AS `nama`,`tb_transaction`.`date_transaction` AS `tanggal_setor`,sum(`tb_transaction_detail`.`total`) AS `total_global`,`tb_transaction_detail`.`total` AS `total_jukir`,`tb_transaction_detail`.`tgr` AS `total_tgr`,(`tb_transaction_detail`.`tgr` / 2) AS `total_cashback`,(curdate() - cast(`tb_jukir`.`created_at` as date)) AS `status` from ((`tb_jukir` join `tb_transaction`) join `tb_transaction_detail`) where ((`tb_jukir`.`id` = `tb_transaction`.`jukir_id`) and (`tb_transaction`.`id` = `tb_transaction_detail`.`transaction_id`)) group by `tb_jukir`.`id`,`tb_transaction`.`date_transaction`,`tb_jukir`.`nik`,`tb_jukir`.`full_name`,`tb_jukir`.`created_at` ;
1367
1368-- --------------------------------------------------------
1369
1370--
1371-- Structure for view `vw_cashback_2`
1372--
1373DROP TABLE IF EXISTS `vw_cashback_2`;
1374
1375CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_cashback_2`  AS  select (curdate() - cast(`tb_jukir`.`created_at` as date)) AS `status`,`tb_transaction`.`date_transaction` AS `tanggal_setor`,`tb_ms_location`.`location_name` AS `lokasi`,`tb_jukir`.`nik` AS `nik`,`tb_jukir`.`full_name` AS `nama`,sum((case when (`tb_transaction_detail`.`vehicle_type` = 1) then 1 else 0 end)) AS `mobil`,sum((case when (`tb_transaction_detail`.`vehicle_type` = 2) then 1 else 0 end)) AS `motor`,sum((case when (`tb_transaction_detail`.`vehicle_type` = 3) then 1 else 0 end)) AS `truck`,sum((case when (`tb_transaction_detail`.`vehicle_type` = 4) then 1 else 0 end)) AS `speda`,sum(`tb_transaction_detail`.`total`) AS `total_global`,(sum(`tb_transaction_detail`.`total`) * 0.8) AS `total_jukir`,(sum(`tb_transaction_detail`.`total`) * 0.2) AS `total_tgr`,((sum(`tb_transaction_detail`.`total`) * 0.2) / 2) AS `total_cashback` from (((`tb_jukir` join `tb_transaction`) join `tb_transaction_detail`) join `tb_ms_location`) where ((`tb_jukir`.`id` = `tb_transaction`.`jukir_id`) and (`tb_transaction`.`id` = `tb_transaction_detail`.`transaction_id`) and (`tb_ms_location`.`id` = `tb_transaction`.`location_id`)) group by `tb_jukir`.`id`,`tb_transaction`.`date_transaction`,`tb_jukir`.`nik`,`tb_jukir`.`full_name`,`tb_jukir`.`created_at`,`tb_ms_location`.`location_name` ;
1376
1377-- --------------------------------------------------------
1378
1379--
1380-- Structure for view `vw_cashback_tes`
1381--
1382DROP TABLE IF EXISTS `vw_cashback_tes`;
1383
1384CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_cashback_tes`  AS  select `tb_jukir`.`is_active` AS `active`,`tb_jukir`.`nik` AS `nik`,`tb_jukir`.`full_name` AS `nama`,`tb_ms_location`.`location_name` AS `lokasi`,`tb_transaction`.`date_transaction` AS `tanggal_setor`,sum((case when (`tb_transaction_detail`.`vehicle_type` = 1) then 1 else 0 end)) AS `mobil`,sum((case when (`tb_transaction_detail`.`vehicle_type` = 2) then 1 else 0 end)) AS `motor`,sum((case when (`tb_transaction_detail`.`vehicle_type` = 3) then 1 else 0 end)) AS `truck`,sum((case when (`tb_transaction_detail`.`vehicle_type` = 4) then 1 else 0 end)) AS `speda`,sum(`tb_transaction_detail`.`total`) AS `total_global`,(sum(`tb_transaction_detail`.`total`) * 0.8) AS `total_jukir`,(sum(`tb_transaction_detail`.`total`) * 0.2) AS `total_tgr`,((sum(`tb_transaction_detail`.`total`) * 0.2) / 2) AS `total_cashback`,(curdate() - cast(`tb_jukir`.`created_at` as date)) AS `status` from (((`tb_jukir` join `tb_transaction`) join `tb_transaction_detail`) join `tb_ms_location`) where ((`tb_jukir`.`id` = `tb_transaction`.`jukir_id`) and (`tb_transaction`.`id` = `tb_transaction_detail`.`transaction_id`) and (`tb_ms_location`.`id` = `tb_transaction`.`location_id`)) group by `tb_jukir`.`id`,`tb_transaction`.`date_transaction`,`tb_jukir`.`nik`,`tb_jukir`.`is_active`,`tb_jukir`.`full_name`,`tb_jukir`.`created_at`,`tb_ms_location`.`location_name` ;
1385
1386-- --------------------------------------------------------
1387
1388--
1389-- Structure for view `vw_mobil_transaction`
1390--
1391DROP TABLE IF EXISTS `vw_mobil_transaction`;
1392
1393CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_mobil_transaction`  AS  select `tb_transaction`.`jukir_id` AS `jukir_id`,`tb_jukir`.`nik` AS `nik`,`tb_transaction_detail`.`end_date` AS `date`,sum(`tb_transaction_detail`.`vehicle_type`) AS `mobil` from ((`tb_transaction_detail` join `tb_transaction` on((`tb_transaction`.`id` = `tb_transaction_detail`.`transaction_id`))) join `tb_jukir` on((`tb_transaction`.`jukir_id` = `tb_jukir`.`id`))) where (`tb_transaction_detail`.`vehicle_type` = '1') group by `tb_transaction_detail`.`end_date`,`tb_transaction`.`jukir_id` ;
1394
1395-- --------------------------------------------------------
1396
1397--
1398-- Structure for view `vw_motor_transaction`
1399--
1400DROP TABLE IF EXISTS `vw_motor_transaction`;
1401
1402CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_motor_transaction`  AS  select `tb_transaction`.`jukir_id` AS `jukir_id`,`tb_transaction_detail`.`end_date` AS `date`,sum(`tb_transaction_detail`.`vehicle_type`) AS `motor` from (`tb_transaction_detail` join `tb_transaction` on((`tb_transaction`.`id` = `tb_transaction_detail`.`transaction_id`))) where (`tb_transaction_detail`.`vehicle_type` = '2') group by `tb_transaction_detail`.`end_date`,`tb_transaction`.`jukir_id` ;
1403
1404-- --------------------------------------------------------
1405
1406--
1407-- Structure for view `vw_revenue_mj`
1408--
1409DROP TABLE IF EXISTS `vw_revenue_mj`;
1410
1411CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_revenue_mj`  AS  select `tb_jukir`.`nik` AS `nik`,`tb_jukir`.`full_name` AS `nama`,`tb_ms_location`.`location_name` AS `lokasi`,`tb_transaction`.`date_transaction` AS `tanggal_setor`,sum(`tb_transaction_detail`.`total`) AS `total_global` from (((`tb_jukir` join `tb_transaction` on((`tb_jukir`.`id` = `tb_transaction`.`jukir_id`))) join `tb_transaction_detail` on((`tb_transaction`.`id` = `tb_transaction_detail`.`transaction_id`))) join `tb_ms_location` on((`tb_ms_location`.`id` = `tb_transaction`.`location_id`))) group by `tb_transaction`.`date_transaction`,`tb_jukir`.`nik`,`tb_jukir`.`full_name`,`tb_jukir`.`created_at`,`tb_ms_location`.`location_name` ;
1412
1413-- --------------------------------------------------------
1414
1415--
1416-- Structure for view `vw_speda_transaction`
1417--
1418DROP TABLE IF EXISTS `vw_speda_transaction`;
1419
1420CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_speda_transaction`  AS  select `tb_transaction`.`jukir_id` AS `jukir_id`,`tb_transaction_detail`.`end_date` AS `date`,sum(`tb_transaction_detail`.`vehicle_type`) AS `mobil` from (`tb_transaction_detail` join `tb_transaction` on((`tb_transaction`.`id` = `tb_transaction_detail`.`transaction_id`))) where (`tb_transaction_detail`.`vehicle_type` = '4') group by `tb_transaction_detail`.`end_date`,`tb_transaction`.`jukir_id` ;
1421
1422-- --------------------------------------------------------
1423
1424--
1425-- Structure for view `vw_sum_report_transaction`
1426--
1427DROP TABLE IF EXISTS `vw_sum_report_transaction`;
1428
1429CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_sum_report_transaction`  AS  select `tb_transaction`.`jukir_id` AS `jukir_id`,cast(`tb_transaction`.`date_transaction` as date) AS `date`,sum(`tb_transaction`.`amount`) AS `amount` from `tb_transaction` group by `tb_transaction`.`date_transaction`,`tb_transaction`.`jukir_id` order by cast(`tb_transaction`.`date_transaction` as date) ;
1430
1431-- --------------------------------------------------------
1432
1433--
1434-- Structure for view `vw_sum_report_transaction_detail`
1435--
1436DROP TABLE IF EXISTS `vw_sum_report_transaction_detail`;
1437
1438CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_sum_report_transaction_detail`  AS  select `td`.`id` AS `id`,`td`.`transaction_id` AS `transaction_id`,`t`.`jukir_id` AS `jukir_id`,`t`.`date_transaction` AS `date_transaction`,`t`.`location_id` AS `location_id`,`td`.`transaction_type` AS `transaction_type`,`td`.`payment_type` AS `payment_type`,sum(`td`.`total`) AS `sum(td.total)` from (`tb_transaction_detail` `td` join `tb_transaction` `t` on((`t`.`id` = `td`.`transaction_id`))) group by `t`.`date_transaction`,`td`.`transaction_type`,`td`.`payment_type`,`t`.`location_id`,`t`.`jukir_id` ;
1439
1440-- --------------------------------------------------------
1441
1442--
1443-- Structure for view `vw_transaction_detail`
1444--
1445DROP TABLE IF EXISTS `vw_transaction_detail`;
1446
1447CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_transaction_detail`  AS  select `td`.`id` AS `id`,`td`.`transaction_id` AS `transaction_id`,`t`.`jukir_id` AS `jukir_id`,`j`.`full_name` AS `full_name`,`j`.`nik` AS `nik`,`t`.`location_id` AS `location_id`,`l`.`location_name` AS `location_name`,`td`.`vehicle_type` AS `vehicle_type`,`vt`.`name` AS `vehicle_type_name`,`td`.`police_number` AS `police_number`,`td`.`start_date` AS `start_date`,`td`.`start_time` AS `start_time`,`td`.`end_date` AS `end_date`,`td`.`end_time` AS `end_time`,`td`.`base_amout` AS `base_amout`,`td`.`promo` AS `promo`,`td`.`services_charges` AS `services_charges`,`td`.`total` AS `total`,(`td`.`total` * 0.8) AS `total_jukir`,(`td`.`total` * 0.2) AS `total_tgr`,`td`.`transaction_type` AS `transaction_type`,`tt`.`name` AS `transaction_type_name`,`td`.`payment_type` AS `payment_type`,`tpt`.`name` AS `payment_type_name`,`td`.`note` AS `note`,`td`.`upd_by` AS `upd_by`,`td`.`created_at` AS `created_at`,`td`.`updated_at` AS `updated_at`,`t`.`is_active` AS `is_active` from (((((((`tb_transaction_detail` `td` join `tb_transaction_type` `tt` on((`td`.`transaction_type` = `tt`.`id`))) join `tb_transaction` `t` on((`t`.`id` = `td`.`transaction_id`))) join `tb_transaction_sub_type` `tst` on((`tst`.`id` = `td`.`payment_type`))) join `tb_payment_type` `tpt` on((`tpt`.`id` = `td`.`payment_type`))) join `tb_vehicle_type` `vt` on((`vt`.`id` = `td`.`vehicle_type`))) join `tb_ms_location` `l` on((`l`.`id` = `t`.`location_id`))) join `tb_jukir` `j` on((`j`.`id` = `t`.`jukir_id`))) ;
1448
1449-- --------------------------------------------------------
1450
1451--
1452-- Structure for view `vw_transaction_detail2`
1453--
1454DROP TABLE IF EXISTS `vw_transaction_detail2`;
1455
1456CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_transaction_detail2`  AS  select `td`.`id` AS `id`,`td`.`transaction_id` AS `transaction_id`,`t`.`jukir_id` AS `jukir_id`,`j`.`full_name` AS `full_name`,`j`.`nik` AS `nik`,`t`.`location_id` AS `location_id`,`l`.`location_name` AS `location_name`,`td`.`vehicle_type` AS `vehicle_type`,`vt`.`name` AS `vehicle_type_name`,`td`.`police_number` AS `police_number`,`td`.`start_date` AS `start_date`,`td`.`start_time` AS `start_time`,`td`.`end_date` AS `end_date`,`td`.`end_time` AS `end_time`,`td`.`base_amout` AS `base_amout`,`td`.`promo` AS `promo`,`td`.`services_charges` AS `services_charges`,`td`.`total` AS `total`,(`td`.`total` * 0.8) AS `total_jukir`,(`td`.`total` * 0.2) AS `total_tgr`,`td`.`transaction_type` AS `transaction_type`,`tt`.`name` AS `transaction_type_name`,`td`.`payment_type` AS `payment_type`,`tpt`.`name` AS `payment_type_name`,`td`.`note` AS `note`,`td`.`upd_by` AS `upd_by`,`td`.`created_at` AS `created_at`,`td`.`updated_at` AS `updated_at`,`t`.`is_active` AS `is_active` from (((((((`tb_transaction_detail` `td` join `tb_transaction_type` `tt` on((`td`.`transaction_type` = `tt`.`id`))) join `tb_transaction` `t` on((`t`.`id` = `td`.`transaction_id`))) join `tb_transaction_sub_type` `tst` on((`tst`.`id` = `td`.`payment_type`))) join `tb_payment_type` `tpt` on((`tpt`.`id` = `td`.`payment_type`))) join `tb_vehicle_type` `vt` on((`vt`.`id` = `td`.`vehicle_type`))) join `tb_ms_location` `l` on((`l`.`id` = `t`.`location_id`))) join `tb_jukir` `j` on((`j`.`id` = `t`.`jukir_id`))) ;
1457
1458-- --------------------------------------------------------
1459
1460--
1461-- Structure for view `vw_truck_transaction`
1462--
1463DROP TABLE IF EXISTS `vw_truck_transaction`;
1464
1465CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_truck_transaction`  AS  select `tb_transaction`.`jukir_id` AS `jukir_id`,`tb_transaction_detail`.`end_date` AS `date`,sum(`tb_transaction_detail`.`vehicle_type`) AS `truck` from (`tb_transaction_detail` join `tb_transaction` on((`tb_transaction`.`id` = `tb_transaction_detail`.`transaction_id`))) where (`tb_transaction_detail`.`vehicle_type` = '3') group by `tb_transaction_detail`.`end_date`,`tb_transaction`.`jukir_id` ;
1466
1467--
1468-- Indexes for dumped tables
1469--
1470
1471--
1472-- Indexes for table `bukti_transaksi`
1473--
1474ALTER TABLE `bukti_transaksi`
1475  ADD PRIMARY KEY (`id`);
1476
1477--
1478-- Indexes for table `migrations`
1479--
1480ALTER TABLE `migrations`
1481  ADD PRIMARY KEY (`id`);
1482
1483--
1484-- Indexes for table `password_resets`
1485--
1486ALTER TABLE `password_resets`
1487  ADD KEY `password_resets_email_index` (`email`),
1488  ADD KEY `password_resets_token_index` (`token`);
1489
1490--
1491-- Indexes for table `roles`
1492--
1493ALTER TABLE `roles`
1494  ADD PRIMARY KEY (`id`),
1495  ADD UNIQUE KEY `name` (`name`);
1496
1497--
1498-- Indexes for table `summary_transaction_detail`
1499--
1500ALTER TABLE `summary_transaction_detail`
1501  ADD UNIQUE KEY `transaction_id` (`transaction_id`);
1502
1503--
1504-- Indexes for table `summary_virtual`
1505--
1506ALTER TABLE `summary_virtual`
1507  ADD PRIMARY KEY (`id`),
1508  ADD UNIQUE KEY `transaction_id` (`transaction_id`);
1509
1510--
1511-- Indexes for table `summary_virtual_1`
1512--
1513ALTER TABLE `summary_virtual_1`
1514  ADD PRIMARY KEY (`id`),
1515  ADD UNIQUE KEY `id_trans` (`id_transaction`);
1516
1517--
1518-- Indexes for table `sum_bc`
1519--
1520ALTER TABLE `sum_bc`
1521  ADD PRIMARY KEY (`transaction_id`);
1522
1523--
1524-- Indexes for table `tb_3d_account`
1525--
1526ALTER TABLE `tb_3d_account`
1527  ADD PRIMARY KEY (`id`);
1528
1529--
1530-- Indexes for table `tb_admin_location`
1531--
1532ALTER TABLE `tb_admin_location`
1533  ADD PRIMARY KEY (`id`);
1534
1535--
1536-- Indexes for table `tb_apps_landing_page`
1537--
1538ALTER TABLE `tb_apps_landing_page`
1539  ADD PRIMARY KEY (`id_landing`);
1540
1541--
1542-- Indexes for table `tb_apps_splash_screen`
1543--
1544ALTER TABLE `tb_apps_splash_screen`
1545  ADD PRIMARY KEY (`id_splash`);
1546
1547--
1548-- Indexes for table `tb_apps_version`
1549--
1550ALTER TABLE `tb_apps_version`
1551  ADD PRIMARY KEY (`id_version`);
1552
1553--
1554-- Indexes for table `tb_cashback`
1555--
1556ALTER TABLE `tb_cashback`
1557  ADD PRIMARY KEY (`id`);
1558
1559--
1560-- Indexes for table `tb_category_nik`
1561--
1562ALTER TABLE `tb_category_nik`
1563  ADD PRIMARY KEY (`id_category_nik`),
1564  ADD UNIQUE KEY `id_category_nik` (`nik`);
1565
1566--
1567-- Indexes for table `tb_complain_category`
1568--
1569ALTER TABLE `tb_complain_category`
1570  ADD PRIMARY KEY (`id`);
1571
1572--
1573-- Indexes for table `tb_customer`
1574--
1575ALTER TABLE `tb_customer`
1576  ADD PRIMARY KEY (`id`),
1577  ADD UNIQUE KEY `name_UNIQUE` (`name`),
1578  ADD UNIQUE KEY `email_UNIQUE` (`email`),
1579  ADD UNIQUE KEY `phone_number_UNIQUE` (`phone_number`);
1580
1581--
1582-- Indexes for table `tb_customer_vehicle`
1583--
1584ALTER TABLE `tb_customer_vehicle`
1585  ADD PRIMARY KEY (`id`);
1586
1587--
1588-- Indexes for table `tb_jukir`
1589--
1590ALTER TABLE `tb_jukir`
1591  ADD PRIMARY KEY (`id`),
1592  ADD UNIQUE KEY `nik_UNIQUE` (`nik`),
1593  ADD UNIQUE KEY `id` (`id`,`nik`) USING BTREE;
1594
1595--
1596-- Indexes for table `tb_jukir_balance`
1597--
1598ALTER TABLE `tb_jukir_balance`
1599  ADD PRIMARY KEY (`id_jukir`);
1600
1601--
1602-- Indexes for table `tb_jukir_profile`
1603--
1604ALTER TABLE `tb_jukir_profile`
1605  ADD PRIMARY KEY (`id_jukir_profile`),
1606  ADD UNIQUE KEY `id_jukir` (`id_jukir`);
1607
1608--
1609-- Indexes for table `tb_jukir_virtual_payment`
1610--
1611ALTER TABLE `tb_jukir_virtual_payment`
1612  ADD PRIMARY KEY (`id_transaction`);
1613
1614--
1615-- Indexes for table `tb_jukir_virtual_payment_status`
1616--
1617ALTER TABLE `tb_jukir_virtual_payment_status`
1618  ADD PRIMARY KEY (`id`);
1619
1620--
1621-- Indexes for table `tb_location_type`
1622--
1623ALTER TABLE `tb_location_type`
1624  ADD PRIMARY KEY (`id`);
1625
1626--
1627-- Indexes for table `tb_location_vehicle_fare`
1628--
1629ALTER TABLE `tb_location_vehicle_fare`
1630  ADD PRIMARY KEY (`id`);
1631
1632--
1633-- Indexes for table `tb_ms_city`
1634--
1635ALTER TABLE `tb_ms_city`
1636  ADD PRIMARY KEY (`id`);
1637
1638--
1639-- Indexes for table `tb_ms_location`
1640--
1641ALTER TABLE `tb_ms_location`
1642  ADD PRIMARY KEY (`id`),
1643  ADD UNIQUE KEY `location_name_UNIQUE` (`location_name`),
1644  ADD UNIQUE KEY `id` (`id`) USING BTREE;
1645
1646--
1647-- Indexes for table `tb_ms_parking_charges`
1648--
1649ALTER TABLE `tb_ms_parking_charges`
1650  ADD PRIMARY KEY (`id`);
1651
1652--
1653-- Indexes for table `tb_ms_province`
1654--
1655ALTER TABLE `tb_ms_province`
1656  ADD PRIMARY KEY (`id`);
1657
1658--
1659-- Indexes for table `tb_ms_sub_district`
1660--
1661ALTER TABLE `tb_ms_sub_district`
1662  ADD PRIMARY KEY (`id`);
1663
1664--
1665-- Indexes for table `tb_ms_user_type`
1666--
1667ALTER TABLE `tb_ms_user_type`
1668  ADD PRIMARY KEY (`id`);
1669
1670--
1671-- Indexes for table `tb_owner`
1672--
1673ALTER TABLE `tb_owner`
1674  ADD PRIMARY KEY (`id`),
1675  ADD UNIQUE KEY `tes` (`code`);
1676
1677--
1678-- Indexes for table `tb_payment_type`
1679--
1680ALTER TABLE `tb_payment_type`
1681  ADD PRIMARY KEY (`id`),
1682  ADD UNIQUE KEY `name_UNIQUE` (`name`);
1683
1684--
1685-- Indexes for table `tb_pendaftaran_mitra_jukir`
1686--
1687ALTER TABLE `tb_pendaftaran_mitra_jukir`
1688  ADD PRIMARY KEY (`id_pendaftaran`),
1689  ADD UNIQUE KEY `email` (`email`),
1690  ADD UNIQUE KEY `hp` (`no_hp`);
1691
1692--
1693-- Indexes for table `tb_profit`
1694--
1695ALTER TABLE `tb_profit`
1696  ADD PRIMARY KEY (`id`);
1697
1698--
1699-- Indexes for table `tb_progressive`
1700--
1701ALTER TABLE `tb_progressive`
1702  ADD UNIQUE KEY `qr_code` (`qr_code`);
1703
1704--
1705-- Indexes for table `tb_titik_parkir`
1706--
1707ALTER TABLE `tb_titik_parkir`
1708  ADD PRIMARY KEY (`id`);
1709
1710--
1711-- Indexes for table `tb_transaction`
1712--
1713ALTER TABLE `tb_transaction`
1714  ADD PRIMARY KEY (`id`);
1715
1716--
1717-- Indexes for table `tb_transaction_detail`
1718--
1719ALTER TABLE `tb_transaction_detail`
1720  ADD PRIMARY KEY (`id`);
1721
1722--
1723-- Indexes for table `tb_transaction_sub_type`
1724--
1725ALTER TABLE `tb_transaction_sub_type`
1726  ADD PRIMARY KEY (`id`);
1727
1728--
1729-- Indexes for table `tb_transaction_type`
1730--
1731ALTER TABLE `tb_transaction_type`
1732  ADD PRIMARY KEY (`id`),
1733  ADD UNIQUE KEY `id` (`id`) USING BTREE;
1734
1735--
1736-- Indexes for table `tb_user`
1737--
1738ALTER TABLE `tb_user`
1739  ADD PRIMARY KEY (`id`);
1740
1741--
1742-- Indexes for table `tb_user_booking`
1743--
1744ALTER TABLE `tb_user_booking`
1745  ADD PRIMARY KEY (`id`);
1746
1747--
1748-- Indexes for table `tb_vehicle_type`
1749--
1750ALTER TABLE `tb_vehicle_type`
1751  ADD PRIMARY KEY (`id`),
1752  ADD UNIQUE KEY `id` (`id`) USING BTREE;
1753
1754--
1755-- Indexes for table `users`
1756--
1757ALTER TABLE `users`
1758  ADD PRIMARY KEY (`id`),
1759  ADD UNIQUE KEY `users_email_unique` (`email`);
1760
1761--
1762-- AUTO_INCREMENT for dumped tables
1763--
1764
1765--
1766-- AUTO_INCREMENT for table `bukti_transaksi`
1767--
1768ALTER TABLE `bukti_transaksi`
1769  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
1770
1771--
1772-- AUTO_INCREMENT for table `migrations`
1773--
1774ALTER TABLE `migrations`
1775  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
1776
1777--
1778-- AUTO_INCREMENT for table `roles`
1779--
1780ALTER TABLE `roles`
1781  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
1782
1783--
1784-- AUTO_INCREMENT for table `summary_virtual_1`
1785--
1786ALTER TABLE `summary_virtual_1`
1787  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
1788
1789--
1790-- AUTO_INCREMENT for table `tb_3d_account`
1791--
1792ALTER TABLE `tb_3d_account`
1793  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
1794
1795--
1796-- AUTO_INCREMENT for table `tb_admin_location`
1797--
1798ALTER TABLE `tb_admin_location`
1799  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=38;
1800
1801--
1802-- AUTO_INCREMENT for table `tb_apps_landing_page`
1803--
1804ALTER TABLE `tb_apps_landing_page`
1805  MODIFY `id_landing` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;
1806
1807--
1808-- AUTO_INCREMENT for table `tb_apps_splash_screen`
1809--
1810ALTER TABLE `tb_apps_splash_screen`
1811  MODIFY `id_splash` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
1812
1813--
1814-- AUTO_INCREMENT for table `tb_apps_version`
1815--
1816ALTER TABLE `tb_apps_version`
1817  MODIFY `id_version` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=64;
1818
1819--
1820-- AUTO_INCREMENT for table `tb_cashback`
1821--
1822ALTER TABLE `tb_cashback`
1823  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
1824
1825--
1826-- AUTO_INCREMENT for table `tb_category_nik`
1827--
1828ALTER TABLE `tb_category_nik`
1829  MODIFY `id_category_nik` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
1830
1831--
1832-- AUTO_INCREMENT for table `tb_complain_category`
1833--
1834ALTER TABLE `tb_complain_category`
1835  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
1836
1837--
1838-- AUTO_INCREMENT for table `tb_customer_vehicle`
1839--
1840ALTER TABLE `tb_customer_vehicle`
1841  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
1842
1843--
1844-- AUTO_INCREMENT for table `tb_jukir_profile`
1845--
1846ALTER TABLE `tb_jukir_profile`
1847  MODIFY `id_jukir_profile` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
1848
1849--
1850-- AUTO_INCREMENT for table `tb_jukir_virtual_payment_status`
1851--
1852ALTER TABLE `tb_jukir_virtual_payment_status`
1853  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
1854
1855--
1856-- AUTO_INCREMENT for table `tb_location_type`
1857--
1858ALTER TABLE `tb_location_type`
1859  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
1860
1861--
1862-- AUTO_INCREMENT for table `tb_location_vehicle_fare`
1863--
1864ALTER TABLE `tb_location_vehicle_fare`
1865  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=678;
1866
1867--
1868-- AUTO_INCREMENT for table `tb_ms_city`
1869--
1870ALTER TABLE `tb_ms_city`
1871  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=506;
1872
1873--
1874-- AUTO_INCREMENT for table `tb_ms_location`
1875--
1876ALTER TABLE `tb_ms_location`
1877  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=32;
1878
1879--
1880-- AUTO_INCREMENT for table `tb_ms_parking_charges`
1881--
1882ALTER TABLE `tb_ms_parking_charges`
1883  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
1884
1885--
1886-- AUTO_INCREMENT for table `tb_ms_province`
1887--
1888ALTER TABLE `tb_ms_province`
1889  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=35;
1890
1891--
1892-- AUTO_INCREMENT for table `tb_ms_sub_district`
1893--
1894ALTER TABLE `tb_ms_sub_district`
1895  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6995;
1896
1897--
1898-- AUTO_INCREMENT for table `tb_ms_user_type`
1899--
1900ALTER TABLE `tb_ms_user_type`
1901  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;
1902
1903--
1904-- AUTO_INCREMENT for table `tb_owner`
1905--
1906ALTER TABLE `tb_owner`
1907  MODIFY `id` int(6) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
1908
1909--
1910-- AUTO_INCREMENT for table `tb_payment_type`
1911--
1912ALTER TABLE `tb_payment_type`
1913  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
1914
1915--
1916-- AUTO_INCREMENT for table `tb_profit`
1917--
1918ALTER TABLE `tb_profit`
1919  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
1920
1921--
1922-- AUTO_INCREMENT for table `tb_titik_parkir`
1923--
1924ALTER TABLE `tb_titik_parkir`
1925  MODIFY `id` int(6) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
1926
1927--
1928-- AUTO_INCREMENT for table `tb_transaction_detail`
1929--
1930ALTER TABLE `tb_transaction_detail`
1931  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=659636;
1932
1933--
1934-- AUTO_INCREMENT for table `tb_transaction_sub_type`
1935--
1936ALTER TABLE `tb_transaction_sub_type`
1937  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
1938
1939--
1940-- AUTO_INCREMENT for table `tb_transaction_type`
1941--
1942ALTER TABLE `tb_transaction_type`
1943  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
1944
1945--
1946-- AUTO_INCREMENT for table `tb_user`
1947--
1948ALTER TABLE `tb_user`
1949  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=164;
1950
1951--
1952-- AUTO_INCREMENT for table `tb_vehicle_type`
1953--
1954ALTER TABLE `tb_vehicle_type`
1955  MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
1956
1957--
1958-- AUTO_INCREMENT for table `users`
1959--
1960ALTER TABLE `users`
1961  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
1962COMMIT;