AD Hoc Monthly Reports

AD Hoc Monthly Reports

by James Todd -
Number of replies: 9

Hi All

I would like to run a monthly report and automatically email it out to a manager on the 1st of the month.

I can't get the report to filter on details from the last 30 days.

I've been looking at tutorials for 3 days, none of them seem to work.

Versions 3.12 and 4.01


Average of ratings: -
In reply to James Todd

Re: AD Hoc Monthly Reports

by Marcus Green -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
Can you provide more information?
Average of ratings: -
In reply to Marcus Green

Re: AD Hoc Monthly Reports

by James Todd -
Thanks Marcus.
I want to run a monthly report and send it out automatically on the 1st of the month.
I'm using ad hoc database queries.
I can't get it to display the last 31 days from todays date..

The code works perfect until I add the date range filters, some codes show a syntax errors as not recognised. This one shows nothing

here's the code, it's the last line I cant get to work.

SELECT DISTINCT u.firstname AS firstname, u.lastname AS lastname, q.name AS Quiz,
ROUND(qg.grade,0) AS Grade, DATE_FORMAT(FROM_UNIXTIME(qg.timemodified),'%d-%m-%Y') AS date

FROM prefix_user AS u
JOIN prefix_user_info_data AS uid ON uid.userid = u.id
JOIN prefix_user_info_data AS uid2 ON uid2.userid = u.id
JOIN prefix_user_info_data AS uid3 ON uid3.userid = u.id
JOIN prefix_user_info_field AS uif ON uid.fieldid = uif.id
JOIN prefix_quiz_grades AS qg ON u.id = qg.userid
JOIN prefix_quiz AS q ON qg.quiz = q.id


WHERE uid.fieldid = '13'
AND (uid2.fieldid = '14' or uid2.fieldid = 'null')
AND uid3.data ='managers name'
AND qg.timemodified = CURRENT_DATE - interval 31 day
Average of ratings: -
In reply to James Todd

Re: AD Hoc Monthly Reports

by Michael Milette -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers Picture of Translators
Hi James,

Perhaps something like this (untested) might work for you (assuming MySQL or MariaDB)...

: : ;
AND qg.timemodified BETWEEN NOW() - INTERVAL 31 DAY AND NOW()

If you want to just compare today's date but ignore hours, minutes and seconds, you can replace both instances of NOW() with CURDATE().

Hope you find this helpful.

Best regards,

Michael Milette
Average of ratings: -
In reply to Michael Milette

Re: AD Hoc Monthly Reports

by Marcus Green -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
I suggest testing the date range filters separate from the rest of the code. Also is your database mysql because I think they are specific to MySQL. I was wrangling some dates recently on a Postgres system and it is a whole different world when it comes to dates.
Average of ratings: -
In reply to James Todd

Re: AD Hoc Monthly Reports

by Randy Thornton -
The field qg.timemodified is in Unix Timestamp format not a date format. You will need to convert it into a date format of some kind to use most of the common MySQL date functions which expect a string formatted like a date and time as input.

There's a bunch of examples how to do this over in the Ad-hoc contributed reports page including an explanation I wrote about this: https://docs.moodle.org/400/en/ad-hoc_contributed_reports#About_Date_and_Time_Formatting
Average of ratings: -
In reply to James Todd

Re: AD Hoc Monthly Reports

by James Todd -
This is where I'm at.
it still returns no results.
there's no errors though.

AND DATE_FORMAT(FROM_UNIXTIME(qg.timemodified),'%d-%m-%Y') BETWEEN CURDATE() - INTERVAL 31 DAY AND CURDATE()

I'll have a read up on your recommended page today. Randy

Thanks
Average of ratings: -
In reply to James Todd

Re: AD Hoc Monthly Reports

by Randy Thornton -
This part

CURDATE() - INTERVAL 31 DAY

is not working. Instead look at using a function like DATE_SUB() or DATE_ADD() to do date arithmetic like this. https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-add.

Also, your date format needs to be in the proper canonical OSI order: Y-m-d as expected for the MySQL date functions. Inputting d-m-Y is legal because it is three numbers but it will not give you the math you expect ;) See all the examples in that link and how they are formatted.
Average of ratings: Useful (1)
In reply to James Todd

Re: AD Hoc Monthly Reports

by James Todd -
Just going through old posts.
Thanks for everyone's help.
This is the code that I got working with advice from Randy and Michael.


SELECT DISTINCT u.firstname AS firstname, u.lastname AS lastname, uid.data AS Manager, uid1.data AS department, q.name AS Quiz,
ROUND(qg.grade,0) AS Grade, DATE_FORMAT(FROM_UNIXTIME(qg.timemodified),'%d-%m-%Y') AS date

FROM prefix_user AS u
JOIN prefix_user_info_data AS uid ON uid.userid = u.id
JOIN prefix_user_info_data AS uid1 ON uid1.userid = u.id
JOIN prefix_user_info_field AS uif ON uid.fieldid = uif.id
JOIN prefix_quiz_grades AS qg ON u.id = qg.userid
JOIN prefix_quiz AS q ON qg.quiz = q.id

WHERE uid.fieldid = '1'
AND uid1.fieldid = '2'
AND q.name LIKE '%course name%'
AND uid.data = 'managers email'
AND FROM_UNIXTIME(qg.timemodified, '%Y-%m-%d') >= DATE_SUB(NOW(), INTERVAL 31 DAY)
Average of ratings: Useful (2)