AgN2ZjZD

1-- Ex.7 a)
2go
3drop procedure getCategoryCars
4go
5
6CREATE PROCEDURE getCategoryCars(@category int, @location int, @startTime datetime, @endtime datetime)
7as
8-- check category data about car
9declare @toDelete int
10declare @id int
11declare @carId int
12declare @starttime1 datetime
13declare @endtime1 datetime
14declare @goodDate int
15declare @notUsedBefore int
16
17
18--Set temporary table to save important data from bookings
19Create Table #TempInfoTable (
20id int identity,
21carId int,
22starttime datetime,
23endtime datetime,
24goodDate int
25);
26
27Insert Into #TempInfoTable select car.carId, booking.starttime, booking.endtime,0 from car inner join booking on car.carId = booking.carId 
28inner join location on location.locationId = car.locationid inner join category on category.categoryId = car.categoryId where location.locationId = @location and category.categoryId = @category
29
30set @notUsedBefore = (SELECT car.carId FROM car 
31inner join location on location.locationId = car.locationId 
32WHERE  car.categoryId = @category and car.locationId = @location and not exists (select * from booking where booking.carId = car.carId))
33
34DECLARE p CURSOR FOR SELECT * from #TempInfoTable
35OPEN p;
36FETCH p into @id,@carId, @starttime1, @endtime1, @goodDate
37WHILE @@FETCH_STATUS = 0
38BEGIN
39if @endtime1 between @starttime and @endtime  or @starttime1 between @starttime and @endtime or @starttime between @starttime1 and @endtime1 or @endtime between @starttime1 and @endtime1
40begin
41update t set t.goodDate = 1 from #TempInfoTable t where id = @id
42end
43FETCH p into @id, @carId, @starttime1, @endtime1, @goodDate
44END;
45CLOSE p;
46DEALLOCATE p;
47set @toDelete = (select carId from #TempInfoTable where goodDate = 1) 
48delete from #TempInfoTable where carId = @toDelete
49if @notUsedBefore > 0 
50begin
51Insert Into #TempInfoTable select car.carId, 0,0,0 FROM car 
52inner join location on location.locationId = car.locationId 
53WHERE  car.categoryId = @category and car.locationId = @location and not exists (select * from booking where booking.carId = car.carId)
54end
55select * from #TempInfoTable
56
57
58
59-- avail
60exec getCategoryCars 1,1,'2019.05.31', '2019.06.24'	
61go
62
63select * from booking where booking.carId = 1
64
65-- not available for bookin
66exec getCategoryCars 1,5,'2019.01.15', '2019.02.24'
67select * from booking where booking.carId = 6
68
69-- never had booking before
70exec getCategoryCars 5,4,'2018.11.30', '2019.01.24'
71go
72
73
74select * from car 
75select * from car inner join location on car.locationid = location.locationId where car.carId = 2
76
77
78delete from booking where carId = 2
79
80--Ex.7 b)
81go
82drop procedure getCarIfAvailable
83go
84
85CREATE PROCEDURE getCarIfAvailable(@car int, @startTime datetime, @endtime datetime)
86as
87declare @notUsedBefore int
88declare @isItFree int
89
90--find out if there are any  cars that did not have booking yet
91
92set @notUsedBefore = (SELECT COUNT(car.carId) FROM car 
93inner join location on location.locationId = car.locationId 
94where car.carId = @car and not exists (select * from booking where booking.carId = @car))
95
96--find out if there any cars available right now
97
98set @isItFree = (SELECT COUNT(car.carId) from car 
99inner join location on location.locationId = car.locationId 
100inner join booking  on car.carId = booking.carId 
101where car.carId = @car and @startTime not between booking.starttime and booking.endtime
102 and @endtime not between booking.starttime and booking.endtime 
103 and booking.carId = car.carId and  booking.starttime not between @starttime and @endtime and booking.endtime not between @starttime and @endtime)
104
105select location.locationId,car.categoryId, car.carId,car.plateNumber, car.description,car.firstRegistration,location.address, car.odoMeter from car 
106inner join location on location.locationId = car.locationId 
107inner join booking  on car.carId = booking.carId 
108where car.carId = @car and @startTime not between booking.starttime and booking.endtime
109 and @endtime not between booking.starttime and booking.endtime 
110 and booking.carId = car.carId and  booking.starttime not between @starttime and @endtime and booking.endtime not between @starttime and @endtime
111
112-- data that is not in booking table yet.
113if @notUsedBefore > 0 
114begin
115select location.locationId,car.categoryId, car.carId,car.plateNumber, car.description,car.firstRegistration,location.address, car.odoMeter from car 
116inner join location on location.locationId = car.locationId 
117where car.carId = @car and not exists (select * from booking where booking.carId = car.carId)
118end
119--error if there is nothing available
120if @notUsedBefore = 0 and @isItFree = 0
121begin
122raiserror('Sorry not available',16,1)
123end
124
125return
126go
127--not available
128exec getCarIfAvailable 1,'2018.12.31', '2019.03.23'
129go
130--available
131exec getCarIfAvailable 1,'2019.05.30', '2019.06.24'
132select * from booking where booking.carId = 1
133go
134--car that never had booking before
135exec getCarIfAvailable 25,'2018.11.30','2019.01.24'