se/SE/se-lib/TypespecialVariable.php

<?php

Lib::loadClass('TypespecialBase');
Lib::loadClass('SqlQueryWhereBuilder');

class TypespecialVariable extends TypespecialBase {

	private $fldID;
	private $fldName;

	public function __construct($fldID, $fldName) {
		$this->fldID = $fldID;
		$this->fldName = $fldName;
	}

	public static function getInstance($fldID, $fldName) {
		switch ($fldName) {
			case 'A_ADM_COMPANY':
			case 'A_CLASSIFIED':
			case 'K_OD_KOGO':
			case 'OD_KOGO_ADRES':
			case 'M_DISTRIBUTOR':
			case '__CONNECTIONS':
			case '__NESTED_GROUPS':
			case '__USER_GROUPS':
			case '__TELBOXES':
			case '__TELBOXES_NAME':
			case '__DEALS_SALES_TELBOXES_NAME':
			case '__ZALICZKI_NAJEMCOW__NAJEMCA':
			case '__ZASOB':
			case '__PROCES':
			case '__COMPANIES':
			case 'DEFAULT_ACL_GROUP':
			case 'VERSION_GIT':
			case '__USER_ID':
			case '__USER_LOGIN':
			case 'A_EXEC_GROUP':
			// case 'L_APPOITMENT_USER':
				return new TypespecialVariable($fldID, $fldName);
				break;
		}
		return null;
	}

	/**
	 * @returns [{id:int, param_out:str, exports:[]}]
	 */
	public function getValuesWithExports($query, $params = array()) {
		$items = $this->_getValues($query, false, $params);

		return $items;
	}

	public function getValuesByIds($tblId, $ids) {
		return null;
	}

	public function getEditSelectedValuesByIds($tblId, $id, $fieldName, $fieldValue) {
		$items = $this->_getValues($fieldValue, true);
		$values = array();
		foreach ($items as $vItem) {
			$values[$vItem->param_out] = $vItem->param_out;
		}
		return $values;
	}

	public function hGetFormItem($acl, $fieldNamespace, $tblID, $fName, $selValue = '', $params = array(), $record = null) {
		$jsonAllowCreate = 'true';
		switch ($this->fldName) {
			case 'L_APPOITMENT_USER':
			case 'A_ADM_COMPANY':
			case 'A_CLASSIFIED':
			case 'DEFAULT_ACL_GROUP':
			case 'VERSION_GIT':
			case '__USER_ID':
			case '__USER_LOGIN':
				$jsonAllowCreate = 'false';
				break;
		}
		if (array_key_exists('allowCreate', $params)) $jsonAllowCreate = ($params['allowCreate'])? 'true' : 'false';

		$tsValue = V::get('typespecialValue', '', $params);
		if (!empty($selValue) && !empty($tsValue)) {
			$tsValue = "{$selValue}: {$tsValue}"; // TODO: only in TypespecialVariable - skip in Typespecial
		}

		$options = [];
		if (!empty($selValue)) {
			$tsVal = (!empty($tsValue))? $tsValue : $selValue;
			//$out .= '<option value="' . $selValue . '" selected="selected" typespecial="'.$tsVal.'">' . $tsVal . '</option>';
			$out .= '<option value="' . $selValue . '" selected="selected">' . $tsVal . '</option>';
			$options[] = [
				'id' => $selValue,
				'name' => ($tsValue)? $tsValue : $selValue
			];
		}

		return [ 'P5UI__Typespecial', array_filter([
			'idField' => $this->fldID,
			'fieldNamespace' => $fieldNamespace,
			'fieldName' => $fName,
			'frmFldName' => V::get('formFieldName', $fName, $params),
			'create' => $jsonAllowCreate,
			'ajaxDataUrlBase' => V::get('ajaxDataUrlBase', "index-ajax.php?_cls=TableAjax&_zasobID={$tblID}&_task=TYPESPECIAL&fldID={$this->fldID}", $params),
			'options' => $options,
			'type' => "TypespecialVariable",
			'preload' => false,
		], function ($value) { return null !== $value; }),
			(!empty($options))
				? [ [ 'option', [ 'value' => $options[0]['id'] ], $options[0]['name'] ] ]
				: []
		];
	}

	public function showFormItem($tblID, $fName, $selValue = '', $params = array(), $record = null) {
		$out = '';

		$jsonAllowCreate = 'true';
		switch ($this->fldName) {
			case 'L_APPOITMENT_USER':
			case 'A_ADM_COMPANY':
			case 'A_CLASSIFIED':
			case 'DEFAULT_ACL_GROUP':
			case 'VERSION_GIT':
			case '__USER_ID':
			case '__USER_LOGIN':
				$jsonAllowCreate = 'false';
				break;
			default:
				$jsonAllowCreate = 'true';
		}
		if (isset($params['allowCreate'])) {
			$jsonAllowCreate = ($params['allowCreate'])? 'true' : 'false';
		}

		$jsonPreload = 'false';
		switch ($this->fldName) {
			case 'VERSION_GIT':
				//$jsonPreload = 'true';
				break;
			default:
				$jsonPreload = 'false';
		}

		$tsValue = V::get('typespecialValue', '', $params);
		if (!empty($selValue) && !empty($tsValue)) {
			$tsValue = "{$selValue}: {$tsValue}";
		}

		$optionsJson = 'null';
		$out .= '<div class="typepsecial">';
		$outPlaceholder = '';
		if ('' != ($paramPlaceholder = V::get('placeholder', '', $params))) {
			$outPlaceholder = ' placeholder="' . $paramPlaceholder . '"';
		}
		$out .= '<select id="ts-' . $fName . '"' . $outPlaceholder . '>';
			if (!empty($selValue)) {
				$tsVal = (!empty($tsValue))? $tsValue : $selValue;
				//$out .= '<option value="' . $selValue . '" selected="selected" typespecial="'.$tsVal.'">' . $tsVal . '</option>';
				$out .= '<option value="' . $selValue . '" selected="selected">' . $tsVal . '</option>';
				$optionsJson = new stdClass();
				$optionsJson->id = $selValue;
				$optionsJson->name = ($tsValue)? $tsValue : $selValue;
				$optionsJson = array($optionsJson);
				$optionsJson = json_encode($optionsJson);
			}
		$out .= '</select>';
		$out .= '</div>';

		$ajaxDataUrlBase = "index-ajax.php?_cls=TableAjax&_zasobID={$tblID}&_task=TYPESPECIAL&fldID={$this->fldID}";
		$ajaxDataUrlBase = V::get('ajaxDataUrlBase', $ajaxDataUrlBase, $params);

		$frmFldName = V::get('formFieldName', $fName, $params);

		$out .= '<script>' . "
jQuery(document).ready(function(){
	var fldNode=jQuery('#{$fName}'), tsNode=jQuery('#ts-{$fName}');
	if (!fldNode && !tsNode) {
		return;
	}

	fldNode.attr('name', '');
	fldNode.hide();
	if (fldNode.parent().hasClass('show-last-value')) {
		fldNode.parent().hide();
	}
	tsNode.attr('name', '{$frmFldName}');
	tsNode.attr('tabindex', fldNode.attr('tabindex'));


	tsNode.selectize({
		theme: 'typespecial',
		valueField: 'id',
		labelField: 'id',
		searchField: 'name',
		sortField: 'name',
		create: {$jsonAllowCreate},
		delimiter: ';',
		dataAttr: 'typespecial',
		preload: {$jsonPreload},
		options: {$optionsJson},
		render: {
			item: function(item, escape) {
				//console.log('item', item);
				//var name = formatName(item);
				return '<div>' +
					'<span class=\"name\">' + escape(item.name || item.id) + '</span>' +
				'</div>';
			},
			option: function(item, escape) {
//console.log('--- render.option item(',item,') this:',this);
				return '<div>' +
					'<span class=\"title\">' +
						escape(item.name || item.id) +
					'</span>' +
				'</div>';
			}
		},
		onItemAdd: function(value, item) {// Invoked when an item is selected.
			var curSel = this.options[value] || null, fieldExport;
//console.log('--- onItemAdd val(',value,'/',item.data('name'),'/',item,') this:curSel:',curSel);
			if (curSel && curSel.exports) {
				for (var i in curSel.exports) {
					fieldExport = jQuery('#ts-f' + i);
					if (fieldExport.length) {
//console.log('--- onItemAdd fieldExport.selectize(',fieldExport.selectize,')', fieldExport);
						if (fieldExport.get(0).selectize) {
							fieldExport.get(0).selectize.addOption({id:curSel.exports[i], name:curSel.exports[i]});
							fieldExport.get(0).selectize.setValue(curSel.exports[i]);
						} else {
							//jQuery('#f' + i).val(curSel.exports[i]);
						}
					} else {
//console.log('--- onItemAdd jQuery(#f' + i + ').val(', curSel.exports[i], '): fieldExport.is(input:',fieldExport.is('input'),'/select:',fieldExport.is('select'),')', fieldExport);
						fieldExport = jQuery('#f' + i);
						if (fieldExport.is('input')) {
							jQuery('#f' + i).val(curSel.exports[i]);
						} else if (fieldExport.is('select')) {
							//TODO: add option and select //jQuery('#f' + i).val(curSel.exports[i]);
						}
					}
				}
			}
		},
		score: function(search) {
			var score = this.getScoreFunction(search);
			return function(item) {
				//console.log('score:item:', item, ', score:', score(item));
				if (search && search == item.id) {
					return 1;
				} else {
					return score(item);// score(item) * (1 + Math.min(item.watchers / 100, 1));
				}
			};
		},
		load: function(query, callback) {
			if (!query.length) return callback();
			$.ajax({
				url: '{$ajaxDataUrlBase}',
				data: 'q=' + encodeURIComponent(query),
				type: 'POST',
				error: function() {
					callback();
				},
				success: function(res) {
					for (var i in res) {
						if (!res[i].name || res[i].id != res[i].name) {
							res[i].name = res[i].id + ': ' + res[i].name;
						}
					}
					callback(res);
				}
			});
		}
	});

});
		" . '</script>';

		return $out;
	}

	/**
	 * @params $query - query string
	 * @params $strict - search only equal value
	 * @returns [{id:int, param_out:str, exports:[]}]
	 */
	private function _getValues($query, $strict = false, $params = array()) {
		$values = array();

		switch ($this->fldName) {
			case 'A_ADM_COMPANY':
			case 'A_CLASSIFIED':
			case 'A_EXEC_GROUP':
				Lib::loadClass('UsersHelper');
				$userName = User::getLogin();
				$userLdapGroups = UsersHelper::getLDAPGroupByUserName($userName);
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">userLdapGroups (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($userLdapGroups);echo'</pre>';}
				if (!empty($userLdapGroups)) {
					foreach ($userLdapGroups as $kID => $vLdapGroup) {
						$allowGroup = false;
						//$allowGroup = $vLdapGroup->gidNumber > 1000;
						if ('workgroup' == $vLdapGroup->cn) {
							$allowGroup = true;
						} else {
							$cnTest = str_replace('-', '_', $vLdapGroup->cn);
							$cnTest = explode('_', $cnTest);
							$cnTest = $cnTest[0];
							if (is_numeric($cnTest)) {
								$allowGroup = true;
							}
						}
						if ($allowGroup) {
							if ($strict) {
								if (!empty($query) && $query == $vLdapGroup->cn) {
									$values[$vLdapGroup->cn] = (object)array('id'=>$vLdapGroup->cn, 'param_out'=>$vLdapGroup->cn);
								}
							} else {
								$values[$vLdapGroup->cn] = (object)array('id'=>$vLdapGroup->cn, 'param_out'=>$vLdapGroup->cn);
								//$values[$vLdapGroup->cn] = $vLdapGroup->cn;
							}
						}
					}
				}
				break;
			case 'DEFAULT_ACL_GROUP':
				Lib::loadClass('UsersHelper');
				$userLdapGroups = UsersHelper::getLDAPGroupsAll();
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">userLdapGroups (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($userLdapGroups);echo'</pre>';}
				if (!empty($userLdapGroups)) {
					foreach ($userLdapGroups as $kID => $vLdapGroup) {
						$allowGroup = false;
						//$allowGroup = $vLdapGroup->gidNumber > 1000;
						if ('workgroup' == $vLdapGroup->cn) {
							$allowGroup = true;
						} else {
							$cnTest = str_replace('-', '_', $vLdapGroup->cn);
							$cnTest = explode('_', $cnTest);
							$cnTest = $cnTest[0];
							if (is_numeric($cnTest)) {
								$allowGroup = true;
							}
						}
						if ($allowGroup) {
							if ($strict) {
								if (!empty($query) && $query == $vLdapGroup->cn) {
									$values[$vLdapGroup->cn] = (object)array('id'=>$vLdapGroup->cn, 'param_out'=>$vLdapGroup->cn);
								}
							} else {
								$values[$vLdapGroup->cn] = (object)array('id'=>$vLdapGroup->cn, 'param_out'=>$vLdapGroup->cn);
								//$values[$vLdapGroup->cn] = $vLdapGroup->cn;
							}
						}
					}
				}
				break;
			case 'K_OD_KOGO':
				$db = DB::getDB();
				$OD_KOGO_ADRES_ID = 0;
				$sql = "select z2.`ID`
					from `CRM_LISTA_ZASOBOW` as z
						join `CRM_LISTA_ZASOBOW` as z2 on (z2.`PARENT_ID`=z.`PARENT_ID`)
					where
						z.`ID`='{$this->fldID}'
						and z2.`DESC`='OD_KOGO_ADRES'
				";
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
				$res = $db->query($sql);
				while ($r = $db->fetch($res)) {
					$OD_KOGO_ADRES_ID = $r->ID;
				}

				$sqlLimit = 20;
				$query = trim($query, ' %');
				$query = $db->_($query);
				$sqlSearch = "k.`K_OD_KOGO` like '%{$query}%'";
				if ($strict) {
					if (!empty($query)) {
						$sqlLimit = 1;
						$sqlSearch = "k.`K_OD_KOGO`='{$query}'";
					} else {
						return $values;
					}
				}

				$sql = "select k.`K_OD_KOGO`, k.`OD_KOGO_ADRES`
					from `IN7_DZIENNIK_KORESP` as k
					where {$sqlSearch}
					group by k.`K_OD_KOGO`
					limit {$sqlLimit}
				";
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
				$res = $db->query($sql);
				while ($r = $db->fetch($res)) {
					$values[] = (object)array('id'=>$r->K_OD_KOGO, 'param_out'=>$r->K_OD_KOGO, 'exports'=>array($OD_KOGO_ADRES_ID => $r->OD_KOGO_ADRES));
				}
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">values (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($values);echo'</pre>';}
				break;
			case 'OD_KOGO_ADRES':
				$db = DB::getDB();
				$query = trim($query, ' %');
				$query = $db->_($query);
				$sqlLimit = 20;
				$sqlSearch = "k.`OD_KOGO_ADRES` like '%{$query}%'";
				if ($strict) {
					if (!empty($query)) {
						$sqlLimit = 1;
						$sqlSearch = "k.`OD_KOGO_ADRES`='{$query}'";
					} else {
						return $values;
					}
				}

				$sql = "select k.`OD_KOGO_ADRES`
					from `IN7_DZIENNIK_KORESP` as k
					where {$sqlSearch}
					group by k.`OD_KOGO_ADRES`
					limit {$sqlLimit}
				";
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
				$res = $db->query($sql);
				while ($r = $db->fetch($res)) {
					$values[] = (object)array('id'=>$r->OD_KOGO_ADRES, 'param_out'=>$r->OD_KOGO_ADRES);
				}
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">values (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($values);echo'</pre>';}
				break;
			case 'M_DISTRIBUTOR':
				$db = DB::getDB();
				$query = trim($query, ' %');
				$query = $db->_($query);
				$sqlLimit = 20;
				$sqlSearch = "p.`M_DISTRIBUTOR` like '%{$query}%'";
				if ($strict) {
					if (!empty($query)) {
						$sqlLimit = 1;
						$sqlSearch = "p.`M_DISTRIBUTOR`='{$query}'";
					} else {
						return $values;
					}
				}
				$sql = "select p.`M_DISTRIBUTOR`
					from `IN7_MK_BAZA_DYSTRYBUCJI` as p
					where p.`M_DISTRIBUTOR` like '%{$query}%'
					group by p.`M_DISTRIBUTOR`
					limit {$sqlLimit}
				";
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
				$res = $db->query($sql);
				while ($r = $db->fetch($res)) {
					$values[] = (object)array('id'=>$r->M_DISTRIBUTOR, 'param_out'=>$r->M_DISTRIBUTOR);
				}
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">values (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($values);echo'</pre>';}
				break;
			case '__NESTED_GROUPS': {
				$db = DB::getDB();
				$query = trim($query, ' %');
				$query = $db->_($query);
				$sqlLimit = 20;
				$sql = "select z.`ID`, z.`DESC`, z.`TYPE`
					from `CRM_LISTA_ZASOBOW` as z
					where z.`A_STATUS` in('NORMAL', 'WAITING')
						and z.`TYPE` in('STANOWISKO', 'PODMIOT', 'DZIAL')
						and (z.`DESC` like '%{$query}%' or z.`ID` like '%{$query}%')
					group by z.`DESC`
					limit {$sqlLimit}
				";
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
				$res = $db->query($sql);
				while ($r = $db->fetch($res)) {
					$values[] = (object)array('id'=>$r->ID, 'param_out'=>$r->TYPE . ' ' . $r->DESC);
				}
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">values (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($values);echo'</pre>';}
				break;
			}
			case '__USER_GROUPS': {
				$db = DB::getDB();
				$query = trim($query, ' %');
				$query = $db->_($query);
				$sqlLimit = 20;
				$sql = "select z.`ID`, z.`DESC`, z.`TYPE`
					from `CRM_LISTA_ZASOBOW` as z
					where z.`A_STATUS` in('NORMAL', 'WAITING')
						and z.`TYPE` in('STANOWISKO', 'PODMIOT', 'DZIAL')
						and (z.`DESC` like '%{$query}%' or z.`ID` like '%{$query}%')
					group by z.`DESC`
					limit {$sqlLimit}
				";
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
				$res = $db->query($sql);
				while ($r = $db->fetch($res)) {
					$values[] = (object)array('id'=>$r->ID, 'param_out'=>$r->TYPE . ' ' . $r->DESC);
				}
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">values (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($values);echo'</pre>';}
				break;
			}
			case '__TELBOXES': {
				$db = DB::getDB();
				$query = trim($query, ' %');
				$query = $db->_($query);
				$sqlLimit = 20;
				$sql = "select tx.`ID`, tx.`T_TELBOX_NAME`, tx.`T_TELBOX_TYPE`
					from `TELBOXES` as tx
					where
						tx.`A_STATUS`!='DELETED'
						and (tx.`ID` like '%{$query}%' or tx.`T_TELBOX_NAME` like '%{$query}%')
					order by tx.`T_TELBOX_NAME`
					limit {$sqlLimit}
				";
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
				$res = $db->query($sql);
				while ($r = $db->fetch($res)) {
					$values[] = (object)array('id'=>$r->ID, 'param_out'=>$r->T_TELBOX_NAME . ' ' . $r->T_TELBOX_TYPE);
				}
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">values (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($values);echo'</pre>';}
				break;
			}
			case '__TELBOXES_NAME': {
				$db = DB::getDB();
				$query = trim($query, ' %');
				$query = $db->_($query);
				$sqlLimit = 20;
				$sql = "select tx.`ID`, tx.`T_TELBOX_NAME`, tx.`T_TELBOX_TYPE`
					from `TELBOXES` as tx
					where
						tx.`A_STATUS`!='DELETED'
						and (tx.`ID` like '%{$query}%' or tx.`T_TELBOX_NAME` like '%{$query}%')
					order by tx.`T_TELBOX_NAME`
					limit {$sqlLimit}
				";
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
				$res = $db->query($sql);
				while ($r = $db->fetch($res)) {
					$values[] = (object)array('id'=>$r->T_TELBOX_NAME, 'param_out'=>$r->T_TELBOX_NAME . ' ' . $r->T_TELBOX_TYPE);
				}
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">values (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($values);echo'</pre>';}
				break;
			}
			case '__DEALS_SALES_TELBOXES_NAME': {
				$db = DB::getDB();
				$query = trim($query, ' %');
				$query = $db->_($query);
				$sqlLimit = 20;
				$sql = "select x.`id`, x.`label`
					from (
						select d.`T_TELBOX_NEIGHBOUR_IN` as `id`
							, d.`marka` as `label`
						from `DEALS_SALES` d
						where d.`A_STATUS`!='DELETED'
						group by d.`T_TELBOX_NEIGHBOUR_IN`, d.`marka`
					) as x
					where x.`id` like '%{$query}%' or x.`label` like '%{$query}%'
					order by x.`label`
					limit {$sqlLimit}
				";
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
				$res = $db->query($sql);
				while ($r = $db->fetch($res)) {
					$values[] = (object)array('id'=>$r->id . ': '. $r->label, 'param_out'=>'');
				}
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">values (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($values);echo'</pre>';}
				break;
			}
			case '__ZALICZKI_NAJEMCOW__NAJEMCA': {
				$pdo = DB::getPDO();
				$query = trim($query, ' %');
				$sqlLimit = 20;
				$sql = "select x.`id`, x.`label`
					from (
						select d.`NAJEMCA` as `id`
							, d.`NAJEMCA` as `label`
						from `ZALICZKI_NAJEMCOW` d
						where d.`A_STATUS`!='DELETED'
							and d.`NAJEMCA` like :query_like
						group by d.`NAJEMCA`
					) as x
					order by x.`label`
					limit {$sqlLimit}
				";
				DBG::_('DBG_TS', '>2', "sql", $sql, __CLASS__, __FUNCTION__, __LINE__);
				$sth = $pdo->prepare($sql);
				$sth->bindValue(':query_like', "%{$query}%", PDO::PARAM_STR);
				$sth->execute();
				$list = $sth->fetchAll();
				foreach ($list as $item) {
					$values[] = (object)array('id'=>$item['id'], 'param_out'=>'');
				}
				DBG::_('DBG_TS', '>2', "values", $values, __CLASS__, __FUNCTION__, __LINE__);
				break;
			}
			case '__ZASOB': {
				$db = DB::getDB();
				$sqlQuery = new stdClass();
				$sqlQuery->limit = 20;
				$sqlQuery->orderBy = "";
				$sqlQuery->selectLabel = "concat(z.`TYPE`, ' ', z.`DESC`)";
				$sqlQuery->selectAddBestFit = "";
				$sqlQuery->whereAddQueryByWords = "";

				$sqlWhereAddType = "";
				if (!empty($params['zasob_type_in'])) {
					if (is_scalar($params['zasob_type_in'])) $params['zasob_type_in'] = array($params['zasob_type_in']);
					$sqlWhereAddType = " and z.`TYPE` IN('" . implode("','", $params['zasob_type_in']) . "') ";

					if (in_array('KOMORKA', $params['zasob_type_in'])) {
						$sqlQuery->selectLabel = "concat(z.`TYPE`, ' ', z.`DESC` , ' (', (select zp.`DESC` from `CRM_LISTA_ZASOBOW` as zp where zp.`ID`=z.`PARENT_ID` limit 1), ')')";
					}
				}
				$query = trim($query, ' %');
				$sqlQuery->_queryByWords = array();
				if (is_numeric($query)) {
					$sqlQueryNum = intval($query);
					$sqlQuery->_queryByWords[] = "z.`ID` like '%{$sqlQueryNum}%'";
					$sqlQuery->selectAddBestFit = "
						, IF (z.`ID`='{$sqlQueryNum}', 1000,
							IF (z.`ID` like '{$sqlQueryNum}%', 900, 100)
						) as _bestFit
					";
					$sqlQuery->orderBy = "order by _bestFit DESC";
				} else {
					$queryWhereBuilder = new SqlQueryWhereBuilder();
					$searchWords = $queryWhereBuilder->splitQueryToWords($query);
					DBG::_('DBG_TS', '>2', "SqlQueryWhereBuilder->splitQueryToWords({$query})", $searchWords, __CLASS__, __FUNCTION__, __LINE__);
					$sqlWords = array();
					if (!empty($searchWords)) {
						foreach ($searchWords as $word) {
							if (is_numeric($word)) {
								$sqlWord = intval($word);
								$sqlQuery->_queryByWords[] = " ( z.`ID` like '%{$sqlWord}%' or z.`DESC` like '%{$sqlWord}%' ) ";
							} else {
								$sqlWord = $db->_($word);
								$sqlQuery->_queryByWords[] = "z.`DESC` like '%{$sqlWord}%'";
							}
						}
					}
				}
				if (!empty($sqlQuery->_queryByWords)) {
					$sqlQuery->whereAddQueryByWords = " and (" . implode(" and ", $sqlQuery->_queryByWords) . ")";
				}

				$sql = "select z.`ID`
						, {$sqlQuery->selectLabel} as `LABEL`
						{$sqlQuery->selectAddBestFit}
					from `CRM_LISTA_ZASOBOW` as z
					where z.`A_STATUS` in('NORMAL', 'WAITING')
						{$sqlQuery->whereAddQueryByWords}
						{$sqlWhereAddType}
					{$sqlQuery->orderBy}
					limit {$sqlQuery->limit}
				";
				DBG::_('DBG_TS', '>2', "sql", $sql, __CLASS__, __FUNCTION__, __LINE__);
				$res = $db->query($sql);
				while ($r = $db->fetch($res)) {
					$values[] = (object)array('id'=>$r->ID, 'param_out'=>$r->LABEL);
				}
				DBG::_('DBG_TS', '>2', "values", $values, __CLASS__, __FUNCTION__, __LINE__);
				break;
			}
			case '__PROCES': {
				$db = DB::getDB();
				$query = trim($query, ' %');
				$query = $db->_($query);
				$sqlLimit = 20;
				$sqlSelectLabel = "concat(z.`TYPE`, ' ', z.`DESC`)";
				$sqlWhereAdd = "";
				if (is_numeric($query)) {
					$sql = "select z.`ID`
							, {$sqlSelectLabel} as `LABEL`
							, IF (z.`ID`='{$query}', 1000,
							      IF (z.`ID` like '{$query}%', 900, 100)
							     ) as _bestFit
						from `CRM_PROCES` as z
						where z.`A_STATUS` in('NORMAL', 'WAITING')
							and (z.`DESC` like '%{$query}%' or z.`ID` like '%{$query}%')
							{$sqlWhereAdd}
						order by _bestFit DESC
						limit {$sqlLimit}
					";
				} else {
					$sql = "select z.`ID`
							, {$sqlSelectLabel} as `LABEL`
						from `CRM_PROCES` as z
						where z.`A_STATUS` in('NORMAL', 'WAITING')
							and (z.`DESC` like '%{$query}%' or z.`ID` like '%{$query}%')
							{$sqlWhereAdd}
						limit {$sqlLimit}
					";
				}
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
				$res = $db->query($sql);
				while ($r = $db->fetch($res)) {
					$values[] = (object)array('id'=>$r->ID, 'param_out'=>$r->LABEL);
				}
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">values (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($values);echo'</pre>';}
				break;
			}
			case '__COMPANIES': {
				$db = DB::getDB();
				$query = trim($query, ' %');
				$query = $db->_($query);
				$sqlLimit = 20;
				$sql = "select c.`ID`, c.`P_NAME`, c.`P_NIP`
					from `COMPANIES` as c
					where c.`A_STATUS` in('NORMAL', 'WAITING')
						and (c.`P_NAME` like '%{$query}%' or c.`P_NIP` like '%{$query}%' or c.`ID` like '%{$query}%')
					limit {$sqlLimit}
				";
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
				$res = $db->query($sql);
				while ($r = $db->fetch($res)) {
					$values[] = (object)array('id'=>$r->ID, 'param_out'=>$r->P_NAME . ' ' . $r->P_NIP);
				}
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">values (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($values);echo'</pre>';}
				break;
			}
			case 'VERSION_GIT': {
				$gitPath = '/Library/Server/Web/Data/Sites/SE-production-git/';
				$versions = array();
				if (file_exists($gitPath)) {
					$cmd = "cd {$gitPath} && git tag -l| sort -r -n -t. -k1,1 -k2,2 -k3,3 -k4,4|head -10";
					$cmdOut = null; $cmdRet = null;
					exec($cmd, $cmdOut, $cmdRet);
					if ($cmdRet == 0) {
						if (!empty($cmdOut)) {
							foreach ($cmdOut as $tag) {
								array_unshift($versions, $tag);
							}
						}
					}
				}
				foreach ($versions as $version) {
					if (!empty($query)) {
						if ($strict) {
							if ($version != $query) {
								continue;
							}
						}
						else {
							if (false === strpos($version, $query)) {
								continue;
							}
						}
					}
					$values[] = (object)array('id'=>$version, 'param_out'=>$version);
				}
			}
			case '__USER_ID': {
				$db = DB::getDB();
				$query = trim($query, ' %');
				$query = $db->_($query);
				$sqlLimit = 20;
				$sqlSelectLabel = array();
				$sqlSelectLabel[] = 'u.`ADM_NAME`';
				$sqlSelectLabel[] = "' ('";
				$sqlSelectLabel[] = 'u.`ADM_ACCOUNT`';
				$sqlSelectLabel[] = "', '";
				$sqlSelectLabel[] = 'u.`EMAIL`';
				$sqlSelectLabel[] = "', '";
				$sqlSelectLabel[] = 'u.`ADM_PHONE`';
				$sqlSelectLabel[] = "')'";
				$sqlSelectLabel = "concat(" . implode(", ", $sqlSelectLabel) . ")";

				$sql = "select u.`ID`
						, {$sqlSelectLabel} as `LABEL`
					from `ADMIN_USERS` as u
					where u.`A_STATUS` in('NORMAL', 'WAITING','MONITOR','WARNING')
						and u.`EMPLOYEE_TYPE` in('Pracownik','Partner')
						and (u.`ID` like '%{$query}%'
							or u.`ADM_ACCOUNT` like '%{$query}%'
							or u.`ADM_NAME` like '%{$query}%'
							or u.`ADM_PHONE` like '%{$query}%'
							or u.`EMAIL` like '%{$query}%'
						)
					limit {$sqlLimit}
				";
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
				$res = $db->query($sql);
				while ($r = $db->fetch($res)) {
					$values[] = (object)array('id'=>$r->ID, 'param_out'=>$r->LABEL);
				}
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">values (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($values);echo'</pre>';}
				break;
			}
			case '__USER_LOGIN': {
				$db = DB::getDB();
				$query = trim($query, ' %');
				$query = $db->_($query);
				$sqlLimit = 20;
				$sqlSelectLabel = array();
				$sqlSelectLabel[] = 'u.`ADM_NAME`';
				$sqlSelectLabel[] = "' ('";
				$sqlSelectLabel[] = 'u.`ADM_ACCOUNT`';
				$sqlSelectLabel[] = "', '";
				$sqlSelectLabel[] = 'u.`EMAIL`';
				$sqlSelectLabel[] = "', '";
				$sqlSelectLabel[] = 'u.`ADM_PHONE`';
				$sqlSelectLabel[] = "')'";
				$sqlSelectLabel = "concat(" . implode(", ", $sqlSelectLabel) . ")";

				$sql = "select u.`ADM_ACCOUNT` as ID
						, {$sqlSelectLabel} as `LABEL`
					from `ADMIN_USERS` as u
					where u.`A_STATUS` in('NORMAL', 'WAITING','MONITOR','WARNING')
						and u.`EMPLOYEE_TYPE` in('Pracownik','Partner')
						and (u.`ID` like '%{$query}%'
							or u.`ADM_ACCOUNT` like '%{$query}%'
							or u.`ADM_NAME` like '%{$query}%'
							or u.`ADM_PHONE` like '%{$query}%'
							or u.`EMAIL` like '%{$query}%'
						)
					limit {$sqlLimit}
				";
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
				$res = $db->query($sql);
				while ($r = $db->fetch($res)) {
					$values[] = (object)array('id'=>$r->ID, 'param_out'=>$r->LABEL);
				}
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">values (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($values);echo'</pre>';}
				return $values;
			}
			case 'L_APPOITMENT_USER': {
				$query = trim($query, ' %');
				$acl = User::getAcl()->getObjectAcl('default_db', 'ADMIN_USERS');
				$queryFeatures = $acl->buildQuery([
					(!empty($query))
						? [null, 'or', [
								(is_numeric($query)) ? ['ID', 'like', "{$query}%"] : null,
								['ADM_ACCOUNT', 'like', "%{$query}%"],
								['ADM_NAME', 'like', "%{$query}%"],
								['ADM_PHONE', 'like', "%{$query}%"],
								['EMAIL', 'like', "%{$query}%"],
							] ]
						: null,
					'f_A_STATUS' => '=NORMAL',
					'f_ADM_TECH_WORKER' => '!NO',
					'limit' => 20
				]);
				return array_map(function ($item) {
					return (object)[
						'id' => $item['ID'],
						'param_out' => "{$item['ADM_NAME']} ({$item['ADM_ACCOUNT']})"
					];
				}, $queryFeatures->getItems());
			} break;
			default:
		}
		return $values;
	}

	/**
	 * @returns object
	 * 		{
	 * 				ext_tbl_id: "1466",
	 * 				ext_row_id: "2975",
	 * 				id: "2975",
	 * 				tbl_label: "Zasoby",
	 * 				link_type_id: "5",
	 * 				link_type: "NestedGroups",
	 * 		},
	 */
	public function getReturnData($tblId, $rowId, $fieldName) {
		$retData = new stdClass();
		$retData->items = array();
		$values = array();
		switch ($this->fldName) {
			case '__CONNECTIONS': {
				$linkTypes = array();
				$tblLabels = array();
				$db = DB::getDB();
				$sqlLimit = 21;
				$sql = "select l.*
					from `ITEM_LINKS` as l
					where l.`A_STATUS` in('NORMAL', 'WAITING')
						and (
								(l.`TABLE_1_ZASOB_ID`={$tblId} and l.`TABLE_1_ID`={$rowId})
							or
								(l.`TABLE_2_ZASOB_ID`={$tblId} and l.`TABLE_2_ID`={$rowId})
						)
						and l.`LINKS_TYPE_ID`>1
					limit {$sqlLimit}
				";
				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
				$res = $db->query($sql);
				while ($r = $db->fetch($res)) {
					$connObj = new stdClass();
					if ($r->TABLE_1_ZASOB_ID == $tblId && $r->TABLE_1_ID == $rowId) {
						$connObj->ext_tbl_id = $r->TABLE_2_ZASOB_ID;
						$connObj->ext_row_id = $r->TABLE_2_ID;
						$connObj->id = $r->TABLE_2_ID;
						$connObj->label = "{$r->TABLE_2_ZASOB_ID}({$r->TABLE_2_ID})";
					}
					else if ($r->TABLE_2_ZASOB_ID == $tblId && $r->TABLE_2_ID == $rowId) {
						$connObj->ext_tbl_id = $r->TABLE_1_ZASOB_ID;
						$connObj->ext_row_id = $r->TABLE_1_ID;
						$connObj->id = $r->TABLE_1_ID;
						$connObj->label = "{$r->TABLE_1_ZASOB_ID}({$r->TABLE_1_ID})";
					}
					if ($connObj) {
						$connObj->link_type_id = $r->LINKS_TYPE_ID;
						$linkTypes[$connObj->link_type_id] = '';
						$tblLabels[$connObj->ext_tbl_id] = '';
						$retData->items[] = $connObj;
					}
				}

				if (!empty($linkTypes)) {
					$sql = "select lt.`ID`, lt.`NAME`
						from `ITEM_LINK_TYPES` as lt
						where lt.`ID` in (" . implode(", ", array_keys($linkTypes)) . ")
					";
					$res = $db->query($sql);
					while ($r = $db->fetch($res)) {
						$linkTypes[$r->ID] = $r->NAME;
					}
				}

				if (!empty($tblLabels)) {
					$sql = "select z.`ID`, z.`DESC`, z.`DESC_PL`, z.`OPIS`
						from `CRM_LISTA_ZASOBOW` as z
						where z.`ID` in (" . implode(", ", array_keys($tblLabels)) . ")
					";
					$res = $db->query($sql);
					while ($r = $db->fetch($res)) {
						if (!empty($r->DESC_PL)) {
							$tblLabels[$r->ID] = $r->DESC_PL;
						} else if (!empty($r->OPIS)) {
							$tblLabels[$r->ID] = V::strShortUtf8($r->OPIS, 20);
						} else {
							$tblLabels[$r->ID] = $r->DESC;
						}
					}
				}

				foreach ($retData->items as $k => $connObj) {
					$connObj->link_type = V::get($connObj->link_type_id, $connObj->link_type_id, $linkTypes);
					$connObj->tbl_label = V::get($connObj->ext_tbl_id, $connObj->ext_tbl_id, $tblLabels);
				}

				if(V::get('DBG_TS', 0, $_GET) > 2){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">retData->items('.count($retData->items).') (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($retData->items);echo'</pre>';}

				return $retData;
				break;
			}
			default:
		}

		return null;
	}

}