RmdFP1L6

1ALTER TABLE books ADD bestseller BOOLEAN;
2
3/* ----- */
4
5DROP FUNCTION IF EXISTS IsBestseller;
6
7DELIMITER $$
8
9CREATE FUNCTION IsBestseller(bk_id INT) RETURNS DECIMAL(5, 2) /*BOOLEAN*/ DETERMINISTIC
10BEGIN
11	DECLARE result BOOLEAN DEFAULT FALSE;
12    DECLARE days_rented, rentals INT;
13    DECLARE rents_per_month DECIMAL(5, 2);
14    
15    SELECT DATEDIFF(MAX(rent_date), MIN(rent_date)) + 1 FROM rents
16	WHERE book_id = bk_id
17	INTO days_rented;
18    
19    SELECT COUNT(*) FROM rents
20    WHERE book_id = bk_id
21    INTO rentals;
22    
23    /* Number of rentals in last 30 days
24    SELECT COUNT(*) FROM rents
25    WHERE book_id = bk_id
26    AND rent_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE()
27    INTO rents_per_month;*/
28    
29    /* Forecasted number of rentals per month
30    SET rents_per_month = rentals * (30 / days_rented);*/
31
32	
33    IF rentals < 31 THEN
34		SET rents_per_month = rentals;
35	ELSE
36		SET rents_per_month = rentals / (days_rented / 30);
37	END IF;
38    
39    IF rents_per_month > 2 THEN
40		SET result = true;
41    ELSE
42		SET result = false;
43	END IF;
44    
45    RETURN result;
46END $$
47
48DELIMITER ;
49
50/* ----- */
51
52SELECT IsBestseller(5) AS Bestseller;
53
54/* ----- */
55/* ----- */
56
57DROP PROCEDURE IF EXISTS UpdateBestsellers;
58
59DELIMITER $$
60
61CREATE PROCEDURE UpdateBestsellers()
62BEGIN
63	DECLARE bk_id INT;
64	DECLARE is_bestseller BOOLEAN;
65    DECLARE finished INT DEFAULT 0;
66    DECLARE all_books CURSOR FOR SELECT book_id FROM books;
67    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
68    OPEN all_books;
69    WHILE(finished = 0) DO
70		FETCH all_books INTO bk_id;
71        IF(finished = 0) THEN
72			UPDATE books SET bestseller = IsBestseller(bk_id)
73			WHERE book_id = bk_id;
74            COMMIT;
75		END IF;
76	END WHILE;
77    CLOSE all_books;
78END $$
79
80DELIMITER ;
81
82/* ----- */
83
84CALL UpdateBestsellers();