se/SE/se-lib/BN/CompanyHelper.php

<?php


class BN_CompanyHelper {

	/**
	 * @return object | null
	 */
	function getById($id) {
		$sql = "
			select bua.`id_users`
				, bua.`id_users` as ID_BILLING_USERS
				, bua.`is_firma`
				, bua.`P_NAME`
				, bua.`P_NAME_SECOND`
				, bua.`P_NIP`
				, bua.`P_PESEL`
				, bua.`P_PHONE`
				, bua.`P_ADDRESS_REGION`
				, bua.`P_ADDRESS_CITY`
				, bua.`P_ADDRESS_POST_CODE`
				, bua.`P_ADDRESS_STREET`
				, bua.`P_ADDRESS_HOME`
				, bua.`P_ADDRESS_HOUSE`
				, bua.`user_mail_contact`
				, bu.`BILLING_OWNER`
			from `BILLING_USERS_ADD` as bua
				left join `BILLING_USERS` as bu on ( bu.`ID` = bua.`id_users` )
			where bua.`id_users` = '{$id}'
		";

		return DB::getPDO()->fetchFirstAsObject("
			select c.`ID`
				, c.`ID` as id_users
				, c.`ID` as ID_BILLING_USERS
				, c.`is_firma`
				, c.`P_NAME`
				, c.`P_NAME_SECOND`
				, c.`P_NIP`
				, c.`P_PESEL`
				, c.`P_PHONE`
				, c.`P_ADDRESS_REGION`
				, c.`P_ADDRESS_CITY`
				, c.`P_ADDRESS_POST_CODE`
				, c.`P_ADDRESS_STREET`
				, c.`P_ADDRESS_HOME`
				, c.`P_ADDRESS_HOUSE`
				, c.`user_mail_contact`
				, c.`BILLING_OWNER`
				, c.`CRM_LISTA_ZASOBOW_ID`
			from `COMPANIES` as c
			where c.`ID` = :id
		", [
			':id' => $id,
		]);
	}

	/**
	 * @return object | null
	 */
	function getByNip($nip) {
		$sqlNip = trim(str_replace('-', '', $nip));

		return DB::getPDO()->fetchFirstAsObject("
			select c.`ID`
				, c.`ID` as id_users
				, c.`ID` as ID_BILLING_USERS
				, c.`is_firma`
				, c.`P_NAME`
				, c.`P_NAME_SECOND`
				, c.`P_NIP`
				, c.`P_PESEL`
				, c.`P_PHONE`
				, c.`P_ADDRESS_REGION`
				, c.`P_ADDRESS_CITY`
				, c.`P_ADDRESS_POST_CODE`
				, c.`P_ADDRESS_STREET`
				, c.`P_ADDRESS_HOME`
				, c.`P_ADDRESS_HOUSE`
				, c.`user_mail_contact`
				, c.`BILLING_OWNER`
				, c.`CRM_LISTA_ZASOBOW_ID`
			from `COMPANIES` as c
			where replace(c.`P_NIP`, '-', '') = :nip
		", [
			':nip' => $sqlNip,
		]);
	}

	/**
	 * @param object $company @see getById
	 * @return object | null
	 */
	function getBillingOwner($company) {
		if (!$company) return null;
		if ($company->BILLING_OWNER <= 0) return null;

		/*
			CREATE TABLE IF NOT EXISTS `BILLING_OWNER` (
				`id` int(10) NOT NULL AUTO_INCREMENT,
				`name1` varchar(255) NOT NULL DEFAULT '',
				`name2` varchar(255) NOT NULL DEFAULT '',
				`kod` varchar(6) NOT NULL DEFAULT '',
				`miasto` varchar(150) NOT NULL DEFAULT '',
				`ulica` varchar(255) NOT NULL DEFAULT '',
				`numer_dom` int(5) DEFAULT NULL,
				`numer_pos` tinyint(5) DEFAULT NULL,
				`uwagi` text NOT NULL,
				`tel` varchar(100) NOT NULL DEFAULT '',
				`fax` varchar(100) NOT NULL DEFAULT '',
				`nip` varchar(13) NOT NULL DEFAULT '',
				`regon` varchar(10) NOT NULL DEFAULT '',
				`bank` varchar(255) NOT NULL DEFAULT '',
				`nr_rach` varchar(255) NOT NULL DEFAULT '',
				`NR_RACH_MASS_PAY` varchar(24) DEFAULT NULL,
				`BILLING_OWNER_EMAIL` varchar(100) NOT NULL DEFAULT '',
				`CRM_LISTA_ZASOBOW_ID` int(11) NOT NULL,
				UNIQUE KEY `id` (`id`)
			) ENGINE=MyISAM  DEFAULT CHARSET=latin2 AUTO_INCREMENT=3 ;

			INSERT INTO `BILLING_OWNER` (`id`, `name1`, `name2`, `kod`, `miasto`, `ulica`, `numer_dom`, `numer_pos`, `uwagi`, `tel`, `fax`, `nip`, `regon`, `bank`, `nr_rach`, `NR_RACH_MASS_PAY`, `BILLING_OWNER_EMAIL`, `CRM_LISTA_ZASOBOW_ID`) VALUES
			(1, '"BIALL-NET" Sp. z o.o.', '', '80-174', 'Gdańsk, Otomin', 'Słoneczna', 43, NULL, '', '0-58 320-72-92', '0-58 320-72-96', '593-22-68-672', '192120212', 'Bank Zachodni WBK SA I O/Gdansk', '46 1090 1098 0000 0001 0253 7156', '109000049887', 'biall-net@biall.net.pl', 12889),
			(2, '"NET-DAY" s.c.', 'Adrian i Ewa Wieczorkowscy', '80-809', 'Gdańsk', 'Kopeckiego', 9, 24, '', '0-58 741 84 54', '0-58 741 84 56', '583-27-54-031', '192578721', '', '84 1500 1171 1211 7002 9997 0000', '109000049669', 'netday@netday.pl', 12919);
		*/
		return DB::getPDO()->fetchFirstAsObject("
			select o.*
			from `BILLING_OWNER` as o
			where o.`ID` = :id_owner
		", [
			':id_owner' => $company->BILLING_OWNER,
		]);
	}

	/**
	 * Powiązanie zasobu z companies za pomocą $company->CRM_LISTA_ZASOBOW_ID
	 * @return object | null | false
	 */
	function getZasobPodmiot($company, $billingOwner) {
		if (!$company || !$billingOwner) return false;
		if (!$billingOwner->CRM_LISTA_ZASOBOW_ID) return false;
		if (!$company->CRM_LISTA_ZASOBOW_ID) return null;

		return DB::getPDO()->fetchFirstAsObject("
			select z.*
			from `CRM_LISTA_ZASOBOW` as z
			where z.`ID` = :id_zasob
		", [
			':id_zasob' => $company->CRM_LISTA_ZASOBOW_ID,
		]);
		//if ($zasob->PARENT_ID == $billingOwner->CRM_LISTA_ZASOBOW_ID) {
		//	return $zasob;
		//} else {
		//	return false;
		//}
	}

	function createZasobPodmiot($company, $billingOwner) {
		if (!$company || !$billingOwner) return false;
		if (!$billingOwner->CRM_LISTA_ZASOBOW_ID) return false;
		if ($company->CRM_LISTA_ZASOBOW_ID > 0) return null;

		$podmiot = new stdClass();
		$podmiot->PARENT_ID = $billingOwner->CRM_LISTA_ZASOBOW_ID;
		$podmiot->TYPE = 'PODMIOT';
		$podmiot->DESC = "{$company->P_NAME} {$company->P_NAME_SECOND}";

		$zasobId = DB::getDB()->ADD_NEW_OBJ('CRM_LISTA_ZASOBOW', $podmiot);
		if ($zasobId > 0) {
			$sqlCom = new stdClass();
			$sqlCom->ID = $company->ID;
			$sqlCom->CRM_LISTA_ZASOBOW_ID = $zasobId;
			$affeted = DB::getDB()->UPDATE_OBJ('COMPANIES', $sqlCom);
		}
		return $zasobId;
	}

	/**
	 * Powiązane konta do autoryzacji w systemie
	 */
	function getAccountsList($company) {
		if (!$company) return false;
		if (!$company->ID) return false;
		if (!$company->CRM_LISTA_ZASOBOW_ID) return false;

		return DB::getPDO()->fetchAllByKey("
			select u.*
				, count(up.ID) as cntProfile
			from `ADMIN_USERS` as u
				left join `CRM_AUTH_PROFILE` as up on ( up.`REMOTE_ID` = u.`ID` and up.`REMOTE_TABLE` = 'ADMIN_USERS' )
			where (
					u.`ADM_ACCOUNT` like 'c{$company->ID}%'
					or u.`ID_BILLING_USERS` = '{$company->ID}'
				)
			group by u.`ID`
		", 'ID');
	}

	/**
	 * @see POKAZ_OFERTY_AKTUALNE_FUNC: PokazOfertyHelper::get_deals_active_by_user($ID_BILLING_USERS, $only_active = true)
	 */
	function getDealsFull($ID_BILLING_USERS, $only_active = true) {
		$deals = $this->getDealsAll([ 'ID_BILLING_USERS' => $ID_BILLING_USERS, 'return_by' => 'ID', 'P_DEALNUMBER' => true ]);
		$this->addDealsActiveInfo($deals, $ID_BILLING_USERS, $only_active);
		return $deals;
	}

	/**
	 * @see POKAZ_OFERTY_AKTUALNE_FUNC: PokazOfertyHelper::deals_active_add_info(&$active_deals_by_id, $ID_BILLING_USERS, $only_active = false)
	 */
	function addDealsActiveInfo(&$active_deals_by_id, $ID_BILLING_USERS, $only_active = false) {
		if (empty($active_deals_by_id)) {
			return;
		}
		$uslugi = $this->getServicesByDeals($ID_BILLING_USERS, array_keys($active_deals_by_id), $only_active);
		foreach ($active_deals_by_id as $k_deal_id => $v_deal) {
			if (array_key_exists($k_deal_id, $uslugi)) {
				$active_deals_by_id[$k_deal_id]->services = $uslugi[$k_deal_id];
				$active_deals_by_id[$k_deal_id]->services_by_id = array();
				foreach ($uslugi[$k_deal_id] as $k_p_service => $v_srv_arr) {
					foreach ($v_srv_arr as $k_srv_id => $v_srv) {
						$active_deals_by_id[$k_deal_id]->services_by_id[$k_srv_id] = $v_srv;
					}
				}
			}
		}

		// add $v_deal->ses_x_a
		$out_id_count = array();
		$uslugi_params = $this->getServicesParams($uslugi);
		foreach ($active_deals_by_id as $k_deal_id => $v_deal) {
			$active_deals_by_id[$k_deal_id]->ses_x_a = array();
			foreach ($v_deal->services as $k_p_service => $v_srv_arr) {
				foreach ($v_srv_arr as $k_srv_id => $v_srv) {
					$v_deal->services[$k_p_service][$k_srv_id]->offer_p_service = $k_p_service;

					// add SERVICE params if exists
					if (array_key_exists($k_srv_id, $uslugi_params)) {
						$active_deals_by_id[$k_deal_id]->ses_x_a[$k_srv_id] = $uslugi_params[$k_srv_id];
						if ($v_srv->NAME_LIST_SERVICES == 'TV' && $uslugi_params[$k_srv_id]->P_SERVICE_CLASS != '') {
							$v_deal->services[$k_p_service][$k_srv_id]->offer_p_service = $uslugi_params[$k_srv_id]->P_SERVICE_CLASS;
						}
					}

					// add out_id - @used in JavaScript overview
					if (!in_array($v_srv->offer_p_service, $out_id_count)) {
						$out_id_count[$v_srv->offer_p_service] = 1;
					} else {
						$out_id_count[$v_srv->offer_p_service] += 1;
					}
					$v_deal->services[$k_p_service][$k_srv_id]->out_id = $v_srv->offer_p_service . '_' . $out_id_count[$v_srv->offer_p_service];
				}
			}
		}
	}

	/**
	 * @see POKAZ_OFERTY_AKTUALNE_FUNC: PokazOfertyHelper::get_services_by_user_deals($user_id, $deals_ids = array(), $only_active = false)
	 */
	function getServicesByDeals($user_id, $deals_ids = array(), $only_active = false) {
		if (empty($deals_ids)) return [];
		
		$sqlDeals = implode(",", $deals_ids);
		$listServices = DB::getPDO()->fetchAll("
			select
				s.`ID`, s.`NAME_LIST_SERVICES`, s.`A_STATUS`
				, s.`ID_OFFERS`
				, s.`ID_DEALS_TABLE`
				, s.`P_ID_SERVICES`
				, s.`ABONAMENT_VALUE` as ABONAMENT_VALUE
				, s.`P_ID_SERVICES` as P_ID
				, s.`HANGUP_STATUS`
				, s.`HANGUP_FROM`
				, s.`HANGUP_TILL`
				, ls.`description` as description
		--		, lst.`name` as A_STATUS_DESC
				, '' as A_STATUS_DESC
			from `SERVICES` as s
				left join `LIST_SERVICES` as ls on ( ls.`name` = s.`NAME_LIST_SERVICES` )
		--		left join `LIST_status` as lst on ( lst.`ses_users2` = s.`A_STATUS` )
		--		left join `LIST_status` as lst on ( lst.`ses_users2` = s.`HANGUP_STATUS` and lst.`ID` > 2 )
			where `ID_BILLING_USERS` = :id
				and `ID_DEALS_TABLE` in ( {$sqlDeals} )
		--		and s.`A_STATUS` = 'NORMAL'
		", [
			':id' => $user_id,
		]);
		$deals = [];
		foreach ($listServices as $item) {
			// TODO: only active services - @from $A_STATUS_L2_SQL
			// $item['HANGUP_STATUS'] = ('NORMAL', 'OFF_SOFT', 'OFF_HARD')
			$item['HANGUP_TILL'] = (!$item['HANGUP_TILL'] || $item['HANGUP_TILL'] == '0000-00-00')? null : $item['HANGUP_TILL'];
			$item['HANGUP_FROM'] = (!$item['HANGUP_FROM'] || $item['HANGUP_FROM'] == '0000-00-00')? null : $item['HANGUP_FROM'];
			$item['_status'] = null;
			$today = date("Y-m-d");
			if ($item['HANGUP_STATUS'] == $item['A_STATUS']) {
				$item['_status'] = $item['A_STATUS'];
			} else if (!$item['HANGUP_TILL'] && $item['HANGUP_FROM'] > '' && $today > $item['HANGUP_FROM']) {
				$item['_status'] = $item['HANGUP_STATUS'];
			} else if (!$item['HANGUP_TILL'] && $item['HANGUP_FROM'] > '' && $today < $item['HANGUP_FROM']) {
				$item['_status'] = $item['A_STATUS'];
			} else if ($item['HANGUP_TILL'] > '' && $item['HANGUP_FROM'] > '' && $today > $item['HANGUP_FROM'] && $today < $item['HANGUP_TILL']) {
				$item['_status'] = $item['HANGUP_STATUS'];
			} else if ($item['HANGUP_TILL'] > '' && $item['HANGUP_FROM'] > '' && $today < $item['HANGUP_FROM'] && $today < $item['HANGUP_TILL']) {
				$item['_status'] = $item['A_STATUS'];
			} else if (!$item['HANGUP_TILL'] && !$item['HANGUP_FROM']) {
				$item['_status'] = $item['A_STATUS'];
			} else if ($item['HANGUP_TILL'] > '' && $item['HANGUP_FROM'] > '' && $today > $item['HANGUP_FROM'] && $today > $item['HANGUP_TILL']) {
				$item['_status'] = $item['A_STATUS'];
			} else if ($item['HANGUP_TILL'] > '' && $item['HANGUP_FROM'] > '' && $today > $item['HANGUP_FROM'] && $today > $item['HANGUP_TILL'] && $item['HANGUP_FROM'] < $item['HANGUP_TILL']) {
				$item['_status'] = 'ERROR1';
			} else {
				$item['_status'] = 'ERROR2';
			}

			$item['A_STATUS_DESC'] = $this->getStatusName($item['_status']);

			if ($only_active && !in_array($item['_status'], [ 'NORMAL' ])) {
				continue;
			}

			// TODO: P_SERVICE used?
			$item['P_SERVICE'] = $item['NAME_LIST_SERVICES'];
			if ($item['P_SERVICE'] == 'USERS2') $item['P_SERVICE'] = 'NET';
			else if ($item['P_SERVICE'] == 'VOIP') $item['P_SERVICE'] = 'TEL';

			$deals[ $item['ID_DEALS_TABLE'] ][ $item['P_SERVICE'] ][ $item['ID'] ] = (object)$item;
		}

		return $deals;
	}

	/**
	 * @see POKAZ_OFERTY_AKTUALNE_FUNC: PokazOfertyHelper::get_l2_list_status_name($status)
	 */
	function getStatusName($status) {
		static $listStatusL2 = [];

		if (empty($listStatusL2)) {
			$listStatusL2 = DB::getPDO()->fetchValuesListByKey("
				select lst.`name`
					, lst.`ses_users2`
					, lst.`description`
				from `LIST_status` as lst
				where lst.`ID` > 2
			", 'ses_users2');
		}

		return (array_key_exists($status, $listStatusL2)) ? $listStatusL2[$status] : null;
	}

	/**
	 * @see POKAZ_OFERTY_AKTUALNE_FUNC: PokazOfertyHelper::get_services_params($uslugi)
	 */
	function getServicesParams($uslugi) {
		if (empty($uslugi)) return [];

		$ses_x_a = array();
		$allowed_name_list_srv = array('USERS2', 'TV', 'VOIP');

		$load_ses_x_a_ids = array();
		foreach ($uslugi as $k_deal_id => $v_deal_arr) {
			foreach ($v_deal_arr as $k_p_service => $v_srv_arr) {
				if (empty($v_srv_arr)) continue;

				foreach ($v_srv_arr as $k_srv_id => $v_srv) {
					$load_ses_x_a_ids[$v_srv->NAME_LIST_SERVICES][] = $v_srv->ID;
				}
			}
		}

		foreach ($load_ses_x_a_ids as $k_name_list_srv => $v_srv_ids) {
			if (!in_array($k_name_list_srv, $allowed_name_list_srv)) continue;
			if (empty($v_srv_ids)) continue;

			$listItems = DB::getPDO()->fetchAll("
				select *
				from `SES_{$k_name_list_srv}_A`
				where `ID_SERVICES` in ( " . implode(", ", $v_srv_ids) . " )
			");
			foreach ($listItems as $item) {
				$ses_x_a[ $item['ID_SERVICES'] ] = (object)$item;
			}
		}
		return $ses_x_a;
	}

	function getDealsAll($params = array()) {
		$sqlReturnBy = V::get('return_by', '', $params);

		$forceUpdate = false;
		Lib::loadClass('ColumnDealsStatus');
		ColumnDealsStatus::run_update($forceUpdate);

		$dealsActive = array();
		$sql_left_join = "";
		$sql_select_arr = array();
		$sql_select_arr[] = "ds.`ID_DEALS`";
		$sql_select_arr[] = "ds.`ID_BILLING_USERS`";
		$sql_select_arr[] = "ds.`SERVICES_S_ADDRESS_STREET`";
		$sql_select_arr[] = "concat(c.`P_NAME`, ' ', c.`P_NAME_SECOND`) as P_NAME";
		if ('' != V::get('ID_BILLING_USERS', '', $params)) {
			$sql_left_join .= "\n left join `DEALS_TABLE` as d on (d.`ID`=ds.`ID_DEALS`)";
			$sql_select_arr[] = "d.`P_DEALNUMBER`";
			$sql_select_arr[] = "d.`P_DEALDATE`";
			$sql_select_arr[] = "d.`P_DEALDATE_TERM`";
			$sql_select_arr[] = "d.`ID_OFFERS_GROUP`";
			$sql_select_arr[] = "d.`ID_OFFERS_ON`";
			$sql_select_arr[] = "d.`ID_OFFERS_OFF`";
			$sql_select_arr[] = "d.`S_ADDRESS_STREET`";
		}
		$sql_where_arr = array();
		//$sql_where_arr[] = "ds.`DEALS_ACTIVE`=1";
		if ('' != ($user_id = V::get('ID_BILLING_USERS', '', $params))) {
			$sql_where_arr[] = "ds.`ID_BILLING_USERS`='{$user_id}'";
		}
		if ('' != ($street = V::get('S_ADDRESS_STREET', '', $params))) {
			$sql_where_arr[] = "(ds.`SERVICES_S_ADDRESS_STREET`='{$street}' or concat(',', ds.`SERVICES_S_ADDRESS_STREET`, ',') like '%,{$street},%')";
		}
		$sql_where_arr = implode("\n and ", $sql_where_arr);
		$listItems = DB::getPDO()->fetchAll("
			select " . implode("\n, ", $sql_select_arr) . "
			from `temp_DEALS_STATUS` as ds
				left join `COMPANIES` as c on (c.`ID`=ds.`ID_BILLING_USERS`)
				{$sql_left_join}
			where {$sql_where_arr}
			order by ds.`ID_DEALS` DESC
		");
		foreach ($listItems as $item) {
			if ('ID' === $sqlReturnBy) {
				$dealsActive[$item['ID_DEALS']] = (object)$item;
			} else {
				$dealsActive[$item['ID_BILLING_USERS']][] = (object)$item;
			}
		}
		return $dealsActive;
	}

	/**
	 * @see POKAZ_OFERTY_AKTUALNE_FUNC: PokazOfertyHelper::get_deals_active($params)
	 */
	function getDealsActive($params = array()) {
		$sqlReturnBy = V::get('return_by', '', $params);

		$forceUpdate = false;
		Lib::loadClass('ColumnDealsStatus');
		ColumnDealsStatus::run_update($forceUpdate);

		$dealsActive = array();
		$sql_left_join = "";
		$sql_select_arr = array();
		$sql_select_arr[] = "ds.`ID_DEALS`";
		$sql_select_arr[] = "ds.`ID_BILLING_USERS`";
		$sql_select_arr[] = "ds.`SERVICES_S_ADDRESS_STREET`";
		$sql_select_arr[] = "concat(c.`P_NAME`, ' ', c.`P_NAME_SECOND`) as P_NAME";
		if ('' != V::get('ID_BILLING_USERS', '', $params)) {
			$sql_left_join .= "\n left join `DEALS_TABLE` as d on (d.`ID`=ds.`ID_DEALS`)";
			$sql_select_arr[] = "d.`P_DEALNUMBER`";
			$sql_select_arr[] = "d.`P_DEALDATE`";
			$sql_select_arr[] = "d.`P_DEALDATE_TERM`";
			$sql_select_arr[] = "d.`ID_OFFERS_GROUP`";
			$sql_select_arr[] = "d.`ID_OFFERS_ON`";
			$sql_select_arr[] = "d.`ID_OFFERS_OFF`";
			$sql_select_arr[] = "d.`S_ADDRESS_STREET`";
		}
		$sql_where_arr = array();
		$sql_where_arr[] = "ds.`DEALS_ACTIVE`=1";
		if ('' != ($user_id = V::get('ID_BILLING_USERS', '', $params))) {
			$sql_where_arr[] = "ds.`ID_BILLING_USERS`='{$user_id}'";
		}
		if ('' != ($street = V::get('S_ADDRESS_STREET', '', $params))) {
			$sql_where_arr[] = "(ds.`SERVICES_S_ADDRESS_STREET`='{$street}' or concat(',', ds.`SERVICES_S_ADDRESS_STREET`, ',') like '%,{$street},%')";
		}
		$sql_where_arr = implode("\n and ", $sql_where_arr);
		$listItems = DB::getPDO()->fetchAll("
			select " . implode("\n, ", $sql_select_arr) . "
			from `temp_DEALS_STATUS` as ds
				left join `COMPANIES` as c on (c.`ID`=ds.`ID_BILLING_USERS`)
				{$sql_left_join}
			where {$sql_where_arr}
			order by ds.`ID_DEALS` DESC
		");
		foreach ($listItems as $item) {
			if ($sqlReturnBy == 'ID') {
				$dealsActive[$item['ID_DEALS']] = (object)$item;
			} else {
				$dealsActive[$item['ID_BILLING_USERS']][] = (object)$item;
			}
		}
		return $dealsActive;
	}

	/**
	 * Emulates TypespecialVariable->getValuesWithExports($query)
	 * TODO: only groups visible for current Partner - $company
	 */
	function searchTypeSpecialPartnerGroups($company, $query) {
		$query = trim($query, ' %');
		$sqlLimit = 20;
		$listItems = DB::getPDO()->fetchAll("
			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}
		", [
			':query' => "%{$query}%",
		]);
		return array_map(function ($item) {
			return (object)[ 'id' => $item['ID'], 'param_out' => "{$item['TYPE']} {$item['DESC']}" ];
		}, $listItems);
	}

}