SELECT
*
FROM prefix_course AS c,prefix_course_categories cc, prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE cc.id = c.category
AND ra.roleid = 5
AND ctx.instanceid = c.id
ORDER BY u.lastname, u.firstname
ASC


SELECT
*
FROM prefix_context AS c,prefix_course_categories cc, prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE cc.id = c.category
AND ra.roleid = 5
AND ctx.instanceid = c.id
ORDER BY u.lastname, u.firstname
ASC


-- ALUMNOS POR GRUPO ESCOLARIZADO

SELECT
u.username,
u.firstname,
u.lastname,
u.email,
u.country,
u.city,
c.shortname,
c.fullname,
cc.name Carrera,
DATEDIFF(NOW(), FROM_UNIXTIME(u.lastaccess))

     AS 'ULTIMO ACCESO'
FROM prefix_course AS c,prefix_course_categories cc, prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE cc.id = c.category
AND ra.roleid = 5
AND ctx.instanceid = c.id
AND cc.path like "/42/45%"
 ORDER BY u.lastname, u.firstname
ASC

-- ALUMNOS POR GRUPO PREPA

SELECT
u.username,
u.firstname,
u.lastname,
u.email,
u.country,
u.city,
c.shortname,
c.fullname,
cc.name Carrera,
DATEDIFF(NOW(), FROM_UNIXTIME(u.lastaccess))

     AS 'ULTIMO ACCESO'
FROM prefix_course AS c,prefix_course_categories cc, prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE cc.id = c.category
AND ra.roleid = 5
AND ctx.instanceid = c.id
AND cc.path like "/42/43%"
 ORDER BY u.lastname, u.firstname
ASC

-- CURSOS PREPA

SELECT prefix_course. * , prefix_course_categories. *
FROM prefix_course, prefix_course_categories
WHERE prefix_course.category = prefix_course_categories.id
AND (
prefix_course_categories.path LIKE '/42/43%'
)

-- sep ALUMNOS POR GRUPO ESCOLARIZADO

SELECT
u.username,
u.firstname,
u.lastname,
u.email,
u.country,
u.city,
c.shortname,
c.fullname,
cc.name Carrera,
DATEDIFF(NOW(), FROM_UNIXTIME(u.lastaccess))

     AS 'ULTIMO ACCESO'
FROM prefix_course AS c,prefix_course_categories cc, prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE cc.id = c.category
AND ra.roleid = 5
AND ctx.instanceid = c.id
AND cc.path like "/42/65%"
 ORDER BY u.lastname, u.firstname
ASC


-- sep PROFESORES ESCOLARIZADO

SELECT
	cc.name AS CARRERA,
	CONCAT("<a target='_blank'  href='http://cloud.aguascalientes.tecnm.mx/moodle/course/view.php",CHAR(63),"id=",c.id,"'>", c.fullname,"</a>") MATERIA,
	u.username AS USUARIO,
	u.email,
	CONCAT("<a target='_blank' href='http://cloud.aguascalientes.tecnm.mx/moodle/report/log/user.php",CHAR(63),"id=",u.id,"&course=",c.id,"&mode=all'>",u.lastname," ",u.firstname,"</a>") NOMBRE,

	(SELECT	DATE_FORMAT(FROM_UNIXTIME(MAX(log.timecreated)) , '%d/%m/%Y %H:%i:%s')
	 FROM prefix_logstore_standard_log log
	 WHERE log.userid = u.id AND log.courseid = c.id
	) AS 'ULTIMO ACCESO',

	(SELECT	DATEDIFF(NOW(), FROM_UNIXTIME(MAX(log.timecreated)))

		 FROM prefix_logstore_standard_log log
		 WHERE log.userid = u.id AND log.courseid = c.id
	) AS 'Dias desde ultimo acceso',

	(SELECT	count(distinct(DATE_FORMAT(FROM_UNIXTIME(log.timecreated) , '%d/%m/%Y')))
		 FROM prefix_logstore_standard_log log
		 WHERE log.userid = u.id AND log.courseid = c.id

	) AS 'dias totales que ha ingresado',

	(SELECT Count( raa.userid ) AS Users FROM prefix_role_assignments AS raa
	JOIN prefix_context AS ctx2 ON raa.contextid = ctx2.id
	WHERE raa.roleid = 5 AND ctx2.instanceid = c.id) AS ALUMNOS

FROM
	prefix_user u,
	prefix_course c,
	prefix_course_categories cc,
	prefix_role_assignments ra,
	prefix_context ctx

WHERE 	c.category	= cc.id
    AND cc.path 	like "/42/65%"
    AND ra.contextid 	= ctx.id
    AND u.id 		= ra.userid
    AND ra.roleid 	= 3
    AND ctx.instanceid 	= c.id

ORDER BY 7 DESC,1,2


--Reporte general
SELECT
 *
FROM prefix_course AS c,prefix_course_categories cc, prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE cc.id = c.category
AND ra.roleid = 5
AND ctx.instanceid = c.id
 ORDER BY u.lastname, u.firstname
ASC

SELECT
 *
FROM
prefix_context
WHERE id > 820281






SELECT
u.id,
u.username,
u.firstname,
u.lastname,
u.email,
u.country,
u.city,
c.id AS courseid,
c.shortname,
c.fullname,
cc.id AS course_categories_id,
cc.name AS Carrera,
cc.path
FROM prefix_course AS c,prefix_course_categories cc, prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE cc.id = c.category
AND ra.roleid = 5
AND ctx.instanceid = c.id
AND u.id > 5663
ORDER BY u.id
ASC



SELECT * FROM prefix_user; -- u

SELECT * FROM prefix_course;
SELECT * FROM prefix_course_categories;
SELECT * FROM prefix_role_assignments;
SELECT * FROM prefix_context;
SELECT * FROM prefix_logstore_standard_log;
SELECT * FROM prefix_context;









