SELECT * FROM (

	# RECUPERA USUÁRIOS NÃO MATRICULADOS NO CURSO
	
	SELECT
		c.id AS idCurso,
		c.name AS curso,
		cl.num_enterprise AS Empresa,
		cl.tip_colaborador AS tipoColaborador,
		concat(u.first_name, u.last_name) AS Colaborador,
		cl.mat_colaborador AS Matricula,
		(
			CASE
				WHEN cl.tip_colaborador = 1 && cl.num_enterprise = 1 THEN 'Empregado'
				WHEN cl.tip_colaborador = 1 && cl.num_enterprise = 3 THEN 'Cooperado'
				WHEN cl.tip_colaborador = 1 && cl.num_enterprise = 3000 THEN 'Empregado'
				ELSE 'Terceiro'
			END
		
		) AS TipoVinculo,
    	ma.name AS Gestao,
		s.name AS Setor,
		jr.name AS Cargo,
		IF(u.enabled = 1, 'Ativo', 'Ferias/Demitido/Afastado') AS Situacao,
		'' AS DataConclusao,
		'' AS Tentativas,
		'' AS Nota,
		'Nao Iniciado' AS 'Status'
	FROM course AS c, user AS u
	INNER JOIN client AS cl 
		ON u.id = cl.id
	INNER JOIN sector AS s 
		ON u.sector_id = s.id
    INNER JOIN management AS ma 
    	ON u.management_id = ma.id
	INNER JOIN job_role AS jr 
		ON u.job_role_id = jr.id
	WHERE 
		#u.id = 62 AND
		c.active = 1 AND
		u.id NOT IN (SELECT i.user_id FROM inscription i WHERE i.course_id = c.id AND i.user_id = u.id AND i.inscriptionstatus_id = 2)


	######################################################
	UNION
	######################################################


	# RECUPERA USUÁRIOS MATRICULADOS NO CURSO QUE POSSUI PROVA, MAS QUE NÃO REALIZARAM A PROVA

	SELECT
		c.id AS idCurso,
		c.name AS curso,
		cl.num_enterprise AS Empresa,
		cl.tip_colaborador AS tipoColaborador,
		concat(u.first_name, u.last_name) AS Colaborador,
		cl.mat_colaborador AS Matricula,
		(
			CASE
				WHEN cl.tip_colaborador = 1 && cl.num_enterprise = 1 THEN 'Empregado'
				WHEN cl.tip_colaborador = 1 && cl.num_enterprise = 3 THEN 'Cooperado'
				WHEN cl.tip_colaborador = 1 && cl.num_enterprise = 3000 THEN 'Empregado'
				ELSE 'Terceiro'
			END
		
		) AS TipoVinculo,
    	ma.name AS Gestao,
		s.name AS Setor,
		jr.name AS Cargo,
		IF(u.enabled = 1, 'Ativo', 'Ferias/Demitido/Afastado') AS Situacao,
		'' AS DataConclusao,
		'' AS Tentativas,
		'' AS Nota,
		'Em andamento' AS 'Status'
	FROM course AS c, user AS u
	INNER JOIN client AS cl 
		ON u.id = cl.id
	INNER JOIN sector AS s 
		ON u.sector_id = s.id
    INNER JOIN management AS ma
    	ON u.management_id = ma.id
	INNER JOIN job_role AS jr 
		ON u.job_role_id = jr.id
	WHERE 
		#u.id = 62 AND
		c.active = 1 AND
		c.id IN (
			SELECT 
				c.id
			FROM course AS c 
			INNER JOIN module AS m 
				ON m.course_id = c.id
			INNER JOIN evaluation AS e 
				ON e.module_id = m.id
		) AND
		u.id IN (SELECT i.user_id FROM inscription i WHERE i.course_id = c.id AND i.user_id = u.id  AND i.inscriptionstatus_id = 2) AND 
		u.id NOT IN ( 
			SELECT 
				uu.id     
			FROM user_evaluation AS ue, evaluation AS ee, module AS mm, course AS cc, user AS uu
			WHERE 
				ue.evaluation_id = ee.id AND
				ue.user_id = uu.id AND
				ee.module_id = mm.id AND
				mm.course_id = cc.id AND
				cc.id = c.id AND
				uu.id = u.id 
		)

		
	######################################################
	UNION
	######################################################

	# RECUPERA USUÁRIOS QUE FINALIZARAM O CURSO QUE POSSUEM PROVA

	SELECT
		c.id AS idCurso,
		c.name AS curso,
		cl.num_enterprise AS Empresa,
		cl.tip_colaborador AS tipoColaborador,
		concat(u.first_name, u.last_name) AS Colaborador,
		cl.mat_colaborador AS Matricula,
		(
			CASE
				WHEN cl.tip_colaborador = 1 && cl.num_enterprise = 1 THEN 'Empregado'
				WHEN cl.tip_colaborador = 1 && cl.num_enterprise = 3 THEN 'Cooperado'
				WHEN cl.tip_colaborador = 1 && cl.num_enterprise = 3000 THEN 'Empregado'
				ELSE 'Terceiro'
			END
		
		) AS TipoVinculo,
    	ma.name AS Gestao,
		s.name AS Setor,
		jr.name AS Cargo,
		IF(u.enabled = 1, 'Ativo', 'Ferias/Demitido/Afastado') AS Situacao,
		ue.dt_update AS DataConclusao,
		(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 Nota,
		IF(MAX(grade) >= e.minimum_grade,'Aprovado','Reprovado') AS 'Status'
	FROM user_evaluation AS ue 
	INNER JOIN user AS u 
		ON ue.user_id = u.id
	INNER JOIN client AS cl 
		ON u.id = cl.id
	INNER JOIN sector AS s 
		ON u.sector_id = s.id
    INNER JOIN management AS ma 
    	ON u.management_id = ma.id
	INNER JOIN job_role AS jr 
		ON u.job_role_id = jr.id
	INNER JOIN evaluation AS e 
		ON ue.evaluation_id = e.id
	INNER JOIN module AS m 
		ON e.module_id = m.id
	INNER JOIN course AS c 	
		ON m.course_id = c.id    
	WHERE 
		#u.id = 62 AND 
		c.active = 1 		
	GROUP BY ue.user_id, ue.evaluation_id
	
			
	######################################################
	UNION
	######################################################

	# RECUPERA USUÁRIOS EM CURSOS SEM PROVA
	
	SELECT 
	cc.id AS idCurso,
	cc.name AS curso,
	cl.num_enterprise AS Empresa,
	cl.tip_colaborador AS tipoColaborador,
    concat(u.first_name, u.last_name) AS Colaborador,
    cl.mat_colaborador AS Matricula,
	(
        CASE
            WHEN cl.tip_colaborador = 1 && cl.num_enterprise = 1 THEN 'Empregado'
            WHEN cl.tip_colaborador = 1 && cl.num_enterprise = 3 THEN 'Cooperado'
			WHEN cl.tip_colaborador = 1 && cl.num_enterprise = 3000 THEN 'Empregado'
            ELSE 'Terceiro'
        END

    ) AS TipoVinculo,
    ma.name AS Gestao,
	s.name AS Setor,
	jr.name AS Cargo,
    IF(u.enabled = 1, 'Ativo', 'Ferias/Demitido/Afastado') AS Situacao,
    IF((SELECT COUNT(1) FROM inscription_lesson AS iil WHERE iil.finished IS NULL AND iil.inscription_id = i.id) > 0,'-', (SELECT MAX(iill.finished) FROM inscription_lesson AS iill WHERE iill.inscription_id = i.id)) AS DataConclusao,
    '1' AS Tentativas,
    IF((SELECT COUNT(1) FROM inscription_lesson AS iil WHERE iil.finished IS NULL AND iil.inscription_id = i.id) > 0,'','100') AS Nota,
    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 course AS cc 
	INNER JOIN inscription AS i 
		ON i.course_id = cc.id
	LEFT JOIN inscription_lesson AS il
		ON il.inscription_id = i.id
	INNER JOIN user AS u 
		ON u.id = i.user_id 
	INNER JOIN client AS cl 
		ON u.id = cl.id
	INNER JOIN sector AS s 
		ON u.sector_id = s.id
    INNER JOIN management AS ma 
    	ON u.management_id = ma.id
	INNER JOIN job_role AS jr 
		ON u.job_role_id = jr.id
	WHERE 
		#u.id = 62 AND
		cc.active = 1 AND 
		cc.id NOT IN (
			SELECT 
				c.id
			FROM course AS c 
			INNER JOIN module AS m 
				ON m.course_id = c.id
			INNER JOIN evaluation AS e 
				ON e.module_id = m.id
		)
	GROUP BY cc.name, u.first_name, u.last_name

	
) AS tabela ORDER BY curso  ASC