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