public function importExcelData($id)
{
$sql = null;
$inputFileName = "uploads/" . $_FILES['file']['name'];
//create random string
$length = 10;
// Read your Excel workbook
$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
PHPExcel_Calculation::getInstance($objPHPExcel)->cyclicFormulaCount = 1;
$sheet = $objPHPExcel->getActiveSheet();
$highR = $sheet->getHighestRow();
$highC = $sheet->getHighestColumn();
$sheetData = $sheet->rangeToArray('A2:' . $highC . $highR, NULL, TRUE, FALSE);
for ($i = 0; $i < count($sheetData); $i++) {
// assign new temp password per excel record
$temp_password = random_str($length);
//random_str($length);
$temp_hash = password_hash($temp_password, PASSWORD_DEFAULT);
$empId = $sheetData[$i][0];
$fname = $sheetData[$i][1];
$surname = $sheetData[$i][2];
$email = $sheetData[$i][3];
$phone = $sheetData[$i][4];
$gender = $sheetData[$i][5];
// check if employee number exists
$stmt = $this->db->prepare("SELECT employee_number, Email FROM tat_user WHERE employee_number = '{$empId}'\n OR Email = '{$email}'");
$stmt->execute();
// insert student data
if ($stmt->rowCount() == 0) {
$stmt = $this->db->prepare("INSERT INTO tat_user (employee_number, First_Name, Last_Name, Email, Phone_Number, Gender, Password, Access_Level, Token)\n VALUES('{$empId}','{$fname}', '{$surname}', '{$email}',\n '{$phone}', '{$gender}','{$temp_password}', 'student', '{$temp_password}')");
$stmt->execute();
// insert user id and course id
$stmt = $this->db->prepare("SELECT Id FROM tat_user WHERE employee_number = '{$empId}'");
$stmt->execute();
$result = $stmt->fetch();
$sid = $result['Id'];
$stmt = $this->db->prepare("INSERT INTO tat_course_student (User_Id, Course_Instance_Id, Skills_Updated)\n VALUES('{$sid}','{$id}', 'No')");
$stmt->execute();
// send the emails to inform the students
$fullName = $fname . " " . $surname;
$email = new Emailer();
$email->sendStudentTempEmail($sheetData[$i][3], $fullName, $temp_password);
} else {
// check if student exists in course instance
$stmt = $this->db->prepare("SELECT employee_number, Email FROM tat_course_student AS cs \n LEFT JOIN tat_user AS u ON (cs.User_Id=u.id) \n WHERE cs.Course_Instance_Id={$id} \n AND (employee_number='{$empId}' OR Email='{$email}')");
$stmt->execute();
if ($stmt->rowCount() == 0) {
// insert user id and course id
$stmt = $this->db->prepare("SELECT Id FROM tat_user WHERE employee_number = '{$empId}'");
$stmt->execute();
$result = $stmt->fetch();
$sid = $result['Id'];
$stmt = $this->db->prepare("INSERT INTO tat_course_student (User_Id, Course_Instance_Id, Skills_Updated)\n VALUES('{$sid}','{$id}', 'No')");
$stmt->execute();
// send the emails to inform the students
$fullName = $fname . " " . $surname;
$email = new Emailer();
$email->sendStudentAddedToCourseEmail($sheetData[$i][3], $fullName);
} else {
$_SESSION['none_adds'][] = $empId . " " . $fname . " " . $surname . " " . $email;
}
}
}
}