/** * 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; }