array(
'bold' => true,
'size' => 12,
'name' => 'Arial'
)
);
private $styleArray = [
'borders' => [
'allBorders' => [
'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
'color' => ['argb' => 'FF000000'],
],
],
'font' => array(
'bold' => true,
'size' => 9,
'name' => 'Arial'
),
'alignment' => array(
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
)
];
private $styleArrayNormal = [
'borders' => [
'allBorders' => [
'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
'color' => ['argb' => 'FF000000'],
],
],
'font' => array(
'size' => 9,
'name' => 'Arial'
),
];
private function generateExcel($sheet, $title, $data, $header, $sheetName)
{
$sheet->setTitle($sheetName);
$sheet->setCellValue("A1", $title);
$sheet->getStyle("A1")->applyFromArray($this->styleArrayHeader);
#region header
$col = 1;
$row = 3;
foreach ($header as $headerTitle) {
$sheet->setCellValueByColumnAndRow($col++, $row, strtoupper($headerTitle));
}
$headerRange = "A$row:" . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col - 1) . ($row);
$sheet->getStyle($headerRange)->applyFromArray($this->styleArray);
$sheet->getStyle($headerRange)->getAlignment()->setWrapText(true);
#endregion header
#region data
$no = 1;
$row++;
$start = $row;
$header = array_slice($header, 3);
foreach ($data as $d) {
$col = 1;
$sheet->setCellValueByColumnAndRow($col++, $row, $no++);
$sheet->setCellValueByColumnAndRow($col++, $row, strtoupper($d['wilayah_provinsi']));
$sheet->setCellValueByColumnAndRow($col++, $row, strtoupper($d['pemda']));
foreach ($header as $headerTitle) {
if (isset($d[$headerTitle])) {
$sheet->setCellValueByColumnAndRow($col++, $row, $d[$headerTitle] / 100);
} else {
$sheet->setCellValueByColumnAndRow($col++, $row, 0);
}
}
$row++;
}
#endregion data
$range = "A$start:C" . ($row - 1);
$sheet->getStyle($range)->applyFromArray($this->styleArrayNormal);
$range = "D$start:" . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col - 1) . ($row - 1);
$sheet->getStyle($range)->applyFromArray($this->styleArrayNormal);
// $sheet->getStyle($range)->getNumberFormat()->setFormatCode('0%'); //set if the result type is percentage
$sheet->getColumnDimension('B')->setAutoSize(true);
$sheet->getColumnDimension('C')->setAutoSize(true);
foreach (range('D', \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col - 1)) as $columnID) {
$sheet->getColumnDimension($columnID)
->setWidth(20);
}
// return $sheet;
}
private function generateExcelSubIndikator($sheet, $title, $data, $sheetName)
{
$sheet->setTitle($sheetName);
$sheet->setCellValue("A1", $title);
$sheet->getStyle("A1")->applyFromArray($this->styleArrayHeader);
#region header
$col = 1;
$row = 3;
$header = [
"Provinsi",
"Instansi",
"Area Intervensi",
"Bobot Area Intervensi",
"Indikator",
"Bobot Indikator",
"Sub Indikator",
"Bobot Sub Indikator",
"Nilai",
"Nilai Verifikasi"
];
foreach ($header as $headerTitle) {
$sheet->setCellValueByColumnAndRow($col++, $row, strtoupper($headerTitle));
}
$headerRange = "A$row:" . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col - 1) . ($row);
$sheet->getStyle($headerRange)->applyFromArray($this->styleArray);
$sheet->getStyle($headerRange)->getAlignment()->setWrapText(true);
// #endregion header
#region data
$no = 1;
$row++;
$start = $row;
foreach ($data as $d) {
$col = 1;
$sheet->setCellValueByColumnAndRow($col++, $row, $d["provinsi_name"]);
$sheet->setCellValueByColumnAndRow($col++, $row, $d["instansi"]);
$sheet->setCellValueByColumnAndRow($col++, $row, $d["area_intervensi"]);
$sheet->setCellValueByColumnAndRow($col++, $row, $d["bobot_area_intervensi"]);
$sheet->setCellValueByColumnAndRow($col++, $row, $d["indikator"]);
$sheet->setCellValueByColumnAndRow($col++, $row, $d["bobot_indikator"]);
$sheet->setCellValueByColumnAndRow($col++, $row, $d["sub_indikator"]);
$sheet->setCellValueByColumnAndRow($col++, $row, $d["bobot_subindikator"]);
$sheet->setCellValueByColumnAndRow($col++, $row, $d["nilai"]);
$sheet->setCellValueByColumnAndRow($col++, $row, $d["nilai_verifikasi"]);
$row++;
}
#endregion data
$range = "A$start:C" . ($row - 1);
$sheet->getStyle($range)->applyFromArray($this->styleArrayNormal);
$range = "D$start:" . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col - 1) . ($row - 1);
$sheet->getStyle($range)->applyFromArray($this->styleArrayNormal);
$sheet->getColumnDimension('B')->setAutoSize(true);
$sheet->getColumnDimension('C')->setAutoSize(true);
$sheet->getColumnDimension('E')->setWidth(20);
$sheet->getColumnDimension('G')->setWidth(20);
// foreach (range('D', \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col - 1)) as $columnID) {
// $sheet->getColumnDimension($columnID)
// ->setWidth(20);
// }
// return $sheet;
}
private function generateExcelMcpPerDirektorat($sheet, $title, $data, $header, $sheetName)
{
// generate Excel sheet for MCP per Direktorat per tanggal
if (strlen($sheetName) > 31) {
$sheetName = substr($sheetName, 0, 30);
}
$sheet->setTitle($sheetName);
$sheet->setCellValue("A1", $title);
$sheet->getStyle("A1")->applyFromArray($this->styleArrayHeader);
#region header
$col = 1;
$row = 3;
foreach ($header as $headerTitle) {
$sheet->setCellValueByColumnAndRow($col++, $row, strtoupper($headerTitle));
}
$headerRange = "A$row:" . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col - 1) . ($row);
$sheet->getStyle($headerRange)->applyFromArray($this->styleArray);
$sheet->getStyle($headerRange)->getAlignment()->setWrapText(true);
$sheet->getColumnDimension('C')->setAutoSize(true);
$headerCol = $col - 1;
#endregion header
#region data
$row++;
foreach ($data as $dataRow) {
$col = 1;
foreach ($dataRow as $dataCol) {
$sheet->setCellValueByColumnAndRow($col++, $row, $dataCol);
}
$row++;
}
$range = "A4:" . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($headerCol) . ($row - 1);
$sheet->getStyle($range)->applyFromArray($this->styleArrayNormal);
#endregion data
}
public function index()
{
$req = $this->request;
$periode = $req->get('id_periode');
$id_instansi = $req->get('id_instansi');
if (empty($periode)) {
$periode = -1;
}
if (empty($id_instansi)) {
$id_instansi = -1;
}
$aiDanaDesa = $this->rawQuery(
'SELECT
ai.id_transfer_bobot AS id_transfer,
ai.presentase_bobot AS bobot
FROM
jaga.korsupgah_template t
JOIN jaga.korsupgah_template_sub_indikator tsi ON tsi.id_template = t.id
JOIN jaga.korsupgah_sub_indikator si ON si.id = tsi.id_sub_indikator
JOIN jaga.korsupgah_indikator i ON i.id = si.id_indikator
JOIN jaga.korsupgah_area_intervensi ai ON ai.id = i.id_area_intervensi
JOIN jaga.korsupgah_survey ks on ks.id_template = t.id
WHERE ks.id_periode = :periode AND tsi.deleted_at IS NULL;',
[
'periode' => $periode
]
);
$bobotDanaDesa = [];
foreach ($aiDanaDesa as $item) {
$bobotDanaDesa[$item['id_transfer']] = $item['bobot'];
}
$queryInstansi = "select
mp.provinsi_name as wilayah_provinsi,
i.nama as pemda,
i.id_tipe as tipe_instansi,
kai.nama as area_intervensi,
kai.id as id_area_intervensi,
ki.nama as indikator,
tplt.id_dana_desa,
i.flag_dana_desa,
kai.presentase_bobot,
ka.nilai_verifikasi as nilai,
ki.presentase_bobot as presentase_bobot_indikator,
ksi.presentase_bobot as presentase_bobot_subindikator,
kai.presentase_bobot * (COALESCE ( ka.nilai_verifikasi, 0 )) / 100 AS nilai_verifikasi,
ki.presentase_bobot * (COALESCE ( ka.nilai_verifikasi, 0 )) / 100 AS nilai_verifikasi_indikator
FROM
jaga.korsupgah_survey ks
JOIN jaga.instansi i ON ks.id_instansi = i.id
JOIN jaga.master_provinsi mp ON mp.provinsi_code :: INT = i.id_provinsi
JOIN jaga.korsupgah_template_sub_indikator ktsi ON ktsi.id_template = ks.id_template
JOIN jaga.korsupgah_sub_indikator ksi ON ktsi.id_sub_indikator = ksi.
ID
LEFT JOIN jaga.korsupgah_answer ka ON ( ka.id_question = ksi.ID AND ka.id_survey = ks.ID )
JOIN jaga.korsupgah_indikator ki ON ki.ID = ksi.id_indikator
JOIN jaga.korsupgah_area_intervensi kai ON kai.id = ki.id_area_intervensi
JOIN jaga.korsupgah_template tplt ON tplt.id = ks.id_template
where (i.id = $id_instansi or $id_instansi = -1) and
(ks.id_periode = $periode or $periode = -1)
AND ktsi.deleted_at IS NULL
order by i.nama, mp.provinsi_name, kai.nama";
$dataInstansi = $this->rawQuery($queryInstansi, []);
$query = "select * from jaga.instansi i JOIN jaga.master_provinsi mp ON mp.provinsi_code :: INT = i.id_provinsi order by nama";
$data = $this->rawQuery($query, []);
$res = array();
$index = 0;
$result = array();
foreach ($data as $d) {
$result[$d['nama']] = array_filter($dataInstansi, function ($val) use ($d) {
return $val['pemda'] == $d['nama'];
}, ARRAY_FILTER_USE_BOTH);
$res[$index] =
[
'wilayah_provinsi' => $d['provinsi_name'],
'pemda' => $d['nama'],
];
foreach ($result[$d['nama']] as $r) {
//$res[$index][$r['area_intervensi']]=$r['nilai_verifikasi'];
$bobot = $r['presentase_bobot'];
if (array_key_exists($r['id_area_intervensi'], $bobotDanaDesa)) {
$bobot += $bobotDanaDesa[$r['id_area_intervensi']];
}
$res[$index][$r['area_intervensi']] = $r['nilai'] * $bobot / 100;
}
$index++;
}
$headerArea = ["NO", "WILAYAH PROVINSI", "PEMERINTAH DAERAH"];
$query = "select * from jaga.korsupgah_area_intervensi kai order by kai.nama";
$dataArea = $this->rawQuery($query, []);
foreach ($dataArea as $area) {
$headerArea[] = $area['nama'];
}
$sheetIndex = 0;
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->createSheet($sheetIndex++);
$this->generateExcel($sheet, "PROGRESS CAPAIAN MCP SELURUH PEMDA DI INDONESIA", $res, $headerArea, "TOTAL");
// by area
foreach ($dataArea as $area) {
$header = ["NO", "WILAYAH PROVINSI", "PEMERINTAH DAERAH"];
$query = "select * from jaga.korsupgah_indikator ki where id_area_intervensi = " . $area['id'] . " order by ki.id";
$dataIndikator = $this->rawQuery($query, []);
foreach ($dataIndikator as $indikator) {
$header[] = $indikator['nama'];
}
$acronym = "";
$words = explode(" ", $area['nama']);
if (count($words) == 1) {
$acronym .= strtoupper($words[0]);
} else {
foreach ($words as $w) {
$acronym .= strtoupper($w[0]);
}
}
$res = array();
$index = 0;
foreach ($data as $d) {
$res[$index] =
[
'wilayah_provinsi' => $d['provinsi_name'],
'pemda' => $d['nama'],
];
foreach ($result[$d['nama']] as $r) {
//$res[$index][$r['indikator']]=$r['nilai_verifikasi_indikator'];
//$res[$index][$r['indikator']]=$r['nilai'] * $r['presentase_bobot_indikator'] / 100;
$bobot = $r['presentase_bobot_indikator'];
if (array_key_exists($r['id_area_intervensi'], $bobotDanaDesa)) {
$bobot += $bobotDanaDesa[$r['id_area_intervensi']];
}
$res[$index][$r['indikator']] = $r['nilai'] * $bobot / 100;
}
$index++;
}
$sheet = $spreadsheet->createSheet($sheetIndex++);
$sheetName = $acronym;
$this->generateExcel($sheet, "PROGRESS CAPAIAN MCP SELURUH PEMDA DI INDONESIA", $res, $header, $sheetName);
}
// by provinsi
$res = array();
$index = 0;
foreach ($data as $d) {
if ($d['id_tipe'] == 1) {
$result = array_filter($dataInstansi, function ($val) use ($d) {
return $val['pemda'] == $d['nama'] && $val['tipe_instansi'] == 1;
}, ARRAY_FILTER_USE_BOTH);
$res[$index] =
[
'wilayah_provinsi' => $d['provinsi_name'],
'pemda' => $d['nama'],
];
foreach ($result as $r) {
//$res[$index][$r['area_intervensi']]=$r['nilai_verifikasi'];
//$res[$index][$r['area_intervensi']]=$r['nilai'] * $r['presentase_bobot'] / 100;
$bobot = $r['presentase_bobot'];
if (array_key_exists($r['id_area_intervensi'], $bobotDanaDesa)) {
$bobot += $bobotDanaDesa[$r['id_area_intervensi']];
}
$res[$index][$r['area_intervensi']] = $r['nilai'] * $bobot / 100;
}
$index++;
}
}
$sheet = $spreadsheet->createSheet($sheetIndex++);
$sheetName = "PROVINSI";
$this->generateExcel($sheet, "PROGRESS CAPAIAN MCP SELURUH PEMDA DI INDONESIA", $res, $headerArea, $sheetName);
// by kabkot
$res = array();
$index = 0;
foreach ($data as $d) {
if ($d['id_tipe'] == 2) {
$result = array_filter($dataInstansi, function ($val) use ($d) {
return $val['pemda'] == $d['nama'] && $val['tipe_instansi'] == 2;
}, ARRAY_FILTER_USE_BOTH);
$res[$index] =
[
'wilayah_provinsi' => $d['provinsi_name'],
'pemda' => $d['nama'],
];
foreach ($result as $r) {
//$res[$index][$r['area_intervensi']]=$r['nilai_verifikasi'];
//$res[$index][$r['area_intervensi']]=$r['nilai'] * $r['presentase_bobot'] / 100;
$bobot = $r['presentase_bobot'];
if (array_key_exists($r['id_area_intervensi'], $bobotDanaDesa)) {
$bobot += $bobotDanaDesa[$r['id_area_intervensi']];
}
$res[$index][$r['area_intervensi']] = $r['nilai'] * $bobot / 100;
}
$index++;
}
}
$sheet = $spreadsheet->createSheet($sheetIndex++);
$sheetName = "KABUPATEN_KOTA";
$this->generateExcel($sheet, "PROGRESS CAPAIAN MCP SELURUH PEMDA DI INDONESIA", $res, $headerArea, $sheetName);
// subindikator
$query = "SELECT
*
FROM
(
SELECT
mp.provinsi_name,
i.nama AS instansi,
kai.nama as area_intervensi,
kai.presentase_bobot as bobot_area_intervensi,
ki.nama AS indikator,
ki.presentase_bobot AS bobot_indikator,
ktsi.id_sub_indikator,
ksi.nama AS sub_indikator,
ksi.presentase_bobot AS bobot_subindikator,
COALESCE ( ka.nilai, 0 ) AS nilai,
COALESCE ( ka.nilai_verifikasi, 0 ) AS nilai_verifikasi,
ka.created_at AS tgl_lapor,
RANK ( ) OVER ( PARTITION BY i.nama, ka.id_question ORDER BY ka.created_at DESC ) AS rk
FROM
jaga.korsupgah_survey ks
JOIN jaga.instansi i ON ks.id_instansi = i.id
JOIN jaga.master_provinsi mp ON mp.provinsi_code :: INT = i.id_provinsi
JOIN jaga.korsupgah_template_sub_indikator ktsi ON ktsi.id_template = ks.id_template
JOIN jaga.korsupgah_sub_indikator ksi ON ktsi.id_sub_indikator = ksi.
ID
LEFT JOIN jaga.korsupgah_answer ka ON ( ka.id_question = ksi.ID AND ka.id_survey = ks.ID )
JOIN jaga.korsupgah_indikator ki ON ki.ID = ksi.id_indikator
JOIN jaga.korsupgah_area_intervensi kai ON kai.id = ki.id_area_intervensi
WHERE
ks.id_template = 1 and (i.id = $id_instansi or $id_instansi = -1) and
(ks.id_periode = $periode or $periode = -1)
AND ktsi.deleted_at IS NULL
ORDER BY
mp.provinsi_name,
i.nama,
ktsi.id_sub_indikator ASC
) AS t_report
WHERE rk = 1;";
// echo '';
// print_r($query);
// echo '';
// die;
$data = $this->rawQuery($query, []);
$sheet = $spreadsheet->createSheet($sheetIndex++);
$sheetName = "SUBINDIKATOR";
$this->generateExcelSubIndikator($sheet, "PROGRESS CAPAIAN MCP SELURUH PEMDA DI INDONESIA", $data, $sheetName);
// generate excel
$spreadsheet->setActiveSheetIndex(0);
$writer = new Xlsx($spreadsheet);
$now = date("YmdHis");
$filename = $now . "-Progress_MCP.xlsx";
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="' . $filename . '"');
return $writer->save('php://output');
}
public function total()
{
$req = $this->request;
$periode = $req->get('id_periode');
$id_instansi = $req->get('id_instansi');
if (empty($periode)) {
$periode = -1;
}
if (empty($id_instansi)) {
$id_instansi = -1;
}
$queryInstansi = "select
mp.provinsi_name as wilayah_provinsi,
i.nama as pemda,
kai.nama as area_intervensi,
kai.presentase_bobot * (COALESCE ( ka.nilai_verifikasi, 0 )) / 100 AS nilai_verifikasi
FROM
jaga.korsupgah_survey ks
JOIN jaga.instansi i ON ks.id_instansi = i.id
JOIN jaga.master_provinsi mp ON mp.provinsi_code :: INT = i.id_provinsi
JOIN jaga.korsupgah_template_sub_indikator ktsi ON ktsi.id_template = ks.id_template
JOIN jaga.korsupgah_sub_indikator ksi ON ktsi.id_sub_indikator = ksi.
ID
LEFT JOIN jaga.korsupgah_answer ka ON ( ka.id_question = ksi.ID AND ka.id_survey = ks.ID )
JOIN jaga.korsupgah_indikator ki ON ki.ID = ksi.id_indikator
JOIN jaga.korsupgah_area_intervensi kai ON kai.id = ki.id_area_intervensi
where (i.id = $id_instansi or $id_instansi = -1) and
(ks.id_periode = $periode or $periode = -1)
order by i.nama, mp.provinsi_name, kai.nama";
$dataInstansi = $this->rawQuery($queryInstansi, []);
$query = "select * from jaga.instansi i JOIN jaga.master_provinsi mp ON mp.provinsi_code :: INT = i.id_provinsi";
$data = $this->rawQuery($query, []);
$res = array();
$index = 0;
foreach ($data as $d) {
$result = array_filter($dataInstansi, function ($val) use ($d) {
return $val['pemda'] == $d['nama'];
}, ARRAY_FILTER_USE_BOTH);
$res[$index] =
[
'wilayah_provinsi' => $d['provinsi_name'],
'pemda' => $d['nama'],
];
foreach ($result as $r) {
$res[$index][$r['area_intervensi']] = $r['nilai_verifikasi'];
}
$index++;
}
$header = ["NO", "WILAYAH PROVINSI", "PEMERINTAH DAERAH"];
$query = "select * from jaga.korsupgah_area_intervensi kai order by kai.nama";
$dataArea = $this->rawQuery($query, []);
foreach ($dataArea as $area) {
$header[] = $area['nama'];
}
$spreadsheet = $this->generateExcel("PROGRESS CAPAIAN MCP SELURUH PEMDA DI INDONESIA", $res, $header, "TOTAL");
//return new CollectionPaginatedResponse($res);
$writer = new Xlsx($spreadsheet);
$filename = "Progress_MCP_By_Total.xlsx";
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="' . $filename . '"');
return $writer->save('php://output');
}
private function area_intervensi()
{
$req = $this->request;
$periode = $req->get('id_periode');
$id_instansi = $req->get('id_instansi');
$id_area_intervensi = $req->get('id_area_intervensi');
if (empty($periode)) {
$periode = -1;
}
if (empty($id_instansi)) {
$id_instansi = -1;
}
$queryInstansi = "select
mp.provinsi_name as wilayah_provinsi,
i.nama as pemda,
ki.nama as indikator,
kai.presentase_bobot * (COALESCE ( ka.nilai_verifikasi, 0 )) / 100 AS nilai_verifikasi
FROM
jaga.korsupgah_survey ks
JOIN jaga.instansi i ON ks.id_instansi = i.id
JOIN jaga.master_provinsi mp ON mp.provinsi_code :: INT = i.id_provinsi
JOIN jaga.korsupgah_template_sub_indikator ktsi ON ktsi.id_template = ks.id_template
JOIN jaga.korsupgah_sub_indikator ksi ON ktsi.id_sub_indikator = ksi.
ID
LEFT JOIN jaga.korsupgah_answer ka ON ( ka.id_question = ksi.ID AND ka.id_survey = ks.ID )
JOIN jaga.korsupgah_indikator ki ON ki.ID = ksi.id_indikator
JOIN jaga.korsupgah_area_intervensi kai ON kai.id = ki.id_area_intervensi
where kai.id = $id_area_intervensi
and (i.id = $id_instansi or $id_instansi = -1) and
(ks.id_periode = $periode or $periode = -1)
order by i.nama, mp.provinsi_name, kai.nama";
$dataInstansi = $this->rawQuery($queryInstansi, []);
$query = "select * from jaga.instansi i JOIN jaga.master_provinsi mp ON mp.provinsi_code :: INT = i.id_provinsi";
$data = $this->rawQuery($query, []);
$res = array();
$index = 0;
foreach ($data as $d) {
$result = array_filter($dataInstansi, function ($val) use ($d) {
return $val['pemda'] == $d['nama'];
}, ARRAY_FILTER_USE_BOTH);
$res[$index] =
[
'wilayah_provinsi' => $d['provinsi_name'],
'pemda' => $d['nama'],
];
foreach ($result as $r) {
$res[$index][$r['indikator']] = $r['nilai_verifikasi'];
}
$index++;
}
$kai = KorsupgahAreaIntervensi::find(
[
"id = :id:",
"bind" => [
"id" => $id_area_intervensi,
]
]
);
if ($kai->count() != 1) {
throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, 'Area Intervensi tidak ditemukan');
}
$query = "select * from jaga.korsupgah_indikator ki where id_area_intervensi = $id_area_intervensi order by ki.nama";
$dataIndikator = $this->rawQuery($query, []);
$header = ["NO", "WILAYAH PROVINSI", "PEMERINTAH DAERAH"];
foreach ($dataIndikator as $indikator) {
$header[] = $indikator['nama'];
}
$spreadsheet = $this->generateExcel($kai[0]->nama, $res, $header, "AREA INTERVENSI");
//return new CollectionPaginatedResponse($res);
$writer = new Xlsx($spreadsheet);
$filename = "Progress_MCP_By_Area_Intervensi.xlsx";
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="' . $filename . '"');
return $writer->save('php://output');
}
public function indikator()
{
$req = $this->request;
$periode = $req->get('id_periode');
$id_instansi = $req->get('id_instansi');
$id_indikator = $req->get('id_indikator');
if (empty($periode)) {
$periode = -1;
}
if (empty($id_instansi)) {
$id_instansi = -1;
}
$queryInstansi = "select
mp.provinsi_name as wilayah_provinsi,
i.nama as pemda,
ksi.nama as indikator,
kai.presentase_bobot * (COALESCE ( ka.nilai_verifikasi, 0 )) / 100 AS nilai_verifikasi
FROM
jaga.korsupgah_survey ks
JOIN jaga.instansi i ON ks.id_instansi = i.id
JOIN jaga.master_provinsi mp ON mp.provinsi_code :: INT = i.id_provinsi
JOIN jaga.korsupgah_template_sub_indikator ktsi ON ktsi.id_template = ks.id_template
JOIN jaga.korsupgah_sub_indikator ksi ON ktsi.id_sub_indikator = ksi.
ID
LEFT JOIN jaga.korsupgah_answer ka ON ( ka.id_question = ksi.ID AND ka.id_survey = ks.ID )
JOIN jaga.korsupgah_indikator ki ON ki.ID = ksi.id_indikator
JOIN jaga.korsupgah_area_intervensi kai ON kai.id = ki.id_area_intervensi
where ksi.id_indikator = $id_indikator
and (i.id = $id_instansi or $id_instansi = -1) and
(ks.id_periode = $periode or $periode = -1)
order by i.nama, mp.provinsi_name, kai.nama";
$dataInstansi = $this->rawQuery($queryInstansi, []);
$query = "select * from jaga.instansi i JOIN jaga.master_provinsi mp ON mp.provinsi_code :: INT = i.id_provinsi";
$data = $this->rawQuery($query, []);
$res = array();
$index = 0;
foreach ($data as $d) {
$result = array_filter($dataInstansi, function ($val) use ($d) {
return $val['pemda'] == $d['nama'];
}, ARRAY_FILTER_USE_BOTH);
$res[$index] =
[
'wilayah_provinsi' => $d['provinsi_name'],
'pemda' => $d['nama'],
];
foreach ($result as $r) {
$res[$index][$r['indikator']] = $r['nilai_verifikasi'];
}
$index++;
}
$kai = KorsupgahIndikator::find(
[
"id = :id:",
"bind" => [
"id" => $id_indikator,
]
]
);
if ($kai->count() != 1) {
throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, 'Indikator tidak ditemukan');
}
$query = "select * from jaga.korsupgah_sub_indikator ki where id_indikator = $id_indikator order by ki.nama";
$dataSubIndikator = $this->rawQuery($query, []);
$header = ["NO", "WILAYAH PROVINSI", "PEMERINTAH DAERAH"];
foreach ($dataSubIndikator as $subindikator) {
$header[] = $subindikator['nama'];
}
$spreadsheet = $this->generateExcel($kai[0]->nama, $res, $header, "INDIKATOR");
//return new CollectionPaginatedResponse($res);
$writer = new Xlsx($spreadsheet);
$filename = "Progress_MCP_By_Indikator.xlsx";
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="' . $filename . '"');
return $writer->save('php://output');
}
public function sub_indikator()
{
$req = $this->request;
$periode = $req->get('id_periode');
$id_instansi = $req->get('id_instansi');
$id_sub_indikator = $req->get('id_sub_indikator');
if (empty($periode)) {
$periode = -1;
}
if (empty($id_instansi)) {
$id_instansi = -1;
}
$queryInstansi = "select
mp.provinsi_name as wilayah_provinsi,
i.nama as pemda,
ksi.nama as indikator,
ksi.presentase_bobot * (COALESCE ( ka.nilai_verifikasi, 0 )) / 100 AS nilai_verifikasi
FROM
jaga.korsupgah_survey ks
JOIN jaga.instansi i ON ks.id_instansi = i.id
JOIN jaga.master_provinsi mp ON mp.provinsi_code :: INT = i.id_provinsi
JOIN jaga.korsupgah_template_sub_indikator ktsi ON ktsi.id_template = ks.id_template
JOIN jaga.korsupgah_sub_indikator ksi ON ktsi.id_sub_indikator = ksi.
ID
LEFT JOIN jaga.korsupgah_answer ka ON ( ka.id_question = ksi.ID AND ka.id_survey = ks.ID )
JOIN jaga.korsupgah_indikator ki ON ki.ID = ksi.id_indikator
JOIN jaga.korsupgah_area_intervensi kai ON kai.id = ki.id_area_intervensi
where ksi.id = $id_sub_indikator
and (i.id = $id_instansi or $id_instansi = -1) and
(ks.id_periode = $periode or $periode = -1)
order by i.nama, mp.provinsi_name, kai.nama";
$dataInstansi = $this->rawQuery($queryInstansi, []);
$query = "select * from jaga.instansi i JOIN jaga.master_provinsi mp ON mp.provinsi_code :: INT = i.id_provinsi";
$data = $this->rawQuery($query, []);
$res = array();
$index = 0;
foreach ($data as $d) {
$result = array_filter($dataInstansi, function ($val) use ($d) {
return $val['pemda'] == $d['nama'];
}, ARRAY_FILTER_USE_BOTH);
$res[$index] =
[
'wilayah_provinsi' => $d['provinsi_name'],
'pemda' => $d['nama'],
];
foreach ($result as $r) {
$res[$index][$r['indikator']] = $r['nilai_verifikasi'];
}
$index++;
}
return new CollectionPaginatedResponse($res);
}
public function provinsi()
{
$req = $this->request;
$periode = $req->get('id_periode');
if (empty($periode)) {
$periode = -1;
}
$queryInstansi = "select
mp.provinsi_name as wilayah_provinsi,
i.nama as pemda,
kai.nama as area_intervensi,
kai.presentase_bobot * (COALESCE ( ka.nilai_verifikasi, 0 )) / 100 AS nilai_verifikasi
FROM
jaga.korsupgah_survey ks
JOIN jaga.instansi i ON ks.id_instansi = i.id
JOIN jaga.master_provinsi mp ON mp.provinsi_code :: INT = i.id_provinsi
JOIN jaga.korsupgah_template_sub_indikator ktsi ON ktsi.id_template = ks.id_template
JOIN jaga.korsupgah_sub_indikator ksi ON ktsi.id_sub_indikator = ksi.
ID
LEFT JOIN jaga.korsupgah_answer ka ON ( ka.id_question = ksi.ID AND ka.id_survey = ks.ID )
JOIN jaga.korsupgah_indikator ki ON ki.ID = ksi.id_indikator
JOIN jaga.korsupgah_area_intervensi kai ON kai.id = ki.id_area_intervensi
where
(ks.id_periode = $periode or $periode = -1) and i.id_tipe = 1
order by i.nama, mp.provinsi_name, kai.nama";
$dataInstansi = $this->rawQuery($queryInstansi, []);
$query = "select * from jaga.instansi i JOIN jaga.master_provinsi mp ON mp.provinsi_code :: INT = i.id_provinsi";
$data = $this->rawQuery($query, []);
$res = array();
$index = 0;
foreach ($data as $d) {
$result = array_filter($dataInstansi, function ($val) use ($d) {
return $val['pemda'] == $d['nama'];
}, ARRAY_FILTER_USE_BOTH);
$res[$index] =
[
'wilayah_provinsi' => $d['provinsi_name'],
'pemda' => $d['nama'],
];
foreach ($result as $r) {
$res[$index][$r['area_intervensi']] = $r['nilai_verifikasi'];
}
$index++;
}
$header = ["NO", "WILAYAH PROVINSI", "PEMERINTAH DAERAH"];
$query = "select * from jaga.korsupgah_area_intervensi kai order by kai.nama";
$dataArea = $this->rawQuery($query, []);
foreach ($dataArea as $area) {
$header[] = $area['nama'];
}
$spreadsheet = $this->generateExcel("PROGRESS CAPAIAN MCP SELURUH PEMDA DI INDONESIA", $res, $header, "PROVINSI");
//return new CollectionPaginatedResponse($res);
$writer = new Xlsx($spreadsheet);
$filename = "Progress_MCP_By_Provinsi.xlsx";
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="' . $filename . '"');
return $writer->save('php://output');
}
public function kabupaten()
{
$req = $this->request;
$periode = $req->get('id_periode');
$id_provinsi = $req->get('id_provinsi');
if (empty($periode)) {
$periode = -1;
}
$queryInstansi = "select
mp.provinsi_name as wilayah_provinsi,
i.nama as pemda,
kai.nama as area_intervensi,
kai.presentase_bobot * (COALESCE ( ka.nilai_verifikasi, 0 )) / 100 AS nilai_verifikasi
FROM
jaga.korsupgah_survey ks
JOIN jaga.instansi i ON ks.id_instansi = i.id
JOIN jaga.master_provinsi mp ON mp.provinsi_code :: INT = i.id_provinsi
JOIN jaga.korsupgah_template_sub_indikator ktsi ON ktsi.id_template = ks.id_template
JOIN jaga.korsupgah_sub_indikator ksi ON ktsi.id_sub_indikator = ksi.
ID
LEFT JOIN jaga.korsupgah_answer ka ON ( ka.id_question = ksi.ID AND ka.id_survey = ks.ID )
JOIN jaga.korsupgah_indikator ki ON ki.ID = ksi.id_indikator
JOIN jaga.korsupgah_area_intervensi kai ON kai.id = ki.id_area_intervensi
where (i.id_provinsi = $id_provinsi) and
(ks.id_periode = $periode or $periode = -1) and i.id_tipe = 2
order by i.nama, mp.provinsi_name, kai.nama";
$dataInstansi = $this->rawQuery($queryInstansi, []);
$query = "select * from jaga.instansi i JOIN jaga.master_provinsi mp ON mp.provinsi_code :: INT = i.id_provinsi and i.id_provinsi = $id_provinsi";
$data = $this->rawQuery($query, []);
$res = array();
$index = 0;
foreach ($data as $d) {
$result = array_filter($dataInstansi, function ($val) use ($d) {
return $val['pemda'] == $d['nama'];
}, ARRAY_FILTER_USE_BOTH);
$res[$index] =
[
'wilayah_provinsi' => $d['provinsi_name'],
'pemda' => $d['nama'],
];
foreach ($result as $r) {
$res[$index][$r['area_intervensi']] = $r['nilai_verifikasi'];
}
$index++;
}
$header = ["NO", "WILAYAH PROVINSI", "PEMERINTAH DAERAH"];
$query = "select * from jaga.korsupgah_area_intervensi kai order by kai.nama";
$dataArea = $this->rawQuery($query, []);
foreach ($dataArea as $area) {
$header[] = $area['nama'];
}
$spreadsheet = $this->generateExcel("PROGRESS CAPAIAN MCP SELURUH PEMDA DI INDONESIA", $res, $header, "KOTA_KABUPATEN");
//return new CollectionPaginatedResponse($res);
$writer = new Xlsx($spreadsheet);
$filename = "Progress_MCP_By_Kota_Kabupaten.xlsx";
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="' . $filename . '"');
return $writer->save('php://output');
}
public function reports()
{
ini_set('memory_limit', '2048M');
$req = $this->request->get();
$params = $this->validate(
$req,
[
'id_periode' => ['validators' => ['digit']],
'id_instansi' => ['validators' => ['digit']],
'id_area_intervensi' => ['validators' => ['digit']],
'id_template' => ['validators' => ['digit', 'required']]
]
);
$periode = empty($params['id_periode']) ? 13 : $params['id_periode'];
$id_instansi = empty($params['id_instansi']) ? -1 : $params['id_instansi'];
$id_template = 1;
$id_template_dd = 0;
$tahun = 0;
if (!empty($params['id_template'])) {
$id_template = $params['id_template'];
$dataKorTemplate = KorsupgahTemplate::findFirst($id_template);
if (!empty($dataKorTemplate)) {
$id_template_dd = $dataKorTemplate->id_dana_desa;
if (empty($id_template_dd) || $id_template_dd == null)
$id_template_dd = 0;
$tahun = $dataKorTemplate->tahun;
}
} else {
$id_template_dd = getenv('KORWIL_ID_DD');
}
//region mencari periode yg tepat dari template
$survey = KorsupgahSurvey::find([
'conditions' => 'id_template = ?0 AND deleted_at IS NULL',
'bind' => [
$id_template
],
'limit' => 1
]);
if (!empty($survey)) {
$periode = $survey->toArray()[0]['id_periode'];
}
//endregion
//region check jika user adalah bpkp
$whereBpkp = '';
$isBpkp = $this->hasRole('korwil_bpkp');
if ($isBpkp) {
$provinsiIds = array_column(
KorwilUserBpkpProvinsi::find([
'conditions' => 'user_id = ?0',
'bind' => [
$this->shared->user->um_id
]
])->toArray(),
'provinsi_id'
);
if (!empty($provinsiIds)) {
$cond = "('" . implode("','", $provinsiIds) . "')";
$whereBpkp = " AND (case when riw.id_provinsi is null then i.id_provinsi IN " . $cond . " else riw.id_provinsi IN " . $cond . " end) ";
}
}
//endregion
$q = "
SELECT *
FROM
(
SELECT
mp.provinsi_name,
i.nama AS instansi,
kp_direktorat.nama as direktorat,
ks.id_pengampu,
ks.id_template,
i.id_tipe,
i.flag_dana_desa as dana_desa,
kai.nama AS area_intervensi,
CASE
WHEN ( i.flag_dana_desa = 0 AND kai.id = '6' ) THEN
kai.presentase_bobot + 5
WHEN (i.flag_dana_desa = 0)
THEN kai.presentase_bobot_wo_dana_desa
ELSE
kai.presentase_bobot
END bobot_area_intervensi,
ki.nama AS indikator,
ki.presentase_bobot AS bobot_indikator,
ktsi.id_sub_indikator,
ksi.nama AS sub_indikator,
ksi.presentase_bobot AS bobot_subindikator,
COALESCE ( ka.nilai, 0 ) :: FLOAT AS nilai,
COALESCE ( ka.nilai_verifikasi_qa, ka.nilai_verifikasi, 0 ) :: FLOAT AS nilai_verifikasi,
ka.created_at AS tgl_lapor,
COALESCE (ka.qa_verified_at, ka.verified_at) AS tgl_verif,
RANK ( ) OVER ( PARTITION BY i.nama, ki.nama, ksi.nama ORDER BY ka.verified_at DESC ) AS rk,
COALESCE ( ka.nilai_verifikasi_qa, ka.nilai_verifikasi, 0 ) :: FLOAT * ksi.presentase_bobot / 100 AS nilai_subindikator,
COALESCE ( ka.nilai_verifikasi_qa, ka.nilai_verifikasi, 0 ) :: FLOAT * ksi.presentase_bobot / 100 * ki.presentase_bobot / 100 AS nilai_indikator,
CASE
WHEN ( i.flag_dana_desa = 0 AND kai.id = '6' )
THEN
COALESCE ( ka.nilai_verifikasi_qa, ka.nilai_verifikasi, 0 ) :: FLOAT * ksi.presentase_bobot / 100 * ki.presentase_bobot / 100 * ( kai.presentase_bobot + 5 ) / 100
WHEN (i.flag_dana_desa = 0)
THEN
COALESCE ( ka.nilai_verifikasi_qa, ka.nilai_verifikasi, 0 ) :: FLOAT * ksi.presentase_bobot / 100 * ki.presentase_bobot / 100 * kai.presentase_bobot_wo_dana_desa / 100
ELSE
COALESCE ( ka.nilai_verifikasi_qa, ka.nilai_verifikasi, 0 ) :: FLOAT * ksi.presentase_bobot / 100 * ki.presentase_bobot / 100 * kai.presentase_bobot / 100
END nilai_area,
kai.id_area_fokus,
kmps.score as penalty_score
FROM
jaga.korsupgah_survey ks
JOIN jaga.instansi i ON ks.id_instansi = i.ID
LEFT JOIN jaga.rel_instansi_wilayah riw ON riw.id_instansi = i.id AND $tahun >= riw.start_year AND $tahun <= riw.end_year
JOIN jaga.master_provinsi mp ON mp.provinsi_code :: INT = COALESCE(riw.id_provinsi, i.id_provinsi)
join jaga.korsupgah_template t on t.id = ks.id_template
JOIN jaga.korsupgah_template_sub_indikator ktsi ON t.id = ktsi.id_template
JOIN jaga.korsupgah_sub_indikator ksi ON ktsi.id_sub_indikator = ksi.ID
LEFT JOIN (
SELECT distinct on (1, 2) a.id_question, a.id_survey, a.id as answer_id, a.nilai, a.nilai_verifikasi, a.nilai_verifikasi_qa,
a.created_at, a.verified_at, a.qa_verified_at,
a.keterangan_verifikasi, a.keterangan_verifikasi_qa
FROM jaga.korsupgah_answer a
WHERE a.status >= 1
ORDER BY a.id_question, a.id_survey, a.created_at DESC
) ka ON ( ka.id_question = ksi.ID AND ka.id_survey = ks.ID )
JOIN jaga.korsupgah_indikator ki ON ki.ID = ksi.id_indikator
JOIN jaga.korsupgah_area_intervensi kai ON kai.ID = ki.id_area_intervensi
JOIN jaga.korsupgah_pengampu kp_satgas ON kp_satgas.id = ks.id_pengampu
JOIN jaga.korsupgah_pengampu kp_direktorat ON kp_direktorat.id = kp_satgas.id_parent
left outer join jaga.korsupgah_mcp_penalty_score kmps
on kmps.id_instansi = ks.id_instansi and kmps.id_template = ks.id_template
and kmps.deleted_at is null
WHERE
(ks.id_template = $id_template OR ks.id_template = $id_template_dd)
and (i.id = $id_instansi or $id_instansi = -1)
and (ks.id_periode = $periode or $periode = -1)
AND ktsi.deleted_at IS NULL
AND ks.deleted_at IS NULL
$whereBpkp
ORDER BY
mp.provinsi_name,
i.nama,
ktsi.id_sub_indikator ASC
) AS t_report
WHERE
rk = 1;";
// echo '';
// print_r($q);
// echo '';
// die;
$rawReport = $this->rawQuery($q, []);
//region check if user kemdagri
$isKemdagri = $this->hasRole('korwil_kemdagri');
$shownFokusAreaIds = [];
if ($isKemdagri && $this->shared->user->id_jabatan != null) {
$shownFokusAreaIds = array_column(
KorsupgahAreaFokusPenanggungJawab::find([
'conditions' => 'id_jabatan = ?0',
'bind' => [
$this->shared->user->id_jabatan
]
])->toArray(),
'id_area_fokus'
);
}
//endregion
$report_mcp = [];
$curInstansi = "";
$curAreaIntervensi = "";
$curIndikator = "";
$nInstansi = $nAreaIntervensi = $nIndikator = 0;
foreach ($rawReport as $key => $rep) {
// nilai capaian instansi
if ($curInstansi != $rep['instansi']) {
$curInstansi = $rep['instansi'];
$nInstansi = 0;
$curAreaIntervensi = $rep['area_intervensi'];
$nAreaIntervensi = 0;
$curIndikator = $rep['indikator'];
$nIndikator = 0;
}
$nInstansi += $rep['nilai_area'];
$report_mcp[$rep['instansi']]['total'] = $nInstansi;
$report_mcp[$rep['instansi']]['direktorat'] = $rep['direktorat'];
$report_mcp[$rep['instansi']]['dana_desa'] = $rep['dana_desa'];
$report_mcp[$rep['instansi']]['provinsi'] = $rep['provinsi_name'];
$report_mcp[$rep['instansi']]['id_tipe'] = $rep['id_tipe'];
// nilai capaian area intervensi
if ($curAreaIntervensi != $rep['area_intervensi']) {
$curAreaIntervensi = $rep['area_intervensi'];
$nAreaIntervensi = 0;
$curIndikator = $rep['indikator'];
$nIndikator = 0;
}
$nAreaIntervensi += $rep['nilai_indikator'];
$report_mcp[$rep['instansi']][$rep['area_intervensi']]['total'] = $nAreaIntervensi;
$report_mcp[$rep['instansi']][$rep['area_intervensi']]['bobot_area'] = $rep['bobot_area_intervensi'];
// nilai capaian indikator
if ($curIndikator != $rep['indikator']) {
$curIndikator = $rep['indikator'];
$nIndikator = 0;
}
$nIndikator += $rep['nilai_subindikator'];
$report_mcp[$rep['instansi']][$rep['area_intervensi']][$rep['indikator']]['total'] = $nIndikator;
$report_mcp[$rep['instansi']][$rep['area_intervensi']][$rep['indikator']]['bobot_indikator'] = $rep['bobot_indikator'];
$report_mcp[$rep['instansi']][$rep['area_intervensi']][$rep['indikator']][$rep['sub_indikator']] = $rep['nilai_subindikator'];
}
// populate penalty_score
$penalties = [];
foreach ($rawReport as $row) {
if ($row['penalty_score']) {
$penalties[$row['instansi']] = $row['penalty_score'];
}
}
// impose the penalties
foreach ($penalties as $instansi => $penalty) {
$report_mcp[$instansi]['total'] -= intval($penalty);
}
// get area intervensi
$data_areaintervensi = [];
$templateIds = [
$id_template,
$id_template_dd
];
$subIndikatorIds = array_column(
KorsupgahTemplateSubIndikator::find([
'conditions' => 'id_template IN ({templateIds:array})',
'bind' => [
'templateIds' => $templateIds
]
])->toArray(),
'id_sub_indikator'
);
if (!empty($subIndikatorIds)) {
$indikatorIds = array_column(
KorsupgahSubIndikator::find([
'conditions' => 'id IN ({subIndikatorIds:array})',
'bind' => [
'subIndikatorIds' => array_values($subIndikatorIds)
]
])->toArray(),
'id_indikator'
);
if (!empty($indikatorIds)) {
$areaIntervensiIds = array_column(
KorsupgahIndikator::find([
'conditions' => 'id IN ({indikatorIds:array})',
'bind' => [
'indikatorIds' => array_values($indikatorIds)
]
])->toArray(),
'id_area_intervensi'
);
if (!empty($areaIntervensiIds)) {
$data_areaintervensi = KorsupgahAreaIntervensi::find([
'columns' => 'id, nama, keterangan, id_area_fokus',
'conditions' => 'id IN ({areaIntervensiIds:array})',
'bind' => [
'areaIntervensiIds' => $areaIntervensiIds
]
])->toArray();
}
}
}
if (empty($data_areaintervensi)) {
$qAi = "SELECT kai.ID, kai.nama,kai.keterangan, kai.id_area_fokus FROM korsupgah_area_intervensi kai ORDER BY kai.ID;";
$data_areaintervensi = $this->rawQuery($qAi, []);
}
// initiate excel
$sheetIndex = 0;
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->createSheet($sheetIndex++);
// echo '';
// print_r($report_mcp['Pemerintah Kabupaten Bolaang Mongondow']);
// echo '';
// die;
//region selected area intervensi for kemdagri
$selected_areaintervensi = $data_areaintervensi;
if ($isKemdagri && $this->shared->user->id_jabatan != null) {
$selected_areaintervensi = [];
foreach ($data_areaintervensi as $item) {
if (in_array($item['id_area_fokus'], $shownFokusAreaIds)) {
array_push($selected_areaintervensi, $item);
}
}
}
//endregion
// total area intervensi
$this->generateSheetTotal($sheet, $report_mcp, $selected_areaintervensi, 'PROGRESS CAPAIAN MCP SELURUH PEMDA DI INDONESIA', 'Total');
// sub indikator per area intervensi
foreach ($data_areaintervensi as $key => $ai) {
if (!$isKemdagri || ($isKemdagri && $this->shared->user->id_jabatan == null) || ($isKemdagri && $this->shared->user->id_jabatan != null && in_array($ai['id_area_fokus'], $shownFokusAreaIds))) {
$sheet = $spreadsheet->createSheet($sheetIndex++);
$this->generateSheetPerAreaIntervensi($sheet, $report_mcp, $ai['nama'], $ai['nama'], $ai['nama'], $ai['id']);
}
}
// provinsi
$sheet = $spreadsheet->createSheet($sheetIndex++);
$filterBy = 1;
$report_mcp_prov = array_filter($report_mcp, function ($var) use ($filterBy) {
return ($var['id_tipe'] == $filterBy);
});
$this->generateSheetTotal($sheet, $report_mcp_prov, $selected_areaintervensi, 'PROGRESS CAPAIAN MCP PEMERINTAHAN PROVINSI', 'Pemprov');
// Kota Kabupaten
$sheet = $spreadsheet->createSheet($sheetIndex++);
$filterBy = 2;
$report_mcp_kabkot = array_filter($report_mcp, function ($var) use ($filterBy) {
return ($var['id_tipe'] == $filterBy);
});
$this->generateSheetTotal($sheet, $report_mcp_kabkot, $selected_areaintervensi, 'PROGRESS CAPAIAN MCP PEMERINTAHAN KABUPATEN & KOTA', 'Kabupaten Kota');
// Rekap Per Direktorat
$sheet = $spreadsheet->createSheet($sheetIndex++);
$initial_empty_values_per_area['total'] = [
'count' => 0,
'sum' => 0
];
foreach ($data_areaintervensi as $area) {
$initial_empty_values_per_area[$area['nama']] = [
'count' => 0,
'sum' => 0
];
}
// inisialisasi nilai kosong per area per direktorat
$qNamaDirektorat = "SELECT nama FROM korsupgah_pengampu where id_parent is NULL ORDER BY nama;";
$daftarDirektorat = $this->rawQuery($qNamaDirektorat, []);
for ($i = 0; $i < sizeof($daftarDirektorat); $i++) {
$namaDirektorat = $daftarDirektorat[$i]['nama'];
$dataPerDirektorat[$namaDirektorat] = $initial_empty_values_per_area;
}
foreach ($report_mcp as $pemda) {
$namaDirektorat = $pemda['direktorat'];
foreach ($dataPerDirektorat[$namaDirektorat] as $area => $tmp) {
if (isset($pemda[$area])) {
$dataPerDirektorat[$namaDirektorat][$area]['count']++;
$dataPerDirektorat[$namaDirektorat][$area]['sum'] += $area === 'total' ? $pemda[$area] : $pemda[$area]['total'];
}
}
}
// Hitung rata-rata semua area
foreach ($dataPerDirektorat as $namaDirektorat => $areas) {
foreach ($areas as $area => $tmp) {
$dataPerDirektorat[$namaDirektorat][$area]['avg'] =
$dataPerDirektorat[$namaDirektorat][$area]['count'] === 0 ? 0 :
$dataPerDirektorat[$namaDirektorat][$area]['sum'] /
$dataPerDirektorat[$namaDirektorat][$area]['count'];
}
}
$this->generateSheetPerDirektorat($sheet, $dataPerDirektorat, 'PROGRESS CAPAIAN MCP PER DIREKTORAT', 'Direktorat');
// sub indikator
/*$sheet = $spreadsheet->createSheet($sheetIndex++);
$this->generateSheetSubIndikator($sheet, $rawReport, 'DETIL NILAI SUBINDIKATOR', 'Sub Indikator');*/
// generate excel
$spreadsheet->setActiveSheetIndex(0);
$writer = new Xlsx($spreadsheet);
$now = date("YmdHis");
$filename = $now . "-Progress_MCP.xlsx";
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="' . $filename . '"');
return $writer->save('php://output');
// return $report_mcp;
}
public function reports2()
{
ini_set('memory_limit', '2048M');
$req = $this->request->get();
$params = $this->validate(
$req,
[
'id_template' => ['validators' => ['required']],
'date' => ['validators' => ['required']],
'provinsi_name' => ['validators' => []],
'area_intervensi' => ['validators' => []],
'year' => ['validators' => ['digit']]
]
);
if (!isset($params['year']) || empty($params['year']) || $params['year'] < 2025) {
$writer = $this->generateReports2Excel($params);
} else {
// For 2025 and later, we will generate a different report
$writer = $this->generateNeoReports2Excel($params);
}
$filename = date("YmdHis") . "-Progress_MCP_By_Provinsi.xlsx";
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="' . $filename . '"');
return $writer->save('php://output');
}
private function generateReports2Excel(array $params)
{
$id_template = $params['id_template'];
$date = $params['date'];
/** t1: mengambil presentase bobot subindikator, indikator, area intervensi */
// area intervensi: bila 0 -> semua area intervensi -> tidak perlu kondisi khusus -> null
$area_intervensi_param = $params['area_intervensi'] && !in_array(0, $params['area_intervensi'])
? implode(',', $params['area_intervensi'])
: null;
$t1_area_intervensi_cond = $area_intervensi_param ? "and C.id in ($area_intervensi_param)" : '';
$t1_query = "
with t1 as (select A.id as id_sub_indikator,
A.nama as nama_sub_indikator,
A.presentase_bobot as presentase_bobot_sub_indikator,
B.id as id_indikator,
B.nama as nama_indikator,
B.presentase_bobot as presentase_bobot_indikator,
C.id as id_area_intervensi,
C.nama as nama_area_intervensi,
C.presentase_bobot as presentase_bobot_area_intervensi,
D.id_template
from jaga.korsupgah_sub_indikator as A
inner join jaga.korsupgah_indikator as B on B.id = A.id_indikator
inner join jaga.korsupgah_area_intervensi as C on C.id = B.id_area_intervensi
inner join jaga.korsupgah_template_sub_indikator as D on D.id_sub_indikator = A.id
inner join jaga.korsupgah_template as E on E.id = D.id_template
where E.id = $id_template
$t1_area_intervensi_cond
order by A.id
)
";
/** t2: mengambil nilai_verifikasi per instansi */
$provinsi_name_cond = $params['provinsi_name'] && $params['provinsi_name'] !== '0' ?
"and F.provinsi_name = '" . $params['provinsi_name'] . "'" : '';
$t2_query = ",
t2 as (
select distinct on(D.id, D.nama, A.id_instansi, B.id_question)
D.id, D.nama as direktorat, A.id_instansi, E.nama as nama_instansi,
F.provinsi_name as nama_provinsi,
B.id_question,
B.verified_at, B.nilai_verifikasi, B.nilai_verifikasi_qa,
G.score as penalty_score from jaga.korsupgah_survey as A
left join jaga.korsupgah_answer as B on A.id = B.id_survey
inner join jaga.korsupgah_pengampu as C on A.id_pengampu = C.id
inner join jaga.korsupgah_pengampu as D on D.id = C.id_parent
inner join jaga.instansi as E on E.id = A.id_instansi
inner join jaga.master_provinsi as F on F.provinsi_code::integer = E.id_provinsi
left outer join jaga.korsupgah_mcp_penalty_score G
on G.id_instansi = A.id_instansi
and G.id_template = (select id_template from t1 limit 1)
and G.deleted_at is null
where B.id_question in (select distinct id_sub_indikator from t1)
and B.verified_at < date('$date') + INTERVAL '1 day'
$provinsi_name_cond
order by D.id, D.nama, A.id_instansi, B.id_question, B.created_at desc
)";
$query = $t1_query . $t2_query . " select t2.direktorat,
t2.nama_provinsi,
t2.nama_instansi,
t2.id_instansi,
COALESCE(t2.nilai_verifikasi_qa, t2.nilai_verifikasi, 0) * t1.presentase_bobot_sub_indikator / 100.0 as score_sub_indikator,
COALESCE(t2.nilai_verifikasi_qa, t2.nilai_verifikasi, 0) * t1.presentase_bobot_sub_indikator / 100.0 * t1.presentase_bobot_indikator / 100.0 as score_indikator,
COALESCE(t2.nilai_verifikasi_qa, t2.nilai_verifikasi, 0) * t1.presentase_bobot_sub_indikator / 100.0 * t1.presentase_bobot_indikator / 100.0 * presentase_bobot_area_intervensi / 100.0 as score_area_intervensi,
t1.id_sub_indikator,
t1.nama_sub_indikator,
t1.id_indikator,
t1.nama_indikator,
t1.id_area_intervensi,
t1.nama_area_intervensi,
t2.penalty_score
from t1 left join t2 on t1.id_sub_indikator = t2.id_question
order by direktorat, nama_provinsi, nama_instansi, id_area_intervensi, id_indikator, id_sub_indikator";
$data = $this->rawQuery($query);
// Summary level
$populatedSummaryMcp = [];
foreach ($data as $row) {
if (array_key_exists($row['id_instansi'], $populatedSummaryMcp)) {
if (array_key_exists($row['nama_area_intervensi'], $populatedSummaryMcp[$row['id_instansi']]['score_all_area_intervensi'])) {
$populatedSummaryMcp[$row['id_instansi']]['score_mcp'] += $row['score_area_intervensi'];
$populatedSummaryMcp[$row['id_instansi']]['mcp_pasca_koreksi'] += $row['score_area_intervensi'];
$populatedSummaryMcp[$row['id_instansi']]['score_all_area_intervensi'][$row['nama_area_intervensi']] += $row['score_area_intervensi'];
} else {
$populatedSummaryMcp[$row['id_instansi']]['score_mcp'] += $row['score_area_intervensi'];
$populatedSummaryMcp[$row['id_instansi']]['mcp_pasca_koreksi'] += $row['score_area_intervensi'];
$populatedSummaryMcp[$row['id_instansi']]['score_all_area_intervensi'][$row['nama_area_intervensi']] = $row['score_area_intervensi'];
}
} else {
// belum ada sama sekali
$populatedSummaryMcp[$row['id_instansi']] = [
'direktorat' => $row['direktorat'],
'nama_provinsi' => $row['nama_provinsi'],
'nama_instansi' => $row['nama_instansi'],
'score_mcp' => $row['score_area_intervensi'],
'mcp_pasca_koreksi' => $row['score_area_intervensi'],
'faktor_koreksi' => 0,
'score_all_area_intervensi' => [
$row['nama_area_intervensi'] => $row['score_area_intervensi']
]
];
}
}
// populate penalty_score
$penalties = [];
foreach ($data as $row) {
if ($row['penalty_score']) {
$penalties[$row['id_instansi']] = $row['penalty_score'];
}
}
// impose the penalties
foreach ($penalties as $id_instansi => $penalty) {
$populatedSummaryMcp[$id_instansi]['faktor_koreksi'] = intval($penalty);
$populatedSummaryMcp[$id_instansi]['mcp_pasca_koreksi'] -= intval($penalty);
}
// Area Intervensi Level
$populatedMcpAI = [];
foreach ($data as $row) {
if (array_key_exists($row['id_area_intervensi'], $populatedMcpAI)) {
if (array_key_exists($row['id_instansi'], $populatedMcpAI[$row['id_area_intervensi']]['instansi'])) {
if (
array_key_exists(
$row['id_indikator'],
$populatedMcpAI[$row['id_area_intervensi']]['instansi'][$row['id_instansi']]['indikator']
)
) {
// sum the score_indikator
$populatedMcpAI[$row['id_area_intervensi']]['instansi'][$row['id_instansi']]['score_area_intervensi'] += $row['score_indikator'];
$populatedMcpAI[$row['id_area_intervensi']]['instansi'][$row['id_instansi']]['indikator'][$row['id_indikator']]['score_indikator'] +=
$row['score_indikator'];
} else {
// assign the score_indikator
$populatedMcpAI[$row['id_area_intervensi']]['instansi'][$row['id_instansi']]['score_area_intervensi'] += $row['score_indikator'];
$populatedMcpAI[$row['id_area_intervensi']]['instansi'][$row['id_instansi']]['indikator'][$row['id_indikator']] = [
'nama_indikator' => $row['nama_indikator'],
'score_indikator' => $row['score_indikator']
];
}
} else {
$populatedMcpAI[$row['id_area_intervensi']]['instansi'][$row['id_instansi']] = [
'direktorat' => $row['direktorat'],
'nama_instansi' => $row['nama_instansi'],
'nama_provinsi' => $row['nama_provinsi'],
'score_area_intervensi' => $row['score_indikator'],
'indikator' => [
$row['id_indikator'] => [
'nama_indikator' => $row['nama_indikator'],
'score_indikator' => $row['score_indikator']
]
]
];
}
} else {
// belum ada sama sekali
$populatedMcpAI[$row['id_area_intervensi']] = [
'nama_area_intervensi' => $row['nama_area_intervensi'],
'instansi' => [
$row['id_instansi'] => [
'direktorat' => $row['direktorat'],
'nama_instansi' => $row['nama_instansi'],
'nama_provinsi' => $row['nama_provinsi'],
'score_area_intervensi' => $row['score_indikator'],
'indikator' => [
$row['id_indikator'] => [
'nama_indikator' => $row['nama_indikator'],
'score_indikator' => $row['score_indikator']
]
]
]
]
];
}
}
// Indikator level
$populatedMcpIn = [];
foreach ($data as $row) {
if (array_key_exists($row['id_indikator'], $populatedMcpIn)) {
if (array_key_exists($row['id_instansi'], $populatedMcpIn[$row['id_indikator']]['instansi'])) {
if (
array_key_exists(
$row['id_sub_indikator'],
$populatedMcpIn[$row['id_indikator']]['instansi'][$row['id_instansi']]['sub_indikator']
)
) {
// sum the score_indikator
$populatedMcpIn[$row['id_indikator']]['instansi'][$row['id_instansi']]['score_indikator'] += $row['score_sub_indikator'];
$populatedMcpIn[$row['id_indikator']]['instansi'][$row['id_instansi']]['sub_indikator'][$row['id_sub_indikator']]['score_sub_indikator'] +=
$row['score_sub_indikator'];
} else {
// assign the score_indikator
$populatedMcpIn[$row['id_indikator']]['instansi'][$row['id_instansi']]['score_indikator'] += $row['score_sub_indikator'];
$populatedMcpIn[$row['id_indikator']]['instansi'][$row['id_instansi']]['sub_indikator'][$row['id_sub_indikator']] = [
'nama_sub_indikator' => $row['nama_sub_indikator'],
'score_sub_indikator' => $row['score_sub_indikator']
];
}
} else {
$populatedMcpIn[$row['id_indikator']]['instansi'][$row['id_instansi']] = [
'direktorat' => $row['direktorat'],
'nama_instansi' => $row['nama_instansi'],
'nama_provinsi' => $row['nama_provinsi'],
'score_indikator' => $row['score_sub_indikator'],
'sub_indikator' => [
$row['id_sub_indikator'] => [
'nama_sub_indikator' => $row['nama_sub_indikator'],
'score_sub_indikator' => $row['score_sub_indikator']
]
]
];
}
} else {
// belum ada sama sekali
$populatedMcpIn[$row['id_indikator']] = [
'nama_indikator' => $row['nama_indikator'],
'instansi' => [
$row['id_instansi'] => [
'direktorat' => $row['direktorat'],
'nama_instansi' => $row['nama_instansi'],
'nama_provinsi' => $row['nama_provinsi'],
'score_indikator' => $row['score_sub_indikator'],
'sub_indikator' => [
$row['id_sub_indikator'] => [
'nama_sub_indikator' => $row['nama_sub_indikator'],
'score_sub_indikator' => $row['score_sub_indikator']
]
]
]
]
];
}
}
ksort($populatedMcpAI);
$areaIntervensiList = [];
$spreadsheet = new Spreadsheet();
$sheetIndex = 0;
$areaIndex = 1;
foreach ($populatedMcpAI as $area) {
$areaIntervensiList[] = $area['nama_area_intervensi'];
/** extract all area intervensi -> generate sheet */
$sheet = $spreadsheet->createSheet($sheetIndex++);
$title = 'Progress Capaian MCP Area ' . $area['nama_area_intervensi'];
$sheetName = "$areaIndex-" . $area['nama_area_intervensi'];
$header = ['Direktorat', 'Wilayah Provinsi', 'Pemerintah Daerah', 'Total Capaian'];
$data = [];
$instansiIndex = 1;
// populate the existing id_indikator
$indikatorList = [];
foreach ($area['instansi'] as $instansi) {
foreach ($instansi['indikator'] as $id_indikator => $indikator) {
if (!array_key_exists($id_indikator, $indikatorList)) {
$indikatorList[$id_indikator] = $indikator;
}
}
}
// header untuk sub indikator
ksort($indikatorList);
$idIndikatorList = array_keys($indikatorList);
foreach ($indikatorList as $indikator) {
$header[] = $indikator['nama_indikator'];
}
foreach ($area['instansi'] as $instansi) {
$row = [
$instansi['direktorat'],
$instansi['nama_provinsi'],
$instansi['nama_instansi'],
$instansi['score_area_intervensi'],
];
// pengisian score indikator
$indikatorScores = array_fill(0, sizeof($indikatorList), 0);
foreach ($instansi['indikator'] as $id_indikator => $indikator) {
$indikatorScoreIndex = array_search($id_indikator, $idIndikatorList);
if ($indikatorScoreIndex === false)
continue;
$indikatorScores[$indikatorScoreIndex] = $indikator['score_indikator'];
}
$row = array_merge($row, $indikatorScores);
$data[] = $row;
$instansiIndex++;
}
$this->generateExcelMcpPerDirektorat($sheet, $title, $data, $header, $sheetName);
$indikatorIndex = 1;
foreach ($idIndikatorList as $index) {
/** extract indikator terkait -> generate sheet */
$indikator = $populatedMcpIn[$index];
$sheet = $spreadsheet->createSheet($sheetIndex++);
$title = 'Progress Capaian MCP Indikator ' . $indikator['nama_indikator'];
$sheetName = "$areaIndex.$indikatorIndex-" . $area['nama_area_intervensi'];
$header = ['Direktorat', 'Wilayah Provinsi', 'Pemerintah Daerah', 'Total Capaian'];
$data = [];
// populate the existing id_sub_indikator
$subIndikatorList = [];
foreach ($indikator['instansi'] as $instansi) {
foreach ($instansi['sub_indikator'] as $id_sub_indikator => $subIndikator) {
if (!array_key_exists($id_sub_indikator, $subIndikatorList)) {
$subIndikatorList[$id_sub_indikator] = $subIndikator;
}
}
}
// header untuk sub indikator
ksort($subIndikatorList);
$idSubIndikatorList = array_keys($subIndikatorList);
foreach ($subIndikatorList as $subIndikator) {
$header[] = $subIndikator['nama_sub_indikator'];
}
foreach ($indikator['instansi'] as $instansi) {
$row = [
$instansi['direktorat'],
$instansi['nama_provinsi'],
$instansi['nama_instansi'],
$instansi['score_indikator'],
];
$subIndikatorScores = array_fill(0, sizeof($subIndikatorList), 0);
foreach ($instansi['sub_indikator'] as $id_sub_indikator => $sub_indikator) {
$subIndikatorScoreIndex = array_search($id_sub_indikator, $idSubIndikatorList);
if ($subIndikatorScoreIndex === false)
continue;
$subIndikatorScores[$subIndikatorScoreIndex] = $sub_indikator['score_sub_indikator'];
}
$row = array_merge($row, $subIndikatorScores);
$data[] = $row;
}
$this->generateExcelMcpPerDirektorat($sheet, $title, $data, $header, $sheetName);
$indikatorIndex++;
}
$areaIndex++;
}
$sheet = $spreadsheet->createSheet(0);
$title = 'Progress Capaian MCP';
$sheetName = 'Progress Capaian MCP';
$header = ['Direktorat', 'Wilayah Provinsi', 'Pemerintah Daerah', 'Score MCP', 'Faktor Koreksi', 'MCP Pasca Koreksi'];
foreach ($areaIntervensiList as $areaIntervensi) {
$header[] = $areaIntervensi;
}
$data = [];
foreach ($populatedSummaryMcp as $instansi) {
$row = [
$instansi['direktorat'],
$instansi['nama_provinsi'],
$instansi['nama_instansi'],
$instansi['score_mcp'],
$instansi['faktor_koreksi'],
$instansi['mcp_pasca_koreksi']
];
// pengisian score area intervensi
$areaIntervensiScoreList = array_fill(0, sizeof($areaIntervensiList), 0);
foreach ($instansi['score_all_area_intervensi'] as $areaIntervensiName => $areaIntervensiScore) {
$areaIntervensiIndex = array_search($areaIntervensiName, $areaIntervensiList);
if ($areaIntervensiIndex === false)
continue;
$areaIntervensiScoreList[$areaIntervensiIndex] = $areaIntervensiScore;
}
$row = array_merge($row, $areaIntervensiScoreList);
$data[] = $row;
}
$this->generateExcelMcpPerDirektorat($sheet, $title, $data, $header, $sheetName);
$spreadsheet->setActiveSheetIndex(0);
return new Xlsx($spreadsheet);
}
private function generateNeoReports2Excel($params)
{
// get all instansi based on provinsi
$filterByProvinsi = '';
if ($params['provinsi_name'] && $params['provinsi_name'] !== '0') {
$filterByProvinsi = " AND mprov.provinsi_name = '" . $params['provinsi_name'] . "'";
}
$query = "
SELECT
ins.id,
ins.nama,
mprov.provinsi_name
FROM
jaga.instansi ins
LEFT JOIN jaga.master_provinsi mprov
ON ins.id_provinsi = mprov.provinsi_code::INTEGER
WHERE
ins.id_tipe IN (1, 2)
AND ins.deleted_at IS NULL
$filterByProvinsi
ORDER BY
ins.id
";
$instansi = $this->rawQuery($query);
// Create a lookup array for instansi data
$instansiLookup = [];
foreach ($instansi as $inst) {
$instansiLookup[$inst['id']] = [
'nama' => $inst['nama'],
'provinsi_name' => $inst['provinsi_name']
];
}
// get all instansi id
$eKDParams = [];
$eKDParams['year'] = $params['year'] ?? date('Y');
if ($params['provinsi_name'] && $params['provinsi_name'] !== '0') {
$eKDParams['instansi_ids'] = implode(',', array_keys($instansiLookup));
}
if (isset($params['area_intervensi']) && !empty($params['area_intervensi']) && $params['area_intervensi'] !== '0') {
$eKDParams['area_intervensi'] = is_array($params['area_intervensi']) ?
implode(',', $params['area_intervensi']) : $params['area_intervensi'];
}
try {
$mcpService = new McpService();
$result = $mcpService->get('/api/template/report', $eKDParams);
if (!$result->success || $result->data === null) {
throw new Exception("Failed to fetch data from MCP 2025 service.");
}
$hierarchyTemplate = $result->data->hierarchy ?? null;
$institutionsAnswers = $result->data->institutions_answers ?? [];
} catch (Exception $e) {
throw $e;
}
if (!$hierarchyTemplate) {
throw new Exception("Hierarchy data not found in response.");
}
return $this->generateExcelFromHierarchy($hierarchyTemplate, $institutionsAnswers, $instansiLookup);
}
private function generateExcelFromHierarchy($hierarchyTemplate, $institutionsAnswers, $instansiLookup)
{
$spreadsheet = new Spreadsheet();
$sheetIndex = 0;
$areaIntervensiList = [];
if (isset($hierarchyTemplate->children)) {
foreach ($hierarchyTemplate->children as $areaChild) {
$areaIntervensiList[] = $areaChild;
}
}
$sheet = $spreadsheet->createSheet($sheetIndex++);
$title = 'Progress Capaian MCSP';
$sheetName = 'Progress Capaian MCSP';
$header = ['Wilayah Provinsi', 'Pemerintah Daerah', 'Score MCSP'];
foreach ($areaIntervensiList as $area) {
$header[] = $area->nama ?? 'Unknown Area';
}
$mainData = [];
foreach ($institutionsAnswers as $institution) {
$instansiId = $institution->instansi_id ?? null;
if (!$instansiId || !isset($instansiLookup[$instansiId])) {
continue;
}
$instansiInfo = $instansiLookup[$instansiId];
$hierarchy = $institution->hierarchy ?? null;
$row = [
$instansiInfo['provinsi_name'],
$instansiInfo['nama'],
$hierarchy->score ?? 0
];
foreach ($areaIntervensiList as $area) {
$areaScore = $this->getAreaScore($hierarchy, $area->id ?? $area->nama) ?? 0;
$row[] = $areaScore;
}
$mainData[] = $row;
}
$this->generateExcelMcpPerDirektorat($sheet, $title, $mainData, $header, $sheetName);
$areaIndex = 1;
foreach ($areaIntervensiList as $area) {
$sheet = $spreadsheet->createSheet($sheetIndex++);
$title = 'Progress Capaian MCSP Area ' . ($area->nama ?? 'Unknown');
$sheetName = "$areaIndex-" . ($area->nama ?? 'Unknown');
$header = ['Wilayah Provinsi', 'Pemerintah Daerah', 'Total Capaian'];
$aspekList = [];
if (isset($area->children)) {
foreach ($area->children as $aspekChild) {
$aspekList[] = $aspekChild;
$header[] = $aspekChild->nama ?? 'Unknown Aspek';
}
}
$areaData = [];
foreach ($institutionsAnswers as $institution) {
$instansiId = $institution->instansi_id ?? null;
if (!$instansiId || !isset($instansiLookup[$instansiId])) {
continue;
}
$instansiInfo = $instansiLookup[$instansiId];
$hierarchy = $institution->hierarchy ?? null;
$areaScore = $this->getAreaScore($hierarchy, $area->id ?? $area->nama) ?? 0;
$row = [
$instansiInfo['provinsi_name'],
$instansiInfo['nama'],
$areaScore
];
foreach ($aspekList as $aspek) {
$aspekScore = $this->getAspekScore($hierarchy, $area->id ?? $area->nama, $aspek->id ?? $aspek->nama) ?? 0;
$row[] = $aspekScore;
}
$areaData[] = $row;
}
$this->generateExcelMcpPerDirektorat($sheet, $title, $areaData, $header, $sheetName);
$aspekIndex = 1;
foreach ($aspekList as $aspek) {
$sheet = $spreadsheet->createSheet($sheetIndex++);
$title = 'Progress Capaian MCSP Aspek ' . ($aspek->nama ?? 'Unknown');
$sheetName = "$areaIndex.$aspekIndex-" . ($aspek->nama ?? 'Unknown');
$header = ['Wilayah Provinsi', 'Pemerintah Daerah', 'Total Capaian'];
$sasaranList = [];
if (isset($aspek->children)) {
foreach ($aspek->children as $sasaranChild) {
$sasaranList[] = $sasaranChild;
$header[] = $sasaranChild->nama ?? 'Unknown Sasaran';
}
}
$aspekData = [];
foreach ($institutionsAnswers as $institution) {
$instansiId = $institution->instansi_id ?? null;
if (!$instansiId || !isset($instansiLookup[$instansiId])) {
continue;
}
$instansiInfo = $instansiLookup[$instansiId];
$hierarchy = $institution->hierarchy ?? null;
$aspekScore = $this->getAspekScore($hierarchy, $area->id ?? $area->nama, $aspek->id ?? $aspek->nama) ?? 0;
$row = [
$instansiInfo['provinsi_name'],
$instansiInfo['nama'],
$aspekScore
];
foreach ($sasaranList as $sasaran) {
$sasaranScore = $this->getSasaranScore($hierarchy, $area->id ?? $area->nama, $aspek->id ?? $aspek->nama, $sasaran->id ?? $sasaran->nama) ?? 0;
$row[] = $sasaranScore;
}
$aspekData[] = $row;
}
$this->generateExcelMcpPerDirektorat($sheet, $title, $aspekData, $header, $sheetName);
$aspekIndex++;
}
$areaIndex++;
}
$spreadsheet->setActiveSheetIndex(0);
return new Xlsx($spreadsheet);
}
private function getAreaScore($hierarchy, $areaId)
{
if (!$hierarchy || !isset($hierarchy->children)) {
return 0;
}
foreach ($hierarchy->children as $child) {
if (($child->id ?? $child->nama) === $areaId) {
return $child->score ?? 0;
}
}
return 0;
}
private function getAspekScore($hierarchy, $areaId, $aspekId)
{
if (!$hierarchy || !isset($hierarchy->children)) {
return 0;
}
foreach ($hierarchy->children as $areaChild) {
if (($areaChild->id ?? $areaChild->nama) === $areaId) {
if (isset($areaChild->children)) {
foreach ($areaChild->children as $aspekChild) {
if (($aspekChild->id ?? $aspekChild->nama) === $aspekId) {
return $aspekChild->score ?? 0;
}
}
}
}
}
return 0;
}
private function getSasaranScore($hierarchy, $areaId, $aspekId, $sasaranId)
{
if (!$hierarchy || !isset($hierarchy->children)) {
return 0;
}
foreach ($hierarchy->children as $areaChild) {
if (($areaChild->id ?? $areaChild->nama) === $areaId) {
if (isset($areaChild->children)) {
foreach ($areaChild->children as $aspekChild) {
if (($aspekChild->id ?? $aspekChild->nama) === $aspekId) {
if (isset($aspekChild->children)) {
foreach ($aspekChild->children as $sasaranChild) {
if (($sasaranChild->id ?? $sasaranChild->nama) === $sasaranId) {
return $sasaranChild->score ?? 0;
}
}
}
}
}
}
}
}
return 0;
}
public function mcp_per_direktorat()
{
// mengembalikan laporan excel spreadsheet MCP per direktorat per tanggal-tanggal tertentu
$req = $this->request->get();
$params = $this->validate(
$req,
[
'id_periode' => ['validators' => ['required', 'digit']],
'dates' => ['validators' => ['required']]
]
);
$id_periode = $params['id_periode'];
$query =
// get all persentase_bobot per direktorat, nama_provinsi, nama_instansi: sub_indikator -> indikator -> area
"with theader as (
select I.nama as nama_direktorat,
G.provinsi_name as nama_provinsi,
F.nama as nama_instansi,
E.id_instansi,
A.id as id_sub_indikator,
A.presentase_bobot as presentase_bobot_sub_indikator,
B.presentase_bobot as presentase_bobot_indikator,
C.presentase_bobot as presentase_bobot_area_intervensi
from jaga.korsupgah_sub_indikator as A
inner join jaga.korsupgah_indikator as B on B.id = A.id_indikator
inner join jaga.korsupgah_area_intervensi as C on C.id = B.id_area_intervensi
inner join jaga.korsupgah_template_sub_indikator as D on D.id_sub_indikator = A.id
inner join jaga.korsupgah_survey as E on E.id_template = D.id_template
inner join jaga.instansi as F on F.id = E.id_instansi
inner join jaga.master_provinsi as G on CAST(G.provinsi_code as INT) = F.id_provinsi
left join jaga.korsupgah_pengampu as H on H.id = E.id_pengampu
left join jaga.korsupgah_pengampu as I on I.id = H.id_parent
where E.id_periode = $id_periode
order by nama_direktorat, nama_instansi, id_sub_indikator
)
";
// hitung nilai MCP per daerah per tanggal
sort($params['dates']);
foreach ($params['dates'] as $index => $date) {
$query .= ",
tdetail$index as (
select distinct on(id_instansi, id_question)
B.id_instansi, A.id_question, A.verified_at, COALESCE(A.nilai_verifikasi_qa, A.nilai_verifikasi, 0) as nilai_verifikasi
from jaga.korsupgah_answer as A
inner join jaga.korsupgah_survey B on A.id_survey = B.id
where id_question in (select id_sub_indikator from theader)
and verified_at < date('$date') + interval '1 day'
order by B.id_instansi, A.id_question, A.verified_at desc
)
";
}
$query .= "select theader.nama_direktorat, theader.nama_provinsi, theader.nama_instansi";
foreach ($params['dates'] as $index => $date) {
$query .= "
, SUM (tdetail$index.nilai_verifikasi
* theader.presentase_bobot_sub_indikator / 100.0
* theader.presentase_bobot_indikator / 100.0
* theader.presentase_bobot_area_intervensi / 100.0) as mcp$index
";
}
$query .= " from theader";
foreach ($params['dates'] as $index => $date) {
$query .= " left join tdetail$index on theader.id_instansi = tdetail$index.id_instansi
and theader.id_sub_indikator = tdetail$index.id_question";
}
$query .= " GROUP BY theader.nama_provinsi, theader.nama_instansi, theader.nama_direktorat
order by theader.nama_provinsi, theader.nama_instansi, theader.nama_direktorat";
$mcpTable = $this->rawQuery($query);
// hitung rata-rata MCP setiap direktorat
// populate MCP Table menurut nama direktorat
$mcpTableByDirektorat = [];
foreach ($mcpTable as $row) {
if ($row['nama_direktorat'] === null)
continue;
$nama_direktorat = $row['nama_direktorat'];
unset($row['nama_direktorat']);
if (array_key_exists($nama_direktorat, $mcpTableByDirektorat)) {
$mcpTableByDirektorat[$nama_direktorat][] = $row;
} else {
$mcpTableByDirektorat[$nama_direktorat] = [$row];
}
}
ksort($mcpTableByDirektorat);
// calculate the average MCP
$mcpAverageTableByDirektorat = [];
foreach ($mcpTableByDirektorat as $direktorat => $instansiList) {
$mcpAverageTableByDirektorat[$direktorat] = [];
// init empty values for calculating average
foreach ($instansiList as $instansi) {
unset($instansi['nama_provinsi']);
unset($instansi['nama_instansi']);
foreach ($instansi as $mcpKey => $mcpScore) {
$mcpAverageTableByDirektorat[$direktorat][$mcpKey] = [
'sum' => 0,
'count' => 0
];
}
}
foreach ($instansiList as $instansi) {
unset($instansi['nama_provinsi']);
unset($instansi['nama_instansi']);
foreach ($instansi as $mcpKey => $mcpScore) {
$mcpAverageTableByDirektorat[$direktorat][$mcpKey]['count']++;
$mcpAverageTableByDirektorat[$direktorat][$mcpKey]['sum'] += $mcpScore;
}
}
}
// hitung average & populate
$mcpAverageByDirektorat = [];
foreach ($mcpAverageTableByDirektorat as $direktorat => $mcpList) {
$mcpPerDirektorat['nama_direktorat'] = $direktorat;
foreach ($mcpList as $mcpKey => $item) {
$mcpPerDirektorat[$mcpKey] =
$item['count'] === 0 ? 0 :
$item['sum'] / $item['count'];
}
$mcpAverageByDirektorat[] = $mcpPerDirektorat;
}
// Ready: Output the spreadsheet
$spreadsheet = new Spreadsheet();
$sheetIndex = 0;
// sheet rata-rata
$headerDates = [];
foreach ($params['dates'] as $date) {
$dateObject = DateTime::createFromFormat('Y-m-d', $date);
$headerDates[] = $dateObject->format('d M Y');
}
$header = ['Direktorat'];
$header = array_merge($header, $headerDates);
$sheet = $spreadsheet->createSheet($sheetIndex++);
$this->generateExcelMcpPerDirektorat($sheet, "Progress MCP", $mcpAverageByDirektorat, $header, 'Rata-rata Per Direktorat');
$header = ['Provinsi', 'Pemda'];
$header = array_merge($header, $headerDates);
foreach ($mcpTableByDirektorat as $namaDirektorat => $row) {
$sheet = $spreadsheet->createSheet($sheetIndex++);
$this->generateExcelMcpPerDirektorat($sheet, "Progress MCP", $row, $header, $namaDirektorat);
}
//return new CollectionPaginatedResponse($res);
$writer = new Xlsx($spreadsheet);
$filename = date("Ymd") . "-Progress_MCP_By_Direktorat.xlsx";
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="' . $filename . '"');
return $writer->save('php://output');
}
public function mcp_payload_per_provinsi()
{
$req = $this->request->get();
$params = $this->validate(
$req,
[
'template_id' => ['validators' => ['required', 'digit']],
'provinsi_id' => ['validators' => ['digit']]
]
);
$template_id = $params['template_id'];
$provinsi_id = $params['provinsi_id'] ?? null;
$provinsiCond = $provinsi_id === null ? "" : "and C.id_provinsi = $provinsi_id";
$query = "
with t_count_question as (
select D.id as id_area_intervensi, COUNT(B.id) as n_question
from jaga.korsupgah_template_sub_indikator as A
inner join jaga.korsupgah_sub_indikator as B on B.id = A.id_sub_indikator
inner join jaga.korsupgah_indikator as C on C.id = B.id_indikator
inner join jaga.korsupgah_area_intervensi as D on D.id = C.id_area_intervensi
where A.id_template = $template_id
group by D.id
),
t_answer as (
select C.id_instansi,
F.id as id_area_intervensi,
F.nama as nama_area_intervensi,
COUNT(B.id) as n_answer,
SUM(CASE WHEN B.verified_at is null then 0 ELSE 1 END) as n_verified,
COUNT(B.id) - SUM(CASE WHEN B.verified_at is null then 0 ELSE 1 END) as n_unverified
from jaga.korsupgah_template_sub_indikator as A
left join jaga.korsupgah_answer as B on B.id_question = A.id_sub_indikator
left join jaga.korsupgah_survey as C on C.id = B.id_survey
inner join jaga.korsupgah_sub_indikator as D on D.id = A.id_sub_indikator
inner join jaga.korsupgah_indikator as E on E.id = D.id_indikator
inner join jaga.korsupgah_area_intervensi as F on F.id = E.id_area_intervensi
where A.id_template = $template_id
group by C.id_instansi, F.id, F.nama
),
t_instansi as (
select F.nama as direktorat, D.provinsi_name as nama_provinsi, C.id as id_instansi, C.nama as nama_instansi
from jaga.korsupgah_survey as A
inner join jaga.instansi as C on A.id_instansi = C.id
inner join jaga.master_provinsi as D on D.provinsi_code::integer = C.id_provinsi
left join jaga.korsupgah_pengampu as E on E.id = A.id_pengampu
left join jaga.korsupgah_pengampu as F on F.id = E.id_parent
left join jaga.korsupgah_answer as B on A.id = B.id_survey
where A.id_template = $template_id $provinsiCond
group by F.nama, D.provinsi_name, C.id, C.nama
) select A.id_instansi, A.direktorat, A.nama_instansi, A.nama_provinsi, B.id_area_intervensi, B.nama_area_intervensi, C.n_question, B.n_answer, B.n_verified, B.n_unverified,
(CASE WHEN B.n_answer = 0 THEN '0' ELSE round(B.n_unverified * 100.0 / B.n_answer, 2) || '%' END ) as percentage,
D.total_question
from t_instansi as A
left join t_answer as B
on A.id_instansi = B.id_instansi
left join t_count_question as C
on C.id_area_intervensi = B.id_area_intervensi
cross join (select sum(n_question) as total_question from t_count_question) as D";
$data = $this->rawQuery($query);
// populate data
// get all area intervensi
$areaIntervensiColumns = [];
foreach ($data as $row) {
if ($row['nama_area_intervensi'] === null)
continue;
if (array_key_exists($row['id_area_intervensi'], $areaIntervensiColumns))
continue;
$areaIntervensiColumns[$row['id_area_intervensi']] = [
'nama_area_intervensi' => $row['nama_area_intervensi']
];
}
ksort($areaIntervensiColumns);
// setup the columns of output
$initialRow = [
'direktorat' => '',
'nama_provinsi' => '',
'nama_instansi' => '',
'total_question' => 0,
'total_answer' => 0,
'total_verified' => 0,
'total_unverified' => 0,
];
foreach ($areaIntervensiColumns as $id_area_intervensi => $temp) {
$initialRow["n_question_$id_area_intervensi"] = 0;
$initialRow["n_answer_$id_area_intervensi"] = 0;
$initialRow["n_verified_$id_area_intervensi"] = 0;
$initialRow["n_unverified_$id_area_intervensi"] = 0;
}
// populate by instansi_id
# 1. initial row
$output = [];
foreach ($data as $row) {
if (array_key_exists($row['id_instansi'], $output))
continue;
$output[$row['id_instansi']] = $initialRow;
}
# 2. assign the rows
$count_question_per_area = []; // untuk menyimpan jumlah pertanyaan per area,
// karena amat mungkin pemda belum jawab pertanyaan sehingga
// dalam hal ini jumlah pertanyaan jadi 0 karena tidak terambil di query di atas
foreach ($data as $row) {
$id_area_intervensi = $row['id_area_intervensi'];
$output[$row['id_instansi']]['direktorat'] = $row['direktorat'];
$output[$row['id_instansi']]['nama_provinsi'] = $row['nama_provinsi'];
$output[$row['id_instansi']]['nama_instansi'] = $row['nama_instansi'];
$output[$row['id_instansi']]['total_question'] = $row['total_question'];
$output[$row['id_instansi']]['total_answer'] += $row['n_answer'];
$output[$row['id_instansi']]['total_verified'] += $row['n_verified'];
$output[$row['id_instansi']]['total_unverified'] += $row['n_unverified'];
if ($id_area_intervensi) {
$output[$row['id_instansi']]["n_question_$id_area_intervensi"] = $row['n_question'];
// CAUTION: asumsi: minimal ada satu jawaban dari tiap area.
// Apabila dari tiap area tidak ada jawaban sama sekali alias semua jawaban adalah 0,
// maka jumlah pertanyaan di area tersebut akan tidak ada, dan di excel output akan tertera 0 (salah)
if ($row['n_question'] > 0) {
$count_question_per_area["n_question_$id_area_intervensi"] = $row['n_question'];
}
$output[$row['id_instansi']]["n_answer_$id_area_intervensi"] = $row['n_answer'];
$output[$row['id_instansi']]["n_verified_$id_area_intervensi"] = $row['n_verified'];
$output[$row['id_instansi']]["n_unverified_$id_area_intervensi"] = $row['n_unverified'];
}
}
# 3. Samakan jumlah pertanyaan di tiap area
foreach ($output as $i => $row) {
foreach ($count_question_per_area as $n_question_key => $n_question_value) {
$output[$i][$n_question_key] = $n_question_value;
}
}
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue("A1", 'Payload Per Pemda');
$sheet->getStyle("A1")->applyFromArray($this->styleArrayHeader); // style untuk title
$sheet->setCellValue("A3", "Direktorat");
$sheet->mergeCells('A3:A4');
$sheet->setCellValue("B3", "Provinsi");
$sheet->mergeCells('B3:B4');
$sheet->setCellValue("C3", "Instansi");
$sheet->mergeCells('C3:C4');
$sheet->setCellValue('D3', 'Total');
$sheet->mergeCells('D3:G3'); // 4 kolom
$valueTitles = ['Pertanyaan', 'Jawaban', 'Sudah Verif', 'Belum Verif'];
$col = 4;
$row = 3;
foreach ($valueTitles as $title) {
$sheet->setCellValueByColumnAndRow($col, $row + 1, $title);
$col++;
}
foreach ($areaIntervensiColumns as $column) {
$sheet->setCellValueByColumnAndRow($col, $row, $column['nama_area_intervensi']);
$sheet->mergeCellsByColumnAndRow($col, $row, $col + 3, $row);
foreach ($valueTitles as $title) {
$sheet->setCellValueByColumnAndRow($col, $row + 1, $title);
$col++;
}
}
# style untuk table header
$headerRange = "A$row:" . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col - 1) . ($row + 1);
$sheet->getStyle($headerRange)->applyFromArray($this->styleArray);
$sheet->getStyle($headerRange)->getAlignment()->setWrapText(true);
# 3. write all the data rows
$row = 4;
foreach ($output as $outputRow) {
$row++;
$col = 0;
foreach ($outputRow as $outputCol) {
$col++;
$sheet->setCellValueByColumnAndRow($col, $row, $outputCol);
}
}
# style untuk table rows
$range = "A5:" . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col) . ($row);
$sheet->getStyle($range)->applyFromArray($this->styleArrayNormal);
$writer = new Xlsx($spreadsheet);
$filename = "Progress Jawaban dan Verifikasi MCP - " . DateHelper::formatTodayIndex() . ".xlsx";
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="' . $filename . '"');
return $writer->save('php://output');
}
private function generateSheetTotal($sheet, $jsonData, $areaIntervensi, $title, $sheetName)
{
// sheet total ---------------------
$sheet->setTitle($sheetName);
$sheet->setCellValue("A1", $title);
$sheet->getStyle("A1")->applyFromArray($this->styleArrayHeader);
#region header
$col = 1;
$row = 3;
$sheet->setCellValueByColumnAndRow($col++, $row, 'DIREKTORAT');
$sheet->setCellValueByColumnAndRow($col++, $row, 'WILAYAH PROVINSI');
$sheet->setCellValueByColumnAndRow($col++, $row, 'PEMERINTAH DAERAH');
$sheet->setCellValueByColumnAndRow($col++, $row, 'TOTAL CAPAIAN');
foreach ($areaIntervensi as $key => $ai) {
$sheet->setCellValueByColumnAndRow($col++, $row, strtoupper($ai['nama']));
}
$headerRange = "A$row:" . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col - 1) . ($row);
$sheet->getStyle($headerRange)->applyFromArray($this->styleArray);
$sheet->getStyle($headerRange)->getAlignment()->setWrapText(true);
// generate content
$no = 1;
$row++;
$start = $row;
foreach ($jsonData as $key => $dataReport) {
$col = 1;
$sheet->setCellValueByColumnAndRow($col++, $row, $dataReport['direktorat']);
$sheet->setCellValueByColumnAndRow($col++, $row, $dataReport['provinsi']);
$sheet->setCellValueByColumnAndRow($col++, $row, $key);
$sheet->setCellValueByColumnAndRow($col++, $row, number_format((float) $dataReport['total'], 2, '.', ''));
foreach ($areaIntervensi as $key => $ai) {
$nilai_area_intervensi = isset($dataReport[$ai['nama']])
? number_format((float) $dataReport[$ai['nama']]['total'], 2, '.', '')
: 0;
$sheet->setCellValueByColumnAndRow($col++, $row, $nilai_area_intervensi);
}
$row++;
}
// styling & Format
$range = "A$start:C" . ($row - 1);
$sheet->getStyle($range)->applyFromArray($this->styleArrayNormal);
$range = "D$start:" . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col - 1) . ($row - 1);
$sheet->getStyle($range)->applyFromArray($this->styleArrayNormal);
// $sheet->getStyle($range)->getNumberFormat()->setFormatCode('0%'); //set if the result type is percentage
$sheet->getColumnDimension('B')->setAutoSize(true);
$sheet->getColumnDimension('C')->setAutoSize(true);
foreach (range('D', \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col - 1)) as $columnID) {
$sheet->getColumnDimension($columnID)
->setWidth(20);
}
}
private function generateSheetPerAreaIntervensi($sheet, $jsonData, $areaIntervensi, $title, $sheetName, $areaIntervensiId)
{
// get indikator header
$q = "
SELECT
ki.id, ki.nama, ki.id_area_intervensi, kai.nama as nama_ai
FROM
korsupgah_indikator ki
JOIN korsupgah_area_intervensi kai ON kai.ID = ki.id_area_intervensi
WHERE
kai.id = " . $areaIntervensiId . "
ORDER BY
ki.ID ASC;";
$arrIndikator = $this->rawQuery($q);
// sheet total ---------------------
$acrTitle = $this->acronym($sheetName);
$sheet->setTitle($acrTitle);
$sheet->setCellValue("A1", $title);
$sheet->getStyle("A1")->applyFromArray($this->styleArrayHeader);
#region header
$col = 1;
$row = 3;
$sheet->setCellValueByColumnAndRow($col++, $row, 'NO');
$sheet->setCellValueByColumnAndRow($col++, $row, 'WILAYAH PROVINSI');
$sheet->setCellValueByColumnAndRow($col++, $row, 'PEMERINTAH DAERAH');
$sheet->setCellValueByColumnAndRow($col++, $row, 'TOTAL CAPAIAN');
foreach ($arrIndikator as $key => $indikator) {
$sheet->setCellValueByColumnAndRow($col++, $row, strtoupper($indikator['nama']));
}
$headerRange = "A$row:" . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col - 1) . ($row);
$sheet->getStyle($headerRange)->applyFromArray($this->styleArray);
$sheet->getStyle($headerRange)->getAlignment()->setWrapText(true);
// generate content
$no = 1;
$row++;
$start = $row;
$maxCol = $col;
foreach ($jsonData as $key => $dataReport) {
$col = 1;
$sheet->setCellValueByColumnAndRow($col++, $row, $no++);
$sheet->setCellValueByColumnAndRow($col++, $row, $dataReport['provinsi']);
$sheet->setCellValueByColumnAndRow($col++, $row, $key);
$ntotal = isset($dataReport[$areaIntervensi]['total']) ?
$this->decimal($dataReport[$areaIntervensi]['total'], 2) : 0;
$sheet->setCellValueByColumnAndRow($col++, $row, $ntotal);
if (isset($dataReport[$areaIntervensi])) {
foreach ($arrIndikator as $key => $indikator) {
$nSubindikator = isset($dataReport[$areaIntervensi][$indikator['nama']]) ? $dataReport[$areaIntervensi][$indikator['nama']]['total'] : 0;
$sheet->setCellValueByColumnAndRow($col++, $row, $nSubindikator);
}
}
$row++;
if ($col > $maxCol)
$maxCol = $col;
}
// styling & Format
$range = "A$start:C" . ($row - 1);
$sheet->getStyle($range)->applyFromArray($this->styleArrayNormal);
$range = "D$start:" . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($maxCol - 1) . ($row - 1);
$sheet->getStyle($range)->applyFromArray($this->styleArrayNormal);
$sheet->getColumnDimension('B')->setAutoSize(true);
$sheet->getColumnDimension('C')->setAutoSize(true);
foreach (range('D', \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col - 1)) as $columnID) {
$sheet->getColumnDimension($columnID)
->setWidth(20);
}
}
private function generateSheetSubIndikator($sheet, $jsonData, $title, $sheetName)
{
// sheet total ---------------------
$sheet->setTitle($sheetName);
$sheet->setCellValue("A1", $title);
$sheet->getStyle("A1")->applyFromArray($this->styleArrayHeader);
#region header
$col = 1;
$row = 3;
$sheet->setCellValueByColumnAndRow($col++, $row, 'NO');
$sheet->setCellValueByColumnAndRow($col++, $row, 'WILAYAH PROVINSI');
$sheet->setCellValueByColumnAndRow($col++, $row, 'PEMERINTAH DAERAH');
$sheet->setCellValueByColumnAndRow($col++, $row, 'DANA DESA');
$sheet->setCellValueByColumnAndRow($col++, $row, 'AREA INTERVENSI');
$sheet->setCellValueByColumnAndRow($col++, $row, 'BOBOT AREA INTERVENSI');
$sheet->setCellValueByColumnAndRow($col++, $row, 'INDIKATOR');
$sheet->setCellValueByColumnAndRow($col++, $row, 'BOBOT INDIKATOR');
$sheet->setCellValueByColumnAndRow($col++, $row, 'SUB INDIKATOR');
$sheet->setCellValueByColumnAndRow($col++, $row, 'BOBOT SUB INDIKATOR');
$sheet->setCellValueByColumnAndRow($col++, $row, 'NILAI');
$sheet->setCellValueByColumnAndRow($col++, $row, 'NILAI VERIFIKASI');
$sheet->setCellValueByColumnAndRow($col++, $row, 'TGL LAPOR');
$headerRange = "A$row:" . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col - 1) . ($row);
$sheet->getStyle($headerRange)->applyFromArray($this->styleArray);
$sheet->getStyle($headerRange)->getAlignment()->setWrapText(true);
// generate content
$no = 1;
$row++;
$start = $row;
// echo '';
// print_r($jsonData);
// echo '';
// die;
foreach ($jsonData as $key => $dataReport) {
$col = 1;
$sheet->setCellValueByColumnAndRow($col++, $row, $no++);
$sheet->setCellValueByColumnAndRow($col++, $row, $dataReport['provinsi_name']);
$sheet->setCellValueByColumnAndRow($col++, $row, $dataReport['instansi']);
$sheet->setCellValueByColumnAndRow($col++, $row, $dataReport['dana_desa']);
$sheet->setCellValueByColumnAndRow($col++, $row, $dataReport['area_intervensi']);
$sheet->setCellValueByColumnAndRow($col++, $row, $dataReport['bobot_area_intervensi']);
$sheet->setCellValueByColumnAndRow($col++, $row, $dataReport['indikator']);
$sheet->setCellValueByColumnAndRow($col++, $row, $dataReport['bobot_indikator']);
$sheet->setCellValueByColumnAndRow($col++, $row, $dataReport['sub_indikator']);
$sheet->setCellValueByColumnAndRow($col++, $row, $dataReport['bobot_subindikator']);
$n = isset($dataReport['nilai']) ? $dataReport['nilai'] : 0;
$sheet->setCellValueByColumnAndRow($col++, $row, $n);
$nv = isset($dataReport['nilai_verifikasi']) ? $dataReport['nilai_verifikasi'] : 0;
$sheet->setCellValueByColumnAndRow($col++, $row, $nv);
$sheet->setCellValueByColumnAndRow($col++, $row, $dataReport['tgl_lapor']);
$row++;
}
// // styling & Format
$range = "A$start:C" . ($row - 1);
$sheet->getStyle($range)->applyFromArray($this->styleArrayNormal);
// $range = "D$start:" . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col - 1) . ($row - 1);
// $sheet->getStyle($range)->applyFromArray($this->styleArrayNormal);
$sheet->getColumnDimension('B')->setAutoSize(true);
$sheet->getColumnDimension('C')->setAutoSize(true);
$sheet->getColumnDimension('E')->setWidth(20);
$sheet->getColumnDimension('G')->setWidth(20);
$sheet->getColumnDimension('I')->setWidth(20);
$sheet->getColumnDimension('M')->setWidth(20);
}
private function generateSheetPerDirektorat($sheet, $table, $title, $sheetName)
{
// sheet data per direktorat ---------------------
$sheet->setTitle($sheetName);
$sheet->setCellValue("A1", $title);
$sheet->getStyle("A1")->applyFromArray($this->styleArrayHeader);
#region header
$col = 1;
$row = 3;
$sheet->setCellValueByColumnAndRow($col++, $row, 'DIREKTORAT');
// Kolom area-area
$firstDataRow = reset($table);
foreach ($firstDataRow as $area => $tmp) {
$sheet->setCellValueByColumnAndRow($col++, $row, strtoupper($area));
}
// Data
foreach ($table as $namaDirektorat => $direktorat) {
$row++;
$col = 1;
$sheet->setCellValueByColumnAndRow($col++, $row, strtoupper($namaDirektorat));
foreach ($direktorat as $area => $dataArea) {
$sheet->setCellValueByColumnAndRow($col++, $row, $dataArea['avg']); // rata-rata per direktorat
}
}
// styling & Format
$range = "A3:J3";
$sheet->getStyle($range)->applyFromArray($this->styleArray);
$range = "A4:J$row";
$sheet->getStyle($range)->applyFromArray($this->styleArrayNormal);
}
private function acronym($sentences)
{
if (strlen($sentences) < 5) {
return $sentences;
}
$arr_words = preg_split("/[\s,_-]+/", $sentences);
$acronym = "";
foreach ($arr_words as $w) {
if (strlen($w) > 0)
$acronym .= $w[0];
}
return $acronym;
}
private function decimal($number, $after)
{
return number_format((float) $number, $after, '.', '');
}
public function mcspVerifierReport($year)
{
if ((int) $year < 2025) {
throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data hanya tersedia untuk MCSP 2025');
}
$params = $this->request->get();
$validatedParams = $this->validate($params, [
'all_area' => ['validators' => []],
]);
if (isset($validatedParams['all_area'])) {
$all_area = $validatedParams['all_area'] === 'true' ? true : false;
} else {
$all_area = false;
}
try {
$mcpService = new McpService();
$result = $mcpService->get("/api/dashboard/verifier/$year", [
'all_area' => $all_area ?? null
]);
if (!$result->success || $result->data === null) {
throw new Exception("Failed to fetch data from MCP 2025 service.");
}
if ($all_area) {
return $this->exportReportPerArea($result);
} else {
return $this->exportTotalReport($result);
}
} catch (Exception $e) {
throw $e;
}
}
private function exportTotalReport($result)
{
// Title
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->createSheet(0);
$title = 'Progress Pelaporan dan Verifikasi MCSP 2025';
$sheet->setCellValue("A1", $title);
$sheet->getStyle("A1")->applyFromArray($this->styleArrayHeader);
date_default_timezone_set('Asia/Jakarta');
$sheet->setCellValue("A2", date("F j Y, H:i:s") . " WIB");
// Header
#region header
$col = 1;
$row = 4;
$header = ['No', 'Direktorat', 'Provinsi', 'Pemerintah Daerah', 'Score', 'Σ dokumen', 'Σ final', 'Σ belum final', 'Σ diunggah', 'Σ belum diunggah', 'Σ diterima', 'Σ ditolak', 'Σ belum diverifikasi'];
foreach ($header as $headerTitle) {
$sheet->setCellValueByColumnAndRow($col++, $row, strtoupper($headerTitle));
}
$no = 1;
foreach ($result->data as $item) {
$row++;
$col = 1;
$sheet->setCellValueByColumnAndRow($col++, $row, $no++);
$sheet->setCellValueByColumnAndRow($col++, $row, $item->instansi->direktorat);
$sheet->setCellValueByColumnAndRow($col++, $row, $item->instansi->provinsi->name);
$sheet->setCellValueByColumnAndRow($col++, $row, $item->instansi->name);
$sheet->setCellValueByColumnAndRow($col++, $row, $item->score);
$sheet->setCellValueByColumnAndRow($col++, $row, $item->attachmentSummary->max);
$sheet->setCellValueByColumnAndRow($col++, $row, $item->attachmentSummary->locked);
$sheet->setCellValueByColumnAndRow($col++, $row, $item->attachmentSummary->rest);
$sheet->setCellValueByColumnAndRow($col++, $row, $item->attachmentSummary->uploaded);
$sheet->setCellValueByColumnAndRow($col++, $row, $item->attachmentSummary->unuploaded);
$sheet->setCellValueByColumnAndRow($col++, $row, $item->attachmentSummary->accepted);
$sheet->setCellValueByColumnAndRow($col++, $row, $item->attachmentSummary->rejected);
$sheet->setCellValueByColumnAndRow($col++, $row, $item->attachmentSummary->unverified);
}
// generate excel
$spreadsheet->setActiveSheetIndex(0);
$writer = new Xlsx($spreadsheet);
$now = date("YmdHis");
$filename = $now . "-Progress_MCP.xlsx";
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="' . $filename . '"');
return $writer->save('php://output');
}
private function exportReportPerArea($result)
{
// Title
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->createSheet(0);
$title = 'Progress Pelaporan dan Verifikasi MCSP 2025 Per Area';
$sheet->setCellValue("A1", $title);
// Merge title across multiple columns (A1 to M1 for example)
$sheet->mergeCells('A1:M1');
$sheet->getStyle("A1")->applyFromArray($this->styleArrayHeader);
date_default_timezone_set('Asia/Jakarta');
$sheet->setCellValue("A2", date("F j Y, H:i:s") . " WIB");
$sheet->mergeCells('A2:M2');
// Area names mapping (based on typical MCSP structure)
$areaNames = [
0 => 'Perencanaan',
1 => 'Anggaran',
2 => 'PBJ',
3 => 'Yanblik',
4 => 'M-ASN',
5 => 'BMD',
6 => 'OPD',
7 => 'APIP'
];
// Create header structure
$col = 1;
$row = 4;
// Basic info columns
$sheet->setCellValueByColumnAndRow($col, $row, 'No');
$sheet->mergeCellsByColumnAndRow($col, $row, $col, $row + 1);
$col++;
$sheet->setCellValueByColumnAndRow($col, $row, 'Direktorat');
$sheet->mergeCellsByColumnAndRow($col, $row, $col, $row + 1);
$col++;
$sheet->setCellValueByColumnAndRow($col, $row, 'Provinsi');
$sheet->mergeCellsByColumnAndRow($col, $row, $col, $row + 1);
$col++;
$sheet->setCellValueByColumnAndRow($col, $row, 'Pemerintah Daerah');
$sheet->mergeCellsByColumnAndRow($col, $row, $col, $row + 1);
$col++;
$sheet->setCellValueByColumnAndRow($col, $row, 'Score');
$sheet->mergeCellsByColumnAndRow($col, $row, $col, $row + 1);
$col++;
// Total columns
$totalStartCol = $col;
$sheet->setCellValueByColumnAndRow($col, $row, 'Total');
$sheet->mergeCellsByColumnAndRow($col, $row, $col + 4, $row);
$subHeaders = ['Σ dokumen', 'Σ diunggah', 'Σ belum diunggah', '% Terjawab', 'Σ belum diverifikasi'];
foreach ($subHeaders as $subHeader) {
$sheet->setCellValueByColumnAndRow($col, $row + 1, $subHeader);
$col++;
}
// Area columns
foreach ($areaNames as $areaIndex => $areaName) {
$areaStartCol = $col;
$sheet->setCellValueByColumnAndRow($col, $row, $areaName);
$sheet->mergeCellsByColumnAndRow($col, $row, $col + 4, $row);
foreach ($subHeaders as $subHeader) {
$sheet->setCellValueByColumnAndRow($col, $row + 1, $subHeader);
$col++;
}
}
// Style headers
$headerRange1 = "A$row:" . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col - 1) . $row;
$headerRange2 = "A" . ($row + 1) . ":" . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col - 1) . ($row + 1);
$sheet->getStyle($headerRange1)->applyFromArray($this->styleArray);
$sheet->getStyle($headerRange2)->applyFromArray($this->styleArray);
$sheet->getStyle($headerRange1)->getAlignment()->setWrapText(true);
$sheet->getStyle($headerRange2)->getAlignment()->setWrapText(true);
// Data rows
$dataRow = $row + 2;
$no = 1;
foreach ($result->data as $item) {
$col = 1;
// Basic info
$sheet->setCellValueByColumnAndRow($col++, $dataRow, $no++);
$sheet->setCellValueByColumnAndRow($col++, $dataRow, $item->instansi->direktorat);
$sheet->setCellValueByColumnAndRow($col++, $dataRow, $item->instansi->provinsi->name);
$sheet->setCellValueByColumnAndRow($col++, $dataRow, $item->instansi->name);
$sheet->setCellValueByColumnAndRow($col++, $dataRow, $item->score);
// Total summary
$totalPercentage = $item->attachmentSummary->max > 0 ? round(($item->attachmentSummary->uploaded / $item->attachmentSummary->max) * 100, 2) : 0;
$sheet->setCellValueByColumnAndRow($col++, $dataRow, $item->attachmentSummary->max);
$sheet->setCellValueByColumnAndRow($col++, $dataRow, $item->attachmentSummary->uploaded);
$sheet->setCellValueByColumnAndRow($col++, $dataRow, $item->attachmentSummary->unuploaded);
$sheet->setCellValueByColumnAndRow($col++, $dataRow, $totalPercentage . '%');
$sheet->setCellValueByColumnAndRow($col++, $dataRow, $item->attachmentSummary->unverified);
// Area-specific data
foreach ($areaNames as $areaIndex => $areaName) {
if (isset($item->attachmentSummary_per_area[$areaIndex])) {
$areaData = $item->attachmentSummary_per_area[$areaIndex];
$areaPercentage = $areaData->max > 0 ? round(($areaData->uploaded / $areaData->max) * 100, 2) : 0;
$sheet->setCellValueByColumnAndRow($col++, $dataRow, $areaData->max);
$sheet->setCellValueByColumnAndRow($col++, $dataRow, $areaData->uploaded);
$sheet->setCellValueByColumnAndRow($col++, $dataRow, $areaData->unuploaded);
$sheet->setCellValueByColumnAndRow($col++, $dataRow, $areaPercentage . '%');
$sheet->setCellValueByColumnAndRow($col++, $dataRow, $areaData->unverified);
} else {
// Fill with zeros if area data doesn't exist
$sheet->setCellValueByColumnAndRow($col++, $dataRow, 0);
$sheet->setCellValueByColumnAndRow($col++, $dataRow, 0);
$sheet->setCellValueByColumnAndRow($col++, $dataRow, 0);
$sheet->setCellValueByColumnAndRow($col++, $dataRow, '0%');
$sheet->setCellValueByColumnAndRow($col++, $dataRow, 0);
}
}
$dataRow++;
}
// Style data rows
$dataRange = "A" . ($row + 2) . ":" . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col - 1) . ($dataRow - 1);
$sheet->getStyle($dataRange)->applyFromArray($this->styleArrayNormal);
// Auto-size columns
foreach (range('A', 'E') as $columnID) {
$sheet->getColumnDimension($columnID)->setAutoSize(true);
}
$spreadsheet->setActiveSheetIndex(0);
$writer = new Xlsx($spreadsheet);
$now = date("YmdHis");
$filename = $now . "-Progress_MCP_Per_Area.xlsx";
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="' . $filename . '"');
return $writer->save('php://output');
}
public function mcspIndikatorReport($year)
{
if ((int) $year < 2025) {
throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data hanya tersedia untuk MCSP 2025');
}
try {
$mcpService = new McpService();
$result = $mcpService->get("/api/report/indikator/$year");
if (!$result->success || $result->data === null) {
throw new Exception("Failed to fetch data from MCP 2025 service.");
}
// populate the data
$areaList = [];
for ($i = 2; $i <= 9; $i++) {
$areaList[$i] = [
'name' => '',
'indikator' => [],
'instansi' => []
];
}
$data = $result->data;
foreach ($data as $dataRow) {
$instansi = $dataRow->instansi;
$indikatorScores = $dataRow->indikator_scores;
foreach ($indikatorScores as $score) {
$areaId = $score->area->_id;
$areaList[$areaId]['name'] = $score->area->nama;
try {
if (array_key_exists($instansi->id, $areaList[$areaId]['instansi'])) {
$areaList[$areaId]['instansi'][$instansi->id]['scores'][] = [
'_id' => $score->_id,
'score' => $score->score
];
} else {
$areaList[$areaId]['instansi'][$instansi->id] = [
'name' => $instansi->name,
'provinsi' => $instansi->provinsi->name,
'direktorat' => $instansi->direktorat,
'satgas' => "{$instansi->direktorat}.{$instansi->satgas}",
'scores' => [
[
'_id' => $score->_id,
'score' => $score->score
]
]
];
}
if (!array_key_exists($score->_id, $areaList[$areaId]['indikator'])) {
$areaList[$areaId]['indikator'][$score->_id] = [
'id' => $score->_id,
'name' => $score->nama
];
}
} catch (Exception $e) {
$this->logger->error("mcspIndikatorReport error: " . $e->getMessage());
}
}
}
return $this->createMcspIndikatorSpreadsheet($areaList);
} catch (Exception $e) {
throw $e;
}
}
/**
* Create Spreadsheet for MCSP Indikator
* @param mixed $areaList
*/
private function createMcspIndikatorSpreadsheet($areaList)
{
ini_set('memory_limit', '2048M');
$spreadsheet = new Spreadsheet();
$sheetIndex = 0;
foreach ($areaList as $area) {
$sheet = $spreadsheet->createSheet($sheetIndex);
$sheet->setTitle($area['name']);
$sheet->setCellValue("A1", "Nilai MCSP Per Indikator Area {$area['name']}");
$sheet->getStyle("A1")->applyFromArray($this->styleArrayHeader);
date_default_timezone_set('Asia/Jakarta');
$sheet->setCellValue("A2", date("F j Y, H:i:s") . " WIB");
// header
$header = ['Direktorat', 'Satgas', 'Provinsi', 'Instansi'];
$indikatorNo = 1;
foreach ($area['indikator'] as $indikator) {
$header[] = "$indikatorNo {$indikator['name']}";
$indikatorNo++;
}
$col = 1;
$row = 4;
foreach ($header as $headerTitle) {
$sheet->setCellValueByColumnAndRow($col++, $row, $headerTitle);
}
// body
foreach ($area['instansi'] as $instansi) {
$row++;
$col = 1;
$sheet->setCellValueByColumnAndRow($col++, $row, $instansi['direktorat']);
$sheet->setCellValueByColumnAndRow($col++, $row, $instansi['satgas']);
$sheet->setCellValueByColumnAndRow($col++, $row, $instansi['provinsi']);
$sheet->setCellValueByColumnAndRow($col++, $row, $instansi['name']);
foreach ($instansi['scores'] as $score) {
$sheet->setCellValueByColumnAndRow($col++, $row, $score['score']);
}
}
$sheetIndex++;
}
$writer = new Xlsx($spreadsheet);
$now = date("YmdHis");
$filename = $now . "-Indikator_MCSP_2025.xlsx";
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="' . $filename . '"');
return $writer->save('php://output');
}
public function mcspDownloadVerifierReport($year)
{
$tempFilePath = null;
try {
$baseUrl = getenv('MCP_2025_SERVICE_URL');
$url = $baseUrl . "/api/report/progress/$year";
$client = new \GuzzleHttp\Client([
'timeout' => 300, // Timeout 5 menit
'connect_timeout' => 300,
]);
$response = $client->get($url);
if ($response->getStatusCode() !== 200) {
throw new CustomErrorException("Microservice returned HTTP " . $response->getStatusCode(), BaseResponse::GUZZLE_ERROR);
}
$fileContent = $response->getBody()->getContents();
if (empty($fileContent)) {
throw new CustomErrorException("Empty response from microservice", BaseResponse::DATA_NOT_AVAILABLE);
}
// Create temp file
$tempDir = sys_get_temp_dir();
$tempFileName = 'verifier_report_' . $year . '_' . uniqid() . '.xlsx';
$tempFilePath = $tempDir . DIRECTORY_SEPARATOR . $tempFileName;
if (file_put_contents($tempFilePath, $fileContent) === false) {
throw new CustomErrorException("Failed to create temporary file", BaseResponse::UNEXPECTED_ERROR);
}
if (!file_exists($tempFilePath) || filesize($tempFilePath) === 0) {
throw new CustomErrorException("Temporary file creation failed or empty", BaseResponse::UNEXPECTED_ERROR);
}
if (ob_get_level()) {
ob_clean();
}
$filename = "Rekapitulasi Verifikasi MCSP $year.xlsx";
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="' . $filename . '"');
header('Content-Length: ' . filesize($tempFilePath));
header('Cache-Control: no-cache, must-revalidate');
header('Pragma: public');
readfile($tempFilePath);
unlink($tempFilePath);
exit;
} catch (\GuzzleHttp\Exception\ConnectException $e) {
throw new CustomErrorException("Cannot connect to microservice at URL: " . ($url ?? 'unknown') . ". Error: " . $e->getMessage(), BaseResponse::DATA_NOT_AVAILABLE);
} catch (\GuzzleHttp\Exception\RequestException $e) {
$statusCode = $e->hasResponse() ? $e->getResponse()->getStatusCode() : 0;
$responseBody = $e->hasResponse() ? $e->getResponse()->getBody()->getContents() : '';
throw new CustomErrorException("Microservice request failed (HTTP $statusCode): " . $e->getMessage() . ($responseBody ? " Response: " . substr($responseBody, 0, 200) : ""), BaseResponse::GUZZLE_ERROR);
} catch (\Exception $e) {
if ($e instanceof CustomErrorException) {
throw $e;
}
throw new CustomErrorException("Failed to download verifier report: " . $e->getMessage(), BaseResponse::UNEXPECTED_ERROR);
} finally {
// Ensure temp file is cleaned up even if there's an error
if ($tempFilePath && file_exists($tempFilePath)) {
unlink($tempFilePath);
}
}
}
public function mcspDownloadFilledAttachmentReport($year)
{
if ((int) $year < 2025) {
throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data hanya tersedia untuk MCSP 2025');
}
try {
$mcpService = new McpService();
$result = $mcpService->get("/api/report/filled-attachment-count/$year");
if (!$result->success || $result->data === null) {
throw new Exception("Failed to fetch data from MCP 2025 service.");
}
return $this->createFilledAttachmentSpreadsheet($result->data, $year);
} catch (Exception $e) {
throw $e;
}
}
private function createFilledAttachmentSpreadsheet($hierarchyData, $year)
{
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle("Filled Count Report");
$sheet->setCellValue("A1", "Rekapitulasi Filled Count MCSP $year");
$sheet->getStyle("A1")->applyFromArray($this->styleArrayHeader);
$sheet->mergeCells('A1:J1');
date_default_timezone_set('Asia/Jakarta');
$sheet->setCellValue("A2", date("F j Y, H:i:s") . " WIB");
$sheet->mergeCells('A2:J2');
// Extract all attachments from the hierarchy
$attachmentsData = $this->extractAttachmentsFromJsonHierarchy($hierarchyData);
// Header
$header = ['No', 'Area', 'Aspek', 'Sasaran', 'Indikator', 'Penilaian', 'Tipe Instansi', 'Dokumen', 'Instansi yang Mengisi', 'Total Instansi'];
$row = 4;
$col = 1;
foreach ($header as $headerTitle) {
$sheet->setCellValueByColumnAndRow($col++, $row, $headerTitle);
}
// Apply header styling
$headerRange = "A$row:" . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col - 1) . $row;
$sheet->getStyle($headerRange)->applyFromArray($this->styleArray);
// Data rows with merging for duplicate values
$no = 1;
$startRow = $row + 1;
$mergeTracker = [
'area' => ['value' => '', 'startRow' => $startRow],
'aspek' => ['value' => '', 'startRow' => $startRow],
'sasaran' => ['value' => '', 'startRow' => $startRow],
'indikator' => ['value' => '', 'startRow' => $startRow],
'penilaian' => ['value' => '', 'startRow' => $startRow],
'instansi_type' => ['value' => '', 'startRow' => $startRow]
];
foreach ($attachmentsData as $attachment) {
$row++;
$col = 1;
// No column
$sheet->setCellValueByColumnAndRow($col++, $row, $no++);
// Area column (B)
$this->handleMergeCell($sheet, $mergeTracker['area'], $attachment['area'], $col++, $row, 'B');
// Aspek column (C)
$this->handleMergeCell($sheet, $mergeTracker['aspek'], $attachment['aspek'], $col++, $row, 'C');
// Sasaran column (D)
$this->handleMergeCell($sheet, $mergeTracker['sasaran'], $attachment['sasaran'], $col++, $row, 'D');
// Indikator column (E)
$this->handleMergeCell($sheet, $mergeTracker['indikator'], $attachment['indikator'], $col++, $row, 'E');
// Penilaian column (F)
$this->handleMergeCell($sheet, $mergeTracker['penilaian'], $attachment['penilaian'], $col++, $row, 'F');
// Tipe Instansi column (G)
$this->handleMergeCell($sheet, $mergeTracker['instansi_type'], $attachment['instansi_type'], $col++, $row, 'G');
// Dokumen column (H)
$sheet->setCellValueByColumnAndRow($col++, $row, $attachment['label']);
// Instansi yang Mengisi column (I)
$sheet->setCellValueByColumnAndRow($col++, $row, $attachment['filled_count']);
// Total Instansi column (J)
$sheet->setCellValueByColumnAndRow($col++, $row, $attachment['total_count']);
}
// Finalize any remaining merges
$this->finalizeMerges($sheet, $mergeTracker, $row);
// Style data rows
if ($row > 4) {
$dataRange = "A5:" . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col - 1) . $row;
$sheet->getStyle($dataRange)->applyFromArray($this->styleArrayNormal);
}
// Set fixed column widths
$sheet->getColumnDimension('A')->setWidth(5); // No
$sheet->getColumnDimension('B')->setWidth(18); // Area
$sheet->getColumnDimension('C')->setWidth(25); // Aspek
$sheet->getColumnDimension('D')->setWidth(22); // Sasaran
$sheet->getColumnDimension('E')->setWidth(30); // Indikator
$sheet->getColumnDimension('F')->setWidth(35); // Penilaian
$sheet->getColumnDimension('G')->setWidth(20); // Tipe Instansi
$sheet->getColumnDimension('H')->setWidth(60); // Dokumen (Attachment Label)
$sheet->getColumnDimension('I')->setWidth(15); // Instansi yang Mengisi (Filled Count)
$sheet->getColumnDimension('J')->setWidth(15); // Total Instansi (Total Count)
$writer = new Xlsx($spreadsheet);
$now = date("YmdHis");
$filename = $now . "-Filled_Count_MCSP_$year.xlsx";
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="' . $filename . '"');
return $writer->save('php://output');
}
private function handleMergeCell($sheet, &$tracker, $value, $col, $row, $columnLetter)
{
if ($tracker['value'] !== $value) {
// Finalize previous merge if needed
if ($tracker['startRow'] < $row - 1) {
$sheet->mergeCells("{$columnLetter}{$tracker['startRow']}:{$columnLetter}" . ($row - 1));
// Center align merged cells
$sheet->getStyle("{$columnLetter}{$tracker['startRow']}:{$columnLetter}" . ($row - 1))
->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
}
// Set new value and start new tracking
$sheet->setCellValueByColumnAndRow($col, $row, $value);
$tracker['value'] = $value;
$tracker['startRow'] = $row;
}
// If value is the same, don't set cell value (it will be merged)
}
private function finalizeMerges($sheet, $mergeTracker, $lastRow)
{
$columns = ['B' => 'area', 'C' => 'aspek', 'D' => 'sasaran', 'E' => 'indikator', 'F' => 'penilaian'];
foreach ($columns as $columnLetter => $key) {
if ($mergeTracker[$key]['startRow'] < $lastRow) {
$sheet->mergeCells("{$columnLetter}{$mergeTracker[$key]['startRow']}:{$columnLetter}{$lastRow}");
// Center align merged cells
$sheet->getStyle("{$columnLetter}{$mergeTracker[$key]['startRow']}:{$columnLetter}{$lastRow}")
->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
}
}
}
private function extractAttachmentsFromJsonHierarchy($hierarchyData, $areaName = '', $aspekName = '', $sasaranName = '', $indikatorName = '', $penilaianName = '', $areaCounter = 0, $aspekCounter = 0, $sasaranCounter = 0, $indikatorCounter = 0, $penilaianCounter = 0)
{
$attachments = [];
// Convert to object if it's an array
if (is_array($hierarchyData)) {
$hierarchyData = (object) $hierarchyData;
}
// Extract attachments from current node
if (isset($hierarchyData->attachments) && is_array($hierarchyData->attachments)) {
foreach ($hierarchyData->attachments as $attachment) {
if (is_array($attachment)) {
$attachment = (object) $attachment;
}
$attachments[] = [
'area' => $areaName,
'aspek' => $aspekName,
'sasaran' => $sasaranName,
'indikator' => $indikatorName,
'penilaian' => $penilaianName,
'label' => $attachment->label ?? '',
'uid' => $attachment->uid ?? '',
'filled_count' => $attachment->filled_count ?? 0,
'total_count' => $attachment->total_count ?? 0,
];
}
}
// Process children recursively
if (isset($hierarchyData->children) && is_array($hierarchyData->children)) {
$currentAreaCounter = $areaCounter;
$currentAspekCounter = $aspekCounter;
$currentSasaranCounter = $sasaranCounter;
$currentIndikatorCounter = $indikatorCounter;
$currentPenilaianCounter = $penilaianCounter;
foreach ($hierarchyData->children as $child) {
if (is_array($child)) {
$child = (object) $child;
}
$childAttachments = [];
$level = $child->level ?? 0;
switch ($level) {
case 1: // Area level (Perencanaan, etc.)
$currentAreaCounter++;
$areaWithIdentifier = $currentAreaCounter . '. Area ' . ($child->nama ?? '');
$childAttachments = $this->extractAttachmentsFromJsonHierarchy(
$child,
$areaWithIdentifier,
'',
'',
'',
'',
$currentAreaCounter,
0, // Reset aspek counter for new area
0, // Reset sasaran counter for new area
0, // Reset indikator counter for new area
0 // Reset penilaian counter for new area
);
break;
case 2: // Aspek level (Perencanaan Pembangunan Daerah, etc.)
$currentAspekCounter++;
$aspekWithIdentifier = $areaCounter . '.' . $currentAspekCounter . '. Aspek ' . ($child->nama ?? '');
$childAttachments = $this->extractAttachmentsFromJsonHierarchy(
$child,
$areaName,
$aspekWithIdentifier,
'',
'',
'',
$areaCounter,
$currentAspekCounter,
0, // Reset sasaran counter for new aspek
0, // Reset indikator counter for new aspek
0 // Reset penilaian counter for new aspek
);
break;
case 3: // Sasaran level (Transparansi, etc.)
$currentSasaranCounter++;
$sasaranWithIdentifier = $areaCounter . '.' . $aspekCounter . '.' . $currentSasaranCounter . '. Sasaran ' . ($child->nama ?? '');
$childAttachments = $this->extractAttachmentsFromJsonHierarchy(
$child,
$areaName,
$aspekName,
$sasaranWithIdentifier,
'',
'',
$areaCounter,
$aspekCounter,
$currentSasaranCounter,
0, // Reset indikator counter for new sasaran
0 // Reset penilaian counter for new sasaran
);
break;
case 4: // Indikator level
$currentIndikatorCounter++;
$indikatorWithIdentifier = $areaCounter . '.' . $aspekCounter . '.' . $sasaranCounter . '.' . $currentIndikatorCounter . '. Indikator ' . ($child->nama ?? '');
$childAttachments = $this->extractAttachmentsFromJsonHierarchy(
$child,
$areaName,
$aspekName,
$sasaranName,
$indikatorWithIdentifier,
'',
$areaCounter,
$aspekCounter,
$sasaranCounter,
$currentIndikatorCounter,
0 // Reset penilaian counter for new indikator
);
break;
case 5: // Penilaian level (special case for nama = null)
$currentPenilaianCounter++;
$penilaianName = '';
if ($child->nama === null || $child->nama === '') {
// Use hierarchical identifier for Penilaian
$penilaianName = 'Penilaian ' . $areaCounter . '.' . $aspekCounter . '.' . $sasaranCounter . '.' . $indikatorCounter . '.' . $currentPenilaianCounter;
} else {
$penilaianName = $child->nama;
}
$childAttachments = $this->extractAttachmentsFromJsonHierarchy(
$child,
$areaName,
$aspekName,
$sasaranName,
$indikatorName,
$penilaianName,
$areaCounter,
$aspekCounter,
$sasaranCounter,
$indikatorCounter,
$currentPenilaianCounter
);
break;
default: // Deeper levels
$childAttachments = $this->extractAttachmentsFromJsonHierarchy(
$child,
$areaName,
$aspekName,
$sasaranName,
$indikatorName,
$penilaianName,
$areaCounter,
$aspekCounter,
$sasaranCounter,
$indikatorCounter,
$penilaianCounter
);
break;
}
$attachments = array_merge($attachments, $childAttachments);
}
}
return $attachments;
}
public function mcspDownloadFilledAttachmentReportPerInstansi($year, $instansiId)
{
if ((int) $year < 2025) {
throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data hanya tersedia untuk MCSP 2025');
}
try {
$mcpService = new McpService();
$result = $mcpService->get("/api/report/filled-attachment-count-per-instansi/$year/$instansiId");
if (!$result->success || $result->data === null) {
throw new Exception("Failed to fetch data from MCP 2025 service.");
}
$filteredData = $this->filterQuestionByInstansiType($result->data);
return $this->createAttachmentReportSpreadsheet($filteredData, $year);
} catch (Exception $e) {
throw $e;
}
}
private function filterQuestionByInstansiType($data)
{
$hierarchyData = json_decode(json_encode($data), true);
$instansi = $hierarchyData['instansi'];
// echo $instansi["type"]."\n";
foreach ($hierarchyData['children'] as $areaIndex => $area) {
foreach ($area['children'] as $sasaranIndex => $sasaran) {
foreach ($sasaran['children'] as $aspekIndex => $aspek) {
foreach ($aspek['children'] as $indikatorIndex => $indikator) {
for ($questionIndex = sizeof($indikator['children']) - 1; $questionIndex >= 0; $questionIndex--) {
if ($indikator['children'][$questionIndex]['instansi_type'] !== null && $indikator['children'][$questionIndex]['instansi_type'] !== (int)$instansi['type']) {
array_splice($hierarchyData['children'][$areaIndex]['children'][$sasaranIndex]['children'][$aspekIndex]['children'][$indikatorIndex]['children'], $questionIndex, 1);
} else {
// remove instansi_type
unset($hierarchyData['children'][$areaIndex]['children'][$sasaranIndex]['children'][$aspekIndex]['children'][$indikatorIndex]['children'][$questionIndex]['instansi_type']);
}
}
}
}
}
}
// print_r($hierarchyData); die;
return json_decode(json_encode($hierarchyData));
}
private function createAttachmentReportSpreadsheet($hierarchyData, $year)
{
// print_r($hierarchyData); die;
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle("MCSP Report");
$sheet->setCellValue("A1", "Rekapitulasi MCSP $year");
$sheet->getStyle("A1")->applyFromArray($this->styleArrayHeader);
$sheet->mergeCells('A1:K1');
date_default_timezone_set('Asia/Jakarta');
$sheet->setCellValue("A2", date("F j Y, H:i:s") . " WIB");
$sheet->mergeCells('A2:K2');
// Extract all attachments from the hierarchy
$attachmentsData = $this->extractAttachments($hierarchyData);
// Header
$header = ['No', 'Area', 'Aspek', 'Sasaran', 'Indikator', 'Penilaian', 'Dokumen', 'Status Unggah Dokumen', 'Keterangan', 'Status Penilaian'];
$row = 4;
$col = 1;
foreach ($header as $headerTitle) {
$sheet->setCellValueByColumnAndRow($col++, $row, $headerTitle);
}
// Apply header styling
$headerRange = "A$row:" . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col - 1) . $row;
$sheet->getStyle($headerRange)->applyFromArray($this->styleArray);
// Data rows with merging for duplicate values
$no = 1;
$startRow = $row + 1;
$mergeTracker = [
'area' => ['value' => '', 'startRow' => $startRow],
'aspek' => ['value' => '', 'startRow' => $startRow],
'sasaran' => ['value' => '', 'startRow' => $startRow],
'indikator' => ['value' => '', 'startRow' => $startRow],
'penilaian' => ['value' => '', 'startRow' => $startRow]
];
foreach ($attachmentsData as $attachment) {
$row++;
$col = 1;
// No column
$sheet->setCellValueByColumnAndRow($col++, $row, $no++);
// Area column (B)
$this->handleMergeCell($sheet, $mergeTracker['area'], $attachment['area'], $col++, $row, 'B');
// Aspek column (C)
$this->handleMergeCell($sheet, $mergeTracker['aspek'], $attachment['aspek'], $col++, $row, 'C');
// Sasaran column (D)
$this->handleMergeCell($sheet, $mergeTracker['sasaran'], $attachment['sasaran'], $col++, $row, 'D');
// Indikator column (E)
$this->handleMergeCell($sheet, $mergeTracker['indikator'], $attachment['indikator'], $col++, $row, 'E');
// Penilaian column (F)
$this->handleMergeCell($sheet, $mergeTracker['penilaian'], $attachment['penilaian'], $col++, $row, 'F');
// Dokumen column (G)
$sheet->setCellValueByColumnAndRow($col++, $row, $attachment['label'] ?? '');
// Status Upload column (H)
$sheet->setCellValueByColumnAndRow($col++, $row, $attachment['status_upload'] ?? '');
// Catatan column (I)
$sheet->setCellValueByColumnAndRow($col++, $row, $attachment['note'] ?? '');
// Status Scoring column (J)
$sheet->setCellValueByColumnAndRow($col++, $row, $attachment['status_scoring'] ?? '');
}
// Finalize any remaining merges
$this->finalizeMerges($sheet, $mergeTracker, $row);
// Style data rows
if ($row > 4) {
$dataRange = "A5:" . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col - 1) . $row;
$sheet->getStyle($dataRange)->applyFromArray($this->styleArrayNormal);
}
// Set fixed column widths
$sheet->getColumnDimension('A')->setWidth(5); // No
$sheet->getColumnDimension('B')->setWidth(18); // Area
$sheet->getColumnDimension('C')->setWidth(25); // Aspek
$sheet->getColumnDimension('D')->setWidth(22); // Sasaran
$sheet->getColumnDimension('E')->setWidth(30); // Indikator
$sheet->getColumnDimension('F')->setWidth(35); // Penilaian
$sheet->getColumnDimension('G')->setWidth(60); // Dokumen (Attachment Label)
$sheet->getColumnDimension('H')->setWidth(25); // Status Unggah Dokumen
$sheet->getColumnDimension('I')->setWidth(20); // Keterangan
$sheet->getColumnDimension('J')->setWidth(35); // Status Penilaian
$writer = new Xlsx($spreadsheet);
$now = date("YmdHis");
$filename = $now . "-MCSP_Recap_$year.xlsx";
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="' . $filename . '"');
return $writer->save('php://output');
}
// private function finalizeMerges($sheet, $mergeTracker, $lastRow)
// {
// $columns = ['B' => 'area', 'C' => 'aspek', 'D' => 'sasaran', 'E' => 'indikator', 'F' => 'penilaian', 'G' => 'instansi_type'];
// foreach ($columns as $columnLetter => $key) {
// if ($mergeTracker[$key]['startRow'] < $lastRow) {
// $sheet->mergeCells("{$columnLetter}{$mergeTracker[$key]['startRow']}:{$columnLetter}{$lastRow}");
// // Center align merged cells
// $sheet->getStyle("{$columnLetter}{$mergeTracker[$key]['startRow']}:{$columnLetter}{$lastRow}")
// ->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
// }
// }
// }
private function extractAttachments($hierarchyData, $areaName = '', $aspekName = '', $sasaranName = '', $indikatorName = '', $penilaianName = '', $instansiType = null, $areaCounter = 0, $aspekCounter = 0, $sasaranCounter = 0, $indikatorCounter = 0, $penilaianCounter = 0)
{
$attachments = [];
// Convert to object if it's an array
if (is_array($hierarchyData)) {
$hierarchyData = (object) $hierarchyData;
}
// Extract status_scoring from current node (if it exists)
$status_scoring = $hierarchyData->status_scoring ?? '';
// Extract attachments from current node
if (isset($hierarchyData->attachments) && is_array($hierarchyData->attachments)) {
foreach ($hierarchyData->attachments as $attachment) {
if (is_array($attachment)) {
$attachment = (object) $attachment;
}
$tipe_instansi = '-';
if ($instansiType !== null) {
switch ($instansiType) {
case 1:
$tipe_instansi = 'Provinsi';
break;
case 2:
$tipe_instansi = 'Kabupaten/Kota';
break;
}
}
$attachments[] = [
'area' => $areaName,
'aspek' => $aspekName,
'sasaran' => $sasaranName,
'indikator' => $indikatorName,
'penilaian' => $penilaianName,
'instansi_type' => $tipe_instansi,
'label' => $attachment->label ?? '',
'uid' => $attachment->uid ?? '',
'status_upload' => $attachment->status_upload ?? '',
'note' => $attachment->note ?? '',
'status_scoring' => $status_scoring,
];
}
}
// Process children recursively
if (isset($hierarchyData->children) && is_array($hierarchyData->children)) {
$currentAreaCounter = $areaCounter;
$currentAspekCounter = $aspekCounter;
$currentSasaranCounter = $sasaranCounter;
$currentIndikatorCounter = $indikatorCounter;
$currentPenilaianCounter = $penilaianCounter;
foreach ($hierarchyData->children as $child) {
if (is_array($child)) {
$child = (object) $child;
}
$childAttachments = [];
$level = $child->level ?? 0;
switch ($level) {
case 1: // Area level (Perencanaan, etc.)
$currentAreaCounter++;
$areaWithIdentifier = $currentAreaCounter . '. Area ' . ($child->nama ?? '');
$childAttachments = $this->extractAttachments(
$child,
$areaWithIdentifier,
'',
'',
'',
'',
null, // No instansi_type at area level
$currentAreaCounter,
0, // Reset aspek counter for new area
0, // Reset sasaran counter for new area
0, // Reset indikator counter for new area
0 // Reset penilaian counter for new area
);
break;
case 2: // Aspek level (Perencanaan Pembangunan Daerah, etc.)
$currentAspekCounter++;
$aspekWithIdentifier = $areaCounter . '.' . $currentAspekCounter . '. Aspek ' . ($child->nama ?? '');
$childAttachments = $this->extractAttachments(
$child,
$areaName,
$aspekWithIdentifier,
'',
'',
'',
null, // No instansi_type at aspek level
$areaCounter,
$currentAspekCounter,
0, // Reset sasaran counter for new aspek
0, // Reset indikator counter for new aspek
0 // Reset penilaian counter for new aspek
);
break;
case 3: // Sasaran level (Transparansi, etc.)
$currentSasaranCounter++;
$sasaranWithIdentifier = $areaCounter . '.' . $aspekCounter . '.' . $currentSasaranCounter . '. Sasaran ' . ($child->nama ?? '');
$childAttachments = $this->extractAttachments(
$child,
$areaName,
$aspekName,
$sasaranWithIdentifier,
'',
'',
null, // No instansi_type at sasaran level
$areaCounter,
$aspekCounter,
$currentSasaranCounter,
0, // Reset indikator counter for new sasaran
0 // Reset penilaian counter for new sasaran
);
break;
case 4: // Indikator level
$currentIndikatorCounter++;
$indikatorWithIdentifier = $areaCounter . '.' . $aspekCounter . '.' . $sasaranCounter . '.' . $currentIndikatorCounter . '. Indikator ' . ($child->nama ?? '');
$childAttachments = $this->extractAttachments(
$child,
$areaName,
$aspekName,
$sasaranName,
$indikatorWithIdentifier,
'',
null, // No instansi_type at indikator level
$areaCounter,
$aspekCounter,
$sasaranCounter,
$currentIndikatorCounter,
0 // Reset penilaian counter for new indikator
);
break;
case 5: // Penilaian level (special case for nama = null)
$currentPenilaianCounter++;
$penilaianName = '';
if ($child->nama === null || $child->nama === '') {
// Use hierarchical identifier for Penilaian
$penilaianName = 'Penilaian ' . $areaCounter . '.' . $aspekCounter . '.' . $sasaranCounter . '.' . $indikatorCounter . '.' . $currentPenilaianCounter;
} else {
$penilaianName = $child->nama;
}
// Extract instansi_type from penilaian level
$currentInstansiType = $child->instansi_type ?? null;
$childAttachments = $this->extractAttachments(
$child,
$areaName,
$aspekName,
$sasaranName,
$indikatorName,
$penilaianName,
$currentInstansiType, // Pass instansi_type from penilaian level
$areaCounter,
$aspekCounter,
$sasaranCounter,
$indikatorCounter,
$currentPenilaianCounter
);
break;
default: // Deeper levels
$childAttachments = $this->extractAttachments(
$child,
$areaName,
$aspekName,
$sasaranName,
$indikatorName,
$penilaianName,
$instansiType,
$areaCounter,
$aspekCounter,
$sasaranCounter,
$indikatorCounter,
$penilaianCounter
);
break;
}
$attachments = array_merge($attachments, $childAttachments);
}
}
return $attachments;
}
public function indikatorRecap($year)
{
try {
$mcpService = new McpService();
$result = $mcpService->get("/api/report/indikator-recap/$year");
if (!$result->success || $result->data === null) {
throw new Exception("Failed to fetch data from MCP service.");
}
return $this->createIndikatorRecapSpreadsheet($result->data, $year);
} catch (Exception $e) {
throw new CustomErrorException(BaseResponse::UNEXPECTED_ERROR, 'Terjadi kesalahan tak terduga: ' . $e->getMessage());
}
}
private function createIndikatorRecapSpreadsheet($data, $year)
{
$spreadsheet = new Spreadsheet();
$sheetIndex = 0;
// Define the sheet names based on the data keys
$sheetNames = ['Provinsi', 'Kota/Kabupaten', 'DKI Jakarta'];
foreach ($sheetNames as $sheetName) {
if (!isset($data->$sheetName)) {
continue;
}
$sheet = $spreadsheet->createSheet($sheetIndex);
// Clean sheet name for Excel compatibility (remove invalid characters)
$cleanSheetName = str_replace(['/', '\\', '*', '?', ':', '[', ']'], '_', $sheetName);
$sheet->setTitle($cleanSheetName);
// Title
$sheet->setCellValue("A1", "Rekapitulasi Indikator MCSP $year - $sheetName");
$sheet->getStyle("A1")->applyFromArray($this->styleArrayHeader);
$sheet->mergeCells('A1:D1');
// Date
date_default_timezone_set('Asia/Jakarta');
$sheet->setCellValue("A2", date("F j Y, H:i:s") . " WIB");
$sheet->mergeCells('A2:D2');
// Headers
$row = 4;
$headers = ['No', 'Area Intervensi', 'Jumlah Indikator', 'Jumlah Lampiran'];
$col = 1;
foreach ($headers as $header) {
$sheet->setCellValueByColumnAndRow($col++, $row, $header);
}
// Apply header styling
$headerRange = "A$row:D$row";
$sheet->getStyle($headerRange)->applyFromArray($this->styleArray);
// Data rows
$no = 1;
foreach ($data->$sheetName as $item) {
$row++;
$col = 1;
$sheet->setCellValueByColumnAndRow($col++, $row, $no++);
$sheet->setCellValueByColumnAndRow($col++, $row, $item->nama);
$sheet->setCellValueByColumnAndRow($col++, $row, $item->level_4_count);
$sheet->setCellValueByColumnAndRow($col++, $row, $item->attachment_count);
}
// Style data rows
if ($row > 4) {
$dataRange = "A5:D$row";
$sheet->getStyle($dataRange)->applyFromArray($this->styleArrayNormal);
}
// Auto-size columns
$sheet->getColumnDimension('A')->setWidth(5);
$sheet->getColumnDimension('B')->setWidth(25);
$sheet->getColumnDimension('C')->setWidth(20);
$sheet->getColumnDimension('D')->setWidth(20);
$sheetIndex++;
}
// Set first sheet as active
$spreadsheet->setActiveSheetIndex(0);
$writer = new Xlsx($spreadsheet);
$now = date("YmdHis");
$filename = $now . "-Indikator_Recap_MCSP_$year.xlsx";
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="' . $filename . '"');
return $writer->save('php://output');
}
public function mcspDownloadUnverifiedDocsReport($year)
{
ini_set('memory_limit', '2048M');
try {
$mcpService = new McpService();
$result = $mcpService->get("/api/report/unverified-docs/$year");
if (!$result->success || $result->data === null) {
throw new Exception("Failed to fetch data from MCP service.");
}
return $this->createUnverifiedDocsSpreadsheet($result->data);
} catch (Exception $e) {
throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, 'Terjadi kesalahan tak terduga: ' . $e->getMessage());
}
}
private function createUnverifiedDocsSpreadsheet($data)
{
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->createSheet(0);
// Clean sheet name for Excel compatibility (remove invalid characters)
$sheet->setTitle("Dokumen per Tanggal Unggah");
// Title
$sheet->setCellValue('A1', 'Laporan Dokumen Belum Terverifikasi');
$sheet->getStyle('A1')->applyFromArray($this->styleArrayHeader);
$sheet->mergeCells('A1:F1');
// Date
date_default_timezone_set('Asia/Jakarta');
$sheet->setCellValue('A2', date('j F Y, H:i:s') . ' WIB');
$sheet->mergeCells('A2:F2');
// Header
$headers = ['No', 'Pemerintah', 'Area', 'Penilaian', 'Dokumen', 'Tanggal Unggah'];
$col = 1;
$row = 4;
foreach ($headers as $headerTitle) {
$sheet->setCellValueByColumnAndRow($col++, $row, strtoupper($headerTitle));
}
$sheet->getStyle("A4:F4")->applyFromArray($this->styleArrayHeader);
// Body
// Data rows
$no = 1;
foreach ($data as $item) {
$row++;
$col = 1;
$sheet->setCellValueByColumnAndRow($col++, $row, $no++);
$sheet->setCellValueByColumnAndRow($col++, $row, $item->instansi_name);
$sheet->setCellValueByColumnAndRow($col++, $row, $item->area_name);
$sheet->setCellValueByColumnAndRow($col++, $row, $this->clean_html($item->question_name));
$sheet->setCellValueByColumnAndRow($col++, $row, $this->clean_html($item->question_attachment));
$uploadedAtDateTimeObj = new \DateTime($item->question_uploaded_at);
$uploadedAtDateTimeVal = Date::PHPToExcel($uploadedAtDateTimeObj);
$sheet->setCellValueByColumnAndRow($col++, $row, $uploadedAtDateTimeVal);
}
$sheet->getStyle("F4:F$row")->getNumberFormat()->setFormatCode('dd mmmm yyyy hh:mm:ss');
$sheet->getStyle("A4:F$row")->applyFromArray($this->styleArrayNormal);
$sheet->getColumnDimension('A')->setWidth(5);
$sheet->getColumnDimension('B')->setWidth(30);
$sheet->getColumnDimension('C')->setWidth(15);
$sheet->getColumnDimension('D')->setWidth(40);
$sheet->getColumnDimension('E')->setWidth(40);
$sheet->getColumnDimension('F')->setWidth(20);
$writer = new Xlsx($spreadsheet);
$now = date('YmdHis');
$filename = "$now-Laporan_Dokumen_Belum_Terverifikasi.xlsx";
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="' . $filename . '"');
return $writer->save('php://output');
}
private function clean_html($text)
{
$clean = strip_tags($text);
$clean = html_entity_decode($clean, ENT_QUOTES | ENT_HTML5, 'UTF-8');
// Replace non-breaking spaces and trim
$clean = str_replace("\xC2\xA0", ' ', $clean); // handle UTF-8 non-breaking space
$clean = trim($clean);
return $clean;
}
public function mcspQaReport($year)
{
try {
$mcpService = new McpService();
$result = $mcpService->get("/api/report/qa/$year");
if (!$result->success || $result->data === null) {
throw new Exception("Failed to fetch data from MCP service.");
}
$data = json_decode(json_encode($result->data), true);
return $this->createQAReportSpreadsheet($data);
} catch (Exception $e) {
throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, 'Terjadi kesalahan tak terduga: ' . $e->getMessage());
}
}
private function createQAReportSpreadsheet($data)
{
$spreadsheet = new Spreadsheet();
$sheetIndex = 0;
foreach ($data as $item) {
$sheet = $spreadsheet->createSheet($sheetIndex++);
// Create sheet name from id and truncated keterangan (max 31 chars for Excel)
$index = $item['index']['Provinsi'] ?? $item['index']['Kota/Kabupaten'] ?? $item['index']['DKI Jakarta'];
$sheetName = $index."-".mb_substr($item['keterangan'], 0, 15);
$sheetName = preg_replace('/[\[\]\*\/\\\?\:]/', '', $sheetName); // Remove invalid Excel sheet name characters
$sheet->setTitle($sheetName);
$sheet->getColumnDimension('A')->setWidth(5);
$sheet->getColumnDimension('B')->setWidth(40);
$sheet->getColumnDimension('C')->setWidth(5);
// Set headers
$row = 1;
$sheet->setCellValue("A1", $index);
$sheet->getStyle("A1")->applyFromArray($this->styleArrayHeader);
$sheet->setCellValue("A2", $this->clean_html($item['keterangan']));
// Table header for instansi_qa
$sheet->setCellValue('A4', 'No');
$sheet->setCellValue('B4', 'Instansi');
$sheet->setCellValue('C4', 'Score');
$sheet->getStyle('A4:C4')->applyFromArray($this->styleArray);
// Data rows for instansi_qa
$row = 5;
$no = 1;
$startDataRow = $row;
if (isset($item['instansi_qa']) && is_array($item['instansi_qa'])) {
foreach ($item['instansi_qa'] as $qa) {
$sheet->setCellValue('A' . $row, $no);
$sheet->setCellValue('B' . $row, $qa['instansi'] ?? '-');
$sheet->setCellValue('C' . $row, $qa['score'] ?? '-');
$no++;
$row++;
}
}
// Apply border to data rows
if ($row > $startDataRow) {
$dataRange = "A{$startDataRow}:C" . ($row - 1);
$sheet->getStyle($dataRange)->applyFromArray($this->styleArrayNormal);
}
}
// Remove the default empty sheet if we created new ones
// if ($sheetIndex > 0) {
// $spreadsheet->removeSheetByIndex($sheetIndex);
// }
// Set first sheet as active
$spreadsheet->setActiveSheetIndex(0);
$writer = new Xlsx($spreadsheet);
$now = date('YmdHis');
$filename = "$now-Score.xlsx";
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="' . $filename . '"');
return $writer->save('php://output');
}
public function mcspNodeScoresReport($year, $id) {
try {
$mcpService = new McpService();
$result = $mcpService->get("/api/report/node-scores/$year/$id", []);
if (!$result->success || $result->data === null) {
throw new Exception("Failed to fetch data from MCP 2025 service.");
}
$data = json_decode(json_encode($result->data), true);
return $this->createNodeScoresReportSpreadsheet($data, $year, $id);
} catch (Exception $e) {
throw $e;
}
}
private function createNodeScoresReportSpreadsheet($data, $year, $id) {
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->getColumnDimension('A')->setWidth(40);
$sheet->getColumnDimension('B')->setWidth(5);
$sheet->setCellValue("A1", "Nilai $year: $id");
$sheet->getStyle("A1")->applyFromArray($this->styleArrayHeader);
$sheet->setCellValue("A3", "Pemda");
$sheet->setCellValue("B3", "Nilai");
$sheet->getStyle("A3:B3")->applyFromArray($this->styleArray);
$row = 4;
foreach ($data as $pemda => $score) {
$sheet->setCellValue("A$row", $pemda);
$sheet->setCellValue("B$row", $score);
$sheet->getStyle("A$row:B$row")->applyFromArray($this->styleArrayNormal);
$row++;
}
$spreadsheet->setActiveSheetIndex(0);
$writer = new Xlsx($spreadsheet);
$now = date('YmdHis');
$filename = "$now-node-scores-$year-$id.xlsx";
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="' . $filename . '"');
return $writer->save('php://output');
}
}