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.
853 lines
34 KiB
PHP
853 lines
34 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 Excel{
|
|
|
|
public function akun($year = "", $app = null, $status = null){
|
|
$data = [];
|
|
$data["kode"] = [];
|
|
$data["dk"] = [];
|
|
$data["no"] = [];
|
|
$data["nama"] = [];
|
|
$data["sad"] = [];
|
|
$data["sak"] = [];
|
|
|
|
$condition = "";
|
|
if($app != null){
|
|
$condition = " WHERE acc.app = '$app' ";
|
|
}
|
|
|
|
$cond2 = "";
|
|
$name = "acc.nama";
|
|
$kode = "acc.kode";
|
|
if($status == "u"){
|
|
$kode = "acc.main kode";
|
|
$name = "acc.nama_main nama";
|
|
$cond2 = " GROUP BY acc.main ";
|
|
}else{
|
|
$kode = "acc.kode";
|
|
$name = "acc.nama";
|
|
$cond2 = " GROUP BY acc.kode ";
|
|
}
|
|
|
|
$acc = DB::query_result_object("SELECT
|
|
$kode
|
|
, acc.dk
|
|
, $name
|
|
, sum(ifnull(sa.debit,0)) sad
|
|
, sum(ifnull(sa.kredit,0)) sak
|
|
, sum(ifnull(j.debit,0)) jd
|
|
, sum(ifnull(j.kredit,0)) jk
|
|
, sum(ifnull(f.debit,0)) fd
|
|
, sum(ifnull(f.kredit,0)) fk
|
|
, sum(ifnull(m.debit,0)) md
|
|
, sum(ifnull(m.kredit,0)) mk
|
|
, sum(ifnull(a.debit,0)) ad
|
|
, sum(ifnull(a.kredit,0)) ak
|
|
, sum(ifnull(me.debit,0)) med
|
|
, sum(ifnull(me.kredit,0)) mek
|
|
, sum(ifnull(jn.debit,0)) jnd
|
|
, sum(ifnull(jn.kredit,0)) jnk
|
|
, sum(ifnull(ju.debit,0)) jud
|
|
, sum(ifnull(ju.kredit,0)) juk
|
|
, sum(ifnull(ag.debit,0)) agd
|
|
, sum(ifnull(ag.kredit,0)) agk
|
|
, sum(ifnull(s.debit,0)) sd
|
|
, sum(ifnull(s.kredit,0)) sk
|
|
, sum(ifnull(o.debit,0)) od
|
|
, sum(ifnull(o.kredit,0)) ok
|
|
, sum(ifnull(na.debit,0)) nd
|
|
, sum(ifnull(na.kredit,0)) nk
|
|
, sum(ifnull(de.debit,0)) ded
|
|
, sum(ifnull(de.kredit,0)) dek
|
|
FROM acc
|
|
LEFT JOIN (
|
|
SELECT trx, kode, tgl, keterangan, sum(debit) debit, sum(kredit) kredit, 0 total FROM (
|
|
SELECT rekdebit kode, 1 trx, tgl, keterangan, debit, 0 kredit FROM lap_bb WHERE tgl < '$year-01-01'
|
|
UNION ALL
|
|
SELECT rekkredit kode, 1 trx, tgl, keterangan, 0 debit, kredit FROM lap_bb WHERE tgl < '$year-01-01'
|
|
) a GROUP BY kode
|
|
) sa ON sa.kode = acc.kode
|
|
LEFT JOIN (
|
|
SELECT trx, kode, tgl, keterangan, sum(debit) debit, sum(kredit) kredit, 0 total FROM (
|
|
SELECT rekdebit kode, 1 trx, tgl, keterangan, debit, 0 kredit FROM lap_bb WHERE tgl LIKE '%$year-01%'
|
|
UNION ALL
|
|
SELECT rekkredit kode, 1 trx, tgl, keterangan, 0 debit, kredit FROM lap_bb WHERE tgl LIKE '%$year-01%'
|
|
) a GROUP BY kode
|
|
) j ON j.kode = acc.kode
|
|
LEFT JOIN (
|
|
SELECT trx, kode, tgl, keterangan, sum(debit) debit, sum(kredit) kredit, 0 total FROM (
|
|
SELECT rekdebit kode, 1 trx, tgl, keterangan, debit, 0 kredit FROM lap_bb WHERE tgl LIKE '%$year-02%'
|
|
UNION ALL
|
|
SELECT rekkredit kode, 1 trx, tgl, keterangan, 0 debit, kredit FROM lap_bb WHERE tgl LIKE '%$year-02%'
|
|
) a GROUP BY kode
|
|
) f ON f.kode = acc.kode
|
|
LEFT JOIN (
|
|
SELECT trx, kode, tgl, keterangan, sum(debit) debit, sum(kredit) kredit, 0 total FROM (
|
|
SELECT rekdebit kode, 1 trx, tgl, keterangan, debit, 0 kredit FROM lap_bb WHERE tgl LIKE '%$year-03%'
|
|
UNION ALL
|
|
SELECT rekkredit kode, 1 trx, tgl, keterangan, 0 debit, kredit FROM lap_bb WHERE tgl LIKE '%$year-03%'
|
|
) a GROUP BY kode
|
|
) m ON m.kode = acc.kode
|
|
LEFT JOIN (
|
|
SELECT trx, kode, tgl, keterangan, sum(debit) debit, sum(kredit) kredit, 0 total FROM (
|
|
SELECT rekdebit kode, 1 trx, tgl, keterangan, debit, 0 kredit FROM lap_bb WHERE tgl LIKE '%$year-04%'
|
|
UNION ALL
|
|
SELECT rekkredit kode, 1 trx, tgl, keterangan, 0 debit, kredit FROM lap_bb WHERE tgl LIKE '%$year-04%'
|
|
) a GROUP BY kode
|
|
) a ON a.kode = acc.kode
|
|
LEFT JOIN (
|
|
SELECT trx, kode, tgl, keterangan, sum(debit) debit, sum(kredit) kredit, 0 total FROM (
|
|
SELECT rekdebit kode, 1 trx, tgl, keterangan, debit, 0 kredit FROM lap_bb WHERE tgl LIKE '%$year-05%'
|
|
UNION ALL
|
|
SELECT rekkredit kode, 1 trx, tgl, keterangan, 0 debit, kredit FROM lap_bb WHERE tgl LIKE '%$year-05%'
|
|
) a GROUP BY kode
|
|
) me ON me.kode = acc.kode
|
|
LEFT JOIN (
|
|
SELECT trx, kode, tgl, keterangan, sum(debit) debit, sum(kredit) kredit, 0 total FROM (
|
|
SELECT rekdebit kode, 1 trx, tgl, keterangan, debit, 0 kredit FROM lap_bb WHERE tgl LIKE '%$year-06%'
|
|
UNION ALL
|
|
SELECT rekkredit kode, 1 trx, tgl, keterangan, 0 debit, kredit FROM lap_bb WHERE tgl LIKE '%$year-06%'
|
|
) a GROUP BY kode
|
|
) jn ON jn.kode = acc.kode
|
|
LEFT JOIN (
|
|
SELECT trx, kode, tgl, keterangan, sum(debit) debit, sum(kredit) kredit, 0 total FROM (
|
|
SELECT rekdebit kode, 1 trx, tgl, keterangan, debit, 0 kredit FROM lap_bb WHERE tgl LIKE '%$year-07%'
|
|
UNION ALL
|
|
SELECT rekkredit kode, 1 trx, tgl, keterangan, 0 debit, kredit FROM lap_bb WHERE tgl LIKE '%$year-07%'
|
|
) a GROUP BY kode
|
|
) ju ON ju.kode = acc.kode
|
|
LEFT JOIN (
|
|
SELECT trx, kode, tgl, keterangan, sum(debit) debit, sum(kredit) kredit, 0 total FROM (
|
|
SELECT rekdebit kode, 1 trx, tgl, keterangan, debit, 0 kredit FROM lap_bb WHERE tgl LIKE '%$year-08%'
|
|
UNION ALL
|
|
SELECT rekkredit kode, 1 trx, tgl, keterangan, 0 debit, kredit FROM lap_bb WHERE tgl LIKE '%$year-08%'
|
|
) a GROUP BY kode
|
|
) ag ON ag.kode = acc.kode
|
|
LEFT JOIN (
|
|
SELECT trx, kode, tgl, keterangan, sum(debit) debit, sum(kredit) kredit, 0 total FROM (
|
|
SELECT rekdebit kode, 1 trx, tgl, keterangan, debit, 0 kredit FROM lap_bb WHERE tgl LIKE '%$year-09%'
|
|
UNION ALL
|
|
SELECT rekkredit kode, 1 trx, tgl, keterangan, 0 debit, kredit FROM lap_bb WHERE tgl LIKE '%$year-09%'
|
|
) a GROUP BY kode
|
|
) s ON s.kode = acc.kode
|
|
LEFT JOIN (
|
|
SELECT trx, kode, tgl, keterangan, sum(debit) debit, sum(kredit) kredit, 0 total FROM (
|
|
SELECT rekdebit kode, 1 trx, tgl, keterangan, debit, 0 kredit FROM lap_bb WHERE tgl LIKE '%$year-10%'
|
|
UNION ALL
|
|
SELECT rekkredit kode, 1 trx, tgl, keterangan, 0 debit, kredit FROM lap_bb WHERE tgl LIKE '%$year-10%'
|
|
) a GROUP BY kode
|
|
) o ON o.kode = acc.kode
|
|
LEFT JOIN (
|
|
SELECT trx, kode, tgl, keterangan, sum(debit) debit, sum(kredit) kredit, 0 total FROM (
|
|
SELECT rekdebit kode, 1 trx, tgl, keterangan, debit, 0 kredit FROM lap_bb WHERE tgl LIKE '%$year-11%'
|
|
UNION ALL
|
|
SELECT rekkredit kode, 1 trx, tgl, keterangan, 0 debit, kredit FROM lap_bb WHERE tgl LIKE '%$year-11%'
|
|
) a GROUP BY kode
|
|
) na ON na.kode = acc.kode
|
|
LEFT JOIN (
|
|
SELECT trx, kode, tgl, keterangan, sum(debit) debit, sum(kredit) kredit, 0 total FROM (
|
|
SELECT rekdebit kode, 1 trx, tgl, keterangan, debit, 0 kredit FROM lap_bb WHERE tgl LIKE '%$year-12%'
|
|
UNION ALL
|
|
SELECT rekkredit kode, 1 trx, tgl, keterangan, 0 debit, kredit FROM lap_bb WHERE tgl LIKE '%$year-12%'
|
|
) a GROUP BY kode
|
|
) de ON de.kode = acc.kode
|
|
$condition
|
|
$cond2
|
|
");
|
|
foreach ($acc as $key => $d) {
|
|
$dtot = 0;
|
|
$ktot = 0;
|
|
$data["no"][] = $key + 1;
|
|
$data["dk"][] = $d->dk;
|
|
$data["kode"][] = $d->kode;
|
|
$data["nama"][] = $d->nama;
|
|
$data["sad"][] = $d->sad;
|
|
$dtot += $d->sad;
|
|
$data["sak"][] = $d->sak;
|
|
$ktot += $d->sak;
|
|
$data["jd"][] = $d->jd;
|
|
$dtot += $d->jd;
|
|
$data["jk"][] = $d->jk;
|
|
$ktot += $d->jk;
|
|
$data["fd"][] = $d->fd;
|
|
$dtot += $d->fd;
|
|
$data["fk"][] = $d->fk;
|
|
$ktot += $d->fk;
|
|
$data["md"][] = $d->md;
|
|
$dtot += $d->md;
|
|
$data["mk"][] = $d->mk;
|
|
$ktot += $d->mk;
|
|
$data["ad"][] = $d->ad;
|
|
$dtot += $d->ad;
|
|
$data["ak"][] = $d->ak;
|
|
$ktot += $d->ak;
|
|
$data["med"][] = $d->med;
|
|
$dtot += $d->med;
|
|
$data["mek"][] = $d->mek;
|
|
$ktot += $d->mek;
|
|
$data["jnd"][] = $d->jnd;
|
|
$dtot += $d->jnd;
|
|
$data["jnk"][] = $d->jnk;
|
|
$ktot += $d->jnk;
|
|
$data["jud"][] = $d->jud;
|
|
$dtot += $d->jud;
|
|
$data["juk"][] = $d->juk;
|
|
$ktot += $d->juk;
|
|
$data["agd"][] = $d->agd;
|
|
$dtot += $d->agd;
|
|
$data["agk"][] = $d->agk;
|
|
$ktot += $d->agk;
|
|
$data["sd"][] = $d->sd;
|
|
$dtot += $d->sd;
|
|
$data["sk"][] = $d->sk;
|
|
$ktot += $d->sk;
|
|
$data["od"][] = $d->od;
|
|
$dtot += $d->od;
|
|
$data["ok"][] = $d->ok;
|
|
$ktot += $d->ok;
|
|
$data["nd"][] = $d->nd;
|
|
$dtot += $d->nd;
|
|
$data["nk"][] = $d->nk;
|
|
$ktot += $d->nk;
|
|
$data["ded"][] = $d->ded;
|
|
$dtot += $d->ded;
|
|
$data["dek"][] = $d->dek;
|
|
$ktot += $d->dek;
|
|
$data["nmd"][] = $dtot;
|
|
$data["nmk"][] = $ktot;
|
|
}
|
|
return $data;
|
|
}
|
|
|
|
public static function all($app=null, $status=null, $thn = null){
|
|
ini_set('display_errors', 1);
|
|
$year = date('Y');
|
|
if($thn != null){
|
|
$year = $thn;
|
|
}
|
|
$akun = (new self)->akun($year, $app, $status);
|
|
$file = SETUP_PATH.'excel/accounting-warpeka-'.$year.'.xlsx';
|
|
$filex = SETUP_PATH.'/excel/template.xlsx';
|
|
|
|
define('SPECIAL_ARRAY_TYPE', CellSetterArrayValueSpecial::class);
|
|
|
|
$newPar = new Param();
|
|
|
|
$newPar->add('no', $akun);
|
|
$newPar->add('kode', $akun);
|
|
$newPar->add('dk', $akun);
|
|
$newPar->add('nama', $akun);
|
|
$newPar->add('sad', $akun);
|
|
$newPar->add('sak', $akun);
|
|
$newPar->add('jd', $akun);
|
|
$newPar->add('jk', $akun);
|
|
$newPar->add('fd', $akun);
|
|
$newPar->add('fk', $akun);
|
|
$newPar->add('md', $akun);
|
|
$newPar->add('mk', $akun);
|
|
$newPar->add('ad', $akun);
|
|
$newPar->add('ak', $akun);
|
|
$newPar->add('med', $akun);
|
|
$newPar->add('mek', $akun);
|
|
$newPar->add('jnd', $akun);
|
|
$newPar->add('jnk', $akun);
|
|
$newPar->add('jud', $akun);
|
|
$newPar->add('juk', $akun);
|
|
$newPar->add('agd', $akun);
|
|
$newPar->add('agk', $akun);
|
|
$newPar->add('sd', $akun);
|
|
$newPar->add('sk', $akun);
|
|
$newPar->add('od', $akun);
|
|
$newPar->add('ok', $akun);
|
|
$newPar->add('nd', $akun);
|
|
$newPar->add('nk', $akun);
|
|
$newPar->add('ded', $akun);
|
|
$newPar->add('dek', $akun);
|
|
$newPar->add('nmd', $akun);
|
|
$newPar->add('nmk', $akun);
|
|
|
|
$params = $newPar->get();
|
|
|
|
$callbacks = [
|
|
|
|
];
|
|
|
|
// PhpExcelTemplator::saveToFile($filex, $file, $params, $callbacks);
|
|
PhpExcelTemplator::outputToFile($filex, $file, $params, $callbacks);
|
|
|
|
}
|
|
|
|
private function ubahFormatTanggal($tanggal = "2023-08-05") {
|
|
$bulanIndonesia = [
|
|
'01' => 'Januari',
|
|
'02' => 'Februari',
|
|
'03' => 'Maret',
|
|
'04' => 'April',
|
|
'05' => 'Mei',
|
|
'06' => 'Juni',
|
|
'07' => 'Juli',
|
|
'08' => 'Agustus',
|
|
'09' => 'September',
|
|
'10' => 'Oktober',
|
|
'11' => 'November',
|
|
'12' => 'Desember'
|
|
];
|
|
|
|
$tanggalArr = explode('-', $tanggal);
|
|
$bulan = $bulanIndonesia[$tanggalArr[1]];
|
|
$bulan = strtoupper($bulan);
|
|
$tahun = $tanggalArr[0];
|
|
|
|
return "$bulan $tahun";
|
|
}
|
|
|
|
public static function tagihan($kode){
|
|
ini_set('display_errors', 1);
|
|
$year = date('Y');
|
|
$file = SETUP_PATH.'excel/tagihan-'.$kode.'-'.$year.'.xlsx';
|
|
$filex = SETUP_PATH.'/excel/tagihan.xlsx';
|
|
define('SPECIAL_ARRAY_TYPE', CellSetterArrayValueSpecial::class);
|
|
|
|
// copy
|
|
$l = DB::query_result_object("
|
|
SELECT tgl FROM mtagihan m WHERE m.kodetagihan = '$kode' LIMIT 1
|
|
");
|
|
|
|
|
|
$nama = DB::query_result_object("
|
|
SELECT jurubayar FROM mtagihan m WHERE m.kodetagihan = '$kode' LIMIT 1
|
|
");
|
|
|
|
$tgl = date('Y-m-d');
|
|
|
|
$jurubayar = "";
|
|
|
|
if( count($l) > 0 ){
|
|
$tgl = (new self)->ubahFormatTanggal( $l[0]->tgl );
|
|
}
|
|
if( count($nama) > 0 ){
|
|
$jurubayar = $nama[0]->jurubayar;
|
|
}
|
|
|
|
$dataParse = [];
|
|
$data = DB::query_result_object("SELECT
|
|
m.kode
|
|
,a.nama
|
|
,ifnull(sum(if(m.kodesp = 'bw', jumlah,0)),0) bw
|
|
,ifnull(sum(if(m.kodesp = 'sp', jumlah,0)),0) sp
|
|
,ifnull(sum(if(m.kodesp = 'pt', m.pokok,0)),0) pt
|
|
,ifnull(sum(if(m.kodesp = 'sm', jumlah,0)),0) manasuka
|
|
,ifnull(sum(if(m.kodesp = 'tk', jumlah,0)),0) tk
|
|
,ifnull(sum(if(m.kodesp = 'sw', jumlah,0)),0) sw
|
|
,ifnull(sum(if(m.kodesp = 'swp', jumlah,0)),0) swp
|
|
,ifnull(sum(if(m.kodesp = 'usp1', m.pokok + m.bunga,0)),0) usp1
|
|
,ifnull(sum(if(m.kodesp = 'usp2', m.pokok + m.bunga,0)),0) usp2
|
|
,ifnull(sum(if(m.kodesp = 'usp3', m.pokok + m.bunga,0)),0) usp3
|
|
,ifnull(sum(if(m.kodesp = 'reg', m.pokok,0)),0) pokok
|
|
,ifnull(sum(if(m.kodesp = 'reg', m.bunga,0)),0) jasa
|
|
,ifnull(sum(if(m.kodesp = 'reg', (
|
|
SELECT pokok tot from mpinjam where faktur = m.faktur
|
|
),0)),0) pinjaman
|
|
, ifnull(sum(if(m.kodesp = 'reg', m.ke,0)),0) ke
|
|
,ifnull(sum(if(m.kodesp = 'reg', (
|
|
SELECT lama from mpinjam where faktur = m.faktur
|
|
),0)),0) masa
|
|
,\"=C{no}-(G{no}*E{no})\" hsisa
|
|
,\"=SUM(G{no}:K{no})\" jmlusp
|
|
,\"=SUM(M{no}:S{no})\" jmlinuk
|
|
,\"=L{no}+T{no}\" totalall
|
|
FROM mtagihan m
|
|
LEFT JOIN anggota a ON m.kode = a.kode
|
|
WHERE m.kodetagihan = '$kode' GROUP BY m.kode");
|
|
|
|
$newPar = new Param();
|
|
$dataParse['kode'] = (new self)->toArray($data, "kode");
|
|
$newPar->add('kode', $dataParse);
|
|
$dataParse['nama'] = (new self)->toArray($data, "nama");
|
|
$newPar->add('nama', $dataParse);
|
|
$dataParse['bw'] = (new self)->toArray($data, "bw");
|
|
$newPar->add('bw', $dataParse);
|
|
$dataParse['sp'] = (new self)->toArray($data, "sp");
|
|
$newPar->add('sp', $dataParse);
|
|
$dataParse['pt'] = (new self)->toArray($data, "pt");
|
|
$newPar->add('pt', $dataParse);
|
|
$dataParse['manasuka'] = (new self)->toArray($data, "manasuka");
|
|
$newPar->add('manasuka', $dataParse);
|
|
$dataParse['tk'] = (new self)->toArray($data, "tk");
|
|
$newPar->add('tk', $dataParse);
|
|
$dataParse['sw'] = (new self)->toArray($data, "sw");
|
|
$newPar->add('sw', $dataParse);
|
|
$dataParse['swp'] = (new self)->toArray($data, "swp");
|
|
$newPar->add('swp', $dataParse);
|
|
$dataParse['usp1'] = (new self)->toArray($data, "usp1");
|
|
$newPar->add('usp1', $dataParse);
|
|
$dataParse['usp2'] = (new self)->toArray($data, "usp2");
|
|
$newPar->add('usp2', $dataParse);
|
|
$dataParse['usp3'] = (new self)->toArray($data, "usp3");
|
|
$newPar->add('usp3', $dataParse);
|
|
$dataParse['pinjaman'] = (new self)->toArray($data, "pinjaman");
|
|
$newPar->add('pinjaman', $dataParse);
|
|
$dataParse['masa'] = (new self)->toArray($data, "masa");
|
|
$newPar->add('masa', $dataParse);
|
|
$dataParse['pokok'] = (new self)->toArray($data, "pokok");
|
|
$newPar->add('pokok', $dataParse);
|
|
$dataParse['jasa'] = (new self)->toArray($data, "jasa");
|
|
$newPar->add('jasa', $dataParse);
|
|
$dataParse['sisa'] = (new self)->toArray($data, "hsisa");
|
|
$newPar->add('sisa', $dataParse);
|
|
$dataParse['ke'] = (new self)->toArray($data, "ke");
|
|
$newPar->add('ke', $dataParse);
|
|
$dataParse['jmlusp'] = (new self)->toArray($data, "jmlusp");
|
|
$newPar->add('jmlusp', $dataParse);
|
|
$dataParse['jmlinuk'] = (new self)->toArray($data, "jmlinuk");
|
|
$newPar->add('jmlinuk', $dataParse);
|
|
$dataParse['totalall'] = (new self)->toArray($data, "totalall");
|
|
$newPar->add('totalall', $dataParse);
|
|
$newPar->single('mounthy', $tgl);
|
|
$newPar->single('jurubayar', strtoupper($jurubayar));
|
|
|
|
$params = $newPar->get();
|
|
|
|
// encopy
|
|
$callbacks = [];
|
|
PhpExcelTemplator::outputToFile($filex, $file, $params, $callbacks);
|
|
}
|
|
|
|
private function toArray($data = null, $name = ""){
|
|
$new = [];
|
|
$start = 8;
|
|
foreach ($data as $key => $n) {
|
|
$e = ( array ) $n;
|
|
$new[] = str_replace("{no}",$start+$key, $e[$name]);
|
|
}
|
|
return $new;
|
|
}
|
|
|
|
public static function test($kode){
|
|
|
|
ini_set('display_errors', 1);
|
|
define('SPECIAL_ARRAY_TYPE', CellSetterArrayValueSpecial::class);
|
|
|
|
// copy
|
|
|
|
$dataParse = [];
|
|
$data = DB::query_result_object("SELECT
|
|
m.kode
|
|
,a.nama
|
|
,ifnull(sum(if(m.kodesp = 'reg', (
|
|
SELECT pokok + bunga tot from mpinjam where faktur = m.faktur
|
|
),0)),0) pinjaman
|
|
,ifnull(sum(if(m.kodesp = 'bw', jumlah,0)),0) bw
|
|
,ifnull(sum(if(m.kodesp = 'sp', jumlah,0)),0) sp
|
|
,ifnull(sum(if(m.kodesp = 'pt', jumlah,0)),0) pt
|
|
,ifnull(sum(if(m.kodesp = 'sm', jumlah,0)),0) manasuka
|
|
,\"=C{no}-(G{no}*E{no})\" hsisa
|
|
FROM mtagihan m
|
|
LEFT JOIN anggota a ON m.kode = a.kode
|
|
WHERE m.kodetagihan = '$kode' GROUP BY m.kode");
|
|
|
|
$newPar = new Param();
|
|
$dataParse['pinjaman'] = (new self)->toArray($data, "pinjaman");
|
|
$newPar->add('pinjaman', $dataParse);
|
|
$dataParse['kode'] = (new self)->toArray($data, "kode");
|
|
$newPar->add('kode', $dataParse);
|
|
$dataParse['nama'] = (new self)->toArray($data, "nama");
|
|
$newPar->add('nama', $dataParse);
|
|
$dataParse['bw'] = (new self)->toArray($data, "bw");
|
|
$newPar->add('bw', $dataParse);
|
|
$dataParse['sp'] = (new self)->toArray($data, "sp");
|
|
$newPar->add('sp', $dataParse);
|
|
$dataParse['pt'] = (new self)->toArray($data, "pt");
|
|
$newPar->add('pt', $dataParse);
|
|
$dataParse['manasuka'] = (new self)->toArray($data, "manasuka");
|
|
$newPar->add('manasuka', $dataParse);
|
|
|
|
$params = $newPar->get();
|
|
|
|
// encopy
|
|
|
|
DD::view($params);
|
|
|
|
}
|
|
|
|
public static function neraca($tahun, $bulan, $type = 1, $all = ""){
|
|
$bulanData = ["Januari", "Februari", "Maret", "April", "Mei", "Juni", "Juli", "Agustus", "September", "Oktober", "November", "Desember"];
|
|
ini_set('display_errors', 1);
|
|
$file = SETUP_PATH.'excel/neraca-lajur-'.$tahun.'-'.$bulan.'-'.date('ymdhis').'.xlsx';
|
|
$filex = SETUP_PATH.'/excel/neracalajur.xlsx';
|
|
define('SPECIAL_ARRAY_TYPE', CellSetterArrayValueSpecial::class);
|
|
|
|
$nm = "
|
|
, acc.kode
|
|
, acc.nama
|
|
";
|
|
if($type == 1){
|
|
$nm = "
|
|
, acc.main kode
|
|
, acc.nama_main nama
|
|
";
|
|
}
|
|
|
|
$condition = "";
|
|
|
|
if($all == 'induk'){
|
|
$condition = " WHERE acc.app = 'acc' ";
|
|
}
|
|
|
|
if($all == 'usp'){
|
|
$condition = " WHERE acc.app = 'usp' ";
|
|
}
|
|
|
|
$qr = "
|
|
SELECT
|
|
'={no}-6' `no`
|
|
$nm
|
|
, sum(ifnull(na.debet,0)) nad
|
|
, sum(ifnull(na.kredit,0)) nak
|
|
, sum(ifnull(nm.debet,0)) nmd
|
|
, sum(ifnull(nm.kredit,0)) nmk
|
|
, '=D{no}+F{no}' npd
|
|
, '=E{no}+G{no}' npk
|
|
, '=IF(H{no}>I{no},H{no}-I{no},0)' nsd
|
|
, '=IF(I{no}>H{no},I{no}-H{no},0)' nsk
|
|
, '0' pyd
|
|
, '0' pyk
|
|
, '=IF(AND(J{no}>0,K{no}>0),0,IF(AND(J{no}>0,K{no}=0),(J{no}+L{no})-M{no},IF(AND(K{no}>0,F{no}=0),0,L{no})))' pnd
|
|
, '=IF(AND(J{no}>0,K{no}>0),0,IF(AND(J{no}>0,K{no}=0),0,IF(AND(K{no}>0,J{no}=0),(K{no}+M{no})-L{no},M{no})))' pnk
|
|
, '=IF(OR(LEFT(B{no},1)=\"4\",LEFT(B{no},1)=\"5\",LEFT(B{no},1)=\"6\",LEFT(B{no},1)=\"7\",LEFT(B{no},1)=\"8\"),N{no},0)' pld
|
|
, '=IF(OR(LEFT(B{no},1)=\"4\",LEFT(B{no},1)=\"5\",LEFT(B{no},1)=\"6\",LEFT(B{no},1)=\"7\",LEFT(B{no},1)=\"8\"),O{no},0)' plk
|
|
, '=IF(OR(LEFT(B{no},1)=\"1\",LEFT(B{no},1)=\"2\",LEFT(B{no},1)=\"3\"),N{no},0)' ned
|
|
, '=IF(OR(LEFT(B{no},1)=\"1\",LEFT(B{no},1)=\"2\",LEFT(B{no},1)=\"3\"),O{no},0)' nek
|
|
FROM acc
|
|
LEFT JOIN (
|
|
SELECT kode, sum(debet) debet, sum(kredit) kredit FROM (
|
|
SELECT
|
|
rekdebit kode
|
|
, sum(debit) debet
|
|
, 0 kredit
|
|
FROM lap_bb
|
|
LEFT JOIN acc ON acc.kode = lap_bb.rekdebit WHERE tgl < '$tahun-$bulan-01'
|
|
GROUP BY rekdebit
|
|
UNION ALL
|
|
SELECT
|
|
rekkredit kode
|
|
, 0 debet
|
|
, sum(kredit) kredit
|
|
FROM lap_bb
|
|
LEFT JOIN acc ON acc.kode = lap_bb.rekkredit WHERE tgl < '$tahun-$bulan-01'
|
|
GROUP BY rekkredit
|
|
) a GROUP BY a.kode
|
|
) na ON na.kode = acc.kode
|
|
LEFT JOIN (
|
|
SELECT kode, sum(debet) debet, sum(kredit) kredit FROM (
|
|
SELECT
|
|
rekdebit kode
|
|
, sum(debit) debet
|
|
, 0 kredit
|
|
FROM lap_bb
|
|
LEFT JOIN acc ON acc.kode = lap_bb.rekdebit WHERE
|
|
tgl >= '$tahun-$bulan-01' AND tgl <= '".date("Y-m-t",strtotime("$tahun-$bulan"))."'
|
|
GROUP BY rekdebit
|
|
UNION ALL
|
|
SELECT
|
|
rekkredit kode
|
|
, 0 debet
|
|
, sum(kredit) kredit
|
|
FROM lap_bb
|
|
LEFT JOIN acc ON acc.kode = lap_bb.rekkredit WHERE
|
|
tgl >= '$tahun-$bulan-01' AND tgl <= '".date("Y-m-t",strtotime("$tahun-$bulan"))."'
|
|
GROUP BY rekkredit
|
|
) a GROUP BY a.kode
|
|
) nm ON nm.kode = acc.kode
|
|
$condition
|
|
GROUP BY kode
|
|
ORDER BY acc.kode ASC
|
|
";
|
|
// echo "<pre>";
|
|
// echo $qr;
|
|
$data = DB::query_result_object($qr);
|
|
$dataParse = [];
|
|
$newPar = new Param();
|
|
$dataParse['no'] = (new self)->toArray2($data, "no");
|
|
$newPar->add('no', $dataParse);
|
|
$dataParse['kode'] = (new self)->toArray2($data, "kode");
|
|
$newPar->add('kode', $dataParse);
|
|
$dataParse['nama'] = (new self)->toArray2($data, "nama");
|
|
$newPar->add('nama', $dataParse);
|
|
$dataParse['nad'] = (new self)->toArray2($data, "nad");
|
|
$newPar->add('nad', $dataParse);
|
|
$dataParse['nak'] = (new self)->toArray2($data, "nak");
|
|
$newPar->add('nak', $dataParse);
|
|
$dataParse['nmd'] = (new self)->toArray2($data, "nmd");
|
|
$newPar->add('nmd', $dataParse);
|
|
$dataParse['nmk'] = (new self)->toArray2($data, "nmk");
|
|
$newPar->add('nmk', $dataParse);
|
|
$dataParse['npd'] = (new self)->toArray2($data, "npd");
|
|
$newPar->add('npd', $dataParse);
|
|
$dataParse['npk'] = (new self)->toArray2($data, "npk");
|
|
$newPar->add('npk', $dataParse);
|
|
$dataParse['nsd'] = (new self)->toArray2($data, "nsd");
|
|
$newPar->add('nsd', $dataParse);
|
|
$dataParse['nsk'] = (new self)->toArray2($data, "nsk");
|
|
$newPar->add('nsk', $dataParse);
|
|
$dataParse['pyd'] = (new self)->toArray2($data, "pyd");
|
|
$newPar->add('pyd', $dataParse);
|
|
$dataParse['pyk'] = (new self)->toArray2($data, "pyk");
|
|
$newPar->add('pyk', $dataParse);
|
|
$dataParse['pnd'] = (new self)->toArray2($data, "pnd");
|
|
$newPar->add('pnd', $dataParse);
|
|
$dataParse['pnk'] = (new self)->toArray2($data, "pnk");
|
|
$newPar->add('pnk', $dataParse);
|
|
$dataParse['pld'] = (new self)->toArray2($data, "pld");
|
|
$newPar->add('pld', $dataParse);
|
|
$dataParse['plk'] = (new self)->toArray2($data, "plk");
|
|
$newPar->add('plk', $dataParse);
|
|
$dataParse['ned'] = (new self)->toArray2($data, "ned");
|
|
$newPar->add('ned', $dataParse);
|
|
$dataParse['nek'] = (new self)->toArray2($data, "nek");
|
|
$newPar->add('nek', $dataParse);
|
|
$newPar->single('per', $bulanData[ $bulan - 1 ] ." ". $tahun );
|
|
|
|
$lastDates = date("t/m/Y" , strtotime($tahun."-".$bulan."-01"));
|
|
function ubahFormatTanggal($tanggal) {
|
|
// Array bulan dalam bahasa Indonesia
|
|
$bulan = array(
|
|
1 => 'Januari',
|
|
2 => 'Februari',
|
|
3 => 'Maret',
|
|
4 => 'April',
|
|
5 => 'Mei',
|
|
6 => 'Juni',
|
|
7 => 'Juli',
|
|
8 => 'Agustus',
|
|
9 => 'September',
|
|
10 => 'Oktober',
|
|
11 => 'November',
|
|
12 => 'Desember'
|
|
);
|
|
|
|
// Pecah tanggal menjadi array
|
|
$tanggalArray = explode('/', $tanggal);
|
|
// Ambil elemen tanggal, bulan, dan tahun
|
|
$tanggal = $tanggalArray[0];
|
|
$bulanIndex = (int)$tanggalArray[1];
|
|
$tahun = $tanggalArray[2];
|
|
// Ubah bulan menjadi teks
|
|
$bulanTeks = $bulan[$bulanIndex];
|
|
// Format ulang tanggal
|
|
$tanggalFormatBaru = $tanggal . ' ' . $bulanTeks . ' ' . $tahun;
|
|
return $tanggalFormatBaru;
|
|
}
|
|
|
|
$newPar->single('date', ubahFormatTanggal($lastDates) );
|
|
|
|
$params = $newPar->get();
|
|
// encopy
|
|
$callbacks = [];
|
|
PhpExcelTemplator::outputToFile($filex, $file, $params, $callbacks);
|
|
}
|
|
|
|
public static function neraca_tahunan($tahun, $bulan, $type = 1, $all = ""){
|
|
$bulanData = ["Januari", "Februari", "Maret", "April", "Mei", "Juni", "Juli", "Agustus", "September", "Oktober", "November", "Desember"];
|
|
ini_set('display_errors', 1);
|
|
$file = SETUP_PATH.'excel/neraca-lajur-'.$tahun.'-'.$bulan.'-'.date('ymdhis').'.xlsx';
|
|
$filex = SETUP_PATH.'/excel/neracalajur.xlsx';
|
|
define('SPECIAL_ARRAY_TYPE', CellSetterArrayValueSpecial::class);
|
|
|
|
$nm = "
|
|
, acc.kode
|
|
, acc.nama
|
|
";
|
|
if($type == 1){
|
|
$nm = "
|
|
, acc.main kode
|
|
, acc.nama_main nama
|
|
";
|
|
}
|
|
|
|
$condition = "";
|
|
|
|
if($all == 'induk'){
|
|
$condition = " WHERE acc.app = 'acc' ";
|
|
}
|
|
|
|
if($all == 'usp'){
|
|
$condition = " WHERE acc.app = 'usp' ";
|
|
}
|
|
|
|
$qr = "
|
|
SELECT
|
|
'={no}-6' `no`
|
|
$nm
|
|
, sum(ifnull(na.debet,0)) nad
|
|
, sum(ifnull(na.kredit,0)) nak
|
|
, sum(ifnull(nm.debet,0)) nmd
|
|
, sum(ifnull(nm.kredit,0)) nmk
|
|
, '=D{no}+F{no}' npd
|
|
, '=E{no}+G{no}' npk
|
|
, '=IF(H{no}>I{no},H{no}-I{no},0)' nsd
|
|
, '=IF(I{no}>H{no},I{no}-H{no},0)' nsk
|
|
, '0' pyd
|
|
, '0' pyk
|
|
, '=IF(AND(J{no}>0,K{no}>0),0,IF(AND(J{no}>0,K{no}=0),(J{no}+L{no})-M{no},IF(AND(K{no}>0,F{no}=0),0,L{no})))' pnd
|
|
, '=IF(AND(J{no}>0,K{no}>0),0,IF(AND(J{no}>0,K{no}=0),0,IF(AND(K{no}>0,J{no}=0),(K{no}+M{no})-L{no},M{no})))' pnk
|
|
, '=IF(OR(LEFT(B{no},1)=\"4\",LEFT(B{no},1)=\"5\",LEFT(B{no},1)=\"6\",LEFT(B{no},1)=\"7\",LEFT(B{no},1)=\"8\"),N{no},0)' pld
|
|
, '=IF(OR(LEFT(B{no},1)=\"4\",LEFT(B{no},1)=\"5\",LEFT(B{no},1)=\"6\",LEFT(B{no},1)=\"7\",LEFT(B{no},1)=\"8\"),O{no},0)' plk
|
|
, '=IF(OR(LEFT(B{no},1)=\"1\",LEFT(B{no},1)=\"2\",LEFT(B{no},1)=\"3\"),N{no},0)' ned
|
|
, '=IF(OR(LEFT(B{no},1)=\"1\",LEFT(B{no},1)=\"2\",LEFT(B{no},1)=\"3\"),O{no},0)' nek
|
|
FROM acc
|
|
LEFT JOIN (
|
|
SELECT kode, sum(debet) debet, sum(kredit) kredit FROM (
|
|
SELECT
|
|
rekdebit kode
|
|
, sum(debit) debet
|
|
, 0 kredit
|
|
FROM lap_bb
|
|
LEFT JOIN acc ON acc.kode = lap_bb.rekdebit WHERE tgl < '$tahun-01-01'
|
|
GROUP BY rekdebit
|
|
UNION ALL
|
|
SELECT
|
|
rekkredit kode
|
|
, 0 debet
|
|
, sum(kredit) kredit
|
|
FROM lap_bb
|
|
LEFT JOIN acc ON acc.kode = lap_bb.rekkredit WHERE tgl < '$tahun-01-01'
|
|
GROUP BY rekkredit
|
|
) a GROUP BY a.kode
|
|
) na ON na.kode = acc.kode
|
|
LEFT JOIN (
|
|
SELECT kode, sum(debet) debet, sum(kredit) kredit FROM (
|
|
SELECT
|
|
rekdebit kode
|
|
, sum(debit) debet
|
|
, 0 kredit
|
|
FROM lap_bb
|
|
LEFT JOIN acc ON acc.kode = lap_bb.rekdebit WHERE
|
|
tgl >= '$tahun-01-01' AND tgl <= '".date("Y-m-t",strtotime("$tahun-$bulan"))."'
|
|
GROUP BY rekdebit
|
|
UNION ALL
|
|
SELECT
|
|
rekkredit kode
|
|
, 0 debet
|
|
, sum(kredit) kredit
|
|
FROM lap_bb
|
|
LEFT JOIN acc ON acc.kode = lap_bb.rekkredit WHERE
|
|
tgl >= '$tahun-01-01' AND tgl <= '".date("Y-m-t",strtotime("$tahun-$bulan"))."'
|
|
GROUP BY rekkredit
|
|
) a GROUP BY a.kode
|
|
) nm ON nm.kode = acc.kode
|
|
$condition
|
|
GROUP BY kode
|
|
ORDER BY acc.kode ASC
|
|
";
|
|
// echo "<pre>";
|
|
// echo $qr;
|
|
$data = DB::query_result_object($qr);
|
|
$dataParse = [];
|
|
$newPar = new Param();
|
|
$dataParse['no'] = (new self)->toArray2($data, "no");
|
|
$newPar->add('no', $dataParse);
|
|
$dataParse['kode'] = (new self)->toArray2($data, "kode");
|
|
$newPar->add('kode', $dataParse);
|
|
$dataParse['nama'] = (new self)->toArray2($data, "nama");
|
|
$newPar->add('nama', $dataParse);
|
|
$dataParse['nad'] = (new self)->toArray2($data, "nad");
|
|
$newPar->add('nad', $dataParse);
|
|
$dataParse['nak'] = (new self)->toArray2($data, "nak");
|
|
$newPar->add('nak', $dataParse);
|
|
$dataParse['nmd'] = (new self)->toArray2($data, "nmd");
|
|
$newPar->add('nmd', $dataParse);
|
|
$dataParse['nmk'] = (new self)->toArray2($data, "nmk");
|
|
$newPar->add('nmk', $dataParse);
|
|
$dataParse['npd'] = (new self)->toArray2($data, "npd");
|
|
$newPar->add('npd', $dataParse);
|
|
$dataParse['npk'] = (new self)->toArray2($data, "npk");
|
|
$newPar->add('npk', $dataParse);
|
|
$dataParse['nsd'] = (new self)->toArray2($data, "nsd");
|
|
$newPar->add('nsd', $dataParse);
|
|
$dataParse['nsk'] = (new self)->toArray2($data, "nsk");
|
|
$newPar->add('nsk', $dataParse);
|
|
$dataParse['pyd'] = (new self)->toArray2($data, "pyd");
|
|
$newPar->add('pyd', $dataParse);
|
|
$dataParse['pyk'] = (new self)->toArray2($data, "pyk");
|
|
$newPar->add('pyk', $dataParse);
|
|
$dataParse['pnd'] = (new self)->toArray2($data, "pnd");
|
|
$newPar->add('pnd', $dataParse);
|
|
$dataParse['pnk'] = (new self)->toArray2($data, "pnk");
|
|
$newPar->add('pnk', $dataParse);
|
|
$dataParse['pld'] = (new self)->toArray2($data, "pld");
|
|
$newPar->add('pld', $dataParse);
|
|
$dataParse['plk'] = (new self)->toArray2($data, "plk");
|
|
$newPar->add('plk', $dataParse);
|
|
$dataParse['ned'] = (new self)->toArray2($data, "ned");
|
|
$newPar->add('ned', $dataParse);
|
|
$dataParse['nek'] = (new self)->toArray2($data, "nek");
|
|
$newPar->add('nek', $dataParse);
|
|
$newPar->single('per', $bulanData[ $bulan - 1 ] ." ". $tahun );
|
|
|
|
$lastDates = date("t/m/Y" , strtotime($tahun."-".$bulan."-01"));
|
|
function ubahFormatTanggal($tanggal) {
|
|
// Array bulan dalam bahasa Indonesia
|
|
$bulan = array(
|
|
1 => 'Januari',
|
|
2 => 'Februari',
|
|
3 => 'Maret',
|
|
4 => 'April',
|
|
5 => 'Mei',
|
|
6 => 'Juni',
|
|
7 => 'Juli',
|
|
8 => 'Agustus',
|
|
9 => 'September',
|
|
10 => 'Oktober',
|
|
11 => 'November',
|
|
12 => 'Desember'
|
|
);
|
|
|
|
// Pecah tanggal menjadi array
|
|
$tanggalArray = explode('/', $tanggal);
|
|
// Ambil elemen tanggal, bulan, dan tahun
|
|
$tanggal = $tanggalArray[0];
|
|
$bulanIndex = (int)$tanggalArray[1];
|
|
$tahun = $tanggalArray[2];
|
|
// Ubah bulan menjadi teks
|
|
$bulanTeks = $bulan[$bulanIndex];
|
|
// Format ulang tanggal
|
|
$tanggalFormatBaru = $tanggal . ' ' . $bulanTeks . ' ' . $tahun;
|
|
return $tanggalFormatBaru;
|
|
}
|
|
|
|
$newPar->single('date', ubahFormatTanggal($lastDates) );
|
|
|
|
$params = $newPar->get();
|
|
// encopy
|
|
$callbacks = [];
|
|
PhpExcelTemplator::outputToFile($filex, $file, $params, $callbacks);
|
|
}
|
|
|
|
private function toArray2($data = null, $name = ""){
|
|
$new = [];
|
|
$start = 7;
|
|
foreach ($data as $key => $n) {
|
|
$e = ( array ) $n;
|
|
$new[] = str_replace("{no}",$start+$key, $e[$name]);
|
|
}
|
|
return $new;
|
|
}
|
|
|
|
} |