Symfony Profiler

<?php
namespace App\Controller\Base;
use App\Entity\FeedbackIsi;
use App\Entity\MKategori;
use App\Entity\MKategoriDetail;
use App\Entity\MSetting;
use App\Entity\PtiGroup;
use App\Entity\PtiRule;
use App\Entity\TData;
use App\Entity\TDokumen;
use App\Entity\TGrafik;
use App\Entity\TPublikasi;
use App\Entity\TVariabel;
use App\Entity\TVariabelGrafik;
use App\Entity\MKota;
use App\Entity\TExploreDataDetail;
use App\Entity\TMetaData;
use App\Repository\MFeedbackRepository;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use App\Utils\ObjectManager;
use App\Utils\Breadcrumb\BreadcrumbBuilder;
use Symfony\Contracts\Translation\TranslatorInterface;
use Lexik\Bundle\FormFilterBundle\Filter\FilterBuilderUpdaterInterface;
use Symfony\Component\Form\FormInterface;
use Symfony\Component\HttpFoundation\Request;
use Doctrine\ORM\QueryBuilder;
use Doctrine\ORM\Query;
use Doctrine\Persistence\ManagerRegistry;
use Pagerfanta\Adapter\DoctrineORMAdapter;
use Pagerfanta\Pagerfanta;
use Ob\HighchartsBundle\Highcharts\Highchart;
use Symfony\Component\Security\Core\Security;
use Doctrine\Common\Proxy\Proxy;
use App\Repository\FeedbackIsiRepository;
use App\Repository\FeedbackRepository;
use App\Repository\MSettingRepository;
use App\Repository\MKotaRepository;
use App\Repository\TExploreDataDetailRepository;
use DateTime;
use Symfony\Component\CssSelector\Parser\Handler\WhitespaceHandler;
use Symfony\Component\HttpFoundation\JsonResponse;
class BaseController extends AbstractController
{
/**
* @var limit
*/
private $limit = 10;
/**
* @var max_per_page
*/
private $max_per_page = array(10, 50, 100);
private $translation;
private $filterBuilder;
private $objectManager;
private $breadcrumbBuilder;
private $managerRegistry;
private $MFeedback;
private $feedbackIsi;
private $mKota;
public $currentMaxLink = 1;
public function __construct(
ObjectManager $objectManager,
TranslatorInterface $translation,
FilterBuilderUpdaterInterface $filterBuilder,
BreadcrumbBuilder $breadcrumbBuilder,
MFeedbackRepository $MFeedback,
FeedbackIsiRepository $feedbackIsi,
MKotaRepository $mKota,
ManagerRegistry $managerRegistry
) {
$this->translation = $translation;
$this->filterBuilder = $filterBuilder;
$this->objectManager = $objectManager;
$this->breadcrumbBuilder = $breadcrumbBuilder;
$this->managerRegistry = $managerRegistry;
$this->MFeedback = $MFeedback;
$this->feedbackIsi = $feedbackIsi;
$this->mKota = $mKota;
}
protected function getBreadcrumb()
{
return $this->breadcrumbBuilder;
}
/**
* @return Symfony\Component\Translation\TranslatorInterface
*/
public function getTranslator(): TranslatorInterface
{
return $this->translation;
}
public function getQueryBuilder(string $entityClassName): QueryBuilder
{
$queryBuilder = $this->getDoctrine()->getManager()->createQueryBuilder()
->select('this')
->from($entityClassName, 'this');
return $queryBuilder;
}
protected function buildFilter(Request $request, FormInterface $form, QueryBuilder $queryBuilder): QueryBuilder
{
// if ($request->query->get('_reset') and Request::METHOD_GET === $request->getMethod()) {
if ($request->query->get('_reset') and $request->getMethod() === 'GET') {
// dump($request->getMethod()); exit;
// if ($request->query->get('_reset') == 1) {
$type = get_class($form->getConfig()->getType()->getInnerType());
$options = $form->getConfig()->getOptions();
$options['data'] = null;
$form = parent::createForm($type, null, $options);
$this->get('session')->set($form->getName(), null);
return $this->getFilterAdapter()->addFilterConditions($form, $queryBuilder);
// return $form;
}
$filters = $request->get($form->getName());
if ($filters) {
$form->submit($filters);
$this->get('session')->set($form->getName(), $form->getData());
}
return $this->getFilterAdapter()->addFilterConditions($form, $queryBuilder);
}
public function createPaginator(Request $request, Query $query): Pagerfanta
{
if ($request->get('_limit') && is_numeric($request->get('_limit'))) {
$request->getSession()->set('limit', $request->get('_limit'));
} else {
$request->getSession()->set('limit', $this->limit);
}
// if(!$request->getSession()->get("limit")) {
// $request->getSession()->set('limit', $this->limit);
// }
$adapter = new DoctrineORMAdapter($query, false);
$paginator = new Pagerfanta($adapter);
$paginator->setAllowOutOfRangePages(true);
// Set pages based on the request parameters.
$paginator->setMaxPerPage($request->getSession()->get("limit"));
$paginator->setCurrentPage($request->query->get('page', 1));
return $paginator;
}
public function setSessionLimit($request)
{
if ($request->get('_limit') && is_numeric($request->get('_limit'))) {
$request->getSession()->set('limit', $request->get('_limit'));
$this->limit = $request->getSession()->get('limit');
// } else {
// $request->getSession()->set('limit', $this->limit);
}
}
public function getLimit()
{
return $this->limit;
}
public function setMaxPerPage($max_page = array())
{
$this->max_per_page = $max_page;
}
public function getMaxPerPage()
{
return $this->max_per_page;
}
public function setFilters($filters = array(), $name)
{
$this->get('session')->set($name, $filters);
}
public function getFilters($name)
{
$filters = $this->get('session')->get($name, null);
if (!is_array($filters)) {
return null;
}
foreach ($filters as $k => $v) {
if (!is_object($v)) {
continue;
}
$manager = $this->getDoctrine()->getManager();
if (!$manager->getMetadataFactory()->isTransient(get_class($v)) or $v instanceof Proxy) {
$filters[$k] = $manager->getRepository(get_class($v))->find($v->getId());
}
}
return $filters;
}
public function getFilterAdapter()
{
return $this->filterBuilder;
}
protected function createFormFilter(string $type, array $options = array()): FormInterface
{
// $reflection = new \ReflectionClass($type);
// $name = sprintf("%s", strtolower($reflection->getShortName()));
$form = parent::createForm($type);
$data = $this->getFilters($form->getName());
$form = parent::createForm($type, $data, $options);
// dump($form->getData());exit;
return $form;
}
protected function processFormAjax(FormInterface $form, Request $request)
{
$form->handleRequest($request);
if ($form->isSubmitted()) {
$type = ($form->getData() && !is_array($form->getData()) && $form->getData()->getId()) ? "update" : "add";
if ($form->isValid()) {
$result = $this->objectManager->save($form->getData());
if ($result) {
return ["process" => true, "status" => true, "message" => $this->getTranslator()->trans('messages.' . $type . '.success'), "errors" => null];
} else {
return ["process" => true, "status" => false, "message" => $this->getTranslator()->trans('messages.' . $type . '.error'), "errors" => 'error while saved data.'];
}
} else {
$errors = $this->getErrorsFromForm($form);
return ["process" => true, "status" => false, "message" => $this->getTranslator()->trans('messages.' . $type . '.error'), "errors" => implode(", ", $errors)];
}
}
return ["process" => false];
}
protected function getErrorsFromForm(FormInterface $form)
{
$errors = array();
foreach ($form->getErrors() as $error) {
$errors[] = $error->getMessage();
}
foreach ($form->all() as $childForm) {
if ($childForm instanceof FormInterface) {
if ($childErrors = $this->getErrorsFromForm($childForm)) {
$errors[$childForm->getName()] = $childForm->getName() . ': ' . implode(", ", $childErrors);
}
}
}
return $errors;
}
protected function doDelete($object)
{
return $this->objectManager->delete($object);
}
public function addSelected(Request $request)
{
$sessionName = $request->get("name");
$this->get('session')->set($sessionName, $request->get('selected'));
$selected = (!empty($this->get('session')->get($sessionName))) ? $this->get('session')->get($sessionName) : [];
return $this->json($selected);
}
public function actionSelected($className, Request $request, ObjectManager $objectManager)
{
$sessionName = $request->get("name");
if ($this->isCsrfTokenValid($sessionName . '_action_selected', $request->request->get('_token'))) {
$selected = (!empty($this->get('session')->get($sessionName))) ? $this->get('session')->get($sessionName) : [];
$deleted = $objectManager->deleteByIds($className, $selected);
if ($deleted) {
$this->get('session')->set($sessionName, []);
$this->addFlash('success', $this->getTranslator()->trans('messages.deleted.success'));
} else {
$this->addFlash('error', $this->getTranslator()->trans('messages.deleted.error'));
}
}
}
public function doDeleted(Request $request, $object, ObjectManager $objectManager)
{
if ($this->isCsrfTokenValid('delete' . $object->getId(), $request->request->get('_token'))) {
if ($this->doDelete($object, $objectManager)) {
$this->addFlash('success', $this->getTranslator()->trans('messages.deleted.success'));
} else {
$this->addFlash('error', $this->getTranslator()->trans('messages.deleted.error'));
}
} else {
$this->addFlash('error', $this->getTranslator()->trans('messages.deleted.error'));
}
}
public function getSetting()
{
$mSetting = $this->getDoctrine()->getRepository(MSetting::class);
$setting = $mSetting->findOneBy(['is_active' => true], ['id' => 'desc']);
return $setting;
}
public function getHeaderKategori()
{
$mKategoriRepo = $this->getDoctrine()->getRepository(MKategori::class);
$kategori = $mKategoriRepo->findBy([], ['id' => 'asc']);
return $kategori;
}
public function getVariable($id)
{
$mVariableRepo = $this->getDoctrine()->getRepository(TVariabel::class);
$variabel = $mVariableRepo->find($id);
return $variabel;
}
public function getDataTable($id, $limit, $page, $get, $state = null, $row = null, $choiced_field = null, $tahun = null)
{
$connection = $this->getDoctrine()->getConnection();
// $connection = DriverManager::getConnection($this->connParams);
$tbl_func = "CREATE EXTENSION IF NOT EXISTS tablefunc; ";
$stmt = $connection->prepare($tbl_func);
$stmt->execute();
$variabel_repo = $this->getDoctrine()->getRepository(TVariabel::class);
$periode_update = $variabel_repo->getPeriodeVariableByPubId($id)[0]->getId();
$period_update_status = false;
if ($choiced_field) {
in_array($periode_update, $choiced_field) ? $period_update_status = true : array_push($choiced_field, "{$periode_update}");
$variabel = $variabel_repo->findBy(['id' => $choiced_field, 'is_tampil' => true], ['id' => 'asc']);
} else {
$period_update_status = true;
$variabel = $variabel_repo->findBy(['pub' => $id, 'is_tampil' => true], ['id' => 'asc']);
}
$as_columns = "";
foreach ($variabel as $var) {
$as_columns .= ", " . $var->getNama() . " text";
}
if ($choiced_field) {
$count = 0;
$choiced = "(";
foreach ($choiced_field as $field) {
$count = $count + 1;
if ($count == count($choiced_field)) {
$choiced = $choiced . $field;
} else {
$choiced = $choiced . $field . ',';
}
}
$choiced = $choiced . ')';
$sql = "SELECT * FROM crosstab('select a.row_id, b.id, a.isi
from t_data a
join t_variabel b
on a.variabel_id = b.id
where a.pub_id = " . $id . " and b.is_tampil is true and a.variabel_id IN " . $choiced;
} else {
$sql = "SELECT * FROM crosstab('select a.row_id, b.id, a.isi
from t_data a
join t_variabel b
on a.variabel_id = b.id
where a.pub_id = " . $id . " and b.is_tampil is true";
}
$sql .= " order by 1, 2')
AS ct (row_id int" . $as_columns . ") where 1=1";
// $sql .= " and ct.periode_update ilike '%" . $tahun . "%'";
// dd($choiced_field);
// foreach($choiced_field as $field) {
// if($this->getVariable($field)->getNama() == 'periode_update') {
// $sql .= " and ct.periode_update ilike '%" . $tahun . "%'";
// }
// }
$sql .= " and ct.periode_update ilike '%" . $tahun . "%'";
if ($row) {
$sql .= " and row_id = " . $row;
}
if (!$period_update_status) {
$variabel = array_filter($variabel, function ($var) use ($periode_update) {
return $var->getId() != $periode_update;
});
}
if ($get) {
foreach ($variabel as $var) {
if ($value = $get[$var->getNama()]) {
$sql .= " and " . $var->getNama() . " ILIKE '%" . $value . "%'";
}
// if($var->getNama() == 'periode_update') {
// continue;
// } else {
// if ($value = $get[$var->getNama()]) {
// $sql .= " and " . $var->getNama() . " ILIKE '%" . $value . "%'";
// }
// }
}
}
if ($limit) {
$sql .= " LIMIT " . $limit;
}
if ($page) {
$sql .= " OFFSET " . $page;
}
$stmt = $connection->prepare($sql);
$stmt->execute();
$result = array();
foreach ($stmt->fetchAll() as $data) {
$temp = array();
if ($state) {
$temp['id'] = $data['row_id'];
}
foreach ($variabel as $var) {
if ($var->getNama() == 'periode_update') {
if (!$period_update_status) {
continue;
}
}
$temp[] = $data[$var->getNama()];
}
$result[] = $temp;
}
// print_r($result);exit;
return $result;
}
public function getDataDownload($id)
{
$connection = $this->getDoctrine()->getConnection();
// $connection = DriverManager::getConnection($this->connParams);
$tbl_func = "CREATE EXTENSION IF NOT EXISTS tablefunc; ";
$stmt = $connection->prepare($tbl_func);
$stmt->execute();
$variabel_repo = $this->getDoctrine()->getRepository(TVariabel::class);
$pub_rebo = $this->managerRegistry->getRepository(TPublikasi::class);
$periode_update = $variabel_repo->getPeriodeVariableByPubId($id)[0]->getId();
$period_update_status = false;
$variabel = $variabel_repo->findBy(['pub' => $id, 'is_tampil' => true], ['id' => 'asc']);
$as_columns = "";
foreach ($variabel as $var) {
$as_columns .= ", " . $var->getNama() . " text";
}
$sql = "SELECT * FROM crosstab('select a.row_id, b.id, a.isi
from t_data a
join t_variabel b
on a.variabel_id = b.id
where a.pub_id = " . $id . " and b.is_tampil is true";
$sql .= " order by 1, 2')
AS ct (row_id int" . $as_columns . ") where 1=1";
$stmt = $connection->prepare($sql);
$stmt->execute();
$result = array();
$sqlResult = $stmt->fetchAll();
$years = [];
$orderedPeriode = [];
foreach ($sqlResult as $key => $value) {
if (!in_array(preg_replace('/[^0-9]/', '', $value['periode_update']), $years)) {
$years[] = preg_replace('/[^0-9]/', '', $value['periode_update']);
}
}
$publikasi = $pub_rebo->find($id);
$opt_periode = $this->getPeriodeName($publikasi->getPeriode()->getId());
sort($years);
foreach ($years as $k => $v) {
foreach ($opt_periode as $periode_key => $periode_v) {
$orderedPeriode[] = $periode_v . ' ' . $v;
}
}
$orderedData = [];
foreach ($orderedPeriode as $k => $v) {
foreach ($sqlResult as $k_result => $v_result) {
if ($v_result['periode_update'] == $v) {
$orderedData[] = $v_result;
}
}
}
foreach ($orderedData as $data) {
$temp = array();
foreach ($variabel as $var) {
$temp[] = $data[$var->getNama()];
}
$result[] = $temp;
}
// print_r($result);exit;
return $result;
}
public function getDataTableRawData($id, $limit, $page, $get, $state = null, $row = null)
{
$connection = $this->getDoctrine()->getConnection();
// $connection = DriverManager::getConnection($this->connParams);
$sql = "SELECT * FROM t_raw_data";
if ($limit) {
$sql .= " LIMIT " . $limit;
}
if ($page) {
$sql .= " OFFSET " . $page;
}
$stmt = $connection->prepare($sql);
$stmt->execute();
$result = array();
foreach ($stmt->fetchAll() as $key => $data) {
// $result[$key]['head1'] = json_decode($data['head_1'], true);
// $result[$key]['head2'] = json_decode($data['head_2'], true);
$result['isi'][$key] = json_decode($data['isi'], true);
}
// print_r($result);exit;
return $result;
}
public function getDataTableCleansing($id, $limit, $page, $get, $state = null, $row = null)
{
$connection = $this->getDoctrine()->getConnection();
// $connection = DriverManager::getConnection($this->connParams);
$sql = "SELECT * FROM t_cleansing_raw";
if ($limit) {
$sql .= " LIMIT " . $limit;
}
if ($page) {
$sql .= " OFFSET " . $page;
}
$stmt = $connection->prepare($sql);
$stmt->execute();
$result['isi'] = [];
foreach ($stmt->fetchAll() as $key => $data) {
$kategori_plant = $data['kategori_plant'];
$regional = $data['regional'];
$status_pp_update = $data['status_pp_update'];
$kategori_tahap = $data['kategori_tahap'];
$nilai_saving_terhadap_anggaran = $data['nilai_saving_terhadap_anggaran'];
$nilai_saving_terhadap_hps = $data['nilai_saving_terhadap_hps'];
$saving_persen_terhadap_angggaran = $data['saving_persen_terhadap_angggaran'];
$saving_persen_terhadap_hps = $data['saving_persen_terhadap_hps'];
$decoded = json_decode($data['isi'], true);
if (!is_array($decoded)) continue;
// Jika bukan array of array, bungkus ke array
if (!isset($decoded[0]) || !is_array($decoded[0])) {
$decoded = [$decoded];
}
$items = array_map(function ($item) use ($kategori_plant, $regional, $status_pp_update, $kategori_tahap, $nilai_saving_terhadap_anggaran, $nilai_saving_terhadap_hps, $saving_persen_terhadap_angggaran, $saving_persen_terhadap_hps) {
$item['kategori_plant'] = $kategori_plant;
$item['regional'] = $regional;
$item['status_pp_update'] = $status_pp_update;
$item['kategori_tahap'] = $kategori_tahap;
$item['nilai_saving_terhadap_anggaran'] = $nilai_saving_terhadap_anggaran;
$item['nilai_saving_terhadap_hps'] = $nilai_saving_terhadap_hps;
$item['saving_persen_terhadap_angggaran'] = $saving_persen_terhadap_angggaran;
$item['saving_persen_terhadap_hps'] = $saving_persen_terhadap_hps;
return $item;
}, $decoded);
$result['isi'] = array_merge($result['isi'], $items);
// $result['isi'][$key] = json_decode($data['isi'], true);
// print_r($result);exit;
}
return $result;
}
public function getDataHeader()
{
$connection = $this->getDoctrine()->getConnection();
// $connection = DriverManager::getConnection($this->connParams);
$sql = "SELECT * FROM t_header";
$stmt = $connection->prepare($sql);
$stmt->execute();
$result = array();
foreach ($stmt->fetchAll() as $data) {
$result[]['head1'] = json_decode($data['head_1'], true);
$result[]['head2'] = json_decode($data['head_2'], true);
}
// print_r($result);exit;
return $result;
}
public function highlight()
{
$connection = $this->getDoctrine()->getConnection();
// $connection = DriverManager::getConnection($this->connParams);
$sql = "SELECT id, judul, 'INFOGRAFIK'::text as tipe, created_at as tanggal, 'detail_infografik' as link FROM t_infografik
WHERE is_arsip is false AND tipe = 1
-- UNION ALL
-- SELECT id, judul, 'DATASET'::text as tipe, updated_at as tanggal, 'dataset_detail' as link FROM t_publikasi
-- WHERE status_id = 3
ORDER BY
tanggal DESC
LIMIT 5
";
$stmt = $connection->query($sql);
// $stmt->execute();
$result = $stmt->fetchAllAssociative();
$TDokumenRepo = $this->getDoctrine()->getRepository(TDokumen::class);
$mKategoriDetailRepo = $this->getDoctrine()->getRepository(MKategoriDetail::class);
foreach ($result as $k => $rs) {
$result[$k]['icon'] = '';
if ($rs['tipe'] == 'INFOGRAFIK') {
$dokumen = $TDokumenRepo->findBy(['reff_id' => $rs['id'], 'reff_name' => 'INFOGRAFIK'], ['id' => 'asc'], 1);
if ($dokumen) {
$result[$k]['icon'] = $dokumen[0]->getPath();
}
} else {
$kategori = $mKategoriDetailRepo->findBy(['reff_id' => $rs['id'], 'reff_name' => 'PUBLIKASI'], ['id' => 'asc'], 1);
if ($kategori) {
$result[$k]['icon'] = $kategori[0]->getKategori()->getIcon();
}
}
}
return $result;
}
public function getGrafikDataset(TPublikasi $detail, $p_order)
{
$tGrafikRepo = $this->getDoctrine()->getRepository(TGrafik::class);
$tDataRepo = $this->getDoctrine()->getRepository(TData::class);
$tVariabelRepo = $this->getDoctrine()->getRepository(TVariabel::class);
$grafik = $tGrafikRepo->findOneBy(['pub' => $detail->getId()]);
if ($grafik) {
$tdataIsi = $tDataRepo->findBy(['pub' => $detail->getId(), 'variabel' => $grafik->getAxisX()], ['id' => 'asc']);
$tdataKategori = $tDataRepo->findBy(['pub' => $detail->getId(), 'variabel' => $grafik->getAxisY()], ['id' => 'asc']);
$variabel_x = $tVariabelRepo->find($grafik->getAxisX());
$variabel_y = $tVariabelRepo->find($grafik->getAxisY());
$data_chart = $this->getTabelGrafik($detail->getId(), $variabel_x->getNama(), $p_order);
// dump($test); exit;
// $data_chart = array();
// foreach($tdataIsi as $td){
// $data_chart[] = floatval($td->getIsi());
// }
$data_kategori = array();
foreach ($tdataKategori as $td) {
$data_kategori[] = $td->getIsi();
}
// Chart
$series = array(
array("name" => $variabel_x->getNama(), "data" => $data_chart)
);
$ob = new Highchart();
$ob->chart->renderTo('linechart'); // The #id of the div where to render the chart
$ob->chart->type($grafik->getTipe());
$ob->title->text($grafik->getJudul());
$ob->xAxis->title(array('text' => ""));
$ob->xAxis->categories($data_kategori);
$ob->yAxis->title(array('text' => ""));
$ob->series($series);
return $ob;
}
return false;
}
public function dashboardSearch($search)
{
$connection = $this->getDoctrine()->getConnection();
$sql =
"SELECT * from
(
select distinct on (x.tipe, x.id) b.nama as kategori, x.*
from m_kategori_detail a
join m_kategori b
on a.kategori_id = b.id
right join
(
select * from (
select 'DATASET'::text as tipe, a.id as id, a.judul as judul, a.deskripsi as deskripsi, c.nama as urusan, d.nama as sektor, b.div_nama as div, a.updated_at as tanggal
from t_publikasi a
join t_div b
on a.div_id = b.id
join m_urusan c
on a.urusan_id = c.id
join m_sektor d
on a.sektor_id = d.id
where a.status_id = 3
UNION ALL
select 'INFOGRAFIK'::text as tipe, a.id as id, a.judul as judul, a.deskripsi as deskripsi, ''::text as urusan, ''::text as sektor, a.sumber::text as div, a.created_at as tanggal
from t_infografik a
where a.is_arsip = false
) as search
) as x
on a.reff_id = x.id and a.reff_name = x.tipe
) as fix
where judul ilike '%$search%' or tipe ilike '%$search%' or deskripsi ilike '%$search%' or urusan ilike '%$search%' or sektor ilike '%$search%' or div ilike '%$search%'
order by tanggal desc
limit 5";
$stmt = $connection->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
return $result;
}
public function buttonCredentials(string $name = null)
{
$ptiRuleRepo = $this->getDoctrine()->getRepository(PtiRule::class);
$user_group = $this->getUser()->getRoles()[0];
$qb = $ptiRuleRepo->createQueryBuilder('t')
->join('t.groups', 'g')
->join('t.module_action', 'ma')
->join('ma.module', 'm')
->select('t.id, m.url, ma.action')
->andWhere('g.credential = :credential')->setParameter('credential', $user_group)
->andWhere('t.is_allowed = true');
if (null !== $name) {
$qb
->andWhere('m.url = :url')->setParameter('url', $name);
}
$rules = $qb->getQuery()->getResult();
$credentials = [];
foreach ($rules as $rule) {
if (!isset($credentials[$rule['url']])) {
$credentials[$rule['url']] = [];
}
$credentials[$rule['url']][$rule['action']] = $rule['id'];
}
return $credentials;
}
public function getPeriodeName($pId)
{
$tipex = array();
// Hari
// if ($pId == 5) {
// return intval($key);
// }
// Bulan
if ($pId == 6) {
$tipex = array(
1 => 'Januari',
2 => 'Februari',
3 => 'Maret',
4 => 'April',
5 => 'Mei',
6 => 'Juni',
7 => 'Juli',
8 => 'Agustus',
9 => 'September',
10 => 'Oktober',
11 => 'November',
12 => 'Desember'
);
}
// Triwulan
else if ($pId == 7) {
$tipex = array(
1 => 'Triwulan I',
2 => 'Triwulan II',
3 => 'Triwulan III',
4 => 'Triwulan IV',
);
}
// Semester
else if ($pId == 8) {
$tipex = array(
1 => 'Semester I',
2 => 'Semester II',
);
}
// Tahun
else if ($pId == 9) {
$tipex = array(
1 => 'Tahun',
);
}
return $tipex;
}
public function experimentGrouping($id, $variabel, $tahun)
{
$connection = $this->getDoctrine()->getConnection();
$tbl_func = "CREATE EXTENSION IF NOT EXISTS tablefunc; ";
$stmt = $connection->prepare($tbl_func);
$stmt->execute();
$publikasi_repo = $this->getDoctrine()->getRepository(TPublikasi::class);
$variabel_repo = $this->getDoctrine()->getRepository(TVariabel::class);
$as_columns = "";
$newVariable =
$variabel_repo->findBy(['pub' => $id, 'is_tampil' => true], ['id' => 'asc']);
foreach ($newVariable as $var) {
$as_columns .= ", " . $var->getNama() . " text";
}
$selCols = [];
$selCols[] = "ct.periode_update";
$containPeriodeUpdate = false;
foreach ($variabel as $col) {
if ($col->getNama() == 'periode_update') {
$containPeriodeUpdate = true;
continue;
}
$selCols[] = "ct." . $col->getNama();
}
$selCols = implode(", ", $selCols);
// $this->hitungRataRata($id);
$newSql = "SELECT $selCols FROM crosstab('select a.row_id, b.id, a.isi
from t_data a
join t_variabel b
on a.variabel_id = b.id
where a.pub_id = " . $id . " and b.is_tampil is true";
$newSql .= " order by 1, 2')
AS ct (row_id int" . $as_columns . ") where 1=1";
$newSql .= " and ct.periode_update ilike '%" . $tahun . "%'";
$stmt = $connection->prepare($newSql);
$stmt->execute();
$result = $stmt->fetchAll();
$newResult = $result;
if (!$containPeriodeUpdate) {
foreach ($newResult as $key => $value) {
unset($value['periode_update']);
$newResult[$key] = $value;
}
}
$data = array_map('array_values', $newResult);
$periode = [];
$kategori = [];
$series = [];
$length_x_axis = 1;
$first = reset($newResult);
$header = array_keys($first);
foreach ($data as $key => $dt) {
$periode[$key] = $dt[0];
foreach ($dt as $k => $val) {
if ($k > 0) {
$series[$k][$key] = $val;
}
}
}
$periode = array_unique($periode);
$kategori = array_unique($kategori);
//jika periode_update kosong atau hanya memilih periode update
if ($containPeriodeUpdate) {
$minValue = null;
$maxValue = null;
$looped_variabel = $length_x_axis == 1 ? $periode : $kategori;
$tahunData = [];
foreach ($periode as $k => $v) {
$result = array_filter($newResult, function ($data) use ($v) {
return str_contains($data['periode_update'], $v);
});
foreach ($result as $key_result => $value_result) {
$tahunData[$v][] = $value_result['periode_update'];
}
}
foreach ($series as $k => $v) {
$index = 0;
$total = 0;
$decrementValue = 0;
foreach ($tahunData as $key_tahun => $value_tahun) {
for ($x = 0; $x < count($value_tahun); $x++) {
$total += round(str_replace(',', '', $v[$index]));
$index++;
$decrementValue++;
}
$series[$k][$index - $decrementValue] = $total;
$total = 0;
$decrementValue = 0;
}
}
// foreach ($looped_variabel as $k => $val) {
// if ($minValue === null) {
// $minValue = $k;
// } else {
// $maxValue = $k;
// break;
// }
// }
// $selisih = $maxValue - $minValue;
// $looped_variabel = $length_x_axis == 1 ? $periode : $kategori;
// foreach($series as $key_series => $value_series) {
// if($selisih == 0) {
// } else {
// $total = 0;
// $countJump = 0;
// foreach ($looped_variabel as $key => $value) {
// for ($x = 0; $x < $selisih; $x++) {
// $total = $total + round(str_replace(',', '', ($value_series[$minValue + $x + ($selisih * $countJump)])));
// }
// $series[$key_series][$key] = $total;
// $total = 0;
// $countJump++;
// }
// }
// }
} else {
$maxValue = null;
$minValue = null;
$jumptCount = 0;
$jump = 0;
$looped_variabel = $length_x_axis == 1 ? $periode : $kategori;
foreach ($looped_variabel as $k => $val) {
if ($minValue === null) {
$minValue = $k;
$maxValue = $k;
} else if ($k > $maxValue) {
$maxValue = $k;
}
}
$selisih = count($looped_variabel);
foreach ($series as $key_series => $value_series) {
$total = 0;
for ($x = 0; $x < $selisih; $x++) {
while (true) {
if (isset($value_series[$minValue + $x + (($selisih) * $jumptCount)])) {
$total = $total + round(str_replace(',', '', ($value_series[$minValue + $x + (($selisih) * $jumptCount)])));
$jumptCount++;
$jump++;
} else {
$series[$key_series][$minValue + $x] = $total;
$total = 0;
$jumptCount = 0;
break;
}
}
}
}
}
foreach ($series as $k => $v) {
$series[$k] = array_filter($series[$k], function ($data) {
return gettype($data) == 'double';
});
}
$minMax = $this->hitungMinMaxValues($series, $header, $periode);
$total = 0;
foreach ($series as $k => $v) {
foreach ($v as $ke_value => $value) {
$total += $value;
}
}
return ['total' => $total, 'min-max' => $minMax];
// $rs = [];
// $i = 0;
// foreach ($periode as $k => $p) {
// if ($length_x_axis == 1) {
// $rs['categories'][] = $p;
// } else {
// $rs[$i]['categories']['name'] = $p;
// foreach ($kategori as $kat) {
// $rs[$i]['categories'][] = $kat;
// }
// }
// $i++;
// }
// $i = 0;
// foreach ($series as $k => $seri) {
// $rs['series'][$i]['name'] = $header[$k];
// foreach ($seri as $s) {
// $rs['series'][$i]['data'][] = round($s);
// }
// $i++;
// }
}
public function expereimentGrafik($id)
{
$connection = $this->managerRegistry->getConnection();
$tbl_func = "CREATE EXTENSION IF NOT EXISTS tablefunc; ";
$stmt = $connection->prepare($tbl_func);
$stmt->execute();
$publikasi_repo = $this->managerRegistry->getRepository(TPublikasi::class);
$variabel_repo = $this->managerRegistry->getRepository(TVariabel::class);
$x_axis = $variabel_repo->findBy(['id' => [13021, 13022]]);
$y_axis = $variabel_repo->findBy(['id' => [13023, 13024]]);
$as_columns = "";
$selCols = [];
$variabel = $variabel_repo->findBy(['pub' => $id, 'is_tampil' => true], ['id' => 'asc']);
foreach ($variabel as $var) {
$as_columns .= ", " . $var->getNama() . " text";
}
foreach ($variabel as $col) {
$selCols[] = "ct." . $col->getNama();
}
$selCols = implode(", ", $selCols);
$newSql = ";with C as (
Select $selCols from
crosstab('select a.row_id, b.id, a.isi
from t_data a
join t_variabel b
on a.variabel_id = b.id
where a.pub_id = " . $id . " and b.is_tampil is true";
$newSql .= " order by 1, 2')
AS ct (row_id int" . $as_columns . ")
where 1=1
)
select C.periode_update, C.jenis_kunjungan ,SUM(CAST (replace(C.laki_laki, ',', '') AS FLOAT)) as laki_laki
from C
group by C.periode_update, C.jenis_kunjungan
;";
$stmt = $connection->prepare($newSql);
$stmt->execute();
$result = $stmt->fetchAll();
//contain periode update
if (true) {
$label_grafik = [];
$value_grafik = [];
$years = [];
$orderedPeriode = [];
$orderedData = [];
foreach ($result as $key => $value) {
if (!in_array(preg_replace('/[^0-9]/', '', $value['periode_update']), $years)) {
$years[] = preg_replace('/[^0-9]/', '', $value['periode_update']);
}
}
$publikasi = $publikasi_repo->find($id);
$opt_periode = $this->getPeriodeName($publikasi->getPeriode()->getId());
sort($years);
foreach ($years as $k => $v) {
foreach ($opt_periode as $periode_key => $periode_v) {
$orderedPeriode[] = $periode_v . ' ' . $v;
}
}
foreach ($orderedPeriode as $k => $v) {
foreach ($result as $k_result => $v_result) {
if ($v_result['periode_update'] == $v) {
$orderedData[] = $v_result;
}
}
}
$data = array_map('array_values', $orderedData);
$first = reset($orderedData);
$header = array_keys($first);
foreach ($data as $k => $v) {
foreach ($v as $key => $value) {
if ($key < count($x_axis)) {
$label_grafik[$key][] = $value;
} else {
$value_grafik[$header[$key]][] = $value;
}
}
}
foreach ($label_grafik as $k => $v) {
$label_grafik[$k] = array_unique($v);
}
dump($label_grafik);
$categories = [];
$count = 0;
while (true) {
foreach ($label_grafik[$count] as $k => $v) {
if ($count == 0) {
$categories[] = [
'name' => $v,
'categories' => []
];
} else {
foreach ($categories as $category) {
$tempCategory = null;
while (true) {
if ($tempCategory == null) {
$tempCategory = $category['categories'];
}
if (isset($tempCategory['categories'])) {
$tempCategory = $tempCategory['categories']['categories'];
} else {
if ($count == count($label_grafik) - 1) {
foreach ($label_grafik[$count] as $value) {
$tempCategory['categories'][] = $v;
}
} else {
$cats = [];
foreach ($label_grafik[$count] as $value) {
$cats[] = $v;
}
$tempCategory['categories'] = [
'nama' => $v,
'categories' => $cats
];
}
break;
}
}
}
}
}
if ($count == count($label_grafik) - 1) {
break;
} else {
$count++;
}
}
dump($categories);
exit;
}
}
public function getCrossTabData($id)
{
$connection = $this->getDoctrine()->getConnection();
$tbl_func = "CREATE EXTENSION IF NOT EXISTS tablefunc; ";
$stmt = $connection->prepare($tbl_func);
$stmt->execute();
$variabel_repo = $this->getDoctrine()->getRepository(TVariabel::class);
$as_columns = "";
$newVariable =
$variabel_repo->findBy(['pub' => $id, 'is_tampil' => true], ['id' => 'asc']);
foreach ($newVariable as $var) {
$as_columns .= ", " . $var->getNama() . " text";
}
$newSql = "SELECT * FROM crosstab('select a.row_id, b.id, a.isi
from t_data a
join t_variabel b
on a.variabel_id = b.id
where a.pub_id = " . $id . " and b.is_tampil is true";
$newSql .= " order by 1, 2')
AS ct (row_id int" . $as_columns . ") where 1=1";
$stmt = $connection->prepare($newSql);
$stmt->execute();
$result = $stmt->fetchAll();
$tahun = [];
foreach ($result as $key => $value) {
if (!in_array(preg_replace('/[^0-9]/', '', $value['periode_update']), $tahun)) {
$tahun[] = preg_replace('/[^0-9]/', '', $value['periode_update']);
}
}
sort($tahun);
return [
'crosstab' => $result[0],
'tahun' => $tahun
];
}
public function hitungMinMaxValues($series, $header, $periode)
{
$results = [];
// dump($series);
// dump($header);
// dump($periode);
// exit;
foreach ($series as $k => $v) {
// $total = 0;
// foreach($v as $key_value => $value) {
// $total += $value;
// }
// $results[$k] = $total;
$maxValue = max($v);
$minValue = min($v);
$labelValue = $header[$k];
$keyMax = array_keys($v, max($v))[0];
$keyMin = array_keys($v, min($v))[0];
$labelVarMin = $periode[$keyMin];
$labelVarMax = $periode[$keyMax];
}
return ['max' => [
"$labelValue / $labelVarMax" => $maxValue,
], 'min' => [
"$labelValue / $labelVarMin" => $minValue,
]];
}
public function hitungRataRata($id, $variabel)
{
$connection = $this->getDoctrine()->getConnection();
$tbl_func = "CREATE EXTENSION IF NOT EXISTS tablefunc; ";
$stmt = $connection->prepare($tbl_func);
$stmt->execute();
$publikasi_repo = $this->getDoctrine()->getRepository(TPublikasi::class);
$variabel_repo = $this->getDoctrine()->getRepository(TVariabel::class);
$as_columns = "";
$newVariable =
$variabel_repo->findBy(['pub' => $id, 'is_tampil' => true], ['id' => 'asc']);
foreach ($newVariable as $var) {
$as_columns .= ", " . $var->getNama() . " text";
}
$selCols[] = 'ct.periode_update';
foreach ($variabel as $col) {
$selCols[] = "ct." . $col->getNama();
}
$selCols = implode(", ", $selCols);
$newSql = "SELECT $selCols FROM crosstab('select a.row_id, b.id, a.isi
from t_data a
join t_variabel b
on a.variabel_id = b.id
where a.pub_id = " . $id . " and b.is_tampil is true";
$newSql .= " order by 1, 2')
AS ct (row_id int" . $as_columns . ") where 1=1";
$stmt = $connection->prepare($newSql);
$stmt->execute();
$result = $stmt->fetchAll();
$tahun = array();
$indexTahun = [];
$newResult = $result;
//extract tahun rata-rata
foreach ($newResult as $key => $value) {
if (!in_array(preg_replace('/[^0-9]/', '', $value['periode_update']), $tahun)) {
$tahun[] = preg_replace('/[^0-9]/', '', $value['periode_update']);
}
}
sort($tahun);
$orderedResult = array();
$tahunData = [];
foreach ($tahun as $k => $v) {
$result = array_filter($newResult, function ($data) use ($v) {
return str_contains($data['periode_update'], $v);
});
foreach ($result as $key_result => $value_result) {
$orderedResult[] = $value_result;
$tahunData[$v][] = $value_result['periode_update'];
}
}
$data = array_map('array_values', $orderedResult);
$periode = [];
$kategori = [];
$series = [];
$length_x_axis = 1;
$first = reset($newResult);
$header = array_keys($first);
foreach ($data as $key => $dt) {
$periode[$key] = $dt[0];
foreach ($dt as $k => $val) {
if ($k > 0) {
$series[$k][$key] = $val;
}
}
}
$periode = array_unique($periode);
$kategori = array_unique($kategori);
$results = [];
$index = 0;
foreach ($tahunData as $k => $v) {
$total = 0;
foreach ($series as $key_series => $value_series) {
for ($x = 0; $x < count($v); $x++) {
$total += round(str_replace(',', '', $value_series[$index]));
$index++;
}
$results[$k][] = $total;
$total = 0;
}
}
$totalData = 0;
foreach ($results as $k => $v) {
$totalData += $v[0];
}
$hasil = ['tahun' => count($tahun), 'rata-rata' => $totalData / count($tahun)];
return $hasil;
// //hitung rata-rata
// foreach ($tahun as $year) {
// foreach ($periode as $k => $v) {
// if (str_contains($v, $year)) {
// $indexTahun[$year][] = $k;
// }
// }
// }
// $lastIndexOfIndexTahun = [];
// foreach ($indexTahun as $k => $values) {
// $lastIndexOfIndexTahun[$k][] = count($values);
// }
// $minValue = null;
// $maxValue = null;
// $looped_variabel = $length_x_axis == 1 ? $periode : $kategori;
// foreach ($looped_variabel as $k => $val) {
// if ($minValue === null) {
// $minValue = $k;
// } else {
// $maxValue = $k;
// break;
// }
// }
// $selisih = $maxValue - $minValue;
// dump($periode);
// dump($selisih);
// dump($series);
// exit;
// $looped_variabel = $length_x_axis == 1 ? $periode : $kategori;
// foreach ($series as $key_series => $value_series) {
// $total = 0;
// $countJump = 0;
// foreach ($looped_variabel as $key => $value) {
// for ($x = 0; $x < $selisih; $x++) {
// $total = $total + round(str_replace(',', '', ($value_series[$minValue + $x + ($selisih * $countJump)])));
// }
// $series[$key_series][$key] = $total;
// $total = 0;
// $countJump++;
// }
// }
// foreach ($series as $k => $v) {
// $series[$k] = array_filter($series[$k], function ($data) {
// return gettype($data) == 'double';
// });
// }
// $totalData = 0;
// foreach ($series as $key => $value) {
// $totalData += count($value);
// foreach ($indexTahun as $key_tahun => $value_tahun) {
// foreach ($value_tahun as $index_tahun) {
// foreach ($value as $k => $v) {
// if ($index_tahun == $k) {
// if (isset($indexTahun[$key_tahun][$lastIndexOfIndexTahun[$key_tahun][0]])) {
// $indexTahun[$key_tahun][$lastIndexOfIndexTahun[$key_tahun][0]] += $value[$k];
// } else {
// $indexTahun[$key_tahun][$lastIndexOfIndexTahun[$key_tahun][0]] = $value[$k];
// }
// }
// }
// }
// }
// }
// $total = 0;
// foreach ($indexTahun as $kunci => $nilai) {
// $total += end($nilai);
// }
// $totalData = 0;
// foreach ($oldSeries as $key_old => $value_old) {
// $totalData += count($value_old);
// }
}
public function getKategori($id, $periode, $x_axis)
{
$variabel_repo = $this->getDoctrine()->getRepository(TVariabel::class);
$connection = $this->getDoctrine()->getConnection();
$tbl_func = "CREATE EXTENSION IF NOT EXISTS tablefunc; ";
$stmt = $connection->prepare($tbl_func);
$stmt->execute();
$as_columns = "";
$newVariable =
$variabel_repo->findBy(['pub' => $id, 'is_tampil' => true], ['id' => 'asc']);
foreach ($newVariable as $var) {
$as_columns .= ", " . $var->getNama() . " text";
}
$sql = "SELECT * FROM crosstab('select a.row_id, b.id, a.isi
from t_data a
join t_variabel b
on a.variabel_id = b.id
where a.pub_id = " . $id . " and b.is_tampil is true";
$sql .= " order by 1, 2')
AS ct (row_id int" . $as_columns . ") where 1=1";
$sql .= " and ct.periode_update ilike '%" . $periode . "%'";
$stmt = $connection->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
return $result;
}
public function getTabelGrafikDinamis($id, $cols, $p_order)
{
$connection = $this->getDoctrine()->getConnection();
$tbl_func = "CREATE EXTENSION IF NOT EXISTS tablefunc; ";
$stmt = $connection->prepare($tbl_func);
$stmt->execute();
$variabel_repo = $this->getDoctrine()->getRepository(TVariabelGrafik::class);
$variabel = $variabel_repo->findBy(['grafik' => $id], ['id' => 'asc']);
$as_columns = "";
foreach ($variabel as $var) {
$as_columns .= ", " . $var->getNama() . " varchar";
}
$selCols = [];
foreach ($cols as $col) {
$selCols[] = "ct." . $col->getNama();
}
$selCols = implode(", ", $selCols);
// $sql = "SELECT $selCols, ct.periode_order, ct.periode_update
$sql = "SELECT $selCols
FROM crosstab('select a.row_id, b.id, a.isi
from tdata_grafik a
join tvariabel_grafik b
on a.variabel_id = b.id
where a.grafik_id = " . $id;
$sql .= " order by 1, 2')
AS ct (row_id int" . $as_columns . ") where 1=1";
// $sql .= " and ct.periode_update = '$p_order'";
$stmt = $connection->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
// foreach ($stmt->fetchAll() as $data) {
// $result[] = floatval($data[$col]);
// }
return $result;
}
public function datasetCkanQuery($request, $limit = null, $offset = null, $doCount = false)
{
$connection = $this->managerRegistry->getConnection();
$sql =
"SELECT * from
(";
if ($request->get('kategori')) {
$sql .= "SELECT a.kategori_id as kategori_id, b.nama as kategori, x.*
from m_kategori_detail a
join m_kategori b
on a.kategori_id = b.id
right join
(";
}
if ($request->get('opd')) {
$sql .= "SELECT * from (
select 'PUBLIKASI'::text as tipe, a.id as id, a.judul as judul, b.id as div_id, b.div_nama as div, a.sektor_id as sektor, a.created_at as tanggal, a.view as view, b.parent as parent
from t_publikasi a
join t_div b
on a.div_id = b.id
where a.status_id = 3
and a.id NOT IN (select distinct(t_publikasi.id) from t_publikasi inner join t_ckan on t_publikasi.judul = t_ckan.title and t_ckan.div_id_id = {$request->get('opd')})
UNION ALL
select 'CKAN'::text as tipe, a.id as id, a.title as judul, b.id as div_id, b.div_nama as div, null::integer as sektor, a.metadata_created as tanggal, a.view as view, b.parent as parent
from t_ckan a
join t_div b
on a.div_id_id = b.id
) as search";
} else {
$sql .= "SELECT * from (
select 'PUBLIKASI'::text as tipe, a.id as id, a.judul as judul, b.id as div_id, b.div_nama as div, a.sektor_id as sektor, a.created_at as tanggal, a.view as view, b.parent as parent
from t_publikasi a
join t_div b
on a.div_id = b.id
where a.status_id = 3
UNION ALL
select 'CKAN'::text as tipe, a.id as id, a.title as judul, b.id as div_id, b.div_nama as div, null::integer as sektor, a.metadata_created as tanggal, a.view as view, b.parent as parent
from t_ckan a
join t_div b
on a.div_id_id = b.id
) as search";
}
if ($request->get('opd')) {
$sql .= " where div_id=" . $request->get('opd');
}
// else if ($request->get('opdx')) {
// $sql .= " where div_id=".$request->get('opdx') . " and tipe='CKAN' ";
// }
$sql .= ") as x";
if ($request->get('kategori')) {
$sql .= " on a.reff_id = x.id and a.reff_name = x.tipe ";
$sql .= " where b.id=" . $request->get('kategori');
$sql .= ") as fix where 1=1";
}
if ($request->get('judul') || $request->get('sektor')) {
$sql .= " where 1=1";
}
if ($request->get('judul')) {
$sql .= " and judul ilike '%" . $request->get('judul') . "%'";
}
if ($request->get('sektor')) {
$sql .= " and sektor =" . $request->get('sektor');
}
$sql .= " order by parent asc, tanggal desc";
if ($limit) {
$sql .= " limit $limit";
}
if ($offset) {
$sql .= " offset $offset";
}
$stmt = $connection->prepare($sql);
$stmt->execute();
if ($doCount) {
$result = $stmt->rowCount();
} else {
$result = $stmt->fetchAll();
}
return $result;
}
public function getLinks($page, $lastPage, $nb_links = 5)
{
$links = array();
$tmp = $page - floor($nb_links / 2);
$check = $lastPage - $nb_links + 1;
$limit = $check > 0 ? $check : 1;
$begin = $tmp > 0 ? ($tmp > $limit ? $limit : $tmp) : 1;
$i = (int) $begin;
while ($i < $begin + $nb_links && $i <= $lastPage) {
$links[] = $i++;
}
$this->currentMaxLink = count($links) ? $links[count($links) - 1] : 1;
return $links;
}
public function genCodeDataset()
{
# code...
}
public function getDataTableFeedback($limit, $page, $get, $survey, $cetak = false, $doCount = false)
{
$isiRepo = $this->getDoctrine()->getRepository(FeedbackIsi::class);
$MFeedback = $this->MFeedback->findBy(['is_active' => true, 'survey' => $survey], ['no_urut' => 'asc']);
$dataFeedback = array();
if ($doCount) {
foreach ($MFeedback as $key => $value) {
$dataFeedback[] = count($value->getFeedbackIsis());
}
return max($dataFeedback);
}
foreach ($MFeedback as $key => $value) {
$qb = $isiRepo->createQueryBuilder('t')
->andWhere("t.feedback_id = :feedback_id")->setParameter('feedback_id', $value->getId());
if ($get["q" . $value->getId()]) {
$search = strtolower($get["q" . $value->getId()]);
$qb->andWhere("lower(t.isi) like :isi")->setParameter('isi', "%" . $search . "%");
}
$qb->setMaxResults($limit)->setFirstResult($page);
$qb->addOrderBy('t.id', 'desc');
$feedbackIsi = $qb->getQuery()->getResult();
// $feedbackIsi = $this->feedbackIsi->findBy(['feedback_id' => $value->getId()], ['id' => 'desc'], $limit, $page);
$dataFeedback[$value->getId()] = [];
foreach ($feedbackIsi as $isi) {
if ($cetak) {
$isinya = $isi->getIsi();
} else {
if ($value->getTipe() == 4) {
$isinya = '';
for ($i = 1; $i <= intval($isi->getIsi()); $i++) {
$isix = '<i class="fa fa-star text-warning"></i>';
$isinya .= $isix;
}
if (intval($isi->getIsi()) != 5) {
for ($i = 1; $i <= 5 - $isi->getIsi(); $i++) {
$isix = '<i class="fa fa-star"></i>';
$isinya .= $isix;
}
}
} else {
$isinya = $isi->getIsi();
}
}
$dataFeedback[$value->getId()][] = $isinya;
}
}
$map = array_map('count', $dataFeedback);
arsort($map);
$keyMax = array_keys($map)[0];
$result = [];
foreach ($dataFeedback as $data) {
foreach ($dataFeedback[$keyMax] as $k => $v) {
$result[$k][] = (isset($data[$k])) ? $data[$k] : '';
}
}
return $result;
}
public function getTipeFeedback()
{
$tipe = ['1' => 'Input', '2' => 'Select', '3' => 'Text Area', '4' => 'Rating'];
return $tipe;
}
public function getTipeInfoGrafik()
{
$tipe = ['1' => 'Infografik', '2' => 'E-Book'];
return $tipe;
}
public function getIp()
{
return ($_SERVER['REMOTE_ADDR'] == '127.0.0.1') ? $_SERVER['REMOTE_ADDR'] : $_SERVER['HTTP_X_FORWARDED_FOR'];
}
public function countJumlahFeedback()
{
$connection = $this->getDoctrine()->getConnection();
// $connection = DriverManager::getConnection($this->connParams);
$today = date("Y-m-d");
$mulai = date("Y-m-d") . " 00:00:01";
$sampai = date("Y-m-d") . " 23:59:59";
$ip_user = self::getIp();
$sql = "SELECT id, ip_user, created_at as tanggal FROM feedback WHERE created_at >= '" . $mulai . "'" . " AND created_at <= '" . $sampai . "'" . " AND ip_user = '" . $ip_user . "'";
$stmt = $connection->query($sql);
$result = $stmt->fetchAllAssociative();
return count($result);
}
public function countJumlahDownload($reffid = null)
{
$connection = $this->getDoctrine()->getConnection();
// $connection = DriverManager::getConnection($this->connParams);
$today = date("Y-m-d");
$mulai = date("Y-m-d") . " 00:00:01";
$sampai = date("Y-m-d") . " 23:59:59";
$ip_user = self::getIp();
if ($reffid) {
$sql = "SELECT id, ip_user, created_at as tanggal FROM tdownload WHERE created_at >= '" . $mulai . "'" . " AND created_at <= '" . $sampai . "'" . " AND reff_id = '" . $reffid . "'" . " AND ip_user = '" . $ip_user . "'";
} else {
$sql = "SELECT id, ip_user, created_at as tanggal FROM tdownload WHERE created_at >= '" . $mulai . "'" . " AND created_at <= '" . $sampai . "'" . " AND ip_user = '" . $ip_user . "'";
}
$stmt = $connection->query($sql);
$result = $stmt->fetchAllAssociative();
return count($result);
}
public function mapArray($obj)
{
$mKotaRepository = $this->getDoctrine()->getRepository(MKota::class);
$data = array();
$count = 0;
foreach ($obj as $key => $arr) {
$data[$arr['kota_id']] = $arr['count'];
}
return $data;
}
public function getGrafikPeta($id, $cols, $axisX, $p_order)
{
$period = explode("#", $p_order);
// $periode = ($period[1] == 'All') ? $period[0] : $period[0].' '.$period[1];
$connection = $this->getDoctrine()->getConnection();
// print_r($periode); exit;
$tbl_func = "CREATE EXTENSION IF NOT EXISTS tablefunc; ";
$stmt = $connection->prepare($tbl_func);
$stmt->execute();
$publikasi_repo = $this->getDoctrine()->getRepository(TPublikasi::class);
$variabel_repo = $this->getDoctrine()->getRepository(TVariabel::class);
$variabel = $variabel_repo->findBy(['pub' => $id], ['id' => 'asc']);
$publikasi = $publikasi_repo->find($id);
if ($period[1] == 'All') {
$opt_periode = $this->getPeriodeName($publikasi->getPeriode()->getId());
foreach ($opt_periode as $k => $v) {
if ($period[0] == $v) {
$periodex = $k;
}
}
} else {
$periodex = $period[0] . ' ' . $period[1];
}
// dump($variabel); exit;
$as_columns = "";
foreach ($variabel as $var) {
$as_columns .= ", " . $var->getNama() . " varchar";
}
$selCols = [];
foreach ($cols as $col) {
$selCols[] = "ct." . $col->getNama();
}
$selCols = implode(", ", $selCols);
$sql = "SELECT $selCols, ct.periode_order, ct.periode_update,$axisX
FROM crosstab('select a.row_id, b.id, a.isi
from t_data a
join t_variabel b
on a.variabel_id = b.id
where a.pub_id = " . $id;
$sql .= " order by 1, 2')
AS ct (row_id int" . $as_columns . ") where 1=1";
if ($period[0] == 'All' && $period[1] == 'All') {
} else {
if ($period[0] == 'All' && $period[1] != 'All') {
$sql .= " and ct.periode_update ilike '%$period[1]%'";
} else if ($period[1] == 'All') {
$sql .= " and ct.periode_update ilike '%$period[0]%'";
// $sql .= " and ct.periode_order = '$periodex'";
} else {
$sql .= " and ct.periode_update = '$periodex'";
}
}
$stmt = $connection->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
return $result;
}
public function getBulan()
{
return array('01' => 'Januari', '02' => 'Februari', '03' => 'Maret', '04' => 'April', '05' => 'Mei', '06' => 'Juni', '07' => 'Juli', '08' => 'Agustus', '09' => 'September', '10' => 'Oktober', '11' => 'Nopember', '12' => 'Desember');
}
public function getDataTableMetaData($id, $limit, $page, $get, $state = null, $row = null)
{
$connection = $this->getDoctrine()->getConnection();
// $connection = DriverManager::getConnection($this->connParams);
$meta_data_repo = $this->getDoctrine()->getRepository(TMetaData::class);
$variabel_repo = $this->getDoctrine()->getRepository(TVariabel::class);
$variabel = $variabel_repo->findBy(['reff_id' => $id, 'reff_kode' => 'METADATA'], ['id' => 'asc']);
$meta_data = $meta_data_repo->findOneBy(['id' => $id]);
$sql = "SELECT * FROM " . $meta_data->getNamaTabel();
// if ($row) {
// $sql .= " and row_id = " . $row;
// }
if ($get) {
foreach ($variabel as $var) {
if ($value = $get[$var->getNama()]) {
$sql .= " WHERE " . $var->getNama() . "::text ILIKE '%" . $value . "%'";
}
}
}
if ($limit) {
$sql .= " LIMIT " . $limit;
}
if ($page) {
$sql .= " OFFSET " . $page;
}
$stmt = $connection->prepare($sql);
$stmt->execute();
$result = array();
foreach ($stmt->fetchAll() as $data) {
$temp = array();
foreach ($variabel as $var) {
$temp[] = $data[$var->getNama()];
}
$result[] = $temp;
}
// print_r($result);exit;
return $result;
}
public function getCardDashboard($tabel, $tahap, $count, $kolom_periode, $tanggal_awal, $tanggal_akhir, $tipe_tahun, $tahun, $sumber_dana)
{
$connection = $this->getDoctrine()->getConnection();
$filter = ($tipe_tahun == 1) ? "t.tahun_anggaran = '$tahun'" : "t.$kolom_periode BETWEEN '$tanggal_awal' AND '$tanggal_akhir'";
if ($count) {
// $sql = "SELECT count(*) from $tabel t WHERE t.tahun_anggaran IS NOT NULL
// AND t.sumber_dana IS NOT NULL AND t.$kolom_periode BETWEEN '$tanggal_awal' AND '$tanggal_akhir'";
$sql = "SELECT count(*) from $tabel t WHERE $filter";
if ($tahap == 'tahap_pembuatan_paket') {
$sql .= " AND t.kategori_tahap='Tahap pembuatan paket'";
} else if ($tahap == 'tahap_penyusunan_hps') {
$sql .= " AND t.kategori_tahap='Tahap penyusunan hps'";
} else if ($tahap == 'tahap_pemilihan_penyedia') {
$sql .= " AND t.kategori_tahap IN ('Dalam proses (pemilihan penyedia)','Selesai (pemilihan penyedia)')";
} else if ($tahap == 'dalam_proses') {
$sql .= " AND t.kategori_tahap='Dalam proses (pemilihan penyedia)'";
} else if ($tahap == 'selesai') {
$sql .= " AND t.kategori_tahap='Selesai (pemilihan penyedia)'";
}
} else {
// $sql = "SELECT * FROM $tabel t WHERE t.tahun_anggaran IS NOT NULL
// AND t.sumber_dana IS NOT NULL AND t.$kolom_periode BETWEEN '$tanggal_awal' AND '$tanggal_akhir'";
$sql = "SELECT * FROM $tabel t WHERE $filter";
if ($tahap == 'tahap_pembuatan_paket') {
$sql .= " AND t.kategori_tahap='Tahap pembuatan paket'";
} else if ($tahap == 'tahap_penyusunan_hps') {
$sql .= " AND t.kategori_tahap='Tahap penyusunan hps'";
} else if ($tahap == 'tahap_pemilihan_penyedia') {
$sql .= " AND t.kategori_tahap IN ('Dalam proses (pemilihan penyedia)','Selesai (pemilihan penyedia)')";
} else if ($tahap == 'dalam_proses') {
$sql .= " AND t.kategori_tahap='Dalam proses (pemilihan penyedia)'";
} else if ($tahap == 'selesai') {
$sql .= " AND t.kategori_tahap='Selesai (pemilihan penyedia)'";
}
}
$stmt = $connection->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
return $result;
}
public function getDashboard($tabel, $tipe, $tahap = null, $count)
{
$connection = $this->getDoctrine()->getConnection();
if ($count) {
$sql = "SELECT count(no_pr) from $tabel t WHERE t.kategori_tahap !=''";
if ($tipe == 'jumlah_pk') {
$sql .= " AND t.status_pr IN( 'approve','submit' )";
}
if ($tahap) {
if ($tahap == 'tahap_pembuatan_paket') {
$sql .= " AND t.kategori_tahap='Tahap pembuatan paket'";
} else if ($tahap == 'tahap_penyusunan_hps') {
$sql .= " AND t.kategori_tahap='Tahap penyusunan hps'";
} else if ($tahap == 'tahap_pemilihan_penyedia') {
$sql .= " AND t.kategori_tahap IN ('Dalam proses (pemilihan penyedia)','Selesai (pemilihan penyedia)')";
} else if ($tahap == 'dalam_proses') {
$sql .= " AND t.kategori_tahap='Dalam proses (pemilihan penyedia)'";
} else if ($tahap == 'selesai') {
$sql .= " AND t.kategori_tahap='Selesai (pemilihan penyedia)'";
}
}
} else {
$sql = "SELECT * FROM $tabel t WHERE t.kategori_tahap !=''";
if ($tahap == 'tahap_pembuatan_paket') {
$sql .= " AND t.kategori_tahap='Tahap pembuatan paket'";
} else if ($tahap == 'tahap_penyusunan_hps') {
$sql .= " AND t.kategori_tahap='Tahap penyusunan hps'";
} else if ($tahap == 'tahap_pemilihan_penyedia') {
$sql .= " AND t.kategori_tahap IN ('Dalam proses (pemilihan penyedia)','Selesai (pemilihan penyedia)')";
} else if ($tahap == 'dalam_proses') {
$sql .= " AND t.kategori_tahap='Dalam proses (pemilihan penyedia)'";
} else if ($tahap == 'selesai') {
$sql .= " AND t.kategori_tahap='Selesai (pemilihan penyedia)'";
}
}
$stmt = $connection->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
return $result;
}
public function getTabelGrafik($grafik_id, $tipe, $tahun)
{
$connection = $this->getDoctrine()->getConnection();
$grafik_repo = $this->getDoctrine()->getRepository(TGrafik::class);
$grafik = $grafik_repo->find($grafik_id);
$meta_repo = $this->getDoctrine()->getRepository(TMetaData::class);
$meta_reop = $meta_repo->findOneBy(['id' => $grafik->getReffId()]);
$table = $grafik->getTabel(); // Nama tabel
$sumbu_x = $grafik->getAxisx(); // Misalnya: plant
$sumbu_y = $grafik->getAxisYIds(); // Misalnya: ['count_days', 'nilai_investasi']
$operation = $grafik->getOperation();
$filter = $grafik->getFilter();
$res = [];
$where_clauses = [];
$t = ($sumbu_x == 'kategori_plant') ? 't.' : null;
foreach ($sumbu_y as $value) {
if ($operation == 'SUM') {
$res[] = "$operation(CASE WHEN $t$value IS NOT NULL THEN $t$value ELSE 0 END) AS $value"; // atau AVG($value) sesuai kebutuhan
} else if ($operation == 'COUNT') {
// $res[] = "$operation(CASE WHEN $t$value IS NOT NULL THEN 1 END) AS $value";
$res[] = "COUNT(DISTINCT CASE WHEN $t$value IS NOT NULL THEN $t$value END) AS $value";
} else {
$res[] = "$operation($t$value) AS $t$value";
}
// $where_clauses[] = "$value IS NOT NULL";
}
// Gabungkan bagian SELECT dan WHERE
if ($filter) {
$filter = implode(" AND ", $filter);
$filter = " AND $filter";
} else {
$filter = null;
}
$y = implode(", ", $res);
// $where = implode(" AND ", $where_clauses);
// $sql = "SELECT $sumbu_x, $y FROM $table WHERE $where GROUP BY $sumbu_x";
if ($sumbu_x == 'kategori_plant') {
// $sql = "SELECT m_plant.kategori as plant, $y FROM $table t JOIN m_plant ON t.plant = m_plant.nama GROUP BY m_plant.kategori;";
//left join
$sql = "SELECT m_plant.kategori as plant, $y FROM m_plant LEFT JOIN $table t ON UPPER(t.plant) = UPPER(m_plant.nama) GROUP BY m_plant.kategori;";
} elseif ($sumbu_x == 'all') {
// $sql = "SELECT m_plant.kategori as plant, $y FROM $table t JOIN m_plant ON t.plant = m_plant.nama GROUP BY m_plant.kategori;";
//left join
$sql = "SELECT $y FROM $table WHERE tahun_anggaran = '$tahun' ";
} else {
$sql = "SELECT $sumbu_x, $y FROM $table WHERE $sumbu_x IS NOT NULL AND tahun_anggaran = '$tahun' AND $sumbu_x != '' $filter GROUP BY $sumbu_x";
}
// print_r($filter); exit;
$stmt = $connection->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
// print_r($result); exit;
if ($tipe == 'chart') {
$columns = [];
$seriesData = [];
// Inisialisasi array data kosong untuk tiap Y
$seriesTemplate = [];
foreach ($sumbu_y as $y) {
$seriesTemplate[$y] = [];
}
// Loop data SQL
foreach ($result as $row) {
if ($sumbu_x != 'all') {
$columns[] = $row[($sumbu_x == 'kategori_plant') ? 'plant' : $sumbu_x];
}
foreach ($sumbu_y as $y) {
$seriesTemplate[$y][] = (float)$row[$y];
}
}
// Susun dalam format Highcharts
$series = [];
foreach ($seriesTemplate as $name => $values) {
$series[] = [
'name' => $name,
'data' => $values
];
}
// Return JSON (Symfony style)
return new JsonResponse([
'columns' => $columns,
'datas' => $series
]);
} else {
$request = $this->get('request_stack')->getCurrentRequest();
$draw = (int) $request->request->get('draw', 1);
$start = (int) $request->request->get('start', 0);
$length = (int) $request->request->get('length', 10);
$search = $request->request->get('search')['value'] ?? '';
// Mapping kolom
$columns = array_merge([($sumbu_x == 'kategori_plant') ? 'plant' : $sumbu_x], $sumbu_y);
// Total data sebelum filtering
$totalData = count($result);
// Filtering
if ($search !== '') {
$result = array_filter($result, function ($row) use ($search) {
foreach ($row as $value) {
if (stripos((string)$value, $search) !== false) return true;
}
return false;
});
}
// Total data setelah filtering
$totalFiltered = count($result);
// Slice/pagination
$data = array_slice($result, $start, $length);
// Return JSON sesuai format DataTables
return new JsonResponse([
'draw' => $draw,
'recordsTotal' => $totalData,
'recordsFiltered' => $totalFiltered,
'data' => array_values($data),
]);
}
}
public function getKolomTabel($table)
{
$connection = $this->getDoctrine()->getConnection();
$query = $connection->prepare("select column_name from information_schema.columns WHERE table_name='$table'");
$query->execute();
$hasil = $query->fetchAll();
return $hasil;
}
public function getTabelReport($group)
{
$connection = $this->getDoctrine()->getConnection();
// $query = $connection->prepare("select column_name from information_schema.columns WHERE table_name='$table'");
$query = $connection->prepare("WITH sumber_dana_list AS (
SELECT DISTINCT sumber_dana FROM t_weekly_explore_report UNION SELECT 'investasi' UNION SELECT 'eksploitasi'),
-- 2. Daftar kategori_plant dari m_plant
kategori_list AS (
SELECT DISTINCT $group FROM m_plant
),
-- 3. Buat semua kombinasi sumber_dana × kategori
kombinasi AS (
SELECT s.sumber_dana, k.$group
FROM sumber_dana_list s
CROSS JOIN kategori_list k
),
-- 4. Ambil data riil dengan join m_plant dan t_weekly_explore_report
data_riil AS (
SELECT
t.sumber_dana,
mp.$group,
t.anggaran,
t.hps,
t.nilai_akhir,
t.tahap_pembuatan_paket,
t.tahap_penyusunan_hps,
t.tahap_pemilihan_penyedia,
t.selesai,
t.dalam_proses,
t.rata_rata_countdays_proses,
t.nilai_paket_tahap_penyusunan_penyedia,
t.nilai_paket_tahap_penysunan_hps
FROM
t_weekly_explore_report t
JOIN
m_plant mp ON UPPER(t.plant) = UPPER(mp.nama)
WHERE
t.sumber_dana IS NOT NULL
)
-- 5. LEFT JOIN kombinasi dengan data riil
SELECT
k.sumber_dana,
k.$group,
SUM(CASE WHEN (d.anggaran IS NOT NULL) THEN d.anggaran ELSE 0 END) AS anggaran,
SUM(CASE WHEN (d.hps IS NOT NULL) THEN d.hps ELSE 0 END) AS hps,
SUM(CASE WHEN (d.nilai_akhir IS NOT NULL) THEN d.nilai_akhir ELSE 0 END) AS nilai_akhir,
COUNT(CASE WHEN d.tahap_pembuatan_paket IS NOT NULL THEN 1 END) AS tahap_pembuatan_paket,
COUNT(CASE WHEN d.tahap_penyusunan_hps IS NOT NULL THEN 1 END) AS tahap_penyusunan_hps,
COUNT(CASE WHEN d.tahap_pemilihan_penyedia IS NOT NULL THEN 1 END) AS tahap_pemilihan_penyedia,
COUNT(CASE WHEN d.selesai IS NOT NULL THEN 1 END) AS selesai,
COUNT(CASE WHEN d.dalam_proses IS NOT NULL THEN 1 END) AS dalam_proses,
COUNT(CASE WHEN d.rata_rata_countdays_proses IS NOT NULL THEN 1 END) AS rata_rata_countdays_proses,
SUM(CASE WHEN (d.nilai_paket_tahap_penyusunan_penyedia IS NOT NULL) THEN d.nilai_paket_tahap_penyusunan_penyedia ELSE 0 END) AS nilai_paket_tahap_penyusunan_penyedia,
SUM(CASE WHEN (d.nilai_paket_tahap_penysunan_hps IS NOT NULL) THEN d.nilai_paket_tahap_penysunan_hps ELSE 0 END) AS nilai_paket_tahap_penysunan_hps
-- AVG(d.rata_rata_countdays_proses) AS rata_rata_proses
FROM
kombinasi k
LEFT JOIN
data_riil d
ON d.sumber_dana = k.sumber_dana
AND d.$group = k.$group
WHERE k.sumber_dana IS NOT NULL
GROUP BY
k.sumber_dana, k.$group
ORDER BY
k.sumber_dana, k.$group;");
$query->execute();
$hasil = $query->fetchAll();
return $hasil;
}
public function getTabelLaporan($tabel, $group, $kolom_periode, $tgl_awal, $tgl_akhir, $tipe_tahun, $tahun, $sumber_dana, $kategori_plant = null, $realisasi = null)
{
$connection = $this->getDoctrine()->getConnection();
// $query = $connection->prepare("select column_name from information_schema.columns WHERE table_name='$table'");
$filter_periode_pr = ($tipe_tahun == 1) ? "t.tahun_anggaran = '$tahun'" : "t.tgl_verifikasi_pr BETWEEN '$tgl_awal' AND '$tgl_akhir'";
if ($realisasi) {
$filter_periode_pk = ($tipe_tahun == 1) ? "t.tahun_anggaran = '$tahun' AND t.kategori_tahap = 'Selesai (pemilihan penyedia)'" : "t.tgl_submit_pk_ke_hps BETWEEN '$tgl_awal' AND '$tgl_akhir' AND t.kategori_tahap = 'Selesai (pemilihan penyedia)'";
} else {
$filter_periode_pk = ($tipe_tahun == 1) ? "t.tahun_anggaran = '$tahun'" : "t.tgl_submit_pk_ke_hps BETWEEN '$tgl_awal' AND '$tgl_akhir'";
}
$filter_periode_pp = ($tipe_tahun == 1) ? "t.tahun_anggaran = '$tahun'" : "t.tgl_approval_sppbj BETWEEN '$tgl_awal' AND '$tgl_akhir'";
$filter_periode_submit_ke_pengadaan = ($tipe_tahun == 1) ? "t.tahun_anggaran = '$tahun'" : "t.tgl_submit_ke_pengadaan BETWEEN '$tgl_awal' AND '$tgl_akhir'";
$filter_periode = ($tipe_tahun == 1) ? " WHERE t.tahun_anggaran = '$tahun'" : "";
$filter_periode_and = ($tipe_tahun == 1) ? " AND t.tahun_anggaran = '$tahun'" : "";
if ($kategori_plant) {
$filter_kategori_plant_kombinasi = " WHERE kategori_plant IN ($kategori_plant)";
$filter_kategori_plant = " AND t.kategori_plant IN ($kategori_plant)";
} else {
$filter_kategori_plant_kombinasi = null;
$filter_kategori_plant = null;
}
$query = $connection->prepare("WITH
kombinasi AS (
SELECT
s.sumber_dana,
p.$group
FROM
(SELECT DISTINCT $group FROM $tabel $filter_kategori_plant_kombinasi) p
CROSS JOIN
(SELECT UNNEST(ARRAY['investasi', 'eksploitasi']) AS sumber_dana) s
),
agregasi AS (
SELECT
t.sumber_dana,
t.$group,
-- COUNT(DISTINCT t.no_pr) FILTER (WHERE $filter_periode_pr) AS total_pr,
-- COUNT(DISTINCT t.no_pk) FILTER (WHERE $filter_periode_pk) AS total_pk,
-- COUNT(DISTINCT t.no_pp) FILTER (WHERE $filter_periode_pp) AS total_pp,
COUNT(DISTINCT t.no_pr) FILTER (WHERE $filter_periode_pr AND t.status_pr IN ('approve','submit')) AS total_pr,
COUNT(DISTINCT t.no_pk) FILTER (WHERE $filter_periode_pk AND t.status_pk IN ('draft','assign','pengadaan','submit')) AS total_pk,
COUNT(DISTINCT t.no_pp) FILTER (WHERE $filter_periode_pp AND t.status_pp_ IN ('draft','laksanakan','finish') ) AS total_pp,
COUNT(DISTINCT t.no_sppbj) FILTER (WHERE $filter_periode_pp AND t.kategori_tahap = 'Selesai (pemilihan penyedia)' AND t.no_sppbj IS NOT NULL AND t.no_sppbj != '') AS total_sppbj,
COUNT(DISTINCT NULLIF(UPPER(TRIM(t.vendor_pemenang)), '')) FILTER (WHERE $filter_periode_pp AND t.kategori_tahap = 'Selesai (pemilihan penyedia)') AS total_vendor_pemenang,
SUM(CASE WHEN $filter_periode_pr
THEN NULLIF(NULLIF(btrim(t.total_nilai_hasil::text), ''), '-')::numeric
ELSE 0 END) AS total_nilai,
SUM(CASE WHEN t.kategori_tahap = 'Selesai (pemilihan penyedia)'
AND $filter_periode_pr
THEN NULLIF(NULLIF(btrim(t.nilai_akhir_hasil::text), ''), '-')::numeric
ELSE 0 END) AS total_nilai_akhir,
SUM(CASE WHEN t.kategori_tahap = 'Selesai (pemilihan penyedia)'
AND $filter_periode_pr
THEN NULLIF(NULLIF(btrim(t.anggaran_hasil::text), ''), '-')::numeric
ELSE 0 END) AS total_anggaran,
SUM(CASE WHEN t.kategori_tahap = 'Selesai (pemilihan penyedia)'
AND $filter_periode_pr
THEN NULLIF(NULLIF(btrim(t.hps_hasil::text), ''), '-')::numeric
ELSE 0 END) AS total_hps,
SUM(CASE WHEN t.kategori_tahap = 'Tahap penyusunan hps'
AND $filter_periode_pr
THEN NULLIF(NULLIF(btrim(t.total_nilai_hasil::text), ''), '-')::numeric
ELSE 0 END) AS nilai_penyusunan_hps,
SUM(CASE WHEN t.kategori_tahap IN ('Dalam proses (pemilihan penyedia)', 'Selesai (pemilihan penyedia)')
AND $filter_periode_submit_ke_pengadaan
THEN NULLIF(NULLIF(btrim(t.total_nilai_hasil::text), ''), '-')::numeric
ELSE 0 END) AS nilai_pemilihan_penyedia,
COUNT(DISTINCT t.no_pk) FILTER (WHERE t.kategori_tahap = 'Tahap pembuatan paket' AND $filter_periode_pr) AS tahap_pembuatan_paket,
COUNT(DISTINCT t.no_pk) FILTER (WHERE t.kategori_tahap = 'Tahap penyusunan hps' AND $filter_periode_pr) AS tahap_penyusunan_hps,
COUNT(DISTINCT t.no_pk) FILTER (WHERE t.kategori_tahap = 'Dalam proses (pemilihan penyedia)' AND $filter_periode_submit_ke_pengadaan) AS dalam_proses,
COUNT(DISTINCT t.no_pk) FILTER (WHERE t.kategori_tahap = 'Selesai (pemilihan penyedia)' AND $filter_periode_submit_ke_pengadaan) AS selesai
FROM $tabel t
WHERE t.tahun_anggaran IS NOT NULL $filter_kategori_plant
AND t.sumber_dana IS NOT NULL
GROUP BY t.sumber_dana, t.$group
),
hari_kerja AS (
SELECT
sumber_dana,
$group,
COUNT(*) AS total_hari_kerja
FROM (
SELECT
t.sumber_dana,
t.$group,
generate_series(
t.tgl_submit_ke_pengadaan::date,
CURRENT_DATE,
interval '1 day'
)::date AS tanggal
FROM $tabel t
WHERE t.kategori_tahap = 'Dalam proses (pemilihan penyedia)'
) tanggalan
LEFT JOIN m_libur l ON tanggalan.tanggal = l.tanggal::date
WHERE EXTRACT(DOW FROM tanggalan.tanggal) NOT IN (0, 6)
AND l.tanggal IS NULL
GROUP BY sumber_dana, $group
)
SELECT
k.sumber_dana,
k.$group,
COALESCE(a.total_vendor_pemenang, 0) AS total_vendor_pemenang,
COALESCE(a.total_pk, 0) AS total_pk,
COALESCE(a.total_pr, 0) AS total_pr,
COALESCE(a.total_pp, 0) AS total_pp,
COALESCE(a.total_nilai, 0) AS total_nilai,
COALESCE(a.total_nilai_akhir, 0) AS total_nilai_akhir,
COALESCE(a.total_anggaran, 0) AS total_anggaran,
COALESCE(a.total_hps, 0) AS total_hps,
COALESCE(a.nilai_penyusunan_hps, 0) AS nilai_penyusunan_hps,
COALESCE(a.nilai_pemilihan_penyedia, 0) AS nilai_pemilihan_penyedia,
COALESCE(a.tahap_pembuatan_paket, 0) AS tahap_pembuatan_paket,
COALESCE(a.tahap_penyusunan_hps, 0) AS tahap_penyusunan_hps,
COALESCE(a.dalam_proses, 0) AS dalam_proses,
COALESCE(a.selesai, 0) AS selesai,
COALESCE(h.total_hari_kerja, 0) AS total_hari_kerja
FROM kombinasi k
LEFT JOIN agregasi a
ON a.sumber_dana = k.sumber_dana
AND a.$group = k.$group
LEFT JOIN hari_kerja h
ON h.sumber_dana = k.sumber_dana
AND h.$group = k.$group
ORDER BY k.sumber_dana, k.$group;");
$query->execute();
$hasil = $query->fetchAll();
return $hasil;
}
public function getLaporanUnGroup($tabel, $tgl_awal, $tgl_akhir, $tipe_tahun, $tahun)
{
$connection = $this->getDoctrine()->getConnection();
// $query = $connection->prepare("select column_name from information_schema.columns WHERE table_name='$table'");
$filter_periode_pk = ($tipe_tahun == 1) ? "t.tahun_anggaran = '$tahun'" : "t.tgl_submit_pk_ke_hps BETWEEN '$tgl_awal' AND '$tgl_akhir'";
$filter_periode_pr = ($tipe_tahun == 1) ? "t.tahun_anggaran = '$tahun'" : "t.tgl_verifikasi_pr BETWEEN '$tgl_awal' AND '$tgl_akhir'";
$filter_periode_pp = ($tipe_tahun == 1) ? "t.tahun_anggaran = '$tahun'" : "t.tgl_approval_sppbj BETWEEN '$tgl_awal' AND '$tgl_akhir'";
$filter_periode_submit_ke_pengadaan = ($tipe_tahun == 1) ? "t.tahun_anggaran = '$tahun'" : "t.tgl_submit_ke_pengadaan BETWEEN '$tgl_awal' AND '$tgl_akhir'";
$query = $connection->prepare("SELECT
-- COUNT(DISTINCT t.no_pr) FILTER (WHERE $filter_periode_pr) AS total_pr,
-- COUNT(DISTINCT t.no_pk) FILTER (WHERE $filter_periode_pk) AS total_pk,
-- COUNT(DISTINCT t.no_pp) FILTER (WHERE $filter_periode_pp) AS total_pp,
COUNT(DISTINCT t.no_pr) FILTER (WHERE $filter_periode_pr AND t.status_pr IN ('approve','submit')) AS total_pr,
COUNT(DISTINCT t.no_pk) FILTER (WHERE $filter_periode_pk AND t.status_pk IN ('draft','assign','pengadaan','submit')) AS total_pk,
COUNT(DISTINCT t.no_pp) FILTER (WHERE $filter_periode_pp AND t.status_pp_ IN ('draft','laksanakan','finish') ) AS total_pp,
COUNT(DISTINCT t.no_sppbj) FILTER (WHERE $filter_periode_pp AND t.kategori_tahap = 'Selesai (pemilihan penyedia)' AND t.no_sppbj IS NOT NULL AND t.no_sppbj != '') AS total_sppbj,
COUNT(DISTINCT NULLIF(UPPER(TRIM(t.vendor_pemenang)), '')) FILTER (WHERE $filter_periode_pp AND t.kategori_tahap = 'Selesai (pemilihan penyedia)') AS total_vendor_pemenang,
SUM(CASE WHEN $filter_periode_pr
THEN NULLIF(NULLIF(btrim(t.total_nilai_hasil::text), ''), '-')::numeric
ELSE 0 END) AS total_nilai,
SUM(CASE WHEN t.kategori_tahap = 'Selesai (pemilihan penyedia)'
AND $filter_periode_pr
THEN NULLIF(NULLIF(btrim(t.nilai_akhir_hasil::text), ''), '-')::numeric
ELSE 0 END) AS total_nilai_akhir,
SUM(CASE WHEN $filter_periode_pr
THEN NULLIF(NULLIF(btrim(t.anggaran_hasil::text), ''), '-')::numeric
ELSE 0 END) AS total_anggaran,
SUM(CASE WHEN $filter_periode_pr
THEN NULLIF(NULLIF(btrim(t.hps_hasil::text), ''), '-')::numeric
ELSE 0 END) AS total_hps,
SUM(CASE WHEN t.kategori_tahap = 'Tahap penyusunan hps'
AND $filter_periode_pr
THEN NULLIF(NULLIF(btrim(t.total_nilai_hasil::text), ''), '-')::numeric
ELSE 0 END) AS nilai_penyusunan_hps,
SUM(CASE WHEN t.kategori_tahap IN ('Dalam proses (pemilihan penyedia)', 'Selesai (pemilihan penyedia)')
AND $filter_periode_submit_ke_pengadaan
THEN NULLIF(NULLIF(btrim(t.total_nilai_hasil::text), ''), '-')::numeric
ELSE 0 END) AS nilai_pemilihan_penyedia,
COUNT(DISTINCT t.no_pk) FILTER (WHERE t.kategori_tahap = 'Tahap pembuatan paket' AND $filter_periode_pr) AS tahap_pembuatan_paket,
COUNT(DISTINCT t.no_pk) FILTER (WHERE t.kategori_tahap = 'Tahap penyusunan hps' AND $filter_periode_pr) AS tahap_penyusunan_hps,
COUNT(DISTINCT t.no_pk) FILTER (WHERE t.kategori_tahap = 'Dalam proses (pemilihan penyedia)' AND $filter_periode_submit_ke_pengadaan) AS dalam_proses,
COUNT(DISTINCT t.no_pk) FILTER (WHERE t.kategori_tahap = 'Selesai (pemilihan penyedia)' AND $filter_periode_submit_ke_pengadaan) AS selesai
FROM $tabel t
WHERE t.tahun_anggaran IS NOT NULL AND t.sumber_dana IS NOT NULL");
$query->execute();
$hasil = $query->fetchAll();
return $hasil;
}
public function getDashbord($tabel, $group)
{
$connection = $this->getDoctrine()->getConnection();
// $query = $connection->prepare("select column_name from information_schema.columns WHERE table_name='$table'");
$query = $connection->prepare("WITH
cleaned_data AS (
SELECT
*,
CASE WHEN TRIM(REPLACE(total_nilai::text, ',', '')) = '' THEN NULL ELSE TRIM(REPLACE(total_nilai::text, ',', ''))::numeric END AS total_nilai_bersih,
CASE WHEN TRIM(REPLACE(anggaran::text, ',', '')) = '' THEN NULL ELSE TRIM(REPLACE(anggaran::text, ',', ''))::numeric END AS anggaran_bersih,
CASE WHEN TRIM(REPLACE(hps::text, ',', '')) = '' THEN NULL ELSE TRIM(REPLACE(hps::text, ',', ''))::numeric END AS hps_bersih,
CASE WHEN TRIM(REPLACE(nilai_akhir::text, ',', '')) = '' THEN NULL ELSE TRIM(REPLACE(nilai_akhir::text, ',', ''))::numeric END AS nilai_akhir_bersih,
CASE WHEN TRIM(REPLACE(nilai_saving_terhadap_anggaran, ',', '')) = '' THEN NULL ELSE TRIM(REPLACE(nilai_saving_terhadap_anggaran, ',', ''))::numeric END AS saving_anggaran_bersih,
CASE WHEN TRIM(REPLACE(nilai_saving_terhadap_hps, ',', '')) = '' THEN NULL ELSE TRIM(REPLACE(nilai_saving_terhadap_hps, ',', ''))::numeric END AS saving_hps_bersih,
CASE WHEN TRIM(REPLACE(saving_persen_terhadap_angggaran, ',', '')) = '' THEN NULL ELSE TRIM(REPLACE(saving_persen_terhadap_angggaran, ',', ''))::numeric END AS persen_saving_anggaran_bersih,
CASE WHEN TRIM(REPLACE(saving_persen_terhadap_hps, ',', '')) = '' THEN NULL ELSE TRIM(REPLACE(saving_persen_terhadap_hps, ',', ''))::numeric END AS persen_saving_hps_bersih
FROM $tabel
),
pk_per_pp AS (
SELECT no_pp, no_pk, COUNT(*) AS jumlah
FROM cleaned_data
GROUP BY no_pp, no_pk
),
cek_duplikat_pk AS (
SELECT no_pp, COUNT(*) FILTER (WHERE jumlah > 1) AS jumlah_pk_duplikat
FROM pk_per_pp
GROUP BY no_pp
),
gabung AS (
SELECT t.*, COALESCE(d.jumlah_pk_duplikat, 0) AS jumlah_pk_duplikat
FROM cleaned_data t
LEFT JOIN cek_duplikat_pk d ON t.no_pp = d.no_pp
),
aggregated AS (
SELECT
regional,
COUNT(DISTINCT no_pk) AS distinct_pk,
COUNT(no_pk) AS total_pk,
CASE WHEN COUNT(DISTINCT no_pk) < COUNT(no_pk) THEN ROUND(AVG(total_nilai_bersih), 2) ELSE SUM(total_nilai_bersih) END AS total_total_nilai,
CASE WHEN COUNT(DISTINCT no_pk) < COUNT(no_pk) THEN ROUND(AVG(nilai_akhir_bersih), 2) ELSE SUM(nilai_akhir_bersih) END AS total_nilai_akhir,
CASE WHEN COUNT(DISTINCT no_pk) < COUNT(no_pk) THEN ROUND(AVG(anggaran_bersih), 2) ELSE SUM(anggaran_bersih) END AS total_anggaran,
CASE WHEN COUNT(DISTINCT no_pk) < COUNT(no_pk) THEN ROUND(AVG(hps_bersih), 2) ELSE SUM(hps_bersih) END AS total_hps,
CASE WHEN COUNT(DISTINCT no_pk) < COUNT(no_pk) THEN ROUND(AVG(saving_anggaran_bersih), 2) ELSE SUM(saving_anggaran_bersih) END AS total_saving_anggaran,
CASE WHEN COUNT(DISTINCT no_pk) < COUNT(no_pk) THEN ROUND(AVG(saving_hps_bersih), 2) ELSE SUM(saving_hps_bersih) END AS total_saving_hps,
CASE WHEN COUNT(DISTINCT no_pk) < COUNT(no_pk) THEN ROUND(AVG(persen_saving_anggaran_bersih), 2) ELSE SUM(persen_saving_anggaran_bersih) END AS total_persen_saving_anggaran,
CASE WHEN COUNT(DISTINCT no_pk) < COUNT(no_pk) THEN ROUND(AVG(persen_saving_hps_bersih), 2) ELSE SUM(persen_saving_hps_bersih) END AS total_persen_saving_hps,
CASE WHEN COUNT(DISTINCT no_pk) < COUNT(no_pk)
THEN ROUND(AVG(CASE WHEN kategori_tahap = 'Tahap penyusunan hps' THEN total_nilai_bersih END), 2)
ELSE SUM(CASE WHEN kategori_tahap = 'Tahap penyusunan hps' THEN total_nilai_bersih ELSE 0 END)
END AS nilai_penyusunan_hps,
CASE WHEN COUNT(DISTINCT no_pk) < COUNT(no_pk)
THEN ROUND(AVG(CASE WHEN kategori_tahap IN ('Dalam proses (pemilihan penyedia)', 'Selesai (pemilihan penyedia)') THEN total_nilai_bersih END), 2)
ELSE SUM(CASE WHEN kategori_tahap IN ('Dalam proses (pemilihan penyedia)', 'Selesai (pemilihan penyedia)') THEN total_nilai_bersih ELSE 0 END)
END AS nilai_pemilihan_penyedia,
COUNT(DISTINCT no_pk) FILTER (WHERE kategori_tahap = 'Tahap pembuatan paket') AS tahap_pembuatan_paket,
COUNT(DISTINCT no_pk) FILTER (WHERE kategori_tahap = 'Tahap penyusunan hps') AS tahap_penyusunan_hps,
COUNT(DISTINCT no_pk) FILTER (WHERE kategori_tahap = 'Dalam proses (pemilihan penyedia)') AS dalam_proses,
COUNT(DISTINCT no_pk) FILTER (WHERE kategori_tahap = 'Selesai (pemilihan penyedia)') AS selesai
FROM gabung
GROUP BY $group
)
SELECT
$group,
total_total_nilai AS total_nilai,
total_nilai_akhir,
total_anggaran,
total_hps,
total_saving_anggaran,
total_saving_hps,
total_persen_saving_anggaran,
total_persen_saving_hps,
nilai_penyusunan_hps,
nilai_pemilihan_penyedia,
tahap_pembuatan_paket,
tahap_penyusunan_hps,
dalam_proses,
selesai
FROM aggregated
ORDER BY regional;");
$query->execute();
$hasil = $query->fetchAll();
return $hasil;
}
public function getDataTableDetail($limit, $page, $get, $group, $nama, $tahap, $sumber_dana, $tabel, $kolom_periode, $tgl_awal, $tgl_akhir, $tipe_tahun, $tahun)
{
$connection = $this->getDoctrine()->getConnection();
$sql = "SELECT * FROM $tabel t ";
$sql .= " WHERE t.kategori_tahap !=''";
if ($tipe_tahun == 1) {
$sql .= " AND tahun_anggaran = '$tahun'";
} else {
$sql .= " AND tahun_anggaran IS NOT NULL AND sumber_dana IS NOT NULL AND $kolom_periode BETWEEN '$tgl_awal' AND '$tgl_akhir' ";
}
if ($tahap == 'tahap_pembuatan_paket') {
$sql .= " AND t.kategori_tahap='Tahap pembuatan paket'";
} else if ($tahap == 'tahap_penyusunan_hps') {
$sql .= " AND t.kategori_tahap='Tahap penyusunan hps'";
} else if ($tahap == 'tahap_pemilihan_penyedia') {
$sql .= " AND t.kategori_tahap IN ('Dalam proses (pemilihan penyedia)','Selesai (pemilihan penyedia)')";
} else if ($tahap == 'dalam_proses') {
$sql .= " AND t.kategori_tahap='Dalam proses (pemilihan penyedia)'";
} else if ($tahap == 'selesai') {
$sql .= " AND t.kategori_tahap='Selesai (pemilihan penyedia)'";
}
if ($group == 'all') {
} else {
if ($group == 'kategori_plant') {
// $sql = "SELECT * FROM $tabel t JOIN m_plant ON UPPER(t.plant) = UPPER(m_plant.nama)";
$sql .= " AND UPPER(t.kategori_plant) = UPPER('$nama')";
} else {
$sql .= " AND UPPER(t.plant) = UPPER('$nama')";
}
$sql .= " AND t.sumber_dana ='$sumber_dana'";
}
if (!empty($get['filters']['no_pr'])) {
$sql .= " AND t.no_pr::text ILIKE '%" . $get['filters']['no_pr'] . "%'";
}
if (!empty($get['filters']['plant'])) {
if ($group == 'kategori_plant') {
$sql .= " AND t.kategori_plant::text ILIKE '%" . $get['filters']['plant'] . "%'";
} else {
$sql .= " AND t.plant::text ILIKE '%" . $get['filters']['plant'] . "%'";
}
}
if (!empty($get['filters']['sumber_dana'])) {
$sql .= " AND t.sumber_dana:text ILIKE '%" . $get['filters']['sumber_dana'] . "%'";
}
if (!empty($get['filters']['no_pp'])) {
$sql .= " AND t.no_pp:text ILIKE '%" . $get['filters']['no_pp'] . "%'";
}
if (!empty($get['filters']['no_pk'])) {
$sql .= " AND t.no_pk:text ILIKE '%" . $get['filters']['no_pk'] . "%'";
}
if (!empty($get['filters']['anggaran'])) {
$sql .= " AND t.anggaran::text ILIKE '%" . $get['filters']['anggaran'] . "%'";
}
if (!empty($get['filters']['tahun_anggaran'])) {
$sql .= " AND t.tahun_anggaran::text ILIKE '%" . $get['filters']['tahun_anggaran'] . "%'";
}
if ($limit) {
$sql .= " LIMIT " . intval($limit);
}
if ($page) {
$sql .= " OFFSET " . intval($page);
}
$stmt = $connection->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
$res = [];
$no = 1;
foreach ($result as $key => $value) {
$res[$key]['no'] = $no++;
$res[$key]['no_pr'] = $value['no_pr'];
$res[$key]['plant'] = ($group == 'kategori_plant') ? $value['kategori_plant'] : $value['plant'];
$res[$key]['sumber_dana'] = $value['sumber_dana'];
$res[$key]['tahun_anggaran'] = $value['tahun_anggaran'];
$res[$key]['anggaran'] = $value['anggaran'];
$res[$key]['no_pk'] = $value['no_pk'];
if ($tahap == 'tahap_pemilihan_penyedia') {
$res[$key]['no_pp'] = $value['no_pp'];
}
}
// print_r($result); exit;
return $res;
}
public function getDataTableDetailExplorasiPeriode($limit, $page, $get, $tabel, $explorasi_detail_id)
{
$exploreDetail = $this->getDoctrine()->getRepository(TExploreDataDetail::class);
$explorasi = $exploreDetail->findOneBy(['id' => $explorasi_detail_id]);
// print_r($explorasi); exit;
$filter = $explorasi->getFilter();
$connection = $this->getDoctrine()->getConnection();
$whereClause = implode(' AND ', $filter);
$kondisi = ($explorasi->isIsCreateView() != 1) ? ' AND ' . $whereClause : null;
$sql = "SELECT periode, SUM(nilai) AS nilai FROM $tabel t WHERE t.periode != '' $kondisi
-- filter tambahan
" . (!empty($get['filters']['periode']) ? " AND t.periode ILIKE '%" . $get['filters']['periode'] . "%'" : "") . "
" . (!empty($get['filters']['nilai']) ? " AND t.nilai::text ILIKE '%" . $get['filters']['nilai'] . "%'" : "") . "
GROUP BY
periode
ORDER BY
TO_DATE(LEFT(periode, 3) || ' ' || SUBSTRING(periode, 4, 4), 'Mon YYYY') ASC,
CASE SPLIT_PART(periode, '.', 2)
WHEN 'I' THEN 1
WHEN 'II' THEN 2
WHEN 'III' THEN 3
WHEN 'IV' THEN 4
ELSE 0
END ASC";
if ($limit) {
$sql .= " LIMIT " . intval($limit);
}
if ($page) {
$sql .= " OFFSET " . intval($page);
}
$stmt = $connection->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
// print_r($result); exit;
$res = [];
$no = 1;
foreach ($result as $key => $value) {
$res[$key]['no'] = $no++;
$res[$key]['periode'] = $value['periode'];
$res[$key]['nilai'] = $value['nilai'];
}
return $res;
}
public function getGrafikReport($tahun, $bulan)
{
$connection = $this->getDoctrine()->getConnection();
$tahun_sebelumnya = $tahun - 1;
$sql = "WITH all_dates AS (
SELECT d::date AS tanggal
FROM generate_series('$tahun_sebelumnya-10-01'::date, '$tahun-$bulan'::date - INTERVAL '1 day', INTERVAL '1 day') d
),
minggu_fixed AS (
SELECT
TO_CHAR(tanggal, 'MONYYYY') || '.' ||
CASE
WHEN EXTRACT(DAY FROM tanggal) BETWEEN 1 AND 7 THEN 'I'
WHEN EXTRACT(DAY FROM tanggal) BETWEEN 8 AND 14 THEN 'II'
WHEN EXTRACT(DAY FROM tanggal) BETWEEN 15 AND 21 THEN 'III'
ELSE 'IV'
END AS periode,
tanggal
FROM all_dates
),
periode_final AS (
SELECT
periode,
MIN(tanggal) AS start_date,
MAX(tanggal) AS end_date
FROM minggu_fixed
GROUP BY periode
),
data_dengan_periode AS (
SELECT
sumber_dana,
TO_CHAR(tgl_submit_ke_pengadaan, 'MONYYYY') || '.' ||
CASE
WHEN EXTRACT(DAY FROM tgl_submit_ke_pengadaan) BETWEEN 1 AND 7 THEN 'I'
WHEN EXTRACT(DAY FROM tgl_submit_ke_pengadaan) BETWEEN 8 AND 14 THEN 'II'
WHEN EXTRACT(DAY FROM tgl_submit_ke_pengadaan) BETWEEN 15 AND 21 THEN 'III'
ELSE 'IV'
END AS periode,
COUNT(*) AS jumlah
FROM tabel_35
WHERE status_pr = 'approve'
AND sumber_dana IS NOT NULL
AND tgl_submit_ke_pengadaan >= '$tahun_sebelumnya-10-01'
AND tgl_submit_ke_pengadaan < '$tahun-$bulan'
GROUP BY sumber_dana, periode
)
SELECT
p.periode,
s.sumber_dana,
COALESCE(d.jumlah, 0) AS jumlah_pengajuan
FROM periode_final p
CROSS JOIN (
SELECT DISTINCT sumber_dana
FROM tabel_35
WHERE status_pr = 'approve'
AND sumber_dana IS NOT NULL
) s
LEFT JOIN data_dengan_periode d
ON p.periode = d.periode AND s.sumber_dana = d.sumber_dana
ORDER BY p.start_date, s.sumber_dana";
$stmt = $connection->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
$res = [];
foreach ($result as $key => $value) {
$res[$value['sumber_dana']][$key] = $value;
}
$res = array_reverse($res);
return $res;
}
public function getDataTerbaruTerlama($table, $kolom_periode, $kategori_tahap = null, $tahun, $sumber_dana)
{
$connection = $this->getDoctrine()->getConnection();
if ($kategori_tahap == 'tahap_pembuatan_paket') {
$kategori = " AND kategori_tahap='Tahap pembuatan paket'";
} else if ($kategori_tahap == 'tahap_penyusunan_hps') {
$kategori = " AND kategori_tahap='Tahap penyusunan hps'";
} else if ($kategori_tahap == 'tahap_pemilihan_penyedia') {
$kategori = " AND kategori_tahap IN ('Dalam proses (pemilihan penyedia)','Selesai (pemilihan penyedia)')";
} else if ($kategori_tahap == 'dalam_proses') {
$kategori = " AND kategori_tahap='Dalam proses (pemilihan penyedia)'";
} else if ($kategori_tahap == 'selesai') {
$kategori = " AND kategori_tahap='Selesai (pemilihan penyedia)'";
} else {
$kategori = "";
}
$sql = "
(
SELECT 'terlama' AS tipe, *
FROM $table
WHERE tahun_anggaran = '$tahun'
AND $kolom_periode IS NOT NULL AND sumber_dana IS NOT NULL
$kategori
ORDER BY $kolom_periode ASC
LIMIT 1
)
UNION ALL
(
SELECT 'terbaru' AS tipe, *
FROM $table
WHERE tahun_anggaran = '$tahun'
AND $kolom_periode IS NOT NULL AND sumber_dana IS NOT NULL
$kategori
ORDER BY $kolom_periode DESC
LIMIT 1
);";
$stmt = $connection->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
if ($result) {
$periode = [
'periode_awal' => $result[0][$kolom_periode],
'periode_akhir' => $result[1][$kolom_periode]
];
} else {
$periode = [
'periode_awal' => date("$tahun-m-d"),
'periode_akhir' => date("$tahun-m-d")
];
}
return $periode;
}
public function getGrafikTotalPkPpPr($table, $kolom, $kolom_periode, $tanggal_awal, $tanggal_akhir, $kategori_tahap = null, $tipe_tahun, $tahun, $sumber_dana)
{
$connection = $this->getDoctrine()->getConnection();
if ($kategori_tahap == 'tahap_pembuatan_paket') {
$kategori = " AND kategori_tahap='Tahap pembuatan paket'";
} else if ($kategori_tahap == 'tahap_penyusunan_hps') {
$kategori = " AND kategori_tahap='Tahap penyusunan hps'";
} else if ($kategori_tahap == 'tahap_pemilihan_penyedia') {
$kategori = " AND kategori_tahap IN ('Dalam proses (pemilihan penyedia)','Selesai (pemilihan penyedia)')";
} else if ($kategori_tahap == 'dalam_proses') {
$kategori = " AND kategori_tahap='Dalam proses (pemilihan penyedia)'";
} else if ($kategori_tahap == 'selesai') {
$kategori = " AND kategori_tahap='Selesai (pemilihan penyedia)'";
} else {
$kategori = "";
}
if ($tipe_tahun == 1) {
// $tahun_sebelumnya = $tahun - 1;
// // --- Tentukan tanggal awal (Jumat pertama >= 1 Oktober) ---
// $tanggal_awal_obj = new DateTime("$tahun_sebelumnya-10-01");
// if ($tanggal_awal_obj->format('N') != 5) { // 5 = Jumat
// $tanggal_awal_obj->modify('next friday');
// }
// $tanggal_awal = $tanggal_awal_obj->format('Y-m-d');
// // --- Tentukan tanggal akhir (Kamis terakhir <= 30 September) ---
// $bulan_tanggal = ($tahun == date("Y") && date("m") < 9) ? date("m-d") : "09-30";
// $tanggal_akhir_obj = new DateTime("$tahun-$bulan_tanggal");
// if ($tanggal_akhir_obj->format('N') != 4) { // 4 = Kamis
// $tanggal_akhir_obj->modify('last thursday');
// }
// $tanggal_akhir = $tanggal_akhir_obj->format('Y-m-d');
$periode_tanggal = $this->getDataTerbaruTerlama($table, $kolom_periode, $kategori_tahap, $tahun, $sumber_dana);
$tanggal_awal = $periode_tanggal['periode_awal'];
$tanggal_akhir = $periode_tanggal['periode_akhir'];
$tahun_anggaran = " tahun_anggaran='$tahun'";
} else {
$tahun_anggaran = " tahun_anggaran IS NOT NULL";
}
$sql = "WITH parameter AS (
SELECT
'$tanggal_awal'::date AS tanggal_awal,
'$tanggal_akhir'::date AS tanggal_akhir
),
bulan_series AS (
SELECT generate_series(
date_trunc('month', tanggal_awal),
date_trunc('month', tanggal_akhir),
interval '1 month'
)::date AS bulan
FROM parameter
),
all_weeks AS (
SELECT
bulan + interval '0 day' AS start_date,
bulan + interval '7 day' AS start_date_2,
bulan + interval '14 day' AS start_date_3,
bulan + interval '21 day' AS start_date_4,
(date_trunc('month', bulan) + interval '1 month - 1 day')::date AS end_of_month
FROM bulan_series
),
periode_final AS (
SELECT bulan,
(bulan)::date AS start_date,
(bulan + interval '6 day')::date AS end_date
FROM bulan_series
UNION ALL
SELECT bulan,
(bulan + interval '7 day')::date,
(bulan + interval '13 day')::date
FROM bulan_series
UNION ALL
SELECT bulan,
(bulan + interval '14 day')::date,
(bulan + interval '20 day')::date
FROM bulan_series
UNION ALL
SELECT bulan,
(bulan + interval '21 day')::date,
(date_trunc('month', bulan) + interval '1 month - 1 day')::date
FROM bulan_series
),
data_dengan_periode AS (
SELECT
sumber_dana,
date_trunc('month', $kolom_periode)::date
+ CASE
WHEN extract(day from $kolom_periode) BETWEEN 1 AND 7 THEN 0
WHEN extract(day from $kolom_periode) BETWEEN 8 AND 14 THEN 7
WHEN extract(day from $kolom_periode) BETWEEN 15 AND 21 THEN 14
ELSE 21
END * interval '1 day' AS start_date,
COUNT(DISTINCT $kolom) AS jumlah,
SUM(
NULLIF(REPLACE(REPLACE(total_nilai, ',', ''), ' ', ''), '')::numeric
) AS total_nilai
FROM $table
WHERE $tahun_anggaran
AND sumber_dana IS NOT NULL
$kategori
AND $kolom_periode >= (SELECT tanggal_awal FROM parameter)
AND $kolom_periode <= (SELECT tanggal_akhir FROM parameter)
GROUP BY sumber_dana, start_date
)
SELECT
to_char(p.start_date, 'YYYY-MM-DD') || ' s/d ' ||
to_char(p.end_date, 'YYYY-MM-DD') AS periode_tanggal,
-- Label periode seperti OCT2024.1, OCT2024.8, OCT2024.15, OCT2024.22
to_char(p.start_date, 'MONYYYY') || '.' ||
extract(day from p.start_date)::text AS periode,
s.sumber_dana,
COALESCE(d.jumlah, 0) AS jumlah_pengajuan,
COALESCE(d.total_nilai, 0) AS total_nilai
FROM periode_final p
CROSS JOIN (
SELECT DISTINCT sumber_dana
FROM $table
WHERE $tahun_anggaran
AND sumber_dana IS NOT NULL
) s
LEFT JOIN data_dengan_periode d
ON p.start_date = d.start_date
AND s.sumber_dana = d.sumber_dana
ORDER BY p.start_date, s.sumber_dana";
$stmt = $connection->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
$res = [];
foreach ($result as $key => $value) {
$res[$value['sumber_dana']][$key] = $value;
}
$res = array_reverse($res);
return $res;
}
// public function getGrafikTotalPkPpPr($table, $kolom, $kolom_periode, $tanggal_awal, $tanggal_akhir, $kategori_tahap = null, $tipe_tahun, $tahun, $sumber_dana)
// {
// $connection = $this->getDoctrine()->getConnection();
// if ($kategori_tahap == 'tahap_pembuatan_paket') {
// $kategori = " AND kategori_tahap='Tahap pembuatan paket'";
// } else if ($kategori_tahap == 'tahap_penyusunan_hps') {
// $kategori = " AND kategori_tahap='Tahap penyusunan hps'";
// } else if ($kategori_tahap == 'tahap_pemilihan_penyedia') {
// $kategori = " AND kategori_tahap IN ('Dalam proses (pemilihan penyedia)','Selesai (pemilihan penyedia)')";
// } else if ($kategori_tahap == 'dalam_proses') {
// $kategori = " AND kategori_tahap='Dalam proses (pemilihan penyedia)'";
// } else if ($kategori_tahap == 'selesai') {
// $kategori = " AND kategori_tahap='Selesai (pemilihan penyedia)'";
// } else {
// $kategori = "";
// }
// if ($tipe_tahun == 1) {
// $tahun_sebelumnya = $tahun - 1;
// // --- Tentukan tanggal awal (Jumat pertama >= 1 Oktober) ---
// $tanggal_awal_obj = new DateTime("$tahun_sebelumnya-10-01");
// if ($tanggal_awal_obj->format('N') != 5) { // 5 = Jumat
// $tanggal_awal_obj->modify('next friday');
// }
// $tanggal_awal = $tanggal_awal_obj->format('Y-m-d');
// // --- Tentukan tanggal akhir (Kamis terakhir <= 30 September) ---
// $bulan_tanggal = ($tahun == date("Y") && date("m") < 9) ? date("m-d") : "09-30";
// $tanggal_akhir_obj = new DateTime("$tahun-$bulan_tanggal");
// if ($tanggal_akhir_obj->format('N') != 4) { // 4 = Kamis
// $tanggal_akhir_obj->modify('last thursday');
// }
// $tanggal_akhir = $tanggal_akhir_obj->format('Y-m-d');
// $tahun_anggaran = " tahun_anggaran='$tahun'";
// } else {
// $tahun_anggaran = " tahun_anggaran IS NOT NULL";
// }
// $sql = "WITH parameter AS (
// SELECT
// '$tanggal_awal'::date AS tanggal_awal, -- awal tahun anggaran
// '$tanggal_akhir'::date AS tanggal_akhir -- akhir tahun anggaran
// ),
// minggu_awal AS (
// SELECT
// -- cari Jumat pertama >= tanggal_awal
// tanggal_awal
// + mod(5 - CAST(EXTRACT(dow FROM tanggal_awal) AS int) + 7, 7) * interval '1 day'
// AS first_friday,
// tanggal_akhir
// FROM parameter
// ),
// all_weeks AS (
// SELECT
// generate_series(
// first_friday,
// tanggal_akhir,
// interval '7 day'
// )::date AS start_week
// FROM minggu_awal
// ),
// periode_final AS (
// SELECT
// start_week,
// start_week AS start_date,
// start_week + interval '6 day' AS end_date
// FROM all_weeks
// ),
// data_dengan_periode AS (
// SELECT
// sumber_dana,
// (
// $kolom_periode::date
// + mod(5 - CAST(EXTRACT(dow FROM $kolom_periode) AS int) + 7, 7) * interval '1 day'
// )::date AS start_week,
// COUNT(DISTINCT $kolom) AS jumlah,
// SUM(
// NULLIF(REPLACE(REPLACE(total_nilai, ',', ''), ' ', ''), '')::numeric
// ) AS total_nilai
// FROM $table
// WHERE $tahun_anggaran
// AND sumber_dana IS NOT NULL
// $kategori
// AND $kolom_periode >= (SELECT tanggal_awal FROM parameter)
// AND $kolom_periode <= (SELECT tanggal_akhir FROM parameter)
// GROUP BY sumber_dana, start_week
// )
// SELECT
// -- Periode tanggal (Jumat s/d Kamis)
// to_char(p.start_week, 'YYYY-MM-DD') || ' s/d ' ||
// to_char(p.end_date, 'YYYY-MM-DD') AS periode_tanggal,
// -- Label periode (contoh: OCT2024.1, OCT2024.2, dst)
// to_char(p.start_week, 'MONYYYY') || '.' ||
// (dense_rank() OVER (
// PARTITION BY date_trunc('month', p.start_week)
// ORDER BY p.start_week
// ))::text AS periode,
// s.sumber_dana,
// COALESCE(d.jumlah, 0) AS jumlah_pengajuan,
// COALESCE(d.total_nilai, 0) AS total_nilai
// FROM periode_final p
// CROSS JOIN (
// SELECT DISTINCT sumber_dana
// FROM $table
// WHERE $tahun_anggaran
// AND sumber_dana IS NOT NULL
// ) s
// LEFT JOIN data_dengan_periode d
// ON p.start_week = d.start_week
// AND s.sumber_dana = d.sumber_dana
// ORDER BY p.start_week, s.sumber_dana;";
// $stmt = $connection->prepare($sql);
// $stmt->execute();
// $result = $stmt->fetchAll();
// $res = [];
// foreach ($result as $key => $value) {
// $res[$value['sumber_dana']][$key] = $value;
// }
// $res = array_reverse($res);
// return $res;
// }
public function getGrafikPertahap($table, $kolom, $kolom_periode, $tanggal_awal, $tanggal_akhir, $kategori_tahap = null, $tipe_tahun, $tahun, $sumber_dana)
{
$connection = $this->getDoctrine()->getConnection();
if ($kategori_tahap == 'tahap_pembuatan_paket') {
$kategori = " AND kategori_tahap='Tahap pembuatan paket'";
} else if ($kategori_tahap == 'tahap_penyusunan_hps') {
$kategori = " AND kategori_tahap='Tahap penyusunan hps'";
} else if ($kategori_tahap == 'tahap_pemilihan_penyedia') {
$kategori = " AND kategori_tahap IN ('Dalam proses (pemilihan penyedia)','Selesai (pemilihan penyedia)')";
} else if ($kategori_tahap == 'dalam_proses') {
$kategori = " AND kategori_tahap='Dalam proses (pemilihan penyedia)'";
} else if ($kategori_tahap == 'selesai') {
$kategori = " AND kategori_tahap='Selesai (pemilihan penyedia)'";
} else {
$kategori = "";
}
if ($tipe_tahun == 1) {
// $tahun_sebelumnya = $tahun - 1;
// $tanggal_awal = "$tahun_sebelumnya-10-01";
// $bulan_tanggal = ($tahun == date("Y") && date("m") < 9) ? date("m-d") : "09-30";
// $tanggal_akhir = "$tahun-$bulan_tanggal";
$filter = " tahun_anggaran='$tahun'";
} else {
$filter = " tahun_anggaran IS NOT NULL AND $kolom_periode BETWEEN '$tanggal_awal' AND '$tanggal_akhir'";
}
$sql = "WITH sumber AS (
SELECT unnest(ARRAY['investasi','eksploitasi']) AS sumber_dana
),
tahap AS (
SELECT DISTINCT kategori_tahap AS tahap
FROM $table
WHERE $filter $kategori
),
data AS (
SELECT
kategori_tahap AS tahap,
sumber_dana,
COUNT(DISTINCT $kolom) AS jumlah_pengajuan,
SUM(NULLIF(REPLACE(REPLACE(total_nilai, ',', ''), ' ', ''), '')::numeric) AS total_nilai
FROM $table
WHERE $filter $kategori
GROUP BY kategori_tahap, sumber_dana
)
SELECT
t.tahap,
s.sumber_dana,
COALESCE(d.jumlah_pengajuan, 0) AS jumlah_pengajuan,
COALESCE(d.total_nilai, 0) AS total_nilai
FROM tahap t
CROSS JOIN sumber s
LEFT JOIN data d ON d.tahap = t.tahap AND d.sumber_dana = s.sumber_dana
ORDER BY t.tahap, s.sumber_dana;";
$stmt = $connection->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
// $res = [];
// foreach ($result as $key => $value) {
// $res[$value['tahap']][$key] = $value;
// }
// $res = array_reverse($res);
return $result;
}
public function cek_karakter($str)
{
$data = str_replace(array(" ", "\"", "/", "(", ")", """, "-"), "_", htmlspecialchars($str));
return $data;
}
public function buang($str)
{
$data = str_replace(array(",", "'", '.', "%"), "", htmlspecialchars($str));
return $data;
}
public function getJumlahHariKerja($table, $periode_awal, $periode_akhir, $tipe_pengadaan = null, $tanggal_awal, $tanggal_akhir, $tipe_tahun, $tahun, $sumber_dana)
{
$connection = $this->getDoctrine()->getConnection();
$filter_periode = ($tipe_tahun == 1) ? "t.tahun_anggaran = '$tahun'" : "t.$periode_awal BETWEEN '$tanggal_awal' AND '$tanggal_akhir'";
// $sql = "SELECT
// t.no_pr,t.$tanggal_awal,t.$tanggal_akhir,
// COUNT(g.tanggal) AS jumlah_hari_kerja
// FROM
// tabel_40 t
// JOIN LATERAL (
// SELECT tanggal
// FROM generate_series(t.$tanggal_awal, t.$tanggal_akhir, interval '1 day') AS tanggal
// WHERE EXTRACT(DOW FROM tanggal) NOT IN (0, 6) -- bukan Sabtu atau Minggu
// AND tanggal NOT IN (SELECT tanggal FROM m_libur)
// ) g ON true
// GROUP BY t.id";
if ($tipe_pengadaan) {
$sql = "SELECT
CASE
WHEN sub.metode_pengadaan = 'Penunjukan Langsung' THEN 'PL'
WHEN sub.metode_pengadaan IN ('Tender Umum', 'Tender Terbatas', 'Seleksi Terbatas') THEN 'Tender'
ELSE 'Lainnya'
END AS tipe_pengadaan,
ROUND(AVG(sub.jumlah_hari_kerja)::numeric, 2) AS rata_rata_hari_kerja
FROM (
SELECT
t.metode_pengadaan,
COUNT(g.tanggal) AS jumlah_hari_kerja
FROM
$table t
JOIN LATERAL (
SELECT tanggal
FROM generate_series(t.$periode_awal, t.$periode_akhir, interval '1 day') AS tanggal
WHERE EXTRACT(DOW FROM tanggal) NOT IN (0, 6)
AND tanggal NOT IN (SELECT tanggal FROM m_libur)
) g ON true
WHERE $filter_periode
GROUP BY t.id, t.metode_pengadaan
) sub
GROUP BY
CASE
WHEN sub.metode_pengadaan = 'Penunjukan Langsung' THEN 'PL'
WHEN sub.metode_pengadaan IN ('Tender Umum', 'Tender Terbatas', 'Seleksi Terbatas') THEN 'Tender'
ELSE 'Lainnya'
END;";
} else {
$sql = "SELECT
ROUND(AVG(jumlah_hari_kerja)::numeric, 2) AS rata_rata_hari_kerja
FROM (
SELECT
COUNT(g.tanggal) AS jumlah_hari_kerja
FROM
$table t
JOIN LATERAL (
SELECT tanggal
FROM generate_series(t.$periode_awal, t.$periode_akhir, interval '1 day') AS tanggal
WHERE EXTRACT(DOW FROM tanggal) NOT IN (0, 6)
AND tanggal NOT IN (SELECT tanggal FROM m_libur)
) g ON true
WHERE $filter_periode
GROUP BY t.id
) AS sub";
}
$stmt = $connection->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
return $result;
}
public function getJumlahBerdasrkanMetode($table, $kolom_periode, $tgl_awal, $tgl_akhir, $tipe_tahun, $tahun, $sumber_dana)
{
$periode_filter = ($tipe_tahun == 1) ? "tahun_anggaran = '$tahun'" : "$kolom_periode BETWEEN '$tgl_awal' AND '$tgl_akhir'";
$connection = $this->getDoctrine()->getConnection();
$sql = "SELECT
CASE
WHEN sub.metode_pengadaan = 'Penunjukan Langsung' THEN 'PL'
WHEN sub.metode_pengadaan IN ('Tender Umum', 'Tender Terbatas', 'Seleksi Terbatas') THEN 'Tender'
ELSE 'Lainnya'
END AS tipe_pengadaan,
COUNT(DISTINCT no_pk) AS jumlah,
SUM(total_nilai_hasil) AS total_nilai
FROM $table AS sub
WHERE status_pr IN ('approve', 'submit')
AND tahun_anggaran IS NOT NULL AND sumber_dana IS NOT NULL
-- AND $kolom_periode BETWEEN '$tgl_awal' AND '$tgl_akhir'
AND $periode_filter
GROUP BY
CASE
WHEN sub.metode_pengadaan = 'Penunjukan Langsung' THEN 'PL'
WHEN sub.metode_pengadaan IN ('Tender Umum', 'Tender Terbatas', 'Seleksi Terbatas') THEN 'Tender'
ELSE 'Lainnya'
END;";
$stmt = $connection->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
return $result;
}
public function getRangeNilaiPk($table, $kolom_periode, $tgl_awal, $tgl_akhir, $tipe_tahun, $tahun, $sumber_dana)
{
$connection = $this->getDoctrine()->getConnection();
$periode_filter = ($tipe_tahun == 1) ? "tahun_anggaran = '$tahun'" : "$kolom_periode BETWEEN '$tgl_awal' AND '$tgl_akhir'";
$sql = "SELECT
CASE
WHEN total_nilai_hasil BETWEEN 1 AND 50000000 THEN '≤ 50 JT'
WHEN total_nilai_hasil BETWEEN 50000001 AND 200000000 THEN '> 50-200 JT'
ELSE '> 200 JT'
END AS range_nilai,
COUNT(DISTINCT no_pk) AS jumlah_pk
FROM $table
WHERE status_pr IN ('approve', 'submit')
AND total_nilai_hasil IS NOT NULL AND tahun_anggaran IS NOT NULL AND sumber_dana IS NOT NULL AND $kolom_periode IS NOT NULL
AND $periode_filter
GROUP BY
CASE
WHEN total_nilai_hasil BETWEEN 1 AND 50000000 THEN '≤ 50 JT'
WHEN total_nilai_hasil BETWEEN 50000001 AND 200000000 THEN '> 50-200 JT'
ELSE '> 200 JT'
END
ORDER BY MIN(total_nilai_hasil);";
$stmt = $connection->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
return $result;
}
public function getKategoriPlant()
{
$connection = $this->getDoctrine()->getConnection();
$sql = "select kategori, count(*) as jumlah from m_plant GROUP BY kategori order by kategori desc";
$stmt = $connection->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
return $result;
}
public function getBulanList()
{
$bulanList = [
'01' => 'Januari',
'02' => 'Februari',
'03' => 'Maret',
'04' => 'April',
'05' => 'Mei',
'06' => 'Juni',
'07' => 'Juli',
'08' => 'Agustus',
'09' => 'September',
'10' => 'Oktober',
'11' => 'November',
'12' => 'Desember'
];
return $bulanList;
}
}

src/Controller/Base/BaseController.php line 2316