search

moodleou / moodle-report_customsql

A Moodle report plugin that lets you easily create simple reports that can be expressed as a single SQL query
46 stars 100 forks source link

sql code problem in moodle #151

Closed bruyen72 closed 4 months ago

bruyen72 commented 4 months ago

'm having a hard time trying to create SQL for 6 hours to add Moodle and if I'm wrong, can you correct it for me in my code?

I tested several errors and two messages said : Error executing query: ERROR: Incorrect number of query parameters. Expected 2, got 0. Error executing the query: ERROR: Parameter types mixed in SQL query!!

Any value in the output that looks like a URL will be automatically transformed into a link. If the results of your query have two columns column_name and column_name_link_url, the resulting report output will have a single column containing a link with the first column as the link text and the second as the URL. If a column name in the results ends with the characters of date and the column has integer values, they will be treated as Unix timestamps and automatically converted into human-readable dates. The token %%USERID%% in the query will be replaced by the ID of the user viewing the report before the report is executed. For scheduled reports, the tokens %%STARTTIME%% and %%ENDTIME%% are replaced by the Unix timestamp at the start and end of the report week/month in the query before it is executed.

You can put parameters in the SQL using named placeholders, for example :parameter_name. So, when the report is executed, the user can enter values for the parameters to be used when executing the query. If :parameter_name starts or ends with date characters, a date and time selector will be used to enter this value; otherwise, a simple text box will be used. You cannot use the characters :, ;, or ? in strings in your query. If you need them in the output data (such as when sending URLs), you can use the tokens %%C%%, %%S%%, and %%Q%% respectively.

Column Definitions: Full course name with link: Should display the course name as a link. Full name with link: Should display the student's name as a link. CPF (numbers only): The student's identification number. Role: The user's role in the course (student, teacher, etc.). Enrollment time: The date of enrollment in the course. Completion time: The course completion date (or 'Not completed' if the course was not completed). Time to complete: Time until completion (in HH format) or 'Not completed' if the course was not completed. Days to completion: Days until completion or 'Not completed' if the course was not completed. Grade: Final course grade, rounded to two decimal places.

CODE:

SELECT CONCAT('', mdl_course.fullname, '') AS "Nome completo do curso com link", CONCAT('', mdl_user.firstname, ' ', mdl_user.lastname, '') AS "Nome completo com link", mdl_user.idnumber AS "CPF (somente números)", mdl_role.shortname AS "Papel", FROM_UNIXTIME(mdl_user_enrolments.timecreated) AS "Tempo de inscrição", CASE WHEN mdl_user_enrolments.timestart > 0 THEN FROM_UNIXTIME(mdl_user_enrolments.timestart) ELSE 'Não concluído' END AS "Tempo de encerramento", CASE WHEN mdl_user_enrolments.timestart > 0 THEN SEC_TO_TIME(mdl_user_enrolments.timestart - mdl_user_enrolments.timecreated) ELSE 'Não concluído' END AS "Tempo concluído", CASE WHEN mdl_user_enrolments.timestart > 0 THEN DATEDIFF(FROM_UNIXTIME(mdl_user_enrolments.timestart), FROM_UNIXTIME(mdl_user_enrolments.timecreated)) ELSE 'Não concluído' END AS "Dias até a conclusão", ROUND(mdl_grade_grades.finalgrade, 2) AS "Nota" FROM mdl_user JOIN mdl_user_enrolments ON mdl_user.id = mdl_user_enrolments.userid JOIN mdl_enrol ON mdl_user_enrolments.enrolid = mdl_enrol.id JOIN mdl_course ON mdl_enrol.courseid = mdl_course.id JOIN mdl_context ON mdl_course.id = mdl_context.instanceid JOIN mdl_role_assignments ON mdl_role_assignments.contextid = mdl_context.id AND mdl_role_assignments.userid = mdl_user.id JOIN mdl_role ON mdl_role_assignments.roleid = mdl_role.id LEFT JOIN mdl_grade_grades ON mdl_grade_grades.userid = mdl_user.id AND mdl_grade_grades.itemid IN ( SELECT id FROM mdl_grade_items WHERE courseid = mdl_course.id AND itemtype = 'course' ) WHERE mdl_user.id = 12345 -- Substitua este valor pelo ID do usuário para teste ORDER BY mdl_course.fullname, mdl_user.lastname, mdl_user.firstname

timhunt commented 4 months ago

I can't see anything obviously wrong with your SQL, but I only looked quickly.

If you want help writing queries, please post in https://moodle.org/mod/forum/view.php?id=8044.

If something is not working, start with a much smaller query, and only add a little bit at a tiem, checking there are no errors, to build up the full query.