se/SE/se-lib/ProcesHelper.php

<?php


class ProcesHelper {

	public static function get_wskazniki($id) {
		$ret = array();
		$db = DB::getDB();
		$sql = "select
				clz.*
				, w.`ID` as CW_ID, w.`TYP` as CW_TYP, w.`OPIS_ZASOB` as OPIS_ZASOB, w.`SORT_PRIO` as CW_SORT_PRIO, w.`ID_PRZYPADEK` as CW_ID_PRZYPADEK
				, w.`A_HAS_IMAGE` as CW_A_HAS_IMAGE
				, cp.`PYTANIE` as CP_PYTANIE, cp.`OPIS` as CP_OPIS, cp.`FORM_TREAT` as CP_FORM_TREAT
			from `CRM_WSKAZNIK` as w
				left join `CRM_LISTA_ZASOBOW` as clz on (clz.`ID`=w.`ID_ZASOB`)
				left join `CRM_PRZYPADEK` as cp on (cp.`ID`=w.`ID_PRZYPADEK`)
			where
				w.`ID_PROCES`='".$id."'
				and w.`A_STATUS` in('WAITING','NORMAL','MONITOR')
			order by w.`SORT_PRIO` asc, w.`ID` asc
		";
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$ret[$r->CW_ID] = $r;
		}
		return $ret;
	}

	public static function getWskaznikiByIds($ids, $params = array()) {
		$wskazniki = array();
		if (empty($ids)) {
			return $wskazniki;
		}
		$db = DB::getDB();
		$sql = "select
				clz.*
				, w.`ID_PROCES`
				, w.`ID` as CW_ID, w.`TYP` as CW_TYP, w.`OPIS_ZASOB` as OPIS_ZASOB, w.`SORT_PRIO` as CW_SORT_PRIO, w.`ID_PRZYPADEK` as CW_ID_PRZYPADEK
				, w.`A_HAS_IMAGE` as CW_A_HAS_IMAGE
				, cp.`PYTANIE` as CP_PYTANIE, cp.`OPIS` as CP_OPIS, cp.`FORM_TREAT` as CP_FORM_TREAT
			from `CRM_WSKAZNIK` as w
				left join `CRM_LISTA_ZASOBOW` as clz on (clz.`ID`=w.`ID_ZASOB`)
				left join `CRM_PRZYPADEK` as cp on (cp.`ID`=w.`ID_PRZYPADEK`)
			where
				w.`ID_PROCES` in(" . implode(",", $ids) . ")
				and w.`A_STATUS` in('WAITING','NORMAL','MONITOR')
			order by w.`SORT_PRIO` asc, w.`ID` asc
		";
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$wskazniki[$r->ID_PROCES][$r->CW_ID] = $r;
		}

		if (1 == V::get('group_stanowiska', '', $params)) {
			foreach ($wskazniki as $kProcesId => $vWskazniki) {
				$stanowiska = array();
				foreach ($vWskazniki as $kWskId => $vWsk) {
					if ($vWsk->TYPE == 'STANOWISKO') {
						$stanowiska[$kWskId] = $vWsk;
					}
				}
				if (!empty($stanowiska)) {
					foreach ($stanowiska as $kWskId => $vWsk) {
						unset($wskazniki[$kProcesId][$kWskId]);
					}
				}
				$newWsk = array('stanowiska'=>$stanowiska, 'inne'=>$wskazniki[$kProcesId]);
				$wskazniki[$kProcesId] = $newWsk;
			}
		}

		return $wskazniki;
	}

	public static function get_list_for_user_count($user_groups, $params = array()) {
		$ret = array();
		$db = DB::getDB();
		$sql = self::_get_list_for_user_sql($user_groups, $params, true);
		$res = $db->query($sql);
		if ($r = $db->fetch($res)) {
			$ret = $r->cnt;
		}
		return $ret;
	}

	public static function get_list_for_user($user_groups, $params = array()) {
		$ret = array();
		$db = DB::getDB();
		$sql = self::_get_list_for_user_sql($user_groups, $params);
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$ret[] = $r;
		}
		return $ret;
	}

	/**
	 * TODO: find user proces
	 */
	public static function _get_list_for_user_sql($user_groups, $params = array(), $count = false) {
		$sql_select = array();
		$sql_limit = "";
		$sql_where = "";

		if ($count) {
			$sql_select[] = "count(1) as cnt";
		} else {
			$sql_select[] = "t.*";
			$sql_limit = V::get('limit', '10', $params);
			$sql_offset = V::get('offset', '0', $params);
			$sql_limit = "limit {$sql_limit} offset {$sql_offset}";
		}

		$sql_select = implode(", ", $sql_select);
		$sql = "select
				{$sql_select}
			from `CRM_PROCES_LOG` as t
	--			left join `` as p on(p.``=t.``)
			where
				{$sql_where}
			{$sql_limit}
		";
		return $sql;
	}

	/**
	 * Get allowed Stanowiska ID for given proces.
	 * @param $proces_id - proces ID
	 * @returns array of Stanowiska ID
	 * Search recursively up by p.PARENT_ID, stop when find p.PROCES_INIT or find z.STANOWISKO
	 */
	public static function get_allowed_stanowiska_by_proces_id($proces_id) {
		$ret = array();
		$proces_id = intval($proces_id);
		if ($proces_id <= 0) return $ret;
		// TODO: DB::get_by_id('CRM_PROCES', $proces_id);
		// TODO: check TYPE=PROCES_INIT -> get STANOWISKO and stop
		// TODO: find wskazniki STANOWISKO
		// TODO: if !empty return else recursive - TODO: add recursive limit!
		$db = DB::getDB();
		$sql = "select
				p.`TYPE` as p__TYPE
				, z.`ID` as z__ID
				, z.`TYPE` as z__TYPE
			from `CRM_PROCES` as p
				left join `CRM_WSKAZNIK` as w on(w.`ID_PROCES`=p.`ID`)
				left join `CRM_LISTA_ZASOBOW` as z on (z.`ID`=w.`ID_ZASOB`)
			where
				p.`ID`='{$proces_id}'
				and w.`A_STATUS` in('WAITING','NORMAL','MONITOR')
				and z.`TYPE`='STANOWISKO'
		";
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$ret[] = $r->z__ID;
		}
	//echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;">ret: ';print_r($ret);echo'</pre>';
		return $ret;
	}

	public static function getProcesByUser($user_name, $groups) {
		$ret = array();
		$db = DB::getDB();
		$sql = "select plog.*
			from `CRM_PROCES_LOG` as plog
			where
				( plog.`A_RECORD_CREATE_AUTHOR`='{$user_name}'
					or plog.`A_RECORD_UPDATE_AUTHOR`='{$user_name}'
				)
			order by plog.ID DESC
		";
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$ret[$r->ID] = $r;
		}
		return $ret;
	}

	public static function split_wskazniki_by_table(&$wsk) {
		$wsk_split = array();
		echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;">wsk: ';print_r($wsk);echo'</pre>';
		foreach ($wsk as $k_id => $w) {
			$w->_parents = TreeHelper::get_all_parents('CRM_LISTA_ZASOBOW', $w->PARENT_ID);
		}
		foreach ($wsk as $k_id => $w) {// $w->CW_ID == $k_id
			if ($w->TYPE == 'KOMORKA') {
				$last_wsk = end($wsk_split);
				if (empty($last_wsk)) {

				}
			} else if ($w->TYPE == 'DOKUMENTY') {
				// file
			}
		}
	}

	/**
	 * @param $stanowiska_id - array of integer
	 */
	public static function get_procesy_by_stanowiska($stanowiska_id = array()) {
		$db = DB::getDB();
		$ret = array();
		if (empty($stanowiska_id)) {
			return $ret;
		}
		$sql_stanowiska_id = array();
		foreach ($stanowiska_id as $v_id) {
			$v_id = intval($v_id);
			if ($v_id > 0) {
				$sql_stanowiska_id[] = "'{$v_id}'";
			}
		}
		if (empty($sql_stanowiska_id)) {
			return $ret;
		}
		$sql = "select
				p.`ID`
				, p.`PARENT_ID`
				, p.`TYPE`
				, p.`DESC`
				, w.`ID` as w__ID
				, w.`OPIS_ZASOB` as w__OPIS_ZASOB
				, z.`ID` as z__ID
			from `CRM_PROCES` as p
				left join `CRM_WSKAZNIK` as w on(w.`ID_PROCES`=p.`ID`)
				left join `CRM_LISTA_ZASOBOW` as z on(z.`ID`=w.`ID_ZASOB`)
			where
				p.`TYPE`='PROCES_INIT'
				and p.`A_STATUS` in('WAITING','NORMAL','MONITOR')
				and w.`A_STATUS` in('WAITING','NORMAL','MONITOR')
			--	and w.`ID_PRZYPADEK`=1
				and z.`A_STATUS` in('WAITING','NORMAL','MONITOR')
				and z.`TYPE`='STANOWISKO'
				and z.`ID` is not null
				and z.`ID` in (" . implode(",", $sql_stanowiska_id) . ")
			order by p.`TEST_SORT_PRIO` DESC, p.`ID` ASC
		";
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$ret[] = $r;
		}
		return $ret;
	}

	/**
	 * List of PROCES_INIT - only order info.
	 * @return array ID => TEST_SORT_PRIO
	 */
	public static function get_procesy_init_list_order() {
		$ret = array();
		$db = DB::getDB();
		$sql = "select
				p.`ID`
				, p.`TEST_SORT_PRIO`
			from `CRM_PROCES` as p
			where
				p.`TYPE`='PROCES_INIT'
				and p.`A_STATUS` in('WAITING','NORMAL','MONITOR')
			order by p.`TEST_SORT_PRIO` DESC, p.`ID` ASC
		";
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$ret[$r->ID]= $r->TEST_SORT_PRIO;
		}
		return $ret;
	}

	/**
	 * List of PROCES_INIT to sort.
	 */
	public static function get_procesy_init_list() {
		$ret = array();
		$db = DB::getDB();
		$sql = "select
				p.`ID`
				, p.`PARENT_ID`
				, p.`TYPE`
				, p.`DESC`
				, p.`TEST_SORT_PRIO`
--				, w.`ID` as w__ID
--				, w.`OPIS_ZASOB` as w__OPIS_ZASOB
--				, z.`ID` as z__ID
				, cps.`path`
				, p.`TEST_SORT_PRIO`
				, p.`SORT_PRIO`
			from `CRM_PROCES` as p
--				left join `CRM_WSKAZNIK` as w on(w.`ID_PROCES`=p.`ID`)
--				left join `CRM_LISTA_ZASOBOW` as z on(z.`ID`=w.`ID_ZASOB`)
				left join `_CRM_PROCES_STATS_proc_wiev` as cps on(p.`ID`=cps.`ID`)
			where
				p.`TYPE`='PROCES_INIT'
				and p.`A_STATUS` in('WAITING','NORMAL','MONITOR')
--				and w.`A_STATUS` in('WAITING','NORMAL','MONITOR')
--				and w.`ID_PRZYPADEK`=1
--				and z.`A_STATUS` in('WAITING','NORMAL','MONITOR')
--				and z.`TYPE`='STANOWISKO'
--				and z.`ID` is not null
			order by p.`TEST_SORT_PRIO` DESC, p.`ID` ASC
		";
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$ret[] = $r;
		}
		return $ret;
	}

	/**
	 * Przeniesienie procesu w kolejności w testach.
	 */
	public static function proces_init_move($proces_id, $sort_prio_dir) {
		$db = DB::getDB();

		$proces_list = self::get_procesy_init_list();
		$proces_list = array_reverse($proces_list);

		$wsk = array();
		$wsk_order = array();
		$sort_prio = 0;
		foreach ($proces_list as $r) {
			$wsk[$r->ID] = $sort_prio;//$r->SORT_PRIO;
			$wsk_order[$sort_prio] = $r->ID;
			$sort_prio += 1;
		}
		if (empty($wsk)) return;
		$wsk_new = array();
		if ($sort_prio_dir == 'dw') {// wskaznik $wsk_id 'w gore' (zmniejsz sort_prio)
			$old_wsk_id = $wsk[$proces_id];
			if ($old_wsk_id == 0) return;
			//echo'zamien "'.$proces_id.'" na "'.$wsk_order[$wsk[$proces_id] - 1].'"';
			$tmp = $wsk_order[$wsk[$proces_id]];
			$wsk_order[$wsk[$proces_id]] = $wsk_order[$wsk[$proces_id] - 1];
			$wsk_order[$wsk[$proces_id] - 1] = $tmp;
		} else {// wskaznik $wsk_id 'w dol' (zwieksz sort_prio)
			$old_wsk_id = $wsk[$proces_id];
			if ($old_wsk_id + 1 == count($wsk)) return;
			//echo'zamien "'.$proces_id.'" na "'.$wsk_order[$wsk[$proces_id] + 1].'"';
			$tmp = $wsk_order[$wsk[$proces_id]];
			$wsk_order[$wsk[$proces_id]] = $wsk_order[$wsk[$proces_id] + 1];
			$wsk_order[$wsk[$proces_id] + 1] = $tmp;
		}
		if (empty($wsk_order)) return;
		foreach ($wsk_order as $k_sort_prio => $v_proces_id) {
			$sql = "update `CRM_PROCES` set `TEST_SORT_PRIO`='{$k_sort_prio}' where `ID`='{$v_proces_id}'; ";
			$db->query($sql);
		}
		return true;
	}

	public static function proces_flag($proces_id, $goto_id, $flag) {
		$ret = '';
		switch ($flag) {
			case 'GOTO':
				$ret = '<span style="color:#FF0000">' . "&rarr;" . '</span>' . $goto_id;
				break;
			case 'GOTO_AND_RETURN':
				$ret = '<span style="color:#FF0000">' . "&rarr;" . '</span>' . $goto_id . '<span style="color:#FF0000">' . "&crarr;" . '</span>';
				break;
			case 'FORK':
				$ret = '<span style="color:#FF0000">' . "&oplus;&rarr;" . '</span>' . $goto_id;
				break;
		}
		return $ret;
	}

	/**
	 * Get proces GOTO data list.
	 * @param array $params
	 *   $params['return_by']:
	 *     '' - default retrun array('ID'=>$row)
	 *     'dest' - return array('IF_TRUE_GOTO'=>array('ID'=>$row))
	 * @returns array
	 */
	public static function get_goto_list($params = array()) {
		$ret = array();
		$return_by = V::get('return_by', '', $params);
		$db = DB::getDB();
		$sql = "select
				p.`ID`
				, p.`IF_TRUE_GOTO`
				, p.`IF_TRUE_GOTO_FLAG`
			from `CRM_PROCES` as p
			where
				p.`IF_TRUE_GOTO`>0
				and p.`PARENT_ID`>=0
				and p.`A_STATUS` in('WAITING','NORMAL')
		";
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			if ($return_by == 'dest') {
				$ret[$r->IF_TRUE_GOTO][] = $r;
			} else {
				$ret[$r->ID] = $r;
			}
		}
		return $ret;
	}

	public static function getGoToProces($id) {
		static $_cache;
		if (!is_array($_cache)) {
			$_cache = array();
			$db = DB::getDB();
			 //todo do optymalizacji ze struktur
			$sql="select p.`ID`, p.`IF_TRUE_GOTO`, p.`IF_TRUE_GOTO_FLAG`
				from `CRM_PROCES` as p
				where p.`IF_TRUE_GOTO` > 0
					and p.`A_STATUS`!='DELETED'
			";
			$res = $db->query($sql);
			while ($r = $db->fetch($res)) {
				$_cache[$r->IF_TRUE_GOTO][$r->ID] = $r->IF_TRUE_GOTO_FLAG;
			}
		}

		$gotoList = array();
		if (array_key_exists($id, $_cache)) {
			$gotoList = $_cache[$id];
		}

		return $gotoList;
	}

	public static function get_wskaznik($wsk_id) {
		$ret = array();
		$db = DB::getDB();
		$sql = "select
				clz.*
				, w.`ID` as CW_ID, w.`TYP` as CW_TYP, w.`OPIS_ZASOB` as OPIS_ZASOB, w.`SORT_PRIO` as CW_SORT_PRIO, w.`ID_PRZYPADEK` as CW_ID_PRZYPADEK
				, w.`A_HAS_IMAGE` as CW_A_HAS_IMAGE
				, cp.`PYTANIE` as CP_PYTANIE, cp.`OPIS` as CP_OPIS, cp.`FORM_TREAT` as CP_FORM_TREAT
			from `CRM_WSKAZNIK` as w
				left join `CRM_LISTA_ZASOBOW` as clz on (clz.`ID`=w.`ID_ZASOB`)
				left join `CRM_PRZYPADEK` as cp on (cp.`ID`=w.`ID_PRZYPADEK`)
			where
				w.`ID`='{$wsk_id}'
				and w.`A_STATUS` in('WAITING','NORMAL','MONITOR')
		";
		$res = $db->query($sql);
		if ($r = $db->fetch($res)) {
			$ret[$r->CW_ID] = $r;
		}
		return $ret;
	}

	public static function get_proces($next_id) {
		return DB::get_by_id('CRM_PROCES', $next_id);
	}

	public static function get_proces_init_by_zasob_id($id_zasob) {
		$db = DB::getDB();
		$sqlFilter = " cw.ID_ZASOB={$id_zasob}";
		if (is_array($id_zasob)) {
			$sqlFilter = " cw.ID_ZASOB in ".implode(',', $id_zasob);
		}
		$sql = "select
				cps.ID_PROCES_INIT
				, cw.ID_PROCES
			from CRM_WSKAZNIK as cw
				left join _CRM_PROCES_STATS_proc_wiev as cps on(cps.ID = cw.ID_PROCES)
			where cw.A_STATUS in('NORMAL', 'WAITING')
				and cps.ID_PROCES_INIT > 0 and
				{$sqlFilter}
		";
		$res = $db->query($sql);
		while ($h = $db->fetch($res)) {
			$ret[] = $h->ID_PROCES_INIT;
		}
		return $ret;
	}

	public static function getZasobTableInfo($idTable) {
		$tableInfo = null;
		$db = DB::getDB();
		$sql = <<<SQL
			select tv.`ID_TABLE` as `ID`
				, tv.`TABLE_NAME` as `DESC`
				, tv.`TABLE_LABEL` as `DESC_PL`
				, tv.`TABLE_DESCRIPTION` as `OPIS`
				, zpp.`ID` as P__ID
				, zpp.`DESC` as P__DESC
				, zpp.`TYPE` as P__TYPE
			from `CRM_PROCES_idx_TABLES_INFO_VIEW` as tv
				join `CRM_LISTA_ZASOBOW` as zpp on(zpp.`ID`=tv.`ID_DATABASE`)
			where tv.`ID_TABLE`='{$idTable}'
			limit 1
SQL;
		$res = $db->query($sql);
		if ($res) {
			$tableInfo = $db->fetch($res);
		}
		return $tableInfo;
	}

	public static function getZasobTableInfoByUri($uri) {
		$zasobObj = null;
		$db = DB::getDB();
		$uriParts = explode('/', $uri);
		if (count($uriParts) < 2) {
			return null;
		}
		$tblName = array_pop($uriParts);
		$dbName = array_pop($uriParts);
		if ('default_db' == $dbName) {
			$dbName = $db->getDatabaseName();
		}
		$sql = "select z.`ID`, z.`DESC`, z.`DESC_PL`, z.`OPIS`
				, zp.`ID` as P__ID, zp.`DESC` as P__DESC, zp.`TYPE` as P__TYPE
			from `CRM_LISTA_ZASOBOW` as z
				left join `CRM_LISTA_ZASOBOW` as zp on(zp.`ID`=z.`PARENT_ID`)
			where z.`DESC`='{$tblName}'
				and z.`TYPE`='TABELA'
		--		and zp.`DESC`='{$dbName}'
				and zp.`TYPE` in('DATABASE_MYSQL','DATABASE','BAZA_DANYCH')
		";
		DBG::_('DBG_SQL', '1', "sql", $sql, __CLASS__, __FUNCTION__, __LINE__);
		$res = $db->query($sql);
		if ($res) {
			$zasobObj = $db->fetch($res);
		}
		return $zasobObj;
	}

	public static function getZasobTableFieldsInfo($id) {
		$fldsInfo = array();
		$db = DB::getDB();
		$sql = "select z.`ID`, z.`DESC`, z.`DESC_PL`, z.`OPIS`, z.`SORT_PRIO`
			from `CRM_LISTA_ZASOBOW` as z
			where z.`TYPE`='KOMORKA'
				and z.`PARENT_ID`={$id}
		";
		if('1' == V::get('DBG_SQL', '', $_GET)){echo'<pre style="max-height:200px;overflow:auto;border:1px solid green;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$fldsInfo[$r->DESC] = $r;
		}
		return $fldsInfo;
	}

	public static function getZasobInfo($zasobID) {
		$zasobObj = null;
		$db = DB::getDB();
		$sql = "select z.`ID`, z.`DESC`, z.`DESC_PL`, z.`OPIS`, z.`TYPE`
				, zp.`ID` as P__ID
				, zp.`DESC` as P__DESC
				, zp.`TYPE` as P__TYPE
			from `CRM_LISTA_ZASOBOW` as z
				left join `CRM_LISTA_ZASOBOW` as zp on(zp.`ID`=z.`PARENT_ID`)
			where z.`ID`='{$zasobID}'
		";
		$res = $db->query($sql);
		if ($res) {
			$zasobObj = $db->fetch($res);
		}
		return $zasobObj;
	}

	public static function getZasobTableID($tblName) {
		$zasobID = 0;
		$db = DB::getDB();
		$sql = "select z.`ID`
			from `CRM_LISTA_ZASOBOW` as z
			where z.`DESC`='{$tblName}'
				and z.`TYPE`='TABELA'
				and z.`A_STATUS` in('NORMAL','WAITING')
		";
		$res = $db->query($sql);
		if ($res) {
			$r = $db->fetch($res);
			$zasobID = $r->ID;
		}
		return $zasobID;
	}

}