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