Voting_system/test.sql at main · mohamedhesham124/Voting_system · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
USE voting_system;

-- test user - country
/*
SELECT user_id, co.code
FROM Districts D
JOIN Cities c on c.id = D.city_id
JOIN States_Governorates s on s.id = c.states_governorates_id
JOIN Countries co on co.code = s.country_code
JOIN locates u on u.district_id = D.id;
*/
/*

SELECT
  vote_id,
  CONVERT(
    AES_DECRYPT(
        FROM_BASE64(encrypted_candidate),
        UNHEX(SHA2('b7b_nagat', 256)),
        iv
        )USING utf8mb4
  ) AS decrypted_candidate
FROM encrypted_candidates;


set @iv = UNHEX('000102030405060708090A0B0C0D0E0F');
set @key_str = UNHEX(SHA2('b7b_nagat', 256));
CALL EncryptData(CAST(7 AS CHAR), @key_str, @encrypted_text, @iv);
SELECT @encrypted_text AS 'Encrypted', TO_BASE64(@iv) AS 'IV (base64)';

*/

-- call calculate_election_results_per_group(1, UNHEX(SHA2('b7b_nagat', 256)));

/*
set @iv = UNHEX('000102030405060708090A0B0C0D0E0F');

CALL p_EncryptData(CAST(12 AS CHAR), UNHEX(SHA2('b7b_nagat', 256)), @encrypted_text, UNHEX('000102030405060708090A0B0C0D0E0F'));
SELECT @encrypted_text AS 'Encrypted', TO_BASE64(@iv) AS 'IV (base64)';

CALL p_DecryptData(@encrypted_text, UNHEX(SHA2('b7b_nagat', 256)), UNHEX('000102030405060708090A0B0C0D0E0F'), @decrypted_text);
SELECT @decrypted_text AS 'Decrypted Value';
*/


call p_get_user_group_id(26, 1, @group_id);
SELECT @group_id;


call p_calculate_election_results_per_group(1, 'b7b_nagat');
call p_calculate_final_election_results(1);

call p_calculate_election_results_per_group(2,'b7b_nagat');
call p_calculate_final_election_results(2);
call p_calculate_election_results_per_group(3, 'b7b_nagat');
call p_calculate_final_election_results(3);

call p_calculate_election_results_per_group(4, 'b7b_nagat');
call p_calculate_final_election_results(4);


SELECT erg.id, eg.election_id, erg.group_id, erg.candidate_id, erg.vote_count, erg.percentage
FROM ElectionResultsPerGroup erg
JOIN ElectionGroups eg ON erg.group_id = eg.id
where eg.election_id = 1;

SELECT erg.id, eg.election_id, erg.group_id, erg.candidate_id, erg.vote_count, erg.percentage
FROM ElectionResultsPerGroup erg
JOIN ElectionGroups eg ON erg.group_id = eg.id
where eg.election_id = 2;

SELECT erg.id, eg.election_id, erg.group_id, erg.candidate_id, erg.vote_count, erg.percentage
FROM ElectionResultsPerGroup erg
JOIN ElectionGroups eg ON erg.group_id = eg.id
where eg.election_id = 3;

SELECT erg.id, eg.election_id, erg.group_id, erg.candidate_id, erg.vote_count, erg.percentage
FROM ElectionResultsPerGroup erg
JOIN ElectionGroups eg ON erg.group_id = eg.id
where eg.election_id = 4;
SELECT * FROM ElectionResults;

call p_get_election_results(1);
call p_get_election_results(2);

call p_get_election_results(3);
call p_get_election_results(4);