/**
* Import deviations found in the survey to the database from a spreadsheet
*
* @param int $id entity id
*
* @return void
*/
private function _handle_import($id)
{
$id = (int) $id;
if (!$id) {
throw new Exception('uicondition_survey::_handle_import() - missing id');
}
$step = phpgw::get_var('step', 'int', 'REQUEST');
$sheet_id = phpgw::get_var('sheet_id', 'int', 'REQUEST');
$sheet_id = $sheet_id ? $sheet_id : phpgw::get_var('selected_sheet_id', 'int', 'REQUEST');
if (!$step) {
if ($cached_file = phpgwapi_cache::session_get('property', 'condition_survey_import_file')) {
phpgwapi_cache::session_clear('property', 'condition_survey_import_file');
unlink($cached_file);
unset($cached_file);
}
}
if ($start_line = phpgw::get_var('start_line', 'int', 'REQUEST')) {
phpgwapi_cache::system_set('property', 'import_sheet_start_line', $start_line);
} else {
$start_line = phpgwapi_cache::system_get('property', 'import_sheet_start_line');
$start_line = $start_line ? $start_line : 1;
}
if ($columns = phpgw::get_var('columns')) {
phpgwapi_cache::system_set('property', 'import_sheet_columns', $columns);
} else {
$columns = phpgwapi_cache::system_get('property', 'import_sheet_columns');
$columns = $columns && is_array($columns) ? $columns : array();
}
if ($step > 1) {
$cached_file = phpgwapi_cache::session_get('property', 'condition_survey_import_file');
}
if ($step == 1 || isset($_FILES['import_file']['tmp_name'])) {
$file = $_FILES['import_file']['tmp_name'];
$cached_file = "{$file}_temporary_import_file";
// save a copy to survive multiple steps
file_put_contents($cached_file, file_get_contents($file));
phpgwapi_cache::session_set('property', 'condition_survey_import_file', $cached_file);
$step = 1;
// Add the file to documents
$bofiles = CreateObject('property.bofiles');
$to_file = "{$bofiles->fakebase}/condition_survey/{$id}/" . str_replace(' ', '_', $_FILES['import_file']['name']);
$bofiles->vfs->rm(array('string' => $to_file, 'relatives' => array(RELATIVE_NONE)));
$bofiles->create_document_dir("condition_survey/{$id}");
$bofiles->vfs->override_acl = 1;
$bofiles->vfs->cp(array('from' => $_FILES['import_file']['tmp_name'], 'to' => $to_file, 'relatives' => array(RELATIVE_NONE | VFS_REAL, RELATIVE_ALL)));
$bofiles->vfs->override_acl = 0;
unset($bofiles);
}
$tabs = array();
switch ($step) {
case 0:
$active_tab = 'step_1';
$lang_submit = lang('continue');
$tabs['step_1'] = array('label' => lang('choose file'), 'link' => '#step_1');
$tabs['step_2'] = array('label' => lang('choose sheet'), 'link' => null);
$tabs['step_3'] = array('label' => lang('choose start line'), 'link' => null);
$tabs['step_4'] = array('label' => lang('choose columns'), 'link' => null);
break;
case 1:
$active_tab = 'step_2';
$lang_submit = lang('continue');
$tabs['step_1'] = array('label' => lang('choose file'), 'link' => self::link(array('menuaction' => 'property.uicondition_survey.import', 'id' => $id, 'step' => 0, 'sheet_id' => $sheet_id, 'start_line' => $start_line)));
$tabs['step_2'] = array('label' => lang('choose sheet'), 'link' => '#step_2');
$tabs['step_3'] = array('label' => lang('choose start line'), 'link' => null);
$tabs['step_4'] = array('label' => lang('choose columns'), 'link' => null);
break;
case 2:
$active_tab = 'step_3';
$lang_submit = lang('continue');
$tabs['step_1'] = array('label' => lang('choose file'), 'link' => self::link(array('menuaction' => 'property.uicondition_survey.import', 'id' => $id, 'step' => 0, 'sheet_id' => $sheet_id, 'start_line' => $start_line)));
$tabs['step_2'] = array('label' => lang('choose sheet'), 'link' => self::link(array('menuaction' => 'property.uicondition_survey.import', 'id' => $id, 'step' => 1, 'sheet_id' => $sheet_id, 'start_line' => $start_line)));
$tabs['step_3'] = array('label' => lang('choose start line'), 'link' => '#step_3');
$tabs['step_4'] = array('label' => lang('choose columns'), 'link' => null);
break;
case 3:
$active_tab = 'step_4';
$lang_submit = lang('import');
$tabs['step_1'] = array('label' => lang('choose file'), 'link' => self::link(array('menuaction' => 'property.uicondition_survey.import', 'id' => $id, 'step' => 0, 'sheet_id' => $sheet_id, 'start_line' => $start_line)));
$tabs['step_2'] = array('label' => lang('choose sheet'), 'link' => self::link(array('menuaction' => 'property.uicondition_survey.import', 'id' => $id, 'step' => 1, 'sheet_id' => $sheet_id, 'start_line' => $start_line)));
$tabs['step_3'] = array('label' => lang('choose start line'), 'link' => self::link(array('menuaction' => 'property.uicondition_survey.import', 'id' => $id, 'step' => 2, 'sheet_id' => $sheet_id, 'start_line' => $start_line)));
$tabs['step_4'] = array('label' => lang('choose columns'), 'link' => '#step_4');
break;
/*
case 4://temporary
phpgwapi_cache::session_clear('property', 'condition_survey_import_file');
unlink($cached_file);
$GLOBALS['phpgw']->redirect_link('/index.php',array('menuaction' => 'property.uicondition_survey.import', 'id' =>$id, 'step' => 0));
break;
*/
}
//-----------
if (!$step) {
phpgwapi_cache::session_clear('property', 'condition_survey_import_file');
unlink($cached_file);
} else {
if ($cached_file) {
phpgw::import_class('phpgwapi.phpexcel');
try {
$objPHPExcel = PHPExcel_IOFactory::load($cached_file);
$AllSheets = $objPHPExcel->getSheetNames();
$sheets = array();
if ($AllSheets) {
foreach ($AllSheets as $key => $sheet) {
$sheets[] = array('id' => $key, 'name' => $sheet, 'selected' => $sheet_id == $key);
}
}
$objPHPExcel->setActiveSheetIndex((int) $sheet_id);
} catch (Exception $e) {
if ($e) {
phpgwapi_cache::message_set($e->getMessage(), 'error');
phpgwapi_cache::session_clear('property', 'condition_survey_import_file');
unlink($cached_file);
}
}
}
}
$survey = $this->bo->read_single(array('id' => $id, 'view' => $mode == 'view'));
$rows = $objPHPExcel->getActiveSheet()->getHighestDataRow();
$highestColumm = $objPHPExcel->getActiveSheet()->getHighestDataColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumm);
$i = 0;
$html_table = '<table border="1">';
if ($rows > 1 && $step == 2) {
$cols = array();
for ($j = 0; $j < $highestColumnIndex; $j++) {
$cols[] = $this->getexcelcolumnname($j);
}
$html_table .= "<tr><th align = 'center'>" . lang('start') . "</th><th align='center'>" . implode("</th><th align='center'>", $cols) . '</th></tr>';
foreach ($objPHPExcel->getActiveSheet()->getRowIterator() as $row) {
if ($i > 20) {
break;
}
$i++;
$row_key = $i;
$_checked = '';
if ($start_line == $row_key) {
$_checked = 'checked="checked"';
}
$_radio = "[{$row_key}]<input id=\"start_line\" type =\"radio\" {$_checked} name=\"start_line\" value=\"{$row_key}\">";
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
$row_values = array();
foreach ($cellIterator as $cell) {
if (!is_null($cell)) {
$row_values[] = $cell->getCalculatedValue();
}
}
$html_table .= "<tr><td><pre>{$_radio}</pre></td><td>" . implode('</td><td>', $row_values) . '</td></tr>';
}
echo '</table>';
} else {
if ($rows > 1 && $step == 3) {
$_options = array('_skip_import_' => 'Utelates fra import/implisitt', 'import_type' => 'import type', 'building_part' => 'bygningsdels kode', 'descr' => 'Tilstandbeskrivelse', 'title' => 'Tiltak/overskrift', 'condition_degree' => 'Tilstandsgrad', 'condition_type' => 'Konsekvenstype', 'consequence' => 'Konsekvensgrad', 'probability' => 'Sannsynlighet', 'due_year' => 'År (innen)', 'amount_investment' => 'Beløp investering', 'amount_operation' => 'Beløp drift', 'amount_potential_grants' => 'Potensial for offentlig støtte');
$custom = createObject('phpgwapi.custom_fields');
$attributes = $custom->find('property', '.project.request', 0, '', '', '', true, true);
foreach ($attributes as $attribute) {
$_options["custom_attribute_{$attribute['id']}"] = $attribute['input_text'];
}
phpgw::import_class('phpgwapi.sbox');
for ($j = 0; $j < $highestColumnIndex; $j++) {
$_column = $this->getexcelcolumnname($j);
$_value = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($j, $start_line)->getCalculatedValue();
$selected = isset($columns[$_column]) && $columns[$_column] ? $columns[$_column] : '';
$_listbox = phpgwapi_sbox::getArrayItem("columns[{$_column}]", $selected, $_options, true);
$html_table .= "<tr><td>[{$_column}] {$_value}</td><td>{$_listbox}</td><tr>";
}
} else {
if ($rows > 1 && $step == 4) {
$rows = $objPHPExcel->getActiveSheet()->getHighestDataRow();
$rows = $rows ? $rows + 1 : 0;
$import_data = array();
for ($i = $start_line; $i < $rows; $i++) {
$_result = array();
foreach ($columns as $_row_key => $_value_key) {
if ($_value_key != '_skip_import_') {
$_result[$_value_key] = $objPHPExcel->getActiveSheet()->getCell("{$_row_key}{$i}")->getCalculatedValue();
}
}
$import_data[] = $_result;
}
if ($import_data) {
try {
$this->bo->import($survey, $import_data);
} catch (Exception $e) {
if ($e) {
phpgwapi_cache::message_set($e->getMessage(), 'error');
}
}
}
// $msg = "'{$cached_file}' contained " . count($import_data) . " lines";
// phpgwapi_cache::message_set($msg, 'message');
}
}
}
$html_table .= '</table>';
if (isset($survey['location_code']) && $survey['location_code']) {
$survey['location_data'] = execMethod('property.solocation.read_single', $survey['location_code']);
}
$bolocation = CreateObject('property.bolocation');
$location_data = $bolocation->initiate_ui_location(array('values' => $survey['location_data'], 'type_id' => 2, 'lookup_type' => 'view2', 'tenant' => false, 'lookup_entity' => array(), 'entity_data' => isset($survey['p']) ? $survey['p'] : ''));
$data = array('lang_submit' => $lang_submit, 'survey' => $survey, 'location_data2' => $location_data, 'step' => $step + 1, 'sheet_id' => $sheet_id, 'start_line' => $start_line, 'html_table' => $html_table, 'sheets' => array('options' => $sheets), 'tabs' => $GLOBALS['phpgw']->common->create_tabs($tabs, $active_tab));
$GLOBALS['phpgw_info']['flags']['app_header'] = lang('property') . '::' . lang('condition survey import');
self::render_template_xsl(array('condition_survey_import'), $data);
}
