function xls_read($sequence = -1)
{
global $_CAMILA;
if ($this->interactive) {
$filename = $_REQUEST['configurator_filename'];
$sheetnum = $_REQUEST['configurator_sheet'] - 1;
$name = $_REQUEST['configurator_wtname'];
$desc = $_REQUEST['configurator_wtdesc'];
$this->db = $_CAMILA['db'];
} else {
$filename = $this->filename;
$sheetnum = $this->sheetnum;
$name = 'name';
$desc = 'desc';
}
$success = true;
$result = $this->db->Execute('select max(id) as id from ' . CAMILA_TABLE_WORKT);
if ($result === false) {
camila_error_page(camila_get_translation('camila.sqlerror') . ' ' . $this->db->ErrorMsg());
}
$id = intval($result->fields['id']) + 1;
$record['id'] = $id;
$record['status'] = 'd';
$record['active'] = 'n';
$record['filename'] = basename($filename);
$record['sheetnum'] = $sheetnum;
$record['scriptname'] = 'cf_worktable' . $id . '.php';
$record['short_title'] = $name;
$record['full_title'] = $desc;
$record['tablename'] = CAMILA_TABLE_WORKP . $id;
$record['sequence'] = $sequence > 0 ? $sequence : $id;
$insertSQL = $this->db->AutoExecute(CAMILA_TABLE_WORKT, $record, 'INSERT');
if (!$insertSQL) {
camila_information_text(camila_get_translation('camila.worktable.db.error'));
$success = false;
}
if ($filename != '') {
require_once CAMILA_LIB_DIR . 'php-excel-reader/excel_reader2.php';
if ($this->interactive) {
$data = new Spreadsheet_Excel_Reader(CAMILA_TMP_DIR . '/' . $filename);
} else {
$data = new Spreadsheet_Excel_Reader($filename);
}
$fmt = str_replace(array('d', 'm', 'y'), array('dd', 'mm', 'yyyy'), strtolower($_CAMILA['date_format']));
$datefmt = preg_replace('/[^a-z0-9]/', '', $fmt);
$i = 1;
while ($data->val(1, $i, $sheetnum) != '') {
$cols[$i - 1] = $data->val(1, $i, $sheetnum);
//print_r($data->sheets[$sheetnum]['cellsInfo']);
for ($j = 1; $j <= $data->rowcount($sheetnum); $j++) {
$curr = $data->type($j, $i, $sheetnum);
if ($data->sheets[$sheetnum]['cellsInfo'][$j][$i]['hyperlink']['link'] != '') {
$curr = 'hyperlink';
}
if ($curr != '') {
$safefmt = strtolower(preg_replace('/[^a-z0-9]/', '', $data->read16bitstring($data->sheets[$sheetnum]['cellsInfo'][$j][$i]['format'], 0)));
//echo $data->read16bitstring($data->sheets[$sheetnum]['cellsInfo'][$j][$i]['format'],0);
if ($curr == 'unknown' && $safefmt == $datefmt) {
$curr = 'date';
}
$types[$i - 1] = $curr;
continue;
}
}
$i++;
}
if (!$this->interactive || $sheetnum + 2 <= count($data->sheets) && $this->is_configuration_sheet($data, $sheetnum + 1)) {
$colNum = count($cols);
$fieldArr = array();
for ($i = 0; $i < $colNum; $i++) {
//echo $data->val(2, 2 + $i, $sheetnum + 1);
$name = $data->val(1, 2 + $i, $sheetnum + 1);
$sequence = $data->val(2, 2 + $i, $sheetnum + 1);
$nameAbbrev = $data->val(3, 2 + $i, $sheetnum + 1);
$type = $data->val(4, 2 + $i, $sheetnum + 1);
$listboxOptions = $data->val(5, 2 + $i, $sheetnum + 1);
$maxLength = $data->val(6, 2 + $i, $sheetnum + 1);
$required = $data->val(7, 2 + $i, $sheetnum + 1);
$defaultValue = $data->val(8, 2 + $i, $sheetnum + 1);
$readonly = $data->val(9, 2 + $i, $sheetnum + 1);
$visible = $data->val(10, 2 + $i, $sheetnum + 1);
$forceCase = $data->val(11, 2 + $i, $sheetnum + 1);
$mustBeUnique = $data->val(12, 2 + $i, $sheetnum + 1);
$fieldOptions = $data->val(13, 2 + $i, $sheetnum + 1);
$autosuggestWorktableName = $data->val(14, 2 + $i, $sheetnum + 1);
$autosuggestWorktableColName = $data->val(15, 2 + $i, $sheetnum + 1);
$name = isUTF8($name) ? $name : utf8_encode($name);
$sequence = isUTF8($sequence) ? $sequence : utf8_encode($sequence);
$nameAbbrev = isUTF8($nameAbbrev) ? $nameAbbrev : utf8_encode($nameAbbrev);
$type = isUTF8($type) ? $type : utf8_encode($type);
$listboxOptions = isUTF8($listboxOptions) ? $listboxOptions : utf8_encode($listboxOptions);
$maxLength = isUTF8($maxLength) ? $maxLength : utf8_encode($maxLength);
$required = isUTF8($required) ? $required : utf8_encode($required);
$defaultValue = isUTF8($defaultValue) ? $defaultValue : utf8_encode($defaultValue);
$readonly = isUTF8($readonly) ? $readonly : utf8_encode($readonly);
$visible = isUTF8($visible) ? $visible : utf8_encode($visible);
$forceCase = isUTF8($forceCase) ? $forceCase : utf8_encode($forceCase);
$mustBeUnique = isUTF8($mustBeUnique) ? $mustBeUnique : utf8_encode($mustBeUnique);
$fieldOptions = isUTF8($fieldOptions) ? $fieldOptions : utf8_encode($fieldOptions);
$autosuggestWorktableName = isUTF8($autosuggestWorktableName) ? $autosuggestWorktableName : utf8_encode($autosuggestWorktableName);
$autosuggestWorktableColName = isUTF8($autosuggestWorktableColName) ? $autosuggestWorktableColName : utf8_encode($autosuggestWorktableColName);
if ($this->interactive) {
$yesNoArr = camila_get_translation_array('camila.worktable.options.noyes');
$fieldTypeArr = camila_get_translation_array('camila.worktable.options.fieldtype');
$forceArr = camila_get_translation_array('camila.worktable.options.force');
$orderDirArr = camila_get_translation_array('camila.worktable.options.order.dir');
} else {
$yesNoArr = $this->get_translation_array('camila.worktable.options.noyes');
$fieldTypeArr = $this->get_translation_array('camila.worktable.options.fieldtype');
$forceArr = $this->get_translation_array('camila.worktable.options.force');
$orderDirArr = $this->get_translation_array('camila.worktable.options.order.dir');
}
$record = array();
$record['wt_id'] = $id;
$record['applied_to_database'] = 'n';
$record['is_deleted'] = 'n';
//$record['orig_name'] = preg_replace("/[\n\r]/", "", $value);
//$record['orig_type'] = $types[$key];
$record['type'] = array_search($type, $fieldTypeArr);
$record['listbox_options'] = $listboxOptions;
$record['name'] = preg_replace("/[\n\r]/", ' ', $name);
$record['col_name'] = $this->get_field_name($name);
$record['name_abbrev'] = $nameAbbrev;
$record['readonly'] = array_search($readonly, $yesNoArr);
$record['must_be_unique'] = array_search($mustBeUnique, $yesNoArr);
$record['visible'] = array_search($visible, $yesNoArr);
$record['required'] = array_search($required, $yesNoArr);
$record['sequence'] = $sequence;
$record['maxlength'] = $maxLength;
$record['size'] = $this->default_size[$record['type']];
$record['force_case'] = array_search($forceCase, $forceArr);
$record['default_value'] = $defaultValue;
$record['field_options'] = $fieldOptions;
$record['autosuggest_wt_name'] = $autosuggestWorktableName;
$record['autosuggest_wt_colname'] = $autosuggestWorktableColName;
$insertSQL = $this->db->AutoExecute(CAMILA_TABLE_WORKC, $record, 'INSERT');
if (!$insertSQL) {
camila_information_text(camila_get_translation('camila.worktable.db.error'));
$success = false;
}
$fieldArr[$record['col_name']] = $record['name'];
}
$shortTitle = $data->val(18, 2, $sheetnum + 1);
$fullTitle = $data->val(19, 2, $sheetnum + 1);
$orderField = $data->val(20, 2, $sheetnum + 1);
$orderDir = $data->val(21, 2, $sheetnum + 1);
$canUpdate = $data->val(22, 2, $sheetnum + 1);
$canInsert = $data->val(23, 2, $sheetnum + 1);
$canDelete = $data->val(24, 2, $sheetnum + 1);
$category = $data->val(25, 2, $sheetnum + 1);
$shortTitle = isUTF8($shortTitle) ? $shortTitle : utf8_encode($shortTitle);
$fullTitle = isUTF8($fullTitle) ? $fullTitle : utf8_encode($fullTitle);
$orderField = isUTF8($orderField) ? $orderField : utf8_encode($orderField);
$orderDir = isUTF8($orderDir) ? $orderDir : utf8_encode($orderDir);
$canUpdate = isUTF8($canUpdate) ? $canUpdate : utf8_encode($canUpdate);
$canInsert = isUTF8($canInsert) ? $canInsert : utf8_encode($canInsert);
$canDelete = isUTF8($canDelete) ? $canDelete : utf8_encode($canDelete);
$category = isUTF8($category) ? $category : utf8_encode($category);
$record = array();
$record['id'] = $id;
$record['short_title'] = $shortTitle;
$record['full_title'] = $fullTitle;
$record['order_field'] = array_search($orderField, $fieldArr);
$record['order_dir'] = array_search($orderDir, $orderDirArr);
$record['canupdate'] = array_search($canUpdate, $yesNoArr);
$record['caninsert'] = array_search($canInsert, $yesNoArr);
$record['candelete'] = array_search($canDelete, $yesNoArr);
$record['category'] = $category;
$updateSQL = $this->db->AutoExecute(CAMILA_TABLE_WORKT, $record, 'UPDATE', 'id=' . $this->db->qstr($id));
if (!$updateSQL) {
camila_information_text(camila_get_translation('camila.worktable.db.error'));
$success = false;
}
$j = 0;
while ($data->val(18 + $j, 3, $sheetnum + 1) != '') {
$title = $data->val(18 + $j, 3, $sheetnum + 1);
$title = isUTF8($title) ? $title : utf8_encode($title);
$url = $data->val(18 + $j, 4, $sheetnum + 1);
$url = isUTF8($url) ? $url : utf8_encode($url);
$record['id'] = $this->db->GenID(CAMILA_APPLICATION_PREFIX . 'bookmarkseq', 10000);
$record['title'] = $title;
$record['base_url'] = 'cf_worktable' . $id . '.php';
$record['url'] = $record['base_url'];
if ($url != '') {
$record['url'] .= '?filter=' . urlencode($url);
}
$record['lang'] = $_REQUEST['lang'];
$record['sequence'] = $j + 1;
$insertSQL = $this->db->AutoExecute(CAMILA_APPLICATION_PREFIX . 'camila_bookmarks', $record, 'INSERT');
if (!$insertSQL) {
camila_information_text(camila_get_translation('camila.worktable.db.error'));
$success = false;
}
$j++;
}
} else {
$count = 1;
foreach ($cols as $key => $value) {
$record = array();
$record['wt_id'] = $id;
$record['applied_to_database'] = 'n';
$record['is_deleted'] = 'n';
$record['orig_name'] = preg_replace("/[\n\r]/", "", $value);
$record['orig_type'] = $types[$key];
if ($types[$key] == 'number') {
$record['type'] = 'integer';
} elseif ($types[$key] == 'date') {
$record['type'] = 'date';
} elseif ($types[$key] == 'hyperlink') {
$record['type'] = 'hyperlink';
} else {
$record['type'] = 'string';
}
$record['col_name'] = $this->get_field_name($value);
$name = preg_replace("/[\n\r]/", ' ', $value);
$record['name'] = !isUTF8($name) ? utf8_encode($name) : $name;
$record['name_abbrev'] = $record['name'];
$record['readonly'] = 'n';
$record['must_be_unique'] = 'n';
$record['visible'] = 'y';
$record['required'] = 'n';
$record['sequence'] = $count;
$record['maxlength'] = $this->default_maxlength[$record['type']];
$record['size'] = $this->default_size[$record['type']];
$insertSQL = $this->db->AutoExecute(CAMILA_TABLE_WORKC, $record, 'INSERT');
if (!$insertSQL) {
camila_information_text(camila_get_translation('camila.worktable.db.error'));
$success = false;
}
$count++;
}
}
if ($success && $this->interactive) {
camila_information_text(camila_get_translation('camila.worktable.xls.successful'));
}
} else {
if ($interactive) {
$myText = new CHAW_text(camila_get_translation('camila.wizard.filecardcreated'));
$myText->set_br(2);
$_CAMILA['page']->add_text($myText);
}
}
if ($this->interactive) {
$form3 = new phpform('camila', 'cf_worktable_wizard_step2.php', HAW_METHOD_GET);
$form3->submitbutton = camila_get_translation('camila.wizard.next');
$form3->drawrules = false;
new form_hidden($form3, 'custom', $id);
$form3->process();
$form3->draw();
}
return $id;
}
