# NÃO INICIADO
# PEGA USUÁRIOS QUE ESTÃO MATRICULADOS, MAS NÃO CONCLUIRAM NENHUMA LIÇÃO

SELECT 
	c.id AS idCurso,
	REPLACE(c.title,',','') AS Curso,
    c.duracao_curso AS 'Carga Horária',
	cl.num_enterprise AS Empresa,
	cl.tip_colaborador AS tipoColaborador,
    concat(concat(u.first_name,' '), u.last_name) AS Nome,
	cl.mat_colaborador AS Matricula,
    u.email AS 'E-mail',	
	if (cl.num_enterprise = 3, 
		if(cl.tip_colaborador = 1,
			'Cooperado',      
			if(cl.mat_colaborador > 6000000,
				'Terceiro', 
				'Cooperado'
			)
		),        
		if(cl.tip_colaborador = 1,
			'Empregado',
			if(cl.mat_colaborador > 6000000,
				'Terceiro', 
				'Empregado'
			)
		)
	) AS 'TipoVinculo',
	REPLACE(ma.name,',','') AS 'Gestao',
	REPLACE(s.name,',','') AS 'Setor',
	REPLACE(jr.name,',','') AS Cargo,
	(SELECT name FROM user_status us WHERE us.id = u.user_status_id) AS Situacao,
	'' AS 'Data Conclusao',
	'' AS 'Data de Certificacao',
	'' AS Tentativas,
	'' AS Nota1,
	'' AS Nota2,
	'' AS Nota3,
	'Não Iniciado' AS 'Status'
FROM inscription i 
	INNER JOIN user u 
    	ON u.id = i.user_id
    INNER JOIN client cl
    	ON cl.id = u.id
	LEFT JOIN management ma 
		ON u.management_id = ma.id
    INNER JOIN course c 
    	ON i.course_id = c.id
    LEFT JOIN sector s 
        ON u.sector_id = s.id
    LEFT JOIN job_role jr 
        ON u.job_role_id = jr.id
    LEFT JOIN inscription_lesson il
    	ON il.inscription_id = i.id
    
WHERE 
	i.dt_creation <  '2019-01-01 00:00:00' AND
	i.id not in (SELECT inscription_id  FROM `inscription_lesson`) AND
	u.id NOT IN (SELECT ue.user_id from user_evaluation ue WHERE ue.course_id = c.id) 
    GROUP BY u.id, c.id

# EM ANDAMENTO
# PEGA USUÁRIOS QUE ESTÃO MATRICULADOS, QUE JÁ CONCLUÍRAM PELO MENOS UMA LIÇÃO, NÃO FINALIZARAM TODAS AS LIÇÕES E NÃO REALIZARAM PROVA

UNION ALL

SELECT 
	c.id AS idCurso,
	REPLACE(c.title,',','') AS Curso,
    c.duracao_curso AS 'Carga Horária',
	cl.num_enterprise AS Empresa,
	cl.tip_colaborador AS tipoColaborador,
    concat(concat(u.first_name,' '), u.last_name) AS Nome,
	cl.mat_colaborador AS Matricula,
    u.email AS 'E-mail',	
	if (cl.num_enterprise = 3, 
		if(cl.tip_colaborador = 1,
			'Cooperado',      
			if(cl.mat_colaborador > 6000000,
				'Terceiro', 
				'Cooperado'
			)
		),        
		if(cl.tip_colaborador = 1,
			'Empregado',
			if(cl.mat_colaborador > 6000000,
				'Terceiro', 
				'Empregado'
			)
		)
	) AS 'TipoVinculo',
	REPLACE(ma.name,',','') AS 'Gestao',
	REPLACE(s.name,',','') AS 'Setor',
	REPLACE(jr.name,',','') AS Cargo,
	(SELECT name FROM user_status us WHERE us.id = u.user_status_id) AS Situacao,
	'' AS 'Data Conclusao',
	'' AS 'Data de Certificacao',
	'' AS Tentativas,
	'' AS Nota1,
	'' AS Nota2,
	'' AS Nota3,
	'Em andamento' AS 'Status'
FROM inscription i 
	INNER JOIN user u 
    	ON u.id = i.user_id
    INNER JOIN client cl
    	ON cl.id = u.id
	LEFT JOIN management ma 
		ON u.management_id = ma.id
    INNER JOIN course c 
    	ON i.course_id = c.id
    LEFT JOIN sector s 
        ON u.sector_id = s.id
    LEFT JOIN job_role jr 
        ON u.job_role_id = jr.id
    LEFT JOIN inscription_lesson il
    	ON il.inscription_id = i.id
WHERE 
	i.dt_creation <  '2019-01-01 00:00:00' AND
	i.id IN (SELECT inscription_id  FROM `inscription_lesson`) AND 
	((SELECT COUNT(1) FROM inscription_lesson AS iil WHERE iil.inscription_id = i.id) < (SELECT COUNT(1) FROM lesson l WHERE l.module_id IN (SELECT m.id FROM module m WHERE m.course_id = c.id)) OR
    (SELECT COUNT(1) FROM inscription_lesson AS iil WHERE iil.finished IS NULL AND iil.inscription_id = i.id) > 0) AND
     u.id NOT IN (SELECT ue.user_id from user_evaluation ue WHERE ue.course_id = c.id AND ue.user_id = u.id) 
    GROUP BY u.id, c.id
	
UNION ALL

# CONCLUÍDO
# PEGA USUÁRIOS QUE FINALIZARAM TODAS AS LIÇÕES, MAS NÃO REALIZARAM PROVA

SELECT 
	c.id AS idCurso,
	REPLACE(c.title,',','') AS Curso,
    c.duracao_curso AS 'Carga Horária',
	cl.num_enterprise AS Empresa,
	cl.tip_colaborador AS tipoColaborador,
    concat(concat(u.first_name,' '), u.last_name) AS Nome,
	cl.mat_colaborador AS Matricula,
    u.email AS 'E-mail',	
	if (cl.num_enterprise = 3, 
		if(cl.tip_colaborador = 1,
			'Cooperado',      
			if(cl.mat_colaborador > 6000000,
				'Terceiro', 
				'Cooperado'
			)
		),        
		if(cl.tip_colaborador = 1,
			'Empregado',
			if(cl.mat_colaborador > 6000000,
				'Terceiro', 
				'Empregado'
			)
		)
	) AS 'TipoVinculo',
	REPLACE(ma.name,',','') AS 'Gestao',
	REPLACE(s.name,',','') AS 'Setor',
	REPLACE(jr.name,',','') AS Cargo,
	(SELECT name FROM user_status us WHERE us.id = u.user_status_id) AS Situacao,
	(SELECT MAX(iil.finished) FROM inscription_lesson AS iil WHERE iil.inscription_id = i.id) AS 'Data Conclusao',
	'' AS 'Data de Certificacao',
	'' AS Tentativas,
	'' AS Nota1,
	'' AS Nota2,
	'' AS Nota3,
	'Concluído' AS 'Status'
FROM inscription i 
	INNER JOIN user u 
    	ON u.id = i.user_id
    INNER JOIN client cl
    	ON cl.id = u.id
	LEFT JOIN management ma 
		ON u.management_id = ma.id
    INNER JOIN course c 
    	ON i.course_id = c.id
    LEFT JOIN sector s 
        ON u.sector_id = s.id
    LEFT JOIN job_role jr 
        ON u.job_role_id = jr.id
    LEFT JOIN inscription_lesson il
    	ON il.inscription_id = i.id
WHERE  
	i.dt_creation <  '2019-01-01 00:00:00' AND
	i.id IN (SELECT inscription_id  FROM `inscription_lesson`) AND 
	u.id NOT IN (SELECT ue.user_id from user_evaluation ue WHERE ue.course_id = c.id AND ue.user_id = u.id) AND
	(SELECT COUNT(1) FROM inscription_lesson AS iil WHERE iil.inscription_id = i.id) = (SELECT COUNT(1) FROM lesson l WHERE l.module_id IN (SELECT m.id FROM module m WHERE m.course_id = c.id)) AND
    (SELECT COUNT(1) FROM inscription_lesson AS iil WHERE iil.finished IS NULL AND iil.inscription_id = i.id) = 0
    GROUP BY u.id, c.id
    

# APROVADO/REPROVADO
# RWECUPERA USUÁRIOS QUE REALIZARAM PROVA, E FORAM APROVADOS OU REPROVADOS

UNION ALL


# RECUPERA CURSOS QUE POSSUEM PROVA OU NÃO

SELECT
	c.id AS idCurso,
	REPLACE(c.title,',','') AS Curso,
    c.duracao_curso AS 'Carga Horária',
	cl.num_enterprise AS Empresa,
	cl.tip_colaborador AS tipoColaborador,
    concat(concat(u.first_name,' '), u.last_name) AS Nome,
	cl.mat_colaborador AS Matricula,
    u.email AS 'E-mail',	
	if (cl.num_enterprise = 3, 
		if(cl.tip_colaborador = 1,
			'Cooperado',      
			if(cl.mat_colaborador > 6000000,
				'Terceiro', 
				'Cooperado'
			)
		),        
		if(cl.tip_colaborador = 1,
			'Empregado',
			if(cl.mat_colaborador > 6000000,
				'Terceiro', 
				'Empregado'
			)
		)
	) AS 'TipoVinculo',
	REPLACE(ma.name,',','') AS 'Gestao',
	REPLACE(s.name,',','') AS 'Setor',
	REPLACE(jr.name,',','') AS Cargo,
	(SELECT name FROM user_status us WHERE us.id = u.user_status_id) AS Situacao,
	(CASE 
	WHEN (SELECT MAX(iil.finished) FROM inscription_lesson AS iil WHERE iil.inscription_id = i.id) IS NULL 
    	THEN (SELECT uee.dt_creation FROM user_evaluation uee WHERE uee.user_id = u.id AND uee.course_id = c.id ORDER BY uee.dt_creation LIMIT 1) 
    ELSE (SELECT MAX(iil.finished) FROM inscription_lesson AS iil WHERE iil.inscription_id = i.id)
	END) AS 'Data Conclusao',
	(SELECT uee.dt_creation FROM user_evaluation uee WHERE uee.user_id = u.id AND uee.grade >= 70 AND uee.course_id = c.id ORDER BY uee.dt_creation LIMIT 1) AS 'Data de Certificacao',
	(SELECT COUNT(1) FROM user_evaluation AS uee WHERE uee.user_id = u.id AND uee.course_id = c.id) AS 'Tentativas',
    (SELECT uee.grade AS grade from user_evaluation uee WHERE uee.user_id = u.id AND uee.course_id = c.id ORDER by uee.grade DESC LIMIT   1) AS Nota1,
	(SELECT uee.grade AS grade from user_evaluation uee WHERE uee.user_id = u.id AND uee.course_id = c.id ORDER by uee.grade DESC LIMIT 1,1) AS Nota2,
	(SELECT uee.grade AS grade from user_evaluation uee WHERE uee.user_id = u.id AND uee.course_id = c.id ORDER by uee.grade DESC LIMIT 2,1) AS Nota3,
    IF((SELECT uee.grade AS grade from user_evaluation uee WHERE uee.user_id = u.id AND uee.course_id = c.id ORDER by uee.grade DESC LIMIT   1) >= 70,'Aprovado','Reprovado') AS 'Status' # CURSO SEM PROVA A NOTA É SEMPRE 100% E O STATUS APROVADO
FROM user_evaluation ue
INNER JOIN user u 
	ON ue.user_id = u.id
INNER JOIN client cl 
	ON cl.id = u.id
LEFT JOIN course c 
	ON ue.course_id = c.id
LEFT JOIN sector s 
	ON u.sector_id = s.id
LEFT JOIN management ma 
	ON u.management_id = ma.id
LEFT JOIN job_role jr 
	On u.job_role_id = jr.id
INNER JOIN inscription i 
	ON i.user_id = u.id
	AND i.course_id = c.id
WHERE 
	i.dt_creation <  '2019-01-01 00:00:00' AND
	(SELECT COUNT(1) FROM user_evaluation uee WHERE uee.user_id = u.id AND uee.course_id = c.id) >= 1 AND
	(SELECT COUNT(1) FROM evaluation e INNER JOIN module m ON m.id = e.module_id WHERE m.course_id=c.id) <= 1 
GROUP BY ue.user_id, ue.course_id


UNION ALL


# RECUPERA CURSOS QUE POSSUEM MAIS DE UMA PROVA

SELECT
	c.id AS idCurso,
	REPLACE(c.title,',','') AS Curso,
    c.duracao_curso AS 'Carga Horária',
	cl.num_enterprise AS Empresa,
	cl.tip_colaborador AS tipoColaborador,
    concat(concat(u.first_name,' '), u.last_name) AS Nome,
	cl.mat_colaborador AS Matricula,
    u.email AS 'E-mail',	
	if (cl.num_enterprise = 3, 
		if(cl.tip_colaborador = 1,
			'Cooperado',      
			if(cl.mat_colaborador > 6000000,
				'Terceiro', 
				'Cooperado'
			)
		),        
		if(cl.tip_colaborador = 1,
			'Empregado',
			if(cl.mat_colaborador > 6000000,
				'Terceiro', 
				'Empregado'
			)
		)
	) AS 'TipoVinculo',
	REPLACE(ma.name,',','') AS 'Gestao',
	REPLACE(s.name,',','') AS 'Setor',
	REPLACE(jr.name,',','') AS Cargo,
	(SELECT name FROM user_status us WHERE us.id = u.user_status_id) AS Situacao,
	(CASE 
	WHEN (SELECT MAX(iil.finished) FROM inscription_lesson AS iil WHERE iil.inscription_id = i.id) IS NULL 
    	THEN (SELECT uee.dt_creation FROM user_evaluation uee WHERE uee.user_id = u.id AND uee.course_id = c.id ORDER BY uee.dt_creation LIMIT 1) 
    ELSE (SELECT MAX(iil.finished) FROM inscription_lesson AS iil WHERE iil.inscription_id = i.id)
	END) AS 'Data Conclusao',
	(SELECT MAX(data) FROM (SELECT MAX(uee.dt_creation) AS data, uee.user_id AS uee_user, uee.evaluation_id AS uee_ev, uee.course_id AS uee_course
									  FROM user_evaluation uee
									  WHERE uee.evaluation_id IS NOT NULL GROUP BY uee.evaluation_id, uee.user_id) AS tbl WHERE uee_user = u.id AND uee_course = c.id)
    AS 'Data de Certificacao',
	(SELECT COUNT(1) AS nav FROM user_evaluation uee WHERE uee.user_id = u.id AND uee.course_id = c.id GROUP BY uee.evaluation_id ORDER BY nav DESC LIMIT 1) AS 'Tentativas',
    (SELECT SUM(grade) DIV (SELECT COUNT(1) FROM evaluation e INNER JOIN module m ON m.id = e.module_id WHERE m.course_id=c.id) FROM (SELECT MAX(uee.grade) AS grade, uee.user_id AS uee_user, uee.evaluation_id AS uee_ev, uee.course_id AS uee_course
									  FROM user_evaluation uee
									  WHERE uee.evaluation_id IS NOT NULL GROUP BY uee.evaluation_id, uee.user_id) AS tbl WHERE uee_user = u.id AND uee_course = c.id
		) AS Nota1,
	'' AS Nota2,
	'' AS Nota3,
    IF(
		(SELECT SUM(grade) DIV (SELECT COUNT(1) FROM evaluation e INNER JOIN module m ON m.id = e.module_id WHERE m.course_id=c.id) FROM (SELECT MAX(uee.grade) AS grade, uee.user_id AS uee_user, uee.evaluation_id AS uee_ev, uee.course_id AS uee_course
									  FROM user_evaluation uee
									  WHERE uee.evaluation_id IS NOT NULL GROUP BY uee.evaluation_id, uee.user_id) AS tbl WHERE uee_user = u.id AND uee_course = c.id
		) >= 70,'Aprovado','Reprovado') AS 'Status' # CURSO SEM PROVA A NOTA É SEMPRE 100% E O STATUS APROVADO
FROM user_evaluation ue
INNER JOIN user u 
	ON ue.user_id = u.id
INNER JOIN client cl 
	ON cl.id = u.id
LEFT JOIN course c 
	ON ue.course_id = c.id
LEFT JOIN sector s 
	ON u.sector_id = s.id
LEFT JOIN management ma 
	ON u.management_id = ma.id
LEFT JOIN job_role jr 
	On u.job_role_id = jr.id
INNER JOIN inscription i 
	ON i.user_id = u.id
	AND i.course_id = c.id
WHERE 
	i.dt_creation <  '2019-01-01 00:00:00' AND
	(SELECT COUNT(1) FROM user_evaluation uee WHERE uee.user_id = u.id AND uee.course_id = c.id) >= 1 AND
	(SELECT COUNT(1) FROM evaluation e INNER JOIN module m ON m.id = e.module_id WHERE m.course_id=c.id) > 1
GROUP BY ue.user_id, ue.course_id