· 9 years ago · Jan 03, 2017, 12:24 AM
1CREATE TABLE `tour` (
2 `Fahrzeug` int(11) NOT NULL,
3 `Mitarbeiter` int(11) NOT NULL,
4 `Datum` date NOT NULL,
5 `Uhrzeit` time NOT NULL,
6 `TNr` int(11) NOT NULL AUTO_INCREMENT,
7 `Bewertung` int(11) DEFAULT NULL,
8 `Verweilzeit` int(11) DEFAULT NULL,
9 `BNr` int(11) DEFAULT NULL,
10 PRIMARY KEY (`TNr`),
11 KEY `tour_fahrzeug_FNr_fk` (`Fahrzeug`),
12 KEY `tour_mitarbeiter_PNr_fk` (`Mitarbeiter`),
13 KEY `tour_gebuchteleistung_BNr_fk` (`BNr`),
14 CONSTRAINT `tour_fahrzeug_FNr_fk` FOREIGN KEY (`Fahrzeug`) REFERENCES `fahrzeug` (`FNr`),
15 CONSTRAINT `tour_gebuchteleistung_BNr_fk` FOREIGN KEY (`BNr`) REFERENCES `gebuchteleistung` (`BNr`),
16 CONSTRAINT `tour_mitarbeiter_PNr_fk` FOREIGN KEY (`Mitarbeiter`) REFERENCES `mitarbeiter` (`PNr`)
17) ENGINE=InnoDB AUTO_INCREMENT=391 DEFAULT CHARSET=utf8
18
19CREATE PROCEDURE `planTours`(IN `datum` DATE)
20 BEGIN
21 DECLARE responsibleEmployee INT;
22 DECLARE iStreet VARCHAR(30);
23 DECLARE iPatient INT;
24 DECLARE isPatientFirstOneInThisStreet BOOL DEFAULT TRUE;
25 DECLARE driveTime INT DEFAULT 0;
26 DECLARE endTimeOfLastVisit TIME DEFAULT '8:00';
27 DECLARE iService INT;
28 DECLARE visitDuration INT;
29
30 -- Clean up in case something went wrong in a previous run
31 DROP TABLE IF EXISTS servicesToBePerformedAtThisPatient;
32 DROP TABLE IF EXISTS patientsToVisitInThisStreet;
33 DROP TABLE IF EXISTS streetsToVisit;
34 DROP TABLE IF EXISTS availableVehicles;
35 DROP TABLE IF EXISTS availableEmployees;
36 DROP TABLE IF EXISTS servicesToPlan;
37
38 CREATE TEMPORARY TABLE servicesToPlan (
39 BNr INT,
40 service INT,
41 patient INT
42 );
43
44 CREATE TEMPORARY TABLE availableEmployees (
45 PNr INT,
46 workload INT DEFAULT 0,
47 vehicle INT
48 );
49
50 CREATE TEMPORARY TABLE availableVehicles (
51 VehicleID INT
52 );
53 INSERT INTO availableVehicles (SELECT FNr
54 FROM fahrzeug);
55
56 CREATE TEMPORARY TABLE streetsToVisit (
57 street VARCHAR(30)
58 );
59
60 CREATE TEMPORARY TABLE patientsToVisitInThisStreet (
61 PNr INT
62 );
63
64 CREATE TEMPORARY TABLE servicesToBePerformedAtThisPatient (
65 BNr INT
66 );
67
68 -- get available employees
69 INSERT INTO availableEmployees (PNr, workload)
70 SELECT
71 M.PNr,
72 0
73 FROM mitarbeiter M
74 WHERE M.Abteilung = 'Pflege'
75 AND M.PNr NOT IN (SELECT U.PNr
76 FROM urlaubstag U
77 WHERE U.Datum = datum);
78
79 -- reset tours of this day
80 DELETE FROM tour
81 WHERE tour.Datum = datum;
82
83 -- Get all tours that need to be made
84 INSERT INTO servicesToPlan (BNr, service, patient)
85 SELECT
86 B.BNr,
87 B.Leistung,
88 B.Patient
89 FROM gebuchteleistung B, person P
90 WHERE B.Patient = P.PNr
91 AND datum BETWEEN B.Von AND B.Bis
92 ORDER BY Patient, P.strasse;
93
94 INSERT INTO streetsToVisit (street)
95 SELECT person.strasse
96 FROM servicesToPlan, person
97 WHERE servicesToPlan.patient = person.PNr;
98
99 -- Iterate streets that need to be visited
100 WHILE (SELECT COUNT(*)
101 FROM streetsToVisit) > 0 DO
102 -- get current street
103 SET iStreet = (SELECT *
104 FROM streetsToVisit
105 ORDER BY street
106 LIMIT 1);
107 DELETE FROM streetsToVisit
108 WHERE streetsToVisit.street = iStreet;
109
110 SET isPatientFirstOneInThisStreet = TRUE;
111
112 -- Calculate responsible employee
113 SET responsibleEmployee = (SELECT PNr
114 FROM availableEmployees
115 ORDER BY availableEmployees.workload
116 LIMIT 1);
117
118 -- Assign Vehicle if not yet assigned to employee
119 IF (SELECT availableEmployees.vehicle
120 FROM availableEmployees
121 WHERE availableEmployees.PNr = responsibleEmployee) IS NULL
122 THEN
123 UPDATE availableEmployees
124 SET availableEmployees.vehicle = (SELECT VehicleID
125 FROM availableVehicles
126 LIMIT 1)
127 WHERE PNr = responsibleEmployee;
128
129 -- Remove Vehicle from pool
130 DELETE FROM availableVehicles
131 WHERE VehicleID = (SELECT vehicle
132 FROM availableEmployees
133 WHERE PNr = responsibleEmployee);
134
135 -- If Pool is now empty, clear all remaining employees
136 IF (SELECT COUNT(*)
137 FROM availableVehicles) = 0
138 THEN
139 DELETE FROM availableEmployees
140 WHERE vehicle IS NULL;
141 END IF;
142 END IF;
143
144 -- Get all patients in the current street
145 INSERT INTO patientsToVisitInThisStreet (PNr)
146 SELECT DISTINCT U.patient
147 FROM servicesToPlan U, person
148 WHERE U.patient = person.PNr
149 AND person.strasse = iStreet;
150
151 -- Iterate patients
152 WHILE (SELECT COUNT(*)
153 FROM patientsToVisitInThisStreet) > 0 DO
154
155 -- get current patient and remove them from pool
156 SET iPatient = (SELECT PNr
157 FROM patientsToVisitInThisStreet
158 LIMIT 1);
159 DELETE FROM patientsToVisitInThisStreet
160 WHERE PNr = iPatient;
161
162 -- Calculate drive time
163 IF isPatientFirstOneInThisStreet
164 THEN
165 SET driveTime = (SELECT anfahrtzeit
166 FROM anfahrtzeiten
167 WHERE anfahrtzeiten.straße = iStreet
168 LIMIT 1);
169 SET isPatientFirstOneInThisStreet = FALSE;
170 ELSE
171 SET driveTime = 5; -- choose 5 as an estimate to switch patients in the same street
172 END IF;
173
174 -- get serives, that need to be performed at that patient
175 INSERT INTO servicesToBePerformedAtThisPatient (BNr)
176 SELECT BNr
177 FROM servicesToPlan
178 WHERE servicesToPlan.patient = iPatient;
179
180 -- Iterate services
181 WHILE (SELECT COUNT(*)
182 FROM servicesToBePerformedAtThisPatient) > 0 DO
183
184 -- get current service and remove from pool
185 SET iService = (SELECT BNr
186 FROM servicesToBePerformedAtThisPatient
187 LIMIT 1);
188 DELETE FROM servicesToBePerformedAtThisPatient
189 WHERE BNr = iService;
190
191 -- calculate end of the last visit of this employee
192 IF (SELECT COUNT(*)
193 FROM tour
194 WHERE tour.Datum = datum AND tour.Mitarbeiter = responsibleEmployee) > 0
195 THEN
196 SET visitDuration = (SELECT L.Dauer
197 FROM gebuchteleistung B, leistung L
198 WHERE B.BNr = (SELECT BNr
199 FROM tour
200 WHERE tour.Datum = datum AND tour.Mitarbeiter = responsibleEmployee
201 ORDER BY Uhrzeit DESC
202 LIMIT 1)
203 AND B.Leistung = L.LNr
204 LIMIT 1);
205
206 SET endTimeOfLastVisit = ADDTIME((SELECT tour.Uhrzeit
207 FROM tour
208 WHERE tour.Datum = datum AND tour.Mitarbeiter = responsibleEmployee
209 ORDER BY Uhrzeit DESC
210 LIMIT 1),
211 MAKETIME(FLOOR(visitDuration / 60), visitDuration % 60, 0));
212 ELSE
213 SET endTimeOfLastVisit = MAKETIME(8, 0, 0);
214 END IF;
215
216 -- Add tour to database
217 INSERT INTO tour (Fahrzeug, Mitarbeiter, Datum, Uhrzeit, BNr) VALUES (
218 (SELECT vehicle
219 FROM availableEmployees
220 WHERE PNr = responsibleEmployee
221 LIMIT 1),
222 responsibleEmployee,
223 datum,
224 ADDTIME(endTimeOfLastVisit, MAKETIME(FLOOR(driveTime / 60), driveTime % 60, 0)),
225 iService
226 );
227
228 UPDATE availableEmployees
229 SET workload = (workload + driveTime + (SELECT L.Dauer
230 FROM leistung L, gebuchteleistung B
231 WHERE L.LNr = B.Leistung AND B.BNr = iService
232 LIMIT 1))
233 WHERE PNr = responsibleEmployee;
234
235 SET driveTime = MAKETIME(0, 0,
236 0); -- set drive to 0 in case additional services need to be made at this exast patient
237 END WHILE;
238 TRUNCATE servicesToBePerformedAtThisPatient;
239
240 END WHILE;
241
242 TRUNCATE patientsToVisitInThisStreet;
243 END WHILE;
244
245 DROP TABLE IF EXISTS servicesToBePerformedAtThisPatient;
246 DROP TABLE IF EXISTS patientsToVisitInThisStreet;
247 DROP TABLE IF EXISTS streetsToVisit;
248 DROP TABLE IF EXISTS availableVehicles;
249 DROP TABLE IF EXISTS availableEmployees;
250 DROP TABLE IF EXISTS servicesToPlan;
251 END
252
253CREATE TABLE `gebuchteleistung` (
254 `BNr` int(3) NOT NULL AUTO_INCREMENT,
255 `Leistung` int(2) NOT NULL,
256 `Von` date NOT NULL,
257 `Bis` date NOT NULL,
258 `Buchungsdatum` date NOT NULL,
259 `Patient` int(3) NOT NULL,
260 PRIMARY KEY (`BNr`),
261 KEY `gebuchteleistung_leistung_LNr_fk` (`Leistung`),
262 KEY `gebuchteleistung_patient_PNr_fk` (`Patient`),
263 CONSTRAINT `gebuchteleistung_leistung_LNr_fk` FOREIGN KEY (`Leistung`) REFERENCES `leistung` (`LNr`),
264 CONSTRAINT `gebuchteleistung_patient_PNr_fk` FOREIGN KEY (`Patient`) REFERENCES `patient` (`PNr`)
265) ENGINE=InnoDB AUTO_INCREMENT=303 DEFAULT CHARSET=utf8
266
267CREATE TABLE `leistung` (
268 `LNr` int(2) NOT NULL AUTO_INCREMENT,
269 `Name` varchar(27) DEFAULT NULL,
270 `Dauer` int(11) DEFAULT NULL,
271 `Eigenanteil` decimal(2,2) DEFAULT NULL,
272 `Tagespreis` int(2) DEFAULT NULL,
273 PRIMARY KEY (`LNr`)
274) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8
275
276CREATE TABLE `patient` (
277 `PNr` int(3) NOT NULL,
278 `Krankenkasse` int(2) NOT NULL,
279 PRIMARY KEY (`PNr`),
280 KEY `patient_krankenkasse_KNr_fk` (`Krankenkasse`),
281 CONSTRAINT `patient_krankenkasse_KNr_fk` FOREIGN KEY (`Krankenkasse`) REFERENCES `krankenkasse` (`KNr`),
282 CONSTRAINT `patient_person_PNr_fk` FOREIGN KEY (`PNr`) REFERENCES `person` (`PNr`)
283) ENGINE=InnoDB DEFAULT CHARSET=utf8
284
285CREATE TABLE `person` (
286 `PNr` int(3) NOT NULL AUTO_INCREMENT,
287 `Vorname` varchar(11) NOT NULL,
288 `Nachname` varchar(10) NOT NULL,
289 `Telefonnummer` varchar(17) DEFAULT NULL,
290 `Email` varchar(33) DEFAULT NULL,
291 `strasse` varchar(30) DEFAULT NULL,
292 `hausnummer` int(11) DEFAULT NULL,
293 PRIMARY KEY (`PNr`)
294) ENGINE=InnoDB AUTO_INCREMENT=301 DEFAULT CHARSET=utf8
295
296CREATE TABLE `anfahrtzeiten` (
297 `straße` varchar(30) NOT NULL,
298 `anfahrtzeit` int(11) DEFAULT NULL,
299 PRIMARY KEY (`straße`)
300) ENGINE=InnoDB DEFAULT CHARSET=utf8
301
302CREATE TABLE `mitarbeiter` (
303 `PNr` int(1) NOT NULL,
304 `Stundenlohn` decimal(4,2) NOT NULL,
305 `Einstelldatum` date NOT NULL,
306 `Abteilung` varchar(10) NOT NULL,
307 PRIMARY KEY (`PNr`),
308 CONSTRAINT `mitarbeiter_person_PNr_fk` FOREIGN KEY (`PNr`) REFERENCES `person` (`PNr`)
309) ENGINE=InnoDB DEFAULT CHARSET=utf8
310
311CREATE TABLE `urlaubstag` (
312 `PNr` int(1) NOT NULL,
313 `Datum` varchar(10) NOT NULL,
314 PRIMARY KEY (`PNr`,`Datum`),
315 CONSTRAINT `urlaubstag_mitarbeiter_PNr_fk` FOREIGN KEY (`PNr`) REFERENCES `mitarbeiter` (`PNr`)
316) ENGINE=InnoDB DEFAULT CHARSET=utf8
317
318CREATE TABLE `fahrzeug` (
319 `FNr` int(1) NOT NULL AUTO_INCREMENT,
320 `Modell` varchar(10) DEFAULT NULL,
321 `Kennzeichen` varchar(10) DEFAULT NULL,
322 `Baujahr` int(4) DEFAULT NULL,
323 `Letzte Inspektion` int(4) DEFAULT NULL,
324 PRIMARY KEY (`FNr`)
325) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8