/** * Import a training */ public function importAction() { //ini_set('max_execution_time','300'); $errs = array(); $this->view->assign('pageTitle', t('Import a training')); // template redirect if ($this->getSanParam('download')) { return $this->importTrainingTemplateAction(); } if (!$this->hasACL('import_training')) { $this->doNoAccessError(); } //CSV STUFF $filename = $_FILES['upload']['tmp_name']; if ($filename) { require_once 'models/table/TrainingLocation.php'; require_once 'models/table/Person.php'; require_once 'models/table/TrainingToTrainer.php'; require_once 'models/table/PersonToTraining.php'; $trainingObj = new Training(); $personToTraining = new PersonToTraining(); while ($row = $this->_csv_get_row($filename)) { $cntz++; // todo remove $values = array(); if (!is_array($row)) { continue; } if (!isset($cols)) { $cols = $row; // first row is headers (fieldnames) continue; } if (!empty($row)) { // add $countValidFields = 0; foreach ($row as $i => $v) { if (empty($v) && $v !== '0') { continue; } if ($v == 'n/a') { // has to be able to process values from a data export $v = NULL; } $countValidFields++; if (strpos($v, "\n")) { // explode by newline then comma to give us arrays $v = explode("\n", $v); foreach ($v as $key => $value) { $delimiter = strpos($value, ','); if ($delimiter && $value[$delimiter - 1] != '\\') { // todo this doesnt really work, explode will still break it (supposed to be a \, handler (escaped comma), so you can use commas in fields) $values[$cols[$i]][] = explode(',', $this->sanitize($value)); } else { $values[$cols[$i]][] = $this->sanitize($value); } } } else { $delimiter = strpos($v, ','); if ($delimiter && $v[$delimiter - 1] != '\\') { $values[$cols[$i]] = explode(',', $this->sanitize($v)); } else { $values[$cols[$i]] = $this->sanitize($v); } } } } // all values are now in a hash ex: $values['training_id'] if ($countValidFields) { //validate if (isset($values['uuid'])) { unset($values['uuid']); } if (isset($values['id'])) { unset($values['id']); } if (isset($values['is_deleted'])) { unset($values['is_deleted']); } if (isset($values['created_by'])) { unset($values['created_by']); } if (isset($values['modified_by'])) { unset($values['modified_by']); } if (isset($values['timestamp_created'])) { unset($values['timestamp_created']); } if (isset($values['timestamp_updated'])) { unset($values['timestamp_updated']); } if (!$this->hasACL('approve_trainings')) { $values['is_approved'] = 0; } if (!$this->setting('module_approvals_enabled') == 0) { $values['is_approved'] = 1; } if ($values['training_start_date']) { $values['training_start_date'] = $this->_date_to_sql($values['training_start_date']); } if ($values['training_end_date']) { $values['training_end_date'] = $this->_date_to_sql($values['training_end_date']); } if ($values['training_length_interval'] == 'days') { $values['training_length_interval'] = 'day'; } if ($values['training_length_interval'] == 'weeks') { $values['training_length_interval'] = 'week'; } if ($values['training_length_interval'] == 'hours') { $values['training_length_interval'] = 'hour'; } // remap fields (field names differ vs actual column names on export/template for readibility thanks to some function in iTechTranslate) if ($values['training_title_phrase']) { $values['training_title_option_id'] = $values['training_title_phrase']; } if ($values['training_title']) { $values['training_title_option_id'] = $values['training_title']; } if ($values['custom3_phrase']) { $values['custom_3'] = $values['custom3_phrase']; } if ($values['custom4_phrase']) { $values['custom_4'] = $values['custom4_phrase']; } if ($values['custom5_phrase']) { $values['custom_5'] = $values['custom5_phrase']; } if ($values['refresher_phrase_option']) { $values['training_refresher_phrase'] = $values['refresher_phrase_option']; } if ($values['training_refresher_option_id']) { $values['training_refresher_phrase'] = $values['training_refresher_option_id']; } if ($values['language_phrase']) { $values['training_primary_language_option_id'] = $values['language_phrase']; } // required fields if (!$values['training_title_option_id']) { $values['training_title_option_id'] = 0; } if (!$values['comments']) { $values['comments'] = ''; } if (!$values['got_comments']) { $values['got_comments'] = ''; } if (!$values['objectives']) { $values['objectives'] = ''; } // training location $num_location_tiers = $this->setting('num_location_tiers'); $bSuccess = true; if (!isset($regionNames)) { $regionNames = array('', t('Region A (Province)'), t('Region B (Health District)'), t('Region C (Local Region)'), t('Region D'), t('Region E'), t('Region F'), t('Region G'), t('Region H'), t('Region I'), t('City')); } $location_id = 0; $training_location_id = $values['training_location_id'] ? $values['training_location_id'] : 0; // training_location_id and a default // validity check location name if ($training_location_id && $values['training_location_name']) { $dupe = new TrainingLocation(); $select = $dupe->select()->where('id =' . $training_location_id . ' and training_location_name = "' . $values['training_location_name'] . '"'); $training_location_id = ($a = $dupe->fetchRow($select)) ? $a->id : 0; // valid or doesnt match lets reset it and insert a new one this record was imported from another site } // insert new location $tier = 1; if (!$training_location_id && $values['training_location_name']) { for ($i = 1; $i < $num_location_tiers + 1; $i++) { // insert/find locations $location_name = $i == $num_location_tiers ? @$values[t('City')] : @$values[$regionNames[$i]]; // last one? if ($regionNames[$i] != t('City') && (empty($location_name) || $bSuccess == false)) { $bSuccess = false; continue; } else { // insert $location_id = Location::insertIfNotFound($location_name, $location_id, $tier); if (!$location_id) { $bSuccess = false; $errs[] = t('Error locating/creating region or city:') . ' ' . $location_name . ' ' . t('Training Location') . ': ' . $values['training_location_name']; break; } } $tier++; } if ($bSuccess && $location_id) { // we have a region id (location id), now save training_location // dupecheck $dupe = new TrainingLocation(); $select = $dupe->select()->where('location_id =' . $location_id . ' and training_location_name = "' . $values['training_location_name'] . '"'); if ($a = $dupe->fetchRow($select)) { $training_location_id = $a->id; } else { // save try { $trainingLocModel = new TrainingLocation(); $trainingLocationObj = $trainingLocModel->createRow(); $trainingLocationObj->training_location_name = $values['training_location_name']; $trainingLocationObj->location_id = $location_id; $training_location_id = $trainingLocationObj->save(); } catch (Exception $e) { $errored = 1; $errs[] = nl2br($e->getMessage()) . space . t('ERROR: The training location could not be saved.') . space . '"' . $values['training_location_name'] . '"'; } if (!$training_location_id) { $errored = 1; } } } if ($errored || !$bSuccess) { // couldn't save location $errs[] = t('Error locating/creating') . space . t('Training Location') . space . ': ' . $location_name . ' ' . t('Training Location') . ': ' . $values['training_location_name']; $errored = 0; $bSuccess = true; } } if ($training_location_id) { $values['training_location_id'] = $training_location_id; } // done, saved training location. // save try { // option tables / lookup tables // insert or get id, and remap col names from csv template name to actual column name, asdf_phrase to asdf_option_id // the logic is: if ($values['title_phrase']) $training->title_option_id = findOrCreate('training_title', $values['title_phrase']) ); if (isset($values['training_title_option_id'])) { $values['training_title_option_id'] = $this->_importHelperFindOrCreate('training_title_option', 'training_title_phrase', $values['training_title_option_id']); } if (isset($values['training_got_curriculum_phrase'])) { $values['training_got_curriculum_option_id'] = $this->_importHelperFindOrCreate('training_got_curriculum_option', 'training_got_curriculum_phrase', $values['training_got_curriculum_phrase']); } if (isset($values['training_level_phrase'])) { $values['training_level_option_id'] = $this->_importHelperFindOrCreate('training_level_option', 'training_level_phrase', $values['training_level_phrase']); } if (isset($values['custom1_phrase'])) { $values['training_custom_1_option_id'] = $this->_importHelperFindOrCreate('training_custom_1_option', 'custom1_phrase', $values['custom1_phrase']); } if (isset($values['custom2_phrase'])) { $values['training_custom_2_option_id'] = $this->_importHelperFindOrCreate('training_custom_2_option', 'custom2_phrase', $values['custom2_phrase']); } if (isset($values['training_organizer_phrase'])) { $values['training_organizer_option_id'] = $this->_importHelperFindOrCreate('training_organizer_option', 'training_organizer_phrase', $values['training_organizer_phrase']); } if (isset($values['training_method_phrase'])) { $values['training_method_option_id'] = $this->_importHelperFindOrCreate('training_method_option', 'training_method_phrase', $values['training_method_phrase']); } if (isset($values['training_primary_language_option_id'])) { $values['training_primary_language_option_id'] = $this->_importHelperFindOrCreate('trainer_language_option', 'language_phrase', $values['training_primary_language_option_id']); } if (isset($values['training_refresher_phrase']) && !is_array($values['training_refresher_phrase'])) { $values['training_refresher_option_id'] = $this->_importHelperFindOrCreate('training_refresher_option', 'refresher_phrase_option', $values['training_refresher_phrase']); } // participants, trainers $tableObj = $trainingObj->createRow(); $tableObj = ITechController::fillFromArray($tableObj, $values); $row_id = $tableObj->save(); // done save - we have a training id now // linked tables if ($row_id > 0) { $training_id = $row_id; $success[] = t('Successfully imported training #') . space . '<a href="' . Settings::$COUNTRY_BASE_URL . '/training/view/id/' . $training_id . '">' . $training_id . '</a>'; // multiOptionList tables if ($values['funding_phrase']) { $bSuccess = $this->_importHelperFindOrCreateMOLT('training_funding_option', 'funding_phrase', $values['funding_phrase'], 'funding', $training_id, $values['funding_amount']); if (!$bSuccess) { $errs[] = t('Training') . space . "#{$training_id} " . t('Some Data not imported') . " (funding)"; } } if ($values['pepfar_category_phrase']) { $bSuccess = $this->_importHelperFindOrCreateMOLT('training_pepfar_categories_option', 'pepfar_category_phrase', $values['pepfar_category_phrase'], 'pepfar', $training_id, $values['pepfar_duration_days']); if (!$bSuccess) { $errs[] = t('Training') . space . "#{$training_id} " . t('Some Data not imported') . " (PEPFAR)"; } } if ($values['training_refresher_phrase']) { $bSuccess = $this->_importHelperFindOrCreateMOLT('training_refresher_option', 'refresher_phrase_option', $values['training_refresher_phrase'], 'refresher', $training_id); if (!$bSuccess) { $errs[] = t('Training') . space . "#{$training_id} " . t('Some Data not imported') . " (refresher course)"; } } if ($values['training_topic_phrase']) { $bSuccess = $this->_importHelperFindOrCreateMOLT('training_topic_option', 'training_topic_phrase', $values['training_topic_phrase'], 'topic', $training_id); if (!$bSuccess) { $errs[] = t('Training') . space . "#{$training_id} " . t('Some Data not imported') . " (training topic)"; } } //unknown participants if (!empty($values['unknown participants'])) { $upTable = new ITechTable(array('name' => 'training_to_person_qualification_option')); foreach ($values['unknown participants'] as $i => $data) { if (empty($data) || $data == 'n/a') { continue; } try { # (this row is $data) row format: array( "2(na)"," 2(male)"," 2(female)"," "QualificationPhrase") $qual_id = $data[count($data) - 1]; $qual_id = trim($qual_id); if (!is_int($qual_id) && strpos($qual_id, '(na)') === false && strpos($qual_id, '(female)') === false && strpos($qual_id, '(male)') === false) { $qual_id = $this->_importHelperFindOrCreate('person_qualification_option', 'qualification_phrase', $qual_id); } else { $errs[] = 'Training #' . $row_id . space . t('You did not set a qualification for a group of unknown participants.'); $qual_id = 0; } $tablerow = $upTable->createRow(); $tablerow->id = null; $tablerow->training_id = $row_id; $tablerow->person_qualification_option_id = $qual_id ? $qual_id : 0; $tablerow->age_range_option_id = 0; // todo age_range_option_id #reference: $age_opts = OptionList::suggestionList('age_range_option',array('id','age_range_phrase'), false, 100, false); $tablerow->person_count_na = 0; $tablerow->person_count_male = 0; $tablerow->person_count_female = 0; foreach ($data as $v) { if (strpos($v, '(na)')) { $tablerow->person_count_na = trim(str_replace('(na)', '', $v)); } if (strpos($v, '(male)')) { $tablerow->person_count_male = trim(str_replace('(male)', '', $v)); } if (strpos($v, '(female)')) { $tablerow->person_count_female = trim(str_replace('(female)', '', $v)); } } if (!$tablerow->person_count_na && !$tablerow->person_count_male && !$tablerow->person_count_female) { continue; } //empty $tablerow->save(); } catch (Exception $e) { $errs[] = t('Error saving unknown participant information.') . space . 'Training #' . $row_id . space . 'Error: ' . $e->getMessage(); } } } //training_to_trainer foreach (array($values['trainers'], $values['participants']) as $imode => $personArr) { $persons = explode("\n", $personArr); if (empty($personArr)) { continue; } if (!is_array($personArr)) { $personArr = array($personArr); } foreach ($personArr as $tRow) { $tRow = trim($tRow); if ($tRow == '' || $tRow == 'n/a') { continue; } echo '<br>' . PHP_EOL . 'trainer [t|p]' . $imode . ' row @ training id #training_id: ' . print_r($tRow, true) . "<Br>" . PHP_EOL; if (is_array($tRow)) { if (count($tRow) == 4 && is_numeric($tRow[0])) { // some handling if it has a middle name or ID # attached or exported from training search $trainer_id = $tRow[0]; } else { if (count($tRow) == 3 && $this->setting('display_middle_name') == 0 && is_numeric($tRow[0])) { $trainer_id = $tRow[0]; } else { list($trainer_first, $trainer_middle, $trainer_last) = $tRow; } } // expects comma seperated list of names... } else { list($trainer_first, $trainer_middle, $trainer_last) = explode(' ', $tRow); } if ($trainer_middle && !$trainer_last) { $trainer_last = $trainer_middle; $trainer_middle = ''; } echo "trainer_first, trainer_middle, trainer_last = {$trainer_first}, {$trainer_middle}, {$trainer_last}"; $trainer_id = Person::tryFind($trainer_first, $trainer_middle, $trainer_last); echo " and trainer_id = {$trainer_id} <br>\n"; if (!$trainer_id) { $errs[] = t("Could not add user to training because they were not found in the system.") . space . t('Training') . " #{$training_id}: '{$trainer_first} {$trainer_middle} {$trainer_last}'"; continue; } echo " and trainer found, addTrainerToTraining({$trainer_id}, {$training_id},0)<br>\n"; if ($imode == 0) { TrainingToTrainer::addTrainerToTraining($trainer_id, $training_id, 0); } elseif ($imode == 1) { $personToTraining->addPersonToTraining($trainer_id, $training_id); } } } } } catch (Exception $e) { $errored = 1; $errs[] = nl2br($e->getMessage()) . ' ' . t('ERROR: The training data could not be saved.') . space . ($training_id ? t('Training') . space . "#{$training_id}" . space . t('Warning: Some data imported.') . space . t('Check Funding, PEPFAR, Topic, Refresher options and Participants and Trainers Data; or delete the training and try again.') : ''); } if (!$row_id) { $errored = 1; } } } // done processing rows $_POST['redirect'] = null; $status = ValidationContainer::instance(); if (empty($errored) && empty($errs)) { $stat = t('Your changes have been saved.'); } else { $stat = t('Error importing data. Some data may have been imported and some may not have.'); } foreach ($success as $errmsg) { $stat .= '<br>' . $errmsg; } foreach ($errs as $errmsg) { $stat .= '<br>' . 'Error: ' . htmlspecialchars($errmsg, ENT_QUOTES); } $status->setStatusMessage($stat); $this->view->assign('status', $status); } // done with import }
/** * Import a training location */ public function importLocationAction() { $this->view->assign('pageTitle', t('Import a training location')); require_once 'models/table/Location.php'; require_once 'models/table/TrainingLocation.php'; // template redirect if ($this->getSanParam('download')) { return $this->importLocationTemplateAction(); } if (!$this->hasACL('import_training_location')) { $this->doNoAccessError(); } //CSV STUFF $filename = $_FILES['upload']['tmp_name']; if ($filename) { $trainingLocationObj = new TrainingLocation(); $errs = array(); while ($row = $this->_csv_get_row($filename)) { $values = array(); if (!is_array($row)) { continue; } // sanity? if (!isset($cols)) { // set headers (field names) $cols = $row; // first row is headers (field names) continue; } $countValidFields = 0; if (!empty($row)) { // add foreach ($row as $i => $v) { // proccess each column if (empty($v) && $v !== '0') { continue; } if ($v == 'n/a') { // has to be able to process values from a data export $v = NULL; } $countValidFields++; $delimiter = strpos($v, ','); // is this field a comma seperated list too (or array)? if ($delimiter && $v[$delimiter - 1] != '\\') { // handle arrays as field values(Export), and comma seperated values(import manual entry), and strings or int $values[$cols[$i]] = explode(',', $this->sanitize($v)); } else { $values[$cols[$i]] = $this->sanitize($v); } } } // done now all fields are named and in $values['my_field'] if ($countValidFields) { //validate if (isset($values['uuid'])) { unset($values['uuid']); } if (isset($values['id'])) { unset($values['id']); } if (isset($values['is_deleted'])) { unset($values['is_deleted']); } if (isset($values['created_by'])) { unset($values['created_by']); } if (isset($values['modified_by'])) { unset($values['modified_by']); } if (isset($values['timestamp_created'])) { unset($values['timestamp_created']); } if (isset($values['timestamp_updated'])) { unset($values['timestamp_updated']); } //required if (empty($values['training_location_name'])) { $errs[] = t('Error adding training location, training location name cannot be empty.'); } //locations $num_location_tiers = $this->setting('num_location_tiers'); $bSuccess = true; $location_id = null; if ($values['location_id']) { $location_id = $values['location_id']; } $tier = 1; if (!$location_id) { for ($i = 0; $i <= $num_location_tiers; $i++) { // insert/find locations $r = 1 + $i; // first location field in csv row // could use this too: $values[t('Region A (Province)')] if (empty($row[$r]) || $bSuccess == false) { continue; } $location_id = Location::insertIfNotFound($row[$r], $location_id, $tier); if (!$location_id) { $bSuccess = false; break; } $tier++; } } if (!$bSuccess || !$location_id) { $errs[] = t('Error locating/creating region or city:') . ' ' . $row[$r] . ' ' . t('Training Location') . ': ' . $values['training_location_name']; continue; // couldnt save location } $values['location_id'] = $location_id; //dupecheck $dupe = new TrainingLocation(); $select = $dupe->select()->where('location_id =' . $location_id . ' and training_location_name = "' . $values['training_location_name'] . '"'); if ($dupe->fetchRow($select)) { $errs[] = t('The training location could not be saved. A training location with this name already exists in that location.') . ' ' . t('training location') . ': ' . $values['training_location_name']; $bSuccess = false; } if (!$bSuccess) { continue; } //save try { $tableObj = $trainingLocationObj->createRow(); $tableObj->training_location_name = $values['training_location_name']; $tableObj->location_id = $location_id; $row_id = $tableObj->save(); } catch (Exception $e) { $errored = 1; $errs[] = nl2br($e->getMessage()) . ' ' . t('ERROR: The training location could not be saved.'); } if (!$row_id) { $errored = 1; } //sucess - done } //loop } // done processing rows $_POST['redirect'] = null; $status = ValidationContainer::instance(); if (empty($errored) && empty($errs)) { $stat = t('Your changes have been saved.'); } else { $stat = t('Error importing data. Some data may have been imported and some may not have.'); } foreach ($errs as $errmsg) { $stat .= '<br>' . 'Error: ' . htmlspecialchars($errmsg, ENT_QUOTES); } $status->setStatusMessage($stat); $this->view->assign('status', $status); } // done with import }