API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Mar 13th, 2017
123
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 25.00 KB | None | 0 0
raw download clone embed print report
  1. -- Adminer 4.2.5 MySQL dump
  2.  
  3. SET NAMES utf8;
  4. SET time_zone = '+00:00';
  5. SET foreign_key_checks = 0;
  6. SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
  7.  
  8. DROP TABLE IF EXISTS `accesses`;
  9. CREATE 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.  
  16. INSERT 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.  
  27. DROP TABLE IF EXISTS `access_user_group`;
  28. CREATE 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.  
  41. INSERT 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.  
  59. DROP TABLE IF EXISTS `education_stages`;
  60. CREATE 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.  
  69. INSERT INTO `education_stages` (`id`, `description`, `next`) VALUES
  70. ('1',   'SMA',  NULL),
  71. ('2',   'SMP',  '1'),
  72. ('3',   'SD',   '2'),
  73. ('4',   'TK',   '3');
  74.  
  75. DROP TABLE IF EXISTS `fees`;
  76. CREATE 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.  
  90. INSERT 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.  
  102. DROP TABLE IF EXISTS `fee_codes`;
  103. CREATE 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.  
  112. INSERT 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.  
  118. DROP TABLE IF EXISTS `fee_code_spp_yearly_transaction`;
  119. CREATE 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.  
  132. INSERT 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.  
  136. DROP TABLE IF EXISTS `fee_transactions`;
  137. CREATE 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.  
  150. INSERT 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.  
  158. DROP TABLE IF EXISTS `fee_yearly_transactions`;
  159. CREATE 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.  
  174. INSERT 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.  
  178. DROP TABLE IF EXISTS `generic_codes`;
  179. CREATE 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.  
  186. INSERT 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.  
  195. DROP TABLE IF EXISTS `grades`;
  196. CREATE 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.  
  208. INSERT 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.  
  224. DROP TABLE IF EXISTS `migrations`;
  225. CREATE 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.  
  232. INSERT 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.  
  251. DROP TABLE IF EXISTS `school`;
  252. CREATE 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.  
  264. INSERT 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.  
  267. DROP TABLE IF EXISTS `spp_monthly_transactions`;
  268. CREATE 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.  
  284. INSERT 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.  
  298. DROP TABLE IF EXISTS `spp_terms`;
  299. CREATE 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.  
  313. INSERT 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.  
  320. DROP TABLE IF EXISTS `spp_yearly_transactions`;
  321. CREATE 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.  
  346. INSERT 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.  
  349. DROP TABLE IF EXISTS `students`;
  350. CREATE 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.  
  380. INSERT 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.  
  384. DROP TABLE IF EXISTS `users`;
  385. CREATE 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.  
  400. INSERT 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.  
  405. DROP TABLE IF EXISTS `user_groups`;
  406. CREATE 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.  
  414. INSERT 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.  
  418. DROP TABLE IF EXISTS `user_logs`;
  419. CREATE 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.  
  429. DROP TABLE IF EXISTS `user_notifications`;
  430. CREATE 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.  
  445. DROP TABLE IF EXISTS `year_periods`;
  446. CREATE 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.  
  454. INSERT 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!