· 5 years ago · Sep 26, 2020, 02:54 PM
1DROP TABLE IF EXISTS `employees`;
2CREATE TABLE `employees`(
3 `id` INT PRIMARY KEY AUTO_INCREMENT,
4 `first_name` VARCHAR(50) NOT NULL,
5 `last_name` VARCHAR(50) NOT NULL,
6 `title` VARCHAR(50) NOT NULL,
7 `notes` TEXT(400)
8);
9
10DROP TABLE IF EXISTS `customers`;
11CREATE TABLE `customers`(
12 `account_number` VARCHAR(10) PRIMARY KEY,
13 `first_name` VARCHAR(50) NOT NULL,
14 `last_name` VARCHAR(50) NOT NULL,
15 `phone_number` INT NOT NULL,
16 `emergency_name` VARCHAR(50),
17 `emergency_number` INT,
18 `notes` TEXT(400)
19);
20
21DROP TABLE IF EXISTS `room_status`;
22CREATE TABLE `room_status`(
23 `room_status` VARCHAR(50) PRIMARY KEY,
24 `notes` TEXT(400)
25);
26
27DROP TABLE IF EXISTS `room_types`;
28CREATE TABLE `room_types`(
29 `room_type` VARCHAR(50) PRIMARY KEY,
30 `notes` TEXT(400)
31);
32
33DROP TABLE IF EXISTS `bed_types`;
34CREATE TABLE `bed_types`(
35 `bed_type` VARCHAR(50) PRIMARY KEY,
36 `notes` TEXT(400)
37);
38
39DROP TABLE IF EXISTS `rooms`;
40CREATE TABLE `rooms`(
41 `room_number` INT PRIMARY KEY,
42 `room_type` VARCHAR(50) NOT NULL,
43 `bed_type` VARCHAR(50) NOT NULL,
44 `rate` FLOAT(2),
45 `room_status` VARCHAR(50) NOT NULL,
46 `notes` TEXT(400)
47);
48
49/* payments (id, employee_id, payment_date, account_number, first_date_occupied, last_date_occupied, total_days, amount_charged, tax_rate, tax_amount, payment_total, notes) */
50
51DROP TABLE IF EXISTS `payments`;
52CREATE TABLE `payments`(
53 `id` INT PRIMARY KEY AUTO_INCREMENT,
54 `employee_id` INT NOT NULL,
55 `payment_date` DATE NOT NULL,
56 `account_number` VARCHAR(10) NOT NULL,
57 `first_date_occupied` DATE NOT NULL,
58 `last_date_occupied` DATE NOT NULL,
59 `total_days` INT NOT NULL,
60 `amount_charged` FLOAT(2) NOT NULL,
61 `tax_rate` FLOAT(2) NOT NULL,
62 `tax_amount` FLOAT(2) NOT NULL,
63 `payment_total` FLOAT(2) NOT NULL,
64 `notes` TEXT(400)
65);
66
67
68DROP TABLE IF EXISTS `occupancies`;
69CREATE TABLE `occupancies`(
70 `id` INT PRIMARY KEY AUTO_INCREMENT,
71 `employee_id` INT NOT NULL,
72 `date_occupied` DATE NOT NULL,
73 `account_number` VARCHAR(10) NOT NULL,
74 `room_number` INT NOT NULL,
75 `rate_applied` FLOAT(2),
76 `phone_charge` INT,
77 `notes` TEXT(400)
78);
79
80INSERT INTO `employees` (`first_name`, `last_name`, `title`, `notes`)
81VALUES
82('Ivancho', 'Slavkata', 'Senior', 'osdjhapioujsdhapi9uh'),
83('Slavcho', 'Ivanov', 'CEO', 'osdjhapioujsdhapi9uh'),
84('Kura', 'mi qnko', 'Senior', 'osdjhapioujsdhapi9uh');
85
86INSERT INTO `customers` (`account_number`, `first_name`, `last_name`, `phone_number`, `emergency_name`, `emergency_number`, `notes`)
87VALUES
88('0123456789', 'Ivancho', 'Slavkata', 0887714520, 'Slavkata', 0887714520, 'osdjhapioujsdhapi9uh'),
89('1234567890', 'Ivancho', 'Slavkata', 0887714520, 'Slavkata', 0887714520, 'osdjhapioujsdhapi9uh'),
90('2345678901', 'Ivancho', 'Slavkata', 0887714520, 'Slavkata', 0887714520, 'osdjhapioujsdhapi9uh');
91
92INSERT INTO `room_status` (`room_status`, `notes`)
93VALUES
94('free', 'osdjhapioujsdhapi9uh'),
95('occupied', 'osdjhapioujsdhapi9uh'),
96('cleaning', 'osdjhapioujsdhapi9uh');
97
98INSERT INTO `room_types` (`room_type`, `notes`)
99VALUES
100('Box', 'osdjhapioujsdhapi9uh'),
101('Apartment', 'osdjhapioujsdhapi9uh'),
102('House', 'osdjhapioujsdhapi9uh');
103
104INSERT INTO `bed_types` (`bed_type`, `notes`)
105VALUES
106('single', 'osdjhapioujsdhapi9uh'),
107('double', 'osdjhapioujsdhapi9uh'),
108('triple', 'osdjhapioujsdhapi9uh');
109
110INSERT INTO `rooms` (`room_number`, `room_type`, `bed_type`, `rate`, `room_status`, `notes`)
111VALUES
112(111, 'Box', 'single', 4.69, 'free', 'ne'),
113(222, 'Apartment', 'double', 4.69, 'occupied', 'ne'),
114(333, 'House', 'triple', 4.69, 'cleaning', 'ne');
115
116INSERT INTO `payments` (`employee_id`, `payment_date`, `account_number`, `first_date_occupied`, `last_date_occupied`
117, `total_days`, `amount_charged`, `tax_rate`, `tax_amount`, `payment_total`, `notes`)
118VALUES
119(1, '2020-08-15', '0123456789', '2020-08-16', '2020-08-24', 9, 25, 25, 12, 85, 'ipkjg'),
120(2, '2020-08-15', '0123456789', '2020-08-16', '2020-08-24', 9, 25, 25, 12, 85, 'ipkjg'),
121(3, '2020-08-15', '0123456789', '2020-08-16', '2020-08-24', 9, 25, 25, 12, 85, 'ipkjg');
122
123INSERT INTO `occupancies` (`employee_id`, `date_occupied`, `account_number`, `room_number`, `rate_applied`, `phone_charge`, `notes`)
124VALUES
125(1, '2020-08-16', '0123456789', 111, 25, 54, 'fa'),
126(2, '2020-08-16', '1234567890', 111, 25, 54, 'fa'),
127(3, '2020-08-16', '2345678901', 111, 25, 54, 'fa');