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