-
Notifications
You must be signed in to change notification settings - Fork 0
/
exploit.sql
87 lines (85 loc) · 4.38 KB
/
exploit.sql
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
-- Rapprochement des revues recues et données
SELECT DISTINCT
user.id,
firstname,
lastname,
received_review.comment AS `Received comment`,
CASE received_review.comparison
WHEN 'worse' THEN 'According to your peer, your delivery is worth ⭐'
WHEN 'similar' THEN 'According to your peer, your delivery is worth ⭐⭐'
WHEN 'better' THEN 'According to your peer, your delivery is worth ⭐⭐⭐'
END AS `Received comparison (compared to your pair)`,
CASE given_review.comparison
WHEN 'worse' THEN 'According to yourself, your delivery is worth ⭐⭐⭐'
WHEN 'similar' THEN 'According to yourself, your delivery is worth ⭐⭐'
WHEN 'better' THEN 'According to yourself, your delivery is worth ⭐'
END AS `Given comparison (compared to your pair)`,
given_review.comment AS `Given comment`,
(
SELECT GROUP_CONCAT(CONCAT(pair_user.firstname, ' ', pair_user.lastname), ', ')
FROM `user` AS pair_user
INNER JOIN submission_users_user AS pair_submission_users_user ON pair_submission_users_user.userId = pair_user.id
WHERE pair_submission_users_user.submissionId = received_review.reviewerSubmissionId
) AS 'Your pair',
CASE TRUE
WHEN received_review.comparison IS NULL AND given_review.comparison IS NULL THEN 0
WHEN received_review.comparison IS NULL OR given_review.comparison IS NULL THEN 1
WHEN received_review.comparison = 'worse' AND given_review.comparison = 'worse' THEN 0
WHEN received_review.comparison = 'worse' AND given_review.comparison = 'similar' THEN 0.5
WHEN received_review.comparison = 'worse' AND given_review.comparison = 'better' THEN 1
WHEN received_review.comparison = 'similar' AND given_review.comparison = 'worse' THEN 0.5
WHEN received_review.comparison = 'similar' AND given_review.comparison = 'similar' THEN 1
WHEN received_review.comparison = 'similar' AND given_review.comparison = 'better' THEN 0.5
WHEN received_review.comparison = 'better' AND given_review.comparison = 'worse' THEN 1
WHEN received_review.comparison = 'better' AND given_review.comparison = 'similar' THEN 0.5
WHEN received_review.comparison = 'better' AND given_review.comparison = 'better' THEN 0
END AS `Reliability score`,
CASE received_review.comparison
WHEN NULL THEN 0.5
WHEN 'worse' THEN 0
WHEN 'similar' THEN 0.25
WHEN 'better' THEN 0.5
END + CASE given_review.comparison
WHEN NULL THEN 0
WHEN 'worse' THEN 0.5
WHEN 'similar' THEN 0.25
WHEN 'better' THEN 0
END AS `Comparison score`
FROM `user`
INNER JOIN submission_users_user ON submission_users_user.userId = user.id
INNER JOIN submission ON submission.id = submission_users_user.submissionId
INNER JOIN review AS received_review ON received_review.reviewedSubmissionId = submission.id
INNER JOIN review AS given_review ON given_review.reviewerSubmissionId = submission.id
WHERE
(user.firstname != 'Thomas' || user.lastname != 'VEILLARD') AND
submission.projectId IN (5, 6, 7, 8, 9, 10)
ORDER BY id ASC
-- Synthèse des travaux rendus par exercice
SELECT
user.id,
user.firstname,
user.lastname,
GROUP_CONCAT(IF(submission.projectId IN (5, 6), CONCAT('https://peer.thomas-veillard.fr/public/submissions/', submission.fileToken), '') SEPARATOR '') AS `TP 2 Part 1`,
GROUP_CONCAT(IF(submission.projectId IN (7, 8), CONCAT('https://peer.thomas-veillard.fr/public/submissions/', submission.fileToken), '') SEPARATOR '') AS `TP 2 Part 2`,
GROUP_CONCAT(IF(submission.projectId IN (9, 10), CONCAT('https://peer.thomas-veillard.fr/public/submissions/', submission.fileToken), '') SEPARATOR '') AS `TP 2 Part 3`
FROM `user`
LEFT JOIN submission_users_user ON submission_users_user.userId = user.id
LEFT JOIN submission ON submission.id = submission_users_user.submissionId
WHERE user.firstname != 'Thomas' || user.lastname != 'VEILLARD'
GROUP BY user.id
ORDER BY id ASC
-- Synthèse des revues données par exercice
SELECT
user.id,
firstname,
lastname,
SUM(IF(submission.projectId IN (5, 6), 1, 0)) AS `TP 2 Part 1`,
SUM(IF(submission.projectId IN (7, 8), 1, 0)) AS `TP 2 Part 2`,
SUM(IF(submission.projectId IN (9, 10), 1, 0)) AS `TP 2 Part 3`
FROM `user`
LEFT JOIN submission_users_user ON submission_users_user.userId = user.id
LEFT JOIN submission ON submission.id = submission_users_user.submissionId
LEFT JOIN review ON review.reviewerSubmissionId = submission.id
WHERE user.firstname != 'Thomas' || user.lastname != 'VEILLARD'
GROUP BY user.id
ORDER BY id ASC