· 9 years ago · Mar 13, 2017, 08:26 AM
1-- Adminer 4.2.5 MySQL dump
2
3SET NAMES utf8;
4SET time_zone = '+00:00';
5SET foreign_key_checks = 0;
6SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
7
8DROP TABLE IF EXISTS `accesses`;
9CREATE TABLE `accesses` (
10 `id` varchar(32) NOT NULL,
11 `description` varchar(64) DEFAULT NULL,
12 PRIMARY KEY (`id`),
13 UNIQUE KEY `id_UNIQUE` (`id`)
14) ENGINE=InnoDB DEFAULT CHARSET=latin1;
15
16INSERT INTO `accesses` (`id`, `description`) VALUES
17('ARSIP', 'Menampilkan data arsip transaksi bulanan dan tahunan'),
18('KOREKSI_BULANAN', 'Menampilkan Data Koreksi Bulanan'),
19('LAPORAN_TRANSAKSI', 'Menampilkan data laporan'),
20('MANAJEMEN_AKUN', 'CRUD user dan memberikan hak akses ke setiap user'),
21('MANAJEMEN_BIAYA', 'CRUD Term spp, kode biaya, parameter biaya '),
22('MANAJEMEN_SISWA', 'CRUD siswa'),
23('PEMBAYARAN', 'Memproses Pembayaran'),
24('PENGATURAN_SEKOLAH', 'Melakukan setting untuk sekolah'),
25('TAHUNAN', 'Memproses tranksaksi tahunan');
26
27DROP TABLE IF EXISTS `access_user_group`;
28CREATE TABLE `access_user_group` (
29 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
30 `access_id` varchar(32) CHARACTER SET latin1 NOT NULL,
31 `user_group_id` int(10) unsigned NOT NULL,
32 `created_at` timestamp NULL DEFAULT NULL,
33 `updated_at` timestamp NULL DEFAULT NULL,
34 PRIMARY KEY (`id`),
35 KEY `access_id` (`access_id`),
36 KEY `user_group_id` (`user_group_id`),
37 CONSTRAINT `access_user_group_ibfk_2` FOREIGN KEY (`access_id`) REFERENCES `accesses` (`id`) ON UPDATE CASCADE,
38 CONSTRAINT `access_user_group_ibfk_3` FOREIGN KEY (`user_group_id`) REFERENCES `user_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
39) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
40
41INSERT INTO `access_user_group` (`id`, `access_id`, `user_group_id`, `created_at`, `updated_at`) VALUES
42(10, 'PENGATURAN_SEKOLAH', 2, NULL, NULL),
43(11, 'MANAJEMEN_AKUN', 2, NULL, NULL),
44(12, 'MANAJEMEN_BIAYA', 2, NULL, NULL),
45(13, 'LAPORAN_TRANSAKSI', 2, NULL, NULL),
46(14, 'ARSIP', 2, NULL, NULL),
47(15, 'TAHUNAN', 2, NULL, NULL),
48(16, 'PEMBAYARAN', 2, NULL, NULL),
49(31, 'PENGATURAN_SEKOLAH', 1, NULL, NULL),
50(32, 'MANAJEMEN_AKUN', 1, NULL, NULL),
51(33, 'MANAJEMEN_BIAYA', 1, NULL, NULL),
52(34, 'MANAJEMEN_SISWA', 1, NULL, NULL),
53(35, 'LAPORAN_TRANSAKSI', 1, NULL, NULL),
54(36, 'ARSIP', 1, NULL, NULL),
55(37, 'TAHUNAN', 1, NULL, NULL),
56(38, 'KOREKSI_BULANAN', 1, NULL, NULL),
57(39, 'PEMBAYARAN', 1, NULL, NULL);
58
59DROP TABLE IF EXISTS `education_stages`;
60CREATE TABLE `education_stages` (
61 `id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
62 `description` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
63 `next` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
64 PRIMARY KEY (`id`),
65 KEY `education_stages_next_foreign` (`next`),
66 CONSTRAINT `education_stages_next_foreign` FOREIGN KEY (`next`) REFERENCES `education_stages` (`id`) ON UPDATE CASCADE
67) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
68
69INSERT INTO `education_stages` (`id`, `description`, `next`) VALUES
70('1', 'SMA', NULL),
71('2', 'SMP', '1'),
72('3', 'SD', '2'),
73('4', 'TK', '3');
74
75DROP TABLE IF EXISTS `fees`;
76CREATE TABLE `fees` (
77 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
78 `education_stage_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
79 `fee_code_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
80 `amount` decimal(19,4) NOT NULL,
81 `created_at` timestamp NULL DEFAULT NULL,
82 `updated_at` timestamp NULL DEFAULT NULL,
83 PRIMARY KEY (`id`),
84 KEY `fees_education_stage_id_foreign` (`education_stage_id`),
85 KEY `fees_fee_code_id_foreign` (`fee_code_id`),
86 CONSTRAINT `fees_education_stage_id_foreign` FOREIGN KEY (`education_stage_id`) REFERENCES `education_stages` (`id`) ON UPDATE CASCADE,
87 CONSTRAINT `fees_fee_code_id_foreign` FOREIGN KEY (`fee_code_id`) REFERENCES `fee_codes` (`id`) ON UPDATE CASCADE
88) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
89
90INSERT INTO `fees` (`id`, `education_stage_id`, `fee_code_id`, `amount`, `created_at`, `updated_at`) VALUES
91(1, '2', 'BIAYA_MASUK', 6700000.0000, '2017-02-08 02:00:28', '2017-02-08 02:00:28'),
92(2, '2', 'BIAYA_PEMBANGUNAN', 700000.0000, '2017-02-08 02:02:35', '2017-02-08 02:02:35'),
93(3, '2', 'PENDAFTARAN', 300000.0000, '2017-02-08 02:02:47', '2017-02-08 02:02:47'),
94(4, '2', 'SERAGAM', 120000.0000, '2017-02-08 02:02:47', '2017-02-08 02:02:47'),
95(5, '1', 'BIAYA_MASUK', 20000000.0000, '2017-02-12 23:50:31', '2017-02-12 23:50:31'),
96(6, '1', 'BIAYA_PEMBANGUNAN', 6000000.0000, '2017-02-15 06:37:40', '2017-02-15 06:37:40'),
97(7, '1', 'PENDAFTARAN', 600000.0000, '2017-02-15 06:38:00', '2017-02-15 06:38:00'),
98(8, '1', 'SERAGAM', 250000.0000, '2017-02-15 06:38:19', '2017-02-15 06:38:19'),
99(9, '4', 'BIAYA_MASUK', 6000000.0000, '2017-03-07 02:41:38', '2017-03-07 02:41:38'),
100(10, '4', 'BIAYA_PEMBANGUNAN', 2000000.0000, '2017-03-07 02:48:08', '2017-03-07 02:48:08');
101
102DROP TABLE IF EXISTS `fee_codes`;
103CREATE TABLE `fee_codes` (
104 `id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
105 `description` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
106 `deleted_at` timestamp NULL DEFAULT NULL,
107 `created_at` timestamp NULL DEFAULT NULL,
108 `updated_at` timestamp NULL DEFAULT NULL,
109 PRIMARY KEY (`id`)
110) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
111
112INSERT INTO `fee_codes` (`id`, `description`, `deleted_at`, `created_at`, `updated_at`) VALUES
113('BIAYA_MASUK', 'Biaya masuk', NULL, '2017-02-08 08:56:27', '2017-02-08 08:56:27'),
114('BIAYA_PEMBANGUNAN', 'Biaya pembangunan', NULL, '2017-02-08 08:56:27', '2017-02-08 08:56:27'),
115('PENDAFTARAN', 'Biaya registrasi dan pendaftaran', NULL, '2017-02-08 08:55:53', '2017-02-08 08:55:53'),
116('SERAGAM', 'Seragam sekolah', NULL, '2017-02-08 08:54:24', '2017-02-08 08:54:24');
117
118DROP TABLE IF EXISTS `fee_code_spp_yearly_transaction`;
119CREATE TABLE `fee_code_spp_yearly_transaction` (
120 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
121 `spp_yearly_transaction_id` int(10) unsigned NOT NULL,
122 `fee_code_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
123 `created_at` timestamp NULL DEFAULT NULL,
124 `updated_at` timestamp NULL DEFAULT NULL,
125 PRIMARY KEY (`id`),
126 KEY `spp_yearly_transaction_id` (`spp_yearly_transaction_id`),
127 KEY `fee_code_id` (`fee_code_id`),
128 CONSTRAINT `fee_code_spp_yearly_transaction_ibfk_1` FOREIGN KEY (`spp_yearly_transaction_id`) REFERENCES `spp_yearly_transactions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
129 CONSTRAINT `fee_code_spp_yearly_transaction_ibfk_2` FOREIGN KEY (`fee_code_id`) REFERENCES `fee_codes` (`id`) ON UPDATE CASCADE
130) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
131
132INSERT INTO `fee_code_spp_yearly_transaction` (`id`, `spp_yearly_transaction_id`, `fee_code_id`, `created_at`, `updated_at`) VALUES
133(7, 5, 'BIAYA_MASUK', NULL, NULL),
134(8, 5, 'BIAYA_PEMBANGUNAN', NULL, NULL);
135
136DROP TABLE IF EXISTS `fee_transactions`;
137CREATE TABLE `fee_transactions` (
138 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
139 `fee_yearly_transaction_id` int(11) unsigned NOT NULL,
140 `amount` decimal(19,4) NOT NULL,
141 `paid_at` timestamp NULL DEFAULT NULL,
142 `pending_at` timestamp NULL DEFAULT NULL,
143 `created_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
144 `updated_at` timestamp NULL DEFAULT NULL,
145 PRIMARY KEY (`id`),
146 KEY `fee_yearly_transaction_id` (`fee_yearly_transaction_id`),
147 CONSTRAINT `fee_transactions_ibfk_2` FOREIGN KEY (`fee_yearly_transaction_id`) REFERENCES `fee_yearly_transactions` (`id`) ON DELETE CASCADE
148) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
149
150INSERT INTO `fee_transactions` (`id`, `fee_yearly_transaction_id`, `amount`, `paid_at`, `pending_at`, `created_at`, `updated_at`) VALUES
151(1, 39, 111.0000, '2017-03-13 01:11:10', '2017-03-13 07:20:21', '2017-03-13 08:11:10', '2017-03-13 01:11:10'),
152(2, 40, 222.0000, '2017-03-13 01:11:10', '2017-03-13 07:20:21', '2017-03-13 08:11:10', '2017-03-13 01:11:10'),
153(3, 39, 500000.0000, '2017-03-13 01:14:31', '2017-03-13 08:12:20', '2017-03-13 08:14:31', '2017-03-13 01:14:31'),
154(4, 40, 1000000.0000, '2017-03-13 01:14:31', '2017-03-13 08:12:20', '2017-03-13 08:14:31', '2017-03-13 01:14:31'),
155(5, 39, 500000.0000, '2017-03-13 01:15:03', '2017-03-13 01:14:51', '2017-03-13 08:15:03', '2017-03-13 01:15:03'),
156(6, 39, 2000000.0000, NULL, '2017-03-13 01:23:25', '2017-03-13 01:23:25', '2017-03-13 01:23:25');
157
158DROP TABLE IF EXISTS `fee_yearly_transactions`;
159CREATE TABLE `fee_yearly_transactions` (
160 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
161 `spp_yearly_transaction_id` int(11) unsigned NOT NULL,
162 `fee_code_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
163 `amount` decimal(19,4) NOT NULL,
164 `paid_at` timestamp NULL DEFAULT NULL,
165 `created_at` timestamp NULL DEFAULT NULL,
166 `updated_at` timestamp NULL DEFAULT NULL,
167 PRIMARY KEY (`id`),
168 KEY `fee_codes_id` (`fee_code_id`),
169 KEY `spp_yearly_transaction_id` (`spp_yearly_transaction_id`),
170 CONSTRAINT `fee_yearly_transactions_ibfk_2` FOREIGN KEY (`fee_code_id`) REFERENCES `fee_codes` (`id`),
171 CONSTRAINT `fee_yearly_transactions_ibfk_3` FOREIGN KEY (`spp_yearly_transaction_id`) REFERENCES `spp_yearly_transactions` (`id`) ON DELETE CASCADE
172) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=latin1;
173
174INSERT INTO `fee_yearly_transactions` (`id`, `spp_yearly_transaction_id`, `fee_code_id`, `amount`, `paid_at`, `created_at`, `updated_at`) VALUES
175(39, 5, 'BIAYA_MASUK', 5250000.0000, NULL, '2017-03-10 01:18:13', '2017-03-10 01:18:13'),
176(40, 5, 'BIAYA_PEMBANGUNAN', 1750000.0000, NULL, '2017-03-10 01:18:13', '2017-03-10 01:18:13');
177
178DROP TABLE IF EXISTS `generic_codes`;
179CREATE TABLE `generic_codes` (
180 `id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
181 `group` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
182 `description` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
183 PRIMARY KEY (`id`)
184) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
185
186INSERT INTO `generic_codes` (`id`, `group`, `description`) VALUES
187('BUDDHA', 'RELIGION', 'Buddha'),
188('HINDU', 'RELIGION', 'Hindu'),
189('ISLAM', 'RELIGION', 'Islam'),
190('KATOLIK', 'RELIGION', 'Katolik'),
191('KRISTEN', 'RELIGION', 'Kristen Protestan'),
192('PRIA', 'SEX', 'Pria'),
193('WANITA', 'SEX', 'Wanita');
194
195DROP TABLE IF EXISTS `grades`;
196CREATE TABLE `grades` (
197 `id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
198 `education_stage_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
199 `next_id` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
200 `created_at` timestamp NULL DEFAULT NULL,
201 `updated_at` timestamp NULL DEFAULT NULL,
202 PRIMARY KEY (`id`),
203 KEY `classes_next_id_foreign` (`next_id`),
204 KEY `grades_education_stage_id_foreign` (`education_stage_id`),
205 CONSTRAINT `grades_education_stage_id_foreign` FOREIGN KEY (`education_stage_id`) REFERENCES `education_stages` (`id`) ON UPDATE CASCADE
206) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
207
208INSERT INTO `grades` (`id`, `education_stage_id`, `next_id`, `created_at`, `updated_at`) VALUES
209('SD 1', '3', 'SD 2', NULL, NULL),
210('SD 2', '3', 'SD 3', NULL, NULL),
211('SD 3', '3', 'SD 4', NULL, NULL),
212('SD 4', '3', 'SD 5', NULL, NULL),
213('SD 5', '3', 'SD 6', NULL, NULL),
214('SD 6', '3', 'SMP 1', NULL, NULL),
215('SMA 1', '1', 'SMA 2', NULL, NULL),
216('SMA 2', '1', 'SMA 3', NULL, NULL),
217('SMA 3', '1', NULL, NULL, NULL),
218('SMP 1', '2', 'SMP 2', NULL, NULL),
219('SMP 2', '2', 'SMP 3', NULL, NULL),
220('SMP 3', '2', 'SMA 1', NULL, NULL),
221('TK A', '4', 'TK B', NULL, NULL),
222('TK B', '4', 'SD 1', NULL, NULL);
223
224DROP TABLE IF EXISTS `migrations`;
225CREATE TABLE `migrations` (
226 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
227 `migration` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
228 `batch` int(11) NOT NULL,
229 PRIMARY KEY (`id`)
230) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
231
232INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES
233(1, '2017_01_24_020638_create_generic_codes_table', 1),
234(2, '2017_01_24_155356_create_users_table', 1),
235(3, '2017_01_24_162950_create_user_groups_table', 1),
236(4, '2017_01_24_163556_create_user_groups_user_table', 1),
237(5, '2017_01_24_164718_create_pages_table', 1),
238(6, '2017_01_24_165101_create_page_user_group_table', 1),
239(7, '2017_01_24_165522_create_year_periods_table', 1),
240(8, '2017_01_24_165804_create_school_table', 1),
241(9, '2017_01_25_000000_create_education_stages_table', 1),
242(10, '2017_01_25_000001_create_class_table', 1),
243(11, '2017_01_25_000111_create_students_table', 1),
244(12, '2017_01_25_010212_create_fee_codes_table', 1),
245(13, '2017_01_25_014050_create_fees_table', 1),
246(14, '2017_01_25_050720_create_spp_terms_table', 1),
247(15, '2017_01_25_051413_create_student_transactions_table', 1),
248(16, '2017_01_25_075950_create_fee_code_student_transaction_table', 1),
249(17, '2017_01_25_142335_create_notifications_table', 2);
250
251DROP TABLE IF EXISTS `school`;
252CREATE TABLE `school` (
253 `code` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
254 `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
255 `address` varchar(256) COLLATE utf8_unicode_ci DEFAULT NULL,
256 `phone` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
257 `fax` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
258 `email` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
259 `website` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
260 `spp_date` date NOT NULL,
261 PRIMARY KEY (`code`)
262) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
263
264INSERT INTO `school` (`code`, `name`, `address`, `phone`, `fax`, `email`, `website`, `spp_date`) VALUES
265('MJ', 'Sekolah Maju Jaya', 'Jl. Anggur Segar XI Blok NC2 No. 12 Sunter', '082342424242', '0877346433255', 'majujaya@gmail.com', 'majujaya.com', '2017-03-01');
266
267DROP TABLE IF EXISTS `spp_monthly_transactions`;
268CREATE TABLE `spp_monthly_transactions` (
269 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
270 `spp_yearly_transaction_id` int(10) unsigned NOT NULL,
271 `month` int(10) unsigned NOT NULL,
272 `spp_amount` decimal(19,4) NOT NULL,
273 `is_generated` tinyint(4) DEFAULT NULL,
274 `paid_at` timestamp NULL DEFAULT NULL,
275 `pending_at` timestamp NULL DEFAULT NULL,
276 `deleted_at` timestamp NULL DEFAULT NULL,
277 `created_at` timestamp NULL DEFAULT NULL,
278 `updated_at` timestamp NULL DEFAULT NULL,
279 PRIMARY KEY (`id`),
280 KEY `spp_yearly_transaction_id` (`spp_yearly_transaction_id`),
281 CONSTRAINT `spp_monthly_transactions_ibfk_1` FOREIGN KEY (`spp_yearly_transaction_id`) REFERENCES `spp_yearly_transactions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
282) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=latin1;
283
284INSERT INTO `spp_monthly_transactions` (`id`, `spp_yearly_transaction_id`, `month`, `spp_amount`, `is_generated`, `paid_at`, `pending_at`, `deleted_at`, `created_at`, `updated_at`) VALUES
285(38, 5, 1, -82333.0000, 1, NULL, NULL, NULL, '2017-03-10 01:18:13', '2017-03-10 01:18:13'),
286(39, 5, 2, -82333.0000, 1, NULL, NULL, NULL, '2017-03-10 01:18:13', '2017-03-10 01:18:13'),
287(40, 5, 3, -82333.0000, 1, NULL, NULL, NULL, '2017-03-10 01:18:13', '2017-03-10 01:18:13'),
288(41, 5, 4, -82333.0000, 1, NULL, NULL, NULL, '2017-03-10 01:18:13', '2017-03-10 01:18:13'),
289(42, 5, 5, -82333.0000, 1, NULL, NULL, NULL, '2017-03-10 01:18:13', '2017-03-10 01:18:13'),
290(43, 5, 6, -82333.0000, 1, NULL, NULL, NULL, '2017-03-10 01:18:13', '2017-03-10 01:18:13'),
291(44, 5, 7, -82333.0000, 1, NULL, NULL, NULL, '2017-03-10 01:18:13', '2017-03-10 01:18:13'),
292(45, 5, 8, -82333.0000, 1, NULL, NULL, NULL, '2017-03-10 01:18:13', '2017-03-10 01:18:13'),
293(46, 5, 9, -82333.0000, 1, NULL, NULL, NULL, '2017-03-10 01:18:13', '2017-03-10 01:18:13'),
294(47, 5, 10, -82333.0000, 1, NULL, NULL, NULL, '2017-03-10 01:18:13', '2017-03-10 01:18:13'),
295(48, 5, 11, -82333.0000, 1, NULL, NULL, NULL, '2017-03-10 01:18:13', '2017-03-10 01:18:13'),
296(49, 5, 12, -82337.0000, 1, NULL, NULL, NULL, '2017-03-10 01:18:13', '2017-03-10 01:18:13');
297
298DROP TABLE IF EXISTS `spp_terms`;
299CREATE TABLE `spp_terms` (
300 `id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
301 `description` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
302 `education_stage_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
303 `months_term` int(11) NOT NULL DEFAULT '1',
304 `spp_amount` decimal(19,4) NOT NULL,
305 `spp_discount` decimal(19,4) NOT NULL,
306 `created_at` timestamp NULL DEFAULT NULL,
307 `updated_at` timestamp NULL DEFAULT NULL,
308 PRIMARY KEY (`id`),
309 KEY `spp_terms_education_stage_id_foreign` (`education_stage_id`),
310 CONSTRAINT `spp_terms_education_stage_id_foreign` FOREIGN KEY (`education_stage_id`) REFERENCES `education_stages` (`id`) ON UPDATE CASCADE
311) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
312
313INSERT INTO `spp_terms` (`id`, `description`, `education_stage_id`, `months_term`, `spp_amount`, `spp_discount`, `created_at`, `updated_at`) VALUES
314('SMA-STANDAR', 'Biaya standar SMA', '1', 1, 2000000.0000, 0.0000, '2017-02-12 23:15:36', '2017-02-12 23:15:36'),
315('SMP-STANDAR', 'SPP SMP Standar', '2', 1, 1600000.0000, 0.0000, '2017-02-08 01:39:28', '2017-02-08 01:39:28'),
316('SMP-TRIWULAN', 'SPP SMP Paket Triwulan', '2', 3, 1600000.0000, 100000.0000, '2017-02-08 01:43:14', '2017-02-08 01:43:14'),
317('SPPBULANANSD', 'SPP BULAN SD', '3', 1, 250000.0000, 0.0000, '2017-03-03 02:40:01', '2017-03-03 02:40:01'),
318('TK_BULANAN', 'TK Bulanan', '4', 1, 1000.0000, 0.0000, '2017-03-03 02:06:48', '2017-03-03 02:06:48');
319
320DROP TABLE IF EXISTS `spp_yearly_transactions`;
321CREATE TABLE `spp_yearly_transactions` (
322 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
323 `student_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
324 `year_period_id` int(10) unsigned NOT NULL,
325 `spp_term_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
326 `grade_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
327 `effective_date` date NOT NULL,
328 `is_installment` tinyint(4) NOT NULL DEFAULT '0',
329 `spp_scholarship` decimal(19,4) DEFAULT NULL,
330 `fee_scholarship` decimal(19,4) DEFAULT NULL,
331 `paid_at` timestamp NULL DEFAULT NULL,
332 `deleted_at` timestamp NULL DEFAULT NULL,
333 `created_at` timestamp NULL DEFAULT NULL,
334 `updated_at` timestamp NULL DEFAULT NULL,
335 PRIMARY KEY (`id`),
336 KEY `grade_id` (`grade_id`),
337 KEY `student_id` (`student_id`),
338 KEY `year_period_id` (`year_period_id`),
339 KEY `spp_term_id` (`spp_term_id`),
340 CONSTRAINT `spp_yearly_transactions_ibfk_1` FOREIGN KEY (`grade_id`) REFERENCES `grades` (`id`),
341 CONSTRAINT `spp_yearly_transactions_ibfk_2` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON UPDATE CASCADE,
342 CONSTRAINT `spp_yearly_transactions_ibfk_3` FOREIGN KEY (`year_period_id`) REFERENCES `year_periods` (`id`) ON UPDATE CASCADE,
343 CONSTRAINT `spp_yearly_transactions_ibfk_4` FOREIGN KEY (`spp_term_id`) REFERENCES `spp_terms` (`id`) ON UPDATE CASCADE
344) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
345
346INSERT INTO `spp_yearly_transactions` (`id`, `student_id`, `year_period_id`, `spp_term_id`, `grade_id`, `effective_date`, `is_installment`, `spp_scholarship`, `fee_scholarship`, `paid_at`, `deleted_at`, `created_at`, `updated_at`) VALUES
347(5, '00003', 1, 'TK_BULANAN', 'TK A', '2016-07-01', 0, 1000000.0000, 1000000.0000, NULL, NULL, '2017-03-10 01:18:13', '2017-03-10 01:18:13');
348
349DROP TABLE IF EXISTS `students`;
350CREATE TABLE `students` (
351 `id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
352 `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
353 `grade_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
354 `grade_detail` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
355 `birth_place` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
356 `birth_date` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
357 `sex_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
358 `religion_id` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
359 `address` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
360 `father_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
361 `mother_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
362 `email` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
363 `join_date` date NOT NULL,
364 `account_no` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
365 `account_name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
366 `balance` decimal(19,4) DEFAULT NULL,
367 `balance_last_checked` timestamp NULL DEFAULT NULL,
368 `created_at` timestamp NULL DEFAULT NULL,
369 `updated_at` timestamp NULL DEFAULT NULL,
370 `deleted_at` timestamp NULL DEFAULT NULL,
371 PRIMARY KEY (`id`),
372 KEY `students_religion_foreign` (`religion_id`),
373 KEY `students_sex_foreign` (`sex_id`),
374 KEY `grade_id` (`grade_id`),
375 CONSTRAINT `students_ibfk_2` FOREIGN KEY (`grade_id`) REFERENCES `grades` (`id`),
376 CONSTRAINT `students_religion_foreign` FOREIGN KEY (`religion_id`) REFERENCES `generic_codes` (`id`) ON UPDATE CASCADE,
377 CONSTRAINT `students_sex_foreign` FOREIGN KEY (`sex_id`) REFERENCES `generic_codes` (`id`) ON UPDATE CASCADE
378) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
379
380INSERT INTO `students` (`id`, `name`, `grade_id`, `grade_detail`, `birth_place`, `birth_date`, `sex_id`, `religion_id`, `address`, `father_name`, `mother_name`, `email`, `join_date`, `account_no`, `account_name`, `balance`, `balance_last_checked`, `created_at`, `updated_at`, `deleted_at`) VALUES
381('00003', 'Devata Udzubika', 'TK A', '1', 'Tangerang', '1905-06-12 00:00:00', 'PRIA', 'BUDDHA', 'jl.asal 12', 'Gautama Udzubika', 'Lusiana Kirti', 'email@gmail.com', '1905-07-09', '12345', 'JOKOWI', 4950000.0000, '2017-03-13 01:23:27', '2017-03-06 02:41:10', '2017-03-13 01:23:27', NULL),
382('00004', 'Dewi Citra Shanti', 'TK A', '1', 'Tangerang', '1905-06-12 00:00:00', 'PRIA', 'BUDDHA', 'jl.asal 13', 'Dion Horj', 'Meyliana Novita Sari', 'email@gmail.com', '1905-07-09', '12345', NULL, 4950000.0000, '2017-03-06 02:41:11', '2017-03-06 02:41:10', '2017-03-06 02:41:11', NULL);
383
384DROP TABLE IF EXISTS `users`;
385CREATE TABLE `users` (
386 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
387 `username` varchar(24) COLLATE utf8_unicode_ci NOT NULL,
388 `email` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
389 `password` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
390 `user_group_id` int(10) unsigned DEFAULT NULL,
391 `remember_token` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
392 `created_at` timestamp NULL DEFAULT NULL,
393 `updated_at` timestamp NULL DEFAULT NULL,
394 `deleted_at` timestamp NULL DEFAULT NULL,
395 PRIMARY KEY (`id`),
396 KEY `users_user_group_id_foreign` (`user_group_id`),
397 CONSTRAINT `users_user_group_id_foreign` FOREIGN KEY (`user_group_id`) REFERENCES `user_groups` (`id`) ON UPDATE CASCADE
398) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
399
400INSERT INTO `users` (`id`, `username`, `email`, `password`, `user_group_id`, `remember_token`, `created_at`, `updated_at`, `deleted_at`) VALUES
401(11, 'master', 'furibito@gmail.com', '$2y$10$s.2lAxFZsp1ocv86vHv9cuXRY7.9Ozps5hXXYmPUFEcSdgtS1EzES', 1, 'ptb2nxCNxScBpIJL5opZlUsTjaFHYFJl46AuAkp5uqvun3jIgMHbXliHFDhr', '2017-01-27 22:24:34', '2017-02-02 07:13:50', NULL),
402(13, 'admin', 'sdf@a.com', '$2y$10$f6pAuXnra1FEw7EgUMgcN.3iFyC6NmJzd0shUYLz.wl6yKTzpsnYK', 1, 'gx4AGlFEUES2uK5Q73VJSP7CLhYTTFhednN9NIjcAaA8cKBQwKWUo6GNxXpQ', '2017-02-04 21:34:37', '2017-03-05 20:41:15', NULL),
403(14, 'gregory', 'sfasd@a.com', '$2y$10$jvrpjD68.Se5CZfORyhM5ueyBuhRjAfP4or4eOJUx3DcSPPSOJAJ.', 2, NULL, '2017-02-05 01:05:12', '2017-02-05 01:05:12', NULL);
404
405DROP TABLE IF EXISTS `user_groups`;
406CREATE TABLE `user_groups` (
407 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
408 `name` varchar(24) COLLATE utf8_unicode_ci NOT NULL,
409 `created_at` timestamp NULL DEFAULT NULL,
410 `updated_at` timestamp NULL DEFAULT NULL,
411 PRIMARY KEY (`id`)
412) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
413
414INSERT INTO `user_groups` (`id`, `name`, `created_at`, `updated_at`) VALUES
415(1, 'Master Group', '0000-00-00 00:00:00', '2017-02-04 21:22:52'),
416(2, 'Akuntan', '2017-02-04 20:54:53', '2017-02-04 20:54:53');
417
418DROP TABLE IF EXISTS `user_logs`;
419CREATE TABLE `user_logs` (
420 `id` int(11) NOT NULL AUTO_INCREMENT,
421 `user_id` int(10) unsigned NOT NULL,
422 `action` varchar(256) DEFAULT NULL,
423 PRIMARY KEY (`id`),
424 KEY `user_id` (`user_id`),
425 CONSTRAINT `user_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
426) ENGINE=InnoDB DEFAULT CHARSET=latin1;
427
428
429DROP TABLE IF EXISTS `user_notifications`;
430CREATE TABLE `user_notifications` (
431 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
432 `user_id` int(10) unsigned NOT NULL,
433 `importance` int(10) unsigned NOT NULL,
434 `summary` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
435 `detail` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
436 `seen` tinyint(4) NOT NULL DEFAULT '0',
437 `created_at` timestamp NULL DEFAULT NULL,
438 `updated_at` timestamp NULL DEFAULT NULL,
439 PRIMARY KEY (`id`),
440 KEY `notifications_user_id_foreign` (`user_id`),
441 CONSTRAINT `notifications_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
442) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
443
444
445DROP TABLE IF EXISTS `year_periods`;
446CREATE TABLE `year_periods` (
447 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
448 `start` date NOT NULL,
449 `end` date NOT NULL,
450 `active` tinyint(4) NOT NULL DEFAULT '0',
451 PRIMARY KEY (`id`)
452) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
453
454INSERT INTO `year_periods` (`id`, `start`, `end`, `active`) VALUES
455(1, '2016-07-01', '2017-06-01', 1),
456(2, '2017-07-01', '2018-06-01', 0);
457
458-- 2017-03-13 08:25:00