private function getDateConstraint($query, $from, $to)
 {
     $to2 = $this->reportUtility->getTo($from, $to);
     $constDate[] = $query->logicalAnd($query->greaterThanOrEqual('actions.date', $from), $query->lessThan('actions.date', $to2));
     //$constDate[]=$query->logicalAnd($query->greaterThanOrEqual('treatments.startDate',$from),$query->lessThan('treatments.startDate',$to2));
     return $query->logicalOr($constDate);
 }
 /**
  * @param \DateTime $from
  * @param \DateTime $to
  * @param integer $reportType   
  * @return void
  */
 public function reportAction(\DateTime $from = null, \DateTime $to = null, $reportType = 0)
 {
     if ($from) {
         $col = function ($item) {
             return $item['firstname'] . ' ' . $item['lastname'] . '(' . \DLigo\Animaltool\Domain\Model\User::teamIDString($item['teamID']) . ')';
         };
         $source = function ($item) {
             if ($item['web']) {
                 $label = $this->translator->translateById('report.source.web', array(), NULL, NULL, 'Main', 'DLigo.Animaltool');
             } else {
                 $label = $this->translator->translateById('report.source.app', array(), NULL, NULL, 'Main', 'DLigo.Animaltool');
             }
             return $label;
         };
         $ownerStat = function ($item) {
             if ($item['ownerStat']) {
                 $label = $this->translator->translateById('report.withOwner', array(), NULL, NULL, 'Main', 'DLigo.Animaltool');
             } else {
                 $label = $this->translator->translateById('report.withoutOwner', array(), NULL, NULL, 'Main', 'DLigo.Animaltool');
             }
             return $label;
         };
         $tagType = function ($item) {
             if ($item['tagType'] == 2) {
                 $label = $this->translator->translateById('report.tagType.2', array(), NULL, NULL, 'Main', 'DLigo.Animaltool');
             } elseif ($item['tagType'] == 1) {
                 $label = $this->translator->translateById('report.tagType.1', array(), NULL, NULL, 'Main', 'DLigo.Animaltool');
             } else {
                 $label = $this->translator->translateById('report.tagType.0', array(), NULL, NULL, 'Main', 'DLigo.Animaltool');
             }
             return $label;
         };
         $gender = function ($item) {
             $label = $this->translator->translateById('report.gender.' . strtolower($item['gender']), array(), NULL, NULL, 'Main', 'DLigo.Animaltool');
             return $label;
         };
         $dummy = new \DLigo\Animaltool\Domain\Model\Animal();
         $stats = $dummy->getStayStatuses();
         $stay = function ($item) use($stats) {
             return $stats[$item['stayStatus']];
         };
         $stats = $dummy->getTherapyStatuses();
         $therapy = function ($item) use($stats) {
             return $stats[$item['therapyStatus']];
         };
         if ($reportType == 1) {
             for ($i = 100; $i <= 105; $i++) {
                 $report[$i] = true;
             }
             $typename = 'simple';
         } elseif ($reportType == 0) {
             for ($i = 200; $i <= 218; $i++) {
                 $report[$i] = true;
             }
             $typename = 'advanced';
         } elseif ($reportType == 2) {
             for ($i = 300; $i <= 302; $i++) {
                 $report[$i] = true;
             }
             $typename = 'lists';
         } else {
             $report[$reportType] = true;
         }
         $tables = array();
         if (!empty($report[100])) {
             $counts = $this->reportUtility->countAllSpeciesAndSource($from, $to, $this->session->getLocation());
             $table = $this->reportUtility->buildReportTable($counts, 'species', 'web', null, $source, true);
             $this->view->assign('species_source', $table);
             $name = 'species-source';
             $tables[] = $table;
             $this->view->assign('tables', $tables);
         }
         if (!empty($report[101])) {
             $counts = $this->reportUtility->countAllSpeciesAndOwnerStat($from, $to, $this->session->getLocation());
             $table = $this->reportUtility->buildReportTable($counts, 'species', 'ownerStat', null, $ownerStat, true);
             $this->view->assign('species_ownerstat', $table);
             $name = 'species-ownerstat';
             $tables[] = $table;
             $this->view->assign('tables', $tables);
         }
         if (!empty($report[102])) {
             $counts = $this->reportUtility->countAllSpeciesAndGender($from, $to, $this->session->getLocation());
             $table = $this->reportUtility->buildReportTable($counts, 'species', 'gender', null, $gender, true);
             $this->view->assign('species_gender', $table);
             $name = 'species-gender';
             $tables[] = $table;
             $this->view->assign('tables', $tables);
         }
         if (!empty($report[103])) {
             $counts = $this->reportUtility->countAllSpeciesAndGender($from, $to, $this->session->getLocation(), 'th.name=\'Sterilisation\'', true);
             $table = $this->reportUtility->buildReportTable($counts, 'species', 'gender', null, $gender, true);
             $this->view->assign('species_gender_sterilisation', $table);
             $name = 'species-gender-sterilisation';
             $tables[] = $table;
             $this->view->assign('tables', $tables);
         }
         if (!empty($report[104])) {
             $counts = $this->reportUtility->countAllSpeciesAndGender($from, $to, $this->session->getLocation(), '(th.name=\'Rabies vaccine\' OR th.name=\'Other vaccine\')', true);
             $table = $this->reportUtility->buildReportTable($counts, 'species', 'gender', null, $gender, true);
             $this->view->assign('species_gender_vaccination', $table);
             $name = 'species-gender-vaccination';
             $tables[] = $table;
             $this->view->assign('tables', $tables);
         }
         if (!empty($report[105])) {
             $counts = $this->reportUtility->countAllSpeciesAndStayStatus($from, $to, $this->session->getLocation(), '(a.stayStatus=' . Animal::THERAPY . ' OR a.stayStatus=' . Animal::RELEASED . ' OR a.stayStatus=' . Animal::EXTERNAL . ')');
             $table = $this->reportUtility->buildReportTable($counts, 'species', 'stayStatus', null, $stay, true);
             $this->view->assign('species_staystatus', $table);
             $name = 'species-staystatus-simple';
             $tables[] = $table;
             $this->view->assign('tables', $tables);
         }
         if (!empty($report[200])) {
             $counts = $this->reportUtility->countAllSpeciesAndTeam($from, $to, $this->session->getLocation());
             $table = $this->reportUtility->buildReportTable($counts, 'species', 'teamID', null, $col);
             $this->view->assign('species_team', $table);
             $name = 'species-team';
             $tables[] = $table;
             $this->view->assign('tables', $tables);
         }
         if (!empty($report[201])) {
             $counts = $this->reportUtility->countAllSpeciesAndTeam($from, $to, $this->session->getLocation(), "a.owner IS NOT NULL");
             $table = $this->reportUtility->buildReportTable($counts, 'species', 'teamID', null, $col);
             $this->view->assign('species_team_owner', $table);
             $name = 'species-team-owner';
             $tables[] = $table;
             $this->view->assign('tables', $tables);
         }
         if (!empty($report[202])) {
             $counts = $this->reportUtility->countAllSpeciesAndTeam($from, $to, $this->session->getLocation(), "a.owner IS NULL");
             $table = $this->reportUtility->buildReportTable($counts, 'species', 'teamID', null, $col);
             $this->view->assign('species_team_noowner', $table);
             $name = 'species-team-noowner';
             $tables[] = $table;
             $this->view->assign('tables', $tables);
         }
         if (!empty($report[203])) {
             $counts = $this->reportUtility->countAllSpeciesAndTeam($from, $to, $this->session->getLocation(), "a.stayStatus=" . Animal::RELEASED, true);
             $table = $this->reportUtility->buildReportTable($counts, 'species', 'teamID', null, $col);
             $this->view->assign('species_team_done', $table);
             $name = 'species-team-done';
             $tables[] = $table;
             $this->view->assign('tables', $tables);
         }
         if (!empty($report[204])) {
             $counts = $this->reportUtility->countAllTherapyAndTeam($from, $to, $this->session->getLocation());
             $table = $this->reportUtility->buildReportTable($counts, 'therapy', 'teamID', null, $col);
             $this->view->assign('therapy_team', $table);
             $name = 'therapy-team';
             $tables[] = $table;
             $this->view->assign('tables', $tables);
         }
         if (!empty($report[205])) {
             $counts = $this->reportUtility->countAllTherapyAndTeam($from, $to, $this->session->getLocation(), "a.gender='F'");
             $table = $this->reportUtility->buildReportTable($counts, 'therapy', 'teamID', null, $col);
             $this->view->assign('therapy_team_female', $table);
             $name = 'therapy-team-female';
             $tables[] = $table;
             $this->view->assign('tables', $tables);
         }
         if (!empty($report[206])) {
             $counts = $this->reportUtility->countAllTherapyAndTeam($from, $to, $this->session->getLocation(), "a.gender='M'");
             $table = $this->reportUtility->buildReportTable($counts, 'therapy', 'teamID', null, $col);
             $this->view->assign('therapy_team_male', $table);
             $name = 'therapy-team-male';
             $tables[] = $table;
             $this->view->assign('tables', $tables);
         }
         if (!empty($report[207])) {
             $counts = $this->reportUtility->countAllSpeciesAndStayStatus($from, $to, $this->session->getLocation());
             $table = $this->reportUtility->buildReportTable($counts, 'species', 'stayStatus', null, $stay);
             $this->view->assign('species_staystatus', $table);
             $name = 'species-staystatus';
             $tables[] = $table;
             $this->view->assign('tables', $tables);
         }
         if (!empty($report[208])) {
             $counts = $this->reportUtility->countAllSpeciesAndTherapyStatus($from, $to, $this->session->getLocation());
             $table = $this->reportUtility->buildReportTable($counts, 'species', 'therapyStatus', null, $therapy);
             $this->view->assign('species_therapystatus', $table);
             $name = 'species-therapystatus';
             $tables[] = $table;
             $this->view->assign('tables', $tables);
         }
         /*      if(!empty($report[209])){
                 $counts=$this->reportUtility->countAllSpeciesAndTherapyTeam($from,$to,$this->session->getLocation(),"a.stayStatus=".Animal::OPERATION);
                 $table=$this->reportUtility->buildReportTable($counts,'species','teamID',null,$col);
                 $this->view->assign('species_therapyteam', $table);
                 $name='species-therapyteam';
                 $tables[]=$table;
                 $this->view->assign('tables', $tables);
               };*/
         if (!empty($report[210])) {
             $counts = $this->reportUtility->countAllSpeciesAndTeam($from, $to, $this->session->getLocation(), "a.gender='F'");
             $table = $this->reportUtility->buildReportTable($counts, 'species', 'teamID', null, $col);
             $this->view->assign('species_team_female', $table);
             $name = 'species-team-female';
             $tables[] = $table;
             $this->view->assign('tables', $tables);
         }
         if (!empty($report[211])) {
             $counts = $this->reportUtility->countAllSpeciesAndTeam($from, $to, $this->session->getLocation(), "a.gender='M'");
             $table = $this->reportUtility->buildReportTable($counts, 'species', 'teamID', null, $col);
             $this->view->assign('species_team_male', $table);
             $name = 'species-team-male';
             $tables[] = $table;
             $this->view->assign('tables', $tables);
         }
         if (!empty($report[212])) {
             $counts = $this->reportUtility->countAllTherapyAndTeam($from, $to, $this->session->getLocation(), "s.name='Dog'");
             $table = $this->reportUtility->buildReportTable($counts, 'therapy', 'teamID', null, $col);
             $this->view->assign('therapy_team_dog', $table);
             $name = 'therapy-team-dog';
             $tables[] = $table;
             $this->view->assign('tables', $tables);
         }
         if (!empty($report[213])) {
             $counts = $this->reportUtility->countAllTherapyAndTeam($from, $to, $this->session->getLocation(), "a.gender='F' AND s.name='Dog'");
             $table = $this->reportUtility->buildReportTable($counts, 'therapy', 'teamID', null, $col);
             $this->view->assign('therapy_team_dog_female', $table);
             $name = 'therapy-team-dog-female';
             $tables[] = $table;
             $this->view->assign('tables', $tables);
         }
         if (!empty($report[214])) {
             $counts = $this->reportUtility->countAllTherapyAndTeam($from, $to, $this->session->getLocation(), "a.gender='M' AND s.name='Dog'");
             $table = $this->reportUtility->buildReportTable($counts, 'therapy', 'teamID', null, $col);
             $this->view->assign('therapy_team_dog_male', $table);
             $name = 'therapy-team-dog-male';
             $tables[] = $table;
             $this->view->assign('tables', $tables);
         }
         if (!empty($report[215])) {
             $counts = $this->reportUtility->countAllTherapyAndTeam($from, $to, $this->session->getLocation(), "s.name='Cat'");
             $table = $this->reportUtility->buildReportTable($counts, 'therapy', 'teamID', null, $col);
             $this->view->assign('therapy_team_cat', $table);
             $name = 'therapy-team-cat';
             $tables[] = $table;
             $this->view->assign('tables', $tables);
         }
         if (!empty($report[216])) {
             $counts = $this->reportUtility->countAllTherapyAndTeam($from, $to, $this->session->getLocation(), "a.gender='F' AND s.name='Cat'");
             $table = $this->reportUtility->buildReportTable($counts, 'therapy', 'teamID', null, $col);
             $this->view->assign('therapy_team_cat_female', $table);
             $name = 'therapy-team-cat-female';
             $tables[] = $table;
             $this->view->assign('tables', $tables);
         }
         if (!empty($report[217])) {
             $counts = $this->reportUtility->countAllTherapyAndTeam($from, $to, $this->session->getLocation(), "a.gender='M' AND s.name='Cat'");
             $table = $this->reportUtility->buildReportTable($counts, 'therapy', 'teamID', null, $col);
             $this->view->assign('therapy_team_cat_male', $table);
             $name = 'therapy-team-cat-male';
             $tables[] = $table;
             $this->view->assign('tables', $tables);
         }
         if (!empty($report[218])) {
             $counts = $this->reportUtility->countAllSpeciesAndTagType($from, $to, $this->session->getLocation(), "s.name='Dog' AND ((a.rFID IS NOT NULL AND a.rFID!='') OR (a.earTag IS NOT NULL AND a.earTag!=''))");
             $table = $this->reportUtility->buildReportTable($counts, 'species', 'tagType', null, $tagType);
             $this->view->assign('species_tagtype', $table);
             $name = 'species-tagtype';
             $tables[] = $table;
             $this->view->assign('tables', $tables);
         }
         if (!empty($report[300])) {
             $animals = $this->animalRepository->findByDate($from, $to, $this->session->getLocation());
             $this->view->assign('animals', $animals);
             $name = 'animals';
             $pdf = new \Famelo\PDF\Document('DLigo.Animaltool:report', 'A4');
             $pdf->assign('animals', $animals);
         }
         if (!empty($report[301])) {
             $owners = $this->animalRepository->findByDate($from, $to, $this->session->getLocation(), true);
             $this->view->assign('animals_withowner', $owners);
             $name = 'owners';
         }
         if (!empty($report[302])) {
             $owners = $this->animalRepository->findByDate($from, $to, $this->session->getLocation(), true, array('func' => 'equals', 'name' => 'species.name', 'value' => 'Dog'));
             $this->view->assign('recs', $owners);
             $name = 'recs';
         }
         $this->view->assign('report', true);
     } else {
         $this->view->assign('report', false);
     }
     $this->view->assign('from', $from);
     $this->view->assign('to', $to);
     $this->view->assign('reportType', $reportType);
     if ($this->request->getFormat() == 'csv') {
         $this->response->setHeader('Content-Type', 'text/plain');
         if (count($tables) > 1) {
             $name = $typename;
         }
         $name = 'export_' . $name;
         $name .= '_' . $from->format('Y-m-d');
         if ($to) {
             $name .= '_' . $to->format('Y-m-d');
         }
         if ($this->session->getLocation()) {
             $name .= '_' . $this->session->getLocation()->getName();
         }
         $name .= '.csv';
         $this->response->setHeader('Content-Disposition', 'attachment; filename="' . $name . '"');
     }
     if ($this->request->getFormat() == 'pdf') {
         $pdf->assign('from', $from);
         $pdf->assign('to', $to);
         $pdf->assign('session', $this->session);
         $name = 'report_anmals';
         $name .= '_' . $from->format('Y-m-d');
         if ($to) {
             $name .= '_' . $to->format('Y-m-d');
         }
         if ($this->session->getLocation()) {
             $name .= '_' . $this->session->getLocation()->getName();
         }
         $name .= '.pdf';
         $pdf->download($name);
     }
 }