function certificate_cron() { // Get the data $_sku_cids = db_query("SELECT skus, cid FROM {certificates}"); $sku_cids = array(); while($sku_cid = db_fetch_object($_sku_cids)) { $sku_cids[$sku_cid->cid] = explode(',', $sku_cid->skus); } $_uid_skus = db_query("SELECT uid, sku FROM {attendance}"); $uid_skus = array(); while($uid_sku = db_fetch_array($_uid_skus)) { $uid_skus[$uid_sku['sku']][$uid_sku['uid']] = TRUE; } // Need this one to know who has already got one $_certificates_recieved = db_query("SELECT uid, cid FROM {certificates_recieved}"); $certificates_recieved = array(); while($certificate_recieved = db_fetch_array($_certificates_recieved)) { $certificates_recieved[$certificate_recieved['uid']][$certificate_recieved['cid']] = TRUE; } $finished = array(); foreach($sku_cids as $cid => $skus) { $completed = array(); foreach($skus as $sku) { foreach($uid_skus as $_sku => $uid) { if($_sku == $sku) { $_uid = array_keys($uid); $uid = $_uid[0]; $completed[$uid][] = $sku; } } } sort($skus, SORT_NUMERIC); foreach($completed as $uid => $user_skus) { sort($user_skus, SORT_NUMERIC); if($user_skus == $skus) { if(!isset($certificates_recieved[$uid][$cid])) { // User has completed certificate for the first time $finished[] = array('uid' => $uid, 'cid' => $cid); $certificates_recieved[$uid][$cid] = TRUE; } } } } $output = array(); foreach($finished as $finish) { $cert_name = db_fetch_object(db_query("SELECT certificate_name AS cert_name FROM {certificates} WHERE cid = %d", $finish['cid'])); $user_name = db_fetch_object(db_query("SELECT name FROM {users} WHERE uid = %d", $finish['uid'])); $link = l($user_name->name, 'user/'. $finish['uid'], array(), NULL, NULL, TRUE, FALSE); $output[] = $link ." has completed ". $cert_name->cert_name ." certificate"; db_query("INSERT INTO {certificates_recieved} VALUES(%d, %d)", $finish['cid'], $finish['uid']); } $output_html = implode("
", $output); drupal_mail("certificate_completed", variable_get("certificate_email", ""), "Subject goes here", $output_html); print $output_html; }