src/OceanExpertBundle/Controller/DocumentController.php line 1344

Open in your IDE?
  1. <?php
  2. namespace OceanExpertBundle\Controller;
  3. use DateTime;
  4. use OceanExpertBundle\Entity\AgendaitemDocuments;
  5. use OceanExpertBundle\Entity\DoclistDocuments;
  6. use OceanExpertBundle\Entity\DoclistGroups;
  7. use OceanExpertBundle\Entity\Doclists;
  8. use OceanExpertBundle\Entity\DocumentFiles;
  9. use OceanExpertBundle\Entity\Documents;
  10. use OceanExpertBundle\Entity\EventBackgrounddocs;
  11. use OceanExpertBundle\Entity\EventOtherdocs;
  12. use OceanExpertBundle\Entity\EventPresentations;
  13. use OceanExpertBundle\Entity\EventReports;
  14. use OceanExpertBundle\Repository\DoclistsRepository;
  15. use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
  16. use Symfony\Component\HttpFoundation\JsonResponse;
  17. use Symfony\Component\HttpFoundation\Request;
  18. use Symfony\Component\HttpFoundation\Response;
  19. use Symfony\Component\HttpFoundation\BinaryFileResponse;
  20. use Symfony\Component\HttpFoundation\ResponseHeaderBag;
  21. /**
  22.  * Class DocumentController
  23.  * @package OceanExpertBundle\Controller
  24.  */
  25. class DocumentController extends AbstractController
  26. {
  27.     private DoclistsRepository $doclistsRepository;
  28.     public function __construct(DoclistsRepository $doclistsRepository)
  29.     {
  30.         $this->doclistsRepository $doclistsRepository;
  31.     }
  32.     /**
  33.      * @param $name
  34.      *
  35.      * @return Response
  36.      */
  37.     public function indexAction($name): Response
  38.     {
  39.         return $this->render('', array('name' => $name));
  40.     }
  41.     /**
  42.      * @param Request $request
  43.      * @return Response
  44.      */
  45.     public function searchDocumentAction(Request $request)
  46.     {
  47.         $searchTerm $request->request->get('searchterm');
  48.         $request->request->get('doctype');
  49.         $em $this->getDoctrine()->getManager();
  50.         $result $em->getRepository('OceanExpertBundle:Documents')->createQueryBuilder('d')
  51.             ->select('d.idDoc,d.title,d.authorText,dt.doctypename')
  52.             ->leftJoin('OceanExpertBundle:Doctypes''dt''WITH''d.idDoctype = dt.idDoctype')
  53.             ->where('d.title LIKE :searchterm')
  54.             ->orWhere('d.summary LIKE :searchterm')
  55.             ->orWhere('d.authorText LIKE :searchterm')
  56.             ->orWhere('d.docCode LIKE :searchterm')
  57.             ->andWhere('d.approved = 1')
  58.             ->setParameter('searchterm''%' $searchTerm '%')
  59.             ->getQuery()
  60.             ->getResult();
  61.         return new JsonResponse(array('data' => $result));
  62.     }
  63.     /**
  64.      * @param Request $request
  65.      * @param $idDoc
  66.      * @return Response
  67.      */
  68.     public function getDocumentByIdAction(Request $request$idDoc)
  69.     {
  70.         if ($idDoc == 0) {
  71.             $idDoc $request->query->get('idDoc');
  72.         }
  73.         $em $this->getDoctrine()->getManager();
  74.         $result $em->getRepository('OceanExpertBundle:Documents')->createQueryBuilder('d')
  75.             ->select('d.docCode, d.idDoc, d.title, d.summary, d.authorText, d.publishedOn, d.notes, d.keywords, d.createdAt, d.updatedAt,d.idCreator,d.lastEditBy,dt.doctypename')
  76.             ->leftJoin('OceanExpertBundle:Doctypes''dt''WITH''d.idDoctype = dt.idDoctype')
  77.             ->where('d.idDoc = :idDoc')
  78.             ->setParameter('idDoc'$idDoc)
  79.             ->getQuery()
  80.             ->getResult()[0];
  81.         if ($result['idCreator'] != '') {
  82.             $indiv $this->getDoctrine()->getRepository('OceanExpertBundle:Indiv')->findOneByIdInd($result['idCreator']);
  83.             if ($indiv) {
  84.                 $result['createdBy'] = $indiv->getFname() . ' ' $indiv->getSname();
  85.             } else {
  86.                 $result['createdBy'] = '';
  87.             }
  88.         }
  89.         if ($result['lastEditBy'] != '') {
  90.             $indiv $this->getDoctrine()->getRepository('OceanExpertBundle:Indiv')->findOneByIdInd($result['lastEditBy']);
  91.             if ($indiv) {
  92.                 $result['updatedBy'] = $indiv->getFname() . ' ' $indiv->getSname();
  93.             } else {
  94.                 $result['updatedBy'] = '';
  95.             }
  96.         }
  97.         $groups $em->getRepository('OceanExpertBundle:DocumentGroups')->createQueryBuilder('d')
  98.             ->select('d.idGroup,g.groupname')
  99.             ->leftJoin('OceanExpertBundle:Groups''g''WITH''d.idGroup = g.idGroup')
  100.             ->where('d.idDoc = :idDoc')
  101.             ->setParameter('idDoc'$idDoc)
  102.             ->getQuery()
  103.             ->getResult();
  104.         $docGroupList $em->getRepository('OceanExpertBundle:DoclistDocuments')->createQueryBuilder('d')
  105.             ->select('dl.idDoclist, dl.title')
  106.             ->leftJoin('OceanExpertBundle:Doclists''dl''WITH''dl.idDoclist = d.idDoclist')
  107.             ->where('d.idDoc = :idDoc')
  108.             ->setParameter('idDoc'$idDoc)
  109.             ->getQuery()
  110.             ->getResult();
  111.         $docFiles $em->getRepository('OceanExpertBundle:DocumentFiles')->createQueryBuilder('d')
  112.             ->select('d.idFileformat, d.version, l.languagename, d.idFile, f.filename, f.url, f.size,f.content,f.path')
  113.             ->leftJoin('OceanExpertBundle:Files''f''WITH''f.idFile = d.idFile')
  114.             ->leftJoin('OceanExpertBundle:Languages''l''WITH''f.idLanguage = l.idLanguage')
  115.             ->where('d.idDoc = :idDoc')
  116.             ->setParameter('idDoc'$idDoc)
  117.             ->getQuery()
  118.             ->getResult();
  119.         $result['documentGroup'] = $groups;
  120.         $result['docGroupList'] = $docGroupList;
  121.         $result['docFiles'] = $docFiles;
  122.         return $this->render(
  123.             'Documents/viewDocumentBlock.html.twig',
  124.             array(
  125.                 'data' => $result
  126.             )
  127.         );
  128.     }
  129.     /**
  130.      *
  131.      */
  132.     public function deleteEventDocumentAction(Request $request)
  133.     {
  134.         $authorised true;
  135.         $docType $request->request->get('doctype');
  136.         $idEvent $request->request->get('idEvent');
  137.         $idDoc $request->request->get('idDoc');
  138.         if (null != ($request->request->get('idAgenda'))) {
  139.             $idAgenda $request->request->get('idAgenda');
  140.         } else {
  141.             $idAgenda '';
  142.         }
  143.         $id 0;
  144.         if ($authorised) {
  145.             $em $this->getDoctrine()->getManager();
  146.             switch ($docType) {
  147.                 case 'report':
  148.                     $report $em->getRepository('OceanExpertBundle:EventReports')
  149.                         ->findOneBy(
  150.                             array(
  151.                                 'idEvent' => $idEvent,
  152.                                 'idDoc' => $idDoc
  153.                             )
  154.                         );
  155.                     if ($report) {
  156.                         $em->remove($report);
  157.                         $em->flush();
  158.                         $id 1;
  159.                     }
  160.                     break;
  161.                 case 'presentation':
  162.                     $presentation $em->getRepository('OceanExpertBundle:EventPresentations')
  163.                         ->findOneBy(
  164.                             array(
  165.                                 'idEvent' => $idEvent,
  166.                                 'idDoc' => $idDoc
  167.                             )
  168.                         );
  169.                     if ($presentation) {
  170.                         $em->remove($presentation);
  171.                         $em->flush();
  172.                         $id 2;
  173.                     }
  174.                     break;
  175.                 case 'backgrounddocs':
  176.                     $backgroundocs $em->getRepository('OceanExpertBundle:EventBackgrounddocs')
  177.                         ->findOneBy(
  178.                             array(
  179.                                 'idEvent' => $idEvent,
  180.                                 'idDoc' => $idDoc
  181.                             )
  182.                         );
  183.                     if ($backgroundocs) {
  184.                         $em->remove($backgroundocs);
  185.                         $em->flush();
  186.                         $id 3;
  187.                     }
  188.                     break;
  189.                 case 'agendaitems':
  190.                     if ($idAgenda != '') {
  191.                         $agendaDetails $em->getRepository('OceanExpertBundle:EventAgendaitems')
  192.                             ->findOneBy(
  193.                                 array(
  194.                                     'idEvent' => $idEvent,
  195.                                     'id' => $idAgenda
  196.                                 )
  197.                             );
  198.                         if ($agendaDetails) {
  199.                             $agendaitem $agendaDetails->getIdAgendaitem();
  200.                             $agendaDocs $em->getRepository('OceanExpertBundle:AgendaitemDocuments')
  201.                                 ->findOneBy(
  202.                                     array(
  203.                                         'idEvent' => $idEvent,
  204.                                         'idDoc' => $idDoc,
  205.                                         'idAgendaitem' => $agendaitem
  206.                                     )
  207.                                 );
  208.                             if ($agendaDocs) {
  209.                                 $em->remove($agendaDocs);
  210.                                 $em->flush();
  211.                                 $id 5;
  212.                             }
  213.                         }
  214.                     }
  215.                     break;
  216.                 case 'otherdocs':
  217.                     $otherdocs $em->getRepository('OceanExpertBundle:EventOtherdocs')
  218.                         ->findOneBy(
  219.                             array(
  220.                                 'idEvent' => $idEvent,
  221.                                 'idDoc' => $idDoc
  222.                             )
  223.                         );
  224.                     if ($otherdocs) {
  225.                         $em->remove($otherdocs);
  226.                         $em->flush();
  227.                         $id 4;
  228.                     }
  229.                     break;
  230.                 default:
  231.                     echo 'No type provided';
  232.                     break;
  233.             }
  234.         }
  235.         if ($id != 0) {
  236.             $data = array(
  237.                 'status' => 1,
  238.                 'section' => $docType,
  239.                 'idAgenda' => $idAgenda,
  240.                 'idEvent' => $idEvent,
  241.                 'idDoc' => $idDoc
  242.             );
  243.             return new JsonResponse($data);
  244.         } else {
  245.             return new JsonResponse(array('status' => 0));
  246.         }
  247.     }
  248.     /**
  249.      * upload a new file that is attached to an event or a doclist
  250.      *
  251.      * @param Request $request
  252.      *
  253.      * @return JsonResponse
  254.      */
  255.     public function uploadEventDocumentAction(Request $request): JsonResponse
  256.     {
  257.         $userId $this->get('security.token_storage')
  258.             ->getToken()
  259.             ->getUser()
  260.             ->getid();
  261.         //get all the data we need
  262.         $uploadFile $request->files->get('file');
  263.         parse_str($request->request->get('formdata'), $params);
  264.         //summary and notes are in the get request for both events and doclists
  265.         $summary $request->request->get('summary');
  266.         $notes $request->request->get('notes');
  267.         //something strange is going on with the title when using doclist
  268.         //the title of the document is replaced with the title of the doclist
  269.         //try to avoid that
  270.         if ($request->request->get('doc-title') != '') {
  271.             $title $request->request->get('doc-title');
  272.         } elseif (isset($params['title'])
  273.             && $params['title'] != ''
  274.         ) {
  275.             $title $params['title'];
  276.         } else {
  277.             $title 'no title';
  278.         }
  279.         if (isset($params['doctype'])
  280.             && $params['doctype'] != ''
  281.         ) {
  282.             $docType $params['doctype'];
  283.         } else {
  284.             $docType $request->request->get('doctype');
  285.         }
  286.         if (isset($params['status'])
  287.             && $params['status'] != ''
  288.         ) {
  289.             $status $params['status'];
  290.         } else {
  291.             $status $request->request->get('status');
  292.         }
  293.         if (isset($params['author'])
  294.             && $params['author'] != ''
  295.         ) {
  296.             $authors $params['author'];
  297.         } elseif ($request->request->get('authors') !== null
  298.             && $request->request->get('authors') != ''
  299.         ) {
  300.             $authors $request->request->get('authors');
  301.         } else {
  302.             $authors '';
  303.         }
  304.         if (isset($params['keywords'])
  305.             && $params['keywords'] != ''
  306.         ) {
  307.             $keywords $params['keywords'];
  308.         } elseif ($request->request->get('keywords') !== null) {
  309.             $keywords $request->request->get('keywords');
  310.         } else {
  311.             $keywords '';
  312.         }
  313.         if (isset($params['doccode'])
  314.             && $params['doccode'] != ''
  315.         ) {
  316.             $docCode $params['doccode'];
  317.         } elseif ($request->request->get('doccode') !== null) {
  318.             $docCode $request->request->get('doccode');
  319.         } else {
  320.             $docCode '';
  321.         }
  322.         if (isset($params['websiteUrl'])
  323.             && $params['websiteUrl'] != ''
  324.         ) {
  325.             $url $params['websiteUrl'];
  326.         } else {
  327.             $url $request->request->get('url');
  328.         }
  329.         if (isset($params['doclist'])
  330.             && $params['doclist'] != ''
  331.         ) {
  332.             $docList $params['doclist'];
  333.         } else {
  334.             $docList $request->request->get('doclist');
  335.         }
  336.         if (isset($params['version'])
  337.             && is_array($params['version'])
  338.             && count($params['version'])
  339.         ) {
  340.             $version $params['version'];
  341.         } else {
  342.             //default version is 1
  343.             $version = array(1);
  344.         }
  345.         /*
  346.         dump($request->request);
  347.         dump($summary);
  348.         dump($title);
  349.         dump($notes);
  350.         dump($uploadFile);
  351.         dump($docType);
  352.         dump($status);
  353.         dump($authors);
  354.         dump($keywords);
  355.         dump($docCode);
  356.         dump($url);
  357.         dump($docList);
  358.         die();
  359.         */
  360.         //cleanup the data
  361.         $agendaItem '';
  362.         $idEvent '';
  363.         $idAgenda '';
  364.         $section '';
  365.         $idDoc '';
  366.         if ($docType != ''
  367.             && $status != ''
  368.         ) {
  369.             $em $this->getDoctrine()->getManager();
  370.             /**
  371.              * Create a document entry in documents table
  372.              */
  373.             $document = new Documents();
  374.             $document->setIdDoctype($docType);
  375.             $document->setDocCode($docCode);
  376.             $document->setTitle($title);
  377.             $document->setSummary($summary);
  378.             $document->setAuthorText($authors);
  379.             $document->setNotes($notes);
  380.             $document->setKeywords($keywords);
  381.             $document->setUrl($url);
  382.             $document->setIdDocstatus($status);
  383.             if ($status == 1) {
  384.                 $document->setPublishedOn(new DateTime('now'));
  385.             }
  386.             $document->setCreatedAt(new DateTime('now'));
  387.             $document->setUpdatedAt(new DateTime('now'));
  388.             if ($this->get('security.authorization_checker')->isGranted('ROLE_GLOBAL_EDITOR')) {
  389.                 $document->setApproved(1);
  390.             } else {
  391.                 $document->setApproved(0);
  392.             }
  393.             $document->setIdCreator($userId);
  394.             $document->setLastEditBy($userId);
  395.             $em->persist($document);
  396.             $em->flush();
  397.             if (is_array($docList)
  398.                 && count($docList) > 0
  399.             ) {
  400.                 // if documentlist is set then enter id documentlist table.
  401.                 foreach ($docList as $docListID) {
  402.                     $doclist $em->getRepository('OceanExpertBundle:DoclistDocuments')
  403.                         ->findOneBy(
  404.                             array(
  405.                                 'idDoclist' => $docListID,
  406.                                 'idDoc' => $document->getIdDoc()
  407.                             )
  408.                         );
  409.                     if (!$doclist) {
  410.                         $doclist = new DoclistDocuments();
  411.                     }
  412.                     $doclist->setIdDoclist($docListID);
  413.                     $doclist->setIdDoc($document->getIdDoc());
  414.                     $doclist->setAddedAt(new DateTime('now'));
  415.                     $em->persist($doclist);
  416.                     $em->flush();
  417.                 }
  418.             }
  419.             //if file is uploaded then upload the files in event or document section.
  420.             if (is_countable($uploadFile)
  421.                 && count($uploadFile) > 0
  422.             ) {
  423.                 $i 0;
  424.                 foreach ($uploadFile as $file) {
  425.                     $fileType $file->guessClientExtension();
  426.                     //$fileName = $file->getClientOriginalName();
  427.                     //cleanup the final filename
  428.                     $fileName preg_replace(
  429.                         '/[^\w\.]+/',
  430.                         '_',
  431.                         $file->getClientOriginalName()
  432.                     );
  433.                     $fileSize $file->getClientSize();
  434.                     if ($fileType == 'bin') {
  435.                         $fileType pathinfo($fileNamePATHINFO_EXTENSION);
  436.                     }
  437.                     $format $em->getRepository('OceanExpertBundle:Fileformats')
  438.                         ->findOneBy(
  439.                             array(
  440.                                 'extension' => $fileType
  441.                             )
  442.                         );
  443.                     if ($format) {
  444.                         $idFileFormat $format->getIdFileformat();
  445.                     } else {
  446.                         $idFileFormat 0;
  447.                     }
  448.                     if (array_key_exists('idEvent',$params)) {
  449.                         $directory 'uploads/events/documents/' $params['idEvent'] . '/';
  450.                     } else {
  451.                         $directory 'uploads/documents/';
  452.                     }
  453.                     $uploadFile $file->move($directory$fileName);
  454.                     $filepath $directory $fileName;
  455.                     /**
  456.                      * Create file entry in files table.
  457.                      * == filenew is added as multiple column primary indexes were set in old table,
  458.                      * cannot change as it might affect previous PPC sections.
  459.                      * Need to verify if there is no break of code in the earlier ppc.
  460.                      */
  461.                     if (isset($params['id_language'][$i])) {
  462.                         $defaultLanguage $params['id_language'][$i];
  463.                     } else {
  464.                         $defaultLanguage 1;
  465.                     }
  466.                     $conn $this->getDoctrine()->getConnection();
  467.                     $stmt $conn->prepare('INSERT INTO files 
  468.                             (version, id_language, id_fileformat, url, size, content, filename) 
  469.                         VALUES 
  470.                             (?, ?, ?, ?, ?, ?, ?)'
  471.                     );
  472.                     if (isset($version[$i])) {
  473.                         $thisVersion $version[$i];
  474.                     } else {
  475.                         $thisVersion 1;
  476.                     }
  477.                     $stmt->bindValue(1$thisVersion);
  478.                     $stmt->bindValue(2$defaultLanguage);
  479.                     $stmt->bindValue(3$idFileFormat);
  480.                     $stmt->bindValue(4utf8_encode($filepath));
  481.                     $stmt->bindValue(5$fileSize);
  482.                     $stmt->bindValue(6'');
  483.                     $stmt->bindValue(7utf8_encode($filepath));
  484.                     $stmt->execute();
  485.                     $idFile $this->getDoctrine()
  486.                         ->getConnection()
  487.                         ->lastInsertId();
  488.                     $files = new DocumentFiles();
  489.                     $files->setIdDoc($document->getIdDoc());
  490.                     $files->setIdFile($idFile);
  491.                     $files->setVersion($thisVersion);
  492.                     $files->setIdLanguage($defaultLanguage);
  493.                     $files->setIdFileformat($idFileFormat);
  494.                     $em->persist($files);
  495.                     $em->flush();
  496.                     $i++;
  497.                 }
  498.             }
  499.             if (array_key_exists('filesection',$params)) {
  500.                 if (array_key_exists('idEvent',$params)) {
  501.                     $idEvent $params['idEvent'];
  502.                 }
  503.                 if (array_key_exists('idEvent',$params)) {
  504.                     $idAgenda $params['idAgenda'];
  505.                 }
  506.                 $idDoc $document->getIdDoc();
  507.                 if ($idAgenda != '') {
  508.                     $agenda $em->getRepository('OceanExpertBundle:EventAgendaitems')
  509.                         ->findOneBy(
  510.                             array(
  511.                                 'id' => $idAgenda,
  512.                                 'idEvent' => $idEvent
  513.                             )
  514.                         );
  515.                     if ($agenda) {
  516.                         $agendaItem $agenda->getIdAgendaitem();
  517.                     }
  518.                 }
  519.                 //is this linked to some event or agenda
  520.                 if (array_key_exists('filesection',$params)) {
  521.                     $section $params['filesection'];
  522.                     switch ($section) {
  523.                         case 1:
  524.                             $report $em->getRepository('OceanExpertBundle:EventReports')
  525.                                 ->findOneBy(
  526.                                     array('idEvent' => $idEvent)
  527.                                 );
  528.                             if (!$report) {
  529.                                 $report = new EventReports();
  530.                             }
  531.                             $report->setIdEvent($idEvent);
  532.                             $report->setIdDoc($idDoc);
  533.                             $em->persist($report);
  534.                             $em->flush();
  535.                             break;
  536.                         case 2:
  537.                             $presentation = new EventPresentations();
  538.                             $presentation->setIdEvent($idEvent);
  539.                             $presentation->setIdDoc($idDoc);
  540.                             $presentation->setIdAgendaitem('');
  541.                             $em->persist($presentation);
  542.                             $em->flush();
  543.                             break;
  544.                         case 3:
  545.                             $backgrounddocs = new EventBackgrounddocs();
  546.                             $backgrounddocs->setIdEvent($idEvent);
  547.                             $backgrounddocs->setIdDoc($idDoc);
  548.                             $backgrounddocs->setIdAgendaitem('');
  549.                             $em->persist($backgrounddocs);
  550.                             $em->flush();
  551.                             break;
  552.                         case 4:
  553.                             $otherdocs = new EventOtherdocs();
  554.                             $otherdocs->setIdEvent($idEvent);
  555.                             $otherdocs->setIdDoc($idDoc);
  556.                             $em->persist($otherdocs);
  557.                             $em->flush();
  558.                             break;
  559.                         case 5:
  560.                             if (isset($agendaItem) && $agendaItem != '') {
  561.                                 $agendaItemDocs = new AgendaitemDocuments();
  562.                                 $agendaItemDocs->setIdEvent($idEvent);
  563.                                 $agendaItemDocs->setIdDoc($idDoc);
  564.                                 $agendaItemDocs->setIdAgendaitem($agendaItem);
  565.                                 $em->persist($agendaItemDocs);
  566.                                 $em->flush();
  567.                             }
  568.                             break;
  569.                         default:
  570.                             $data = array(
  571.                                 'status' => 0,
  572.                                 'message' => 'document could not be uploaded'
  573.                             );
  574.                             return new JsonResponse($data);
  575.                     }
  576.                 }
  577.             }
  578.             $data = array(
  579.                 'status' => 1,
  580.                 'section' => $section,
  581.                 'idEvent' => $idEvent,
  582.                 'idAgenda' => $idAgenda,
  583.                 'idDoc' => $idDoc,
  584.                 'docname' => $title
  585.             );
  586.         } else {
  587.             /*
  588.             print 'params:';
  589.             dump($params);
  590.             dump($summary);
  591.             dump($notes);
  592.             dump($uploadFile);
  593.             die();
  594.             */
  595.             $data = array(
  596.                 'status' => 1,
  597.                 'error' => 'something went wrong, not enough data'
  598.             );
  599.         }
  600.         return new JsonResponse($data);
  601.     }
  602.     /**
  603.      * @param $idDoclist
  604.      *
  605.      * @return Response
  606.      */
  607.     public function viewDoclistAction($idDoclist)
  608.     {
  609.        $data $this->doclistsRepository
  610.            ->getDoclistDocuments(
  611.                intval($idDoclist)
  612.            );
  613.        return $this->render(
  614.            'Documents/viewDoclist.html.twig',
  615.            array(
  616.                'data' => $data
  617.            )
  618.        );
  619.     }
  620.     /**
  621.      */
  622.     public function viewAllDoclistsAction()
  623.     {
  624.         /*
  625.          * @todo
  626.          * this is not used
  627.          * should this be gone or will that serve for later?
  628.          * Arno 18/06/2021
  629.          *
  630.         $repository = $this->getDoctrine()
  631.             ->getManager()
  632.             ->createQueryBuilder();
  633.         $repository->add('select', 'g.idGroup, g.groupname');
  634.         $repository->add('from', 'OceanExpertBundle:Groups g');
  635.         $repository->where('g.hasSite = 1');
  636.         $allGroups = $repository->getQuery()->getResult();
  637.         */
  638.         $repository $this->getDoctrine()
  639.             ->getManager()
  640.             ->createQueryBuilder();
  641.         $repository->add(
  642.             'select',
  643.             'd.idDoclist, d.title, d.description,g.idGroup,g.groupname'
  644.         );
  645.         $repository->add(
  646.             'from',
  647.             'OceanExpertBundle:Doclists d'
  648.         );
  649.         $repository->leftJoin(
  650.             'OceanExpertBundle:DoclistGroups',
  651.             'dg',
  652.             'WITH',
  653.             'd.idDoclist = dg.idDoclist'
  654.         );
  655.         $repository->leftJoin(
  656.             'OceanExpertBundle:Groups',
  657.             'g',
  658.             'WITH',
  659.             'g.idGroup = dg.idGroup'
  660.         );
  661.         $repository->where(
  662.             'g.groupname is not null'
  663.         );
  664.         $repository->orderBy(
  665.             'g.idGroup',
  666.             'ASC'
  667.         );
  668.         $doclists $repository->getQuery()
  669.             ->getResult();
  670.         $doclistsArr = [];
  671.         $templevel=$doclists[0]['groupname'];
  672.         /*
  673.          * @todo
  674.          * this is not used
  675.          * should this be gone or will that serve for later?
  676.          * Arno 18/06/2021
  677.          *
  678.         $grouparr[$templevel] = '';
  679.         */
  680.         foreach ($doclists as $key => $val) {
  681.             if ($templevel == $val['groupname']) {
  682.                 $doclistsArr[$templevel][] = $val;
  683.             } else {
  684.                 $doclistsArr[$val['groupname']][] = $val;
  685.             }
  686.         }
  687.         return $this->render(
  688.             'Documents/viewDoclists.html.twig',
  689.             array(
  690.                 'data' => $doclistsArr
  691.             )
  692.         );
  693.     }
  694.     /**
  695.      * @param int $idDoclist
  696.      *
  697.      * @return array
  698.      */
  699.     public function getDoclistDocuments(int $idDoclist): array
  700.     {
  701.         $data = array();
  702.         $em $this->getDoctrine()->getManager();
  703.         $doclist $em->getRepository('OceanExpertBundle:Doclists')->findOneBy(array('idDoclist'=>$idDoclist));
  704.         if($doclist){
  705.             $repository $this->getDoctrine()->getManager()->createQueryBuilder();
  706.             $repository->add('select''d.docCode, d.title, d.summary, d.authorText, d.publishedOn,d.idDoc, d.idDocstatus');
  707.             $repository->add('from''OceanExpertBundle:Documents d');
  708.             $repository->leftJoin('OceanExpertBundle:DoclistDocuments''dd''WITH''dd.idDoc = d.idDoc');
  709.             $repository->where('dd.idDoclist = :idDoclist');
  710.             $repository->setParameter('idDoclist'$idDoclist);
  711.             switch ($doclist->getOrderBy()){
  712.                 case 1:
  713.                     $repository->orderBy('d.publishedOn','desc');
  714.                     break;
  715.                 case 2:
  716.                     $repository->orderBy('d.publishedOn','asc');
  717.                     break;
  718.                 case 3:
  719.                     $repository->orderBy('dd.addedAt','desc');
  720.                     break;
  721.                 case 4:
  722.                     $repository->orderBy('dd.addedAt','asc');
  723.                     break;
  724.                 case 5:
  725.                     $repository->orderBy('d.docCode','desc');
  726.                     break;
  727.                 case 6:
  728.                     $repository->orderBy('d.docCode','asc');
  729.                     break;
  730.                 default:
  731.                     $repository->orderBy('d.title','asc');
  732.                     break;
  733.             }
  734.             $documents =  $repository->getQuery()->getResult();
  735.             $data = array(
  736.                 'doclist'=>$doclist,
  737.                 'documents'=>$documents,
  738.             );
  739.         }
  740.         return $data;
  741.     }
  742.     /**
  743.      * @param int     $idDoclist
  744.      * @param Request $request
  745.      *
  746.      * @return  Response
  747.      */
  748.     public function editDoclistAction(int $idDoclistRequest $request): Response
  749.     {
  750.         $em $this->getDoctrine()->getManager();
  751.         $author =  $this->get('security.token_storage')->getToken()->getUser();
  752.         if ($request->isMethod('POST')) {
  753.             $doclistdata $request->request->all();
  754.             if ($idDoclist != 0) {
  755.                 $doclist $em->getRepository('OceanExpertBundle:Doclists')
  756.                     ->findOneBy(
  757.                         array(
  758.                             'idDoclist' => $idDoclist
  759.                         )
  760.                     );
  761.             } else {
  762.                 $doclist = new Doclists();
  763.                 $doclist->setCreatedAt(new DateTime('now'));
  764.                 $doclist->setCreatedBy($author->getId());
  765.             }
  766.             $doclist->setTitle(
  767.                 $doclistdata['title'] ?? ''
  768.             );
  769.             $doclist->setDescription(
  770.                 $doclistdata['description'] ?? ''
  771.             );
  772.             $doclist->setIsPublic(
  773.                 $doclistdata['isPublic'] ?? 0
  774.             );
  775.             $doclist->setOrderBy(
  776.                 $doclistdata['order'] ?? 1
  777.             );
  778.             $doclist->setUpdatedAt(new DateTime('now'));
  779.             $em->persist($doclist);
  780.             $em->flush();
  781.             if (isset($doclistdata['websitegroup'])
  782.                 && $doclistdata['websitegroup'] !== ''
  783.             ) {
  784.                 echo $doclistdata['websitegroup'];
  785.                 $doclistGroup $em->getRepository('OceanExpertBundle:DoclistGroups')
  786.                     ->findOneBy(
  787.                         array(
  788.                             'idDoclist' => $idDoclist
  789.                         )
  790.                     );
  791.                 if (!$doclistGroup) {
  792.                     $doclistGroup = new DoclistGroups();
  793.                     $doclistGroup->setIdDoclist($doclist->getIdDoclist());
  794.                 }
  795.                 $doclistGroup->setIdGroup($doclistdata['websitegroup']);
  796.                 $em->persist($doclistGroup);
  797.                 $em->flush();
  798.             }
  799.             return $this->redirect(
  800.                 $this->generateUrl(
  801.                     'edit_doclist',
  802.                     array(
  803.                         'idDoclist' => $doclist->getIdDoclist()
  804.                     )
  805.                 )
  806.             );
  807.         }
  808.         $data $this->getDoclistDocuments($idDoclist);
  809.         $data['doclistOrder'] = array(
  810.           '0' => 'title',
  811.           '1' => 'date published (most recent first)',
  812.           '2' => 'date published (oldest first)',
  813.           '3' => 'date added to list (most recent first)',
  814.           '4' => 'date added to list (oldest first)',
  815.           '5' => 'document code (reverse alphabetically)',
  816.           '6' => 'document code (alphabetically)',
  817.         );
  818.         $repository $this->getDoctrine()->getManager()->createQueryBuilder();
  819.         $repository->add('select''g.idGroup, g.groupname');
  820.         $repository->add('from''OceanExpertBundle:Groups g');
  821.         $repository->where('g.hasSite = 1');
  822.         $data['allwebsiteGroup'] = $repository->getQuery()->getResult();
  823.         if ($idDoclist != 0){
  824.             /*
  825.              * @todo
  826.              * doclistgroup is not used in the template
  827.              * should this be gone or will that serve for later?
  828.              * Arno 22/05/2025
  829.              *
  830.             $repository = $this->getDoctrine()->getManager()->createQueryBuilder();
  831.             $repository->add('select', 'd.idGroup');
  832.             $repository->add('from', 'OceanExpertBundle:DoclistGroups d');
  833.             $repository->where('d.idDoclist = :idDoclist');
  834.             $repository->setParameter('idDoclist',$idDoclist);
  835.             $data['doclistGroup'] = $repository->getQuery()->getOneOrNullResult();
  836.             //$data['doclistGroup'] = $repository->getQuery()->getSingleResult();
  837.             */
  838.             $doclist $em->getRepository('OceanExpertBundle:Doclists')->findOneBy(array('idDoclist'=>$idDoclist));
  839.             if($doclist){
  840.                 $data['doclist'] = $doclist;
  841.             }
  842.             $data['documentList'] = $this->getDocumentList();
  843.         }
  844.         return $this->render('Documents/editDoclist.html.twig',array('data'=>$data));
  845.     }
  846.     /**
  847.      * @param Request $request
  848.      *
  849.      * @return Response
  850.      */
  851.     public function documentDownloadBlockAction(Request $request): Response
  852.     {
  853.         if (null != $request->request->get('idDoc')) {
  854.             $idDoc $request->request->get('idDoc');
  855.             $repository $this->getDoctrine()->getManager()->createQueryBuilder();
  856.             $repository->add('select''d.idDoc,d.url, df.idFile, df.version, l.languagename, l.languagecode, ff.mimetype, ff.extension,f.filename, f.size, f.content');
  857.             $repository->add('from''OceanExpertBundle:Documents d');
  858.             $repository->leftJoin('OceanExpertBundle:DocumentFiles''df''WITH''d.idDoc = df.idDoc');
  859.             $repository->leftJoin('OceanExpertBundle:Languages''l''WITH''df.idLanguage = l.idLanguage');
  860.             $repository->leftJoin('OceanExpertBundle:Fileformats''ff''WITH''df.idFileformat = ff.idFileformat');
  861.             $repository->leftJoin('OceanExpertBundle:Files''f''WITH''df.idFile = f.idFile');
  862.             $repository->where('d.idDoc =:idDoc');
  863.             $repository->setParameter('idDoc'$idDoc);
  864.             $documents $repository->getQuery()->getResult();
  865.             return $this->render(
  866.                 'Documents/downloadDocument.html.twig',
  867.                 array(
  868.                     'documents' => $documents
  869.                 )
  870.             );
  871.         } else {
  872.             return new Response('No document Available');
  873.         }
  874.     }
  875.     /**
  876.      * download the file with the given file id (!= doc id)
  877.      *
  878.      * @param int $idFile
  879.      *
  880.      * @return mixed
  881.      */
  882.     public function downloadFileAction($idFile)
  883.     {
  884.         if ($idFile == 0) {
  885.             return new JsonResponse(
  886.                 array(
  887.                     'status' => 1,
  888.                     'error' => 'file id is missing, nothing to be downloaded'
  889.                 )
  890.             );
  891.         } else {
  892.             $fileData $this->getDoctrine()
  893.                 ->getRepository('OceanExpertBundle:Files')
  894.                 ->findOneBy(
  895.                     array(
  896.                         'idFile' => $idFile
  897.                     )
  898.                 );
  899.             if ($fileData) {
  900.                 /*
  901.                  * no idea why we should do that...
  902.                 $downloadFileName = preg_replace(
  903.                     '/(.+)-(.+?)$/',
  904.                     "$1.$2",
  905.                     basename($fileData->getFilename())
  906.                 );
  907.                 */
  908.                 //clean the filename that will be used to download the file
  909.                 // see #433
  910.                 $downloadFileName trim(
  911.                     basename(
  912.                         $fileData->getFilename()
  913.                     )
  914.                 );
  915.                 $downloadFileName preg_replace(
  916.                     '/[[:^print:]]/',
  917.                     '_',
  918.                     $downloadFileName
  919.                 );
  920.                 //sometimes we have like very strange filenames
  921.                 //where the file extension is glued to the filename
  922.                 //get rid of that glue and make it a file extension again
  923.                 $fileExtensionId $fileData->getIdFileFormat();
  924.                 $fileExtensionData $this->getDoctrine()
  925.                     ->getRepository('OceanExpertBundle:Fileformats')
  926.                     ->findOneBy(
  927.                         array(
  928.                             'idFileformat' => $fileExtensionId
  929.                         )
  930.                     );
  931.                 if ($fileExtensionData) {
  932.                     $fileExtension $fileExtensionData->getExtension();
  933.                 } else {
  934.                     return new JsonResponse(
  935.                         array(
  936.                             'status' => 1,
  937.                             'error' => "problem : cannot get the file extension data from $downloadFileName"
  938.                         )
  939.                     );
  940.                 }
  941.                 if (!preg_match("/(.+?)\.$fileExtension$/"$downloadFileName)) {
  942.                     $downloadFileName preg_replace(
  943.                         "/(.+)\W$fileExtension$/",
  944.                         "$1.$fileExtension",
  945.                         $downloadFileName
  946.                     );
  947.                     /*
  948.                     return new JsonResponse(
  949.                         array(
  950.                             'status' => 1,
  951.                             'error' => "correct : file ext $fileExtensionId : $fileExtension : $downloadFileName"
  952.                         )
  953.                     );
  954.                     */
  955.                 }
  956.                 /*
  957.                 return new JsonResponse(
  958.                     array(
  959.                         'status' => 1,
  960.                         'error' => "wrong : file ext $fileExtensionId : $fileExtension : $downloadFileName"
  961.                     )
  962.                 );
  963.                 */
  964.                 if ($fileData->getUrl() == '') {
  965.                     //this is a file that we have stored in the db
  966.                     // Return a response with a specific content
  967.                     $response = new Response(stream_get_contents($fileData->getContent()));
  968.                     // Create the disposition of the file
  969.                     $disposition $response->headers->makeDisposition(
  970.                         ResponseHeaderBag::DISPOSITION_ATTACHMENT,
  971.                         $downloadFileName
  972.                     );
  973.                     // Set the content disposition
  974.                     $response->headers->set('Content-Disposition'$disposition);
  975.                     // Dispatch request
  976.                     return $response;
  977.                 } else {
  978.                     //check if the file exists
  979.                     $fileToCheck $fileData->getUrl();
  980.                     if (is_file($fileToCheck)) {
  981.                         $response = new BinaryFileResponse($fileData->getUrl());
  982.                         $response->setContentDisposition(
  983.                             ResponseHeaderBag::DISPOSITION_ATTACHMENT,
  984.                             $downloadFileName
  985.                         );
  986.                         return $response;
  987.                     } else {
  988.                         return new JsonResponse(
  989.                             array(
  990.                                 'status' => 3,
  991.                                 'error' => "no file with file id '$idFile' found on the server ('$fileToCheck')"
  992.                             )
  993.                         );
  994.                     }
  995.                 }
  996.             } else {
  997.                 return new JsonResponse(
  998.                     array(
  999.                         'status' => 2,
  1000.                         'error' => "no file found with file id '$idFile'"
  1001.                     )
  1002.                 );
  1003.             }
  1004.         }
  1005.     }
  1006.     /**
  1007.      * @param int     $idDoc
  1008.      * @param Request $request
  1009.      *
  1010.      * @return Response
  1011.      */
  1012.     public function addDocumentAction(int $idDocRequest $request)
  1013.     {
  1014.         $document = array();
  1015.         $docDoclist = array();
  1016.         $files = array();
  1017.         $type '';
  1018.         $postdata '';
  1019.         $filedata '';
  1020.         $userId $this->get('security.token_storage')
  1021.             ->getToken()
  1022.             ->getUser()
  1023.             ->getid();
  1024.         $em $this->getDoctrine()
  1025.             ->getManager();
  1026.         if ($idDoc != 0) {
  1027.             $document $this->getDoctrine()
  1028.                 ->getRepository('OceanExpertBundle:Documents')
  1029.                 ->findOneBy(
  1030.                     array(
  1031.                         'idDoc' => $idDoc
  1032.                     )
  1033.                 );
  1034.         }
  1035.         if ($request->request->has('submit')) {
  1036.             $postdata $request->request->all();
  1037.             $filedata $request->files->get('userfile');
  1038.             if (array_key_exists('doctype'$postdata)
  1039.                 && $postdata['doctype'] != ''
  1040.                 && $postdata['status'] != '') {
  1041.                 /**
  1042.                  * Create a document entry in documents table
  1043.                  */
  1044.                 if ($idDoc == 0) {
  1045.                     $document = new Documents();
  1046.                 }
  1047.                 $document->setIdDoctype($postdata['doctype']);
  1048.                 $document->setDocCode(isset($postdata['doccode']) ? $postdata['doccode'] : '');
  1049.                 $document->setTitle(isset($postdata['title']) ? $postdata['title'] : '');
  1050.                 $document->setSummary(isset($postdata['summary']) ? $postdata['summary'] : '');
  1051.                 $document->setAuthorText(isset($postdata['authors']) ? $postdata['authors'] : '');
  1052.                 if ($postdata['status'] == 2) {
  1053.                     $document->setPublishedOn(new DateTime('now'));
  1054.                 }
  1055.                 $document->setNotes(isset($postdata['notes']) ? $postdata['notes'] : '');
  1056.                 $document->setKeywords(isset($postdata['keywords']) ? $postdata['keywords'] : '');
  1057.                 $document->setUrl(isset($postdata['websiteUrl']) ? $postdata['websiteUrl'] : '');
  1058.                 $document->setIdDocstatus(isset($postdata['status']) ? $postdata['status'] : '');
  1059.                 $document->setCreatedAt(new DateTime('now'));
  1060.                 $document->setUpdatedAt(new DateTime('now'));
  1061.                 if ($this->get('security.authorization_checker')->isGranted('ROLE_GLOBAL_EDITOR')) {
  1062.                     $document->setApproved(1);
  1063.                 } else {
  1064.                     $document->setApproved(0);
  1065.                 }
  1066.                 $document->setIdCreator($userId);
  1067.                 $document->setLastEditBy($userId);
  1068.                 $em->persist($document); //marks object to be saved in the next transaction.
  1069.                 $em->flush(); //performs all saves and transactions.
  1070.                 if (isset($postdata['doclist'])
  1071.                     && count($postdata['doclist']) > 0
  1072.                 ) {
  1073.                     /**
  1074.                      * if documentlist is set then enter id documentlist table.
  1075.                      */
  1076.                     foreach ($postdata['doclist'] as $docListID) {
  1077.                         $doclist $em->getRepository('OceanExpertBundle:DoclistDocuments')
  1078.                             ->findOneBy(
  1079.                                 array(
  1080.                                     'idDoclist' => $docListID,
  1081.                                     'idDoc' => $document->getIdDoc()
  1082.                                 )
  1083.                             );
  1084.                         if (!$doclist) {
  1085.                             $doclist = new DoclistDocuments();
  1086.                         }
  1087.                         $doclist->setIdDoclist($docListID);
  1088.                         $doclist->setIdDoc($document->getIdDoc());
  1089.                         $doclist->setAddedAt(new DateTime('now'));
  1090.                         $em->persist($doclist);
  1091.                         $em->flush();
  1092.                     }
  1093.                 }
  1094.                 if (count($filedata) > 0) {
  1095.                     /**
  1096.                      * if file is uploaded then upload the files in event section.
  1097.                      */
  1098.                     $i 0;
  1099.                     foreach ($filedata as $file) {
  1100.                         if ($file != null) {
  1101.                             $fileType $file->guessClientExtension();
  1102.                             //cleanup the filename, we don't want any strange chars here
  1103.                             $fileName $file->getClientOriginalName();
  1104.                             $fileName preg_replace(
  1105.                                 array(
  1106.                                     '/\s+/',
  1107.                                     '/\W+/',
  1108.                                     ),
  1109.                                 array(
  1110.                                     '_',
  1111.                                     '-'
  1112.                                 ),
  1113.                                 $fileName
  1114.                             );
  1115.                             if ($fileType == 'bin') {
  1116.                                 $fileType pathinfo($fileNamePATHINFO_EXTENSION);
  1117.                             }
  1118.                             $format $em->getRepository('OceanExpertBundle:Fileformats')
  1119.                                 ->findOneBy(
  1120.                                     array(
  1121.                                         'extension' => $fileType
  1122.                                     )
  1123.                                 );
  1124.                             if ($format) {
  1125.                                 $idFileFormat $format->getIdFileformat();
  1126.                             } else {
  1127.                                 $idFileFormat 0;
  1128.                             }
  1129.                             $directory 'uploads/documents/' $document->getIdDoc() . '/';
  1130.                             $uploadFile $file->move($directory$fileName);
  1131.                             $filepath $directory $fileName;
  1132.                             /**
  1133.                              * Create file entry in files table. == filenew is added as multiple column primary indexes were set in old table,
  1134.                              * cannot change as it might affect previos PPC sections. needs to verify if there is no break of code in the e
  1135.                              * earlier ppc.
  1136.                              */
  1137.                             if (isset($postdata['id_language'][$i])) {
  1138.                                 $language $postdata['id_language'][$i];
  1139.                             } else {
  1140.                                 $language 1;
  1141.                             }
  1142.                             $conn $this->getDoctrine()->getConnection();
  1143.                             $stmt $conn->prepare('INSERT 
  1144.                                 INTO files (
  1145.                                             version, 
  1146.                                             id_language, 
  1147.                                             id_fileformat, 
  1148.                                             url, 
  1149.                                             size, 
  1150.                                             content, 
  1151.                                             filename
  1152.                                             ) 
  1153.                                 VALUES (?, ?, ?, ?, ?, ?, ?)'
  1154.                             );
  1155.                             $stmt->bindValue(1$postdata['version'][$i]);
  1156.                             $stmt->bindValue(2$language);
  1157.                             $stmt->bindValue(3$idFileFormat);
  1158.                             $stmt->bindValue(4utf8_encode($filepath));
  1159.                             $stmt->bindValue(5$file->getClientSize());
  1160.                             $stmt->bindValue(6'');
  1161.                             $stmt->bindValue(7utf8_encode($filepath));
  1162.                             $stmt->execute();
  1163.                             $idFile $this->getDoctrine()->getConnection()->lastInsertId();
  1164.                             $files = new DocumentFiles();
  1165.                             $files->setIdDoc($document->getIdDoc());
  1166.                             $files->setIdFile($idFile);
  1167.                             $files->setVersion($postdata['version'][$i]);
  1168.                             $files->setIdLanguage($language);
  1169.                             $files->setIdFileformat($idFileFormat);
  1170.                             $em->persist($files);
  1171.                             $em->flush();
  1172.                             $i++;
  1173.                         }
  1174.                     }
  1175.                 }
  1176.             }
  1177.             return $this->redirectToRoute(
  1178.                 'view_document',
  1179.                 array(
  1180.                     'idDoc' => $document->getIdDoc()
  1181.                 )
  1182.             );
  1183.         }
  1184.         if ($idDoc != 0) {
  1185.             $repository $em->createQueryBuilder();
  1186.             $repository->add(
  1187.                 'select',
  1188.                 'd.idDoclist'
  1189.             );
  1190.             $repository->add('from''OceanExpertBundle:DoclistDocuments d');
  1191.             $repository->where('d.idDoc = :idDoc');
  1192.             $repository->setParameter('idDoc'$idDoc);
  1193.             $docDoclist array_column($repository->getQuery()->getResult(), 'idDoclist');
  1194.             $repository $em->createQueryBuilder();
  1195.             $repository->add(
  1196.                 'select',
  1197.                 'd.idDoc, l.languagename, f.fileformatname,d.version, d.idFileformat,d.idFile'
  1198.             );
  1199.             $repository->add('from''OceanExpertBundle:DocumentFiles d');
  1200.             $repository->leftJoin('OceanExpertBundle:Languages''l''WITH''d.idLanguage = l.idLanguage');
  1201.             $repository->leftJoin('OceanExpertBundle:Fileformats''f''WITH''f.idFileformat = d.idFileformat');
  1202.             $repository->where('d.idDoc = :idDoc');
  1203.             $repository->setParameter('idDoc'$idDoc);
  1204.             $files $repository->getQuery()->getResult();
  1205.         }
  1206.         $docType = array(
  1207.             => 'Report',
  1208.             => 'Presentation',
  1209.             => 'Information Document',
  1210.             => 'Letter',
  1211.             => 'Book',
  1212.             => 'MOU',
  1213.             => 'Other',
  1214.             => 'Link to online document',
  1215.             10 => 'Reference Document',
  1216.             11 => 'Working Document'
  1217.         );
  1218.         $data = array(
  1219.             'document' => $document,
  1220.             'docDoclist' => $docDoclist,
  1221.             'type' => $type,
  1222.             'postdata' => $postdata,
  1223.             'filedata' => $filedata,
  1224.             'files' => $files,
  1225.             'docTypes' => $docType,
  1226.             'documentList' => $this->getDocumentList(),
  1227.         );
  1228.         return $this->render(
  1229.             'Documents/editDocument.html.twig',
  1230.             array(
  1231.                 'data' => $data
  1232.             )
  1233.         );
  1234.     }
  1235.     /**
  1236.      * view the info of a document with the given doc id
  1237.      *
  1238.      * @param $idDoc
  1239.      *
  1240.      * @return Response
  1241.      */
  1242.     public function viewDocumentAction($idDoc)
  1243.     {
  1244.         $em $this->getDoctrine()->getManager();
  1245.         $queryBuilder $em->getRepository('OceanExpertBundle:Documents')->createQueryBuilder('d');
  1246.         $queryBuilder->select('
  1247.                 d.docCode, 
  1248.                 d.idDoc, 
  1249.                 d.idDocstatus, 
  1250.                 d.title, 
  1251.                 d.summary, 
  1252.                 d.authorText, 
  1253.                 d.publishedOn, 
  1254.                 d.notes, 
  1255.                 d.keywords, 
  1256.                 d.createdAt, 
  1257.                 d.updatedAt,
  1258.                 d.idCreator,
  1259.                 d.lastEditBy,
  1260.                 d.url,
  1261.                 d.idDoctype,
  1262.                 dt.doctypename ')
  1263.             ->leftJoin(
  1264.                 'OceanExpertBundle:Doctypes',
  1265.                 'dt',
  1266.                 'WITH',
  1267.                 'd.idDoctype = dt.idDoctype')
  1268.             ->where('d.idDoc = :idDoc');
  1269.         if (!($this->get('security.authorization_checker')->isGranted('ROLE_MANAGER'))) {
  1270.             $queryBuilder->andWhere('d.approved = 1');
  1271.         }
  1272.         $queryBuilder->setParameter('idDoc'$idDoc);
  1273.         $query $queryBuilder->getQuery();
  1274.         $result $query->getResult();
  1275.         if (count($result)>0) {
  1276.             $result $result[0];
  1277.             if ($result['idCreator'] != '') {
  1278.                 $indiv $this->getDoctrine()->getRepository('OceanExpertBundle:Indiv')->findOneByIdInd($result['idCreator']);
  1279.                 if ($indiv) {
  1280.                     $result['createdBy'] = $indiv->getFname() . ' ' $indiv->getSname();
  1281.                 } else {
  1282.                     $result['createdBy'] = '';
  1283.                 }
  1284.             }
  1285.             if ($result['lastEditBy'] != '') {
  1286.                 $indiv $this->getDoctrine()->getRepository('OceanExpertBundle:Indiv')->findOneByIdInd($result['lastEditBy']);
  1287.                 if ($indiv) {
  1288.                     $result['updatedBy'] = $indiv->getFname() . ' ' $indiv->getSname();
  1289.                 } else {
  1290.                     $result['updatedBy'] = '';
  1291.                 }
  1292.             }
  1293.         }
  1294.         //check in what event this document is used
  1295.         //event_backgrounddocs
  1296.         //agendaitem_documents
  1297.         //event_otherdocs
  1298.         //event_presentations
  1299.         //event_reports
  1300.         $docEventList = array();
  1301.         $tmpEventList = array();
  1302.         $relatedTables = array(
  1303.             'EventBackgrounddocs',
  1304.             'AgendaitemDocuments',
  1305.             'EventOtherdocs',
  1306.             'EventPresentations',
  1307.             'EventReports'
  1308.         );
  1309.         foreach ($relatedTables as $relatedTable) {
  1310.             /*
  1311.             $eventList = $em->getRepository('OceanExpertBundle:Events')->createQueryBuilder('e')
  1312.                 ->select('e.idEvent,e.title')
  1313.                 ->leftJoin('OceanExpertBundle:EventBackgrounddocs', 'b', 'WITH', 'e.idEvent = b.idEvent')
  1314.                 ->leftJoin('OceanExpertBundle:AgendaitemDocuments', 'a', 'WITH', 'e.idEvent = a.idEvent')
  1315.                 ->leftJoin('OceanExpertBundle:EventOtherdocs', 'o', 'WITH', 'e.idEvent = o.idEvent')
  1316.                 ->leftJoin('OceanExpertBundle:EventPresentations', 'p', 'WITH', 'e.idEvent = p.idEvent')
  1317.                 ->leftJoin('OceanExpertBundle:EventReports', 'r', 'WITH', 'e.idEvent = r.idEvent')
  1318.                 ->where('b.idDoc = :idDoc')
  1319.                 ->orWhere('a.idDoc = :idDoc')
  1320.                 ->orWhere('o.idDoc = :idDoc')
  1321.                 ->orWhere('p.idDoc = :idDoc')
  1322.                 ->orWhere('r.idDoc = :idDoc')
  1323.                 ->setParameter('idDoc', $idDoc)
  1324.                 ->getQuery()
  1325.                 ->getResult();
  1326.             */
  1327.             $leftJoin 'OceanExpertBundle:' $relatedTable;
  1328.             $eventList $em->getRepository('OceanExpertBundle:Events')->createQueryBuilder('e')
  1329.                 ->select('distinct e.idEvent,e.title')
  1330.                 ->leftJoin($leftJoin'b''WITH''e.idEvent = b.idEvent')
  1331.                 ->where('b.idDoc = :idDoc')
  1332.                 ->setParameter('idDoc'$idDoc)
  1333.                 ->getQuery()
  1334.                 ->getResult();
  1335.             if (is_array($eventList)
  1336.                 && count($eventList)
  1337.             ) {
  1338.                 $tmpEventList array_merge($tmpEventList$eventList);
  1339.                 /*
  1340.                 foreach ($eventList as $event) {
  1341.                     $docEventList[$event['idEvent']] = $event;
  1342.                 }
  1343.                 */
  1344.             }
  1345.         }
  1346.         foreach ($tmpEventList as $event) {
  1347.             $docEventList[$event['idEvent']] = $event;
  1348.         }
  1349.         //dump($docEventList);
  1350.         //die();
  1351.         $groups $em->getRepository('OceanExpertBundle:DocumentGroups')->createQueryBuilder('d')
  1352.             ->select('d.idGroup,g.groupname')
  1353.             ->leftJoin('OceanExpertBundle:Groups''g''WITH''d.idGroup = g.idGroup')
  1354.             ->where('d.idDoc = :idDoc')
  1355.             ->setParameter('idDoc'$idDoc)
  1356.             ->getQuery()
  1357.             ->getResult();
  1358.         $docGroupList $em->getRepository('OceanExpertBundle:DoclistDocuments')->createQueryBuilder('d')
  1359.             ->select('dl.idDoclist, dl.title')
  1360.             ->leftJoin('OceanExpertBundle:Doclists''dl''WITH''dl.idDoclist = d.idDoclist')
  1361.             ->where('d.idDoc = :idDoc')
  1362.             ->setParameter('idDoc'$idDoc)
  1363.             ->orderBy('dl.title','ASC')
  1364.             ->getQuery()
  1365.             ->getResult();
  1366.         $docGroupListAll $em->getRepository('OceanExpertBundle:Doclists')->createQueryBuilder('d')
  1367.             ->select("d.idDoclist, CONCAT(g.groupname, ' - ', d.title) as title")
  1368.             ->leftJoin('OceanExpertBundle:DoclistGroups''dg''WITH''d.idDoclist = dg.idDoclist')
  1369.             ->leftJoin('OceanExpertBundle:Groups''g''WITH''dg.idGroup = g.idGroup')
  1370.             ->where("g.groupname != ''")
  1371.             ->orderBy('g.groupname, d.title','ASC')
  1372.             ->getQuery()
  1373.             ->getResult();
  1374.         $docFiles $em->getRepository('OceanExpertBundle:DocumentFiles')->createQueryBuilder('d')
  1375.             ->select('d.idFileformat, d.version, l.languagename, d.idFile, f.filename, f.url, f.size,f.content,f.path')
  1376.             ->leftJoin('OceanExpertBundle:Files''f''WITH''f.idFile = d.idFile')
  1377.             ->leftJoin('OceanExpertBundle:Languages''l''WITH''f.idLanguage = l.idLanguage')
  1378.             ->where('d.idDoc = :idDoc')
  1379.             ->setParameter('idDoc'$idDoc)
  1380.             ->getQuery()
  1381.             ->getResult();
  1382.         $docGroupListArr = array();
  1383.         foreach ($docGroupList as $doclist){
  1384.             $docGroupListArr[] = $doclist['idDoclist'];
  1385.         }
  1386.         $result['documentGroup'] = $groups;
  1387.         $result['docGroupList'] = $docGroupList;
  1388.         $result['docFiles'] = $docFiles;
  1389.         $result['docGroupListArr'] = $docGroupListArr;
  1390.         $result['docGroupListAll'] = $docGroupListAll;
  1391.         $result['docEventList'] = $docEventList;
  1392.         return $this->render(
  1393.             'Documents/viewDocument.html.twig',
  1394.             array(
  1395.                 'data' => $result
  1396.             )
  1397.         );
  1398.     }
  1399.     /**
  1400.      * show all existing documents
  1401.      *
  1402.      * @param Request $request
  1403.      *
  1404.      * @return Response|void
  1405.      */
  1406.     function viewDocumentsActionRequest $request): Response
  1407.     {
  1408.         $em $this->get('doctrine')->getManager();
  1409.         $connection $em->getConnection();
  1410.         $limits = array(
  1411.             10 => 10,
  1412.             25 => 25,
  1413.             50 => 50,
  1414.             100 => 100,
  1415.             500 => 500
  1416.         );
  1417.         $limit $request->query->get('limit'10);
  1418.         $query $request->query->get('search''');
  1419.         $orderby $request->query->get('orderby''order');
  1420.         $dir $request->query->get('dir''asc');
  1421.         if (!in_array($orderby, array('id''title'))) {
  1422.             $orderby 'title';
  1423.         }
  1424.         if (!in_array($dir, array('asc''desc'))) {
  1425.             $dir 'asc';
  1426.         }
  1427.         //get all the doclists
  1428.         $repo $this->getDoctrine()
  1429.             ->getRepository('OceanExpertBundle:Doclists');
  1430.         $docListsQB $repo->createQueryBuilder('dl');
  1431.         $docListsQB->select('
  1432.             dl.idDoclist, 
  1433.             dl.title'
  1434.         );
  1435.         $docListsQuery $docListsQB->getQuery();
  1436.         $docListsTmp $docListsQuery->execute();
  1437.         foreach($docListsTmp as $docList) {
  1438.             $docLists[$docList['idDoclist']] = $docList['title'];
  1439.         }
  1440.         //get all the doclist docs, this avoids having to make an join that is veryveryvery slow
  1441.         $repo $this->getDoctrine()
  1442.             ->getRepository('OceanExpertBundle:DoclistDocuments');
  1443.         $docListDocumentsQB $repo->createQueryBuilder('dld');
  1444.         $docListDocumentsQB->select('
  1445.             dld.id,
  1446.             dld.idDoclist, 
  1447.             dld.idDoc'
  1448.         );
  1449.         $docListDocumentsQuery $docListDocumentsQB->getQuery();
  1450.         $docListDocumentsTmp $docListDocumentsQuery->execute();
  1451.         foreach($docListDocumentsTmp as $docListDocument) {
  1452.             $docListDocuments[$docListDocument['idDoc']][] = $docListDocument;
  1453.         }
  1454.         $repo $this->getDoctrine()
  1455.             ->getRepository('OceanExpertBundle:Documents');
  1456.         $documents $repo->createQueryBuilder('d');
  1457.         $documents->select('
  1458.             d.idDoc, 
  1459.             d.title,
  1460.             d.createdAt'
  1461.         );
  1462.         if (trim($query) != '') {
  1463.             $documents->andwhere('d.title LIKE :query');
  1464.             $documents->setParameter(
  1465.                 'query',
  1466.                 '%' trim($query) . '%'
  1467.             );
  1468.         }
  1469.         $documents->getQuery();
  1470.         $paginator $this->get('knp_paginator');
  1471.         $data $paginator->paginate(
  1472.             $documents,
  1473.             $request->query->getInt('page'1),
  1474.             $limit,
  1475.             array(
  1476.                 'pageParameterName' => 'page',
  1477.                 'sortDirectionParameterName' => 'dir',
  1478.                 'defaultSortFieldName' => 'd.createdAt',
  1479.                 'defaultSortDirection' => 'desc'
  1480.             )
  1481.         );
  1482.         $data->setCustomParameters([
  1483.             'limits' => $limits,
  1484.             'title' => 'Documents'
  1485.         ]);
  1486.         return $this->render(
  1487.             'Documents/viewDocuments.html.twig',
  1488.             array(
  1489.                 'documentData' => $data,
  1490.                 'doclists' => $docLists,
  1491.                 'doclistDocuments' => $docListDocuments
  1492.             )
  1493.         );
  1494.     }
  1495.     /**
  1496.      * delete a document
  1497.      * this should not be called in the browser or via a direct link
  1498.      *
  1499.      * @return Response
  1500.      */
  1501.     public function deleteDocumentAction(Request $request)
  1502.     {
  1503.         $idDoc $request->request->get('idDoc');
  1504.         if ($idDoc
  1505.             && $idDoc != ''
  1506.             && is_numeric($idDoc)
  1507.         ) {
  1508.             $em $this->getDoctrine()->getManager();
  1509.             //we will not delete files that are still in an event or doclist
  1510.             $eventList $em->getRepository('OceanExpertBundle:Events')->createQueryBuilder('e')
  1511.                 ->select('e.idEvent,e.title')
  1512.                 ->leftJoin('OceanExpertBundle:EventBackgrounddocs''b''WITH''e.idEvent = b.idEvent')
  1513.                 ->leftJoin('OceanExpertBundle:AgendaitemDocuments''a''WITH''e.idEvent = a.idEvent')
  1514.                 ->leftJoin('OceanExpertBundle:EventOtherdocs''o''WITH''e.idEvent = o.idEvent')
  1515.                 ->leftJoin('OceanExpertBundle:EventPresentations''p''WITH''e.idEvent = p.idEvent')
  1516.                 ->leftJoin('OceanExpertBundle:EventReports''r''WITH''e.idEvent = r.idEvent')
  1517.                 ->where('b.idDoc = :idDoc')
  1518.                 ->orWhere('a.idDoc = :idDoc')
  1519.                 ->orWhere('o.idDoc = :idDoc')
  1520.                 ->orWhere('p.idDoc = :idDoc')
  1521.                 ->orWhere('r.idDoc = :idDoc')
  1522.                 ->setParameter('idDoc'$idDoc)
  1523.                 ->getQuery()
  1524.                 ->getResult();
  1525.             $docList $em->getRepository('OceanExpertBundle:DoclistDocuments')->createQueryBuilder('d')
  1526.                 ->select('dl.idDoclist, dl.title')
  1527.                 ->leftJoin('OceanExpertBundle:Doclists''dl''WITH''dl.idDoclist = d.idDoclist')
  1528.                 ->where('d.idDoc = :idDoc')
  1529.                 ->setParameter('idDoc'$idDoc)
  1530.                 ->orderBy('dl.title','ASC')
  1531.                 ->getQuery()
  1532.                 ->getResult();
  1533.             if ((is_array($eventList)
  1534.                 && count($eventList))
  1535.                 || (is_array($docList)
  1536.                     && count($docList))
  1537.             ) {
  1538.                 $data = array(
  1539.                     'status' => 2,
  1540.                     'message' => "document cannot be deleted as it is still used in either a doclist or an event"
  1541.                 );
  1542.             } else {
  1543.                 $document $em->getRepository('OceanExpertBundle:Documents')
  1544.                     ->findOneBy(
  1545.                         array(
  1546.                             'idDoc' => $idDoc
  1547.                         )
  1548.                     );
  1549.                 $documentFiles $em->getRepository('OceanExpertBundle:DocumentFiles')
  1550.                     ->findBy(
  1551.                         array(
  1552.                             'idDoc' => $idDoc
  1553.                         )
  1554.                     );
  1555.                 if ($document) {
  1556.                     $em->remove($document);
  1557.                     $em->flush();
  1558.                 }
  1559.                 if ($documentFiles) {
  1560.                     foreach ($documentFiles as $documentFile) {
  1561.                         $file $em->getRepository('OceanExpertBundle:Files')
  1562.                             ->findOneBy(
  1563.                                 array(
  1564.                                     'idFile' => $documentFile->getIdFile()
  1565.                                 )
  1566.                             );
  1567.                         if ($file) {
  1568.                             if (file_exists($file->getFilename())) {
  1569.                                 unlink($file->getFilename());
  1570.                             }
  1571.                             $em->remove($file);
  1572.                             $em->flush();
  1573.                         }
  1574.                         $em->remove($documentFile);
  1575.                         $em->flush();
  1576.                     }
  1577.                 }
  1578.                 $data = array('status' => 1);
  1579.             }
  1580.         } else {
  1581.             $data = array(
  1582.                 'status' => 0,
  1583.                 'message' => "no document id found, did you call this from a browser? Don't"
  1584.             );
  1585.         }
  1586.         return new JsonResponse($data);
  1587.     }
  1588.     public function removeDocumentFileAction(Request $request)
  1589.     {
  1590.         $idDoc $request->request->get('idDoc');
  1591.         $idFile $request->request->get('idFile');
  1592.         if (isset($idDoc)
  1593.             && is_numeric($idDoc)
  1594.             && isset($idFile)
  1595.             && is_numeric($idFile)
  1596.         ) {
  1597.             $em $this->getDoctrine()->getManager();
  1598.             $docFiles $em->getRepository('OceanExpertBundle:DocumentFiles')
  1599.                 ->findOneBy(
  1600.                     array(
  1601.                         'idDoc' => $idDoc,
  1602.                         'idFile' => $idFile
  1603.                     )
  1604.                 );
  1605.             if ($docFiles) {
  1606.                 $em->remove($docFiles);
  1607.                 $em->flush();
  1608.             }
  1609.             $file $em->getRepository('OceanExpertBundle:Files')->findOneBy(array('idFile' => $idFile));
  1610.             if (file_exists($file->getFilename())) {
  1611.                 unlink($file->getFilename());
  1612.             }
  1613.             if ($file) {
  1614.                 $em->remove($file);
  1615.                 $em->flush();
  1616.             }
  1617.             $data = array('status' => 1);
  1618.         } else {
  1619.             $data = array(
  1620.                 'status' => 0,
  1621.                 'message' => 'do not call this directly in the browser, we need post data'
  1622.             );
  1623.         }
  1624.         return new JsonResponse($data);
  1625.     }
  1626.     /**
  1627.      * @return Response
  1628.      */
  1629.     public function removeDocumentFromListAction(Request $request)
  1630.     {
  1631.         $idDoc $request->request->get('idDoc');
  1632.         $idDoclist $request->request->get('idDoclist');
  1633.         $em $this->getDoctrine()->getManager();
  1634.         $document $em->getRepository('OceanExpertBundle:DoclistDocuments')->findOneBy(array('idDoc' => $idDoc,'idDoclist'=>$idDoclist));
  1635.         if ($document) {
  1636.             $em->remove($document);
  1637.             $em->flush();
  1638.         }
  1639.         $data = array('status' => 1);
  1640.         return new JsonResponse($data);
  1641.     }
  1642.     /**
  1643.      * @return Response
  1644.      */
  1645.     public function deleteDocumentListAction(Request $request)
  1646.     {
  1647.         $idDoclist $request->request->get('idDoclist');
  1648.         $em $this->getDoctrine()->getManager();
  1649.         $documentList $em->getRepository('OceanExpertBundle:Doclists')->findOneBy(array('idDoclist'=>$idDoclist));
  1650.         if ($documentList) {
  1651.             $em->remove($documentList);
  1652.             $em->flush();
  1653.         }
  1654.         $documents $em->getRepository('OceanExpertBundle:DoclistDocuments')->findBy(array('idDoclist'=>$idDoclist));
  1655.         if ($documents) {
  1656.             foreach ($documents as $document){
  1657.                 $em->remove($document);
  1658.                 $em->flush();
  1659.             }
  1660.         }
  1661.         $data = array('status' => 1);
  1662.         return new JsonResponse($data);
  1663.     }
  1664.     /**
  1665.      * get all the user groups this current active user belongs to
  1666.      *
  1667.      * @return array | boolean
  1668.      */
  1669.     function getUserGroups()
  1670.     {
  1671.         $userId $this->get('security.token_storage')
  1672.             ->getToken()
  1673.             ->getUser()
  1674.             ->getid();
  1675.         if ($userId) {
  1676.             $repository $this->getDoctrine()
  1677.                 ->getManager()
  1678.                 ->createQueryBuilder();
  1679.             $repository->add(
  1680.                 'select',
  1681.                 'g.idGroup'
  1682.             );
  1683.             $repository->add(
  1684.                 'from',
  1685.                 'OceanExpertBundle:MemberGroups g'
  1686.             );
  1687.             $repository->where(
  1688.                 'g.idInd = :userId'
  1689.             );
  1690.             $repository->setParameter(
  1691.                 'userId',
  1692.                 $userId
  1693.             );
  1694.             return $repository->getQuery()->getResult();
  1695.         } else {
  1696.             return false;
  1697.         }
  1698.     }
  1699.     /**
  1700.      * @return array
  1701.      */
  1702.     function getDocumentList(){
  1703.         //this is what we will return
  1704.         $documentList = array();
  1705.         //get all the user groups this current active user belongs to
  1706.         $grouplist array_map(
  1707.             'current',
  1708.             $this->getUserGroups()
  1709.         );
  1710.         //get all the doclists that are linked to these groups
  1711.         $repository $this->getDoctrine()
  1712.             ->getManager()
  1713.             ->createQueryBuilder();
  1714.         $repository->add(
  1715.             'select',
  1716.             'distinct(d.idGroup) as idGroup, g.groupname'
  1717.         );
  1718.         $repository->add(
  1719.             'from',
  1720.             'OceanExpertBundle:DoclistGroups d'
  1721.         );
  1722.         $repository->leftJoin(
  1723.             'OceanExpertBundle:Groups',
  1724.             'g',
  1725.             'WITH',
  1726.             'd.idGroup = g.idGroup'
  1727.         );
  1728.         $repository->where(
  1729.             'g.idGroup in (:grouplist)'
  1730.         );
  1731.         $repository->setParameter(
  1732.             'grouplist',
  1733.             $grouplist
  1734.         );
  1735.         $repository->orderBy('g.groupname');
  1736.         $docGroups $repository->getQuery()->getResult();
  1737.         foreach ($docGroups as $docGroup) {
  1738.             $repository $this->getDoctrine()
  1739.                 ->getManager()
  1740.                 ->createQueryBuilder();
  1741.             $repository->add(
  1742.                 'select',
  1743.                 'd.idDoclist, d.title, dg.idGroup'
  1744.             );
  1745.             $repository->add(
  1746.                 'from',
  1747.                 'OceanExpertBundle:Doclists d'
  1748.             );
  1749.             $repository->leftJoin(
  1750.                 'OceanExpertBundle:DoclistGroups',
  1751.                 'dg',
  1752.                 'WITH',
  1753.                 'dg.idDoclist = d.idDoclist'
  1754.             );
  1755.             $repository->where('dg.idGroup =:idGroup');
  1756.             $repository->setParameter(
  1757.                 'idGroup',
  1758.                 $docGroup['idGroup']
  1759.             );
  1760.             $repository->orderBy('d.title''asc');
  1761.             $documentList[$docGroup['groupname']] = $repository->getQuery()->getResult();
  1762.         }
  1763.         return $documentList;
  1764.     }
  1765.     /**
  1766.      * add a document to a doclist
  1767.      *
  1768.      * @param Request $request
  1769.      *
  1770.      * @return JsonResponse
  1771.      *
  1772.      */
  1773.     public function addDocumentToDoclistAction(Request $request)
  1774.     {
  1775.         $em $this->getDoctrine()->getManager();
  1776.         //dump($request->request->all());
  1777.         //die();
  1778.         $idDoclist $request->request->get('idDoclist');
  1779.         $idDoc $request->request->get('idDoc');
  1780.         if ($idDoclist == ''
  1781.             || $idDoclist == 'undefined'
  1782.             || $idDoc == ''
  1783.         ) {
  1784.             $message 'We really need a document id and a doclist id to do this. ';
  1785.             $message .= 'Did you call this directly in the browser? You should not do this.';
  1786.             return new JsonResponse(
  1787.                 array(
  1788.                     'status' => 2,
  1789.                     'error' => $message
  1790.                 )
  1791.             );
  1792.         }
  1793.         $doclistDocument $em->getRepository('OceanExpertBundle:DoclistDocuments')
  1794.             ->findOneBy(
  1795.                 array(
  1796.                     'idDoclist' => $idDoclist,
  1797.                     'idDoc' => $idDoc)
  1798.             );
  1799.         if (!$doclistDocument) {
  1800.             $doclistDocument = new DoclistDocuments();
  1801.         }
  1802.         $doclistDocument->setIdDoclist($idDoclist);
  1803.         $doclistDocument->setIdDoc($idDoc);
  1804.         $doclistDocument->setAddedAt(new DateTime('now'));
  1805.         $em->persist($doclistDocument);
  1806.         $em->flush();
  1807.         return new JsonResponse(
  1808.             array(
  1809.                 'status' => 1
  1810.             )
  1811.         );
  1812.     }
  1813. }