<?

require_once ('DB.php');
require_once ('Logger.php');

DEFINE('WHERE_PERIOD',
	' AND (
		(period_from IS NULL AND period_to IS NULL)
		OR SYSDATETIME() BETWEEN period_from AND period_to
		OR (period_to IS NULL AND SYSDATETIME() > period_from)
		OR (period_from IS NULL AND SYSDATETIME() < period_to)
	)');
DEFINE('TYPE_OPTION_BY_VALUE', 'value');
DEFINE('TYPE_OPTION_BY_TEXT', 'text');

class DBTable
{

	protected static $logger;
	protected static $db;

	protected $__TABLE;
	protected static $__COLUMNS;
	protected $totalPages;

	/**
	 * Constructor function
	 *
	 */
	public function __construct()
	{
		$this::$db = new DB ();
		$this::$logger = Logger::getInstance($_SERVER['PHP_SELF']);
	}

	public function getInstance()
	{
		//return MysqliDb::getInstance();
		if ($this::$db===null) new DBTable();
		return $this::$db;
	}

  private function toQsParams($arry) {
    $qstr = '';
    foreach ($arry as $key => $value) {
      $qstr .= '?,';
    }
    return strlen($qstr) > 0 ? substr($qstr, 0, -1) : '';
  }

  private function getColumns($arry) {
    $qstr = '';
    foreach ($arry as $key => $value) {
      $qstr .= $key . ',';
    }
    return strlen($qstr) > 0 ? substr($qstr, 0, -1) : '';
  }

  private function getValuesArray($arry) {
    $out_ = array();
    foreach ($arry as $key => $value) {
      array_push($out_, $value);
    }
    return $out_;
  }

	public static function DbRows2ArrayMap($rows, $key, $val) {
		$out_ = array();
		foreach ($rows as $index => $row) {
				$out_[$row[$key]] = $row[$val];
		}
		return $out_;
	}

	public static function DbRows2ArrayMapWithDivisions($rows, $key, $val, $division) {
		$out_ = array();
		foreach ($rows as $index => $row) {
				$out_[$row[$key]][$row[$division]] = $row[$val];
		}
		return $out_;
	}

	public static function DbRows2OptionsMap($rows, $type='value', $option_key, $attr_key) {
		$str = "{ type:'" . $type . "', attrs: { ";
		foreach ($rows as $i => $row) {
				$str .= "'" . $row[$option_key] . "': {attr: '" . $attr_key . "', value:'" . $row[$attr_key] . "' },";
		}
			$str .= '}}';

			return $str;
	}

	/**
	 * Insert record with generated ID
	 *
	 * @return generated ID
	 */
	public function add($data) {
		try {
		$sql = "INSERT INTO " . $this->__TABLE .
				" (" . $this->getColumns($data) . ") " .
				" VALUES (" . $this->toQsParams($data) . "); " .
				" SELECT SCOPE_IDENTITY()";

				$this::$logger->debug("SQL: " . $sql);
				// fix remove comma from numeric value
				foreach ($data as $key => $value) {
					if (preg_match("/^[0-9,\.]+$/", $value))
						$data[$key] = str_replace(',', '', $value);
				}

		$resource=sqlsrv_query($this::$db->getInstance(), $sql, $this->getValuesArray($data));
		sqlsrv_next_result($resource);
		sqlsrv_fetch($resource);
		return sqlsrv_get_field($resource, 0);
		} catch (Exception $e) {
			throw new Exception($this::$db->getInstance()->getLastError());
		}
	}

	/**
	 * Inquiry data with conditions
	 *
	 * @return inquire rows
	 */
	public function get($cols, $where=null, $limit=null, $orderby=null, $ordertype="Asc") {
		$sql = "SELECT " .
			($limit ? " TOP " . $limit . ' ' : '') .
		  $this->getColumns($cols) .
			" FROM " . $this->__TABLE .
			($where ? " WHERE " . $where : '') .
			($orderby ? " ORDER BY " . $orderby . " " . $ordertype : '');

		$this::$logger->debug("SQL: " . $sql);

		$stmt = sqlsrv_query( $this::$db->getInstance(), $sql );
		if($stmt === false) {
		$this::$logger->error("Caught exception: " . print_r(sqlsrv_errors(), true));
		}
			$rows = array();
		while( $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
				array_push( $rows, $row );
			}

			sqlsrv_free_stmt( $stmt);
			return $rows;
	}

	public function set($cols, $where='1=1')
	{
		// fix remove comma from numeric value
		foreach ($cols as $key => $value) {
			if (preg_match("/^[0-9,\.]+$/", $value))
				$cols[$key] = str_replace(',', '', $value);
		}

		$sql = "UPDATE " . $this->__TABLE .
			" SET " . implode (", ", array_map(function($k, $v){ return "$k=" . (isset($v) ? "'$v'" : "NULL"); }
				, array_keys($cols), array_values($cols)) ) .
			" WHERE " . $where;

		$this::$logger->debug("SQL: " . $sql);

		$stmt = sqlsrv_query( $this::$db->getInstance(), $sql );
		$rows_affected = sqlsrv_rows_affected( $stmt);
		$this::$logger->debug('rows_affected: ' . $rows_affected);
			if( $rows_affected === false) {
			$this::$logger->error("Caught exception: " . print_r(sqlsrv_errors(), true));
			} elseif( $rows_affected == -1) {
		$rows_affected = 0;
			} else {
		$this::$logger->debug($rows_affected." rows were updated.");
			}
			return $rows_affected;
	}

	public function remove($where)
	{
		$sql = "DELETE " .
			" FROM " . $this->__TABLE .
			($where ? " WHERE " . $where : '');

		$this::$logger->debug("SQL: " . $sql);

		$stmt = sqlsrv_query( $this::$db->getInstance(), $sql );
		$rows_affected = sqlsrv_rows_affected( $stmt);
		$this::$logger->debug('rows_affected: ' . $rows_affected);
			if( $rows_affected === false) {
			$this::$logger->error("Caught exception: " . print_r(sqlsrv_errors(), true));
			} elseif( $rows_affected == -1) {
		$rows_affected = 0;
			} else {
		$this::$logger->debug($rows_affected." rows were removed.");
			}
			return $rows_affected;
	}

  /**
   * Inquiry all data
   *
   * @return fetched rows
   */
  public function getAll() {
    return $this->get($this::$__COLUMNS);
  }

}
