#Qui a badge imputabilité
select * from user, pv_center, pv_center_badge
where pv_center.user_id = user.id and pv_center.game_session_id = 3
and pv_center_badge.center_id = pv_center.id and pv_center_badge.badge_id=2
and pv_center_badge.status=1;


#Score d'engagement
select user.email, user.full_name, scores.avatar_xp, scores.description_xp, scores.join_courses_xp, scores.notions_xp,
scores.quizzes_xp, scores.evaluations_xp, scores.discussions_xp, scores.businessgame_xp, scores.total_score,
scores.number_of_courses,scores.number_of_quizzes,scores.number_of_questions
from user, scores,training_session_participant  where
scores.user_id=user.id and
training_session_participant.training_session_id=179 and
training_session_participant.participant_id = user.id and
scores.user_id=training_session_participant.participant_id;

#Score total
select user.email as Email, user.full_name as Name, pv_center.id as CenterId, sum(pv_decision.score1) as SCORE1, sum(pv_decision.score2) as SCORE2, sum(pv_decision.score3) as SCORE3
from user, pv_center, pv_decision, pv_center_decision
where  user.id = pv_center.user_id and
pv_decision.id = pv_center_decision.decision_id and pv_center.id = pv_center_decision.center_id and pv_center.game_session_id = 3
group by user.email, user.full_name, pv_center.id;

#Score par jour
select user.email as Email, user.full_name as Name, pv_center.id as CenterId, pv_decision.day_id as DAY,
sum(pv_decision.score1) as SCORE1, sum(pv_decision.score2) as SCORE2, sum(pv_decision.score3) as SCORE3,
sum(pv_decision.score1 + pv_decision.score2 + pv_decision.score3) as TOTALSCORE,
pv_center_day.finish_date as ENDDATE, pv_center_day.stars as STARS
from user, pv_center, pv_decision, pv_center_decision, pv_center_day where user.id = pv_center.user_id
and pv_decision.id = pv_center_decision.decision_id and pv_center.id = pv_center_decision.center_id and
pv_center_day.center_id = pv_center.id and pv_center_day.day_id = pv_decision.day_id
and pv_center.game_session_id = 3
group by user.email, user.full_name, pv_center.id, pv_decision.day_id,
pv_center_day.finish_date, pv_center_day.stars;

