function xls_import($id, $returl = '') { global $_CAMILA; require_once CAMILA_DIR . 'datagrid/form.class.php'; require_once CAMILA_DIR . 'datagrid/elements/form/hidden.php'; require_once CAMILA_DIR . 'datagrid/elements/form/filebox.php'; require_once CAMILA_DIR . 'datagrid/elements/form/static_listbox.php'; //if ($returl != '') // $form3 = new phpform('camilastep4', $returl); //else $form3 = new phpform('camilastep4', 'cf_worktable_wizard_step4.php'); $form3->submitbutton = camila_get_translation('camila.wizard.next'); $form3->drawrules = false; new form_hidden($form3, 'custom', $id); if ($returl != '') { new form_hidden($form3, 'returl', $_REQUEST['camila_returl']); } new form_filebox($form3, 'filename', camila_get_translation('camila.worktable.xls.choose'), 50, CAMILA_TMP_DIR); $sheet_list = ''; for ($i = 0; $i < 10; $i++) { if ($i > 0) { $sheet_list .= ','; } $sheet_list .= $i . ';' . ($i + 1); } new form_static_listbox($form3, 'sheetnum', camila_get_translation('camila.worktable.xls.sheetnum'), $sheet_list); $success = true; if ($form3->process()) { $filename = $form3->fields['filename']->value[0]; $sheetnum = $form3->fields['sheetnum']->value; $result = $_CAMILA['db']->Execute('select short_title, scriptname, tablename, filename, sheetnum from ' . CAMILA_TABLE_WORKT . ' where id=' . $_CAMILA['db']->qstr($id)); if ($result === false) { camila_error_page(camila_get_translation('camila.sqlerror') . ' ' . $_CAMILA['db']->ErrorMsg()); } $table = $result->fields['tablename']; $worktablename = $result->fields['short_title']; $worktablescript = $result->fields['scriptname']; if ($filename == '' && $result->fields['filename'] != '') { $filename = $result->fields['filename']; $sheetnum = $result->fields['sheetnum']; } if ($filename != '') { require_once CAMILA_LIB_DIR . 'php-excel-reader/excel_reader2.php'; $data = new Spreadsheet_Excel_Reader(CAMILA_TMP_DIR . '/' . $filename); $excelColNames = array(); $i = 0; while ($data->val(1, $i + 1, $sheetnum) != '') { $name = $data->val(1, $i + 1, $sheetnum); $excelColNames[$i] = camila_strtoupper_utf8(isUTF8($name) ? $name : utf8_encode($name)); $i++; } $result = $_CAMILA['db']->Execute('select * from ' . CAMILA_TABLE_WORKC . ' where (wt_id=' . $_CAMILA['db']->qstr($id) . ' and is_deleted<>' . $_CAMILA['db']->qstr('y') . ') order by sequence'); if ($result === false) { camila_error_page(camila_get_translation('camila.sqlerror') . ' ' . $_CAMILA['db']->ErrorMsg()); } $fields = array(); $types = array(); $defVals = array(); $forceCase = array(); $orig_types = array(); $fieldMapping = array(); $forceArr = camila_get_translation_array('camila.worktable.options.force'); $count = 0; while (!$result->EOF) { $colName = $result->fields['col_name']; $name = camila_strtoupper_utf8($result->fields['name']); $fieldMapping[$colName] = isUTF8($name) ? $name : utf8_encode($name); $fields[$count] = $colName; $types[$count] = $result->fields['type']; $orig_types[$count] = $result->fields['orig_type']; $defVals[$count] = $result->fields['default_value']; $forceCase[$count] = $result->fields['force_case']; $count++; $result->MoveNext(); } $successCount = 0; $failCount = 0; //db fields for ($i = 2; $i <= $data->rowcount($sheetnum); $i++) { $record = array(); $emptyrow = true; //db fields reset($fields); foreach ($fields as $k => $v) { //k Field position into database //k2 Position in Excel file $k2 = array_search($fieldMapping[$v], $excelColNames); //Is it in Excel file? if ($k2 !== false) { $excelColName = camila_strtoupper_utf8($data->value(1, $k2 + 1, $sheetnum)); //$excelColName = $v; $worktableColName = array_search($excelColName, $fieldMapping); $worktableColName = $v; if ($worktableColName != '') { if ($types[$k] == 'date' && $data->val($i, $k2 + 1, $sheetnum) != '') { $numValue = $data->sheets[$sheetnum]['cellsInfo'][$i][$k2 + 1]['raw']; $utcDays = floor($numValue - ($data->nineteenFour ? SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS1904 : SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS)); $utcValue = $utcDays * SPREADSHEET_EXCEL_READER_MSINADAY; $dateinfo = gmgetdate($utcValue); $fractionalDay = $numValue - floor($numValue) + 1.0E-7; // The .0000001 is to fix for php/excel fractional diffs $totalseconds = floor(SPREADSHEET_EXCEL_READER_MSINADAY * $fractionalDay); $secs = $totalseconds % 60; $totalseconds -= $secs; $hours = floor($totalseconds / (60 * 60)); $mins = floor($totalseconds / 60) % 60; $dt = date('Y-m-d', mktime($hours, $mins, $secs, $dateinfo["mon"], $dateinfo["mday"], $dateinfo["year"])); $record[$worktableColName] = $_CAMILA['db']->BindDate($dt); } elseif ($orig_types[$k] == 'number' && $data->sheets[$sheetnum]['cellsInfo'][$i][$k2 + 1]['raw'] != '') { $record[$worktableColName] = $data->sheets[$sheetnum]['cellsInfo'][$i][$k2 + 1]['raw']; } elseif ($types[$k] == 'hyperlink' && $data->hyperlink($i, $k2 + 1, $sheetnum) != '') { //$record[$worktableColName] = '<a href="' . $data->hyperlink($i, $k2+1, $sheetnum) . '" target="_blank">' . $data->value($i, $k2+1, $sheetnum) . '</a>'; $record[$worktableColName] = $data->hyperlink($i, $k2 + 1, $sheetnum); } else { $record[$worktableColName] = $data->value($i, $k2 + 1, $sheetnum); } if ($defVals[$k] != '' && $record[$worktableColName] == '') { $record[$worktableColName] = camila_parse_default_expression($defVals[$k], '_camila_seq_num_', true); } if ($record[$worktableColName] != '') { if ($forceCase[$k] == 'upper') { $record[$worktableColName] = mb_strtoupper($record[$worktableColName], 'UTF-8'); } if ($forceCase[$k] == 'lower') { $record[$worktableColName] = mb_strtolower($record[$worktableColName], 'UTF-8'); } $emptyrow = false; } } } else { if ($defVals[$k] != '') { $record[$fields[$k]] = camila_parse_default_expression($defVals[$k], '_camila_seq_num_', true); } } } if (!$emptyrow) { $now = $_CAMILA['db']->BindTimeStamp(gmdate("Y-m-d H:i:s", time())); $id = $_CAMILA['db']->GenID('worktableseq', 100000); foreach ($record as $k => $v) { $record[$k] = str_replace('_camila_seq_num_', $id, $v); } $record['id'] = $id; $record['created'] = $now; $record['created_by'] = $_CAMILA['user']; $record['created_src'] = 'import'; $record['created_by_surname'] = $_CAMILA['user_surname']; $record['created_by_name'] = $_CAMILA['user_name']; $record['last_upd'] = $now; $record['last_upd_by'] = $_CAMILA['user']; $record['last_upd_src'] = 'import'; $record['last_upd_by_surname'] = $_CAMILA['user_surname']; $record['last_upd_by_name'] = $_CAMILA['user_name']; $record['mod_num'] = 0; $insertSQL = $_CAMILA['db']->AutoExecute($table, $record, 'INSERT'); if (!$insertSQL) { //camila_information_text(camila_get_translation('camila.worktable.db.importerror')); $failCount++; $success = false; } else { $successCount++; } } } } camila_information_text(camila_get_translation('camila.worktable.db.importedrows') . ': ' . $successCount); camila_information_text(camila_get_translation('camila.worktable.db.skippedrows') . ': ' . $failCount); @unlink(CAMILA_TMP_DIR . '/' . $filename); } else { $result = $_CAMILA['db']->Execute('select tablename, filename, sheetnum from ' . CAMILA_TABLE_WORKT . ' where id=' . $_CAMILA['db']->qstr($id)); if ($result === false) { camila_error_page(camila_get_translation('camila.sqlerror') . ' ' . $_CAMILA['db']->ErrorMsg()); } $filename = $result->fields['filename']; // if ($filename != '') { $myText = new CHAW_text(camila_get_translation('camila.wizard.choosefileforimport')); $_CAMILA['page']->add_text($myText); $form3->draw(); $success = false; // } } if ($success) { if ($worktablename != '') { $myLink = new CHAW_link($worktablename, $worktablescript); $myLink->set_br(0); $_CAMILA['page']->add_link($myLink); $myText = new CHAW_text(' - ' . camila_get_translation('camila.worktable.db.importok')); $_CAMILA['page']->add_text($myText); } else { $myText = new CHAW_text(camila_get_translation('camila.wizard.configurationapplied')); $_CAMILA['page']->add_text($myText); } } }