· 4 years ago · Jun 14, 2021, 03:10 PM
1DROP TABLE IF EXISTS addresses;
2DROP TABLE IF EXISTS users;
3DROP TABLE IF EXISTS cars;
4DROP TABLE IF EXISTS carsRental;
5DROP TABLE IF EXISTS logs;
6
7CREATE TABLE addresses(
8 addressId number not null,
9 city varchar2(50),
10 country varchar2(50),
11 street varchar2(50),
12 house_nr varchar2(5),
13 PRIMARY KEY(addressId)
14);
15
16
17CREATE TABLE users (
18 userId number not null,
19 username varchar2(25),
20 password varchar2(25),
21 acc_balance number,
22 PRIMARY KEY(userId)
23);
24
25CREATE TABLE cars(
26 carId number not null,
27 carVin varchar2(10),
28 carBrand varchar2(50),
29 carModel varchar2(50),
30 carColor varchar2(15),
31 carValue number,
32 PRIMARY KEY (carId)
33);
34
35
36CREATE TABLE carsRental(
37 rentalId number not null,
38 carId number not null,
39 userId number not null,
40 rentalDate date,
41 rentalEndDate date,
42 rentalValue number,
43 PRIMARY KEY (rentalId),
44 FOREIGN KEY(carId) REFERENCES cars(carId),
45 FOREIGN KEY(userId) REFERENCES users(userId)
46);
47CREATE TABLE UsersAddressesRelation(
48 userId number not null,
49 addressId number not null,
50 FOREIGN KEY (userId) REFERENCES users(userId),
51 FOREIGN KEY (addressId) REFERENCES addresses(addressId),
52 UNIQUE (userId, addressId)
53);
54CREATE TABLE logs(
55 logId number PRIMARY KEY,
56 userId number,
57 carId number,
58 rentalId number,
59 rentalDate date,
60 rentalEndDate date,
61 rentalValue number,
62 newUserAccBalance number,
63 oldUserAccBalance number,
64 rentalOperation varchar2(20)
65);
66insert into addresses values(1,'admin','admin','admin','1');
67insert into users values (1,'admin','admin',9999);
68insert into UsersAddressesRelation values(1,1);
69
70insert into cars values (1,'SWA123D1','BMW','M3','grey',150);
71insert into cars values (2,'SWA123D2','BMW','M5','black',150);
72insert into cars values (3,'SWA123D3','BMW','M8','red',500);
73insert into cars values (4,'SWA123D4','Audi','RS5','green',450);
74insert into cars values (5,'SWA123D5','Dogde','Challenger','blue',350);
75insert into cars values (6,'SWA123D6','Chevrolet','Camaro SS','yellow',300);
76insert into cars values (7,'SWA123D7','Ford','Mustang GT','white',300);
77-- ======= USER PACKAGE definition
78
79CREATE OR REPLACE PACKAGE projekt_packet AUTHID DEFINER AS
80logged boolean := false;
81saved_username varchar(50) := '';
82--notlogged person
83PROCEDURE register(u_city varchar2,u_country varchar2,u_street varchar2,u_house_nr varchar2, u_username varchar2, u_password varchar2);
84PROCEDURE login(u_username varchar2, u_password varchar2);
85PROCEDURE showAllCars;
86PROCEDURE checkAvailability (u_BeginDate date, u_EndDate date, u_carVin varchar2);
87-- user procedure
88PROCEDURE logout;
89PROCEDURE add_balance(u_acc_balance number);
90PROCEDURE addAddress(u_city varchar2,u_country varchar2,u_street varchar2,u_house_nr varchar2);
91PROCEDURE showProfile;
92PROCEDURE showHistory;
93PROCEDURE rentCar(u_carVin varchar2, u_BeginDate date, u_EndDate date, u_addressNumber number);
94-- admin procedure
95PROCEDURE carProfit (u_carVin varchar2, u_BeginDate date, u_EndDate date);
96PROCEDURE userProfit;
97PROCEDURE carHistory (u_carVin varchar2);
98PROCEDURE carProfit;
99PROCEDURE showAllCarsAdmin;
100PROCEDURE showLogsAdmin;
101PROCEDURE showAllUsersAdmin;
102PROCEDURE addCar(u_carVin varchar2,u_carBrand varchar2, u_carModel varchar2, u_carColor varchar2,u_carValue number);
103FUNCTION checkBalance(u_carId number, u_BeginDate date, u_EndDate date ) RETURN boolean;
104FUNCTION checkDate(u_BeginDate in date, u_EndDate in date, u_carVin in varchar2) RETURN number;
105FUNCTION checkCar (u_carVin varchar2) RETURN boolean;
106
107end projekt_packet;
108--- TEST RENTCARA BO MEGA ZMIENIONY
109-- ======= USER PACKAGE body
110CREATE OR REPLACE PACKAGE BODY projekt_packet AS
111
112-- ******************** USER PROCEDURES
113PROCEDURE register (u_city varchar2,u_country varchar2,u_street varchar2,u_house_nr varchar2, u_username varchar2, u_password varchar2) IS
114addressExist BOOLEAN;
115user_exist BOOLEAN;
116address_count Number;
117user_count Number;
118address_id number;
119user_already_exist EXCEPTION;
120BEGIN
121addressExist:=false;
122user_exist:=false;
123address_count:=0;
124user_count:=0;
125address_id:=0;
126 FOR address_record in (select * from addresses)
127 LOOP
128 if address_record.city = u_city and address_record.country = u_country and address_record.street = u_street and address_record.house_nr = u_house_nr then
129 addressExist:=true;
130 address_id:= address_record.addressId;
131 END IF;
132 address_count:=address_count+1;
133 END LOOP;
134 address_count:=address_count+1;
135 if addressExist = false then
136 insert into addresses values(address_count,u_city,u_country,u_street,u_house_nr);
137 address_id:=address_count;
138 else
139 update addresses set city = u_city, street = u_street, country = u_country, house_nr=u_house_nr where addressId = address_id;
140 end if;
141
142 FOR user_record in (select * from users) LOOP
143 if(user_record.username = u_username) then
144 user_exist:=true;
145 raise user_already_exist;
146 end if;
147 user_count:=user_count+1;
148 END LOOP;
149 user_count:= user_count+1;
150 if user_exist = false then
151 insert into users values (user_count,u_username,u_password,0);
152 insert into UsersAddressesRelation values (user_count,address_id);
153 DBMS_OUTPUT.PUT_LINE('User succesfuly created');
154 end if;
155 EXCEPTION
156 when user_already_exist then
157 DBMS_OUTPUT.PUT_LINE('User already exists, try other username');
158END register;
159
160
161PROCEDURE login (u_username varchar2, u_password varchar2)
162IS
163user_exist BOOLEAN;
164uAreLogged exception;
165BEGIN
166 if logged = false then
167 for user_record in (select * from users) LOOP
168 if user_record.username = u_username and user_record.password = u_password then
169 DBMS_OUTPUT.PUT_LINE('Password and login are correct u are logged in');
170 logged:=true;
171 user_exist:=true;
172 saved_username:=u_username;
173 end if;
174 end LOOP;
175 if user_exist = false then
176 DBMS_OUTPUT.PUT_LINE('Password and login are not correct, try again');
177 end if;
178 else
179 raise uAreLogged;
180 end if;
181EXCEPTION
182 when uAreLogged then
183 DBMS_OUTPUT.PUT_LINE('You cant log in if you are already logged');
184END login;
185
186
187PROCEDURE showAllCars is
188cursor searching_cars is select * from cars;
189begin
190 for car in searching_cars loop
191 DBMS_OUTPUT.PUT_LINE('Car VIN: '||car.carVin||' | Car brand: '||car.carBrand||' | Car model: '||car.carModel||' | Car color: '||car.carColor||' | Car value: '||car.carValue);
192 end loop;
193
194end showAllCars;
195
196PROCEDURE checkAvailability (u_BeginDate date, u_EndDate date, u_carVin varchar2)
197is
198v_carId number;
199noCarFound EXCEPTION;
200v_temp number;
201PRAGMA EXCEPTION_INIT(noCarFound,+100);
202begin
203 v_temp := 0;
204 select carId into v_carId from cars where carVin = u_carVin;
205 FOR snRecord in (select * from carsRental where carId = v_carId and ((rentalDate >= u_BeginDate and rentalEndDate <= u_EndDate) or (u_EndDate>= rentalDate and u_EndDate<= rentalEndDate))) LOOP
206 v_temp:= snRecord.carId;
207 END LOOP;
208 if v_temp = 0 then
209 DBMS_OUTPUT.PUT_LINE('This car is free at that date');
210 else
211 DBMS_OUTPUT.PUT_LINE('This car isnt free at that date');
212 end if;
213
214EXCEPTION
215 WHEN noCarFound then
216 DBMS_OUTPUT.PUT_LINE('We couldnt find car with that VIN number.');
217end checkAvailability;
218
219--- logged person UI
220PROCEDURE logout is
221begin
222 if logged = true then
223 logged:=false;
224 DBMS_OUTPUT.PUT_LINE('You have been logged out');
225 else
226 DBMS_OUTPUT.PUT_LINE('Please login first');
227 end if;
228end logout;
229
230PROCEDURE add_balance(u_acc_balance number) is
231begin
232 if logged = true then
233 update users set acc_balance = u_acc_balance where username = saved_username;
234 DBMS_OUTPUT.PUT_LINE('You have added money to your account');
235 else
236 DBMS_OUTPUT.PUT_LINE('Please login first');
237 end if;
238end add_balance;
239
240PROCEDURE addAddress(u_city varchar2,u_country varchar2,u_street varchar2,u_house_nr varchar2) is
241aintLogged exception;
242v_userId number;
243v_addressCount number;
244v_temp number;
245begin
246 if logged = true then
247 v_temp := 0;
248 FOR snRecord in (select addressId from addresses where city = u_city and country = u_country and street = u_street and house_nr = u_house_nr) LOOP
249 v_temp := snRecord.addressId;
250 END LOOP;
251 IF v_temp = 0 THEN
252 select userId into v_userId from users where username = saved_username;
253 select count(*) into v_addressCount from addresses;
254 v_addressCount:=v_addressCount+1;
255 insert into addresses values (v_addressCount,u_city,u_country,u_street,u_house_nr);
256 insert into UsersAddressesRelation values(v_userId,v_addressCount);
257 DBMS_OUTPUT.PUT_LINE('Your address have been added');
258 else
259 DBMS_OUTPUT.PUT_LINE('There is already that address and if it is your contact with administrator');
260 end if;
261 else
262 raise aintLogged;
263 end if;
264EXCEPTION
265 when aintLogged then
266 DBMS_OUTPUT.PUT_LINE('Please login first');
267end addAddress;
268
269PROCEDURE showProfile is
270aintLogged exception;
271v_userId number;
272v_username varchar2(50);
273v_password varchar2(50);
274v_acc_balance number;
275
276begin
277 if logged = true then
278 select u.userId,u.username,u.password,u.acc_balance into v_userId,v_username, v_password, v_acc_balance from users u where u.username = saved_username;
279 DBMS_OUTPUT.PUT_LINE('=== PROFILE ===');
280 DBMS_OUTPUT.PUT_LINE(v_username||' '||v_password||' | Acc balance: '||v_acc_balance);
281 DBMS_OUTPUT.PUT_LINE('=== ADDRESS ===');
282 FOR singleRecord in (select a.country, a.city, a.street, a.house_nr from addresses a, UsersAddressesRelation uar where uar.userId = v_userId and uar.addressId = a.addressId) LOOP
283 DBMS_OUTPUT.PUT_LINE(singleRecord.country||' '||singleRecord.city||' '||singleRecord.street||' '||singleRecord.house_nr);
284 END LOOP;
285 else
286 raise aintLogged;
287 end if;
288EXCEPTION
289 when aintLogged then
290 DBMS_OUTPUT.PUT_LINE('Please login first');
291end showProfile;
292
293PROCEDURE showHistory is
294aintLogged exception;
295v_userId number;
296begin
297 if logged = true then
298 select userId into v_userId from users where username = saved_username;
299 DBMS_OUTPUT.PUT_LINE('===HISTORY===');
300 FOR singleRecord in (select r.rentalDate, r.rentalEndDate, r.rentalValue, c.carBrand, c.carModel from carsRental r, cars c where r.userId = v_userId and r.carId = c.carId) LOOP
301 DBMS_OUTPUT.PUT_LINE('Car: '||singleRecord.carBrand||' '||singleRecord.carModel||' | Rental date: '||singleRecord.rentalDate||'-'||singleRecord.rentalEndDate||' | Value: '||singleRecord.rentalValue);
302 END LOOP;
303 else
304 raise aintLogged;
305 end if;
306EXCEPTION
307 when aintLogged then
308 DBMS_OUTPUT.PUT_LINE('Please login first');
309end showHistory;
310
311-- ******************** ADMIN procedures
312PROCEDURE carProfit (u_carVin varchar2, u_BeginDate date, u_EndDate date) is
313aintAdmin exception;
314v_carId number;
315v_sumUp number;
316begin
317 if saved_username = 'admin' then
318 DBMS_OUTPUT.PUT_LINE('===Car '||u_carVin||' profit only ADMIN===');
319 v_sumUp := 0;
320 select carId into v_carId from cars where carVin = u_carVin;
321 FOR singleRecord in (select rentalValue from carsRental where carId = v_carId and rentalDate >= u_BeginDate and rentalEndDate <= u_EndDate ) LOOP
322 v_sumUp := v_sumUp + singleRecord.rentalValue;
323 end LOOP;
324 DBMS_OUTPUT.PUT_LINE('This car earned '||v_sumUp||' from '||u_BeginDate||' to '||u_EndDate);
325 else
326 raise aintAdmin;
327 end if;
328EXCEPTION
329 when aintAdmin then
330 DBMS_OUTPUT.PUT_LINE('ERROR you dont have permission to that command');
331end carProfit;
332
333PROCEDURE userProfit is
334aintAdmin exception;
335CURSOR my_users is select u.username,sum(r.rentalValue) as sumUp from carsRental r,users u where u.userId = r.userId group by u.username order by sumUp Desc;
336singleRecord my_users%ROWTYPE;
337begin
338 if saved_username = 'admin' then
339 DBMS_OUTPUT.PUT_LINE('=== Users profit only ADMIN===');
340 FOR singleRecord in my_users LOOP
341 DBMS_OUTPUT.PUT_LINE('Username: '||singleRecord.username||' | spent money: '||singleRecord.sumUp);
342 END LOOP;
343 else
344 raise aintAdmin;
345 end if;
346EXCEPTION
347 when aintAdmin then
348 DBMS_OUTPUT.PUT_LINE('ERROR you dont have permission to that command');
349end userProfit;
350
351PROCEDURE carHistory (u_carVin varchar2 ) is
352aintAdmin exception;
353cursor my_car is SELECT u.username, r.rentalDate, r.rentalEndDate from users u,carsRental r,cars c where r.userId = u.userId and r.carId = c.carId and c.carVin = u_carVin order by r.rentalEndDate DESC;
354singleRecord my_car%rowtype;
355begin
356 if saved_username = 'admin' then
357 DBMS_OUTPUT.PUT_LINE('=== Car '||u_carVin||' history only ADMIN===');
358 FOR singleRecord in my_car LOOP
359 DBMS_OUTPUT.PUT_LINE('Username: '||singleRecord.username||' | from: '||singleRecord.rentalDate||' to: '||singleRecord.rentalEndDate);
360 END LOOP;
361 else
362 raise aintAdmin;
363 end if;
364EXCEPTION
365 when aintAdmin then
366 DBMS_OUTPUT.PUT_LINE('ERROR you dont have permission to that command');
367end carHistory;
368
369PROCEDURE carProfit is
370aintAdmin exception;
371cursor my_cars is select c.carVin, sum(r.rentalValue) as sumUp from carsRental r, cars c where r.carId = c.carId group by c.carVin order by sumUp desc;
372singleRecord my_cars%rowtype;
373begin
374 if saved_username = 'admin' then
375 DBMS_OUTPUT.PUT_LINE('=== CARS profit only ADMIN===');
376 FOR singleRecord in my_cars LOOP
377 DBMS_OUTPUT.PUT_LINE('Car VIN: '||singleRecord.carVin||' | earned money: '||singleRecord.sumUp);
378 END LOOP;
379 else
380 raise aintAdmin;
381 end if;
382EXCEPTION
383 when aintAdmin then
384 DBMS_OUTPUT.PUT_LINE('ERROR you dont have permission to that command');
385end carProfit;
386
387PROCEDURE showAllCarsAdmin is
388aintAdmin exception;
389cursor searching_cars is select * from cars;
390begin
391 if saved_username = 'admin' then
392 DBMS_OUTPUT.PUT_LINE('===SHOW CARS only ADMIN===');
393 for car in searching_cars loop
394 DBMS_OUTPUT.PUT_LINE('Car VIN: '||car.carVin||' | Car id: '||car.carId||' | Car brand: '||car.carBrand||' | Car model: '||car.carModel||' | Car color: '||car.carColor||' | Car value: '||car.carValue);
395 end loop;
396 else
397 raise aintAdmin;
398 end if;
399EXCEPTION
400 when aintAdmin then
401 DBMS_OUTPUT.PUT_LINE('ERROR you dont have permission to that command');
402end showAllCarsAdmin;
403
404PROCEDURE showAllUsersAdmin is
405aintAdmin exception;
406cursor searching_users is select * from users;
407begin
408 if saved_username = 'admin' then
409 for s_user in searching_users loop
410 DBMS_OUTPUT.PUT_LINE('User id: '||s_user.userId||' | User name: '||s_user.username||' | User password: '||s_user.password||' | User balance: '||s_user.acc_balance);
411 end loop;
412 else
413 raise aintAdmin;
414 end if;
415EXCEPTION
416 when aintAdmin then
417 DBMS_OUTPUT.PUT_LINE('ERROR you dont have permission to that command');
418end showAllUsersAdmin;
419
420PROCEDURE showLogsAdmin is
421aintAdmin exception;
422begin
423 if saved_username = 'admin' then
424 DBMS_OUTPUT.PUT_LINE('===LOGS only ADMIN===');
425 FOR singleRecord in (select logId, userId, carId, rentalId, rentalDate, rentalEndDate, rentalValue, rentalOperation,newUserAccBalance,oldUserAccBalance from logs) LOOP
426 DBMS_OUTPUT.PUT_LINE('Log id: '||singleRecord.logId||' | User id: '||singleRecord.userId|| ' | Car id: '||singleRecord.carId||' | Rental id: '||singleRecord.rentalId||' | Rental date: '||singleRecord.rentalDate||' | Rental end date: '||singleRecord.rentalEndDate||' | Rental value: '||singleRecord.rentalValue||' | Old acc balance: '||singleRecord.oldUserAccBalance||' | New acc balance: '||singleRecord.newUserAccBalance||' | Rental operation: '||singleRecord.rentalOperation);
427 END LOOP;
428 else
429 raise aintAdmin;
430 end if;
431EXCEPTION
432 when aintAdmin then
433 DBMS_OUTPUT.PUT_LINE('ERROR you dont have permission to that command');
434end showLogsAdmin;
435
436PROCEDURE addCar(u_carVin varchar2,u_carBrand varchar2, u_carModel varchar2, u_carColor varchar2,u_carValue number) is
437carCount number;
438toLowCarValue exception;
439aintAdmin exception;
440begin
441 if saved_username = 'admin' then
442 if u_carValue < 100 then
443 raise toLowCarValue;
444 end if;
445
446 select count(*) into carCount from cars;
447 carCount := carCount + 1;
448
449 insert into cars values (carCount,u_carVin,u_carBrand,u_carModel,u_carColor,u_carValue );
450 DBMS_OUTPUT.PUT_LINE('Car succesfuly added');
451 else
452 raise aintAdmin;
453 end if;
454EXCEPTION
455 when toLowCarValue then
456 DBMS_OUTPUT.PUT_LINE('Car value is below 100pln');
457 when aintAdmin then
458 DBMS_OUTPUT.PUT_LINE('ERROR you dont have permission to that command');
459end addCar;
460
461
462
463PROCEDURE rentCar(u_carVin varchar2, u_BeginDate date, u_EndDate date, u_addressNumber number)
464is
465aintLogged exception;
466freeCarNotFound exception;
467notEnoughMoney exception;
468carNotFound exception;
469addressNotFound exception;
470v_carId number;
471v_ableToPay boolean;
472v_rentCount number;
473v_userId number;
474v_carValue number;
475v_sumUp number;
476v_date number;
477v_city varchar2(50);
478v_country varchar2(50);
479v_street varchar2(50);
480v_houseNr varchar2(50);
481v_addressFounded boolean;
482v_iterator number;
483begin
484 if logged = true then
485 v_addressFounded := false;
486 v_iterator:=1;
487 if checkCar(u_carVin) = false then
488 raise carNotFound;
489 end if;
490 v_carId := checkDate(u_BeginDate, u_EndDate, u_carVin);
491 if v_carId = 0 then
492 raise freeCarNotFound;
493 end if;
494 v_ableToPay := checkBalance(v_carId, u_BeginDate, u_EndDate);
495 if v_ableToPay = true then
496 raise notEnoughMoney;
497 end if;
498 select count(*) into v_rentCount from carsRental;
499 v_rentCount := v_rentCount + 1;
500 select userId into v_userId from users where username = saved_username;
501 select carValue into v_carValue from cars where carId = v_carId;
502 v_date := u_EndDate - u_BeginDate;
503 v_sumUp := v_carValue * v_date;
504 -- getAddress
505
506 FOR singleRecord in (select a.city,a.street, a.country, a.house_nr from addresses a, UsersAddressesRelation uar where uar.addressId = a.addressId and uar.userId = v_userId) LOOP
507 if v_iterator = u_addressNumber then
508 v_city := singleRecord.city;
509 v_country := singleRecord.country;
510 v_street := singleRecord.street;
511 v_houseNr := singleRecord.house_nr;
512 v_addressFounded:=true;
513 end if;
514 v_iterator := v_iterator+1;
515 END LOOP;
516 if v_addressFounded = false then
517 raise addressNotFound;
518 end if;
519 savepoint sp1;
520 insert into carsRental values (v_rentCount,v_carId,v_userId,u_BeginDate,u_EndDate,v_sumUp);
521 update users set acc_balance = acc_balance - v_sumUp where username = saved_username;
522 commit;
523 DBMS_OUTPUT.PUT_LINE('You succesfuly rent '||u_carVin||' it will be delivered to your address: '||v_country||' '||v_city||' '||v_street||' '||v_houseNr);
524 else
525 raise aintLogged;
526 end if;
527EXCEPTION
528 when aintLogged then
529 DBMS_OUTPUT.PUT_LINE('Please login first');
530 when freeCarNotFound then
531 DBMS_OUTPUT.PUT_LINE('This car isnt free at this date, try other one');
532 when notEnoughMoney then
533 DBMS_OUTPUT.PUT_LINE('You dont have enough money for this car');
534 when carNotFound then
535 DBMS_OUTPUT.PUT_LINE('We dont have that car in our registry');
536 when addressNotFound then
537 DBMS_OUTPUT.PUT_LINE('Address number not found');
538 when DUP_VAL_ON_INDEX then
539 rollback to sp1;
540 DBMS_OUTPUT.PUT_LINE('Inserts were rolled back');
541
542end rentCar;
543
544FUNCTION checkDate(u_BeginDate in date, u_EndDate in date, u_carVin in varchar2)
545RETURN number
546is
547aintLogged exception;
548invalidDate exception;
549v_carId number;
550begin
551 if logged = true then
552 v_carId:=0;
553 if u_BeginDate>u_EndDate then
554 raise invalidDate;
555 end if;
556
557 for singleRecord in (select c.carId from carsRental r, cars c where c.carVin = u_carVin and c.carId = r.carId and ((r.rentalDate >= u_BeginDate and r.rentalEndDate <= u_EndDate) or u_EndDate>= r.rentalDate and u_EndDate<= r.rentalEndDate)) LOOP
558 v_carId := singleRecord.carId;
559 end LOOP;
560 if v_carId = 0 then
561 select carId into v_carId from cars where carVin = u_carVin;
562 else
563 v_carId := 0;
564 end if;
565 RETURN v_carId;
566 else
567 raise aintLogged;
568 end if;
569EXCEPTION
570 when aintLogged then
571 DBMS_OUTPUT.PUT_LINE('Please login first');
572 when invalidDate then
573 DBMS_OUTPUT.PUT_LINE('Begining rental date is after ending date');
574end checkDate;
575
576FUNCTION checkBalance(u_carId in number, u_BeginDate in date, u_EndDate in date )
577return boolean
578is
579dateDifference number;
580accountBalance number;
581carPerDay number;
582sumUp number;
583aintLogged exception;
584begin
585 if logged = true then
586 dateDifference := u_BeginDate - u_EndDate;
587 select acc_balance into accountBalance from users where username = saved_username;
588 select carValue into carPerDay from cars where carId = u_carId;
589 sumUp:= carPerDay * dateDifference;
590 if accountBalance<sumUp then
591 RETURN true;
592 else
593 RETURN false;
594 end if;
595 else
596 raise aintLogged;
597 end if;
598EXCEPTION
599 when aintLogged then
600 DBMS_OUTPUT.PUT_LINE('Please login first');
601end checkBalance;
602FUNCTION checkCar (u_carVin varchar2)
603return boolean
604is
605aintLogged exception;
606countNumber number;
607begin
608 if logged = true then
609 countNumber:= 0 ;
610 select count(*) into countNumber from cars where carVin = u_carVin;
611
612 if countNumber = 0 then
613 return false;
614 else
615 return true;
616 end if;
617 else
618 raise aintLogged;
619 end if;
620EXCEPTION
621 when aintLogged then
622 DBMS_OUTPUT.PUT_LINE('Please login first');
623end checkCar;
624
625
626end projekt_packet;
627
628CREATE OR REPLACE TRIGGER tr_rentCars
629 BEFORE INSERT OR UPDATE OF rentalId, carId, userId, rentalDate, rentalEndDate, rentalValue
630 ON carsRental
631 FOR EACH ROW
632 DECLARE
633 v_logId number;
634 v_acc_balance number;
635 v_new_acc_balance number;
636 operationName varchar(20);
637 BEGIN
638 select count(*) into v_logId from logs;
639 v_logId := v_logId + 1;
640 select acc_balance into v_acc_balance from users where userId = :NEW.userId;
641 v_new_acc_balance := v_acc_balance - :NEW.rentalValue;
642 CASE
643 WHEN INSERTING then
644 operationName :='INSERT';
645 WHEN UPDATING then
646 operationName :='UPDATE';
647 END CASE;
648 insert into logs values (v_logId,:NEW.userId, :NEW.carId, :NEW.rentalId, :NEW.rentalDate, :NEW.rentalEndDate, :NEW.rentalValue, v_acc_balance,v_new_acc_balance, operationName );
649 END;
650
651
652
653begin
654projekt_packet.showAllCars();
655projekt_packet.checkAvailability(TO_DATE('2021-06-05','YYYY-MM-DD'),TO_DATE('2021-06-10','YYYY-MM-DD'),'SWA123D4');
656--projekt_packet.register('Lublin','Polska','Kwiatkowska','12A','maciek','maciek');
657
658projekt_packet.login('maciek','maciek');
659projekt_packet.add_balance(50000);
660--projekt_packet.addAddress('Warszawa','Polska','Jana','12B');
661projekt_packet.showProfile();
662projekt_packet.rentCar('SWA123D4',TO_DATE('2021-06-05','YYYY-MM-DD'),TO_DATE('2021-06-10','YYYY-MM-DD'));
663projekt_packet.showHistory();
664projekt_packet.logout();
665
666projekt_packet.login('admin','admin');
667
668projekt_packet.showLogsAdmin();
669projekt_packet.showAllUsersAdmin();
670
671projekt_packet.addCar('TTT123T1','Fiat','126p','Grey',100);
672projekt_packet.showAllCarsAdmin();
673projekt_packet.logout();
674end;
675