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 readGIEC()
{
$errors = [];
$data = $this->getSheetData();
$defaults = $this->defaults;
$defaults->data = [];
$defaults->delete = [];
$mines = ZP::getExtractiveCompanies();
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']);
// extractive company
$C = trim($data[$i]['C']);
// TPIN
$D = trim($data[$i]['D']);
// interest start
$E = trim($data[$i]['E']);
// interest end
$F = trim($data[$i]['F']);
// nature of transaction
$G = trim($data[$i]['G']);
// value of transaction
$H = trim($data[$i]['H']);
// terms of transaction
$I = trim($data[$i]['I']);
// beneficiary of transaction
$J = trim($data[$i]['J']);
// responsibility to cover expenses
$K = trim($data[$i]['K']);
// terms attached to equity
if (strcmp($B, 'Management sign-off') == 0) {
if (count($errors) > 0 && count($errors) < 6) {
return ['success' => false, 'errors' => $errors];
} else {
if (count($errors) >= 6) {
return ['success' => false, 'errors' => $errors, 'msg' => 'There are multiple errors in the submission.'];
} else {
if (count($defaults->data) > 0) {
return ['success' => true, 'data' => $defaults];
}
}
}
return ['success' => true, 'message' => 'Request completed successfully.'];
} 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 && strlen($I) == 0 && strlen($J) == 0 && strlen($K) == 0) {
array_push($defaults->delete, $A);
} else {
if (strlen($B) != 0 || strlen($C) != 0 || strlen($D) != 0 || strlen($E) != 0 || strlen($F) != 0 || strlen($G) != 0 || strlen($H) != 0 || strlen($I) != 0 || strlen($J) != 0 || strlen($K) != 0) {
$is_mine = false;
foreach ($mines as $v) {
if (strcmp($C, strval($v->TPIN)) == 0) {
$is_mine = true;
break;
}
}
if (!$is_mine) {
$errors['C' . $i] = 'Please input a valid TPIN';
$hasErrors = true;
}
if (doubleval($D) <= 0) {
$errors['D' . $i] = 'Please input a number in this field';
$hasErrors = true;
}
if (doubleval($E) <= 0) {
$errors['E' . $i] = 'Please input a number in this field';
$hasErrors = true;
}
if (!strlen($F)) {
$errors['F' . $i] = 'Please input nature of transaction';
$hasErrors = true;
}
if (doubleval($G) <= 0) {
$errors['G' . $i] = 'Please input a number in this field';
$hasErrors = true;
}
if (!strlen($H)) {
$errors['H' . $i] = 'Please input terms of transaction';
$hasErrors = true;
}
if (!strlen($I)) {
$errors['I' . $i] = 'Please input beneficiary of transaction';
$hasErrors = true;
}
if (!strlen($J)) {
$errors['J' . $i] = 'Please input valid value in this field';
$hasErrors = true;
}
if (!strlen($K)) {
$errors['K' . $i] = 'Please input valid value in this field';
$hasErrors = true;
}
if (!$hasErrors) {
array_push($defaults->data, (object) array('interestID' => $A, 'TPIN' => $C, 'percent_interest_1st' => $D, 'percent_interest_2nd' => $E, 'nameOfTransaction' => $F, 'valueOfTransaction_ZMW' => $G, 'termsOfTransaction' => $H, 'beneficiaryOfTransaction' => $I, 'isresponsible_for_expenses' => $J, 'terms_attached_for_equity' => $K));
}
}
}
}
}
}
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 getExtractiveCompanies()
{
return ZP::getExtractiveCompanies();
}
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];
}
