SQLize Online / PHPize Online  /  SQLtest Online

SELECT CONCAT(U.firstname, ' ', U.lastname) AS 'Nome', U.username AS 'CPF', U.email AS 'E-mail', U.city AS 'Cidade', ATUACAO.data AS 'Área de Atuação', CARGO.data AS 'Cargo', IF( CC.timecompleted IS NULL, 'Não', FROM_UNIXTIME(CC.timecompleted, '%d/%m/%Y') ) AS 'Data Conclusão', ( SELECT IF( UE.timecreated IS NULL, '-', FROM_UNIXTIME( MAX(UE.timecreated), '%d/%m/%Y' ) ) FROM mdl_user_enrolments UE INNER JOIN prefix_enrol E ON UE.enrolid = E.id WHERE E.courseid = 34 AND UE.userid = U.ID AND E.status = 0 AND UE.status = 0 ) AS 'Data de Inscrição', ( SELECT IF( UE.timecreated IS NULL, '-', FLOOR( TIMESTAMPDIFF( DAY, FROM_UNIXTIME( MAX(UE.timecreated), '%Y-%m-%d' ), CURDATE() ) / 365 ) + 1 ) FROM mdl_user_enrolments UE INNER JOIN prefix_enrol E ON UE.enrolid = E.id WHERE E.courseid = 34 AND UE.userid = U.ID AND E.status = 0 AND UE.status = 0 ) AS 'Ciclo', TIMESTAMPDIFF( DAY, CURDATE(), TIMESTAMPADD( YEAR, ( FLOOR( TIMESTAMPDIFF( DAY, FROM_UNIXTIME(CC.timecompleted, '%Y-%m-%d'), CURDATE() ) / 365 ) + 1 ), FROM_UNIXTIME(CC.timecompleted, '%Y-%m-%d') ) ) AS 'Dias Restantes', IF( CC.timecompleted IS NULL, TIMESTAMPDIFF( DAY, ( SELECT FROM_UNIXTIME( MAX(UE.timecreated), '%Y-%m-%d' ) FROM mdl_user_enrolments UE INNER JOIN mdl_enrol E ON UE.enrolid = E.id WHERE E.courseid = 34 AND UE.userid = U.ID AND E.status = 0 AND UE.status = 0 ), CURDATE() ), '-' ) AS 'DIAS SEM CERTIFICAR' FROM mdl_user U LEFT JOIN mdl_user_info_data ATUACAO ON ATUACAO.fieldid = 2 AND ATUACAO.userid = U.id LEFT JOIN mdl_user_info_data CARGO ON CARGO.fieldid = 1 AND CARGO.userid = U.id INNER JOIN mdl_context CT ON CT.contextlevel = 50 AND CT.instanceid = 34 INNER JOIN mdl_role_assignments RA ON RA.contextid = CT.id AND RA.userid = U.id AND RA.roleid = 5 LEFT JOIN mdl_course_completions CC ON CC.course = CT.instanceid AND CC.userid = U.id LEFT JOIN mdl_customcert CCC ON CCC.course = CT.instanceid INNER JOIN mdl_customcert_issues CCI ON CCI.userid = U.id AND CCC.id = CCI.customcertid INNER JOIN mdl_user_enrolments UE ON UE.userid = U.id WHERE CCC.course = 34 AND UE.status = 0