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 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.");
}
}
$action = $_REQUEST['action'];
$dbaccess = new ZP\MEDBAccess();
switch ($action) {
case 'addEntity':
$entity = json_decode($_REQUEST['entity']);
$user = isset($_REQUEST['user']) ? json_decode($_REQUEST['user']) : false;
$_REQUEST['success'] = false;
//exit(json_encode($_REQUEST));
if ($user) {
print json_encode($dbaccess->addEntity($entity, $user));
} else {
print json_encode($dbaccess->addEntity($entity));
}
break;
case 'getEntities':
\ZP\ZP::log('Opened Manage Entities', 'Opened Manage Entities', $_SESSION['user']->getID(), $_SESSION['module_number']);
print json_encode($dbaccess->getEntities());
break;
case 'getEntityDetails':
$ID = json_decode($_REQUEST['ID']);
print json_encode($dbaccess->getEntities($ID));
break;
case 'findEntity':
$queryString = $_REQUEST['queryString'];
print json_encode($dbaccess->findEntity($queryString));
break;
case 'deleteEntity':
$ID = json_decode($_REQUEST['ID']);
//exit(json_encode(['success'=>false, 'id'=>$ID]));
print json_encode($dbaccess->deleteEntity($ID));
break;
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));
}
}
}
}
}
}
$reportID = intval($_REQUEST['reportID']);
$status = $_REQUEST['status'];
$mine = intval($_REQUEST['mine']);
$entity = intval($_REQUEST['entity']) > 1 ? intval($_REQUEST['entity']) : null;
$templateID = intval($_REQUEST['templateID']);
$period = intval($_REQUEST['period']);
if (!in_array($status, ['Approved', 'Rejected', 'Not Approved'])) {
exit(json_encode(['success' => false, 'message' => 'Please select a valid status.']));
}
$dbaccess->approveSubmission($reportID, $status, $mine, $entity, $templateID, $period);
print json_encode($dbaccess->getSubmittedReports(date('Y')));
} else {
print json_encode(['success' => false, 'message' => 'You do not have permission to approve or reject a submission.']);
}
break;
case 'download-receipt':
$id = $_REQUEST['id'];
\ZP\ZP::downloadItem($id);
break;
case 'delete-receipt':
$id = $_REQUEST['id'];
if (strlen(trim($id))) {
$dbaccess->deleteReceipt($id);
} else {
print json_encode(['success' => false, 'error' => 'Please ensure you select a valid receipt']);
}
break;
default:
print json_encode(["success" => false, 'exception' => 'Invalid action']);
}
}
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>';
}
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 send($recipient, $subject, $message, $cc = null, $bcc = null, $signature = "Thanks,<br/></br>ZEITI Portal")
{
try {
$mail = new PHPMailer(true);
$mail->isSMTP();
//$mail->SMTPDebug = 2;
//$mail->Debugoutput = 'html';// Set mailer to use SMTP
$mail->Host = 'smtp.gmail.com';
//'smtp.zamnet.zm'; // Specify main and backup SMTP servers
$mail->SMTPAuth = true;
// Enable SMTP authentication
$mail->Username = '******';
//;'*****@*****.**'; // SMTP username
$mail->Password = '******';
//'abcd34'; // SMTP password
$mail->SMTPSecure = 'tls';
//'' // Enable TLS encryption, `ssl` also accepted
$mail->Port = 587;
//25;
$mail->isHTML(true);
$mail->Subject = $subject;
if (is_array($recipient) && array_key_exists('name', $recipient)) {
$name = $recipient['name'];
$mail->Body = "<p>Dear {$name},</p>" . "<p>{$message}</p>" . "<p>{$signature}</p>";
} else {
$mail->Body = "<p>Dear user,</p>" . "<p>{$message}</p>" . "<p>{$signature}</p>";
}
//$mail->AltBody = 'This is the body in plain text for non-HTML mail clients';
$mail->setFrom('*****@*****.**', 'ZEITI Portal Team');
//$mail->addReplyTo('*****@*****.**', 'Information');
if (ZP::isEmail($recipient)) {
$mail->addAddress($recipient);
} else {
if (is_array($recipient)) {
if (array_key_exists('email', $recipient)) {
if (array_key_exists('name', $recipient)) {
$mail->addAddress($recipient['email'], $recipient['name']);
} else {
$mail->addAddress($recipient['email']);
}
} else {
foreach ($recipient as $value) {
if (array_key_exists('email', $value)) {
if (array_key_exists('name', $value)) {
$mail->addAddress($value['email'], $value['name']);
} else {
$mail->addAddress($value['email']);
}
}
}
}
} else {
return ['success' => false, 'error' => 'Invalid recipient specified.'];
}
}
// if cc has been specified
if (!is_null($cc)) {
if (array_key_exists('email', $cc)) {
if (array_key_exists('name', $cc)) {
$mail->addCC($cc['email'], $cc['name']);
} else {
$mail->addCC($cc['email']);
}
} else {
foreach ($cc as $value) {
if (array_key_exists('email', $value)) {
if (array_key_exists('name', $value)) {
$mail->addCC($value['email'], $value['name']);
} else {
$mail->addCC($value['email']);
}
}
}
}
}
// if bcc has been specified
if (!is_null($bcc)) {
if (array_key_exists('email', $bcc)) {
if (array_key_exists('name', $bcc)) {
$mail->addBCC($bcc['email'], $bcc['name']);
} else {
$mail->addBCC($bcc['email']);
}
} else {
foreach ($bcc as $value) {
if (array_key_exists('email', $value)) {
if (array_key_exists('name', $value)) {
$mail->addBCC($value['email'], $value['name']);
} else {
$mail->addBCC($value['email']);
}
}
}
}
}
return ['success' => $mail->send()];
} catch (\phpmailerException $e) {
return ['success' => false];
}
}
}
print json_encode($dao->adjustValue($period, $mine, $typeID, $groupID, $value, $type, $currency));
break;
case "get-ec-reconciliation":
print json_encode($dao->getECReconciliation($_REQUEST['period']));
break;
case "get-reconciliation-by-ec":
print json_encode($dao->getReconciliationByEC($_REQUEST['period']));
break;
case "get-reconciliation-by-rs":
print json_encode($dao->getReconciliationByRS($_REQUEST['period']));
break;
case "export-mine-recon":
$temp = new \ZP\IEE\Writer();
$data = $dao->getReconciliation($_REQUEST['mine'], $_REQUEST['period'])['data'];
$mine = \ZP\ZP::getExtractiveCompany($_REQUEST['mine']);
if (is_object($mine)) {
$temp->createReconciliationMine($data, $mine->companyName, $_REQUEST['period'], $_REQUEST['currency']);
break;
}
exit(json_encode(['success' => false, 'message' => 'Please select a valid extractive company.']));
case "export-mines-recon":
$temp = new \ZP\IEE\Writer();
$data = $dao->getECReconciliation($_REQUEST['period'])['data'];
//exit(json_encode($data));
$temp->createReconciliationMines($data, $_REQUEST['period'], $_REQUEST['currency']);
break;
case "export-by-ec-recon":
$temp = new \ZP\IEE\Writer();
$data = $dao->getReconciliationByEC($_REQUEST['period'])['data'];
//exit(json_encode($data));
public function set_reconciliation_by_mines($data)
{
// first create templates to be used
$tmp = ZP::getExtractiveCompanies();
$this->template = [];
foreach ($tmp as $mine) {
$this->template[$mine->key] = $mine;
$this->template[$mine->key]->mine_zmk = 0.0;
$this->template[$mine->key]->mine_usd = 0.0;
$this->template[$mine->key]->mine_adjust_zmk = 0.0;
$this->template[$mine->key]->mine_adjust_usd = 0.0;
$this->template[$mine->key]->mine_final_zmk = 0.0;
$this->template[$mine->key]->mine_final_usd = 0.0;
$this->template[$mine->key]->gov_zmk = 0.0;
$this->template[$mine->key]->gov_usd = 0.0;
$this->template[$mine->key]->gov_adjust_zmk = 0.0;
$this->template[$mine->key]->gov_adjust_usd = 0.0;
$this->template[$mine->key]->gov_final_zmk = 0.0;
$this->template[$mine->key]->gov_final_zmw = 0.0;
$this->template[$mine->key]->original_diff_zmk = 0.0;
$this->template[$mine->key]->original_diff_usd = 0.0;
$this->template[$mine->key]->adjust_diff_zmk = 0.0;
$this->template[$mine->key]->adjust_diff_usd = 0.0;
$this->template[$mine->key]->final_diff_zmk = 0.0;
$this->template[$mine->key]->final_diff_usd = 0.0;
}
// add values for totals
$this->template['Total'] = new \stdClass();
$this->template['Total']->mine_zmk = 0.0;
$this->template['Total']->mine_usd = 0.0;
$this->template['Total']->mine_adjust_zmk = 0.0;
$this->template['Total']->mine_adjust_usd = 0.0;
$this->template['Total']->mine_final_zmk = 0.0;
$this->template['Total']->mine_final_usd = 0.0;
$this->template['Total']->gov_zmk = 0.0;
$this->template['Total']->gov_usd = 0.0;
$this->template['Total']->gov_adjust = 0.0;
$this->template['Total']->gov_final_zmk = 0.0;
$this->template['Total']->gov_final_usd = 0.0;
$this->template['Total']->original_diff_zmk = 0.0;
$this->template['Total']->original_diff_usd = 0.0;
$this->template['Total']->adjust_diff_zmk = 0.0;
$this->template['Total']->adjust_diff_usd = 0.0;
$this->template['Total']->final_diff_zmk = 0.0;
$this->template['Total']->final_diff_usd = 0.0;
// add mine data to templates
foreach ($data as $mine) {
// set mine data
$this->template[$mine->name]->mine_zmk = doubleval($mine->mine_zmk);
$this->template[$mine->name]->mine_usd = doubleval($mine->mine_usd);
$this->template[$mine->name]->mine_adjust_zmk = doubleval($mine->mine_adjust_zmk);
$this->template[$mine->name]->mine_adjust_usd = doubleval($mine->mine_adjust_usd);
$this->template[$mine->name]->mine_final_zmk = $mine->mine_zmk + $mine->mine_adjust_zmk;
$this->template[$mine->name]->mine_final_usd = $mine->mine_usd + $mine->mine_adjust_usd;
// set government entity data
$this->template[$mine->name]->gov_zmk = doubleval($mine->gov_zmk);
$this->template[$mine->name]->gov_usd = doubleval($mine->gov_usd);
$this->template[$mine->name]->gov_adjust_zmk = doubleval($mine->gov_adjust_zmk);
$this->template[$mine->name]->gov_adjust_usd = doubleval($mine->gov_adjust_usd);
$this->template[$mine->name]->gov_final_zmk = $mine->gov_zmk + $mine->gov_adjust_zmk;
$this->template[$mine->name]->gov_final_usd = $mine->gov_usd + $mine->gov_adjust_usd;
// set differences
$this->template[$mine->name]->original_diff_zmk = doubleval($this->template[$mine->name]->mine_zmk) - doubleval($this->template[$mine->name]->gov_zmk);
$this->template[$mine->name]->original_diff_usd = doubleval($this->template[$mine->name]->mine_usd) - doubleval($this->template[$mine->name]->gov_usd);
$this->template[$mine->name]->adjust_diff_zmk = doubleval($this->template[$mine->name]->mine_adjust_zmk) - doubleval($this->template[$mine->name]->gov_adjust_zmk);
$this->template[$mine->name]->adjust_diff_usd = doubleval($this->template[$mine->name]->mine_adjust_usd) - doubleval($this->template[$mine->name]->gov_adjust_usd);
$this->template[$mine->name]->final_diff_zmk = doubleval($this->template[$mine->name]->mine_final_zmk) - doubleval($this->template[$mine->name]->gov_final_zmk);
$this->template[$mine->name]->final_diff_usd = doubleval($this->template[$mine->name]->mine_final_usd) - doubleval($this->template[$mine->name]->gov_final_usd);
// add originals totals
$this->template['Total']->mine_zmk += doubleval($mine->mine_zmk);
$this->template['Total']->mine_usd += doubleval($mine->mine_usd);
$this->template['Total']->mine_adjust_zmk += doubleval($mine->mine_adjust_zmk);
$this->template['Total']->mine_adjust_usd += doubleval($mine->mine_adjust_usd);
$this->template['Total']->mine_final_zmk += doubleval($this->template[$mine->name]->mine_final_zmk);
$this->template['Total']->mine_final_usd += doubleval($this->template[$mine->name]->mine_final_usd);
$this->template['Total']->gov_zmk += doubleval($mine->gov_zmk);
$this->template['Total']->gov_usd += doubleval($mine->gov_usd);
$this->template['Total']->gov_adjust_zmk += doubleval($mine->gov_adjust_zmk);
$this->template['Total']->gov_adjust_usd += doubleval($mine->gov_adjust_usd);
$this->template['Total']->gov_final_zmk += doubleval($this->template[$mine->name]->gov_final_zmk);
$this->template['Total']->gov_final_usd += doubleval($this->template[$mine->name]->gov_final_usd);
// add differences totals
$this->template['Total']->original_diff_zmk = $this->template['Total']->mine_zmk - $this->template['Total']->gov_zmk;
$this->template['Total']->original_diff_usd = $this->template['Total']->mine_usd - $this->template['Total']->gov_usd;
$this->template['Total']->adjust_diff_zmk = $this->template['Total']->mine_adjust_zmk - $this->template['Total']->gov_adjust_zmk;
$this->template['Total']->adjust_diff_usd = $this->template['Total']->mine_adjust_usd - $this->template['Total']->gov_adjust_usd;
$this->template['Total']->final_diff_zmk = $this->template['Total']->mine_final_zmk - $this->template['Total']->gov_final_zmk;
$this->template['Total']->final_diff_usd = $this->template['Total']->mine_final_usd - $this->template['Total']->gov_final_usd;
}
}
public function getECReconciliation($period)
{
$data = [];
$mines = ZP::getExtractiveCompanies();
foreach ($mines as $mine) {
$tmp = $this->getReconciliation($mine->TPIN, $period);
if ($tmp['success']) {
$data[$mine->key] = $tmp['data'];
}
}
return count($data) ? ['success' => true, 'data' => $data] : ['success' => 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]));
}
}
<?php
/*
* To change this license header, choose License Headers in Project Properties.
* To change this templates file, choose Tools | Templates
* and open the templates in the editor.
*/
include_once '../dataAccess/pdoDAO.php';
include_once '../../classes/json.php';
$dao = new pdoDAO();
$action = $_REQUEST['action'];
switch ($action) {
case "initReports":
\ZP\ZP::log('Opened Reports', 'Opened Reports', $_SESSION['user']->getID(), $_SESSION['module_number']);
$obj = $dao->initReports();
json::sendJsonObj($obj);
break;
case "getReport":
$mineID = trim($_POST['mineID']);
$from = trim($_POST['from']);
$to = trim($_POST['to']);
$indicatorID = trim($_POST['indicatorID']);
$report = $dao->getReport($mineID, $from, $to, $indicatorID);
json::sendJsonObj($report);
break;
default:
break;
}
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 addLicence($licence)
{
\ZP\ZP::log('Add License', 'Add License', $_SESSION['user']->getID(), $_SESSION['module_number']);
$_user = $_SESSION["user"];
//get user and company info
// $entityType = "";
// $govtid = 'null';
// $TPIN = 'null';
// //check if user is from mine or government
// if($_user->TPIN != null){
// $entityType = 'mine';
// $TPIN = $_user->TPIN;
// }else{
// $entityType = 'gov';
// $govtid = $_user->entityID;
// }
$TPIN = $_user->getTPIN();
//var_dump($TPIN);
//exit();
//set sql
$sql = "INSERT INTO licence(licenceID, code, resources, area, location, status, type) VALUES";
foreach ($list as $value) {
$sql .= "({$value->licenceID},'{$value->code}','{$value->resources}','{$value->area}','{$value->location}', '{$value->status}', '{$value->type}'),";
}
$sql = chop($sql, ",");
$sql .= "ON DUPLICATE KEY UPDATE code = VALUES(code), resources = VALUES(resources),area = VALUES(area), " . "location = VALUES(location), status = VALUES(status),type = VALUES(type)";
//
mysqli_query($this->con, $sql);
var_dump(mysqli_error($this->con), $sql);
// try{
// $result = mysqli_query($this->con, $sql);
//
//
// }
// catch(Exception $e) {
// echo 'Message: ' .$e->getMessage();
// }
}
<?php
/*
* To change this license header, choose License Headers in Project Properties.
* To change this templates file, choose Tools | Templates
* and open the templates in the editor.
*/
include_once '../dataAccess/pdoDAO.php';
include_once '../../classes/json.php';
require_once $_SERVER['DOCUMENT_ROOT'] . "/ZeitiPortal/resources/required_scripts.php";
session_start();
$dao = new pdoDAO();
$action = $_REQUEST['action'];
switch ($action) {
case "getReconciliation":
\ZP\ZP::log('Opened Reconciliation', 'Opened Reconciliation', $_SESSION['user']->getID(), $_SESSION['module_number']);
$mineID = trim($_POST['mineID']);
$period = trim($_POST['period']);
$report = $dao->getReconciliation($mineID, $period);
json::sendJsonObj($report);
break;
default:
break;
}
<!DOCTYPE html>
<?php
require_once $_SERVER['DOCUMENT_ROOT'] . "/ZeitiPortal/resources/required_scripts.php";
session_start();
if (!isset($_SESSION['user'])) {
header('Location: ../');
}
$_SESSION['module_number'] = 10;
if (!\ZP\ZP::canViewModule($_SESSION['module_number'], $_SESSION['group']->getPermissions())) {
header('Location: ../');
}
?>
<html>
<head>
<meta charset="UTF-8">
<title>Company Profile</title>
<link href="../../css/reports.css" rel="stylesheet" type="text/css"/>
<link href="../../css/profile.css" rel="stylesheet" type="text/css"/>
<link rel="stylesheet" type="text/css" href="../../css/jquery-ui/jquery.ui.all.css">
<!-- <script src="//code.jquery.com/jquery-1.10.2.js"></script>
<script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script>-->
<style>
div#reportContent, .ui-widget { font-size: 62.5%; }
div#reportContent label, div#contentbar input, .ui-widget label, .ui-widget input { display:block; }
div#reportContent input.text, .ui-widget input.text { margin-bottom:12px; width:95%; padding: .4em; }
div#reportContent fieldset, .ui-widget fieldset { padding:0; border:0; margin-top:25px; }
div#reportContent .ui-widget { font-family: Trebuchet MS,Tahoma,Verdana,Arial,sans-serif; font-size: 1.5em; }
div#reportContent h1, .ui-widget h1 { font-size: 1.2em; margin: .6em 0; }
div#users-contain { width: 850px; margin: 20px auto; }
div#users-contain table { margin: 1em 0; border-collapse: collapse; width: 100%; }
div#users-contain table td, div#users-contain table th { border: 1px solid #eee; padding: .6em 10px; text-align: left; }
public function getBeneficiaries($TPIN)
{
return ZP::getBeneficiaries($TPIN);
}
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 delete($data)
{
try {
$user = $this->getUser($data->ID);
$this->getConnection()->beginTransaction();
if (strcmp($data->type, 'user') == 0) {
$sql = "DELETE u " . "FROM `user` u " . "WHERE u.userID = :ID";
$query = $this->getConnection()->prepare($sql);
$query->bindValue(":ID", $data->ID, PDO::PARAM_INT);
if ($query->execute()) {
$this->getConnection()->commit();
if ($user['success']) {
ZP::log('User Deletion', json_encode($user['user']), $_SESSION['user']->getID(), $_SESSION['module_number']);
}
return $this->getEntities();
}
return ["success" => false, "exception" => "Internal error occurred. Could not delete user"];
} else {
if (strcmp($data->type, 'group') == 0) {
$group = (object) $this->getGroup($data->ID);
$sql = "DELETE FROM `group` WHERE groupID = :ID; ";
$query = $this->getConnection()->prepare($sql);
$query->bindValue(":ID", $data->ID, PDO::PARAM_INT);
if ($query->execute()) {
$this->getConnection()->commit();
//var_dump($group);
//exit(0);
if ($group->success) {
ZP::log('Group Deletion', json_encode($group->group), $_SESSION['user']->getID(), $_SESSION['module_number']);
}
return $this->getEntities();
//return ["success"=>false, "exception"=>"Internal error occurred. Could not delete user"];
}
return ["success" => false, "exception" => "Internal error occurred. Could not delete user"];
}
}
return ["success" => false, "exception" => "Internal error occurred. Could not delete group"];
} catch (\PDOException $e) {
$this->getConnection()->rollBack();
return ["success" => false, "exception" => $e];
}
}
