need to change random code to issued certicate #

need to change random code to issued certicate #

by Sandra King -
Number of replies: 15
Hello, I am trying to modify the certificate to return a certificate # issued by the state instead of the random code originally programmed. I am not much of a programmer, although I have had limited success with minor hacks in the past. I have already followed the instructions I found in this forum to insert fields that I created in the user profile for each student, and modified the certificate to match what we are currently using. I have attached a sample of my certificate (you can open the image in a new window to see it larger) samplecert.pdfNow I have to get the certificate # to display and record. My first idea was to set the id field in the certificate_issues table to the first number on our list, and then show that. It worked, except that it doesn't auto_increment the very next number on the list - and since we pay 2.50 per number to the state that would be very wasteful to us.

My second idea was to create a new table I call mdl_certificate_code and populate it with the next 200 numbers we will be using. Obviously I will have to keep going back to it and adding more numbers, but at least it should be able to make sure we issue only the correct numbers in order. When it runs out of numbers it should send a notice to the student that they must call the office to get a proper certificate mailed out. In other words it can't just automatically continue past the point of have pre-populated rows.

I have created the table in PhpMyadmin and right now it looks like this...

CREATE TABLE mdl_certificate_code (
id bigint(10) unsigned NOT NULL auto_increment,
certcode bigint(10) unsigned NOT NULL,
userid bigint(10) unsigned default NULL,
PRIMARY KEY (id)
My idea was to record the userid of the student who is issued the code, although it could record a timestamp instead if that requires less work. I have already put the userid in the first couple of rows and also added the certcode to the mdl_certificate_issues table
tablecertcode.gif
sample/certissues.gif

Could some one please help me with this. Thank you.


Average of ratings: -
In reply to Sandra King

Re: need to change random code to issued certicate #

by Sandra King -
I wonder if it would be better to use a field called 'issued' with a possible 0 or 1 to show whether a certificate number has been issued. It could update from 0 to 1 on issue, and the certificate_issues table already stores the code with the student's other information.

CREATE TABLE mdl_certificate_code (
id bigint(10) unsigned NOT NULL auto_increment,
certcode bigint(10) unsigned NOT NULL,
userid bigint(10) unsigned default NULL,
PRIMARY KEY (id)

Change to :
CREATE TABLE mdl_certificate_code (
id bigint(10) unsigned NOT NULL auto_increment,
certcode bigint(10) unsigned NOT NULL,
issued bigint(10) unsigned default NULL,
PRIMARY KEY (id)

I am currently reading tutorials on php and mysql to try to figure this out.
Average of ratings: -
In reply to Sandra King

Re: need to change random code to issued certicate #

by Raymond Fürst -
Hello Sandra,

I would discard the idea of an extra table, because you will need to find a way to match this table with the other certificate data.

I assume that you do not need the random code (for other courses perhaps?), so we can use it to store the actual state issued #.
Furthermore I assume that the state code numbers are sequential or can be generated using an algorithm.

My basic idea is to replace the function that generates the code with another one that picks or calculates the proper number.
In the file mod/certificate/lib.php there is a function certificate_prepare_issue that generates a new certificate issue for a student. It contains the line:

$code = certificate_generate_code();

Replace it with something like this:

$code = certificate_generate_serial_number();

Now you need to design a function certificate_generate_serial_number that somehow delivers the wanted number. It should read a global variable, return its value to be used in the issued certificate, modify it by adding one (or calculating the next possible value by other means) and store it.

The "print credit hours" field might be used as a variable.
Average of ratings: -
In reply to Raymond Fürst

Re: need to change random code to issued certicate #

by Sandra King -
Hello,

Thank you for trying to help. I like the idea of automatically issuing numbers in series, my only concern is that periodically the starting number is going to change. For example, we might start out issuing numbers from 1000 to 1200, however when we purchase the next block of certificate #s from the state they might start us at something line 87650. There is no way to know what the next number after 1200 will be until it is issued by the state.

So would something like

Start at 1000

Issue next number in series

repeat 200x then

echo "Warning!you will need to contact the office to get your certificate number";

That way we don't issue an invalid code,
however would we then be able to go back in and set the new start # and start issuing from there?

I think I can create or find the code to do that. I will spend a couple of hours on it today.
Average of ratings: -
In reply to Sandra King

Re: need to change random code to issued certicate #

by Raymond Fürst -
Hm, it's getting tricky.The drawback is: the student uses up the number (by downloading the certificate) that your organization has to purchase in advance. An automatic lock at the end of a block is very complicated.

The concept of buying a serial number from the state sounds strange to me. I wonder if those numbers cannot be available as a range, say all certificates issued from your organization have a number between 555-000.000.001 and 555-999.999.999 . Does your organization have to report who received wich certificate number? Can you return unused certificate numbers back to the state?

Back to the problem:
I suggest the "print credit hours" field as a place to store the next issued serial number. You can access it from the certificate form and fill in lets say "1000" as a starting value.

Then add the following function to the mod\certificate\lib.php library (in pseudocode):

function certificate_generate_serial_number(certificate$) {
$serialnumber = certificate$->printhours;
$newnumber = $serialnumber + 1; // (may need typecasting to integer)
certificate$->printhours = $newnumber; // (may need error correction and typecasting to string)
return $serialnumber;
}

In mod\certificate\lib.php replace
$code = certificate_generate_code();
with
$code = certificate_generate_serial_number(certificate$);

The first student who invokes his certificate creates a new issue and gets "1000" as the certificate # written in his "code" field. The function should write "1001" as the next number into the "print credit hours" field. The next student gets "1001" and changes to "1002".

As a Teacher, you must track and monitor the certificate numbers. Lets say the last available certificate number is "1200". Student number 201 can get the certificate with the number "1200", but before student number 202 opens his certificate, you must do something.

Either you edit the certificate instance and replace "1201" (next number) to "87650", as provided from the state. Or, before student number 202 opens his certificate, you must hide the certificate, until you receive the next starting number from the state. Then enter "87650" in the certificate instance and unhide it.

The "print credit hours" field is I think an integer. You might have to change it to long or more, if the number needs more digits.

Looks dirty, but might work.
Average of ratings: -
In reply to Raymond Fürst

Re: need to change random code to issued certicate #

by Sandra King -
Have you ever had one of those weeks?

I have the code to the point where it is generating the printhours field plus 1. However it is not updating that field, and now the studentname and coursename that were showing in the certificate before aren't there, and the issued certificate isn't recording into the database anymore either.

in lib.php I have this

function certificate_generate_number($certificate) {
$serialnumber = $certificate->printhours;
$newnumber = $serialnumber + 1; // (may need typecasting to integer)
$certificate->printhours = $newnumber; // (may need error correction and typecasting to string)
$code = $serialnumber;
return $code;
}

and in certificate.php I have
$code = '';
if($certificate->printnumber > 0) {
$code = $certificate->printhours; }


but I don't know what has gone wrong that the rest which used to work no longer works.

I am going to try to get the original lib.php and certificate.php and compare them to see if I inadvertently changed something else...

OK it is official - something I did on the new lib.php broke the studentname and coursename. I uploaded the old and that fixed that, and also the recording of the certificate issue.

Now I have to figure out how to get the printhours updated.
Average of ratings: -
In reply to Sandra King

Re: need to change random code to issued certicate #

by Sandra King -
This is crazy. As I said I uploaded the original lib.php and that fixed the studentname and coursename and the recording into certificate_issues.

I just went back and pasted in my edited code, and changed the original function - and it is broken again.

-----------
Okay have now figured out that putting in

function certificate_generate_number($certificate)
//this code breaks the certificate_issue and the two fields

function certificate_generate_number()
//with this code the certificate_isssue and two fields work but the number in print hours is sent, not the number we generated ( +1)
Average of ratings: -
In reply to Sandra King

Re: need to change random code to issued certicate #

by Raymond Fürst -
I think the function certificate_generate_number() must be invoked with a parameter, so it knows from which certificate instance it can take and update the certificate number.
Average of ratings: -
In reply to Raymond Fürst

Re: need to change random code to issued certicate #

by Sandra King -
Okay I now have it doing everything except updating the printhours field in the certificate table. I am wondering if it wouldn't be better to have it look up the last used code in certificate_issues instead?

Here is the code I am currently using...
in lib.php...
/************************************************************************
* Generates a 10-digit code of random letters and numbers.
Actually it is now supposed to generate a number based on the number recorded in print hours *
************************************************************************/
function certificate_generate_number($certificate) {
global $CFG, $USER, $certificate;

if ($certificate->printnumber > 0) {
$code = $certificate->printhours + 1;
return $code;
}
$certificate->printhours = $code;
return $certificate->printhours;
}

and in certificate.php
// Print the code number
$code = '';
if($certificate->printnumber > 0)
{ $code = $certrecord->code;
}

The result is that the pdf shows code 1001 --mdl_certificate_issues shows code 1001 -- but it doesn't update the mdl_certificate->printhours to 1001 (it stills shows 1000)--


Average of ratings: -
In reply to Sandra King

Re: need to change random code to issued certicate #

by Sandra King -
Okay it seems to me that if I can get the code to update_record properly I would be finished with this. When I use this code snippet

function certificate_generate_number($certificate) {
global $CFG, $USER, $certificate;

if ($certificate->printnumber > 0) {
$code = $certificate->printhours + 1;
return $code;

$certificate->printhours = $code;
update_record('certificate', addslashes_object($certificate->printhours));

every thing works as expected - except that it still doesn't update the field. I am sure this is just a beginners problem - I imagine that maybe I need to write a new function? and have it update the printhours? and also change what is inside the egg so that it correctly points to the certificate id 1?

It happens to be the only certificate in the course, but truly if the id field in the certificate_issue table would only increment by 1 I would set it to read that field, and print/save it as the code. All my troubles would be solved, and I wouldn't have to edit anything else - unfortunately, although I have discovered that I could probably reset the auto increment amount, if I am reading things correctly I would have to restart my server, and that means getting approval at the main office - not exactly fun or easy.
Average of ratings: -
In reply to Sandra King

Re: need to change random code to issued certicate #

by Raymond Fürst -
try
update_record('certificate',addslashes_object($certificate));
instead of
update_record('certificate',addslashes_object($certificate->printhours));

The function update_record can only update a complete data set , i.e. the certificate itself, and not just a single field. Since you modify only the ->printhours-field, all other fields do not matter.

I'm not sure wheather $certificate can be defined as a global variable, as it is passed as a function parameter. Maybe the second line should only contain "global $CFG,$USER;"


Average of ratings: -
In reply to Raymond Fürst

Re: need to change random code to issued certicate #

by Sandra King -
I tried your suggestion and several similar ideas that I came up with so far no dice - I can't get the certificate to update with the new printhours

Thank you for all your help though.
Average of ratings: -
In reply to Sandra King

Re: need to change random code to issued certicate #

by Sandra King -
Hello, I hope the answer to this is simple... but I can't seem to figure it out myself.

I couldn't figure out how to program the function in the certificate/lib.php to do what I needed it to do - grab the print hours from _certificate, use it as the code in lib.php and then add 1 to the field in certificate so that it worked again the next time it was called to generate a new certificate number.

I finally decided to write piece of code outside of moodle that does what I need. In less than 6 hours I had it done - but I can't get it to integrate with moodle so that it works there. I am posting my code in the hopes that someone else can tell me how to make it work...

I should let you know, as I was writing the code I had it echoing at each step of the process so that I could verify each piece worked. I then removed all of the echos once the program did what I needed. I have added a piece which checks a new table called mdl_certificate_code and updates each certificate code as issued so that we can use that field to insert the next series of numbers when they are given to us by the state.

I have also inserted a couple of emailed messages to me to let me know if we are running out, or completely out of codes - I haven't ever received the messages it is supposed to be sending, but it is successfully stopping the program from issueing certificates without codes.

Here is what my code looks like.

function certificate_generate_number() {
//Get ready to use certificate->printhours as the code in the certificate of completion...
// Retrieve all the data from the "mdl_certificate" table

$resultcertificate = mysql_query("SELECT * FROM mdl_certificate")
or die(mysql_error());

// store the record of the "mdl_certificate" table into $row
$rowcert = mysql_fetch_array( $resultcertificate );
// Print out the contents of the entry

$printhours = $rowcert['printhours'];

// Retrieve all the data from the "mdl_certificate" table

$result = mysql_query("SELECT * FROM mdl_certificate_code");
$num_rows = mysql_num_rows($result);

//select record
$resultcertificatecode = mysql_query("SELECT * FROM mdl_certificate_code WHERE certcode='$printhours'")
or die(mysql_error());

$rowcode = mysql_fetch_array( $resultcertificatecode );
if ($rowcode['issued'] == '1') {
$to = "sking@oltraining.com.com";
$subject = "Hi! You are issuing old certificate numbers /n";
$body = "Hi,\n\nYou are issuing old certificate numbers /n";
if (mail($to, $subject, $body)) {
echo "<p>Message successfully sent! You are issuing old certificate numbers</p>";
} }
$currentrows = $rowcode['id'];
//check to make sure certcodes are available
if ($num_rows - 1 < $currentrows) {
$to = "sking@oltraining.com.com";
$subject = "Hi! we have a problem here - we seem to be out of codes /n";
$body = "Hi,\n\nwe have a problem here - we seem to be out of codes /n";
if (mail($to, $subject, $body)) {
echo "<p>Message successfully sent! We have a problem here - we seem to be out of codes</p>";
}
}
else {
// Add 1 and Update mdl_certificate with the new printhours so that it is ready for next time.
// Retrieve all the data from the "mdl_certificate" table

$resultcertificate = mysql_query("SELECT * FROM mdl_certificate")
or die(mysql_error());

// store the record of the "mdl_certificate" table into $row
$rowcert = mysql_fetch_array( $resultcertificate );


$printhours = $rowcert['printhours'];
$newprinthours = $printhours +1;
$resultcertificate = mysql_query("UPDATE mdl_certificate SET printhours='$newprinthours' WHERE printhours='$printhours'")
or die(mysql_error());
//set code = $printhours for certificate of completion. This is the number actually printed on teh certificate ...
$code = $printhours;
//select record
// Update the line in mdl_certificate_code to let us know that a certificate code has been issued.

$resultcertificate = mysql_query("SELECT * FROM mdl_certificate WHERE id='1'")
or die(mysql_error());

$row = mysql_fetch_array( $resultcertificate );
$resultcertificate = mysql_query("UPDATE mdl_certificate_code SET issued='1' WHERE certcode='$printhours'")
or die(mysql_error());

//select record &
//check how many codes are left and send email if we are almost out of codes.

$resultcertificate = mysql_query("SELECT * FROM mdl_certificate_code WHERE certcode='$printhours'")
or die(mysql_error());

$row = mysql_fetch_array( $resultcertificate );

}if ($num_rows - 1 < $currentrows + 10) {
$to = "sking@oltraining.com.com";
$subject = "Hi! we have a problem here - we are almost out of codes /n";
$body = "Hi,\n\nwe have a problem here - we are almost out of codes /n";
if (mail($to, $subject, $body)) {
echo "<p>Message successfully sent! We have a problem here - we are almost out of codes</p>";

}

return $code;
}}

certificate_generate_number();

I plan to continue to try to edit this code to work in Moodle - but any and all help is appreciated.

Average of ratings: -
In reply to Sandra King

Re: need to change random code to issued certicate #

by Sandra King -

I did finally get the code working about 2 months ago.  I have a function stored in a new file and call it as needed.  I don't know why it works better that way but it does.

Average of ratings: -
In reply to Sandra King

Re: need to change random code to issued certicate #

by Javier Sánchez G. -

hello could you post the code to test it on a site I am creating, please thanks

Average of ratings: -
In reply to Javier Sánchez G.

Re: need to change random code to issued certicate #

by Sandra King -

Here is the final code I used for the issued certicate #.

In lib.php

I changed the section on Random code to this...

/************************************************************************
* Generates a 10-digit code of random letters and numbers.
Actually it is now supposed to generate a number based on the number recorded in print hours *
************************************************************************/
function certificate_generate_number($certificate) {
global $CFG, $USER, $certificate;

include("testpage.php");

return $code;
}

I also saved a new file in certificate called testpage.php (attached to this post) and created a new folder which for me is called type/trafficlandscape and my certificate has a field (the important part for this programmed number) that is:

cert_printtext(210, 400, 'C', 'Times', '', 18, utf8_decode($code));

I can post the entire file if you like, but it has been modified to match a certificate we were using in the physical classroom.

In my mysql database I created a new table mdl_certificate_code. You will need to populate this table with all of the codes you wish to draw from, if you run out of codes you should receive an error message, also if you accidentally reuse the same code.:

-- Table structure for table `mdl_certificate_code`
--

CREATE TABLE IF NOT EXISTS `mdl_certificate_code` (
`id` bigint(10) unsigned NOT NULL auto_increment,
`certcode` bigint(10) unsigned NOT NULL,
`issued` tinyint(1) unsigned NOT NULL default '0' COMMENT '1 for true 0 for false all should start with 0.',
PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Info about issued certificate codes' AUTO_INCREMENT=202 ;

Another table is mdl_certificate_issues - I don't recall if there were any changes in this table for the final version so I am including it for your comparison

CREATE TABLE IF NOT EXISTS `mdl_certificate_issues` (
`id` bigint(10) unsigned NOT NULL auto_increment,
`certificateid` bigint(10) unsigned NOT NULL default '0',
`userid` bigint(10) unsigned NOT NULL default '0',
`timecreated` bigint(10) unsigned NOT NULL default '0',
`studentname` varchar(40) NOT NULL default '',
`code` bigint(40) unsigned NOT NULL default '0',
`classname` varchar(254) NOT NULL default '',
`certdate` bigint(10) unsigned default '0',
`reportgrade` varchar(10) default NULL,
`mailed` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY  (`id`),
UNIQUE KEY `code` (`code`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Info about issued certificates' AUTO_INCREMENT=1452 ;

--

I believe that this is all of the changes I made to get my needed results.I hope this is helpfull if you have any more questions please let me know, I will try to check back more frequently.

Average of ratings: -