Esempio n. 1
0
 public static function process_emp_excel($file_name)
 {
     require_once 'Classes/PHPExcel.php';
     require_once 'Classes/PHPExcel/IOFactory.php';
     $auth = Zend_Auth::getInstance();
     if ($auth->hasIdentity()) {
         $loginUserId = $auth->getStorage()->read()->id;
     }
     $emp_model = new Default_Model_Employee();
     $usersModel = new Default_Model_Usermanagement();
     $identity_code_model = new Default_Model_Identitycodes();
     $objReader = PHPExcel_IOFactory::createReaderForFile($file_name);
     $objPHPExcel = $objReader->load($file_name);
     //Read first sheet
     $sheet = $objPHPExcel->getSheet(0);
     // Get worksheet dimensions
     $sizeOfWorksheet = $sheet->getHighestDataRow();
     $highestColumn = $sheet->getHighestDataColumn();
     if ($sizeOfWorksheet > 1) {
         $arrReqHeaders = array('Prefix', 'First name', 'Last name', 'Role Type', 'Email', 'Business Unit', 'Department', 'Reporting manager', 'Job Title', 'Position', 'Employment Status', 'Date of joining', 'Date of leaving', 'Experience', 'Extension', 'Work telephone number', 'Fax');
         //Get first/header from excel
         $firstRow = $sheet->rangeToArray('A' . 1 . ':' . $highestColumn . 1, NULL, TRUE, TRUE);
         $arrGivenHeaders = $firstRow[0];
         $diffArray = array_diff_assoc($arrReqHeaders, $arrGivenHeaders);
         $prefix_arr = $emp_model->getPrefix_emp_excel();
         $roles_arr = $emp_model->getRoles_emp_excel();
         $bu_arr = $emp_model->getBU_emp_excel();
         $dep_arr = $emp_model->getDep_emp_excel();
         $job_arr = $emp_model->getJobs_emp_excel();
         $positions_arr = $emp_model->getPositions_emp_excel();
         $users_arr = $emp_model->getUsers_emp_excel();
         $emp_stat_arr = $emp_model->getEstat_emp_excel();
         $dol_emp_stat_arr = $emp_model->getDOLEstat_emp_excel();
         $mng_roles_arr = $emp_model->getMngRoles_emp_excel();
         $emps_arr = $emp_model->getEmps_emp_excel();
         $emails_arr = $emps_arr['email'];
         $emp_ids_arr = $emps_arr['ids'];
         $emp_depts_arr = $emp_model->getEmpsDeptWise();
         $dept_bu_arr = $emp_model->getDeptBUWise();
         $pos_jt_arr = $emp_model->getPosJTWise();
         $identity_codes = $identity_code_model->getIdentitycodesRecord();
         $emp_identity_code = isset($identity_codes[0]) ? $identity_codes[0]['employee_code'] : "";
         $trDb = Zend_Db_Table::getDefaultAdapter();
         // starting transaction
         $trDb->beginTransaction();
         try {
             //start of validations
             $ex_prefix_arr = array();
             //$ex_fullname_arr = array();
             $ex_firstname_arr = array();
             $ex_lastname_arr = array();
             $ex_role_arr = array();
             $ex_email_arr = array();
             $ex_bu_arr = array();
             $ex_dep_arr = array();
             $ex_rm_arr = array();
             $ex_jt_arr = array();
             $ex_pos_arr = array();
             $ex_es_arr = array();
             $ex_doj_arr = array();
             $ex_dol_arr = array();
             $ex_exp_arr = array();
             $ex_ext_arr = array();
             $ex_wn_arr = array();
             $ex_fax_arr = array();
             $tot_rec_cnt = 0;
             $err_msg = "";
             for ($i = 2; $i <= $sizeOfWorksheet; $i++) {
                 $rowData_org = $sheet->rangeToArray('A' . $i . ':' . $highestColumn . $i, NULL, TRUE, TRUE);
                 $rowData = $rowData_org[0];
                 $rowData_cpy = $rowData;
                 foreach ($rowData_cpy as $rkey => $rvalue) {
                     $rowData[$rkey] = trim($rvalue);
                 }
                 //start of mandatory checking
                 if (empty($rowData[0])) {
                     $err_msg = "Prefix cannot be empty at row " . $i . ".";
                     break;
                 }
                 /* if(empty($rowData[1]))
                    {
                        $err_msg = "Full Name cannot be empty at row ".$i.".";
                        break;
                    }*/
                 if (empty($rowData[1])) {
                     $err_msg = "First name cannot be empty at row " . $i . ".";
                     break;
                 }
                 if (empty($rowData[2])) {
                     $err_msg = "Last name cannot be empty at row " . $i . ".";
                     break;
                 }
                 if (empty($rowData[3])) {
                     $err_msg = "Role type cannot be empty at row " . $i . ".";
                     break;
                 }
                 if (empty($rowData[4])) {
                     $err_msg = "Email cannot be empty at row " . $i . ".";
                     break;
                 }
                 if (empty($rowData[7])) {
                     $err_msg = "Reporting manager cannot be empty at row " . $i . ".";
                     break;
                 }
                 if (empty($rowData[8])) {
                     $err_msg = "Job title cannot be empty at row " . $i . ".";
                     break;
                 }
                 if (empty($rowData[9])) {
                     $err_msg = "Position cannot be empty at row " . $i . ".";
                     break;
                 }
                 if (empty($rowData[10])) {
                     $err_msg = "Employment status cannot be empty at row " . $i . ".";
                     break;
                 }
                 if (empty($rowData[11])) {
                     $err_msg = "Date of joining cannot be empty at row " . $i . ".";
                     break;
                 }
                 if (!in_array($rowData[3], $mng_roles_arr) && empty($rowData[6])) {
                     $err_msg = "Department cannot be empty at row " . $i . ".";
                     break;
                 }
                 if (in_array($rowData[10], $dol_emp_stat_arr) && empty($rowData[12])) {
                     $err_msg = "Date of leaving cannot be empty at row " . $i . ".";
                     break;
                 }
                 if (!in_array($rowData[10], $dol_emp_stat_arr) && !empty($rowData[12]) && in_array($rowData[10], $emp_stat_arr)) {
                     $err_msg = "Date of leaving must be empty for '" . $rowData[10] . "' at row " . $i . ".";
                     break;
                 }
                 // end of mandatory checking
                 // start of pattern checking
                 if (!preg_match("/^(?=.*[a-zA-Z])([^ ][a-zA-Z0-9 ]*)\$/", trim($rowData[0])) && !empty($rowData[0])) {
                     $err_msg = "Prefix is not a valid format at row " . $i . ".";
                     break;
                 }
                 /* if (!preg_match("/^([a-zA-Z.]+ ?)+$/", $rowData[1])  && !empty($rowData[1]))
                    {
                        $err_msg = "Full Name is not a valid format at row ".$i.".";
                        break;
                    }*/
                 if (!preg_match("/^([a-zA-Z.]+ ?)+\$/", $rowData[1]) && !empty($rowData[1])) {
                     $err_msg = "First name is not a valid format at row " . $i . ".";
                     break;
                 }
                 if (!preg_match("/^([a-zA-Z.]+ ?)+\$/", $rowData[2]) && !empty($rowData[2])) {
                     $err_msg = "Last name is not a valid format at row " . $i . ".";
                     break;
                 }
                 if (!preg_match("/^[a-zA-Z]+?\$/", $rowData[3]) && !empty($rowData[3])) {
                     $err_msg = "Role type is not a valid format at row " . $i . ".";
                     break;
                 }
                 if (!preg_match("/^(?!.*\\.{2})[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+\$/", $rowData[4]) && !empty($rowData[4])) {
                     $err_msg = "Email is not a valid format at row " . $i . ".";
                     break;
                 }
                 if (!preg_match("/^[a-zA-Z0-9\\&\\'\\.\\s]+\$/", $rowData[5]) && !empty($rowData[5])) {
                     $err_msg = "Business unit is not a valid format at row " . $i . ".";
                     break;
                 }
                 if (!preg_match("/^[a-zA-Z0-9\\&\\'\\.\\s]+\$/", $rowData[6]) && !empty($rowData[6])) {
                     $err_msg = "Department is not a valid format at row " . $i . ".";
                     break;
                 }
                 if (!preg_match("/^[a-zA-Z0-9\\&\\'\\.\\s]+\$/", $rowData[7]) && !empty($rowData[7])) {
                     $err_msg = "Reporting manager is not a valid format at row " . $i . ".";
                     break;
                 }
                 if (!preg_match("/^[a-zA-Z][a-zA-Z0-9\\s]*\$/", $rowData[8]) && !empty($rowData[8])) {
                     $err_msg = "Job title is not a valid format at row " . $i . ".";
                     break;
                 }
                 if (!preg_match("/^[a-zA-Z][a-zA-Z0-9\\-\\s]*\$/i", $rowData[9]) && !empty($rowData[9])) {
                     $err_msg = "Position is not a valid format at row " . $i . ".";
                     break;
                 }
                 if (!preg_match("/^(?=.*[a-zA-Z])([^ ][a-zA-Z0-9 ]*)\$/", $rowData[10]) && !empty($rowData[10])) {
                     $err_msg = "Employment status is not a valid format at row " . $i . ".";
                     break;
                 }
                 if (!empty($rowData[11])) {
                     try {
                         $test_doj = new DateTime($rowData[11]);
                     } catch (Exception $ex) {
                         return array('status' => 'error', 'msg' => "Date of joining is not a valid format at row " . $i . ".");
                     }
                 }
                 if (!empty($rowData[12])) {
                     try {
                         $test_dol = new DateTime($rowData[12]);
                     } catch (Exception $ex) {
                         return array('status' => 'error', 'msg' => "Date of leaving is not a valid format at row " . $i . ".");
                     }
                 }
                 if (!empty($rowData[12]) && $rowData[12] < $rowData[11]) {
                     $err_msg = "Date of leaving must be greater than date of joining at row " . $i . ".";
                     break;
                 }
                 if (!preg_match("/^[0-9]\\d{0,1}(\\.\\d*)?\$/", $rowData[13]) && !empty($rowData[13])) {
                     $err_msg = "Experience is not a valid format at row " . $i . ".";
                     break;
                 }
                 if (!preg_match("/^[0-9]+\$/", $rowData[14]) && !empty($rowData[14])) {
                     $err_msg = "Extension is not a valid format at row " . $i . ".";
                     break;
                 }
                 if (!preg_match("/^(?!0{10})[0-9\\+\\-\\)\\(]+\$/", $rowData[15]) && !empty($rowData[15])) {
                     $err_msg = "Work telephone number is not a valid format at row " . $i . ".";
                     break;
                 }
                 if (!preg_match("/^[0-9\\+\\-\\)\\(]+\$/", $rowData[16]) && !empty($rowData[16])) {
                     $err_msg = "Fax is not a valid format at row " . $i . ".";
                     break;
                 }
                 // end of pattern checking
                 // start of checking existence in the system.
                 if (!array_key_exists(strtolower($rowData[0]), $prefix_arr) && !empty($rowData[0])) {
                     $err_msg = "Unknown prefix at row " . $i . ".";
                     break;
                 }
                 if (!array_key_exists(strtolower($rowData[3]), $roles_arr) && !empty($rowData[3])) {
                     $err_msg = "Unknown role type at row " . $i . ".";
                     break;
                 }
                 if (!array_key_exists(strtolower($rowData[5]), $bu_arr) && !empty($rowData[5])) {
                     $err_msg = "Unknown business unit at row " . $i . ".";
                     break;
                 }
                 if (!array_key_exists(strtolower($rowData[6]), $dep_arr) && !empty($rowData[6])) {
                     $err_msg = "Unknown department at row " . $i . ".";
                     break;
                 }
                 if (in_array(strtolower($rowData[4]), $emails_arr) && !empty($rowData[4])) {
                     $err_msg = "Email already exists at row " . $i . ".";
                     break;
                 }
                 if (!in_array(strtolower($rowData[7]), $emp_ids_arr) && !empty($rowData[7])) {
                     $err_msg = "Unknown reporting manager at row " . $i . ".";
                     break;
                 }
                 if (!array_key_exists(strtolower($rowData[8]), $job_arr) && !empty($rowData[8])) {
                     $err_msg = "Unknown job title at row " . $i . ".";
                     break;
                 }
                 if (!array_key_exists(strtolower($rowData[9]), $positions_arr) && !empty($rowData[9])) {
                     $err_msg = "Unknown position at row " . $i . ".";
                     break;
                 }
                 if (!array_key_exists(strtolower($rowData[10]), $emp_stat_arr) && !empty($rowData[10])) {
                     $err_msg = "Unknown employment status at row " . $i . ".";
                     break;
                 }
                 // end of checking existence in the system.
                 if (!empty($rowData[6])) {
                     if (isset($emp_depts_arr[$dep_arr[strtolower($rowData[6])]]) && !in_array(strtolower($rowData[7]), $emp_depts_arr[$dep_arr[strtolower($rowData[6])]])) {
                         if (isset($emp_depts_arr[0]) && is_array($emp_depts_arr[0])) {
                             if (!in_array(strtolower($rowData[7]), $emp_depts_arr[0])) {
                                 $err_msg = "Reporting manager is not belongs to '" . $rowData[6] . "' department at row " . $i . ".";
                                 break;
                             }
                         }
                     }
                 } else {
                     if (isset($emp_depts_arr[0]) && is_array($emp_depts_arr[0])) {
                         if (!in_array(strtolower($rowData[7]), $emp_depts_arr[0])) {
                             $err_msg = "Reporting manager is not belongs to management group at row " . $i . ".";
                             break;
                         }
                     }
                 }
                 if (!empty($rowData[6])) {
                     if (isset($dept_bu_arr[0]) && is_array($dept_bu_arr[0])) {
                         if (in_array(strtolower($rowData[6]), $dept_bu_arr[0]) && !empty($rowData[5])) {
                             $err_msg = "Business unit not needed for this department '" . $rowData[6] . "' at row " . $i . ".";
                             break;
                         }
                         if (!in_array(strtolower($rowData[6]), $dept_bu_arr[0]) && empty($rowData[5])) {
                             $err_msg = "Business unit cannot be empty at row " . $i . ".";
                             break;
                         }
                     }
                     if (!empty($rowData[5])) {
                         if (isset($dept_bu_arr[$bu_arr[strtolower($rowData[5])]]) && !in_array(strtolower($rowData[6]), $dept_bu_arr[$bu_arr[strtolower($rowData[5])]]) && !empty($rowData[5])) {
                             $err_msg = "Department is not belongs to '" . $rowData[5] . "' business unit at row " . $i . ".";
                             break;
                         }
                     }
                 }
                 if (!empty($rowData[8]) && !empty($rowData[9])) {
                     if (isset($pos_jt_arr[$job_arr[strtolower($rowData[8])]]) && !in_array(strtolower($rowData[9]), $pos_jt_arr[$job_arr[strtolower($rowData[8])]]) && !empty($rowData[8])) {
                         $err_msg = "Position is not belongs to '" . $rowData[8] . "' job title at row " . $i . ".";
                         break;
                     }
                 }
             }
             //end of for loop
             if (!empty($err_msg)) {
                 return array('status' => 'error', 'msg' => $err_msg);
             }
             $err_msg = "";
             for ($i = 2; $i <= $sizeOfWorksheet; $i++) {
                 $rowData_org = $sheet->rangeToArray('A' . $i . ':' . $highestColumn . $i, NULL, TRUE, TRUE);
                 $rowData = $rowData_org[0];
                 $rowData_cpy = $rowData;
                 foreach ($rowData_cpy as $rkey => $rvalue) {
                     $rowData[$rkey] = trim($rvalue);
                 }
                 $ex_prefix_arr[] = $rowData[0];
                 //$ex_fullname_arr[] = $rowData[1];
                 $ex_firstname_arr[] = $rowData[1];
                 $ex_lastname_arr[] = $rowData[2];
                 $ex_role_arr[] = $rowData[3];
                 $ex_email_arr[$i] = $rowData[4];
                 $ex_bu_arr[] = $rowData[5];
                 $ex_dep_arr[] = $rowData[6];
                 $ex_rm_arr[] = $rowData[7];
                 $ex_jt_arr[] = $rowData[8];
                 $ex_pos_arr[] = $rowData[9];
                 $ex_es_arr[] = $rowData[10];
                 $ex_doj_arr[] = $rowData[11];
                 $ex_dol_arr[] = $rowData[12];
                 $ex_exp_arr[] = $rowData[13];
                 $ex_ext_arr[] = $rowData[14];
                 $ex_wn_arr[] = $rowData[15];
                 $ex_fax_arr[] = $rowData[16];
                 $tot_rec_cnt++;
             }
             foreach ($ex_email_arr as $key1 => $value1) {
                 $d = 0;
                 foreach ($ex_email_arr as $key2 => $value2) {
                     if ($key1 != $key2 && $value1 == $value2) {
                         $err_msg = "Duplicate email entry at row " . $key2 . ".";
                         $d++;
                         break;
                     }
                 }
                 if ($d > 0) {
                     break;
                 }
             }
             if (!empty($err_msg)) {
                 return array('status' => 'error', 'msg' => $err_msg);
             }
             //end of validations
             //start of saving
             if ($tot_rec_cnt > 0) {
                 for ($i = 2; $i <= $sizeOfWorksheet; $i++) {
                     $emp_id = $emp_identity_code . str_pad($usersModel->getMaxEmpId($emp_identity_code), 4, '0', STR_PAD_LEFT);
                     $rowData_org = $sheet->rangeToArray('A' . $i . ':' . $highestColumn . $i, NULL, TRUE, TRUE);
                     $rowData = $rowData_org[0];
                     $rowData_cpy = $rowData;
                     foreach ($rowData_cpy as $rkey => $rvalue) {
                         $rowData[$rkey] = trim($rvalue);
                     }
                     $emppassword = sapp_Global::generatePassword();
                     $date = new DateTime($rowData[11]);
                     $date_of_joining = $date->format('Y-m-d');
                     $date_of_leaving = "";
                     if ($rowData[12] != '') {
                         $ldate = new DateTime($rowData[12]);
                         $date_of_leaving = $ldate->format('Y-m-d');
                     }
                     $userfullname = $rowData[1] . ' ' . $rowData[2];
                     $user_data = array('emprole' => $roles_arr[strtolower($rowData[3])], 'userfullname' => $userfullname, 'firstname' => $rowData[1], 'lastname' => $rowData[2], 'emailaddress' => $rowData[4], 'jobtitle_id' => $job_arr[strtolower($rowData[8])], 'modifiedby' => $loginUserId, 'modifieddate' => gmdate("Y-m-d H:i:s"), 'emppassword' => md5($emppassword), 'employeeId' => $emp_id, 'modeofentry' => "Direct", 'selecteddate' => $date_of_joining, 'userstatus' => 'old');
                     $user_data['createdby'] = $loginUserId;
                     $user_data['createddate'] = gmdate("Y-m-d H:i:s");
                     $user_data['isactive'] = 1;
                     $user_id = $usersModel->SaveorUpdateUserData($user_data, '');
                     $data = array('user_id' => $user_id, 'reporting_manager' => $users_arr[strtolower($rowData[7])], 'emp_status_id' => $emp_stat_arr[strtolower($rowData[10])], 'businessunit_id' => !empty($rowData[4]) ? $bu_arr[strtolower($rowData[5])] : 0, 'department_id' => !empty($rowData[5]) ? $dep_arr[strtolower($rowData[6])] : null, 'jobtitle_id' => $job_arr[strtolower($rowData[8])], 'position_id' => $positions_arr[strtolower($rowData[9])], 'prefix_id' => $prefix_arr[strtolower($rowData[0])], 'extension_number' => $rowData[13] != '' ? $rowData[14] : NULL, 'office_number' => $rowData[14] != '' ? $rowData[15] : NULL, 'office_faxnumber' => $rowData[15] != '' ? $rowData[16] : NULL, 'date_of_joining' => $date_of_joining, 'date_of_leaving' => $date_of_leaving != '' ? $date_of_leaving : NULL, 'years_exp' => $rowData[13] == '' ? null : $rowData[13], 'modifiedby' => $loginUserId, 'modifieddate' => gmdate("Y-m-d H:i:s"));
                     $data['createdby'] = $loginUserId;
                     $data['createddate'] = gmdate("Y-m-d H:i:s");
                     $data['isactive'] = 1;
                     $emp_model->SaveorUpdateEmployeeData($data, '');
                     $text = "<div style='padding: 0; text-align: left; font-size:14px; font-family:Arial, Helvetica, sans-serif;'>\t\t\t\t\n\t<span style='color:#3b3b3b;'>Hello " . ucfirst($userfullname) . ",</span><br />\n\t\n\t<div style='padding:20px 0 0 0;color:#3b3b3b;'>You have been added to " . APPLICATION_NAME . ". The login credentials for your Sentrifugo account are:</div>\n\t\n\t<div style='padding:20px 0 0 0;color:#3b3b3b;'>Username: <strong>" . $emp_id . "</strong></div>\n\t<div style='padding:5px 0 0 0;color:#3b3b3b;'>Password: <strong>" . $emppassword . "</strong></div>\n\t\n\t<div style='padding:20px 0 10px 0;'>Please <a href='" . DOMAIN . "index/popup' target='_blank' style='color:#b3512f;'>click here</a> to login  to your Sentrifugo account.</div>\n\n</div>";
                     $options['subject'] = APPLICATION_NAME . ': Login Credentials';
                     $options['header'] = 'Greetings from Sentrifugo';
                     $options['toEmail'] = $rowData[4];
                     $options['toName'] = $userfullname;
                     $options['message'] = $text;
                     $options['cron'] = 'yes';
                     $result = sapp_Global::_sendEmail($options);
                 }
                 //end of for loop
                 $trDb->commit();
                 return array('status' => "success", 'msg' => 'Employees saved successfully.');
             } else {
                 return array('status' => 'error', 'msg' => "No records to save.");
             }
             //end of saving
         } catch (Exception $e) {
             $trDb->rollBack();
             return array('status' => 'error', 'msg' => "Something went wrong,please try again.");
         }
     } else {
         return array('status' => 'error', 'msg' => "No records to save.");
     }
 }