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