-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathTaller2.txt
405 lines (299 loc) · 20.6 KB
/
Taller2.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
---- SE CREA UNA TABLA QUE ALMACENA LOS ESTADOS DE LOS AVIONES
CREATE TABLE STATE_AIRPLANE(ID NUMBER GENERATED ALWAYS AS IDENTITY, STATE VARCHAR2(100) NOT NULL);
ALTER TABLE STATE_AIRPLANE ADD CONSTRAINT PK_STATE_AIRPLANE
PRIMARY KEY (ID);
--- SE AGREGA UNA NUEVA COLUMNA A LOS AVIONES PARA SOLUCIONAR EL LA NUEVA IMPLEMENTACION DE LA LOGICA QUE SE CAMBIO SEGUN NUMERAL A
ALTER TABLE ENROLLMENT ADD STATE_AIRPLANE_ID INT NULL;
--- SE AGREGA UNA NUEVA COLUMNA A LOS AVIONES PARA SOLUCIONAR EL LA NUEVA IMPLEMENTACION DE LA LOGICA QUE SE CAMBIO SEGUN NUMERAL B
ALTER TABLE ENROLLMENT ADD AIRPORT_ID INT NULL;
------- SE AGREGA UNA NUEVA COLUMNA A LOS AVIONES PARA SOLUCIONAR EL LA NUEVA IMPLEMENTACION DE LA LOGICA QUE SE CAMBIO SEGUN NUMERAL C
CREATE TABLE STATE_AIRPLANE_CONFIRMATION(ID NUMBER GENERATED ALWAYS AS IDENTITY, STATE VARCHAR2(100) NOT NULL);
ALTER TABLE STATE_AIRPLANE_CONFIRMATION ADD CONSTRAINT PK_STATE_AIRPLANE_CONFIRMATION
PRIMARY KEY (ID);
ALTER TABLE CONFIRMED_FLIGHTS ADD STATE_AIRPLANE_CONFIRMATION_ID INT NULL;
--- SOLUCION PUNTO 1
CREATE OR REPLACE VIEW AIRPLANE_AIRPORT_DEPARTURE
AS
SELECT EN.REGISTRATION, EN.SERIAL_NUMBER, CF.ID CONFIRMATION_ID, AR.AIRPORT_ORIGIN_ID, EN.CHAIRS, DURATION_REAL, TIME_ESTIMATE_DEPARTURE
FROM CONFIRMED_FLIGHTS CF INNER JOIN ROUTE_ITINERARY RI ON CF.ID = RI.CONFIRMED_FLIGHTS_ID
INNER JOIN AIRPORT_ROUTES AR ON RI.AIRPORT_ROUTES_ID = AR.ID
INNER JOIN ENROLLMENT EN ON AR.AIRPORT_ORIGIN_ID = EN.AIRPORT_ID
WHERE CF.STATE_AIRPLANE_CONFIRMATION_ID = 6 AND (TIME_ESTIMATE_DEPARTURE - interval '2' HOUR) > SYSDATE;
SOLUCION PUNTO 2
create or replace PROCEDURE Asignar_Tripulacion_Vuelo
AS
BEGIN
-- declarando variables necesarias en el procedimiento
DECLARE CONFIRMACION_ID AIRPLANE_AIRPORT_DEPARTURE.CONFIRMATION_ID%TYPE := 0;
PILOTO_ID EMPLOYEE.ID%TYPE := 0;
COPILOTO_ID EMPLOYEE.ID%TYPE:= 0;
CIUDAD_ID AIRPORT.CITY_ID%TYPE := 0;
NUMERO_VUELO AIRPLANE_AIRPORT_DEPARTURE.REGISTRATION%TYPE := NULL;
TOTAL_ASIENTOS AIRPLANE_AIRPORT_DEPARTURE.CHAIRS%TYPE := 0;
TOTAL_DURATION AIRPLANE_AIRPORT_DEPARTURE.DURATION_REAL%TYPE := 0;
FECHA_ESTIMADA_SALIDA AIRPLANE_AIRPORT_DEPARTURE.TIME_ESTIMATE_DEPARTURE%TYPE := NULL;
CONT_CAPA_853 NUMBER:= 0;
TYPE temp_table_type IS TABLE OF INFORMATION_EMPLOYEES%ROWTYPE
INDEX BY BINARY_INTEGER;
AUX_VUELO temp_table_type;
TOTAL_AAD INT := 0;
TOTAL_PILOTO INT := 0;
TOTAL_COPILOTO INT := 0;
TOTAL_TRIPU INT := 0;
BEGIN
-- se pretende garantizar que la vista retorne datos
SELECT COUNT(*) INTO TOTAL_AAD
FROM AIRPLANE_AIRPORT_DEPARTURE;
IF (TOTAL_AAD >0)
THEN
-- almacenamos en variables el primer registro de la ejecucion de la vista
SELECT NVL(CHAIRS,0),NVL(DURATION_REAL,0), NVL(CONFIRMATION_ID,0),NVL(TIME_ESTIMATE_DEPARTURE,SYSDATE), NVL(REGISTRATION,'') INTO TOTAL_ASIENTOS, TOTAL_DURATION, CONFIRMACION_ID,FECHA_ESTIMADA_SALIDA, NUMERO_VUELO
FROM AIRPLANE_AIRPORT_DEPARTURE
FETCH FIRST 1 ROWS ONLY;
DBMS_OUTPUT.PUT_LINE(TOTAL_ASIENTOS || TOTAL_DURATION || CONFIRMACION_ID );
--- se valida si la hora estimada de llegada es menor a 3 horas con respecto a la fecha actual.
--- son 5 pero la vista ya viene filtrada con 2 horas
IF ((FECHA_ESTIMADA_SALIDA - interval '3' HOUR) > SYSDATE OR FECHA_ESTIMADA_SALIDA = NULL)
THEN
--obteniendo la ciudad para posteriormente buscar el piloto
SELECT CITY_ID INTO CIUDAD_ID
FROM AIRPORT
WHERE ID = (SELECT AIRPORT_ORIGIN_ID
FROM AIRPLANE_AIRPORT_DEPARTURE
FETCH FIRST 1 ROWS ONLY);
DBMS_OUTPUT.PUT_LINE('ciudad');
DBMS_OUTPUT.PUT_LINE(CIUDAD_ID);
---ENCONTRANDO EL PILOTO A ASIGNAR
SELECT COUNT(*) INTO TOTAL_PILOTO
FROM INFORMATION_EMPLOYEES
WHERE TYPE = 'PILOTO' AND STATE = 'ACTIVO' AND REST_HOURS_LAS_FLIGHT >= 2
AND CITY_LOCATION_ID = CIUDAD_ID ;
IF (TOTAL_PILOTO> 0)
THEN
SELECT ID INTO PILOTO_ID
FROM INFORMATION_EMPLOYEES
WHERE TYPE = 'PILOTO' AND STATE = 'ACTIVO' AND REST_HOURS_LAS_FLIGHT >= 2
AND CITY_LOCATION_ID = CIUDAD_ID
FETCH FIRST 1 ROWS ONLY;
ELSE
DBMS_OUTPUT.PUT_LINE('No hay pilotos para asignar');
END IF;
DBMS_OUTPUT.PUT_LINE('piloto');
DBMS_OUTPUT.PUT_LINE(PILOTO_ID);
------encontrando copiloto
SELECT COUNT(*) INTO TOTAL_COPILOTO
FROM INFORMATION_EMPLOYEES
WHERE TYPE = 'COPILOTO' AND STATE = 'ACTIVO' AND REST_HOURS_LAS_FLIGHT >= 2
AND CITY_LOCATION_ID = CIUDAD_ID ;
IF (TOTAL_COPILOTO > 0)
THEN
SELECT ID INTO COPILOTO_ID
FROM INFORMATION_EMPLOYEES
WHERE TYPE = 'COPILOTO' AND STATE = 'ACTIVO' AND REST_HOURS_LAS_FLIGHT >= 2
AND CITY_LOCATION_ID = CIUDAD_ID
FETCH FIRST 1 ROWS ONLY;
ELSE
DBMS_OUTPUT.PUT_LINE('No hay copilotos para asignar');
END IF;
DBMS_OUTPUT.PUT_LINE('copiloto');
DBMS_OUTPUT.PUT_LINE(COPILOTO_ID);
------encontrando AUXILIADRES DE VUELO
SELECT COUNT(*) INTO TOTAL_TRIPU
FROM INFORMATION_EMPLOYEES
WHERE TYPE = 'AUXILIAR DE VUELO' AND STATE = 'ACTIVO' AND REST_HOURS_LAS_FLIGHT >= 2
AND CITY_LOCATION_ID = CIUDAD_ID ;
IF (TOTAL_TRIPU > 0)
THEN
DBMS_OUTPUT.PUT_LINE('INGRESO');
SELECT * BULK COLLECT INTO AUX_VUELO
FROM INFORMATION_EMPLOYEES
WHERE TYPE = 'AUXILIAR DE VUELO' AND STATE = 'ACTIVO' AND REST_HOURS_LAS_FLIGHT >= 2
AND CITY_LOCATION_ID = CIUDAD_ID ;
ELSE
DBMS_OUTPUT.PUT_LINE('No hay ni un solo auxiliar de vuelo para asignar');
END IF;
IF (TOTAL_PILOTO >0 OR TOTAL_COPILOTO >0 OR TOTAL_TRIPU > 0)
THEN
DBMS_OUTPUT.PUT_LINE('INGRESO dos');
IF (TOTAL_ASIENTOS >= 19 AND TOTAL_ASIENTOS < 50)
THEN
DBMS_OUTPUT.PUT_LINE('uno' );
FOR indx IN 1 .. 1
LOOP
INSERT INTO ASSIGNED_CREW (CONFIRMED_FLIGHTS_ID, CREW_ID) VALUES (1, AUX_VUELO(indx).ID) ;
END LOOP;
END IF;
IF (TOTAL_ASIENTOS >= 50 AND TOTAL_ASIENTOS <= 100)
THEN
DBMS_OUTPUT.PUT_LINE('dos' );
IF (AUX_VUELO.COUNT >= 2)
THEN
FOR indx IN 1 .. 2
LOOP
INSERT INTO ASSIGNED_CREW (CONFIRMED_FLIGHTS_ID, CREW_ID) VALUES (1, AUX_VUELO(indx).ID) ;
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('No hay suficiente tripulacion para el vuelo. Solo hay' || AUX_VUELO.COUNT);
END IF;
END IF;
IF (TOTAL_ASIENTOS > 100 AND TOTAL_ASIENTOS <= 180)
THEN
DBMS_OUTPUT.PUT_LINE('tres' );
IF (AUX_VUELO.COUNT >= 4)
THEN
FOR indx IN 1 .. 4
LOOP
INSERT INTO ASSIGNED_CREW (CONFIRMED_FLIGHTS_ID, CREW_ID) VALUES (1, AUX_VUELO(indx).ID) ;
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('No hay suficiente tripulacion para el vuelo. Solo hay' || AUX_VUELO.COUNT);
END IF;
END IF;
IF (TOTAL_ASIENTOS > 180 AND TOTAL_ASIENTOS <= 853)
THEN
DBMS_OUTPUT.PUT_LINE('cuatro' );
IF (TOTAL_DURATION >=6)
THEN
CONT_CAPA_853 := 19;
ELSE
CONT_CAPA_853 := 18;
END IF;
IF (AUX_VUELO.COUNT >= CONT_CAPA_853)
THEN
FOR indx IN 1 .. CONT_CAPA_853
LOOP
INSERT INTO ASSIGNED_CREW (CONFIRMED_FLIGHTS_ID, CREW_ID) VALUES (1, AUX_VUELO(indx).ID) ;
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('No hay suficiente tripulacion para el vuelo. Solo hay' || AUX_VUELO.COUNT);
END IF;
END IF;
UPDATE CONFIRMED_FLIGHTS
SET PILOT_ID = PILOTO_ID, COP_PILOT_ID = COPILOTO_ID, STATE_AIRPLANE_CONFIRMATION_ID = 4, ENROLLMENT_REGISTRATION = NUMERO_VUELO
WHERE ID = CONFIRMACION_ID;
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE('Cuando no hay al menos un piloto o copiloto o auxiliares de vuelo, no se puede asignar tripulacion');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Al vuelo le faltan menos de 5 horas para su salida. Ya no se puede asignar tripulacion');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('El vuelo no esta programado o le faltan menos de 5 horas.');
END IF;
END;
END;
--CREACION DE VISTA DENTRO DEL PROCEDIMIENTO ANTERIOR
CREATE OR REPLACE VIEW INFORMATION_EMPLOYEES
AS
SELECT EMP.ID, EMPT.TYPE, EMS.STATE,EMP.REST_HOURS_LAS_FLIGHT, EMP.CITY_LOCATION_ID
from EMPLOYEE EMP INNER JOIN EMPLOYEES_TYPE EMPT ON EMP.EMPLOYEE_TYPE_ID = EMPT."ID"
INNER JOIN EMPLOYEE_STATE EMS ON EMP.STATE_ID = EMS."ID"
--------SOLUCION PUNTO 3
create or replace PROCEDURE Realizar_RegistroConfirmacion_Pasajeros (ID_CONFIRMED_FLIGHTS NUMBER, ID_PASSANGER NUMBER, ID_TYPE_CHAIRS NUMBER)
AS
BEGIN
DECLARE TOTAL_ASIENTOS ENROLLMENT.CHAIRS%TYPE := 0;
TOTAL_PASAJEROS CONFIRMED_FLIGHTS_TYPE_CHAIRS.NUMBER_PASSANGERS%TYPE := 0;
TOTAL_REGISTRO NUMBER := 0;
VUELO_CONFIRMADO NUMBER := 0;
PASAJERO_CONFIRMADO NUMBER := 0;
BEGIN
-- garantizando que el vuelo este confirmado
SELECT COUNT(*) INTO VUELO_CONFIRMADO
FROM CONFIRMED_FLIGHTS
WHERE ID = ID_CONFIRMED_FLIGHTS and STATE_AIRPLANE_CONFIRMATION_ID = 4;
IF (VUELO_CONFIRMADO = 1)
THEN
SELECT COUNT(*) INTO PASAJERO_CONFIRMADO
FROM CHECK_IN
WHERE CONFIRMED_FLIGHT_ID = ID_CONFIRMED_FLIGHTS AND PASSANGER_ID = ID_PASSANGER AND TYPE_CHAIR_ID IS NOT NULL;
IF (PASAJERO_CONFIRMADO = 0)
THEN
SELECT CHAIRS INTO TOTAL_ASIENTOS
FROM ENROLLMENT EN INNER JOIN CONFIRMED_FLIGHTS CF ON EN.REGISTRATION = CF.ENROLLMENT_REGISTRATION
WHERE CF.ID = ID_CONFIRMED_FLIGHTS;
DBMS_OUTPUT.PUT_LINE(TOTAL_ASIENTOS);
SELECT NVL(SUM(NUMBER_PASSANGERS),0) INTO TOTAL_PASAJEROS
FROM CONFIRMED_FLIGHTS_TYPE_CHAIRS
WHERE CONFIRMED_FLIGHTS_ID = ID_CONFIRMED_FLIGHTS;
DBMS_OUTPUT.PUT_LINE(TOTAL_PASAJEROS);
---validando que el total de sillas del avion no sea inferior a la demanda del registro de pasajeros
IF (TOTAL_ASIENTOS >= TOTAL_PASAJEROS )
THEN
DBMS_OUTPUT.PUT_LINE('primero');
IF (TOTAL_PASAJEROS = 0)
THEN
DBMS_OUTPUT.PUT_LINE('segundo');
INSERT INTO CONFIRMED_FLIGHTS_TYPE_CHAIRS (CONFIRMED_FLIGHTS_ID,TYPE_CHAIRS_ID ,NUMBER_PASSANGERS )
VALUES (ID_CONFIRMED_FLIGHTS, ID_TYPE_CHAIRS, 1);
ELSE
DBMS_OUTPUT.PUT_LINE('tercero');
SELECT COUNT(*) INTO TOTAL_REGISTRO
FROM CONFIRMED_FLIGHTS_TYPE_CHAIRS
WHERE CONFIRMED_FLIGHTS_ID = ID_CONFIRMED_FLIGHTS AND TYPE_CHAIRS_ID = ID_TYPE_CHAIRS;
DBMS_OUTPUT.PUT_LINE(TOTAL_REGISTRO);
IF (TOTAL_REGISTRO > 0)
THEN
DBMS_OUTPUT.PUT_LINE('cuarto');
UPDATE CONFIRMED_FLIGHTS_TYPE_CHAIRS SET NUMBER_PASSANGERS = NUMBER_PASSANGERS + 1
WHERE CONFIRMED_FLIGHTS_ID = ID_CONFIRMED_FLIGHTS AND TYPE_CHAIRS_ID = ID_TYPE_CHAIRS;
ELSE
DBMS_OUTPUT.PUT_LINE('quinto');
INSERT INTO CONFIRMED_FLIGHTS_TYPE_CHAIRS (CONFIRMED_FLIGHTS_ID,TYPE_CHAIRS_ID ,NUMBER_PASSANGERS )
VALUES (ID_CONFIRMED_FLIGHTS, ID_TYPE_CHAIRS, 1);
END IF;
END IF;
UPDATE CHECK_IN
SET TYPE_CHAIR_ID = ID_TYPE_CHAIRS
WHERE PASSANGER_ID = ID_PASSANGER AND CONFIRMED_FLIGHT_ID = ID_CONFIRMED_FLIGHTS ;
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE('Ya no hay cupo en el avion. Esta lleno.');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Este pasajero ya fue confirmado.');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('El id de confirmacion no esta confirmado');
END IF ;
END;
END;
-------SOLUCION PUNTO 4
CREATE OR REPLACE VIEW PERSON_ASSIGNED_FLIGHT
AS
select CF.ID CONFIRMATION_ID,ENROLLMENT_REGISTRATION,EMP.NAME, EMP.LAST_NAME, EMPT.TYPE CARGO
from CONFIRMED_FLIGHTS CF INNER JOIN EMPLOYEE EMP ON CF.PILOT_ID = EMP.ID
INNER JOIN EMPLOYEES_TYPE EMPT ON EMP.EMPLOYEE_TYPE_ID = EMPT.ID
UNION ALL
select CF.ID CONFIRMATION_ID,ENROLLMENT_REGISTRATION,EMP.NAME, EMP.LAST_NAME, EMPT.TYPE
from CONFIRMED_FLIGHTS CF INNER JOIN EMPLOYEE EMP ON CF.COP_PILOT_ID = EMP.ID
INNER JOIN EMPLOYEES_TYPE EMPT ON EMP.EMPLOYEE_TYPE_ID = EMPT.ID
UNION ALL
select CF.ID CONFIRMATION_ID,ENROLLMENT_REGISTRATION,EMP.NAME, EMP.LAST_NAME, EMPT.TYPE
from CONFIRMED_FLIGHTS CF INNER JOIN ASSIGNED_CREW AC ON CF.ID = AC.CONFIRMED_FLIGHTS_ID
INNER JOIN EMPLOYEE EMP ON AC.CREW_ID = EMP.ID
INNER JOIN EMPLOYEES_TYPE EMPT ON EMP.EMPLOYEE_TYPE_ID = EMPT.ID
SOLUCION PUNTO 5
CREATE OR REPLACE VIEW SCHEDULED_FLIGHTS
AS
SELECT AI.NAME AIRPORT_ORIGIN, AI2.NAME AIRPORT_TARGET,CF.ENROLLMENT_REGISTRATION,TIME_ESTIMATE_DEPARTURE, TIME_NOW_DEPARTURE
FROM CONFIRMED_FLIGHTS CF INNER JOIN ROUTE_ITINERARY RI ON CF.ID = RI.CONFIRMED_FLIGHTS_ID
INNER JOIN AIRPORT_ROUTES AR ON RI.AIRPORT_ROUTES_ID = AR.ID
INNER JOIN AIRPORT AI ON AR.AIRPORT_ORIGIN_ID = AI.ID
INNER JOIN AIRPORT AI2 ON AR.AIRPORT_TARGET_ID = AI2.ID
WHERE (SYSDATE <= TIME_ESTIMATE_DEPARTURE) AND ((SYSDATE + interval '336' HOUR) > TIME_ESTIMATE_DEPARTURE)
PUNTO 6
EXPLAIN PLAN SET STATEMENT_ID = 'BAD1' FOR
SELECT * FROM AIRPLANE_AIRPORT_DEPARTURE ;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'BAD1','TYPICAL'));
EXPLAIN PLAN SET STATEMENT_ID = 'BAD2' FOR
SELECT * FROM PERSON_ASSIGNED_FLIGHT;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'BAD2','TYPICAL'));
EXPLAIN PLAN SET STATEMENT_ID = 'BAD3' FOR
SELECT * FROM SCHEDULED_FLIGHTS;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'BAD3','TYPICAL'));
--- PUNTO 7
El video fue cargado a dropbox y se compartio la carpeta a su correo electronico
----PUNTO 8
El video fue cargado a dropbox y se compartio la carpeta a su correo electronico