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++; //} }
/** * 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'); }
/** * Creates a new Record entity. * * @Secure(roles="ROLE_SUPER_USER,ROLE_RECORD_CREATE") * @Route("/", name="record_create") * @Method("POST") * @Template("HrisRecordsBundle:Record:new.html.twig") */ public function createAction(Request $request) { $em = $this->getDoctrine()->getManager(); $entity = new Record(); //$record = $this->createForm(new RecordType(), $entity); //$record->bind($request); $message = ''; $formId = $this->getRequest()->get('formid'); $user = $this->container->get('security.context')->getToken()->getUser(); $onrgunitParent = $this->get('request')->request->get('orgunitParent'); $orunitUid = $this->get('request')->request->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(); $instance = ''; foreach ($uniqueFields as $key => $field_unique) { $instance .= $this->getRequest()->get($field_unique->getName()); if ($field_unique->getDataType()->getName() != "Date") { $message .= $this->getRequest()->get($field_unique->getName()) . " "; } } foreach ($fields as $key => $field) { $recordValue = $this->get('request')->request->get($field->getName()); 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. */ // 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()); $recordArray[$valueKey] = $recordValue; } $entity->setValue($recordArray); $entity->setForm($form); $entity->setInstance(md5($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(); try { $em->persist($entity); $em->flush(); $message .= "saved successfully"; $success = 'true'; } catch (DBALException $exception) { $record = $em->getRepository('HrisRecordsBundle:Record')->findOneBy(array('instance' => $entity->getInstance())); $message .= " is existing for " . $entity->getOrganisationunit()->getLongname(); $parent = $entity->getOrganisationunit()->getParent(); if (!empty($parent)) { $message .= " in " . $entity->getOrganisationunit()->getParent()->getLongname() . "!"; } $message .= ' <a href="' . $this->generateUrl('record_edit', array('id' => $record->getId(), 'message' => $message)) . '">Click here to edit existing record</a>'; $success = 'false'; } return $this->redirect($this->generateUrl('record_new', array('id' => $form->getId(), 'message' => $message, 'success' => $success))); }
public function load(ObjectManager $manager) { $stopwatch = new Stopwatch(); $stopwatch->start('dummyRecordGeneration'); // Populate dummy forms $this->addDummyFemaleNames(); $this->addDummyMaleNmes(); $this->addDummyCourseNames(); $this->addDummyCourseLocations(); $this->addDummySponsors(); $loadUserData = new LoadUserData(); $loadUserData->addDummyUsers(); $dummyUsers = $loadUserData->getUsers(); $loadFieldData = new LoadFieldData(); $loadFieldData->addDummyFields(); $dummyFields = $loadFieldData->getFields(); $loadFormData = new LoadFormData(); $loadFormData->addDummyForms(); $dummyForms = $loadFormData->getForms(); $organiastionunits = $manager->getRepository('HrisOrganisationunitBundle:Organisationunit')->findAll(); /* * Add data to facilities */ if (!empty($organiastionunits)) { foreach ($organiastionunits as $organiastionunitKey => $organisationunit) { /* * Assign data to dispensary, hospital and health centres only. */ if (preg_match('/dispensary|hospital|health centre|council/i', $organisationunit->getLongname())) { // Initiate record entering // Enter two records for each orgunit for ($recordIncr = 0; $recordIncr < $this->recordsPerOrganisationunit; $recordIncr++) { $record = new Record(); $record->setOrganisationunit($organisationunit); // Enter record for public and private form $formNames = array('Public Employee Form', 'Private Employee Form'); $formName = $formNames[array_rand($formNames, 1)]; if (empty($formName)) { $formName = 'Public Employee Form'; } $form = $manager->getRepository('HrisFormBundle:Form')->findOneBy(array('name' => $formName)); // Find history fields belonging to this form for population of data $queryBuilder = $manager->createQueryBuilder(); $historyFields = $queryBuilder->select('field')->from('HrisFormBundle:Field', 'field')->join('field.formFieldMember', 'formFieldMember')->join('formFieldMember.form', 'form')->where('form.id=:formId')->andWhere('field.hashistory=True')->setParameter('formId', $form->getId())->getQuery()->getResult(); $record->setForm($form); $record->setComplete(True); $record->setCorrect(True); $record->setHashistory(False); $record->setHastraining(False); $dummyUserKey = array_rand($dummyUsers, 1); $dummyUsername = $dummyUsers[$dummyUserKey]['username']; $record->setUsername($dummyUsername); // Constructing a Value Array // @todo removing hard-coding of HrisRecordBundle:Record values $value = array(); // Fetch all field members belonging to the form and add records // Roll a dice with gender of employee to pick name $genders = array('Male', 'Female'); $gender_picked = array_rand($genders, 1); $formFieldMembers = $manager->getRepository('HrisFormBundle:FormFieldMember')->findBy(array('form' => $form)); foreach ($formFieldMembers as $formFieldMemberKey => $formFieldMember) { /** * Made dynamic, on which field column is used as key, i.e. uid, name or id. */ // Translates to $formFieldMember->getField()->getUid() // or $formFieldMember->getField()->getUid() depending on value of $recordKeyName $recordKeyName = ucfirst(Record::getFieldKey()); $valueKey = call_user_func_array(array($formFieldMember->getField(), "get{$recordKeyName}"), array()); if ($formFieldMember->getField()->getName() == "Firstname" || $formFieldMember->getField()->getName() == "Middlename" || $formFieldMember->getField()->getName() == "Surname" || $formFieldMember->getField()->getName() == "NextofKin") { // Deal with names if ($gender_picked == "Female" && ($formFieldMember->getField()->getName() == "Firstname" || $formFieldMember->getField()->getName() == "NextofKin")) { $value[$valueKey] = $this->femaleNames[array_rand($this->femaleNames, 1)]; } else { $value[$valueKey] = $this->maleNames[array_rand($this->maleNames, 1)]; } if ($formFieldMember->getField()->getName() == "NextofKin") { $value[$valueKey] .= ' ' . $this->maleNames[array_rand($this->maleNames, 1)]; } //@todo remove hard-coding of instance // used later for instance formulation if ($formFieldMember->getField()->getName() == "Firstname") { $firstName = $value[$valueKey]; } if ($formFieldMember->getField()->getName() == "Middlename") { $middleName = $value[$valueKey]; } if ($formFieldMember->getField()->getName() == "Surname") { $surname = $value[$valueKey]; } } else { if ($formFieldMember->getField()->getInputType()->getName() == "Select") { // Deal with select /** * Made dynamic, on which field column is used as key, i.e. uid, name or id. */ // Translates to $fieldOptions[0]->getUid() // or $fieldOptions[0]->getValue() depending on value of $recordKeyName // $fieldOptionKey = ucfirst($record->getFieldOptionKey()); //$valueKey = call_user_func_array(array($fieldOptions[0], "get${fieldOptionKey}"),array()); $fieldOptionKey = ucfirst(Record::getFieldOptionKey()); $fieldOptions = $manager->getRepository('HrisFormBundle:FieldOption')->findBy(array('field' => $formFieldMember->getField())); // For case of gender choose match name with gender if ($formFieldMember->getField()->getName() == "Sex") { // Made FieldOption key to store in record value array dynamic. if ($fieldOptions[0]->getValue() == $gender_picked) { $value[$valueKey] = call_user_func_array(array($fieldOptions[0], "get{$fieldOptionKey}"), array()); } else { $value[$valueKey] = call_user_func_array(array($fieldOptions[1], "get{$fieldOptionKey}"), array()); } } else { // Made fieldOption key to store in record value array dynamic $value[$valueKey] = call_user_func_array(array($fieldOptions[array_rand($fieldOptions, 1)], "get{$fieldOptionKey}"), array()); } } else { if ($formFieldMember->getField()->getInputType()->getName() == "Date") { // Deal with dates // If birth date pick 20 - 55 date range // If employment data set it to birth date range+18 // If confirmation date, set it to employment date+1 // If promotion date, set it to confirmation+3 $beginDateStart = 50; $beginDateStop = 75; $endDateStart = 40; $endDateStop = 50; if ($formFieldMember->getField()->getName() == "DateOfBirth") { $beginDateStart = 50; $beginDateStop = 75; $endDateStart = 40; $endDateStop = 50; } elseif ($formFieldMember->getField()->getName() == "DateofFirstAppointment") { $beginDateStart -= 36; $beginDateStop -= 36; $endDateStart -= 36; $endDateStop -= 36; } elseif ($formFieldMember->getField()->getName() == "DateofConfirmation") { $beginDateStart -= 37; $beginDateStop -= 37; $endDateStart -= 37; $endDateStop -= 37; } elseif ($formFieldMember->getField()->getName() == "DateofLastPromotion") { $beginDateStart -= 40; $beginDateStop -= 40; $endDateStart -= 40; $endDateStop -= 40; } $value[$valueKey] = new \DateTime($this->getRandDate(array($beginDateStart, $beginDateStop), array($endDateStart, $endDateStop))); //@todo remove hard-coding of instance if ($formFieldMember->getField()->getName() == "DateOfBirth") { $dateOfBirth = $value[$valueKey]; } } else { if ($formFieldMember->getField()->getInputType()->getName() == "Text") { // Deal with numbers if ($formFieldMember->getField()->getName() == "NumberofChildrenDependants") { $value[$valueKey] = rand(0, 10); } elseif ($formFieldMember->getField()->getName() == "CheckNumber") { $value[$valueKey] = rand(9999999, 9999999999); } elseif ($formFieldMember->getField()->getName() == "EmployersFileNumber") { $value[$valueKey] = "FN/" . rand(100, 100000); } elseif ($formFieldMember->getField()->getName() == "RegistrationNumber") { $value[$valueKey] = "RB/" . rand(10, 10000); } elseif ($formFieldMember->getField()->getName() == "MonthlyBasicSalary") { $value[$valueKey] = rand(100, 1500) . '000'; } else { $value[$valueKey] = $this->maleNames[array_rand($this->maleNames, 1)] . " Street"; } } else { if ($formFieldMember->getField()->getInputType()->getName() == "TextArea") { // Deal with domicile, contact if ($formFieldMember->getField()->getName() == "ContactsofEmployee" || $formFieldMember->getField()->getName() == "ContactsofNextofKin") { $value[$valueKey] = "+255" . rand(6, 7) . rand(53, 69) . rand(01, 998) . rand(01, 998); } } } } } } } $instance = md5($firstName . $middleName . $surname . $dateOfBirth->format('Y-m-d')); $record->setInstance($instance); $record->setValue($value); //@todo check for uniqueness of instance and unique fields $recordReference = strtolower(str_replace(' ', '', $record->getInstance())) . '-record'; $this->addReference($recordReference, $record); $manager->persist($record); // Randomly on flip of a coin assign history & training data $outcomes = array(True, False); if ($outcomes[array_rand($outcomes, 1)]) { // Assign randomly between 2 to 4 histories per record $numberofHistoriesToAssign = array(1, 2); for ($incr = 0; $incr < $numberofHistoriesToAssign[array_rand($numberofHistoriesToAssign, 1)]; $incr++) { $history = new History(); $history->setRecord($record); $history->setUsername($record->getUsername()); //Calculate start date ranging starting form now-2yrs back and and stopping between 3-5 years back $beginDateStart = 3; $beginDateStop = 5; $endDateStart = 0; $endDateStop = 2; $startDate = new \DateTime($this->getRandDate(array($beginDateStart, $beginDateStop), array($endDateStart, $endDateStop))); $history->setStartdate($startDate); $historyField = $historyFields[array_rand($historyFields, 1)]; //echo get_class($historyField);exit; // If history field is Combo assign combo if text assign text if ($historyField->getInputType() == "Select") { $historyFieldOptions = $historyField->getFieldOption(); $historyFieldOptions = $historyFieldOptions->getValues(); $selectedHistoryOption = $historyFieldOptions[array_rand($historyFieldOptions, 1)]; $historyValue = $selectedHistoryOption->getValue(); } elseif ($historyField->getInputType() == "Date") { //Calculate start date ranging starting form 1-3yrs back and and stopping between 5-8 years back $beginDateStart = 5; $beginDateStop = 8; $endDateStart = 1; $endDateStop = 3; $historyDateObject = new \DateTime($this->getRandDate(array($beginDateStart, $beginDateStop), array($endDateStart, $endDateStop))); $historyValue = $historyDateObject->format('Y-m-d'); } else { // Deal with string history fields if ($historyField->getName() == "Firstname" || $historyField->getName() == "Middlename" || $historyField->getName() == "Surname" || $historyField->getName() == "NextofKin") { // Deal with names if ($gender_picked == "Female" && ($historyField->getName() == "Firstname" || $historyField->getName() == "NextofKin")) { $historyValue = $this->femaleNames[array_rand($this->femaleNames, 1)]; } else { $historyValue = $this->maleNames[array_rand($this->maleNames, 1)]; } if ($historyField->getName() == "NextofKin") { $historyValue .= ' ' . $this->maleNames[array_rand($this->maleNames, 1)]; } } else { if ($historyField->getInputType()->getName() == "Text") { // Deal with numbers if ($historyField->getName() == "NumberofChildrenDependants") { $historyValue = rand(0, 10); } elseif ($historyField->getName() == "CheckNumber") { $historyValue = rand(9999999, 9999999999); } elseif ($historyField->getName() == "EmployersFileNumber") { $historyValue = "FN/" . rand(100, 100000); } elseif ($historyField->getName() == "RegistrationNumber") { $historyValue = "RB/" . rand(10, 10000); } elseif ($historyField->getName() == "MonthlyBasicSalary") { $historyValue = rand(100, 1500) . '000'; } else { $historyValue = $this->maleNames[array_rand($this->maleNames, 1)] . " Street"; } } else { if ($historyField->getInputType()->getName() == "TextArea") { // Deal with domicile, contact if ($historyField->getName() == "ContactsofEmployee" || $historyField->getName() == "ContactsofNextofKin") { $historyValue = "+255" . rand(6, 7) . rand(53, 69) . rand(01, 998) . rand(01, 998); } } } } } $reason = $historyField->getCaption() . " changed."; $history->setField($historyField); $history->setHistory($historyValue); $history->setReason($reason); $manager->persist($history); unset($history); } $record->setHashistory(True); $manager->persist($record); } if ($outcomes[array_rand($outcomes, 1)]) { // Assign randomly between 2 to 4 trainings per record $numberofTrainingsToAssign = array(1, 2); for ($incr = 0; $incr < $numberofTrainingsToAssign[array_rand($numberofTrainingsToAssign, 1)]; $incr++) { $training = new Training(); $training->setRecord($record); $training->setCoursename($this->courseNames[array_rand($this->courseNames, 1)]); $training->setCourselocation($this->courseLocations[array_rand($this->courseLocations, 1)]); $training->setSponsor($this->sponsor[array_rand($this->sponsor, 1)]); //Calculate start date ranging starting form 9-10yrs back and and stopping between 10-12 years back $beginDateStart = 10; $beginDateStop = 12; $endDateStart = 9; $endDateStop = 10; $startDate = new \DateTime($this->getRandDate(array($beginDateStart, $beginDateStop), array($endDateStart, $endDateStop))); //Calculate end date ranging starting form 11-13yrs back and and stopping between 13-15 years back $beginDateStart = 13; $beginDateStop = 15; $endDateStart = 11; $endDateStop = 13; $endDate = new \DateTime($this->getRandDate(array($beginDateStart, $beginDateStop), array($endDateStart, $endDateStop))); $training->setStartdate($startDate); $training->setEnddate($endDate); $training->setUsername($record->getUsername()); $manager->persist($training); unset($training); } $record->setHastraining(True); $manager->persist($record); } unset($record); } } } } $manager->flush(); /* * Check Clock for time spent */ $dummyRecordGenerationTime = $stopwatch->stop('dummyRecordGeneration'); $duration = $dummyRecordGenerationTime->getDuration() / 1000; unset($stopwatch); if ($duration < 60) { $durationMessage = round($duration, 2) . ' seconds'; } elseif ($duration >= 60 && $duration < 3600) { $durationMessage = round($duration / 60, 2) . ' minutes'; } elseif ($duration >= 3600 && $duration < 216000) { $durationMessage = round($duration / 3600, 2) . ' hours'; } else { $durationMessage = round($duration / 86400, 2) . ' hours'; } echo "\tDummy Records generation complete in " . $durationMessage . ".\n\n"; }