se/SE/se-lib/ProcesTestyHelper.php

<?php


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


	public static 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`>=3
			and `A_STATUS`!='OFF_HARD' and  `A_STATUS`!='DELETED'
			 and `EMPLOYEE_TYPE` like '%Kandydat%'
			{$sql_order_by}
			limit 1000
		";
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$ret[] = $r;
		}
		return $ret;
	}

	public static 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);
	}

	public static 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;
	}

	public static 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;
	}

	public static function get_testy($params = array()) {
		$ret = array();
		$sql_select = array();
		$sql_select[] = "t.`ID`";
		$sql_select[] = "t.`ID_TEST_TO_FIX`";
		$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} ) )
	 */
	public static 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;
	}

	public static 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
	 */
	public static 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.
	 * moved to trigger after insert/delete `CRM_TESTY_PYTANIA`
	 */
	public static 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;
	}

	public static 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
		";
		$db->query($sql);
		$affected = $db->affected_rows();

		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)
	 */
	public static 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 ('' != $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
	 */
	public static 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 MAX(tbl.`ID`) as `ID`
				, tbl.`ID_PROCES_INIT`
				, tbl.`OCENA`
				, tbl.`A_STATUS`
				, tbl.`TEST_TYPE`
				, tbl.`TEST_END`
			from ( select t.`ID`, 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
	 */
	public static function get_max_update_date($steps_ids) {// TODO: RMME, mved to UserAcl->getProcesMaxUpdateDate
		$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;
	}

}