protected function execute(InputInterface $input, OutputInterface $output)
{
$capturedYear = $input->getArgument('year');
if (!empty($capturedYear)) {
$year = $capturedYear;
} else {
$year = date("Y") - 1;
}
$hr_prefix = "hr_";
$normalizer = new GetSetMethodNormalizer();
//Setup Custom dropdown fields
$this->customDropDown = array("level1_mohsw", "level2_categories", "level3_regions_departments_institutions_referrals", "level4_districts_reg_hospitals", "level5_facility", "type", "ownership", "organisationunit_name", "form_name");
$sensitiveFields = array("firstname", "middlename", "surname", "file_no", "reg_no", "check_no", "salary", "religion", "next_kin", "contact_of_next_of_kin");
$name = $input->getArgument('name');
if ($name) {
$this->resourceTableName = $name;
} else {
$this->resourceTableName = 'All Fields';
}
if ($input->getOption('json')) {
$this->resourceTableNature = 'json';
$encoder = new JsonEncoder();
} elseif ($input->getOption('xml')) {
$this->resourceTableNature = 'xml';
$encoder = new XmlEncoder();
} elseif ($input->getOption('csv')) {
$this->resourceTableNature = 'csv';
} else {
$this->resourceTableNature = 'json';
$encoder = new JsonEncoder();
}
$filename = "events";
$destination = "/tmp/";
$csvFile = $destination . $filename . "_" . $year . ".csv";
//$normalizer->setIgnoredAttributes(array('age'));
$metaData = array();
$metaData["created"] = (new \DateTime('now'))->format('Y-m-d\\TH:i:s.000O');
$em = $this->getContainer()->get('doctrine')->getManager();
$logger = $this->getContainer()->get('logger');
// Find Resource table to know existing fields in the genreated table
$resourceTableEntity = $em->getRepository('HrisFormBundle:ResourceTable')->findOneBy(array('name' => $this->resourceTableName));
$resourceTableName = ResourceTable::getStandardResourceTableName();
$query = "SELECT ResourceTable.*,hris_organisationunit.dhisuid FROM " . $resourceTableName . " ResourceTable INNER JOIN hris_organisationunit ON hris_organisationunit.id= ResourceTable.organisationunit_id WHERE hris_organisationunit.dhisuid IS NOT NULL AND ResourceTable.first_appointment_year<=" . $year . " AND ResourceTable.retirementdate_year>=" . $year . " AND ResourceTable.employment_status !='Resigned' AND ResourceTable.employment_status !='Deceased' AND ResourceTable.employment_status !='Retired' AND ResourceTable.employment_status !='Abscondent' AND ResourceTable.employment_status !='Off Duty'";
echo $query;
echo "\n";
$hrRecords = $em->getConnection()->executeQuery($query)->fetchAll();
$csvContents = "event,status,program,programStage,enrollment,orgUnit,eventDate,dueDate,latitude,longitude,dataElement,value,storedBy,providedElsewhere\n";
file_put_contents($csvFile, $csvContents);
unset($csvContents);
foreach ($hrRecords as $hrRecordkey => $hrRecord) {
$csvContents = '';
//For each record field go through the resource table field columns and put the record values
foreach ($resourceTableEntity->getResourceTableFieldMember() as $resourceTableKey => $resourceTableFieldMember) {
$field = $resourceTableFieldMember->getField();
//Sensitive fields to skip
if (in_array(strtolower($field->getName()), $sensitiveFields)) {
continue;
}
//If no value stored, no need to bulk up csv export
if (empty(trim($hrRecord[strtolower($field->getName())]))) {
continue;
}
//Fill in value for a data row
$csvContents .= "I" . substr($hrRecord["instance"], strlen($hrRecord["instance"]) - 10) . ",ACTIVE,Gcghe0W76Ms,NHIu1snluiZ,," . $hrRecord["dhisuid"] . "," . $year . substr($hrRecord["lastupdated"], 4, strlen($hrRecord["lastupdated"])) . ",,,," . "F" . substr($field->getUid(), 3) . "," . "\"" . str_replace("\"", "`", $hrRecord[strtolower($field->getName())]) . "\"" . ",mukulu,false" . "\n";
if ($field->getDataType()->getName() == "Date") {
$csvContents .= "I" . substr($hrRecord["instance"], strlen($hrRecord["instance"]) - 10) . ",ACTIVE,Gcghe0W76Ms,NHIu1snluiZ,," . $hrRecord["dhisuid"] . "," . $year . substr($hrRecord["lastupdated"], 4, strlen($hrRecord["lastupdated"])) . ",,,," . "Fmt" . substr($field->getUid(), 5) . "," . "\"" . str_replace("\"", "`", $hrRecord[strtolower($field->getName()) . '_month_text']) . "\"" . ",mukulu,false" . "\n";
$csvContents .= "I" . substr($hrRecord["instance"], strlen($hrRecord["instance"]) - 10) . ",ACTIVE,Gcghe0W76Ms,NHIu1snluiZ,," . $hrRecord["dhisuid"] . "," . $year . substr($hrRecord["lastupdated"], 4, strlen($hrRecord["lastupdated"])) . ",,,," . "Fyr" . substr($field->getUid(), 5) . "," . "\"" . str_replace("\"", "`", $hrRecord[strtolower($field->getName()) . '_year']) . "\"" . ",mukulu,false" . "\n";
}
if ($field->getHashistory()) {
$csvContents .= "I" . substr($hrRecord["instance"], strlen($hrRecord["instance"]) - 10) . ",ACTIVE,Gcghe0W76Ms,NHIu1snluiZ,," . $hrRecord["dhisuid"] . "," . $year . substr($hrRecord["lastupdated"], 4, strlen($hrRecord["lastupdated"])) . ",,,," . "Flu" . substr($field->getUid(), 5) . "," . "\"" . str_replace("\"", "`", $hrRecord[strtolower($field->getName()) . '_last_updated']) . "\"" . ",mukulu,false" . "\n";
$csvContents .= "I" . substr($hrRecord["instance"], strlen($hrRecord["instance"]) - 10) . ",ACTIVE,Gcghe0W76Ms,NHIu1snluiZ,," . $hrRecord["dhisuid"] . "," . $year . substr($hrRecord["lastupdated"], 4, strlen($hrRecord["lastupdated"])) . ",,,," . "Fhmt" . substr($field->getUid(), 6) . "," . "\"" . str_replace("\"", "`", $hrRecord[strtolower($field->getName()) . '_last_updated_month_text']) . "\"" . ",mukulu,false" . "\n";
$csvContents .= "I" . substr($hrRecord["instance"], strlen($hrRecord["instance"]) - 10) . ",ACTIVE,Gcghe0W76Ms,NHIu1snluiZ,," . $hrRecord["dhisuid"] . "," . $year . substr($hrRecord["lastupdated"], 4, strlen($hrRecord["lastupdated"])) . ",,,," . "Fhyr" . substr($field->getUid(), 6) . "," . "\"" . str_replace("\"", "`", $hrRecord[strtolower($field->getName()) . '_last_updated_year']) . "\"" . ",mukulu,false" . "\n";
}
}
//Go through each record organsationunit level columns
$organisationunitLevels = $em->createQuery('SELECT DISTINCT organisationunitLevel FROM HrisOrganisationunitBundle:OrganisationunitLevel organisationunitLevel ORDER BY organisationunitLevel.level ')->getResult();
foreach ($organisationunitLevels as $organisationunitLevelKey => $organisationunitLevel) {
$organisationunitLevelName = "level" . $organisationunitLevel->getLevel() . "_" . str_replace(',', '_', str_replace('.', '_', str_replace('/', '_', str_replace(' ', '_', $organisationunitLevel->getName()))));
$csvContents .= "I" . substr($hrRecord["instance"], strlen($hrRecord["instance"]) - 10) . ",ACTIVE,Gcghe0W76Ms,NHIu1snluiZ,," . $hrRecord["dhisuid"] . "," . $year . substr($hrRecord["lastupdated"], 4, strlen($hrRecord["lastupdated"])) . ",,,," . "Lvl" . $organisationunitLevel->getLevel() . substr($organisationunitLevel->getUid(), 6) . "," . "\"" . str_replace("\"", "`", $hrRecord[strtolower($organisationunitLevelName)]) . "\"" . ",mukulu,false" . "\n";
}
// Go through OrganisationunitGroupsets Column
$organisationunitGroupsets = $em->getRepository('HrisOrganisationunitBundle:OrganisationunitGroupset')->findAll();
foreach ($organisationunitGroupsets as $organisationunitGroupsetKey => $organisationunitGroupset) {
$csvContents .= "I" . substr($hrRecord["instance"], strlen($hrRecord["instance"]) - 10) . ",ACTIVE,Gcghe0W76Ms,NHIu1snluiZ,," . $hrRecord["dhisuid"] . "," . $year . substr($hrRecord["lastupdated"], 4, strlen($hrRecord["lastupdated"])) . ",,,," . "Grp" . substr($organisationunitGroupset->getUid(), 5) . "," . "\"" . str_replace("\"", "`", $hrRecord[strtolower($organisationunitGroupset->getName())]) . "\"" . ",mukulu,false" . "\n";
}
$csvContents .= "I" . substr($hrRecord["instance"], strlen($hrRecord["instance"]) - 10) . ",ACTIVE,Gcghe0W76Ms,NHIu1snluiZ,," . $hrRecord["dhisuid"] . "," . $year . substr($hrRecord["lastupdated"], 4, strlen($hrRecord["lastupdated"])) . ",,,," . "OrgunitName" . "," . "\"" . str_replace("\"", "`", $hrRecord[strtolower('Organisationunit_name')]) . "\"" . ",mukulu,false" . "\n";
$csvContents .= "I" . substr($hrRecord["instance"], strlen($hrRecord["instance"]) - 10) . ",ACTIVE,Gcghe0W76Ms,NHIu1snluiZ,," . $hrRecord["dhisuid"] . "," . $year . substr($hrRecord["lastupdated"], 4, strlen($hrRecord["lastupdated"])) . ",,,," . "HrhFormName" . "," . "\"" . str_replace("\"", "`", $hrRecord[strtolower('Form_name')]) . "\"" . ",mukulu,false" . "\n";
file_put_contents($csvFile, $csvContents, FILE_APPEND);
unset($csvContents);
}
$output->writeln('Done creating csv!!');
//Outputfile
$gzfile = $csvFile . ".gz";
//Open the gz file with w9 highest compression
$fp = gzopen($gzfile, 'w9');
//Compress the file
gzwrite($fp, file_get_contents($csvFile));
//Close the gz file
gzclose($fp);
}
protected function execute(InputInterface $input, OutputInterface $output)
{
$hr_prefix = "hr_";
$normalizer = new GetSetMethodNormalizer();
//Setup Custom dropdown fields
$this->customDropDown = array("level1_mohsw", "level2_categories", "level3_regions_departments_institutions_referrals", "level4_districts_reg_hospitals", "level5_facility", "type", "ownership", "organisationunit_name", "form_name");
$name = $input->getArgument('name');
if ($name) {
$this->resourceTableName = $name;
} else {
$this->resourceTableName = 'All Fields';
}
if ($input->getOption('json')) {
$this->resourceTableNature = 'json';
$encoder = new JsonEncoder();
} elseif ($input->getOption('xml')) {
$this->resourceTableNature = 'xml';
$encoder = new XmlEncoder();
} else {
$this->resourceTableNature = 'json';
$encoder = new JsonEncoder();
}
//$normalizer->setIgnoredAttributes(array('age'));
$metaData = array();
$metaData["created"] = (new \DateTime('now'))->format('Y-m-d\\TH:i:s.000O');
$em = $this->getContainer()->get('doctrine')->getManager();
$logger = $this->getContainer()->get('logger');
// Find Resource table for generation
$resourceTableEntity = $em->getRepository('HrisFormBundle:ResourceTable')->findOneBy(array('name' => $this->resourceTableName));
//Defining program and programStage for human resource data
$humanResourceProgram = array();
$humanResourceProgram["name"] = $hr_prefix . "HumanResource";
//$humanResourceProgram["created"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
//$humanResourceProgram["lastUpdated"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
$humanResourceProgram["externalAccess"] = "false";
$humanResourceProgram["id"] = "Gcghe0W76Ms";
$humanResourceProgramStage = array();
$humanResourceProgramStage["name"] = $hr_prefix . "Single-Event HumanResource";
//$humanResourceProgramStage["created"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
//$humanResourceProgramStage["lastUpdated"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
$humanResourceProgramStage["externalAccess"] = "false";
$humanResourceProgramStage["id"] = "NHIu1snluiZ";
//Construct dataElement meta-data for the resource table
// Create other columns(fields, organisationunits,etc) in the resource table
$dataElements = array();
$programStageDataElements = array();
$optionSets = array();
$allOptions = array();
$sensitiveFields = array("firstname", "middlename", "surname", "file_no", "reg_no", "check_no", "salary", "religion", "next_kin", "contact_of_next_of_kin");
//Months optionsets
//Place optionsets
$monthOptionSet = array();
$monthOptionSet["name"] = $hr_prefix . 'Month_text';
$monthOptionSet["code"] = 'Month_text';
$monthOptionSet["id"] = "o" . substr(md5($monthOptionSet["name"]), 0, 10);
//$optionSet["created"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
//$optionSet["lastUpdated"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
$monthOptionSet["externalAccess"] = "false";
$monthOptionSet["publicAccess"] = "rw------";
$monthOptionSet["version"] = "1";
$options = array();
$monthsArray = array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December");
foreach ($monthsArray as $monthKey => $month) {
$option = array();
$option["name"] = $month;
$option["code"] = $month;
$option["id"] = "o" . substr(md5($option["name"]), 0, 10);
//$option["created"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
//$option["lastUpdated"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
$option["externalAccess"] = "false";
$options[] = $option;
$allOptions[] = $option;
unset($option);
}
$monthOptionSet["options"] = $options;
$optionSets[] = $monthOptionSet;
unset($options);
//Go through resource table fields to create dataelements metadata
foreach ($resourceTableEntity->getResourceTableFieldMember() as $resourceTableKey => $resourceTableFieldMember) {
$field = $resourceTableFieldMember->getField();
//Sensitive fields to skip
if (in_array(strtolower($field->getName()), $sensitiveFields)) {
continue;
}
//Prepare a data element
$dataElement = array();
$dataElement["name"] = $hr_prefix . $field->getName();
$dataElement["code"] = $field->getName();
$dataElement["id"] = "F" . substr($field->getUid(), 3);
//$dataElement["created"]=$field->getDatecreated()->format('Y-m-d\TH:i:s.000O');
//$dataElement["lastUpdated"]=$field->getLastupdated()->format('Y-m-d\TH:i:s.000O');
//Inserting minimum dataElementInfo into programStageDataElement
$programStageDataElement = array();
$programStageDataElement["programStage"] = $humanResourceProgramStage;
$programStageDataElement["dataElement"] = $dataElement;
$programStageDataElement["compulsory"] = "false";
$programStageDataElement["allowProvidedElsewhere"] = "false";
$programStageDataElement["sortOrder"] = "0";
$programStageDataElement["displayInReports"] = "false";
$programStageDataElement["allowFutureDate"] = "false";
$programStageDataElements[] = $programStageDataElement;
//continue with preparting dataElement
$dataElement["shortName"] = $hr_prefix . substr(str_replace("_", "", str_replace(" ", "", $field->getName())), 0, 49);
$dataElement["domainType"] = "TRACKER";
$dataElement["aggregationOperator"] = "sum";
if ($field->getDataType()->getName() == "String") {
//For string place
$dataElement["type"] = "string";
$dataElement["textType"] = "text";
} elseif ($field->getDataType()->getName() == "Integer") {
$dataElement["type"] = "int";
$dataElement["numberType"] = "int";
} elseif ($field->getDataType()->getName() == "Double") {
$dataElement["type"] = "int";
$dataElement["numberType"] = "number";
} elseif ($field->getDataType()->getName() == "Date") {
$dataElement["type"] = "date";
$dataElement["numberType"] = "number";
// Additional date extrapolation columns
$dataElementMonthText = array();
$dataElementMonthText["name"] = $hr_prefix . $field->getName() . '_month_text';
$dataElementMonthText["code"] = $field->getName() . '_month_text';
$dataElementMonthText["id"] = "Fmt" . substr($field->getUid(), 5);
//$dataElementMonthText["created"]=$field->getDatecreated()->format('Y-m-d\TH:i:s.000O');
//$dataElementMonthText["lastUpdated"]=$field->getLastupdated()->format('Y-m-d\TH:i:s.000O');
//Inserting minimum dataElementInfo into programStageDataElement
$programStageDataElement = array();
$programStageDataElement["programStage"] = $humanResourceProgramStage;
$programStageDataElement["dataElement"] = $dataElementMonthText;
$programStageDataElement["compulsory"] = "false";
$programStageDataElement["allowProvidedElsewhere"] = "false";
$programStageDataElement["sortOrder"] = "0";
$programStageDataElement["displayInReports"] = "false";
$programStageDataElement["allowFutureDate"] = "false";
$programStageDataElements[] = $programStageDataElement;
//continue with preparting dataElement
$dataElementMonthText["shortName"] = $hr_prefix . substr(str_replace("_", "", str_replace(" ", "", $field->getName() . '_month_text')), 0, 49);
$dataElementMonthText["domainType"] = "TRACKER";
$dataElementMonthText["aggregationOperator"] = "sum";
$dataElementMonthText["type"] = "string";
$dataElementMonthText["textType"] = "text";
//Place optionsets
$dataElementMonthText["optionSet"] = $monthOptionSet;
$dataElements[] = $dataElementMonthText;
unset($dataElementMonthText);
$dataElementYear = array();
$dataElementYear["name"] = $hr_prefix . $field->getName() . '_year';
$dataElementYear["code"] = $field->getName() . '_year';
$dataElementYear["id"] = "Fyr" . substr($field->getUid(), 5);
//$dataElementYear["created"]=$field->getDatecreated()->format('Y-m-d\TH:i:s.000O');
//$dataElementYear["lastUpdated"]=$field->getLastupdated()->format('Y-m-d\TH:i:s.000O');
//Inserting minimum dataElementInfo into programStageDataElement
$programStageDataElement = array();
$programStageDataElement["programStage"] = $humanResourceProgramStage;
$programStageDataElement["dataElement"] = $dataElementYear;
$programStageDataElement["compulsory"] = "false";
$programStageDataElement["allowProvidedElsewhere"] = "false";
$programStageDataElement["sortOrder"] = "0";
$programStageDataElement["displayInReports"] = "false";
$programStageDataElement["allowFutureDate"] = "false";
$programStageDataElements[] = $programStageDataElement;
//continue with preparting dataElement
$dataElementYear["shortName"] = $hr_prefix . substr(str_replace("_", "", str_replace(" ", "", $field->getName() . '_year')), 0, 49);
$dataElementYear["domainType"] = "TRACKER";
$dataElementYear["aggregationOperator"] = "sum";
$dataElementYear["type"] = "int";
$dataElementYear["numberType"] = "int";
$dataElements[] = $dataElementYear;
unset($dataElementYear);
}
if ($field->getInputType()->getName() == "Select") {
$optionSet = array();
$optionSet["name"] = $hr_prefix . $field->getName();
$optionSet["code"] = substr(str_replace("_", "", str_replace(" ", "", $field->getName())), 0, 49);
$optionSet["id"] = "o" . substr(md5($optionSet["name"]), 0, 10);
//$optionSet["created"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
//$optionSet["lastUpdated"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
$optionSet["externalAccess"] = "false";
$optionSet["publicAccess"] = "rw------";
$optionSet["version"] = "1";
$options = array();
//Populate optionsets for dropdown fields//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();
$query = "SELECT DISTINCT ResourceTable." . $field->getName() . " FROM " . $resourceTableName . " ResourceTable";
$fieldOptionsResults = $em->getConnection()->executeQuery($query)->fetchAll();
$fieldOptionsResults = $resourceTableEntity->array_value_recursive(strtolower($field->getName()), $fieldOptionsResults);
if (!empty($fieldOptionsResults) && is_array($fieldOptionsResults)) {
foreach ($fieldOptionsResults as $fieldOptionsResultKey => $fieldOptionsResult) {
$option = array();
if (!empty($fieldOptionsResult)) {
$option["name"] = $fieldOptionsResult;
$option["code"] = $fieldOptionsResult;
$option["id"] = "o" . substr(md5($option["name"]), 0, 10);
//$option["created"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
//$option["lastUpdated"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
$option["externalAccess"] = "false";
$options[] = $option;
$allOptions[] = $option;
unset($option);
}
}
$optionSet["options"] = $options;
$optionSets[] = $optionSet;
unset($options);
} else {
if (!empty($fieldOptionsResults) && is_string($fieldOptionsResults)) {
$option = array();
$option["name"] = $fieldOptionsResults;
$option["code"] = $fieldOptionsResults;
$option["id"] = "o" . substr(md5($option["name"]), 0, 10);
$option["externalAccess"] = "false";
$options[] = $option;
$allOptions[] = $option;
unset($option);
$optionSet["options"] = $options;
$optionSets[] = $optionSet;
unset($options);
}
}
$dataElement["optionSet"] = $optionSet;
}
$dataElements[] = $dataElement;
unset($dataElement);
// @todo implement after creation of history date class
// Add History date field for fields with history
if ($field->getHashistory()) {
$dataElementHistoryLastUpdated = array();
$dataElementHistoryLastUpdated["name"] = $hr_prefix . $field->getName() . '_last_updated';
$dataElementHistoryLastUpdated["code"] = substr(str_replace("_", "", str_replace(" ", "", $field->getName() . '_last_updated')), 0, 49);
$dataElementHistoryLastUpdated["id"] = "Flu" . substr($field->getUid(), 5);
//$dataElementHistoryLastUpdated["created"]=$field->getDatecreated()->format('Y-m-d\TH:i:s.000O');
//$dataElementHistoryLastUpdated["lastUpdated"]=$field->getLastupdated()->format('Y-m-d\TH:i:s.000O');
//Inserting minimum dataElementInfo into programStageDataElement
$programStageDataElement = array();
$programStageDataElement["programStage"] = $humanResourceProgramStage;
$programStageDataElement["dataElement"] = $dataElementHistoryLastUpdated;
$programStageDataElement["compulsory"] = "false";
$programStageDataElement["allowProvidedElsewhere"] = "false";
$programStageDataElement["sortOrder"] = "0";
$programStageDataElement["displayInReports"] = "false";
$programStageDataElement["allowFutureDate"] = "false";
$programStageDataElements[] = $programStageDataElement;
//continue with preparting dataElement
$dataElementHistoryLastUpdated["shortName"] = $hr_prefix . substr(str_replace("_", "", str_replace(" ", "", $field->getName() . '_last_updated')), 0, 49);
$dataElementHistoryLastUpdated["domainType"] = "TRACKER";
$dataElementHistoryLastUpdated["aggregationOperator"] = "sum";
$dataElementHistoryLastUpdated["type"] = "date";
$dataElementHistoryLastUpdated["numberType"] = "number";
$dataElements[] = $dataElementHistoryLastUpdated;
unset($dataElementHistoryLastUpdated);
// Additional analysis columns
$dataElementHistoryLastUpdatedMonthText = array();
$dataElementHistoryLastUpdatedMonthText["name"] = $hr_prefix . $field->getName() . '_last_updated_month_text';
$dataElementHistoryLastUpdatedMonthText["code"] = substr(str_replace("_", "", str_replace(" ", "", $field->getName() . '_last_updated_month_text')), 0, 49);
$dataElementHistoryLastUpdatedMonthText["id"] = "Fhmt" . substr($field->getUid(), 6);
//$dataElementHistoryLastUpdatedMonthText["created"]=$field->getDatecreated()->format('Y-m-d\TH:i:s.000O');
//$dataElementHistoryLastUpdatedMonthText["lastUpdated"]=$field->getLastupdated()->format('Y-m-d\TH:i:s.000O');
//Inserting minimum dataElementInfo into programStageDataElement
$programStageDataElement = array();
$programStageDataElement["programStage"] = $humanResourceProgramStage;
$programStageDataElement["dataElement"] = $dataElementHistoryLastUpdatedMonthText;
$programStageDataElement["compulsory"] = "false";
$programStageDataElement["allowProvidedElsewhere"] = "false";
$programStageDataElement["sortOrder"] = "0";
$programStageDataElement["displayInReports"] = "false";
$programStageDataElement["allowFutureDate"] = "false";
$programStageDataElements[] = $programStageDataElement;
//continue with preparting dataElement
$dataElementHistoryLastUpdatedMonthText["shortName"] = $hr_prefix . substr(str_replace("_", "", str_replace(" ", "", $field->getName() . '_last_updated_month_text')), 0, 49);
$dataElementHistoryLastUpdatedMonthText["domainType"] = "TRACKER";
$dataElementHistoryLastUpdatedMonthText["aggregationOperator"] = "sum";
$dataElementHistoryLastUpdatedMonthText["type"] = "string";
$dataElementHistoryLastUpdatedMonthText["textType"] = "text";
//Place optionsets
$dataElementHistoryLastUpdatedMonthText["optionSet"] = $monthOptionSet;
$dataElements[] = $dataElementHistoryLastUpdatedMonthText;
unset($dataElementHistoryLastUpdatedMonthText);
$dataElementHistoryLastUpdatedYear = array();
$dataElementHistoryLastUpdatedYear["name"] = $hr_prefix . $field->getName() . '_last_updated_year';
$dataElementHistoryLastUpdatedYear["code"] = substr(str_replace("_", "", str_replace(" ", "", $field->getName() . '_last_updated_year')), 0, 49);
$dataElementHistoryLastUpdatedYear["id"] = "Fhyr" . substr($field->getUid(), 6);
//$dataElementHistoryLastUpdatedYear["created"]=$field->getDatecreated()->format('Y-m-d\TH:i:s.000O');
//$dataElementHistoryLastUpdatedYear["lastUpdated"]=$field->getLastupdated()->format('Y-m-d\TH:i:s.000O');
//Inserting minimum dataElementInfo into programStageDataElement
$programStageDataElement = array();
$programStageDataElement["programStage"] = $humanResourceProgramStage;
$programStageDataElement["dataElement"] = $dataElementHistoryLastUpdatedYear;
$programStageDataElement["compulsory"] = "false";
$programStageDataElement["allowProvidedElsewhere"] = "false";
$programStageDataElement["sortOrder"] = "0";
$programStageDataElement["displayInReports"] = "false";
$programStageDataElement["allowFutureDate"] = "false";
$programStageDataElements[] = $programStageDataElement;
//continue with preparting dataElement
$dataElementHistoryLastUpdatedYear["shortName"] = $hr_prefix . substr(str_replace("_", "", str_replace(" ", "", $field->getName() . '_last_updated_year')), 0, 49);
$dataElementHistoryLastUpdatedYear["domainType"] = "TRACKER";
$dataElementHistoryLastUpdatedYear["aggregationOperator"] = "sum";
$dataElementHistoryLastUpdatedYear["type"] = "int";
$dataElementHistoryLastUpdatedYear["numberType"] = "int";
$dataElements[] = $dataElementHistoryLastUpdatedYear;
unset($dataElementHistoryLastUpdatedYear);
}
unset($field);
}
// Make OrganisationunitLevels of orgunit
$organisationunitLevels = $em->createQuery('SELECT DISTINCT organisationunitLevel FROM HrisOrganisationunitBundle:OrganisationunitLevel organisationunitLevel ORDER BY organisationunitLevel.level ')->getResult();
foreach ($organisationunitLevels as $organisationunitLevelKey => $organisationunitLevel) {
$organisationunitLevelName = "level" . $organisationunitLevel->getLevel() . "_" . str_replace(',', '_', str_replace('.', '_', str_replace('/', '_', str_replace(' ', '_', $organisationunitLevel->getName()))));
$dataElementOrgunitLevel = array();
$dataElementOrgunitLevel["name"] = $hr_prefix . $organisationunitLevelName;
$dataElementOrgunitLevel["code"] = substr(str_replace("_", "", str_replace(" ", "", $organisationunitLevelName)), 0, 49);
$dataElementOrgunitLevel["id"] = "Lvl" . $organisationunitLevel->getLevel() . substr($organisationunitLevel->getUid(), 6);
//$dataElementOrgunitLevel["created"]=$organisationunitLevel->getDatecreated();
//$dataElementOrgunitLevel["lastUpdated"]=$organisationunitLevel->getLastupdated();
//Inserting minimum dataElementInfo into programStageDataElement
$programStageDataElement = array();
$programStageDataElement["programStage"] = $humanResourceProgramStage;
$programStageDataElement["dataElement"] = $dataElementOrgunitLevel;
$programStageDataElement["compulsory"] = "false";
$programStageDataElement["allowProvidedElsewhere"] = "false";
$programStageDataElement["sortOrder"] = "0";
$programStageDataElement["displayInReports"] = "false";
$programStageDataElement["allowFutureDate"] = "false";
$programStageDataElements[] = $programStageDataElement;
//continue with preparting dataElement
$dataElementOrgunitLevel["shortName"] = $hr_prefix . substr(str_replace("_", "", str_replace(" ", "", $organisationunitLevelName)), 0, 49);
$dataElementOrgunitLevel["domainType"] = "TRACKER";
$dataElementOrgunitLevel["aggregationOperator"] = "sum";
$dataElementOrgunitLevel["type"] = "string";
$dataElementOrgunitLevel["textType"] = "text";
//Place optionsets
$optionSet = array();
$optionSet["name"] = $hr_prefix . $organisationunitLevelName;
$optionSet["code"] = substr(str_replace("_", "", str_replace(" ", "", $organisationunitLevelName)), 0, 49);
$optionSet["id"] = "o" . substr(md5($optionSet["name"]), 0, 10);
//$optionSet["created"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
//$optionSet["lastUpdated"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
$optionSet["externalAccess"] = "false";
$optionSet["publicAccess"] = "rw------";
$optionSet["version"] = "1";
$options = array();
//Populate optionsets for dropdown fields//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();
$query = "SELECT DISTINCT ResourceTable." . $organisationunitLevelName . " FROM " . $resourceTableName . " ResourceTable";
$fieldOptionsResults = $em->getConnection()->executeQuery($query)->fetchAll();
$fieldOptionsResults = $resourceTableEntity->array_value_recursive(strtolower($organisationunitLevelName), $fieldOptionsResults);
if (!empty($fieldOptionsResults) && is_array($fieldOptionsResults)) {
foreach ($fieldOptionsResults as $fieldOptionsResultKey => $fieldOptionsResult) {
$option = array();
if (!empty($fieldOptionsResult)) {
$option["name"] = $fieldOptionsResult;
$option["code"] = $fieldOptionsResult;
$option["id"] = "o" . substr(md5($option["name"]), 0, 10);
//$option["created"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
//$option["lastUpdated"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
$option["externalAccess"] = "false";
$options[] = $option;
$allOptions[] = $option;
unset($option);
}
}
$optionSet["options"] = $options;
$optionSets[] = $optionSet;
unset($options);
} else {
if (!empty($fieldOptionsResults) && is_string($fieldOptionsResults)) {
$option = array();
$option["name"] = $fieldOptionsResults;
$option["code"] = $fieldOptionsResults;
$option["id"] = "o" . substr(md5($option["name"]), 0, 10);
$option["externalAccess"] = "false";
$options[] = $option;
$allOptions[] = $option;
unset($option);
$optionSet["options"] = $options;
$optionSets[] = $optionSet;
unset($options);
}
}
$dataElementOrgunitLevel["optionSet"] = $optionSet;
$dataElements[] = $dataElementOrgunitLevel;
unset($dataElementOrgunitLevel);
}
// Make OrganisationunitGroupsets Column
$organisationunitGroupsets = $em->getRepository('HrisOrganisationunitBundle:OrganisationunitGroupset')->findAll();
foreach ($organisationunitGroupsets as $organisationunitGroupsetKey => $organisationunitGroupset) {
$dataElementOrgunitGroupset = array();
$dataElementOrgunitGroupset["name"] = $hr_prefix . $organisationunitGroupset->getName();
$dataElementOrgunitGroupset["code"] = substr(str_replace("_", "", str_replace(" ", "", $organisationunitGroupset->getName())), 0, 49);
$dataElementOrgunitGroupset["id"] = "Grp" . substr($organisationunitGroupset->getUid(), 5);
//$dataElementOrgunitGroupset["created"]=$organisationunitGroupset->getDatecreated();
//$dataElementOrgunitGroupset["lastUpdated"]=$organisationunitGroupset->getLastupdated();
//Inserting minimum dataElementInfo into programStageDataElement
$programStageDataElement = array();
$programStageDataElement["programStage"] = $humanResourceProgramStage;
$programStageDataElement["dataElement"] = $dataElementOrgunitGroupset;
$programStageDataElement["compulsory"] = "false";
$programStageDataElement["allowProvidedElsewhere"] = "false";
$programStageDataElement["sortOrder"] = "0";
$programStageDataElement["displayInReports"] = "false";
$programStageDataElement["allowFutureDate"] = "false";
$programStageDataElements[] = $programStageDataElement;
//continue with preparting dataElement
$dataElementOrgunitGroupset["shortName"] = $hr_prefix . substr(str_replace("_", "", str_replace(" ", "", $organisationunitGroupset->getName())), 0, 49);
$dataElementOrgunitGroupset["domainType"] = "TRACKER";
$dataElementOrgunitGroupset["aggregationOperator"] = "sum";
$dataElementOrgunitGroupset["type"] = "string";
$dataElementOrgunitGroupset["textType"] = "text";
//Place optionsets
$optionSet = array();
$optionSet["name"] = $hr_prefix . $organisationunitGroupset->getName();
$optionSet["code"] = substr(str_replace("_", "", str_replace(" ", "", $organisationunitGroupset->getName())), 0, 49);
$optionSet["id"] = "o" . substr(md5($optionSet["name"]), 0, 10);
//$optionSet["created"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
//$optionSet["lastUpdated"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
$optionSet["externalAccess"] = "false";
$optionSet["publicAccess"] = "rw------";
$optionSet["version"] = "1";
$options = array();
//Populate optionsets for dropdown fields//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();
$query = "SELECT DISTINCT ResourceTable." . $organisationunitGroupset->getName() . " FROM " . $resourceTableName . " ResourceTable";
$fieldOptionsResults = $em->getConnection()->executeQuery($query)->fetchAll();
$fieldOptionsResults = $resourceTableEntity->array_value_recursive(strtolower($organisationunitGroupset->getName()), $fieldOptionsResults);
if (!empty($fieldOptionsResults) && is_array($fieldOptionsResults)) {
foreach ($fieldOptionsResults as $fieldOptionsResultKey => $fieldOptionsResult) {
$option = array();
if (!empty($fieldOptionsResult)) {
$option["name"] = $fieldOptionsResult;
$option["code"] = $fieldOptionsResult;
$option["id"] = "o" . substr(md5($option["name"]), 0, 10);
//$option["created"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
//$option["lastUpdated"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
$optionSet["externalAccess"] = "false";
$options[] = $option;
$allOptions[] = $option;
unset($option);
}
}
$optionSet["options"] = $options;
$optionSets[] = $optionSet;
unset($options);
} else {
if (!empty($fieldOptionsResults) && is_string($fieldOptionsResults)) {
$option = array();
$option["name"] = $fieldOptionsResults;
$option["code"] = $fieldOptionsResults;
$option["id"] = "o" . substr(md5($option["name"]), 0, 10);
$optionSet["externalAccess"] = "false";
$options[] = $option;
$allOptions[] = $option;
unset($option);
$optionSet["options"] = $options;
$optionSets[] = $optionSet;
unset($options);
}
}
$dataElementOrgunitGroupset["optionSet"] = $optionSet;
$dataElements[] = $dataElementOrgunitGroupset;
unset($dataElementOrgunitGroupset);
}
// Form and Organisationunit name
$dataElementOrgunitName = array();
$dataElementOrgunitName["name"] = $hr_prefix . 'Organisationunit_name';
$dataElementOrgunitName["code"] = substr(str_replace("_", "", str_replace(" ", "", 'Organisationunit_name')), 0, 49);
$dataElementOrgunitName["id"] = "OrgunitName";
//$dataElementOrgunitName["created"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
//$dataElementOrgunitName["lastUpdated"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
//Inserting minimum dataElementInfo into programStageDataElement
$programStageDataElement = array();
$programStageDataElement["programStage"] = $humanResourceProgramStage;
$programStageDataElement["dataElement"] = $dataElementOrgunitName;
$programStageDataElement["compulsory"] = "false";
$programStageDataElement["allowProvidedElsewhere"] = "false";
$programStageDataElement["sortOrder"] = "0";
$programStageDataElement["displayInReports"] = "false";
$programStageDataElement["allowFutureDate"] = "false";
$programStageDataElements[] = $programStageDataElement;
//continue with preparting dataElement
$dataElementOrgunitName["shortName"] = $hr_prefix . substr(str_replace("_", "", str_replace(" ", "", 'Organisationunit_name')), 0, 49);
$dataElementOrgunitName["domainType"] = "TRACKER";
$dataElementOrgunitName["aggregationOperator"] = "sum";
$dataElementOrgunitName["type"] = "string";
$dataElementOrgunitName["textType"] = "text";
//Place optionsets
$optionSet = array();
$optionSet["name"] = $dataElementOrgunitName["name"];
$optionSet["code"] = substr(str_replace("_", "", str_replace(" ", "", 'Organisationunit_name')), 0, 49);
$optionSet["id"] = "o" . substr(md5($optionSet["name"]), 0, 10);
///$optionSet["created"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
//$optionSet["lastUpdated"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
$optionSet["externalAccess"] = "false";
$optionSet["publicAccess"] = "rw------";
$optionSet["version"] = "1";
$options = array();
//Populate optionsets for dropdown fields//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();
$query = "SELECT DISTINCT ResourceTable." . 'Organisationunit_name' . " FROM " . $resourceTableName . " ResourceTable";
$fieldOptionsResults = $em->getConnection()->executeQuery($query)->fetchAll();
$fieldOptionsResults = $resourceTableEntity->array_value_recursive(strtolower('Organisationunit_name'), $fieldOptionsResults);
if (!empty($fieldOptionsResults) && is_array($fieldOptionsResults)) {
foreach ($fieldOptionsResults as $fieldOptionsResultKey => $fieldOptionsResult) {
$option = array();
if (!empty($fieldOptionsResult)) {
$option["name"] = $fieldOptionsResult;
$option["code"] = $fieldOptionsResult;
$option["id"] = "o" . substr(md5($option["name"]), 0, 10);
//$option["created"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
//$option["lastUpdated"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
$optionSet["externalAccess"] = "false";
$options[] = $option;
$allOptions[] = $option;
unset($option);
}
}
$optionSet["options"] = $options;
$optionSets[] = $optionSet;
unset($options);
} else {
if (!empty($fieldOptionsResults) && is_string($fieldOptionsResults)) {
$option = array();
$option["name"] = $fieldOptionsResults;
$option["code"] = $fieldOptionsResults;
$option["id"] = "o" . substr(md5($option["name"]), 0, 10);
$optionSet["externalAccess"] = "false";
$options[] = $option;
$allOptions[] = $option;
unset($option);
$optionSet["options"] = $options;
$optionSets[] = $optionSet;
unset($options);
}
}
$dataElementOrgunitName["optionSet"] = $optionSet;
$dataElements[] = $dataElementOrgunitName;
unset($dataElementOrgunitName);
$dataElementHrhFormName = array();
$dataElementHrhFormName["name"] = $hr_prefix . 'Form_name';
$dataElementHrhFormName["code"] = substr(str_replace("_", "", str_replace(" ", "", 'Form_name')), 0, 49);
$dataElementHrhFormName["id"] = "HrhFormName";
//$dataElementHrhFormName["created"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
//$dataElementHrhFormName["lastUpdated"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
//Inserting minimum dataElementInfo into programStageDataElement
$programStageDataElement = array();
$programStageDataElement["programStage"] = $humanResourceProgramStage;
$programStageDataElement["dataElement"] = $dataElementHrhFormName;
$programStageDataElement["compulsory"] = "false";
$programStageDataElement["allowProvidedElsewhere"] = "false";
$programStageDataElement["sortOrder"] = "0";
$programStageDataElement["displayInReports"] = "false";
$programStageDataElement["allowFutureDate"] = "false";
$programStageDataElements[] = $programStageDataElement;
//continue with preparting dataElement
$dataElementHrhFormName["shortName"] = $hr_prefix . 'Form_name';
$dataElementHrhFormName["domainType"] = "TRACKER";
$dataElementHrhFormName["aggregationOperator"] = "sum";
$dataElementHrhFormName["type"] = "string";
$dataElementHrhFormName["textType"] = "text";
//Place optionsets
$optionSet = array();
$optionSet["name"] = $dataElementHrhFormName["name"];
$optionSet["code"] = substr(str_replace("_", "", str_replace(" ", "", $dataElementHrhFormName["name"])), 0, 49);
$optionSet["id"] = "o" . substr(md5($optionSet["name"]), 0, 10);
//$optionSet["created"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
//$optionSet["lastUpdated"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
$optionSet["externalAccess"] = "false";
$optionSet["publicAccess"] = "rw------";
$optionSet["version"] = "1";
$options = array();
//Populate optionsets for dropdown fields//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();
$query = "SELECT DISTINCT ResourceTable." . 'Form_name' . " FROM " . $resourceTableName . " ResourceTable";
$fieldOptionsResults = $em->getConnection()->executeQuery($query)->fetchAll();
$fieldOptionsResults = $resourceTableEntity->array_value_recursive(strtolower('Form_name'), $fieldOptionsResults);
if (!empty($fieldOptionsResults) && is_array($fieldOptionsResults)) {
foreach ($fieldOptionsResults as $fieldOptionsResultKey => $fieldOptionsResult) {
$option = array();
if (!empty($fieldOptionsResult)) {
$option["name"] = $fieldOptionsResult;
$option["code"] = $fieldOptionsResult;
$option["id"] = "o" . substr(md5($option["name"]), 0, 10);
//$option["created"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
//$option["lastUpdated"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
$option["externalAccess"] = "false";
$options[] = $option;
$allOptions[] = $option;
unset($option);
}
}
$optionSet["options"] = $options;
$optionSets[] = $optionSet;
unset($options);
} else {
if (!empty($fieldOptionsResults) && is_string($fieldOptionsResults)) {
$option = array();
$option["name"] = $fieldOptionsResults;
$option["code"] = $fieldOptionsResults;
$option["id"] = "o" . substr(md5($option["name"]), 0, 10);
$option["externalAccess"] = "false";
$options[] = $option;
$allOptions[] = $option;
unset($option);
$optionSet["options"] = $options;
$optionSets[] = $optionSet;
unset($options);
}
}
$dataElementHrhFormName["optionSet"] = $optionSet;
$dataElements[] = $dataElementHrhFormName;
unset($dataElementHrhFormName);
$dataElementInstance = array();
$dataElementInstance["name"] = $hr_prefix . 'instance';
$dataElementInstance["code"] = substr(str_replace("_", "", str_replace(" ", "", 'instance')), 0, 49);
$dataElementInstance["id"] = "recInstance";
//$dataElementInstance["created"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
//$dataElementInstance["lastUpdated"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
//Inserting minimum dataElementInfo into programStageDataElement
$programStageDataElement = array();
$programStageDataElement["programStage"] = $humanResourceProgramStage;
$programStageDataElement["dataElement"] = $dataElementInstance;
$programStageDataElement["compulsory"] = "false";
$programStageDataElement["allowProvidedElsewhere"] = "false";
$programStageDataElement["sortOrder"] = "0";
$programStageDataElement["displayInReports"] = "false";
$programStageDataElement["allowFutureDate"] = "false";
$programStageDataElements[] = $programStageDataElement;
//continue with preparting dataElement
$dataElementInstance["shortName"] = $hr_prefix . 'instance';
$dataElementInstance["domainType"] = "TRACKER";
$dataElementInstance["aggregationOperator"] = "sum";
$dataElementInstance["type"] = "string";
$dataElementInstance["textType"] = "text";
$dataElements[] = $dataElementInstance;
unset($dataElementInstanc);
$metaData["dataElements"] = $dataElements;
//Setting up program stages
$programStages = array();
$completeProgramStage = $humanResourceProgramStage;
$completeProgramStage["minDaysFromStart"] = 0;
$completeProgramStage["program"] = $humanResourceProgram;
$completeProgramStage["programStageDataElements"] = $programStageDataElements;
$completeProgramStage["reportDateDescription"] = "Report date";
$completeProgramStage["autoGenerateEvent"] = "true";
$completeProgramStage["validCompleteOnly"] = "false";
$completeProgramStage["displayGenerateEventBox"] = "false";
$completeProgramStage["captureCoordinates"] = "false";
$completeProgramStage["blockEntryForm"] = "false";
$completeProgramStage["preGenerateUID"] = "false";
$completeProgramStage["remindCompleted"] = "false";
$completeProgramStage["generatedByEnrollmentDate"] = "false";
$completeProgramStage["allowGenerateNextVisit"] = "false";
$completeProgramStage["openAfterEnrollment"] = "false";
$completeProgramStage["dataEntryType"] = "default";
$completeProgramStage["defaultTemplateMessage"] = "Dear {person-name} please come to your appointment on {program-stage-name} at {due-date}";
$completeProgramStage["id"] = "NHIu1snluiZ";
$completeProgramStage["repeatable"] = "false";
$programStages[] = $completeProgramStage;
$metaData["programStages"] = $programStages;
$programs = array();
$humanResourceProgram["name"] = $hr_prefix . "HumanResource";
//$humanResourceProgram["created"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
//$humanResourceProgram["lastUpdated"]=(new \DateTime('now'))->format('Y-m-d\TH:i:s.000O');
$humanResourceProgram["description"] = "Raw Data from HRH Database";
$humanResourceProgram["version"] = "1";
$humanResourceProgram["version"] = 1;
$humanResourceProgram["dateOfEnrollmentDescription"] = "Enrollment Date";
$humanResourceProgram["dateOfIncidentDescription"] = "Incident Date";
$humanResourceProgram["type"] = 3;
$humanResourceProgram["displayIncidentDate"] = false;
$humanResourceProgram["ignoreOverdueEvents"] = false;
$humanResourceProgram["onlyEnrollOnce"] = false;
$humanResourceProgram["selectEnrollmentDatesInFuture"] = false;
$humanResourceProgram["selectIncidentDatesInFuture"] = false;
$humanResourceProgram["relationshipText"] = "";
$humanResourceProgram["dataEntryMethod"] = false;
$humanResourceProgram["registration"] = false;
$humanResourceProgram["singleEvent"] = true;
$humanResourceProgram["kind"] = "SINGLE_EVENT_WITHOUT_REGISTRATION";
$humanResourceProgram["programStages"] = array($humanResourceProgramStage);
$programs[] = $humanResourceProgram;
$metaData["programs"] = $programs;
$metaData["options"] = $allOptions;
$metaData["optionSets"] = $optionSets;
$serializer = new Serializer(array($normalizer), array($encoder));
$outputMetadata = $serializer->serialize($metaData, $this->resourceTableNature);
// Output: {"name":"foo","sportsman":false}
$this->messageLog = $outputMetadata;
$output->writeln($this->messageLog);
}
protected function execute(InputInterface $input, OutputInterface $output)
{
$em = $this->getContainer()->get('doctrine')->getManager();
$logger = $this->getContainer()->get('logger');
$year = date("Y") - 1;
$interval = 20;
$stopwatch = new Stopwatch();
$stopwatch->start('syncing');
$id = $input->getArgument('id');
$capturedYear = $input->getArgument('year');
$orgunitid = $input->getArgument('orgunitid');
$entity = $em->getRepository('HrisIntergrationBundle:DHISDataConnection')->find($id);
if (!empty($orgunitid)) {
$orgunit = $em->getRepository('HrisOrganisationunitBundle:Organisationunit')->find($orgunitid);
} else {
$orgunit = $entity->getParentOrganisationunit();
}
if (!empty($capturedYear)) {
$year = $capturedYear;
}
if ($id) {
$this->dhisConnectionId = $id;
} else {
throw new NotFoundHttpException("Data connection not found!");
}
$this->xmlContents = "<?xml version='1.0' encoding='UTF-8'?>\n<dataValueSet xmlns=\"http://dhis2.org/schema/dxf/2.0\">";
$xmlFile = "/tmp/hrhis_data_" . str_replace(' ', '_', $orgunit) . ".xml";
file_put_contents($xmlFile, $this->xmlContents);
// Aggregate data for each orgunit in the current level.
$em = $this->getContainer()->get('doctrine')->getManager();
$entity = $em->getRepository('HrisIntergrationBundle:DHISDataConnection')->find($this->dhisConnectionId);
/*
* Initializing query for dhis dataset calculation
*/
// Get Standard Resource table name
$resourceTableName = str_replace(' ', '_', trim(strtolower(ResourceTable::getStandardResourceTableName())));
$resourceTableAlias = "ResourceTable";
/*
* Aggregate organisationunit for all the children
*/
$queryBuilder = $em->createQueryBuilder();
$allOrganisationunitsChildren = $queryBuilder->select('DISTINCT organisationunit')->from('HrisOrganisationunitBundle:Organisationunit', 'organisationunit')->join('organisationunit.organisationunitStructure', 'organisationunitStructure')->join('organisationunitStructure.level', 'level')->andWhere('
(
level.level >= :organisationunitLevel
AND organisationunitStructure.level' . $orgunit->getOrganisationunitStructure()->getLevel()->getLevel() . 'Organisationunit=:levelOrganisationunit
) AND organisationunit.dhisUid is not null
AND organisationunit.id IN ( SELECT DISTINCT(recordOrganisationunit.id) FROM HrisRecordsBundle:Record record INNER JOIN record.organisationunit recordOrganisationunit )')->setParameters(array('levelOrganisationunit' => $orgunit, 'organisationunitLevel' => $orgunit->getOrganisationunitStructure()->getLevel()->getLevel()))->getQuery()->getResult();
// Query for Options to exclude from reports
$fieldOptionsToSkip = $this->getContainer()->get('doctrine')->getManager()->getRepository('HrisFormBundle:FieldOption')->findBy(array('skipInReport' => True));
//filter the records with exclude report tag
foreach ($fieldOptionsToSkip as $key => $fieldOptionToSkip) {
if (empty($fieldOptionsToSkipQuery)) {
$fieldOptionsToSkipQuery = "{$resourceTableAlias}." . $fieldOptionToSkip->getField()->getName() . " !='" . $fieldOptionToSkip->getValue() . "'";
} else {
$fieldOptionsToSkipQuery .= " AND {$resourceTableAlias}." . $fieldOptionToSkip->getField()->getName() . " !='" . $fieldOptionToSkip->getValue() . "'";
}
}
$dataValueColumnName = NULL;
$selectQuery = NULL;
$organisationunitNames = NULL;
$incr = 0;
$totalIncr = 0;
$lastLap = $stopwatch->lap('syncing');
$lastLapDuration = round($lastLap->getDuration() / 1000, 2);
$previousTotalLapTime = round($lastLap->getDuration() / 1000, 2);
foreach ($allOrganisationunitsChildren as $organisationunitKey => $organisationunit) {
$organisationunitNames .= $organisationunit->getLongname() . ', ';
$incr++;
// Aggregate data for each orgunit in the current level.
$em = $this->getContainer()->get('doctrine')->getManager();
$entity = $em->getRepository('HrisIntergrationBundle:DHISDataConnection')->find($this->dhisConnectionId);
$fromClause = " FROM {$resourceTableName} {$resourceTableAlias} ";
$organisationunitLevelsWhereClause = " {$resourceTableAlias}.organisationunit_id=" . $organisationunit->getId() . " ";
// Dataelement field option relation
$dataelementFieldOptionRelation = $entity->getDataelementFieldOptionRelation();
foreach ($dataelementFieldOptionRelation as $dataelementFieldOptionKey => $dataelementFieldOptionValue) {
// Formulate Query for calculating field option
$columnFieldOptionGroup = $dataelementFieldOptionValue->getColumnFieldOptionGroup();
$rowFieldOptionGroup = $dataelementFieldOptionValue->getRowFieldOptionGroup();
$seriesFieldName = $rowFieldOptionGroup->getName();
//Column Query construction
$queryColumnNames[] = str_replace('-', '_', str_replace(' ', '', $columnFieldOptionGroup->getName()));
$categoryColumnFieldNames[] = $columnFieldOptionGroup->getField()->getName();
$categoryRowFieldName = $columnFieldOptionGroup->getField()->getName();
$columnWhereClause = NULL;
foreach ($columnFieldOptionGroup->getFieldOption() as $columnFieldOptionKey => $columnFieldOption) {
$operator = $columnFieldOptionGroup->getOperator();
if (empty($operator)) {
$operator = "OR";
}
$categoryColumnFieldOptionValue = str_replace('-', '_', $columnFieldOption->getValue());
$categoryColumnFieldName = $columnFieldOption->getField()->getName();
$categoryColumnResourceTableName = $resourceTableAlias;
if (!empty($columnWhereClause)) {
$columnWhereClause = $columnWhereClause . " " . strtoupper($operator) . " {$categoryColumnResourceTableName}.{$categoryColumnFieldName}='" . $categoryColumnFieldOptionValue . "'";
} else {
$columnWhereClause = "{$categoryColumnResourceTableName}.{$categoryColumnFieldName}='" . $categoryColumnFieldOptionValue . "'";
}
}
// Row Query construction
$rowWhereClause = NULL;
foreach ($rowFieldOptionGroup->getFieldOption() as $rowFieldOptionKey => $rowFieldOption) {
$operator = $rowFieldOptionGroup->getOperator();
if (empty($operator)) {
$operator = "OR";
}
$categoryRowFieldOptionValue = str_replace('-', '_', $rowFieldOption->getValue());
$categoryRowFieldName = $rowFieldOption->getField()->getName();
$categoryRowResourceTableName = $resourceTableAlias;
if (!empty($rowWhereClause)) {
$rowWhereClause = $rowWhereClause . " " . strtoupper($operator) . " {$categoryRowResourceTableName}.{$categoryRowFieldName}='" . $categoryRowFieldOptionValue . "'";
} else {
$rowWhereClause = "{$categoryRowResourceTableName}.{$categoryRowFieldName}='" . $categoryRowFieldOptionValue . "'";
}
}
$verboseDataValaueColumnNames = $dataelementFieldOptionValue->getRowFieldOptionGroup() . "--" . $dataelementFieldOptionValue->getColumnFieldOptionGroup();
$dataValueColumnName = $organisationunit->getDhisUid() . '--' . $dataelementFieldOptionValue->getDataelementUid() . '--' . $dataelementFieldOptionValue->getCategoryComboUid();
if (!empty($selectQuery)) {
$selectQuery .= " UNION ALL SELECT CAST('" . $dataValueColumnName . "' AS text) AS datavaluelabel, COUNT(DISTINCT(instance)) AS value " . " {$fromClause} WHERE ({$rowWhereClause}) AND ({$columnWhereClause}) AND " . $resourceTableAlias . ".first_appointment_year<=" . $year . " AND {$organisationunitLevelsWhereClause}" . (!empty($fieldOptionsToSkipQuery) ? " AND ( {$fieldOptionsToSkipQuery} )" : "") . " HAVING COUNT(DISTINCT(instance))>0";
} else {
$selectQuery = "SELECT CAST('" . $dataValueColumnName . "' AS text) AS datavaluelabel, COUNT(DISTINCT(instance)) AS value " . " {$fromClause} WHERE ({$rowWhereClause}) AND ({$columnWhereClause}) AND " . $resourceTableAlias . ".first_appointment_year<=" . $year . " AND {$organisationunitLevelsWhereClause}" . (!empty($fieldOptionsToSkipQuery) ? " AND ( {$fieldOptionsToSkipQuery} )" : "") . " HAVING COUNT(DISTINCT(instance))>0";
}
unset($dhisUid);
// Intercept after certain number of orgunits for fetching results
// So as not to exceed database max_stack_depth
if ($incr >= $interval) {
// Reset counter
$totalIncr = $incr + $totalIncr;
$incr = 0;
// Process SQL Batch
$sqlResult = $this->getContainer()->get('doctrine')->getManager()->getConnection()->fetchAll($selectQuery);
foreach ($sqlResult as $resultKey => $resultRow) {
$dataValueKeys = explode('--', $resultRow['datavaluelabel']);
//dhisUid--dataelementUid--categoryComboUid$recordRow ='<!--'.$resultRow['datavaluelabelverbose'].'-->';
$recordRow = '<dataValue orgUnit="' . $dataValueKeys[0] . '" period="' . $year . '" dataElement="' . $dataValueKeys[1] . '" categoryOptionCombo="' . $dataValueKeys[2] . '" value="' . $resultRow['value'] . '" storedBy="hrhis" lastUpdated="' . date("c") . '" followUp="false" />';
file_put_contents($xmlFile, $recordRow, FILE_APPEND);
}
$currentLap = $stopwatch->lap('syncing');
$currentLapDuration = NULL;
$currentLapDuration = round($currentLap->getDuration() / 1000, 2) - $previousTotalLapTime;
if ($currentLapDuration < 60) {
$durationMessage = round($currentLapDuration, 2) . ' seconds';
} elseif ($currentLapDuration >= 60 && $currentLapDuration < 3600) {
$durationMessage = round($currentLapDuration / 60, 2) . ' minutes';
} elseif ($currentLapDuration >= 3600 && $currentLapDuration < 216000) {
$durationMessage = round($currentLapDuration / 3600, 2) . ' hours';
} else {
$durationMessage = round($currentLapDuration / 86400, 2) . ' hours';
}
$lastLapDuration = NULL;
$lastLapDuration = $currentLapDuration;
$previousTotalLapTime = round($currentLap->getDuration() / 1000, 2);
$output->writeln('Fetched records for ' . $totalIncr . ' out of ' . count($allOrganisationunitsChildren) . ' organisationunits in ' . $durationMessage . " " . count($sqlResult) . " results found");
//$output->writeln('Organisationunits parsed:'."\n".$organisationunitNames."\n");
$selectQuery = NULL;
$organisationunitNames = NULL;
}
}
}
// Process last remaining SQL Batch
$sqlResult = $this->getContainer()->get('doctrine')->getManager()->getConnection()->fetchAll($selectQuery);
foreach ($sqlResult as $resultKey => $resultRow) {
$dataValueKeys = explode('--', $resultRow['datavaluelabel']);
//dhisUid--dataelementUid--categoryComboUid
$recordRow = '<dataValue orgUnit="' . $dataValueKeys[0] . '" period="' . $year . '" dataElement="' . $dataValueKeys[1] . '" categoryOptionCombo="' . $dataValueKeys[2] . '" value="' . $resultRow['value'] . '" storedBy="hrhis" lastUpdated="' . date("c") . '" followUp="false" />';
file_put_contents($xmlFile, $recordRow, FILE_APPEND);
}
$currentLap = $stopwatch->lap('syncing');
$currentLapDuration = NULL;
$currentLapDuration = round($currentLap->getDuration() / 1000, 2) - $previousTotalLapTime;
if ($currentLapDuration < 60) {
$durationMessage = round($currentLapDuration, 2) . ' seconds';
} elseif ($currentLapDuration >= 60 && $currentLapDuration < 3600) {
$durationMessage = round($currentLapDuration / 60, 2) . ' minutes';
} elseif ($currentLapDuration >= 3600 && $currentLapDuration < 216000) {
$durationMessage = round($currentLapDuration / 3600, 2) . ' hours';
} else {
$durationMessage = round($currentLapDuration / 86400, 2) . ' hours';
}
$lastLapDuration = NULL;
$lastLapDuration = $currentLapDuration;
$previousTotalLapTime = round($currentLap->getDuration() / 1000, 2);
$output->writeln('Fetched records for ' . $interval . ' out of ' . count($allOrganisationunitsChildren) . ' organisationunits in ' . $durationMessage . " " . count($sqlResult) . " results found ");
//$output->writeln('Organisationunits parsed:'."\n".$organisationunitNames."\n");
$selectQuery = NULL;
$organisationunitNames = NULL;
$this->xmlContents = $this->xmlContents . '</dataValueSet>';
file_put_contents($xmlFile, '</dataValueSet>', FILE_APPEND);
/*
* Check Clock for time spent
*/
$totalTime = $stopwatch->stop('syncing');
$duration = $totalTime->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';
}
$output->writeln("Data Syncing completed in " . $durationMessage . ".\n\n");
$this->messageLog = "Sync aggregation for " . $orgunit->getLongname() . " operation is complete";
$output->writeln($this->messageLog);
}
/**
* Download aggregated reports by Cadre
*
* @Secure(roles="ROLE_SUPER_USER,ROLE_REPORTAGGREGATION_DOWNLOADRECORDS")
* @Route("/records", name="report_aggregation_download_records")
* @Method("GET")
* @Template()
*/
public function recordsAction(Request $request)
{
$em = $this->getDoctrine()->getManager();
$organisationUnitid = $request->query->get('organisationUnit');
$formsId = explode(",", $request->query->get('formsId'));
$organisationunitGroupsId = explode(",", $request->query->get('organisationunitGroupsId'));
$withLowerLevels = $request->query->get('withLowerLevels');
$fieldsId = $request->query->get('fields');
$fieldsTwoId = $request->query->get('fieldsTwo');
$forms = new ArrayCollection();
$organisationunitGroups = new ArrayCollection();
//Get the objects from the the variables
$organisationUnit = $em->getRepository('HrisOrganisationunitBundle:Organisationunit')->find($organisationUnitid);
$fields = $em->getRepository('HrisFormBundle:Field')->find($fieldsId);
$fieldsTwo = $em->getRepository('HrisFormBundle:Field')->find($fieldsTwoId);
foreach ($formsId as $formId) {
$forms->add($em->getRepository('HrisFormBundle:Form')->find($formId));
}
foreach ($organisationunitGroupsId as $organisationunitGroupId) {
if ($organisationunitGroupId != NULL) {
$organisationunitGroups->add($em->getRepository('HrisOrganisationunitBundle:OrganisationunitGroup')->find($organisationunitGroupId));
}
}
//get the list of options to exclude from the reports
$fieldOptionsToExclude = $em->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]);
}
}
//Pull the organisation unit Structure
$selectedOrgunitStructure = $em->getRepository('HrisOrganisationunitBundle:OrganisationunitStructure')->findOneBy(array('organisationunit' => $organisationUnit->getId()));
$resourceTableName = ResourceTable::getStandardResourceTableName();
//create the query to select the records from the resource table
$query = "SELECT ResourceTable.firstname, ResourceTable.middlename, ResourceTable.surname, ResourceTable.profession,ResourceTable." . $fields->getName();
if ($fieldsTwo->getId() != $fields->getId()) {
$query .= " ,ResourceTable." . $fieldsTwo->getName();
}
$query .= " ,Orgunit.longname 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
$groups = NULL;
foreach ($organisationunitGroups as $organisationunitGroup) {
if ($organisationunitGroup != NULL) {
$groups .= "'" . $organisationunitGroup->getName() . "',";
}
}
if ($groups != NULL) {
//remove the last comma in the query
$groups = rtrim($groups, ",");
$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() . "'";
}
//sort the records by cadre
$query .= " ORDER BY ResourceTable.profession, " . $fields->getName();
$results = $em->getConnection()->executeQuery($query)->fetchAll();
//create the title
$title = 'List of Records by ' . $fields->getCaption();
if ($fieldsId != $fieldsTwoId) {
$title .= " and " . $fieldsTwo->getCaption();
}
$title .= ' for ' . $organisationUnit->getLongname();
if ($withLowerLevels == 1) {
$title .= " with lower levels";
}
// ask the service for a Excel5
$excelService = $this->get('phpexcel')->createPHPExcelObject();
$excelService->getProperties()->setCreator("HRHIS3")->setLastModifiedBy("HRHIS3")->setTitle($title)->setSubject("Office 2005 XLSX Test Document")->setDescription("Test document for Office 2005 XLSX, generated using PHP classes.")->setKeywords("office 2005 openxml php")->setCategory("Test result file");
//write the header of the report
$column = 'A';
$row = 1;
$date = "Date: " . date("jS F Y");
$excelService->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);
$excelService->getActiveSheet()->getDefaultColumnDimension()->setWidth(15);
$excelService->setActiveSheetIndex(0)->setCellValue($column . $row++, $title)->setCellValue($column . $row, $date);
//add style to the header
$heading_format = array('font' => array('bold' => true, 'color' => array('rgb' => '3333FF')), 'alignment' => array('wrap' => true, 'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER));
//add style to the Value header
$header_format = array('font' => array('bold' => true, 'color' => array('rgb' => 'FFFFFF')), 'alignment' => array('wrap' => true, 'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'fill' => array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => '000099')));
//add style to the text to display
$text_format1 = array('font' => array('bold' => false, 'color' => array('rgb' => '000000')), 'alignment' => array('wrap' => true, 'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_LEFT));
//add style to the Value header
$text_format2 = array('font' => array('bold' => false, 'color' => array('rgb' => '000000')), 'alignment' => array('wrap' => true, 'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_LEFT), 'fill' => array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => 'E0E0E0')));
$excelService->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
$excelService->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
//reset the colomn and row number
$column == 'A';
$row += 2;
//apply the styles
if ($fields->getId() != $fieldsTwo->getId()) {
$cellMerge = 'F';
} else {
$cellMerge = 'E';
}
$excelService->getActiveSheet()->getStyle('A1:' . $cellMerge . '2')->applyFromArray($heading_format);
$excelService->getActiveSheet()->mergeCells('A1:' . $cellMerge . '1');
$excelService->getActiveSheet()->mergeCells('A2:' . $cellMerge . '2');
//write the table heading of the values
$excelService->getActiveSheet()->getStyle('A4:' . $cellMerge . '4')->applyFromArray($header_format);
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, 'SN')->setCellValue($column++ . $row, 'Name')->setCellValue($column++ . $row, 'Profession');
if ($fields->getName() != "profession") {
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, $fields->getCaption());
}
if ($fields->getId() != $fieldsTwo->getId()) {
if ($fieldsTwo->getName() != "profession") {
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, $fieldsTwo->getCaption());
}
}
$excelService->setActiveSheetIndex(0)->setCellValue($column . $row, 'Facility Name');
//write the values
$i = 1;
//count the row
foreach ($results as $result) {
$column = 'A';
//return to the 1st column
$row++;
//increment one row
//format of the row
if ($row % 2 == 1) {
$excelService->getActiveSheet()->getStyle($column . $row . ':' . $cellMerge . $row)->applyFromArray($text_format1);
} else {
$excelService->getActiveSheet()->getStyle($column . $row . ':' . $cellMerge . $row)->applyFromArray($text_format2);
}
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, $i++)->setCellValue($column++ . $row, $result['firstname'] . ' ' . $result['middlename'] . ' ' . $result['surname'])->setCellValue($column++ . $row, $result['profession']);
if ($fields->getName() != "profession") {
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, $result[strtolower($fields->getName())]);
}
if ($fields->getId() != $fieldsTwo->getId()) {
if ($fieldsTwo->getName() != "profession") {
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, $result[strtolower($fieldsTwo->getName())]);
}
}
$excelService->setActiveSheetIndex(0)->setCellValue($column . $row, $result['longname']);
}
$excelService->getActiveSheet()->setTitle('List of Records');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$excelService->setActiveSheetIndex(0);
// create the writer
$writer = $this->get('phpexcel')->createWriter($excelService, 'Excel5');
// create the response
$response = $this->get('phpexcel')->createStreamedResponse($writer);
$response->headers->set('Content-Type', 'text/vnd.ms-excel; charset=utf-8');
$response->headers->set('Content-Disposition', 'attachment;filename=' . str_replace(" ", "_", $title) . '.xls');
$response->headers->set('Pragma', 'public');
$response->headers->set('Cache-Control', 'maxage=1');
//$response->sendHeaders();
return $response;
}
/**
* Download records reports
*
* @Secure(roles="ROLE_SUPER_USER,ROLE_REPORTRECORDS_DOWNLOADBYCADRE")
* @Route("/download_bycarde", name="report_employeerecords_download_bycarde")
* @Method("GET")
* @Template()
*/
public function downloadByCardeAction(Request $request)
{
$em = $this->getDoctrine()->getManager();
$organisationUnitid = $request->query->get('organisationUnit');
$withLowerLevels = $request->query->get('withLowerLevels');
$forms = new ArrayCollection();
//Get the objects from the the variables
$formNames = '';
$formid = '';
$organisationUnit = $em->getRepository('HrisOrganisationunitBundle:Organisationunit')->find($organisationUnitid);
$proffesionFieldId = $em->getRepository('HrisFormBundle:Field')->findOneBy(array('name' => "profession"))->getId();
foreach ($request->query->get('formsId') as $formIds) {
$form = $em->getRepository('HrisFormBundle:Form')->find($formIds);
$formNames .= $form->getName() . ',';
$forms->add($form);
$formid .= $formIds . ',';
}
$formNames = rtrim($formNames, ",");
$formid = rtrim($formid, ",");
$title = "Records Report for " . $organisationUnit->getLongname();
if ($withLowerLevels) {
$title .= " with Lower Levels";
}
$title .= " for " . $formNames;
$title .= ' Order by Cadre';
$selectedOrgunitStructure = $em->getRepository('HrisOrganisationunitBundle:OrganisationunitStructure')->findOneBy(array('organisationunit' => $organisationUnit->getId()));
$resourceTableName = ResourceTable::getStandardResourceTableName();
$sql = "SELECT * FROM (SELECT DISTINCT ON (field_id) field_id, form_id, sort, name,caption FROM hris_form_fieldmembers inner join hris_field ON hris_field.id=hris_form_fieldmembers.field_id WHERE form_id in ({$formid})) as T order by T.sort";
$results = $em->getConnection()->executeQuery($sql)->fetchAll();
//get the list of options to exclude from the reports
$fieldOptionsToExclude = $em->getRepository('HrisFormBundle:FieldOption')->findBy(array('skipInReport' => "YES"));
//create the query to select the records from the resource table
$query = "SELECT ";
foreach ($results as $key => $value) {
$query .= "ResourceTable." . strtolower($value['name']) . " ,";
}
// Make Levels of orgunit
$orgunitLevels = $em->createQuery('SELECT DISTINCT ol FROM HrisOrganisationunitBundle:OrganisationunitLevel ol ORDER BY ol.level ')->getResult();
foreach ($orgunitLevels as $orgunitLevelLevelKey => $orgunitLevel) {
$orgunitLevelName = "level" . $orgunitLevel->getLevel() . "_" . str_replace(' ', '_', str_replace('.', '_', str_replace('/', '_', $orgunitLevel->getName())));
$query .= "ResourceTable." . $orgunitLevelName . " ,";
}
// Make Groupset Column
$groupsets = $em->getRepository('HrisOrganisationunitBundle:OrganisationunitGroupset')->findAll();
foreach ($groupsets as $groupsetKey => $groupset) {
$query .= "ResourceTable." . $groupset->getName() . " ,";
}
// Calculated fields
$query .= "ResourceTable.form_name ,";
//From Clause
$query .= " Orgunit.longname 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 .= " INNER JOIN ( SELECT * FROM hris_fieldoption where field_id=" . $proffesionFieldId . ") AS fieldoption ON fieldoption.value = ResourceTable.profession ";
$query .= " WHERE ResourceTable.form_id in (" . $formid . ")";
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 with exclude report tag
foreach ($fieldOptionsToExclude as $key => $fieldOptionToExclude) {
$query .= " AND " . $fieldOptionToExclude->getField()->getName() . " !='" . $fieldOptionToExclude->getValue() . "'";
}
$query .= " ORDER BY ResourceTable.profession, ResourceTable.first_appointment";
#CHANGE TO THIS WHEN ALL THE FIELD OPTIONS ARE SORTED
//$query .= " ORDER BY fieldoption.sort, ResourceTable.first_appointment";
$report = $em->getConnection()->executeQuery($query)->fetchAll();
// ask the service for a Excel5
$excelService = $this->get('phpexcel')->createPHPExcelObject();
$excelService->getProperties()->setCreator("HRHIS3")->setLastModifiedBy("HRHIS3")->setTitle($title)->setSubject("Office 2005 XLSX Test Document")->setDescription("Test document for Office 2005 XLSX, generated using PHP classes.")->setKeywords("office 2005 openxml php")->setCategory("Test result file");
//write the header of the report
$column = 'A';
$row = 1;
$date = "Date: " . date("jS F Y");
$excelService->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);
$excelService->getActiveSheet()->getDefaultColumnDimension()->setWidth(15);
$excelService->setActiveSheetIndex(0)->setCellValue($column . $row++, $title)->setCellValue($column . $row, $date);
//add style to the header
$heading_format = array('font' => array('bold' => true, 'color' => array('rgb' => '3333FF')), 'alignment' => array('wrap' => true, 'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER));
//add style to the Value header
$header_format = array('font' => array('bold' => true, 'color' => array('rgb' => 'FFFFFF')), 'alignment' => array('wrap' => true, 'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'fill' => array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => '000099')));
//add style to the text to display
$text_format1 = array('font' => array('bold' => false, 'color' => array('rgb' => '000000')), 'alignment' => array('wrap' => true, 'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_LEFT));
//add style to the Value header
$text_format2 = array('font' => array('bold' => false, 'color' => array('rgb' => '000000')), 'alignment' => array('wrap' => true, 'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_LEFT), 'fill' => array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => 'E0E0E0')));
$excelService->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
$excelService->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
//reset the colomn and row number
$column == 'A';
$columnmerge = 'A';
$row += 2;
//calculate the width of the styles
for ($i = 1; $i < count($results) + 2 + sizeof($orgunitLevels) + sizeof($groupsets) + 1; $i++) {
$columnmerge++;
}
//apply the styles
$excelService->getActiveSheet()->getStyle('A1:' . $columnmerge . '2')->applyFromArray($heading_format);
$excelService->getActiveSheet()->mergeCells('A1:' . $columnmerge . '1');
$excelService->getActiveSheet()->mergeCells('A2:' . $columnmerge . '2');
//write the values
$i = 1;
//count the row
$currentProfessional = null;
foreach ($report as $rows) {
$column = 'A';
//return to the 1st column
$row++;
//increment one row
if ($currentProfessional != $rows['profession']) {
//write the heading for the professional
$row++;
$excelService->getActiveSheet()->getStyle($column . $row . ':D' . $row)->applyFromArray($heading_format);
$excelService->getActiveSheet()->mergeCells($column . $row . ':D' . $row);
$excelService->setActiveSheetIndex(0)->setCellValue($column . $row, $rows['profession']);
//Write the heading for the data
$row++;
$column = 'A';
//reset to the first column
$excelService->getActiveSheet()->getStyle($column . $row . ':' . $columnmerge . $row)->applyFromArray($header_format);
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, 'SN');
foreach ($results as $key => $value) {
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, $value['caption']);
}
// Make Levels of orgunit
foreach ($orgunitLevels as $orgunitLevelLevelKey => $orgunitLevel) {
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, $orgunitLevel->getName());
}
// Make Groupset Column
foreach ($groupsets as $groupsetKey => $groupset) {
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, $groupset->getName());
}
// Calculated fields
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, 'Form Name')->setCellValue($column . $row, 'Duty Post');
$i = 1;
//reset the serial number
$row++;
$column = 'A';
//return to the 1st column
}
//format of the row
if ($row % 2 == 1) {
$excelService->getActiveSheet()->getStyle($column . $row . ':' . $columnmerge . $row)->applyFromArray($text_format1);
} else {
$excelService->getActiveSheet()->getStyle($column . $row . ':' . $columnmerge . $row)->applyFromArray($text_format2);
}
//Display the serial number
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, $i++);
foreach ($results as $key => $value) {
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, str_replace('=', '', $rows[strtolower($value['name'])]));
}
// Make Levels of orgunit
foreach ($orgunitLevels as $orgunitLevelLevelKey => $orgunitLevel) {
$orgunitLevelName = "level" . $orgunitLevel->getLevel() . "_" . str_replace(' ', '_', str_replace('.', '_', str_replace('/', '_', $orgunitLevel->getName())));
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, $rows[strtolower($orgunitLevelName)]);
}
// Make Groupset Column
foreach ($groupsets as $groupsetKey => $groupset) {
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, $rows[strtolower($groupset->getName())]);
}
// Calculated fields
$excelService->setActiveSheetIndex(0)->setCellValue($column++ . $row, $rows["form_name"])->setCellValue($column . $row, $rows["longname"]);
$currentProfessional = $rows['profession'];
}
$excelService->getActiveSheet()->setTitle('List of Records');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$excelService->setActiveSheetIndex(0);
//create the response
// create the writer
$writer = $this->get('phpexcel')->createWriter($excelService, 'Excel5');
// create the response
$response = $this->get('phpexcel')->createStreamedResponse($writer);
$title = str_replace(',', ' ', $title);
//$response = $excelService->getResponse();
//$response->headers->set('Content-Type', 'application/vnd.ms-excel; charset=utf-8');
//$response->headers->set('Content-Disposition', 'attachment; filename='.$title.'.xls');
// If you are using a https connection, you have to set those two headers and use sendHeaders() for compatibility with IE <9
//$response->headers->set('Pragma', 'public');
//$response->headers->set('Cache-Control', 'maxage=1');
$response->headers->set('Content-Type', 'text/vnd.ms-excel; charset=utf-8');
$response->headers->set('Content-Disposition', 'attachment;filename=' . str_replace(" ", "_", $title) . '.xls');
$response->headers->set('Pragma', 'public');
$response->headers->set('Cache-Control', 'maxage=1');
//$response->sendHeaders();
return $response;
}
public function load(ObjectManager $manager)
{
$logger = $this->container->get('logger');
$stopwatch = new Stopwatch();
$stopwatch->start('dummyResourceTableGeneration');
// Populate dummy forms
$this->addDummyResourceTables();
// Seek dummy fields
$loadFieldData = new LoadFieldData();
$loadFieldData->addDummyFields();
$dummyFields = $loadFieldData->getFields();
foreach ($this->resourceTables as $resourceTableKey => $humanResourceResourceTable) {
$resourceTable = new ResourceTable();
$resourceTable->setName($humanResourceResourceTable['name']);
$resourceTable->setDescription($humanResourceResourceTable['description']);
$resourceTableRefernce = strtolower(str_replace(' ', '', $humanResourceResourceTable['name'])) . '-resourcetable';
$this->addReference($resourceTableRefernce, $resourceTable);
$manager->persist($resourceTable);
// Add Field Members for the resource table created
$sort = 1;
foreach ($dummyFields as $key => $dummyField) {
//Filter addition of fields not compliant to filter
if ($humanResourceResourceTable['filter'] == false || $humanResourceResourceTable['inputType'] == $dummyField['inputType'] || $humanResourceResourceTable['compulsory'] == $dummyField['compulsory']) {
$resourceTableMember = new ResourceTableFieldMember();
$resourceTableMember->setField($manager->merge($this->getReference(strtolower(str_replace(' ', '', $dummyField['name'])) . '-field')));
$resourceTableMember->setResourceTable($manager->merge($this->getReference($resourceTableRefernce)));
$resourceTableMember->setSort($sort++);
$referenceName = strtolower(str_replace(' ', '', $humanResourceResourceTable['name']) . str_replace(' ', '', $dummyField['name'])) . '-resourcetable-field-member';
$this->addReference($referenceName, $resourceTableMember);
$manager->persist($resourceTableMember);
$resourceTable->addResourceTableFieldMember($resourceTableMember);
unset($resourceTableMember);
}
}
$manager->persist($resourceTable);
unset($resourceTable);
}
$manager->flush();
$dummyResourceTableGenerationLap = $stopwatch->lap('dummyResourceTableGeneration');
$dummyResourceTableGenerationDuration = round($dummyResourceTableGenerationLap->getDuration() / 1000, 2);
if ($dummyResourceTableGenerationDuration < 60) {
$dummyResourceTableGenerationDurationMessage = round($dummyResourceTableGenerationDuration, 2) . ' sec.';
} elseif ($dummyResourceTableGenerationDuration >= 60 && $dummyResourceTableGenerationDuration < 3600) {
$dummyResourceTableGenerationDurationMessage = round($dummyResourceTableGenerationDuration / 60, 2) . ' min.';
} elseif ($dummyResourceTableGenerationDuration >= 3600 && $dummyResourceTableGenerationDuration < 216000) {
$dummyResourceTableGenerationDurationMessage = round($dummyResourceTableGenerationDuration / 3600, 2) . ' hrs';
} else {
$dummyResourceTableGenerationDurationMessage = round($dummyResourceTableGenerationDuration / 86400, 2) . ' days';
}
echo "\tDummy data schema generation complete in " . $dummyResourceTableGenerationDurationMessage . "\n";
// Generate resource tables
$resourceTables = $manager->getRepository('HrisFormBundle:ResourceTable')->findAll();
foreach ($resourceTables as $resourceTableKey => $resourceTable) {
// Ugly hack to generate resource table for "All Fields" only
if ($resourceTable->getName() == "All Fields") {
$success = $resourceTable->generateResourceTable($manager, $logger);
$messageLog = $resourceTable->getMessageLog();
if ($success) {
echo $messageLog;
} else {
echo "Failed with:" . $messageLog;
}
}
}
}
/**
* Generate friendly reports
*
* @Secure(roles="ROLE_SUPER_USER,ROLE_REPORTFRIENDLYREPORT_GENERATE")
* @Route("/", name="report_friendlyreport_generate")
* @Method("PUT")
* @Template()
*/
public function generateAction(Request $request)
{
$entityManager = $this->getDoctrine()->getManager();
$friendlyReportForm = $this->createForm(new ReportFriendlyReportType($this->getUser()), null, array('em' => $this->getDoctrine()->getManager()));
$friendlyReportForm->bind($request);
if ($friendlyReportForm->isValid()) {
$friendlyReportFormData = $friendlyReportForm->getData();
$friendlyReport = $friendlyReportFormData['genericReport'];
$organisationunit = $friendlyReportFormData['organisationunit'];
$targets = $friendlyReportFormData['targets'];
$targetValues = $targets->getValues();
$forms = $friendlyReportFormData['forms'];
$organisationunitGroupset = isset($friendlyReportFormData['organisationunitGroupset']) ? $friendlyReportFormData['organisationunitGroupset'] : null;
$organisationunitGroup = isset($friendlyReportFormData['organisationunitGroup']) ? $friendlyReportFormData['organisationunitGroup'] : null;
// Create FormIds
$formIds = NULL;
foreach ($forms as $formKey => $formObject) {
if (empty($formIds)) {
$formIds = $formObject->getId();
} else {
$formIds .= ',' . $formObject->getId();
}
}
// Create target columns if targets have been passed
$targetColumns = NULL;
if (isset($targets) && !empty($targets)) {
foreach ($targets as $targetKey => $target) {
$targetColumns = empty($targetColumns) ? str_replace(' ', '', $target->getName()) : $targetColumns . "," . str_replace(' ', '', $target->getName());
}
}
// Create OrganisationunitGroupIds
$organisationunitGroupIds = NULL;
if (isset($organisationunitGroup) && !empty($organisationunitGroup)) {
foreach ($organisationunitGroup as $organisationunitGroupKey => $organisationunitGroupObject) {
if (empty($organisationunitGroupIds)) {
$organisationunitGroupIds = $organisationunitGroupObject->getId();
} else {
$organisationunitGroupIds .= ',' . $organisationunitGroupObject->getId();
}
}
}
}
$title = $friendlyReport->getName() . ' for ' . $organisationunit->getLongname() . ' and lower levels';
if ($friendlyReport->getType() == "sql") {
$selectQuery = $friendlyReport->getSQL();
// Get Standard Resource table name
$resourceTableName = str_replace(' ', '_', trim(strtolower(ResourceTable::getStandardResourceTableName())));
$resourceTableAlias = "ResourceTable";
$organisationUnitJoinClause = " INNER JOIN hris_organisationunit as Organisationunit ON Organisationunit.id = {$resourceTableAlias}.organisationunit_id\n INNER JOIN hris_organisationunitstructure AS Structure ON Structure.organisationunit_id = {$resourceTableAlias}.organisationunit_id ";
// Clause for filtering target organisationunits
$organisationunitId = $organisationunit->getId();
// With Lower Levels
$organisationunit = $this->getDoctrine()->getManager()->getRepository('HrisOrganisationunitBundle:Organisationunit')->find($organisationunitId);
$organisationunitLevelsWhereClause = " Structure.level" . $organisationunit->getOrganisationunitStructure()->getLevel()->getLevel() . "_id={$organisationunitId} AND Structure.level_id >= ( SELECT hris_organisationunitlevel.level FROM hris_organisationunitstructure INNER JOIN hris_organisationunitlevel ON hris_organisationunitstructure.level_id=hris_organisationunitlevel.id WHERE hris_organisationunitstructure.organisationunit_id={$organisationunitId} ) ";
// Clause for filtering target forms
$formsWhereClause = " {$resourceTableAlias}.form_id IN ({$formIds}) ";
$selectQuery = str_replace('#{organisationunitJoinClause}', $organisationUnitJoinClause, $selectQuery);
$selectQuery = str_replace('#{organisationunitWhereClause}', $organisationunitLevelsWhereClause, $selectQuery);
$selectQuery = str_replace('#{formWhereClause}', $formsWhereClause, $selectQuery);
$sqlQueries = explode(';', $selectQuery);
foreach ($sqlQueries as $sqlKey => $sqlQuery) {
$friendlyReportResults = $this->getDoctrine()->getManager()->getConnection()->fetchAll($sqlQuery);
}
$adjustedColspan = NULL;
$reversedRepetition = NULL;
$target = NULL;
$colspanCounter = NULL;
$groupPositionCounter = NULL;
} else {
/*
* Initializing query for friendly report calculation
*/
// Get Standard Resource table name
$resourceTableName = str_replace(' ', '_', trim(strtolower(ResourceTable::getStandardResourceTableName())));
$resourceTableAlias = "ResourceTable";
$organisationUnitJoinClause = " INNER JOIN hris_organisationunit as Organisationunit ON Organisationunit.id = {$resourceTableAlias}.organisationunit_id\n INNER JOIN hris_organisationunitstructure AS Structure ON Structure.organisationunit_id = {$resourceTableAlias}.organisationunit_id ";
if (!empty($targetValues)) {
$indicatorTargetJoinClause = " INNER JOIN hris_organisationunitgroup_members as OrganisationunitGroupMembers ON OrganisationunitGroupMembers.organisationunit_id={$resourceTableAlias}.organisationunit_id\n INNER JOIN hris_organisationunitgroup as OrganisationunitGroup ON OrganisationunitGroup.id=OrganisationunitGroupMembers.organisationunitgroup_id\n\t INNER JOIN hris_indicator_target as IndicatorTarget ON IndicatorTarget.organisationunitgroup_id=OrganisationunitGroup.id";
$organisationUnitJoinClause .= $indicatorTargetJoinClause;
} else {
$indicatorTargetJoinClause = NULL;
}
$joinClause = $organisationUnitJoinClause;
$fromClause = " FROM {$resourceTableName} {$resourceTableAlias} ";
// Clause for filtering target organisationunits
$organisationunitId = $organisationunit->getId();
// With Lower Levels
$organisationunit = $this->getDoctrine()->getManager()->getRepository('HrisOrganisationunitBundle:Organisationunit')->find($organisationunitId);
$organisationunitLevelsWhereClause = " Structure.level" . $organisationunit->getOrganisationunitStructure()->getLevel()->getLevel() . "_id={$organisationunitId} AND Structure.level_id >= ( SELECT hris_organisationunitlevel.level FROM hris_organisationunitstructure INNER JOIN hris_organisationunitlevel ON hris_organisationunitstructure.level_id=hris_organisationunitlevel.id WHERE hris_organisationunitstructure.organisationunit_id={$organisationunitId} ) ";
// Clause for filtering target forms
$formsWhereClause = " {$resourceTableAlias}.form_id IN ({$formIds}) ";
// Query for Options to exclude from reports
$fieldOptionsToSkip = $this->getDoctrine()->getManager()->getRepository('HrisFormBundle:FieldOption')->findBy(array('skipInReport' => True));
//filter the records with exclude report tag
if ($friendlyReport->getIgnoreSkipInReport() == False) {
foreach ($fieldOptionsToSkip as $key => $fieldOptionToSkip) {
if (empty($fieldOptionsToSkipQuery)) {
$fieldOptionsToSkipQuery = "{$resourceTableAlias}." . $fieldOptionToSkip->getField()->getName() . " !='" . $fieldOptionToSkip->getValue() . "'";
} else {
$fieldOptionsToSkipQuery .= " AND {$resourceTableAlias}." . $fieldOptionToSkip->getField()->getName() . " !='" . $fieldOptionToSkip->getValue() . "'";
}
}
}
// Deducing colspan for cells in the header
$groupPositionCounter = 0;
$previousColspan = 1;
$colspan = NULL;
$repetition = NULL;
// Row count for the entire database(rows accessible by user)
// Combination of serie and category columns
$seriesFieldName = $friendlyReport->getSerie()->getField()->getName();
/*
* Go through categories and construct viable columns for SQL
*/
foreach ($friendlyReport->getFriendlyReportCategory() as $friendlyReportCategoryKey => $friendlyReportCategory) {
if (!isset($pastFirstCategory)) {
$pastFirstCategory = True;
}
// Initiate first category
foreach ($friendlyReportCategory->getFieldOptionGroup()->getFieldOption() as $fieldOptionKey => $fieldOption) {
$queryColumnNames[] = str_replace('-', '_', str_replace(' ', '', $fieldOption->getValue()));
$categoryFieldNames[] = $fieldOption->getField()->getName();
$categoryFieldName = $fieldOption->getField()->getName();
$categoryFieldOptionValue = str_replace('-', '_', $fieldOption->getValue());
$categoryFieldOptionValues[] = str_replace('-', '_', $fieldOption->getValue());
$categoryResourceTableName = $resourceTableAlias . str_replace(' ', '', $categoryFieldOptionValue);
$queryColumnWhereClause[str_replace('-', '_', $fieldOption->getValue())] = "{$categoryResourceTableName}.{$categoryFieldName}='" . str_replace(' ', '', $categoryFieldOptionValue) . "'";
}
}
foreach ($queryColumnNames as $queryColumnNameKey => $queryColumnName) {
// construction of category column query in relation to serie
// randomize resourcetable alias
$categoryFieldName = $categoryFieldNames[$queryColumnNameKey];
$categoryFieldOptionValue = $categoryFieldOptionValues[$queryColumnNameKey];
$categoryResourceTableName = $resourceTableAlias . str_replace(' ', '', $categoryFieldOptionValue);
$joinClause .= " LEFT JOIN\n (\n SELECT COUNT({$categoryResourceTableName}." . str_replace(' ', '', $categoryFieldName) . ") AS " . str_replace(' ', '', $categoryFieldOptionValue) . ", {$categoryResourceTableName}.{$seriesFieldName}\n FROM {$resourceTableName} {$categoryResourceTableName}\n " . str_replace($resourceTableAlias, $categoryResourceTableName, $organisationUnitJoinClause) . "\n WHERE {$categoryResourceTableName}.{$categoryFieldName}='{$categoryFieldOptionValue}'\n " . (!empty($fieldOptionsToSkipQuery) ? str_replace($resourceTableAlias, $categoryResourceTableName, " AND ( {$fieldOptionsToSkipQuery} )") : "") . "\n " . (!empty($organisationunitLevelsWhereClause) ? str_replace($resourceTableAlias, $categoryResourceTableName, " AND ( {$organisationunitLevelsWhereClause} )") : "") . "\n " . (!empty($formsWhereClause) ? str_replace($resourceTableAlias, $categoryResourceTableName, " AND ( {$formsWhereClause} )") : "") . "\n GROUP BY {$categoryResourceTableName}.{$seriesFieldName}\n ) {$categoryResourceTableName} ON {$categoryResourceTableName}.{$seriesFieldName}= {$resourceTableAlias}.{$seriesFieldName}";
}
//Target join clause
$targetJoinClause = NULL;
if (isset($targets) && !empty($targets)) {
foreach ($targets as $targetKey => $target) {
$targetColumn = str_replace(' ', '', $target->getName());
$targetJoinClause .= " LEFT JOIN (\n SELECT hris_fieldoption.value as " . $targetColumn . "fieldOption,(\n hris_indicator_targetfieldoption.value *\n (\n SELECT count(Organisationunit.id) FROM hris_organisationunit AS Organisationunit\n INNER JOIN hris_organisationunitstructure AS Structure2 ON Structure2.organisationunit_id = Organisationunit.id\n INNER JOIN hris_organisationunitgroup_members as OrganisationunitGroupMembers ON OrganisationunitGroupMembers.organisationunit_id=Organisationunit.id\n INNER JOIN hris_organisationunitgroup as OrganisationunitGroup ON OrganisationunitGroup.id=OrganisationunitGroupMembers.organisationunitgroup_id\n INNER JOIN hris_indicator_target as IndicatorTarget ON IndicatorTarget.organisationunitgroup_id=OrganisationunitGroup.id\n WHERE " . str_replace('Structure', 'Structure2', $organisationunitLevelsWhereClause) . "\n AND IndicatorTarget.id=" . $target->getId() . "\n )\n\n ) as {$targetColumn} FROM hris_fieldoption\n INNER JOIN hris_indicator_targetfieldoption ON hris_indicator_targetfieldoption.fieldoption_id=hris_fieldoption.id\n INNER JOIN hris_indicator_target ON hris_indicator_target.id=hris_indicator_targetfieldoption.target_id\n WHERE hris_indicator_target.id=" . $target->getId() . "\n ORDER BY " . $targetColumn . "fieldOption\n ) HrisTargetValues" . $targetColumn . " ON HrisTargetValues" . $targetColumn . "." . $targetColumn . "fieldOption = {$resourceTableAlias}.{$seriesFieldName}";
}
}
$columns = " DISTINCT {$resourceTableAlias}.{$seriesFieldName} as {$seriesFieldName}," . implode(',', $queryColumnNames) . (!empty($targetColumns) ? ',' . $targetColumns : '');
if (!empty($targetJoinClause)) {
$joinClause .= $targetJoinClause;
}
$selectQuery = "SELECT {$columns} {$fromClause} {$joinClause} WHERE {$organisationunitLevelsWhereClause}" . (!empty($fieldOptionsToSkipQuery) ? " AND ( {$fieldOptionsToSkipQuery} )" : "");
$friendlyReportResults = $this->getDoctrine()->getManager()->getConnection()->fetchAll($selectQuery);
}
foreach ($friendlyReport->getFriendlyReportCategory() as $friendlyReportCategoryKey => $friendlyReportCategory) {
/**
* Deduce measurements of table display on view page
* i.e. column span, rowspan, and cell repetitions per column spans
*/
$colspanCounter[$groupPositionCounter] = $friendlyReportCategory->getFieldOptionGroup()->getName();
// Increment colspan
if ($groupPositionCounter == 0) {
$colspan[$friendlyReportCategory->getFieldOptionGroup()->getName()] = $previousColspan;
} else {
$colspan[$friendlyReportCategory->getFieldOptionGroup()->getName()] = $previousColspan * $friendlyReportCategory->getFieldOptionGroup()->getFieldOption()->count();
$previousColspan = $colspan[$friendlyReportCategory->getFieldOptionGroup()->getName()];
}
$repetition[$friendlyReportCategory->getFieldOptionGroup()->getName()] = $friendlyReportCategory->getFieldOptionGroup()->getFieldOption()->count();
$groupPositionCounter++;
}
$reverseCounter = 0;
for ($colspanIncr = $groupPositionCounter - 1; $colspanIncr >= 0; $colspanIncr--) {
/**
* Calibrate measurements for columnspan and rowspan
*/
$adjustedColspan[$colspanCounter[$reverseCounter]] = $colspan[$colspanCounter[$colspanIncr]];
$reversedRepetition[$colspanCounter[$reverseCounter]] = $repetition[$colspanCounter[$colspanIncr]];
$reverseCounter++;
}
$reversedRepetition[$colspanCounter[0]] = 0;
// no repetition for first row.
return array('friendlyReport' => $friendlyReport, 'title' => $title, 'colspan' => $adjustedColspan, 'repetition' => $reversedRepetition, 'friendlyReportResults' => $friendlyReportResults, 'targets' => $targets);
}
/**
* Generates export file with DHIS2 Dataset aggregated values.
*
* @Secure(roles="ROLE_SUPER_USER,ROLE_DHISDATACONNECTION_SYNCDATA")
* @Route("/syncdata/{id}/aggregation.{_format}", requirements={"_format"="yml|xml|json"}, defaults={"_format"="xml"}, name="dhisdataconnection_aggregation")
* @Method("POST")
* @Template()
*/
public function aggregationAction($id)
{
$em = $this->getDoctrine()->getManager();
$this->dhisConnectionId = $id;
$this->xmlContents = "<?xml version='1.0' encoding='UTF-8'?>\n<dataValueSet xmlns=\"http://dhis2.org/schema/dxf/2.0\">";
$entity = $em->getRepository('HrisIntergrationBundle:DHISDataConnection')->find($id);
$xmlFile = "/tmp/hrhis_data_" . str_replace(' ', '_', $entity->getParentOrganisationunit()) . ".xml";
file_put_contents($xmlFile, $this->xmlContents);
/*
* Aggregate data for the parent organisationunit
*/
// Aggregate data for each orgunit in the current level.
$em = $this->getDoctrine()->getManager();
$entity = $em->getRepository('HrisIntergrationBundle:DHISDataConnection')->find($this->dhisConnectionId);
/*
* Initializing query for dhis dataset calculation
*/
// Get Standard Resource table name
$resourceTableName = str_replace(' ', '_', trim(strtolower(ResourceTable::getStandardResourceTableName())));
$resourceTableAlias = "ResourceTable";
//@todo create join clause to only go through orgunit with dhisUid
$fromClause = " FROM {$resourceTableName} {$resourceTableAlias} ";
$organisationunitLevelsWhereClause = " {$resourceTableAlias}.organisationunit_id=" . $entity->getParentOrganisationunit()->getId() . " ";
// Query for Options to exclude from reports
$fieldOptionsToSkip = $this->getDoctrine()->getManager()->getRepository('HrisFormBundle:FieldOption')->findBy(array('skipInReport' => True));
//filter the records with exclude report tag
foreach ($fieldOptionsToSkip as $key => $fieldOptionToSkip) {
if (empty($fieldOptionsToSkipQuery)) {
$fieldOptionsToSkipQuery = "{$resourceTableAlias}." . $fieldOptionToSkip->getField()->getName() . " !='" . $fieldOptionToSkip->getValue() . "'";
} else {
$fieldOptionsToSkipQuery .= " AND {$resourceTableAlias}." . $fieldOptionToSkip->getField()->getName() . " !='" . $fieldOptionToSkip->getValue() . "'";
}
}
// Dataelement field option relation
$dataelementFieldOptionRelation = $entity->getDataelementFieldOptionRelation();
foreach ($dataelementFieldOptionRelation as $dataelementFieldOptionKey => $dataelementFieldOptionValue) {
// Formulate Query for calculating field option
$columnFieldOptionGroup = $dataelementFieldOptionValue->getColumnFieldOptionGroup();
$rowFieldOptionGroup = $dataelementFieldOptionValue->getRowFieldOptionGroup();
$seriesFieldName = $rowFieldOptionGroup->getName();
//Column Query construction
$queryColumnNames[] = str_replace('-', '_', str_replace(' ', '', $columnFieldOptionGroup->getName()));
$categoryColumnFieldNames[] = $columnFieldOptionGroup->getField()->getName();
$categoryRowFieldName = $columnFieldOptionGroup->getField()->getName();
$columnWhereClause = NULL;
foreach ($columnFieldOptionGroup->getFieldOption() as $columnFieldOptionKey => $columnFieldOption) {
$operator = $columnFieldOptionGroup->getOperator();
if (empty($operator)) {
$operator = "OR";
}
$categoryColumnFieldOptionValue = str_replace('-', '_', $columnFieldOption->getValue());
$categoryColumnFieldName = $columnFieldOption->getField()->getName();
$categoryColumnResourceTableName = $resourceTableAlias;
if (!empty($columnWhereClause)) {
$columnWhereClause = $columnWhereClause . " " . strtoupper($operator) . " {$categoryColumnResourceTableName}.{$categoryColumnFieldName}='" . $categoryColumnFieldOptionValue . "'";
} else {
$columnWhereClause = "{$categoryColumnResourceTableName}.{$categoryColumnFieldName}='" . $categoryColumnFieldOptionValue . "'";
}
}
$rowWhereClause = NULL;
foreach ($rowFieldOptionGroup->getFieldOption() as $rowFieldOptionKey => $rowFieldOption) {
$operator = $rowFieldOptionGroup->getOperator();
if (empty($operator)) {
$operator = "OR";
}
$categoryRowFieldOptionValue = str_replace('-', '_', $rowFieldOption->getValue());
$categoryRowFieldName = $rowFieldOption->getField()->getName();
$categoryRowResourceTableName = $resourceTableAlias;
if (!empty($rowWhereClause)) {
$rowWhereClause = $rowWhereClause . " " . strtoupper($operator) . " {$categoryRowResourceTableName}.{$categoryRowFieldName}='" . $categoryRowFieldOptionValue . "'";
} else {
$rowWhereClause = "{$categoryRowResourceTableName}.{$categoryRowFieldName}='" . $categoryRowFieldOptionValue . "'";
}
}
$selectQuery = "SELECT COUNT(DISTINCT(instance)) {$fromClause} WHERE ({$rowWhereClause}) AND ({$columnWhereClause}) AND {$organisationunitLevelsWhereClause}" . (!empty($fieldOptionsToSkipQuery) ? " AND ( {$fieldOptionsToSkipQuery} )" : "");
$instanceCount = $this->array_value_recursive('count', $this->getDoctrine()->getManager()->getConnection()->fetchAll($selectQuery));
$dhisUid = $entity->getParentOrganisationunit()->getDhisUid();
if (!empty($dhisUid) && $instanceCount > 0) {
//$this->xmlContents = $this->xmlContents.'<dataValue dataElement="'.$dataelementFieldOptionValue->getDataelementUid().'" period="'.date("Y").'" orgUnit="'.$entity->getParentOrganisationunit()->getDhisUid().'" categoryOptionCombo="'.$dataelementFieldOptionValue->getCategoryComboUid().'" value="'.$instanceCount.'" storedBy="hrhis" lastUpdated="'.date("c").'" followUp="false" />';
file_put_contents($xmlFile, '<dataValue dataElement="' . $dataelementFieldOptionValue->getDataelementUid() . '" period="' . date("Y") . '" orgUnit="' . $entity->getParentOrganisationunit()->getDhisUid() . '" categoryOptionCombo="' . $dataelementFieldOptionValue->getCategoryComboUid() . '" value="' . $instanceCount . '" storedBy="hrhis" lastUpdated="' . date("c") . '" followUp="false" />', FILE_APPEND);
}
unset($dhisUid);
}
/*
* Aggregate organisationunit for all the children
*/
$queryBuilder = $em->createQueryBuilder();
$allOrganisationunitsChildren = $queryBuilder->select('organisationunit')->from('HrisOrganisationunitBundle:Organisationunit', 'organisationunit')->join('organisationunit.organisationunitStructure', 'organisationunitStructure')->join('organisationunitStructure.level', 'level')->andWhere('
(
level.level >= :organisationunitLevel
AND organisationunitStructure.level' . $entity->getParentOrganisationunit()->getOrganisationunitStructure()->getLevel()->getLevel() . 'Organisationunit=:levelOrganisationunit
) AND organisationunit.dhisUid is not null')->setParameters(array('levelOrganisationunit' => $entity->getParentOrganisationunit(), 'organisationunitLevel' => $entity->getParentOrganisationunit()->getOrganisationunitStructure()->getLevel()->getLevel()))->getQuery()->getResult();
foreach ($allOrganisationunitsChildren as $organisationunitKey => $organisationunit) {
// Aggregate data for each orgunit in the current level.
$em = $this->getDoctrine()->getManager();
$entity = $em->getRepository('HrisIntergrationBundle:DHISDataConnection')->find($this->dhisConnectionId);
/*
* Initializing query for dhis dataset calculation
*/
// Get Standard Resource table name
$resourceTableName = str_replace(' ', '_', trim(strtolower(ResourceTable::getStandardResourceTableName())));
$resourceTableAlias = "ResourceTable";
//@todo create join clause to only go through orgunit with dhisUid
$fromClause = " FROM {$resourceTableName} {$resourceTableAlias} ";
$organisationunitLevelsWhereClause = " {$resourceTableAlias}.organisationunit_id=" . $organisationunit->getId() . " ";
// Query for Options to exclude from reports
$fieldOptionsToSkip = $this->getDoctrine()->getManager()->getRepository('HrisFormBundle:FieldOption')->findBy(array('skipInReport' => True));
//filter the records with exclude report tag
foreach ($fieldOptionsToSkip as $key => $fieldOptionToSkip) {
if (empty($fieldOptionsToSkipQuery)) {
$fieldOptionsToSkipQuery = "{$resourceTableAlias}." . $fieldOptionToSkip->getField()->getName() . " !='" . $fieldOptionToSkip->getValue() . "'";
} else {
$fieldOptionsToSkipQuery .= " AND {$resourceTableAlias}." . $fieldOptionToSkip->getField()->getName() . " !='" . $fieldOptionToSkip->getValue() . "'";
}
}
// Dataelement field option relation
$dataelementFieldOptionRelation = $entity->getDataelementFieldOptionRelation();
foreach ($dataelementFieldOptionRelation as $dataelementFieldOptionKey => $dataelementFieldOptionValue) {
// Formulate Query for calculating field option
$columnFieldOptionGroup = $dataelementFieldOptionValue->getColumnFieldOptionGroup();
$rowFieldOptionGroup = $dataelementFieldOptionValue->getRowFieldOptionGroup();
$seriesFieldName = $rowFieldOptionGroup->getName();
//Column Query construction
$queryColumnNames[] = str_replace('-', '_', str_replace(' ', '', $columnFieldOptionGroup->getName()));
$categoryColumnFieldNames[] = $columnFieldOptionGroup->getField()->getName();
$categoryRowFieldName = $columnFieldOptionGroup->getField()->getName();
$columnWhereClause = NULL;
foreach ($columnFieldOptionGroup->getFieldOption() as $columnFieldOptionKey => $columnFieldOption) {
$operator = $columnFieldOptionGroup->getOperator();
if (empty($operator)) {
$operator = "OR";
}
$categoryColumnFieldOptionValue = str_replace('-', '_', $columnFieldOption->getValue());
$categoryColumnFieldName = $columnFieldOption->getField()->getName();
$categoryColumnResourceTableName = $resourceTableAlias;
if (!empty($columnWhereClause)) {
$columnWhereClause = $columnWhereClause . " " . strtoupper($operator) . " {$categoryColumnResourceTableName}.{$categoryColumnFieldName}='" . $categoryColumnFieldOptionValue . "'";
} else {
$columnWhereClause = "{$categoryColumnResourceTableName}.{$categoryColumnFieldName}='" . $categoryColumnFieldOptionValue . "'";
}
}
$rowWhereClause = NULL;
foreach ($rowFieldOptionGroup->getFieldOption() as $rowFieldOptionKey => $rowFieldOption) {
$operator = $rowFieldOptionGroup->getOperator();
if (empty($operator)) {
$operator = "OR";
}
$categoryRowFieldOptionValue = str_replace('-', '_', $rowFieldOption->getValue());
$categoryRowFieldName = $rowFieldOption->getField()->getName();
$categoryRowResourceTableName = $resourceTableAlias;
if (!empty($rowWhereClause)) {
$rowWhereClause = $rowWhereClause . " " . strtoupper($operator) . " {$categoryRowResourceTableName}.{$categoryRowFieldName}='" . $categoryRowFieldOptionValue . "'";
} else {
$rowWhereClause = "{$categoryRowResourceTableName}.{$categoryRowFieldName}='" . $categoryRowFieldOptionValue . "'";
}
}
$selectQuery = "SELECT COUNT(DISTINCT(instance)) {$fromClause} WHERE ({$rowWhereClause}) AND ({$columnWhereClause}) AND {$organisationunitLevelsWhereClause}" . (!empty($fieldOptionsToSkipQuery) ? " AND ( {$fieldOptionsToSkipQuery} )" : "");
$instanceCount = $this->array_value_recursive('count', $this->getDoctrine()->getManager()->getConnection()->fetchAll($selectQuery));
//Only send non-zero data
if ($instanceCount > 0) {
//$this->xmlContents = $this->xmlContents.'<dataValue dataElement="'.$dataelementFieldOptionValue->getDataelementUid().'" period="'.date("Y").'" orgUnit="'.$organisationunit->getDhisUid().'" categoryOptionCombo="'.$dataelementFieldOptionValue->getCategoryComboUid().'" value="'.$instanceCount.'" storedBy="hrhis" lastUpdated="'.date("c").'" followUp="false" />';
file_put_contents($xmlFile, '<dataValue dataElement="' . $dataelementFieldOptionValue->getDataelementUid() . '" period="' . date("Y") . '" orgUnit="' . $organisationunit->getDhisUid() . '" categoryOptionCombo="' . $dataelementFieldOptionValue->getCategoryComboUid() . '" value="' . $instanceCount . '" storedBy="hrhis" lastUpdated="' . date("c") . '" followUp="false" />', FILE_APPEND);
}
unset($dhisUid);
}
}
$this->xmlContents = $this->xmlContents . '</dataValueSet>';
file_put_contents($xmlFile, '</dataValueSet>', FILE_APPEND);
// Initializing export file
$outputFile = $fileSystem = new Filesystem();
$exportFileName = "Export_" . date("Y_m_d_His") . ".zip";
$exportArchive = new ZipArchive();
$exportArchive->open("/tmp/" . $exportFileName, ZipArchive::CREATE);
$exportArchive->addFromString("Export_" . date("Y_m_d_His") . 'xml', $this->xmlContents);
$exportArchive->close();
$fileSystem->chmod("/tmp/" . $exportFileName, 0666);
$response = new Response(file_get_contents("/tmp/" . $exportFileName));
$d = $response->headers->makeDisposition(ResponseHeaderBag::DISPOSITION_ATTACHMENT, $exportFileName);
$response->headers->set('Content-Disposition', $d);
unlink("/tmp/" . $exportFileName);
$result = $this->xmlContents;
// /*
// * Send data through web api
// */
// $additionalHeaders = '';
// $process = curl_init($entity-> getHostUrl().'/api/dataValueSets');
// //curl_setopt($process, CURLOPT_POST, true);
// curl_setopt($process, CURLOPT_RETURNTRANSFER, 1);
// curl_setopt($process, CURLOPT_HTTPHEADER, array('Content-Type: application/xml', $additionalHeaders));
// curl_setopt($process, CURLOPT_USERPWD, $entity -> getUsername() . ":" . $entity -> getPassword());
// curl_setopt($process, CURLOPT_POST, 1);
// curl_setopt($process, CURLOPT_POSTFIELDS, $exportFileName);
// curl_setopt($process, CURLOPT_TIMEOUT, 30);
//
// $return = curl_exec($process);
// curl_close($process);
return array('result' => $result);
}
