se/SE/se-lib/ProcesTestyHelper.php

<?php


/**
 * Proces Testy helper.
 * 
 * @use table `CRM_TESTY`
 */
class ProcesTestyHelper {


function &get_kandydaci( $params = array() ) {
	$ret = array();
	$params['order by'] = V::get('order by', '', $params);
	$allowed_order_by = array();
	$allowed_order_by ['ID']= 'DESC';
	$allowed_order_by ['ADM_NAME']= 'ASC';
	if (!array_key_exists($params['order by'], $allowed_order_by)) {
		$params['order by'] = "";
	}
	$sql_order_by = "";
	if ($params['order by']) {
		$sql_order_by = "order by " . $params['order by'] . " " . $allowed_order_by[ $params['order by'] ];
	}
	$db = DB::getDB();
	$sql = "select `ID`,`ADM_NAME`,`ADM_PHONE`,`ADM_OTHER_INFO`
		from `ADMIN_USERS`
		where `ADM_ADMIN_LEVEL`='6'
			and `A_STATUS`='WAITING'
			and `ADM_AREA` like 'KANDYDAT'
		" . $sql_order_by . "
		limit 1000
	";
	$res = $db->query($sql);
	while ($r = $db->fetch($res)) {
		$ret []= $r;
	}
	return $ret;
}


function &get_testy_by_kandydat( $kandydat_id ) {
	$params = array();
	$params['id_tester'] = $kandydat_id;
	$params['order_by'] = 'ID';
	$params['order_dir'] = 'desc';
	return self::get_testy( $params );
}


function _get_testy_sql_where($params = array()) {
	$sql_where = "1=1";
	if (($id_tester = V::get('id_tester', 0, $params, 'int')) > 0) {
		$sql_where .= " and t.`ID_TESTER`='{$id_tester}'";
	}
	if ('TAK' == V::get('SHOW_IN_CERT', '', $params)) {
		$sql_where .= " and t.`SHOW_IN_CERT`='TAK'";
	}
	return $sql_where;
}

function &get_testy_total( $params = array() ) {
	$ret = 0;
	$sql_where = self::_get_testy_sql_where($params);
	$db = DB::getDB();
	$sql = "select
			count(1) as cnt
		from `CRM_TESTY` as t
		where
			".$sql_where."
	";
	$res = $db->query($sql);
	if ($r = $db->fetch($res)) {
		$ret = $r->cnt;
	}
	return $ret;
}


function &get_testy( $params = array() ) {
	$ret = array();
	$sql_select = array();
	$sql_select []= "t.`ID`";
	$sql_select []= "t.`A_STATUS`";
	$sql_select []= "t.`A_RECORD_CREATE_DATE`";
	$sql_select []= "t.`ID_TESTER`";
	$sql_select []= "t.`ID_PROCES_INIT`";
	$sql_select []= "t.`ID_STANOWISKO`";
	$sql_select []= "t.`TEST_INIT`";
	$sql_select []= "t.`TEST_START`";
	$sql_select []= "t.`TEST_END`";
	$sql_select []= "t.`UWAGI`";
	$sql_select []= "t.`OCENA`";
	$sql_select []= "t.`DISCOUNT`";
	$sql_select []= "t.`ID_ORGANIZER`";
	$sql_select []= "t.`TEST_PLACE`";
	//$sql_select []= "t.`TEST_TIME`";
	$sql_select []= "t.`COST`";
	$sql_select []= "t.`TEST_TYPE`";
	$sql_select []= "z.`DESC` as Stanowisko";
	$sql_select []= "concat(u.`ADM_NAME`, ' (', u.`ADM_PHONE`, ', ', u.`ADM_OTHER_INFO`, ')') as Kandydat";
	$sql_select []= "IF( u.`ID` is null, t.`ID_TESTER`, u.`ADM_NAME`) as Kandydat_Name";
	$sql_select []= "t.`SHOW_IN_CERT`";
	$sql_join = array();
	$sql_order_by = "";
	if (($p_order_by = V::get('order_by', '', $params)) != '') {
		$sql_order_by = "order by ".$p_order_by;
		if (in_array(strtoupper($p_order_dir = V::get('order_dir', '', $params)), array('ASC','DESC'))) {
			$sql_order_by .= " ".$p_order_dir;
		}
	}
	$sql_limit = V::get('limit', 10, $params, 'int');
	if (($p_offset = V::get('offset', 0, $params, 'int')) > 0) {
		$sql_limit .= " offset ".$p_offset;
	}

	$sql_where = self::_get_testy_sql_where($params);

	if ((true == V::get('proces_name', '', $params))) {
		$sql_join []= "left join `CRM_PROCES` as p on(p.`ID`=t.`ID_PROCES_INIT`)";
		$sql_select []= "p.`DESC` as Proces_Name";
	}

	if ((true == V::get('organizer_name', '', $params))) {
		$sql_join []= "left join `ADMIN_USERS` as u_org on(u_org.`ID`=t.`ID_ORGANIZER`)";
		$sql_select []= "u_org.`ADM_NAME` as Organizer";
	}

	$sql_join = implode("\n\t", $sql_join);
	$sql_select = implode(", ", $sql_select);
	$db = DB::getDB();
	$sql = "select
			".$sql_select."
		from `CRM_TESTY` as t
			left join `CRM_LISTA_ZASOBOW` as z on(z.`ID`=t.`ID_STANOWISKO`)
			left join `ADMIN_USERS` as u on(u.`ID`=t.`ID_TESTER`)
			".$sql_join."
		where
			".$sql_where."
		".$sql_order_by."
		limit ".$sql_limit."
	";
	$res = $db->query($sql);
	while ($r = $db->fetch($res)) {
		//$sql_select []= "IF( z.`ID` is null, t.`ID_STANOWISKO`, z.`DESC` ) as Stanowisko";
		//$sql_select []= "IF( u.`ID` is null, t.`ID_TESTER`, concat(u.`ADM_NAME`, ' (', u.`ADM_PHONE`, ', ', u.`ADM_OTHER_INFO`, ')') ) as Kandydat";
		//$sql_select []= "IF( u.`ID` is null, t.`ID_TESTER`, u.`ADM_NAME`) as Kandydat_Name";
		if (!$r->Stanowisko) {
			$r->Stanowisko = $r->ID_STANOWISKO;
		}
		if (!$r->Kandydat) {
			$r->Kandydat = $r->ID_TESTER;
		}
		if (!$r->Kandydat_Name) {
			$r->Kandydat_Name = $r->ID_TESTER;
		}

		$ret []= $r;
	}
	return $ret;
}


/**
 * @return array( ID_PROCES => array( ID_PYTANIE => {ID,ODPOWIEDZ,ODP_0,ODP_1,ODP_2,ODP_3,ODP_4,OCENA} ) )
 */
function &get_odpowiedzi( $test_id, $type = 'TEORETYCZNY' ) {
	$ret = array();
	$sql_type = "";
	$types_allowed = self::get_allowed_types();
	if (array_key_exists($type, $types_allowed)) {
		$sql_type = "and pyt.`TEST_TYPE`='" . $type . "'";
	}
	$db = DB::getDB();
	$sql = "select
			odp.`ID`
			, odp.`ID_PYTANIE`
			, pyt.`ID_PROCES` as ID_PROCES
			, odp.`ODPOWIEDZ`
			, odp.`ODP_0`
			, odp.`ODP_1`
			, odp.`ODP_2`
			, odp.`ODP_3`
			, odp.`ODP_4`
			, odp.`OCENA`
		from `CRM_TESTY_ODPOWIEDZI` as odp
			left join `CRM_TESTY_PYTANIA` as pyt on(pyt.`ID`=odp.`ID_PYTANIE`)
		where
			pyt.`ID` is not NULL
			and odp.`ID_TEST`='".$test_id."'
			" . $sql_type . "
	";
	$res = $db->query($sql);
	while ($r = $db->fetch($res)) {
		$ret[$r->ID_PROCES][$r->ID_PYTANIE] = $r;
	}
	return $ret;
}


// TODO: RMME @use get_odpowiedzi
function &get_oceny( $test_id ) {
	$ret = array();
	$db = DB::getDB();
	$sql = "select
			odp.`ID_PYTANIE`
			, pyt.`ID_PROCES` as ID_PROCES
			, odp.`OCENA`
		from `CRM_TESTY_ODPOWIEDZI` as odp
			left join `CRM_TESTY_PYTANIA` as pyt on(pyt.`ID`=odp.`ID_PYTANIE`)
		where
			pyt.`ID` is not NULL
			and odp.`ID_TEST`='".$test_id."'
	";
	$res = $db->query($sql);
	while ($r = $db->fetch($res)) {
		$ret[$r->ID_PROCES][$r->ID_PYTANIE] = $r->OCENA;
	}
	return $ret;
}


function &get_pytania_by_proces_id( $proces_id, $type = 'TEORETYCZNY' ) {
	$ret = array();
	$sql_type = "";
	$types_allowed = self::get_allowed_types();
	if (array_key_exists($type, $types_allowed)) {
		$sql_type = "and pyt.`TEST_TYPE`='".$type."'";
	}
	$db = DB::getDB();
	$sql = "select pyt.*
		from `CRM_TESTY_PYTANIA` as pyt
		where
			pyt.`ID_PROCES`='".$proces_id."'
			" . $sql_type . "
		order by pyt.`ID` ASC
	";
	$res = $db->query($sql);
	while ($r = $db->fetch($res)) {
		$ret[$r->ID] = $r;
	}
	return $ret;
}


/**
 * @param $pytania_id - array if int
 */
function &get_pytania_by_id( $pytania_id ) {
	$ret = array();
	if (empty($pytania_id)) return $ret;
	// TODO: if int or array
	if (!is_array($pytania_id)) $pytania_id = array($pytania_id);
	$db = DB::getDB();
	$sql = "select
			pyt.*
		from `CRM_TESTY_PYTANIA` as pyt
		where
			pyt.`ID` in(".implode(",", $pytania_id).")
	";
	$res = $db->query($sql);
	while ($r = $db->fetch($res)) {
		$ret[$r->ID] = $r;
	}
	return $ret;
}


/**
 * Updates field TEST_PYTANIE in remote table.
 */
function update_proces_stat($remote_id) {
	$db = DB::getDB();
	$sql = "update `CRM_PROCES`
			set `TEST_PYTANIE`=(
				select count(1)
				from `CRM_TESTY_PYTANIA`
				where
					`ID_PROCES`='".$remote_id."'
			)
		where `ID`='".$remote_id."'
		limit 1;
	";
	// update all records
	$sql = "update `CRM_PROCES` as p
		set p.`TEST_PYTANIE`=(
			select count(1)
			from `CRM_TESTY_PYTANIA` as pyt
			where
				pyt.`ID_PROCES`=p.ID
		)
	";
	$res = $db->query($sql);
	$affected = $db->affected_rows();
	return $affected;
}


function delete_pytanie($id, $remote_id) {
	$db = DB::getDB();
	$sql = "delete from `CRM_TESTY_PYTANIA`
		where
			`ID`='".$id."'
			and `ID_PROCES`='".$remote_id."'
		limit 1
	";
	//echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;">sql delete = ';print_r($sql);echo'</pre>';
	$db->query($sql);
	$affected = $db->affected_rows();

	self::update_proces_stat($remote_id);

	return $affected;
}


/**
 * Ocena pytania.
 * @param $p - sql object pytanie
 * @param $selected_values - array of user selected values
 * @returns null if pytanie is openm ale return integer value 0 - 3 (avg)
 */
function ocen_pytanie(&$p, &$selected_values) {
	$ret = null;
	/**
	 * TODO: pokaz tylko odpowiedz udzielona przez kandydata i czy jest prawidlowa
	 * ODP_{id_pyt}
	 * ODP_{id_pyt}_{id_odp} (TAK/NIE)
	 */
	$pytanie_otwarte = true;
	$avg_odp_cnt = 0;
	$avg_odp_ok_cnt = 0;
	for ($i = 0; $i < 5; $i++) {
		$odp_field = "ODP_".$i;
		if (!empty($p->$odp_field)) {// jest odpowiedz do pytania
			$avg_odp_cnt += 1;
			$p_field_prawidlowa_odp = "ODP_".$i."_ODP";
			$pytanie_otwarte = false;
			if (!empty($selected_values[$i]) != '' && $p->$p_field_prawidlowa_odp == $selected_values[$i]) {
				$avg_odp_ok_cnt += 1;
			}
		}
	}
	if (!$pytanie_otwarte) {
		$ret = round((3 * floatval($avg_odp_ok_cnt)) / $avg_odp_cnt);
	}
	return $ret;
}


	/**
	 * CRM_TESTY_PYTANIA.TEST_TYPE
	 * CRM_TESTY.TEST_TYPE
	 */
	function get_allowed_types() {
		$types_allowed = array('TEORETYCZNY'=>"Teoretyczny", 'PRAKTYCZNY'=>"Praktyczny");
		return $types_allowed;
	}

	/**
	 * Get Testy stats by user.
	 * 
	 * @param int $user_id
	 * @param int $max_age
	 * @param array $proces_ids
	 * 
	 * @usage $testy = ProcesTestyHelper::get_tetsy_stats($user_id, 30000000, $proces_ids);
	 */
	public static function get_tetsy_stats($user_id, $max_age = 0, $proces_ids = array()) {
		$testy_stats = array();
		Lib::loadClass('DB');
		$db = DB::getDB();
		$sql_where_arr = array();
		$sql_where_arr []= "t.`ID_TESTER`='{$user_id}'";
		if (!empty($proces_ids)) {
			$sql_where_arr []= "t.`ID_PROCES_INIT` in (" . implode(",", $proces_ids) . ")";
		}
		if ($max_age > 0) {
			$sql_where_arr []= "UNIX_TIMESTAMP(t.`TEST_END`) > (UNIX_TIMESTAMP(NOW()) - $max_age)";
		}
		$sql = "select tbl.*
			from ( select t.`ID_PROCES_INIT`, t.`OCENA`, t.`A_STATUS`, t.`TEST_TYPE`, t.`TEST_END`
				from `CRM_TESTY` as t
				where " . implode("\n and ", $sql_where_arr) . "
				order by t.`ID` DESC
			) AS tbl
			group by tbl.TEST_TYPE, tbl.ID_PROCES_INIT
		";
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$testy_stats []= $r;
		}
		return $testy_stats;
	}

	/**
	 * Remove tests which is unactual - proces or step has changed.
	 * @param $steps_ids
	 */
	function get_max_update_date($steps_ids) {
		$max_update_date = null;
		$db = DB::getDB();
		$sql = "select max(p.`A_RECORD_UPDATE_DATE`) as max_update_date
			from `CRM_PROCES` as p
			where p.`ID` in(" . implode(", ", $steps_ids) . ")
		";
		$res = $db->query($sql);
		if ($r = $db->fetch($res)) {
			$max_update_date = $r->max_update_date;
		}
		return $max_update_date;
	}

	public static function getLastTestEndDate($id_test, $id_tester, $id_proces_init, $id_stanowisko) {
		$lastTestEnd = null;
		$db = DB::getDB();
		$sql = "select t.`TEST_END`
			from `CRM_TESTY` as t
			where
				t.`ID_TESTER`={$id_tester}
				and t.`ID_PROCES_INIT`={$id_proces_init}
				and t.`ID_STANOWISKO`={$id_stanowisko}
				and t.`ID`<{$id_test}
			limit 1
		";
		$res = $db->query($sql);
		if ($r = $db->fetch($res)) {
			$lastTestEnd = $r->TEST_END;
		}
		return $lastTestEnd;
	}

}