/**
* Returns organisationunit children collection
* @param Organisationunit $organisationunit
* @return \Doctrine\Common\Collections\Collection
*/
public function getAllChildren(Organisationunit $organisationunit)
{
$queryBuilder = $this->getEntityManager()->createQueryBuilder();
$organisationunitChildren = $queryBuilder->select('organisationunit', 'p.shortname')->from('HrisOrganisationunitBundle:Organisationunit', 'organisationunit')->join('organisationunit.parent', 'p')->join('organisationunit.organisationunitStructure', 'organisationunitStructure')->join('organisationunitStructure.level', 'level')->andWhere('
(
level.level >= :organisationunitLevel
AND organisationunitStructure.level' . $organisationunit->getOrganisationunitStructure()->getLevel()->getLevel() . 'Organisationunit=:levelOrganisationunit
)')->setParameters(array('levelOrganisationunit' => $organisationunit, 'organisationunitLevel' => $organisationunit->getOrganisationunitStructure()->getLevel()->getLevel()))->getQuery()->getArrayResult();
return $organisationunitChildren;
}
/**
* 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 organisationunit
*
* @param Organisationunit $organisationunit
*/
public function removeOrganisationunit(Organisationunit $organisationunit)
{
$this->organisationunit->removeElement($organisationunit);
}
/**
* Set organisationunit
*
* @param \Hris\OrganisationunitBundle\Entity\Organisationunit $organisationunit
* @return User
*/
public function setOrganisationunit(\Hris\OrganisationunitBundle\Entity\Organisationunit $organisationunit = null)
{
$this->organisationunit = $organisationunit;
$organisationunit->addUser($this);
return $this;
}
/**
* 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;
}
/**
* return nursing records
*
* @param Organisationunit $organisationUnit
* @param $formArray
* @param $withLowerLevels
* @param $startdate
* @param $enddate
* @param $NursingCadre
* @param $licence
* @return mixed
*/
private function nursingRecords1(Organisationunit $organisationUnit, $formArray = array(), $withLowerLevels, $startdate, $enddate, $NursingCadre, $licence)
{
$entityManager = $this->getDoctrine()->getManager();
$resourceTableName = "_resource_all_fields";
//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 = " ";
}
if ($NursingCadre == "Enrolled") {
$cardequery = " AND R.edu_evel = 'Certificate' ";
} elseif ($NursingCadre == "Registered") {
$cardequery = " AND R.edu_evel != 'Certificate' ";
} else {
$cardequery = "";
}
if ($licence == "Licensed") {
$licensequery = " AND R.reg_no != '' ";
} elseif ($licence == "NotLicensed") {
$licensequery = " AND R.reg_no = '' ";
} else {
$licensequery = " ";
}
//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.hosp_superlative_post, R.surname, R.designation,R.dob, R.sex, R.edu_evel, R.check_no, R.department, R.employment_status, R.level5_facility ,R.retirementdistribution ";
$query .= "FROM " . $resourceTableName . " R ";
$query .= "INNER JOIN hris_record as V on V.instance = R.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($formArray) != 0) {
$query .= " WHERE V.form_id IN (" . implode(",", $formArray) . ") AND R.profession = 'Nurse' AND R.department != '' " . $cardequery . $licensequery;
} else {
$query .= " WHERE R.profession = 'Nurse' AND R.department != '' " . $cardequery . $licensequery;
}
$query .= " AND (" . $subQuery . ") ";
$query .= " ORDER BY R.firstname ASC";
// echo $query; die();
//get the records
$report = $entityManager->getConnection()->executeQuery($query)->fetchAll();
return $report;
}
/**
* Loads metadata into the database
*
* @param ObjectManager $manager
*/
public function load(ObjectManager $manager)
{
$stopwatch = new Stopwatch();
$stopwatch->start('dummyOrganisationGeneration');
$this->addDummyOrganisationunits();
// Dummy organisationunit levels
$this->addDummyOrganisationunitLevels();
$this->addDummyOrganisationunitNames();
// Keep Array of distinct parent and lognames existing
$distinctLongnameAndParent = array();
// Populate dummy organisationunits
foreach ($this->organisationunits as $organisationunitKey => $humanResourceOrganisationunit) {
$organisationunit = new Organisationunit();
$organisationunit->setCode($humanResourceOrganisationunit['shortname']);
$organisationunit->setShortname($humanResourceOrganisationunit['shortname']);
$organisationunit->setLongname($humanResourceOrganisationunit['longname']);
if (isset($humanResourceOrganisationunit['description']) && !empty($humanResourceOrganisationunit['description'])) {
$organisationunit->setDescription($humanResourceOrganisationunit['description']);
}
if (isset($humanResourceOrganisationunit['parent']) && !empty($humanResourceOrganisationunit['parent'])) {
$parentReference = strtolower(str_replace(' ', '', $humanResourceOrganisationunit['parent'])) . '-organisationunit';
$parentOrganisationunit = $manager->merge($this->getReference($parentReference));
$organisationunit->setParent($parentOrganisationunit);
$distinctLongnameAndParent[] = array('longname' => $humanResourceOrganisationunit['shortname'], 'parent' => $parentOrganisationunit->getId());
$organisationunitReference = strtolower(str_replace(' ', '', $humanResourceOrganisationunit['shortname'])) . '-organisationunit';
$this->addReference($organisationunitReference, $organisationunit);
$manager->persist($organisationunit);
// Keep reference index for senquential generation of organisation unit structure
$this->addToIndexedOrganisationunit($organisationunitReference);
} else {
$organisationunitReference = strtolower(str_replace(' ', '', $humanResourceOrganisationunit['shortname'])) . '-organisationunit';
$this->addReference($organisationunitReference, $organisationunit);
$manager->persist($organisationunit);
// Keep reference index for senquential generation of organisation unit structure
$this->addToIndexedOrganisationunit($organisationunitReference);
}
unset($organisationunit);
// Randomly populate dispensaries, health centres & hospitals under municipal & district councils
if (strpos($humanResourceOrganisationunit['longname'], 'District Council') > 0 || strpos($humanResourceOrganisationunit['longname'], 'Municipal Council') > 0 || strpos($humanResourceOrganisationunit['longname'], 'City Council') > 0 || strpos($humanResourceOrganisationunit['longname'], 'Town Council') > 0) {
$dispensaryCount = rand($this->minDispensaryCount, $this->maxDispensaryCount);
$healthCentreCount = rand($this->minHealthCentreCount, $this->maxHealthCentreCount);
$hospitalCount = rand($this->minHospitalCount, $this->maxHospitalCount);
$parentReference = strtolower(str_replace(' ', '', $humanResourceOrganisationunit['shortname'])) . '-organisationunit';
$parentOrganisationunit = $manager->merge($this->getReference($parentReference));
// Populate Dispensaries
for ($dispensaryIncr = 0; $dispensaryIncr < $dispensaryCount; $dispensaryIncr++) {
$dispensary = new Organisationunit();
//Kip picking dispensaries randomly until unique reference is found
do {
$dispensaryKey = array_rand($this->organisationunitNames, 1);
$dispensaryName = $this->organisationunitNames[$dispensaryKey] . " Dispensary";
$dispensaryShortname = substr(strtolower(str_replace(' ', '', str_replace(' Dispensary', '', $dispensaryName))), 0, 12) . substr($parentOrganisationunit->getShortname(), 0, 5) . 'dsp';
$dispensaryReference = strtolower(str_replace(' ', '', $dispensaryShortname . substr($parentOrganisationunit->getShortname(), 0, 5))) . '-organisationunit';
$parentorgunitreference = array('longname' => $dispensaryName, 'parent' => $parentOrganisationunit->getId());
} while ($this->hasReference($dispensaryReference) || in_array($parentorgunitreference, $distinctLongnameAndParent));
$dispensary->setCode($dispensaryShortname);
$dispensary->setShortname($dispensaryShortname);
$dispensary->setLongname($dispensaryName);
$dispensary->setParent($parentOrganisationunit);
$dispensary->setActive(true);
$this->addReference($dispensaryReference, $dispensary);
$distinctLongnameAndParent[] = array('longname' => $dispensaryName, 'parent' => $parentOrganisationunit->getId());
$manager->persist($dispensary);
// Populate expected completeness figures for public and private
// Enter record for public and private form
$formNames = array('Public Employee Form', 'Private Employee Form');
$form = $manager->getRepository('HrisFormBundle:Form')->findOneBy(array('name' => $formNames[array_rand($formNames, 1)]));
$organisationunitCompleteness = new OrganisationunitCompleteness();
$organisationunitCompleteness->setOrganisationunit($dispensary);
$organisationunitCompleteness->setForm($form);
$expectations = array(2, 3, 4);
$organisationunitCompleteness->setExpectation($expectations[array_rand($expectations, 1)]);
$manager->persist($organisationunitCompleteness);
// Keep reference index for senquential generation of organisation unit structure
$this->addToIndexedOrganisationunit($dispensaryReference);
$dispensary = NULL;
$dispensaryReference = NULL;
}
// Populate Health Centre
for ($healthCentreIncr = 0; $healthCentreIncr < $healthCentreCount; $healthCentreIncr++) {
$healthCentre = new Organisationunit();
//Kip picking health centres randomly until unique reference is found
do {
$healthCentreKey = array_rand($this->organisationunitNames, 1);
$healthCentreName = $this->organisationunitNames[$healthCentreKey] . " Health Centre";
$healthCentreShortname = substr(strtolower(str_replace(' ', '', str_replace(' Health Centre', '', $healthCentreName))), 0, 12) . substr($parentOrganisationunit->getShortname(), 0, 5) . 'htc';
$healthCentreReference = strtolower(str_replace(' ', '', $healthCentreShortname . substr($parentOrganisationunit->getShortname(), 0, 5))) . '-organisationunit';
$parentorgunitreference = array('longname' => $healthCentreName, 'parent' => $parentOrganisationunit->getId());
} while ($this->hasReference($healthCentreReference) || in_array($parentorgunitreference, $distinctLongnameAndParent));
$healthCentre->setCode($healthCentreShortname);
$healthCentre->setShortname($healthCentreShortname);
$healthCentre->setLongname($healthCentreName);
$healthCentre->setParent($parentOrganisationunit);
$healthCentre->setActive(true);
$this->addReference($healthCentreReference, $healthCentre);
$distinctLongnameAndParent[] = array('longname' => $healthCentreName, 'parent' => $parentOrganisationunit->getId());
$manager->persist($healthCentre);
// Populate expected completeness figures for public and private
// Enter record for public and private form
$formNames = array('Public Employee Form', 'Private Employee Form');
$form = $manager->getRepository('HrisFormBundle:Form')->findOneBy(array('name' => $formNames[array_rand($formNames, 1)]));
$organisationunitCompleteness = new OrganisationunitCompleteness();
$organisationunitCompleteness->setOrganisationunit($healthCentre);
$organisationunitCompleteness->setForm($form);
$organisationunitCompleteness->setExpectation(array_rand(array(2, 3, 4), 1));
$manager->persist($organisationunitCompleteness);
// Keep reference index for senquential generation of organisation unit structure
$this->addToIndexedOrganisationunit($healthCentreReference);
$healthCentre = NULL;
$healthCentreReference = NULL;
}
// Populate Hosptial
for ($hospitalIncr = 0; $hospitalIncr < $hospitalCount; $hospitalIncr++) {
$hospital = new Organisationunit();
//Kip picking hospitals randomly until unique reference is found
do {
$hospitalKey = array_rand($this->organisationunitNames, 1);
$hospitalName = $this->organisationunitNames[$hospitalKey] . " Hospital";
$hospitalName = str_replace(' ', ' ', str_replace(' ', ' ', str_replace(' ', ' ', str_replace('\\t', ' ', $hospitalName))));
$hospitalShortname = substr(strtolower(str_replace(' ', '', str_replace(' Hospital', '', $hospitalName))), 0, 12) . substr($parentOrganisationunit->getShortname(), 0, 5) . 'hsp';
$hospitalReference = strtolower(str_replace(' ', '', $hospitalShortname . substr($parentOrganisationunit->getShortname(), 0, 5))) . '-organisationunit';
$parentorgunitreference = array('longname' => $hospitalName, 'parent' => $parentOrganisationunit->getId());
} while ($this->hasReference($hospitalReference) || in_array($parentorgunitreference, $distinctLongnameAndParent));
$hospital->setCode($hospitalShortname);
$hospital->setShortname($hospitalShortname);
$hospital->setLongname($hospitalName);
$hospital->setParent($parentOrganisationunit);
$hospital->setActive(true);
$this->addReference($hospitalReference, $hospital);
$distinctLongnameAndParent[] = array('longname' => $hospitalName, 'parent' => $parentOrganisationunit->getId());
$manager->persist($hospital);
// Populate expected completeness figures for public and private
// Enter record for public and private form
$formNames = array('Public Employee Form', 'Private Employee Form');
$form = $manager->getRepository('HrisFormBundle:Form')->findOneBy(array('name' => $formNames[array_rand($formNames, 1)]));
$organisationunitCompleteness = new OrganisationunitCompleteness();
$organisationunitCompleteness->setOrganisationunit($hospital);
$organisationunitCompleteness->setForm($form);
$organisationunitCompleteness->setExpectation(array_rand(array(2, 3, 4), 1));
$manager->persist($organisationunitCompleteness);
// Keep reference index for senquential generation of organisation unit structure
$this->addToIndexedOrganisationunit($hospitalReference);
$hospital = NULL;
$hospitalReference = NULL;
}
}
$parentOrganisationunit = NULL;
}
/**
* Generate organisation unit structure and
* organisationunit levels.
*/
// Workound parent reference
$organisationunitLevelReference = strtolower(str_replace(' ', '', 'Level 1')) . '-organisationunitlevel';
if ($this->hasReference($organisationunitLevelReference)) {
// Get orgunitlevel from reference
$organisationunitLevel = $this->getReference($organisationunitLevelReference);
} else {
// Persist and it's reference
$organisationunitLevel = new OrganisationunitLevel();
$organisationunitLevel->setLevel(1);
// $levelName = 'Level '.$organisationunitLevel->getLevel();
// if($organisationunitLevel->getLevel()==1) $levelName="Ministry Of Health &SW";
// $organisationunitLevel->setName($levelName);
$organisationunitLevel->setName('Level ' . $organisationunitLevel->getLevel());
$this->addReference($organisationunitLevelReference, $organisationunitLevel);
$manager->persist($organisationunitLevel);
}
// Generating organisationunit structure
if (!empty($this->indexedOrganisationunits)) {
foreach ($this->getIndexedOrganisationunits() as $indexedOrganisationunitKey => $indexedOrganisationunitReference) {
$organisationunit = $manager->merge($this->getReference($indexedOrganisationunitReference));
// Populate orgunit structure
$organisationunitStructure = new OrganisationunitStructure();
$organisationunitStructure->setOrganisationunit($organisationunit);
// Figureout level on the structure by parent
if ($organisationunit->getParent() == NULL) {
// Use created default first level for organisationunit structure
$organisationunitStructure->setLevel($organisationunitLevel);
$organisationunitStructure->setLevel1Organisationunit($organisationunitStructure->getOrganisationunit());
} else {
// Create new orgunit structure based parent structure
//Refer to previously created orgunit structure.
$parentOrganisationunitStructureReferenceName = strtolower(str_replace(' ', '', $organisationunit->getParent()->getShortname())) . '-organisationunitstructure';
$parentOrganisationunitStructureByReference = $manager->merge($this->getReference($parentOrganisationunitStructureReferenceName));
// Cross check to see if level is already created for reusability.
$currentOrganisationunitLevelname = 'Level ' . ($parentOrganisationunitStructureByReference->getLevel()->getLevel() + 1);
if ($this->hasReference(strtolower(str_replace(' ', '', $currentOrganisationunitLevelname)) . '-organisationunitlevel')) {
// Reuse existing reference
$currentOrganisationunitLevel = $this->getReference(strtolower(str_replace(' ', '', $currentOrganisationunitLevelname)) . '-organisationunitlevel');
$organisationunitLevel = $manager->merge($currentOrganisationunitLevel);
} else {
// Create new Level and reference.
$organisationunitLevel = new OrganisationunitLevel();
// $organisationunitLevel->setLevel($levelName);
$organisationunitLevel->setLevel($parentOrganisationunitStructureByReference->getLevel()->getLevel() + 1);
$organisationunitLevel->setName('Level ' . $organisationunitLevel->getLevel());
//Wild hack to set data entry level
if ($organisationunitLevel->getLevel() == 4) {
$organisationunitLevel->setDataentrylevel(True);
$organisationunitLevel->setDescription("Data Entry Level");
}
$organisationunitLevelReference = strtolower(str_replace(' ', '', $organisationunitLevel->getName())) . '-organisationunitlevel';
$this->addReference($organisationunitLevelReference, $organisationunitLevel);
$manager->persist($organisationunitLevel);
}
// Use reference of created/re-used level
$organisationunitStructure->setLevel($organisationunitLevel);
unset($organisationunitLevel);
/*
* Append Level organisation units based on their parent level.
*/
if ($organisationunitStructure->getLevel()->getLevel() == 1) {
$organisationunitStructure->setLevel1Organisationunit($organisationunitStructure->getOrganisationunit()->getParent());
} elseif ($organisationunitStructure->getLevel()->getLevel() == 2) {
$organisationunitStructure->setLevel2Organisationunit($organisationunitStructure->getOrganisationunit());
$organisationunitStructure->setLevel1Organisationunit($organisationunitStructure->getOrganisationunit()->getParent());
} elseif ($organisationunitStructure->getLevel()->getLevel() == 3) {
$organisationunitStructure->setLevel3Organisationunit($organisationunitStructure->getOrganisationunit());
$organisationunitStructure->setLevel2Organisationunit($organisationunitStructure->getOrganisationunit()->getParent());
$organisationunitStructure->setLevel1Organisationunit($organisationunitStructure->getOrganisationunit()->getParent()->getParent());
} elseif ($organisationunitStructure->getLevel()->getLevel() == 4) {
$organisationunitStructure->setLevel4Organisationunit($organisationunitStructure->getOrganisationunit());
$organisationunitStructure->setLevel3Organisationunit($organisationunitStructure->getOrganisationunit()->getParent());
$organisationunitStructure->setLevel2Organisationunit($organisationunitStructure->getOrganisationunit()->getParent()->getParent());
$organisationunitStructure->setLevel1Organisationunit($organisationunitStructure->getOrganisationunit()->getParent()->getParent()->getParent());
} elseif ($organisationunitStructure->getLevel()->getLevel() == 5) {
$organisationunitStructure->setLevel5Organisationunit($organisationunitStructure->getOrganisationunit());
$organisationunitStructure->setLevel4Organisationunit($organisationunitStructure->getOrganisationunit()->getParent());
$organisationunitStructure->setLevel3Organisationunit($organisationunitStructure->getOrganisationunit()->getParent()->getParent());
$organisationunitStructure->setLevel2Organisationunit($organisationunitStructure->getOrganisationunit()->getParent()->getParent()->getParent());
$organisationunitStructure->setLevel1Organisationunit($organisationunitStructure->getOrganisationunit()->getParent()->getParent()->getParent()->getParent());
} elseif ($organisationunitStructure->getLevel()->getLevel() == 6) {
$organisationunitStructure->setLevel6Organisationunit($organisationunitStructure->getOrganisationunit());
$organisationunitStructure->setLevel5Organisationunit($organisationunitStructure->getOrganisationunit()->getParent());
$organisationunitStructure->setLevel4Organisationunit($organisationunitStructure->getOrganisationunit()->getParent()->getParent());
$organisationunitStructure->setLevel3Organisationunit($organisationunitStructure->getOrganisationunit()->getParent()->getParent()->getParent());
$organisationunitStructure->setLevel2Organisationunit($organisationunitStructure->getOrganisationunit()->getParent()->getParent()->getParent()->getParent()->getParent());
$organisationunitStructure->setLevel1Organisationunit($organisationunitStructure->getOrganisationunit()->getParent()->getParent()->getParent()->getParent()->getParent()->getParent());
}
}
$organisationunitStructureReference = strtolower(str_replace(' ', '', $organisationunit->getShortname())) . '-organisationunitstructure';
$this->addReference($organisationunitStructureReference, $organisationunitStructure);
$manager->persist($organisationunitStructure);
unset($organisationunitStructure);
}
}
// Once organisatinounits are in database, assign admin to ministry
// and district user to one of the districts
//admin user
$adminUserByReference = $manager->merge($this->getReference('admin-user'));
$mohswByReference = $manager->merge($this->getReference('mohsw-organisationunit'));
$adminUserByReference->setOrganisationunit($mohswByReference);
$manager->persist($adminUserByReference);
//district user
$districtUserByReference = $manager->merge($this->getReference('district-user'));
$arushadcByReference = $manager->merge($this->getReference('arushadc-organisationunit'));
$districtUserByReference->setOrganisationunit($arushadcByReference);
$manager->persist($districtUserByReference);
//hospital user
$hospitalUserByReference = $manager->merge($this->getReference('hospital-user'));
$bugandorefhspByReference = $manager->merge($this->getReference('bugandorefhsp-organisationunit'));
$hospitalUserByReference->setOrganisationunit($bugandorefhspByReference);
$manager->persist($hospitalUserByReference);
$manager->flush();
/*
* Check Clock for time spent
*/
$dummyOrganisationGenerationTime = $stopwatch->stop('dummyOrganisationGeneration');
$duration = $dummyOrganisationGenerationTime->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 Organisationunit generation complete in ". $durationMessage .".\n\n";
}
/**
* Importing Legacy Organisation Units.
*
*/
public function LegacyUpdateOrganisationUnitsAction($organisationUnits)
{
global $refOrganisationUnit;
$em = $this->getDoctrine()->getManager();
$organisationUnits = json_decode($organisationUnits, true);
foreach ($organisationUnits as $key => $organisationUnit) {
$parent = null;
$parent = $em->getRepository('HrisOrganisationunitBundle:Organisationunit')->findOneby(array('longname' => $organisationUnit['longname']));
$orgunitObjectCheck = $em->getRepository('HrisOrganisationunitBundle:Organisationunit')->findOneby(array('longname' => $organisationUnit[0]['longname'], 'parent' => $parent));
if ($orgunitObjectCheck != NULL) {
$refOrganisationUnit[$organisationUnit[0]['id']] = $orgunitObjectCheck->getUid();
// print 'this record Exists '.$organisationUnit[0]['longname']." Parent: ".$organisationUnit['longname'].'<br>';
} else {
$orgunitObject = new Organisationunit();
$orgunitObject->setUid(uniqid());
if ($parent != NULL) {
$orgunitObject->setParent($parent);
//print '<br>Parent Exists with LongName: '. $parent->getLongname();
} else {
$parent = NULL;
$orgunitObject->setParent($parent);
}
$orgunitObject->setShortname($organisationUnit[0]['shortname']);
$orgunitObject->setLongname($organisationUnit[0]['longname']);
$orgunitObject->setCode($organisationUnit[0]['code']);
$em->persist($orgunitObject);
$em->flush();
$refOrganisationUnit[$organisationUnit[0]['id']] = $orgunitObject->getUid();
}
}
$em->flush();
return new Response('success');
}
/**
* Records Engine
*
* @param Organisationunit $organisationUnit
* @param Form $forms
* @param Field $fields
* @param $reportType
* @param $withLowerLevels
* @return mixed
*/
private function recordsEngine(Organisationunit $organisationUnit, $groups, $forms, $reportType, $withLowerLevels, $startdate, $enddate)
{
$entityManager = $this->getDoctrine()->getManager();
$resourceTableName = "_resource_all_fields";
if ($organisationUnit->getLongName() == "Ministry Of Health") {
$organisation_unit_clause = "";
} else {
$organisation_unit_clause = "and I.region= '" . $organisationUnit->getLongName() . "'";
}
if ($reportType == "trainings") {
//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 = "SELECT T.coursename as coursename,I.region as region,I.district as district,I.venue as venue,I.startdate as startdate,I.enddate as enddate ";
$query .= "FROM hris_traininginstance I ";
$query .= "INNER JOIN hris_trainings as T on T.id = I.training_id ";
$query .= "INNER JOIN hris_instance_records as F on F.instance_id = I.id ";
$query .= "INNER JOIN hris_record as V on V.id = F.record_id ";
$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 .= "INNER JOIN hris_organisationunit as O on O.longname = I.region ";
$query .= "where V.form_id in (" . implode(",", $forms) . ")";
$query .= "and I.training_id in (" . implode(",", $groups[0]) . ") " . $organisation_unit_clause . " and (date_part('year',startdate) between " . $startdate . " and " . $enddate . " ) GROUP BY T.coursename,I.region,I.district,I.venue,I.startdate,I.enddate ORDER BY I.startdate DESC";
$results = $entityManager->getConnection()->executeQuery($query)->fetchAll();
// }
}
if ($reportType == "participants") {
//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 = "SELECT * ";
$query .= "FROM hris_instance_records F ";
$query .= "INNER JOIN hris_traininginstance as I on I.id = F.instance_id ";
$query .= "INNER JOIN hris_trainings as T on T.id = I.training_id ";
$query .= "INNER JOIN hris_record as V on V.id = F.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 .= "INNER JOIN hris_organisationunit as O on O.longname = I.region ";
$query .= "where V.form_id in (" . implode(",", $forms) . ") " . $organisation_unit_clause . " and I.training_id in (" . implode(",", $groups[0]) . ") GROUP BY I.startdate,F.id,I.id,T.id,V.id,R.id,S.id,L.id,O.id";
$results = $entityManager->getConnection()->executeQuery($query)->fetchAll();
}
if ($reportType == "facilitators") {
//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 = "SELECT * ";
$query .= "FROM hris_instanceFacilitator F ";
$query .= "INNER JOIN hris_traininginstance as I on I.id = F.instance_id ";
$query .= "INNER JOIN hris_trainings as T on T.id = I.training_id ";
$query .= "INNER JOIN hris_record as V on V.id = F.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 .= "INNER JOIN hris_organisationunit as O on O.longname = I.region ";
$query .= "where V.form_id in (" . implode(",", $forms) . ") " . $organisation_unit_clause . " and I.training_id in (" . implode(",", $groups[0]) . ") GROUP BY I.startdate,F.id,I.id,T.id,V.id,R.id,S.id,L.id,O.id";
$results = $entityManager->getConnection()->executeQuery($query)->fetchAll();
}
return $results;
}
/**
* 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;
}
