public function executeSql($query) { $result = mysqli_query($this->dbConnection, $query); if (!$result) { $logMessage = 'MySQL Error: ' . mysqli_error($this->dbConnection) . ". \nQuery: {$query}\n"; UpgradeLogger::writeErrorMessage($logMessage); } return $result; }
public function execute($request) { $dbInfo = $this->getUser()->getAttribute('dbInfo'); $taskNo = $request->getParameter('task'); UpgradeLogger::writeLogMessage('Task:' . $taskNo); $currentTask = "SchemaIncrementTask{$taskNo}"; UpgradeLogger::writeLogMessage("Running task class: {$currentTask}"); $task = new $currentTask($dbInfo); try { $task->execute(); } catch (Exception $e) { UpgradeLogger::writeErrorMessage("Error when running task: " . $e->getMessage() . ', stacktrace = ' . $e->getTraceAsString()); } $progeress = $task->getProgress(); $arr = array('progress' => $progeress); echo json_encode($arr); }
protected function addLeaveEntitlement() { $result = $this->upgradeUtility->executeSql("SELECT * FROM `ohrm_leave`"); if (!$result) { throw new Exception("SELECT * FROM ohrm_leave failed"); } while ($leave = mysqli_fetch_array($result)) { $status = $leave['status']; $new_entitlement_id = $leave['new_entitlement_id']; $length_days = $leave['length_days']; $leave_id = $leave['id']; $emp_number = $leave['emp_number']; $leave_type_id = $leave['leave_type_id']; $date = $leave['date']; if ($status != 4 && $status != 5) { $lengthCountRes = $this->upgradeUtility->executeSql("SELECT SUM(`length_days`) FROM `ohrm_leave` WHERE `new_entitlement_id` = " . $new_entitlement_id); if (!$lengthCountRes) { throw new Exception("lengthCountRes failed" . mysqli_e); } $lengthCountRow = mysqli_fetch_row($lengthCountRes); $leave_sum = $lengthCountRow[0]; $daysUsedRes = $this->upgradeUtility->executeSql("SELECT `no_of_days` - `days_used` FROM `ohrm_leave_entitlement` WHERE `id` = " . $new_entitlement_id); if (!$daysUsedRes) { throw new Exception("query failed"); } $daysUsedResRow = mysqli_fetch_row($daysUsedRes); $curr_bal = $daysUsedResRow[0]; //if no matching leave quota is there, you need to add to the ohrm_leave_entitlement, & take the leave entitlement id if ($new_entitlement_id == 0) { //$insert_ohrm_leave_entitlement_sql = "INSERT INTO `ohrm_leave_entitlement` (emp_number, no_of_days, leave_type_id, from_date, to_date, credited_date, note, entitlement_type, `deleted`) VALUES (" . $emp_number . ", 0.00, ". $leave_type_id . ", CONCAT(YEAR(" . $date . "), '-01-01'), CONCAT(YEAR(" . $date . "), '-12-31'), CONCAT(YEAR(" . $date . "), '-01-01'), 'added by the script', 1, 0) ;"; $leavePeriodForDate = $this->getLeavePeriodForDate($date); $from_date = $leavePeriodForDate[0]; $to_date = $leavePeriodForDate[1]; $insertOhrmLeaveEntitlementSql = "INSERT INTO `ohrm_leave_entitlement` (emp_number, no_of_days, leave_type_id, from_date, to_date, credited_date, note, entitlement_type, `deleted`) VALUES (" . $emp_number . ", 0.00, " . $leave_type_id . ", '" . $from_date . "', '" . $to_date . "', '" . $from_date . "', 'added by the script', 1, 0) ;"; $added1 = $this->upgradeUtility->executeSql($insertOhrmLeaveEntitlementSql); if ($added1) { $lastIdRes = $this->upgradeUtility->executeSql("SELECT LAST_INSERT_ID()"); $lastIdRow = mysqli_fetch_row($lastIdRes); $new_entitlement_id = $lastIdRow[0]; } else { UpgradeLogger::writeErrorMessage("Could not add: " . $new_entitlement_id . "!\nError: " . mysql_error()); throw new Exception("Upgrade Failed"); } } if ($leave_sum <= $curr_bal) { //insert length_days $insert_leave_leave_entitlement_sql = "INSERT INTO `ohrm_leave_leave_entitlement` (`leave_id`, `entitlement_id`, `length_days`) VALUES (" . $leave_id . ", " . $new_entitlement_id . ", " . $length_days . ");"; } elseif ($curr_bal > 0) { //insert curr_bal $insert_leave_leave_entitlement_sql = "INSERT INTO `ohrm_leave_leave_entitlement` (`leave_id`, `entitlement_id`, `length_days`) VALUES (" . $leave_id . ", " . $new_entitlement_id . ", " . $curr_bal . ");"; } if ($insert_leave_leave_entitlement_sql != NULL) { $added = $this->upgradeUtility->executeSql($insert_leave_leave_entitlement_sql); if (!$added) { UpgradeLogger::writeErrorMessage("Could not add: " . $new_entitlement_id . "!\nError: " . mysql_error()); throw new Exception("Upgrade Failed"); } } $insert_leave_leave_entitlement_sql = NULL; } } //drop unwanted column $drop_new_entitlement_id_sql = "alter table `ohrm_leave` drop column new_entitlement_id;"; $dropped = $this->upgradeUtility->executeSql($drop_new_entitlement_id_sql); if (!$dropped) { UpgradeLogger::writeErrorMessage("Could not drop column: " . $drop_new_entitlement_id_sql . "!\nError: " . mysql_error()); throw new Exception("Upgrade Failed"); } //set days_used $set_days_used_sql = "UPDATE `ohrm_leave_entitlement` le SET le.`days_used` = (SELECT SUM(l.`length_days`) FROM `ohrm_leave` l WHERE l.`emp_number` = le.`emp_number` AND l.`leave_type_id` = le.leave_type_id AND l.date BETWEEN le.from_date AND le.to_date);"; $saved = $this->upgradeUtility->executeSql($set_days_used_sql); if (!$saved) { UpgradeLogger::writeErrorMessage("Could not save leave entitlement!\nError: " . mysql_error()); throw new Exception("Upgrade Failed"); } }