371 lines
16 KiB
PHP
371 lines
16 KiB
PHP
<?php
|
|
|
|
/*******************************************************************
|
|
|
|
PHP Class : /Utils/Gel
|
|
Description : Class khusus untuk Gel / Pembukuan
|
|
Created By : Siau Pheng ([email protected]).
|
|
Created Date : February 10th, 2012.
|
|
Last Modified : October 24th, 2026.
|
|
|
|
(c) 3FONIA Software; WWW.3FONIA.COM.
|
|
|
|
*******************************************************************/
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace App\Utils;
|
|
|
|
use flight\Engine;
|
|
use flight\database\SimplePdo;
|
|
use Webapp\Web\Param;
|
|
|
|
final class Gel {
|
|
|
|
private int $id_server;
|
|
private int $id_cab;
|
|
private string $rek_shu;
|
|
protected Engine $app;
|
|
protected SimplePdo $db;
|
|
|
|
public function __construct(Engine $app) {
|
|
$this->app = $app;
|
|
$this->db = $app->db();
|
|
$this->id_server = (int) $app->session()->get('WEB_APP')['SERVER_ID'];
|
|
$this->id_cab = (int) $app->session()->get('APP_INV')['CABANGID'];
|
|
$this->rek_shu = (string) $app->session()->get('APP_GEL')['SHU'];
|
|
}
|
|
|
|
public function Jurnal_Baru(int $cab=0, string $tgl="", bool $save=false): string {
|
|
if (empty($tgl)) {
|
|
$tno = "G" . date("y") . abjad((int) date("n"));
|
|
} else {
|
|
$tmp = explode("-", $tgl);
|
|
$tno = "G" . substr((string) $tmp[2],2,2) . abjad((int) $tmp[1]);
|
|
}
|
|
$tno .= $this->id_server . $cab;
|
|
$ino = (int) $this->db->fetchField("SELECT MAX(RIGHT(Kode_Jurnal,5)) FROM gel_jurnal WHERE Kode_Jurnal LIKE ?", [$tno.'%']) ?? 0;
|
|
$tno .= isi_nol(++$ino, 5);
|
|
if ($save === true) $this->db->runQuery("INSERT INTO gel_jurnal (Kode_Jurnal) VALUES(?)", [$tno]);
|
|
return $tno;
|
|
}
|
|
|
|
public function Jurnal_Post(array $ajurnal = [], array $adetail = []): string {
|
|
if (count($ajurnal) === 0) return '';
|
|
foreach ($ajurnal as $_key => $_val) {
|
|
if (is_int($_key)) {
|
|
unset($ajurnal[$_key]);
|
|
$ajurnal[$_val] = Param::Get($_val);
|
|
}
|
|
}
|
|
$ajurnal['CabID'] ??= $this->id_cab;
|
|
$ajurnal['Tanggal'] ??= tgl_dmy();
|
|
$ajurnal['Kode_Jurnal'] ??= $this->Jurnal_Baru((int) $ajurnal['CabID'], $ajurnal['Tanggal']);
|
|
|
|
$afield = []; $avalue = []; $aupdate = []; $aholder = array_fill(0, count($ajurnal), '?');
|
|
foreach ($ajurnal as $_key => $_val) {
|
|
$afield[] = $_key;
|
|
$avalue[] = (in_array($_key, ["Tanggal"]) ? tgl_sql2str($_val) : $_val);
|
|
if ($_key == "Kode_Jurnal") continue;
|
|
$aupdate[] = $_key . '=VALUES('. $_key . ')';
|
|
}
|
|
array_push($afield, "DateStamp", "UserID");
|
|
array_push($aholder, 'NOW()', '?');
|
|
array_push($aupdate, 'DateStamp=VALUES(DateStamp)', 'UserID=VALUES(UserID)');
|
|
array_push($avalue, $this->app->user()->id());
|
|
$this->db->runQuery("INSERT INTO gel_jurnal (".implode(",",$afield).") VALUES (".implode(",",$aholder).") ON DUPLICATE KEY UPDATE ".implode(",",$aupdate), $avalue);
|
|
|
|
if (count($adetail) === 0) return $ajurnal['Kode_Jurnal'];
|
|
$aper = $this->Periode($ajurnal['Tanggal']);
|
|
$this->db->delete("gel_jurnal_detail", "Kode_Jurnal=?", [$ajurnal['Kode_Jurnal']]);
|
|
foreach ($adetail as $_key1 => $_detail) {
|
|
if (empty($_detail['Kode_Rek']) || (float) $_detail['Jumlah'] === 0) continue;
|
|
$ainsert = [];
|
|
foreach ($_detail as $_key2 => $_val) {
|
|
$ainsert[$_key2] = (in_array($_key2, ["Jumlah"]) ? (abs($_val) * (((string) $_detail['DK']==="K") ? -1 : 1)) : $_val);
|
|
}
|
|
$ainsert = array_merge($ainsert, ['Kode_Jurnal'=>$ajurnal['Kode_Jurnal'], 'TipeID'=>$ajurnal['TipeID'], 'Tgl_Ref'=>tgl_sql2str($ajurnal['Tanggal']), 'Tahun'=>$aper['Tahun'], 'Bulan'=>$aper['Bulan'], 'CabID'=>$ajurnal['CabID'], 'UserID'=>$this->app->user()->id()]);
|
|
$this->db->insert("gel_jurnal_detail", $ainsert);
|
|
}
|
|
return $ajurnal['Kode_Jurnal'];
|
|
}
|
|
|
|
public function Jurnal_Save(string $kode_jurnal, int $tipe, int $cab, string $tgl, ?string $no_ref, ?string $ket): string {
|
|
$tgl = (empty($tgl)) ? tgl_dmy() : $tgl;
|
|
$kode_jurnal = empty($kode_jurnal) ? $this->Jurnal_Baru($cab, $tgl) : $kode_jurnal;
|
|
|
|
$afield = [ 'Kode_Jurnal', 'TipeID', 'CabID', 'Tanggal', 'Kode_Ref', 'Catatan', 'DateStamp', 'UserID' ];
|
|
$aholder = [ '?', '?', '?', '?', '?', '?', 'NOW()', '?' ];
|
|
$avalue = [ $kode_jurnal, $tipe, $cab, tgl_sql2str($tgl), (string) $no_ref, (string) $ket, $this->app->user()->id() ];
|
|
$aupdate = array_map(fn($fi) => $fi . '=VALUES(' .$fi. ')', array_diff($afield, ['Kode_Jurnal']));
|
|
|
|
$this->db->runQuery("INSERT INTO gel_jurnal (".implode(",",$afield).") VALUES (".implode(",",$aholder).") ON DUPLICATE KEY UPDATE ".implode(",",$aupdate), $avalue);
|
|
return $kode_jurnal;
|
|
}
|
|
|
|
public function Detail_Copy(string $kode_jurnal): void {
|
|
if (empty($kode_jurnal)) return;
|
|
$this->Detail_Cancel($kode_jurnal);
|
|
$this->db->runQuery("INSERT INTO tmp_gel_jurnal_detail (Kode_Jurnal,TipeID,Tgl_Ref,Tahun,Bulan,Kode_Rek,MemberID,SupID,CabID,DK,Jumlah,UserID,Unix) SELECT Kode_Jurnal,TipeID,Tgl_Ref,Tahun,Bulan,Kode_Rek,MemberID,SupID,CabID,DK,Jumlah,UserID,'".$this->app->user()->unix()."' FROM gel_jurnal_detail WHERE Kode_Jurnal='".$kode_jurnal."'");
|
|
}
|
|
|
|
public function Detail_Save(string $kode_jurnal, int $tipe=0, int $cab=0, string $tgl="", bool $baru=false, string $kode_lama=""): void {
|
|
if (empty($kode_jurnal)) return;
|
|
$aper = $this->Periode($tgl);
|
|
$this->db->delete("gel_jurnal_detail", "Kode_Jurnal=? OR Kode_Jurnal=?", [$kode_jurnal, $kode_lama]);
|
|
if ($baru === true) {
|
|
$this->Detail_Cancel($kode_jurnal);
|
|
$this->db->update("tmp_gel_jurnal_detail", ['Kode_Jurnal'=>$kode_jurnal], "Kode_Jurnal=? AND Unix=?", [$kode_lama, $this->app->user()->unix()]);
|
|
}
|
|
$this->db->runQuery("INSERT INTO gel_jurnal_detail (Kode_Jurnal,TipeID,Tgl_Ref,Tahun,Bulan,Kode_Rek,MemberID,SupID,CabID,DK,Jumlah,UserID) SELECT Kode_Jurnal,?,'".tgl_sql2str($tgl)."','".$aper['Tahun']."','".$aper['Bulan']."',Kode_Rek,MemberID,SupID,?,DK,Jumlah,UserID FROM tmp_gel_jurnal_detail WHERE Kode_Jurnal=?", [$tipe, $cab, $kode_jurnal]);
|
|
$this->Detail_Cancel($kode_jurnal);
|
|
}
|
|
|
|
public function Detail_Cancel(string $kode_jurnal): void {
|
|
if (empty($kode_jurnal)) return;
|
|
$this->db->delete("tmp_gel_jurnal_detail", "Kode_Jurnal=?", [$kode_jurnal]);
|
|
}
|
|
|
|
public function JurnalSelisih(array $ajurnal=[]): float {
|
|
$saldo = 0;
|
|
foreach ($ajurnal as $jurnal) {
|
|
$saldo += (($jurnal[1]==="D") ? 1 : -1) * abs($jurnal[2]);
|
|
}
|
|
return $saldo;
|
|
}
|
|
|
|
public function Saldo(string $tgl="", int $mod=1, int $level=3, string $tipe="N"): array {
|
|
//mod=1:akum,2:bln | tipe=N:Neraca,R:Rugilaba
|
|
$aper = $this->Periode($tgl);
|
|
|
|
// generate sql script, for accumulate monthly rekap
|
|
$tjml = "R.Muta_".isi_nol($aper["Bulan"], 2);
|
|
if ($mod === 1) {
|
|
$tjml = "R.Open";
|
|
for ($i=1; $i<=$aper["Bulan"]; $i++) {
|
|
$tjml .= "+R.Muta_".isi_nol($i, 2);
|
|
}
|
|
}
|
|
$tsql = " AND (Grup_RekID BETWEEN ". (($tipe==="N") ? "1 AND 3" : "4 AND 5") .") ";
|
|
|
|
// ambil rekening yang sesuai batas level
|
|
$arek = $this->db->fetchAll("SELECT A.*, ".$tjml." Jumlah FROM (SELECT * FROM gel_acc WHERE Level<=?".$tsql.") A LEFT JOIN gel_rekap R ON (A.Kode_Rek=R.Kode_Rek AND R.Tahun='".$aper['Tahun']."') ORDER BY A.Kode_Rek", [$level]);
|
|
|
|
// ambil rekening diluar batas level laporan dan mode group
|
|
$arek2 = $this->db->fetchAll("SELECT *,0 Jumlah FROM gel_acc WHERE Level>? AND Mode='G'".$tsql, [$level]);
|
|
|
|
// ambil rekap diluar batas level laporan dan mode detail
|
|
$aluar = $this->db->fetchAll("SELECT A.Kode_Induk Kode_Rek,SUM(".$tjml.") Jumlah FROM (SELECT * FROM gel_acc WHERE Level>? AND Mode='D'".$tsql.") A LEFT JOIN gel_rekap R ON (A.Kode_Rek=R.Kode_Rek AND R.Tahun='".$aper['Tahun']."') GROUP BY A.Kode_Induk", [$level]);
|
|
foreach ($aluar as $row) {
|
|
//cek batas luar
|
|
$ba = false;
|
|
foreach ($arek2 as $key => $val) {
|
|
if ($val['Kode_Rek'] == $row['Kode_Rek']) {
|
|
$ba = true;
|
|
$arek2[$key]['Jumlah'] += $row['Jumlah'];
|
|
break;
|
|
}
|
|
}
|
|
if ($ba === false) {
|
|
foreach ($arek as $key => $val) {
|
|
if ($val['Kode_Rek'] == $row['Kode_Rek']) {
|
|
$ba = true;
|
|
$arek[$key]['Jumlah'] += $row['Jumlah'];
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
if ($ba === false) echo $row['Kode_Rek']; //debug
|
|
}
|
|
|
|
// merger rekap luar level pada pilihan level 2
|
|
foreach ($arek2 as $key2 => $val2) {
|
|
$ba = false;
|
|
foreach ($arek as $key => $val) {
|
|
if ($val2['Kode_Induk'] == $val['Kode_Rek']) {
|
|
$ba = true;
|
|
break;
|
|
}
|
|
}
|
|
if ($ba === false) {
|
|
foreach ($arek2 as $key3 => $val3) {
|
|
if ($val2['Kode_Induk'] == $val3['Kode_Rek']) {
|
|
$arek2[$key3]['Jumlah'] += $val2['Jumlah'];
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
// merger rekap luar level kedalam batas level
|
|
foreach ($arek2 as $key2 => $val2) {
|
|
foreach ($arek as $key => $val) {
|
|
if ($val2['Kode_Induk'] == $val['Kode_Rek']) {
|
|
$arek[$key]['Jumlah'] += $val2['Jumlah'];
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
|
|
if ($tipe === "N") {
|
|
// rekap pendapatan + biaya kedalam rekening SHU tahun berjalan
|
|
$dshu = $this->db->fetchField("SELECT SUM(".$tjml.") Jumlah FROM gel_rekap R LEFT JOIN gel_acc A ON (A.Kode_Rek=R.Kode_Rek) WHERE R.Tahun='".$aper['Tahun']."' AND (A.Grup_RekID BETWEEN 4 AND 5)") ?? 0;
|
|
foreach ($arek as $key => $val) {
|
|
if ($val['Kode_Rek'] == $this->rek_shu) {
|
|
$arek[$key]['Jumlah'] += $dshu;
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
|
|
// rekap saldo dari level besar ke level 2
|
|
for ($nlev=$level; $nlev>1; $nlev--) {
|
|
foreach ($arek as $key => $val) {
|
|
if ($val['Level'] == $nlev) {
|
|
foreach ($arek as $key2 => $val2) {
|
|
if ($val2['Kode_Rek'] == $val['Kode_Induk']) {
|
|
$arek[$key2]['Jumlah'] += $val['Jumlah'];
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
return $arek;
|
|
}
|
|
|
|
public function RugiLaba(int $bln1=0, int $thn1=0, int $bln2=0, int $thn2=0, int $level=3): array {
|
|
if ($bln1===0 || $thn1===0 || $bln2===0 || $thn2===0) return [];
|
|
|
|
$tjml = "R.Muta_".isi_nol($bln2, 2);
|
|
if ($bln1 !== $bln2) {
|
|
$tjml = ($bln1 === 1)? "R.Open" : "0";
|
|
for ($i=$bln1; $i<=$bln2; $i++) {
|
|
$tjml .= "+R.Muta_".isi_nol($i, 2);
|
|
}
|
|
}
|
|
$taddsql = " ";
|
|
|
|
// ambil rekening yang sesuai batas level
|
|
$arek = $this->db->fetchAll("SELECT A.*, ".$tjml." Jumlah FROM (SELECT * FROM gel_acc WHERE Level<=? AND (Grup_RekID BETWEEN 4 AND 5)) A LEFT JOIN gel_rekap R ON (A.Kode_Rek=R.Kode_Rek AND R.Tahun='".$thn2."') ORDER BY A.Kode_Rek", [$level]);
|
|
|
|
// ambil rekening diluar batas level laporan dan mode group
|
|
$arek2 = $this->db->fetchAll("SELECT *,0 Jumlah FROM gel_acc WHERE Level>? AND (Grup_RekID BETWEEN 4 AND 5) AND Mode='G'", [$level]);
|
|
|
|
// ambil rekap diluar batas level laporan dan mode detail
|
|
$aluar = $this->db->fetchAll("SELECT A.Kode_Induk Kode_Rek,SUM(".$tjml.") Jumlah FROM (SELECT * FROM gel_acc WHERE Level>? AND (Grup_RekID BETWEEN 4 AND 5) AND Mode='D') A LEFT JOIN gel_rekap R ON (A.Kode_Rek=R.Kode_Rek AND R.Tahun='".$thn2."') GROUP BY A.Kode_Induk", [$level]);
|
|
foreach ($aluar as $row) {
|
|
//cek batas luar
|
|
$ba = false;
|
|
foreach ($arek2 as $key => $val) {
|
|
if ($val['Kode_Rek'] === $row['Kode_Rek']) {
|
|
$ba = true;
|
|
$arek2[$key]['Jumlah'] += $row['Jumlah'];
|
|
break;
|
|
}
|
|
}
|
|
if ($ba === false) {
|
|
foreach ($arek as $key => $val) {
|
|
if ($val['Kode_Rek'] === $row['Kode_Rek']) {
|
|
$ba = true;
|
|
$arek[$key]['Jumlah'] += $row['Jumlah'];
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
if ($ba === false) echo $row['Kode_Rek']; //debug
|
|
}
|
|
// merger rekap luar level pada pilihan level 2
|
|
foreach ($arek2 as $key2 => $val2) {
|
|
$ba = false;
|
|
foreach ($arek as $key => $val) {
|
|
if ($val2['Kode_Induk'] === $val['Kode_Rek']) {
|
|
$ba = true;
|
|
break;
|
|
}
|
|
}
|
|
if ($ba === false) {
|
|
foreach ($arek2 as $key3 => $val3) {
|
|
if ($val2['Kode_Induk'] === $val3['Kode_Rek']) {
|
|
$arek2[$key3]['Jumlah'] += $val2['Jumlah'];
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
// merger rekap luar level kedalam batas level
|
|
foreach ($arek2 as $key2 => $val2) {
|
|
foreach ($arek as $key => $val) {
|
|
if ($val2['Kode_Induk'] == $val['Kode_Rek']) {
|
|
$arek[$key]['Jumlah'] += $val2['Jumlah'];
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
// rekap saldo dari level besar ke level 2
|
|
for ($nlev=$level; $nlev>1; $nlev--) {
|
|
foreach ($arek as $key => $val) {
|
|
if ($val['Level'] == $nlev) {
|
|
foreach ($arek as $key2 => $val2) {
|
|
if ($val2['Kode_Rek'] == $val['Kode_Induk']) {
|
|
$arek[$key2]['Jumlah'] += $val['Jumlah'];
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
return $arek;
|
|
}
|
|
|
|
public function Tahun_Array(): array {
|
|
$ahasil = $this->db->fetchPairs("SELECT Tahun,Tahun FROM gel_rekap GROUP BY Tahun ASC") ?? [];
|
|
return $ahasil;
|
|
}
|
|
|
|
public function Karya_Baru(string $tgl): string {
|
|
if (empty($tgl)) {
|
|
$tno = date("\T\Kym\-");
|
|
} else {
|
|
$tmp = explode("-", $tgl);
|
|
$tno = "TK".substr($tmp[2],2,2).$tmp[1]."-";
|
|
}
|
|
$ino = (int) $this->db->fetchField("SELECT MAX(RIGHT(No_Trans,4)) FROM gel_karyawan WHERE No_Trans LIKE ?", [$tno.'%']) ?? 0;
|
|
$tno .= isi_nol(++$ino, 4);
|
|
return $tno;
|
|
}
|
|
|
|
public function Update_Lunas_Sup(int $id_sup): bool {
|
|
if (empty($id_sup)) return false;
|
|
$isup = $this->db->fetchField("SELECT COUNT(*) FROM inv_beli WHERE Lunas=0 AND SupID=?", [$id_sup]) ?? 0;
|
|
$this->db->runQuery("UPDATE para_supplier SET Lunas=? WHERE SupID=?", [$isup, $id_sup]);
|
|
return true;
|
|
}
|
|
|
|
public function Update_Lunas_Buy(int $id_member): bool {
|
|
if (empty($id_member)) return false;
|
|
$imem = $this->db->fetchField("SELECT COUNT(*) FROM inv_jual WHERE Lunas=0 AND MemberID=?", [$id_member]) ?? 0;
|
|
$this->db->runQuery("UPDATE para_member SET Lunas=? WHERE MemberID=?", [$imem, $id_member]);
|
|
return true;
|
|
}
|
|
|
|
public static function Periode(string $tgl=""): array {
|
|
$tgl = (empty($tgl)) ? tgl_dmy() : $tgl;
|
|
$atgl = explode("-", $tgl);
|
|
return [ 'Tahun' => $atgl[2], 'Bulan' => $atgl[1] ];
|
|
}
|
|
|
|
public static function DK(int $grup=0): string {
|
|
return (in_array($grup, [1,5])) ? "D" : "K";
|
|
}
|
|
|
|
public static function Level_Array(int $level = -1, int $max_level = 5): array|string {
|
|
//$_SESSION['__APP_GEL']['MAX_LEVEL']
|
|
$_alevel = []; for ($i=1; $i<=$max_level; $i++) $_alevel[$i] = "Level ".$i;
|
|
return ($level === -1) ? $_alevel : $_alevel[$level];
|
|
}
|
|
|
|
}
|