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.
283 lines
9.0 KiB
PHP
283 lines
9.0 KiB
PHP
<?php
|
|
|
|
use alhimik1986\PhpExcelTemplator\PhpExcelTemplator;
|
|
use alhimik1986\PhpExcelTemplator\params\ExcelParam;
|
|
use alhimik1986\PhpExcelTemplator\params\CallbackParam;
|
|
use alhimik1986\PhpExcelTemplator\setters\CellSetterArrayValueSpecial;
|
|
|
|
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 Rekapitulasi {
|
|
|
|
private function toArray2($data = null, $name = ""){
|
|
$new = [];
|
|
$start = 6;
|
|
foreach ($data as $key => $n) {
|
|
$e = ( array ) $n;
|
|
$new[] = str_replace("{no}",$start+$key, $e[$name]);
|
|
}
|
|
return $new;
|
|
}
|
|
|
|
public static function do($tahun, $bulan, $type, $gol = "01"){
|
|
$tht = date('Y-m-t', strtotime( $tahun.'-'.$bulan ) );
|
|
$bulanData = ["Januari", "Februari", "Maret", "April", "Mei", "Juni", "Juli", "Agustus", "September", "Oktober", "November", "Desember"];
|
|
ini_set('display_errors', 1);
|
|
$file = SETUP_PATH.'excel/rekapitulasi-'.$tahun.'-'.$bulan.'-'.date('ymdhis').'.xlsx';
|
|
if(base64_decode($type) != 'all'){
|
|
$filex = SETUP_PATH.'/excel/rekapitulasi2.xlsx';
|
|
}else{
|
|
$filex = SETUP_PATH.'/excel/rekapitulasi.xlsx';
|
|
if($gol == "02"){
|
|
$filex = SETUP_PATH.'/excel/rekapitulasi3.xlsx';
|
|
}
|
|
}
|
|
define('SPECIAL_ARRAY_TYPE', CellSetterArrayValueSpecial::class);
|
|
|
|
$kodeSwithch = "jurubayar";
|
|
|
|
if($gol == "02"){
|
|
$kodeSwithch = "rekapgol";
|
|
}
|
|
|
|
$nm = "
|
|
GROUP BY $kodeSwithch
|
|
";
|
|
|
|
$c = "
|
|
, $kodeSwithch jurubayar
|
|
";
|
|
|
|
$x = "
|
|
|
|
";
|
|
|
|
$nm2 = "
|
|
GROUP BY $kodeSwithch, kodesp
|
|
";
|
|
|
|
if(base64_decode($type) != 'all'){
|
|
$nm = "
|
|
GROUP BY kode
|
|
";
|
|
$nm2 = "
|
|
GROUP BY kode, kodesp
|
|
";
|
|
$c = "
|
|
, nama jurubayar
|
|
";
|
|
$x = "
|
|
HAVING jbyr = '".base64_decode($type)."'
|
|
";
|
|
}
|
|
|
|
$qr = "
|
|
SELECT
|
|
'={no} - 5' no
|
|
,'=H{no}+G{no}+F{no}+C{no}' jum1
|
|
,'=SUM(J{no}:P{no})' jum2
|
|
,'=Q{no}+I{no}' jum3
|
|
$c
|
|
, $kodeSwithch jbyr
|
|
, kodesp
|
|
, sum(if(kodesp = 'reg', jumlah, 0)) reg
|
|
, sum(if(kodesp = 'swp', jumlah, 0)) swp
|
|
, sum(if(kodesp = 'sm', jumlah, 0)) sm
|
|
, sum(if(kodesp = 'tk', jumlah, 0)) tk
|
|
, sum(if(kodesp = 'sp', jumlah, 0)) sp
|
|
, sum(if(kodesp = 'sw', jumlah, 0)) sw
|
|
, sum(if(kodesp = 'bw', jumlah, 0)) bw
|
|
, sum(if(kodesp = 'pk', jumlah, 0)) pk
|
|
, sum(if(kodesp = 'usp1', jumlah, 0)) usp1
|
|
, sum(if(kodesp = 'usp2', jumlah, 0)) usp2
|
|
, sum(if(kodesp = 'usp3', jumlah, 0)) usp3
|
|
, sum(if(kodesp = 'reg', pokok, 0)) pokok
|
|
, sum(if(kodesp = 'reg', bunga, 0)) bunga
|
|
, sum(jumlah) jumlah
|
|
FROM (
|
|
SELECT
|
|
m.tgl
|
|
, m.kode
|
|
, a.nama
|
|
, a.jurubayar
|
|
, a.rekapgol
|
|
, m.kodesp
|
|
, sum(if(m.dk = 'D', m.jumlah, m.jumlah * -1)) jumlah
|
|
, 0 pokok
|
|
, 0 bunga
|
|
FROM msimpan m
|
|
LEFT JOIN anggota a ON m.kode = a.kode
|
|
WHERE m.tgl <= '$tht'
|
|
$nm2
|
|
HAVING jumlah <> 0
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
tgl
|
|
, totsl.kodesc kode
|
|
,a.nama
|
|
, a.jurubayar
|
|
, a.rekapgol
|
|
, 'PK' kodesp
|
|
, sum(kredit) jumlah
|
|
, 0 pokok
|
|
, 0 bunga
|
|
FROM totsl
|
|
LEFT JOIN anggota a ON a.kode = totsl.kodesc
|
|
WHERE kredit <> 0 AND totsl.tgl <= '$tht'
|
|
$nm2
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
tgl
|
|
, totsl.kodesc kode
|
|
, a.nama
|
|
, a.jurubayar
|
|
, a.rekapgol
|
|
, 'BW' kodesp
|
|
, sum( totsl.debet ) * -1 jumlah
|
|
, 0 pokok
|
|
, 0 bunga
|
|
FROM totsl
|
|
LEFT JOIN anggota a ON a.kode = totsl.kodesc
|
|
WHERE kredit <> 0 AND totsl.tgl <= '$tht'
|
|
$nm2
|
|
HAVING jumlah <> 0
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
m.tgl
|
|
, m.kode
|
|
, a.nama
|
|
, a.jurubayar
|
|
, a.rekapgol
|
|
, m.kodesp
|
|
, sum(m.pokok + m.bunga) jumlah
|
|
, 0 pokok
|
|
, 0 bunga
|
|
FROM mpinjam m
|
|
LEFT JOIN anggota a ON a.kode = m.kode
|
|
WHERE m.tgl <= '$tht'
|
|
$nm2
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
mags.tgl
|
|
, m.kode
|
|
, a.nama
|
|
, a.jurubayar
|
|
, a.rekapgol
|
|
, m.kodesp
|
|
, sum(mags.pokok + mags.bunga) * -1 jumlah
|
|
, mags.pokok
|
|
, mags.bunga
|
|
FROM mags
|
|
LEFT JOIN mpinjam m ON m.faktur = mags.fkt
|
|
LEFT JOIN anggota a ON a.kode = m.kode
|
|
WHERE mags.tgl <= '$tht'
|
|
$nm2
|
|
|
|
UNION All
|
|
|
|
SELECT
|
|
ptg.tgl
|
|
, kodesc kode
|
|
, a.nama
|
|
, a.jurubayar
|
|
, a.rekapgol
|
|
, 'PK' kodesp
|
|
, sum(ptg.lunas) * -1 jumlah
|
|
, 0 pokok
|
|
, 0 bunga
|
|
FROM ptg
|
|
LEFT JOIN totsl ON totsl.faktur = ptg.fkt
|
|
LEFT JOIN anggota a ON a.kode = totsl.kodesc
|
|
WHERE totsl.kredit <> 0 AND ptg.tgl <= '$tht'
|
|
$nm2
|
|
) a $nm $x
|
|
";
|
|
$data = DB::query_result_object($qr);
|
|
$dataParse = [];
|
|
$newPar = new Param();
|
|
|
|
$dataParse['no'] = (new self)->toArray2($data, "no");
|
|
$newPar->add('no', $dataParse);
|
|
|
|
$dataParse['nama'] = (new self)->toArray2($data, "jurubayar");
|
|
$newPar->add('nama', $dataParse);
|
|
|
|
$dataParse['swp'] = (new self)->toArray2($data, "swp");
|
|
$newPar->add('swp', $dataParse);
|
|
|
|
$dataParse['reg'] = (new self)->toArray2($data, "reg");
|
|
$newPar->add('reg', $dataParse);
|
|
|
|
$dataParse['usp1'] = (new self)->toArray2($data, "usp1");
|
|
$newPar->add('usp1', $dataParse);
|
|
|
|
$dataParse['usp2'] = (new self)->toArray2($data, "usp2");
|
|
$newPar->add('usp2', $dataParse);
|
|
|
|
$dataParse['usp3'] = (new self)->toArray2($data, "usp3");
|
|
$newPar->add('usp3', $dataParse);
|
|
|
|
$dataParse['pk'] = (new self)->toArray2($data, "pk");
|
|
$newPar->add('pk', $dataParse);
|
|
|
|
$dataParse['bw'] = (new self)->toArray2($data, "bw");
|
|
$newPar->add('bw', $dataParse);
|
|
|
|
$dataParse['sw'] = (new self)->toArray2($data, "sw");
|
|
$newPar->add('sw', $dataParse);
|
|
|
|
$dataParse['sp'] = (new self)->toArray2($data, "sp");
|
|
$newPar->add('sp', $dataParse);
|
|
|
|
$dataParse['tk'] = (new self)->toArray2($data, "tk");
|
|
$newPar->add('tk', $dataParse);
|
|
|
|
$dataParse['sm'] = (new self)->toArray2($data, "sm");
|
|
$newPar->add('sm', $dataParse);
|
|
|
|
$dataParse['pokok'] = (new self)->toArray2($data, "pokok");
|
|
$newPar->add('pokok', $dataParse);
|
|
|
|
$dataParse['bunga'] = (new self)->toArray2($data, "bunga");
|
|
$newPar->add('bunga', $dataParse);
|
|
|
|
$dataParse['jum1'] = (new self)->toArray2($data, "jum1");
|
|
$newPar->add('jum1', $dataParse);
|
|
|
|
$dataParse['jum2'] = (new self)->toArray2($data, "jum2");
|
|
$newPar->add('jum2', $dataParse);
|
|
|
|
$dataParse['jum3'] = (new self)->toArray2($data, "jum3");
|
|
$newPar->add('jum3', $dataParse);
|
|
|
|
$newPar->single('time', strtoupper($bulanData[ $bulan - 1 ]) ." ". $tahun );
|
|
$newPar->single('jubar', strtoupper(base64_decode($type)) );
|
|
|
|
// $dataParse['swp'] = (new self)->toArray2($data, "swp");
|
|
// $newPar->add('swp', $dataParse);
|
|
|
|
$params = $newPar->get();
|
|
// encopy
|
|
$callbacks = [];
|
|
PhpExcelTemplator::outputToFile($filex, $file, $params, $callbacks);
|
|
}
|
|
|
|
} |