SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
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 prefix_user_enrolments UE INNER JOIN prefix_enrol E ON UE.enrolid = E.id WHERE E.courseid = %%COURSEID%% 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 prefix_user_enrolments UE INNER JOIN prefix_enrol E ON UE.enrolid = E.id WHERE E.courseid = %%COURSEID%% 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 prefix_user_enrolments UE INNER JOIN prefix_enrol E ON UE.enrolid = E.id WHERE E.courseid = %%COURSEID%% AND UE.userid = U.ID AND E.status = 0 AND UE.status = 0), CURDATE()),'-') AS 'DIAS SEM CERTIFICAR' FROM prefix_user U LEFT JOIN prefix_user_info_data ATUACAO ON ATUACAO.fieldid = 2 AND ATUACAO.userid = U.id LEFT JOIN prefix_user_info_data CARGO ON CARGO.fieldid = 1 AND CARGO.userid = U.id INNER JOIN prefix_context CT ON CT.contextlevel = 50 AND CT.instanceid = %%COURSEID%% INNER JOIN prefix_role_assignments RA ON RA.contextid = CT.id AND RA.userid = U.id AND RA.roleid = 5 LEFT JOIN prefix_course_completions CC ON CC.course = CT.instanceid AND CC.userid = U.id LEFT JOIN prefix_customcert CCC ON CCC.course = CT.instanceid INNER JOIN prefix_customcert_issues CCI ON CCI.userid = U.id AND CCC.id = CCI.customcertid WHERE CCC.course = %%COURSEID%%
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear