No certificate issue entry in database for some users

No certificate issue entry in database for some users

by Gary Lyon -
Number of replies: 14

Moodle v2.5.1,

 

Oracle 11g

Php 5.3.3

Ubuntu 2.6.32

Certificate v2013102300

Background: we are using Moodle for professional development courses where many of the courses must be completed every year.

Problem: I am experiencing problems with some users being unable to print a certificate of completion with the most current quiz completion date and the module is not making an entry into m_certificate_issues table when the certificate is printed. If I log into the user's account and attempt to print the certificate, the certificate is created with the correct date but there is still no entry made in m_certificate_issues.

I found that the log entries for people experiencing the problem are as shown in the first screen capture below. User A receives a certificate but it is with the date from a prior course completion. (Note the 2013 log entries include "Certificate received" in the action column, but there is no such entry in the 2014 entries):

 

User A Log Entries

 

In the case of User B below, they have only taken the course once, and when they created their Certificate of Completion the entry was properly made in the database.

User B Log Entries

 

All suggestions are appreciated. Thank you community.

Average of ratings: -
In reply to Gary Lyon

Re: No certificate issue entry in database for repeat users

by Gary Lyon -

UPDATE: After considerable testing the problem appears to be related to whether a user has previously completed a course. Even if they are reenrolling the certificate module does not appear to be making a new entry in the database for a new issue of the certificate. This is a big problem for anyone with a course that is recompleted regularly. Any suggestions/help is greatly appreciated. 

Average of ratings: -
In reply to Gary Lyon

Re: No certificate issue entry in database for repeat users

by Dave Fisher -
We had a similar issue with certificates issued based on grades from SCORM activities. These activities are part of a larger course that need to be repeated annually. We found that deleting the prior SCORM attempt for a user before they repeated the SCORM activity would allow their new attempt to be shown when they generated a new certificate. 
Average of ratings: -
In reply to Dave Fisher

Re: No certificate issue entry in database for repeat users

by Gary Lyon -

Thanks for the feedback. 

My problem appears to happen whether the course is SCORM or using the 'Lesson' activity. Typically the trigger for making a certificate available is a passing score on a test and some courses additionally require completion of the course evaluation. 

As a test I deleted all user attempts of a quiz except their most recent and reissued the certificate; no difference in results - no 'Certificate Received' entry for the user in the d/b. This seems to be the missing element for any reissues - once a certificate is initially issued no other entry is made in the d/b to indicate future certificates have been received.  

I've tried a variety of certificate configurations with no luck and am increasingly frustrated  that with over 20,600 this is becoming a major administrative headache. The irony is that something has changed in the code since this has not been the case throughout all of 2012 and 2013. 

Any other ideas are appreciated.

Average of ratings: -
In reply to Gary Lyon

Re: No certificate issue entry in database for repeat users

by Dave Fisher -

In my prior research into this, I found that the certificate plugin used to have a function where you could reissue a users certificate or delete an old one. That function was written out of the code a few versions back. 

The only way I can get it to issue a new certificate now is if I delete ALL the SCORM activity attempts that the certificate is based on. Leaving one attempt gives me the old certificate. When all are deleted, then a new attempt made, a correct certificate is issued.

Dave

Average of ratings: -
In reply to Dave Fisher

Re: No certificate issue entry in database for repeat users

by Gary Lyon -

That's what I thought. We use Certificate reports so deleting past issues creates a problem for our reporting requirements. And even if that were not an issue there isn't a 'reset' option under Certificate Administration so I assume you must be doing it at the database level (which I do not have)? 

If  reset.php (from the moodle/course) could be incorporated in the Certificate module I would think it could be activated nightly via cron to clear the certificate records but, it would definitely be better if  a new entry (with new date based on the most recent completion of requirements) were made in the database EVERY time a certificate is issued, no matter if previous issues had been made.

Thanks for the feedback.

Average of ratings: -
In reply to Gary Lyon

Re: No certificate issue entry in database for some users

by Gary Lyon -

UPDATE/QUESTION: I upgraded to 2013102301 and the problem still exists. In reviewing the view.php code for Certificate I see the following code in lines 126 and 127:

// Add to log, only if we are reissuing

      add_to_log($course->id, 'certificate', 'view', "view.php?id=$cm->id", $certificate->id, $cm->id);

While I cannot find any controls for setting 'reissue' to one any more isn't this code saying there should be an option for allowing reissue of certificates and should this code make a fresh entry every time a certificate is reissued (assuming reissue is on)?

Why isn't the 'reissue' option available in this version?

Average of ratings: -
In reply to Gary Lyon

Re: No certificate issue entry in database for some users

by Gary Lyon -

Can anyone tell me what file/s and code are involved in making the database entry? I am not a programmer but I may have access to some folks who can help figure out why the database entries aren't being made and what can be done to work around our current issue. This whole thing is very strange because it only stopped working some time in mid/late May.

Thanks in advance.

Average of ratings: -
In reply to Gary Lyon

Re: No certificate issue entry in database for some users

by Gary Lyon -

UPDATE: When attempting to view user completion summary received the following (Developer-level debug was on):

Debug info: Objects are are not allowed: context_module
Error code: codingerror
Stack trace:
  • line 751 of /lib/dml/moodle_database.php: coding_exception thrown
  • line 770 of /lib/dml/moodle_database.php: call to moodle_database->detect_objects()
  • line 1122 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->fix_sql_params()
  • line 576 of /lib/filestorage/file_storage.php: call to oci_native_moodle_database->get_records_sql()
  • line 616 of /mod/certificate/lib.php: call to file_storage->get_area_files()
  • line 212 of /mod/certificate/lib.php: call to certificate_print_user_files()
  • line 122 of /report/outline/user.php: call to certificate_user_complete()
Any thoughts/suggestions are GREATLY appreciated. 
Average of ratings: -
In reply to Gary Lyon

Re: No certificate issue entry in database for some users

by Jean-Michel Védrine -

Hello Gary,

I think you have found a bug in the certificate code : in mod/certificate/lib.php the certificate_user_complete function is passing a context to the certificate_print_user_files function and it should pass a contextid.

Try to replace the lines 211-212 in mod/certificate/lib.php

 $cm = get_coursemodule_from_instance('certificate', $certificate->id, $course->id);
 certificate_print_user_files($certificate->id, $user->id, context_module::instance($cm->id));

With

$cm = get_coursemodule_from_instance('certificate', $certificate->id, $course->id);
$context = context_module::instance($cm->id);
certificate_print_user_files($certificate->id, $user->id, $context->id);

Unfortunately I am unable to test this today or tomorrow but it should fix the problem.


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

Re: No certificate issue entry in database for some users

by Gary Lyon -

Jean-Michel, you are awesome. Your fix did the trick. I am on 2.5 (Build: 20130808 ) and the certificate version is 2013102301.  Hopefully will soon update to at least 2.6 but until then as you may have read in my thread, I am having a real problem with the certificate module not updating the database when a user retakes a course and attempts to print a new certificate. (First time users are not an issue; the database entry in mdl_certificate_issues does its job and triggers an entry in our HR system). Our 'trigger' depends on this so it is causing me to have to manually transfer completion info to our HR system for any repeat users; at 20,900+ users the 'fun monkey' has definitely left the building!

Angry Fun Monkey

Any suggestions or do you know if this addressed in v2.6+?

Thanks again for the fix. 

Average of ratings: -
In reply to Gary Lyon

Re: No certificate issue entry in database for some users

by Gary Lyon -

(Keep in mind I am not a php developer as you read the following).

I see the following in lib.php lines 649-651 (see attachment):

 // Check if there is an issue already, should only ever be one

    if ($certissue = $DB->get_record('certificate_issues', array('userid' => $user->id, 'certificateid' => $certificate->id))) {

        return $certissue;

}

Immediately following this:

// Create new certificate issue record

    $certissue = new stdClass();

    $certissue->certificateid = $certificate->id;

    $certissue->userid = $user->id;

    $certissue->code = certificate_generate_code();

    $certissue->timecreated =  time();

    $certissue->id = $DB->insert_record('certificate_issues', $certissue);

Am I correct that commenting out lines 649-651 would prevent the check of exiting certificate issues for a user and would allow multiple entries (for any user) in the mdl_certificate_issues table?

Thanks for any feedback.


Average of ratings: -
In reply to Gary Lyon

Re: No certificate issue entry in database for some users

by Gary Lyon -

Update: 

I tested what I talked about in my last post. The system will now reissue but the list of reissued certificates increases each time I select the certificate link, not the 'Get Certificate' button (fig. 1). Strange thing is that the admin's report of what has been issued shows the proper number of issues (fig. 2).  Is the admin list of previous issues  (fig. 2) generated from log entries instead of entries into mdl_certificate_issues (fig 3)?  i looked at view.php and lib.php and am unsure of where to go at this point. 

All suggestions are appreciated. 

Fig. 1 list of issued certificates for admin (userid 2) and student (userid 3)




Fig 2. Admin view of issued certificates list in Moodle

List of issued certificates in moodle certificate instruction page

Fig. 3 mdl_certificate_issues table entries

entries in mdl_certificate_issues database table

Average of ratings: -
In reply to Gary Lyon

Re: No certificate issue entry in database for some users

by Jean-Michel Védrine -

Hello Gary,

Sorry, I missed that post.

You said:

Am I correct that commenting out lines 649-651 would prevent the check of exiting certificate issues for a user and would allow multiple entries (for any user) in the mdl_certificate_issues table?

No, no ! Don't do that !

All the certificate code is written on the assumption that there is only one record in the certificate_issues table for a given user and a given certificate instance.

If you remove these lines, you will create several records for the same user and the same certificate instance and strange things will happen (when the code will fetch the table to retrieve the  certificate for an user, as it expect only one to exists, you can't be sure of which one will be returned for instance).

Sorry I didn't saw this post sooner. If you have done this on your production server, I am sorry to report that data in your certificate_issues is now badly broken. And you need a php script that will delete all record but the newest for each userid and each certificateid.

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

Re: No certificate issue entry in database for some users

by Gary Lyon -

Bummer, I already put it on the production system. I assume a script like you describe could be initiated through my nightly cron so the d/b is reset each night?

(Anyone out there have such a script...again, my weakness is that I am not a developer.)

The capability to reissue certificates when a course has been retaken appears to have a lot of user interest. I certainly will testify to how important that option is. This module is so integral to Moodle I have a hard time understanding why it has not become part of the core release and get some full-time support. 

To borrow a phrase... C'est la vie!

Average of ratings: -