Symfony Profiler

<?php
namespace App\Controller\Backend;
use App\Controller\Base\BaseController;
use App\Entity\TDataGrafik;
use App\Entity\TFileGrafik;
use App\Entity\TGrafik;
use App\Form\TGrafikType;
use App\Entity\TGrafikDinamis;
use App\Entity\TMetaData;
use App\Entity\TVariabel;
use App\Entity\TVariabelGrafik;
use App\Repository\MKategoriDetailRepository;
use App\Repository\MKategoriRepository;
use App\Repository\TDivRepository;
use App\Repository\TGrafikDinamisRepository;
use App\Repository\TGrafikRepository;
use App\Repository\TLogRepository;
use App\Repository\TVariabelGrafikRepository;
use App\Repository\TFileGrafikRepository;
use Doctrine\Persistence\ManagerRegistry;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\Routing\Annotation\Route;
use Kematjaya\Breadcrumb\Lib\Builder as BreacrumbBuilder;
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 DateTime;
use Sonata\SeoBundle\Seo\SeoPageInterface;
use PhpOffice\PhpSpreadsheet\IOFactory;
use Knp\Component\Pager\PaginatorInterface;
use App\Filter\TGrafikDinamisFilterType;
use App\Filter\TMetaDataFilterType;
use App\Repository\TDataGrafikRepository;
use App\Repository\TMetaDataRepository;
use App\Repository\TProgresRepository;
use App\Repository\TRawDataRepository;
use App\Repository\TVariabelRepository;
use Symfony\Component\Process\Process;
class TMetaDataController extends BaseController
{
private $pageName = 't_meta_data';
private $class = TMetaData::class;
/**
* @Route("/t/meta/data", name="t_meta_data")
*/
public function index(SeoPageInterface $seoPage, BreacrumbBuilder $builder, Request $request, PaginatorInterface $paginatorInterface): Response
{
$userRoles = $this->getUser()->getRoles();
$seoPage->addTitlePrefix("Dataset");
$builder->add('Dashboard ', "dashboard", array(), " ");
$builder->add('Meta Data');
$filter = TMetaDataFilterType::class;
$form = $this->get('form.factory')->create($filter, $this->getFilters($filter));
$queryBuilder = $this->getQueryBuilder($this->class);
// if ($userRoles[0] != 'ROLE_ADMINISTRATOR' && $userRoles[0] != 'ROLE_WALIDATA' && $userRoles[0] != 'ROLE_SUPER_USER'){
// $queryBuilder = $queryBuilder->where('this.div = ' . $this->getUser()->getDiv()->getId());
// }
$queryBuilder = $queryBuilder->where('this.id = 40');
$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/t_meta_data/index.html.twig', [
'page_name' => $this->pageName,
'button_credential' => $this->buttonCredentials(),
'filter' => $form->createView(),
'pagination' => $pagination,
]);
}
/**
* @Route("/t_meta_data/{id}/detail", name="t_meta_data_detail", methods={"GET", "POST"})
*/
public function detail(
BreacrumbBuilder $builder,
TMetaDataRepository $tMetaDataRepo,
TMetaData $tMetaData,
MKategoriRepository $mKategoriDetailRepo,
TVariabelRepository $tVariabelRepo,
TGrafikRepository $tGrafikRepo,
TFileGrafikRepository $tfileGrafikRepo
) {
$builder->add('Dashboard ', "dashboard", array(), " ");
$builder->add('Meta Data', "t_meta_data");
$builder->add('Detail');
$detail = $tMetaDataRepo->find($tMetaData);
// $files = $tfileGrafikRepo->findBy(['grafik' => $detail->getId()]);
// $kategori = $mKategoriDetailRepo->findBy(['reff_id' => $detail->getId(), 'reff_name' => 'PUBLIKASI']);
$variabel = $tVariabelRepo->findBy(['reff_id' => $detail->getId(), 'reff_kode' => 'METADATA'], ['id' => 'ASC']);
// $opt_periode = $this->getPeriodeName($detail->getPeriode()->getId());
$grafik = $tGrafikRepo->findOneBy(['reff_id' => $detail->getId()]);
$grafik_info = [];
// if ($grafik) {
// // $variabel_x = $tVariabelRepo->find($grafik->getAxisX());
// // $variabel_y = $tVariabelGrafikRepo->find($grafik->getAxisY());
// $grafik_info = [
// 'title' => $grafik->getJudul(),
// 'tipe' => $grafik->getTipe(),
// 'varX' => $variabel_x->getNama()
// // 'varY' => $variabel_y->getNama()
// ];
// }
// dump($grafik_info); exit;
return $this->render('backend/t_meta_data/detail.html.twig', [
'page_name' => $this->pageName,
'button_credential' => $this->buttonCredentials(),
'meta_data' => $detail,
// 'kategori' => $kategori,
'variabel' => $variabel,
// 'grafik' => $grafik,
// 'opt_periode' => $opt_periode,
// 'grafik_info' => $grafik_info,
// 'files' => $files
]);
}
/**
* @Route("/t_meta_data/{id}/delete_t_meta_data", name="t_meta_data_delete", methods={"GET"})
*/
public function delete(?string $id = null, TMetaDataRepository $tMetaDataRepo, TLogRepository $tLogRepo, TVariabelRepository $tVariabelRepo)
{
$meta_data = $tMetaDataRepo->find($id);
// dump($meta_data); exit;
$entityManager = $this->getDoctrine()->getManager();
$entityManager->remove($meta_data);
$entityManager->flush();
//hapus variable
$variabel = $tVariabelRepo->findBy(['reff_id' => $meta_data->getId(), 'reff_kode' => 'METADATA'], ['id' => 'ASC']);
foreach ($variabel as $var) {
$entityManager->remove($var);
$entityManager->flush();
}
//hapus db
$connection = $this->getDoctrine()->getConnection();
$tbl = $meta_data->getNamaTabel();
$query = $connection->prepare("select * from information_schema.tables WHERE table_schema='public' AND table_name='$tbl'");
$query->execute();
$hasil = $query->fetchAll();
// print_r($hasil); exit;
if ($hasil) {
$stmt = $connection->prepare("DROP TABLE $tbl");
$stmt->execute();
}
$tLogRepo->setLog($this->getDoctrine()->getManager(), null, $meta_data->getId(), "Hapus Meta Data " . $meta_data->getJudul() . ' nama_table: ' . $meta_data->getNamaTabel(), $this->getUser()->getName());
$this->addFlash('notice', 'Meta Data "' . $meta_data->getJudul() . '" berhasil dihapus');
return $this->redirectToRoute('t_meta_data');
}
/**
* @Route("/t_meta_data/new/{id}", defaults={"id"= null}, name="t_meta_data_new", methods={"POST", "GET"})
*/
public function new(?string $id, Request $request, TMetaDataRepository $tMetaDataRepo, ManagerRegistry $managerRegistry, TVariabelRepository $tVariabelRepo, BreacrumbBuilder $builder)
{
$user = $this->getUser();
$builder->add('Dashboard ', "dashboard", array(), " ");
$builder->add('Meta Data', "t_meta_data");
$builder->add('Form Meta Data');
$header = $this->getDataHeader();
// print_r($header); exit;
$header = $header[1]['head2'];
$isAdmin = false;
if ($user->getRoles()[0] == "ROLE_ADMINISTRATOR" || $user->getRoles()[0] == "ROLE_SUPER_USER") {
$isAdmin = true;
}
$em = $managerRegistry->getManager();
if ($id) {
$t_meta_data = $tMetaDataRepo->find($id);
} else {
$t_meta_data = new TMetaData();
}
if (!$t_meta_data) {
throw new NotFoundHttpException();
}
// dump($t_meta_data); exit;
if ($request->isMethod('POST')) {
$connection = $this->getDoctrine()->getConnection();
//cek table
// print_r($var); exit;
//created_db
// $connection = DriverManager::getConnection($this->connParams);
// $tbl = 'table_ab';
// $var_static = 'no_pr VARCHAR(250)';
// $koloms = [$var_static.','.implode(",",$var)];
// print_r($koloms); exit;
// $kolom = [];
// foreach($koloms as $key=>$value){
// $kolom[] = $value. ' VARCHAR(250)';
// }
if (!$id) {
//$countPub = $tGrafikDinamisRepo->findBy(['urusan'=>$request->get('urusan'), 'sektor'=>$request->get('sektor')]);
// $kode = $this->generateKode($request->get('urusan'), $request->get('sektor'), count($countPub));
//$publikasi->setKode($kode);
$t_meta_data->setCreatedAt(new \DateTimeImmutable());
}
$t_meta_data->setJudul($request->get('judul'));
$t_meta_data->setDeskripsi($request->get('deskripsi'));
// $publikasi->setDeskripsi($request->get('deskripsi'));
// $publikasi->setUrusan($mUrusanRepo->find($request->get('urusan')));
// $publikasi->setSektor($mSektorRepo->find($request->get('sektor')));
// if ($isAdmin) {
// $publikasi->setDiv($tDivRepo->find($request->get('opd')));
// } else {
// $publikasi->setDiv($user->getDiv());
// }
// $publikasi->setStatus($mStatusRepo->find(13));
if (!$id) {
$variable = $request->get('variable');
if (!$variable) {
$this->addFlash('error', 'Simpan Gagal, Wajib memasukkan variable');
return $this->redirectToRoute('t_meta_data_new');
}
$em->persist($t_meta_data);
$em->flush();
// print_r($variable); exit;
$variable_tambahan = ['kategori_plant' => 'kategori_plant', 'regional' => 'regional', 'kategori_tahap' => 'kategori_tahap', 'nilai_saving_terhadap_anggaran' => 'nilai_saving_terhadap_anggaran', 'nilai_saving_terhadap_hps' => 'nilai_saving_terhadap_hps', 'saving_persen_terhadap_angggaran' => 'saving_persen_terhadap_angggaran', 'saving_persen_terhadap_hps' => 'saving_persen_terhadap_hps'];
$header = array_merge($header, $variable_tambahan);
$connection = $this->getDoctrine()->getConnection();
$tbl = str_replace(" ", "_", strtolower('tabel_' . $t_meta_data->getId()));
$t_meta_data->setNamaTabel($tbl);
$em->persist($t_meta_data);
$em->flush();
// $query = $connection->prepare("select * from information_schema.tables WHERE table_schema='public' AND table_name='$tbl'");
// $query->execute();
// $hasil = $query->fetchAll();
// if ($hasil) {
// $this->addFlash('error', 'Nama Table "' . $tbl . '" Sudah Ada');
// return $this->redirectToRoute('t_meta_data');
// }
$var = [];
$variable = array_unique(array_merge(array_values($variable), array_values($variable_tambahan)));
// print_r($variable); exit;
foreach ($variable as $key => $value) {
if (in_array($value, $variable_tambahan)) {
$tipe_data = 'VARCHAR(250)';
$label_kolom = $value;
// $kolomx = $key;
$nama_var = $value;
} else {
$tipe_data = ($request->get('tipe_data')[$key] == 'VARCHAR') ? 'VARCHAR(250)' : $request->get('tipe_data')[$key];
$label_kolom = $header[$value];
$nama_var = $this->cek_karakter($this->buang(strtolower($header[$value])));
// $kolomx = $value;
}
$var[] = $nama_var . ' ' . $tipe_data;
if ($label_kolom == 'saving_persen_terhadap_angggaran') {
$label_kolomx = 'Saving Tehadap Anggaran (%)';
} else if ($label_kolom == 'saving_persen_terhadap_hps') {
$label_kolomx = 'Saving Tehadap HPS (%)';
} else {
$label_kolomx = str_replace("_", " ", $label_kolom);
}
$variablex = new TVariabel();
$variablex->setNama($nama_var);
$variablex->setKolom($value);
$variablex->setReffKode('METADATA');
$variablex->setReffId($t_meta_data->getId());
$variablex->setTipeData(str_replace("(250)", "", $tipe_data));
$variablex->setLabelKolom(ucwords($label_kolomx));
$em->persist($variablex);
$em->flush();
}
$kolom = implode(",", $var);
$stmt = $connection->prepare("CREATE TABLE $tbl(id serial PRIMARY KEY,$kolom)");
$stmt->execute();
//insert data
$variabels = $tVariabelRepo->findBy(['reff_id' => $t_meta_data->getId(), 'reff_kode' => 'METADATA'], ['id' => 'ASC']);
$array = $this->getDataTableCleansing(null, null, null, null);
$items = $array['isi'] ?? [];
// dump($items);
// exit;
$var = [];
foreach ($variabels as $variabel) {
$var[$variabel->getKolom()] = $variabel->getNama();
}
// Ambil hanya key dari var
$keysVar = array_keys($var);
// Hasil akhir
$result = [];
foreach ($items as $index => $row) {
// if ($row['Q'] == 'Ya') {
// continue;
// }
$result[$index] = array_intersect_key($row, array_flip($keysVar));
}
// $tablex = $meta_data->getNamaTabel();
$kolom = array_values($var); // Nama kolom
$kolomSql = implode(",", $kolom);
// print_r($kolomSql); exit;
// Ambil metadata tipe kolom dari database
$schemaManager = $connection->getSchemaManager();
$columnsInfo = $schemaManager->listTableColumns($tbl);
// Buat peta: kolom => tipe data
$columnTypes = [];
foreach ($columnsInfo as $colName => $column) {
$columnTypes[$colName] = $column->getType()->getName(); // contoh: 'date', 'string', 'integer'
}
// Proses setiap baris
foreach ($result as $row) {
$values = [];
foreach ($kolom as $col) {
$value = trim($row[array_search($col, $var)] ?? '');
// Format jika date
if ($columnTypes[$col] === 'date') {
if (!$value || strtolower($value) === 'null') {
$values[] = "NULL";
} else {
$formatted = date('Y-m-d', strtotime($value));
$values[] = "'$formatted'";
}
} else {
// Jika kolom numeric, bersihkan tanda koma
if (in_array($columnTypes[$col], ['decimal', 'numeric', 'float'])) {
$cleanValue = str_replace(',', '', $value);
$values[] = $cleanValue ?: 'NULL';
} else {
$values[] = "'" . addslashes(str_replace(["'", '"'], "", $value)) . "'";
}
}
}
$valuesSql = implode(",", $values);
// print_r('table:' . $tablex);
// print_r('kolom:' . $kolomSql);
// print_r('valuesSql:' . $valuesSql);
// exit;
$stmt = $connection->prepare("INSERT INTO $tbl($kolomSql) VALUES ($valuesSql)");
$stmt->execute();
}
}
$em->persist($t_meta_data);
$em->flush();
$this->addFlash('notice', 'Meta Data "' . $t_meta_data->getJudul() . '" berhasil ditambahkan');
return $this->redirectToRoute('t_meta_data');
}
return $this->render('/backend/t_meta_data/new.html.twig', [
't_meta_data' => $t_meta_data,
'header' => $header,
// 'mUrusan' => $mUrusan,
// 'mSektor' => $mSektor,
// 'tDiv' => $tDiv,
'isAdmin' => $isAdmin,
]);
}
/**
* @Route("/t_meta_data/{id}/sinkron", name="t_meta_data_sinkron", methods={"GET"})
*/
public function sinkron(?string $id = null, TMetaDataRepository $tMetaDataRepo, TVariabelRepository $tVariabelRepo, TRawDataRepository $tRawData, TMetaData $tMetaData, ManagerRegistry $managerRegistry)
{
$meta_data = $tMetaDataRepo->find($id);
$baseCmd = sprintf('%s/bin/console app:meta-data "%s"', $this->getParameter('kernel.project_dir'), $id);
// print_r($baseCmd);
// exit;
if (strncasecmp(PHP_OS, 'WIN', 3) === 0) {
$cmd = 'start /B php ' . $baseCmd;
} else {
$cmd = 'php7.4 ' . $baseCmd . ' > /dev/null 2>&1 &';
}
$process = Process::fromShellCommandline($cmd);
$process->disableOutput();
$process->start();
$this->addFlash('notice', 'Sinkron Meta Data "' . $meta_data->getJudul() . '" berhasil');
return $this->redirectToRoute('t_meta_data');
}
// public function sinkron(?string $id = null, TMetaDataRepository $tMetaDataRepo, TVariabelRepository $tVariabelRepo, TRawDataRepository $tRawData, TMetaData $tMetaData, ManagerRegistry $managerRegistry)
// {
// $connection = $this->getDoctrine()->getConnection();
// $em = $managerRegistry->getManager();
// $user = $this->getUser();
// $meta_data = $tMetaDataRepo->find($id);
// $variabels = $tVariabelRepo->findBy(['reff_id' => $meta_data->getId(), 'reff_kode' => 'METADATA'], ['id' => 'ASC']);
// $array = $this->getDataTableCleansing(null, null, null, null);
// $items = $array['isi'] ?? [];
// // dump($items);
// // exit;
// $var = [];
// foreach ($variabels as $variabel) {
// $var[$variabel->getKolom()] = $variabel->getNama();
// }
// // Ambil hanya key dari var
// $keysVar = array_keys($var);
// // Hasil akhir
// $result = [];
// foreach ($items as $index => $row) {
// // if ($row['Q'] == 'Ya') {
// // continue;
// // }
// $result[$index] = array_intersect_key($row, array_flip($keysVar));
// }
// $tablex = $meta_data->getNamaTabel();
// $kolom = array_values($var); // Nama kolom
// $kolomSql = implode(",", $kolom);
// // print_r($kolomSql); exit;
// // Ambil metadata tipe kolom dari database
// $schemaManager = $connection->getSchemaManager();
// $columnsInfo = $schemaManager->listTableColumns($tablex);
// // Buat peta: kolom => tipe data
// $columnTypes = [];
// foreach ($columnsInfo as $colName => $column) {
// $columnTypes[$colName] = $column->getType()->getName(); // contoh: 'date', 'string', 'integer'
// }
// // print_r($result); exit;
// // Proses setiap baris
// foreach ($result as $row) {
// $values = [];
// foreach ($kolom as $col) {
// $value = trim($row[array_search($col, $var)] ?? '');
// // Format jika date
// if ($columnTypes[$col] === 'date') {
// if (!$value || strtolower($value) === 'null') {
// $values[] = "NULL";
// } else {
// $formatted = date('Y-m-d', strtotime($value));
// $values[] = "'$formatted'";
// }
// } else {
// // Jika kolom numeric, bersihkan tanda koma
// if (in_array($columnTypes[$col], ['decimal', 'numeric', 'float'])) {
// $cleanValue = str_replace(',', '', $value);
// $values[] = $cleanValue ?: 'NULL';
// } else {
// $values[] = "'" . addslashes(str_replace(["'", '"'], "", $value)) . "'";
// }
// }
// }
// $valuesSql = implode(",", $values);
// //cek no_pr
// $no_pr = $row['B'];
// $stmt1 = $connection->prepare("SELECT * FROM $tablex WHERE no_pr = :no_pr");
// $stmt1->bindValue('no_pr', $no_pr);
// $stmt1->execute();
// $hasil = $stmt1->fetchAssociative();
// if ($hasil) {
// $setParts = [];
// foreach (array_combine($kolom, $values) as $colName => $val) {
// $setParts[] = "$colName = $val";
// }
// $setSql = implode(", ", $setParts);
// $stmt = $connection->prepare("UPDATE $tablex SET $setSql WHERE no_pr = '$no_pr'");
// $stmt->execute();
// } else {
// $stmt = $connection->prepare("INSERT INTO $tablex($kolomSql) VALUES ($valuesSql)");
// $stmt->execute();
// }
// }
// $this->addFlash('notice', 'Sinkron Meta Data "' . $meta_data->getJudul() . '" berhasil');
// return $this->redirectToRoute('t_meta_data');
// }
/**
* @Route("/t_meta_data/{id}/dataset_table", name="t_meta_data_dataset_table", methods={"GET", "POST"})
*/
public function dataset_table(?string $id, Request $request)
{
$item_per_page = $_POST['length'];
$page = ($_POST['start'] / $item_per_page) + 1;
$page = ($page - 1) * 10;
$dataset = $this->getDataTableMetaData($id, 10, $page, $_POST);
$jumlah = count($this->getDataTableMetaData($id, null, null, $_POST));
$arrJson['aaData'] = $dataset;
$arrJson['iTotalRecords'] = $jumlah;
$arrJson['iTotalDisplayRecords'] = $jumlah;
return new JsonResponse($arrJson);
}
/**
* @Route("/t_meta_data/progres", name="t_meta_data_progres", methods={"POST"})
*/
public function progress(TProgresRepository $tProgresRepo)
{
$progress = $tProgresRepo->findOneBy(['reff_kode' => 'META_DATA']);
$current = null;
$total = null;
if ($progress) {
$current = $progress->getDataMasuk();
$total = $progress->getJumlahData();
}
return new JsonResponse([
'show' => $progress ? true : false,
'current' => $current,
'total' => $total
]);
}
}

src/Controller/Backend/TMetaDataController.php line 88