You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
69 lines
2.4 KiB
PHP
69 lines
2.4 KiB
PHP
<?php
|
|
|
|
use alhimik1986\PhpExcelTemplator\PhpExcelTemplator;
|
|
use alhimik1986\PhpExcelTemplator\params\ExcelParam;
|
|
use alhimik1986\PhpExcelTemplator\params\CallbackParam;
|
|
use alhimik1986\PhpExcelTemplator\setters\CellSetterArrayValueSpecial;
|
|
use PhpOffice\PhpSpreadsheet\IOFactory;
|
|
|
|
use NN\files;
|
|
use NN\Post;
|
|
use NN\Session;
|
|
use NN\Module\DB;
|
|
use NN\Module\Help;
|
|
use NN\Link;
|
|
use NN\Module\DD;
|
|
use NN\Enc;
|
|
use NN\module\View;
|
|
use xls\Param;
|
|
|
|
class Ekuitas {
|
|
|
|
private function toArray2($data = null, $name = ""){
|
|
$new = [];
|
|
$start = 5;
|
|
foreach ($data as $key => $n) {
|
|
$e = ( array ) $n;
|
|
$new[] = str_replace("{no}",$start+$key, $e[$name]);
|
|
}
|
|
return $new;
|
|
}
|
|
|
|
public function do(){
|
|
$templateFile = SETUP_PATH.'/excel/ekuitas.xlsx';;
|
|
$fileName = SETUP_PATH.'excel/ekuitas.xlsx';
|
|
define('SPECIAL_ARRAY_TYPE', CellSetterArrayValueSpecial::class);
|
|
|
|
$qr = "
|
|
SELECT '={no}' no, main,nama_main, dk, rek,nama,sum(total) total FROM (
|
|
SELECT main,nama_main, dk, rekkredit rek, nama, sum(if(dk = 'D', kredit*-1,kredit)) total FROM lap_bb
|
|
LEFT JOIN acc ON acc.kode = lap_bb.rekkredit WHERE lap_bb.tgl <= \"2023-10-31\"
|
|
GROUP BY rekkredit
|
|
UNION ALL
|
|
SELECT main,nama_main, dk, rekdebit rek, nama, sum(if(dk <> 'D', debit *-1,debit)) total FROM lap_bb
|
|
LEFT JOIN acc ON acc.kode = lap_bb.rekdebit WHERE lap_bb.tgl <= \"2023-10-31\"
|
|
GROUP BY rekdebit
|
|
) a GROUP BY rek HAVING main IS NOT NULL
|
|
";
|
|
$data = DB::query_result_object($qr);
|
|
$dataParse = [];
|
|
$newPar = new Param();
|
|
|
|
$dataParse['no'] = (new self)->toArray2($data, "no");
|
|
$newPar->add('no', $dataParse);
|
|
|
|
$params = $newPar->get();
|
|
|
|
$spreadsheet = IOFactory::load($templateFile);
|
|
$templateVarsArr = $spreadsheet->getActiveSheet()->toArray();
|
|
$callbacks = [];
|
|
$events = [];
|
|
|
|
$sheet1 = $spreadsheet->getSheetByName('neraca');
|
|
PhpExcelTemplator::renderWorksheet($sheet1, $templateVarsArr, $params, $callbacks, $events);
|
|
|
|
// var_dump($templateVarsArr);
|
|
// PhpExcelTemplator::saveSpreadsheetToFile($spreadsheet, $fileName);
|
|
PhpExcelTemplator::outputSpreadsheetToFile($spreadsheet, $fileName); // to download the file from web page
|
|
}
|
|
} |