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