· 6 years ago · Jul 24, 2019, 08:30 AM
1SET NAMES utf8;
2SET time_zone = '+00:00';
3SET foreign_key_checks = 0;
4SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
5
6SET NAMES utf8mb4;
7
8DROP TABLE IF EXISTS `action`;
9CREATE TABLE `action` (
10 `action_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
11 `menu_id` int(10) unsigned NOT NULL,
12 `action_value` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
13 `action_status` tinyint(4) NOT NULL,
14 PRIMARY KEY (`action_id`),
15 KEY `menu_id` (`menu_id`),
16 CONSTRAINT `action_ibfk_1` FOREIGN KEY (`menu_id`) REFERENCES `menus` (`id`)
17) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
18
19INSERT INTO `action` (`action_id`, `menu_id`, `action_value`, `action_status`) VALUES
20(1, 15, 'create_role', 1),
21(2, 15, 'edit_role', 1),
22(3, 15, 'delete_role', 1),
23(4, 16, 'create_user', 1),
24(5, 16, 'edit_user', 1),
25(6, 16, 'delete_user', 1),
26(7, 17, 'edit_password_menu', 1),
27(8, 6, 'payment_gateway', 1)
28ON DUPLICATE KEY UPDATE `action_id` = VALUES(`action_id`), `menu_id` = VALUES(`menu_id`), `action_value` = VALUES(`action_value`), `action_status` = VALUES(`action_status`);
29
30DROP TABLE IF EXISTS `customers`;
31CREATE TABLE `customers` (
32 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
33 `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
34 `email` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
35 `city` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
36 `deposit` int(11) NOT NULL,
37 `created_at` timestamp NULL DEFAULT NULL,
38 `updated_at` timestamp NULL DEFAULT NULL,
39 PRIMARY KEY (`id`),
40 UNIQUE KEY `customers_email_unique` (`email`)
41) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
42
43INSERT INTO `customers` (`id`, `name`, `email`, `city`, `deposit`, `created_at`, `updated_at`) VALUES
44(1, 'Sedrick Beatty', 'lesch.catharine@example.net', 'New Tayamouth', 78745, '2019-07-22 02:08:08', '2019-07-22 02:08:08'),
45(2, 'Adrianna Witting', 'maybelle.gorczany@example.com', 'Zoebury', 53294, '2019-07-22 02:08:08', '2019-07-22 02:08:08'),
46(3, 'Mr. Tito Rau', 'lesly.jacobs@example.net', 'Chandlerberg', 34848, '2019-07-22 02:08:08', '2019-07-22 02:08:08'),
47(4, 'Emmanuelle Considine MD', 'jairo17@example.org', 'East Hayley', 75768, '2019-07-22 02:08:08', '2019-07-22 02:08:08'),
48(5, 'Prof. Johnathon Turner DDS', 'windler.callie@example.org', 'East Lelahton', 91572, '2019-07-22 02:08:08', '2019-07-22 02:08:08'),
49(6, 'Dr. Margot Luettgen', 'jennings73@example.com', 'New Lindseyfurt', 88471, '2019-07-22 02:08:08', '2019-07-22 02:08:08'),
50(7, 'Hortense Goldner', 'wilma.schmidt@example.com', 'New Derekchester', 97874, '2019-07-22 02:08:08', '2019-07-22 02:08:08'),
51(8, 'Rosemarie Ebert', 'odie.goodwin@example.net', 'Domenicville', 21525, '2019-07-22 02:08:08', '2019-07-22 02:08:08'),
52(9, 'Audie West', 'rice.zora@example.org', 'Reymundoton', 78538, '2019-07-22 02:08:08', '2019-07-22 02:08:08'),
53(10, 'Queen Blanda', 'bkonopelski@example.net', 'New Brent', 32696, '2019-07-22 02:08:08', '2019-07-22 02:08:08'),
54(11, 'Prof. Ismael Kiehn', 'kenny79@example.net', 'Ethelynchester', 18844, '2019-07-22 02:08:08', '2019-07-22 02:08:08'),
55(12, 'Christelle Gusikowski', 'wromaguera@example.org', 'North Jacefurt', 50067, '2019-07-22 02:08:08', '2019-07-22 02:08:08'),
56(13, 'Dr. Eula Koelpin', 'marcos10@example.net', 'Randallfort', 36282, '2019-07-22 02:08:08', '2019-07-22 02:08:08'),
57(14, 'Miss Francisca Zulauf', 'oprice@example.org', 'Leonelhaven', 46219, '2019-07-22 02:08:08', '2019-07-22 02:08:08'),
58(15, 'Eula Schaden', 'viviane.lueilwitz@example.org', 'Port Myra', 25424, '2019-07-22 02:08:08', '2019-07-22 02:08:08'),
59(16, 'Dr. Tara Rau DVM', 'prudence68@example.net', 'South Oleta', 53087, '2019-07-22 02:08:08', '2019-07-22 02:08:08'),
60(17, 'Ricardo Skiles', 'homenick.trinity@example.org', 'Gerholdfort', 93114, '2019-07-22 02:08:08', '2019-07-22 02:08:08'),
61(18, 'Kavon Schroeder', 'zmurazik@example.com', 'East Amber', 21092, '2019-07-22 02:08:08', '2019-07-22 02:08:08'),
62(19, 'Prof. Jolie Mitchell', 'geoffrey08@example.com', 'Haileestad', 52762, '2019-07-22 02:08:08', '2019-07-22 02:08:08'),
63(20, 'Gabe Deckow Sr.', 'morris.dare@example.com', 'Maximoport', 40509, '2019-07-22 02:08:08', '2019-07-22 02:08:08')
64ON DUPLICATE KEY UPDATE `id` = VALUES(`id`), `name` = VALUES(`name`), `email` = VALUES(`email`), `city` = VALUES(`city`), `deposit` = VALUES(`deposit`), `created_at` = VALUES(`created_at`), `updated_at` = VALUES(`updated_at`);
65
66DROP TABLE IF EXISTS `menus`;
67CREATE TABLE `menus` (
68 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
69 `parent_id` int(11) NOT NULL,
70 `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
71 `icon` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
72 `url` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
73 `menu_status` tinyint(3) unsigned NOT NULL DEFAULT '1',
74 PRIMARY KEY (`id`)
75) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
76
77INSERT INTO `menus` (`id`, `parent_id`, `name`, `icon`, `url`, `menu_status`) VALUES
78(1, 0, 'Dashboard', 'fa fa-dashboard', '/home', 1),
79(2, 0, 'Master Data', 'fa fa-database', '', 1),
80(3, 0, 'Transaction', 'fa fa-server', '', 1),
81(4, 0, 'Report', 'fa fa-book', '', 1),
82(5, 0, 'Setting', 'fa fa-gear', '', 1),
83(6, 2, 'Payment Gateway', 'fa fa-circle-o', '/payment', 1),
84(7, 2, 'Payment Channel', 'fa fa-circle-o', '#', 1),
85(8, 2, 'Merchant', 'fa fa-circle-o', '#', 1),
86(9, 2, 'Merchant Channel', 'fa fa-circle-o', '#', 1),
87(10, 3, 'Request', 'fa fa-circle-o', '#', 1),
88(11, 3, 'Notification', 'fa fa-circle-o', '#', 1),
89(12, 3, 'Confirmation', 'fa fa-circle-o', '#', 1),
90(13, 3, 'Log', 'fa fa-circle-o', '#', 1),
91(14, 4, 'Transaction', 'fa fa-circle-o', '#', 1),
92(15, 5, 'Role', 'fa fa-user', '/role', 1),
93(16, 5, 'User', 'fa fa-group', '/user', 1),
94(17, 5, 'Edit Password', 'fa fa-edit', '/editpassword', 1)
95ON DUPLICATE KEY UPDATE `id` = VALUES(`id`), `parent_id` = VALUES(`parent_id`), `name` = VALUES(`name`), `icon` = VALUES(`icon`), `url` = VALUES(`url`), `menu_status` = VALUES(`menu_status`);
96
97DROP TABLE IF EXISTS `migrations`;
98CREATE TABLE `migrations` (
99 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
100 `migration` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
101 `batch` int(11) NOT NULL,
102 PRIMARY KEY (`id`)
103) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
104
105INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES
106(1, '2014_10_12_000000_create_users_table', 1),
107(2, '2014_10_12_100000_create_password_resets_table', 1),
108(3, '2019_06_26_145932_create_customers_table', 1),
109(4, '2019_07_15_031948_create_roles_table', 1),
110(5, '2019_07_15_045417_create_menus_table', 1),
111(6, '2019_07_22_034249_create_role_user_table', 1)
112ON DUPLICATE KEY UPDATE `id` = VALUES(`id`), `migration` = VALUES(`migration`), `batch` = VALUES(`batch`);
113
114DROP TABLE IF EXISTS `password_resets`;
115CREATE TABLE `password_resets` (
116 `email` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
117 `token` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
118 `created_at` timestamp NULL DEFAULT NULL,
119 KEY `password_resets_email_index` (`email`)
120) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
121
122
123DROP TABLE IF EXISTS `roles`;
124CREATE TABLE `roles` (
125 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
126 `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
127 `description` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
128 `created_at` timestamp NULL DEFAULT NULL,
129 `updated_at` timestamp NULL DEFAULT NULL,
130 PRIMARY KEY (`id`)
131) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
132
133INSERT INTO `roles` (`id`, `name`, `description`, `created_at`, `updated_at`) VALUES
134(1, 'Admin', 'User yang memiliki akses penuh terhadap semua modul dan menu', '2019-07-22 01:30:41', NULL),
135(2, 'Regular', 'User yang dibatasi dalam mengakses modul dan menu tertentu', '2019-07-22 01:06:54', NULL)
136ON DUPLICATE KEY UPDATE `id` = VALUES(`id`), `name` = VALUES(`name`), `description` = VALUES(`description`), `created_at` = VALUES(`created_at`), `updated_at` = VALUES(`updated_at`);
137
138DROP TABLE IF EXISTS `role_user`;
139CREATE TABLE `role_user` (
140 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
141 `user_id` int(10) unsigned NOT NULL,
142 `role_id` int(10) unsigned NOT NULL,
143 `created_at` timestamp NULL DEFAULT NULL,
144 `updated_at` timestamp NULL DEFAULT NULL,
145 PRIMARY KEY (`id`)
146) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
147
148INSERT INTO `role_user` (`id`, `user_id`, `role_id`, `created_at`, `updated_at`) VALUES
149(1, 1, 1, NULL, NULL),
150(2, 3, 1, NULL, NULL),
151(3, 5, 1, NULL, NULL)
152ON DUPLICATE KEY UPDATE `id` = VALUES(`id`), `user_id` = VALUES(`user_id`), `role_id` = VALUES(`role_id`), `created_at` = VALUES(`created_at`), `updated_at` = VALUES(`updated_at`);
153
154DROP TABLE IF EXISTS `users`;
155CREATE TABLE `users` (
156 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
157 `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
158 `email` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
159 `password` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
160 `role_type` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT '2',
161 `remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
162 `created_at` timestamp NULL DEFAULT NULL,
163 `updated_at` timestamp NULL DEFAULT NULL,
164 PRIMARY KEY (`id`),
165 UNIQUE KEY `users_email_unique` (`email`)
166) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
167
168INSERT INTO `users` (`id`, `name`, `email`, `password`, `role_type`, `remember_token`, `created_at`, `updated_at`) VALUES
169(1, 'Admin', 'admin@mail.com', '$2y$10$pfYXefeg2dZrr2wRfIRR/.9FjzfJXkGjBHIFAKSwOr6diOM/djzVq', '1', '97ofsh0OQTlNg7D5egOdGPw6nm3aHlrTQemCLZGG6exghhQoOxdR1nQSP9Vc', '2019-07-22 02:10:21', '2019-07-22 02:10:21'),
170(3, 'Anggit', 'anggitp07@gmail.com', '$2y$10$874.tJJuOwxv6xL0cQOuEuAU/KJok3wkOxN2TkChhfuZ3bOvYVUIK', '2', 'P6IQjcgNDjXXxYg1XvbvbraAXNUWq3eEgxZPWP0vhlP7Y8PdRfHBLvkAuFQn', '2019-07-22 21:24:17', '2019-07-22 21:24:17'),
171(4, 'huhu', 'aaaa@erere.com', '$2y$10$Ko/8uJTurhhsDYTw2uADYeZwsUARApzCGdLjR5fLkb2U8hMcByFhe', '1', 'NguxbKWv7vuEtc04ZgZYC9LfYCViYf8vUXcWz758yNGGWErPYqKnYNtfpwGq', '2019-07-22 23:33:07', '2019-07-22 23:33:07'),
172(5, 'dazai', 'dazai@gmail.com', '$2y$10$.kzwNS8GksKQQivrFcclBuwHQ8RYGHY6GPEqLrZdT0hl51jMMjA4u', '1', 'sY9HkNvh44il9DdP30yG9QPBNg19GK3IhOm6gzEVB8QQlPKVTIW00A0QPFKT', '2019-07-24 00:09:06', '2019-07-24 00:09:06')
173ON DUPLICATE KEY UPDATE `id` = VALUES(`id`), `name` = VALUES(`name`), `email` = VALUES(`email`), `password` = VALUES(`password`), `role_type` = VALUES(`role_type`), `remember_token` = VALUES(`remember_token`), `created_at` = VALUES(`created_at`), `updated_at` = VALUES(`updated_at`);
174
175DROP TABLE IF EXISTS `user_access`;
176CREATE TABLE `user_access` (
177 `user_access_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
178 `role_type_id` int(10) unsigned NOT NULL,
179 `menu_id` int(10) unsigned NOT NULL,
180 PRIMARY KEY (`user_access_id`),
181 KEY `role_type_id` (`role_type_id`),
182 KEY `menu_id` (`menu_id`),
183 CONSTRAINT `user_access_ibfk_1` FOREIGN KEY (`role_type_id`) REFERENCES `roles` (`id`),
184 CONSTRAINT `user_access_ibfk_2` FOREIGN KEY (`menu_id`) REFERENCES `menus` (`id`)
185) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
186
187INSERT INTO `user_access` (`user_access_id`, `role_type_id`, `menu_id`) VALUES
188(1, 1, 1),
189(2, 1, 2),
190(3, 1, 3),
191(4, 1, 4),
192(5, 1, 5),
193(6, 2, 1),
194(7, 2, 2),
195(8, 2, 4),
196(9, 2, 3),
197(10, 2, 5),
198(11, 1, 15),
199(12, 2, 15)
200ON DUPLICATE KEY UPDATE `user_access_id` = VALUES(`user_access_id`), `role_type_id` = VALUES(`role_type_id`), `menu_id` = VALUES(`menu_id`);
201
202DROP TABLE IF EXISTS `user_action`;
203CREATE TABLE `user_action` (
204 `user_action_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
205 `role_type_id` int(10) unsigned NOT NULL,
206 `action_id` int(10) unsigned NOT NULL,
207 PRIMARY KEY (`user_action_id`),
208 KEY `role_type_id` (`role_type_id`),
209 KEY `action_id` (`action_id`),
210 CONSTRAINT `user_action_ibfk_1` FOREIGN KEY (`role_type_id`) REFERENCES `roles` (`id`),
211 CONSTRAINT `user_action_ibfk_2` FOREIGN KEY (`action_id`) REFERENCES `action` (`action_id`)
212) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
213
214INSERT INTO `user_action` (`user_action_id`, `role_type_id`, `action_id`) VALUES
215(1, 1, 1),
216(2, 1, 2),
217(3, 1, 3),
218(4, 1, 4),
219(5, 1, 5),
220(6, 1, 6),
221(7, 1, 7),
222(8, 1, 8),
223(9, 2, 8)
224ON DUPLICATE KEY UPDATE `user_action_id` = VALUES(`user_action_id`), `role_type_id` = VALUES(`role_type_id`), `action_id` = VALUES(`action_id`);