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