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; } } } }