/** * 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; }
/** * Add organisationunit * * @param Organisationunit $organisationunit * @return DashboardChart */ public function addOrganisationunit(Organisationunit $organisationunit) { $this->organisationunit[$organisationunit->getId()] = $organisationunit; 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; }
/** * 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; }