course start date end date

course start date end date

by Sheick Jaufuraully -
Number of replies: 9

Hi Guys,

I have one question if you can assist please. In the basic moodle report which comes as default, we can see the fields "started on" & "Last accessed on"  when generating a report. Do you know which fields these are and under which table in the database please? 

I tried to use "startdate" and "enddate" in the course table but it is not giving me the same result as the default report.

Many Thanks

Sheick


Average of ratings: -
In reply to Sheick Jaufuraully

Re: course start date end date

by John Leggett -

Hi Sheick,

Was wondering whether you found a solution to this as I have exactly the same question.

When I used "startdate" from the course table in a Configurable Report it shows when the course was created (which kinda makes sense of course) and not when the student first took the course, which is what I need.

Many thanks,

John



Average of ratings: -
In reply to John Leggett

Re: course start date end date

by Miguel González Laredo -
Picture of Plugin developers
Hi guys. Please, let me know the exact moodle version and the URL of that report you refered.

I hope be useful soon
Regards
Average of ratings: -
In reply to Miguel González Laredo

Re: course start date end date

by John Leggett -
Hi Miguel,

My Moodle version is 3.9 (Build: 20200615) and the Configurable Report query I am building is below:

----------------------------------------------------------------------------------

SELECT CONCAT(u.firstname , ' ' , u.lastname) AS 'Name',
u.id,
c.shortname AS Course,
u.institution,
uid.data AS Region,
uid2.data AS Type,
uid3.data AS 'Access Households',
uid4.data AS Funder,
ROUND (gr.finalgrade) as Grade,

FROM prefix_course AS c
JOIN prefix_groups AS g ON g.courseid = c.id
JOIN prefix_groups_members AS m ON g.id = m.groupid
JOIN prefix_user AS u ON m.userid = u.id
JOIN prefix_grade_grades AS gr ON gr.id = u.id

JOIN prefix_user_info_data AS uid ON uid.userid = u.id
JOIN prefix_user_info_field AS uif ON uid.fieldid = uif.id
JOIN prefix_user_info_data AS uid2 ON uid2.userid = u.id
JOIN prefix_user_info_field AS uif2 ON uid2.fieldid = uif2.id
JOIN prefix_user_info_data AS uid3 ON uid3.userid = u.id
JOIN prefix_user_info_field AS uif3 ON uid3.fieldid = uif3.id
JOIN prefix_user_info_data AS uid4 ON uid4.userid = u.id
JOIN prefix_user_info_field AS uif4 ON uid4.fieldid = uif4.id

WHERE uif.shortname = 'Region' and uif2.shortname = 'Type' and uif3.shortname = 'Households' and uif4.shortname = 'FunderIDEE' and c.id = %%COURSEID%%
ORDER BY u.lastname

----------------------------------------------------------------------------------

As Sheick mentioned, in the default built-in Moodle Course Report (via the Reports tab) there are field names "Started on" & "Last accessed on" which show the students first attempt for that course and the most recent.

I guess I need to find where, in which Moodle database table, this data resides?

Cheers,


John
Average of ratings: -
In reply to Sheick Jaufuraully

Re: course start date end date

by Ben Haensel -
Try this SQL. It shows the date a student starts and ends in a course in addition to a last access date. This query is setup to only return active students. -Ben
---

select
c.id courseid,
c.fullname course,
u.id userid,
concat(u.lastname, ', ', u.firstname) student,
DATE_FORMAT(from_unixtime(ue.timestart),'%Y-%m-%d') start_date,
DATE_FORMAT(from_unixtime(ue.timeend),'%Y-%m-%d') end_date,
(SELECT DATE_FORMAT(FROM_UNIXTIME(timeaccess),'%Y-%m-%d') FROM prefix_user_lastaccess WHERE userid=u.id AND courseid=c.id) AS CourseLastAccess

FROM prefix_course AS c
JOIN prefix_enrol AS en ON en.courseid = c.id
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
JOIN prefix_user AS u ON ue.userid = u.id
JOIN prefix_context AS ct ON c.id = ct.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ct.id
JOIN prefix_role r on ra.roleid = r.id

WHERE r.name = 'student'
AND ra.userid = u.id
AND ra.userid = ue.userid
and unix_timestamp() between ue.timestart and ue.timeend

#use this for a course specific global report
#and c.id = '%%COURSEID%%'
Average of ratings: -
In reply to Ben Haensel

Re: course start date end date

by John Leggett -
Many thanks Ben,

Tried the query but no records found. That is, no active students I guess? - is there a simple tweak to the SQL to show all records for the course over time whether the learner/student is logged in or not?

Kind regards,


John
Average of ratings: -
In reply to John Leggett

Re: course start date end date

by Ben Haensel -
John-

Put a # in front of this line like this:

#and unix_timestamp() between ue.timestart and ue.timeend

If you still get no results, you could change the whole where clause to be like below. Commenting out the student filter will allow for any roles to be shown. Let me know how that goes. -Ben

WHERE ra.userid = u.id
AND ra.userid = ue.userid
#r.name = 'student'
#and unix_timestamp() between ue.timestart and ue.timeend
Average of ratings: -
In reply to Ben Haensel

Re: course start date end date

by John Leggett -

Many thanks again Ben - much appreciated.

The first '# in front' suggestion didn't work so I tried your 'where clause change' and that did!

Seems like the 'startdate' and 'courselastaccess' columns are producing accurate results but all the 'enddate' column entries state '1970-01-01'? However, it is the  'startdate' and 'courselastaccess' which are important to me.

Now, I'll test my limited, novice knowledge of SQL by working on combining your excellent query with my own query to produce the required final report!

Cheers,


John

Average of ratings: -
In reply to John Leggett

Re: course start date end date

by Ben Haensel -
John - The end date is giving off that odd date since no end date was assigned. Happy coding! -Ben
Average of ratings: -