· 8 years ago · May 19, 2018, 02:26 AM
1DROP DATABASE IF EXISTS cableCompany;
2CREATE DATABASE cableCompany;
3USE cableCompany;
4
5CREATE TABLE customers
6(
7 customerID INT UNSIGNED NOT NULL AUTO_INCREMENT primary key,
8 firstName VARCHAR(55) NOT NULL,
9 middleName VARCHAR(55) NOT NULL,
10 lastName VARCHAR(55) NOT NULL,
11 email VARCHAR(55) NULL,
12 phone VARCHAR(20) NOT NULL,
13 address VARCHAR(255) NOT NULL
14);
15
16CREATE TABLE accounts
17(
18 accountID INT AUTO_INCREMENT PRIMARY KEY,
19 amount DOUBLE NOT NULL,
20 customer_id INT UNSIGNED NOT NULL,
21 CONSTRAINT FOREIGN KEY (customer_id) REFERENCES customers(customerID)
22 ON DELETE RESTRICT ON UPDATE CASCADE
23);
24
25CREATE TABLE plans
26(
27 planID INT AUTO_INCREMENT PRIMARY KEY,
28 name VARCHAR(32) NOT NULL,
29 monthly_fee DOUBLE NOT NULL
30);
31
32CREATE TABLE payments
33(
34 paymentID INT AUTO_INCREMENT PRIMARY KEY,
35 paymentAmount DOUBLE NOT NULL,
36 month TINYINT NOT NULL,
37 year YEAR NOT NULL,
38 dateOfPayment DATETIME NOT NULL,
39 customer_id INT UNSIGNED NOT NULL,
40 plan_id INT UNSIGNED NOT NULL,
41 CONSTRAINT FOREIGN KEY (customer_id) REFERENCES customers(customerID),
42 CONSTRAINT FOREIGN KEY (plan_id) REFERENCES plans (planID),
43 UNIQUE KEY (customer_id, plan_id, month, year)
44);
45
46CREATE TABLE debtors
47(
48 customer_id INT NOT NULL,
49 plan_id INT NOT NULL,
50 debt_amount DOUBLE NOT NULL,
51 FOREIGN KEY (customer_id) REFERENCES customers(customerID) ,
52 FOREIGN KEY (plan_id) REFERENCES plans(planID) ,
53 PRIMARY KEY (customer_id, plan_id)
54);
55
56INSERT INTO customers (firstName, middleName, lastName, email, phone, address)
57VALUES
58( 'Ivan', 'Todorov', 'Petrov', 'Ivan33@gmail.com', '0884123456', 'Sofia- Mladost'),
59( 'Ivanka', 'Toshkova', 'Petkova', 'sunshine35@gmail.com', '0888257826', 'Plovdiv- Mladost'),
60( 'Joro', 'Mitkov', 'Kuchkov', 'Joro12@abv.bg', '0882143552', 'Sliven- Drujba'),
61( 'Filip', 'Ivanov', 'Georgiev', 'Filqka11@mail.bg', '0889134457', 'Varna- Moreto'),
62( 'Hristo', 'Veselinov', 'Mitev', 'Icaka1134@gmail.com', '0882798410', 'England- Swindon'),
63( 'Kalina', 'Dimitrova', 'Rasheva', 'bug21@abv.bg', '0881133357', 'Pleven- Mladost');
64
65INSERT INTO accounts(amount, customer_id)
66VALUES (3000, 1), (1200, 2), (200, 3), (700, 4), (10000, 5), (120, 6);
67
68INSERT INTO plans(name, monthly_fee) VALUES ('Plan 1', 200),
69 ('Plan 2', 350),
70 ('Plan 3', 1200),
71 ('Plan 4', 120);
72
73INSERT INTO payments (paymentAmount, month, year, dateOfPayment, customer_id, plan_id) VALUES
74 (300, 1, 2016, '2016-03-03 16:35:00', 1, 3),
75 (100, 1, 2016, '2016-03-04 17:35:00', 2, 2),
76 (300, 1, 2016, '2016-03-05 11:43:00', 3, 1),
77 (50, 1, 2016, '2016-03-06 15:11:00', 4, 4),
78 (112, 1, 2016, '2016-03-07 09:51:00', 5, 3),
79 (75, 1, 2016, '2016-03-08 10:15:00', 6, 2);
80
81INSERT INTO debtors (customer_id, plan_id, debt_amount)
82VALUES (2, 2, 300), (5, 3, 150), (1, 3, 65), (6, 2, 200);
83
84
85
86DROP PROCEDURE IF EXISTS payMonthTax
87
88delimiter |
89
90CREATE PROCEDURE payMonthTax(IN customerId INT, IN tempSum DECIMAL, OUT success bit)
91
92BEGIN
93
94IF((SELECT amount
95 FROM accounts
96 WHERE amount >= tempSum
97 AND customer_id = customerId
98 ) IS NULL)
99THEN
100 SELECT 'Invalid id or there is not enough money in the account!';
101SET success = 0;
102ELSE
103 IF
104 ((
105 SELECT paymentAmount
106 FROM payments
107 WHERE customer_id = customerId
108 ) = 0)
109 THEN
110 SELECT 'The fee is already paid';
111 SET success = 1;
112 ELSE
113 SET success = 0;
114
115
116START TRANSACTION;
117
118UPDATE accounts SET amount = amount - tempSum
119WHERE customer_id = customerId;
120
121IF(ROW_COUNT() = 0)
122THEN
123 ROLLBACK;
124ELSE
125 UPDATE payments
126 SET paymentAmount = paymentAmount - tempSum WHERE customer_id = customerId;
127
128 IF(ROW_COUNT() = 0)
129 THEN
130 ROLLBACK;
131 ELSE
132SET success = 1;
133SELECT 'Transaction complete'
134COMMIT;
135
136END IF;
137END IF;
138END IF;
139END IF;
140END;
141|
142delimiter ;
143
144
145
146DROP PROCEDURE IF EXISTS paymentTime;
147
148delimiter |
149
150CREATE PROCEDURE paymentTime(IN monthOfPayment TINYINT, IN yearOfPayment INT)
151
152BEGIN
153DECLARE tempSum DECIMAL;
154DECLARE tempCustomerId INT;
155DECLARE tempPlanId INT;
156DECLARE result INT;
157DECLARE paymentCursor CURSOR FOR
158SELECT customer_id, paymentAmount, plan_id
159FROM payments
160WHERE `month` = monthOfPayment
161AND `year` = yearOfPayment;
162DECLARE CONTINUE HANDLER FOR NOT FOUND SET result = 1;
163
164DROP TABLE IF EXISTS tempAccount;
165
166CREATE TEMPORARY TABLE tempAccount(
167tempId INT NOT NULL,
168tempAmount INT NOT NULL,
169CustomerID INT NOT NULL,
170debt INT NOT NULL,
171planId INT NOT NULL
172)ENGINE = MEMORY;
173
174START TRANSACTION;
175OPEN paymentCursor;
176SET result = 0;
177payment_loop: WHILE (result = 0)
178DO FETCH paymentCursor INTO tempCustomerId, tempSum, tempPlanId;
179
180IF(result = 1) THEN LEAVE payment_loop;
181ELSE
182SET @RESULT = 0;
183CALL payMonthTax(tempCustomerId, tempSum, @RESULT);
184
185IF(@RESULT = 0)
186THEN
187SELECT tempCustomerId, tempSum;
188INSERT INTO tempAccount (tempId, tempAmount, CustomerID, debt, planId)
189SELECT a.accountID, a.amount, tempCustomerId, (tempSum - a.amount), tempPlanId
190FROM accounts as a
191WHERE a.customer_id = tempCustomerId;
192END IF;
193END IF;
194END WHILE;
195CLOSE paymentCursor;
196SELECT * FROM tempAccount; #only for test
197
198INSERT INTO debtors(customer_id, plan_id, debt_amount)
199SELECT CustomerID, planId, debt
200FROM tempAccount
201ON DUPLICATE KEY UPDATE
202debt_amount = debt_amount + debt;
203
204
205UPDATE accounts
206SET amount = 0 WHERE customer_id IN (SELECT CustomerID
207 FROM tempAccount);
208
209IF(ROW_COUNT() = 0)
210THEN ROLLBACK;
211ELSE
212
213UPDATE payments
214SET paymentAmount = 0 WHERE customer_id IN (SELECT CustomerID
215 FROM tempAccount)
216AND `month` = monthOfPayment
217AND `year` = yearOfPayment;
218
219IF(ROW_COUNT() = 0)
220THEN ROLLBACK;
221ELSE
222COMMIT;
223SELECT 'Transaction finished!';
224
225END IF;
226END IF;
227DROP TABLE tempAccount;
228END
229
230|
231delimiter ;
232
233CALL paymentTime(1, 2016);
234
235
236
237DROP EVENT IF EXISTS paymentEvent;
238
239delimiter |
240
241CREATE EVENT paymentEvent
242
243ON SCHEDULE EVERY 1 MONTH
244STARTS '2018-05-28 17:00:00'
245DO
246BEGIN
247CALL paymentTime(MONTH(NOW()), YEAR(NOW()));
248END;
249|
250DELIMITER ;
251
252
253DROP VIEW IF EXISTS testView;
254
255CREATE VIEW testView
256AS SELECT customers.firstName, customers.middleName, customers.lastName, payments.year, payments.month,
257plans.name, plans.monthly_fee
258FROM customers JOIN payments JOIN plans
259ON customers.customerId = payments.customer_id
260AND plans.planId = payments.plan_id;
261
262#SELECT * FROM testView;
263
264
265
266DROP TRIGGER IF EXISTS planTrigger
267
268DELIMITER |
269
270CREATE TRIGGER planTrigger BEFORE INSERT ON plans
271FOR EACH ROW
272BEGIN
273IF(NEW.monthly_fee <= 10)
274THEN
275SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'The fee must be over 10 BGN!';
276END IF;
277END
278|
279delimiter ;
280
281#for test
282#INSERT INTO plans(name, monthly_fee)
283#VALUES ('Plan 1', 9);
284
285DROP PROCEDURE IF EXISTS printInfo;
286
287DELIMITER |
288
289
290CREATE PROCEDURE printInfo(IN name1 VARCHAR(15), IN name2 VARCHAR(15), IN name3 VARCHAR(15))
291
292BEGIN
293SELECT customers.*, payments.paymentAmount
294FROM customers JOIN payments
295ON customers.customerId = payments.customer_id
296WHERE firstName = name1 and middleName = name2 and lastName= name3;
297END
298|
299DELIMITER ;
300
301#CALL printInfo('Ivan', 'Todorov', 'Petrov');