SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
SELECT C.fullname AS 'Curso', 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 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 ) AS 'Data de Inscrição', CCI.code AS 'Cód Certificado', from_unixtime(CCI.timecreated, '%d/%m/%Y ') AS 'Data Certificado', ( 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 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 ) 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' 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 IN (47, 38, 37, 36, 35, 34, 44) 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_course C ON C.id = CT.instanceid Where CCC.course IN (47, 38, 37, 36, 35, 34, 44)
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear