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()) {
		if (empty($ids)) return [];
		$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
		";
		$wskazniki = array();
		foreach (DB::getPDO()->fetchAll($sql) as $row) {
			$wskazniki[ $row['ID_PROCES'] ][ $row['CW_ID'] ] = (object)$row;
		}

		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 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()) {
		if (empty($stanowiska_id)) return [];
		$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 = array();
		$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
		";
		return array_map(function ($row) {
			return (object)$row;
		}, DB::getPDO()->fetchAll($sql));
	}

	/**
	 * List of PROCES_INIT - only order info.
	 * @return array ID => TEST_SORT_PRIO
	 */
	public static function get_procesy_init_list_order() {
		$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
		";
		return array_map(function ($row) {
			return $row['TEST_SORT_PRIO'];
		}, DB::getPDO()->fetchAllByKey($sql, 'ID'));
	}

	/**
	 * List of PROCES_INIT to sort.
	 */
	public static function get_procesy_init_list() {
		$ret = array();
		$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
		";
		return array_map(function ($row) {
			return (object)$row;
		}, DB::getPDO()->fetchAll($sql));
	}

	/**
	 * Przeniesienie procesu w kolejności w testach.
	 */
	public static function proces_init_move($proces_id, $sort_prio_dir) {
		$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;
		$db = DB::getDB();
		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) {
		switch ($flag) {
			case 'GOTO': return '<span style="color:#FF0000">' . "&rarr;" . '</span>' . $goto_id;
			case 'GOTO_AND_RETURN': return '<span style="color:#FF0000">' . "&rarr;" . '</span>' . $goto_id . '<span style="color:#FF0000">' . "&crarr;" . '</span>';
			case 'FORK': return '<span style="color:#FF0000">' . "&oplus;&rarr;" . '</span>' . $goto_id;
		}
		return '';
	}

	/**
	 * 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);
		}
		return array_map(function ($row) {
			return $row['ID_PROCES_INIT'];
		}, DB::getPDO()->fetchAll("
			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}
		"));
	}

	public static function getZasobTableInfo($idTable) {
		return DB::getPDO()->fetchFirstAsObject("
			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
		");
	}

	public static function getZasobTableInfoByUri($uri) {
		$zasobObj = null;
		$uriParts = explode('/', $uri);
		if (count($uriParts) < 2) {
			return null;
		}
		$tblName = array_pop($uriParts);
		$dbName = array_pop($uriParts);
		if ('default_db' === $dbName) {
			$dbName = DB::getPDO()->getDatabaseName();
		}
		$zasobInfo = DB::getPDO()->fetchFirst("
			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')
		");
		return ($zasobInfo) ? (object)$zasobInfo : null;
	}

	public static function getZasobTableFieldsInfo($id) {
		$fldsInfo = array();
		$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}
		";
		return array_map(function ($row) {
			return (object)$row;
		}, DB::getPDO()->fetchAllByKey($sql, 'DESC'));
	}

	public static function getZasobInfo($zasobID) {
		return DB::getPDO()->fetchFirstAsObject("
			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}'
		");
	}

	public static function getZasobTableID($tblName) {
		return DB::getPDO()->fetchValue("
			select z.`ID`
			from `CRM_LISTA_ZASOBOW` as z
			where z.`DESC`='{$tblName}'
				and z.`TYPE`='TABELA'
				and z.`A_STATUS` in('NORMAL','WAITING')
		");
	}

}