<?php

class PgSql
{
	public $sql;
	public $tableNo;

	public function getConnection($config)
	{
		$conn_string = "host = '".$config['dbConnection.Host']."' "
						." port = '".$config['dbConnection.Port']."' "
						." dbname = '".$config['dbConnection.DataBase']."' "
						." user = '".$config['dbConnection.UserName']."' "
						." password = '".$config['dbConnection.Password']."'";

		$connection = pg_pconnect($conn_string);
		if (!$connection) {
			print("<h1>Gagal Melakukan Koneksi Ke Server</h1>");
			exit;
		}
	}

	public function getDb()
	{
		return new DB;
	}
	
	public function setSql($sql)
	{
		$this->sql = $sql;
		return $this->sql;
	}
	
	public function addSelect($param)
	{
		$ex = explode(' ', $param);
		if(trim(strtoupper($ex[0])) == 'SELECT'){
			$this->sql = $param;
		}else{
			$this->sql = "SELECT $param";
		}
	
		return $this->sql;
	}
	
	public function getFrom($param, $alias)
	{
		$tab = $this->getDb()->$param;
		if(empty($tab)){
			$tab = $param;
		}
		$this->sql = $this->sql." FROM ".$tab.' '.$alias;
		return $this->sql;
	}
	
	public function addLeftJoin($table, $alias, $param)
	{
		$tab = $this->getDb()->$table;
		if(empty($tab)){
			$tab = $table;
		}
	
		$this->sql = $this->sql." LEFT JOIN ".$tab.' '.$alias.' on ';
		
		$no = 0;
		foreach($param as $row){
			if($no > 0){
				$this->sql = $this->sql.' and ';
			}
			$no++;
			$this->sql = $this->sql.' ('.$row.')';
		}
		
		return $this->sql;
	}
	
	public function addInnerJoin($table, $alias, $param)
	{
		$tab = $this->getDb()->$table;
		if(empty($tab)){
			$tab = $table;
		}
		
		$this->sql = $this->sql." INNER JOIN ".$tab.' '.$alias.' on ';
		
		$no = 0;
		foreach($param as $row){
			if($no > 0){
				$this->sql = $this->sql.' and ';
			}
			$no++;
			$this->sql = $this->sql.' ('.$row.')';
		}
		
		return $this->sql;
	}
	
	public function getWhere($param)
	{
		$this->sql = $this->sql." WHERE $param";
		return $this->sql;
	}
	
	public function andWhere($param)
	{
		$this->sql = $this->sql." AND $param";
		return $this->sql;
	}
	
	public function orWhere($param)
	{
		$this->sql = $this->sql." OR $param";
		return $this->sql;
	}
	
	public function getShortBy($param, $type)
	{
		$this->sql = $this->sql." ORDER BY $param $type";
		return $this->sql;
	}
	
	public function getMultiShort($param)
	{
		$this->sql = $this->sql." ORDER BY $param";
		return $this->sql;
	}
	
	public function getMultiSort($param)
	{
		$this->sql = $this->sql." ORDER BY $param";
		return $this->sql;
	}
	
	public function getSpecialShort($sql, $param)
	{
		$this->sql = $sql." ORDER BY $param";
		return $this->sql;
	}
	
	public function getLimit($start, $finish)
	{
		$this->sql = $this->sql." LIMIT $finish OFFSET $start";
		return $this->sql;
	}
	
	public function getSpecialLimit($sql, $start, $finish)
	{
		return $sql." LIMIT $finish OFFSET $start";
	}
	
	public function addInsert($param)
	{
		$ex = explode(' ', $param);
		if(trim(strtoupper($ex[0])) == 'INSERT'){
			$this->sql = $param;
		}else{
			$this->sql = "INSERT INTO ".$this->getDb()->$param;
		}
		$this->tableNo = $this->getDb()->$param;
		return $this->sql;
	}
	
	public function getAttrib($param)
	{
		$this->sql = $this->sql." ($param)";
		return $this->sql;
	}
	
	protected function cekTypeData($db, $type, $val)
	{
	
		$val = trim($val);
		$res = $val;
		
		$str = $db->$type;
		
		if($val == '0'){
			$res = 0;
		}elseif($str['type'] == 'numeric'){
			$res = trim(str_ireplace(',', '', $res));
			if(empty($res)){
				$res = 0;
			}
		}elseif($str['type'] == 'integer'){
			$res = (int)$val;
		}elseif($str['type'] == 'timestamp without time zone'){
			$dt = date('Y-m-d H:i:s', strtotime($val));
			if($dt == '1970-01-01 07:00:00'){
				$dt = explode("/", $val);
				$dt = $dt[2].'-'.$dt[1].'-'.$dt[0];
				$dt = date('Y-m-d H:i:s', strtotime($dt));
				if($dt == '1970-01-01 07:00:00'){
					$res = 'null';
				}else{
					$res = $dt;
				}
			}else{
				$res = $dt;
			}
		}elseif(empty($val)){
			$res = '';
		}
		if($str['type'] == 'character varying' or $str['type'] == 'character'){
			if(empty($val)){
				$res = '';
			}
		}
			
		return str_ireplace("'", '"', $res);
	}
	
	public function addAttrIns($attr)
	{
		$db = new DB;
	
		$sql_1 = ' (';
		$sql_2 = " (";
		$no = 1;
		foreach($attr as $key => $n){
			if($no > 1){
				$sql_1.= ', ';
				$sql_2.= ', ';
			}
			
			$no++;
			$sql_1.= $this->convert(" $key ");

			$n = $this->cekTypeData($db->getStructure(), $key, $n);
			// $n = strtoupper($n);
			
			if(strtoupper($n) == 'NULL'){
				$sql_2.= $n;
			}else{
				$sql_2.= "'$n'";
			}
		}
		$sql_1.= ')';
		$sql_2.= ')';
		
		$this->sql = $this->sql.$sql_1.' values '.$sql_2;

		return $this->sql;
	}
	
	public function addAttrUp($attr)
	{
		$db = new DB;
		
		$sql_1 = '';
		$no = 1;
		foreach($attr as $key => $n){
			if($no > 1){
				$sql_1.= ', ';
			}
			$no++;

			$n = $this->cekTypeData($db->getStructure(), $key, $n);
			// $n = strtoupper($n);
			
			if(strtoupper($n) == 'NULL'){
				$sql_1.= "$key = $n";
			}else{
				$sql_1.= "$key = '$n'";
			}
		}
		
		$this->sql = $this->sql.' set '.$sql_1;
		return $this->sql;
	}
	
	public function getIf($param, $i, $n, $alias)
	{
		$this->sql = $this->sql
					.", case when ".$param
					." then ".$i
					." else ".$n
					." end"
					." as ".$alias
					;
		return $this->sql;
	}
	
	public function getWhereAndIf($param, $i, $n)
	{
		$this->sql = $this->sql
					." AND case when ".$param
					." then ".$i
					." else ".$n
					." end"
					;
		return $this->sql;
	}
	
	public function addValues($param)
	{
		$this->sql = $this->sql." VALUES ($param)";
		return $this->sql;
	}
	
	public function addUpdate($param)
	{
		$ex = explode(' ', $param);
		if(trim(strtoupper($ex[0])) == 'UPDATE'){
			$this->sql = $param;
		}else{
			$this->sql = "UPDATE ".$this->getDb()->$param;
		}
	
		return $this->sql;
	}
	
	public function addSet($param)
	{
		$this->sql = $this->sql." SET $param";
		return $this->sql;
	}
	
	public function addDeleteCascade($param)
	{
		$ex = explode(' ', $param);
		if(trim(strtoupper($ex[0])) == 'DELETE'){
			$this->sql = $param;
		}else{
			$this->sql = "ON DELETE CASCADE FROM ".$this->getDb()->$param;
		}
	
		return $this->sql;
	}
	
	public function addDelete($param)
	{
		$ex = explode(' ', $param);
		if(trim(strtoupper($ex[0])) == 'DELETE'){
			$this->sql = $param;
		}else{
			$this->sql = "DELETE FROM ".$this->getDb()->$param;
		}
	
		return $this->sql;
	}
	
	public function queryEks($sql)
	{
		$sql = trim($sql);
		$sel = explode(" ", $sql);
		$sel = strtoupper($sel[0]);
		if($sel == 'SELECT'){
			$res = $this->concat($sql);
		}else{
			$res = $sql;
		}
	
		return pg_query($res);
	}
	
	public function getNumRows($data)
	{
		return pg_num_rows($data);
	}
	
	public function fetchObject($data)
	{
		return pg_fetch_object($data);
	}
	
	public function getFetchObject($data)
	{
		$result = array();
		while($row = pg_fetch_object($data)){
			$result[] = $row;
		}
		
		return $result;
	}
	
	public function getFind($table, $select, $param, $dis = false)
	{
		$sql = $this->addSelect($select);
		$sql = $this->getFrom($table, 'a');
		$sql = $this->getWhere('1=1');
		
		foreach($param as $row){
			$sql = $this->andWhere($row);
		}
		$sql = $this->getLimit(0, 1);
		
		if($dis == true){
			echo $sql;
		}
		
		$eks = $this->queryEks($sql);
		$res = $this->getFetchObject($eks);

		return $res[0];
	}
	
	public function getFindSort($table, $select, $param, $sort, $dis = false)
	{
		$sql = $this->addSelect($select);
		$sql = $this->getFrom($table, 'a');
		$sql = $this->getWhere('1=1');
		
		foreach($param as $row){
			$sql = $this->andWhere($row);
		}
		if($sort != null){
			$sql = $this->getMultiShort($sort);
		}
		$sql = $this->getLimit(0, 1);
		
		if($dis == true){
			echo $sql;
		}
		
		$eks = $this->queryEks($sql);
		$res = $this->getFetchObject($eks);

		return $res[0];
	}
	
	public function getFetchArray($data)
	{
		$result = array();
		while($row = pg_fetch_object($data)){
			$result[] = $row;
		}
		return $result;
	}
	
	protected function convert($res)
	{
		$res = str_ireplace(' group', ' "group"', $res);
		$res = str_ireplace('.group', '."group"', $res);

		$res = str_ireplace(' user ', ' "user" ', $res);
		$res = str_ireplace('.user', '."user"', $res);

		$res = str_ireplace(' check ', ' "check" ', $res);
		$res = str_ireplace('.check', '."check"', $res);
		return $res;
	}
	
	public function getRoolbackFlush($sql)
	{
		$query = '';
		foreach($sql as $row){
			$query.= ';'.$this->convert($row);
		}
		$query = $this->concat($query);

		return pg_query($query);
	}
	
	public function getFlush($sql)
	{
		$query = '';
		foreach($sql as $row){
			$query.= ';'.$this->convert($row);
		}
		$query = $this->concat($query);

		return pg_query($query);
	}
	
	public function getGroupBy($param)
	{
		$this->sql = $this->sql." GROUP BY $param";
		return $this->sql;
	}
	
	public function getFindBy($table, $select, $param, $sort, $tampil = false)
	{
		$sql = $this->addSelect($select);
		$sql = $this->getFrom($table, 'a');
		$sql = $this->getWhere('1=1');
		
		foreach($param as $row){
			$sql = $this->andWhere($row);
		}
		
		if($sort != null){
			$sql = $this->getMultiShort($sort);
		}
		if($tampil == true){
			echo $sql;
		}

		$eks = $this->queryEks($sql);
		return $this->getFetchObject($eks);
	}
	
	public function getRoolback($sql)
	{
		pg_query("BEGIN WORK"); 
		foreach($sql as $row){
			$eks = pg_query($row); 

			if(!$eks){
				pg_query("ROLLBACK"); 
				return;
			}else{
				pg_query("COMMIT"); 
			}
		}
	}
	
	public function getBeginTransaction()
	{
		return pg_query("BEGIN WORK"); 
	}
	
	public function getRollBack()
	{
		return pg_query("ROLLBACK"); 
	}
	
	public function getCommit()
	{
		return pg_query("COMMIT"); 
	}
	
	public function getRollCek($res)
	{
		return pg_cmdtuples($res); 
	}
	
	public function getShowTable()
	{
		$sql = "SELECT
					table_schema || '.' || table_name as tab
				FROM
					information_schema.tables
				WHERE
					table_type = 'BASE TABLE'
				AND
					table_schema NOT IN ('pg_catalog', 'information_schema');";
					
		$eks = $this->queryEks($sql);
		return $this->getFetchObject($eks);
	}
	
	public function getCreateTable($table)
	{
		return "CREATE TABLE $table ()";
	}
	
	public function getStructure($table)
	{
		$tab = explode('.', $table);
		$sql = "select column_name as name from INFORMATION_SCHEMA.COLUMNS where table_name = '".$tab[1]."'";
		$eks = $this->queryEks($sql);
		return $this->getFetchObject($eks);
	}
	
	public function getCreateCol($table, $data)
	{
		return "ALTER TABLE $table ADD COLUMN ".$data->name."  ".$this->getSincField($data);
	}
	
	public function getAlterCol($table, $data)
	{
		return "ALTER TABLE $table ALTER COLUMN ".$data->name." TYPE ".$this->getSincField($data);
	}
	
	public function getIndPk($table)
	{
		$tab = explode('.', $table);
		$sql = "Select indexname from pg_indexes where tablename = '".$tab[1]."' and indexname like 'pk_%'";
		$eks = $this->queryEks($sql);
		return $this->getNumRows($eks);
	}
	
	public function getDropPk($table, $coll)
	{
		$name = 'pk_'.str_ireplace('.', '_', $table);
		return "alter table $table drop constraint $name";
	}
	
	public function getCreatePk($table, $coll)
	{
		$name = 'pk_'.str_ireplace('.', '_', $table);
		return "ALTER TABLE $table ADD CONSTRAINT $name PRIMARY KEY ($coll)";
	}
	
	public function getIndIndex($table, $coll)
	{
		$tab = explode('.', $table);
		$name = 'index_'.str_ireplace('.', '_', $table).'_'.$coll;
		$sql = "Select indexname from pg_indexes where tablename = '".$tab[1]."' and indexname like '$name'";
		$eks = $this->queryEks($sql);
		return $this->getNumRows($eks);
	}
	
	public function getDropIndex($table, $coll)
	{
		$tab = explode('.', $table);
		$name = $tab[0].'.index_'.str_ireplace('.', '_', $table).'_'.$coll;
		return "DROP INDEX $name";
	}
	
	public function getCreateIndex($table, $coll)
	{
		$name = 'index_'.str_ireplace('.', '_', $table).'_'.$coll;
		return "CREATE INDEX $name ON $table USING btree (".$coll.")";
	}
	
	public function getDropCell($table, $coll)
	{
		return "ALTER TABLE $table DROP $coll";
	}
	
	public function getDropTable($table)
	{
		return "DROP TABLE $table";
	}
	
	public function getSincField($row)
	{
		$row->type = strtoupper($row->type);
		
		if( ($row->type == strtoupper('character varying')) || ($row->type == strtoupper('varchar')) || ($row->type == strtoupper('char')) ){
			return 'character varying ('.$row->size.')';
		}
		if( $row->type == strtoupper('text')){
			return 'text';
		}
		if( ($row->type == strtoupper('numeric')) || ($row->type == strtoupper('decimal')) ){
			return 'numeric';
		}
		if( ($row->type == strtoupper('timestamp without time zone')) || ($row->type == strtoupper('date'))  || ($row->type == strtoupper('datetime')) ){
			return 'timestamp without time zone';
		}
		if( ($row->type == strtoupper('integer')) || ($row->type == strtoupper('int'))){
			return 'integer';
		}
		if(($row->type == strtoupper('bgint'))){
			return 'bgint';
		}
		if( ($row->type == strtoupper('double precision')) || ($row->type == strtoupper('double')) ){
			return 'double precision';
		}
		return $row->type;
	}
	
	public function concat($sql)
	{
		$sql = str_ireplace("concat", "CONCAT", $sql);
		$sql = explode("CONCAT(", $sql);
		$res = $sql[0];
		if(count($sql) > 1){
			foreach($sql as $no => $row){
				if($no > 0){
					$ex = explode(")", $row);
					
					$con = $ex[0];
					$con = str_ireplace(" ", "", $con);
					$con = str_ireplace(",", "||", $con);
					$con = str_ireplace("||''||", "||' '||", $con);
					$con = '('.$con.')';
					$res.= $con;
					
					foreach($ex as $no2 => $r){
						if($no2 > 0){
							if($no2 > 1){
								$res.= ')';
							}
							$res.= $r;
						}
					}
				}
			}
		}
		return $res;
	}
}