· 5 years ago · Mar 09, 2020, 04:59 PM
1CREATE DATABASE IF NOT EXISTS `bpls`;
2USE `bpls`;
3CREATE TABLE IF NOT EXISTS `businesses` (
4 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
5 `user_id` int(11) NOT NULL,
6 `name` varchar(255) NOT NULL,
7 `address` varchar(255) NOT NULL,
8 `has_permit` tinyint(1) NOT NULL DEFAULT 0,
9 PRIMARY KEY (`id`)
10);
11INSERT INTO `businesses` ( `user_id`, `name`, `address`, `has_permit`) VALUES
12 (3, 'Mekmek Store', 'Lico-an, Barotac Nuevo, Iloilo', 0);
13CREATE TABLE IF NOT EXISTS `expiry` (
14 `permit_id` int(11) NOT NULL,
15 `start_date` date NOT NULL,
16 `end_date` date NOT NULL,
17 PRIMARY KEY (`permit_id`)
18);
19CREATE TABLE IF NOT EXISTS `permit_requests` (
20 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
21 `user_id` int(10) unsigned NOT NULL,
22 `business_id` int(10) unsigned NOT NULL,
23 `type` varchar(255) NOT NULL COMMENT 'New, Renewal',
24 `date` date NOT NULL DEFAULT curdate(),
25 `approved` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'True, False, Expired',
26 `business_account_number` varchar(255) NOT NULL,
27 `name_of_taxpayers` varchar(255) NOT NULL,
28 `telephone_no` varchar(11) NOT NULL,
29 `capital` varchar(255) NOT NULL COMMENT 'Ex: 1,000 PHP',
30 `address` varchar(255) NOT NULL,
31 `barangay_no` varchar(255) NOT NULL,
32 `business_trade_name` varchar(255) NOT NULL,
33 `business_telephone_no` varchar(255) NOT NULL,
34 `fax_no` varchar(255) DEFAULT NULL,
35 `commercial_address` varchar(255) NOT NULL,
36 `street` varchar(255) DEFAULT NULL,
37 `barangay` varchar(255) NOT NULL,
38 `main_line_of_business` varchar(255) DEFAULT NULL,
39 `main_products_and_services` varchar(255) DEFAULT NULL,
40 `barangay_clearance` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT 'True, False',
41 `no_of_employees` int(10) unsigned NOT NULL DEFAULT 1,
42 `public_liability_insurance` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT 'True, False',
43 `issuing_company` varchar(50) NOT NULL,
44 `issuing_company_date` date NOT NULL,
45 `dti_reg_no` varchar(255) DEFAULT NULL,
46 `sec_reg_no` varchar(255) DEFAULT NULL,
47 `proof_of_ownership` varchar(255) DEFAULT NULL,
48 `owned` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT 'True, False',
49 `leased` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT 'True, False',
50 `ownership_type` varchar(50) NOT NULL COMMENT 'Single Propietorship, Partnership, Corporation',
51 `registered_name` varchar(255) NOT NULL,
52 `lessors_name` varchar(255) NOT NULL,
53 `real_property_tax_receipt_no` varchar(255) NOT NULL,
54 `rent_per_month` varchar(255) NOT NULL,
55 `period_date` date NOT NULL,
56 `area_in_sq_meter` varchar(255) NOT NULL,
57 `name_of_applicant` varchar(255) NOT NULL,
58 PRIMARY KEY (`id`),
59 UNIQUE KEY `business_id` (`business_id`)
60);
61INSERT INTO `permit_requests` ( `user_id`, `business_id`, `type`, `date`, `approved`, `business_account_number`, `name_of_taxpayers`, `telephone_no`, `capital`, `address`, `barangay_no`, `business_trade_name`, `business_telephone_no`, `fax_no`, `commercial_address`, `street`, `barangay`, `main_line_of_business`, `main_products_and_services`, `barangay_clearance`, `no_of_employees`, `public_liability_insurance`, `issuing_company`, `issuing_company_date`, `dti_reg_no`, `sec_reg_no`, `proof_of_ownership`, `owned`, `leased`, `ownership_type`, `registered_name`, `lessors_name`, `real_property_tax_receipt_no`, `rent_per_month`, `period_date`, `area_in_sq_meter`, `name_of_applicant`) VALUES
62 (3, 1, 'Renewal', '2020-03-06', 0, 'BUS-NO-200', 'Taxpayer Name', '100', '2000 php', 'Iloilo', 'BRGY 203', 'Makoi Storey', '200', '', 'Barotac Nuevo', NULL, 'Lico-an', 'Sell Shit', 'Get Shit', 1, 2, 1, 'Maven', '2020-03-05', 'DTI-REG-053', 'SEC-REG-125', 'None at All', 0, 1, 'Sole Propietorship', 'Registered Namuu', 'Lessor\'s Namuu', 'RPTRN-205', '500 dolyars', '2020-03-05', '201 sq m', 'Elian Braga');
63CREATE TABLE IF NOT EXISTS `users` (
64 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
65 `username` varchar(255) NOT NULL,
66 `password` varchar(255) NOT NULL,
67 `access_level` tinyint(3) unsigned NOT NULL DEFAULT 1 COMMENT '1 = Applicant, 2 = Admin',
68 PRIMARY KEY (`id`),
69 UNIQUE KEY `username` (`username`)
70);
71INSERT INTO `users` ( `username`, `password`, `access_level`) VALUES
72 ('admin', '$2y$10$pv5.fmzFOTpPLyT/1eVIC.91RJxI28IRs4IZ/zuIE70iA4Sf2mz/e', 2),
73 ('mekkyinblack', '$2y$10$1HgvdW9Nmn9dibIcS6qQbeG5OH2FQUIvArbyEdNOp057j6GB6MDri', 2),
74 ('elian', '$2y$10$tdLZ.Q2/8EyBl2EVIhRXxuR8jFt9V4Xnvggs4hsUMPuKcNhPQ9r3u', 1);
75CREATE TABLE IF NOT EXISTS `user_details` (
76 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
77 `user_id` int(10) unsigned NOT NULL,
78 `first_name` varchar(255) NOT NULL,
79 `last_name` varchar(255) NOT NULL,
80 `contact_number` char(11) NOT NULL,
81 `address` varchar(255) NOT NULL,
82 `email` varchar(255) DEFAULT NULL,
83 PRIMARY KEY (`id`)
84);
85INSERT INTO `user_details` (, `user_id`, `first_name`, `last_name`, `contact_number`, `address`, `email`) VALUES
86 ( 3, 'Elian', 'Braga', '09309488605', 'Lico-an, Barotac Nuevo, Iloilo', 'manlupigelian@gmail.com');