[TikiWiki-commits] [Git][tikiwiki/tiki][27.x] [ENH] import-export formats: ability to join multiple tables with remote ODBC

Victor Emanouilov pushed to branch 27.x at Tiki Wiki CMS Groupware / Tiki


Commits:
9befe161 by Victor Emanouilov at 2025-08-14T09:50:38+03:00
[ENH] import-export formats: ability to join multiple tables with remote ODBC
---
* [FIX] use field id when permname is passed in trackerinput

* [ENH] import-export formats: ability to join multiple tables with remote ODBC sources and map columns to multiple tables; ItemLink sort option

See merge request tikiwiki/tiki!8296

- - - - -


6 changed files:

- lib/core/Tracker/Field/ItemLink.php
- lib/core/Tracker/Tabular/Manager.php
- lib/core/Tracker/Tabular/ODBCManager.php
- lib/core/Tracker/Tabular/Source/ODBCSourceEntry.php
- lib/smarty_tiki/FunctionHandler/TrackerInput.php
- templates/tabular/edit.tpl


Changes:

=====================================
lib/core/Tracker/Field/ItemLink.php
=====================================
@@ -144,6 +144,19 @@ class Tracker_Field_ItemLink extends \Tracker\Field\AbstractItemField implements
                             'value' => 'transfer'
                         ],
                     ],
+                    'displayFieldsListSort' => [
+                        'name' => tr('Default Sort Order'),
+                        'description' => tr('Sort the items in the dropdown list.'),
+                        'filter' => 'alpha',
+                        'options' => [
+                            'itemId' => tr('Item ID'),
+                            'formatted' => tr('Formatted Value'),
+                        ],
+                        'depends' => [
+                            'field' => 'displayFieldsListType',
+                            'value' => 'dropdown'
+                        ],
+                    ],
                     'trackerListOptions' => [
                         'name' => tr('Plugin TrackerList options'),
                         'description' => tr('Override one or more options of Plugin TrackerList to customize displayed table at item edit time (e.g. editable, tsfilters, etc.)'),
@@ -905,6 +918,9 @@ class Tracker_Field_ItemLink extends \Tracker\Field\AbstractItemField implements
                     $this->getOption('displayFieldsListFormat')
                 );
                 $list = $this->handleDuplicates($list);
+                if ($this->getOption('displayFieldsListSort') == 'formatted') {
+                    natsort($list);
+                }
             }
         } else {
             $list = TikiLib::lib('trk')->get_all_items(


=====================================
lib/core/Tracker/Tabular/Manager.php
=====================================
@@ -360,5 +360,30 @@ class Manager
             $odbc_config = $old_config;
             return;
         }
+        try {
+            if (! empty($odbc_config['join_tables'])) {
+                $odbc_config['join_tables'] = json_decode($odbc_config['join_tables'], true, 512, JSON_THROW_ON_ERROR);
+                if (! is_array($odbc_config['join_tables'])) {
+                    throw new Exception('invalid format');
+                }
+                foreach ($odbc_config['join_tables'] as $key => $val) {
+                    if (! is_string($key)) {
+                        throw new Exception('invalid format');
+                    }
+                    if (! is_array($val)) {
+                        throw new Exception('invalid format');
+                    }
+                    foreach ($val as $remote => $local) {
+                        if (! is_scalar($remote) || ! is_scalar($local)) {
+                            throw new Exception('invalid format');
+                        }
+                    }
+                }
+            }
+        } catch (Exception $e) {
+            Feedback::error(tr("Failed parsing Join Tables field: %0. Changes were not saved.", $e->getMessage()));
+            $odbc_config = $old_config;
+            return;
+        }
     }
 }


=====================================
lib/core/Tracker/Tabular/ODBCManager.php
=====================================
@@ -63,8 +63,21 @@ class ODBCManager
         if (! empty($this->config['value_mappings'])) {
             $fields = array_merge($fields, array_keys($this->config['value_mappings']));
         }
-        $select = implode('", "', $fields);
-        $sql = "SELECT \"{$select}\" FROM {$this->config['table']} WHERE 1=1";
+        foreach ($fields as $k => $v) {
+            if (! strstr($v, '.')) {
+                $fields[$k] = $this->config['table'] . '.' . $v;
+            }
+        }
+        $select = implode('", "', str_replace('.', '"."', $fields));
+        $sql = "SELECT \"{$select}\" FROM {$this->config['table']}";
+        if (! empty($this->config['join_tables'])) {
+            foreach ($this->config['join_tables'] as $table => $joinFields) {
+                $sql .= " LEFT JOIN \"" . addslashes($table) . "\" ON " . implode(' AND ', array_map(function ($k, $v) use ($table) {
+                    return "\"" . addslashes($table) . "\".\"" . addslashes($k) . "\" = \"{$this->config['table']}\".\"" . addslashes($v) . "\"";
+                }, array_keys($joinFields), array_values($joinFields)));
+            }
+        }
+        $sql .= " WHERE 1=1";
         $bind = [];
         if ($modifiedField && $lastImport) {
             $sql .= " AND \"{$modifiedField}\" >= ?";
@@ -110,8 +123,10 @@ class ODBCManager
             $exists = false;
             $id = null;
         }
+        $joinFields = [];
         if ($exists) {
             $row = $this->fillFieldsFromConfig($row);
+            $joinFields = $this->removeJoinFields($row);
             foreach (array_chunk($row, 50, true) as $chunk) {
                 unset($chunk[$pk]);
                 if (empty($chunk)) {
@@ -145,6 +160,7 @@ class ODBCManager
                 $row = $fullRow;
             }
             $row = $this->fillFieldsFromConfig($row);
+            $joinFields = $this->removeJoinFields($row);
             $row = array_filter($row, function ($val) {
                 if (is_bool($val) || is_int($val) || is_float($val)) {
                     return true;
@@ -174,6 +190,40 @@ class ODBCManager
             }
         }
         $result['entry'] = $this->reverseMapFieldsFromConfig($result['entry']);
+        if ($joinFields) {
+            $tableFields = [];
+            foreach ($joinFields as $k => $v) {
+                $parts = explode('.', $k);
+                $table = array_shift($parts);
+                $field = array_shift($parts);
+                $tableFields[$table][$field] = $v;
+            }
+            foreach ($tableFields as $table => $fields) {
+                if (! empty($this->config['join_tables'][$table])) {
+                    foreach ($this->config['join_tables'][$table] as $remote => $local) {
+                        $idVal = $result['entry'][$local] ?? null;
+                        if ($idVal) {
+                            $sql = "SELECT * FROM \"" . addslashes($table) . "\" WHERE \"" . addslashes($remote) . "\" = ?";
+                            $rs = odbc_prepare($conn, $sql);
+                            odbc_execute($rs, [$idVal]);
+                            $result = odbc_fetch_array($rs);
+                            if ($result) {
+                                $sql = "UPDATE \"" . addslashes($table) . "\" SET " . implode(', ', array_map(function ($k) {
+                                    return "\"{$k}\" = ?";
+                                }, array_keys($fields))) . " WHERE \"" . addslashes($remote) . "\" = ?";
+                                $rs = odbc_prepare($conn, $sql);
+                                odbc_execute($rs, array_merge(array_values($fields), [$idVal]));
+                            } else {
+                                $fields[$remote] = $idVal;
+                                $sql = "INSERT INTO \"" . addslashes($table) . "\" (\"" . implode('","', array_keys($fields)) . "\") VALUES (" . implode(',', array_fill(0, count($fields), '?')) . ")";
+                                $rs = odbc_prepare($conn, $sql);
+                                odbc_execute($rs, array_values($fields));
+                            }
+                        }
+                    }
+                }
+            }
+        }
         $this->stopErrorHandler();
         return $result;
     }
@@ -514,4 +564,16 @@ class ODBCManager
         }
         return $row;
     }
+
+    private function removeJoinFields(array &$row): array
+    {
+        $joinFields = [];
+        foreach ($row as $k => $v) {
+            if (strstr($k, '.')) {
+                $joinFields[$k] = $v;
+                unset($row[$k]);
+            }
+        }
+        return $joinFields;
+    }
 }


=====================================
lib/core/Tracker/Tabular/Source/ODBCSourceEntry.php
=====================================
@@ -20,12 +20,7 @@ class ODBCSourceEntry implements SourceEntryInterface
         $values = [];
         $fields = $column->getRemoteFields();
         foreach ($fields as $field) {
-            if (isset($this->data[$field])) {
-                $value = $this->data[$field];
-            } else {
-                $value = null;
-            }
-            $values[] = $value;
+            $values[] = $this->getFieldDataFromRemoteField($field);
         }
         return $column->render($allow_multiple ? $values : $values[0], ['allow_multiple' => $allow_multiple]);
     }
@@ -34,8 +29,9 @@ class ODBCSourceEntry implements SourceEntryInterface
     {
         $fields = $column->getRemoteFields();
         foreach ($fields as $field) {
-            if (isset($this->data[$field])) {
-                return $this->data[$field];
+            $value = $this->getFieldDataFromRemoteField($field);
+            if ($value !== null) {
+                return $value;
             }
         }
         return null;
@@ -47,13 +43,27 @@ class ODBCSourceEntry implements SourceEntryInterface
         if (count($remoteFields) > 1) {
             $entry = [];
             foreach ($remoteFields as $remoteField) {
-                if (isset($this->data[$remoteField])) {
-                    $entry[] = $this->data[$remoteField];
+                $value = $this->getFieldDataFromRemoteField($remoteField);
+                if ($value !== null) {
+                    $entry[] = $value;
                 }
             }
         } else {
-            $entry = $this->data[$column->getRemoteField()] ?? null;
+            $entry = $this->getFieldDataFromRemoteField($column->getRemoteField());
         }
         $column->parseInto($info, $entry, $this->data);
     }
+
+    protected function getFieldDataFromRemoteField($remoteField)
+    {
+        if (isset($this->data[$remoteField])) {
+            $value = $this->data[$remoteField];
+        } elseif (strstr($remoteField, '.')) {
+            $parts = explode('.', $remoteField);
+            $value = $this->data[array_pop($parts)] ?? null;
+        } else {
+            $value = null;
+        }
+        return $value;
+    }
 }


=====================================
lib/smarty_tiki/FunctionHandler/TrackerInput.php
=====================================
@@ -18,7 +18,10 @@ class TrackerInput extends Base
 
         if (isset($params['fieldId'])) {
             $field = $trklib->get_tracker_field($params['fieldId']);
-            $field['ins_id'] = "ins_{$params['fieldId']}";
+            if (empty($field)) {
+                return tr('Field %0 not found', $params['fieldId']);
+            }
+            $field['ins_id'] = "ins_{$field['fieldId']}";
             $handler = $trklib->get_field_handler($field, $item);
             if ($handler) {
                 $field = array_merge($field, $handler->getFieldData());


=====================================
templates/tabular/edit.tpl
=====================================
@@ -73,6 +73,17 @@
                         <textarea class="form-control" type="text" name="odbc[value_mappings]">{if $odbc_config.value_mappings}{$odbc_config.value_mappings|json_encode}{/if}</textarea>
                     </div>
                 </div>
+                <div class="mb-3 row">
+                    <label class="col-form-label col-sm-2 offset-sm-1">
+                        {tr}Join Other Tables{/tr}
+                        <a class="tikihelp text-info" title="{tr}Join Other Tables:{/tr} {tr}Specify SQL join conditions to use to include other tables and their data in the result set. JSON format: { table: { remote_field_name: local_field_name}, ...}{/tr}">
+                            {icon name=information}
+                        </a>
+                    </label>
+                    <div class="col-sm-9">
+                        <textarea class="form-control" type="text" name="odbc[join_tables]">{if $odbc_config.join_tables}{$odbc_config.join_tables|json_encode}{/if}</textarea>
+                    </div>
+                </div>
                 <div class="mb-3 row">
                     <label class="col-form-label col-sm-2 offset-sm-1">{tr}Sync deletes{/tr}</label>
                     <div class="col-sm-9">



View it on GitLab: https://gitlab.com/tikiwiki/tiki/-/commit/9befe161f183e548a15777740c89e488114b0997

-- 
View it on GitLab: https://gitlab.com/tikiwiki/tiki/-/commit/9befe161f183e548a15777740c89e488114b0997
You're receiving this email because of your account on gitlab.com.

_______________________________________________
TikiWiki-cvs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/tikiwiki-cvs