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