/** * 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 uniqueRecordForms * * @param Form $uniqueRecordForms * @return Field */ public function addUniqueRecordForm(Form $uniqueRecordForms) { $this->uniqueRecordForms[$uniqueRecordForms->getId()] = $uniqueRecordForms; return $this; }
/** * Add form * * @param Form $form * @return DashboardChart */ public function addForm(Form $form) { $this->form[$form->getId()] = $form; return $this; }
/** * 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; }