345 lines
21 KiB
PHP
345 lines
21 KiB
PHP
<?php
|
|
|
|
/*******************************************************************
|
|
|
|
App. Name : SISTEM INFORMASI MONITORING DAN EVALUASI
|
|
Module : /apps/php/hasil/real.php
|
|
Desc. : PHP - Laporan Realisasi Bulanan Per OPD
|
|
Created By : Siau Pheng ([email protected]).
|
|
Created Date : March 20th, 2012.
|
|
Last Modified : December 27th, 2023.
|
|
|
|
(c) 2012 - 2023, 3FONIA Software; WWW.3FONIA.COM.
|
|
|
|
*******************************************************************/
|
|
|
|
require_once(__DIR__."/../../../../../vendor/autoload.php");
|
|
require_once(__DIR__."/../func/emonev.php");
|
|
require_once("Spreadsheet/Excel/Writer.php");
|
|
|
|
use siaupheng\fonia3\DB\MySQL;
|
|
use siaupheng\fonia3\Web\Param;
|
|
use siaupheng\fonia3\Web\Form;
|
|
|
|
$db = $db ?? new MySQL();
|
|
$ajson = [];
|
|
$data = Param::Get("data");
|
|
$mode = Param::Get("mode");
|
|
$tahun = Param::Get("Tahun", $_SESSION['__WEB_APP']['REAL_THN']);
|
|
$bulan = Param::Get("Bulan", $_SESSION['__WEB_APP']['REAL_BLN']);
|
|
$id_opd = Param::Get("OPDID", 0);
|
|
$thn_submap = map_tahun_submap($tahun);
|
|
|
|
if ( $data == "print" || $data == "export") {
|
|
$a_opd = [];
|
|
$db->Query("SELECT * FROM para90_opd WHERE OPDID='".$id_opd."' LIMIT 1");
|
|
if ($db->Next()) $a_opd = $db->AllRow();
|
|
$tsql = " Tahun='".$tahun."' AND OPDID='".$id_opd."'";
|
|
|
|
if ($bulan == 1) {
|
|
$qK = "(A.RealK_B1)";
|
|
$qF = "(A.RealF_B1,0)";
|
|
}
|
|
if ($bulan == 2) {
|
|
$qK = "(A.RealK_B1+A.RealK_B2)";
|
|
$qF = "(A.RealF_B1,A.RealF_B2)";
|
|
}
|
|
if ($bulan == 3) {
|
|
$qK = "(A.RealK_B1+A.RealK_B2+A.RealK_B3)";
|
|
$qF = "(A.RealF_B1,A.RealF_B2,A.RealF_B3)";
|
|
}
|
|
if ($bulan == 4) {
|
|
$qK = "(A.RealK_B1+A.RealK_B2+A.RealK_B3+A.RealK_B4)";
|
|
$qF = "(A.RealF_B1,A.RealF_B2,A.RealF_B3,A.RealF_B4)";
|
|
}
|
|
if ($bulan == 5) {
|
|
$qK = "(A.RealK_B1+A.RealK_B2+A.RealK_B3+A.RealK_B4+A.RealK_B5)";
|
|
$qF = "(A.RealF_B1,A.RealF_B2,A.RealF_B3,A.RealF_B4,A.RealF_B5)";
|
|
}
|
|
if ($bulan == 6) {
|
|
$qK = "(A.RealK_B1+A.RealK_B2+A.RealK_B3+A.RealK_B4+A.RealK_B5+A.RealK_B6)";
|
|
$qF = "(A.RealF_B1,A.RealF_B2,A.RealF_B3,A.RealF_B4,A.RealF_B5,A.RealF_B6)";
|
|
}
|
|
if ($bulan == 7) {
|
|
$qK = "(A.RealK_B1+A.RealK_B2+A.RealK_B3+A.RealK_B4+A.RealK_B5+A.RealK_B6+A.RealK_B7)";
|
|
$qF = "(A.RealF_B1,A.RealF_B2,A.RealF_B3,A.RealF_B4,A.RealF_B5,A.RealF_B6,A.RealF_B7)";
|
|
}
|
|
if ($bulan == 8) {
|
|
$qK = "(A.RealK_B1+A.RealK_B2+A.RealK_B3+A.RealK_B4+A.RealK_B5+A.RealK_B6+A.RealK_B7+A.RealK_B8)";
|
|
$qF = "(A.RealF_B1,A.RealF_B2,A.RealF_B3,A.RealF_B4,A.RealF_B5,A.RealF_B6,A.RealF_B7,A.RealF_B8)";
|
|
}
|
|
if ($bulan == 9) {
|
|
$qK = "(A.RealK_B1+A.RealK_B2+A.RealK_B3+A.RealK_B4+A.RealK_B5+A.RealK_B6+A.RealK_B7+A.RealK_B8+A.RealK_B9)";
|
|
$qF = "(A.RealF_B1,A.RealF_B2,A.RealF_B3,A.RealF_B4,A.RealF_B5,A.RealF_B6,A.RealF_B7,A.RealF_B8,A.RealF_B9)";
|
|
}
|
|
if ($bulan == 10) {
|
|
$qK = "(A.RealK_B1+A.RealK_B2+A.RealK_B3+A.RealK_B4+A.RealK_B5+A.RealK_B6+A.RealK_B7+A.RealK_B8+A.RealK_B9+A.RealK_B10)";
|
|
$qF = "(A.RealF_B1,A.RealF_B2,A.RealF_B3,A.RealF_B4,A.RealF_B5,A.RealF_B6,A.RealF_B7,A.RealF_B8,A.RealF_B9,A.RealF_B10)";
|
|
}
|
|
if ($bulan == 11) {
|
|
$qK = "(A.RealK_B1+A.RealK_B2+A.RealK_B3+A.RealK_B4+A.RealK_B5+A.RealK_B6+A.RealK_B7+A.RealK_B8+A.RealK_B9+A.RealK_B10+A.RealK_B11)";
|
|
$qF = "(A.RealF_B1,A.RealF_B2,A.RealF_B3,A.RealF_B4,A.RealF_B5,A.RealF_B6,A.RealF_B7,A.RealF_B8,A.RealF_B9,A.RealF_B10,A.RealF_B11)";
|
|
}
|
|
if ($bulan == 12) {
|
|
$qK = "(A.RealK_B1+A.RealK_B2+A.RealK_B3+A.RealK_B4+A.RealK_B5+A.RealK_B6+A.RealK_B7+A.RealK_B8+A.RealK_B9+A.RealK_B10+A.RealK_B11+A.RealK_B12)";
|
|
$qF = "(A.RealF_B1,A.RealF_B2,A.RealF_B3,A.RealF_B4,A.RealF_B5,A.RealF_B6,A.RealF_B7,A.RealF_B8,A.RealF_B9,A.RealF_B10,A.RealF_B11,A.RealF_B12)";
|
|
}
|
|
|
|
// proses rekap database
|
|
Report_Empty("dpa_prog");
|
|
Report_Empty("dpa_keg");
|
|
Report_Empty("dpa_sub");
|
|
// insert prog | GREATEST untuk fisik
|
|
$db->Query("INSERT INTO rep_dpa_prog SELECT A.Tahun,A.OPDID,A.ProgID,'',SUM(A.Pagu_Ini),SUM(A.Renc_Keu),SUM".$qK.",0,'".user_unix()."','".user_id()."' FROM (SELECT * FROM dpa_akun WHERE".$tsql.") A GROUP BY A.ProgID");
|
|
// insert keg
|
|
$db->Query("INSERT INTO rep_dpa_keg SELECT A.Tahun,A.OPDID,A.ProgID,A.KegID,'',SUM(A.Pagu_Ini),SUM(A.Renc_Keu),SUM".$qK.",0,'".user_unix()."','".user_id()."' FROM (SELECT * FROM dpa_akun WHERE".$tsql.") A GROUP BY A.KegID");
|
|
// insert sub
|
|
$db->Query("INSERT INTO rep_dpa_sub SELECT A.Tahun,A.OPDID,A.ProgID,A.KegID,A.SubID,'',SUM(A.Pagu_Ini),SUM(A.Renc_Keu),SUM".$qK.",0,'".user_unix()."','".user_id()."' FROM (SELECT * FROM dpa_akun WHERE".$tsql.") A GROUP BY A.SubID");
|
|
// update kode prog, keg dan subkeg
|
|
$db->Query("UPDATE rep_dpa_prog A,dpa_prog B SET A.PROG_KODE=B.K_Prog WHERE A.Tahun=B.Tahun AND A.OPDID=B.OPDID AND A.ProgID=B.ProgID AND A.Tahun='".$tahun."'");
|
|
$db->Query("UPDATE rep_dpa_keg A,dpa_keg B SET A.KEG_KODE=B.K_Keg WHERE A.Tahun=B.Tahun AND A.OPDID=B.OPDID AND A.ProgID=B.ProgID AND A.KegID=B.KegID AND A.Tahun='".$tahun."'");
|
|
//$db->Query("UPDATE rep_dpa_sub A,dpa_sub B SET A.SUB_KODE=B.K_Sub WHERE A.Tahun=B.Tahun AND A.OPDID=B.OPDID AND A.ProgID=B.ProgID AND A.KegID=B.KegID AND A.SubID=B.SubID AND A.Tahun='".$tahun."'");
|
|
|
|
$db->Query("SELECT A.*,RP.*,RK.*,RS.*,S.K_Sub,S.Real_No_Kontrak,S.Real_Pelaksana,M.N_Prog,M.N_Keg,M.N_Sub,MA.N_Akun,T.N_Satuan,A.Pagu_Ini/RS.SUB_INI*100 Nilai_Bobot,".$qK." AKUN_REALK,GREATEST".$qF." AKUN_REALF FROM (SELECT * FROM dpa_akun WHERE".$tsql.") A LEFT JOIN (SELECT * FROM dpa_sub WHERE".$tsql.") S ON (S.KegID=A.KegID AND S.SubID=A.SubID) LEFT JOIN (SELECT * FROM rep_dpa_sub WHERE Tahun='".$tahun."' AND UserID='".user_id()."' AND Unix='".user_unix()."') RS ON (RS.OPDID=A.OPDID AND RS.ProgID=A.ProgID AND RS.KegID=A.KegID AND RS.SubID=A.SubID) LEFT JOIN (SELECT * FROM rep_dpa_keg WHERE Tahun='".$tahun."' AND UserID='".user_id()."' AND Unix='".user_unix()."') RK ON (RK.OPDID=A.OPDID AND RK.ProgID=A.ProgID AND RK.KegID=A.KegID) LEFT JOIN (SELECT * FROM rep_dpa_prog WHERE Tahun='".$tahun."' AND UserID='".user_id()."' AND Unix='".user_unix()."') RP ON (RP.OPDID=A.OPDID AND RP.ProgID=A.ProgID) LEFT JOIN (SELECT * FROM para90_akun WHERE Tahun='".$_SESSION['__WEB_APP']['AKUN_TAHUN']."') MA ON (MA.AkunID=A.AkunID) LEFT JOIN (SELECT * FROM para90_submap WHERE Tahun='".$thn_submap."') M ON (M.SubID=A.SubID) LEFT JOIN para_satuan T ON (T.SatuanID=A.SatuanID) ORDER BY RP.PROG_KODE,RK.KEG_KODE,S.K_Sub,A.K_Akun");
|
|
}
|
|
|
|
if ( $data == "print" ) {
|
|
$_head = '<div style="text-align:center;font-size:120%;"><b>LAPORAN REALISASI FISIK DAN KEUANGAN<br>TAHUN ANGGARAN '.$tahun.'</b></div><br>
|
|
<table style="width:100%">
|
|
<tr><td>Nama Perangkat Daerah</td><td class="titik-dua">:</td><td>'.$a_opd['N_OPD'].'</td>
|
|
<tr><td nowrap style="width:20%;">Pengguna Anggaran / Kuasa Pengguna Anggaran</td><td class="titik-dua">:</td><td>'.$a_opd['P_Nama'].'</td>
|
|
<tr><td>Bendahara Pengeluaran</td><td class="titik-dua">:</td><td>'.$a_opd['B_Nama'].'</td>
|
|
<tr><td>Tahun Anggaran</td><td class="titik-dua">:</td><td>'.$tahun.'</td>
|
|
<tr><td>Bulan</td><td class="titik-dua">:</td><td>'.bulan_array()[$bulan].'</td>
|
|
</table>
|
|
<table class="garis"><thead>
|
|
<tr><th rowspan="3">Kode</th><th rowspan="3">Uraian</th><th colspan="4">Target</th><th rowspan="3">Bobot (%)</th><th rowspan="3">Nilai Kontrak<br>Swakelola (Rp)</th><th rowspan="3">Pelaksana/Rekanan<br>No & Tanggal Kontrak</th><th colspan="6">Realisasi</th><th rowspan="3">Sisa Anggaran</th><th rowspan="3">Keterangan</th></tr>
|
|
<tr><th colspan="2">Fisik</th><th rowspan="2">Harga Satuan (Rp)</th><th rowspan="2">Keuangan<br>Dana (Rp)</th><th colspan="4">Fisik (%)</th><th colspan="2">Keuangan</th></tr>
|
|
<tr><th>Volume</th><th>Satuan</th><th>Rencana</th><th>Realisasi</th><th>Deviasi</th><th>Tertimbang</th><th>Rp</th><th>%</th></tr>
|
|
<tr><th>1</th><th>2</th><th>3</th><th>4</th><th>5</th><th>6</th><th>7</th><th>8</th><th>9</th><th>10</th><th>11</th><th>12</th><th>13</th><th>14</th><th>15</th><th>16</th><th>17</th></tr></thead><tbody>';
|
|
$_line = '<tr><td nowrap>%s</td><td>%s</td><td class="r">%s</td><td>%s</td><td class="r">%s</td><td class="r">%s</td><td class="r">%s</td><td class="r">%s</td><td>%s</td><td class="r">%s</td><td class="r">%s</td><td class="r">%s</td><td class="r">%s</td><td class="r">%s</td><td class="r">%s</td><td class="r">%s</td><td>%s</td></tr>';
|
|
$_bline = '<tr class="%s"><td class="b" nowrap>%s</td><td class="b">%s</td><td class="b r">%s</td><td class="b r">%s</td><td class="b r">%s</td><td class="b r">%s</td><td class="b r">%s</td><td class="b r">%s</td><td class="b">%s</td><td class="b r">%s</td><td class="b r">%s</td><td class="b r">%s</td><td class="b r">%s</td><td class="b r">%s</td><td class="b r">%s</td><td class="b r">%s</td><td class="b">%s</td></tr>';
|
|
$header = $_head;
|
|
$body = ""; $kprog = ""; $kkeg = ""; $ksub = ""; $tot_ini = 0; $tot_renc = 0; $tot_real = 0;
|
|
while ($db->Next()) {
|
|
if ($kprog <> $db->Row("ProgID")) {
|
|
$body .= sprintf($_bline, "prog", $db->Row("PROG_KODE"), $db->Row("N_Prog"), "", "", "", rupiah2($db->Row("PROG_INI")), "", rupiah2($db->Row("PROG_RENC")), "", "", "", "", "", rupiah2($db->Row("PROG_REAL")), rupiah2($db->Row("PROG_REAL")/$db->Row("PROG_RENC")*100), rupiah2($db->Row("PROG_RENC")-$db->Row("PROG_REAL")), " ");
|
|
$kprog = $db->Row("ProgID");
|
|
}
|
|
if ($kkeg <> $db->Row("KegID")) {
|
|
$body .= sprintf($_bline, "keg", $db->Row("KEG_KODE"), $db->Row("N_Keg"), "", "", "", rupiah2($db->Row("KEG_INI")), "", rupiah2($db->Row("KEG_RENC")), "", "", "", "", "", rupiah2($db->Row("KEG_REAL")), rupiah2($db->Row("KEG_REAL")/$db->Row("KEG_RENC")*100), rupiah2($db->Row("KEG_RENC")-$db->Row("KEG_REAL")), " ");
|
|
$kkeg = $db->Row("KegID");
|
|
}
|
|
if ($ksub <> $db->Row("SubID")) {
|
|
$psn_realk = ($db->RowFloat("SUB_RENC")==0) ? 0 : ($db->RowFloat("SUB_REAL")/$db->RowFloat("SUB_RENC")*100);
|
|
$body .= sprintf($_bline, "sub", $db->Row("K_Sub"), $db->Row("N_Sub"), "", "", "", rupiah2($db->Row("SUB_INI")), rupiah2(100), rupiah2($db->Row("SUB_RENC")), "", "", "", "", "", rupiah2($db->Row("SUB_REAL")), rupiah2($psn_realk), rupiah2($db->Row("SUB_RENC")-$db->Row("SUB_REAL")), " ");
|
|
$ksub = $db->Row("SubID");
|
|
}
|
|
$info_kontrak = $db->Row("Real_No_Kontrak"); $info_kontrak .= ($info_kontrak==""?"":"<br>").$db->Row("Real_Pelaksana");
|
|
$psn_realk = ($db->RowFloat("Renc_Keu")==0) ? 0 : ($db->RowFloat("AKUN_REALK")/$db->RowFloat("Renc_Keu")*100);
|
|
$body .= sprintf($_line, $db->Row("K_Akun"), $db->Row("N_Akun"), rupiah2($db->Row("Volume")), $db->Row("N_Satuan"), rupiah2($db->Row("Harga_Satuan")), rupiah2($db->Row("Pagu_Ini")), rupiah2($db->Row("Nilai_Bobot")), rupiah2($db->Row("Renc_Keu")), $info_kontrak, rupiah2($db->Row("Renc_Fisik")), rupiah2($db->Row("AKUN_REALF")), rupiah2($db->Row("Renc_Fisik")-$db->Row("AKUN_REALF")), rupiah2($db->Row("AKUN_REALF")*$db->Row("Nilai_Bobot")/100), rupiah2($db->Row("AKUN_REALK")), rupiah2($psn_realk), rupiah2($db->Row("Renc_Keu")-$db->Row("AKUN_REALK")), " ");
|
|
$tot_ini += $db->Row("Pagu_Ini");
|
|
$tot_renc += $db->Row("Renc_Keu");
|
|
$tot_real += $db->Row("AKUN_REALK");
|
|
}
|
|
$body .= sprintf('<tr class="opd"><td colspan="2" class="b c">Jumlah</td><td class="b r">%s</td><td class="b r">%s</td><td class="b r">%s</td><td class="b r">%s</td><td class="b r">%s</td><td class="b r">%s</td><td class="b">%s</td><td class="b r">%s</td><td class="b r">%s</td><td class="b r">%s</td><td class="b r">%s</td><td class="b r">%s</td><td class="b r">%s</td><td class="b r">%s</td><td class="b">%s</td></tr>', "", "", "", rupiah2($tot_ini), "", rupiah2($tot_renc), "", "", "", "", "", rupiah2($tot_real), rupiah2($tot_real/$tot_renc*100), rupiah2($tot_renc-$tot_real), " ");
|
|
|
|
echo $header . $body . "</tbody></table>";
|
|
|
|
} else if ( $data == "export" ) {
|
|
$wbook = new Spreadsheet_Excel_Writer();
|
|
$fjudul =& $wbook->addFormat();
|
|
$fjudul->setBold();
|
|
$fjudul->setAlign("center");
|
|
$fhead =& $wbook->addFormat();
|
|
$fhead->setBold();
|
|
$fhead->setAlign("center");
|
|
$fhead->setVAlign("vcenter");
|
|
$fhead->setBorder(1);
|
|
$fhead->setTextWrap();
|
|
$ftxt =& $wbook->addFormat();
|
|
$ftxt->setVAlign("top");
|
|
$ftxt->setBottom(1);
|
|
$ftxt->setTextWrap();
|
|
$ftxt->setNumFormat("@");
|
|
$fnum =& $wbook->addFormat();
|
|
$fnum->setNumFormat(4);
|
|
$fnum->setBottom(1);
|
|
$f_sjudul =& $wbook->addFormat(['NumFormat'=>"@"]);
|
|
|
|
$f_opd =& $wbook->addFormat(['FgColor'=>55,'NumFormat'=>"@"]);
|
|
$f_opd_num =& $wbook->addFormat(['FgColor'=>55,'NumFormat'=>4]);
|
|
$f_prog =& $wbook->addFormat(['FgColor'=>10,'NumFormat'=>"@"]);
|
|
$f_prog_num =& $wbook->addFormat(['FgColor'=>10,'NumFormat'=>4]);
|
|
$f_keg =& $wbook->addFormat(['FgColor'=>45,'NumFormat'=>"@"]);
|
|
$f_keg_num =& $wbook->addFormat(['FgColor'=>45,'NumFormat'=>4]);
|
|
$f_sub =& $wbook->addFormat(['FgColor'=>39,'NumFormat'=>"@"]);
|
|
$f_sub_num =& $wbook->addFormat(['FgColor'=>39,'NumFormat'=>4]);
|
|
|
|
$wsheet =& $wbook->addWorksheet("Real-Bulanan");
|
|
$wsheet->setColumn(0,0,18);
|
|
$wsheet->setColumn(1,1,60);
|
|
$wsheet->setColumn(2,7,16);
|
|
$wsheet->setColumn(8,8,12);
|
|
$wsheet->setColumn(9,15,16);
|
|
$wsheet->setColumn(16,16,8);
|
|
$wsheet->write(0,0,"LAPORAN REALISASI FISIK DAN KEUANGAN",$fjudul);
|
|
$wsheet->setMerge(0,0,0,16);
|
|
$wsheet->write(1,0,"TAHUN ANGGARAN ".$tahun,$fjudul);
|
|
$wsheet->setMerge(1,0,1,16);
|
|
|
|
$baris = 3;
|
|
$wsheet->write($baris,0,"Nama Perangkat Daerah",$f_sjudul);
|
|
$wsheet->write($baris,2,$a_opd['N_OPD'],$f_sjudul);
|
|
$wsheet->write(($baris+1),0,"Pengguna Anggaran / Kuasa Pengguna Anggaran",$f_sjudul);
|
|
$wsheet->write(($baris+1),2,$a_opd['P_Nama'],$f_sjudul);
|
|
$wsheet->write(($baris+2),0,"Bendahara Pengeluaran",$f_sjudul);
|
|
$wsheet->write(($baris+2),2,$a_opd['B_Nama'],$f_sjudul);
|
|
$wsheet->write(($baris+3),0,"Tahun Anggaran",$f_sjudul);
|
|
$wsheet->write(($baris+3),2,$tahun,$f_sjudul);
|
|
$wsheet->write(($baris+4),0,"Bulan",$f_sjudul);
|
|
$wsheet->write(($baris+4),2,bulan_array()[$bulan],$f_sjudul);
|
|
|
|
$baris += 5;
|
|
for ($x=$baris; $x<=($baris+3); $x++) {
|
|
for ($y=0; $y<=16; $y++) $wsheet->write($x,$y,"",$fhead);
|
|
}
|
|
$wsheet->write($baris,0,"Kode",$fhead);
|
|
$wsheet->setMerge($baris,0,($baris+2),0);
|
|
$wsheet->write($baris,1,"Uraian",$fhead);
|
|
$wsheet->setMerge($baris,1,($baris+2),1);
|
|
$wsheet->write($baris,2,"Target",$fhead);
|
|
$wsheet->setMerge($baris,2,$baris,5);
|
|
$wsheet->write($baris,6,"Bobot (%)",$fhead);
|
|
$wsheet->setMerge($baris,6,($baris+2),6);
|
|
$wsheet->write($baris,7,"Nilai Kontrak Swakelola (Rp)",$fhead);
|
|
$wsheet->setMerge($baris,7,($baris+2),7);
|
|
$wsheet->write($baris,8,"Pelaksana/Rekanan No & Tanggal Kontrak",$fhead);
|
|
$wsheet->setMerge($baris,8,($baris+2),8);
|
|
$wsheet->write($baris,9,"Realisasi",$fhead);
|
|
$wsheet->setMerge($baris,9,$baris,14);
|
|
$wsheet->write($baris,15,"Sisa Anggaran",$fhead);
|
|
$wsheet->setMerge($baris,15,($baris+2),15);
|
|
$wsheet->write($baris,16,"Keterangan",$fhead);
|
|
$wsheet->setMerge($baris,16,($baris+2),16);
|
|
$wsheet->write(($baris+1),2,"Fisik",$fhead);
|
|
$wsheet->setMerge(($baris+1),2,($baris+1),3);
|
|
$wsheet->write(($baris+1),4,"Harga Satuan (Rp)",$fhead);
|
|
$wsheet->setMerge(($baris+1),4,($baris+2),4);
|
|
$wsheet->write(($baris+1),4,"Keuangan Dana (Rp)",$fhead);
|
|
$wsheet->setMerge(($baris+1),5,($baris+2),5);
|
|
$wsheet->write(($baris+1),9,"Fisik (%)",$fhead);
|
|
$wsheet->setMerge(($baris+1),9,($baris+1),12);
|
|
$wsheet->write(($baris+1),13,"Keuangan",$fhead);
|
|
$wsheet->setMerge(($baris+1),13,($baris+1),14);
|
|
$wsheet->write(($baris+2),2,"Volume",$fhead);
|
|
$wsheet->write(($baris+2),3,"Satuan",$fhead);
|
|
$wsheet->write(($baris+2),9,"Rencana",$fhead);
|
|
$wsheet->write(($baris+2),10,"Realisasi",$fhead);
|
|
$wsheet->write(($baris+2),11,"Deviasi",$fhead);
|
|
$wsheet->write(($baris+2),12,"Tertimbang",$fhead);
|
|
for ($x=1; $x<=17; $x++) $wsheet->write(($baris+3),$x-1,$x,$fhead);
|
|
|
|
$baris += 4;
|
|
$kprog = ""; $kkeg = ""; $ksub = ""; $tot_ini = 0; $tot_renc = 0; $tot_real = 0;
|
|
while ($db->Next()) {
|
|
if ($kprog <> $db->Row("ProgID")) {
|
|
for ($x=0; $x<=16; $x++) $wsheet->write($baris, $x, "", $f_prog);
|
|
$wsheet->write($baris,0,$db->Row("PROG_KODE"), $f_prog);
|
|
$wsheet->write($baris,1,$db->Row("N_Prog"), $f_prog);
|
|
$wsheet->write($baris,5,$db->Row("PROG_INI"), $f_prog_num);
|
|
$wsheet->write($baris,7,$db->Row("PROG_RENC"), $f_prog_num);
|
|
$wsheet->write($baris,13,$db->Row("PROG_REAL"), $f_prog_num);
|
|
$wsheet->write($baris,14,$db->Row("PROG_REAL")/$db->Row("PROG_RENC")*100, $f_prog_num);
|
|
$wsheet->write($baris,15,$db->Row("PROG_RENC")-$db->Row("PROG_REAL"), $f_prog_num);
|
|
$baris++;
|
|
$kprog = $db->Row("ProgID");
|
|
}
|
|
if ($kkeg <> $db->Row("KegID")) {
|
|
for ($x=0; $x<=16; $x++) $wsheet->write($baris, $x, "", $f_keg);
|
|
$wsheet->write($baris,0,$db->Row("KEG_KODE"), $f_keg);
|
|
$wsheet->write($baris,1,$db->Row("N_Keg"), $f_keg);
|
|
$wsheet->write($baris,5,$db->Row("KEG_INI"), $f_keg_num);
|
|
$wsheet->write($baris,7,$db->Row("KEG_RENC"), $f_keg_num);
|
|
$wsheet->write($baris,13,$db->Row("KEG_REAL"), $f_keg_num);
|
|
$wsheet->write($baris,14,$db->Row("KEG_REAL")/$db->Row("KEG_RENC")*100, $f_keg_num);
|
|
$wsheet->write($baris,15,$db->Row("KEG_RENC")-$db->Row("KEG_REAL"), $f_keg_num);
|
|
$baris++;
|
|
$kkeg = $db->Row("KegID");
|
|
}
|
|
if ($ksub <> $db->Row("SubID")) {
|
|
$psn_realk = ($db->RowFloat("SUB_RENC")==0) ? 0 : ($db->RowFloat("SUB_REAL")/$db->RowFloat("SUB_RENC")*100);
|
|
for ($x=0; $x<=16; $x++) $wsheet->write($baris, $x, "", $f_sub);
|
|
$wsheet->write($baris,0,$db->Row("K_Sub"), $f_sub);
|
|
$wsheet->write($baris,1,$db->Row("N_Sub"), $f_sub);
|
|
$wsheet->write($baris,5,$db->Row("SUB_INI"), $f_sub_num);
|
|
$wsheet->write($baris,7,$db->Row("SUB_RENC"), $f_sub_num);
|
|
$wsheet->write($baris,13,$db->Row("SUB_REAL"), $f_sub_num);
|
|
$wsheet->write($baris,14,$psn_realk, $f_sub_num);
|
|
$wsheet->write($baris,15,$db->Row("SUB_RENC")-$db->Row("SUB_REAL"), $f_sub_num);
|
|
$baris++;
|
|
$ksub = $db->Row("SubID");
|
|
}
|
|
$info_kontrak = $db->Row("Real_No_Kontrak"); $info_kontrak .= ($info_kontrak==""?"":"<br>").$db->Row("Real_Pelaksana");
|
|
$psn_realk = ($db->RowFloat("Renc_Keu")==0) ? 0 : ($db->RowFloat("AKUN_REALK")/$db->RowFloat("Renc_Keu")*100);
|
|
|
|
for ($x=0; $x<=16; $x++) $wsheet->write($baris, $x, "", $ftxt);
|
|
$wsheet->write($baris,0,$db->Row("K_Akun"), $ftxt);
|
|
$wsheet->write($baris,1,$db->Row("N_Akun"), $ftxt);
|
|
$wsheet->write($baris,2,$db->Row("Volume"), $fnum);
|
|
$wsheet->write($baris,3,$db->Row("N_Satuan"), $ftxt);
|
|
$wsheet->write($baris,4,$db->Row("Harga_Satuan"), $fnum);
|
|
$wsheet->write($baris,5,$db->Row("Pagu_Ini"), $fnum);
|
|
$wsheet->write($baris,6,$db->Row("Nilai_Bobot"), $fnum);
|
|
$wsheet->write($baris,7,$db->Row("Renc_Keu"), $fnum);
|
|
$wsheet->write($baris,8,$info_kontrak, $ftxt);
|
|
$wsheet->write($baris,9,$db->Row("Renc_Fisik"), $fnum);
|
|
$wsheet->write($baris,10,$db->Row("AKUN_REALF"), $fnum);
|
|
$wsheet->write($baris,11,$db->Row("Renc_Fisik")-$db->Row("AKUN_REALF"), $fnum);
|
|
$wsheet->write($baris,12,$db->Row("AKUN_REALF")*$db->Row("Nilai_Bobot")/100, $fnum);
|
|
$wsheet->write($baris,13,$db->Row("AKUN_REALK"), $fnum);
|
|
$wsheet->write($baris,14,$psn_realk, $fnum);
|
|
$wsheet->write($baris,15,$db->Row("Renc_Keu")-$db->Row("AKUN_REALK"), $fnum);
|
|
$baris++;
|
|
$tot_ini += $db->Row("Pagu_Ini");
|
|
$tot_renc += $db->Row("Renc_Keu");
|
|
$tot_real += $db->Row("AKUN_REALK");
|
|
}
|
|
for ($x=0; $x<=16; $x++) $wsheet->write($baris, $x, "", $f_opd);
|
|
$wsheet->write($baris,0,"Jumlah", $f_opd);
|
|
$wsheet->setMerge($baris,0,$baris,1);
|
|
$wsheet->write($baris,5,$tot_ini, $f_opd_num);
|
|
$wsheet->write($baris,7,$tot_renc, $f_opd_num);
|
|
$wsheet->write($baris,13,$tot_real, $f_opd_num);
|
|
$wsheet->write($baris,14,$tot_real/$tot_renc*100, $f_opd_num);
|
|
$wsheet->write($baris,15,$tot_renc-$tot_real, $f_opd_num);
|
|
$baris++;
|
|
|
|
$wbook->send("Laporan Realisasi F & K ".bulan_array()[$bulan]."-".$tahun." - ".tgl_dmy(true).".xls");
|
|
$wbook->close();
|
|
|
|
} else if ( $data == "default") {
|
|
$q_opd = "";
|
|
if (in_array($_SESSION['__WEB_USER']['Level'],[1,3,4])) {
|
|
$id_opd = $_SESSION['__WEB_USER']['A_OPD'][0];
|
|
$q_opd = " AND OPDID IN (".$_SESSION['__WEB_USER']['AK_OPD'].")";
|
|
}
|
|
|
|
$form = new Form($db);
|
|
$tthn = $form->option_que("_", "Tahun", $tahun, true, false, "SELECT Tahun,CONCAT('Tahun ',Tahun) N_Tahun FROM dpa_prog GROUP BY Tahun ORDER BY Tahun", ["Tahun","N_Tahun"]);
|
|
$tbln = $form->option("_", "Bulan", $bulan, true, false, bulan_array());
|
|
$topd = $form->option_db("_", "OPDID", $id_opd, true, false, "vi_para90_opd", array("OPDID","Nama_OPD"), "Aktif=1".$q_opd." ORDER BY K_OPD");//, (user_level(3)?array(0,"SEMUA OPD"):null)
|
|
echo '
|
|
<div class="row mb-1">
|
|
<label class="col-sm-4 col-lg-3 col-form-label" for="OPDID">Nama Perangkat Daerah</label>
|
|
<div class="col-sm-8 col-lg-9">'.$topd.'</div>
|
|
</div>
|
|
<div class="row mb-1">
|
|
<label class="col-sm-4 col-lg-3 col-form-label" for="Bulan">Periode Data</label>
|
|
<div class="col-sm-5 col-lg-5">'.$tbln.'</div>
|
|
<div class="col-sm-3 col-lg-4">'.$tthn.'</div>
|
|
</div>';
|
|
}
|
|
|
|
?>
|