/**
* Associate a list of students defined by a CSV list to an exam instance, checking with LDAP if necessary
* @global type $CFG
* @param type $id the examination instance
* @param type $file teh CSV file. It needs to have the header 'studentid'
* @return XML-formatted string containing a count of the successful operations, a count of the failed, or an error
*/
public function associateStudentsWithInstanceByCSV($id, $file)
{
session_start();
global $CFG;
$i = 0;
$length = 0;
//$conn = mysqli_connect($CFG->db, $CFG->dbuser, $CFG->dbuserpass, $CFG->schema) or die('<data><error>failed connecting to database</error><detail>' . mysqli_error($conn) . '</detail></data>');
try {
$conn = new PDO("mysql:host={$CFG->db};dbname={$CFG->schema}", $CFG->dbuser, $CFG->dbuserpass);
} catch (PDOException $e) {
die('<data><error>failed connecting to database</error><detail>' . $e->getMessage() . '</detail></data>');
}
$isfirstrow = true;
$studentnumrow = 0;
$siterow = 0;
$createsuccesscount = 0;
$createfailcount = 0;
$criticalerror = false;
$criticalerrordetails = "";
if ($CFG->use_ldap_for_student_lookups == true) {
// get it from LDAP
$authlib = new authlib();
// set up LDAP connection
$ldap = ldap_connect($CFG->student_ldap) or die('cannot connect to student directory');
$ldappassword = $CFG->student_ldap_adminpass;
$ldaprdn = $CFG->student_ldap_adminuser . $CFG->student_ldap_account_suffix;
ldap_set_option($ldap, LDAP_OPT_PROTOCOL_VERSION, 3);
ldap_set_option($ldap, LDAP_OPT_REFERRALS, 0);
$bind = ldap_bind($ldap, $ldaprdn, $ldappassword);
}
// get CSV
if (is_uploaded_file($file['tmp_name'])) {
if (($handle = fopen($file['tmp_name'], "r")) !== FALSE) {
// get the number of rows in the file
$linecount = count(file($file['tmp_name']));
// get the rows using headers, check for sanity
while (($data = fgetcsv($handle)) !== FALSE) {
// print_r($data);
if ($isfirstrow) {
if (array_search('studentid', $data) !== false) {
$studentnumrow = array_search('studentid', $data);
} else {
return '<data><error>Field header missing</error><detail>Needs to have a header called studentid</detail></data>';
}
if (array_search('site', $data) !== false) {
$siterow = array_search('site', $data);
} else {
return '<data><error>Field header missing</error><detail>Needs to have a header called site</detail></data>';
}
$isfirstrow = false;
} else {
// we've got the header fields, do something with them
// check that this student is already in the user table. If not, we'll have to check LDAP for an entry, and failing that we'll need to pass
$query = "SELECT COUNT(*) as count FROM students WHERE studentnum = :studentnum";
$stmt = $conn->prepare($query);
$stmt->bindValue(':studentnum', $data[$studentnumrow], PDO::PARAM_STR);
$stmt->execute() or die('<data><error>check studente exist query failed</error><detail><![CDATA[' . var_dump($stmt->errorInfo()) . ']]></detail></data>');
// if it doesn't exist in the student table, *AND* we're getting it from LDAP
if ($stmt->fetchObject()->count == 0) {
if ($CFG->use_ldap_for_student_lookups == true) {
// get it from LDAP
$filter = "(&(objectCategory=person)({$CFG->student_ldap_searchfield}={$CFG->student_ldap_search_prefix}{$data[$studentnumrow]}{$CFG->student_ldap_search_suffix}))";
$result = ldap_search($ldap, $CFG->student_ldap_base_dn, $filter);
// ldap_sort($ldap, $result, "sn");
$info = ldap_get_entries($ldap, $result);
// print_r($info);
// for ($i = 0; $i < $info["count"]; $i++) {
if ($info['count'] > 0) {
$criticalerror = false;
// check student
// $returnStr = "<fname>{$info[0][$CFG->student_ldap_fname][0]}</fname><lname>{$info[0][$CFG->student_ldap_lname][0]}</lname><email>{$info[0][$CFG->student_ldap_email][0]}</email>";
} else {
$criticalerror = true;
$criticalerrordetails .= 'The student ID ' . $studentnum . ' is not valid;';
}
// $studentdata = simplexml_load_string($authlib->getStudentDetailsFromLDAP($data[$studentnumrow]));
if (!$criticalerror) {
// $createfailcount++;
// insert it into the student table
$insertresult = $authlib->new_student($info[0][$CFG->student_ldap_fname][0], $info[0][$CFG->student_ldap_lname][0], $data[$studentnumrow], $info[0][$CFG->student_ldap_email][0], 0);
} else {
$criticalerror = true;
$criticalerrordetails .= 'The student ID ' . $data[$studentnumrow] . ' is not valid;';
}
}
$stmt->closeCursor();
// // set a progress variable as event
$i++;
echo round($i / $linecount * 100) . "%,";
ob_flush();
flush();
} else {
// there's an entry in the student table, but is there an entry in LDAP?
if ($CFG->use_ldap_for_student_lookups == true) {
// a MASSIVE hack- send out to the world a status update
$i++;
echo round($i / $linecount * 100) . "%,";
ob_flush();
flush();
$filter = "({$CFG->student_ldap_searchfield}={$CFG->student_ldap_search_prefix}{$data[$studentnumrow]}{$CFG->student_ldap_search_suffix})";
$result = ldap_search($ldap, $CFG->student_ldap_base_dn, $filter);
// ldap_sort($ldap, $result, "sn");
$info = ldap_get_entries($ldap, $result);
// print_r($info);
// for ($i = 0; $i < $info["count"]; $i++) {
if ($info['count'] > 0) {
$criticalerror = false;
}
if (isset($studentdata->error)) {
$criticalerror = true;
$criticalerrordetails .= 'No student with ID ' . $data[$studentnumrow] . ' in LDAP;';
$createfailcount++;
}
}
}
}
// moving on...
if (!$criticalerror) {
// check that this student hasn't already been associated with this exam
$query = "SELECT COUNT(*) as count FROM student_exam_instance_link WHERE students_ID = (SELECT ID FROM students WHERE studentnum = :studentnum) AND exam_instances_ID = :id";
$stmt = $conn->prepare($query);
$stmt->bindValue(':studentnum', $data[$studentnumrow], PDO::PARAM_STR);
$stmt->bindValue(':id', $id, PDO::PARAM_INT);
$stmt->execute() or die('<data><error>check linkage query failed</error><detail><![CDATA[' . var_dump($stmt->errorInfo()) . ']]></detail></data>');
//$result = mysqli_query($conn, $query) or die('<data><error>check linkage query failed</error><detail>' . mysqli_error($conn) . $query . '</detail></data>');
if ($stmt->fetchObject()->count == 0) {
$stmt->closeCursor();
$query = "SELECT ID FROM students WHERE studentnum = :studentnum";
// print($query);
$stmt = $conn->prepare($query);
$stmt->bindValue(':studentnum', $data[$studentnumrow], PDO::PARAM_STR);
$stmt->execute() or die('<data><error>check student ID query failed</error><detail>' . $stmt->errorCode() . '</detail></data>');
//$result2 = mysqli_query($conn, $query) or die('<data><error>check student ID query failed</error><detail>' . mysqli_error($conn) . $query . '</detail></data>');
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$studentid = $row['ID'];
}
if (isset($studentid)) {
$enumlib = new EnumLib();
$resultXMLStr = $this->associateStudentsWithInstance($id, $studentid, $enumlib->getSiteIDByShortCode($data[$siterow]));
$resultXML = simplexml_load_string($resultXMLStr);
// print_r($resultXML);
if ($resultXML->student[0]->id > 0) {
$createsuccesscount++;
} else {
$createfailcount++;
}
}
}
} else {
$createfailcount++;
$criticalerror = false;
}
}
}
fclose($handle);
}
return ",<data><success>{$createsuccesscount}</success><fail>{$createfailcount}</fail>" . (strlen($criticalerrordetails) > 0 ? "<error><detail>{$criticalerrordetails}</detail></error>" : "") . "<report></report></data>";
}
