/**
* 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
}
