Hi Tim,

Thanks for your reply.

email to column: ',2'. 2 is the userid of this user

in the logs there is 1 error:

Execute scheduled task: Ad-hoc database queries: run scheduled reports task (report_customsql\task\run_reports)
... started 11:10:01. Current memory use 4.5MB.
... Looking for old temp CSV files to delete.
... Running report tessie
... REPORT FAILED Invalid user
... used 8 dbqueries
... used 0.063519954681396 seconds
Scheduled task complete: Ad-hoc database queries: run scheduled reports task (report_customsql\task\run_reports)

Kind regards,
Peter

Right, so there is a bug. There should not be a , before the 2. If you can fix that directly in the DB, then it will start working.

Ultimately, I need to push a bug fix for this to the plugins DB.

Thanx Tim. When you push the bug fix, i'll fix the DB.
Regards,
Peter

Tim,

I scheduled a report for 8 pm after I made the change in the database ('2 to 2). It looks like the report has not been picked up after all.

Execute scheduled task: Ad-hoc database queries: run scheduled reports task (report_customsql \ task \ run_reports)
... started 20:10:01. Current memory use 4.5MB.
... Looking for old temp CSV files to delete.
... used 3 dbqueries
... used 0.0085630416870117 seconds
Scheduled task complete: Ad-hoc database queries: run scheduled reports task (report_customsql \ task \ run_reports)

Does the plugin by any chance check whether there has already been a run on this day?

Yes, I think we found this when we were trying to hurry-up some testing. When it computes when next to run the report, it computes the next day from when it last ran, and then puts in the time.

Hello Tim,
Thankyou very much for creating such a helpful plugin. I was using this first time in my moodle instance and wanted the query results to go to my email. I created a scheduled export and set up my email addresses but I did not get any email in my mail box ( I setup 2 emails ).. and also I setup the path for export in. "Export csv report to path / directory" but there is no file coming there for scheduled export. here is my cron job details:
Execute scheduled task: Ad-hoc database queries: run scheduled reports task (report_customsql\task\run_reports)
... started 12:10:02. Current memory use 19.9MB.
... Looking for old temp CSV files to delete.
... used 3 dbqueries
... used 0.0043940544128418 seconds
Scheduled task complete: Ad-hoc database queries: run scheduled reports task (report_customsql\task\run_reports)


I am not sure what I am doing wrong.. Can you please guide?
Thanks

See comments above. There is a bug. Hopefully won't be too long before it is fixed.

I've recently installed Ad-Hoc Reports and it will not send emails. The SQL Query's are working fine and the plugin is writing csv files to the server as scheduked. However, no emails are being sent. Note that I tested outgoing mail configuration, quickmail and re-engagement, everything sends emails. I've also tried using various emails addresses, no change. My system is configured as follows:

1) Moodle 3.11 (Build: 20210517), running on a Linux Server.

2) Ad-hoc database queries, report_customsql4.0 for Moodle 3.9+, 20201218

Any suggestions on how to fix this are appreciated.

Thank you

I think you are seeing https://github.com/moodleou/moodle-report_customsql/issues/89. As it happens, I am working on getting a new version with that fixed pushed today.

New version pushed (with 3.11 compatibility and more.)

Moodle 3.10.4 - PHP v7.2.26 - MSSQL v11.00.7493
Hi - my Moodle system runs on a windows platform with MSSQL. Date fields are displaying as text fields when downloaded as Excel after an ad-hoc report runs and I am trying to work out how to ensure date fields are recognised by excel as dates so my end users can easily filter by year and month without having to first convert the text field to a date (many users are not excel savvy).

I am sure this was working earlier in the year and the only thing I can think of that has changed was a Windows Server 2008 to Windows Server 2019 upgrade.

The SQl I am using to output a date (course completion date in this case) that works fine when displayed as a HTML table is:
CONVERT(VARCHAR(10), DATEADD(second, prefix_course_completions.timecompleted-DATEDIFF(second,GETDATE(),GETUTCDATE()), CONVERT(DATETIME, '1970-01-01', 103)), 103) AS Completed

My SQL is very basic -would a different way of converting/formatting the date in the SQL produce a field excel displays as a date when exported or is this an issues with the download process?

Also - is it possible to do any of the following:
- limit or paginate displayed rows while allowing download of all rows (some of our reports can be large)
- use the same parameter field in multiple places in the where clause (e.g. where fielda = :parameter1 or fieldb= :parameter1) - I get an error: Incorrect number of query parameters. Expected 3, got 2.
- or the above with LIKE and the % in the SQL instead of the end user entering it in the parameter field?

Thank you
Phil

Hi,
the week start for the scheduled mail sent on the 1st day of the week seems to be set to Sunday.
I can see
define('REPORT_CUSTOMSQL_START_OF_WEEK', 6); // Saturday, so I guess Saturday is day 0 and Sunday is day one?
So if I want to change this to Monday, I have to set it to 7?
Thanks
Csaba

@Phil,

1. you should not need to do weird things to achieve date-formatting. Read the on-screen text on the form where you edit the query. That explains how to get Moodle to format the date values.

2. Pagination - this actually puts more load on the server, which is why we prefer to generate thw whole report in one go and then scroll (or filter in excel).

3. Ability to use the same parameter field many times would be lovely, but this is a limitation of Moodle DB query library, which we use. It is almost impossible to get around. There are sometimes tricks you can do in the SQL to get around it. E.g. where :parameter1 IN (fielda, fieldb) - but check that does not prevent the query being executed efficiently.

4. You can do this using string concatenation. Not sure about the MSSQL syntax, but in Postgres it would be fielda LIKE :parameter1 || '%'

@Csaba in recent versions of this plugin, you don't need to set this in PHP code. It is controlled by an admin setting under Admin -> Plugins -> Reports -> Ad-hoc DB Queries.

H I added the plugin and want to run the custom report (Assignments (and Quizzes) waiting to be graded). I tried to add the code mention below. But unable to run the report and save. Can anyone guide me how to do that and run this report.

SELECT concat('',c.fullname,'') AS Course

,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher

,concat('מטלות') AS Assignments

,concat('בחנים') AS 'Quizzes'

,(SELECT COUNT(*)
FROM prefix_course_modules cm
JOIN prefix_modules as m ON m.id = cm.module
WHERE m.name LIKE 'quiz' AND cm.course = c.id
GROUP BY cm.course
) AS 'nQuizzes'

,(SELECT COUNT(*)
FROM prefix_quiz_attempts AS qa
JOIN prefix_quiz AS q ON q.id = qa.quiz
WHERE q.course = c.id
AND qa.timefinish = 0
GROUP BY q.course) AS 'unFinished Quiz attempts'

,(SELECT COUNT(*)
FROM prefix_quiz_attempts AS qa
JOIN prefix_quiz AS q ON q.id = qa.quiz
WHERE q.course = c.id
AND qa.timefinish > 0
GROUP BY q.course) AS 'finished quiz attempts'

,(SELECT Count( ra.userid ) AS Users
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5
AND ctx.instanceid = c.id
) AS nStudents


,(
SELECT count(a.id)
FROM prefix_assignment AS a
JOIN prefix_course_modules AS cm ON a.course = cm.course
WHERE cm.instance = a.id AND cm.module = 1 AND a.course = c.id
) nAssignments

,(
SELECT count(*)
FROM prefix_assignment AS a
WHERE a.course = c.id AND FROM_UNIXTIME(a.timedue) > NOW()
GROUP BY a.course
) 'Open
Assignments'

, CONCAT(ROUND( (100 / iAssignments ) * iOpenAssignments ) ,'%') 'unFinished
Assignments
(percent)'

,(
SELECT count(asb.id)
FROM prefix_assignment_submissions AS asb
JOIN prefix_assignment AS a ON a.id = asb.assignment
JOIN prefix_course_modules AS cm ON a.course = cm.course
WHERE asb.grade < 0 AND cm.instance = a.id AND cm.module = 1 AND a.course = c.id
) 'unChecked
Submissions'

,(
SELECT count(asb.id)
FROM prefix_assignment_submissions AS asb
JOIN prefix_assignment AS a ON a.id = asb.assignment
JOIN prefix_course_modules AS cm ON a.course = cm.course
WHERE cm.instance = a.id AND cm.module = 1 AND a.course = c.id
) 'Submitted
Assignments'

FROM prefix_course AS c
LEFT JOIN (
SELECT course, count(*) AS iAssignments
FROM prefix_assignment AS a
GROUP BY a.course
) AS tblAssignmentsCount ON tblAssignmentsCount.course = c.id

LEFT JOIN (
SELECT course, count(*) AS iOpenAssignments
FROM prefix_assignment AS a
WHERE FROM_UNIXTIME(a.timedue) > NOW()
GROUP BY a.course
) AS tblOpenAssignmentsCount ON tblOpenAssignmentsCount.course = c.id

WHERE 1=1
#AND c.fullname LIKE '%תשעג%'
%%FILTER_YEARS:c.fullname%%
## You can enable the SEMESTER filter as well,
## by uncommenting the following line:
## %%FILTER_SEMESTERS:c.fullname%%
ORDER BY 'Open
Assignments' DESC

Thanks