<?php

class MsSql
{
	public $sql;
	public $db;
	
	public function __construct($config)
	{
		$this->db = $config['dbConnection.DataBase'];
	}

	public function getConnection($config)
	{
		$conn = mssql_connect($config['dbConnection.Host'], $config['dbConnection.UserName'], $config['dbConnection.Password']);
		if (!$conn) die ("<h1>Gagal Melakukan Koneksi Ke Server</h1>");
		mssql_select_db($config['dbConnection.DataBase'], $conn) or die ("<h1>Database Tidak Diketemukan di Server<h1>");
		return;
	}

	public function getDb()
	{
		return new DB;
	}
	
	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)
	{
		$this->sql = $this->sql." FROM [".$this->db."].[dbo].[".$this->getDb()->$param.'] '.$alias;
		return $this->sql;
	}
	
	public function addLeftJoin($table, $alias, $param)
	{
		$tab = $this->getDb()->$table;
		if(empty($tab)){
			$tab = $table;
		}else{
			$tab = "[".$this->db."].[dbo].[".$tab."]";
		}
	
		$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;
		}else{
			$tab = "[".$this->db."].[dbo].[".$tab."]";
		}
	
		$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 getSpecialShort($sql, $param)
	{
		$this->sql = $sql." ORDER BY $param";
		return $this->sql;
	}
	
	protected function getStruktur($tab)
	{
		$sql = "SELECT column_name as nama
				FROM information_schema.COLUMNS WHERE table_name = '".$tab."'
				ORDER BY ordinal_position";
		$res = $this->queryEks($sql);
		return $this->getFetchObject($res);
	}
	
	public function getLimit($awal, $akhir)
	{
		$this->sql = '*******'.$this->sql;
	
		$this->sql = str_ireplace('*******SELECT', '', $this->sql);
		$sql = explode('ORDER BY', $this->sql);
		$qb_1 = $sql[0];
		$qb_2 = $sql[1];
		
		$ex = explode('FROM', $qb_1);
		$ex = trim($ex[0]);
		if($ex == '*'){
			$ex = explode('['.$this->db.'].[dbo].[', $qb_1);
			$col = array();
			foreach($ex as $no => $r){
				if($no > 0){
					$ex = explode(']', $r);
					$table = $ex[0];
					$ins = explode(' ', trim($ex[1]));
					$ins = $ins[0];
					
					$struktur = $this->getStruktur($table);
					foreach($struktur as $row){
						$id = $row->nama;
						$col[$id] = $ins;
					}
				}
			}
			$sel = '';
			$no = 1;
			foreach($col as $key => $row){
				if($no > 1){
					$sel.= ', ';
				}
				$sel.= " $row.$key ";
				$no++;
			}
			$qb_1 = str_ireplace('*', $sel, $qb_1);
		}
		
		$akhir = (int)$awal + (int)$akhir;
		$awal  = $awal + 1;
		
		$this->sql = "SELECT * FROM
						(
						SELECT row_number() OVER (ORDER BY $qb_2) AS rownum, 
							$qb_1
						) AS A
						WHERE A.rownum 
						BETWEEN $awal AND $akhir";
		return $this->convert($this->sql);
	}
	
	public function getSpecialLimit($sql, $awal, $akhir)
	{
		// $this->sql = $sql;
		// return $this->getLimit($awal, $akhir);
		return $sql;
	}
	
	public function addInsert($param)
	{
		$ex = explode(' ', $param);
		if(trim(strtoupper($ex[0])) == 'INSERT'){
			$this->sql = $param;
		}else{
			$tab = "[".$this->db."].[dbo].[".$this->getDb()->$param."]";
			$this->sql = "INSERT INTO ".$tab;
		}
	
		return $this->sql;
	}
	
	public function getAttrib($param)
	{
		$this->sql = $this->sql." ($param)";
		return $this->sql;
	}
	
	public function addAttrIns($attr)
	{
		$sql_1 = ' (';
		$sql_2 = ' (';
		$no = 1;
		foreach($attr as $key => $n){
			if($no > 1){
				$sql_1.= ', ';
				$sql_2.= ', ';
			}
			$no++;
			$sql_1.= $this->convert(" $key ");
			$sql_2.= "'$n'";
		}
		$sql_1.= ')';
		$sql_2.= ')';
		
		$this->sql = $this->sql.$sql_1.' values '.$sql_2;
		return $this->sql;
	}
	
	public function addAttrUp($attr)
	{
		$sql_1 = '';
		$no = 1;
		foreach($attr as $key => $n){
			if($no > 1){
				$sql_1.= ', ';
			}
			$no++;
			
			if($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{
			$tab = "[".$this->db."].[dbo].[".$this->getDb()->$param."]";
			$this->sql = "UPDATE ".$tab;
		}
	
		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 = str_ireplace('to_char(', 'convert(VARCHAR(10), ', $sql);
		$sql = str_ireplace(", 'dd/mm/yyyy')", ', 103)', $sql);
		// echo $sql;
	
		return mssql_query($sql);
	}
	
	public function getNumRows($data)
	{
		return mssql_num_rows($data);
	}
	
	public function getFetchObject($data)
	{
		$result = array();
		while($row = mssql_fetch_object($data)){
			$result[] = $row;
		}
		
		return $result;
	}
	
	public function getFind($table, $select, $param)
	{
		$sql = $this->addSelect($select);
		$sql = $this->getFrom($table, 'a');
		$sql = $this->getWhere('1=1');
		
		foreach($param as $row){
			$sql = $this->andWhere($row);
		}
		
		$eks = $this->queryEks($sql);
		$res = $this->getFetchObject($eks);

		return $res[0];
	}
	
	public function getFetchArray($data)
	{
		$result = array();
		while($row = mssql_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);
		return $res;
	}
	
	public function getRoolbackFlush($sql)
	{
		$query = '';
		foreach($sql as $row){
			$query.= ';'.$this->convert($row);
		}

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

		return mssql_query($query);
	}
	
	public function getGroupBy($param)
	{
		$this->sql = $this->sql." GROUP BY $param";
		return $this->sql;
	}
	
	public function getFindBy($table, $select, $param, $sort)
	{
		$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);
		}

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

			// if(!$eks){
				// mssql_query("ROLLBACK"); 
				// return;
			// }else{
				// mssql_query("COMMIT"); 
			// }
		// }
	}
	
	public function getBeginTransaction()
	{
		// return mssql_query("BEGIN WORK"); 
	}
	
	public function getRollBack()
	{
		// return mssql_query("ROLLBACK"); 
	}
	
	public function getCommit()
	{
		// return mssql_query("COMMIT"); 
	}
	
	public function getRollCek($res)
	{
		// return mssql_cmdtuples($res); 
	}
}