<?php
namespace App\Controller\Base;
use App\Entity\MKategori;
use App\Entity\MKategoriDetail;
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 Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use App\Utils\ObjectManager;
use App\Utils\Breadcrumb\BreadcrumbBuilder;
use Doctrine\DBAL\Driver\PDO\PgSQL\Driver;
use Doctrine\DBAL\DriverManager;
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;
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;
public function __construct(
ObjectManager $objectManager,
TranslatorInterface $translation,
FilterBuilderUpdaterInterface $filterBuilder,
BreadcrumbBuilder $breadcrumbBuilder,
ManagerRegistry $managerRegistry
) {
$this->translation = $translation;
$this->filterBuilder = $filterBuilder;
$this->objectManager = $objectManager;
$this->breadcrumbBuilder = $breadcrumbBuilder;
$this->managerRegistry = $managerRegistry;
}
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->get('_reset')) {
$this->setFilters(null, $form->getName());
}
$filters = $request->get($form->getName());
if ($filters) {
$form->submit($filters);
$this->setFilters($form->getData(), $form->getName());
}
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 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 getHeaderKategori()
{
$mKategoriRepo = $this->getDoctrine()->getRepository(MKategori::class);
$kategori = $mKategoriRepo->findBy([], ['id' => 'asc']);
return $kategori;
}
public function getDataTable($id, $limit, $page, $get, $state = null, $row = null)
{
$connection = $this->managerRegistry->getConnection();
// $connection = DriverManager::getConnection($this->connParams);
$tbl_func = "CREATE EXTENSION IF NOT EXISTS tablefunc; ";
$stmt = $connection->prepare($tbl_func);
$stmt->execute();
$variabel_repo = $this->managerRegistry->getRepository(TVariabel::class);
$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";
if ($row) {
$sql .= " and row_id = " .$row;
}
if ($get) {
foreach($variabel as $var){
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) {
$temp[] = $data[$var->getNama()];
}
$result[] = $temp;
}
// 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
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
select 'DATASET'::text as tipe, a.id as id, a.judul as judul, a.deskripsi as deskripsi, 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
select 'INFOGRAFIK'::text as tipe, a.id as id, a.judul as judul, a.deskripsi as deskripsi, 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%'
where judul ilike '%$search%' or tipe ilike '%$search%' or deskripsi 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()
{
$ptiRuleRepo = $this->getDoctrine()->getRepository(PtiRule::class);
$ptiGroupRepo = $this->getDoctrine()->getRepository(PtiGroup::class);
$user_group = $this->getUser()->getRoles()[0];
$group = $ptiGroupRepo->findOneBy(['credential' => $user_group]);
$rules = $ptiRuleRepo->findBy(['groups' => $group, 'is_allowed' => true]);
$credentials = [];
foreach ($rules as $rule) {
$credentials[$rule->getModuleAction()->getModule()->getUrl()][$rule->getModuleAction()->getAction()] = $rule->getId();
}
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 getTabelGrafik($id, $cols, $p_order)
{
$connection = $this->getDoctrine()->getConnection();
// dump($p_order); exit;
$tbl_func = "CREATE EXTENSION IF NOT EXISTS tablefunc; ";
$stmt = $connection->prepare($tbl_func);
$stmt->execute();
$variabel_repo = $this->getDoctrine()->getRepository(TVariabel::class);
$variabel = $variabel_repo->findBy(['pub' => $id], ['id' => 'asc']);
$pub_repo = $this->getDoctrine()->getRepository(TPublikasi::class);
$publikasi = $pub_repo->find($id);
$as_columns = "";
foreach ($variabel as $var) {
$as_columns .= ", " . $var->getNama() . " text";
}
$selCols = [];
foreach ($cols as $col) {
$selCols[] = "ct." . $col->getNama();
}
$selCols = implode(", ", $selCols);
if($publikasi->getJenis() == 1){
$sql = "SELECT $selCols, ct.periode_order, ct.periode_update
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";
$sql .= " and ct.periode_update = '$p_order'";
}else{
$tah = explode(" ",$p_order);
$sql = "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;
$sql .= " order by 1, 2')
AS ct (row_id int" . $as_columns . ") where 1=1";
// $sql .= " and ct.tahun = '$p_order'";
if($tah[1] == 'All'){
}else{
$sql .= " and ct.tahun = '$tah[1]'";
}
}
$stmt = $connection->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
// foreach ($stmt->fetchAll() as $data) {
// $result[] = floatval($data[$col]);
// }
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 getArrayPeriode($id=null)
{
$arr = [
6 => [
'01-01/01-31' => 'Januari',
'02-01/02-28' => 'Februari',
'03-01/03-31' => 'Maret',
'04-01/04-30' => 'April',
'05-01/05-31' => 'Mei',
'06-01/06-30' => 'Juni',
'07-01/07-31' => 'Juli',
'08-01/08-31' => 'Agustus',
'09-01/09-30' => 'September',
'10-01/10-31' => 'Oktober',
'11-01/11-30' => 'November',
'12-01/12-31' => 'Desember'
],
7 => [
'01-01/03-31' => 'Triwulan I',
'04-01/06-30' => 'Triwulan II',
'07-01/09-30' => 'Triwulan III',
'10-01/12-31' => 'Triwulan IV'
],
8 => [
'01-01/06-30' => 'Semester I',
'07-01/12-31' => 'Semester II'
],
9 => [
'01-01/12-31' => '1 Januari s/d 31 Desember'
]
];
if ($id) {
return $arr[$id];
}
return $arr;
}
public function getPeriodeOrder($tipe, $key) {
if ($tipe == 5) {
return intval($key);
} else if($tipe == 6) {
$tipex = array(
'01-01/01-31' => 1,
'02-01/02-28' => 2,
'03-01/03-31' => 3,
'04-01/04-30' => 4,
'05-01/05-31' => 5,
'06-01/06-30' => 6,
'07-01/07-31' => 7,
'08-01/08-31' => 8,
'09-01/09-30' => 9,
'10-01/10-31' => 10,
'11-01/11-30' => 11,
'12-01/12-31' => 12
);
return $tipex[$key];
}else if($tipe == 7){
$tipex = array(
'01-01/03-31' => 1,
'04-01/06-30' => 2,
'07-01/09-30' => 3,
'10-01/12-31' => 4,
);
return $tipex[$key];
}else if($tipe == 8){
$tipex = array(
'01-01/06-30' => 1,
'07-01/12-31' => 2,
);
return $tipex[$key];
}else{
$tipex = array(
'01-01/12-31' => 1,
);
return $tipex[$key];
}
return null;
}
public function generateKode($sektor, $urusan, $last)
{
$urutan = $last + 1;
$urutan = str_pad($urutan, 4, "0", STR_PAD_LEFT);
return "$sektor.$urusan.$urutan";
}
public function base_url($link='', $image = false)
{
$host = (isset($_SERVER['HTTP_X_FORWARDED_HOST'])) ? $_SERVER['HTTP_X_FORWARDED_HOST'] : $_SERVER['HTTP_HOST'];
$url = ((isset($_SERVER['HTTPS'])) ? 'https' : 'http') . "://" . $host;
if($image){
$url .= preg_replace('@/+$@','',dirname($_SERVER['SCRIPT_NAME']));
}else{
$url .= $_SERVER['SCRIPT_NAME'];
}
$url .= $link;
return $url;
}
}