src/Controller/Backend/TRawDataController.php line 60

Open in your IDE?
  1. <?php
  3. namespace App\Controller\Backend;
  5. use App\Controller\Base\BaseController;
  6. use App\Entity\TFileGrafik;
  7. use App\Entity\TGrafik;
  8. use App\Form\TGrafikType;
  9. use App\Entity\TGrafikDinamis;
  10. use App\Entity\THeader;
  11. use App\Entity\TProgres;
  12. use App\Entity\TRawData;
  13. use App\Entity\TVariabelGrafik;
  14. use App\Entity\TWeeklyEploreReport;
  15. use App\Repository\MKategoriDetailRepository;
  16. use App\Repository\MKategoriRepository;
  17. use App\Repository\TDivRepository;
  18. use App\Repository\TGrafikDinamisRepository;
  19. use App\Repository\TGrafikRepository;
  20. use App\Repository\TLogRepository;
  21. use App\Repository\TVariabelGrafikRepository;
  22. use App\Repository\TFileGrafikRepository;
  23. use Doctrine\Persistence\ManagerRegistry;
  24. use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
  25. use Symfony\Component\HttpFoundation\Response;
  26. use Symfony\Component\HttpFoundation\JsonResponse;
  27. use Symfony\Component\Routing\Annotation\Route;
  28. use Kematjaya\Breadcrumb\Lib\Builder as BreacrumbBuilder;
  29. use Symfony\Component\HttpFoundation\Request;
  30. use Symfony\Component\HttpKernel\Exception\NotFoundHttpException;
  31. use Kematjaya\ImportBundle\Manager\ImportManagerInterface;
  32. use Kematjaya\ImportBundle\DataSource\RemoteDataSource;
  33. use App\Transformer\PublikasiTransformer;
  34. use App\Transformer\VariabelTransformer;
  35. use DateTime;
  36. use Sonata\SeoBundle\Seo\SeoPageInterface;
  37. use PhpOffice\PhpSpreadsheet\IOFactory;
  38. use Knp\Component\Pager\PaginatorInterface;
  39. use App\Filter\TGrafikDinamisFilterType;
  40. use App\Kernel;
  41. use App\Repository\TDataGrafikRepository;
  42. use App\Repository\THeaderRepository;
  43. use App\Repository\TPostingLaporanRepository;
  44. use App\Repository\TProgresRepository;
  45. use App\Repository\TRawDataRepository;
  46. use Symfony\Component\Process\Process;
  47. use Symfony\Component\Process\Exception\ProcessFailedException;
  48. use PhpOffice\PhpSpreadsheet\Reader\IReadFilter;
  49. use Symfony\Component\HttpFoundation\BinaryFileResponse;
  50. use Symfony\Component\HttpFoundation\File\Exception\FileException;
  51. use Symfony\Component\HttpFoundation\ResponseHeaderBag;
  53. class TRawDataController extends BaseController
  54. {
  55.     private $pageName = 't_raw_data';
  56.     private $class = TRawData::class;
  57.     /**
  58.      * @Route("/t/raw/data", name="t_raw_data")
  59.      */
  60.     public function index(SeoPageInterface $seoPage, BreacrumbBuilder $builder, Request $request, PaginatorInterface $paginatorInterface): Response
  61.     {
  62.         $userRoles = $this->getUser()->getRoles();
  64.         $seoPage->addTitlePrefix("Dataset");
  66.         $builder->add('Dashboard ', "dashboard", array(), "&nbsp;<i class='fa fa-home'></i>");
  67.         $builder->add('Raw Data');
  68.         // $array = $this->getDataTableRawData(null, null, null, null);
  69.         // print_r($array); exit;
  70.         $filter = TGrafikDinamisFilterType::class;
  71.         $form = $this->get('form.factory')->create($filter, $this->getFilters($filter));
  72.         $queryBuilder = $this->getQueryBuilder($this->class);
  73.         // if ($userRoles[0] != 'ROLE_ADMINISTRATOR' && $userRoles[0] != 'ROLE_WALIDATA' && $userRoles[0] != 'ROLE_SUPER_USER'){
  74.         //     $queryBuilder = $queryBuilder->where('this.div = ' . $this->getUser()->getDiv()->getId());
  75.         // }
  76.         $queryBuilder = $this->buildFilter($request, $form, $queryBuilder)->addOrderBy("this.id", "DESC");
  78.         $this->setSessionLimit($request);
  79.         $maxPerPage = $request->getSession()->get("limit") ? $request->getSession()->get("limit") : $this->getLimit();
  80.         $pagination = $paginatorInterface->paginate($queryBuilder, $request->query->getInt('page', 1), $maxPerPage);
  81.         // $raw_data = $this->getDataTableRawData(null, null, null, null);
  82.         $header = $this->getDataHeader();
  83.         if (isset($header[0]['head1'])) {
  84.             $head1 = $header[0]['head1'];
  85.             $processedHeaders = [];
  86.             $keys = array_keys($head1);
  87.             $total = count($keys);
  88.             $i = 0;
  90.             while ($i < $total) {
  91.                 $key = $keys[$i];
  92.                 $label = $head1[$key];
  94.                 if ($label !== null) {
  95.                     $colspan = 1;
  96.                     $j = $i + 1;
  98.                     while ($j < $total && $head1[$keys[$j]] === null) {
  99.                         $colspan++;
  100.                         $j++;
  101.                     }
  103.                     $processedHeaders[] = [
  104.                         'label' => $label,
  105.                         'colspan' => $colspan,
  106.                     ];
  108.                     $i += $colspan;
  109.                 } else {
  110.                     $i++;
  111.                 }
  112.             }
  113.         } else {
  114.             $processedHeaders = null;
  115.         }
  117.         // print_r($raw_data); exit;
  118.         return $this->render('backend/t_raw_data/index.html.twig', [
  119.             'page_name' => $this->pageName,
  120.             'header' => $header,
  121.             'headers_grouped' => $processedHeaders,
  122.             'button_credential' => $this->buttonCredentials(),
  123.             'filter' => $form->createView(),
  124.             'pagination' => $pagination,
  125.         ]);
  126.     }
  128.     /**
  129.      * @Route("/t/raw/data_table", name="t_raw_data_table")
  130.      */
  131.     public function raw_data_table(SeoPageInterface $seoPage, BreacrumbBuilder $builder, Request $request, PaginatorInterface $paginatorInterface): Response
  132.     {
  133.         $userRoles = $this->getUser()->getRoles();
  135.         $seoPage->addTitlePrefix("Dataset");
  137.         $builder->add('Dashboard ', "dashboard", array(), "&nbsp;<i class='fa fa-home'></i>");
  138.         $builder->add('Raw Data');
  140.         $filter = TGrafikDinamisFilterType::class;
  141.         $form = $this->get('form.factory')->create($filter, $this->getFilters($filter));
  142.         $queryBuilder = $this->getQueryBuilder($this->class);
  143.         // if ($userRoles[0] != 'ROLE_ADMINISTRATOR' && $userRoles[0] != 'ROLE_WALIDATA' && $userRoles[0] != 'ROLE_SUPER_USER'){
  144.         //     $queryBuilder = $queryBuilder->where('this.div = ' . $this->getUser()->getDiv()->getId());
  145.         // }
  146.         $queryBuilder = $this->buildFilter($request, $form, $queryBuilder)->addOrderBy("this.id", "DESC");
  148.         $this->setSessionLimit($request);
  149.         $maxPerPage = $request->getSession()->get("limit") ? $request->getSession()->get("limit") : $this->getLimit();
  150.         $pagination = $paginatorInterface->paginate($queryBuilder, $request->query->getInt('page', 1), $maxPerPage);
  151.         $raw_data = $this->getDataTableRawData(null, null, null, null);
  152.         $head1 = $raw_data[0]['head1'];
  153.         $processedHeaders = [];
  154.         $keys = array_keys($head1);
  155.         $total = count($keys);
  156.         $i = 0;
  158.         while ($i < $total) {
  159.             $key = $keys[$i];
  160.             $label = $head1[$key];
  162.             if ($label !== null) {
  163.                 $colspan = 1;
  164.                 $j = $i + 1;
  166.                 while ($j < $total && $head1[$keys[$j]] === null) {
  167.                     $colspan++;
  168.                     $j++;
  169.                 }
  171.                 $processedHeaders[] = [
  172.                     'label' => $label,
  173.                     'colspan' => $colspan,
  174.                 ];
  176.                 $i += $colspan;
  177.             } else {
  178.                 $i++;
  179.             }
  180.         }
  181.         // print_r($raw_data); exit;
  182.         return $this->render('backend/t_raw_data/raw_data_table.html.twig', [
  183.             'page_name' => $this->pageName,
  184.             'raw_data' => $raw_data,
  185.             'headers_grouped' => $processedHeaders,
  186.             'button_credential' => $this->buttonCredentials(),
  187.             'filter' => $form->createView(),
  188.             'pagination' => $pagination,
  189.         ]);
  190.     }
  192.     /**
  193.      * @Route("/t_raw_data/{id}/delete_t_raw_data", name="t_raw_data_delete", methods={"GET"})
  194.      */
  195.     public function delete(?string $id = null, TGrafikDinamisRepository $tGrafikDinamisRepo, TLogRepository $tLogRepo)
  196.     {
  197.         $publikasi = $tGrafikDinamisRepo->find($id);
  198.         // dump($kategori); exit;
  199.         $entityManager = $this->getDoctrine()->getManager();
  200.         $entityManager->remove($publikasi);
  201.         $entityManager->flush();
  203.         $tLogRepo->setLog($this->getDoctrine()->getManager(), null, $publikasi->getDiv()->getId(), "Ubah Dataset " . $publikasi->getJudul(), $this->getUser()->getName());
  204.         $this->addFlash('notice', 'Dataset "' . $publikasi->getJudul() . '" berhasil dihapus');
  206.         return $this->redirectToRoute('publikasi');
  207.     }
  209.     /**
  210.      * @Route("/t_raw_data/form_file", name="t_raw_data_form_file", methods={"GET", "POST"})
  211.      */
  212.     // public function form_file(?string $id, Request $request, THeaderRepository $tHeaderRepo, TRawDataRepository $tRawDataRepo, TProgresRepository $tProgresRepo, ManagerRegistry $managerRegistry, Kernel $kernel)
  213.     // {
  214.     //     $em = $managerRegistry->getManager();
  215.     //     // $raw_data = $tRawDataRepo->find($id);
  217.     //     // if(!$raw_data){
  218.     //     //     throw new NotFoundHttpException();
  219.     //     // }
  221.     //     // if($file_id){
  222.     //     //     $file = $tFileGrafikRepo->find($file_id);
  223.     //     // } else {
  224.     //     //     $file = new TFileGrafik();
  225.     //     // }
  226.     //     // if(!$file){
  227.     //     //     throw new NotFoundHttpException();
  228.     //     // }
  230.     //     if ($request->isMethod('POST')) {
  232.     //         // $files = $tFileGrafikRepo->findBy(['grafik'=>$publikasi]);
  233.     //         // // $fileFound = $publikasi->getTFiles()->count();
  234.     //         // $fileFound = count($files);
  236.     //         // if (!$file_id) {
  238.     //         //     $periode_desk = "";
  239.     //         //     $periode_order = 0;
  243.     //         //     $file->setGrafik($publikasi);
  244.     //         //     $file->setDeskripsi($periode_desk);
  245.     //         //     // $file->setPeriodeAwal(\DateTime::createFromFormat('Y-m-d', $periode_awal));
  246.     //         //     // $file->setPeriodeAkhir(\DateTime::createFromFormat('Y-m-d', $periode_akhir));
  247.     //         //     $file->setPeriodeOrder($periode_order);
  248.     //         //     $file->setCreatedAt(new \DateTimeImmutable());
  250.     //         // } else {
  251.     //         //     $file->setUpdatedAt(new \DateTimeImmutable());
  252.     //         //     $datafile = $tDataGrafikRepo->findBy(['file'=>$file]);
  253.     //         //     // $tempFileData = $file->getTData();
  254.     //         //     $tempFileData = $datafile;
  255.     //         //     foreach ($tempFileData as $temp) {
  256.     //         //         $em->remove($temp);
  257.     //         //         $em->flush();
  258.     //         //     }
  259.     //         // }
  261.     //         $uploads_directory = $this->getParameter('uploads_directory');
  262.     //         $dataFile = $request->files->get('file');
  263.     //         $filename = md5(uniqid() . $dataFile->getClientOriginalName()) . '.' . $dataFile->guessExtension();
  267.     //         $dataFile->move(
  268.     //             $uploads_directory,
  269.     //             $filename
  270.     //         );
  272.     //         $spreadsheet = IOFactory::load($uploads_directory . '/' . $filename);
  273.     //         $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
  275.     //         // $header1 = array_values($sheetData[1]);
  278.     //         // if (!$fileFound) {
  279.     //         // foreach ($header as $head) {
  280.     //         //     $var = new TVariabelGrafik();
  281.     //         //     $var->setGrafik($publikasi);
  282.     //         //     $var->setNama(str_replace(" ", "_", strtolower($head)));
  283.     //         //     $var->setIsTampil(true);
  284.     //         //     $var->setCreatedAt(new \DateTimeImmutable());
  286.     //         //     $em->persist($var);
  287.     //         //     $em->flush();
  288.     //         // }
  289.     //         // }
  290.     //         $header1 = $sheetData[1];
  291.     //         $header2 = $sheetData[2];
  293.     //         unset($sheetData[1]);
  294.     //         unset($sheetData[2]);
  295.     //         // print_r(strtolower(json_encode($header1)))."<br>";
  296.     //         // print_r(strtolower(json_encode($header2)));exit;
  298.     //         //cek header
  299.     //         $connection = $this->getDoctrine()->getConnection();
  300.     //         $sql = "SELECT * FROM t_header WHERE LOWER(head_1::text) = LOWER(:head_1) AND LOWER(head_2::text) = LOWER(:head_2)";
  301.     //         $stmt = $connection->prepare($sql);
  302.     //         $result = $stmt->executeQuery(['head_1' => strtolower(json_encode($header1)), 'head_2' => strtolower(json_encode($header2)),]);
  303.     //         $header = $result->fetchAssociative();
  304.     //         // print_r($header); exit;
  305.     //         if ($header) {
  306.     //             $header = $tHeaderRepo->find(['id' => $header['id']]);
  307.     //             $header->setHead1($header1);
  308.     //             $header->setHead2($header2);
  309.     //             $header->setUpdatedAt(new \DateTimeImmutable());
  310.     //         } else {
  311.     //             // unlink($filename);
  312.     //             // $this->addFlash('error', 'Header Tidak Sesuai');
  313.     //             // return $this->redirectToRoute('t_raw_data');
  314.     //             //belum dipake dlu karena pasti 1 header
  315.     //             $header = new THeader();
  316.     //             $header->setHead1($header1);
  317.     //             $header->setHead2($header2);
  318.     //             $header->setCreatedAt(new \DateTimeImmutable());
  319.     //         }
  320.     //         $header->setFile($filename);
  321.     //         $em->persist($header);
  322.     //         $em->flush();
  323.     //         // exit;
  324.     //         $isi = $sheetData;
  325.     //         // print_r(count($isi)); exit;
  326.     //         $batchSize = 50;
  327.     //         $firstBatch = array_slice($sheetData, 0, $batchSize);
  328.     //         $remainingBatch = array_slice($sheetData, $batchSize);
  329.     //         $no = 1;
  330.     //         // foreach ($isi as $key => $value) {
  331.     //         //     //cek raw_data
  332.     //         //     $connection = $this->getDoctrine()->getConnection();
  333.     //         //     $sql = "SELECT * FROM t_raw_data WHERE isi::text = :isi";
  334.     //         //     $stmt = $connection->prepare($sql);
  335.     //         //     $result = $stmt->executeQuery(['isi' => json_encode($value)]);
  336.     //         //     $raw_data = $result->fetchAssociative();
  337.     //         //     if ($raw_data) {
  338.     //         //         $raw_data = $tRawDataRepo->find(['id' => $raw_data['id']]);
  339.     //         //         $raw_data->setIsi($value);
  340.     //         //         $raw_data->setUpdatedAt(new \DateTimeImmutable());
  341.     //         //     } else {
  342.     //         //         $raw_data = new TRawData();
  343.     //         //         $raw_data->setHeaderId($header->getId());
  344.     //         //         $raw_data->setIsi($value);
  345.     //         //         $raw_data->setCreatedAt(new \DateTimeImmutable());
  346.     //         //     }
  348.     //         //     // $raw_data->setFile($filename);
  349.     //         //     $em->persist($raw_data);
  350.     //         //     $em->flush();
  351.     //         // }
  352.     //         $t_progres = new TProgres();
  353.     //         $t_progres->setCreatedAt(new \DateTimeImmutable());
  354.     //         $t_progres->setReffKode('RAW_DATA');
  355.     //         $t_progres->setFile($filename);
  356.     //         $t_progres->setJumlahData(count($isi));
  357.     //         $em->persist($t_progres);
  358.     //         $em->flush();
  360.     //         $data_masuk = 1;
  361.     //         foreach ($firstBatch as $value) {
  362.     //             //cek raw_data
  363.     //             $connection = $this->getDoctrine()->getConnection();
  364.     //             $sql = "SELECT * FROM t_raw_data WHERE isi::text = :isi";
  365.     //             $stmt = $connection->prepare($sql);
  366.     //             $result = $stmt->executeQuery(['isi' => json_encode($value)]);
  367.     //             $raw_data = $result->fetchAssociative();
  368.     //             if ($raw_data) {
  369.     //                 $raw_data = $tRawDataRepo->find(['id' => $raw_data['id']]);
  370.     //                 $raw_data->setIsi($value);
  371.     //                 $raw_data->setUpdatedAt(new \DateTimeImmutable());
  372.     //             } else {
  373.     //                 $raw_data = new TRawData();
  374.     //                 $raw_data->setHeaderId($header->getId());
  375.     //                 $raw_data->setIsi($value);
  376.     //                 $raw_data->setCreatedAt(new \DateTimeImmutable());
  377.     //             }
  379.     //             //progress upload
  381.     //             $t_progres = $tProgresRepo->findOneBy(['reff_kode' => 'RAW_DATA']);
  382.     //             $t_progres->setDataMasuk($data_masuk);
  383.     //             $em->persist($t_progres);
  384.     //             $em->flush();
  386.     //             $tahun_anggaran = $request->request->get('tahun_anggaran');
  387.     //             $raw_data->setTahunAnggaran($tahun_anggaran);
  388.     //             $em->persist($raw_data);
  389.     //             $em->flush();
  390.     //             $data_masuk +=1;
  391.     //         }
  393.     //         $tempPath = $uploads_directory . '/batch_pending.json';
  394.     //         file_put_contents($tempPath, json_encode([
  395.     //             'header_id' => $header->getId(),
  396.     //             'data' => $remainingBatch
  397.     //         ]));
  399.     //         $baseCmd = sprintf('%s/bin/console app:rawdata "%s" "%s"', $this->getParameter('kernel.project_dir'), $tempPath,$tahun_anggaran);
  401.     //         if (strncasecmp(PHP_OS, 'WIN', 3) === 0) {
  402.     //             $cmd = 'start /B php ' . $baseCmd;
  403.     //         } else {
  404.     //             $cmd = 'php7.4 ' . $baseCmd . ' > /dev/null 2>&1 &';
  405.     //         }
  407.     //         $process = Process::fromShellCommandline($cmd);
  408.     //         $process->disableOutput();
  409.     //         $process->start();
  412.     //         // if (!$process->isStarted()) {
  413.     //         //     $managerRegistry->getManager()->remove($entity);
  414.     //         //     $managerRegistry->getManager()->flush();
  415.     //         // }
  418.     //         // $vars = $tVariabelGrafikRepo->findBy(['grafik'=>$publikasi->getId()], ['id'=>'asc']);
  420.     //         // $lastData = $tDataGrafikRepo->findOneBy(['grafik'=>$publikasi->getId()], ['id'=>'desc']);
  421.     //         // $rowNum = 1;
  422.     //         // if ($lastData) {
  423.     //         //     $rowNum += $lastData->getRowId();
  424.     //         // }
  426.     //         // foreach ($sheetData as $value) {
  427.     //         //     $arrData = array_values($value);
  428.     //         //     $isEmpty = trim(implode('', $arrData));
  429.     //         //     if ($isEmpty != '') {
  430.     //         //         foreach ($arrData as $key => $val) {
  431.     //         //             $data = new TDataGrafik();
  432.     //         //             $data->setGrafik($publikasi);
  433.     //         //             $data->setVariabel($vars[$key]);
  434.     //         //             $data->setIsi($val);
  435.     //         //             $data->setFile($file);
  436.     //         //             $data->setRowId($rowNum);
  437.     //         //             $data->setCreatedAt(new \DateTimeImmutable());
  439.     //         //             $em->persist($data);
  440.     //         //             $em->flush();
  441.     //         //         }
  442.     //         //     }
  444.     //         //     $rowNum++;
  445.     //         // }
  448.     //         $this->addFlash('notice', 'Upload file berhasil');
  449.     //         return $this->redirectToRoute('t_raw_data');
  450.     //     }
  452.     //     return $this->render('/backend/t_raw_data/form_file.html.twig', [
  453.     //         // 't_raw_data' => $raw_data,
  454.     //         // 'file' => $file,
  455.     //         // 'bulan' => $this->getBulan(),
  456.     //         // 'periode' => $this->getArrayPeriode()
  457.     //     ]);
  458.     // }
  460.     public function form_file(?string $id, Request $request, THeaderRepository $tHeaderRepo, TRawDataRepository $tRawDataRepo, TProgresRepository $tProgresRepo, TPostingLaporanRepository $tPostingLaporanRepo, ManagerRegistry $managerRegistry, Kernel $kernel)
  461.     {
  462.         $em = $managerRegistry->getManager();
  463.         // $raw_data = $tRawDataRepo->find($id);
  465.         // if(!$raw_data){
  466.         //     throw new NotFoundHttpException();
  467.         // }
  469.         // if($file_id){
  470.         //     $file = $tFileGrafikRepo->find($file_id);
  471.         // } else {
  472.         //     $file = new TFileGrafik();
  473.         // }
  474.         // if(!$file){
  475.         //     throw new NotFoundHttpException();
  476.         // }
  478.         if ($request->isMethod('POST')) {
  482.             $uploads_directory = $this->getParameter('uploads_directory');
  483.             // $tahun_anggaran = $request->get('tahun_anggaran');
  484.             $periode_upload = $request->get('periode_upload');
  485.             // print_r($tahun_anggaran); exit;
  486.             // Upload file1'
  489.             $dataFile = $request->files->get('file');
  491.             // --- 1. Cek ekstensi ---
  492.             $allowedExtensions = ['xls', 'xlsx'];
  493.             $extension = strtolower($dataFile->getClientOriginalExtension());
  494.             if (!in_array($extension, $allowedExtensions)) {
  495.                 // throw new \Exception('File harus berupa Excel (.xls atau .xlsx).');
  496.                 $this->addFlash('error', 'Upload Gagal!, File harus berupa Excel (.xls atau .xlsx).');
  497.                 return $this->redirectToRoute('t_raw_data');
  498.             }
  500.             // --- 2. Cek MIME type ---
  501.             $allowedMimeTypes = [
  502.                 'application/vnd.ms-excel',
  503.                 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
  504.                 'application/octet-stream' // beberapa browser kadang kirim ini
  505.             ];
  506.             $mimeType = $dataFile->getMimeType();
  507.             if (!in_array($mimeType, $allowedMimeTypes)) {
  508.                 // throw new \Exception('Tipe file tidak valid, bukan file Excel.');
  509.                 $this->addFlash('error', 'Upload Gagal!, Tipe file tidak valid, bukan file Excel.');
  510.                 return $this->redirectToRoute('t_raw_data');
  511.             }
  513.             // --- 3. Cek isi file (signature sederhana) ---
  514.            
  515.             // Buka beberapa byte pertama untuk pastikan tidak mengandung kode PHP
  516.            
  517.             $firstBytes = file_get_contents($dataFile->getPathname(), false, null, 0, 2048);
  518.             $dangerousPatterns = [
  519.                 '/<\?php/i',              // PHP
  520.                 '/<script\b/i',           // JS/HTML
  521.                 '/#!\/usr\/bin\/(python|bash|sh|perl)/i', // shebang
  522.                 '/eval\s*\(/i',
  523.                 '/base64_decode/i',
  524.                 '/shell_exec/i',
  525.                 '/exec\s*\(/i',
  526.                 '/os\.system/i',
  527.                 '/import\s+[a-z0-9_]+/i',
  528.                 '/function\s+[a-z0-9_]+\s*\(/i'
  529.             ];
  530.             foreach ($dangerousPatterns as $p) {
  531.                 if (preg_match($p, $firstBytes)) {
  532.                     // hapus tmp file untuk kebersihan, lalu tolak
  533.                     @unlink($dataFile->getPathname());
  534.                     $this->addFlash('error', 'Upload Gagal!, File berisi kode PHP dan ditolak.');
  535.                     return $this->redirectToRoute('t_raw_data');
  536.                 }
  537.             }
  539.             // --- 4. Simpan dengan nama aman ---
  540.             $safeFilename = md5(uniqid() . $dataFile->getClientOriginalName());
  541.             $filename = $safeFilename . '.' . $extension;
  543.             try {
  544.                 $dataFile->move($uploads_directory, $filename);
  545.             } catch (FileException $e) {
  546.                 // throw new \Exception('Gagal menyimpan file: ' . $e->getMessage());
  547.                 $this->addFlash('error', 'Gagal menyimpan file: ' . $e->getMessage());
  548.                 return $this->redirectToRoute('t_raw_data');
  549.             }
  550.             // $filename = md5(uniqid() . $dataFile->getClientOriginalName()) . '.' . $dataFile->guessExtension();
  551.             // $dataFile->move($uploads_directory, $filename);
  553.             // Upload file2 (monitoring)
  554.             // $dataFileMonitoringFull = $request->files->get('file_monitoring_full');
  555.             // $filename_monitoring_full = md5(uniqid() . $dataFileMonitoringFull->getClientOriginalName()) . '.' . $dataFileMonitoringFull->guessExtension();
  556.             // $dataFileMonitoringFull->move($uploads_directory, $filename_monitoring_full);
  558.             // Convert keduanya jadi array
  559.             // $spreadsheet1 = IOFactory::load($uploads_directory . '/' . $filename);
  560.             // $sheetData1 = $spreadsheet1->getActiveSheet()->toArray(null, true, true, true);
  562.             // $spreadsheet2 = IOFactory::load($uploads_directory . '/' . $filename_monitoring_full);
  563.             // $sheetData2 = $spreadsheet2->getActiveSheet()->toArray(null, true, true, true);
  565.             //cek header
  567.             $reader = IOFactory::createReader('Xlsx'); // atau 'Xls'
  569.             // Anonymous class implement IReadFilter
  570.             $reader->setReadFilter(new class implements IReadFilter {
  571.                 public function readCell($column, $row, $worksheetName = '')
  572.                 {
  573.                     // hanya ambil baris 1–2
  574.                     return $row <= 2;
  575.                 }
  576.             });
  578.             $spreadsheet = $reader->load($uploads_directory . '/' . $filename);
  579.             $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
  580.             // print_r($data);
  581.             // exit;
  582.             $header1 = $sheetData[1];
  583.             $header2 = $sheetData[2];
  585.             unset($sheetData[1]);
  586.             unset($sheetData[2]);
  588.             //cek header
  589.             $connection = $this->getDoctrine()->getConnection();
  590.             $sql = "SELECT * FROM t_header WHERE LOWER(head_1::text) = LOWER(:head_1) AND LOWER(head_2::text) = LOWER(:head_2)";
  591.             $stmt = $connection->prepare($sql);
  592.             $result = $stmt->executeQuery(['head_1' => strtolower(json_encode($header1)), 'head_2' => strtolower(json_encode($header2)),]);
  593.             $header = $result->fetchAssociative();
  594.             // print_r($header); exit;
  595.             if ($header) {
  596.                 $header = $tHeaderRepo->find(['id' => $header['id']]);
  597.                 $header->setHead1($header1);
  598.                 $header->setHead2($header2);
  599.                 $header->setUpdatedAt(new \DateTimeImmutable());
  600.                 $header->setPeriodeUpload(new \DateTime($periode_upload));
  601.             } else {
  602.                 // unlink($filename);
  603.                 $this->addFlash('error', 'Header Tidak Sesuai');
  604.                 return $this->redirectToRoute('t_raw_data');
  605.                 //belum dipake dlu karena pasti 1 header
  606.                 // $header = new THeader();
  607.                 // $header->setHead1($header1);
  608.                 // $header->setHead2($header2);
  609.                 // $header->setCreatedAt(new \DateTimeImmutable());
  610.             }
  611.             $header->setFile($filename);
  612.             $em->persist($header);
  613.             $em->flush();
  615.             // print_r('aa'); exit;
  616.             //initializing
  617.             $t_progres = new TProgres();
  618.             $t_progres->setCreatedAt(new \DateTimeImmutable());
  619.             $t_progres->setReffKode('INISIALISASI_DATA');
  620.             $em->persist($t_progres);
  621.             $em->flush();
  623.             // Jalankan background job
  624.             $baseCmd = sprintf(
  625.                 '%s/bin/console app:rawdata "%s" "%s"',
  626.                 $this->getParameter('kernel.project_dir'),
  627.                 $filename,
  628.                 $periode_upload
  629.             );
  630.             if (strncasecmp(PHP_OS, 'WIN', 3) === 0) {
  631.                 $cmd = 'start /B php ' . $baseCmd;
  632.             } else {
  633.                 $cmd = 'php7.4 ' . $baseCmd . ' > /dev/null 2>&1 &';
  634.             }
  636.             $process = Process::fromShellCommandline($cmd);
  637.             $process->disableOutput();
  638.             $process->start();
  640.             // $baseCmd = sprintf(
  641.             //     '%s/bin/console app:rawdata "%s" "%s"',
  642.             //     $this->getParameter('kernel.project_dir'),
  643.             //     $filename,
  644.             //     $tahun_anggaran
  645.             // );
  647.             // $php = PHP_BINARY;
  649.             // if (strncasecmp(PHP_OS, 'WIN', 3) === 0) {
  650.             //     // Windows â†’ jalankan lewat cmd.exe
  651.             //     $cmd = 'cmd /c start /B ' . $php . ' ' . $baseCmd;
  652.             // } else {
  653.             //     // Linux/Unix â†’ async pakai &
  654.             //     $cmd = $php . ' ' . $baseCmd . ' > /dev/null 2>&1 &';
  655.             // }
  657.             // $process = Process::fromShellCommandline($cmd);
  658.             // $process->disableOutput();
  659.             // $process->start();
  661.             // $php = PHP_BINARY;
  662.             // $console = $this->getParameter('kernel.project_dir') . '/bin/console';
  663.             // // print_r($console); exit;
  664.             // try {
  665.             //     // --- Utama: Symfony Process (aman, cross-platform) ---
  666.             //     $process = new Process([
  667.             //         $php,
  668.             //         $console,
  669.             //         'app:rawdata',
  670.             //         $filename,
  671.             //         $tahun_anggaran
  672.             //     ]);
  673.             //     $process->setWorkingDirectory($this->getParameter('kernel.project_dir'));
  674.             //     $process->disableOutput();
  675.             //     $process->start();
  676.             //     print_r('aa'); exit;
  677.             // } catch (\Throwable $e) {
  678.             //     // --- Fallback: exec() (string command) ---
  679.             //     if (strncasecmp(PHP_OS, 'WIN', 3) === 0) {
  680.             //         print_r('bb'); exit;
  681.             //         // Windows background
  682.             //         $cmd = sprintf(
  683.             //             'start /B %s %s app:rawdata %s %s',
  684.             //             $php,
  685.             //             $console,
  686.             //             escapeshellarg($filename),
  687.             //             escapeshellarg($tahun_anggaran)
  688.             //         );
  689.             //     } else {
  690.             //         // Linux/Unix background
  691.             //         $cmd = sprintf(
  692.             //             '%s %s app:rawdata %s %s > /dev/null 2>&1 &',
  693.             //             $php,
  694.             //             $console,
  695.             //             escapeshellarg($filename),
  696.             //             escapeshellarg($tahun_anggaran)
  697.             //         );
  698.             //     }
  700.             //     exec($cmd);
  701.             // }
  704.             $this->addFlash('notice', 'Upload file berhasil');
  705.             return $this->redirectToRoute('t_raw_data');
  706.         }
  707.         $lastUpdate = $tPostingLaporanRepo->findOneBy([], ['tanggal' => 'DESC']);
  709.         $lastUpdate = ($lastUpdate) ? $lastUpdate->getTanggal()->format("Y-m-d") : null;
  711.         return $this->render('/backend/t_raw_data/form_file.html.twig', [
  712.             // 't_raw_data' => $raw_data,
  713.             'lastUpdate' => $lastUpdate,
  714.             // 'bulan' => $this->getBulan(),
  715.             // 'periode' => $this->getArrayPeriode()
  716.         ]);
  717.     }
  719.     /**
  720.      * @Route("/t_raw_data/{id}/delete_t_raw_data_file", name="t_raw_data_delete_file", methods={"GET"})
  721.      */
  722.     public function delete_file(
  723.         ?string $id = null,
  724.         TFileGrafikRepository $tFileGrafikRepo,
  725.         ManagerRegistry $managerRegistry,
  726.         TGrafikDinamisRepository $tGrafikDinamisRepo
  727.     ) {
  728.         $em = $managerRegistry->getManager();
  729.         $file = $tFileGrafikRepo->find($id);
  730.         $pubId = $file->getGrafik()->getId();
  732.         // $countFiles = $file->getPub()->getTFiles()->count();
  733.         $files = $tFileGrafikRepo->findBy(['grafik' => $pubId]);
  734.         $countFiles = count($files);
  735.         if ($countFiles == 1) {
  736.             $grafik = $file->getGrafik()->getTGrafik();
  737.             if ($grafik) {
  738.                 $em->remove($grafik);
  739.             }
  740.             foreach ($file->getPub()->getTVariabels() as $var) {
  741.                 $em->remove($var);
  742.             }
  743.         }
  745.         $em->remove($file);
  746.         $em->flush();
  748.         $this->addFlash('notice', 'File berhasil dihapus');
  750.         return $this->redirectToRoute('publikasi_detail', ['id' => $pubId]);
  751.     }
  753.     /**
  754.      * @Route("/t_raw_data/progres", name="t_raw_data_progres", methods={"POST"})
  755.      */
  756.     public function progress(TProgresRepository $tProgresRepo)
  757.     {
  758.         $progress = $tProgresRepo->findOneBy(['reff_kode' => 'RAW_DATA']);
  759.         $current = null;
  760.         $total = null;
  762.         if ($progress) {
  763.             $current = $progress->getDataMasuk();
  764.             $total = $progress->getJumlahData();
  765.         }
  767.         return new JsonResponse([
  768.             'show' => $progress ? true : false,
  769.             'current' => $current,
  770.             'total' => $total
  771.         ]);
  772.     }
  774.     /**
  775.      * @Route("/publikasi/verifAll", name="publikasi_verif_all", methods={"GET"})
  776.      */
  777.     public function verifall(TPublikasiRepository $tPublikasiRepo, MStatusRepository $mStatusRepo, ManagerRegistry $managerRegistry)
  778.     {
  779.         $em = $managerRegistry->getManager();
  780.         $user = $this->getUser();
  781.         if ($user->getRoles()[0] == 'ROLE_OPERATOR') {
  782.             $tPublikasi = $tPublikasiRepo->findBy(['status' => 13, 'div' => $user->getDiv()->getId()]);
  783.             foreach ($tPublikasi as $pub) {
  784.                 $pub->setStatus($mStatusRepo->find(1));
  785.                 $em->persist($pub);
  786.             }
  788.             $this->addFlash('notice', 'Dataset berhasil diajukan publik');
  789.         } else {
  790.             $tPublikasi = $tPublikasiRepo->findBy(['status' => 1]);
  791.             foreach ($tPublikasi as $pub) {
  792.                 $pub->setStatus($mStatusRepo->find(3));
  793.                 $em->persist($pub);
  794.             }
  796.             $this->addFlash('notice', 'Dataset berhasil diverifikasi');
  797.         }
  799.         $em->flush();
  801.         return $this->redirectToRoute('publikasi');
  802.     }
  804.     /**
  805.      * @Route("/t_raw_data_table", name="t_raw_data_table", methods={"GET", "POST"})
  806.      */
  807.     public function dataset_table(?string $id, Request $request)
  808.     {
  809.         // Ambil data dari sumber
  810.         $array = $this->getDataTableRawData(null, null, null, null);
  811.         $items = $array['isi'] ?? [];
  813.         // Ambil parameter datatables
  814.         $draw = (int) $request->get('draw', 1);
  815.         $start = (int) $request->get('start', 0);
  816.         $length = (int) $request->get('length', 10);
  818.         // Ambil parameter pencarian per kolom
  819.         $columns = $request->get("columns", []);
  820.         $columnSearch = [];
  821.         foreach ($columns as $col) {
  822.             $columnName = $col["data"] ?? '';
  823.             $searchValue = $col["search"]["value"] ?? '';
  824.             if ($columnName !== '') {
  825.                 $columnSearch[$columnName] = $searchValue;
  826.             }
  827.         }
  829.         // Filter data berdasarkan input pencarian
  830.         $filteredData = array_filter($items, function ($row) use ($columnSearch) {
  831.             foreach ($columnSearch as $columnName => $search) {
  832.                 if ($search !== '') {
  833.                     if (!isset($row[$columnName]) || stripos($row[$columnName], $search) === false) {
  834.                         return false;
  835.                     }
  836.                 }
  837.             }
  838.             return true;
  839.         });
  841.         // Total & filtered
  842.         $recordsTotal = count($items);
  843.         $recordsFiltered = count($filteredData);
  845.         // Pagination
  846.         $pagedData = array_slice($filteredData, $start, $length);
  848.         // Response JSON untuk DataTables
  850.         return new JsonResponse([
  851.             'draw' => $draw,
  852.             'recordsTotal' => $recordsTotal,
  853.             'recordsFiltered' => $recordsFiltered,
  854.             'data' => array_values($pagedData),
  855.         ]);
  856.     }
  858.     /**
  859.      * @Route("/t_raw_data/inisialisasi", name="t_raw_data_inisialisasi", methods={"POST"})
  860.      */
  861.     public function inisialisasi(TProgresRepository $tProgresRepo)
  862.     {
  863.         $progress = $tProgresRepo->findOneBy(['reff_kode' => 'INISIALISASI_DATA']);
  866.         return new JsonResponse([
  867.             'show' => $progress ? true : false
  868.         ]);
  869.     }
  871.     /**
  872.      * @Route("/t_raw_data/download_template", name="t_raw_data_download_template", methods={"GET", "POST"})
  873.      */
  874.     public function download_template()
  875.     {
  876.         $filePath = $this->getParameter('kernel.project_dir') . '/public/template_upload.xlsx';
  878.         if (!file_exists($filePath)) {
  879.             throw $this->createNotFoundException('File tidak ditemukan.');
  880.         }
  882.         $response = new BinaryFileResponse($filePath);
  883.         $response->setContentDisposition(
  884.             ResponseHeaderBag::DISPOSITION_ATTACHMENT,
  885.             'template_upload.xlsx'
  886.         );
  888.         return $response;
  889.     }
  890. }