· 8 years ago · Aug 17, 2018, 04:58 PM
1DELIMITER $$
2--
3-- Procedures
4--
5CREATE DEFINER=`db4458`@`localhost` PROCEDURE `CastVote` (IN `_serial` CHAR(32), IN `_candidate` VARCHAR(25), IN `_username` VARCHAR(255), OUT `_isSuccess` TINYINT(1) UNSIGNED) BEGIN
6 DECLARE _exists TINYINT DEFAULT 0;
7 DECLARE _candidateid TINYINT DEFAULT 0;
8 DECLARE _operationDone TINYINT(1) UNSIGNED DEFAULT 0;
9
10 DECLARE EXIT HANDLER FOR NOT FOUND ROLLBACK;
11 DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;
12 DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
13
14 START TRANSACTION;
15 SET _operationDone = 0;
16 SET _isSuccess = _operationDone;
17 -- Checks if the record exists in the Table
18 SELECT COUNT(*) INTO _exists FROM rpVot_Votes WHERE Serial = _serial OR UserName = _username;
19
20 -- If the record exists then it returns 2
21 IF(_exists > 0) THEN
22 SET _operationDone = 2;
23 ELSE
24 SELECT ID INTO _candidateid FROM rpVot_Candidates WHERE Name = _candidate LIMIT 1;
25 IF(_candidateid > 0) THEN
26 INSERT INTO rpVot_Votes(Serial, CandidateID, UserName)
27 VALUES(_serial, _candidateid, _username);
28
29 SET _operationDone = 1;
30 END IF;
31 END IF;
32 COMMIT;
33
34 SET _isSuccess = _operationDone;
35END$$
36
37CREATE DEFINER=`db4458`@`localhost` PROCEDURE `CastVoteByID` (IN `_serial` CHAR(32), IN `_candidateid` VARCHAR(25), IN `_username` VARCHAR(255), OUT `_isSuccess` TINYINT(1) UNSIGNED) BEGIN
38 DECLARE _exists TINYINT DEFAULT 0;
39 DECLARE _operationDone TINYINT(1) UNSIGNED DEFAULT 0;
40
41 DECLARE EXIT HANDLER FOR NOT FOUND ROLLBACK;
42 DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;
43 DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
44
45 START TRANSACTION;
46 SET _operationDone = 0;
47 SET _isSuccess = _operationDone;
48 -- Checks if the record exists in the Table
49 SELECT COUNT(*) INTO _exists FROM rpVot_Votes WHERE Serial = _serial OR UserName = _username;
50
51 -- If the record exists then it returns 2
52 IF(_exists > 0) THEN
53 SET _operationDone = 2;
54 ELSE
55 INSERT INTO rpVot_Votes(Serial, CandidateID, UserName)
56 VALUES(_serial, _candidateid, _username);
57 SET _operationDone = 1;
58 END IF;
59 COMMIT;
60
61 SET _isSuccess = _operationDone;
62END$$
63
64CREATE DEFINER=`db4458`@`localhost` PROCEDURE `CheckIngameCredentials` (IN `_username` VARCHAR(100), IN `_password` VARCHAR(255), OUT `_isSuccess` INT) BEGIN
65 DECLARE _id INT DEFAULT 0;
66
67
68 DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
69
70 START TRANSACTION;
71 SET _id = 0;
72
73 SELECT id, username INTO _id, _username FROM user_data WHERE username = _username AND pwsalted = (SELECT MD5(CONCAT(_username,'SAES',_password))) LIMIT 1;
74
75 COMMIT;
76
77 SET _isSuccess = _id;
78END$$
79
80CREATE DEFINER=`db4458`@`localhost` PROCEDURE `CountVotes` () BEGIN
81 DECLARE _totalVotes INT;
82 SELECT COUNT(Username) INTO _totalVotes FROM rpVot_Votes;
83 SELECT COUNT(v.Username) 'Count', c.Name 'Candidate', c.Party 'Party', c.Seat 'Seat', CEIL((COUNT(v.Username) / _totalVotes) * 100) 'Percentage'
84 FROM rpVot_Votes v, rpVot_Candidates c
85 WHERE v.CandidateID = c.ID
86 GROUP BY CandidateID
87 ORDER BY CandidateID ASC;
88END$$
89
90CREATE DEFINER=`db4458`@`localhost` PROCEDURE `CountVotesOrderByWining` () BEGIN
91 DECLARE _totalVotes INT;
92 SELECT COUNT(Username) INTO _totalVotes FROM rpVot_Votes;
93 SELECT COUNT(v.Username) 'Count', c.Name 'Candidate', c.Party 'Party', c.Seat 'Seat', CEIL((COUNT(v.Username)/_totalVotes)*100) 'Percentage'
94 FROM rpVot_Votes v, rpVot_Candidates c
95 WHERE v.CandidateID = c.ID
96 GROUP BY CandidateID
97 ORDER BY Count DESC;
98END$$
99
100CREATE DEFINER=`db4458`@`localhost` PROCEDURE `GenerateToken` (IN `_username` VARCHAR(100), IN `_email` VARCHAR(210), IN `_validTill` DATETIME, OUT `_uuidOut` CHAR(36)) BEGIN
101 DECLARE _id INT DEFAULT 0;
102 DECLARE _uuidExisted INT DEFAULT 0;
103 DECLARE _uuid CHAR(36);
104
105 DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
106
107 START TRANSACTION;
108 SET _uuid = '0';
109
110 SELECT id INTO _id FROM user_data WHERE email = _email AND username = _username;
111
112 IF(_id > 0) THEN
113 SELECT ID, UserID INTO _uuid, _uuidExisted FROM RPGPasswordTokens WHERE UserID = _id AND Used = 0 AND ValidTill > NOW();
114
115 IF _uuidExisted = 0 THEN
116 SET _uuid = UUID();
117
118 INSERT INTO RPGPasswordTokens (ID, UserID, RequestDate, ValidTill)
119 VALUES (_uuid, _id, NOW(), _validTill);
120 END IF;
121 ELSE
122 SET _uuid = '1';
123 END IF;
124 COMMIT;
125
126 SET _uuidOut = _uuid;
127END$$
128
129CREATE DEFINER=`db4458`@`localhost` PROCEDURE `GetCandidates` () BEGIN
130 SELECT ID, Name, Party, Seat FROM rpVot_Candidates ORDER BY ID ASC;
131END$$
132
133CREATE DEFINER=`db4458`@`localhost` PROCEDURE `GetPlayerSignature` (IN `_id` INT(11), OUT `_isSuccess` INT) BEGIN
134 DECLARE _output INT DEFAULT 0;
135
136 DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
137
138 START TRANSACTION;
139 SET _output = 0;
140
141 SELECT ID INTO _output FROM Frm_Signatures WHERE UserID = _id;
142 COMMIT;
143 SET _isSuccess = _output;
144END$$
145
146CREATE DEFINER=`db4458`@`localhost` PROCEDURE `GetToken` (IN `_id` CHAR(36)) BEGIN
147 SELECT ID, UserID, RequestDate, ValidTill FROM RPGPasswordTokens WHERE ID = _id AND Used = 0 AND ValidTill > NOW();
148END$$
149
150CREATE DEFINER=`db4458`@`localhost` PROCEDURE `GetVotes` () BEGIN
151 SELECT Serial, CandidateID, UserName FROM rpVot_Votes ORDER BY CandidateID ASC;
152END$$
153
154CREATE DEFINER=`db4458`@`localhost` PROCEDURE `HasVoted` (IN `_serial` CHAR(32), IN `_username` VARCHAR(255), OUT `_hasVoted` TINYINT(1) UNSIGNED) BEGIN
155 DECLARE _exists TINYINT(1);
156
157 SELECT COUNT(*) INTO _exists FROM rpVot_Votes WHERE Serial = _serial OR UserName = _username;
158
159 IF _exists > 0 THEN
160 SET _hasVoted = 1;
161 ELSE
162 SET _hasVoted = 0;
163 END IF;
164END$$
165
166CREATE DEFINER=`db4458`@`localhost` PROCEDURE `lockAccount` (IN `_id` INT(11), IN `_note` VARCHAR(255), OUT `_isSuccess` TINYINT(1) UNSIGNED) BEGIN
167 DECLARE _output TINYINT(1) UNSIGNED DEFAULT 0;
168 DECLARE _exists INT(11) DEFAULT 0;
169 DECLARE _email VARCHAR(255);
170 DECLARE _pwsalted VARCHAR(32);
171
172 DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
173
174 START TRANSACTION;
175 SET _output = 0;
176
177 SELECT id, pwsalted, email INTO _exists, _pwsalted, _email FROM user_data WHERE id=_id;
178
179 IF _exists > 0 THEN
180 IF _pwsalted <> 'Locked' THEN
181 UPDATE user_data SET pwsalted='Locked',email=_note,cache=CONCAT(_pwsalted,'^',_email) WHERE id=_id;
182 SET _output = 1;
183 END IF;
184 END IF;
185 COMMIT;
186 SET _isSuccess = _output;
187END$$
188
189CREATE DEFINER=`db4458`@`localhost` PROCEDURE `SetCandidate` (IN `_name` VARCHAR(25), IN `_party` VARCHAR(255), IN `_seat` VARCHAR(255), IN `_ordinal` TINYINT, IN `_isActive` TINYINT(1), OUT `_isSuccess` TINYINT(1) UNSIGNED) BEGIN
190 DECLARE _exists TINYINT DEFAULT 0;
191 DECLARE _operationDone TINYINT(1) UNSIGNED DEFAULT 0;
192 DECLARE _id TINYINT;
193
194 DECLARE EXIT HANDLER FOR NOT FOUND ROLLBACK;
195 DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;
196 DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
197
198 START TRANSACTION;
199 SET _operationDone = 0;
200
201 -- Checks if the record exists in the Table
202 SELECT COUNT(*) INTO _exists FROM rpVot_Candidates WHERE Name = _name;
203
204 -- If the record exists then it tries to update else insert
205 IF(_exists > 0) THEN
206 SELECT ID INTO _id FROM rpVot_Candidates WHERE Name = _name LIMIT 1;
207 UPDATE rpVot_Candidates
208 SET Party = _party,
209 Seat = _seat,
210 Ordinal = _ordinal,
211 IsActive = _isActive
212 WHERE ID = _id;
213
214 SET _operationDone = 2;
215 ELSE
216 INSERT INTO rpVot_Candidates(Name, Party, Seat, Ordinal, IsActive)
217 VALUES(_name, _party, _seat, _ordinal, _isActive);
218
219 SET _operationDone = 1;
220 END IF;
221 COMMIT;
222
223 SET _isSuccess = _operationDone;
224END$$
225
226CREATE DEFINER=`db4458`@`localhost` PROCEDURE `SetPassword` (IN `_userid` INT, IN `_password` VARCHAR(255), OUT `_isSuccess` TINYINT(1) UNSIGNED) BEGIN
227 DECLARE _id INT DEFAULT 0;
228 DECLARE _operationDone TINYINT(1) UNSIGNED DEFAULT 0;
229 DECLARE _username VARCHAR(100);
230
231 DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
232
233 START TRANSACTION;
234 SET _operationDone = 0;
235
236 SELECT id, username INTO _id, _username FROM user_data WHERE id = _userid;
237
238 IF(_id > 0) THEN
239 UPDATE user_data
240 SET pwsalted = (SELECT MD5(CONCAT(_username,'SAES',_password)))
241 WHERE id = _id;
242
243 SET _operationDone = 1;
244 ELSE
245 SET _operationDone = 2;
246 END IF;
247 COMMIT;
248
249 SET _isSuccess = _operationDone;
250END$$
251
252CREATE DEFINER=`db4458`@`localhost` PROCEDURE `SetPasswordWithToken` (IN `_token` CHAR(36), IN `_password` VARCHAR(255), OUT `_isSuccess` TINYINT(1) UNSIGNED) BEGIN
253 DECLARE _operationDone TINYINT(1) UNSIGNED DEFAULT 0;
254 DECLARE _userid INT;
255
256 DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
257
258 START TRANSACTION;
259 SET _operationDone = 0;
260
261 IF IsTokenStillValid(_token) = 1 THEN
262 SELECT UserID INTO _userid FROM RPGPasswordTokens WHERE ID = _token;
263 CALL SetPassword(_userid, _password, @pwout);
264 CALL SetTokenUsed(_token, @tmp);
265 SET _operationDone = @pwout;
266 END IF;
267 COMMIT;
268
269 SET _isSuccess = _operationDone;
270END$$
271
272CREATE DEFINER=`db4458`@`localhost` PROCEDURE `SetPlayerSignature` (IN `_id` INT(11), IN `_text` VARCHAR(25), OUT `_isSuccess` INT) BEGIN
273 DECLARE _output INT DEFAULT 0;
274 DECLARE _exists INT(11) DEFAULT 0;
275
276 DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
277
278 START TRANSACTION;
279 SET _output = 0;
280
281 SELECT ID INTO _exists FROM Frm_Signatures WHERE UserID = _id;
282
283 IF _exists > 0 THEN
284 UPDATE Frm_Signatures SET Type=_text WHERE ID = _exists;
285 SET _output = _exists;
286 ELSE
287 SET _exists = 0;
288 SELECT id INTO _exists FROM user_data WHERE id = _id;
289 IF _exists > 0 THEN
290 INSERT INTO Frm_Signatures (UserID, Type) VALUES (_id, _text);
291 SET _output = LAST_INSERT_ID();
292 END IF;
293 END IF;
294 COMMIT;
295 SET _isSuccess = _output;
296END$$
297
298CREATE DEFINER=`db4458`@`localhost` PROCEDURE `SetTokenUsed` (IN `_token` CHAR(36), OUT `_isSuccess` TINYINT(1) UNSIGNED) BEGIN
299 DECLARE _operationDone TINYINT(1) UNSIGNED DEFAULT 0;
300
301 DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
302
303 START TRANSACTION;
304 SET _operationDone = 0;
305
306 UPDATE RPGPasswordTokens
307 SET Used = 1
308 WHERE ID = _token;
309
310 SET _operationDone = 1;
311 COMMIT;
312
313 SET _isSuccess = _operationDone;
314END$$
315
316CREATE DEFINER=`db4458`@`localhost` PROCEDURE `SetVehiclePlateText` (IN `_id` INT(11), IN `_text` CHAR(8), OUT `_isSuccess` TINYINT(1) UNSIGNED) BEGIN
317 DECLARE _output TINYINT(1) UNSIGNED DEFAULT 0;
318 DECLARE _exists INT(11) DEFAULT 0;
319
320 DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
321
322 START TRANSACTION;
323 SET _output = 0;
324
325 SELECT uid INTO _exists FROM vehicle_data WHERE uid = _id;
326
327 IF _exists > 0 THEN
328 SET _exists = 0;
329 SELECT uid INTO _exists FROM vehicle_data WHERE platetext = _text;
330 IF _exists > 0 THEN
331 SET _output = 2;
332 ELSE
333 UPDATE vehicle_data SET platetext = _text WHERE uid = _id;
334 SET _output = 1;
335 END IF;
336 END IF;
337 COMMIT;
338 SET _isSuccess = _output;
339END$$
340
341CREATE DEFINER=`db4458`@`localhost` PROCEDURE `SetVote` (IN `_serial` CHAR(32), IN `_candidate` VARCHAR(25), IN `_username` VARCHAR(255), OUT `_isSuccess` TINYINT(1) UNSIGNED) BEGIN
342 DECLARE _exists TINYINT DEFAULT 0;
343 DECLARE _candidateid TINYINT DEFAULT 0;
344 DECLARE _operationDone TINYINT(1) UNSIGNED DEFAULT 0;
345
346 DECLARE EXIT HANDLER FOR NOT FOUND ROLLBACK;
347 DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;
348 DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
349
350 START TRANSACTION;
351 SET _operationDone = 0;
352 SET _isSuccess = _operationDone;
353 -- Checks if the record exists in the Table
354 SELECT COUNT(*) INTO _exists FROM rpVot_Votes WHERE Serial = _serial OR UserName = _username;
355
356 -- If the record exists then it returns 2
357 IF(_exists > 0) THEN
358 SET _operationDone = 2;
359 ELSE
360 SELECT ID INTO _candidateid FROM rpVot_Candidates WHERE Name = _candidate LIMIT 1;
361 IF(_candidateid > 0) THEN
362 INSERT INTO rpVot_Votes(Serial, CandidateID, UserName)
363 VALUES(_serial, _candidateid, _username);
364
365 SET _operationDone = 1;
366 END IF;
367 END IF;
368 COMMIT;
369
370 SET _isSuccess = _operationDone;
371END$$
372
373CREATE DEFINER=`db4458`@`localhost` PROCEDURE `unLockAccount` (IN `_id` INT(11), OUT `_isSuccess` TINYINT(1) UNSIGNED) BEGIN
374 DECLARE _output TINYINT(1) UNSIGNED DEFAULT 0;
375 DECLARE _exists INT(11) DEFAULT 0;
376 DECLARE _pwsalted CHAR(32);
377 DECLARE _email VARCHAR(255);
378
379 DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
380
381 START TRANSACTION;
382 SET _output = 0;
383
384 SELECT id, pwsalted INTO _exists, _pwsalted FROM user_data WHERE id=_id;
385
386 IF _exists > 0 THEN
387 IF _pwsalted = 'Locked' THEN
388 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(cache, '^', 1), '^', -1),SUBSTRING_INDEX(SUBSTRING_INDEX(cache, '^', 2), '^', -1) INTO _pwsalted, _email FROM user_data WHERE id=_id;
389 UPDATE user_data SET pwsalted=_pwsalted,email=_email,cache=NULL WHERE id=_id;
390 SET _output = 1;
391 END IF;
392 END IF;
393 COMMIT;
394 SET _isSuccess = _output;
395END$$
396
397
398CREATE DEFINER=`db4458`@`localhost` FUNCTION `IsTokenStillValid` (`_token` CHAR(36)) RETURNS TINYINT(1) BEGIN
399 DECLARE _validTill DATETIME;
400 DECLARE _isValid TINYINT(1) DEFAULT 0;
401
402 SELECT ValidTill INTO _validTill FROM RPGPasswordTokens WHERE ID = _token AND Used = 0;
403
404 IF(_validTill > NOW()) THEN
405 SET _isValid = 1;
406 END IF;
407
408 RETURN _isValid;
409END$$
410
411DELIMITER ;