public function createPFD()
{
$columns = ['Key' => 'A', 'Date' => 'B', 'Receipt Number' => 'C', 'Tax Kind/Type' => 'D', 'Tax Code' => 'E', 'Agent Fees' => 'F', 'Amount ZMW' => 'G', 'Amount USD' => 'H', 'Start Date' => 'I', 'End Date' => 'J', 'Comments' => 'K'];
$template_name = 'Payment Flow Details';
$this->phpExcelObject->getProperties()->setDescription($template_name . " Template.");
$sheet = $this->getActiveSheet();
$sheet->getProtection()->setInsertRows(false);
$sheet->setTitle("Template");
// write sheet defaults
$this->writeDefaults($sheet, $template_name);
// set columns
foreach ($columns as $k => $v) {
switch ($v) {
case 'C':
$width = 20;
break;
case 'D':
$width = 30;
break;
case 'E':
$width = 10;
break;
default:
$width = 20;
break;
}
$sheet->setCellValue($v . '9', $k);
$sheet->getColumnDimension($v)->setWidth($width);
}
// style columns
$this->styleColumns('A9:K9', $sheet);
//exit(json_encode($this->report));
// write data from report
$row = 10;
if (!is_null($this->report)) {
//$sheet->setCellValue('A'.$row, 2);
foreach ($this->report as $obj) {
//exit(json_encode($obj));
$sheet->setCellValue('A' . $row, $obj->Key);
$sheet->setCellValue('B' . $row, $this->dateToDD_MM_YYYYY($obj->date));
$sheet->setCellValue('C' . $row, $obj->receiptNumber);
$sheet->setCellValue('D' . $row, $obj->taxKind);
$sheet->setCellValue('E' . $row, $obj->taxCode);
$sheet->setCellValue('F' . $row, $obj->agent_fees);
$sheet->setCellValue('G' . $row, $obj->amount_zmk);
$sheet->setCellValue('H' . $row, $obj->amount_usd);
$sheet->setCellValue('I' . $row, $this->dateToDD_MM_YYYYY($obj->periodStart));
$sheet->setCellValue('J' . $row, $this->dateToDD_MM_YYYYY($obj->periodEnd));
$sheet->setCellValue('K' . $row, $obj->comments);
$sheet->setCellValue('Y' . $row, $obj->receipt);
$sheet->setCellValue('Z' . $row, $obj->ref);
$row += 1;
}
}
// hide receipt and ref columns
$sheet->getColumnDimension('Y')->setVisible(false);
// hide first column
$sheet->getColumnDimension('Z')->setVisible(false);
// hide first column
// add totals row
$row = $row > 30 ? $row : 35;
$sheet->setCellValue('F' . $row, 'Total');
$sheet->setCellValue('G' . $row, ZP::FormatString('=SUM(G10:G{0})', $row - 1));
$sheet->setCellValue('H' . $row, ZP::FormatString('=SUM(H10:H{0})', $row - 1));
$fill = $sheet->getStyle(ZP::FormatString("A{0}:J{1}", $row, $row))->getFill();
$fill->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
$fill->getStartColor()->setARGB('FFFF00');
//\PHPExcel_Style_Color::COLOR_BLUE);
$fill->getEndColor()->setARGB('FFFF00');
$this->formatCellValue(ZP::FormatString("A{0}:J{1}", $row, $row));
// unprotect editable cells of the sheet
$sheet->getStyle('B10:X' . ($row - 1))->getProtection()->setLocked(\PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
// format cell value as text
$this->formatCellValue('B10:J' . ($row - 1), 'text');
// format amount as number
$this->formatCellValue('F10:H' . ($row - 1));
// write sign off
$this->signOff(1, $row + 5);
$this->setActiveSheet(1);
$sheet = $this->getActiveSheet();
$sheet->setTitle("Tax Types");
$taxes = ZP::getTaxes();
$sheet->setCellValue("B2", "Ref");
$sheet->getColumnDimension("B")->setWidth(10);
$sheet->setCellValue("C2", "Tax Kind/Type");
$sheet->getColumnDimension("C")->setWidth(80);
$sheet->setCellValue("D2", "Tax Code");
$sheet->getColumnDimension("D")->setWidth(20);
$this->styleColumns('B2:D2', $sheet);
$groupName = "";
$row = 3;
foreach ($taxes as $tax) {
if (strcmp($groupName, $tax->groupName) != 0) {
// write group name
$sheet->setCellValue("B" . $row, $tax->groupName);
$sheet->mergeCells(ZP::FormatString("B{0}:D{1}", $row, $row));
$style = $sheet->getStyle(ZP::FormatString("B{0}:D{1}", $row, $row));
$fill = $style->getFill();
$fill->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
$fill->getStartColor()->setARGB('00366498');
$fill->getEndColor()->setARGB('00366498');
$style->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_WHITE);
$groupName = $tax->groupName;
$row++;
// write tax
$sheet->setCellValue("B" . $row, $tax->taxTypeID);
// write reference/taxTypeID
$sheet->getStyle("B" . $row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$sheet->setCellValue("C" . $row, $tax->taxType);
// write tax type
$sheet->setCellValue("D" . $row, $tax->taxCode);
// write tax code
$row++;
} else {
// write tax
$sheet->setCellValue("B" . $row, $tax->taxTypeID);
// write reference/taxTypeID
$sheet->getStyle("B" . $row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$sheet->setCellValue("C" . $row, $tax->taxType);
// write tax type
$sheet->setCellValue("D" . $row, $tax->taxCode);
// write tax code
$row++;
}
}
$this->setActiveSheet(0);
//$sheet->getCell('B10:B510')->setValueExplicit('10', \PHPExcel_Cell_DataType::TYPE_STRING);
$this->outputTemplate($template_name);
}
public function readPFD()
{
$taxes = ZP::getTaxes();
// taxes
$errors = [];
$data = $this->getSheetData();
$defaults = $this->defaults;
$defaults->data = [];
$defaults->delete = [];
for ($i = 10; $i < count($data); $i++) {
$hasErrors = false;
$A = strcmp($data[$i]['A'], '') == 0 ? null : $data[$i]['A'];
// key
$B = trim($data[$i]['B']);
// date
$C = trim($data[$i]['C']);
// receipt
$D = trim($data[$i]['D']);
// taxType
$E = trim($data[$i]['E']);
// taxCode
$F = trim($data[$i]['F']);
// agent fees
$G = trim($data[$i]['G']);
// amountZMK
$H = trim($data[$i]['H']);
// amountUSD
$I = trim($data[$i]['I']);
// startDate
$J = trim($data[$i]['J']);
// endDate
$K = trim($data[$i]['K']);
// comments
$Y = trim($data[$i]['Y']);
// receipt
$Z = trim($data[$i]['Z']);
// ref
if (strcmp($F, 'Total') == 0) {
//exit(json_encode(['success'=>false, 'errorssssssss']));
if (count($errors) > 0 && count($errors) < 6) {
unset($defaults);
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 || count($defaults->delete) > 0) {
//exit(json_encode(['success'=>false, 'data'=>$defaults, 'error'=>'error', 'receipts'=>$files]));
return ['success' => true, 'data' => $defaults];
}
}
}
unset($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) {
if (!\ZP\ZP::isDate($B, 'd/m/Y')) {
$errors['B' . $i] = 'Please input date in the format dd/mm/yyyy';
$hasErrors = true;
} else {
$B = preg_split('/[\\/-]/', $B);
$B = \ZP\ZP::FormatString('{0}-{1}-{2}', $B[2], $B[1], $B[0]);
}
if (strlen($C) == 0) {
$errors['C' . $i] = 'Please input a valid receipt number';
$hasErrors = true;
}
// validate tax code and tax type
$taxTypeID = call_user_func(function ($taxType, $taxCode, $taxes) {
foreach ($taxes as $tax) {
if ($tax->taxCode == $taxCode && strcasecmp($tax->taxType, $taxType) == 0) {
return $tax->taxTypeID;
}
}
return false;
}, $D, $E, $taxes);
//exit(json_encode(['success'=>false, 'taxTypeID'=>$taxTypeID]));
if (!$taxTypeID) {
$errors['D' . $i] = 'Please input a valid tax type. Refer to the Tax Types sheet to get valid values.';
$errors['E' . $i] = 'Please input a valid tax code. Refer to the Tax Types sheet to get valid values.';
$hasErrors = true;
}
// validate agent fees
if (strlen($F) != 0 && !is_numeric($F)) {
$errors['F' . $i] = 'Please input a valid agent fee.';
$hasErrors = true;
}
if (!is_numeric($G)) {
$errors['G' . $i] = 'Please input a valid Kwacha amount';
$hasErrors = true;
}
if (!is_numeric($H)) {
$errors['H' . $i] = 'Please input a valid US Dollar amount';
$hasErrors = true;
}
if (!\ZP\ZP::isDate($I, 'd/m/Y')) {
$errors['I' . $i] = 'Please input date in the format dd/mm/yyyy';
if (!\ZP\ZP::isDate($J, 'd/m/Y')) {
$errors['J' . $i] = 'Please input date in the format dd/mm/yyyy';
}
$hasErrors = true;
} else {
if (!\ZP\ZP::isDate($J, 'd/m/Y')) {
$errors['J' . $i] = 'Please input date in the format dd/mm/yyyy';
$hasErrors = true;
} else {
$I = preg_split('/[\\/-]/', $I);
$I = \ZP\ZP::FormatString('{0}-{1}-{2}', $I[2], $I[1], $I[0]);
$J = preg_split('/[\\/-]/', $J);
$J = \ZP\ZP::FormatString('{0}-{1}-{2}', $J[2], $J[1], $J[0]);
// compare dates
$d1 = new \DateTime($I);
$d2 = new \DateTime($J);
$year = date('Y');
if (\ZP\ZP::isDateBefore($d1, $d2)) {
$errors['J' . $i] = 'Please ensure the End Date is after the Start Date';
}
if (intval($year) < intval($d1->format('Y'))) {
$errors['I' . $i] = "Please ensure the date for the current reporting period ({$year})";
}
if (intval($year) < intval($d2->format('Y'))) {
$errors['J' . $i] = "Please ensure the date for the current reporting period ({$year})";
}
}
}
if (!$hasErrors) {
array_push($defaults->data, (object) array('key' => $A, 'date' => $B, 'receiptNumber' => $C, 'taxType' => $taxTypeID, 'taxCode' => $E, 'agent_fees' => $F, 'amount_zmk' => $G, 'amount_usd' => $H, 'periodStart' => $I, 'periodEnd' => $J, 'comments' => $K, 'receipt' => $Y, 'ref' => $Z));
}
}
}
}
}
}
