/** * Records Engine * * @param Organisationunit $organisationUnit * @param Form $forms * @param Field $fields * @param $reportType * @param $withLowerLevels * @return mixed */ private function recordsEngine(Organisationunit $organisationUnit, Form $forms, Field $fields, $reportType, $withLowerLevels) { $entityManager = $this->getDoctrine()->getManager(); $resourceTableName = "_resource_all_fields"; 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 R.firstname, R.middlename, R.surname, R.profession, T.coursename, T.courselocation, T.sponsor, T.startdate, T.enddate, R.level5_facility "; $query .= "FROM hris_record_training T "; $query .= "INNER JOIN hris_record as V on V.id = T.record_id "; $query .= "INNER JOIN " . $resourceTableName . " as R on R.instance = V.instance "; $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 .= "ORDER BY R.firstname ASC"; } else { //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 R.firstname, R.middlename, R.surname, R.profession, H.history, H.reason, H.startdate, R.level5_facility "; $query .= "FROM hris_record_history H "; $query .= "INNER JOIN hris_record as V on V.id = H.record_id "; $query .= "INNER JOIN " . $resourceTableName . " as R on R.instance = V.instance "; $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 .= " ORDER BY R.firstname ASC"; } //echo $query;exit; //get the records $report = $entityManager->getConnection()->executeQuery($query)->fetchAll(); return $report; }
/** * 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; }
/** * Add uniqueRecordFields * * @param Field $uniqueRecordFields * @return Form */ public function addUniqueRecordField(Field $uniqueRecordFields) { $this->uniqueRecordFields[$uniqueRecordFields->getId()] = $uniqueRecordFields; $uniqueRecordFields->addUniqueRecordForm($this); return $this; }
/** * Add field * * @param Field $field * @return InputType */ public function addField(Field $field) { $this->field[$field->getId()] = $field; return $this; }
/** * Add childField * * @param Field $childField * @return Field */ public function addChildField(Field $childField) { $this->childField[$childField->getId()] = $childField; return $this; }