* fix bug 7930: return the resulting selected array in the methods buildStaticInfoSel...
[TYPO3CMS/Extensions/static_info_tables.git] / class.tx_staticinfotables_div.php
1 <?php
2 /***************************************************************
3 * Copyright notice
4 *
5 * (c) 2004-2008 René Fritz (r.fritz@colorcube.de)
6 * All rights reserved
7 *
8 * This script is part of the Typo3 project. The Typo3 project is
9 * free software; you can redistribute it and/or modify
10 * it under the terms of the GNU General Public License as published by
11 * the Free Software Foundation; either version 2 of the License, or
12 * (at your option) any later version.
13 *
14 * The GNU General Public License can be found at
15 * http://www.gnu.org/copyleft/gpl.html.
16 *
17 * This script is distributed in the hope that it will be useful,
18 * but WITHOUT ANY WARRANTY; without even the implied warranty of
19 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
20 * GNU General Public License for more details.
21 *
22 * This copyright notice MUST APPEAR in all copies of the script!
23 ***************************************************************/
24 /**
25 * Misc functions to access the static info tables
26 *
27 * $Id$
28 *
29 * @author René Fritz <r.fritz@colorcube.de>
30 * @package TYPO3
31 */
32 /**
33 * [CLASS/FUNCTION INDEX of SCRIPT]
34 *
35 *
36 *
37 * 57: class tx_staticinfotables_div
38 * 68: function getTCAlabelField($table, $loadTCA=TRUE, $lang='', $local=FALSE)
39 * 117: function isoCodeType($isoCode)
40 * 139: function getIsoCodeField($table, $isoCode, $loadTCA=TRUE, $index=0)
41 * 165: function getTCAsortField($table, $loadTCA=TRUE)
42 * 177: function getCurrentLanguage()
43 * 210: function getCurrentSystemLanguage($where='')
44 * 243: function getCollateLocale()
45 * 276: function getTitleFromIsoCode($table, $isoCode, $lang='', $local=FALSE)
46 * 336: function replaceMarkersInSQL($sql, $table, $row)
47 * 378: function selectItemsTCA($params)
48 * 475: function updateHotlist ($table, $indexValue, $indexField='', $app='')
49 * 545: function &fetchCountries($country, $iso2='', $iso3='', $isonr='')
50 * 590: function quoteJSvalue($value, $inScriptTags = false)
51 * 612: function loadTcaAdditions($ext_keys)
52 *
53 * TOTAL FUNCTIONS: 14
54 * (This index is automatically created/updated by the extension "extdeveval")
55 *
56 */
57 class tx_staticinfotables_div {
58
59 /**
60 * Returns a label field for the current language
61 *
62 * @param string table name
63 * @param boolean If set (default) the TCA definition of the table should be loaded with t3lib_div::loadTCA(). It will be needed to set it to false if you call this function from inside of tca.php
64 * @param string language to be used
65 * @param boolean If set, we are looking for the "local" title field
66 * @return string field name
67 */
68 function getTCAlabelField($table, $loadTCA=TRUE, $lang='', $local=FALSE) {
69 global $TCA, $LANG, $TSFE;
70
71 if (is_object($LANG)) {
72 $csConvObj = $LANG->csConvObj;
73 } elseif (is_object($TSFE)) {
74 $csConvObj = $TSFE->csConvObj;
75 }
76
77 if (!is_object($csConvObj)) {
78 include_once(PATH_t3lib.'class.t3lib_cs.php');
79 // The object may not exist yet, so we need to create it now.
80 $csConvObj = &t3lib_div::makeInstance('t3lib_cs');
81 }
82
83 $labelFields = array();
84 if($table && is_array($GLOBALS['TYPO3_CONF_VARS']['EXTCONF'][STATIC_INFO_TABLES_EXTkey]['tables'][$table]['label_fields'])) {
85 if ($loadTCA) {
86 t3lib_div::loadTCA($table);
87
88 // get all extending TCAs
89 if (is_array($GLOBALS['TYPO3_CONF_VARS']['EXTCONF'][STATIC_INFO_TABLES_EXTkey]['extendingTCA'])) {
90 tx_staticinfotables_div::loadTcaAdditions($GLOBALS['TYPO3_CONF_VARS']['EXTCONF'][STATIC_INFO_TABLES_EXTkey]['extendingTCA']);
91 }
92 }
93
94 $lang = $lang ? $lang : tx_staticinfotables_div::getCurrentLanguage();
95
96 foreach ($GLOBALS['TYPO3_CONF_VARS']['EXTCONF'][STATIC_INFO_TABLES_EXTkey]['tables'][$table]['label_fields'] as $field) {
97 if ($local) {
98 $labelField = str_replace ('##', 'local', $field);
99 } else {
100 $labelField = str_replace ('##', $csConvObj->conv_case('utf-8',$lang,'toLower'), $field);
101 }
102 if (is_array($TCA[$table]['columns'][$labelField])) {
103 $labelFields[] = $labelField;
104 }
105 }
106 }
107 return $labelFields;
108 }
109
110
111 /**
112 * Returns the type of an iso code: nr, 2, 3
113 *
114 * @param string iso code
115 * @return string iso code type
116 */
117 function isoCodeType($isoCode) {
118 $type = '';
119 if (t3lib_div::testInt($isoCode)) {
120 $type = 'nr';
121 } elseif (strlen($isoCode) == 2) {
122 $type = '2';
123 } elseif (strlen($isoCode) == 3) {
124 $type = '3';
125 }
126 return $type;
127 }
128
129
130 /**
131 * Returns a iso code field for the passed table and iso code
132 *
133 * @param string table name
134 * @param string iso code
135 * @param boolean If set (default) the TCA definition of the table should be loaded with t3lib_div::loadTCA(). It will be needed to set it to FALSE if you call this function from inside of tca.php
136 * @param integer index in the table's isocode_field array in the global variable
137 * $GLOBALS['TYPO3_CONF_VARS']['EXTCONF'][STATIC_INFO_TABLES_EXTkey]['tables']
138 * @return string field name
139 */
140 function getIsoCodeField($table, $isoCode, $bLoadTCA=TRUE, $index=0) {
141 global $TCA;
142 $rc = FALSE;
143
144 if ($isoCode && $table && (($isoCodeField = $GLOBALS['TYPO3_CONF_VARS']['EXTCONF'][STATIC_INFO_TABLES_EXTkey]['tables'][$table]['isocode_field'][$index]) != '')) {
145 if ($bLoadTCA) {
146 t3lib_div::loadTCA($table);
147 }
148 $type = tx_staticinfotables_div::isoCodeType($isoCode);
149 $isoCodeField = str_replace ('##', $type, $isoCodeField);
150
151 if (is_array($TCA[$table]['columns'][$isoCodeField])) {
152 $rc = $isoCodeField;
153 }
154 }
155 return $rc;
156 }
157
158
159 /**
160 * Returns a sort field for the current language
161 *
162 * @param string table name
163 * @param boolean If set (default) the TCA definition of the table should be loaded
164 * @return string field name
165 */
166 function getTCAsortField($table, $loadTCA=TRUE) {
167 $labelFields = tx_staticinfotables_div::getTCAlabelField($table, $loadTCA);
168
169 return $labelFields[0];
170 }
171
172
173 /**
174 * Returns the current language as iso-2-alpha code
175 *
176 * @return string 'DE', 'EN', 'DK', ...
177 */
178 function getCurrentLanguage() {
179 global $LANG, $TSFE, $TYPO3_DB;
180
181 if (is_object($LANG)) {
182 $langCodeT3 = $LANG->lang;
183 $csConvObj = $LANG->csConvObj;
184 } elseif (is_object($TSFE)) {
185 $langCodeT3 = $TSFE->lang;
186 $csConvObj = $TSFE->csConvObj;
187 } else {
188 return 'EN';
189 }
190
191 $res = $TYPO3_DB->exec_SELECTquery(
192 'lg_iso_2,lg_country_iso_2',
193 'static_languages',
194 'lg_typo3='.$TYPO3_DB->fullQuoteStr($langCodeT3,'static_languages')
195 );
196 while ($row = $TYPO3_DB->sql_fetch_assoc($res)) {
197 $lang = $row['lg_iso_2'].($row['lg_country_iso_2']?'_'.$row['lg_country_iso_2']:'');
198 }
199 $TYPO3_DB->sql_free_result($res);
200
201 $rc = $lang ? $lang : $csConvObj->conv_case('utf-8',$langCodeT3,'toUpper');
202 return $rc;
203 }
204
205 /**
206 * Returns the row of the current system language
207 *
208 * @param [type] $where: ...
209 * @return array row in the sys_language table
210 */
211 function getCurrentSystemLanguage($where='') {
212 global $LANG, $TSFE, $TYPO3_DB;
213
214 $rc = array();
215
216 if (is_object($LANG)) {
217 $langCodeT3 = $LANG->lang;
218 } elseif (is_object($TSFE)) {
219 $langCodeT3 = $TSFE->lang;
220 } else {
221 return $rc;
222 }
223
224 $res = $TYPO3_DB->exec_SELECTquery(
225 'sys_language.uid',
226 'sys_language LEFT JOIN static_languages ON sys_language.static_lang_isocode=static_languages.uid',
227 'static_languages.lg_typo3='.$TYPO3_DB->fullQuoteStr($langCodeT3,'static_languages').
228 $where
229 );
230 while($row = $TYPO3_DB->sql_fetch_assoc($res)) {
231 $rc[$row['uid']] = $row;
232 }
233
234 $TYPO3_DB->sql_free_result($res);
235 return $rc;
236 }
237
238 /*
239 *
240 * Returns the locale to used when sorting labels
241 *
242 * @return string locale
243 */
244 function getCollateLocale() {
245 global $LANG, $TSFE, $TYPO3_DB;
246
247 if (is_object($LANG)) {
248 $langCodeT3 = $LANG->lang;
249 } elseif (is_object($TSFE)) {
250 $langCodeT3 = $TSFE->lang;
251 } else {
252 return 'C';
253 }
254
255 $res = $TYPO3_DB->exec_SELECTquery(
256 'lg_collate_locale',
257 'static_languages',
258 'lg_typo3='.$TYPO3_DB->fullQuoteStr($langCodeT3,'static_languages')
259 );
260 while ($row = $TYPO3_DB->sql_fetch_assoc($res)) {
261 $locale = $row['lg_collate_locale'];
262 }
263 $TYPO3_DB->sql_free_result($res);
264 return $locale ? $locale : 'C';
265 }
266
267
268 /**
269 * Fetches short title from an iso code
270 *
271 * @param string table name
272 * @param string iso code
273 * @param string language code - if not set current default language is used
274 * @param boolean local name only - if set local title is returned
275 * @return string short title
276 */
277 function getTitleFromIsoCode($table, $isoCode, $lang='', $local=FALSE) {
278 global $TSFE, $TYPO3_DB;
279
280 $title = '';
281 $titleFields = tx_staticinfotables_div::getTCAlabelField($table, TRUE, $lang, $local);
282 if (count ($titleFields)) {
283 $prefixedTitleFields = array();
284 foreach ($titleFields as $titleField) {
285 $prefixedTitleFields[] = $table.'.'.$titleField;
286 }
287 $fields = implode(',', $prefixedTitleFields);
288 $whereClause = '';
289 if (!is_array($isoCode)) {
290 $isoCode = array($isoCode);
291 }
292 $index = 0;
293 foreach ($isoCode as $index => $code) {
294 if ($code != '') {
295 $tmpField = tx_staticinfotables_div::getIsoCodeField($table, $code, TRUE, $index);
296 $tmpValue = $TYPO3_DB->fullQuoteStr($code,$table);
297 if ($tmpField && $tmpValue) {
298 $whereClause .= ($index?' AND ':'').$table.'.'.$tmpField.' = '.$tmpValue;
299 }
300 }
301 }
302 if (is_object($TSFE)) {
303 $enableFields = $TSFE->sys_page->enableFields($table);
304 } else {
305 $enableFields = t3lib_BEfunc::deleteClause($table);
306 }
307
308 $res = $TYPO3_DB->exec_SELECTquery(
309 $fields,
310 $table,
311 $whereClause.$enableFields
312 );
313 if ($row = $TYPO3_DB->sql_fetch_assoc($res)) {
314 foreach ($titleFields as $titleField) {
315 if ($row[$titleField]) {
316 $title = $row[$titleField];
317 break;
318 }
319 }
320 }
321 $TYPO3_DB->sql_free_result($res);
322 }
323
324 return $title;
325 }
326
327
328 /**
329 * Replaces any dynamic markers in a SQL statement.
330 *
331 * @param string The SQL statement with dynamic markers.
332 * @param string Name of the table.
333 * @param array Database row.
334 * @return string SQL query with dynamic markers subsituted.
335 */
336 function replaceMarkersInSQL($sql, $table, $row) {
337
338 $TSconfig = t3lib_BEfunc::getTCEFORM_TSconfig($table, $row);
339
340 /* Replace references to specific fields with value of that field */
341 if (strstr($sql,'###REC_FIELD_')) {
342 $sql_parts = explode('###REC_FIELD_',$sql);
343 while(list($kk,$vv)=each($sql_parts)) {
344 if ($kk) {
345 $sql_subpart = explode('###',$vv,2);
346 $sql_parts[$kk]=$TSconfig['_THIS_ROW'][$sql_subpart[0]].$sql_subpart[1];
347 }
348 }
349 $sql = implode('',$sql_parts);
350 }
351
352 /* Replace markers with TSConfig values */
353 $sql = str_replace('###THIS_UID###',intval($TSconfig['_THIS_UID']),$sql);
354 $sql = str_replace('###THIS_CID###',intval($TSconfig['_THIS_CID']),$sql);
355 $sql = str_replace('###SITEROOT###',intval($TSconfig['_SITEROOT']),$sql);
356 $sql = str_replace('###PAGE_TSCONFIG_ID###',intval($TSconfig[$field]['PAGE_TSCONFIG_ID']),$sql);
357 $sql = str_replace('###PAGE_TSCONFIG_IDLIST###',$GLOBALS['TYPO3_DB']->cleanIntList($TSconfig[$field]['PAGE_TSCONFIG_IDLIST']),$sql);
358 $sql = str_replace('###PAGE_TSCONFIG_STR###',$GLOBALS['TYPO3_DB']->quoteStr($TSconfig[$field]['PAGE_TSCONFIG_STR'], $table),$sql);
359
360 return $sql;
361 }
362
363
364 /**
365 * Function to use in own TCA definitions
366 * Adds additional select items
367 *
368 * items reference to the array of items (label,value,icon)
369 * config The config array for the field.
370 * TSconfig The "itemsProcFunc." from fieldTSconfig of the field.
371 * table Table name
372 * row Record row
373 * field Field name
374 *
375 * @param array itemsProcFunc data array:
376 * @return void The $items array may have been modified
377 */
378 function selectItemsTCA($params) {
379 global $TCA;
380
381 $where = '';
382 $config = &$params['config'];
383 $table = $config['itemsProcFunc_config']['table'];
384 $tcaWhere = $config['itemsProcFunc_config']['where'];
385 if ($tcaWhere) {
386 $where = tx_staticinfotables_div::replaceMarkersInSQL($tcaWhere, $params['table'], $params['row']);
387 }
388
389 if ($table) {
390 $indexField = $config['itemsProcFunc_config']['indexField'];
391 $indexField = $indexField ? $indexField : 'uid';
392
393 $lang = strtolower(tx_staticinfotables_div::getCurrentLanguage());
394 $titleFields = tx_staticinfotables_div::getTCAlabelField($table, TRUE, $lang);
395 $prefixedTitleFields = array();
396 foreach ($titleFields as $titleField) {
397 $prefixedTitleFields[] = $table.'.'.$titleField;
398 }
399 $fields = $table.'.'.$indexField.','.implode(',', $prefixedTitleFields);
400
401 if ($config['itemsProcFunc_config']['prependHotlist']) {
402
403 $limit = $config['itemsProcFunc_config']['hotlistLimit'];
404 $limit = $limit ? $limit : '8';
405 $app = $config['itemsProcFunc_config']['hotlistApp'];
406 $app = $app ? $app : TYPO3_MODE;
407
408 $res = $GLOBALS['TYPO3_DB']->exec_SELECT_mm_query(
409 $fields,
410 $table,
411 'tx_staticinfotables_hotlist',
412 '', // $foreign_table
413 'AND tx_staticinfotables_hotlist.tablenames='.$GLOBALS['TYPO3_DB']->fullQuoteStr($table,'tx_staticinfotables_hotlist').' AND tx_staticinfotables_hotlist.application='.$GLOBALS['TYPO3_DB']->fullQuoteStr($app,'tx_staticinfotables_hotlist'),
414 '',
415 'tx_staticinfotables_hotlist.sorting DESC', // $orderBy
416 $limit
417 );
418
419 $cnt = 0;
420 $rows = array();
421 while ($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
422
423 foreach ($titleFields as $titleField) {
424 if ($row[$titleField]) {
425 $rows[$row[$indexField]] = $row[$titleField];
426 break;
427 }
428 }
429 $cnt++;
430 }
431 $GLOBALS['TYPO3_DB']->sql_free_result($res);
432
433 if (!isset($config['itemsProcFunc_config']['hotlistSort']) || $config['itemsProcFunc_config']['hotlistSort']) {
434 asort ($rows);
435 }
436
437 foreach ($rows as $index => $title) {
438 $params['items'][] = array($title, $index, '');
439 $cnt++;
440 }
441 if($cnt && !$config['itemsProcFunc_config']['hotlistOnly']) {
442 $params['items'][] = array('--------------', '', '');
443 }
444 }
445
446 // Set ORDER BY:
447 $orderBy = $titleFields[0];
448
449 if(!$config['itemsProcFunc_config']['hotlistOnly']) {
450 $res = $GLOBALS['TYPO3_DB']->exec_SELECTquery($fields, $table, '1=1'.$where.t3lib_BEfunc::deleteClause($table), '', $orderBy);
451 while ($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
452 foreach ($titleFields as $titleField) {
453 if ($row[$titleField]) {
454 $params['items'][] = array($row[$titleField], $row[$indexField], '');
455 break;
456 }
457 }
458 }
459 $GLOBALS['TYPO3_DB']->sql_free_result($res);
460 }
461 }
462 }
463
464
465 /**
466 * Updates the hotlist table.
467 * This means that a hotlist entry will be created or the counter of an existing entry will be increased
468 *
469 * @param string table name: static_countries, ...
470 * @param string value of the following index field
471 * @param string the field which holds the value and is an index field: uid (default) or one of the iso code fields which are also unique
472 * @param string This indicates a counter group. Default is TYPO3_MOD (BE or FE). If you want a unique hotlist for your application you can provide here a name (e.g. extension key)
473 * @return void
474 */
475 function updateHotlist ($table, $indexValue, $indexField='', $app='') {
476
477 if ($table && $indexValue) {
478 $indexField = $indexField ? $indexField : 'uid';
479 $app = $app ? $app : TYPO3_MODE;
480
481 if ($indexField=='uid') {
482 $uid = $indexValue;
483
484 } else {
485 // fetch original record
486 $fields = array();
487 $fields[$indexField] = $indexField;
488 $fields['uid'] = 'uid';
489
490 $res = $GLOBALS['TYPO3_DB']->exec_SELECTquery(implode(',',$fields), $table, $indexField.'='.$GLOBALS['TYPO3_DB']->fullQuoteStr($indexValue,$table).t3lib_BEfunc::deleteClause($table));
491 if ($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
492 $uid = $row['uid'];
493 }
494 $GLOBALS['TYPO3_DB']->sql_free_result($res);
495 }
496
497 if ($uid) {
498 // update record from hotlist table
499 $newRow = array('sorting' => 'sorting+1');
500 // the dumb update function does not allow to use sorting+1 - that's why this trick is necessary
501
502 $GLOBALS['TYPO3_DB']->sql_query(str_replace('"sorting+1"', 'sorting+1', $GLOBALS['TYPO3_DB']->UPDATEquery(
503 'tx_staticinfotables_hotlist',
504 'uid_local='.$uid.
505 ' AND application='.$GLOBALS['TYPO3_DB']->fullQuoteStr($app,'tx_staticinfotables_hotlist').
506 ' AND tablenames='.$GLOBALS['TYPO3_DB']->fullQuoteStr($table,'tx_staticinfotables_hotlist').
507 t3lib_BEfunc::deleteClause('tx_staticinfotables_hotlist'),
508 $newRow)));
509
510 if (!$GLOBALS['TYPO3_DB']->sql_affected_rows()) {
511 // insert new hotlist entry
512 $row = array(
513 'uid_local' => $uid,
514 'tablenames' => $table,
515 'application' => $app,
516 'sorting' => 1,
517 );
518 $res = $GLOBALS['TYPO3_DB']->exec_INSERTquery('tx_staticinfotables_hotlist', $row);
519 $GLOBALS['TYPO3_DB']->sql_free_result($res);
520 }
521 }
522 }
523 }
524
525
526 /**
527 * Get a list of countries by specific parameters or parts of names of countries
528 * in different languages. Parameters might be left empty.
529 *
530 * @param string a name of the country or a part of it in any language
531 * @param string ISO alpha-2 code of the country
532 * @param string ISO alpha-3 code of the country
533 * @param array Database row.
534 * @return array Array of rows of country records
535 */
536 function &fetchCountries($country, $iso2='', $iso3='', $isonr='') {
537 global $TYPO3_DB;
538
539 $rcArray = array();
540 $where = '';
541
542 $table = 'static_countries';
543 if ($country != '') {
544 $value = $TYPO3_DB->fullQuoteStr(trim('%'.$country.'%'),$table);
545 $where = 'cn_official_name_local LIKE '.$value.' OR cn_official_name_en LIKE '.$value.' OR cn_short_local LIKE '.$value;
546 }
547
548 if ($isonr != '') {
549 $where = 'cn_iso_nr='.$TYPO3_DB->fullQuoteStr(trim($isonr),$table);
550 }
551
552 if ($iso2 != '') {
553 $where = 'cn_iso_2='.$TYPO3_DB->fullQuoteStr(trim($iso2),$table);
554 }
555
556 if ($iso3 !='') {
557 $where = 'cn_iso_3='.$TYPO3_DB->fullQuoteStr(trim($iso3),$table);
558 }
559
560 if ($where != '') {
561 $res = $TYPO3_DB->exec_SELECTquery('*', $table, $where);
562
563 if ($res) {
564 while ($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
565 $rcArray[] = $row;
566 }
567 }
568 $GLOBALS['TYPO3_DB']->sql_free_result($res);
569 }
570 return $rcArray;
571 }
572
573
574 /**
575 * Quotes a string for usage as JS parameter. Depends wheter the value is used in script tags (it doesn't need/must not get htmlspecialchar'ed in this case)
576 *
577 * @param string The string to encode.
578 * @param boolean If the values get's used in <script> tags.
579 * @return string The encoded value already quoted
580 */
581 function quoteJSvalue($value, $inScriptTags = false) {
582 global $TSFE;
583
584 $value = addcslashes($value, '"'.chr(10).chr(13));
585 if (!$inScriptTags) {
586
587 $charset = $TSFE->renderCharset;
588 $value = htmlspecialchars($value,ENT_COMPAT,$charset);
589 }
590 return '"'.$value.'"';
591 }
592
593
594 /**
595 * loadTcaAdditions($ext_keys)
596 *
597 * See: div extension
598 * It has been copied here in order not to depend on this class library only for this function.
599 *
600 * @param array extension keys which have TCA additions to load
601 * @return void
602 */
603 function loadTcaAdditions($ext_keys){
604 global $_EXTKEY, $TCA;
605
606 //Merge all ext_keys
607 if (is_array($ext_keys)) {
608 for($i = 0; $i < sizeof($ext_keys); $i++) {
609 if (t3lib_extMgm::isLoaded($ext_keys[$i])) {
610 //Include the ext_table
611 $_EXTKEY = $ext_keys[$i];
612 include(t3lib_extMgm::extPath($ext_keys[$i]).'ext_tables.php');
613 }
614 }
615 }
616 }
617 }
618
619
620 if (defined('TYPO3_MODE') && $GLOBALS['TYPO3_CONF_VARS'][TYPO3_MODE]['XCLASS']['ext/static_info_tables/class.tx_staticinfotables_div.php']) {
621 include_once($GLOBALS['TYPO3_CONF_VARS'][TYPO3_MODE]['XCLASS']['ext/static_info_tables/class.tx_staticinfotables_div.php']);
622 }
623 ?>