# NÃO MATRICULADO
# PEGA USUÁRIOS NÃO INSCRITOS NO CURSO
SELECT 
	c.id AS idCurso,
	REPLACE(c.name,',','') AS Curso,
    c.duracao_curso AS 'Carga Horária',
    concat(u.first_name, u.last_name) AS Nome,
	cl.mat_colaborador AS 'CRM',
    u.email AS 'E-mail',	
	'Cooperado' AS 'TipoVinculo',
	REPLACE(jr.name,',','') AS Especialidade,
	IF(u.enabled = 1, 'Ativo', 'Ferias/Demitido/Afastado') AS Situacao,
	'' as 'Data Inscrição',
    '' as 'Data Término do Curso',
	'' AS 'Data Conclusao',
	'' AS Tentativas,
	'' AS Nota1,
	'' AS Nota2,
	'' AS Nota3,
	'Não Matriculado' AS 'Status'
FROM course c, user u
    INNER JOIN client cl 
        ON cl.id = u.id
    LEFT JOIN sector s 
        ON u.sector_id = s.id
    LEFT JOIN job_role jr 
        ON u.job_role_id = jr.id
WHERE 
	c.id NOT IN (SELECT i.course_id FROM inscription i WHERE i.user_id = u.id) AND
	cl.num_enterprise = 3 AND
	cl.tip_colaborador = 1 AND 
	u.enabled = 1 AND
	c.id = 195

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

UNION ALL

SELECT 
	c.id AS idCurso,
	REPLACE(c.name,',','') AS Curso,
    c.duracao_curso AS 'Carga Horária',
    concat(u.first_name, u.last_name) AS Nome,
	cl.mat_colaborador AS 'CRM',
    u.email AS 'E-mail',	
	'Cooperado' AS 'TipoVinculo',
	REPLACE(jr.name,',','') AS Especialidade,
	IF(u.enabled = 1, 'Ativo', 'Ferias/Demitido/Afastado') AS Situacao,
	i.dt_creation as 'Data Inscrição',
    i.dt_end as 'Data Término do Curso',
	'' AS 'Data Conclusao',
	'' 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
    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
    INNER JOIN module m 
    	ON m.course_id = c.id
    LEFT JOIN inscription_lesson il
    	ON il.inscription_id = i.id
    LEFT JOIN evaluation e 
    	ON e.module_id = m.id
    
WHERE i.id not in (SELECT inscription_id  FROM `inscription_lesson`) AND
	cl.num_enterprise = 3 AND
	cl.tip_colaborador = 1 AND 
	u.enabled = 1 AND
	c.id = 195

# 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.name,',','') AS Curso,
    c.duracao_curso AS 'Carga Horária',
    concat(u.first_name, u.last_name) AS Nome,
	cl.mat_colaborador AS 'CRM',
    u.email AS 'E-mail',	
	'Cooperado' AS 'TipoVinculo',
	REPLACE(jr.name,',','') AS Especialidade,
	IF(u.enabled = 1, 'Ativo', 'Ferias/Demitido/Afastado') AS Situacao,
	i.dt_creation as 'Data Inscrição',
    i.dt_end as 'Data Término do Curso',
	'' AS 'Data Conclusao',
	'' 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
    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
    INNER JOIN module m 
    	ON m.course_id = c.id
    LEFT JOIN inscription_lesson il
    	ON il.inscription_id = i.id
    LEFT JOIN evaluation e 
    	ON e.module_id = m.id    
WHERE 
	i.id IN (SELECT inscription_id  FROM `inscription_lesson`) AND 
	cl.num_enterprise  = 3 AND
	cl.tip_colaborador = 1 AND
	u.enabled = 1 AND
    (SELECT COUNT(1) FROM inscription_lesson AS iil WHERE iil.finished IS NULL AND iil.inscription_id = i.id) > 0 AND
    c.id = 195 
	
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.name,',','') AS Curso,
    c.duracao_curso AS 'Carga Horária',
    concat(u.first_name, u.last_name) AS Nome,
	cl.mat_colaborador AS 'CRM',
    u.email AS 'E-mail',	
	'Cooperado' AS 'TipoVinculo',
	REPLACE(jr.name,',','') AS Especialidade,
	IF(u.enabled = 1, 'Ativo', 'Ferias/Demitido/Afastado') AS Situacao,
	i.dt_creation as 'Data Inscrição',
    i.dt_end as 'Data Término do Curso',
	'' AS 'Data Conclusao',
	'' 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
    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
    INNER JOIN module m 
    	ON m.course_id = c.id
    LEFT JOIN inscription_lesson il
    	ON il.inscription_id = i.id
    LEFT JOIN evaluation e 
    	ON e.module_id = m.id    
WHERE 
	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 OR ue.evaluation_id = e.id) AND ue.user_id = u.id) AND
    (SELECT COUNT(1) FROM inscription_lesson AS iil WHERE iil.finished IS NULL AND iil.inscription_id = i.id) = 0 AND
	cl.num_enterprise  = 3 AND
	cl.tip_colaborador = 1 AND
	u.enabled = 1 AND
    c.id = 195
    

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

UNION ALL

# RECUPERA CURSOS QUE NÃO POSSUEM PROVA
SELECT 
	c.id AS idCurso,
	REPLACE(c.name,',','') AS Curso,
    c.duracao_curso AS 'Carga Horária',
	concat(u.first_name, u.last_name) AS Nome,
    cl.mat_colaborador AS 'CRM',
    u.email AS 'E-mail',	
	'Cooperado' AS 'TipoVinculo',
	REPLACE(jr.name,',','') AS Especialidade,
	IF(u.enabled = 1, 'Ativo', 'Ferias/Demitido/Afastado') AS Situacao,
	i.dt_creation as 'Data Inscrição',
    i.dt_end as 'Data Término do Curso',
	MAX(ue.dt_update) AS 'Data Conclusao',
	(SELECT COUNT(1) FROM user_evaluation AS uee WHERE uee.user_id = u.id AND uee.evaluation_id = e.id) AS Tentativas,
	MAX(ue.grade) AS Nota1,
	'' AS Nota2,
	'' AS Nota3,
    IF((SELECT COUNT(1) FROM inscription_lesson AS iil WHERE iil.finished IS NULL AND iil.inscription_id = i.id) > 0,'Em andamento','Aprovado') AS 'Status'
FROM user_evaluation ue
LEFT JOIN evaluation e
	ON ue.evaluation_id = e.id
INNER JOIN course c 
	ON ue.course_id = c.id
INNER JOIN inscription AS i 
		ON i.course_id = c.id
INNER JOIN user u 
	ON ue.user_id = u.id
INNER JOIN client cl 
	ON cl.id = u.id
LEFT JOIN management ma 
	ON u.management_id = ma.id
LEFT JOIN sector s 
	ON u.sector_id = s.id
LEFT JOIN job_role jr 
	ON u.job_role_id = jr.id
WHERE 
    ue.evaluation_id IS NULL AND
	cl.num_enterprise = 3 AND
	cl.tip_colaborador = 1 AND
	u.enabled = 1 AND
    c.id = 195
GROUP BY ue.course_id, ue.user_id

UNION ALL

# RECUPERA CURSOS QUE POSSUEM PROVA
SELECT
	c.id AS idCurso,
	REPLACE(c.name,',','') AS Curso,
    c.duracao_curso AS 'Carga Horária',
    concat(u.first_name, u.last_name) AS Nome,
	cl.mat_colaborador AS 'CRM',
    u.email AS 'E-mail',	
	'Cooperado' AS 'TipoVinculo',
	REPLACE(jr.name,',','') AS Especialidade,
	IF(u.enabled = 1, 'Ativo', 'Ferias/Demitido/Afastado') AS Situacao,
	i.dt_creation as 'Data Inscrição',
    i.dt_end as 'Data Término do Curso',
	MAX(ue.dt_update) AS 'Data Conclusao',
	(SELECT COUNT(1) FROM user_evaluation AS uee WHERE uee.user_id = u.id AND uee.evaluation_id = e.id) AS 'Tentativas',
    (SELECT uee.grade AS grade from user_evaluation uee WHERE uee.user_id = u.id AND uee.evaluation_id = e.id ORDER by uee.id ASC LIMIT   1) AS Nota1,
	(SELECT uee.grade AS grade from user_evaluation uee WHERE uee.user_id = u.id AND uee.evaluation_id = e.id ORDER by uee.id ASC LIMIT 1,1) AS Nota2,
	(SELECT uee.grade AS grade from user_evaluation uee WHERE uee.user_id = u.id AND uee.evaluation_id = e.id ORDER by uee.id ASC LIMIT 2,1) AS Nota3,
    IF(MAX(ue.grade) >= e.minimum_grade,'Aprovado','Reprovado') AS 'Status' # CURSO SEM PROVA A NOTA É SEMPRE 100% E O STATUS APROVADO
FROM user_evaluation ue
INNER JOIN evaluation e 
	ON ue.evaluation_id = e.id
INNER JOIN user u 
	ON ue.user_id = u.id
INNER JOIN client cl 
	ON cl.id = u.id
LEFT JOIN module m 
	ON e.module_id = m.id
LEFT JOIN course c 
	ON m.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.course_id = c.id
WHERE 
    ue.evaluation_id IS NOT NULL AND
	cl.num_enterprise = 3 AND
	cl.tip_colaborador = 1 AND
	u.enabled = 1 AND
    c.id = 195
GROUP BY ue.user_id, ue.evaluation_id, ue.course_id