How is the Export Attendance "Export to file" functionality implemented on the view.php page? The page does not reload when you select the EXPORT TO FILE button so I can't figure out any way to trace the code that is being used to create the file. I want to customize the output. Where do I find the code?
Thanks!
I have the same request. I am currently using the customized multisession version of face-to-face and we have just discovered an issue with the exported attendance report being incorrect when students have signed up for and attended multiple session. I've been poking around in the database for two days now and cannot even figure out where the attendance information is stored.
There seems to be only one record per face to face instance in the gradebook for each user which is problematic but when viewing signed up users (attendees.php) the correct attendance information is displayed...
Anyway, I thought I'd prod around in the export script and see what I could find there but I can figure out where it is. Oops....I just had an idea while writing this and searched lib.php for evidence of the export function and it appears that the attendance information is being pulled from the gradebook... So, where is the information displayed on attendees.php coming from....
Jason
Jason, I found the call that initiates the attendance export at about LINE 46 on view.php.
if (!empty($download)) {
require_capability('mod/facetoface:viewattendees', $context);
facetoface_download_attendance($facetoface->name, $facetoface->id, $location, $download);
exit();
}
The function being called is in facetoface/lib.php @ about LINE 1280.
facetoface_download_attendance()
I have become very familiar with the way attendees.php works because we did a lot of customization to it on our local install. If you have any specific questions, post them here and I can help point you in the right direction.
Hope that helps!
Rob,
Thanks a million! I will definitely take you up on your offer to help.
I'll poke around given the direction you have provided and then probably post again later. I understood that the multisession version I received a copy of from Alastair still had issues with gradebook integration but I didn't realize that the attendance export was pulling from the gradebook. I figured it was just from a table in the database somewhere where attendance was recorded. My assumptions seem to have been wrong but I'm not even sure of that yet.
Thanks again and I'll be back.
Jason
Rob,
I'm just getting more confused now. But I do enjoy the challenge.
I checked lib.php and there is no
facetoface_download_attendance()
in the version I have. The only thing I could find that looked like what I am searching for was
function facetoface_write_activity_attendance()
Within that function there is a database query that appears to pull the attendance information. It is commented in the file as follows
// Fast version of "facetoface_get_attendees()" for all sessions
For reference, I am using the multisession version of Face-to-face....version 2012140604
I guess a lot of change has taken place since I installed this. I'm more than happy to upgrade but I can't lose the multisession capability.
Any advice? Your help will be greatly appreciated.
Jason
The version I am working from is:
version = 2013010401
release = '2.2.3.1 (2013010400)'
The function seems to be more about creating the EXCEL file than pulling the data. It looks like the data is pulled by:
FROM: function facetoface_write_activity_attendance() (checkout the SQL calls - they illustrate the data structures)
//////////////////////////////////////////$signups = $DB->get_records_sql("
SELECT
su.id AS submissionid,
s.id AS sessionid,
u.*,
f.course AS courseid,
ss.grade,
sign.timecreated
FROM
{facetoface} f
JOIN
{facetoface_sessions} s
ON s.facetoface = f.id
JOIN
{facetoface_signups} su
ON s.id = su.sessionid
JOIN
{facetoface_signups_status} ss
ON su.id = ss.signupid
LEFT JOIN
(
SELECT
ss.signupid,
MAX(ss.timecreated) AS timecreated
FROM
{facetoface_signups_status} ss
INNER JOIN
{facetoface_signups} s
ON s.id = ss.signupid
INNER JOIN
{facetoface_sessions} se
ON s.sessionid = se.id
AND se.facetoface = $facetofaceid
WHERE
ss.statuscode IN (?,?)
GROUP BY
ss.signupid
) sign
ON su.id = sign.signupid
JOIN
{user} u
ON u.id = su.userid
WHERE
f.id = ?
AND ss.superceded != 1
AND ss.statuscode >= ?
ORDER BY
s.id, u.firstname, u.lastname
", array(MDL_F2F_STATUS_BOOKED, MDL_F2F_STATUS_WAITLISTED, $facetofaceid, MDL_F2F_STATUS_APPROVED));
// Fast version of "facetoface_get_sessions($facetofaceid, $location)" $sql = "SELECT d.id as dateid, s.id, s.datetimeknown, s.capacity, s.duration, d.timestart, d.timefinish FROM {facetoface_sessions} s JOIN {facetoface_sessions_dates} d ON s.id = d.sessionid WHERE s.facetoface = ? AND d.sessionid = s.id $locationcondition ORDER BY s.datetimeknown, d.timestart";
$sessions = $DB->get_records_sql($sql, array_merge(array($facetofaceid), $locationparam));
Rob,
Thanks. I am actually currently working with that sql query to see if I can get the information I want/need directly from the database. As a short term solution, I'm planning on just setting up the query using Tim Hunt's custom query tool (or the one provided by Itamar Tzadok's Dataform) to allow people at my school to get the data while I try and figure out how to modify the export tool.
The problem is my sql skills are about as limited as my php skills so there is a lot of trial and error work involved.
Are you using multi sessions (the ability for students to sign up for more than one session within each face-to-face instance)?
Jason
Rob,
A quick correction to what I wrote yesterday, there is a
facetoface_download_attendance
function in my file. Sorry, I don't know how I overlooked it yesterday but I sat down to work on this today and there it was staring me in the face.
Jason
Rob,
Sorry for all the posts. As I mentioned in a previous post, I'm trying to use the sql query, for now, to get the info I want from the database and thought it wouldn't be that hard to convert it to raw sql but am having a little trouble.
Do you know what the following refers to in the query
ss.statuscode IN (?,?)
I can't figure out what the two question marks in the parentheses are referring to and, of course, my sql query doesn't understand it either so it's stopping there.
Jason
Rob,
I'm documenting my progress (or lack thereof) here. I managed to modify the sql query so it could be run directly. The query is pasted below. I should have thought about it a little more as it ran forever. I don't quite yet understand the results yet either. It ended up returning 201 identical rows for each user...? At any rate, mdl_user.* was a mistake. I didn't need all the user fields so I'm working now on reworking this to only include the fields I want and the ones the query requires. Also, I couldn't pass variables via a direct sql query so I entered the face to face id directly (that's the 12).
I'm sharing this info in the hopes that maybe you'll have some advice but also to document the process.
Jason
SELECT
mdl_facetoface_signups.id AS submissionid,
mdl_facetoface_sessions.id AS sessionid,
mdl_user.*,
mdl_facetoface.course AS courseid,
mdl_facetoface_signups_status.grade,
mdl_facetoface_signups_status.timecreated
FROM
{mdl_facetoface} f
JOIN
{mdl_facetoface_sessions} s
ON mdl_facetoface_sessions.facetoface = mdl_facetoface.id
JOIN
{mdl_facetoface_signups} su
ON mdl_facetoface_sessions.id = mdl_facetoface_signups.sessionid
JOIN
{mdl_facetoface_signups_status} ss
ON mdl_facetoface_signups.id = mdl_facetoface_signups_status.signupid
LEFT JOIN
(
SELECT
mdl_facetoface_signups_status.signupid,
MAX(mdl_facetoface_signups_status.timecreated) AS timecreated
FROM
{mdl_facetoface_signups_status} ss
INNER JOIN
{mdl_facetoface_signups} s
ON mdl_facetoface_signups.id = mdl_facetoface_signups_status.signupid
INNER JOIN
{mdl_facetoface_sessions} se
ON mdl_facetoface_sessions.sessionid = mdl_facetoface_sessions.id
AND mdl_facetoface_sessions.facetoface = 12
GROUP BY
mdl_facetoface_signups_status.signupid
) sign
ON mdl_facetoface_signups.id = mdl_facetoface_signups_status.signupid
JOIN
{mdl_user} u
ON mdl_user.id = mdl_facetoface_signups.userid
WHERE
mdl_facetoface.id = 12
AND mdl_facetoface_signups_status.superceded != 1
ORDER BY
mdl_facetoface_sessions.id, mdl_user.firstname, mdl_user.lastname
Rob,
Sorry again for the deluge of information (and cries for help) here. I finally accomplished what I was working for. That is, a way to get the attendance data out of the database for now. This is a temporary solution but working through it has helped me to better understand where the relevant information is stored in the database.
I'm pasting a copy of my sql query (finally successful). It contains facetoface hard coded instances (12 in my case) which would have to be changed for each instance or I guess it could be modified to just export all of them but that would get overwhelming fast. Also, the timestamp is converted to a readable format. I think I'll also work on different labels for the grade values (null = cancellation, 0 = no show, & 100 = attended) or something like that.
For now I'm just happy to have made this much progress. Thanks for pointing me in the right direction.
Jason
SQL Query to export and properly group face-to-face attendance information.
SELECT
mdl_facetoface_signups.id AS submissionid,
mdl_facetoface_sessions.id AS sessionid,
FROM_UNIXTIME(mdl_facetoface_sessions_dates.timestart) AS date,
mdl_user.id,
mdl_user.username,
mdl_user.firstname,
mdl_user.lastname,
mdl_facetoface.course AS courseid,
mdl_facetoface_signups_status.grade,
mdl_facetoface_signups_status.timecreated
FROM
mdl_facetoface
JOIN
mdl_facetoface_sessions
ON mdl_facetoface_sessions.facetoface = mdl_facetoface.id
JOIN
mdl_facetoface_signups
ON mdl_facetoface_sessions.id = mdl_facetoface_signups.sessionid
JOIN
mdl_facetoface_sessions_dates
ON mdl_facetoface_signups.sessionid = mdl_facetoface_sessions_dates.sessionid
JOIN
mdl_facetoface_signups_status
ON mdl_facetoface_signups.id = mdl_facetoface_signups_status.signupid
LEFT JOIN
(
SELECT
mdl_facetoface_signups_status.signupid,
MAX(mdl_facetoface_signups_status.timecreated) AS timecreated
FROM
mdl_facetoface_signups_status
INNER JOIN
mdl_facetoface_signups
ON mdl_facetoface_signups.id = mdl_facetoface_signups_status.signupid
INNER JOIN
mdl_facetoface_sessions
ON mdl_facetoface_signups.sessionid = mdl_facetoface_sessions.id
AND mdl_facetoface_sessions.facetoface = 12
WHERE
mdl_facetoface_signups_status.statuscode IN (80,100)
GROUP BY
mdl_facetoface_signups_status.signupid
) sign
ON mdl_facetoface_signups.id = sign.signupid
JOIN
mdl_user
ON mdl_user.id = mdl_facetoface_signups.userid
WHERE
mdl_facetoface.id = 12
AND mdl_facetoface_signups_status.superceded = 0
AND mdl_facetoface_signups_status.statuscode >= 0
ORDER BY
mdl_facetoface_sessions.id, mdl_facetoface_sessions_dates.timestart , mdl_user.firstname, mdl_user.lastname
Rob,
Ok, well I spent most of the day working on this and finally got something working that I'm satisfied with for now. I refined the sql query a bit to get rid of extraneous information and will post it below (again for reference).
After I finished with that, I looked at the code in lib.php for some time trying to figure out how to have the grade from
mdl_facetoface_signups_status
sent to the Excel export instead of the grade that is apparently being pulled from the gradebook. I'm still a little lost because it appears global functions, that I'm not familiar with, are being called. I feel like I have a grasp on what needs to be done and just have to figure out how to actually get it done.
If you have any advice or suggestions, I will be appreciative.
I will stop posting lengthy sql queries with this message.
Jason
**********************************
SQL Query to get attendance data from the face-to-face module (note each instance value needs to be entered (12 is shown here)). There may be some unneeded information in the query that I have overlooked.
**********************************
SELECT
FROM_UNIXTIME(mdl_facetoface_sessions_dates.timestart) AS Date,
mdl_user.username,
mdl_user.firstname,
mdl_user.lastname,
CASE mdl_facetoface_signups_status.grade WHEN 100 THEN "Attended" WHEN 50 THEN "Partially-Attended" WHEN 0 THEN "No-Show" ELSE "Cancelled" END AS Grade,
FROM_UNIXTIME(mdl_facetoface_signups_status.timecreated) AS When_Created
FROM
mdl_facetoface
JOIN
mdl_facetoface_sessions
ON mdl_facetoface_sessions.facetoface = mdl_facetoface.id
JOIN
mdl_facetoface_signups
ON mdl_facetoface_sessions.id = mdl_facetoface_signups.sessionid
JOIN
mdl_facetoface_sessions_dates
ON mdl_facetoface_signups.sessionid = mdl_facetoface_sessions_dates.sessionid
JOIN
mdl_facetoface_signups_status
ON mdl_facetoface_signups.id = mdl_facetoface_signups_status.signupid
LEFT JOIN
(
SELECT
mdl_facetoface_signups_status.signupid,
MAX(mdl_facetoface_signups_status.timecreated) AS timecreated
FROM
mdl_facetoface_signups_status
INNER JOIN
mdl_facetoface_signups
ON mdl_facetoface_signups.id = mdl_facetoface_signups_status.signupid
INNER JOIN
mdl_facetoface_sessions
ON mdl_facetoface_signups.sessionid = mdl_facetoface_sessions.id
AND mdl_facetoface_sessions.facetoface = 12
WHERE
mdl_facetoface_signups_status.statuscode IN (10,80,90,100)
GROUP BY
mdl_facetoface_signups_status.signupid
) sign
ON mdl_facetoface_signups.id = sign.signupid
JOIN
mdl_user
ON mdl_user.id = mdl_facetoface_signups.userid
WHERE
mdl_facetoface.id = 12
AND mdl_facetoface_signups_status.superceded = 0
AND mdl_facetoface_signups_status.statuscode >= 0
ORDER BY
mdl_facetoface_sessions.id, mdl_facetoface_sessions_dates.timestart , mdl_user.firstname, mdl_user.lastname
Rob,
Sorry, it's me again. I've been looking and looking at the problem area (I think) in lib.php and wonder if you could help me a little?
From the sql query, I see that ss.grade is the value I want to be sent to the Excel worksheet as the grade. Just below the sql query is the following:
// Set grade
if (!empty($grading_info->items) and !empty($grading_info->items[0]->grades[$userid])) {
$signup->grade = $grading_info->items[0]->grades[$userid]->str_grade;
}
After experimenting with that set grade code, the portion that I've highlighted in red, I see that this is the correct place where the grade that gets sent to the worksheet is set but I can't figure out how to alter it so that the ss.grade from the sql query gets sent instead of what it is sending.
Does that make any sense?
Jason
I'm not sure of the context from which you are trying to set grade. But what I think would work would be - if ss.grade is a field being pulled from a sql call then you can reference it like this: $signups->ss.grade
I think! For instance in function facetoface_write_activity_attendance() you have:
$signups = $DB->get_records_sql("
SELECT
su.id AS submissionid,
s.id AS sessionid,
u.*,
f.course AS courseid,
ss.grade,
sign.timecreated
FROM
{facetoface} f ... (etc...)
This enables you to call fields pulled like this: $signups->ss.grade
Or try giving the field an alias like this:
$signups = $DB->get_records_sql("
SELECT
su.id AS submissionid,
s.id AS sessionid,
u.*,
f.course AS courseid,
ss.grade AS myGrade,
sign.timecreated
FROM
{facetoface} f etc...
And reference it like this: $signups->myGrade
If it turns out that's wrong i'll edit the post so as not to spread bad info - but I think that's the way I did it.
Does that help? Is that even what you are asking? LOL!
Rob,
Thanks very much for the push in the right direction. Yes, you understood my dilemma perfectly. I used
$signups->ss.grade
and, strangely, ended up with "grade" in all of the grade cells of the Excel sheet. I looked a little more and saw that a few lines above the set grade code was code that was resetting the $signups array. So, I commented out the two lines that reset that array as follows:
// $grading_info = grade_get_grades(reset($signups)->courseid, 'mod', 'facetoface',
// $facetofaceid, $userids);
I was excited to see that the resulting Excel export now contains the correct attendance data for users! Yeah! But, yes, but,
for some reason that has an impact on what is displayed when I view the individual session attendance records via the face-to-face module through the browser. For some odd reason, the record that appears for an individual session is completely wrong. It is filled with attendees that are from other days and times and even one user who is not registered for any sessions in that particular instance of face-to-face...? Maybe a cancelled user but I'm not sure.
Obviously my commenting out the reset had a negative impact elsewhere so I guess I need to go and find the individual session grade retrieval code and figure out how to get the state created by the reset code in that function....or maybe I'm totally off base...?
Jason
try setting a new variable to equal $signups before it is reset and then use the new variable. ??
//assign new before reset
$myNewSignupsVar = $signups
$grading_info = grade_get_grades(reset($signups)->courseid, 'mod', 'facetoface',
$facetofaceid, $userids);
//then use the new var to get the value that works for you
$myNewSignupsVar->ss.grade
that way you should be able to get the value you want without upsetting anything else?
Rob,
Hmm, that sounded great but it didn't work...? I don't completely understand the purpose of that
$grading_info = grade_get_grades........
line but something is taking place there that is necessary for individual session display (in terms of showing the attendees and whether they attended or not)...or maybe it is in the part of the get_grades line that I have removed. It was originally ($signup->grade = $myNewSignupsVar->$grading_info->items[0]->grades[$userid]->str_grade;
When the reset line is commented out, and I put ss.grade in the set_grade line I get the correct information in the Excel export as I mentioned before but when it un-commented and I use the new variable I get:
1. The wrong display for an individual session
&
2. The word "grade" in the grade column of the Excel export
I'm confused...
Jason
How to get all courses report in attendance module.
I required a student report for all courses they attend in excel If possible can i get sql query for that.(For all course reports include date ant time of courses)
Thanks
Rob,
Sorry, I just discovered a major mistake on my part. I was using a file titled lib-edited.php and toggling between it and my original lib.php (using lib-original.php) and somewhere along the line I had made some changes to lib-edited.php that were causing the problem when I tried to view individual attendance sessions.
I discovered this because I kept looking at the code and realized that there should be no affect on the individual attendance session when changes were made to the
facetoface_write_activity_attendance()
function. I looked back up and saw that I had accidentally changed the
facetoface_get_attendees()
function and had never reverted back to the original. I fixed that and then went back and experimented with the
facetoface_write_activity_attendance()
function. I didn't need to assign the array to a new variable....or, rather, doing so did not achieve what I wanted but, simply commenting out those two lines that reset the array seems to have accomplished what I was working for! They are
// $grading_info = grade_get_grades(reset($signups)->courseid, 'mod', 'facetoface',
// $facetofaceid, $userids);
I still don't completely understand what that code does so I'm a little nervous that I may have screwed something else up but so far everything seems to be ok. (fingers crossed)
I'll experiment for a few days to make sure.
Thanks so much for all of your help. I would never have figured it out without the guidance.
Jason
