SELECT 
	c.id AS idCurso,
	REPLACE(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,
	if (cl.num_enterprise = 3, 
		if(cl.tip_colaborador = 1,
			'Cooperado',      
			 'Terceiro'
		),        
		if(cl.tip_colaborador = 1,
			'Empregado',
			'Terceiro'
		)
	) AS 'TipoVinculo',
	REPLACE(ma.name,',','') AS 'Gestao',
	REPLACE(s.name,',','') AS 'Setor',
	REPLACE(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.course_id = ue.course_id) AS Tentativas,
	MAX(ue.grade) AS Nota,
	IF(MAX(grade) >= 70,'Aprovado','Reprovado') AS 'Status', # CURSO SEM PROVA A NOTA É SEMPRE 100% E O STATUS APROVADO
	70 AS 'NotaMedia'
FROM user_evaluation ue
INNER JOIN course c 
	ON ue.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.dt_update >= '2018-01-01' AND 
    ue.evaluation_id IS NULL
GROUP BY ue.course_id, ue.evaluation_id, ue.user_id

UNION all

SELECT
	c.id AS idCurso,
	REPLACE(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,
	if (cl.num_enterprise = 3, 
		if(cl.tip_colaborador = 1,
			'Cooperado',      
			 'Terceiro'
		),        
		if(cl.tip_colaborador = 1,
			'Empregado',
			'Terceiro'
		)
	) AS 'TipoVinculo',
    REPLACE(ma.name,',','') AS 'Gestao',
    REPLACE(s.name,',','') AS 'Setor',
    REPLACE(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', # CURSO SEM PROVA A NOTA É SEMPRE 100% E O STATUS APROVADO
    e.minimum_grade AS 'NotaMedia'
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.id = jr.id
WHERE 
	ue.dt_update >= '2018-01-01' AND 
    ue.evaluation_id IS NOT NULL
GROUP BY ue.user_id, ue.evaluation_id, ue.course_id