/**
* Finds and displays a Record entity.
*
* @Secure(roles="ROLE_SUPER_USER,ROLE_RECORD_SHOW")
* @Route("/{id}", requirements={"id"="\d+"}, name="record_show")
* @Method("GET")
* @Template()
*/
public function showAction($id)
{
$em = $this->getDoctrine()->getManager();
$entity = $em->getRepository('HrisRecordsBundle:Record')->find($id);
if (!$entity) {
throw $this->createNotFoundException('Unable to find Record entity.');
}
//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();
}
$deleteForm = $this->createDeleteForm($id);
return array('entity' => $entity, 'delete_form' => $deleteForm->createView(), 'optionMap' => $fieldOptionMap);
}
/**
* Lists all Records by forms.
*
* @Secure(roles="ROLE_SUPER_USER,ROLE_RECORD_LIST")
* @Route("/viewrecords/", name="record_viewparticipants")
* @Method("GET")
* @Template()
*/
public function participantsForviewOnlyAction(Request $request)
{
/// querying instance record table to see existing record instance pairs//
$instance_id = $request->query->get('instance_id');
// query instance id from route url
$id = $request->query->get('id');
// query instance id from route url
$em = $this->getDoctrine()->getManager();
$queryBuilder = $this->getDoctrine()->getManager()->createQueryBuilder();
$userManager = $this->get('fos_user.user_manager');
$user = $userManager->findUserByUsername($this->getUser());
$organisationunit = $user->getOrganisationunit();
// if($formid == 0) {
$formIds = $this->getDoctrine()->getManager()->createQueryBuilder()->select('form.id')->from('HrisFormBundle:Form', 'form')->getQuery()->getArrayResult();
// $formIds = $this->array_value_recursive('id',$formIds);
$forms = $em->getRepository('HrisFormBundle:Form')->findAll();
// }else {
// $forms = $em->getRepository('HrisFormBundle:Form')->findby(array('id'=>$formid));
// $formIds[]=$formid;
// }
//Prepare field Option map, converting from stored FieldOption key in record value array to actual text value
$fieldOptions = $this->getDoctrine()->getManager()->getRepository('HrisFormBundle:FieldOption')->findAll();
foreach ($fieldOptions as $fieldOptionKey => $fieldOption) {
$recordFieldOptionKey = ucfirst(Record::getFieldOptionKey());
$fieldOptionMap[call_user_func_array(array($fieldOption, "get{$recordFieldOptionKey}"), array())] = $fieldOption->getValue();
}
//If user's organisationunit is data entry level pull only records of his organisationunit
//else pull lower children too.
$records = $queryBuilder->select('record')->from('HrisRecordsBundle:Record', 'record')->join('record.organisationunit', 'organisationunit')->join('record.form', 'form');
if ($organisationunit->getOrganisationunitStructure()->getLevel()->getDataentrylevel()) {
$records = $records->join('organisationunit.organisationunitStructure', 'organisationunitStructure')->join('organisationunitStructure.level', 'organisationunitLevel')->andWhere('organisationunitLevel.level >= (
SELECT selectedOrganisationunitLevel.level
FROM HrisOrganisationunitBundle:OrganisationunitStructure selectedOrganisationunitStructure
INNER JOIN selectedOrganisationunitStructure.level selectedOrganisationunitLevel
WHERE selectedOrganisationunitStructure.organisationunit=:selectedOrganisationunit )')->andWhere('organisationunitStructure.level' . $organisationunit->getOrganisationunitStructure()->getLevel()->getLevel() . 'Organisationunit=:levelId');
$parameters = array('levelId' => $organisationunit->getId(), 'selectedOrganisationunit' => $organisationunit->getId(), 'formIds' => $formIds);
} else {
$records = $records->andWhere('organisationunit.id=:selectedOrganisationunit');
$parameters = array('selectedOrganisationunit' => $organisationunit->getId(), 'formIds' => $formIds);
}
$records = $records->andWhere($queryBuilder->expr()->in('form.id', ':formIds'))->setParameters($parameters)->getQuery()->getResult();
$formNames = NULL;
$visibleFields = array();
$formFields = array();
$incr = 0;
$formIds = array();
foreach ($forms as $formKey => $form) {
$incr++;
$formIds[] = $form->getId();
// Concatenate form Names
if (empty($formNames)) {
$formNames = $form->getTitle();
} else {
if (count($formNames) == $incr) {
$formNames .= ',' . $form->getTitle();
}
}
// Accrue visible fields
foreach ($form->getFormVisibleFields() as $visibleFieldKey => $visibleField) {
if (!in_array($visibleField->getField(), $visibleFields)) {
$visibleFields[] = $visibleField->getField();
}
}
// Accrue form fields
foreach ($form->getFormFieldMember() as $formFieldKey => $formField) {
if (!in_array($formField->getField(), $formFields)) {
$formFields[] = $formField->getField();
}
}
}
$title_query = "select * from hris_trainings T,hris_traininginstance I where T.id = I.training_id and I.id=" . $instance_id;
$trainings = $em->getConnection()->executeQuery($title_query)->fetchAll();
$training = array(0 => '');
foreach ($trainings as $trains) {
$training['coursename'] = $trains['coursename'];
$training['district'] = $trains['district'];
$training['startdate'] = $trains['startdate'];
$training['venue'] = $trains['venue'];
}
$startdate = strtotime($training['startdate']);
$startdate = date('d-m-Y', $startdate);
$title = "Participants for " . $training['coursename'] . " of " . $training['district'] . " of date " . $startdate . " To Employee Records for " . $organisationunit->getLongname();
$title .= " for " . $formNames;
if (empty($visibleFields)) {
$visibleFields = $formFields;
}
//getting all User Forms for User Migration
$user = $this->container->get('security.context')->getToken()->getUser();
$userForms = $user->getForm();
$instance = new instanceRecord();
$instanceRecordForm = $this->createForm(new instanceRecordType($this->getUser()), $instance, array('method' => 'POST'));
$instanceRecordForm = $instanceRecordForm->createView();
$query = "SELECT record_id FROM hris_instance_records WHERE instance_id =" . $instance_id;
$record_ids = $em->getConnection()->executeQuery($query)->fetchAll();
$id_record = array(0 => -1);
foreach ($record_ids as $records_id) {
$id_record[] = $records_id['record_id'];
}
return $this->render('HrisTrainingBundle:Participant:participantsForviewOnly.html.twig', array('title' => $title, 'visibleFields' => $visibleFields, 'formFields' => $formFields, 'records' => $records, 'optionMap' => $fieldOptionMap, 'userForms' => $userForms, 'formid' => "", 'record_ids' => $id_record, 'instanceRecordForm' => $instanceRecordForm));
}
public function processCompletenessFigures()
{
/*
* Filter out organisationunit by selected parent and desired level
*/
$selectedParentStructure = $this->getDoctrine()->getManager()->getRepository('HrisOrganisationunitBundle:OrganisationunitStructure')->findOneBy(array('organisationunit' => $this->organisationunit));
$queryBuilder = $this->getDoctrine()->getManager()->createQueryBuilder();
if (!empty($sameLevel)) {
$this->sameLevel = True;
}
$em = $this->getDoctrine()->getManager();
$this->organisationunit = $em->getRepository('HrisOrganisationunitBundle:Organisationunit')->findOneBy(array('id' => $this->organisationunit));
$lowestLevel = $queryBuilder->select('MAX(organisationunitLevel.level)')->from('HrisOrganisationunitBundle:OrganisationunitLevel', 'organisationunitLevel')->getQuery()->getSingleScalarResult();
$levelBelowSelected = $this->organisationunit->getOrganisationunitStructure()->getLevel()->getLevel() + 1;
if ($levelBelowSelected > $lowestLevel) {
$this->organisationunitLevel = $this->organisationunit->getOrganisationunitStructure()->getLevel();
$this->sameLevel = True;
} elseif (empty($this->organisationunitLevel)) {
// Default to level immediately below selected orgunit if no organisationunit level was selected
$this->organisationunitLevel = $this->getDoctrine()->getManager()->getRepository('HrisOrganisationunitBundle:OrganisationunitLevel')->findOneBy(array('level' => $this->organisationunit->getOrganisationunitStructure()->getLevel()->getLevel() + 1));
}
// Fetching higher level headings[level<= levelPrefereed] excluding highest level
$queryBuilder = $this->getDoctrine()->getManager()->createQueryBuilder();
$this->lowerLevels = $queryBuilder->select('DISTINCT(organisationunitLevel.level),organisationunitLevel.name')->from('HrisOrganisationunitBundle:OrganisationunitLevel', 'organisationunitLevel')->where($queryBuilder->expr()->lt('organisationunitLevel.level', ':lowerLevel'))->andWhere($queryBuilder->expr()->gt('organisationunitLevel.level', ':selectedLevel'))->setParameters(array('lowerLevel' => $this->organisationunitLevel->getLevel(), 'selectedLevel' => $selectedParentStructure->getLevel()->getLevel()))->orderBy('organisationunitLevel.level', 'DESC')->getQuery()->getResult();
// Create FormIds
$formIds = NULL;
foreach ($this->forms as $formKey => $formObject) {
if (empty($formIds)) {
$formIds = $formObject->getId();
} else {
$formIds .= ',' . $formObject->getId();
}
}
$queryBuilder = $this->getDoctrine()->getManager()->createQueryBuilder();
// Determine lowest level in organisationunit structure
$lowestOrganisationunitLevel = $this->array_value_recursive('maxLevel', $this->getDoctrine()->getManager()->createQuery('SELECT MAX(organisationunitLevel.level) as maxLevel FROM HrisOrganisationunitBundle:OrganisationunitLevel organisationunitLevel')->getResult());
// Determine organisation unit(childrens) to display
if (!empty($this->organisationunitLevel) && $this->organisationunitLevel->getLevel() > $this->organisationunit->getOrganisationunitStructure()->getLevel()->getLevel()) {
// Display children for the given level is passed, provided level is below the parent organisationunit
if (!isset($this->sameLevel)) {
$this->title = "Completeness Report for All " . $this->organisationunitLevel->getName() . " Under " . $this->organisationunit->getLongname();
// Create title
} else {
$this->title = "Completeness Report for Employees directly under " . $this->organisationunit->getLongname();
// Create title
}
$this->organisationunitChildren = $queryBuilder->select('organisationunit')->from('HrisOrganisationunitBundle:Organisationunit', 'organisationunit')->innerJoin('organisationunit.organisationunitStructure', 'organisationunitStructure')->where('organisationunitStructure.level=:organisationunitLevel')->andWhere('organisationunitStructure.level' . $this->organisationunit->getOrganisationunitStructure()->getLevel()->getLevel() . 'Organisationunit=:levelOrganisationunit')->andWhere('organisationunit.active=True')->setParameters(array('organisationunitLevel' => $this->organisationunitLevel, 'levelOrganisationunit' => $this->organisationunit))->getQuery()->getResult();
} else {
// Display children for lower level of the selected organisationunit
$lowerOrganisationunitCount = $this->getDoctrine()->getManager()->createQuery("SELECT COUNT(lowerOrganisationunit.id)\n FROM HrisOrganisationunitBundle:Organisationunit lowerOrganisationunit\n INNER JOIN lowerOrganisationunit.parent parentOrganisationunit\n WHERE lowerOrganisationunit.active=True AND parentOrganisationunit.id=" . $this->organisationunit->getId())->getSingleScalarResult();
if ($this->organisationunit->getOrganisationunitStructure()->getLevel()->getLevel() !== $lowestOrganisationunitLevel && !empty($lowerOrganisationunitCount)) {
// Deal with organisationunit with children
$this->organisationunitLevel = $this->getDoctrine()->getManager()->getRepository('HrisOrganisationunitBundle:OrganisationunitLevel')->findOneBy(array('level' => $this->organisationunit->getOrganisationunitStructure()->getLevel()->getLevel() + 1));
$levelName = $this->organisationunitLevel->getName() . " Under ";
} else {
$levelName = NULL;
}
$this->organisationunitChildren = $this->getDoctrine()->getRepository('HrisOrganisationunitBundle:Organisationunit')->getImmediateChildren($this->organisationunit);
if (!isset($this->sameLevel)) {
$this->title = "Completeness Report for " . $levelName . $this->organisationunit->getLongname();
// Create title
} else {
$this->title = "Completeness Report for Employees directly under " . $this->organisationunit->getLongname();
// Create title
}
}
$this->rootNodeOrganisationunit = $this->organisationunit;
// Establish the root node
$this->parent = NULL;
//$userObject = $this->getDoctrine()->getManager()->getRepository('User')->findOneBy(array('username' => $user->getUsername()));
//check to make sure you can not go beyond your assigned level is now done at presentation layer.
$this->parent = $this->organisationunit->getParent();
// Query for Options to exclude from reports
$fieldOptionsToSkip = $this->getDoctrine()->getManager()->getRepository('HrisFormBundle:FieldOption')->findBy(array('skipInReport' => True));
$maskIncr = 1;
$maskParameters = NULL;
$whereExpression = NULL;
if (!empty($fieldOptionsToSkip)) {
foreach ($fieldOptionsToSkip as $key => $fieldOptionToSkip) {
/**
* 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($fieldOptionToSkip->getField(), "get{$recordFieldKey}"), array());
$recordFieldOptionKey = ucfirst(Record::getFieldOptionKey());
// Translates to $fieldOptionToskip->getUid() assuming Record::getFieldOptionKey returns "uid"
$valuePattern[$valueKey] = call_user_func_array(array($fieldOptionToSkip, "get{$recordFieldOptionKey}"), array());
$jsonPattern = json_encode($valuePattern);
$subJsonpatern = str_replace("{", "", $jsonPattern);
$subJsonpatern = str_replace("}", "", $subJsonpatern);
if ($whereExpression == NULL) {
$whereExpression = ' record.value NOT LIKE ?' . $maskIncr;
} else {
$whereExpression .= ' AND record.value NOT LIKE ?' . $maskIncr;
}
$maskParameters[$maskIncr] = '%' . $subJsonpatern . '%';
$maskIncr++;
}
if ($whereExpression == NULL) {
$whereExpression = ' record.value LIKE ?' . $maskIncr;
} else {
$whereExpression .= ' AND record.value LIKE ?' . $maskIncr;
}
// Translates to $field->getUid()
// or $field->getUid() depending on value of $recordKeyName
$recordFieldKey = ucfirst(Record::getFieldKey());
$valueKey = call_user_func_array(array($fieldOptionToSkip->getField(), "get{$recordFieldKey}"), array());
$maskParameters[$maskIncr] = '%"' . $valueKey . '":%';
}
if (isset($this->organisationunitChildren) && sizeof($this->organisationunitChildren) > 0 && !$this->sameLevel) {
// user choose district and above show total records in the lower facilities
$this->completenessMatrix = NULL;
$this->expectedCompleteness = NULL;
$this->totalExpectedCompleteness = NULL;
$this->totalCompletenessMatrix = NULL;
foreach ($this->organisationunitChildren as $key => $childOrganisationunit) {
/*
* Construct completeness matrix
* @Note: $this->completenessMatrix[organisationunitId][formId] //Holds particular value
* @Note: $this->totalCompletenessMatrix[formId] // Holds total for all records of the organisationunits
* @Note: $childrenNames[organisationunitId] hold names of OrganisationUnits in completeness matrix
* @Note: $this->expectedCompleteness[organisationunitId][formId]
*/
foreach ($this->forms as $key => $form) {
$queryBuilder = $this->getDoctrine()->getManager()->createQueryBuilder();
if ($childOrganisationunit->getOrganisationunitStructure()->getLevel()->getLevel() !== $lowestOrganisationunitLevel) {
// Calculation for levels above the lowest ( Sum for only records below the selectedLevel)
$aliasParameters = $maskParameters;
$aliasParameters['levelId'] = $childOrganisationunit->getId();
$valuecount = $queryBuilder->select('COUNT(record.instance) as employeeCount ')->from('HrisRecordsBundle:Record', 'record')->join('record.organisationunit', 'organisationunit')->join('record.form', 'form')->join('organisationunit.organisationunitStructure', 'organisationunitStructure')->join('organisationunitStructure.level', 'level')->andWhere('
(
(
level.level >= :organisationunitLevel
AND organisationunitStructure.level' . $childOrganisationunit->getOrganisationunitStructure()->getLevel()->getLevel() . 'Organisationunit=:levelId
)
OR organisationunit.id=:organisationunitId
)')->andWhere($queryBuilder->expr()->in('form.id', $form->getId()))->andWhere('organisationunit.active=True')->andWhere($whereExpression);
// Append in query, field options to exclude
$aliasParameters['organisationunitLevel'] = $childOrganisationunit->getOrganisationunitStructure()->getLevel()->getLevel();
$aliasParameters['organisationunitId'] = $childOrganisationunit->getId();
$valuecount = $valuecount->setParameters($aliasParameters)->getQuery()->getResult();
$queryBuilder = $this->getDoctrine()->getManager()->createQueryBuilder();
$expectations = $queryBuilder->select('SUM(organisationunitCompleteness.expectation) as expectation')->from('HrisOrganisationunitBundle:Organisationunit', 'organisationunit')->join('organisationunit.organisationunitCompleteness', 'organisationunitCompleteness')->join('organisationunitCompleteness.form', 'form')->join('organisationunit.organisationunitStructure', 'organisationunitStructure')->join('organisationunitStructure.level', 'level')->andWhere('(
( level.level >= :organisationunitLevel AND organisationunitStructure.level' . $childOrganisationunit->getOrganisationunitStructure()->getLevel()->getLevel() . 'Organisationunit=:levelId )
OR organisationunit.id=:organisationunitid
)')->andWhere($queryBuilder->expr()->in('form.id', $form->getId()))->andWhere('organisationunit.active=True')->setParameters(array('levelId' => $childOrganisationunit->getId(), 'organisationunitLevel' => $childOrganisationunit->getOrganisationunitStructure()->getLevel()->getLevel(), 'organisationunitid' => $childOrganisationunit->getId()))->getQuery()->getResult();
$this->completenessMatrix[$childOrganisationunit->getId()][$form->getId()] = $this->array_value_recursive('employeeCount', $valuecount);
$this->expectedCompleteness[$childOrganisationunit->getId()][$form->getId()] = $this->array_value_recursive('expectation', $expectations);
if (isset($this->totalCompletenessMatrix[$form->getId()])) {
$this->totalCompletenessMatrix[$form->getId()] += $this->completenessMatrix[$childOrganisationunit->getId()][$form->getId()];
} else {
$this->totalCompletenessMatrix[$form->getId()] = $this->completenessMatrix[$childOrganisationunit->getId()][$form->getId()];
}
if (isset($this->totalExpectedCompleteness[$form->getId()])) {
$this->totalExpectedCompleteness[$form->getId()] += $this->expectedCompleteness[$childOrganisationunit->getId()][$form->getId()];
} else {
$this->totalExpectedCompleteness[$form->getId()] = $this->expectedCompleteness[$childOrganisationunit->getId()][$form->getId()];
}
$childrenNames[$childOrganisationunit->getId()] = $childOrganisationunit->getLongname();
} else {
// Calculation for the lowest level ( Sum for records of that particular organisationunits )
$aliasParameters = $maskParameters;
$aliasParameters['organisationunitId'] = $childOrganisationunit->getId();
$valuecount = $queryBuilder->select('COUNT(record.instance) as employeeCount ')->from('HrisRecordsBundle:Record', 'record')->join('record.organisationunit', 'organisationunit')->join('record.form', 'form')->andWhere($queryBuilder->expr()->in('form.id', $form->getId()))->andWhere('organisationunit.id=:organisationunitId')->andWhere('organisationunit.active=True')->andWhere($whereExpression)->setParameters($aliasParameters)->getQuery()->getResult();
$queryBuilder = $this->getDoctrine()->getManager()->createQueryBuilder();
// Sum of expectation records
$expectations = $queryBuilder->select('SUM(organisationunitCompleteness.expectation) as expectation')->from('HrisOrganisationunitBundle:Organisationunit', 'organisationunit')->join('organisationunit.organisationunitCompleteness', 'organisationunitCompleteness')->join('organisationunitCompleteness.form', 'form')->join('organisationunit.organisationunitStructure', 'organisationunitStructure')->andWhere($queryBuilder->expr()->in('form.id', $form->getId()))->andWhere('organisationunit.id=:organisationunitId')->andWhere('organisationunit.active=True')->setParameters(array('organisationunitId' => $childOrganisationunit->getId()))->getQuery()->getResult();
$this->completenessMatrix[$childOrganisationunit->getId()][$form->getId()] = $this->array_value_recursive('employeeCount', $valuecount);
$this->expectedCompleteness[$childOrganisationunit->getId()][$form->getId()] = $this->array_value_recursive('expectation', $expectations);
if (isset($this->totalCompletenessMatrix[$form->getId()])) {
$this->totalCompletenessMatrix[$form->getId()] += $this->completenessMatrix[$childOrganisationunit->getId()][$form->getId()];
} else {
$this->totalCompletenessMatrix[$form->getId()] = $this->completenessMatrix[$childOrganisationunit->getId()][$form->getId()];
}
if (isset($this->totalExpectedCompleteness[$form->getId()])) {
$this->totalExpectedCompleteness[$form->getId()] += $this->expectedCompleteness[$childOrganisationunit->getId()][$form->getId()];
} else {
$this->totalExpectedCompleteness[$form->getId()] = $this->expectedCompleteness[$childOrganisationunit->getId()][$form->getId()];
}
$childrenNames[$childOrganisationunit->getId()] = $childOrganisationunit->getLongname();
}
}
}
// Completeness for the root node organisationunit
foreach ($this->forms as $key => $form) {
$queryBuilder = $this->getDoctrine()->getManager()->createQueryBuilder();
$aliasParameters = $maskParameters;
$aliasParameters['organisationunitId'] = $this->rootNodeOrganisationunit->getId();
$valuecount = $queryBuilder->select('COUNT(record.instance) as employeeCount ')->from('HrisRecordsBundle:Record', 'record')->join('record.organisationunit', 'organisationunit')->join('record.form', 'form')->andWhere($queryBuilder->expr()->in('form.id', $form->getId()))->andWhere('organisationunit.id=:organisationunitId')->andWhere('organisationunit.active=True')->andWhere($whereExpression)->setParameters($aliasParameters)->getQuery()->getResult();
$queryBuilder = $this->getDoctrine()->getManager()->createQueryBuilder();
$expectations = $queryBuilder->select('SUM(organisationunitCompleteness.expectation) as expectation')->from('HrisOrganisationunitBundle:Organisationunit', 'organisationunit')->join('organisationunit.organisationunitCompleteness', 'organisationunitCompleteness')->join('organisationunitCompleteness.form', 'form')->join('organisationunit.organisationunitStructure', 'organisationunitStructure')->andWhere($queryBuilder->expr()->in('form.id', $form->getId()))->andWhere('organisationunit.id=:organisationunitId')->andWhere('organisationunit.active=True')->setParameters(array('organisationunitId' => $this->rootNodeOrganisationunit->getId()))->getQuery()->getResult();
$this->completenessMatrix[$this->rootNodeOrganisationunit->getId()][$form->getId()] = $this->array_value_recursive('employeeCount', $valuecount);
$this->expectedCompleteness[$this->rootNodeOrganisationunit->getId()][$form->getId()] = $this->array_value_recursive('expectation', $expectations);
// Summation of total completeness for selected forms
if (isset($this->totalCompletenessMatrix[$form->getId()])) {
$this->totalCompletenessMatrix[$form->getId()] += $this->completenessMatrix[$this->rootNodeOrganisationunit->getId()][$form->getId()];
} else {
$this->totalCompletenessMatrix[$form->getId()] = $this->completenessMatrix[$this->rootNodeOrganisationunit->getId()][$form->getId()];
}
// Summation of total expected completeness for sselected forms
if (isset($this->totalExpectedCompleteness[$form->getId()])) {
$this->totalExpectedCompleteness[$form->getId()] += $this->expectedCompleteness[$this->rootNodeOrganisationunit->getId()][$form->getId()];
} else {
$this->totalExpectedCompleteness[$form->getId()] = $this->expectedCompleteness[$this->rootNodeOrganisationunit->getId()][$form->getId()];
}
$childrenNames[$this->rootNodeOrganisationunit->getId()] = $this->rootNodeOrganisationunit->getLongname();
}
// Account for displaying of individual records
if (empty($this->organisationunitChildren) || isset($this->sameLevel)) {
// When organisationunit has no children display records(Only root node found)
$queryBuilder = $this->getDoctrine()->getManager()->createQueryBuilder();
// Gather Visible Fields from all passed Forms
if (!empty($this->forms)) {
$visibleFieldIds = $queryBuilder->select('DISTINCT(field),formVisibleFields.sort')->from('HrisFormBundle:FormVisibleFields', 'formVisibleFields')->innerJoin('formVisibleFields.field', 'field')->innerJoin('formVisibleFields.form', 'form')->where($queryBuilder->expr()->in('form.id', $formIds))->orderBy('formVisibleFields.sort', 'ASC')->getQuery()->getResult();
foreach ($visibleFieldIds as $visibleKey => $visibleFieldId) {
$this->visibleFields[] = $this->getDoctrine()->getManager()->createQueryBuilder()->select('afield')->from('HrisFormBundle:Field', 'afield')->where($queryBuilder->expr()->in('afield.id', $visibleFieldId['id']))->getQuery()->getSingleResult();
$this->getDoctrine()->getManager()->flush();
}
} else {
/*
* Make all fields visible
*/
$this->visibleFields = $queryBuilder->select('field')->from('HrisFormBundle:Field', 'field')->orderBy('field.name', 'ASC')->getQuery()->getResult();
}
//Preparing array of Combination Categories
$fieldOption = $this->getDoctrine()->getManager()->getRepository('HrisFormBundle:FieldOption')->findAll();
$counter = 0;
// For Organisation Units without childrens
$aliasParameters = $maskParameters;
$aliasParameters['organisationunitId'] = $this->organisationunit->getId();
$queryBuilder = $this->getDoctrine()->getManager()->createQueryBuilder();
$records = $queryBuilder->select('record')->from('HrisRecordsBundle:Record', 'record')->join('record.organisationunit', 'organisationunit')->join('record.form', 'form')->andWhere($queryBuilder->expr()->in('form.id', $formIds))->andWhere('organisationunit.id=:organisationunitId')->andWhere('organisationunit.active=True')->andWhere($whereExpression)->setParameters($aliasParameters)->getQuery()->getResult();
foreach ($fieldOption as $key => $optionObject) {
// Translates to $optionObject->getUid()
// or $optionObject->getUid() depending on value of $recordKeyName
$recordFieldOptionKey = ucfirst(Record::getFieldOptionKey());
$fieldOptionKey = call_user_func_array(array($optionObject, "get{$recordFieldOptionKey}"), array());
$option[$fieldOptionKey] = $optionObject->getValue();
}
foreach ($records as $key => $dataValueInstance) {
foreach ($this->visibleFields as $key => $visibleField) {
/**
* Made dynamic, on which field column is used as key, i.e. uid, name or id.
*/
// Translates to $field->getUid()
// or $visibleField->getUid() depending on value of $recordKeyName
$recordFieldKey = ucfirst(Record::getFieldKey());
$valueKey = call_user_func_array(array($visibleField, "get{$recordFieldKey}"), array());
$dataValue = $dataValueInstance->getValue();
if ($visibleField->getInputType()->getName() == 'combo') {
if (isset($option[$dataValue[$valueKey]])) {
$displayValue = $option[$dataValue[$valueKey]];
} else {
$displayValue = '';
}
} else {
if ($visibleField->getInputType()->getName() == 'date') {
if (!empty($dataValue[$visibleField->getId()])) {
$dataValue[$valueKey] = new DateTime($dataValue[$visibleField->getId()]['date'], new DateTimeZone($dataValue[$valueKey]['timezone']));
$displayValue = $dataValue[$valueKey];
$displayValue = $displayValue->format('d/m/Y');
}
} else {
if (isset($dataValue[$valueKey])) {
$displayValue = $dataValue[$valueKey];
} else {
$displayValue = '';
}
}
}
}
}
}
} else {
// Organsiationunit without children
$counter = 0;
// For Organisation Units without childrens
$aliasParameters = $maskParameters;
$aliasParameters['organisationunitId'] = $this->organisationunit->getId();
$queryBuilder = $this->getDoctrine()->getManager()->createQueryBuilder();
$records = $queryBuilder->select('record')->from('HrisRecordsBundle:Record', 'record')->join('record.organisationunit', 'organisationunit')->join('record.form', 'form')->andWhere($queryBuilder->expr()->in('form.id', $formIds))->andWhere('organisationunit.id=:organisationunitId')->andWhere('organisationunit.active=True')->andWhere($whereExpression)->setParameters($aliasParameters)->getQuery()->getResult();
//Preparing array of Combination Categories
$fieldOption = $this->getDoctrine()->getManager()->getRepository('HrisFormBundle:FieldOption')->findAll();
foreach ($fieldOption as $key => $optionObject) {
// Translates to $optionObject->getUid()
// or $optionObject->getUid() depending on value of $recordKeyName
$recordFieldOptionKey = ucfirst(Record::getFieldOptionKey());
$fieldOptionKey = call_user_func_array(array($optionObject, "get{$recordFieldOptionKey}"), array());
$option[$fieldOptionKey] = $optionObject->getValue();
}
// Gather Visible Fields from all passed Forms
if (!empty($this->forms)) {
$queryBuilder = $this->getDoctrine()->getManager()->createQueryBuilder();
$visibleFieldIds = $queryBuilder->select('DISTINCT(field),formVisibleFields.sort')->from('HrisFormBundle:FormVisibleFields', 'formVisibleFields')->innerJoin('formVisibleFields.field', 'field')->innerJoin('formVisibleFields.form', 'form')->where($queryBuilder->expr()->in('form.id', $formIds))->orderBy('formVisibleFields.sort', 'ASC')->getQuery()->getResult();
foreach ($visibleFieldIds as $visibleKey => $visibleFieldId) {
$this->visibleFields[] = $this->getDoctrine()->getManager()->createQueryBuilder()->select('afield')->from('HrisFormBundle:Field', 'afield')->where($queryBuilder->expr()->in('afield.id', $visibleFieldId['id']))->getQuery()->getSingleResult();
$this->getDoctrine()->getManager()->flush();
}
if (empty($visibleFieldIds)) {
$queryBuilder = $this->getDoctrine()->getManager()->createQueryBuilder();
$this->visibleFields = $queryBuilder->select('field')->from('HrisFormBundle:Field', 'field')->where('field.isCalculated=False')->orderBy('field.uid', 'ASC')->getQuery()->getResult();
}
} else {
/*
* Make all fields visible
*/
$this->visibleFields = $queryBuilder->select('field')->from('HrisFormBundle:Field', 'field')->orderBy('field.name', 'ASC')->getQuery()->getResult();
}
// Initiate recordsToDisplay array
$this->recordsToDisplay = NULL;
$this->recordInstances = NULL;
foreach ($records as $key => $dataValueInstance) {
$dataValue = $dataValueInstance->getValue();
$this->recordInstances[] = $dataValueInstance->getInstance();
foreach ($this->visibleFields as $key => $visibleField) {
// Translates to $field->getUid()
// or $visibleField->getUid() depending on value of $recordKeyName
$recordFieldKey = ucfirst(Record::getFieldKey());
$valueKey = call_user_func_array(array($visibleField, "get{$recordFieldKey}"), array());
if ($visibleField->getInputType()->getName() == 'Select') {
if (isset($dataValue[$valueKey]) && isset($option[$dataValue[$valueKey]])) {
$displayValue = $option[$dataValue[$valueKey]];
} else {
$displayValue = '';
}
} else {
if ($visibleField->getInputType()->getName() == 'Date') {
if (!empty($dataValue[$valueKey])) {
if (gettype($dataValue[$valueKey]) == "array") {
$dataValue[$valueKey] = new \DateTime($dataValue[$valueKey]['date'], new \DateTimeZone($dataValue[$valueKey]['timezone']));
}
$displayValue = $dataValue[$valueKey];
$displayValue = $displayValue->format('d/m/Y');
}
} else {
if (isset($dataValue[$valueKey])) {
$displayValue = $dataValue[$valueKey];
} else {
$displayValue = '';
}
}
}
//Handle calculated fields
if ($visibleField->getIsCalculated() == True) {
$displayValue = $this->calculateFieldExpression($dataValueInstance, $visibleField->getCalculatedExpression());
}
$this->recordsToDisplay[$dataValueInstance->getInstance()][$visibleField->getUid()] = $displayValue;
}
$this->recordsToDisplay[$dataValueInstance->getInstance()]['form'] = $dataValueInstance->getForm()->getName();
}
}
if (!isset($this->visibleFields)) {
$this->visibleFields = NULL;
}
if (!isset($this->sameLevel)) {
$this->sameLevel = NULL;
}
if (!isset($dataValue)) {
$dataValue = NULL;
}
if (!isset($records)) {
$records = NULL;
}
if (!isset($this->recordsToDisplay)) {
$this->recordsToDisplay = NULL;
}
if (!isset($this->recordInstances)) {
$this->recordInstances = NULL;
}
if (!isset($this->totalCompletenessMatrix)) {
$this->totalCompletenessMatrix = NULL;
}
if (!isset($this->totalExpectedCompleteness)) {
$this->totalExpectedCompleteness = NULL;
}
if (!isset($this->expectedCompleteness)) {
$this->expectedCompleteness = NULL;
}
}
public function load(ObjectManager $manager)
{
$stopwatch = new Stopwatch();
$stopwatch->start('dummyRecordGeneration');
// Populate dummy forms
$this->addDummyFemaleNames();
$this->addDummyMaleNmes();
$this->addDummyCourseNames();
$this->addDummyCourseLocations();
$this->addDummySponsors();
$loadUserData = new LoadUserData();
$loadUserData->addDummyUsers();
$dummyUsers = $loadUserData->getUsers();
$loadFieldData = new LoadFieldData();
$loadFieldData->addDummyFields();
$dummyFields = $loadFieldData->getFields();
$loadFormData = new LoadFormData();
$loadFormData->addDummyForms();
$dummyForms = $loadFormData->getForms();
$organiastionunits = $manager->getRepository('HrisOrganisationunitBundle:Organisationunit')->findAll();
/*
* Add data to facilities
*/
if (!empty($organiastionunits)) {
foreach ($organiastionunits as $organiastionunitKey => $organisationunit) {
/*
* Assign data to dispensary, hospital and health centres only.
*/
if (preg_match('/dispensary|hospital|health centre|council/i', $organisationunit->getLongname())) {
// Initiate record entering
// Enter two records for each orgunit
for ($recordIncr = 0; $recordIncr < $this->recordsPerOrganisationunit; $recordIncr++) {
$record = new Record();
$record->setOrganisationunit($organisationunit);
// Enter record for public and private form
$formNames = array('Public Employee Form', 'Private Employee Form');
$formName = $formNames[array_rand($formNames, 1)];
if (empty($formName)) {
$formName = 'Public Employee Form';
}
$form = $manager->getRepository('HrisFormBundle:Form')->findOneBy(array('name' => $formName));
// Find history fields belonging to this form for population of data
$queryBuilder = $manager->createQueryBuilder();
$historyFields = $queryBuilder->select('field')->from('HrisFormBundle:Field', 'field')->join('field.formFieldMember', 'formFieldMember')->join('formFieldMember.form', 'form')->where('form.id=:formId')->andWhere('field.hashistory=True')->setParameter('formId', $form->getId())->getQuery()->getResult();
$record->setForm($form);
$record->setComplete(True);
$record->setCorrect(True);
$record->setHashistory(False);
$record->setHastraining(False);
$dummyUserKey = array_rand($dummyUsers, 1);
$dummyUsername = $dummyUsers[$dummyUserKey]['username'];
$record->setUsername($dummyUsername);
// Constructing a Value Array
// @todo removing hard-coding of HrisRecordBundle:Record values
$value = array();
// Fetch all field members belonging to the form and add records
// Roll a dice with gender of employee to pick name
$genders = array('Male', 'Female');
$gender_picked = array_rand($genders, 1);
$formFieldMembers = $manager->getRepository('HrisFormBundle:FormFieldMember')->findBy(array('form' => $form));
foreach ($formFieldMembers as $formFieldMemberKey => $formFieldMember) {
/**
* Made dynamic, on which field column is used as key, i.e. uid, name or id.
*/
// Translates to $formFieldMember->getField()->getUid()
// or $formFieldMember->getField()->getUid() depending on value of $recordKeyName
$recordKeyName = ucfirst(Record::getFieldKey());
$valueKey = call_user_func_array(array($formFieldMember->getField(), "get{$recordKeyName}"), array());
if ($formFieldMember->getField()->getName() == "Firstname" || $formFieldMember->getField()->getName() == "Middlename" || $formFieldMember->getField()->getName() == "Surname" || $formFieldMember->getField()->getName() == "NextofKin") {
// Deal with names
if ($gender_picked == "Female" && ($formFieldMember->getField()->getName() == "Firstname" || $formFieldMember->getField()->getName() == "NextofKin")) {
$value[$valueKey] = $this->femaleNames[array_rand($this->femaleNames, 1)];
} else {
$value[$valueKey] = $this->maleNames[array_rand($this->maleNames, 1)];
}
if ($formFieldMember->getField()->getName() == "NextofKin") {
$value[$valueKey] .= ' ' . $this->maleNames[array_rand($this->maleNames, 1)];
}
//@todo remove hard-coding of instance
// used later for instance formulation
if ($formFieldMember->getField()->getName() == "Firstname") {
$firstName = $value[$valueKey];
}
if ($formFieldMember->getField()->getName() == "Middlename") {
$middleName = $value[$valueKey];
}
if ($formFieldMember->getField()->getName() == "Surname") {
$surname = $value[$valueKey];
}
} else {
if ($formFieldMember->getField()->getInputType()->getName() == "Select") {
// Deal with select
/**
* Made dynamic, on which field column is used as key, i.e. uid, name or id.
*/
// Translates to $fieldOptions[0]->getUid()
// or $fieldOptions[0]->getValue() depending on value of $recordKeyName
// $fieldOptionKey = ucfirst($record->getFieldOptionKey());
//$valueKey = call_user_func_array(array($fieldOptions[0], "get${fieldOptionKey}"),array());
$fieldOptionKey = ucfirst(Record::getFieldOptionKey());
$fieldOptions = $manager->getRepository('HrisFormBundle:FieldOption')->findBy(array('field' => $formFieldMember->getField()));
// For case of gender choose match name with gender
if ($formFieldMember->getField()->getName() == "Sex") {
// Made FieldOption key to store in record value array dynamic.
if ($fieldOptions[0]->getValue() == $gender_picked) {
$value[$valueKey] = call_user_func_array(array($fieldOptions[0], "get{$fieldOptionKey}"), array());
} else {
$value[$valueKey] = call_user_func_array(array($fieldOptions[1], "get{$fieldOptionKey}"), array());
}
} else {
// Made fieldOption key to store in record value array dynamic
$value[$valueKey] = call_user_func_array(array($fieldOptions[array_rand($fieldOptions, 1)], "get{$fieldOptionKey}"), array());
}
} else {
if ($formFieldMember->getField()->getInputType()->getName() == "Date") {
// Deal with dates
// If birth date pick 20 - 55 date range
// If employment data set it to birth date range+18
// If confirmation date, set it to employment date+1
// If promotion date, set it to confirmation+3
$beginDateStart = 50;
$beginDateStop = 75;
$endDateStart = 40;
$endDateStop = 50;
if ($formFieldMember->getField()->getName() == "DateOfBirth") {
$beginDateStart = 50;
$beginDateStop = 75;
$endDateStart = 40;
$endDateStop = 50;
} elseif ($formFieldMember->getField()->getName() == "DateofFirstAppointment") {
$beginDateStart -= 36;
$beginDateStop -= 36;
$endDateStart -= 36;
$endDateStop -= 36;
} elseif ($formFieldMember->getField()->getName() == "DateofConfirmation") {
$beginDateStart -= 37;
$beginDateStop -= 37;
$endDateStart -= 37;
$endDateStop -= 37;
} elseif ($formFieldMember->getField()->getName() == "DateofLastPromotion") {
$beginDateStart -= 40;
$beginDateStop -= 40;
$endDateStart -= 40;
$endDateStop -= 40;
}
$value[$valueKey] = new \DateTime($this->getRandDate(array($beginDateStart, $beginDateStop), array($endDateStart, $endDateStop)));
//@todo remove hard-coding of instance
if ($formFieldMember->getField()->getName() == "DateOfBirth") {
$dateOfBirth = $value[$valueKey];
}
} else {
if ($formFieldMember->getField()->getInputType()->getName() == "Text") {
// Deal with numbers
if ($formFieldMember->getField()->getName() == "NumberofChildrenDependants") {
$value[$valueKey] = rand(0, 10);
} elseif ($formFieldMember->getField()->getName() == "CheckNumber") {
$value[$valueKey] = rand(9999999, 9999999999);
} elseif ($formFieldMember->getField()->getName() == "EmployersFileNumber") {
$value[$valueKey] = "FN/" . rand(100, 100000);
} elseif ($formFieldMember->getField()->getName() == "RegistrationNumber") {
$value[$valueKey] = "RB/" . rand(10, 10000);
} elseif ($formFieldMember->getField()->getName() == "MonthlyBasicSalary") {
$value[$valueKey] = rand(100, 1500) . '000';
} else {
$value[$valueKey] = $this->maleNames[array_rand($this->maleNames, 1)] . " Street";
}
} else {
if ($formFieldMember->getField()->getInputType()->getName() == "TextArea") {
// Deal with domicile, contact
if ($formFieldMember->getField()->getName() == "ContactsofEmployee" || $formFieldMember->getField()->getName() == "ContactsofNextofKin") {
$value[$valueKey] = "+255" . rand(6, 7) . rand(53, 69) . rand(01, 998) . rand(01, 998);
}
}
}
}
}
}
}
$instance = md5($firstName . $middleName . $surname . $dateOfBirth->format('Y-m-d'));
$record->setInstance($instance);
$record->setValue($value);
//@todo check for uniqueness of instance and unique fields
$recordReference = strtolower(str_replace(' ', '', $record->getInstance())) . '-record';
$this->addReference($recordReference, $record);
$manager->persist($record);
// Randomly on flip of a coin assign history & training data
$outcomes = array(True, False);
if ($outcomes[array_rand($outcomes, 1)]) {
// Assign randomly between 2 to 4 histories per record
$numberofHistoriesToAssign = array(1, 2);
for ($incr = 0; $incr < $numberofHistoriesToAssign[array_rand($numberofHistoriesToAssign, 1)]; $incr++) {
$history = new History();
$history->setRecord($record);
$history->setUsername($record->getUsername());
//Calculate start date ranging starting form now-2yrs back and and stopping between 3-5 years back
$beginDateStart = 3;
$beginDateStop = 5;
$endDateStart = 0;
$endDateStop = 2;
$startDate = new \DateTime($this->getRandDate(array($beginDateStart, $beginDateStop), array($endDateStart, $endDateStop)));
$history->setStartdate($startDate);
$historyField = $historyFields[array_rand($historyFields, 1)];
//echo get_class($historyField);exit;
// If history field is Combo assign combo if text assign text
if ($historyField->getInputType() == "Select") {
$historyFieldOptions = $historyField->getFieldOption();
$historyFieldOptions = $historyFieldOptions->getValues();
$selectedHistoryOption = $historyFieldOptions[array_rand($historyFieldOptions, 1)];
$historyValue = $selectedHistoryOption->getValue();
} elseif ($historyField->getInputType() == "Date") {
//Calculate start date ranging starting form 1-3yrs back and and stopping between 5-8 years back
$beginDateStart = 5;
$beginDateStop = 8;
$endDateStart = 1;
$endDateStop = 3;
$historyDateObject = new \DateTime($this->getRandDate(array($beginDateStart, $beginDateStop), array($endDateStart, $endDateStop)));
$historyValue = $historyDateObject->format('Y-m-d');
} else {
// Deal with string history fields
if ($historyField->getName() == "Firstname" || $historyField->getName() == "Middlename" || $historyField->getName() == "Surname" || $historyField->getName() == "NextofKin") {
// Deal with names
if ($gender_picked == "Female" && ($historyField->getName() == "Firstname" || $historyField->getName() == "NextofKin")) {
$historyValue = $this->femaleNames[array_rand($this->femaleNames, 1)];
} else {
$historyValue = $this->maleNames[array_rand($this->maleNames, 1)];
}
if ($historyField->getName() == "NextofKin") {
$historyValue .= ' ' . $this->maleNames[array_rand($this->maleNames, 1)];
}
} else {
if ($historyField->getInputType()->getName() == "Text") {
// Deal with numbers
if ($historyField->getName() == "NumberofChildrenDependants") {
$historyValue = rand(0, 10);
} elseif ($historyField->getName() == "CheckNumber") {
$historyValue = rand(9999999, 9999999999);
} elseif ($historyField->getName() == "EmployersFileNumber") {
$historyValue = "FN/" . rand(100, 100000);
} elseif ($historyField->getName() == "RegistrationNumber") {
$historyValue = "RB/" . rand(10, 10000);
} elseif ($historyField->getName() == "MonthlyBasicSalary") {
$historyValue = rand(100, 1500) . '000';
} else {
$historyValue = $this->maleNames[array_rand($this->maleNames, 1)] . " Street";
}
} else {
if ($historyField->getInputType()->getName() == "TextArea") {
// Deal with domicile, contact
if ($historyField->getName() == "ContactsofEmployee" || $historyField->getName() == "ContactsofNextofKin") {
$historyValue = "+255" . rand(6, 7) . rand(53, 69) . rand(01, 998) . rand(01, 998);
}
}
}
}
}
$reason = $historyField->getCaption() . " changed.";
$history->setField($historyField);
$history->setHistory($historyValue);
$history->setReason($reason);
$manager->persist($history);
unset($history);
}
$record->setHashistory(True);
$manager->persist($record);
}
if ($outcomes[array_rand($outcomes, 1)]) {
// Assign randomly between 2 to 4 trainings per record
$numberofTrainingsToAssign = array(1, 2);
for ($incr = 0; $incr < $numberofTrainingsToAssign[array_rand($numberofTrainingsToAssign, 1)]; $incr++) {
$training = new Training();
$training->setRecord($record);
$training->setCoursename($this->courseNames[array_rand($this->courseNames, 1)]);
$training->setCourselocation($this->courseLocations[array_rand($this->courseLocations, 1)]);
$training->setSponsor($this->sponsor[array_rand($this->sponsor, 1)]);
//Calculate start date ranging starting form 9-10yrs back and and stopping between 10-12 years back
$beginDateStart = 10;
$beginDateStop = 12;
$endDateStart = 9;
$endDateStop = 10;
$startDate = new \DateTime($this->getRandDate(array($beginDateStart, $beginDateStop), array($endDateStart, $endDateStop)));
//Calculate end date ranging starting form 11-13yrs back and and stopping between 13-15 years back
$beginDateStart = 13;
$beginDateStop = 15;
$endDateStart = 11;
$endDateStop = 13;
$endDate = new \DateTime($this->getRandDate(array($beginDateStart, $beginDateStop), array($endDateStart, $endDateStop)));
$training->setStartdate($startDate);
$training->setEnddate($endDate);
$training->setUsername($record->getUsername());
$manager->persist($training);
unset($training);
}
$record->setHastraining(True);
$manager->persist($record);
}
unset($record);
}
}
}
}
$manager->flush();
/*
* Check Clock for time spent
*/
$dummyRecordGenerationTime = $stopwatch->stop('dummyRecordGeneration');
$duration = $dummyRecordGenerationTime->getDuration() / 1000;
unset($stopwatch);
if ($duration < 60) {
$durationMessage = round($duration, 2) . ' seconds';
} elseif ($duration >= 60 && $duration < 3600) {
$durationMessage = round($duration / 60, 2) . ' minutes';
} elseif ($duration >= 3600 && $duration < 216000) {
$durationMessage = round($duration / 3600, 2) . ' hours';
} else {
$durationMessage = round($duration / 86400, 2) . ' hours';
}
echo "\tDummy Records generation complete in " . $durationMessage . ".\n\n";
}
/**
* Creates a new ExportMetadata entity.
*
* @Secure(roles="ROLE_SUPER_USER,ROLE_EXPORTMETADATA_CREATE")
* @Route("/{_format}", requirements={"_format"="json|"}, defaults={"_format"="json"}, name="importexport_exportmetadata_create")
* @Method("POST")
* @Template("HrisImportExportBundle:ExportMetadata:export.json.twig")
*/
public function createAction(Request $request, $_format = "json")
{
$serializer = $this->container->get('serializer');
$exportMetadataForm = $this->createForm(new ExportMetadataType(), null, array('em' => $this->getDoctrine()->getManager()));
$exportMetadataForm->bind($request);
if ($exportMetadataForm->isValid()) {
$exportMetadataFormData = $exportMetadataForm->getData();
$organisationunit = $exportMetadataFormData['organisationunit'];
$forms = $exportMetadataFormData['forms'];
$withLowerLevels = $exportMetadataFormData['withLowerLevels'];
}
/*
* Generate an export file with data records
*/
$formIds = array();
foreach ($forms as $formKey => $form) {
$formIds[] = $form->getId();
foreach ($form->getFormFieldMember() as $key => $fieldObject) {
if (!$fieldObject->getField()->getIsCalculated()) {
if (empty($formFieldMemberIds)) {
$formFieldMemberIds = $fieldObject->getField()->getId();
} else {
$formFieldMemberIds .= ',' . $fieldObject->getField()->getId();
}
}
}
}
$em = $this->getDoctrine()->getManager();
$queryBuilder = $this->getDoctrine()->getManager()->createQueryBuilder();
$userManager = $this->container->get('fos_user.user_manager');
$user = $userManager->findUserByUsername($this->getUser());
//Prepare field Option map, converting from stored FieldOption key in record value array to actual text value
$fieldOptions = $this->getDoctrine()->getManager()->getRepository('HrisFormBundle:FieldOption')->findAll();
foreach ($fieldOptions as $fieldOptionKey => $fieldOption) {
$recordFieldOptionKey = ucfirst(Record::getFieldOptionKey());
$fieldOptionMap[call_user_func_array(array($fieldOption, "get{$recordFieldOptionKey}"), array())] = $fieldOption->getValue();
}
//Prepare field map, converting from stored FieldOption key in record value array to actual text value
$fields = $this->getDoctrine()->getManager()->getRepository('HrisFormBundle:Field')->findAll();
foreach ($fields as $fieldKey => $field) {
$recordFieldKey = ucfirst(Record::getFieldKey());
$fieldMap[$field->getName()] = call_user_func_array(array($field, "get{$recordFieldKey}"), array());
}
$records = $queryBuilder->select(' record.id id,
organisationunit.id organisationunit_id,
form.id form_id,
record.uid,
record.instance,
record.value,
record.complete,
record.correct,
record.hashistory,
record.hastraining,
record.datecreated,
record.lastupdated,
record.username')->from('HrisRecordsBundle:Record', 'record')->join('record.organisationunit', 'organisationunit')->join('record.form', 'form')->join('organisationunit.organisationunitStructure', 'organisationunitStructure')->join('organisationunitStructure.level', 'organisationunitLevel')->andWhere('organisationunit.active=True');
if ($withLowerLevels) {
$records = $records->andWhere('organisationunitLevel.level >= (
SELECT selectedOrganisationunitLevel.level
FROM HrisOrganisationunitBundle:OrganisationunitStructure selectedOrganisationunitStructure
INNER JOIN selectedOrganisationunitStructure.level selectedOrganisationunitLevel
WHERE selectedOrganisationunitStructure.organisationunit=:selectedOrganisationunit )')->andWhere('organisationunitStructure.level' . $organisationunit->getOrganisationunitStructure()->getLevel()->getLevel() . 'Organisationunit=:levelId');
} else {
$records = $records->andWhere('organisationunit.id=' . $organisationunit->getId());
}
$records = $records->andWhere($queryBuilder->expr()->in('form.id', ':formIds'))->setParameters(array('levelId' => $organisationunit->getId(), 'selectedOrganisationunit' => $organisationunit->getId(), 'formIds' => $formIds))->getQuery()->getResult();
//Field Details
$fields = $this->getDoctrine()->getManager()->createQueryBuilder()->select(' field.id,
field.uid,
dataType.id datatype_id,
inputType.id inputtype_id,
field.name,
field.compulsory,
field.isUnique,
field.datecreated,
field.lastupdated')->from('HrisFormBundle:Field', 'field')->join('field.dataType', 'dataType')->join('field.inputType', 'inputType')->getQuery()->getResult();
//Field Option Details
$fieldOptions = $this->getDoctrine()->getManager()->createQueryBuilder()->select('fieldOption.id,
fieldOption.value,
field.id field_id,
fieldOption.datecreated,
fieldOption.lastupdated')->from('HrisFormBundle:FieldOption', 'fieldOption')->join('fieldOption.field', 'field')->getQuery()->getResult();
//Organisationunit details
$organisationunits = $this->getDoctrine()->getManager()->createQueryBuilder()->select('organisationunit.uid organisationunit_uid,
organisationunit.longname organisationunit_longname,
organisationunit.datecreated organisationunit_datecreated,
organisationunit.lastupdated organisationunit_lastupdated')->from('HrisOrganisationunitBundle:Organisationunit', 'organisationunit')->join('organisationunit.organisationunitStructure', 'organisationunitStructure')->join('organisationunitStructure.level', 'organisationunitLevel')->andWhere('organisationunit.active=True');
if ($withLowerLevels) {
$organisationunits = $organisationunits->andWhere('organisationunitLevel.level >= (
SELECT selectedOrganisationunitLevel.level
FROM HrisOrganisationunitBundle:OrganisationunitStructure selectedOrganisationunitStructure
INNER JOIN selectedOrganisationunitStructure.level selectedOrganisationunitLevel
WHERE selectedOrganisationunitStructure.organisationunit=:selectedOrganisationunit )')->andWhere('organisationunitStructure.level' . $organisationunit->getOrganisationunitStructure()->getLevel()->getLevel() . 'Organisationunit=:levelId');
} else {
$organisationunits = $organisationunits->andWhere('organisationunit.id=' . $organisationunit->getId());
}
$organisationunits = $organisationunits->setParameters(array('levelId' => $organisationunit->getId(), 'selectedOrganisationunit' => $organisationunit->getId()))->getQuery()->getResult();
$dataexport = array('hris_organisationunit' => $organisationunits, 'hris_field' => $fields, 'hris_fieldoption' => $fieldOptions, 'hris_record' => $records);
$serializer = $this->container->get('serializer');
return array('records' => $serializer->serialize($dataexport, empty($_format) ? "json" : $_format));
}
/**
* Lists all Records by forms.
*
* @Secure(roles="ROLE_SUPER_USER,ROLE_RECORD_LIST")
* @Route("/viewrecords/{formid}/form", requirements={"formid"="\d+"}, defaults={"formid"=0}, name="instancerecord_viewrecords")
* @Method("GET")
* @Template()
*/
public function viewRecordsAction($formid)
{
$em = $this->getDoctrine()->getManager();
$queryBuilder = $this->getDoctrine()->getManager()->createQueryBuilder();
$userManager = $this->get('fos_user.user_manager');
$user = $userManager->findUserByUsername($this->getUser());
$organisationunit = $user->getOrganisationunit();
if ($formid == 0) {
$formIds = $this->getDoctrine()->getManager()->createQueryBuilder()->select('form.id')->from('HrisFormBundle:Form', 'form')->getQuery()->getArrayResult();
$formIds = $this->array_value_recursive('id', $formIds);
$forms = $em->getRepository('HrisFormBundle:Form')->findAll();
} else {
$forms = $em->getRepository('HrisFormBundle:Form')->findby(array('id' => $formid));
$formIds[] = $formid;
}
//Prepare field Option map, converting from stored FieldOption key in record value array to actual text value
$fieldOptions = $this->getDoctrine()->getManager()->getRepository('HrisFormBundle:FieldOption')->findAll();
foreach ($fieldOptions as $fieldOptionKey => $fieldOption) {
$recordFieldOptionKey = ucfirst(Record::getFieldOptionKey());
$fieldOptionMap[call_user_func_array(array($fieldOption, "get{$recordFieldOptionKey}"), array())] = $fieldOption->getValue();
}
//If user's organisationunit is data entry level pull only records of his organisationunit
//else pull lower children too.
$records = $queryBuilder->select('record')->from('HrisRecordsBundle:Record', 'record')->join('record.organisationunit', 'organisationunit')->join('record.form', 'form');
if ($organisationunit->getOrganisationunitStructure()->getLevel()->getDataentrylevel()) {
$records = $records->join('organisationunit.organisationunitStructure', 'organisationunitStructure')->join('organisationunitStructure.level', 'organisationunitLevel')->andWhere('organisationunitLevel.level >= (
SELECT selectedOrganisationunitLevel.level
FROM HrisOrganisationunitBundle:OrganisationunitStructure selectedOrganisationunitStructure
INNER JOIN selectedOrganisationunitStructure.level selectedOrganisationunitLevel
WHERE selectedOrganisationunitStructure.organisationunit=:selectedOrganisationunit )')->andWhere('organisationunitStructure.level' . $organisationunit->getOrganisationunitStructure()->getLevel()->getLevel() . 'Organisationunit=:levelId');
$parameters = array('levelId' => $organisationunit->getId(), 'selectedOrganisationunit' => $organisationunit->getId(), 'formIds' => $formIds);
} else {
$records = $records->andWhere('organisationunit.id=:selectedOrganisationunit');
$parameters = array('selectedOrganisationunit' => $organisationunit->getId(), 'formIds' => $formIds);
}
$records = $records->andWhere($queryBuilder->expr()->in('form.id', ':formIds'))->setParameters($parameters)->getQuery()->getResult();
$formNames = NULL;
$visibleFields = array();
$formFields = array();
$incr = 0;
$formIds = array();
foreach ($forms as $formKey => $form) {
$incr++;
$formIds[] = $form->getId();
// Concatenate form Names
if (empty($formNames)) {
$formNames = $form->getTitle();
} else {
if (count($formNames) == $incr) {
$formNames .= ',' . $form->getTitle();
}
}
// Accrue visible fields
foreach ($form->getFormVisibleFields() as $visibleFieldKey => $visibleField) {
if (!in_array($visibleField->getField(), $visibleFields)) {
$visibleFields[] = $visibleField->getField();
}
}
// Accrue form fields
foreach ($form->getFormFieldMember() as $formFieldKey => $formField) {
if (!in_array($formField->getField(), $formFields)) {
$formFields[] = $formField->getField();
}
}
}
$title = "Employee Records for " . $organisationunit->getLongname();
$title .= " for " . $formNames;
if (empty($visibleFields)) {
$visibleFields = $formFields;
}
//getting all User Forms for User Migration
$user = $this->container->get('security.context')->getToken()->getUser();
$userForms = $user->getForm();
$delete_forms = NULL;
foreach ($records as $entity) {
$delete_form = $this->createDeleteForm($entity->getId());
$delete_forms[$entity->getId()] = $delete_form->createView();
}
return array('title' => $title, 'visibleFields' => $visibleFields, 'formFields' => $formFields, 'records' => $records, 'optionMap' => $fieldOptionMap, 'userForms' => $userForms, 'delete_forms' => $delete_forms, 'formid' => $formid);
}
/**
* Generate resource table
*
* @param $entityManager
* @return string
*/
public function generateResourceTable($entityManager, $logger = NULL)
{
$totalInsertedRecords = NULL;
$totalResourceTableFields = NULL;
$stopwatch = new Stopwatch();
$stopwatch->start('resourceTableGeneration');
$returnMessage = NULL;
$schemaManager = $entityManager->getConnection()->getSchemaManager();
$resourceTableName = '_resource_' . str_replace(' ', '_', trim(strtolower($this->getName())));
//Prepare database name
if ($this->getIsgenerating() == False && ($this->isResourceTableOutdated($entityManager) == True || $this->isResourceTableCompletelyGenerated($entityManager) == False)) {
$logger->info('Resource table is out dated, was not completely generated');
/*
* Resource table is out dated, was not completely generated
*/
//Switch state to generating
$this->setIsgenerating(True);
$entityManager->persist($this);
try {
$entityManager->flush();
} catch (\Doctrine\Orm\NoResultException $e) {
//@todo Cross-check, if database is left in generating state, it'll never be udpated ever.
$error = "Error in Changing State to generating";
echo $error;
return False;
}
if ($this->isResourceTableOutdated($entityManager)) {
$this->messagelog = "Regeneration Trigger: Outdated Resource Table.\n";
} elseif ($this->isResourceTableCompletelyGenerated($entityManager) == False) {
$this->messagelog = "Regeneration Trigger:Incomplete Resource Tabe.\n";
} else {
$this->messagelog = '';
}
/**
* @var $resourceTable String
*/
// Cleanup any residue temporary resourcetable left
if ($schemaManager->tablesExist($resourceTableName . '_temporary')) {
$schemaManager->dropTable($resourceTableName . '_temporary');
}
$resourceTable = new Table($resourceTableName . '_temporary');
//Create database table
// Create primary key
$resourceTable->addColumn('id', "integer", array('nullable' => true, 'precision' => 0, 'scale' => 0));
$resourceTable->addColumn('instance', "string", array('length' => 64, 'notnull' => false));
$resourceTable->setPrimaryKey(array('id'), 'IDX_' . uniqid(''));
$resourceTable->addIndex(array('id'), 'IDX_' . uniqid(''));
// Create other columns(fields, organisationunits,etc) in the resource table
foreach ($this->getResourceTableFieldMember() as $resourceTableKey => $resourceTableFieldMember) {
$field = $resourceTableFieldMember->getField();
if ($field->getDataType()->getName() == "String") {
$resourceTable->addColumn($field->getName(), "string", array('length' => 64, 'notnull' => false));
} elseif ($field->getDataType()->getName() == "Integer") {
$resourceTable->addColumn($field->getName(), "integer", array('notnull' => false, 'precision' => 0, 'scale' => 0));
} elseif ($field->getDataType()->getName() == "Double") {
$resourceTable->addColumn($field->getName(), "float", array('notnull' => false, 'precision' => 0, 'scale' => 0));
} elseif ($field->getDataType()->getName() == "Date") {
$resourceTable->addColumn($field->getName(), "date", array('notnull' => false, 'precision' => 0, 'scale' => 0));
// Additional analysis columns
//$resourceTable->addColumn($field->getName().'_day', "string",array('length'=>64, 'notnull'=>false));
//$resourceTable->addColumn($field->getName().'_month_number', "integer",array('notnull'=>false,'precision'=>0, 'scale'=>0));
$resourceTable->addColumn($field->getName() . '_month_text', "string", array('length' => 64, 'notnull' => false));
$resourceTable->addColumn($field->getName() . '_year', "integer", array('notnull' => false, 'precision' => 0, 'scale' => 0));
//$resourceTable->addColumn($field->getName().'_month_and_year', "string",array('length'=>64, 'notnull'=>false));
}
// @todo implement after creation of history date class
// Add History date field for fields with history
if ($field->getHashistory()) {
$resourceTable->addColumn($field->getName() . '_last_updated', "date", array('notnull' => false, 'precision' => 0, 'scale' => 0));
// Additional analysis columns
//$resourceTable->addColumn($field->getName().'_last_updated_day', "string",array('length'=>64, 'notnull'=>false));
//$resourceTable->addColumn($field->getName().'_last_updated_month_number', "integer",array('notnull'=>false,'precision'=>0, 'scale'=>0));
$resourceTable->addColumn($field->getName() . '_last_updated_month_text', "string", array('length' => 64, 'notnull' => false));
$resourceTable->addColumn($field->getName() . '_last_updated_year', "integer", array('notnull' => false, 'precision' => 0, 'scale' => 0));
//$resourceTable->addColumn($field->getName().'_last_updated_month_and_year', "string",array('length'=>64, 'notnull'=>false));
}
$totalResourceTableFields++;
unset($field);
}
// Make OrganisationunitLevels of orgunit
$organisationunitLevels = $entityManager->createQuery('SELECT DISTINCT organisationunitLevel FROM HrisOrganisationunitBundle:OrganisationunitLevel organisationunitLevel ORDER BY organisationunitLevel.level ')->getResult();
foreach ($organisationunitLevels as $organisationunitLevelKey => $organisationunitLevel) {
$organisationunitLevelName = "level" . $organisationunitLevel->getLevel() . "_" . str_replace(',', '_', str_replace('.', '_', str_replace('/', '_', str_replace(' ', '_', $organisationunitLevel->getName()))));
$resourceTable->addColumn($organisationunitLevelName, "string", array('length' => 64, 'notnull' => false));
}
// Make OrganisationunitGroupsets Column
$organisationunitGroupsets = $entityManager->getRepository('HrisOrganisationunitBundle:OrganisationunitGroupset')->findAll();
foreach ($organisationunitGroupsets as $organisationunitGroupsetKey => $organisationunitGroupset) {
$resourceTable->addColumn($organisationunitGroupset->getName(), "string", array('length' => 64, 'notnull' => false));
}
// Form and Organisationunit name
$resourceTable->addColumn("Organisationunit_name", "string", array('length' => 64, 'notnull' => false));
$resourceTable->addColumn("Form_name", "string", array('length' => 64, 'notnull' => false));
$resourceTable->addColumn('Organisationunit_id', "integer", array('notnull' => false, 'precision' => 0, 'scale' => 0));
$resourceTable->addColumn('Form_id', "integer", array('notnull' => false, 'precision' => 0, 'scale' => 0));
$resourceTable->addColumn("Lastupdated", "datetime", array('notnull' => false, 'precision' => 0, 'scale' => 0));
// Creating table
$schemaManager->createTable($resourceTable);
unset($resourceTable);
$schemaGenerationLap = $stopwatch->lap('resourceTableGeneration');
$schemaGenerationDuration = round($schemaGenerationLap->getDuration() / 1000, 2);
$this->messagelog .= 'Operation: Table named ' . $resourceTableName . ' with ' . $totalResourceTableFields . " Fields Generated in " . $schemaGenerationDuration . " seconds.\n";
// Populating data into created table
$queryBuilder = $entityManager->createQueryBuilder()->select('record')->from('HrisRecordsBundle:Record', 'record')->join('record.organisationunit', 'organisationunit')->join('record.form', 'form')->join('organisationunit.organisationunitStructure', 'organisationunitStructure')->getQuery();
try {
$records = $queryBuilder->getResult();
} catch (\Doctrine\Orm\NoResultException $e) {
echo 'Error in returning Data Values';
}
if (!empty($records)) {
/**
* Make sure organisationunitstructure is uptodate for good measure
*/
// Check and Notify if organisationunit structure doesn't exist
$queryBuilder = $entityManager->createQueryBuilder();
$organisationunitStructureCount = $queryBuilder->select('count( organisationunitStructure.id )')->from('HrisOrganisationunitBundle:OrganisationunitStructure', 'organisationunitStructure')->getQuery()->getSingleScalarResult();
$queryBuilder = $entityManager->createQueryBuilder();
$organisationunitCount = $queryBuilder->select('count( organisationunit.id )')->from('HrisOrganisationunitBundle:Organisationunit', 'organisationunit')->getQuery()->getSingleScalarResult();
// Regenerate Orgunit Stucture of Orgunit and OrgunitStructure Differs
if ($organisationunitCount != $organisationunitStructureCount) {
$logger->info('Regenerating organisationunit structure');
$this->returnMessage = '';
// Regenerate Orgunit Structure
$organisationunitStructure = new OrganisationunitStructureController();
$this->returnMessage = $organisationunitStructure->regenerateOrganisationunitStructure($entityManager);
} else {
$this->returnMessage = 'Organisationunit structure is complete!';
}
// Regenerate Levels if OrgunitLevel and DISTINCT OrgunitStructure.level differs
$organisationunitStructureLevels = $entityManager->createQuery('SELECT DISTINCT organisationunitLevel.level FROM HrisOrganisationunitBundle:OrganisationunitStructure organisationunitStructure INNER JOIN organisationunitStructure.level organisationunitLevel ORDER BY organisationunitLevel.level ')->getResult();
$organisationunitLevelInfos = $entityManager->createQuery('SELECT organisationunitLevel.level,organisationunitLevel.name,organisationunitLevel.description FROM HrisOrganisationunitBundle:OrganisationunitLevel organisationunitLevel ORDER BY organisationunitLevel.level ')->getResult();
$organisationunitStructureLevels = $this->array_value_recursive('level', $organisationunitStructureLevels);
$organisationunitLevelsLevel = $this->array_value_recursive('level', $organisationunitLevelInfos);
if ($organisationunitLevelsLevel != $organisationunitStructureLevels && !empty($organisationunitStructureLevels)) {
$logger->info('Regenerating organisationunit levels');
if (!empty($organisationunitLevelInfos)) {
// Cache in-memory saved Level names and descriptions
$organisationunitLevelsName = $this->array_value_recursive('name', $organisationunitLevelInfos);
$organisationunitLevelsDescription = $this->array_value_recursive('description', $organisationunitLevelInfos);
$organisationunitLevelsName = array_combine($organisationunitLevelsLevel, $organisationunitLevelsName);
$organisationunitLevelsDescription = array_combine($organisationunitLevelsLevel, $organisationunitLevelsDescription);
$qb = $entityManager->createQueryBuilder('organisationunitLevel')->delete('HrisOrganisationunitBundle:OrganisationunitLevel', 'organisationunitLevel')->getQuery()->getResult();
}
foreach ($organisationunitStructureLevels as $key => $organisationunitStructureLevel) {
// Update Levels
$organisationunitLevel = new OrganisationunitLevel();
if (in_array($organisationunitStructureLevel, $organisationunitLevelsLevel)) {
$organisationunitLevel->setName($organisationunitLevelsName[$organisationunitStructureLevel]);
$organisationunitLevel->setDescription($organisationunitLevelsDescription[$organisationunitStructureLevel]);
$organisationunitLevel->setLevel($organisationunitStructureLevel);
$entityManager->persist($organisationunitLevel);
} else {
$organisationunitLevel->setName('Level' . $organisationunitStructureLevel);
$organisationunitLevel->setDescription('Level' . $organisationunitStructureLevel);
$organisationunitLevel->setLevel($organisationunitStructureLevel);
$entityManager->persist($organisationunitLevel);
}
}
$entityManager->flush();
}
$dataArray = NULL;
$id = 0;
//Prepare field Option map, converting from stored FieldOption key in record value array to actual text value
$fieldOptions = $entityManager->getRepository('HrisFormBundle:FieldOption')->findAll();
foreach ($fieldOptions as $fieldOptionKey => $fieldOption) {
$recordFieldOptionKey = ucfirst(Record::getFieldOptionKey());
$fieldOptionMap[call_user_func_array(array($fieldOption, "get{$recordFieldOptionKey}"), array())] = $fieldOption->getValue();
}
unset($fieldOptions);
foreach ($records as $recordKey => $record) {
$currentInstance = $record->getInstance();
$dataValue = $record->getValue();
$id++;
$dataArray['id'] = $id;
$age = NULL;
$retirementDate = NULL;
$employmentDuration = NULL;
$dataArray['instance'] = $record->getInstance();
foreach ($this->getResourceTableFieldMember() as $resourceTableKey => $resourceTableFieldMember) {
unset($field);
$field = $resourceTableFieldMember->getField();
// Field Options
/**
* Made dynamic, on which field column is used as key, i.e. uid, name or id.
*/
// Translates to $field->getUid()
// or $field->getUid() depending on value of $recordKeyName
$recordFieldKey = ucfirst(Record::getFieldKey());
$valueKey = call_user_func_array(array($field, "get{$recordFieldKey}"), array());
if ($field->getIsCalculated()) {
if (preg_match_all('/\\#{([^\\}]+)\\}/', $field->getCalculatedExpression(), $match)) {
$fields = $entityManager->getRepository('HrisFormBundle:Field')->findOneBy(array('name' => $match[1][0]));
// @todo mechanism to notified on flawed formula(resulting in no match in db)
$valueKey = @@call_user_func_array(array($fields, "get{$recordFieldKey}"), array());
}
}
if (isset($dataValue[$valueKey])) {
$dataArray[$field->getName()] = $dataValue[$valueKey];
if ($field->getInputType()->getName() == 'Select') {
if (isset($fieldOptionMap[$dataValue[$valueKey]])) {
// Resolve actual value from stored key
$dataArray[$field->getName()] = trim($fieldOptionMap[$dataValue[$valueKey]]);
} else {
$dataArray[$field->getName()] = NULL;
}
} else {
if ($field->getInputType()->getName() == 'Date') {
if ($field->getIsCalculated() == true) {
if (!empty($dataValue[$valueKey])) {
$displayValue = new \DateTime($dataValue[$valueKey]['date'], new \DateTimeZone($dataValue[$valueKey]['timezone']));
$datavalue = str_replace($match[0][0], $displayValue->format('Y-m-d'), $field->getCalculatedExpression());
$dataArray[$field->getName()] = eval("return {$datavalue};");
//$dataArray[$field->getName()] = trim($displayValue->format('Y-m-d H:i:s.u')); //working on date format fix
if ($field->getDataType()->getName() == 'Date') {
$dataArray[$field->getName() . '_month_text'] = trim($displayValue->format('F'));
$dataArray[$field->getName() . '_year'] = trim($displayValue->format('Y'));
}
} else {
$dataArray[$field->getName()] = NULL;
}
} else {
if (!empty($dataValue[$valueKey])) {
$displayValue = new \DateTime($dataValue[$valueKey]['date'], new \DateTimeZone($dataValue[$valueKey]['timezone']));
$dataArray[$field->getName()] = trim($displayValue->format('Y-m-d'));
//working on date format fix
//$dataArray[$field->getName().'_day'] = trim($displayValue->format('l'));
//$dataArray[$field->getName().'_month_number'] = trim($displayValue->format('m'));
$dataArray[$field->getName() . '_month_text'] = trim($displayValue->format('F'));
$dataArray[$field->getName() . '_year'] = trim($displayValue->format('Y'));
//$dataArray[$field->getName().'_month_and_year'] = trim($displayValue->format('F Y'));
} else {
$dataArray[$field->getName()] = NULL;
}
}
} else {
if ($field->getDataType()->getName() == 'Integer') {
if (!empty($dataValue[$valueKey])) {
$intValue = (int) $dataValue[$valueKey];
$dataArray[$field->getName()] = trim($intValue);
//working on Integers format fix
} else {
$dataArray[$field->getName()] = NULL;
}
} else {
if ($field->getDataType()->getName() == 'Double') {
if (!empty($dataValue[$valueKey])) {
$floatValue = (double) $dataValue[$valueKey];
$dataArray[$field->getName()] = trim($floatValue);
//working on float format fix
} else {
$dataArray[$field->getName()] = NULL;
}
} else {
$dataArray[$field->getName()] = substr(trim($dataValue[$valueKey]), 0, 63);
}
}
}
}
} else {
$dataArray[$field->getName()] = NULL;
}
// @todo implement after creation of history date class
if ($field->getHashistory() && $field->getInputType()->getName() == "Select" && isset($dataValue[$valueKey])) {
// Fetch history date with instance same as our current data
$historyDates = $entityManager->getRepository('HrisRecordsBundle:HistoryDate')->findOneBy(array('instance' => $record->getInstance(), 'history' => $dataValue[$valueKey], 'field' => $field));
if (!empty($historyDates)) {
if (!empty($historyDates)) {
$dataArray[$field->getName() . '_last_updated'] = trim($historyDates->getPreviousdate()->format('Y-m-d H:i:s.u'));
//$dataArray[$field->getName().'_last_updated_day'] = trim($historyDates->getPreviousdate()->format('l'));
//$dataArray[$field->getName().'_last_updated_month_number'] = trim($historyDates->getPreviousdate()->format('m'));
$dataArray[$field->getName() . '_last_updated_month_text'] = trim($historyDates->getPreviousdate()->format('F'));
$dataArray[$field->getName() . '_last_updated_year'] = trim($historyDates->getPreviousdate()->format('Y'));
//$dataArray[$field->getName().'_last_updated_month_and_year'] = trim($historyDates->getPreviousdate()->format('F Y'));
}
}
}
}
// Fill in Levels
foreach ($organisationunitLevels as $organisationunitLevelKey => $organisationunitLevel) {
$organisationunitLevelName = str_replace(' ', '_', "level" . $organisationunitLevel->getLevel() . "_" . str_replace(',', '_', str_replace('.', '_', str_replace('/', '_', $organisationunitLevel->getName()))));
$organisationunitStructure = $record->getOrganisationunit()->getOrganisationunitStructure();
$nLevelParent = $organisationunitStructure->getParentByNLevelsBack($record->getOrganisationunit(), $organisationunitStructure->getLevel()->getLevel() - $organisationunitLevel->getLevel());
if (!empty($nLevelParent)) {
$dataArray[$organisationunitLevelName] = $nLevelParent->getLongname();
}
$thisrganisationunitLevel = $entityManager->getRepository('HrisOrganisationunitBundle:OrganisationunitLevel')->findOneBy(array('level' => $organisationunitStructure->getLevel()->getLevel()));
$organisationunitLevelName = str_replace(' ', '_', "level" . $thisrganisationunitLevel->getLevel() . "_" . str_replace(',', '_', str_replace('.', '_', str_replace('/', '_', $thisrganisationunitLevel->getName()))));
$dataArray[$organisationunitLevelName] = $record->getOrganisationunit()->getLongname();
unset($nLevelParent);
unset($organisationunitLevelName);
unset($organisationunitStructure);
}
// Fill in Groupset Columns
foreach ($organisationunitGroupsets as $organisationunitGroupsetKey => $organisationunitGroupset) {
$organisationunitGroupsetNames = NULL;
foreach ($organisationunitGroupset->getOrganisationunitGroup() as $organisationunitGroupKey => $organisationunitGroup) {
if ($organisationunitGroup->getOrganisationunit()->contains($record->getOrganisationunit())) {
if (empty($organisationunitGroupNames)) {
$organisationunitGroupNames = $organisationunitGroup->getName();
} else {
if (!preg_match("/" . $organisationunitGroup->getName() . "/", $organisationunitGroupNames)) {
$organisationunitGroupNames .= ',' . ($organisationunitGroupNames = $organisationunitGroup->getName());
}
}
}
}
if (!isset($organisationunitGroupNames)) {
$organisationunitGroupNames = NULL;
}
$dataArray[$organisationunitGroupset->getName()] = $organisationunitGroupNames;
unset($organisationunitGroupNames);
}
// Form and Orgunit
$dataArray['Organisationunit_name'] = $record->getOrganisationunit()->getLongname();
$dataArray['Form_name'] = $record->getForm()->getName();
$dataArray['Organisationunit_id'] = $record->getOrganisationunit()->getId();
$dataArray['Form_id'] = $record->getForm()->getId();
$dataArray['Lastupdated'] = trim($record->getLastupdated()->format('Y-m-d H:i:s.u'));
$entityManager->getConnection()->insert($resourceTableName . '_temporary', $dataArray);
$logger->info('Inserted record instance ' . $dataArray['instance'] . ' for ' . $dataArray['Organisationunit_name'] . ' on form: ' . $record->getForm()->getName());
$totalInsertedRecords++;
unset($dataArray);
unset($dataValue);
unset($currentInstance);
}
}
unset($records);
$dataInsertionLap = $stopwatch->lap('resourceTableGeneration');
$dataInsertionDuration = round($dataInsertionLap->getDuration() / 1000, 2) - $schemaGenerationDuration;
$singleDataInsertionDuration = round($dataInsertionDuration / $totalInsertedRecords, 2);
if ($dataInsertionDuration < 60) {
$dataInsertionDurationMessage = round($dataInsertionDuration, 2) . ' sec.';
} elseif ($dataInsertionDuration >= 60 && $dataInsertionDuration < 3600) {
$dataInsertionDurationMessage = round($dataInsertionDuration / 60, 2) . ' min.';
} elseif ($dataInsertionDuration >= 3600 && $dataInsertionDuration < 216000) {
$dataInsertionDurationMessage = round($dataInsertionDuration / 3600, 2) . ' hrs';
} else {
$dataInsertionDurationMessage = round($dataInsertionDuration / 86400, 2) . ' days';
}
if ($singleDataInsertionDuration < 60) {
$singleDataInsertionDurationMessage = "(" . round($singleDataInsertionDuration, 2) . ' sec./record)';
} elseif ($singleDataInsertionDuration >= 60 && $singleDataInsertionDuration < 3600) {
$singleDataInsertionDurationMessage = "(" . round($singleDataInsertionDuration / 60, 2) . ' min./record)';
} elseif ($singleDataInsertionDuration >= 3600 && $singleDataInsertionDuration < 216000) {
$singleDataInsertionDurationMessage = "(" . round($singleDataInsertionDuration / 3600, 2) . ' hrs/record)';
} else {
$singleDataInsertionDurationMessage = "(" . round($singleDataInsertionDuration / 86400, 2) . ' days/record)';
}
$this->messagelog .= "Operation: " . $totalInsertedRecords . " Records Inserted into " . $resourceTableName . " in " . $dataInsertionDurationMessage . $singleDataInsertionDurationMessage . ".\n";
$logger->info($this->messagelog);
/*
* Replace existing resource table with completely regenerated temporary resource table
*/
// Drop table if it exists
if ($schemaManager->tablesExist($resourceTableName)) {
$schemaManager->dropTable($resourceTableName);
}
$schemaManager->renameTable($resourceTableName . '_temporary', $resourceTableName);
unset($schemaManager);
$stopwatch->lap('resourceTableGeneration');
$offlineDuration = round($dataInsertionLap->getDuration() / 1000, 2) - ($schemaGenerationDuration + $dataInsertionDuration);
if ($offlineDuration < 60) {
$offlineDurationMessage = round($offlineDuration, 2) . ' sec.';
} elseif ($offlineDuration >= 60 && $offlineDuration < 3600) {
$offlineDurationMessage = round($offlineDuration / 60, 2) . ' min.';
} elseif ($offlineDuration >= 3600 && $offlineDuration < 216000) {
$offlineDurationMessage = round($offlineDuration / 3600, 2) . ' hrs';
} else {
$offlineDurationMessage = round($offlineDuration / 86400, 2) . ' days';
}
$this->messagelog .= "Reports Offline Time: Resourcetable was offline for " . $offlineDurationMessage . "\n";
// Update last generated after running the script
$this->setLastgenerated(new \DateTime('now'));
$this->setIsgenerating(False);
$entityManager->persist($this);
try {
$entityManager->flush();
} catch (\Doctrine\Orm\NoResultException $e) {
$error = "Error Last generated";
}
/*
* Check Clock for time spent
*/
$resourceTableGenerationTime = $stopwatch->stop('resourceTableGeneration');
$duration = $resourceTableGenerationTime->getDuration() / 1000;
unset($stopwatch);
if ($duration < 60) {
$durationMessage = round($duration, 2) . ' seconds';
} elseif ($duration >= 60 && $duration < 3600) {
$durationMessage = round($duration / 60, 2) . ' minutes';
} elseif ($duration >= 3600 && $duration < 216000) {
$durationMessage = round($duration / 3600, 2) . ' hours';
} else {
$durationMessage = round($duration / 86400, 2) . ' hours';
}
$this->messagelog .= "Operation: Resource Table generation completeted in " . $durationMessage . ".\n\n";
return True;
} else {
$this->messagelog .= "Status: Resource Table " . $resourceTableName . " is upto date.\n";
return False;
}
}
