qMZPtxny

1-- by Raymond Gao C:
2DROP VIEW IF EXISTS VALIDBOOKINGS CASCADE;
3DROP TABLE IF EXISTS TEMPBOOKING CASCADE;
4DROP TABLE IF EXISTS renting CASCADE;
5DROP VIEW IF EXISTS daysRentedPerYear CASCADE;
6
7
8-- validBookings
9
10CREATE OR REPLACE VIEW validBookings as select * from booking x where NOT EXISTS(select * from booking z where 
11(x.listingID = z.listingID and x.startDate != z.startDate)and ((z.startDate <= (x.startDate + x.numNights) and z.startDate >= x.startDate) or (x.startDate <= (z.startDate + z.numNights) and x.startDate >= z.startDate)));
12
13-- Okay, this question is kinda sadistic, what if there's a guy renting a place for like... 10 years?
14-- WELL I'VE GOT A SOLUTION FOR YOU, for only 3 installments of 999999.99
15
16
17-- Create a temporary table, where we'll be modifying values.
18CREATE TABLE tempBooking (
19       listingId integer REFERENCES listing,
20       startdate date NOT NULL,
21       travelerID integer REFERENCES traveler,
22       numNights integer NOT NULL default 1,
23       numGuests integer NOT NULL default 1,
24       price integer NOT NULL,
25       rowNumber integer NOT NULL,
26       PRIMARY KEY (listingId, startdate)
27);
28
29-- Insert into this table, adding row numbers, we'll use these for looping
30INSERT INTO tempBooking select *, ROW_NUMBER() OVER (ORDER BY startDate) from validBookings;
31
32CREATE TABLE renting (
33	listingId integer REFERENCES listing,
34	year integer NOT NULL,
35	numNights integer NOT NULL
36);
37
38DO
39$do$
40DECLARE
41_n int = (select count(*) from tempBooking);
42_x int = 1;
43_t int;
44_currentYear int;
45_tempDate date;
46_lastDay date;
47_nights int;
48_daysInYear int;
49_listingId int;
50BEGIN 
51WHILE _x <= _n 
52LOOP
53	_tempDate = (select startDate from tempBooking where rowNumber = _x);
54	_lastDay = to_date(EXTRACT(YEAR FROM _tempDate)|| '-12-31', 'YYYY-MM-DD');
55	_daysInYear = _lastDay - _tempDate + 1;
56	_nights = (select numNights from tempBooking where rowNumber = _x);
57	_listingId = (select listingId from tempBooking where rowNumber = _x);
58	_currentYear = EXTRACT(YEAR FROM _tempDate);
59	IF _nights <= _daysInYear THEN
60	INSERT INTO renting VALUES (_listingId, _currentYear, _nights);
61	_x = _x + 1;
62	ELSE
63	INSERT INTO renting VALUES (_listingId, _currentYear, _daysInYear);
64	UPDATE tempBooking SET startDate = startDate +  _daysInYear where rowNumber = _x;
65	UPDATE tempBooking SET numNights = numNights - _daysInYear where rowNumber = _x;
66	END IF;
67END LOOP;
68END
69$do$;
70
71-- Aggregate the days
72CREATE VIEW daysRentedPerYear as select renting.listingId, renting.year, sum(numNights) as numNights from renting GROUP BY listingId, year;
73
74-- Max Day Violations
75create view maxViolations as select l.listing,  from daysRentedPerYear r, listing l, cityRegulation c where 
76	r.listingId = l.listingId and l.city = c.city and (l.propertyType = c.propertyType or c.propertyType IS NULL) and
77	c.regulationType = 'max' and r.numNights > c.days;