/**
* Converts into twig template tag {{ record | calculatedFieldExpression(fieldExpression) }}
*
* @param $record
* @param $fieldExpression
* @return mixed
*/
public function calculateFieldExpressionFilter($record, $fieldExpression, $fields)
{
$match = NULL;
$valueKey = NULL;
$recordFieldKey = ucfirst(Record::getFieldKey());
if (preg_match_all('/\\#{([^\\}]+)\\}/', $fieldExpression, $match)) {
foreach ($fields as $fieldKey => $field) {
if ($field->getName() == $match[1][0]) {
// Translates to $field->getUid()
// or $field->getUid() depending on value of $recordKeyName
$valueKey = call_user_func_array(array($field, "get{$recordFieldKey}"), array());
}
}
}
$displayValue = $record->getValue($valueKey, 'Y-m-d');
$datavalue = @@str_replace($match[0][0], $displayValue, $fieldExpression);
$calculatedExpression = eval("return {$datavalue};");
return $calculatedExpression;
}
/**
* Lists all Records by forms.
*
* @Secure(roles="ROLE_SUPER_USER,ROLE_RECORD_LIST")
* @Route("/viewrecords/", name="record_viewparticipants")
* @Method("GET")
* @Template()
*/
public function participantsForviewOnlyAction(Request $request)
{
/// querying instance record table to see existing record instance pairs//
$instance_id = $request->query->get('instance_id');
// query instance id from route url
$id = $request->query->get('id');
// query instance id from route url
$em = $this->getDoctrine()->getManager();
$queryBuilder = $this->getDoctrine()->getManager()->createQueryBuilder();
$userManager = $this->get('fos_user.user_manager');
$user = $userManager->findUserByUsername($this->getUser());
$organisationunit = $user->getOrganisationunit();
// if($formid == 0) {
$formIds = $this->getDoctrine()->getManager()->createQueryBuilder()->select('form.id')->from('HrisFormBundle:Form', 'form')->getQuery()->getArrayResult();
// $formIds = $this->array_value_recursive('id',$formIds);
$forms = $em->getRepository('HrisFormBundle:Form')->findAll();
// }else {
// $forms = $em->getRepository('HrisFormBundle:Form')->findby(array('id'=>$formid));
// $formIds[]=$formid;
// }
//Prepare field Option map, converting from stored FieldOption key in record value array to actual text value
$fieldOptions = $this->getDoctrine()->getManager()->getRepository('HrisFormBundle:FieldOption')->findAll();
foreach ($fieldOptions as $fieldOptionKey => $fieldOption) {
$recordFieldOptionKey = ucfirst(Record::getFieldOptionKey());
$fieldOptionMap[call_user_func_array(array($fieldOption, "get{$recordFieldOptionKey}"), array())] = $fieldOption->getValue();
}
//If user's organisationunit is data entry level pull only records of his organisationunit
//else pull lower children too.
$records = $queryBuilder->select('record')->from('HrisRecordsBundle:Record', 'record')->join('record.organisationunit', 'organisationunit')->join('record.form', 'form');
if ($organisationunit->getOrganisationunitStructure()->getLevel()->getDataentrylevel()) {
$records = $records->join('organisationunit.organisationunitStructure', 'organisationunitStructure')->join('organisationunitStructure.level', 'organisationunitLevel')->andWhere('organisationunitLevel.level >= (
SELECT selectedOrganisationunitLevel.level
FROM HrisOrganisationunitBundle:OrganisationunitStructure selectedOrganisationunitStructure
INNER JOIN selectedOrganisationunitStructure.level selectedOrganisationunitLevel
WHERE selectedOrganisationunitStructure.organisationunit=:selectedOrganisationunit )')->andWhere('organisationunitStructure.level' . $organisationunit->getOrganisationunitStructure()->getLevel()->getLevel() . 'Organisationunit=:levelId');
$parameters = array('levelId' => $organisationunit->getId(), 'selectedOrganisationunit' => $organisationunit->getId(), 'formIds' => $formIds);
} else {
$records = $records->andWhere('organisationunit.id=:selectedOrganisationunit');
$parameters = array('selectedOrganisationunit' => $organisationunit->getId(), 'formIds' => $formIds);
}
$records = $records->andWhere($queryBuilder->expr()->in('form.id', ':formIds'))->setParameters($parameters)->getQuery()->getResult();
$formNames = NULL;
$visibleFields = array();
$formFields = array();
$incr = 0;
$formIds = array();
foreach ($forms as $formKey => $form) {
$incr++;
$formIds[] = $form->getId();
// Concatenate form Names
if (empty($formNames)) {
$formNames = $form->getTitle();
} else {
if (count($formNames) == $incr) {
$formNames .= ',' . $form->getTitle();
}
}
// Accrue visible fields
foreach ($form->getFormVisibleFields() as $visibleFieldKey => $visibleField) {
if (!in_array($visibleField->getField(), $visibleFields)) {
$visibleFields[] = $visibleField->getField();
}
}
// Accrue form fields
foreach ($form->getFormFieldMember() as $formFieldKey => $formField) {
if (!in_array($formField->getField(), $formFields)) {
$formFields[] = $formField->getField();
}
}
}
$title_query = "select * from hris_trainings T,hris_traininginstance I where T.id = I.training_id and I.id=" . $instance_id;
$trainings = $em->getConnection()->executeQuery($title_query)->fetchAll();
$training = array(0 => '');
foreach ($trainings as $trains) {
$training['coursename'] = $trains['coursename'];
$training['district'] = $trains['district'];
$training['startdate'] = $trains['startdate'];
$training['venue'] = $trains['venue'];
}
$startdate = strtotime($training['startdate']);
$startdate = date('d-m-Y', $startdate);
$title = "Participants for " . $training['coursename'] . " of " . $training['district'] . " of date " . $startdate . " To Employee Records for " . $organisationunit->getLongname();
$title .= " for " . $formNames;
if (empty($visibleFields)) {
$visibleFields = $formFields;
}
//getting all User Forms for User Migration
$user = $this->container->get('security.context')->getToken()->getUser();
$userForms = $user->getForm();
$instance = new instanceRecord();
$instanceRecordForm = $this->createForm(new instanceRecordType($this->getUser()), $instance, array('method' => 'POST'));
$instanceRecordForm = $instanceRecordForm->createView();
$query = "SELECT record_id FROM hris_instance_records WHERE instance_id =" . $instance_id;
$record_ids = $em->getConnection()->executeQuery($query)->fetchAll();
$id_record = array(0 => -1);
foreach ($record_ids as $records_id) {
$id_record[] = $records_id['record_id'];
}
return $this->render('HrisTrainingBundle:Participant:participantsForviewOnly.html.twig', array('title' => $title, 'visibleFields' => $visibleFields, 'formFields' => $formFields, 'records' => $records, 'optionMap' => $fieldOptionMap, 'userForms' => $userForms, 'formid' => "", 'record_ids' => $id_record, 'instanceRecordForm' => $instanceRecordForm));
}
protected function execute(InputInterface $input, OutputInterface $output)
{
$filePath = $input->getArgument('file');
$dataEntryFormName = $input->getArgument('form');
$organisationunitName = $input->getArgument('organisationunit');
$em = $this->getContainer()->get('doctrine');
$formEntity = $em->getRepository('HrisFormBundle:Form')->findOneBy(array('name' => $dataEntryFormName));
$fields = $formEntity->getSimpleField();
$phpExcelObject = $this->getContainer()->get('phpexcel')->createPHPExcelObject($filePath);
$objWorksheet = $phpExcelObject->getActiveSheet();
$rowCounter = 0;
$fieldUIDs = array();
$fieldObjects = array();
foreach ($objWorksheet->getRowIterator() as $row) {
//Parse the headings on first row for deducing equivalent fields in database
if ($rowCounter < 1) {
$cellIterator = $row->getCellIterator();
$cellnumber = 1;
foreach ($cellIterator as $cell) {
$field = $em->getRepository('HrisFormBundle:Field')->findOneBy(array('name' => $cell->getValue()));
if (!empty($field)) {
// Construct array of columns with matched Fields
$fieldUIDs[$cellnumber] = $field->getUid();
$fieldObjects[$cellnumber] = $field;
$cellnumber++;
}
}
} else {
break;
//@todo placed to avoid going through many rows
}
$rowCounter++;
}
// Construct data values array
$dataValueArray = array();
$j = 1;
foreach ($objWorksheet->getRowIterator() as $row) {
//if($j > 1 && $j < 7){
$cellIterator = $row->getCellIterator();
$cellnumber = 1;
$instancestring = "";
//$instance=md5($firstName.$middleName.$surname.$dateOfBirth->format('Y-m-d'));
foreach ($cellIterator as $cell) {
if ($cellnumber == 2 || $cellnumber == 3 || $cellnumber == 4) {
$instancestring .= $cell->getValue() . uniqid();
}
if ($cellnumber == 5) {
$instancestring .= $cell->getValue() . uniqid();
}
$cellIntVal = intval($cell->getValue());
if (isset($fieldObjects[$cellnumber]) && !empty($fieldObjects[$cellnumber])) {
if ($fieldObjects[$cellnumber]->getDataType()->getName() == "Date" && $cellIntVal != 0) {
$year = substr($cell->getValue(), 0, 4);
$month = substr($cell->getValue(), 4, 2);
$days = substr($cell->getValue(), 6, 2);
$formattedDate = $year . "-" . $month . "-" . $days;
$dataValueArray[$fieldUIDs[$cellnumber]] = new \DateTime($formattedDate);
} elseif ($fieldObjects[$cellnumber]->getInputType()->getName() == "Select") {
//special check for sex
if ($fieldObjects[$cellnumber]->getName() == "sex") {
foreach ($fieldObjects[$cellnumber]->getFieldOption() as $option) {
$val = $cell->getValue() == "M" ? "Male" : "Female";
if ($option->getValue() == $val) {
$dataValueArray[$fieldUIDs[$cellnumber]] = $option->getUid();
}
}
} elseif ($fieldObjects[$cellnumber]->getName() == "Religion") {
foreach ($fieldObjects[$cellnumber]->getFieldOption() as $option) {
if (strtolower($option->getValue()) == strtolower($cell->getValue())) {
$dataValueArray[$fieldUIDs[$cellnumber]] = $option->getUid();
}
}
} else {
foreach ($fieldObjects[$cellnumber]->getFieldOption() as $option) {
if ($option->getValue() == $cell->getValue()) {
$dataValueArray[$fieldUIDs[$cellnumber]] = $option->getUid();
}
}
}
} else {
$dataValueArray[$fieldUIDs[$cellnumber]] = $cell->getValue();
}
}
$cellnumber++;
}
$instance = md5($instancestring);
//for basic education level
$dataValueArray["5289e93496216"] = "5289e93871f64";
// for employment_status
$dataValueArray["5289e934a6b16"] = "5289e934f353d";
//for employer
$dataValueArray["5289e934a59a6"] = "528a0ae3249d2";
$orgunit = $em->getRepository('HrisOrganisationunitBundle:Organisationunit')->findOneBy(array('longname' => $organisationunitName));
$entity = new Record();
$entity->setValue($dataValueArray);
$entity->setForm($formEntity);
$entity->setInstance($instance);
$entity->setOrganisationunit($orgunit);
$entity->setUsername("admin");
$entity->setComplete(True);
$entity->setCorrect(True);
$entity->setHashistory(False);
$entity->setHastraining(False);
$em->getManager()->persist($entity);
$em->getManager()->flush();
}
//$j++;
//}
}
/**
* Edits an existing Record entity.
*
* @Secure(roles="ROLE_SUPER_USER,ROLE_RECORD_UPDATE")
* @Route("/update", name="record_update")
* @Method("POST")
* @Template("HrisRecordsBundle:Record:viewRecords.html.twig")
*/
public function updateAction(Request $request)
{
$em = $this->getDoctrine()->getManager();
$instance = $this->getRequest()->get('instance');
$entity = $em->getRepository('HrisRecordsBundle:Record')->findOneBy(array('instance' => $instance));
$formId = (int) $this->getRequest()->get('formid');
$user = $this->container->get('security.context')->getToken()->getUser();
$onrgunitParent = $this->getRequest()->get('orgunitParent');
$orunitUid = $this->getRequest()->get('selectedOrganisationunit');
if ($orunitUid != null) {
$orgunit = $em->getRepository('HrisOrganisationunitBundle:Organisationunit')->findOneBy(array('uid' => $orunitUid));
} else {
$orgunit = $user->getOrganisationunit();
}
$form = $em->getRepository('HrisFormBundle:Form')->find($formId);
$uniqueFields = $form->getUniqueRecordFields();
$fields = $form->getSimpleField();
foreach ($fields as $key => $field) {
$recordValue = $this->get('request')->request->get($field->getName());
/*
if($field->getDataType()->getName() == "Date"){
$recordValue = new \DateTime($recordValue);
}
*/
if ($field->getDataType()->getName() == "Date" && $recordValue != null) {
$recordValue = DateTime::createFromFormat('d/m/Y', $recordValue)->format('Y-m-d');
$recordValue = new \DateTime($recordValue);
}
/**
* Made dynamic, on which field column is used as key, i.e. uid, name or id.
*/
$recordFieldKey = ucfirst(Record::getFieldKey());
$valueKey = call_user_func_array(array($field, "get{$recordFieldKey}"), array());
$recordArray[$valueKey] = $recordValue;
}
$entity->setValue($recordArray);
$entity->setForm($form);
$entity->setInstance($instance);
$entity->setOrganisationunit($orgunit);
$entity->setUsername($user->getUsername());
$entity->setComplete(True);
$entity->setCorrect(True);
$entity->setHashistory(False);
$entity->setHastraining(False);
//if ($entity->isValid()) {
$em = $this->getDoctrine()->getManager();
$em->persist($entity);
$em->flush();
return $this->redirect($this->generateUrl('record_viewrecords', array('formid' => $form->getId())));
}
/**
* Remove record
*
* @param Record $record
*/
public function removeRecord(Record $record)
{
$this->record->removeElement($record);
}
/**
* Importing Legacy Records.
*
*/
public function legacyUpdateRecordsAction($records)
{
global $refOrganisationUnit, $refFieldOptions, $refField;
$em = $this->getDoctrine()->getManager();
$records = json_decode($records, True);
foreach ($records as $keys => $record) {
$value = $record[0]['value'];
foreach ($value as $key => $valueInstance) {
//checking if the Key Exists and substitute with the DB reference Key
if (isset($refField[$key])) {
unset($value[$key]);
//checking if the field is Select field for reference checking
$field = $em->getRepository('HrisFormBundle:Field')->findOneby(array('uid' => $refField[$key]));
if ($field->getInputType()->getName() == 'Select') {
if (isset($refFieldOptions[$valueInstance])) {
$valueInstance = $refFieldOptions[$valueInstance];
}
}
$value[$refField[$key]] = $valueInstance;
}
}
//getting the Object if Exist from the Database
$form = $em->getRepository('HrisFormBundle:Form')->findOneby(array('name' => $record['form_name']));
$orgunitParent = $em->getRepository('HrisOrganisationunitBundle:Organisationunit')->findOneby(array('longname' => $record['orgunit_parent']));
$orgunit = $em->getRepository('HrisOrganisationunitBundle:Organisationunit')->findOneby(array('longname' => $record['orgunit_name'], 'parent' => $orgunitParent));
$recordObjectCheck = $em->getRepository('HrisRecordsBundle:Record')->findOneby(array('instance' => $record[0]['instance']));
$createdDate = new \DateTime($record[0]['inputDate']['date']);
if (empty($recordObjectCheck)) {
$recordObject = new Record();
$recordObject->setForm($form);
$recordObject->setOrganisationunit($orgunit);
$recordObject->setInstance($record[0]['instance']);
$recordObject->setUid(uniqid());
$recordObject->setValue($value);
$recordObject->setDatecreated($createdDate);
$recordObject->setUsername($record[0]['username']);
$em->persist($recordObject);
}
}
$em->flush();
return new Response('success');
}
public function load(ObjectManager $manager)
{
$stopwatch = new Stopwatch();
$stopwatch->start('dummyRecordGeneration');
// Populate dummy forms
$this->addDummyFemaleNames();
$this->addDummyMaleNmes();
$this->addDummyCourseNames();
$this->addDummyCourseLocations();
$this->addDummySponsors();
$loadUserData = new LoadUserData();
$loadUserData->addDummyUsers();
$dummyUsers = $loadUserData->getUsers();
$loadFieldData = new LoadFieldData();
$loadFieldData->addDummyFields();
$dummyFields = $loadFieldData->getFields();
$loadFormData = new LoadFormData();
$loadFormData->addDummyForms();
$dummyForms = $loadFormData->getForms();
$organiastionunits = $manager->getRepository('HrisOrganisationunitBundle:Organisationunit')->findAll();
/*
* Add data to facilities
*/
if (!empty($organiastionunits)) {
foreach ($organiastionunits as $organiastionunitKey => $organisationunit) {
/*
* Assign data to dispensary, hospital and health centres only.
*/
if (preg_match('/dispensary|hospital|health centre|council/i', $organisationunit->getLongname())) {
// Initiate record entering
// Enter two records for each orgunit
for ($recordIncr = 0; $recordIncr < $this->recordsPerOrganisationunit; $recordIncr++) {
$record = new Record();
$record->setOrganisationunit($organisationunit);
// Enter record for public and private form
$formNames = array('Public Employee Form', 'Private Employee Form');
$formName = $formNames[array_rand($formNames, 1)];
if (empty($formName)) {
$formName = 'Public Employee Form';
}
$form = $manager->getRepository('HrisFormBundle:Form')->findOneBy(array('name' => $formName));
// Find history fields belonging to this form for population of data
$queryBuilder = $manager->createQueryBuilder();
$historyFields = $queryBuilder->select('field')->from('HrisFormBundle:Field', 'field')->join('field.formFieldMember', 'formFieldMember')->join('formFieldMember.form', 'form')->where('form.id=:formId')->andWhere('field.hashistory=True')->setParameter('formId', $form->getId())->getQuery()->getResult();
$record->setForm($form);
$record->setComplete(True);
$record->setCorrect(True);
$record->setHashistory(False);
$record->setHastraining(False);
$dummyUserKey = array_rand($dummyUsers, 1);
$dummyUsername = $dummyUsers[$dummyUserKey]['username'];
$record->setUsername($dummyUsername);
// Constructing a Value Array
// @todo removing hard-coding of HrisRecordBundle:Record values
$value = array();
// Fetch all field members belonging to the form and add records
// Roll a dice with gender of employee to pick name
$genders = array('Male', 'Female');
$gender_picked = array_rand($genders, 1);
$formFieldMembers = $manager->getRepository('HrisFormBundle:FormFieldMember')->findBy(array('form' => $form));
foreach ($formFieldMembers as $formFieldMemberKey => $formFieldMember) {
/**
* Made dynamic, on which field column is used as key, i.e. uid, name or id.
*/
// Translates to $formFieldMember->getField()->getUid()
// or $formFieldMember->getField()->getUid() depending on value of $recordKeyName
$recordKeyName = ucfirst(Record::getFieldKey());
$valueKey = call_user_func_array(array($formFieldMember->getField(), "get{$recordKeyName}"), array());
if ($formFieldMember->getField()->getName() == "Firstname" || $formFieldMember->getField()->getName() == "Middlename" || $formFieldMember->getField()->getName() == "Surname" || $formFieldMember->getField()->getName() == "NextofKin") {
// Deal with names
if ($gender_picked == "Female" && ($formFieldMember->getField()->getName() == "Firstname" || $formFieldMember->getField()->getName() == "NextofKin")) {
$value[$valueKey] = $this->femaleNames[array_rand($this->femaleNames, 1)];
} else {
$value[$valueKey] = $this->maleNames[array_rand($this->maleNames, 1)];
}
if ($formFieldMember->getField()->getName() == "NextofKin") {
$value[$valueKey] .= ' ' . $this->maleNames[array_rand($this->maleNames, 1)];
}
//@todo remove hard-coding of instance
// used later for instance formulation
if ($formFieldMember->getField()->getName() == "Firstname") {
$firstName = $value[$valueKey];
}
if ($formFieldMember->getField()->getName() == "Middlename") {
$middleName = $value[$valueKey];
}
if ($formFieldMember->getField()->getName() == "Surname") {
$surname = $value[$valueKey];
}
} else {
if ($formFieldMember->getField()->getInputType()->getName() == "Select") {
// Deal with select
/**
* Made dynamic, on which field column is used as key, i.e. uid, name or id.
*/
// Translates to $fieldOptions[0]->getUid()
// or $fieldOptions[0]->getValue() depending on value of $recordKeyName
// $fieldOptionKey = ucfirst($record->getFieldOptionKey());
//$valueKey = call_user_func_array(array($fieldOptions[0], "get${fieldOptionKey}"),array());
$fieldOptionKey = ucfirst(Record::getFieldOptionKey());
$fieldOptions = $manager->getRepository('HrisFormBundle:FieldOption')->findBy(array('field' => $formFieldMember->getField()));
// For case of gender choose match name with gender
if ($formFieldMember->getField()->getName() == "Sex") {
// Made FieldOption key to store in record value array dynamic.
if ($fieldOptions[0]->getValue() == $gender_picked) {
$value[$valueKey] = call_user_func_array(array($fieldOptions[0], "get{$fieldOptionKey}"), array());
} else {
$value[$valueKey] = call_user_func_array(array($fieldOptions[1], "get{$fieldOptionKey}"), array());
}
} else {
// Made fieldOption key to store in record value array dynamic
$value[$valueKey] = call_user_func_array(array($fieldOptions[array_rand($fieldOptions, 1)], "get{$fieldOptionKey}"), array());
}
} else {
if ($formFieldMember->getField()->getInputType()->getName() == "Date") {
// Deal with dates
// If birth date pick 20 - 55 date range
// If employment data set it to birth date range+18
// If confirmation date, set it to employment date+1
// If promotion date, set it to confirmation+3
$beginDateStart = 50;
$beginDateStop = 75;
$endDateStart = 40;
$endDateStop = 50;
if ($formFieldMember->getField()->getName() == "DateOfBirth") {
$beginDateStart = 50;
$beginDateStop = 75;
$endDateStart = 40;
$endDateStop = 50;
} elseif ($formFieldMember->getField()->getName() == "DateofFirstAppointment") {
$beginDateStart -= 36;
$beginDateStop -= 36;
$endDateStart -= 36;
$endDateStop -= 36;
} elseif ($formFieldMember->getField()->getName() == "DateofConfirmation") {
$beginDateStart -= 37;
$beginDateStop -= 37;
$endDateStart -= 37;
$endDateStop -= 37;
} elseif ($formFieldMember->getField()->getName() == "DateofLastPromotion") {
$beginDateStart -= 40;
$beginDateStop -= 40;
$endDateStart -= 40;
$endDateStop -= 40;
}
$value[$valueKey] = new \DateTime($this->getRandDate(array($beginDateStart, $beginDateStop), array($endDateStart, $endDateStop)));
//@todo remove hard-coding of instance
if ($formFieldMember->getField()->getName() == "DateOfBirth") {
$dateOfBirth = $value[$valueKey];
}
} else {
if ($formFieldMember->getField()->getInputType()->getName() == "Text") {
// Deal with numbers
if ($formFieldMember->getField()->getName() == "NumberofChildrenDependants") {
$value[$valueKey] = rand(0, 10);
} elseif ($formFieldMember->getField()->getName() == "CheckNumber") {
$value[$valueKey] = rand(9999999, 9999999999);
} elseif ($formFieldMember->getField()->getName() == "EmployersFileNumber") {
$value[$valueKey] = "FN/" . rand(100, 100000);
} elseif ($formFieldMember->getField()->getName() == "RegistrationNumber") {
$value[$valueKey] = "RB/" . rand(10, 10000);
} elseif ($formFieldMember->getField()->getName() == "MonthlyBasicSalary") {
$value[$valueKey] = rand(100, 1500) . '000';
} else {
$value[$valueKey] = $this->maleNames[array_rand($this->maleNames, 1)] . " Street";
}
} else {
if ($formFieldMember->getField()->getInputType()->getName() == "TextArea") {
// Deal with domicile, contact
if ($formFieldMember->getField()->getName() == "ContactsofEmployee" || $formFieldMember->getField()->getName() == "ContactsofNextofKin") {
$value[$valueKey] = "+255" . rand(6, 7) . rand(53, 69) . rand(01, 998) . rand(01, 998);
}
}
}
}
}
}
}
$instance = md5($firstName . $middleName . $surname . $dateOfBirth->format('Y-m-d'));
$record->setInstance($instance);
$record->setValue($value);
//@todo check for uniqueness of instance and unique fields
$recordReference = strtolower(str_replace(' ', '', $record->getInstance())) . '-record';
$this->addReference($recordReference, $record);
$manager->persist($record);
// Randomly on flip of a coin assign history & training data
$outcomes = array(True, False);
if ($outcomes[array_rand($outcomes, 1)]) {
// Assign randomly between 2 to 4 histories per record
$numberofHistoriesToAssign = array(1, 2);
for ($incr = 0; $incr < $numberofHistoriesToAssign[array_rand($numberofHistoriesToAssign, 1)]; $incr++) {
$history = new History();
$history->setRecord($record);
$history->setUsername($record->getUsername());
//Calculate start date ranging starting form now-2yrs back and and stopping between 3-5 years back
$beginDateStart = 3;
$beginDateStop = 5;
$endDateStart = 0;
$endDateStop = 2;
$startDate = new \DateTime($this->getRandDate(array($beginDateStart, $beginDateStop), array($endDateStart, $endDateStop)));
$history->setStartdate($startDate);
$historyField = $historyFields[array_rand($historyFields, 1)];
//echo get_class($historyField);exit;
// If history field is Combo assign combo if text assign text
if ($historyField->getInputType() == "Select") {
$historyFieldOptions = $historyField->getFieldOption();
$historyFieldOptions = $historyFieldOptions->getValues();
$selectedHistoryOption = $historyFieldOptions[array_rand($historyFieldOptions, 1)];
$historyValue = $selectedHistoryOption->getValue();
} elseif ($historyField->getInputType() == "Date") {
//Calculate start date ranging starting form 1-3yrs back and and stopping between 5-8 years back
$beginDateStart = 5;
$beginDateStop = 8;
$endDateStart = 1;
$endDateStop = 3;
$historyDateObject = new \DateTime($this->getRandDate(array($beginDateStart, $beginDateStop), array($endDateStart, $endDateStop)));
$historyValue = $historyDateObject->format('Y-m-d');
} else {
// Deal with string history fields
if ($historyField->getName() == "Firstname" || $historyField->getName() == "Middlename" || $historyField->getName() == "Surname" || $historyField->getName() == "NextofKin") {
// Deal with names
if ($gender_picked == "Female" && ($historyField->getName() == "Firstname" || $historyField->getName() == "NextofKin")) {
$historyValue = $this->femaleNames[array_rand($this->femaleNames, 1)];
} else {
$historyValue = $this->maleNames[array_rand($this->maleNames, 1)];
}
if ($historyField->getName() == "NextofKin") {
$historyValue .= ' ' . $this->maleNames[array_rand($this->maleNames, 1)];
}
} else {
if ($historyField->getInputType()->getName() == "Text") {
// Deal with numbers
if ($historyField->getName() == "NumberofChildrenDependants") {
$historyValue = rand(0, 10);
} elseif ($historyField->getName() == "CheckNumber") {
$historyValue = rand(9999999, 9999999999);
} elseif ($historyField->getName() == "EmployersFileNumber") {
$historyValue = "FN/" . rand(100, 100000);
} elseif ($historyField->getName() == "RegistrationNumber") {
$historyValue = "RB/" . rand(10, 10000);
} elseif ($historyField->getName() == "MonthlyBasicSalary") {
$historyValue = rand(100, 1500) . '000';
} else {
$historyValue = $this->maleNames[array_rand($this->maleNames, 1)] . " Street";
}
} else {
if ($historyField->getInputType()->getName() == "TextArea") {
// Deal with domicile, contact
if ($historyField->getName() == "ContactsofEmployee" || $historyField->getName() == "ContactsofNextofKin") {
$historyValue = "+255" . rand(6, 7) . rand(53, 69) . rand(01, 998) . rand(01, 998);
}
}
}
}
}
$reason = $historyField->getCaption() . " changed.";
$history->setField($historyField);
$history->setHistory($historyValue);
$history->setReason($reason);
$manager->persist($history);
unset($history);
}
$record->setHashistory(True);
$manager->persist($record);
}
if ($outcomes[array_rand($outcomes, 1)]) {
// Assign randomly between 2 to 4 trainings per record
$numberofTrainingsToAssign = array(1, 2);
for ($incr = 0; $incr < $numberofTrainingsToAssign[array_rand($numberofTrainingsToAssign, 1)]; $incr++) {
$training = new Training();
$training->setRecord($record);
$training->setCoursename($this->courseNames[array_rand($this->courseNames, 1)]);
$training->setCourselocation($this->courseLocations[array_rand($this->courseLocations, 1)]);
$training->setSponsor($this->sponsor[array_rand($this->sponsor, 1)]);
//Calculate start date ranging starting form 9-10yrs back and and stopping between 10-12 years back
$beginDateStart = 10;
$beginDateStop = 12;
$endDateStart = 9;
$endDateStop = 10;
$startDate = new \DateTime($this->getRandDate(array($beginDateStart, $beginDateStop), array($endDateStart, $endDateStop)));
//Calculate end date ranging starting form 11-13yrs back and and stopping between 13-15 years back
$beginDateStart = 13;
$beginDateStop = 15;
$endDateStart = 11;
$endDateStop = 13;
$endDate = new \DateTime($this->getRandDate(array($beginDateStart, $beginDateStop), array($endDateStart, $endDateStop)));
$training->setStartdate($startDate);
$training->setEnddate($endDate);
$training->setUsername($record->getUsername());
$manager->persist($training);
unset($training);
}
$record->setHastraining(True);
$manager->persist($record);
}
unset($record);
}
}
}
}
$manager->flush();
/*
* Check Clock for time spent
*/
$dummyRecordGenerationTime = $stopwatch->stop('dummyRecordGeneration');
$duration = $dummyRecordGenerationTime->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';
}
echo "\tDummy Records generation complete in " . $durationMessage . ".\n\n";
}
/**
* Creates a new ExportMetadata entity.
*
* @Secure(roles="ROLE_SUPER_USER,ROLE_EXPORTMETADATA_CREATE")
* @Route("/{_format}", requirements={"_format"="json|"}, defaults={"_format"="json"}, name="importexport_exportmetadata_create")
* @Method("POST")
* @Template("HrisImportExportBundle:ExportMetadata:export.json.twig")
*/
public function createAction(Request $request, $_format = "json")
{
$serializer = $this->container->get('serializer');
$exportMetadataForm = $this->createForm(new ExportMetadataType(), null, array('em' => $this->getDoctrine()->getManager()));
$exportMetadataForm->bind($request);
if ($exportMetadataForm->isValid()) {
$exportMetadataFormData = $exportMetadataForm->getData();
$organisationunit = $exportMetadataFormData['organisationunit'];
$forms = $exportMetadataFormData['forms'];
$withLowerLevels = $exportMetadataFormData['withLowerLevels'];
}
/*
* Generate an export file with data records
*/
$formIds = array();
foreach ($forms as $formKey => $form) {
$formIds[] = $form->getId();
foreach ($form->getFormFieldMember() as $key => $fieldObject) {
if (!$fieldObject->getField()->getIsCalculated()) {
if (empty($formFieldMemberIds)) {
$formFieldMemberIds = $fieldObject->getField()->getId();
} else {
$formFieldMemberIds .= ',' . $fieldObject->getField()->getId();
}
}
}
}
$em = $this->getDoctrine()->getManager();
$queryBuilder = $this->getDoctrine()->getManager()->createQueryBuilder();
$userManager = $this->container->get('fos_user.user_manager');
$user = $userManager->findUserByUsername($this->getUser());
//Prepare field Option map, converting from stored FieldOption key in record value array to actual text value
$fieldOptions = $this->getDoctrine()->getManager()->getRepository('HrisFormBundle:FieldOption')->findAll();
foreach ($fieldOptions as $fieldOptionKey => $fieldOption) {
$recordFieldOptionKey = ucfirst(Record::getFieldOptionKey());
$fieldOptionMap[call_user_func_array(array($fieldOption, "get{$recordFieldOptionKey}"), array())] = $fieldOption->getValue();
}
//Prepare field map, converting from stored FieldOption key in record value array to actual text value
$fields = $this->getDoctrine()->getManager()->getRepository('HrisFormBundle:Field')->findAll();
foreach ($fields as $fieldKey => $field) {
$recordFieldKey = ucfirst(Record::getFieldKey());
$fieldMap[$field->getName()] = call_user_func_array(array($field, "get{$recordFieldKey}"), array());
}
$records = $queryBuilder->select(' record.id id,
organisationunit.id organisationunit_id,
form.id form_id,
record.uid,
record.instance,
record.value,
record.complete,
record.correct,
record.hashistory,
record.hastraining,
record.datecreated,
record.lastupdated,
record.username')->from('HrisRecordsBundle:Record', 'record')->join('record.organisationunit', 'organisationunit')->join('record.form', 'form')->join('organisationunit.organisationunitStructure', 'organisationunitStructure')->join('organisationunitStructure.level', 'organisationunitLevel')->andWhere('organisationunit.active=True');
if ($withLowerLevels) {
$records = $records->andWhere('organisationunitLevel.level >= (
SELECT selectedOrganisationunitLevel.level
FROM HrisOrganisationunitBundle:OrganisationunitStructure selectedOrganisationunitStructure
INNER JOIN selectedOrganisationunitStructure.level selectedOrganisationunitLevel
WHERE selectedOrganisationunitStructure.organisationunit=:selectedOrganisationunit )')->andWhere('organisationunitStructure.level' . $organisationunit->getOrganisationunitStructure()->getLevel()->getLevel() . 'Organisationunit=:levelId');
} else {
$records = $records->andWhere('organisationunit.id=' . $organisationunit->getId());
}
$records = $records->andWhere($queryBuilder->expr()->in('form.id', ':formIds'))->setParameters(array('levelId' => $organisationunit->getId(), 'selectedOrganisationunit' => $organisationunit->getId(), 'formIds' => $formIds))->getQuery()->getResult();
//Field Details
$fields = $this->getDoctrine()->getManager()->createQueryBuilder()->select(' field.id,
field.uid,
dataType.id datatype_id,
inputType.id inputtype_id,
field.name,
field.compulsory,
field.isUnique,
field.datecreated,
field.lastupdated')->from('HrisFormBundle:Field', 'field')->join('field.dataType', 'dataType')->join('field.inputType', 'inputType')->getQuery()->getResult();
//Field Option Details
$fieldOptions = $this->getDoctrine()->getManager()->createQueryBuilder()->select('fieldOption.id,
fieldOption.value,
field.id field_id,
fieldOption.datecreated,
fieldOption.lastupdated')->from('HrisFormBundle:FieldOption', 'fieldOption')->join('fieldOption.field', 'field')->getQuery()->getResult();
//Organisationunit details
$organisationunits = $this->getDoctrine()->getManager()->createQueryBuilder()->select('organisationunit.uid organisationunit_uid,
organisationunit.longname organisationunit_longname,
organisationunit.datecreated organisationunit_datecreated,
organisationunit.lastupdated organisationunit_lastupdated')->from('HrisOrganisationunitBundle:Organisationunit', 'organisationunit')->join('organisationunit.organisationunitStructure', 'organisationunitStructure')->join('organisationunitStructure.level', 'organisationunitLevel')->andWhere('organisationunit.active=True');
if ($withLowerLevels) {
$organisationunits = $organisationunits->andWhere('organisationunitLevel.level >= (
SELECT selectedOrganisationunitLevel.level
FROM HrisOrganisationunitBundle:OrganisationunitStructure selectedOrganisationunitStructure
INNER JOIN selectedOrganisationunitStructure.level selectedOrganisationunitLevel
WHERE selectedOrganisationunitStructure.organisationunit=:selectedOrganisationunit )')->andWhere('organisationunitStructure.level' . $organisationunit->getOrganisationunitStructure()->getLevel()->getLevel() . 'Organisationunit=:levelId');
} else {
$organisationunits = $organisationunits->andWhere('organisationunit.id=' . $organisationunit->getId());
}
$organisationunits = $organisationunits->setParameters(array('levelId' => $organisationunit->getId(), 'selectedOrganisationunit' => $organisationunit->getId()))->getQuery()->getResult();
$dataexport = array('hris_organisationunit' => $organisationunits, 'hris_field' => $fields, 'hris_fieldoption' => $fieldOptions, 'hris_record' => $records);
$serializer = $this->container->get('serializer');
return array('records' => $serializer->serialize($dataexport, empty($_format) ? "json" : $_format));
}
/**
* Lists all Records by forms.
*
* @Secure(roles="ROLE_SUPER_USER,ROLE_RECORD_LIST")
* @Route("/viewrecords/{formid}/form", requirements={"formid"="\d+"}, defaults={"formid"=0}, name="instancerecord_viewrecords")
* @Method("GET")
* @Template()
*/
public function viewRecordsAction($formid)
{
$em = $this->getDoctrine()->getManager();
$queryBuilder = $this->getDoctrine()->getManager()->createQueryBuilder();
$userManager = $this->get('fos_user.user_manager');
$user = $userManager->findUserByUsername($this->getUser());
$organisationunit = $user->getOrganisationunit();
if ($formid == 0) {
$formIds = $this->getDoctrine()->getManager()->createQueryBuilder()->select('form.id')->from('HrisFormBundle:Form', 'form')->getQuery()->getArrayResult();
$formIds = $this->array_value_recursive('id', $formIds);
$forms = $em->getRepository('HrisFormBundle:Form')->findAll();
} else {
$forms = $em->getRepository('HrisFormBundle:Form')->findby(array('id' => $formid));
$formIds[] = $formid;
}
//Prepare field Option map, converting from stored FieldOption key in record value array to actual text value
$fieldOptions = $this->getDoctrine()->getManager()->getRepository('HrisFormBundle:FieldOption')->findAll();
foreach ($fieldOptions as $fieldOptionKey => $fieldOption) {
$recordFieldOptionKey = ucfirst(Record::getFieldOptionKey());
$fieldOptionMap[call_user_func_array(array($fieldOption, "get{$recordFieldOptionKey}"), array())] = $fieldOption->getValue();
}
//If user's organisationunit is data entry level pull only records of his organisationunit
//else pull lower children too.
$records = $queryBuilder->select('record')->from('HrisRecordsBundle:Record', 'record')->join('record.organisationunit', 'organisationunit')->join('record.form', 'form');
if ($organisationunit->getOrganisationunitStructure()->getLevel()->getDataentrylevel()) {
$records = $records->join('organisationunit.organisationunitStructure', 'organisationunitStructure')->join('organisationunitStructure.level', 'organisationunitLevel')->andWhere('organisationunitLevel.level >= (
SELECT selectedOrganisationunitLevel.level
FROM HrisOrganisationunitBundle:OrganisationunitStructure selectedOrganisationunitStructure
INNER JOIN selectedOrganisationunitStructure.level selectedOrganisationunitLevel
WHERE selectedOrganisationunitStructure.organisationunit=:selectedOrganisationunit )')->andWhere('organisationunitStructure.level' . $organisationunit->getOrganisationunitStructure()->getLevel()->getLevel() . 'Organisationunit=:levelId');
$parameters = array('levelId' => $organisationunit->getId(), 'selectedOrganisationunit' => $organisationunit->getId(), 'formIds' => $formIds);
} else {
$records = $records->andWhere('organisationunit.id=:selectedOrganisationunit');
$parameters = array('selectedOrganisationunit' => $organisationunit->getId(), 'formIds' => $formIds);
}
$records = $records->andWhere($queryBuilder->expr()->in('form.id', ':formIds'))->setParameters($parameters)->getQuery()->getResult();
$formNames = NULL;
$visibleFields = array();
$formFields = array();
$incr = 0;
$formIds = array();
foreach ($forms as $formKey => $form) {
$incr++;
$formIds[] = $form->getId();
// Concatenate form Names
if (empty($formNames)) {
$formNames = $form->getTitle();
} else {
if (count($formNames) == $incr) {
$formNames .= ',' . $form->getTitle();
}
}
// Accrue visible fields
foreach ($form->getFormVisibleFields() as $visibleFieldKey => $visibleField) {
if (!in_array($visibleField->getField(), $visibleFields)) {
$visibleFields[] = $visibleField->getField();
}
}
// Accrue form fields
foreach ($form->getFormFieldMember() as $formFieldKey => $formField) {
if (!in_array($formField->getField(), $formFields)) {
$formFields[] = $formField->getField();
}
}
}
$title = "Employee Records for " . $organisationunit->getLongname();
$title .= " for " . $formNames;
if (empty($visibleFields)) {
$visibleFields = $formFields;
}
//getting all User Forms for User Migration
$user = $this->container->get('security.context')->getToken()->getUser();
$userForms = $user->getForm();
$delete_forms = NULL;
foreach ($records as $entity) {
$delete_form = $this->createDeleteForm($entity->getId());
$delete_forms[$entity->getId()] = $delete_form->createView();
}
return array('title' => $title, 'visibleFields' => $visibleFields, 'formFields' => $formFields, 'records' => $records, 'optionMap' => $fieldOptionMap, 'userForms' => $userForms, 'delete_forms' => $delete_forms, 'formid' => $formid);
}
/**
* Generate resource table
*
* @param $entityManager
* @return string
*/
public function generateResourceTable($entityManager, $logger = NULL)
{
$totalInsertedRecords = NULL;
$totalResourceTableFields = NULL;
$stopwatch = new Stopwatch();
$stopwatch->start('resourceTableGeneration');
$returnMessage = NULL;
$schemaManager = $entityManager->getConnection()->getSchemaManager();
$resourceTableName = '_resource_' . str_replace(' ', '_', trim(strtolower($this->getName())));
//Prepare database name
if ($this->getIsgenerating() == False && ($this->isResourceTableOutdated($entityManager) == True || $this->isResourceTableCompletelyGenerated($entityManager) == False)) {
$logger->info('Resource table is out dated, was not completely generated');
/*
* Resource table is out dated, was not completely generated
*/
//Switch state to generating
$this->setIsgenerating(True);
$entityManager->persist($this);
try {
$entityManager->flush();
} catch (\Doctrine\Orm\NoResultException $e) {
//@todo Cross-check, if database is left in generating state, it'll never be udpated ever.
$error = "Error in Changing State to generating";
echo $error;
return False;
}
if ($this->isResourceTableOutdated($entityManager)) {
$this->messagelog = "Regeneration Trigger: Outdated Resource Table.\n";
} elseif ($this->isResourceTableCompletelyGenerated($entityManager) == False) {
$this->messagelog = "Regeneration Trigger:Incomplete Resource Tabe.\n";
} else {
$this->messagelog = '';
}
/**
* @var $resourceTable String
*/
// Cleanup any residue temporary resourcetable left
if ($schemaManager->tablesExist($resourceTableName . '_temporary')) {
$schemaManager->dropTable($resourceTableName . '_temporary');
}
$resourceTable = new Table($resourceTableName . '_temporary');
//Create database table
// Create primary key
$resourceTable->addColumn('id', "integer", array('nullable' => true, 'precision' => 0, 'scale' => 0));
$resourceTable->addColumn('instance', "string", array('length' => 64, 'notnull' => false));
$resourceTable->setPrimaryKey(array('id'), 'IDX_' . uniqid(''));
$resourceTable->addIndex(array('id'), 'IDX_' . uniqid(''));
// Create other columns(fields, organisationunits,etc) in the resource table
foreach ($this->getResourceTableFieldMember() as $resourceTableKey => $resourceTableFieldMember) {
$field = $resourceTableFieldMember->getField();
if ($field->getDataType()->getName() == "String") {
$resourceTable->addColumn($field->getName(), "string", array('length' => 64, 'notnull' => false));
} elseif ($field->getDataType()->getName() == "Integer") {
$resourceTable->addColumn($field->getName(), "integer", array('notnull' => false, 'precision' => 0, 'scale' => 0));
} elseif ($field->getDataType()->getName() == "Double") {
$resourceTable->addColumn($field->getName(), "float", array('notnull' => false, 'precision' => 0, 'scale' => 0));
} elseif ($field->getDataType()->getName() == "Date") {
$resourceTable->addColumn($field->getName(), "date", array('notnull' => false, 'precision' => 0, 'scale' => 0));
// Additional analysis columns
//$resourceTable->addColumn($field->getName().'_day', "string",array('length'=>64, 'notnull'=>false));
//$resourceTable->addColumn($field->getName().'_month_number', "integer",array('notnull'=>false,'precision'=>0, 'scale'=>0));
$resourceTable->addColumn($field->getName() . '_month_text', "string", array('length' => 64, 'notnull' => false));
$resourceTable->addColumn($field->getName() . '_year', "integer", array('notnull' => false, 'precision' => 0, 'scale' => 0));
//$resourceTable->addColumn($field->getName().'_month_and_year', "string",array('length'=>64, 'notnull'=>false));
}
// @todo implement after creation of history date class
// Add History date field for fields with history
if ($field->getHashistory()) {
$resourceTable->addColumn($field->getName() . '_last_updated', "date", array('notnull' => false, 'precision' => 0, 'scale' => 0));
// Additional analysis columns
//$resourceTable->addColumn($field->getName().'_last_updated_day', "string",array('length'=>64, 'notnull'=>false));
//$resourceTable->addColumn($field->getName().'_last_updated_month_number', "integer",array('notnull'=>false,'precision'=>0, 'scale'=>0));
$resourceTable->addColumn($field->getName() . '_last_updated_month_text', "string", array('length' => 64, 'notnull' => false));
$resourceTable->addColumn($field->getName() . '_last_updated_year', "integer", array('notnull' => false, 'precision' => 0, 'scale' => 0));
//$resourceTable->addColumn($field->getName().'_last_updated_month_and_year', "string",array('length'=>64, 'notnull'=>false));
}
$totalResourceTableFields++;
unset($field);
}
// Make OrganisationunitLevels of orgunit
$organisationunitLevels = $entityManager->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()))));
$resourceTable->addColumn($organisationunitLevelName, "string", array('length' => 64, 'notnull' => false));
}
// Make OrganisationunitGroupsets Column
$organisationunitGroupsets = $entityManager->getRepository('HrisOrganisationunitBundle:OrganisationunitGroupset')->findAll();
foreach ($organisationunitGroupsets as $organisationunitGroupsetKey => $organisationunitGroupset) {
$resourceTable->addColumn($organisationunitGroupset->getName(), "string", array('length' => 64, 'notnull' => false));
}
// Form and Organisationunit name
$resourceTable->addColumn("Organisationunit_name", "string", array('length' => 64, 'notnull' => false));
$resourceTable->addColumn("Form_name", "string", array('length' => 64, 'notnull' => false));
$resourceTable->addColumn('Organisationunit_id', "integer", array('notnull' => false, 'precision' => 0, 'scale' => 0));
$resourceTable->addColumn('Form_id', "integer", array('notnull' => false, 'precision' => 0, 'scale' => 0));
$resourceTable->addColumn("Lastupdated", "datetime", array('notnull' => false, 'precision' => 0, 'scale' => 0));
// Creating table
$schemaManager->createTable($resourceTable);
unset($resourceTable);
$schemaGenerationLap = $stopwatch->lap('resourceTableGeneration');
$schemaGenerationDuration = round($schemaGenerationLap->getDuration() / 1000, 2);
$this->messagelog .= 'Operation: Table named ' . $resourceTableName . ' with ' . $totalResourceTableFields . " Fields Generated in " . $schemaGenerationDuration . " seconds.\n";
// Populating data into created table
$queryBuilder = $entityManager->createQueryBuilder()->select('record')->from('HrisRecordsBundle:Record', 'record')->join('record.organisationunit', 'organisationunit')->join('record.form', 'form')->join('organisationunit.organisationunitStructure', 'organisationunitStructure')->getQuery();
try {
$records = $queryBuilder->getResult();
} catch (\Doctrine\Orm\NoResultException $e) {
echo 'Error in returning Data Values';
}
if (!empty($records)) {
/**
* Make sure organisationunitstructure is uptodate for good measure
*/
// Check and Notify if organisationunit structure doesn't exist
$queryBuilder = $entityManager->createQueryBuilder();
$organisationunitStructureCount = $queryBuilder->select('count( organisationunitStructure.id )')->from('HrisOrganisationunitBundle:OrganisationunitStructure', 'organisationunitStructure')->getQuery()->getSingleScalarResult();
$queryBuilder = $entityManager->createQueryBuilder();
$organisationunitCount = $queryBuilder->select('count( organisationunit.id )')->from('HrisOrganisationunitBundle:Organisationunit', 'organisationunit')->getQuery()->getSingleScalarResult();
// Regenerate Orgunit Stucture of Orgunit and OrgunitStructure Differs
if ($organisationunitCount != $organisationunitStructureCount) {
$logger->info('Regenerating organisationunit structure');
$this->returnMessage = '';
// Regenerate Orgunit Structure
$organisationunitStructure = new OrganisationunitStructureController();
$this->returnMessage = $organisationunitStructure->regenerateOrganisationunitStructure($entityManager);
} else {
$this->returnMessage = 'Organisationunit structure is complete!';
}
// Regenerate Levels if OrgunitLevel and DISTINCT OrgunitStructure.level differs
$organisationunitStructureLevels = $entityManager->createQuery('SELECT DISTINCT organisationunitLevel.level FROM HrisOrganisationunitBundle:OrganisationunitStructure organisationunitStructure INNER JOIN organisationunitStructure.level organisationunitLevel ORDER BY organisationunitLevel.level ')->getResult();
$organisationunitLevelInfos = $entityManager->createQuery('SELECT organisationunitLevel.level,organisationunitLevel.name,organisationunitLevel.description FROM HrisOrganisationunitBundle:OrganisationunitLevel organisationunitLevel ORDER BY organisationunitLevel.level ')->getResult();
$organisationunitStructureLevels = $this->array_value_recursive('level', $organisationunitStructureLevels);
$organisationunitLevelsLevel = $this->array_value_recursive('level', $organisationunitLevelInfos);
if ($organisationunitLevelsLevel != $organisationunitStructureLevels && !empty($organisationunitStructureLevels)) {
$logger->info('Regenerating organisationunit levels');
if (!empty($organisationunitLevelInfos)) {
// Cache in-memory saved Level names and descriptions
$organisationunitLevelsName = $this->array_value_recursive('name', $organisationunitLevelInfos);
$organisationunitLevelsDescription = $this->array_value_recursive('description', $organisationunitLevelInfos);
$organisationunitLevelsName = array_combine($organisationunitLevelsLevel, $organisationunitLevelsName);
$organisationunitLevelsDescription = array_combine($organisationunitLevelsLevel, $organisationunitLevelsDescription);
$qb = $entityManager->createQueryBuilder('organisationunitLevel')->delete('HrisOrganisationunitBundle:OrganisationunitLevel', 'organisationunitLevel')->getQuery()->getResult();
}
foreach ($organisationunitStructureLevels as $key => $organisationunitStructureLevel) {
// Update Levels
$organisationunitLevel = new OrganisationunitLevel();
if (in_array($organisationunitStructureLevel, $organisationunitLevelsLevel)) {
$organisationunitLevel->setName($organisationunitLevelsName[$organisationunitStructureLevel]);
$organisationunitLevel->setDescription($organisationunitLevelsDescription[$organisationunitStructureLevel]);
$organisationunitLevel->setLevel($organisationunitStructureLevel);
$entityManager->persist($organisationunitLevel);
} else {
$organisationunitLevel->setName('Level' . $organisationunitStructureLevel);
$organisationunitLevel->setDescription('Level' . $organisationunitStructureLevel);
$organisationunitLevel->setLevel($organisationunitStructureLevel);
$entityManager->persist($organisationunitLevel);
}
}
$entityManager->flush();
}
$dataArray = NULL;
$id = 0;
//Prepare field Option map, converting from stored FieldOption key in record value array to actual text value
$fieldOptions = $entityManager->getRepository('HrisFormBundle:FieldOption')->findAll();
foreach ($fieldOptions as $fieldOptionKey => $fieldOption) {
$recordFieldOptionKey = ucfirst(Record::getFieldOptionKey());
$fieldOptionMap[call_user_func_array(array($fieldOption, "get{$recordFieldOptionKey}"), array())] = $fieldOption->getValue();
}
unset($fieldOptions);
foreach ($records as $recordKey => $record) {
$currentInstance = $record->getInstance();
$dataValue = $record->getValue();
$id++;
$dataArray['id'] = $id;
$age = NULL;
$retirementDate = NULL;
$employmentDuration = NULL;
$dataArray['instance'] = $record->getInstance();
foreach ($this->getResourceTableFieldMember() as $resourceTableKey => $resourceTableFieldMember) {
unset($field);
$field = $resourceTableFieldMember->getField();
// Field Options
/**
* Made dynamic, on which field column is used as key, i.e. uid, name or id.
*/
// Translates to $field->getUid()
// or $field->getUid() depending on value of $recordKeyName
$recordFieldKey = ucfirst(Record::getFieldKey());
$valueKey = call_user_func_array(array($field, "get{$recordFieldKey}"), array());
if ($field->getIsCalculated()) {
if (preg_match_all('/\\#{([^\\}]+)\\}/', $field->getCalculatedExpression(), $match)) {
$fields = $entityManager->getRepository('HrisFormBundle:Field')->findOneBy(array('name' => $match[1][0]));
// @todo mechanism to notified on flawed formula(resulting in no match in db)
$valueKey = @@call_user_func_array(array($fields, "get{$recordFieldKey}"), array());
}
}
if (isset($dataValue[$valueKey])) {
$dataArray[$field->getName()] = $dataValue[$valueKey];
if ($field->getInputType()->getName() == 'Select') {
if (isset($fieldOptionMap[$dataValue[$valueKey]])) {
// Resolve actual value from stored key
$dataArray[$field->getName()] = trim($fieldOptionMap[$dataValue[$valueKey]]);
} else {
$dataArray[$field->getName()] = NULL;
}
} else {
if ($field->getInputType()->getName() == 'Date') {
if ($field->getIsCalculated() == true) {
if (!empty($dataValue[$valueKey])) {
$displayValue = new \DateTime($dataValue[$valueKey]['date'], new \DateTimeZone($dataValue[$valueKey]['timezone']));
$datavalue = str_replace($match[0][0], $displayValue->format('Y-m-d'), $field->getCalculatedExpression());
$dataArray[$field->getName()] = eval("return {$datavalue};");
//$dataArray[$field->getName()] = trim($displayValue->format('Y-m-d H:i:s.u')); //working on date format fix
if ($field->getDataType()->getName() == 'Date') {
$dataArray[$field->getName() . '_month_text'] = trim($displayValue->format('F'));
$dataArray[$field->getName() . '_year'] = trim($displayValue->format('Y'));
}
} else {
$dataArray[$field->getName()] = NULL;
}
} else {
if (!empty($dataValue[$valueKey])) {
$displayValue = new \DateTime($dataValue[$valueKey]['date'], new \DateTimeZone($dataValue[$valueKey]['timezone']));
$dataArray[$field->getName()] = trim($displayValue->format('Y-m-d'));
//working on date format fix
//$dataArray[$field->getName().'_day'] = trim($displayValue->format('l'));
//$dataArray[$field->getName().'_month_number'] = trim($displayValue->format('m'));
$dataArray[$field->getName() . '_month_text'] = trim($displayValue->format('F'));
$dataArray[$field->getName() . '_year'] = trim($displayValue->format('Y'));
//$dataArray[$field->getName().'_month_and_year'] = trim($displayValue->format('F Y'));
} else {
$dataArray[$field->getName()] = NULL;
}
}
} else {
if ($field->getDataType()->getName() == 'Integer') {
if (!empty($dataValue[$valueKey])) {
$intValue = (int) $dataValue[$valueKey];
$dataArray[$field->getName()] = trim($intValue);
//working on Integers format fix
} else {
$dataArray[$field->getName()] = NULL;
}
} else {
if ($field->getDataType()->getName() == 'Double') {
if (!empty($dataValue[$valueKey])) {
$floatValue = (double) $dataValue[$valueKey];
$dataArray[$field->getName()] = trim($floatValue);
//working on float format fix
} else {
$dataArray[$field->getName()] = NULL;
}
} else {
$dataArray[$field->getName()] = substr(trim($dataValue[$valueKey]), 0, 63);
}
}
}
}
} else {
$dataArray[$field->getName()] = NULL;
}
// @todo implement after creation of history date class
if ($field->getHashistory() && $field->getInputType()->getName() == "Select" && isset($dataValue[$valueKey])) {
// Fetch history date with instance same as our current data
$historyDates = $entityManager->getRepository('HrisRecordsBundle:HistoryDate')->findOneBy(array('instance' => $record->getInstance(), 'history' => $dataValue[$valueKey], 'field' => $field));
if (!empty($historyDates)) {
if (!empty($historyDates)) {
$dataArray[$field->getName() . '_last_updated'] = trim($historyDates->getPreviousdate()->format('Y-m-d H:i:s.u'));
//$dataArray[$field->getName().'_last_updated_day'] = trim($historyDates->getPreviousdate()->format('l'));
//$dataArray[$field->getName().'_last_updated_month_number'] = trim($historyDates->getPreviousdate()->format('m'));
$dataArray[$field->getName() . '_last_updated_month_text'] = trim($historyDates->getPreviousdate()->format('F'));
$dataArray[$field->getName() . '_last_updated_year'] = trim($historyDates->getPreviousdate()->format('Y'));
//$dataArray[$field->getName().'_last_updated_month_and_year'] = trim($historyDates->getPreviousdate()->format('F Y'));
}
}
}
}
// Fill in Levels
foreach ($organisationunitLevels as $organisationunitLevelKey => $organisationunitLevel) {
$organisationunitLevelName = str_replace(' ', '_', "level" . $organisationunitLevel->getLevel() . "_" . str_replace(',', '_', str_replace('.', '_', str_replace('/', '_', $organisationunitLevel->getName()))));
$organisationunitStructure = $record->getOrganisationunit()->getOrganisationunitStructure();
$nLevelParent = $organisationunitStructure->getParentByNLevelsBack($record->getOrganisationunit(), $organisationunitStructure->getLevel()->getLevel() - $organisationunitLevel->getLevel());
if (!empty($nLevelParent)) {
$dataArray[$organisationunitLevelName] = $nLevelParent->getLongname();
}
$thisrganisationunitLevel = $entityManager->getRepository('HrisOrganisationunitBundle:OrganisationunitLevel')->findOneBy(array('level' => $organisationunitStructure->getLevel()->getLevel()));
$organisationunitLevelName = str_replace(' ', '_', "level" . $thisrganisationunitLevel->getLevel() . "_" . str_replace(',', '_', str_replace('.', '_', str_replace('/', '_', $thisrganisationunitLevel->getName()))));
$dataArray[$organisationunitLevelName] = $record->getOrganisationunit()->getLongname();
unset($nLevelParent);
unset($organisationunitLevelName);
unset($organisationunitStructure);
}
// Fill in Groupset Columns
foreach ($organisationunitGroupsets as $organisationunitGroupsetKey => $organisationunitGroupset) {
$organisationunitGroupsetNames = NULL;
foreach ($organisationunitGroupset->getOrganisationunitGroup() as $organisationunitGroupKey => $organisationunitGroup) {
if ($organisationunitGroup->getOrganisationunit()->contains($record->getOrganisationunit())) {
if (empty($organisationunitGroupNames)) {
$organisationunitGroupNames = $organisationunitGroup->getName();
} else {
if (!preg_match("/" . $organisationunitGroup->getName() . "/", $organisationunitGroupNames)) {
$organisationunitGroupNames .= ',' . ($organisationunitGroupNames = $organisationunitGroup->getName());
}
}
}
}
if (!isset($organisationunitGroupNames)) {
$organisationunitGroupNames = NULL;
}
$dataArray[$organisationunitGroupset->getName()] = $organisationunitGroupNames;
unset($organisationunitGroupNames);
}
// Form and Orgunit
$dataArray['Organisationunit_name'] = $record->getOrganisationunit()->getLongname();
$dataArray['Form_name'] = $record->getForm()->getName();
$dataArray['Organisationunit_id'] = $record->getOrganisationunit()->getId();
$dataArray['Form_id'] = $record->getForm()->getId();
$dataArray['Lastupdated'] = trim($record->getLastupdated()->format('Y-m-d H:i:s.u'));
$entityManager->getConnection()->insert($resourceTableName . '_temporary', $dataArray);
$logger->info('Inserted record instance ' . $dataArray['instance'] . ' for ' . $dataArray['Organisationunit_name'] . ' on form: ' . $record->getForm()->getName());
$totalInsertedRecords++;
unset($dataArray);
unset($dataValue);
unset($currentInstance);
}
}
unset($records);
$dataInsertionLap = $stopwatch->lap('resourceTableGeneration');
$dataInsertionDuration = round($dataInsertionLap->getDuration() / 1000, 2) - $schemaGenerationDuration;
$singleDataInsertionDuration = round($dataInsertionDuration / $totalInsertedRecords, 2);
if ($dataInsertionDuration < 60) {
$dataInsertionDurationMessage = round($dataInsertionDuration, 2) . ' sec.';
} elseif ($dataInsertionDuration >= 60 && $dataInsertionDuration < 3600) {
$dataInsertionDurationMessage = round($dataInsertionDuration / 60, 2) . ' min.';
} elseif ($dataInsertionDuration >= 3600 && $dataInsertionDuration < 216000) {
$dataInsertionDurationMessage = round($dataInsertionDuration / 3600, 2) . ' hrs';
} else {
$dataInsertionDurationMessage = round($dataInsertionDuration / 86400, 2) . ' days';
}
if ($singleDataInsertionDuration < 60) {
$singleDataInsertionDurationMessage = "(" . round($singleDataInsertionDuration, 2) . ' sec./record)';
} elseif ($singleDataInsertionDuration >= 60 && $singleDataInsertionDuration < 3600) {
$singleDataInsertionDurationMessage = "(" . round($singleDataInsertionDuration / 60, 2) . ' min./record)';
} elseif ($singleDataInsertionDuration >= 3600 && $singleDataInsertionDuration < 216000) {
$singleDataInsertionDurationMessage = "(" . round($singleDataInsertionDuration / 3600, 2) . ' hrs/record)';
} else {
$singleDataInsertionDurationMessage = "(" . round($singleDataInsertionDuration / 86400, 2) . ' days/record)';
}
$this->messagelog .= "Operation: " . $totalInsertedRecords . " Records Inserted into " . $resourceTableName . " in " . $dataInsertionDurationMessage . $singleDataInsertionDurationMessage . ".\n";
$logger->info($this->messagelog);
/*
* Replace existing resource table with completely regenerated temporary resource table
*/
// Drop table if it exists
if ($schemaManager->tablesExist($resourceTableName)) {
$schemaManager->dropTable($resourceTableName);
}
$schemaManager->renameTable($resourceTableName . '_temporary', $resourceTableName);
unset($schemaManager);
$stopwatch->lap('resourceTableGeneration');
$offlineDuration = round($dataInsertionLap->getDuration() / 1000, 2) - ($schemaGenerationDuration + $dataInsertionDuration);
if ($offlineDuration < 60) {
$offlineDurationMessage = round($offlineDuration, 2) . ' sec.';
} elseif ($offlineDuration >= 60 && $offlineDuration < 3600) {
$offlineDurationMessage = round($offlineDuration / 60, 2) . ' min.';
} elseif ($offlineDuration >= 3600 && $offlineDuration < 216000) {
$offlineDurationMessage = round($offlineDuration / 3600, 2) . ' hrs';
} else {
$offlineDurationMessage = round($offlineDuration / 86400, 2) . ' days';
}
$this->messagelog .= "Reports Offline Time: Resourcetable was offline for " . $offlineDurationMessage . "\n";
// Update last generated after running the script
$this->setLastgenerated(new \DateTime('now'));
$this->setIsgenerating(False);
$entityManager->persist($this);
try {
$entityManager->flush();
} catch (\Doctrine\Orm\NoResultException $e) {
$error = "Error Last generated";
}
/*
* Check Clock for time spent
*/
$resourceTableGenerationTime = $stopwatch->stop('resourceTableGeneration');
$duration = $resourceTableGenerationTime->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';
}
$this->messagelog .= "Operation: Resource Table generation completeted in " . $durationMessage . ".\n\n";
return True;
} else {
$this->messagelog .= "Status: Resource Table " . $resourceTableName . " is upto date.\n";
return False;
}
}
