<?php
 use TCMS\TException;
 class MySqlDb {
 private $conn = null;
 public $_table = null;
 public $_columns = null;
 public $_where = null;
 public $_order = null;
 public $_limit = null;
 public $_join = null;
 public $_isDistinct = false;
 public $_isDebug = false;
 public function __construct($conn, $table) {
 $this->_table = $table;
 $this->conn = $conn;
 }
 public function enableDebug() {
 $this->_isDebug = true;
 return $this;
 }
 public function query($query, array $prepareData = null) {
 if(empty($query)) return $this;
 $this->_query = $query;
 if(empty($prepareData) === false) $this->_prepareData = $prepareData;
 return $this;
 }
 public function toArray() {
 $sth = $this->executeQuery();
 return $sth->fetchAll(PDO::FETCH_ASSOC);
 }
 public function toList() {
 $sth = $this->executeQuery();
 return $sth->fetchAll(PDO::FETCH_OBJ);
 }
 public function first() {
 $sth = $this->executeQuery();
 $this->limit(1);
 return $sth->fetch(PDO::FETCH_OBJ);
 }
 public function find($id, string $column = 'id') {
 $this->setWhere($column, $id);
 $this->limit(1);
 $sth = $this->executeQuery();
 return $sth->fetch(PDO::FETCH_OBJ);
 }
 public function findArray($id, string $column = 'id') {
 $this->setWhere($column, $id);
 $this->limit(1);
 $sth = $this->executeQuery();
 return $sth->fetch(PDO::FETCH_ASSOC);
 }
 public function count($column = '*', bool $isRelease = true) {
 if(is_string($column)) {
 if($column != '*') $col = '`' . $column . '`';
 else $col = $column;
 }
 else {
 $col = '*';
 $isRelease = $column;
 }
 $columns = $this->_columns;
 $this->_columns = [];
 $this->_columns[] = new DbRaw('COUNT(' . $col . ') as cou');
 $sth = $this->executeQuery($isRelease);
 $ret = $sth->fetch(PDO::FETCH_OBJ);
 $count = 0;
 if($ret !== false) {
 $count = $ret->cou;
 }
 if($isRelease === false) {
 $this->_columns = $columns;
 }
 return $count;
 }
 public function sum(string $column = 'id', bool $isRelease = true) {
 $columns = $this->_columns;
 $this->_columns = [];
 $this->_columns[] = new DbRaw('SUM(`' . $column . '`) as smu');
 $sth = $this->executeQuery($isRelease);
 $ret = $sth->fetch(PDO::FETCH_OBJ);
 $sum = 0;
 if($ret !== false) {
 $sum = $ret->smu;
 }
 if($isRelease === false) {
 $this->_columns = $columns;
 }
 return $sum;
 }
 public function max(string $column = 'id', bool $isRelease = true) {
 $columns = $this->_columns;
 $this->_columns = [];
 $this->_columns[] = new DbRaw('MAX(`' . $column . '`) as m');
 $sth = $this->executeQuery($isRelease);
 $ret = $sth->fetch(PDO::FETCH_OBJ);
 $max = 0;
 if($ret !== false) {
 $max = $ret->m;
 }
 if($isRelease === false) {
 $this->_columns = $columns;
 }
 return $max;
 }
 public function avg($column = 'id', bool $isRelease = true) {
 $columns = $this->_columns;
 $this->_columns = [];
 $this->_columns[] = new DbRaw('AVG(`' . $column . '`) as agv');
 $sth = $this->executeQuery($isRelease);
 $ret = $sth->fetch(PDO::FETCH_OBJ);
 $agv = 0;
 if($ret !== false) {
 $agv = $ret->agv;
 }
 if($isRelease === false) {
 $this->_columns = $columns;
 }
 return $agv;
 }
 public function limit($limit, $offset = 0) {
 if(is_integer($limit) === false || is_integer($offset) === false) {
 return $this;
 }
 if($offset == 0) $this->_limit = 'LIMIT ' . $limit;
 else $this->_limit = 'LIMIT ' . $limit . ' OFFSET ' . $offset;
 return $this;
 }
 public function orderBy(string $column) {
 if(is_string($column) === false) {
 throw new TException('$column phải là string', 500);
 }
 if($this->_order == null) {
 $this->_order = 'ORDER BY ';
 $this->_order .= $this->parseColumn($column) . ' ASC';
 }
 else {
 $this->_order .= ', ' . $this->parseColumn($column) . ' ASC';
 }
 return $this;
 }
 public function orderByDesc($column) {
 if(is_string($column) === false) {
 throw new TException('$column phải là string', 500);
 }
 if($this->_order == null) {
 $this->_order = 'ORDER BY ';
 $this->_order .= $this->parseColumn($column) . ' DESC';
 }
 else {
 $this->_order .= ', ' . $this->parseColumn($column) . ' DESC';
 }
 return $this;
 }
 public function distinct() {
 $this->_isDistinct = true;
 return $this;
 }
 public function select() {
 $args = func_get_args();
 if(count($args) == 0) return $this;
 $this->_columns = [];
 foreach($args as $arg) {
 if(is_array($arg)) {
 foreach($arg as $k => $v) {
 $this->_columns[] = $v;
 }
 }
 else {
 $this->_columns[] = $arg;
 }
 }
 return $this;
 }
 public function selectCol($cols) {
 $this->_columns = [];
 foreach($cols as $col) {
 $this->_columns[] = $col;
 }
 return $this;
 }
 public function where($column, $value = null, $operator = '=') {
 $this->setWhere($column, $value, $operator, 'AND');
 return $this;
 }
 public function orWhere($column, $value = null, $operator = '=') {
 $this->setWhere($column, $value, $operator, 'OR');
 return $this;
 }
 public function whereNotNull(string $column, string $type = 'AND') {
 $this->setWhere($column, null, null, $type, 2);
 return $this;
 }
 public function whereNull(string $column, string $type = 'AND') {
 $this->setWhere($column, null, null, $type, 1);
 return $this;
 }
 public function whereIn(string $column, array $value) {
 $val = [];
 foreach($value as $k => $v) {
 $val[] = $this->parseWhereValue($v, true);
 }
 if(count($val) == 0) return $this;
 $this->setWhere($column, $val, 'IN', 'AND');
 return $this;
 }
 public function orWhereIn(string $column, array $value) {
 $val = [];
 foreach($value as $k => $v) {
 $val[] = $this->parseWhereValue($v, true);
 }
 if(count($val) == 0) return $this;
 $this->setWhere($column, $val, 'IN', 'OR');
 return $this;
 }
 public function like($column, $value = null) {
 $this->setWhere($column, $value, null, 'AND', null, true);
 return $this;
 }
 public function orLike($column, $value = null) {
 $this->setWhere($column, $value, null, 'OR', null, true);
 return $this;
 }
 private function setWhere($column, $value = null, $operator = '=', $type = 'AND', $null = null, $isLike = null) {
 if(isset($column) == false || $column == null) throw new TException("Vui lòng nhập column khi gọi lệnh where", 500);
 if($isLike) {
 $operator = ' LIKE ';
 }
 if(is_array($column)) {
 if($this->_where == null) $this->_where = [];
 if($value != null && is_bool($value)) {
 $this->_where[] = [ 'group' => true ];
 }
 foreach($column as $key => $val) {
 $this->_where[] = [ 'column' => $key, 'value' => $val, 'type' => $type, 'o' => $operator, 'null' => $null ];
 }
 if($value != null && is_bool($value)) {
 $this->_where[] = [ 'group' => false ];
 }
 return $this;
 }
 if($value === null) {
 if($column instanceof DbRaw || ($null != null && is_integer($null))) {
 }
 else {
 throw new TException("column phải là Db::raw() hoặc array", 500);
 }
 }
 if($this->_where == null) $this->_where = [];
 $this->_where[] = [ 'column' => $column, 'value' => $value, 'type' => $type, 'o' => $operator, 'null' => $null ];
 }
 public function beginGroup() {
 if($this->_where == null) $this->_where = [];
 $this->_where[] = [ 'group' => true ];
 return $this;
 }
 public function endGroup() {
 if($this->_where == null) $this->_where = [];
 $this->_where[] = [ 'group' => false ];
 return $this;
 }
 public function join(string $table, string $curTableColumn, string $joinTableColumn) {
 $this->setJoin($table, $curTableColumn, $joinTableColumn, ' INNER JOIN ');
 return $this;
 }
 public function outerJoin(string $table, string $curTableColumn, string $joinTableColumn) {
 $this->setJoin($table, $curTableColumn, $joinTableColumn, ' FULL OUTER JOIN ');
 return $this;
 }
 public function leftJoin(string $table, string $curTableColumn, string $joinTableColumn) {
 $this->setJoin($table, $curTableColumn, $joinTableColumn, ' LEFT JOIN ');
 return $this;
 }
 public function rightJoin(string $table, string $curTableColumn, string $joinTableColumn) {
 $this->setJoin($table, $curTableColumn, $joinTableColumn, ' RIGHT JOIN ');
 return $this;
 }
 private function setJoin(string $table, string $curTableColumn, string $joinTableColumn, string $joinType) {
 if($this->_join == null) $this->_join = '';
 $table = $this->parseColumn($table);
 $curTableColumn = $this->parseColumn($curTableColumn);
 $joinTableColumn = $this->parseColumn($joinTableColumn);
 if(strpos($curTableColumn, '`.`') === false) {
 $curTableColumn = '`' . $this->_table . '`.' . $curTableColumn;
 }
 if(strpos($joinTableColumn, '`.`') === false) {
 $joinTableColumn = $table . '.' . $joinTableColumn;
 }
 $this->_join .= $joinType . $table . ' ON ' . $curTableColumn . ' = ' . $joinTableColumn;
 }
 private function executeQuery($isReleaseData = true) {
 if(isset($this->_query)) {
 $query = $this->_query;
 if(isset($this->_prepareData)) {
 $prepareData = $this->_prepareData;
 }
 else {
 $prepareData = [];
 }
 }
 else {
 $col = '';
 if($this->_columns != null && count($this->_columns) > 0) {
 foreach($this->_columns as $value) {
 $v = $value;
 if($this->_join != null) {
 if(strpos($v, ' ') === false && strpos($v, '.') === false) {
 $v = $this->_table . '.' . $v;
 }
 }
 if(is_string($value)) {
 $col .= $this->parseColumn($v). ',';
 }
 else {
 $col .= $v. ',';
 }
 }
 if($col != '') $col = substr($col, 0, strlen($col) - 1);
 }
 if($col == '') $col = '*';
 if($this->_isDistinct) $col = 'DISTINCT ' . $col;
 $query = 'SELECT ' . $col . ' FROM `' . $this->_table . '`';
 if($this->_join != null) {
 $query .= ' ' . $this->_join;
 }
 $prepareData = $this->parseWhere($query);
 if($this->_order != null) {
 $query .= ' ' . $this->_order;
 }
 if($this->_limit != null) {
 $query .= ' ' . $this->_limit;
 }
 }
 if($this->_isDebug) {
 if(count($prepareData) > 0) print_r($prepareData);
 echo '<br><b>' . $query .'</b><br><br>';
 }
 try {
 $sth = $this->conn->prepare($query);
 $sth->execute($prepareData);
 if($isReleaseData) $this->releaseData();
 return $sth;
 }
 catch(PDOException $e) {
 throw new TException($e->getMessage() . ' <br><br>[' . $query . ']', 500);
 }
 }
 public function insert($data) {
 if($data == null) throw new TException("Vui lòng truyền dữ liệu cho lệnh insert", 500);
 $isHasData = false;
 $query = 'INSERT INTO `' . $this->_table . '`(';
 foreach($data as $k => $v) {
 $query .= $this->parseColumn($k) . ',';
 $isHasData = true;
 }
 if($isHasData === false) throw new TException("Vui lòng truyền dữ liệu cho lệnh insert", 500);
 $prepareData = [];
 $query = substr($query, 0, strlen($query) - 1) . ') VALUES(';
 foreach($data as $k => $v) {
 $query .= '?,';
 $prepareData[] = $this->parseWhereValue($v);
 }
 $query = substr($query, 0, strlen($query) - 1) . ')';
 if($this->_isDebug) {
 if(count($prepareData) > 0) print_r($prepareData);
 echo '<br><b>' . $query .'</b><br><br>';
 }
 try {
 $sth = $this->conn->prepare($query);
 $sth->execute($prepareData);
 $this->releaseData();
 return $this->conn->lastInsertId();
 }
 catch(PDOException $e) {
 if(ENVIRONMENT == 'dev') throw new TException($e->getMessage() . ' <br><br>[' . $query . ']', 500);
 return false;
 }
 }
 public function inserts(array $data) {
 if($data == null) throw new TException("Vui lòng truyền dữ liệu cho lệnh inserts", 500);
 $insertRes = [];
 foreach($data as $value) {
 $retVal = $this->insert($value);
 if($retVal !== false) {
 $insertRes[] = $retVal;
 }
 }
 return $insertRes;
 }
 public function update($data) {
 if($data == null) throw new TException("Vui lòng truyền dữ liệu cho lệnh update", 500);
 $_prepareData = [];
 $isHasData = false;
 $query = 'UPDATE `' . $this->_table . '` SET ';
 foreach($data as $k => $v) {
 if($k == 'id') {
 if($this->_where == null || count($this->_where) == 0) {
 $this->where('id', (int)$v);
 }
 continue;
 }
 if($v instanceof DbRaw) {
 $query .= $this->parseColumn($k) .'=' . $v . ',';
 }
 else {
 $query .= $this->parseColumn($k) . '=?,';
 $_prepareData[] = $this->parseWhereValue($v);
 }
 $isHasData = true;
 }
 if(!$this->_where) throw new TException("Vui lòng truyền id cho lệnh update", 500);
 if($isHasData === false) throw new TException("Vui lòng truyền dữ liệu cho lệnh update", 500);
 $query = substr($query, 0, strlen($query) - 1);
 $prepareData = $this->parseWhere($query);
 $l = count($_prepareData);
 for($i = $l - 1;
 $i >= 0;
$i--) {
 array_unshift($prepareData, $_prepareData[$i]);
 }
 if($this->_isDebug) {
 if(count($prepareData) > 0) print_r($prepareData);
 echo '<br><b>' . $query .'</b><br><br>';
 }
 try {
 $sth = $this->conn->prepare($query);
 $sth->execute($prepareData);
 $this->releaseData();
 return true;
 }
 catch(PDOException $e) {
 if(ENVIRONMENT == 'dev') throw new TException($e->getMessage() . ' <br><br>[' . $query . ']', 500);
 return false;
 }
 }
 public function delete($id = null, $column = 'id') {
 $query = "DELETE FROM `" . $this->_table . "`";
 $prepareData = $this->parseWhere($query);
 if(count($prepareData) == 0) {
 if($id == null) throw new TException("Vui lòng truyền dữ liệu cho lệnh delete", 500);
 $query .= ' WHERE ' . $this->parseColumn($column) . '=:' . $column;
 $prepareData[$column] = $this->parseWhereValue($id);
 }
 if($this->_isDebug) {
 if(count($prepareData) > 0) print_r($prepareData);
 echo '<br><b>' . $query .'</b><br><br>';
 }
 try {
 $sth = $this->conn->prepare($query);
 $sth->execute($prepareData);
 $this->releaseData();
 return true;
 }
 catch(PDOException $e) {
 if(ENVIRONMENT == 'dev') throw new TException($e->getMessage() . ' <br><br>[' . $query . ']', 500);
 return false;
 }
 }
 public function deletes(array $ids, $column = 'id') {
 if(count($ids) == 0) throw new TException("Vui lòng truyền dữ liệu cho lệnh deletes", 500);
 $query = "DELETE FROM `" . $this->_table . "`";
 $this->whereIn($column, $ids);
 $prepareData = $this->parseWhere($query);
 if($this->_isDebug) {
 if(count($prepareData) > 0) print_r($prepareData);
 echo '<br><b>' . $query .'</b><br><br>';
 }
 try {
 $sth = $this->conn->prepare($query);
 $sth->execute($prepareData);
 $this->releaseData();
 return true;
 }
 catch(PDOException $e) {
 if(ENVIRONMENT == 'dev') throw new TException($e->getMessage() . ' <br><br>[' . $query . ']', 500);
 return false;
 }
 }
 public function beginTransaction() {
 $this->conn->beginTransaction();
 }
 public function commitTransaction() {
 $this->conn->commit();
 }
 public function rollBackTransaction() {
 $this->conn->rollBack();
 }
 public function exec() {
 if(isset($this->_query)) {
 $query = $this->_query;
 if(isset($this->_prepareData)) {
 $prepareData = $this->_prepareData;
 }
 else {
 $prepareData = [];
 }
 }
 else {
 throw new TException("Vui lòng truyền query trước khi gọi hàm exec", 500);
 }
 try {
 $sth = $this->conn->prepare($query);
 $sth->execute($prepareData);
 $this->releaseData();
 return true;
 }
 catch(PDOException $e) {
 throw new TException($e->getMessage() . ' <br><br>[' . $query . ']', 500);
 }
 }
 private function releaseData() {
 $this->_columns = null;
 $this->_where = null;
 $this->_order = null;
 $this->_limit = null;
 $this->_join = null;
 $this->_isDistinct = false;
 $this->_isDebug = false;
 if(isset($this->_query)) {
 unset($this->_query);
 unset($this->_prepareData);
 }
 }
 private function parseColumn($column) {
 if(!is_string($column)) {
 if($column instanceof \DbRaw) {
 return $column . '';
 }
 }
 else {
 if($column == '*') return '*';
 }
 if(strpos($column, ".*") !== false) {
 return '`' . str_replace('.', '`.', $column);
 }
 $col = '`' . str_replace('.', '`.`', $column) . '`';
 if(strpos($col, " AS ") !== false) {
 $col = str_replace(' AS ', '` AS `', $col);
 }
 else {
 if(strpos($col, " as ") !== false) {
 $col = str_replace(' as ', '` as `', $col);
 }
 }
 return $col;
 }
 private function parseWhereValue($value) {
 if(is_string($value)) return $value;
 if(is_numeric($value)) return $value;
 if(is_bool($value)) return $value ? 1 : 0;
 if($value instanceof DbRaw) {
 return $value . '';
 }
 if($value instanceof \DateTime) {
 return $value->format('Y-m-d H:i:s');
 }
 return $value;
 }
 private function parseWhere(&$query) {
 $prepareData = [];
 if($this->_where != null && count($this->_where) > 0) {
 $query .= ' WHERE ';
 $isTheFirst = true;
 $isNeedOpenGroup = false;
 foreach($this->_where as $key => $value) {
 if(array_key_exists('group', $value)) {
 if($value['group']) {
 if($isTheFirst) {
 $query .= ' (';
 }
 else {
 $isNeedOpenGroup = true;
 }
 }
 else {
 $query .= ')';
 }
 continue;
 }
 if(is_string($value['column'])) {
 $col = $this->parseColumn($value['column']);
 if($isNeedOpenGroup) {
 $col = '(' . $col;
 $isNeedOpenGroup = false;
 }
 if($value['value'] !== null) {
 $isIn = $value['o'] == 'IN';
 if($isTheFirst) {
 $isTheFirst = false;
 }
 else {
 $query .= ' ' . $value['type'] . ' ';
 }
 if($isIn) {
 $query .= $col . ' IN(';
 foreach($value['value'] as $kk => $vv) {
 $query .= '?' . ',';
 $prepareData[] = $this->parseWhereValue($vv);
 }
 $query = substr($query, 0, strlen($query) - 1) . ')';
 }
 else {
 $query .= $col . $value['o'] . '?';
 }
 if($isIn === false) $prepareData[] = $this->parseWhereValue($value['value']);
 }
 else {
 if($value['null'] !== null && is_integer($value['null'])) {
 if($value['null'] == 1) {
 if($isTheFirst) {
 $query .= $col . ' IS NULL';
 $isTheFirst = false;
 }
 else {
 $query .= ' ' . $value['type'] . ' ' . $col . ' IS NULL';
 }
 }
 else {
 if($isTheFirst) {
 $query .= $col . ' IS NOT NULL';
 $isTheFirst = false;
 }
 else {
 $query .= ' ' . $value['type'] . ' ' . $col . ' IS NOT NULL';
 }
 }
 }
 }
 }
 else {
 if($isTheFirst) {
 $query .= $value['column'];
 $isTheFirst = false;
 }
 else {
 $query .= ' ' . $value['type'] . ' ' . $value['column'];
 }
 }
 }
 }
 return $prepareData;
 }
 }
 ?>