· 6 years ago · Jun 04, 2019, 02:34 PM
1using Npgsql;
2using System;
3using System.Collections.Generic;
4using System.Text;
5
6namespace DataBase
7{
8 class PostgresClient
9 {
10 public NpgsqlConnection connection { get; set; }
11
12 public PostgresClient()
13 {
14 connection = new NpgsqlConnection(
15 "Server=localhost;" +
16 "Port=5433;" +
17 "User Id=postgres;" +
18 "Password=admin;" +
19 "Database=airportSystem;"
20 );
21
22 }
23
24 public void CreateTables()
25 {
26 connection.Open();
27 using(var cmd = new NpgsqlCommand())
28 {
29 cmd.Connection = connection;
30
31 cmd.CommandText = "CREATE TABLE Admins (" +
32 "ID int NOT NULL," +
33 "FullName varchar(30) NOT NULL," +
34 "CONSTRAINT Admins_pk PRIMARY KEY(ID)" +
35 "); ";
36 cmd.ExecuteNonQuery();
37 cmd.CommandText = "CREATE TABLE Units (" +
38 "UnitName varchar(30) NOT NULL," +
39 "SubunitID int NOT NULL," +
40 "Admins_ID int NOT NULL," +
41 "CONSTRAINT Units_pk PRIMARY KEY(UnitName, SubunitID)," +
42 "CONSTRAINT Units_fk FOREIGN KEY(Admins_ID) REFERENCES Admins(ID)" +
43 "); ";
44 cmd.ExecuteNonQuery();
45 cmd.CommandText = "CREATE TABLE Workers ("+
46 "ID int NOT NULL," +
47 "FullName char(30) NOT NULL," +
48 "WorkExperience int NOT NULL," +
49 "Gender varchar(1) NOT NULL," +
50 "Age int NOT NULL," +
51 "NumberOfChilds int NOT NULL," +
52 "Salary money NOT NULL," +
53 "Units_UnitName varchar(30) NOT NULL," +
54 "Units_SubunitID int NOT NULL," +
55 "CONSTRAINT Workers_pk PRIMARY KEY(ID)," +
56 "CONSTRAINT Workers_fk FOREIGN KEY(Units_UnitName, Units_SubunitID) REFERENCES Units(UnitName, SubunitID)" +
57 ");";
58 cmd.ExecuteNonQuery();
59 cmd.CommandText = "CREATE TABLE HealthCheckup (" +
60 "Workers_ID int NOT NULL," +
61 "CheckupDate date NOT NULL," +
62 "CheckupResult boolean NOT NULL," +
63 "CONSTRAINT HealthCheckup_pk PRIMARY KEY(Workers_ID, CheckupDate)," +
64 "CONSTRAINT HealthCheckup_fk FOREIGN KEY(Workers_ID) REFERENCES Workers(ID)" +
65 "); ";
66 cmd.ExecuteNonQuery();
67 cmd.CommandText = "CREATE TABLE Planes (" +
68 "ID int NOT NULL," +
69 "InHangar boolean NOT NULL," +
70 "LastTimeArrived timestamp NOT NULL," +
71 "FlightsCounter int NOT NULL DEFAULT 0," +
72 "Age int NOT NULL DEFAULT 0," +
73 "SeatsAmount int NOT NULL," +
74 "Name varchar(20) NOT NULL," +
75 "CONSTRAINT Planes_pk PRIMARY KEY(ID)" +
76 "); ";
77 cmd.ExecuteNonQuery();
78 cmd.CommandText = "CREATE TABLE PlanesCheckup (" +
79 "Planes_ID int NOT NULL," +
80 "CheckupDate date NOT NULL," +
81 "CheckupResult boolean NOT NULL," +
82 "CONSTRAINT PlanesCheckup_pk PRIMARY KEY(Planes_ID, CheckupDate)," +
83 "CONSTRAINT PlanesCheckup_fk FOREIGN KEY(Planes_ID) REFERENCES Planes(ID)" +
84 "); ";
85 cmd.ExecuteNonQuery();
86 cmd.CommandText = "CREATE TABLE Routes (" +
87 "ID int NOT NULL," +
88 "StartPoint varchar(30) NOT NULL," +
89 "EndPoint varchar(30) NOT NULL," +
90 "FlightTime time NOT NULL," +
91 "FirstClassPrice money NOT NULL," +
92 "SecondClassPrice money NOT NULL," +
93 "CONSTRAINT Routes_pk PRIMARY KEY(ID)" +
94 "); ";
95 cmd.ExecuteNonQuery();
96 cmd.CommandText = "CREATE TABLE Flights (" +
97 "ID int NOT NULL," +
98 "FlightType varchar(30) NOT NULL," +
99 "TakeoffTime timestamp NOT NULL," +
100 "Planes_ID int NOT NULL," +
101 "Routes_ID int NOT NULL," +
102 "SoldSeatsAmount int NULL," +
103 "ReturnedSeatsAmount int NULL," +
104 "Status varchar(10) NOT NULL," +
105 "DelayReason varchar(50) NULL," +
106 "CONSTRAINT Flights_pk PRIMARY KEY(ID)," +
107 "CONSTRAINT Flights_fk1 FOREIGN KEY(Planes_ID) REFERENCES Planes(ID)," +
108 "CONSTRAINT Flights_fk2 FOREIGN KEY(Routes_ID) REFERENCES Routes(ID)" +
109 "); ";
110 cmd.ExecuteNonQuery();
111 cmd.CommandText = "CREATE TABLE FlightCrew (" +
112 "Flights_ID int NOT NULL," +
113 "Units_UnitName varchar(20) NOT NULL," +
114 "Units_SubunitID int NOT NULL," +
115 "CONSTRAINT FlightCrew_pk PRIMARY KEY(Flights_ID, Units_UnitName, Units_SubunitID)," +
116 "CONSTRAINT FlightCrew_fk1 FOREIGN KEY(Flights_ID) REFERENCES Flights(ID)," +
117 "CONSTRAINT FlightCrew_fk2 FOREIGN KEY(Units_UnitName, Units_SubunitID) REFERENCES Units(UnitName, SubunitID)" +
118 "); ";
119 cmd.ExecuteNonQuery();
120
121 }
122 connection.Close();
123 }
124
125 public void DropAllTables()
126 {
127 connection.Open();
128 using(var cmd = new NpgsqlCommand())
129 {
130 cmd.Connection = connection;
131 cmd.CommandText = "DROP TABLE IF EXISTS admins CASCADE";
132 cmd.ExecuteNonQuery();
133 cmd.CommandText = "DROP TABLE IF EXISTS flightcrew CASCADE";
134 cmd.ExecuteNonQuery();
135 cmd.CommandText = "DROP TABLE IF EXISTS flights CASCADE";
136 cmd.ExecuteNonQuery();
137 cmd.CommandText = "DROP TABLE IF EXISTS healthcheckup CASCADE";
138 cmd.ExecuteNonQuery();
139 cmd.CommandText = "DROP TABLE IF EXISTS planes CASCADE";
140 cmd.ExecuteNonQuery();
141 cmd.CommandText = "DROP TABLE IF EXISTS planescheckup CASCADE";
142 cmd.ExecuteNonQuery();
143 cmd.CommandText = "DROP TABLE IF EXISTS routes CASCADE";
144 cmd.ExecuteNonQuery();
145 cmd.CommandText = "DROP TABLE IF EXISTS units CASCADE";
146 cmd.ExecuteNonQuery();
147 cmd.CommandText = "DROP TABLE IF EXISTS workers CASCADE";
148 cmd.ExecuteNonQuery();
149 }
150 connection.Close();
151 }
152
153 public void FillTables()
154 {
155 connection.Open();
156 using(var cmd = new NpgsqlCommand())
157 {
158 cmd.Connection = connection;
159 cmd.CommandText = "INSERT INTO Admins (ID, Fullname)" +
160 "VALUES" +
161 "(1, 'Смирнов Михаил')," +
162 "(2, 'Иванов Андрей')," +
163 "(3, 'Кузнецова Ксения')," +
164 "(4, 'Соколова Евгения')," +
165 "(5, 'Новиков Иван')," +
166 "(6, 'Дьячков Павел')," +
167 "(7, 'Денисов Эдуард')," +
168 "(8, 'Титова Светлана');";
169 cmd.ExecuteNonQuery();
170 cmd.CommandText = "INSERT INTO Units (UnitName, SubunitId, Admins_ID)" +
171 "VALUES" +
172 "('Пилоты', 1, 1)," +
173 "('Пилоты', 2, 1)," +
174 "('Пилоты', 3, 1)," +
175 "('Диспетчеры', 1, 2)," +
176 "('Диспетчеры', 2, 2)," +
177 "('Диспетчеры', 3, 2)," +
178 "('Техники', 1, 3)," +
179 "('Техники', 2, 3)," +
180 "('Техники', 3, 3)," +
181 "('Кассиры', 1, 4)," +
182 "('Кассиры', 2, 4)," +
183 "('Кассиры', 3, 4)," +
184 "('Работники службы безопасности', 1, 5)," +
185 "('Работники службы безопасности', 2, 5)," +
186 "('Работники службы безопасности', 3, 5)," +
187 "('Работники справочной службы', 1, 6)," +
188 "('Работники справочной службы', 2, 6)," +
189 "('Работники справочной службы', 3, 6); ";
190 cmd.ExecuteNonQuery();
191 cmd.CommandText = "INSERT INTO Workers(ID, Fullname, WorkExperience, Gender, Age, NumberOfChilds, Salary, Units_UnitName, Units_SubunitID)" +
192 "VALUES" +
193 "(1, 'Пономаренко Фёдор', 5, 'М', 32, 0, 150000, 'Пилоты', 1)," +
194 "(2, 'Филиппов Григорий', 7, 'М', 35, 2, 150000, 'Пилоты', 1)," +
195 "(3, 'Горобчук Клим', 3, 'М', 29, 1, 120000, 'Пилоты', 1)," +
196 "(4, 'Ширяев Ян', 10, 'М', 37, 0, 150000, 'Пилоты', 2)," +
197 "(5, 'Федосеев Всеволод', 8, 'М', 35, 2, 150000, 'Пилоты', 2)," +
198 "(6, 'Рымар Евстахий', 13, 'М', 39, 3, 160000, 'Пилоты', 2)," +
199 "(7, 'Семочко Макар', 2, 'М', 28, 0, 120000, 'Пилоты', 3)," +
200 "(8, 'Колесников Феликс', 9, 'М', 35, 2, 150000, 'Пилоты', 3)," +
201 "(9, 'Карпов Юрий', 11, 'М', 40, 3, 160000, 'Пилоты', 3)," +
202 "(10, 'Гришина Любовь', 5, 'Ж', 32, 0, 80000, 'Диспетчеры', 1)," +
203 "(11, 'Соколова Диана', 7, 'Ж', 35, 2, 80000, 'Диспетчеры', 1)," +
204 "(12, 'Попова Таисия', 3, 'Ж', 29, 1, 70000, 'Диспетчеры', 1)," +
205 "(13, 'Воронцова Маргарита', 10, 'Ж', 37, 0, 80000, 'Диспетчеры', 2)," +
206 "(14, 'Фомичёва Софья', 8, 'Ж', 35, 2, 80000, 'Диспетчеры', 2)," +
207 "(15, 'Кравченко Рада', 13, 'Ж', 39, 3, 80000, 'Диспетчеры', 2)," +
208 "(16, 'Зыкова Инна', 2, 'Ж', 28, 0, 70000, 'Диспетчеры', 3)," +
209 "(17, 'Дьячкова Нонна', 9, 'Ж', 35, 2, 80000, 'Диспетчеры', 3)," +
210 "(18, 'Полищук Изабелла', 11, 'Ж', 40, 3, 80000, 'Диспетчеры', 3)," +
211 "(19, 'Терентьев Леонард', 5, 'М', 32, 0, 50000, 'Техники', 1)," +
212 "(20, 'Гурьев Ждан', 7, 'М', 35, 2, 50000, 'Техники', 1)," +
213 "(21, 'Молчанов Владимир', 3, 'М', 29, 1, 40000, 'Техники', 1)," +
214 "(22, 'Никитин Богдан', 10, 'М', 37, 0, 50000, 'Техники', 2)," +
215 "(23, 'Линник Устин', 4, 'М', 35, 2, 50000, 'Техники', 2)," +
216 "(24, 'Павлов Нестор', 13, 'М', 39, 3, 50000, 'Техники', 2)," +
217 "(25, 'Юдин Григорий', 2, 'М', 28, 0, 40000, 'Техники', 3)," +
218 "(26, 'Федоренко Казбек', 9, 'М', 35, 2, 50000, 'Техники', 3)," +
219 "(27, 'Молчанов Арсений', 11, 'М', 40, 3, 50000, 'Техники', 3)," +
220 "(28, 'Турова Зоя', 5, 'Ж', 32, 0, 30000, 'Кассиры', 1)," +
221 "(29, 'Одинцова Варвара', 7, 'Ж', 35, 2, 30000, 'Кассиры', 1)," +
222 "(30, 'Иванова Лидия', 1, 'Ж', 29, 1, 25000, 'Кассиры', 1)," +
223 "(31, 'Меркушева Флорентина', 16, 'Ж', 37, 0, 30000, 'Кассиры', 2)," +
224 "(32, 'Толочко Юна', 8, 'Ж', 35, 2, 30000, 'Кассиры', 2)," +
225 "(33, 'Барановска Юна', 13, 'Ж', 39, 3, 30000, 'Кассиры', 2)," +
226 "(34, 'Чикольба Доминика', 8, 'Ж', 28, 0, 25000, 'Кассиры', 3)," +
227 "(35, 'Наумова Елизавета', 2, 'Ж', 35, 2, 30000, 'Кассиры', 3)," +
228 "(36, 'Борисова Светлана', 11, 'Ж', 40, 3, 30000, 'Кассиры', 3)," +
229 "(37, 'Михеев Камиль', 5, 'М', 32, 0, 55000, 'Работники службы безопасности', 1)," +
230 "(38, 'Бобров Филипп', 7, 'М', 35, 2, 55000, 'Работники службы безопасности', 1)," +
231 "(39, 'Орлов Гордей', 1, 'М', 29, 1, 55000, 'Работники службы безопасности', 1)," +
232 "(40, 'Богданов Ефим', 16, 'М', 37, 0, 55000, 'Работники службы безопасности', 2)," +
233 "(41, 'Полищук Рафаил', 8, 'М', 35, 2, 55000, 'Работники службы безопасности', 2)," +
234 "(42, 'Туров Доминик', 13, 'М', 39, 3, 55000, 'Работники службы безопасности', 2)," +
235 "(43, 'Иванив Леон', 8, 'М', 28, 0, 55000, 'Работники службы безопасности', 3)," +
236 "(44, 'Дмитриев Альберт', 2, 'М', 35, 2, 55000, 'Работники службы безопасности', 3)," +
237 "(45, 'Кириленко Эрик', 11, 'М', 40, 3, 55000, 'Работники службы безопасности', 3)," +
238 "(46, 'Каськив Кристина', 5, 'Ж', 53, 4, 45000, 'Работники справочной службы', 1)," +
239 "(47, 'Савельева Октябрина', 7, 'Ж', 35, 2, 45000, 'Работники справочной службы', 1)," +
240 "(48, 'Князева Дарья', 1, 'Ж', 31, 1, 40000, 'Работники справочной службы', 1)," +
241 "(49, 'Тимофеева Инга', 16, 'Ж', 37, 7, 45000, 'Работники справочной службы', 2)," +
242 "(50, 'Агафонова Вероника', 8, 'Ж', 35, 2, 45000, 'Работники справочной службы', 2)," +
243 "(51, 'Князева Эрика', 13, 'Ж', 48, 3, 45000, 'Работники справочной службы', 2)," +
244 "(52, 'Бородай Янина', 8, 'Ж', 28, 0, 45000, 'Работники справочной службы', 3)," +
245 "(53, 'Лихачёва Василиса', 2, 'Ж', 35, 2, 40000, 'Работники справочной службы', 3)," +
246 "(54, 'Сидорова Анфиса', 11, 'Ж', 45, 3, 45000, 'Работники справочной службы', 3); ";
247 cmd.ExecuteNonQuery();
248 cmd.CommandText = "INSERT INTO HealthCheckup(Workers_ID, CheckupDate, CheckupResult)" +
249 "VALUES" +
250 "(1, '2015-06-1', true)," +
251 "(1, '2016-06-1', true)," +
252 "(1, '2017-06-1', true)," +
253 "(1, '2018-06-1', true)," +
254 "(1, '2019-06-1', true)," +
255 "(2, '2017-06-1', true)," +
256 "(2, '2018-06-1', true)," +
257 "(2, '2019-06-1', false)," +
258 "(3, '2018-06-1', true)," +
259 "(3, '2019-06-1', true)," +
260 "(4, '2016-06-1', true)," +
261 "(4, '2017-06-1', true)," +
262 "(4, '2018-06-1', true)," +
263 "(4, '2019-06-1', false)," +
264 "(5, '2015-06-1', true)," +
265 "(5, '2016-06-1', false)," +
266 "(5, '2017-06-1', true)," +
267 "(5, '2018-06-1', true)," +
268 "(5, '2019-06-1', true)," +
269 "(6, '2015-06-1', true)," +
270 "(6, '2016-06-1', true)," +
271 "(6, '2017-06-1', false)," +
272 "(6, '2018-06-1', true)," +
273 "(6, '2019-06-1', false)," +
274 "(7, '2015-06-1', true)," +
275 "(7, '2016-06-1', true)," +
276 "(7, '2017-06-1', false)," +
277 "(7, '2018-06-1', true)," +
278 "(7, '2019-06-1', false)," +
279 "(8, '2015-06-1', true)," +
280 "(8, '2016-06-1', true)," +
281 "(8, '2017-06-1', true)," +
282 "(8, '2018-06-1', true)," +
283 "(8, '2019-06-1', true)," +
284 "(9, '2017-06-1', true)," +
285 "(9, '2018-06-1', false)," +
286 "(9, '2019-06-1', true); ";
287 cmd.ExecuteNonQuery();
288 cmd.CommandText = "INSERT INTO Planes(ID, InHangar, LastTimeArrived, FlightsCounter, Age, SeatsAmount, Name)" +
289 "VALUES" +
290 "(1, true, '2019-06-01 21:37:00', 180, 12, 180, 'Airbus A220')," +
291 "(2, true, '2019-06-01 19:23:00', 110, 23, 160, 'Airbus A300')," +
292 "(3, false, '2019-05-31 12:23:00', 431, 37, 190, 'Airbus A310')," +
293 "(4, true, '2019-06-02 01:11:00', 214, 26, 185, 'Boeing 314')," +
294 "(5, false, '2019-06-01 20:11:00', 314, 19, 140, 'Boeing 707')," +
295 "(6, true, '2019-05-30 11:12:00', 84, 7, 150, 'Boeing 787'); ";
296 cmd.ExecuteNonQuery();
297 cmd.CommandText = "INSERT INTO PlanesCheckup(Planes_ID, CheckupDate, CheckupResult)" +
298 "VALUES" +
299 "(1, '2019-05-28', true)," +
300 "(1, '2019-05-29', true)," +
301 "(1, '2019-05-30', true)," +
302 "(1, '2019-05-31', true)," +
303 "(1, '2019-06-1', true)," +
304 "(2, '2019-05-30', true)," +
305 "(2, '2019-05-31', true)," +
306 "(2, '2019-06-1', false)," +
307 "(3, '2019-05-31', true)," +
308 "(3, '2019-06-1', true)," +
309 "(4, '2019-05-29', true)," +
310 "(4, '2019-05-30', true)," +
311 "(4, '2019-05-31', true)," +
312 "(4, '2019-06-1', false)," +
313 "(5, '2019-05-28', true)," +
314 "(5, '2019-05-29', false)," +
315 "(5, '2019-05-30', true)," +
316 "(5, '2019-05-31', true)," +
317 "(5, '2019-06-1', true)," +
318 "(6, '2019-05-28', true)," +
319 "(6, '2019-05-29', true)," +
320 "(6, '2019-05-30', false)," +
321 "(6, '2019-05-31', true)," +
322 "(6, '2019-06-1', false);";
323 cmd.ExecuteNonQuery();
324 cmd.CommandText = "INSERT INTO Routes(ID, StartPoint, EndPoint, FlightTime, FirstClassPrice, SecondClassPrice)" +
325 "VALUES" +
326 "(1, 'Новосибирск', 'Москва', '04:05', 10000, 15000)," +
327 "(2, 'Новосибирск', 'Санкт-Петербург', '06:05', 15000, 30000)," +
328 "(3, 'Новосибирск', 'Грозный', '07:25', 12000, 19000)," +
329 "(4, 'Новосибирск', 'Владивосток', '5:15', 15000, 20000)," +
330 "(5, 'Новосибирск', 'Таллин', '6:15', 18000, 25000)," +
331 "(6, 'Новосибирск', 'Рим', '7:35', 23000, 29000)," +
332 "(7, 'Новосибирск', 'Лондон', '6:35', 25000, 34000)," +
333 "(8, 'Новосибирск', 'Париж', '6:25', 23000, 32000)," +
334 "(9, 'Новосибирск', 'Красноярск', '1:15', 5000, 7000);";
335 cmd.ExecuteNonQuery();
336 cmd.CommandText = "INSERT INTO Flights(ID, FlightType, TakeoffTime, Planes_ID, Routes_ID, SoldSeatsAmount, ReturnedSeatsAmount, Status, DelayReason)" +
337 "VALUES" +
338 "(1, 'Внутренний', '2019-06-04 18:35:00', 1, 1, 150, 0, 'В ожидании', null)," +
339 "(2, 'Внутренний', '2019-06-04 19:25:00', 2, 2, 100, 0, 'В ожидании', null)," +
340 "(3, 'Специальный', '2019-06-04 20:00:00', 3, 3, null, null, 'В ожидании', null)," +
341 "(4, 'Грузовой', '2019-06-04 21:00:00', 4, 4, null, null, 'В ожидании', null)," +
342 "(5, 'Международный', '2019-06-04 22:00:00', 5, 5, 45, 65, 'Задержан', 'Погодные условия')," +
343 "(6, 'Международный', '2019-06-04 22:00:00', 6, 6, 140, 0, 'В ожидании', null); ";
344 cmd.ExecuteNonQuery();
345 cmd.CommandText = "INSERT INTO FlightCrew(Flights_ID, Units_UnitName, Units_SubunitID)" +
346 "VALUES" +
347 "(1, 'Пилоты', 1)," +
348 "(2, 'Пилоты', 2)," +
349 "(3, 'Пилоты', 3)," +
350 "(4, 'Техники', 1)," +
351 "(5, 'Техники', 2)," +
352 "(6, 'Техники', 3); ";
353 cmd.ExecuteNonQuery();
354 }
355 connection.Close();
356 }
357 }
358}