misc_queries

/*Total number of exercises in a course (I have the course ID) */



/*Step 1>Get user id by querying the auth_user table and searching by username/first name/last name/email.*/
Select * from auth_user
/*Step 2>Get all courses that the user is enrolled into.*/
Select * from student_courseenrollment where user_id = 1903

Select * from student_courseaccessrole
/* Role of a user per course */
Select au.first_name, au.last_name, au.email, sc.role 
from student_courseaccessrole as sc 
left join auth_user as au
on au.id = sc.user_id
WHERE course_id = "JEFULP/CFI/Feb2015"
ORDER BY role

/*Info about a person, including their username, activation link, most recent login, activation date, enrollment date, and Company/Client name (I provide email address)*/
/*Step 1>Get user id by querying the auth_user table and searching by username/first name/last name/email*/
Select * from auth_user
/*Step 2: get company info based on user id */
Select *
from organizations_organization_users as org
left join auth_user as au on au.id = org.user_id
left join organizations_organization as oo on org.organization_id = oo.id
where au.id = 5743

/*Insert user to organization:*/
INSERT INTO organizations_organization_users(organization_id, user_id) values (13, 645)

/*Another query similar to daily stats query,but includes everyone and their roles (observers/TAs/regular)*/
SELECT au.id, au.username, au.last_name, au.email, au.last_login, au.date_joined, sc.role, sc.org, 
sc.course_id
FROM student_courseaccessrole as sc 
LEFT JOIN auth_user as au
ON au.id = sc.user_id
WHERE sc.course_id = "AcademyMMP/STRAT/Sept2014"


/*TA REVIEW */
SELECT pwr.id,pwr.workgroup_id, pwr.content_id, SM.id, SM.module_id,
AU.username,SM.grade AS "final_score", 
pwr.question,pwr.answer as "mean_scores"   
FROM auth_user AU
LEFT JOIN courseware_studentmodule as SM ON SM.student_id = AU.id
LEFT JOIN projects_workgroup_users as pwu ON pwu.user_id = SM.student_id
LEFT JOIN projects_workgroupreview as pwr ON pwu.workgroup_id = pwr.workgroup_id
WHERE SM.module_type = 'group-project' 
AND SM.course_id='AcademyMMP/MAPS/Nov2014'
AND pwr.content_id LIKE '%AcademyMMP/MAPS%'
ORDER BY SM.grade, AU.username, pwr.question

/*PEER REVIEW*/
SELECT pwr.id,pwr.workgroup_id, pwr.content_id, SM.id, SM.module_id,
AU.username,SM.grade AS "final_score", 
pwr.question,pwr.answer as "mean_scores"   
FROM auth_user AU
LEFT JOIN courseware_studentmodule as SM ON SM.student_id = AU.id
LEFT JOIN projects_workgroup_users as pwu ON pwu.user_id = SM.student_id
LEFT JOIN projects_workgrouppeerreview as pwr ON pwu.workgroup_id = pwr.workgroup_id
WHERE SM.module_type = 'group-project' 
AND SM.course_id='AcademyMMP/MAPS/Nov2014'
AND pwr.content_id LIKE '%AcademyMMP/MAPS%'
ORDER BY SM.grade, AU.username, pwr.question