se/SE/se-lib/Core/DataSource/Mssql.php

<?php

Lib::loadClass('Core_DataSource');

class Core_DataSource_Mssql extends Core_DataSource {

	function __construct($host, $user, $password, $database, $names = '', $params = array()) {
		if ($names != '') {
			if (strtolower($names) == 'utf8') {
				$names = 'UTF-8';// @see http://stackoverflow.com/questions/1322421/php-sql-server-how-to-set-charset-for-connection
			}
			ini_set('mssql.charset', $names);
		}

		if (!empty($params['tdsver'])) {
			$tdsver = V::get('tdsver', '', $params);
			putenv("TDSVER={$tdsver}");
		}

		$this->_database_name = $database;
		$this->_conn = @mssql_connect($host, $user, $password);
		if (!is_resource($this->_conn)) {
			$this->_set_error('CREATE CONNECTION FAILED');
			return;
		}
		if (false === mssql_select_db($database, $this->_conn)) {
			$this->_set_error('SELECT DATABASE FAILED');
			return;
		}


		if ($names != '') {
			//$this->query(" SET NAMES '$names' ");
		}
	}

	function getConnection() {
		return $this->_conn;
	}

	function getVersion($version) {
		if (!$this->_version) {
			// TODO: get version sql
		}
		return $this->_version;
	}

	/**
	 * Wykonuje podane zapytanie i zwraca wynik mssql_query().
	 */
	function query($query, $msg = 'Query ERROR.') {
		$null = null;
		if (!$this->_conn) { return $null; }
		$res = mssql_query($query, $this->_conn);
		if (!$res) {
			$this->_set_error('SQL QUERY FAILED: ' . mssql_get_last_message());
			return $null;
		}
		return $res;
	}

	function fetch($res) {
		if (!is_resource($res)) return null;
		return mssql_fetch_object($res);
	}

	function fetch_row($res) {
		if (!is_resource($res)) return null;
		return mssql_fetch_row($res);
	}

	/**
	 * Returns an associative array that corresponds to the fetched row and moves
	 * the internal data pointer ahead. mysql_fetch_assoc() is equivalent to calling
	 * mysql_fetch_array() with MYSQL_ASSOC for the optional second parameter.
	 * It only returns an associative array.
	 */
	function fetch_assoc($res) {
		$ret = null;
		if (!is_resource($res)) {
			return null;
		} else {
			$ret = mssql_fetch_assoc($res);
		}
		return $ret;
	}

	function fetch_array($res, $result_type = null) {
		if (!is_resource($res)) return null;
		return ($result_type)? mssql_fetch_array($res, $result_type) : mssql_fetch_array($res);
	}

	function count($res) {
		if (!is_resource($res)) return null;
		return mssql_num_rows($res);
	}

	function num_rows($res) {
		if (!is_resource($res)) return null;
		return mssql_num_rows($res);
	}

	function insert_id() {
		$id = 0;
		$res = $this->query("SELECT @@identity AS id");
		if ($row = $this->fetch_assoc($res)) {
			$id = $row["id"];
		}
		return $id;
	}

	function affected_rows() {
		return mssql_rows_affected($this->_conn);
	}

	function _($str) {
		//TODO: return mssql_real_escape_string($str, $this->_conn);
		return $str;
	}

	function error() {
		return "#".mssql_errno($this->_conn).": ".mssql_error($this->_conn);
	}

	function get_by_id( $table, $id ) {
		$null = null;
		$sql = "select p.*
			from `".$table."` as p
			where p.`ID`='".$id."'
		";
		$res = $this->query( $sql );
		if ($r = $this->fetch( $res )) {
			return $r;
		}
		return $null;
	}

	/**
	 * @returns int
	 *   1 - changed but without add hist
	 *   2 - changed and add hist
	 *   0 - nothing to change
	 *   -1 - error ID not set
	 *   -2 - error id not exists in DB
	 *
	 * TODO: sprawdzac czy w hist mozna odczytac aktualny stan, jesli nie to dodac caly rekord do HIST, jako 'procesy-fix-hist-data'
	 */
	function UPDATE_OBJ( $table, &$sql_obj ) {
		if (!isset($sql_obj->ID) || $sql_obj->ID <= 0) {
			return -1;
		}
		$id = $sql_obj->ID;

		// check id record $id exists
		if (($curr_obj = $this->get_by_id( $table, $sql_obj->ID )) == null) {
			return -2;
		}

		// check if enything changed
		$changed = false;
		$fields_to_change = get_object_vars($sql_obj);
		foreach ($fields_to_change as $k => $v) {
			if ($k == 'ID') continue;
			if ($v == $curr_obj->$k) {// === ?
				unset($sql_obj->$k);
			} else {
				$changed = true;
			}
		}
		if ($changed == false) {
			return 0;// record not changed
		}

		$sql_arr = array();
		// TODO: add admin columns if exists in table - search in session
		$admin_col = array();
		$admin_col []= 'A_RECORD_CREATE_DATE';
		$admin_col []= 'A_RECORD_CREATE_AUTHOR';
		// ...
		$sql_obj->A_RECORD_UPDATE_DATE = date('Y-m-d-H:i');
		$sql_obj->A_RECORD_UPDATE_AUTHOR = User::getName();
		foreach (get_object_vars($sql_obj) as $k => $v) {
			if (strtoupper($v) == 'NOW()') {
				$v = 'NOW()';
			} else if (strtoupper($v) == 'NULL') {
				$v = 'NULL';
			} else {
				$v = $this->_($v);
				$v = "'{$v}'";
			}
			$sql_arr [] = "`{$k}`={$v}";
		}
		$sql = "update `{$table}` set ".implode(",", $sql_arr)." where `ID`='{$id}' limit 1; ";
		$this->query($sql);

		if ($this->has_errors()) {
			//echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">db errors: (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($this->get_errors());echo'</pre>';
		}

		$ret = $this->affected_rows();
		if ($ret) {
			$sql_obj->ID_USERS2 = $sql_obj->ID;
			unset($sql_obj->ID);
			$new_id = $this->ADD_NEW_OBJ("{$table}_HIST", $sql_obj);
			if ($new_id) {
				$ret += 1;
			}
		}
		return $ret;
	}

	function ADD_NEW_OBJ( $table, $sql_obj ) {
		$sql_arr = array();
		// TODO: add admin columns if exists in table - search in session
		$admin_col = array();
		$admin_col []= 'ID';
		$admin_col []= 'A_RECORD_CREATE_DATE';
		$admin_col []= 'A_RECORD_CREATE_AUTHOR';
		$admin_col []= 'A_RECORD_UPDATE_DATE';
		$admin_col []= 'A_RECORD_UPDATE_AUTHOR';
		// ...
		$sql_arr["`ID`"] = "NULL";// add default value for ID, NULL in all inserts
		if (substr($table, 0, -5) == '_HIST') {
			$sql_obj->A_RECORD_UPDATE_DATE = date('Y-m-d-H:i');
			$sql_obj->A_RECORD_UPDATE_AUTHOR = User::getName();
		} else {
			$sql_obj->A_RECORD_CREATE_DATE = date('Y-m-d-H:i');
			$sql_obj->A_RECORD_CREATE_AUTHOR = User::getName();
		}

		foreach (get_object_vars($sql_obj) as $k => $v) {
			if (strtoupper($v) == 'NOW()') {
				$v = 'NOW()';
			} else if (strtoupper($v) == 'NULL' && substr($table, -5) != '_HIST') {
				$v = 'NULL';
			} else {
				$v = $this->_($v);
				$v = "'{$v}'";
			}
			$sql_arr ["`{$k}`"] = $v;
		}
		$sql = "insert into `{$table}` (".implode(",", array_keys($sql_arr)).") values (".implode(",", array_values($sql_arr))."); ";
		$this->query($sql);

		if ($this->has_errors()) {
			//echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">db errors: (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($this->get_errors());echo'</pre>';
		}

		$ret_id = $this->insert_id();
		if (substr($table, -5) == '_HIST') {
			return $ret_id;
		}
		if ($ret_id) {
			$sql_obj->ID_USERS2 = $ret_id;
			unset($sql_obj->ID);
			$new_id_hist = $this->ADD_NEW_OBJ($table . '_HIST', $sql_obj);
			// error jesli nie udalo sie dodac rekordu do tabeli _HIST
		}
		return $ret_id;
	}

}