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";
}