/**
  * Aggregation Engine
  *
  * @param Organisationunit $organisationUnit
  * @param Form $forms
  * @param Field $fields
  * @param $reportType
  * @param $withLowerLevels
  * @return mixed
  */
 private function aggregationEngine(Organisationunit $organisationUnit, Form $forms, Field $fields, $reportType, $withLowerLevels)
 {
     $entityManager = $this->getDoctrine()->getManager();
     //$selectedOrgunitStructure = $entityManager->getRepository('HrisOrganisationunitBundle:OrganisationunitStructure')->findOneBy(array('organisationunit' => $organisationUnit->getId()));
     if ($reportType == "training") {
         //Query all lower levels units from the passed orgunit
         if ($withLowerLevels) {
             $allChildrenIds = "SELECT hris_organisationunitlevel.level ";
             $allChildrenIds .= "FROM hris_organisationunitlevel , hris_organisationunitstructure ";
             $allChildrenIds .= "WHERE hris_organisationunitlevel.id = hris_organisationunitstructure.level_id AND hris_organisationunitstructure.organisationunit_id = " . $organisationUnit->getId();
             $subQuery = "V.organisationunit_id = " . $organisationUnit->getId() . " OR ";
             $subQuery .= " ( L.level >= ( " . $allChildrenIds . ") AND S.level" . $organisationUnit->getOrganisationunitStructure()->getLevel()->getLevel() . "_id =" . $organisationUnit->getId() . " )";
         } else {
             $subQuery = "V.organisationunit_id = " . $organisationUnit->getId();
         }
         //Query all training data and count by start date year
         $query = "SELECT date_part('year',startdate) as data, count(date_part('year',startdate)) as total ";
         $query .= "FROM hris_record_training T ";
         $query .= "INNER JOIN hris_record as V on V.id = T.record_id ";
         $query .= "INNER JOIN hris_organisationunitstructure as S on S.organisationunit_id = V.organisationunit_id ";
         $query .= "INNER JOIN hris_organisationunitlevel as L on L.id = S.level_id ";
         $query .= "WHERE V.form_id = " . $forms->getId();
         $query .= " AND (" . $subQuery . ") ";
         $query .= " GROUP BY date_part('year',startdate) ";
         $query .= "ORDER BY data ASC";
     } else {
         if ($fields->getInputType()->getName() == "Select") {
             //Query all lower levels units from the passed orgunit
             if ($withLowerLevels) {
                 $allChildrenIds = "SELECT hris_organisationunitlevel.level ";
                 $allChildrenIds .= "FROM hris_organisationunitlevel , hris_organisationunitstructure ";
                 $allChildrenIds .= "WHERE hris_organisationunitlevel.id = hris_organisationunitstructure.level_id AND hris_organisationunitstructure.organisationunit_id = " . $organisationUnit->getId();
                 $subQuery = "V.organisationunit_id = " . $organisationUnit->getId() . " OR ";
                 $subQuery .= " ( L.level >= ( " . $allChildrenIds . ") AND S.level" . $organisationUnit->getOrganisationunitStructure()->getLevel()->getLevel() . "_id =" . $organisationUnit->getId() . " )";
             } else {
                 $subQuery = "V.organisationunit_id = " . $organisationUnit->getId();
             }
             //Query all history data and count by field option
             $query = "SELECT H.history as data, count (H.history) as total ";
             $query .= "FROM hris_record_history H ";
             $query .= "INNER JOIN hris_record as V on V.id = H.record_id ";
             $query .= "INNER JOIN hris_organisationunitstructure as S on S.organisationunit_id = V.organisationunit_id ";
             $query .= "INNER JOIN hris_organisationunitlevel as L on L.id = S.level_id ";
             $query .= "WHERE V.form_id = " . $forms->getId() . " AND H.field_id = " . $fields->getId();
             $query .= " AND (" . $subQuery . ") ";
             $query .= " GROUP BY H.history ";
             $query .= " ORDER BY data ASC";
         } else {
             //For other fields which are not combo box, report is based on history dates
             //$subQuery="select Distinct(T.id),T.instance,date_part('year', startdate) from hris_history T, hris_values V where T.instance= V.instance AND V.form_id =".$forms->getId()." AND T.history_type_id = ".$fields->getId()."AND V.orgunit_id in (".$orgunitsid." )";
             //$query = "SELECT F.date_part as data, count (F.date_part) FROM (".$subQuery.") as F GROUP BY F.date_part";
             //Query all lower levels units from the passed orgunit
             if ($withLowerLevels) {
                 $allChildrenIds = "SELECT hris_organisationunitlevel.level ";
                 $allChildrenIds .= "FROM hris_organisationunitlevel , hris_organisationunitstructure ";
                 $allChildrenIds .= "WHERE hris_organisationunitlevel.id = hris_organisationunitstructure.level_id AND hris_organisationunitstructure.organisationunit_id = " . $organisationUnit->getId();
                 $subQuery = "V.organisationunit_id = " . $organisationUnit->getId() . " OR ";
                 $subQuery .= " ( L.level >= ( " . $allChildrenIds . ") AND S.level" . $organisationUnit->getOrganisationunitStructure()->getLevel()->getLevel() . "_id =" . $organisationUnit->getId() . " )";
             } else {
                 $subQuery = "V.organisationunit_id = " . $organisationUnit->getId();
             }
             //Query all history data and history year
             $query = "SELECT date_part('year',startdate) as data, count(date_part('year',startdate)) as total ";
             $query .= "FROM hris_record_history H ";
             $query .= "INNER JOIN hris_record as V on V.id = H.record_id ";
             $query .= "INNER JOIN hris_organisationunitstructure as S on S.organisationunit_id = V.organisationunit_id ";
             $query .= "INNER JOIN hris_organisationunitlevel as L on L.id = S.level_id ";
             $query .= "WHERE V.form_id = " . $forms->getId() . " AND H.field_id = " . $fields->getId();
             $query .= " AND (" . $subQuery . ") ";
             $query .= " GROUP BY date_part('year',startdate) ";
             $query .= " ORDER BY data ASC";
         }
     }
     //echo $query;exit;
     //get the records
     $report = $entityManager->getConnection()->executeQuery($query)->fetchAll();
     return $report;
 }