Symfony Profiler

<?php
namespace App\Controller\Backend;
use App\Controller\Base\BaseController;
use App\Entity\TExploreData;
use App\Entity\TExploreDataDetail;
use App\Entity\TMetaData;
use App\Filter\TExploreDataDetailFilterType;
use App\Filter\TExploreDataFilterType;
use App\Filter\TGrafikFilterType;
use App\Form\TExploreDataType;
use App\Form\TGrafikType;
use App\Repository\TDataRepository;
use App\Repository\TGrafikRepository;
use App\Repository\TLogRepository;
use App\Repository\TPublikasiRepository;
use App\Repository\TVariabelRepository;
use App\Repository\MKotaRepository;
use App\Repository\TDeskripsiDataRepository;
use App\Repository\TExploreDataDetailRepository;
use App\Repository\TExploreDataRepository;
use App\Repository\TMetaDataRepository;
use App\Transformer\DataTransformer;
use App\Transformer\NewPublikasiTransformer;
use App\Utils\ObjectManager;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;
use Kematjaya\Breadcrumb\Lib\Builder as BreacrumbBuilder;
use Knp\Component\Pager\PaginatorInterface;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpKernel\Exception\NotFoundHttpException;
use Kematjaya\ImportBundle\Manager\ImportManagerInterface;
use Kematjaya\ImportBundle\DataSource\RemoteDataSource;
use App\Transformer\PublikasiTransformer;
use App\Transformer\VariabelTransformer;
use Doctrine\Persistence\ManagerRegistry;
use Kematjaya\Export\Manager\ManagerInterface;
use Kematjaya\Export\Processor\Excel\SpreadsheetFromArrayProcessor;
use Psr\Log\LoggerInterface;
use Symfony\Component\Routing\Generator\UrlGeneratorInterface;
use Symfony\Component\Security\Core\Security;
use Exception;
class TExploreDataController extends BaseController
{
private $pageName = 'explore_data';
private $class = TExploreDataDetail::class;
/**
* @Route("/explore_data", name="explore_data")
*/
public function index(BreacrumbBuilder $builder, Request $request, PaginatorInterface $paginatorInterface, Security $security): Response
{
$builder->add('Dashboard ', "dashboard", array(), " <i class='fa fa-home'></i>");
$builder->add('ANALYTICS');
$user = $security->getUser();
// dump($user->getRoles()); exit;
$form = $this->createFormFilter(TExploreDataDetailFilterType::class);
$queryBuilder = $this->getQueryBuilder($this->class);
$queryBuilder = $this->buildFilter($request, $form, $queryBuilder)->addOrderBy("this.id", "DESC");
$this->setSessionLimit($request);
$maxPerPage = $request->getSession()->get("limit") ? $request->getSession()->get("limit") : $this->getLimit();
$pagination = $paginatorInterface->paginate($queryBuilder, $request->query->getInt('page', 1), $maxPerPage);
return $this->render('backend/explore_data/index.html.twig', [
'page_name' => $this->pageName,
'button_credential' => $this->buttonCredentials($this->pageName),
'filter' => $form->createView(),
'pagination' => $pagination,
]);
}
/**
* @Route("/explore_data/{id}/detail", name="explore_data_detail", methods={"GET", "POST"})
*/
public function detail(
BreacrumbBuilder $builder,
TExploreData $tExploreData,
TVariabelRepository $tVariabelRepo,
TExploreDataRepository $tExploreDataRepo,
TExploreDataDetailRepository $tExploreDataDetailRepo,
Request $request,
TDeskripsiDataRepository $tDeskripsiDataRepository
) {
$builder->add('Dashboard ', "dashboard", array(), " <i class='fa fa-home'></i>");
$builder->add('ANALYTICS', "explore_data");
$builder->add('Detail');
$variable = $request->get('variable');
$tahun = $request->get('year') == "" ? (new \DateTime())->format('Y') : $request->get('year');
$explore = $tExploreDataRepo->find($tExploreData);
$detail_explore = $tExploreDataDetailRepo->findBy(['explore_id' => $explore->getId()], ['id' => 'ASC']);
// $tipe_grafik = ($explore) ? $grafik->getTipe() : null;
$grafik_info = [];
return $this->render('backend/explore_data/detail.html.twig', [
'page_name' => $this->pageName,
'button_credential' => $this->buttonCredentials($this->pageName),
// '' => $detail,
// 'kategori' => $kategori,
// 'variabel' => $variabel,
'explore' => $explore,
'detail_explore' => $detail_explore,
// 'columns' => $columns,
// 'grafik_info' => $grafik_info,
// // 'indikator' => $indikator,
// 'tipe_grafik' => $tipe_grafik,
// // 'pub_id' => $detail->getId(),
// 'deskripsi' => $grafik ? $grafik->getDeskripsi() : '',
// 'choiced_field' => json_encode($variable),
// 'total' => $total,
// // 'average_year' => $tahunRataRata,
// // 'average_value' => $valueRataRata,
// 'label_min' => $labelMin,
// 'label_max' => $labelMax,
// 'value_min' => $valueMin,
// 'value_max' => $valueMax,
// 'variabel_name' => $variable_name,
// 'value_name' => $value_name
]);
}
/**
* @Route("/explore_data/{id}/delete_explore_data", name="explore_data_delete", methods={"GET"})
*/
public function delete(?string $id = null, TExploreDataDetailRepository $tExploreDataDetailRepo, TLogRepository $tLogRepo, LoggerInterface $dbLogger, Security $security)
{
$user = $security->getUser();
$explore = $tExploreDataDetailRepo->find($id);
// dump($kategori); exit;
$entityManager = $this->getDoctrine()->getManager();
$entityManager->remove($explore);
$entityManager->flush();
$dbLogger->info("Hapus Eksplorasi " . $explore->getNama(), ["reff_name" => "delete_explorasi", "reff_id" => $explore->getId(), "created_by" => $user->getId()]);
$this->addFlash('notice', 'Eksplorasi Data "' . $explore->getNama() . '" berhasil dihapus');
return $this->redirectToRoute('explore_data');
}
/**
* @Route("/add_selected", name="publikasi_add_selected", methods={"POST"})
*/
public function add_selected(Request $request, ObjectManager $objectManager)
{
$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);
}
/**
* @Route("/action_selected", name="publikasi_action_selected", methods={"POST"})
*/
public function action_selected(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($this->class, $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'));
}
}
return $this->redirectToRoute('publikasi');
}
/**
* @Route("/explore_data/form_explore{id}", defaults={"id"= null}, name="explore_data_form_explore", methods={"GET", "POST"})
*/
public function form_explore(
?string $id,
Request $request,
TMetaDataRepository $tMetaDataRepo,
TExploreDataRepository $tExploreDataRepo,
TVariabelRepository $tVariabelRepo,
LoggerInterface $dbLogger,
Security $security,
ManagerRegistry $managerRegistry
) {
$user = $security->getUser();
$column_select = array('x' => 0, 'y' => 0);
if ($id) {
$explore = $tExploreDataRepo->findOneBy(['id' => $id]);
} else {
$explore = new TExploreData();
}
// if(!$grafik){
// throw new NotFoundHttpException();
// }
$form = $this->createForm(TExploreDataType::class, $explore, ['action' => $this->generateUrl('explore_data_form_explore', ['id' => $id])]);
$form->handleRequest($request);
if ($form->isSubmitted() && $form->isValid()) {
// dump($request->request->get('axisy'));exit;
$data = $form->getData();
$data->setCreatedAt(new \DateTime());
$entityManager = $this->getDoctrine()->getManager();
$entityManager->persist($data);
$entityManager->flush();
$dbLogger->info("Membuat Explore data " . $data->getNama(), ["reff_name" => "buat_explore_data", "reff_id" => $data->getId(), "created_by" => $user->getId()]);
$this->addFlash('notice', 'berhasil Buat Data');
return $this->redirectToRoute('explore_data');
}
return $this->render('/backend/explore_data/form_explore.html.twig', [
'form' => $form->createView()
]);
}
/**
* @Route("/explore_data/form_detail", name="explore_data_form_explore_detail", methods={"GET", "POST"})
*/
public function form_detail(
Request $request,
TMetaDataRepository $tMetaDataRepo,
TExploreDataRepository $tExploreDataRepo,
TExploreDataDetailRepository $tExploreDataDetailRepo,
TVariabelRepository $tVariabelRepo,
LoggerInterface $dbLogger,
Security $security,
ManagerRegistry $managerRegistry
) {
$user = $security->getUser();
$header = $this->getDataHeader();
// print_r($header); exit;
$header = $header[1]['head2'];
$column_select = array('x' => 0, 'y' => 0);
// print_r($request->request->get('id')); exit;
if ($request->request->get('id')) {
$detail = $tExploreDataDetailRepo->findOneBy(['id' => $request->request->get('id')]);
$columns = $this->getKolomTabel($detail->getTabel());
} else {
$detail = new TExploreDataDetail();
$columns = [];
}
// if(!$grafik){
// throw new NotFoundHttpException();
// }
// $tabels = $tMetaDataRepo->findAll(['id' => 'asc']);
$tabels = $tMetaDataRepo->findBy(['id' => 40], ['id' => 'asc']);
$view_explorasi = $tExploreDataDetailRepo->findBy(['is_create_view' => 'true']);
$res_view = [];
foreach ($view_explorasi as $explore) {
$res_view[$explore->getView()] = $explore->getNama() . ' (Analytic)';
}
// $res_table = ['t_weekly_explore_report' => 'Weekly Eksplore Report'];
$res_table = [];
foreach ($tabels as $table) {
$res_table[$table->getNamaTabel()] = $table->getJudul() . ' (Meta Data)';
}
$res_table = array_merge($res_table, $res_view);
// print_r($request->get('explore')); exit;
// $operations = ['SUM' => 'SUM', 'COUNT' => 'COUNT', 'AVG' => 'AVERAGE', 'PERSENTASE' => 'PERSENTASE'];
$operations = ['SUM' => 'SUM', 'COUNT' => 'COUNT', 'AVG' => 'AVERAGE'];
// if ($request->request->get('explore_id')) {
if ($request->isMethod('POST')) {
// dump($request->request->get('variabel'));exit;
$where = [];
$variabels = $request->request->get('variabel');
$operators = $request->request->get('where');
$isians = $request->request->get('isian');
if ($variabels) {
foreach ($variabels as $key => $field) {
$operator = strtoupper(trim($operators[$key]));
$isian = strtolower(trim($isians[$key])); // konversi ke huruf kecil
if ($operator === 'IN') {
// Pisahkan dan lowercase semua nilai
$values = array_map('trim', explode(',', $isian));
$quotedValues = array_map(fn($v) => "'" . strtolower($v) . "'", $values);
$formattedValue = '(' . implode(', ', $quotedValues) . ')';
$where[] = "LOWER($field) IN $formattedValue";
} else {
$formattedValue = "'" . $isian . "'";
$where[] = "LOWER($field) $operator $formattedValue";
}
}
$whereClause = implode(' AND ', $where);
}
$tabel = $request->request->get('table');
$kolom = $request->request->get('kolom');
$operation = $request->request->get('operation');
$connection = $this->getDoctrine()->getConnection();
if ($operation == 'SUM') {
$sql = "SELECT SUM(CASE
WHEN NULLIF(TRIM(REPLACE($kolom::text, ',', '')), '') IS NOT NULL
THEN (REPLACE($kolom::text, ',', ''))::numeric
ELSE 0
END) as jumlah FROM $tabel ";
} else if ($operation == 'PERSENTASE') {
$sql = "SELECT COUNT($kolom) * 100.0 / (SELECT COUNT($kolom) FROM $tabel) as jumlah";
$sql .= " FROM $tabel";
} else {
$sql = "SELECT $operation($kolom) as jumlah FROM $tabel ";
}
if ($variabels) {
$sql .= " WHERE $whereClause";
}
if ($request->request->get('is_create_view') && $request->request->get('periode')) {
$periode = $request->request->get('periode');
if ($variabels) {
$sql .= " AND $periode IS NOT NULL";
} else {
$sql .= " WHERE $periode IS NOT NULL";
}
}
$stmt = $connection->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
// print_r($result); exit;
$detail->setExploreId($request->request->get('explore_id'));
$detail->setNama($request->request->get('nama'));
$detail->setDeskripsi($request->request->get('deskripsi'));
$detail->setTabel($request->request->get('table'));
$detail->setKolom($request->request->get('kolom'));
$detail->setFrekuensi($request->request->get('frekuensi'));
$detail->setNilai($result[0][strtolower('jumlah')]);
$detail->setOperation($request->request->get('operation'));
$detail->setPeriode($request->request->get('periode'));
$detail->setFilter($where);
$detail->setCreatedAt(new \DateTime());
$entityManager = $this->getDoctrine()->getManager();
$entityManager->persist($detail);
$entityManager->flush();
if ($request->request->get('is_create_view')) {
$view = 'v_' . $detail->getId();
$detail->setView($view);
$detail->setIsCreateView(true);
}
// $query = $connection->prepare("select view_definition from information_schema.views where table_name = '$view'");
//create view
if ($request->request->get('is_create_view') && $request->request->get('periode')) {
$periode = $request->request->get('periode');
$table = $request->request->get('table');
if ($operation == 'SUM') {
$operationx = "SUM(CASE
WHEN NULLIF(TRIM(REPLACE($kolom::text, ',', '')), '') IS NOT NULL
THEN (REPLACE($kolom::text, ',', ''))::numeric
ELSE 0
END) AS nilai";
} else if ($operation == 'PERSENTASE') {
if ($variabels) {
$operationx = " COUNT($kolom) FILTER (WHERE $whereClause) * 100.0 / COUNT($kolom) AS nilai";
}
} else {
$operationx = "$operation($kolom) AS nilai";
}
$sql1 = "WITH data_bersih AS (
SELECT
kategori_plant,
sumber_dana,
TO_CHAR($periode, 'MONYYYY') || '.' ||
CASE
WHEN EXTRACT(DAY FROM $periode) BETWEEN 1 AND 7 THEN 'I'
WHEN EXTRACT(DAY FROM $periode) BETWEEN 8 AND 14 THEN 'II'
WHEN EXTRACT(DAY FROM $periode) BETWEEN 15 AND 21 THEN 'III'
ELSE 'IV'
END AS periode,
$operationx
FROM $tabel";
if ($variabels) {
// if ($operation != 'PERSENTASE') {
$sql1 .= " WHERE $whereClause";
$sql1 .= "AND $periode IS NOT NULL";
// }
$sql1 .= " GROUP BY kategori_plant, sumber_dana, periode
)
SELECT
kategori_plant,
sumber_dana,
periode,
ROUND(nilai, 2) AS nilai
FROM data_bersih
ORDER BY kategori_plant, sumber_dana, periode;";
} else {
$sql1 .= " GROUP BY kategori_plant, sumber_dana, periode
)
SELECT
kategori_plant,
sumber_dana,
periode,
ROUND(nilai, 2) AS nilai
FROM data_bersih
ORDER BY kategori_plant, sumber_dana, periode;";
}
if ($request->request->get('is_create_view')) {
$stmt = $connection->prepare("CREATE VIEW $view AS $sql1");
$stmt->execute();
}
}
$entityManager->persist($detail);
$entityManager->flush();
$dbLogger->info("Membuat Explore Data " . $detail->getNama(), ["reff_name" => "buat_detail_explore_data", "reff_id" => $detail->getId(), "created_by" => $user->getId()]);
$this->addFlash('notice', 'Tambah Berhasil');
return $this->redirectToRoute('explore_data');
}
return $this->render('/backend/explore_data/form_detail.html.twig', [
'columns' => $columns,
'explore_id' => ($detail->getExploreId()) ? $detail->getExploreId() : $request->get('explore'),
'tables' => $res_table,
'operations' => $operations,
'detail' => $detail,
'header' => $header,
'isForm' => true
]);
}
/**
* @Route("/explore_data/{id}/delete_detail", name="explore_data_delete_detail", methods={"GET"})
*/
public function delete_detail(?string $id = null, TExploreDataDetailRepository $tExploreDataDetailRepo, TLogRepository $tLogRepo)
{
$detail = $tExploreDataDetailRepo->find($id);
// dump($meta_data); exit;
$entityManager = $this->getDoctrine()->getManager();
$entityManager->remove($detail);
$entityManager->flush();
//hapus view
// $connection = $this->getDoctrine()->getConnection();
// $tbl = $detail->getView();
// $query = $connection->prepare("select * from information_schema.tables WHERE table_schema='public' AND table_name='$tbl'");
// $query->execute();
// $hasil = $query->fetchAll();
// if ($hasil) {
// $stmt = $connection->prepare("DROP TABLE $tbl");
// $stmt->execute();
// }
$tLogRepo->setLog($this->getDoctrine()->getManager(), null, $detail->getId(), "Hapus Explore detail " . $detail->getNama(), $this->getUser()->getName());
$this->addFlash('notice', 'Eksplorasi "' . $detail->getNama() . '" berhasil dihapus');
return $this->redirectToRoute('explore_data_detail', ['id' => $detail->getExploreId()]);
}
/**
* @Route("/explore_data/view_nilai", name="explore_data_view_nilai", methods={"GET", "POST"})
*/
public function view_nilai(
?string $id,
Request $request,
TMetaDataRepository $tMetaDataRepo,
TExploreDataRepository $tExploreDataRepo,
TVariabelRepository $tVariabelRepo,
LoggerInterface $dbLogger,
Security $security,
ManagerRegistry $managerRegistry
) {
$user = $security->getUser();
return $this->render('backend/explore_data/view_nilai.html.twig', [
'explorasi' => $request->get('explorasi')
]);
}
/**
* @Route("/explore_data/detail_data_table_view", name="explore_data_detail_datatable_periode", methods={"GET", "POST"})
*/
public function data_table_periode(Request $request, TExploreDataDetailRepository $tExploreDataDetailRepo)
{
$item_per_page = $_POST['length'];
$page = ($_POST['start'] / $item_per_page) + 1;
$page = ($page - 1) * 10;
$explorasi = $tExploreDataDetailRepo->findOneBy(['id' => $request->get('explorasi')]);
$table = ($explorasi->isIsCreateView() == 1) ? $explorasi->getView() : $explorasi->getTabel();
// print_r($table); exit;
$detail = $this->getDataTableDetailExplorasiPeriode(10, $page, $_POST, $table, $explorasi->getId());
$jumlah = count($this->getDataTableDetailExplorasiPeriode(null, null, $_POST, $table, $explorasi->getId()));
$arrJson['aaData'] = $detail;
$arrJson['iTotalRecords'] = $jumlah;
$arrJson['iTotalDisplayRecords'] = $jumlah;
return new JsonResponse($arrJson);
}
/**
* @Route("/explore_data/get_column", name="explore_data_get_column", methods={"POST", "GET"})
*/
public function get_column(
Request $request,
TMetaDataRepository $tMetaDataRepo,
TVariabelRepository $tVariabelRepo,
ManagerRegistry $managerRegistry
) {
$id = $request->get('id');
// $meta_data = $tMetaDataRepo->findOneBy(['id' => $id]);
// $variabel = $tVariabelRepo->findBy(['reff_id' => $meta_data->getId(), 'reff_kode' => 'METADATA'], ['id' => 'ASC']);
$hasil = $this->getKolomTabel($id);
$optKolom = '<option value=""></option>';
$optPeriode = '<option value=""></option>';
if ($hasil) {
// $kategoriPlant = ['column_name' => 'kategori_plant'];
// array_unshift($hasil, $kategoriPlant);
foreach ($hasil as $value) {
if (in_array($value['column_name'], ['id', 'created_at', 'updated_at'])) {
continue;
}
// Semua kolom untuk dropdown "kolom"
$optKolom .= '<option value="' . $value['column_name'] . '">' . $value['column_name'] . '</option>';
// Khusus kolom yang mengandung "tgl_" untuk dropdown "periode"
if (strpos($value['column_name'], 'tgl_') !== false) {
$optPeriode .= '<option value="' . $value['column_name'] . '">' . $value['column_name'] . '</option>';
}
}
}
return new JsonResponse([
'kolom' => $optKolom,
'periode' => $optPeriode,
]);
}
/**
* @Route("/t_grafik/{id}/chartJson", name="t_grafik_chart_json", methods={"POST", "GET"})
*/
public function chartJson(
Request $request,
// TPublikasi $tPublikasi,
TGrafik $tGrafik,
TGrafikRepository $tGrafikRepo,
TVariabelRepository $tVariabelRepo,
TDataRepository $tDataRepo,
ManagerRegistry $managerRegistry
) {
$detail = $tGrafikRepo->find($tGrafik);
$graph = $this->getTabelGrafik($detail->getId(), 'chart');
return $graph;
}
/**
* @Route("/t_grafik/{id}/dataTable", name="t_grafik_data_table", methods={"POST", "GET"})
*/
public function dataTable(
Request $request,
// TPublikasi $tPublikasi,
TGrafik $tGrafik,
TGrafikRepository $tGrafikRepo,
TVariabelRepository $tVariabelRepo,
TDataRepository $tDataRepo,
ManagerRegistry $managerRegistry
) {
$detail = $tGrafikRepo->find($tGrafik);
$graph = $this->getTabelGrafik($detail->getId(), 'table');
return $graph;
}
}

src/Controller/Backend/TExploreDataController.php line 55