/**
  * 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;
 }
Exemplo n.º 2
0
 /**
  * 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));
 }
Exemplo n.º 3
0
 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++;
     //}
 }
Exemplo n.º 4
0
 /**
  * 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())));
 }
Exemplo n.º 5
0
 /**
  * Remove record
  *
  * @param Record $record
  */
 public function removeRecord(Record $record)
 {
     $this->record->removeElement($record);
 }
Exemplo n.º 6
0
 /**
  * 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');
 }
Exemplo n.º 7
0
 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";
 }
Exemplo n.º 8
0
 /**
  * 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));
 }
Exemplo n.º 9
0
 /**
  * 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);
 }
Exemplo n.º 10
0
 /**
  * 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;
     }
 }