<?php
namespace Db;

class Util {
	public static function execute($dbh, $sql, $parameters = array()) {
		if (!($sth = $dbh->prepare($sql))) {
			throw new Exception("sql prepare statement failure: $sql");
		}
		$sth->setFetchMode(\PDO::FETCH_ASSOC);
		if (!$sth->execute($parameters)) {
			throw new Exception("sql execute statement failure: $sql");
		}
		return $sth;
	}
	
	public static function uuid($dbh) {
		$sql = "SELECT uuid() AS uuid";
		$sth = static::execute($dbh, $sql);
		$record = $sth->fetch(\PDO::FETCH_ASSOC);
		return $record['uuid'];
	}

	public static function columns($dbh, $table) {
		$sql = "SELECT column_name FROM information_schema.columns WHERE table_schema = (SELECT DATABASE()) AND table_name = ?";
		$parameters = array($table);
		$sth = static::execute($dbh, $sql, $parameters);
		return $sth->fetchAll();
	}

	public static function create($dbh, $table, array $data, array $columns = null) {
		$insert_data = array();

		if (empty($columns)) {
			$columns = static::columns($dbh, $table);
		}
		foreach ($columns as $column) {
			$column_name = $column['column_name'];
			if (isset($data[$column_name])) {
				$insert_data[$column_name] = $data[$column_name];
			}
		}
		
		$now = date('Y-m-d H:i:s');
		if (!isset($insert_data['createdate'])) {
			$insert_data['createdate'] = $now;
		}
		if (!isset($insert_data['lastupdate'])) {
			$insert_data['lastupdate'] = $now;
		}
		if (!isset($insert_data['createby'])) {
			$insert_data['createby'] = $_SESSION['webadmin']['id'];
		}
		if (!isset($insert_data['lastupby'])) {
			$insert_data['lastupby'] = $_SESSION['webadmin']['id'];
		}

		$insert_columns = array();
		$values = array();
		$parameters = array();
		foreach ($insert_data as $column => $value) {
			$insert_columns[] = "`$column`";
			$parameters[] = !strlen($value) ? null : $value;
			$values[] = '?';
		}
		$sql = "INSERT `$table` (" . implode(', ', $insert_columns) . ") VALUES (" . implode(', ', $values) . ")";
		$sth = static::execute($dbh, $sql, $parameters);
		return $dbh->lastInsertId();
	}

	public static function update($dbh, $table, array $data, array $columns = null, $primary_key = 'id') {
		if (empty($data[$primary_key])) {
			throw new Exception('No primary key value.');
		}

		$update_data = array();

		if (empty($columns)) {
			$columns = static::columns($dbh, $table);
		}
		foreach ($columns as $column) {
			$column_name = $column['column_name'];
			if (array_key_exists($column_name, $data)) {
				$update_data[$column_name] = $data[$column_name];
			}
		}
		
		$now = date('Y-m-d H:i:s');
		$update_data['lastupdate'] = $now;
		$update_data['lastupby'] = $_SESSION['webadmin']['id'];

		$values = array();
		$parameters = array();
		foreach ($update_data as $column => $value) {
			if ($column != $primary_key) {
				$parameters[] = !strlen($value) ? null : $value;
				$values[] = "`$column` = ?";
			}
		}
		$sql = "UPDATE `$table` SET " . implode(', ', $values) . " WHERE `$primary_key` = ?";
		$parameters[] = $data[$primary_key];
		$sth = static::execute($dbh, $sql, $parameters);
		return $sth->rowCount();
	}
	
	public static function transaction($dbh, $runnable) {
		if (!$dbh->beginTransaction()) {
			throw new Exception('mysql begin transaction failure.');
		}
		try {
			$runnable();
			
			if (!$dbh->commit()) {
				throw new Exception('mysql commit transaction failure.');
			}
		} catch (Exception $exception) {
			if (!$dbh->rollBack()) {
				throw new Exception('mysql roll back transaction failure.');
			}
			throw $exception;
		}
	}
}