getParam("Tahun", $_SESSION['__WEB_APP']['REAL_THN']);
$ntw = $ap->getParam("TW", $_SESSION['__WEB_APP']['REAL_TW']);
$id_opd = $ap->getParam("OPDID", 0);
$id_upt = $ap->getParam("UPTID", 0);
if (in_array($_SESSION['__WEB_USER']['Level'],array(3,4))) $id_opd = $_SESSION['__WEB_USER']['A_OPD'][0];
if ($_SESSION['__WEB_USER']['Level']==4) $id_upt = $_SESSION['__WEB_USER']['A_UPT'][0];
$q_opd = (in_array($_SESSION['__WEB_USER']['Level'],array(3,4))) ? "OPDID='".$_SESSION['__WEB_USER']['A_OPD'][0]."'" : "TRUE";
$q_upt = ($_SESSION['__WEB_USER']['Level']==4) ? "AND UPTID='".$_SESSION['__WEB_USER']['A_UPT'][0]."'" : "OPDID='".$id_opd."'";
$form = new cFORM();
$form->option_db("Tahun Data", "Tahun", $ntahun, true, true, "para_periode", array("Tahun","Tahun"));
$form->option_db("Indikator Tujuan", "RD_TUJUANINDIID", $ap->getParam("RD_TUJUANINDIID", 0), true, true, "rpjmd_tujuan_indi", array("RD_TUJUANINDIID","Txt_Indi"), " TRUE ORDER BY No_Indi", array(0,"-- SEMUA INDIKATOR --"));
$form->option_db("Nama OPD", "OPDID", $id_opd, true, true, "vi_para_opd", array("OPDID","Nama_OPD"), $q_opd." AND Aktif=1 ORDER BY K_OPD", (user_level(1)?array(0,"-- SEMUA OPD --"):null), null, "RepoEvalTujuanJS.oc_Tipe();");
$form->option_db("Nama UPT", "UPTID", $id_upt, true, true, "vi_para_upt", array("UPTID","Nama_UPT"), $q_upt." AND Aktif=1 ORDER BY K_UPT", (user_level(3)?array(0,"-- SEMUA UPT --"):null));
$form->radio("Triwulan", "TW", $ntw, true, true, array(1=>"I",2=>"II",3=>"III",4=>"IV"));
$form->radio("Rutin / Penunjang", "Cetak_Rutin", $i_rutin, true, true, array(1=>"Ya",2=>"Tidak"));
$form->show(' _Tahun_ _RD_TUJUANINDIID_ _OPDID_ _UPTID_ _TW_ _Cetak_Rutin_ ');
}
/************************** RESPON HERE ***************************/
$db = DB();
$db2 = DB();
$data = $ap->getParam("data");
$tahun = $ap->getParam("Tahun", $_SESSION['__WEB_APP']['REAL_THN']);
$twulan = $ap->getParam("TW", $_SESSION['__WEB_APP']['REAL_TW']);
$id_opd = $ap->getParam("OPDID", 0);
$id_upt = $ap->getParam("UPTID", 0);
$id_indi = $ap->getParam("RD_TUJUANINDIID", 0);
if ( $data == "print" || $data == "export") {
$tsql = " WHERE Tahun='".$tahun."'";
if ($id_opd>0) $tsql .= " AND OPDID='".$id_opd."'";
if ($id_upt>0) $tsql .= " AND UPTID='".$id_upt."'";
if ($twulan == 1) {
$field_tw = "TW1";
} else if ($twulan == 2) {
$field_tw = "TW2";
} else if ($twulan == 3) {
$field_tw = "TW3";
} else if ($twulan == 4) {
$field_tw = "TW4";
}
// proses rekap database
Report_Empty("eval_prog");
Report_Empty("eval_prog_indi");
Report_Empty("eval_keg");
Report_Empty("eval_itarget");
// insert urusan
$db->Query("INSERT INTO rep_eval_prog SELECT Tahun,ProgID,SUM(B_Total),'".user_unix()."','".user_id()."' FROM dpa_detail".$tsql." GROUP BY ProgID");
// insert program indi
$db->Query("INSERT INTO rep_eval_prog_indi SELECT '".$tahun."',ProgID,RD_PROGRAMINDIID,0,'".user_unix()."','".user_id()."' FROM rpjmd_program_indi WHERE RD_PROGRAMINDIID IN (SELECT RD_PROGRAMINDIID FROM renja_detail_indi".$tsql.")");
$db->Query("DELETE FROM rep_eval_prog_indi WHERE ProgID NOT IN (SELECT ProgID FROM dpa_detail".$tsql.")");
// insert kegiatan
$db->Query("INSERT INTO rep_eval_keg SELECT D.Tahun,D.ProgID,IFNULL(MIN(I.RD_PROGRAMINDIID),0),D.RKegID,D.B_Total,IFNULL(D.B_Total/P.PAGU_PROG*100,0),IFNULL(D.RealF_TW2/D.RencF_TW2*100,0),0,'".user_unix()."','".user_id()."' FROM (SELECT * FROM dpa_detail".$tsql.") D LEFT JOIN (SELECT * FROM renja_detail_indi".$tsql.") I ON (D.RKegID=I.RKegID) LEFT JOIN rep_eval_prog P ON (D.Tahun=P.Tahun AND D.ProgID=P.ProgID AND P.UserID='".user_id()."' AND P.Unix='".user_unix()."') GROUP BY D.RKegID");
$db->Query("UPDATE rep_eval_keg SET KON_REAL=KON_KINERJA*KON_PAGU/100 WHERE UserID='".user_id()."' AND Unix='".user_unix()."'");
$db->Query("UPDATE rep_eval_prog_indi A, (SELECT ProgID,AVG(KON_KINERJA) KON_PROGRAM FROM rep_eval_keg WHERE UserID='".user_id()."' AND Unix='".user_unix()."' GROUP BY ProgID) B SET A.KON_PROGRAM=B.KON_PROGRAM WHERE A.ProgID=B.ProgID AND A.UserID='".user_id()."' AND A.Unix='".user_unix()."'");
// insert target indikator
$db->Query("INSERT INTO rep_eval_itarget SELECT A.Tahun,P.RD_TUJUANINDIID,AVG(A.KON_PROGRAM)/100,'".user_unix()."','".user_id()."' FROM rep_eval_prog_indi A LEFT JOIN para_program P ON (A.ProgID=P.ProgID) WHERE A.Tahun='".$tahun."' AND A.UserID='".user_id()."' AND A.Unix='".user_unix()."' GROUP BY P.RD_TUJUANINDIID");
$db->Query("SELECT P.RD_TUJUANINDIID,D.ProgID,D.RKegID,T.No_Misi,T.Txt_Misi,T.Kode_Tujuan,T.Txt_Tujuan,T.Kode_Indi_Tujuan,T.Txt_Indi,T.Indi_T2,RT.KON_TUJUANINDI*T.Indi_T2 PRO_INDI,P.N_Prog,RP.PAGU_PROG,D.K_Keg,D.N_Keg,D.RencF_TW2,D.B_Total,RK.KON_PAGU,D.RealF_TW2,RK.KON_KINERJA,RK.KON_REAL,U.N_UPT FROM (SELECT * FROM dpa_detail".$tsql.") D LEFT JOIN rep_eval_prog RP ON (D.Tahun=RP.Tahun AND D.ProgID=RP.ProgID AND RP.UserID='".user_id()."' AND RP.Unix='".user_unix()."') LEFT JOIN rep_eval_keg RK ON (D.Tahun=RK.Tahun AND D.ProgID=RK.ProgID AND D.RKegID=RK.RKegID AND RK.UserID='".user_id()."' AND RK.Unix='".user_unix()."') LEFT JOIN para_program P ON (D.ProgID=P.ProgID) LEFT JOIN rep_eval_itarget RT ON (RT.Tahun=D.Tahun AND RT.RD_TUJUANINDIID=P.RD_TUJUANINDIID AND RT.UserID='".user_id()."' AND RT.Unix='".user_unix()."') LEFT JOIN para_upt U ON (D.UPTID=U.UPTID) LEFT JOIN vi_rpjmd_tujuan_indi T ON (T.RD_TUJUANINDIID=P.RD_TUJUANINDIID) WHERE P.RD_TUJUANINDIID>0 ORDER BY T.Kode_Indi_Tujuan,D.ProgID,D.K_Keg");
}
if ( $data == "print" ) {
echo "dalam progress .. sementara baru excel !";
/*$_head = 'LAPORAN PELAKSANAAN RENCANA PROGRAM DAN ANGGARAN KEGIATAN PEMBANGUNAN DAERAH APBD
PROVINSI KALIMANTAN BARAT TRIWULAN '.romawi($twulan).' TAHUN ANGGARAN '.$tahun.'
| Kode | Urusan/Bidang Urusan Pemerintahan Daerah | Jumlah Anggaran (Rp) | Penyerapan Keuangan (%) | Indikator Keluaran (OUTPUT) |
| Target | % | Realisasi | % | S (%) | R (%) |
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
';
$_line = '| %s | %s | %s | %s | %s | %s | %s | %s | %s |
';
$_bline = '| %s | %s | %s | %s | %s | %s | %s | %s | %s |
';
$header = $_head;
$body = $urus = $fung = $opd = "";
while ($db->Next()) {
if ($urus <> $db->Row("KO_URUSAN")) {
$body .= sprintf($_bline, $db->Row("KO_URUSAN"), $db->Row("N_URUSAN"), uang2($db->Row("URUS_PAGU")), uang2($db->Row("URUS_TARGET")), uang2($db->Row("URUS_TARGET_PSN")), uang2($db->Row("URUS_REAL")), uang2($db->Row("URUS_REAL_PSN")), uang2($db->Row("URUS_INDI_S")), uang2($db->Row("URUS_INDI_R")));
$urus = $db->Row("KO_URUSAN");
}
if ($fung <> $db->Row("KO_FUNGSI")) {
$body .= sprintf($_bline, $db->Row("KO_FUNGSI"), $db->Row("N_FUNGSI"), uang($db->Row("FUNG_PAGU"),2), uang($db->Row("FUNG_TARGET"),2), uang($db->Row("FUNG_TARGET_PSN"),2), uang($db->Row("FUNG_REAL"),2), uang($db->Row("FUNG_REAL_PSN"),2), uang($db->Row("FUNG_INDI_S"),2), uang($db->Row("FUNG_INDI_R"),2));
$fung = $db->Row("KO_FUNGSI");
}
if ($opd <> $db->Row("KO_OPD")) {
$body .= sprintf($_bline, $db->Row("KO_OPD"), $db->Row("N_OPD"), uang($db->Row("OPD_PAGU"),2), uang($db->Row("OPD_TARGET"),2), uang($db->Row("OPD_TARGET_PSN"),2), uang($db->Row("OPD_REAL"),2), uang($db->Row("OPD_REAL_PSN"),2), uang($db->Row("OPD_INDI_S"),2), uang($db->Row("OPD_INDI_R"),2));
$opd = $db->Row("KO_OPD");
}
$body .= sprintf($_line, $db->Row("KO_UPT"), $db->Row("N_UPT"), uang($db->Row("UPT_PAGU"),2), uang($db->Row("UPT_TARGET"),2), uang($db->Row("UPT_TARGET_PSN"),2), uang($db->Row("UPT_REAL"),2), uang($db->Row("UPT_REAL_PSN"),2), uang($db->Row("UPT_INDI_S"),2), uang($db->Row("UPT_INDI_R"),2));
}
echo $header . $body . "
";*/
} else if ( $data == "export" ) {
\PhpOffice\PhpSpreadsheet\Cell\Cell::setValueBinder( new \PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder() );
$styleTxt = [
'numberFormat' => ['formatCode' => \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT]
];
$ssheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$ssheet->getProperties()
->setCreator($_SESSION['__WEB_APP']['APP_NAME']." v".$_SESSION['__WEB_APP']['APP_VER'])
->setLastModifiedBy($_SESSION['__WEB_APP']['APP_NAME']." v".$_SESSION['__WEB_APP']['APP_VER'])
->setTitle("Evaluasi ".$tahun)
->setCategory("Evaluasi ".$tahun)
->setSubject("Evaluasi")
->setDescription("Evaluasi ".$tahun)
->setCompany("BAPPERIDA Provinsi Kalimantan Barat");
$sheet = $ssheet->getActiveSheet();
$sheet->setTitle("EVALUASI");
// header
$styleHeader = [
'font' => ['bold' => true],
'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER]
];
$sheet->setCellValue("A1", "EVALUASI");
$sheet->mergeCells('A1:Y1');
$sheet->setCellValue("A2", "Tahun ".$tahun);
$sheet->mergeCells('A2:Y2');
$sheet->getStyle('A1:Y2')->applyFromArray($styleHeader);
// table header
$sheet->getColumnDimension('A')->setWidth(6);
$sheet->getColumnDimension('B')->setWidth(30);
$sheet->getColumnDimension('C')->setWidth(6);
$sheet->getColumnDimension('D')->setWidth(30);
$sheet->getColumnDimension('E')->setWidth(6);
$sheet->getColumnDimension('F')->setWidth(30);
$sheet->getColumnDimension('G')->setWidth(16);
$sheet->getColumnDimension('H')->setWidth(16);
$sheet->getColumnDimension('I')->setWidth(16);
$sheet->getColumnDimension('J')->setWidth(50);
$sheet->getColumnDimension('K')->setWidth(16);
$sheet->getColumnDimension('L')->setWidth(25);
$sheet->getColumnDimension('M')->setWidth(16);
$sheet->getColumnDimension('N')->setWidth(16);
$sheet->getColumnDimension('O')->setWidth(16);
$sheet->getColumnDimension('P')->setWidth(20);
$sheet->getColumnDimension('Q')->setWidth(20);
$sheet->getColumnDimension('R')->setWidth(20);
$sheet->getColumnDimension('S')->setWidth(20);
$sheet->getColumnDimension('T')->setWidth(16);
$sheet->getColumnDimension('U')->setWidth(16);
$sheet->getColumnDimension('V')->setWidth(16);
$sheet->getColumnDimension('W')->setWidth(16);
$sheet->getColumnDimension('X')->setWidth(16);
$sheet->getColumnDimension('Y')->setWidth(25);
$styleTableH1 = [
'font' => ['bold' => true],
'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, 'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER, 'wrapText' => true],
'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['rgb'=>"2187AC"]],
'borders' => ['allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN, 'color' => ['rgb'=>"000000"]]]
];
$sheet->setCellValue("A4", "Misi RPJMD");
$sheet->mergeCells('A4:B4');
$sheet->setCellValue("C4", "Tujuan RPJMD");
$sheet->mergeCells('C4:D4');
$sheet->setCellValue("E4", "Indikator Tujuan RPJMD");
$sheet->mergeCells('E4:F4');
$sheet->setCellValue("G4", "Target ".$tahun);
$sheet->setCellValue("H4", "Realisasi Tahun n-1 (".($tahun-1).")");
$sheet->setCellValue("I4", "Proyeksi Berdasar Kontribusi Program ".$tahun);
$sheet->setCellValue("J4", "Program");
$sheet->setCellValue("K4", "Anggaran");
$sheet->setCellValue("L4", "Indikator Program");
$sheet->setCellValue("M4", "Target ".$tahun);
$sheet->setCellValue("N4", "Proyeksi Realisasi ".$tahun);
$sheet->setCellValue("O4", "Kontribusi Program");
$sheet->setCellValue("P4", "Kegiatan");
$sheet->setCellValue("Q4", "Indikator Kegiatan");
$sheet->setCellValue("R4", "Target Kinerja");
$sheet->setCellValue("S4", "Satuan");
$sheet->setCellValue("T4", "Anggaran");
$sheet->setCellValue("U4", "Kontribusi Pagu Program");
$sheet->setCellValue("V4", "Realisasi Kinerja ".$tahun);
$sheet->setCellValue("W4", "Kontribusi Kegiatan Per Kinerja");
$sheet->setCellValue("X4", "Kontribusi Realisasi Anggaran");
$sheet->setCellValue("Y4", "OPD Penanggung Jawab");
$sheet->getStyle('A4:Y4')->applyFromArray($styleTableH1);
// jika ada nomor dibawah header disini !!
$baris = 5; $k_misi = $k_tujuan = $k_tujuanindi = $k_prog = "";
while ($db->Next()) {
if ($k_misi <> $db->Row("No_Misi")) {
$sheet->getCell("A".$baris)->setValueExplicit($db->Row("No_Misi"), \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
$sheet->setCellValue("B".$baris, $db->Row("Txt_Misi"));
$k_misi = $db->Row("No_Misi");
}
if ($k_tujuan <> $db->Row("Kode_Tujuan")) {
$sheet->getCell("C".$baris)->setValueExplicit($db->Row("Kode_Tujuan"), \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
$sheet->setCellValue("D".$baris, $db->Row("Txt_Tujuan"));
$k_tujuan = $db->Row("Kode_Tujuan");
}
if ($k_tujuanindi <> $db->Row("Kode_Indi_Tujuan")) {
$sheet->getCell("E".$baris)->setValueExplicit($db->Row("Kode_Indi_Tujuan"), \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
$sheet->setCellValue("F".$baris, $db->Row("Txt_Indi"));
$sheet->setCellValue("G".$baris, $db->Row("Indi_T2"));
$sheet->setCellValue("I".$baris, $db->RowFloat("PRO_INDI"));//angka
$k_tujuanindi = $db->Row("Kode_Indi_Tujuan");
}
if ($k_prog <> $db->Row("ProgID")) {
$sheet->setCellValue("J".$baris, $db->Row("N_Prog"));
$sheet->setCellValue("K".$baris, $db->RowFloat("PAGU_PROG"));//angka
$k_prog = $db->Row("ProgID");
$db2->Query("SELECT PI.Txt_Indi,PI.Indi_T2,IFNULL(PI.Indi_T2/100*I.KON_PROGRAM,0) PRO_REAL,I.KON_PROGRAM FROM rep_eval_prog_indi I LEFT JOIN rpjmd_program_indi PI ON (I.RD_PROGRAMINDIID=PI.RD_PROGRAMINDIID AND PI.RPJMDID=2) WHERE I.Tahun='".$tahun."' AND I.ProgID='".$db->Row("ProgID")."' AND I.UserID='".user_id()."' AND I.Unix='".user_unix()."' ORDER BY PI.No_Indi");
}
if ($db2->Next()) {
$sheet->setCellValue("L".$baris, $db2->Row("Txt_Indi"));
$sheet->setCellValue("M".$baris, $db2->Row("Indi_T2"));//angka
$sheet->setCellValue("N".$baris, $db2->RowFloat("PRO_REAL"));//angka
$sheet->setCellValue("O".$baris, $db2->RowFloat("KON_PROGRAM"));//angka
}
$sheet->setCellValue("P".$baris, "(".$db->Row("K_Keg").") ". $db->Row("N_Keg"));
//$sheet->setCellValue("Q".$baris, $db->Row("N_Prog"));
$sheet->setCellValue("R".$baris, $db->RowFloat("RencF_".$field_tw));
// S satuan
$sheet->setCellValue("T".$baris, $db->RowFloat("B_Total"));
$sheet->setCellValue("U".$baris, $db->RowFloat("KON_PAGU"));
$sheet->setCellValue("V".$baris, $db->RowFloat("RealF_".$field_tw));
$sheet->setCellValue("W".$baris, $db->RowFloat("KON_KINERJA"));
$sheet->setCellValue("X".$baris, $db->RowFloat("KON_REAL"));
$sheet->setCellValue("Y".$baris, $db->Row("N_UPT"));
$baris++;
}
$baris--;
$sheet->getStyle('A5:Y'.$baris)->applyFromArray(['borders' => ['allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN, 'color' => ['rgb'=>"000000"]]]]);
$sheet->getSheetView()->setZoomScale(90);
$sheet->getHeaderFooter()->setOddFooter('&LeMONEv - Kabupaten Mempawah &CEvaluasi &R&I&D &T // Halaman : &P dari &N');
$sheet->getHeaderFooter()->setEvenFooter('&LeMONEv - Kabupaten Mempawah &CEvaluasi &R&I&D &T // Halaman : &P dari &N');
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Evaluasi '.$tahun.'.xlsx"');
header('Cache-Control: max-age=0');
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($ssheet, 'Xlsx');
$writer->save('php://output');
$ssheet->disconnectWorksheets();
unset($ssheet);
unset($writer);
} else if ( $data == "default") {
report_show();
}
?>