<?php

/*******************************************************************

    App. Name     : SISTEM INFORMASI PERENCANAAN PEMBANGUNAN DAERAH
    Module        : /apps/php/xls/data_hasil_forum.php
    Desc.         : PHP - Export Data Hasil Forum OPD
    Created By    : Siau Pheng (siaupheng@3fonia.com).
    Created Date  : January 4th, 2020.
    Last Modified : March 21st, 2022.

    (c) 2020 - 2022, 3FONIA Software; WWW.3FONIA.COM.

*******************************************************************/

require_once(dirname(__FILE__)."/../../../../../modules3/web.auth.php");
require_once(dirname(__FILE__)."/../../../../../modules3/web.form.php");
require_once(dirname(__FILE__)."/../../../../../modules3/data.json.php");
require_once(dirname(__FILE__)."/../../../../../vendor/autoload.php");
require_once(dirname(__FILE__)."/../func.sippd.php");

ini_set("max_execution_time", "300");
ini_set("pcre.backtrack_limit", "5000000");

/************************** RESPON HERE ***************************/

$db = DB();
$ajson = array();
$data = $ap->getParam("data");
$id_rpjmd = $ap->getParam("RPJMDID", $_SESSION['__WEB_APP']['RENC_RPJMDID']);
$tahun = $ap->getParam("Tahun", $_SESSION['__WEB_APP']['RENC_THN']);
$id_forum = $ap->getParam("ForumID", 0);
$id_opd = $ap->getParam("OPDID", 0);
$id_kec = $ap->getParam("KecID", 0);
$id_status = $ap->getParam("StatusID", 0);

if ( $data == "excel" ) {
    $tsql = " Tahun='".$tahun."'";
    $tsql .= ($id_forum==0) ? "" : " AND OPDID IN (SELECT OPDID FROM para90_opd WHERE Aktif=1 AND ForumID='".$id_forum."')";
    $tsql .= ($id_opd==0) ? "" : " AND OPDID='".$id_opd."'";
    $tsql .= ($id_kec==0) ? "" : " AND KecID='".$id_kec."'";
    $tsql .= ($id_status==0) ? " AND StatusID>0" : " AND StatusID='".$id_status."'";

    $db->Query("SELECT U.*,O.K_OPD,O.N_OPD,D.K_Desa,D.N_Desa,D.K_Kec,D.N_Kec,B.K_Belanja,B.N_Belanja,S.N_Satuan,T.K_Status,T.S_Status FROM (SELECT * FROM mus_usulan_desa WHERE".$tsql.") U LEFT JOIN para90_opd O ON (U.OPDID=O.OPDID) LEFT JOIN vi_para_desa D ON (U.KelID=D.DesaID) LEFT JOIN para_belanja B ON (U.BelID=B.BelID) LEFT JOIN para_satuan S ON (U.SatuanID2=S.SatuanID) LEFT JOIN para_status T ON (U.StatusID=T.StatusID)");

    \PhpOffice\PhpSpreadsheet\Cell\Cell::setValueBinder( new \PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder() );

    $styleTxt = [
        'numberFormat' => ['formatCode' => \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT]
    ];
    $styleNum = [
        'numberFormat' => ['formatCode' => \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2]
    ];

    $ssheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
    $ssheet->getProperties()
        ->setCreator($_SESSION['__WEB_APP']['APP_NAME']." v".$_SESSION['__WEB_APP']['APP_VER'])
        ->setLastModifiedBy($_SESSION['__WEB_APP']['APP_NAME']." v".$_SESSION['__WEB_APP']['APP_VER'])
        ->setTitle("Data Hasil Forum OPD ".$tahun)
        ->setCategory("Data Hasil Forum OPD ".$tahun)
        ->setSubject($tahun)
        ->setDescription("Data Hasil Forum OPD ".$tahun)
        ->setCompany("Bappeda Kabupaten Sambas");
    $sheet = $ssheet->getActiveSheet();
    $sheet->setTitle("HASIL FORUM");

    // header
    $styleHeader = [
        'font' => ['bold' => true],
        'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER]
    ];
    $sheet->setCellValue("A1", strtoupper($_SESSION['__WEB_APP']['REG_NAME']));
    $sheet->mergeCells('A1:AC1');
    $sheet->setCellValue("A2", "DATA HASIL FORUM OPD TAHUN ".$tahun);
    $sheet->mergeCells('A2:AC2');
    $sheet->getStyle('A1:AC2')->applyFromArray($styleHeader);

    // table header
    $sheet->getColumnDimension('A')->setWidth(8);
    $sheet->getColumnDimension('B')->setWidth(20);
    $sheet->getColumnDimension('C')->setWidth(20);
    $sheet->getColumnDimension('D')->setWidth(10);
    $sheet->getColumnDimension('E')->setWidth(20);
    $sheet->getColumnDimension('F')->setWidth(20);
    $sheet->getColumnDimension('G')->setWidth(20);
    $sheet->getColumnDimension('H')->setWidth(10);
    $sheet->getColumnDimension('I')->setWidth(9);
    $sheet->getColumnDimension('J')->setWidth(10);
    $sheet->getColumnDimension('K')->setWidth(12);
    $sheet->getColumnDimension('L')->setWidth(5);
    $sheet->getColumnDimension('M')->setWidth(7);
    $sheet->getColumnDimension('N')->setWidth(6);
    $sheet->getColumnDimension('O')->setWidth(12);
    $sheet->getColumnDimension('P')->setWidth(6);
    $sheet->getColumnDimension('Q')->setWidth(12);
    $sheet->getColumnDimension('R')->setWidth(6);
    $sheet->getColumnDimension('S')->setWidth(12);
    $sheet->getColumnDimension('T')->setWidth(6);
    $sheet->getColumnDimension('U')->setWidth(15);
    $sheet->getColumnDimension('V')->setWidth(7);
    $sheet->getColumnDimension('W')->setWidth(15);
    $sheet->getColumnDimension('X')->setWidth(8);
    $sheet->getColumnDimension('Y')->setWidth(8);
    $sheet->getColumnDimension('Z')->setWidth(8);
    $sheet->getColumnDimension('AA')->setWidth(8);
    $sheet->getColumnDimension('AB')->setWidth(8);
    $sheet->getColumnDimension('AC')->setWidth(10);

    $styleTableH = [
        'font' => ['bold' => true],
        'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, 'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER, 'wrapText' => true],
        'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['rgb'=>"D0D0D0"]],
        'borders' => ['allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN, 'color' => ['rgb'=>"C0C0C0"]]]
    ];
    $sheet->setCellValue("A4", "Kode");
    $sheet->setCellValue("B4", "Lokasi");
    $sheet->setCellValue("C4", "Dusun");
    $sheet->setCellValue("D4", "RT");
    $sheet->setCellValue("E4", "Masalah");
    $sheet->setCellValue("F4", "Usulan");
    $sheet->setCellValue("G4", "Sasaran");
    $sheet->setCellValue("H4", "Waktu");
    $sheet->setCellValue("I4", "Volume");
    $sheet->setCellValue("J4", "Satuan");
    $sheet->setCellValue("K4", "Anggaran");
    $sheet->setCellValue("L4", "Prio");
    $sheet->setCellValue("M4", "Status");
    $sheet->setCellValue("N4", "OPD");
    $sheet->setCellValue("O4", "Nama OPD");
    $sheet->setCellValue("P4", "Desa");
    $sheet->setCellValue("Q4", "Nama Desa");
    $sheet->setCellValue("R4", "Kec");
    $sheet->setCellValue("S4", "Nama Kec");
    $sheet->setCellValue("T4", "Bel");
    $sheet->setCellValue("U4", "Nama Belanja");
    $sheet->setCellValue("V4", "Status");
    $sheet->setCellValue("W4", "Ket Status");
    $sheet->setCellValue("X4", "ID UNIQ");
    $sheet->setCellValue("Y4", "ID OPD");
    $sheet->setCellValue("Z4", "ID Kec");
    $sheet->setCellValue("AA4", "ID Kel");
    $sheet->setCellValue("AB4", "ID Bel");
    $sheet->setCellValue("AC4", "ID Satuan");
    $sheet->getStyle('A4:AC4')->applyFromArray($styleTableH);

    $baris = 5;
    while ($db->Next()) {
        $sheet->setCellValue("A".$baris, $db->Row("Kode"));
        $sheet->setCellValue("B".$baris, $db->Row("Lokasi2"));
        $sheet->setCellValue("C".$baris, $db->Row("Dusun2"));
        $sheet->setCellValue("D".$baris, $db->Row("RT2"));
        $sheet->setCellValue("E".$baris, $db->Row("Masalah2"));
        $sheet->setCellValue("F".$baris, $db->Row("Usulan2"));
        $sheet->setCellValue("G".$baris, $db->Row("Sasaran2"));
        $sheet->setCellValue("H".$baris, $db->Row("Waktu2"));
        $sheet->setCellValue("I".$baris, $db->Row("Volume2"));
        $sheet->setCellValue("J".$baris, $db->Row("N_Satuan"));
        $sheet->setCellValue("K".$baris, $db->RowFloat("Rupiah2"));
        $sheet->setCellValue("L".$baris, $db->Row("Prio"));
        $sheet->setCellValue("M".$baris, $db->Row("StatusID"));
        $sheet->setCellValue("N".$baris, $db->Row("K_OPD"));
        $sheet->setCellValue("O".$baris, $db->Row("N_OPD"));
        $sheet->setCellValue("P".$baris, $db->Row("K_Desa"));
        $sheet->setCellValue("Q".$baris, $db->Row("N_Desa"));
        $sheet->setCellValue("R".$baris, $db->Row("K_Kec"));
        $sheet->setCellValue("S".$baris, $db->Row("N_Kec"));
        $sheet->setCellValue("T".$baris, $db->Row("K_Belanja"));
        $sheet->setCellValue("U".$baris, $db->Row("N_Belanja"));
        $sheet->setCellValue("V".$baris, $db->Row("K_Status"));
        $sheet->setCellValue("W".$baris, $db->Row("S_Status"));
        $sheet->setCellValue("X".$baris, $db->RowFloat("UsulanLID"));
        $sheet->setCellValue("Y".$baris, $db->RowFloat("OPDID"));
        $sheet->setCellValue("Z".$baris, $db->RowFloat("KecID"));
        $sheet->setCellValue("AA".$baris, $db->RowFloat("KelID"));
        $sheet->setCellValue("AB".$baris, $db->RowFloat("BelID"));
        $sheet->setCellValue("AC".$baris, $db->RowFloat("SatuanID2"));
        //$sheet->getStyle('K'.$baris.':K'.$baris)->applyFromArray($styleNum);
        $baris++;
    }
    
    $sheet->getSheetView()->setZoomScale(75);
    $sheet->getHeaderFooter()->setOddFooter('&LeSIPPD - Kabupaten Sambas &Usulan Desa '.$tahun.' &R&I&D &T // Halaman : &P dari &N');
    $sheet->getHeaderFooter()->setEvenFooter('&LeSIPPD - Kabupaten Sambas &Usulan Desa '.$tahun.' &R&I&D &T // Halaman : &P dari &N');

    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="Data Hasil Forum OPD '.$tahun.'.xlsx"');
    header('Cache-Control: max-age=0');
    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($ssheet, 'Xlsx');
    $writer->save('php://output');
    $ssheet->disconnectWorksheets();
    unset($ssheet);
    unset($writer);

} else if ( $data == "option-opd" ) {
    $aoption = array('type' => "option", 'id' => "OPDID", 'value' => 0, 'avalue' => array());
    $db->Query("SELECT * FROM vi_para90_opd WHERE Aktif=1 AND ForumID='".$id_forum."'".($_SESSION['__WEB_USER']['Level']==3?" AND OPDID IN (".$_SESSION['__WEB_USER']['AK_OPD'].")":""));
    if ($db->RecordCount()<>1) $aoption['avalue'][] = array('value'=>0, 'html'=>"");
    while ($db->Next()) {
        if ($db->RecordCount()==1) $aoption['value'] = $db->Row("OPDID");
        $aoption['avalue'][] = array('value'=>$db->Row("OPDID"), 'html'=>$db->Row("Nama_OPD"));
    }
    $ajson[] = $aoption;

} else if ( $data == "default") {
    $q_kec = ($_SESSION['__WEB_USER']['Level']==2) ? " AND LokasiID IN (".$_SESSION['__WEB_USER']['AK_KEC'].")": " AND Level=1 AND LokasiID>2";

    $form = new cFORM();
    $ttahun = $form->option_db("_", "Tahun", $tahun, true, true, "para_periode", array("Tahun","Tahun"), "Tahun BETWEEN '".$_SESSION['__WEB_APP']['Tahun_Min']."' AND '".$_SESSION['__WEB_APP']['Tahun_Max']."'");
    $tforum = $form->option_db("_", "ForumID", 0, true, true, "para_forum", array("ForumID","N_Forum"), "Aktif=1 ORDER BY ForumID", array(0,"SEMUA FORUM"), null, "XDataHasilForumJS.oc_Forum()");
    $topd = $form->option("_", "OPDID", 0, true, true, array(0=>"SEMUA OPD"));
    $tkec = $form->option_db("_", "KecID", 0, true, true, "para_lokasi", array("LokasiID","N_Lokasi"), "Aktif=1".$q_kec." ORDER BY N_Lokasi", ($_SESSION['__WEB_USER']['Level']==2?"":array(0,"SEMUA KECAMATAN")));
    $tstatus = $form->option_db("_", "StatusID", 0, true, true, "vi_para_status", array("StatusID","Nama_Status"), "Aktif=1 ORDER BY K_Status", array(0,"SEMUA STATUS"));

    echo '
    <div class="form-row mb-1">
        <label class="col-sm-4 col-lg-3 col-form-label" for="Tahun">Tahun Data</label>
        <div class="col-sm-4 col-lg-5">'.$ttahun.'</div>
    </div>
    <div class="form-row mb-1">
        <label class="col-sm-4 col-lg-3 col-form-label" for="ForumID">Nama Forum</label>
        <div class="col-sm-8 col-lg-9">'.$tforum.'</div>
    </div>
    <div class="form-row mb-1">
        <label class="col-sm-4 col-lg-3 col-form-label" for="OPDID">Nama Perangkat Daerah</label>
        <div class="col-sm-8 col-lg-9">'.$topd.'</div>
    </div>
    <div class="form-row mb-1">
        <label class="col-sm-4 col-lg-3 col-form-label" for="KecID">Nama Kecamatan</label>
        <div class="col-sm-8 col-lg-9">'.$tkec.'</div>
    </div>
    <div class="form-row mb-1">
        <label class="col-sm-4 col-lg-3 col-form-label" for="StatusID">Nama Status</label>
        <div class="col-sm-8 col-lg-9">'.$tstatus.'</div>
    </div>';

}

send_json($ajson);
?>