/**
* 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;
}
/**
* 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;
}
/**
* Remove field
*
* @param Field $field
*/
public function removeField(Field $field)
{
$this->field->removeElement($field);
}
/**
* Remove uniqueRecordFields
*
* @param Field $uniqueRecordFields
*/
public function removeUniqueRecordField(Field $uniqueRecordFields)
{
$this->uniqueRecordFields->removeElement($uniqueRecordFields);
}
/**
* Importing fields.
*/
public function LegacyUpdateFieldsAction($fields)
{
global $refField;
$em = $this->getDoctrine()->getManager();
$fields = json_decode($fields, True);
foreach ($fields as $key => $field) {
//getting the Object if Exist from the Database
$fieldObject = $em->getRepository('HrisFormBundle:Field')->findOneby(array('name' => $field['name']));
if (!empty($fieldObject)) {
$refField[$field['id']] = $fieldObject->getUid();
//print $field['id'];
//print '<br>';
} else {
switch ($field['dataType']['name']) {
case 'String':
$dataType = 'String';
break;
case 'integer':
$dataType = 'Integer';
break;
case 'double':
$dataType = 'Double';
break;
case 'date':
$dataType = 'Date';
break;
default:
$dataType = $field['dataType']['name'];
break;
}
switch ($field['inputType']['name']) {
case 'combo':
$inputType = 'Select';
break;
case 'text':
$inputType = 'Text';
break;
case 'date':
$inputType = 'Date';
break;
case 'textArea':
$inputType = 'TextArea';
break;
default:
$inputType = $field['inputType']['name'];
break;
}
$dataTypeObj = $em->getRepository('HrisFormBundle:DataType')->findOneby(array('name' => $dataType));
$inputTypeObj = $em->getRepository('HrisFormBundle:InputType')->findOneby(array('name' => $inputType));
$fieldObject = new Field();
$fieldObject->setUid(uniqid());
$fieldObject->setName($field['name']);
$fieldObject->setCaption($field['caption']);
$fieldObject->setCompulsory($field['compulsory']);
$fieldObject->setDescription($field['description']);
$fieldObject->setDataType($dataTypeObj);
$fieldObject->setInputType($inputTypeObj);
// $fieldObject->setDatecreated($field[0]['datecreated']);
$em->persist($fieldObject);
$refField[$field['id']] = $fieldObject->getUid();
}
}
$em->flush();
return new Response('success');
}
/**
* Remove childField
*
* @param Field $childField
*/
public function removeChildField(Field $childField)
{
$this->childField->removeElement($childField);
}
/**
* Loads metadata into the database
*
* @param ObjectManager $manager
*/
public function load(ObjectManager $manager)
{
$stopwatch = new Stopwatch();
$stopwatch->start('dummyFieldGeneration');
$this->addDummyFields();
$this->addDummyFieldOptions();
$this->addDummyFieldOptionGroups();
$this->addDummyFieldGroups();
$this->addDummyFieldGroupsets();
// Create FieldOptionGroupset for later OptionGroups assignment
foreach ($this->fieldOptionGroupsets as $fieldOptionGroupsetKey => $humanResourceFieldOptionGroupsets) {
$fieldOptionGroupset = new FieldOptionGroupset();
$fieldOptionGroupset->setName($humanResourceFieldOptionGroupsets['name']);
$fieldOptionGroupset->setDescription($humanResourceFieldOptionGroupsets['description']);
$fieldOptionGroupsetReference = strtolower(str_replace(' ', '', $humanResourceFieldOptionGroupsets['name'])) . '-fieldoptiongroupset';
$this->addReference($fieldOptionGroupsetReference, $fieldOptionGroupset);
$manager->persist($fieldOptionGroupset);
}
// Populate dummy fields
foreach ($this->fields as $fieldKey => $humanResourceField) {
$field = new Field();
$field->setDataType($manager->merge($this->getReference(strtolower($humanResourceField['dataType']) . '-datatype')));
$field->setInputType($manager->merge($this->getReference(strtolower($humanResourceField['inputType']) . '-inputtype')));
$field->setName($humanResourceField['name']);
$field->setCaption($humanResourceField['caption']);
$field->setDescription($humanResourceField['description']);
$field->setHashistory($humanResourceField['history']);
$field->setFieldrelation($humanResourceField['fieldRelation']);
$field->setCompulsory($humanResourceField['compulsory']);
$field->setIsUnique($humanResourceField['isUnique']);
$field->setHastarget($humanResourceField['hastarget']);
$field->setIsCalculated($humanResourceField['isCalculated']);
$field->setSkipInReport($humanResourceField['skipInReport']);
$field->setCalculatedExpression($humanResourceField['calculatedExpression']);
if (!empty($humanResourceField['parentField'])) {
foreach ($humanResourceField['parentField'] as $parentField) {
$parentFieldByReference = $manager->merge($this->getReference(strtolower(str_replace(' ', '', $parentField)) . '-field'));
$field->addParentField($parentFieldByReference);
}
}
$fieldReference = strtolower(str_replace(' ', '', $humanResourceField['name'])) . '-field';
$this->addReference($fieldReference, $field);
$manager->persist($field);
// Append dummy options for input type of select.
if ($humanResourceField['inputType'] == 'Select') {
//Create Field Option Group by Field
$fieldOptionGroup = new FieldOptionGroup();
$fieldOptionGroup->setName($humanResourceField['name']);
$fieldOptionGroup->setField($field);
$fieldOptionGroup->setDescription($humanResourceField['description']);
$fieldOptionGroupReference = strtolower(str_replace(' ', '', $humanResourceField['name'])) . '-fieldoptiongroup';
$this->addReference($fieldOptionGroupReference, $fieldOptionGroup);
// Assign field options to their fields & field option group
foreach ($this->fieldOptions as $fieldOptionKey => $humanResourceFieldOptions) {
// Options are assigned to option groups according to field names(grouping of option by fields they belong to)
if (str_replace('-field', '', $humanResourceFieldOptions['field']) == str_replace(' ', '', $humanResourceField['name'])) {
$fieldOption = new FieldOption();
$fieldOption->setField($manager->merge($this->getReference($fieldReference)));
$fieldOption->setSort($humanResourceFieldOptions['sort']);
$fieldOption->setValue($humanResourceFieldOptions['value']);
$fieldOption->setDescription($humanResourceFieldOptions['description']);
$fieldOption->setSkipInReport($humanResourceFieldOptions['skipinreport']);
// If it has parent Option add it
if (isset($humanResourceFieldOptions['parentFieldOption']) && !empty($humanResourceFieldOptions['parentFieldOption'])) {
// Workout parent field for child option, through parent field of current field option's field.
$childFieldByReference = $manager->merge($this->getReference(strtolower(str_replace(' ', '', $humanResourceFieldOptions['field']))));
$parentField = $childFieldByReference->getParentField()->getValues();
//Workout reference of parent option(note: reference is concatentaion of option value and field name, hence need for parentfieldName)
$parentFieldOptionReference = strtolower(str_replace(' ', '', $humanResourceFieldOptions['parentFieldOption'])) . str_replace('-field', '', $parentField[0]->getName()) . '-fieldoption';
$parentFieldOptionByReference = $manager->merge($this->getReference($parentFieldOptionReference));
$manager->persist($parentFieldOptionByReference);
$fieldOption->addParentFieldOption($parentFieldOptionByReference);
}
$fieldOptionReference = strtolower(str_replace(' ', '', $humanResourceFieldOptions['value'])) . str_replace('-field', '', $humanResourceFieldOptions['field']) . '-fieldoption';
$this->addReference($fieldOptionReference, $fieldOption);
$manager->persist($fieldOption);
// Assign field option to it's field option group
$fieldOptionGroup->addFieldOption($fieldOption);
}
// Append options for Indicator groups of alteast secondary school and University education.
}
// Assign created field option groups to groupset by compulsory and hasHistory
// @Note: names are hard-coded same name as in addDummyFieldGroupsets
if ($humanResourceField['compulsory'] == true) {
$compulsoryFieldOptionGroupset = $manager->merge($this->getReference(strtolower(str_replace(' ', '', 'Compulsory Options')) . '-fieldoptiongroupset'));
$compulsoryFieldOptionGroupset->addFieldOptionGroup($fieldOptionGroup);
}
if ($humanResourceField['history'] == true) {
$hasHistoryGroupsetByReference = $manager->merge($this->getReference(strtolower(str_replace(' ', '', 'History Options')) . '-fieldoptiongroupset'));
$hasHistoryGroupsetByReference->addFieldOptionGroup($fieldOptionGroup);
}
$manager->persist($fieldOptionGroup);
}
unset($field);
unset($fieldOptionGroup);
}
// Create FieldOptionGroups specific for indicators
foreach ($this->fieldOptionGroups as $fieldOptionGroupKey => $humanResourceFieldOptionGroup) {
$fieldOptionGroup = new FieldOptionGroup();
$fieldOptionGroup->setName($humanResourceFieldOptionGroup['name']);
$fieldOptionGroup->setField($manager->merge($this->getReference(strtolower($humanResourceFieldOptionGroup['field']))));
$fieldOptionGroup->setDescription($humanResourceFieldOptionGroup['description']);
$fieldOptionGroupReference = strtolower(str_replace(' ', '', $humanResourceFieldOptionGroup['name'])) . '-fieldoptiongroup';
//Add option members
foreach ($humanResourceFieldOptionGroup['options'] as $dummyFieldOption) {
$fieldOptionReference = $dummyFieldOption;
$fieldOptionByReference = $manager->merge($this->getReference($fieldOptionReference));
$fieldOptionGroup->addFieldOption($fieldOptionByReference);
}
$this->addReference($fieldOptionGroupReference, $fieldOptionGroup);
$manager->persist($fieldOptionGroup);
unset($fieldOptionGroup);
}
// Populate dummy field Groups
foreach ($this->fieldGroups as $fieldGroupKey => $humanResourceFieldGroup) {
$fieldGroup = new FieldGroup();
$fieldGroup->setName($humanResourceFieldGroup['name']);
$fieldGroup->setDescription($humanResourceFieldGroup['description']);
$fieldGroupReference = strtolower(str_replace(' ', '', $humanResourceFieldGroup['name'])) . '-fieldgroup';
$this->addReference($fieldGroupReference, $fieldGroup);
// Add Unique fields in UniqueField Group
if ($humanResourceFieldGroup['name'] == 'Unique Fields') {
// Parse through fields marked unique
foreach ($this->fields as $fieldKey => $humanResourceField) {
if ($humanResourceField['isUnique'] == true) {
$fieldReference = strtolower(str_replace(' ', '', $humanResourceField['name'])) . '-field';
$fieldGroup->addField($manager->merge($this->getReference($fieldReference)));
}
}
}
//Add compulsory Fields in Compulsory Fields Group
if ($humanResourceFieldGroup['name'] == 'Compulsory Fields') {
// Parse through fields marked unique
foreach ($this->fields as $fieldKey => $humanResourceField) {
if ($humanResourceField['compulsory'] == true) {
$fieldReference = strtolower(str_replace(' ', '', $humanResourceField['name'])) . '-field';
$fieldGroup->addField($manager->merge($this->getReference($fieldReference)));
}
}
}
//Add compulsory Fields in Compulsory Fields Group
if ($humanResourceFieldGroup['name'] == 'Combo Fields') {
// Parse through fields marked unique
foreach ($this->fields as $fieldKey => $humanResourceField) {
if ($humanResourceField['inputType'] == 'Select') {
$fieldReference = strtolower(str_replace(' ', '', $humanResourceField['name'])) . '-field';
$fieldGroup->addField($manager->merge($this->getReference($fieldReference)));
}
}
}
$manager->persist($fieldGroup);
unset($fieldGroup);
}
$manager->flush();
/*
* Check Clock for time spent
*/
$dummyFieldGenerationGenerationTime = $stopwatch->stop('dummyFieldGeneration');
$duration = $dummyFieldGenerationGenerationTime->getDuration() / 1000;
unset($stopwatch);
if ($duration < 60) {
$durationMessage = round($duration, 2) . ' seconds';
} elseif ($duration >= 60 && $duration < 3600) {
$durationMessage = round($duration / 60, 2) . ' minutes';
} elseif ($duration >= 3600 && $duration < 216000) {
$durationMessage = round($duration / 3600, 2) . ' hours';
} else {
$durationMessage = round($duration / 86400, 2) . ' hours';
}
//echo "Dummy Fields generation complete in ". $durationMessage .".\n\n";
}