/** * Aggregation Engine * * @param Organisationunit $organisationUnit * @param ArrayCollection $forms * @param Field $fields * @param ArrayCollection $organisationunitGroup * @param $withLowerLevels * @param Field $fieldsTwo * @return mixed */ public function aggregationEngine(Organisationunit $organisationUnit, ArrayCollection $forms, Field $fields, ArrayCollection $organisationunitGroup, $withLowerLevels, Field $fieldsTwo) { $entityManager = $this->getDoctrine()->getManager(); $selectedOrgunitStructure = $entityManager->getRepository('HrisOrganisationunitBundle:OrganisationunitStructure')->findOneBy(array('organisationunit' => $organisationUnit->getId())); //get the list of options to exclude from the reports $fieldOptionsToExclude = $entityManager->getRepository('HrisFormBundle:FieldOption')->findBy(array('skipInReport' => TRUE)); //remove the value which have field option set to exclude in reports //but check to see if the first field is in the list of fields to remove. foreach ($fieldOptionsToExclude as $key => $fieldOptionToExclude) { if ($fieldOptionToExclude->getField()->getId() == $fields->getId()) { unset($fieldOptionsToExclude[$key]); } } //create the query to aggregate the records from the static resource table //check if field one is calculating field so to create the sub query $resourceTableName = ResourceTable::getStandardResourceTableName(); if ($fields->getIsCalculated()) { // @todo implement calculated fields feature and remove hard-coding } $query = "SELECT ResourceTable." . $fields->getName(); if ($fieldsTwo->getId() != $fields->getId()) { $query .= " , ResourceTable." . $fieldsTwo->getName() . " , count(ResourceTable." . $fieldsTwo->getName() . ") as total"; } else { $query .= " , count(ResourceTable." . $fields->getName() . ") as total"; } $query .= " FROM " . $resourceTableName . " ResourceTable inner join hris_organisationunit as Orgunit ON Orgunit.id = ResourceTable.organisationunit_id INNER JOIN hris_organisationunitstructure AS Structure ON Structure.organisationunit_id = ResourceTable.organisationunit_id"; $query .= " WHERE ResourceTable." . $fields->getName() . " is not NULL "; if ($fieldsTwo->getId() != $fields->getId()) { $query .= " AND ResourceTable." . $fieldsTwo->getName() . " is not NULL"; } //filter the records by the selected form and facility $query .= " AND ResourceTable.form_id IN ("; foreach ($forms as $form) { $query .= $form->getId() . " ,"; } //remove the last comma in the query $query = rtrim($query, ",") . ")"; if ($withLowerLevels) { $query .= " AND Structure.level" . $selectedOrgunitStructure->getLevel()->getLevel() . "_id=" . $organisationUnit->getId(); $query .= " AND Structure.level_id >= "; $query .= "(SELECT hris_organisationunitstructure.level_id FROM hris_organisationunitstructure WHERE hris_organisationunitstructure.organisationunit_id=" . $organisationUnit->getId() . " )"; } else { $query .= " AND ResourceTable.organisationunit_id=" . $organisationUnit->getId(); } //filter the records if the organisation group was choosen if ($organisationunitGroup != NULL) { $groups = NULL; foreach ($organisationunitGroup as $organisationunitGroups) { $groups .= "'" . $organisationunitGroups->getName() . "',"; } //remove the last comma in the query $groups = rtrim($groups, ","); if ($groups != NULL) { $query .= " AND (ResourceTable.type IN (" . $groups . ") OR ownership IN (" . $groups . ") )"; } //OR administrative IN (".$groups.") } //remove the record which have field option set to exclude in reports foreach ($fieldOptionsToExclude as $key => $fieldOptionToExclude) { $query .= " AND ResourceTable." . $fieldOptionToExclude->getField()->getName() . " != '" . $fieldOptionToExclude->getValue() . "'"; } $query .= " GROUP BY ResourceTable." . $fields->getName(); if ($fieldsTwo->getId() != $fields->getId()) { $query .= " , ResourceTable." . $fieldsTwo->getName(); } $query .= " ORDER BY ResourceTable." . $fields->getName(); if ($fieldsTwo->getId() != $fields->getId()) { $query .= " , ResourceTable." . $fieldsTwo->getName(); } //get the records $report = $entityManager->getConnection()->executeQuery($query)->fetchAll(); return $report; }