HOW TO: Display date completed quiz

HOW TO: Display date completed quiz

by Andrew Milici -
Number of replies: 10

Hi everyone

I thought after benefiting MUCH from using the certificate module, I thought I would contribute to the forum with my little "hack".

I've noticed a few questions being asked on the forum "How can I change the date on the certificate to display the date the quiz was completed"....my thought was to try it myself, as I have a bit of coding experience.

To do this, I first "commented" the following line in my certificate.php:

cert_printtext(45, 460, 'C', 'Helvetica', '', 14, utf8_decode($certificatedate));

to this:

// cert_printtext(45, 460, 'C', 'Helvetica', '', 14, utf8_decode($certificatedate));

This is to stop the default date from appearing in the certificate

Directly underneath this line, I added the following 3 lines:

 $sqlcmd = "SELECT FROM_UNIXTIME(mdl_quiz_attempts.timefinish + 86400, 'Module completed this %D day of %M, %Y ') AS quizfinish, mdl_quiz.course AS courseid FROM mdl_quiz_attempts INNER JOIN mdl_quiz ON mdl_quiz.id = mdl_quiz_attempts.quiz INNER JOIN mdl_certificate_issues ON mdl_certificate_issues.userid = mdl_quiz_attempts.userid INNER JOIN mdl_certificate ON mdl_certificate.id = mdl_certificate_issues.certificateid AND mdl_quiz.course = mdl_certificate.course WHERE mdl_certificate_issues.id = !!!CERTID!!!";


 $sqlcmd = str_replace('!!!CERTID!!!', $certrecord->id, $sqlcmd);


 $retval = get_record_sql($sqlcmd);

Let me explain.

1. The first line is a SQL call/command to the moodle database. You will notice I hardcoded our prefix into the statement (the "mdl_") part. Yours may be different, so you will need to use a $CFG->prefix in there. Also the other point to note out is the 86400 number near the start. Because all of the times in moodle are stored in unixtime (the number of seconds since 1/1/1970 GMT), I need to convert it to our timezone (Perth, Australia). You will need to work out your own timezone here, or else your dates will be one day off sometimes.

2. The second line replaces the key !!!CERTID!!! in the statement. I used a str_replace function because it is easier to read and debug, rather then tacking it on to the end of the SQL statement.

3. The final line gets the data from the database, and stores it in a variable called $retval.

finally we display the date using this function:

cert_printtext(170, 430, 'C', 'Times', '', 20, utf8_decode($retval->quizfinish));

ALSO one final note, if you want to change the format of the time stamp, edit this section of the code:

SELECT FROM_UNIXTIME(mdl_quiz_attempts.timefinish + 86400, 'Module completed this %D day of %M, %Y ')

The text in the quotes is the FORMAT STRING, you can learn how to manipulate it here:

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

Hope this helps and makes sense

If you have any further questions, post them here, and I will help the best I can

Average of ratings: Useful (4)
In reply to Andrew Milici

Re: HOW TO: Display date completed quiz

by Nick Boyd -

Thanks Andrew.

Very much appreciated

Nick

Average of ratings: -
In reply to Andrew Milici

Re: HOW TO: Display date completed quiz

by Divya Bansal -

This is what I have been looking for. Thank you very much Andrew!

Average of ratings: -
In reply to Andrew Milici

Re: HOW TO: Display date completed quiz

by Harvey Arkawy -

Andrew,

As a programmer who uses a different language, I am not familiar with php software.  With that being said I was wondering if you might be able to review my thinking and comment.

1: Moodle software already posts the date that a test has been completed.  It can be found within the Activities Report for a particular student within a particular course.  That information is displayed as...

Quiz Cylinder Servicing: Disc & Pin Type Final Exam Grade: 78.75 / 100.00 Tuesday, 14 December 2010, 11:56 AM (1 day 3 hours)
Certificate Cylinder Servicing: Disc & Pin Type Certificate Issued Wednesday, 15 December 2010, 10:20 AM (5 hours 27 mins)

 

Notice that the Cert date is the 15th but the grade/exam was completed on the 14th.  If the program is able to code the information for this page, the code exists (somewhere) to format it for this report.

Why can't this same coding (used to print within this report) be used to print to the certificate; copy and paste.

 

In other words, Activity Report reads from the DB the date the exam for this student and this course was passed.  Copy and paste this code to the certificates module and format for the certificate.

 

Does this make sense and if so, can you code it?  Can you specify where in the Moodle software the original code is which will be copied.

I can figure out where within the Certificate module to place it.... I think.

 

Harvey

Average of ratings: -
In reply to Harvey Arkawy

Re: HOW TO: Display date completed quiz

by Raymond Fürst -

I think in principle you are right, it should be possible to determine the completion date of a linked quiz and have it displayed onto the certificate.

What I think makes this approach difficult to achieve is the fact that both the certificate and the quiz are separate/independent activities. The concept of the certificate module is also very flexible, so it is possible to have a certificate linked to a lesson and not to a quiz. A solution must take this into account.

As of version 1.9, the certificate stores one linked activity to display (in mdl_certificate) and an array of linked activities to unlock the certificate (in mdl_certificate_linked_modules). It should be possible to use code from the certificate module to identify the quiz and the related user data of the actual quiz attempt.

Average of ratings: -
In reply to Andrew Milici

Re: HOW TO: Display date completed quiz

by Paul Broekhof -

Great! Thanks for that, Andrew, this is very useful indeed.

Average of ratings: -
In reply to Andrew Milici

Re: HOW TO: Display date completed quiz

by Ricardo Scantamburlo -

Hello, Andrew

I included your code and some people show the date of 01.01.1970 on the certificate. Could you help me?

Ricardo

Average of ratings: -
In reply to Andrew Milici

Odp: HOW TO: Display date completed quiz

by Adam kowalski -

I wrote lines:

$sqlcmd = "SELECT FROM_UNIXTIME( mdl2_user_enrolments.timestart +86400, 'w okresie od dnia %d-%m-%Y ' ) AS timestart FROM mdl2_user_enrolments, mdl2_certificate_issues WHERE mdl2_user_enrolments.userid = mdl2_certificate_issues.userid and mdl2_user_enrolments.userid = " . $USER->id . "";
$sqlcmd = str_replace($USER->id, $sqlcmd);
$timestart = get_record_sql($sqlcmd);

certificate_print_text($pdf, $x+50, $y+270, 'L', 'freeserif', '', 16, utf8_decode($timestart->timestart));

 

What is wrong?

Average of ratings: -
In reply to Adam kowalski

Re: Odp: HOW TO: Display date completed quiz

by Jean-Michel Védrine -

Please tell me what version of Moodle and certificate you are using and I will be able to tell you what is wrong !

What amaze me in that thread is that people post code or try to use posted code without saying anything about Moodle/plugins versions wide eyes That thread started in 2010 and Moodle (and the certificate plugin) have changed a lot in 3 years !

Also note that the sql query included in that code will certainly not work with all databases so in fact it would be a good habit if every poster include detailed information about his  setup

- php version

- database type and version

- Moodle version

- certificate version

Because that would make my life as a forum helper a lot easier big grin

Average of ratings: -
In reply to Jean-Michel Védrine

Re: Odp: HOW TO: Display date completed quiz

by Veerle De Smedt -

I notice that there is a good track changes of quiz completion by the student.

Is it also possible to see the date when a quiz was created by a teacher en who that teacher was?

 

Thank you 

Veerle

Average of ratings: -