src/Repository/ActionRepository.php line 307

Open in your IDE?
  1. <?php
  3. namespace App\Repository;
  5. use App\Entity\Action;
  6. use App\Entity\ActionPlan;
  7. use App\Entity\Configuration\CompanyEntity;
  8. use App\Entity\User;
  9. use App\Enum\PriorityEnum;
  10. use App\Enum\StatusEnum;
  11. use App\Service\SelectedEntityService;
  12. use DateTime;
  13. use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
  14. use Doctrine\Persistence\ManagerRegistry;
  15. use DoctrineExtensions\Query\Mysql\Month;
  16. use App\Utils\Utility;
  17. use Symfony\Component\HttpFoundation\RequestStack;
  18. use Symfony\Component\Security\Core\Security;
  20. /**
  21.  * @method Company|null find($id, $lockMode = null, $lockVersion = null)
  22.  * @method Company|null findOneBy(array $criteria, array $orderBy = null)
  23.  * @method Company[]    findAll()
  24.  * @method Company[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
  25.  */
  26. class ActionRepository extends ServiceEntityRepository
  27. {
  28.     private $requestStack;
  29.     private $security;
  30.     private $selectedEntityService;
  31.     public function __construct(
  32.         ManagerRegistry $registry,
  33.         Security $security,
  34.         RequestStack $requestStack,
  35.         SelectedEntityService $selectedEntityService
  36.     )
  37.     {
  38.         parent::__construct($registry, Action::class);
  39.         $this->security = $security;
  40.         $this->requestStack = $requestStack;
  41.         $this->selectedEntityService = $selectedEntityService;
  42.     }
  45.     
  46.     /*public function findLastActionEE(): ?Action
  47.     {
  48.         return $this->createQueryBuilder('a')
  49.         ->where('a.action IS NOT NULL')
  50.         ->orderBy('a.id', 'desc')
  51.         ->getQuery()
  52.         ->getOneOrNullResult();
  53.         
  54.     }*/
  56.     public function getActionsPeridoques()
  57.     {
  58.         return $this->createQueryBuilder('a')
  59.         ->where('a.periodicity IS NOT NULL')
  60.         ->andWhere('a.action IS NOT 1')
  61.         ->getQuery()
  62.         ->getOneOrNullResult();
  63.     }
  65.     public function findLastRef($prefix)
  66.     {
  67.         $qb= $this->createQueryBuilder('a');
  68.         $qb->select('MAX(a.ref)')
  69.             ->where(
  70.                 $qb->expr()->like('a.ref', ':prefix')
  71.             )
  72.             ->setParameter('prefix', $prefix.'-%');
  73.         
  74.         $ref=$qb->getQuery()->getOneOrNullResult();
  75.         
  76.         return $ref[1];
  77.     }
  79.     public function transform(Action $action,User $user, $remove_link='')
  80.     {
  81.         
  82.         $respectBudget ="OK";
  83.         if($action->getBudgetEstimated()<$action->getBudgetReal()) $respectBudget ="NOK";
  84.         
  85.         $respectDuree = "OK";
  86.         if($action->getDurationEstimated()<$action->getDurationReal()) $respectDuree ="NOK";
  89.         
  90.          
  91.         $respectDelais = "OK";
  92.         $joursRetard="0";
  93.         $now = new DateTime("now");
  94.         if($action->getDateEndEstimated()<$action->getDateEndReal()) 
  95.         {
  96.             $respectDelais ="NOK";
  97.             $joursRetard = $action->getDateEndEstimated()->diff($action->getDateEndReal())->format("%a");
  98.         }
  99.         elseif($action->getDateEndEstimated() < $now)
  100.         {
  101.             $respectDelais ="NOK";
  102.             $joursRetard = $action->getDateEndEstimated()->diff($now)->format("%a");
  103.         }
  104.         
  106.         if(in_array($action->getStatus(),[StatusEnum::STATUS_PLANNED,StatusEnum::STATUS_DRAFT,StatusEnum::STATUS_IN_PROGRESS]))
  107.         {
  108.             $respectBudget="";
  109.             $respectDuree="";
  110.             $respectDelais="";
  111.         }
  112.         if(is_null($action->getBudgetEstimated()) || is_null($action->getBudgetReal()))
  113.         {
  114.             $respectBudget="";
  115.         }
  117.         if(is_null($action->getDurationEstimated()) || is_null($action->getDurationReal()))
  118.         {
  119.             $respectDuree="";
  120.         }
  121.         
  122.         $editable="0";
  123.         
  124.         //if(in_array($user,[$action->getResponsible(),$action->getCreatedBy()]) ) $editable="1";
  125.         if($user==$action->getResponsible() ) $editable="1";
  126.         $actionIsEE="0";
  127.         if($action->getTypeAction()=="AE/AI") $actionIsEE="1";
  130.         $delete_btn="";
  131.         $annuler_btn="";
  132.         if($action->getCreatedBy()==$user)
  133.         {
  134.             $urlDelete= "/action/delete/".$action->getId();
  135.             $delete_btn= "<div class='type_p'>
  136.             <button class='btn bg-delete' 
  137.             onclick='open_delete_dialog_without_from(\"".$urlDelete."\",\"".$action->getRef()."\" )'
  138.             onmouseover='showToolTip(\"Supprimer l'action\",\"span_delete_".$action->getId()."\")' 
  139.             onmouseout='hideToolTip(\"span_delete_".$action->getId()."\")'
  140.             >
  141.             <i class='fa fa-trash-o'></i>
  142.             </button>
  143.             <span class='span_tootltip span_priority' style='left:-80px' id='span_delete_".$action->getId()."'></span>
  144.             </div> ";
  145.             if($action->getStatus() === StatusEnum::STATUS_ANNULE) {
  146.                 $urlAnnuler="/action/activate/".$action->getId();
  147.                 $annuler_btn="<div class='type_p'>
  148.                 <a class='btn bg-activate' 
  149.                 data-toggle='modal' data-target='#myModal'
  150.                 href='/action/activate/".$action->getId()."'
  151.                 onmouseover='showToolTip(\"Réactiver l'action\",\"span_cancel_".$action->getId()."\")' 
  152.                 onmouseout='hideToolTip(\"span_cancel_".$action->getId()."\")'
  153.                 >
  154.                 <i class='fa fa-check'></i>
  155.                 </a> 
  156.                 <span class='span_tootltip span_priority' style='left:-60px' id='span_cancel_".$action->getId()."'></span>
  157.                 </div>";
  158.             }else {
  159.                 $urlAnnuler="/action/annuler/".$action->getId();
  160.                 $annuler_btn="<div class='type_p'>
  161.                 <button class='btn bg-annuler' 
  162.                 onclick='open_annuler_dialog_without_from(\"".$urlAnnuler."\",\"".$action->getRef()."\" )'
  163.                 onmouseover='showToolTip(\"Annuler l'action\",\"span_cancel_".$action->getId()."\")' 
  164.                 onmouseout='hideToolTip(\"span_cancel_".$action->getId()."\")'
  165.                 >
  166.                 <i class='fa fa-times'></i>
  167.                 </button> 
  168.                 <span class='span_tootltip span_priority' style='left:-60px' id='span_cancel_".$action->getId()."'></span>
  169.                 </div>";
  170.             }
  171.             
  172.         }
  173.         
  174.        //validation
  175.         $validation='';
  176.         if(!empty($action->getValidator()) && $action->getStatus()==StatusEnum::STATUS_PLANNED)
  177.         {
  178.             if($action->getValidator()==$user)
  179.             {
  180.                 $validation="<a class='btn btn-xs btn-danger pull-right' style='margin-left:5px' href='/action/".$action->getId()."/validation'>Valider cette action</a> ";
  181.             }
  182.         }
  183.         
  184.         $ftlrActionTypePlanInd="1";
  185.         if(!empty($action->getActionPlan()))
  186.         {
  187.             $ftlrActionTypePlanInd="0";
  188.         }
  191.         $ftlrProcessus=[];
  192.         $list_processus='';
  193.         foreach($action->getProcessus() as $proc)
  194.         {
  195.             $ftlrProcessus[]=$proc->getTitle();
  196.             $list_processus.="<span class='badge bg-color-darken'>".$proc->getTitle()."</span>";
  197.         }
  199.        
  200.         
  201.         $durationEstimated = strval($action->getDurationEstimated());
  202.         
  204.         
  205.         $budgetEstimated="<span class='badge bg-color-darken'>".$action->getBudgetEstimated()." €</span>";
  207.         $img_file="";
  208.         $pdf = '';
  209.         if($action->getPdfFile()) {
  210.             $pdf = strval($action->getId());
  211.             $extension_array = explode('.', $action->getPdfFile());
  212.             $extension =  strtolower($extension_array[1]);
  213.             if($extension === 'png' || $extension === 'jpg') {
  214.                 $img_file='/img/icons/img.png';
  215.             }else if($extension==='doc' || $extension==='docx') {
  216.                 $img_file='/img/icons/doc.png';
  217.             }else if($extension==='ppt') {
  218.                 $img_file='/img/icons/ppt.png';
  219.             }else if (str_contains($extension,'xl')) {
  220.                 $img_file='/img/icons/xl.png';
  221.             }else {
  222.                 $img_file='/img/icons/txt.png';
  223.             }
  224.         } else {
  225.             $pdf = "";
  226.         }
  228.         $ftlrDomaine=[];
  229.         
  230.         if(!empty($action->getDomaines()))
  231.         {
  232.             foreach($action->getDomaines() as $domain)
  233.             {
  234.                 $ftlrDomaine[]=$domain->getTitle();
  235.             }
  236.         }
  237.         $ftlrSource='';
  238.         if($action->getSource()) $ftlrSource = $action->getSource()->getId();
  241. //        if($action->getCreatedBy()->getEntity()) $entity = $action->getCreatedBy()->getEntity()->getNom();
  242.         $entity = "";
  243.         foreach($action->getCompanyEntities() as $ce)
  244.         {
  245.             $entity.="<span class='badge bg-color-darken' style='margin-right: 5px'>".$ce->getNom()."</span>";
  246.         }
  247.         $ref="";
  248.         if($remove_link) {
  249.             $ref= $action->getRef();
  250.         }else {
  251.             $ref="<a href='/action/".$action->getId()."/edit' class='ref'>".$action->getRef()."</a>";
  252.         }
  254.         $comment_color = "#CEDCEF";
  255.         if($action->hasComment()) $comment_color = "#3466A8";
  256.         $comment = "<a href='/action/".$action->getId()."/comment' data-toggle='modal' data-target='#myModal' style='color:".$comment_color."' id='action_comment_".$action->getId()."'><i class='fa fa-comments' style='font-size:20px'></i></a>";
  258.         return [
  259.             "ftlrDomaine" => $ftlrDomaine,
  260.             "ftlrType" => $action->getType()->getId(),
  261.             "ftlrCategorie" => $action->getCategory(),
  262.             "ftlrSource" => $ftlrSource,
  263.             "ftlrProcessus" =>$ftlrProcessus,
  264.             "ftlrEnCharge" => $action->getResponsible()->getId(),
  265.             "responsibleName" => $action->getResponsible()->getFullName(),
  266.             "ftlrActionTypePlanInd" => $ftlrActionTypePlanInd,
  267.             "ftlrActionHasEfficacite" => strval($action->hasActionEE()),
  268.             "ftlrActionEfficacite" => "0",
  269.             "ftlrTypePa" =>  strval($action->getTypePa()),
  270.             "DT_RowId"=> $action->getId(),
  271.             "ref" => $ref,
  272.             "priorite" => $action->getPriority(),
  273.             "dateDebut" => $action->getDateStartReal(true),
  274.             "avancement" => $action->getProgress()."%",
  275.             "datefin" => $action->getDateEndReal(true),
  276.             "duree" => strval($action->getDurationReal()),
  277.             "cout" => strval($action->getBudgetReal()),
  278.             "respectBudget" => $respectBudget,
  279.             "statut" => $action->getStatus(),
  280.             "respectDuree" => $respectDuree,
  281.             "respectDelais" => $respectDelais,
  282.             "joursRetard" => '<span class="chiffre">'.$joursRetard.'</span>',
  283.             "nbrReport" =>'<span class="chiffre">'. $action->getReportNbr().'</span>',
  284.             "efficacite" => strval($action->getEfficient()),
  285.             "dateEnregistrement" => $action->getCreatedAt()->format('d/m/Y'),
  286.             "resume" => '<span class="border-text">'.$action->getTitle().'</span>',
  287.             "desc" => strval($action->getDescription()),
  288.             "commentaires" => "",
  289.             "periodePrevisionelle" => $action->getDateEndEstimated(true),
  290.             "dureeEstimee" => strval($durationEstimated),
  291.             "budgetPrevisionel" => strval($budgetEstimated), 
  292.             "rapport" => $pdf,
  293.             "img_file"=>$img_file,
  294.             "action" => $delete_btn,
  295.             "actionAnnuler" => $annuler_btn,
  296.             "editable"=>$editable,
  297.             "actionIsEE"=>$actionIsEE,
  298.             "validation"=> $validation,
  299.             "list_processus"=>$list_processus,
  300.             "affectedBy"=>$action->getCreatedBy()->getFullName()." / ".$action->getCreatedBy()->getFonction(),
  301.             "entity"=> $entity,
  302.             "periodicity"=>$action->getPeriodicity() ? $action->getPeriodicity():'',
  303.             "comment"=>$comment
  304.         ];
  305.     }
  307.     public function transformAll(User $user,$actionPlan='',$remove_link='',$startDate='',$endDate='')
  308.     {
  309.        
  311.         $actions = $this->findActions($actionPlan,true,$startDate,$endDate);
  312.         
  313.         //dd($actions);
  314.         $actionsArray = ['data' => []];
  316.         $actionsList=[];
  317.         foreach ($actions as $action) 
  318.         {
  319.             
  320.             $actionsList[]=$action;
  321.             if($action->hasActionEE())
  322.             {
  323.                 $actionsList[]=$action->getAction();
  324.             }
  325.         }
  326.         
  327.         foreach ($actionsList as $action) 
  328.         {
  329.             
  330.             $actionsArray['data'][] = $this->transform($action,$user, $remove_link);
  331.         }
  333.         return $actionsArray;
  334.     }
  336.     public function findNbrAction(
  337.         $processus ='',
  338.         $actionPlan='',
  339.         $status='',
  340.         $lateCondition="",
  341.         $priority='',
  342.         $budgetCondition='',
  343.         $month='',
  344.         $cumultative=true,
  345.         $source='',
  346.         $type='',
  347.         $min_progress='',
  348.         $max_progress='',
  349.         $has_started='',
  350.         $durationCondition=''
  351.         )
  352.     {
  353.         $qb =  $this->createQueryBuilder('a')->select('COUNT(a.id)');
  354.         if(!empty($processus))
  355.         {
  356.             if($processus->getId() > 0)
  357.             {
  358.                 $qb ->where(':processus MEMBER OF a.processus')
  359.                     ->setParameter("processus", $processus);
  360.             }
  361.         }
  362.         if(!empty($actionPlan))
  363.         {
  364.             if($actionPlan=="include")
  365.             {
  366.                 $qb ->andWhere('a.actionPlan is NULL');
  367.             }
  368.             elseif($actionPlan=="not_include")
  369.             {
  370.                 $qb ->andWhere('a.actionPlan is NOT NULL');
  371.             }
  372.             elseif($actionPlan->getId()>0)
  373.             {
  374.                 $qb ->andWhere('a.actionPlan = :actionPlan')
  375.                     ->setParameter('actionPlan', $actionPlan);
  376.             }
  377.         }
  378.         
  379.         if(!empty($status))
  380.         {
  381.             
  382.             $qb ->andWhere('a.status = :status')
  383.                 ->setParameter('status', $status);
  384.         }
  385.         if(!empty($lateCondition))
  386.         {
  387.             $condition = '';
  388.             if($lateCondition=='late')
  389.             {
  390.                 
  391.                 $condition = 'a.dateEndEstimated < a.dateEndReal';
  392.                 if($status != StatusEnum::STATUS_CLOSED)
  393.                 {
  394.                     $condition = 'a.dateEndEstimated < NOW()';
  395.                 }
  396.             }
  397.             else
  398.             {
  399.                 $condition = 'a.dateEndEstimated >= a.dateEndReal';
  400.                 if($status != StatusEnum::STATUS_CLOSED)
  401.                 {
  402.                     $condition = 'a.dateEndEstimated >= NOW()';
  403.                 }
  404.             }
  405.             $qb ->andWhere($condition);
  406.             
  407.         }
  408.         if(!empty($priority))
  409.         {
  410.             $qb ->andWhere('a.priority = :priority')
  411.                 ->setParameter('priority', $priority);
  412.         }
  414.         if(!empty($budgetCondition))
  415.         {
  416.             if($budgetCondition=='over')
  417.             {
  418.                 $qb ->andWhere('a.budgetEstimated < a.budgetReal');
  419.             }
  420.             else
  421.             {
  422.                 $qb ->andWhere('a.budgetEstimated >= a.budgetReal');
  423.             }
  424.             
  425.         }
  427.         if($month > 0)
  428.         {
  429.             $month_condition = 'MONTH(a.createdAt) = :month ';
  430.             if($cumultative)
  431.             {
  432.                 $month_condition = 'MONTH(a.createdAt) <= :month ';
  433.             }
  434.             $qb ->andWhere($month_condition)
  435.                 ->setParameter('month', $month);
  436.             
  437.         }
  439.         if(!empty($source))
  440.         {
  441.             
  442.             $qb ->andWhere('a.source = :source')
  443.                 ->setParameter('source', $source);
  444.         }
  446.         if(!empty($type))
  447.         {
  448.             
  449.             $qb ->andWhere('a.type = :type')
  450.                 ->setParameter('type', $type);
  451.         }
  453.         if($max_progress > 0)
  454.         {
  455.             $qb ->andWhere('a.progress >= :min_progress')
  456.                 ->andWhere('a.progress < :max_progress')
  457.                 ->setParameter('min_progress', $min_progress)
  458.                 ->setParameter('max_progress', $max_progress);
  459.         }
  461.         if(!empty($has_started))
  462.         {
  463.             $condition = '';
  464.             if($has_started=='no')
  465.             {
  466.                 
  467.                 $condition = 'a.dateStartEstimated < NOW()';
  468.             }
  469.             else
  470.             {
  471.                 $condition = 'a.dateStartEstimated < NOW()';
  472.             }
  473.             $qb ->andWhere($condition);
  474.         }
  476.         if(!empty($durationCondition))
  477.         {
  478.             $condition = '';
  479.             if($durationCondition=='over')
  480.             {
  481.                 $condition = 'a.durationEstimated < a.durationReal';
  482.             }
  483.             else
  484.             {
  485.                 $condition = 'a.durationEstimated >= a.durationReal';
  486.             }
  487.             $qb ->andWhere($condition);
  488.             
  489.         }
  490.         //draft
  491.         /*
  492.         $qb ->andWhere("a.status  <> :draftstatus")
  493.             ->setParameter('draftstatus', StatusEnum::STATUS_DRAFT);*/
  494.         //$qb ->andWhere("a.validated = 1");
  496.         //dump($qb->getQuery()->getSQL());
  497.         $nbrActions=$qb->getQuery()->getOneOrNullResult();
  498.         return $nbrActions[1];
  499.     }
  501.     public function findSumNbrReport($processus ='',$actionPlan='')
  502.     {
  503.         $qb =  $this->createQueryBuilder('a')->select('SUM(a.reportNbr)');
  504.         if(!empty($processus))
  505.         {
  506.             if($processus->getId() > 0)
  507.             {
  508.                 $qb ->where(':processus MEMBER OF a.processus')
  509.                     ->setParameter("processus", $processus);
  510.             }
  511.         }
  512.         if(!empty($actionPlan))
  513.         {
  514.             if($actionPlan->getId()>0)
  515.             {
  516.                 $qb ->andWhere('a.actionPlan = :actionPlan')
  517.                     ->setParameter('actionPlan', $actionPlan);
  518.             }
  519.         }
  520.         //draft
  521.         // $qb ->andWhere("a.status  <> :draftstatus")
  522.         //     ->setParameter('draftstatus', StatusEnum::STATUS_DRAFT);
  523.         $nbrReport = $qb->getQuery()->getOneOrNullResult();
  524.         return $nbrReport[1];
  525.     }
  527.     public function findByCustom($actionPlan='',$status='',$priority='',$lateCondition='',$limit=0)
  528.     {
  529.         $qb =  $this->createQueryBuilder('a');
  530.         if(!empty($actionPlan))
  531.         {
  532.             if($actionPlan->getId() > 0)
  533.             {
  534.                 $qb ->where('a.actionPlan = :actionPlan')
  535.                     ->setParameter("actionPlan", $actionPlan);
  536.             }
  537.         }
  538.         if(!empty($status))
  539.         {
  540.             $qb ->andWhere('a.status = :status')
  541.                 ->setParameter('status', $status);
  542.         }
  543.         if(!empty($priority))
  544.         {
  545.             $qb ->andWhere('a.priority = :priority')
  546.                 ->setParameter('priority', $priority);
  547.         }
  548.         if(!empty($lateCondition))
  549.         {
  550.             $condition = '';
  551.             if($lateCondition=='late')
  552.             {
  553.                 
  554.                 $condition = 'a.dateEndEstimated < a.dateEndReal';
  555.                 if($status != StatusEnum::STATUS_CLOSED)
  556.                 {
  557.                     $condition = 'a.dateEndEstimated < NOW()';
  558.                 }
  559.             }
  560.             else
  561.             {
  562.                 $condition = 'a.dateEndEstimated >= a.dateEndReal';
  563.                 if($status != StatusEnum::STATUS_CLOSED)
  564.                 {
  565.                     $condition = 'a.dateEndEstimated >= NOW()';
  566.                 }
  567.             }
  568.             $qb ->andWhere($condition);
  569.             
  570.         }
  572.         //draft
  573.         // $qb ->andWhere("a.status  <> :draftstatus")
  574.         //     ->setParameter('draftstatus', StatusEnum::STATUS_DRAFT);
  576.             
  577.         if($limit> 0)
  578.         {
  579.             $qb->setMaxResults($limit);
  580.         }
  583.         $qb->orderBy('a.createdAt', 'DESC');
  585.         //dump($qb->getQuery()->getSQL());
  586.         return $qb->getQuery()->getResult();
  587.     }
  589.     public function findActions($actionPlan='',$withoutAcEE=true,$startDate='',$endDate='')
  590.     {
  591.         $qb =  $this->createQueryBuilder('a');
  592.         if(!empty($actionPlan))
  593.         {
  594.             if($actionPlan->getId() > 0)
  595.             {
  596.                 $qb ->where('a.actionPlan = :actionPlan')
  597.                     ->setParameter("actionPlan", $actionPlan);
  598.             }
  599.         }
  600.         if($withoutAcEE)
  601.         {
  602.             $qb ->andWhere('a.typeAction != :typeAction')
  603.                 ->setParameter("typeAction", 'AE/AI');
  604.         }
  605.         if($startDate) {
  606.             $qb->andWhere('a.dateEndEstimated >= :startDate ')
  607.             ->setParameter("startDate", $startDate);
  608.         }
  609.         if($endDate) {
  610.             $qb->andWhere('a.dateEndEstimated < :endDate ')
  611.             ->setParameter("endDate", $endDate);
  612.         }
  613.         //filtrage par entité
  614.         $selectedEntity = $this->selectedEntityService->getSelectedEntity();
  615.         if ($selectedEntity) {
  616.             $qb->join('a.companyEntities', 'ce')
  617.                 ->andWhere(':entity MEMBER OF a.companyEntities')
  618.                 ->setParameter('entity', $selectedEntity);
  619.         } else {
  620.             $user = $this->security->getUser();
  621.             $entities = $user->getEntities();
  622.             if (count($entities) > 0) {
  623.                 $qb->join('a.companyEntities', 'ce2')
  624.                     ->andWhere('ce2 IN (:entities)')
  625.                     ->setParameter('entities', $entities);
  626.             }
  627.         }
  629.         $qb->orderBy('a.createdAt', 'DESC');
  631.         return $qb->getQuery()->getResult();
  632.     }
  637.     
  638. }