se/SE/se-lib/ProcesHelper.php

<?php


class ProcesHelper {


public static function get_wskazniki( $id ) {
	$ret = array();
	$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();
	$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();
	$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!
	$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();
	$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
		}
	}//end foreach
	
}


	/**
	 * @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
			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
			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
			";
			$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();
		$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($zasobID) {
		$zasobObj = null;
		$db = DB::getDB();
		$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.`ID`='{$zasobID}'
				and z.`TYPE`='TABELA'
		";
		$res = $db->query($sql);
		if ($res) {
			$zasobObj = $db->fetch($res);
		}
		return $zasobObj;
	}

	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'
		";
		$res = $db->query($sql);
		if ($res) {
			$r = $db->fetch($res);
			$zasobID = $r->ID;
		}
		return $zasobID;
	}

}