/**
* 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 form
*
* @param Form $form
*/
public function removeForm(Form $form)
{
$this->form->removeElement($form);
}
/**
* Remove uniqueRecordForms
*
* @param Form $uniqueRecordForms
*/
public function removeUniqueRecordForm(Form $uniqueRecordForms)
{
$this->uniqueRecordForms->removeElement($uniqueRecordForms);
}
public function load(ObjectManager $manager)
{
$stopwatch = new Stopwatch();
$stopwatch->start('dummyFormGeneration');
// Populate dummy forms
$this->addDummyForms();
// Seek dummy fields
$loadFieldData = new LoadFieldData();
$loadFieldData->addDummyFields();
$dummyFields = $loadFieldData->getFields();
// Once forms are in database, assign admin with all forms
// district user to public and private
// and hospital user to hospital form
//admin user
$adminUserByReference = $manager->merge($this->getReference('admin-user'));
//district user
$districtUserByReference = $manager->merge($this->getReference('district-user'));
//hospital user
$hospitalUserByReference = $manager->merge($this->getReference('hospital-user'));
foreach ($this->forms as $key => $humanResourceForm) {
$form = new Form();
$form->setName($humanResourceForm['name']);
$form->setTitle($humanResourceForm['name']);
$this->addReference(strtolower(str_replace(' ', '', $humanResourceForm['name'])) . '-form', $form);
$manager->persist($form);
// Assign all forms to admin user
$adminUserByReference->addForm($form);
$manager->persist($adminUserByReference);
// Assign public and private form to district user and hospital to hospital user
if ($humanResourceForm['name'] == 'Public Employee Form' || $humanResourceForm['name'] == 'Private Employee Form') {
$districtUserByReference->addForm($form);
$manager->persist($districtUserByReference);
} elseif ($humanResourceForm['name'] == 'Hospital Employee Form') {
$hospitalUserByReference->addForm($form);
$manager->persist($hospitalUserByReference);
}
// Add Field Members for the form created
$sort = 1;
foreach ($humanResourceForm['fields'] as $dummyField) {
$fieldByReference = $manager->merge($this->getReference(strtolower(str_replace(' ', '', $dummyField)) . '-field'));
$formByReference = $manager->merge($this->getReference(strtolower(str_replace(' ', '', $humanResourceForm['name'])) . '-form'));
$formMember = new FormFieldMember();
$formMember->setField($fieldByReference);
$formMember->setForm($formByReference);
$formMember->setSort($sort++);
//$referenceName = strtolower(str_replace(' ','',$humanResourceForm['name']).str_replace(' ','',$dummyField)).'-form-field-member';
//$this->addReference($referenceName, $formMember);
$manager->persist($formMember);
// Overwrite fieldnames in inputags ids with uids
$humanResourceForm['hypertext'] = str_replace("id=\"" . $dummyField . "\"", "id=\"" . $fieldByReference->getUid() . "\"", $humanResourceForm['hypertext']);
if ($fieldByReference->getInputType()->getName() == "Select") {
$humanResourceForm['hypertext'] = str_replace("changeRelatedFieldOptions('" . $dummyField . "')", "changeRelatedFieldOptions('" . $fieldByReference->getUid() . "')", $humanResourceForm['hypertext']);
$humanResourceForm['hypertext'] = str_replace("loadFieldOptions('" . $dummyField . "')", "loadFieldOptions('" . $fieldByReference->getUid() . "')", $humanResourceForm['hypertext']);
}
unset($formMember);
}
$sort = 1;
foreach ($humanResourceForm['visibleFields'] as $key => $dummyField) {
$fieldByReference = $manager->merge($this->getReference(strtolower(str_replace(' ', '', $dummyField)) . '-field'));
$formByReference = $manager->merge($this->getReference(strtolower(str_replace(' ', '', $humanResourceForm['name'])) . '-form'));
$visibleFieldMember = new FormVisibleFields();
$visibleFieldMember->setField($fieldByReference);
$visibleFieldMember->setForm($formByReference);
$visibleFieldMember->setSort($sort++);
$manager->persist($visibleFieldMember);
unset($visibleFieldMember);
}
foreach ($humanResourceForm['uniqueFields'] as $key => $dummyField) {
$fieldByReference = $manager->merge($this->getReference(strtolower(str_replace(' ', '', $dummyField)) . '-field'));
$form->addUniqueRecordField($fieldByReference);
}
$form->setHypertext($humanResourceForm['hypertext']);
$manager->persist($form);
unset($form);
}
$manager->flush();
/*
* Check Clock for time spent
*/
$dummyFormGenerationTime = $stopwatch->stop('dummyFormGeneration');
$duration = $dummyFormGenerationTime->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 Form generation complete in ". $durationMessage .".\n\n";
}
/**
* Aggregation Engine
*
* @param Organisationunit $organisationUnit
* @param Form $forms
* @param $profession
* @param $leaves
* @param $withLowerLevels
* @param $reportType
* @param $startdate
* @param $enddate
* @return mixed
*/
private function recordsEngine(Organisationunit $organisationUnit, Form $forms, $profession = array(), $leaves = array(), $withLowerLevels, $reportType, $startdate, $enddate)
{
$entityManager = $this->getDoctrine()->getManager();
$resourceTableName = "_resource_all_fields";
$entityManager = $this->getDoctrine()->getManager();
$resourceTableName = "_resource_all_fields";
//generating the fields which are concerned with leave only.
$leaveTypes = $entityManager->getConnection()->executeQuery("SELECT L.name FROM hris_leave_type L")->fetchAll();
$allLeaves = array();
foreach ($leaveTypes as $leave) {
$allLeaves[] = $leave['name'];
}
//checking if date range is selected
if ($startdate != "" && $enddate != "") {
$datequery = " AND H.startdate between '" . $startdate . "' and '" . $enddate . "' " . " OR H.enddate between '" . $startdate . "' and '" . $enddate . "'";
} elseif ($startdate == "" && $enddate != "") {
$datequery = " AND H.enddate <= '" . $enddate . "' ";
} elseif ($startdate != "" && $enddate == "") {
$datequery = " AND H.startdate >= '" . $startdate . "' ";
} else {
$datequery = " ";
}
$reportTe = "";
if ($reportType == "onLeaveReport") {
$reportTe .= " AND '" . date('Y-m-d') . "' between H.startdate and H.enddate";
}
if (count($profession) != 0) {
$reportTe .= " AND R.profession IN ('" . implode("', '", $profession) . "') ";
} else {
}
//summary of employee taking leave
//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.record_id, H.entitled_payment, H.startdate, H.enddate, H.entitled_payment, 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 ";
if (count($leaves) == 0) {
$query .= " WHERE V.form_id = " . $forms->getId() . " AND H.history IN ('" . implode("', '", $allLeaves) . "') " . $reportTe;
} else {
$query .= " WHERE V.form_id = " . $forms->getId() . " AND H.history IN ('" . implode("', '", $leaves) . "') " . $reportTe;
}
$query .= " AND (" . $subQuery . ") " . $datequery;
$query .= " ORDER BY R.firstname ASC";
//get the records
$report = $entityManager->getConnection()->executeQuery($query)->fetchAll();
return $report;
}
