public function outputTemplate($name) { header("Pragma: public"); // required header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header(\ZP\ZP::FormatString('Content-Disposition: attachment;filename={0}.xlsx', $name)); header('Cache-Control: must-revalidate, post-check=0, pre-check=0'); //max-age=0'); //header("Cache-Control: private",false); header("Content-Type: application/force-download"); header("Content-Type: application/octet-stream"); header("Content-Type: application/download"); header("Content-Transfer-Encoding: binary"); $writer = \PHPExcel_IOFactory::createWriter($this->phpExcelObject, 'Excel2007'); $writer->save('php://output'); }
public function getLogData($start, $end, $modules = null, $events = null) { //exit(json_encode(func_get_args)); try { $sql = "SELECT p.name AS `module`, l.action, l.details, l.time, l.ip_address AS `ip`, u.name AS `user` " . "FROM log l " . "LEFT JOIN user u " . "ON l.userID = u.userID " . "LEFT JOIN permissions p " . "ON l.module = p.permissionID " . "WHERE (l.time BETWEEN :start AND :end) {0} " . "ORDER BY time DESC "; if (is_null($modules)) { $sql = ZP::FormatString($sql, ""); } else { $modules = implode(', ', $modules); //exit($events); //$events = "l.action = '" . implode("' OR l.action = '", $events) . "'"; //exit($events); $ev = "("; foreach ($events as $key => $value) { //exit(json_encode($key)); $ev .= strlen($ev) == 1 ? "l.action = :action{$key}" : " OR l.action = :action{$key}"; } $ev .= ")"; $sql = ZP::FormatString($sql, "AND l.module IN (:modules) AND ({$ev}) "); } $stmt = $this->getConnection()->prepare($sql); $stmt->bindParam(":start", $start, PDO::PARAM_STR); $stmt->bindParam(":end", $end, PDO::PARAM_STR); if (!is_null($modules)) { $stmt->bindParam(":modules", $modules, PDO::PARAM_STR); foreach ($events as $key => $value) { $stmt->bindValue(":action{$key}", $value, PDO::PARAM_STR); } //$stmt->bindValue(":events", $events, PDO::PARAM_STR); //$ "'" . implode("', ", $events) . "'", PDO::PARAM_STR); } //exit(json_encode(['success'=>false, 'sql'=>$sql, 'mo'=>$modules, 'e'=>$events, 's'=>$start, 'es'=>$end])); //exit(json_encode(['success'=>false, 'sql'=>$sql])); if ($stmt->execute()) { return ['success' => true, 'log' => $stmt->fetchAll(PDO::FETCH_OBJ)]; } return ['success' => false, 'error' => 'An internal error occurred could not fetch log items.']; } catch (\PDOException $e) { return ['success' => false, 'error' => 'An internal error occurred could not fetch log items.', 'e' => $e]; } }
public function createLLGGEOS() { $cols = ['A' => 'Key', 'B' => 'Beneficiary (Name of the Entity operating in mining sector)', 'C' => 'TPIN', 'D' => 'Total amount of the loan/loans guarantee', 'E' => 'Date of the Grant', 'F' => 'Repayment Period', 'G' => 'Interest Rate %', 'H' => 'Outstanding amount not reimbursed on 31/12/' . $this->period, 'I' => 'Amount reimbursed during the year', 'J' => 'Other Comments']; $template_name = 'Loans or Loan Guarantee Granted to Entities Operating in Extractive Sector'; $this->phpExcelObject->getProperties()->setDescription($template_name . " Template."); $sheet = $this->getActiveSheet(); $sheet->setTitle('Template'); $sheet->getProtection()->setInsertRows(false); // enable inserting rows //$sheet->getColumnDimension('C')->setVisible(false); // hide first column // write sheet defaults $this->writeDefaults($sheet, $template_name); // set columns $sheet->setCellValue('E9', 'Terms of the Transaction'); $sheet->mergeCells('E9:I9'); $this->styleColumns('E9:I9', $sheet); foreach ($cols as $k => $v) { $sheet->setCellValue($k . '10', $v); //$sheet->mergeCells(ZP::FormatString('{0}10:{1}13', $k, $k)); if (strcmp($k, 'B') == 0) { $sheet->getColumnDimension($k)->setWidth(35); } else { $sheet->getColumnDimension($k)->setWidth(25); } } $r = 30; // write report data if (!is_null($this->report)) { $r = 11; foreach ($this->report as $v) { $sheet->setCellValue("A" . $r, $v->loanID); $sheet->setCellValue("B" . $r, $v->name); $sheet->setCellValue("C" . $r, $v->beneficiaryID); $sheet->setCellValue("D" . $r, $v->amount); $sheet->setCellValue("E" . $r, $this->dateToDD_MM_YYYYY($v->dateOfGrant)); $sheet->setCellValue("F" . $r, $v->repaymentPeriod); $sheet->setCellValue("G" . $r, $v->interestRate); $sheet->setCellValue("H" . $r, $v->amountOutstandingYearEnd); $sheet->setCellValue("I" . $r, $v->amountPayedInYear); $sheet->setCellValue("J" . $r, $v->comments); $r++; } } // style cells $this->styleColumns('A10:J10', $sheet); $sheet->getStyle('A10:J10')->getAlignment()->setWrapText(true); // unprotect editable cells of the sheet $sheet->getStyle('B11:J' . ($r - 1))->getProtection()->setLocked(\PHPExcel_Style_Protection::PROTECTION_UNPROTECTED); $sheet->getStyle(ZP::FormatString('B{0}:J{1}', $r, $r + 10))->getProtection()->setLocked(\PHPExcel_Style_Protection::PROTECTION_UNPROTECTED); // format cell value as text $this->formatCellValue('E11:J' . ($r + 10), 'text'); $this->formatCellValue('C11:C' . ($r + 10), 'text'); $this->formatCellValue('G11:G' . ($r + 10), 'number_00'); $this->formatCellValue('D11:D' . ($r + 10)); $this->formatCellValue('H11:I' . ($r + 10)); // write sign off $this->signOff(1, $r + 15); $this->setActiveSheet(1); $sheet = $this->getActiveSheet(); $sheet->setTitle('Entities in Extractive Sector'); $companies = ZP::getExtractiveCompanies(); $sheet->setCellValue("B2", "Extractive Company"); $sheet->getColumnDimension("B")->setWidth(60); $sheet->setCellValue("C2", "TPIN"); $sheet->getColumnDimension("C")->setWidth(20); $this->styleColumns('B2:C2', $sheet); $r = 3; foreach ($companies as $mine) { $sheet->setCellValue("B" . $r, $mine->key); $sheet->setCellValue("C" . $r, $mine->TPIN); $r++; } $this->setActiveSheet(0); $this->outputTemplate($template_name); }
public function getColumns($months, $has_year = false, $year = 2016, $type = 'type') { //exit(''.$year); //print($year."\n"); if ($has_year) { $cols = strcmp($type, 'type') == 0 ? ", COALESCE((m.jan + m.feb + m.mar + m.april + m.may + m.jun + m.jul + m.aug + m.sep + m.oct + m.nov + m.dec_r), '') AS `{$year}` " : ", COALESCE((SUM(m.jan) + SUM(m.feb) + SUM(m.mar) + SUM(m.april) + SUM(m.may) + SUM(m.jun) + " . "SUM(m.jul) + SUM(m.aug) + SUM(m.sep) + SUM(m.oct) + SUM(m.nov) + SUM(m.dec_r)), '') AS `{$year}`"; } else { $cols = ", '' AS `Total Annual` "; } $num = (object) ['jan' => 1, 'feb' => 2, 'mar' => 3, 'april' => 4, 'may' => 5, 'jun' => 6, 'jul' => 7, 'aug' => 8, 'sep' => 9, 'oct' => 10, 'nov' => 11, 'dec_r' => 12]; foreach ($months as $mon) { if (strcmp($type, 'type') == 0) { $cols .= ZP::FormatString(", COALESCE(m.{0}, '') AS '{1}.{2}' ", $mon, $year, $mon); } else { $cols .= ZP::FormatString(", COALESCE(SUM(m.{0}), '') AS '{1}.{2}' ", $mon, $year, $mon); } } //exit($year); return $cols; }
public static function downloadItem($id) { try { $stmt = Connection::getConnection()->prepare("SELECT original_name AS `name` " . "FROM payment_flow_details " . "WHERE ref_name = :ID"); $stmt->bindParam(":ID", $id, PDO::PARAM_INT); if ($stmt->execute()) { $receipt = $stmt->fetch(PDO::FETCH_OBJ); if (property_exists($receipt, "name")) { $mime = array("pdf" => "application/pdf", "bmp" => "image/bmp", "png" => "image/png", "jpeg" => "image/jpeg", "jpg" => "image/jpeg"); $file = "../resources/pfd_receipts/" . $id; $mime = $mime[strtolower(array_pop(explode(".", $receipt->name)))]; header("Pragma: public"); // required header('Content-Type: ' . $mime); header(ZP::FormatString('Content-Disposition: attachment;filename={0}', $receipt->name)); header('Cache-Control: must-revalidate, post-check=0, pre-check=0'); //max-age=0'); header("Content-Transfer-Encoding: binary"); header('Content-Length: ' . filesize("../resources/pfd_receipts/" . $id)); ob_clean(); flush(); readfile($file); exit; } exit("Receipt with specified key does not exist."); } exit("Internal Error occurred, unable to download receipt."); } catch (\Exception $e) { //exit(json_encode($e)); exit("Internal Error occurred, unable to download receipt."); } }
public function readSPD() { $man_errors = []; $vol_errors = []; $mandatory = []; $voluntary = []; $data = $this->getSheetData(); $defaults = $this->defaults; $defaults->delete = ['mandatory' => [], 'voluntary' => []]; for ($i = 11; $i < count($data); $i++) { $hasErrors = false; $A = strcmp($data[$i]['A'], '') == 0 ? null : $data[$i]['A']; // key $B = trim($data[$i]['B']); // beneficiary $C = trim($data[$i]['C']); // beneficiary location $D = trim($data[$i]['D']); // amount zmw $E = trim($data[$i]['E']); // date $F = trim($data[$i]['F']); // description $G = trim($data[$i]['G']); // project cost incurred $H = trim($data[$i]['H']); // Legal basis of the payment (REf to the agreement ,Act, ..)* $Z = trim($data[$i]['Z']); // beneficiaryID //exit(json_encode(['success'=>false, 'row'=>$data[$i]])); if (strcmp($B, 'Total') == 0) { if (count($man_errors) > 0 || count($mandatory) >= 0) { break; } exit(json_encode(['success' => false, 'error' => 'Data was not uploaded. Please download the templates, fill it with data and try again.'])); } else { if (!is_null($A) && strlen($B) == 0 && strlen($C) == 0 && strlen($D) == 0 && strlen($E) == 0 && strlen($F) == 0 && strlen($G) == 0 && strlen($H) == 0) { array_push($defaults->delete['mandatory'], $A); } else { if (strlen($B) > 0 || strlen($C) > 0 || strlen($D) > 0 || strlen($E) > 0 || strlen($F) > 0 || strlen($G) > 0 || strlen($H) > 0) { //exit(json_encode(['success'=>false, 'row'=>$B])); if (strlen($B) == 0) { $man_errors['B' . $i] = 'Please input a valid beneficiary'; $hasErrors = true; } if (strlen($C) == 0) { $man_errors['C' . $i] = 'Please input a valid location'; $hasErrors = true; } if (!is_numeric($D) || doubleval($D) < 0) { $man_errors['D' . $i] = 'Please input a number in this field'; $hasErrors = true; } if (!\ZP\ZP::isDate($E, 'd/m/Y')) { $man_errors['E' . $i] = 'Please input date in the format dd/mm/yyyy'; $hasErrors = true; } else { $E = preg_split('/[\\/-]/', $E); $E = ZP::FormatString("{0}-{1}-{1}", $E[2], $E[1], $E[0]); } if (!is_numeric($G) || doubleval($G) < 0) { $man_errors['G' . $i] = 'Please input a number in this field'; $hasErrors = true; } //exit(json_encode(['success'=>false, 'hasErrors'=>$hasErrors, 'man_errors'=>$man_errors])); if (!$hasErrors) { array_push($mandatory, (object) array('key' => $A, 'beneficiaryID' => $Z, 'beneficiary' => $B, 'location' => $C, 'cash' => $D, 'date' => $E, 'description' => $F, 'cost_incurred' => $G, 'legalBasis' => $H)); } } } } } $this->setSheetData(1); $data = $this->getSheetData(); for ($i = 12; $i < count($data); $i++) { $hasErrors = false; $A = strcmp($data[$i]['A'], '') == 0 ? null : $data[$i]['A']; // key $B = trim($data[$i]['B']); // beneficiary $C = trim($data[$i]['C']); // beneficiary location $D = trim($data[$i]['D']); // amount zmw $E = trim($data[$i]['E']); // date $F = trim($data[$i]['F']); // description $G = trim($data[$i]['G']); // project cost incurred $Z = trim($data[$i]['Z']); // beneficiaryID //exit(json_encode($data[$i])); if (strcmp($B, 'Total') == 0) { //exit(json_encode($voluntary)); // array_merge($man_errors, $vol_errors) if (count(array_merge($man_errors, $vol_errors)) > 0 && count(array_merge($man_errors, $vol_errors)) <= 6) { $errors = ['Mandatory' => $man_errors, 'Voluntary' => $vol_errors]; return ['success' => false, 'data' => $defaults, 'errors' => $errors]; } else { if (count(array_merge($man_errors, $vol_errors)) > 7) { $errors = ['Mandatory' => $man_errors, 'Voluntary' => $vol_errors]; return ['success' => false, 'data' => $defaults, 'errors' => $errors, 'msg' => 'There are multiple errors in the submission.']; } else { if (count($voluntary) > 0 || count($mandatory) > 0) { $defaults->data = ['mandatory' => $mandatory, 'voluntary' => $voluntary]; return ['success' => true, 'data' => $defaults]; } } } exit(json_encode(['success' => false, 'error' => 'Data was not uploaded. Please download the templates, fill it with data and try again.'])); } else { if (!is_null($A) && strlen($B) == 0 && strlen($C) == 0 && strlen($D) == 0 && strlen($E) == 0 && strlen($F) == 0 && strlen($G) == 0) { array_push($defaults->delete['voluntary'], $A); } else { if (strlen($B) > 0 || strlen($C) > 0 || strlen($D) > 0 || strlen($E) > 0 || strlen($F) > 0 || strlen($G) > 0) { if (strlen($B) == 0) { $vol_errors['B' . $i] = 'Please input a valid beneficiary'; $hasErrors = true; } if (strlen($C) == 0) { $vol_errors['C' . $i] = 'Please input a valid location'; $hasErrors = true; } if (!is_numeric($D) || doubleval($D) < 0) { $vol_errors['D' . $i] = 'Please input a number in this field'; $hasErrors = true; } if (!\ZP\ZP::isDate($E, 'd/m/Y')) { $vol_errors['E' . $i] = 'Please input date in the format dd/mm/yyyy'; $hasErrors = true; } else { $E = preg_split('/[\\/-]/', $E); $E = ZP::FormatString("{0}/{1}/{2}", $E[2], $E[1], $E[0]); //preg_replace('/\//', '-', $E); //exit(json_encode(['success'=>false, 'E'=>$E])); } if (!is_numeric($G) || doubleval($G) < 0) { $vol_errors['G' . $i] = 'Please input a number in this field'; $hasErrors = true; } //exit(json_encode(['success'=>false, 'hasErrors'=>$hasErrors, 'vol_errors'=>$vol_errors])); if (!$hasErrors) { array_push($voluntary, (object) array('key' => $A, 'beneficiaryID' => $Z, 'beneficiary' => $B, 'location' => $C, 'cash' => $D, 'date' => $E, 'description' => $F, 'cost_incurred' => $G)); } } } } } }
private function addDefaultGroups($ID, $type) { try { $sql = "SELECT groupID FROM {0}"; if (strcmp($type, 'gov_entity') == 0) { $stmt = $this->getConnection()->prepare(ZP::FormatString($sql, "goventity_groups WHERE entityID = :ID;")); } else { $stmt = $this->getConnection()->prepare(ZP::FormatString($sql, "extractivecomp_groups WHERE TPIN = :ID;")); } $stmt->bindParam(":ID", $ID, PDO::PARAM_INT); if ($stmt->execute()) { $groupID = 0; if (count($stmt->fetchAll()) == 0) { $names = ['Standard Users', 'Management Users']; foreach ($names as $name) { $stmt = $this->getConnection()->prepare("INSERT INTO `group` (name) VALUES (:name);"); $stmt->bindValue(":name", $name, PDO::PARAM_STR); $stmt->execute(); $lastInsertID = $this->getConnection()->lastInsertId(); if (strcasecmp($name, 'Management Users') == 0) { $groupID = $lastInsertID; } $sql = "INSERT INTO {0} groupID) VALUES(:ID, :groupID);"; if (strcmp($type, 'gov_entity') == 0) { $stmt = $this->getConnection()->prepare(ZP::FormatString($sql, "goventity_groups (entityID, ")); } else { $stmt = $this->getConnection()->prepare(ZP::FormatString($sql, "extractivecomp_groups (TPIN, ")); } $stmt->bindValue(":ID", $ID, PDO::PARAM_INT); $stmt->bindValue(":groupID", $lastInsertID, PDO::PARAM_INT); $stmt->execute(); $permissions = strcmp($type, 'gov_entity') == 0 ? "({$lastInsertID}, 9)" : "({$lastInsertID}, 9), ({$lastInsertID}, 10)"; $stmt = $this->getConnection()->prepare("INSERT INTO group_permissions (groupID, permissionID) VALUES {$permissions};"); $stmt->execute(); } } return $groupID | true; } return false; } catch (\PDOException $e) { return false; } }
public function deleteRow($key, $table, $reportID) { try { switch ($table) { case 'minerals_export_details': $table = 'minerals_export_details'; $col = ["outputTypeID", "reportID"]; break; case 'minerals_production_details': $table = 'minerals_production_details'; $col = ["typeID", "reportID"]; break; case 'payment-payment_flow_details': $col = 't.paymentID'; $table = 'payment_flow_details'; break; case 'beneficiary_ownership_details': $col = 't.beneficialOwnerID'; $table = 'beneficiary_ownership_details'; break; case 'private_stakeholder_declaration': $table = 'private_stakeholder_declaration'; $col = 't.privateStakeholderID'; break; case 'politically_exposed_person': $col = 't.pepID'; $table = 'politically_exposed_person'; break; case 'transctions_iba_details': $col = 't.detailsID'; $table = 'transctions_iba_details'; break; case 'interests_in_extractives_details': $col = 't.interestID'; $table = 'interests_in_extractives_details'; break; case 'loans_to_extractivesector_details': $col = 't.loanID'; $table = 'loans_to_extractivesector_details'; break; case 'mandatory': $col = 't.paymentID'; $table = 'mandatory_payment'; break; case 'voluntary': $col = 't.paymentID'; $table = 'voluntary_payment'; break; default: $table = false; } if ($table) { ///$this->getConnection()->beginTransaction(); //exit('hereaaaaaavvvv'); if (is_array($col)) { $cols = ZP::FormatString("({0}) IN (", implode(",", $col)); foreach ($key as $k => $v) { $pair = ZP::FormatString("({0}, {1})", intval($v), intval($reportID)); $cols .= $k == 0 ? $pair : ", " . $pair; } $cols .= ")"; //exit(json_encode(['query'=>"DELETE t FROM $table t WHERE $cols;"])); $query = $this->getConnection()->prepare("DELETE t FROM {$table} t WHERE {$cols}"); $stmt = $this->getConnection()->prepare("SELECT * FROM {$table} t WHERE {$cols}"); $stmt->execute(); $deleted = $stmt->fetchAll(PDO::FETCH_OBJ); } else { $key = is_array($key) ? implode(",", $key) : $key; // need to find better fix for this if (preg_match("/[a-zA-Z]+/", $key) === 1) { exit(json_encode(['success' => false, 'error' => 'Could not delete rows. Please select valid row(s) to delete.'])); } $stmt = $this->getConnection()->prepare("SELECT * FROM {$table} t WHERE {$col} IN ({$key});"); $stmt->execute(); $deleted = $stmt->fetchAll(PDO::FETCH_OBJ); $query = $this->getConnection()->prepare("DELETE t FROM {$table} t WHERE {$col} IN ({$key});"); //exit(json_encode(['query'=>"DELETE t FROM $table t WHERE $col IN ($key);"])); } if ($query->execute()) { $deleted = json_encode($deleted); while (strlen($deleted) > 500) { $substr = substr($deleted, 0, 499); $deleted = substr($deleted, 500); ZP::log('Template Row Deletion', $substr, $_SESSION['user']->getID(), $_SESSION['module_number']); } ZP::log('Template Row Deletion', $deleted, $_SESSION['user']->getID(), $_SESSION['module_number']); // handle case of mandatory and voluntary payment if (strcasecmp($table, 'voluntary_payment') == 0 || strcasecmp($table, 'mandatory_payment') == 0) { $table = 'social_payment'; } $stmt = $this->getConnection()->prepare("SELECT t.reportID " . "FROM {$table} t " . "WHERE t.reportID = :reportID"); $stmt->bindValue(":reportID", $reportID, PDO::PARAM_INT); if ($stmt->execute()) { if (count($stmt->fetchAll(PDO::FETCH_OBJ)) == 0) { $stmt = $this->getConnection()->prepare("DELETE r FROM report r WHERE r.reportID = :reportID"); $stmt->bindValue(":reportID", $reportID, PDO::PARAM_INT); if (!$stmt->execute()) { exit(json_encode(["success" => false, 'error' => 'Internal error occurred, could not delete report.'])); } ZP::log('Report Deletion', 'Report deleted', $_SESSION['user']->getID(), $_SESSION['module_number']); } } else { exit(json_encode(["success" => false, 'error' => 'Internal error occurred, could not delete row(s). Invalid query statement.'])); } } else { exit(json_encode(["success" => false, 'error' => 'Internal error occurred, could not delete row(s)'])); } } else { exit(json_encode(["success" => false, 'error' => 'Internal error occurred, could not delete row(s). Invalid arguments specified.'])); } } catch (\PDOException $e) { //return 'Deletion error: '.$e->getMessage(); exit(json_encode(["success" => false, 'error' => 'Internal error occurred, could not delete row(s).', 'e' => $e])); } }
public function createModules($permissions) { $str = '<div class="module-cover"><div class="module-sub-cover"><div id="" class="module">' . '<div class="module-icon {0}"></div><div class="module-name {1}">{2}</div></div></div></div>'; $modules = '<div id="modulebar">'; foreach ($permissions as $x) { $class = preg_replace("/'/", "", $x->name); $class = strtolower(str_replace(" ", "-", $class)); $modules .= ZP::FormatString($str, $class, $class . '-name', $x->name); } return $modules . '</div>'; }