<?php

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class GratifReportController extends BaseController
{

  public function baseListKorsupgah()
  {
    // $getParams = $this->request->getJsonRawBody(true);  
    $getParams = $this->request->get();  
        
    // TODO: check the parameters, they were not passed
    $getParams['limit'] = $this->getDefaultLimit($getParams);
    $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
    $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : '';

    $params = $this->validate(
        $getParams,
        [
            'limit' => ['validators' => ['digit']],
            'offset' => ['validators' => ['digit']],
            'keyword' => ['validators' => []]
        ]
    );

    $q = "
    SELECT
      -- ka.id_question,
      -- ka.id as id_answer,
      max(mp.provinsi_name) as provinsi_name,
	    max(i.nama) AS instansi,
      -- ktsi.id_sub_indikator,
      COALESCE ( max(ka.nilai), 0 ) AS nilai,
      -- string_agg(ka.keterangan, '|') as keterangan,
      max(ka.keterangan) AS keterangan,
      COALESCE ( max(ka.nilai_verifikasi), 0 ) AS nilai_verifikasi,
      -- string_agg(ka.keterangan_verifikasi, '|') as keterangan_verifikasi,
      max(ka.keterangan_verifikasi) AS keterangan_verifikasi,
      max(ka.created_at) AS tgl_lapor,
      max(ka.verified_at) AS tgl_verifikasi,
      max(ka.deleted_at) as deleted_at,
      string_agg(att.uuid, '; ') as uuid,
      string_agg(att.filename, '; ') as filename
    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 
      LEFT JOIN jaga.korsupgah_answer_attachment kat ON kat.id_answer = ka.id
      LEFT JOIN jaga.attachments att ON att.id = kat.id_attachment
    WHERE
      ks.id_template = 1 
      AND ksi.nama ILIKE '%Pengendalian Gratifikasi%'";    
  
    $q_where_keyword = '';
    if ($params['keyword'] !== '') {
      $q_where_keyword = " 
        AND (
          mp.provinsi_name ILIKE '%".$params['keyword']."%' OR
          i.nama ILIKE '%".$params['keyword']."%'
          )";
    }

    $q_groupby = " 
    GROUP BY
      mp.provinsi_name,
      i.nama";

    $totalRecord = count($this->rawQuery($q.$q_where_keyword.$q_groupby));

    $q_paginated = "
    ORDER BY
      mp.provinsi_name,
      i.nama ASC 
    OFFSET ".$params['offset'];

    $q_limit = $params['limit'] !== '0' ? " LIMIT ".$params['limit'] : '';
    
    // echo "<pre>";
    // print_r ($q.$q_where_keyword.$q_groupby.$q_paginated.$q_limit);
    // echo "</pre>";
    // die;

    $gratif_report = $this->rawQuery($q.$q_where_keyword.$q_groupby.$q_paginated.$q_limit);

    return [
      'data' => $gratif_report,
      'total_record' => $totalRecord,
      'limit' => $params['limit'],
      'offset' => $params['offset']
    ];
  }

  public function list()
  {
    $report_paginated = $this->baseListKorsupgah();
    return new PaginatedResponse($report_paginated['data'], $report_paginated['total_record'], $report_paginated['limit'], $report_paginated['offset']);
  }

  public function exportExcel()
  {
    // get all data 
    //params: limit = 0, offset = 0, keyword = ''
    $gratif_report = $this->baseListKorsupgah()['data'];

    // init excel
    $columnIndex = 1;
    $rowIndex = 1;
    $spreadsheet = new Spreadsheet();

    $sheet = $spreadsheet->getActiveSheet();
    // title    
    $todayLabel = DateHelper::formatTodayIndex();
    $sheet->setCellValueByColumnAndRow($columnIndex, $rowIndex, 'Report MCP - Gratifikasi');
    $sheet->setCellValueByColumnAndRow($columnIndex, $rowIndex++, $todayLabel);

    // table header
    $rowIndex++;
    $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, 'Provinsi');
    $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, 'Instansi');
    $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, 'Klaim (%)');
    $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, 'Keterangan');
    $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, 'Verifikasi (%)');
    $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, 'Ket. Verifikasi');
    $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, 'Tgl. Lapor');
    $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, 'Tgl. Verifikasi');
    $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, 'Lampiran');

    // table content
    $columnIndex = 1;
    $rowIndex++;
    foreach ($gratif_report as $key => $g) {
      $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $g['provinsi_name']);
      $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $g['instansi']);
      $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $g['nilai']);
      // TODO: convert html to text
      $opt = [
        'ignore_errors' => true, //default false
        'drop_links' => false // default false
      ];
      $ket = \Soundasleep\Html2Text::convert($g['keterangan'], $opt);
      $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $ket);
      // $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $g['keterangan']);
      $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $g['nilai_verifikasi']);
      // $ketv = \Soundasleep\Html2Text::convert($g['keterangan_verifikasi']);
      // $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $ketv);
      $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $g['keterangan_verifikasi']);
      $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $g['tgl_lapor']);
      $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $g['tgl_verifikasi']);
      // TODO: change delimiter to enter
      $uuid = str_replace(';','\n',(string)$g['uuid']);
      $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, "$uuid");
      $filename = str_replace(';','\n',(string)$g['filename']);
      $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, "$filename");

      $rowIndex++;
      $columnIndex = 1;
    }

    // generate output
    $writer = new Xlsx($spreadsheet);
    $filename = $todayLabel . "-MCP-Gratifikasi" .  ".xlsx";
    header('Content-type: application/vnd.ms-excel');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    return $writer->save('php://output');
  }

  
}