<?php 	
if(!(is_readable($_SERVER['DOCUMENT_ROOT'].'/classes/BaseDBFacade.php'))) 
{ 
    die("Error - Could not open or read file BaseDBFacade.php"); 
} 
else 
{ 
    include_once($_SERVER['DOCUMENT_ROOT'].'/classes/BaseDBFacade.php'); 
} 

class MySQLDBFacade extends BaseDBDataFacade
{
	static private $instance = NULL;
	
	public static function getInstance()
	{
		if (self::$instance == NULL)
		{
			self::$instance = new MySQLDBFacade();
		}
		return self::$instance;
	}
	
	private function _connect()
	{
		$cn = null;
		if (IsSettingSet('db.port')){
			$cn = mysqli_connect(GetSetting('db.uri'), GetSetting('db.userLogin'), GetSetting('db.userPassword'), GetSetting('db.database'), GetSetting('db.port'));
		} else {
			$cn = mysqli_connect(GetSetting('db.uri'), GetSetting('db.userLogin'), GetSetting('db.userPassword'), GetSetting('db.database'));
		} 
		
		/* check connection */
		if (mysqli_connect_errno()) {
			printf("Connect failed: %s\n", mysqli_connect_error());
			exit();
		}
		$cn->query('SET NAMES utf8');
		return $cn;	
	}


	public function getReportsToObject($oguid, $iduser, $idlanguage)
	{
		$cn = $this->_connect();
		if ( null === $cn ) {
			return null;
		}
		else {
			$resarr = array();
			$query  = "CALL load_reports( '".$oguid."' , ".$iduser.", '".$idlanguage."' );";
			$query .= "SELECT pr.* FROM portalreport as pr, pr_temp as prt WHERE pr.idportalreport = prt.idportalreport ORDER BY pr.pr_position;";
			if ($cn->multi_query($query)) {
				$cn->next_result();
				$result = $cn->store_result();
				if ($result) {
					while ($row = $result->fetch_assoc()) {
						array_push($resarr, $row);
					}
					$result->close();
				}
			}
			$query .= "DROP TEMPORARY TABLE IF EXISTS `po_temp`;";
			$cn->query($query);
			$cn->close();
			return $resarr;
		}
	}
	
	public function movePersonalLink($iduser, $pos, $dest){
		$cn = $this->_connect();
		if ( null === $cn ) {
			return null;
		}
		else {
//			$ido = self::getIDObject($oguid);
	//		$idr = self::getIDReport($rguid);
		//	$query = 'call add_personallinks('.$iduser.','.$ido.', '.$idr.');';
			$cn->query($query);
			$cn->close();
		}
	}

	public function delPersonalLink($iduser, $pos, $dest){
		$cn = $this->_connect();
		if ( null === $cn ) {
			return null;
		}
		else {
			$query = "SELECT idportalobject FROM portalobject WHERE po_guid='".$oguid."';";
			$ar = $cn->query($query)->fetch_assoc();
			$ido = 0;
			if (sizeof($ar) > 0){
				$ido = $ar['idportalobject'];
			}
			$query = "SELECT idportalreport FROM portalreport WHERE pr_guid='".$rguid."';";
			$ar = $cn->query($query)->fetch_assoc();
			$idr = 0;
			if (sizeof($ar) > 0){
				$idr  = $ar['idportalreport'];
			}
			$query = 'DELETE personallinks WHERE iduser = '.$iduser.' AND idobject = '.$ido.' AND idreport = '.$idr.');';
			$cn->query($query);
			$cn->close();
		}
	}
	
	public function getForumMessages($oguid){
		$cn = $this->_connect();
		if ( null === $cn ) {
			return null;
		}
		else {
			$query = "SELECT idportalobject FROM portalobject WHERE po_guid='".$oguid."';";
			$ar = $cn->query($query)->fetch_assoc();
			$ido = 0;
			$resarr = array();
			if (sizeof($ar) > 0){
				$ido = $ar['idportalobject'];
				$query = "SELECT * FROM `forummessages` as fm where fm.idobject = ".$ido.";";
				$result = $cn->query($query);
				if ($result) {
					while ($row = $result->fetch_assoc()) {
						array_push($resarr, $row);
					}
				}
			}
			return $resarr;
			$cn->close();
		}
	}
	
	public function addForumMessages($idforummessageparetn, $oguid, $iduser, $frmm_body){
		$cn = $this->_connect();
		if ( null === $cn ) {
			return null;
		}
		else {
			$query = "SELECT idportalobject FROM portalobject WHERE po_guid='".$oguid."';";
			$ar = $cn->query($query)->fetch_assoc();
			$ido = 0;
			$resarr = array();
			if (sizeof($ar) > 0){
				$ido = $ar['idportalobject'];
				$query = "call add_forummessages(".$idforummessageparetn.", ".$ido.", ".$iduser.", '".$frmm_body."');";
				$cn->query($query);
			}
			$cn->close();
		}
	}
	
	public function getPersonalLinks($iduser){
		$cn = $this->_connect();
		if ( null === $cn ) {
			return null;
		}
		else {
			$resarr = array();
			$query = "SELECT idpersonallink, pl.pl_pos, obj.po_name, obj.po_guid, rep.pr_name, rep.pr_guid FROM  personallinks as pl, portalobject as obj, portalreport as rep where pl.iduser = ".$iduser." and pl.idobject = obj.idportalobject and pl.idreport = rep.idportalreport order by pl.pl_pos;";
			$result = $cn->query($query);
			if ($result) {
				while ($row = $result->fetch_assoc()) {
					array_push($resarr, $row);
				}
			}
			return $resarr;
			$cn->close();
		}
	}
	
	
	
	public function addPersonalLink($iduser, $oguid, $rguid){
		$cn = $this->_connect();
		if ( null === $cn ) {
			return null;
		}
		else {
			$query = "SELECT idportalobject FROM portalobject WHERE po_guid='".$oguid."';";
			$ar = $cn->query($query)->fetch_assoc();
			$ido = 0;
			if (sizeof($ar) > 0){
				$ido = $ar['idportalobject'];
			}
			$query = "SELECT idportalreport FROM portalreport WHERE pr_guid='".$rguid."';";
			$ar = $cn->query($query)->fetch_assoc();
			$idr = 0;
			if (sizeof($ar) > 0){
				$idr  = $ar['idportalreport'];
			}
			
			$query = 'call add_personallinks('.$iduser.','.$ido.', '.$idr.');';
			$cn->query($query);
			$cn->close();
		}
	}
	

	public function getReportData($iduser, $oguid, $rguid){
		$cn = $this->_connect();
		if ( null === $cn ) {
			return null;
		}
		else {
			$query = "SELECT idportalobject FROM portalobject WHERE po_guid='".$oguid."';";
			$ar = $cn->query($query)->fetch_assoc();
			$ido = 0;
			if (sizeof($ar) > 0){
				$ido = $ar['idportalobject'];
			}
			$query = "SELECT idportalreport FROM portalreport WHERE pr_guid='".$rguid."';";
			$ar = $cn->query($query)->fetch_assoc();
			$idr = 0;
			if (sizeof($ar) > 0){
				$idr  = $ar['idportalreport'];
			}
			$resarr = array();
			$query = ""; 
			$query = "SELECT * FROM `reportdata` AS rd WHERE rd.`idobject` = ".$ido." AND rd.`idreport` = ".$idr." AND rd.`iduser` = ".$iduser.";";
			//echo $query;
			$result = $cn->query($query);
			if ($result) {
				while ($row = $result->fetch_assoc()) {
					array_push($resarr, $row);
				}
			}
			return $resarr;
		}
	}
	
	public function addRating_ReportData($iduser, $oguid, $rguid, $rating){
		$cn = $this->_connect();
		if ( null === $cn ) {
			return null;
		}
		else {
			$query = "SELECT idportalobject FROM portalobject WHERE po_guid='".$oguid."';";
			$ar = $cn->query($query)->fetch_assoc();
			$ido = 0;
			if (sizeof($ar) > 0){
				$ido = $ar['idportalobject'];
			}
			$query = "SELECT idportalreport FROM portalreport WHERE pr_guid='".$rguid."';";
			$ar = $cn->query($query)->fetch_assoc();
			$idr = 0;
			if (sizeof($ar) > 0){
				$idr  = $ar['idportalreport'];
			}
			$query = 'call add_rating_reportdata('.$ido.', '.$idr.','.$iduser.','.$rating.');';
			$cn->query($query);
			$cn->close();
		}
	}
	

	public function clear_portalobject(){
		$cn = $this->_connect();
		if ( null === $cn ) {
			return null;
		}
		else {
			$query = 'call clear_portalobject();';
			$cn->query($query);
			$cn->close();
		}
	}
	
	public function addPortalObject($parent_guid, $name, $guid, $icon){
		$cn = $this->_connect();
		if ( null === $cn ) {
			return null;
		}
		else {
			$query = "call add_portalobject('".$parent_guid."','".$name."','".$guid."',".$icon.");"; 
			$cn->query($query);
			$cn->close();
		}
	}
	

	public function deleteportalreport(){
		$cn = $this->_connect();
		if ( null === $cn ) {
			return null;
		}
		else {
			$query = 'DELETE FROM `portalreport`;';
			$cn->query($query);
			$cn->close();
		}
	}
	
	public function addportalreport($name, $guid, $pos, $dateexpire){
		$cn = $this->_connect();
		if ( null === $cn ) {
			return null;
		}
		else {
				$query = "INSERT INTO `portalreport` (`idportalreport` ,`pr_name` ,`pr_guid` ,`pr_position` ,	`pr_expirationdate`) ";
				$query .= "VALUES (NULL , '".$name."', '".$guid."', ".$pos.", '".$dateexpire."');";
			$cn->query($query);
			$cn->close();
		}
	}
	

	
	public function deleteobjectsreports(){
		$cn = $this->_connect();
		if ( null === $cn ) {
			return null;
		}
		else {
			$query = 'DELETE FROM `objectreport`;';
			$cn->query($query);
			$cn->close();
		}
	}
	
	public function addobjectsreports($oguid, $rguid){
		$cn = $this->_connect();
		if ( null === $cn ) {
			return null;
		}
		else {

			$query = "SELECT idportalobject FROM portalobject WHERE po_guid='".$oguid."';";
			$ar = $cn->query($query)->fetch_assoc();
			$ido = 0;
			if (sizeof($ar) > 0){
				$ido = $ar['idportalobject'];
			}
			echo $ido;
			$query = "SELECT idportalreport FROM portalreport WHERE pr_guid='".$rguid."';";
			$ar = $cn->query($query)->fetch_assoc();
			$idr = 0;
			if (sizeof($ar) > 0){
				$idr  = $ar['idportalreport'];
			}
			echo $idr;
			$query = "NSERT INTO `objectreport` (`idportalobject` ,`idportalreport` ,`or_datechange`)";
			$query .= "VALUES (".$ido.", ".$idr.", NOW());";
			echo $query;
			$cn->query($query);
			$cn->close();
		}
	}
	
	
	
	
	
	public function getDefualtReportToObject($iduser, $oguid){
		$cn = $this->_connect();
		if ( null === $cn ) {
			return null;
		}
		else {
			$query = "SELECT idportalobject FROM portalobject WHERE po_guid='".$oguid."';";
			$ar = $cn->query($query)->fetch_assoc();
			$ido = 0;
			if (sizeof($ar) > 0){
				$ido = $ar['idportalobject'];
			}
			$resarr = array();
			$query = "";
			if (null === $iduser || $iduser == 0 ) {
				$query = "SELECT pr.`pr_guid` FROM `portalreport` AS pr, `objectreport` AS orl WHERE	orl.`idportalobject` = ".$ido."	AND orl.`idportalreport` = pr.`idportalreport` order by pr.pr_position  LIMIT 0 , 1;";
			} else {
				$query = "SELECT pr.`pr_guid` FROM `portalreport` AS pr, `rightsreports` AS rr, `objectreport` AS orl WHERE rr.`iduser` = ".$iduser." AND rr.`idportalreport` = pr.`idportalreport`	AND orl.`idportalobject` = ".$ido."	AND orl.`idportalreport` = pr.`idportalreport`	order by pr.pr_position LIMIT 0 , 1;";
			} 
			$ar = $cn->query($query)->fetch_assoc();
			if (sizeof($ar) > 0){
				return $ar['pr_guid'];
			} else {
				return null;
			}
			$cn->close();
		}
	}
	
	
	public function getObjectTreetoUser($parent_idportalobject, $iduser, $idlanguage)
	{
		$cn = $this->_connect();
		if ( null === $cn ) {
			return null;
		}
		else {
			$resarr = array();
			$query  = "CALL load_portalobject( ".$parent_idportalobject." , ".$iduser." );";
			$query .= "SELECT po.* FROM portalobject as po, po_temp as pot WHERE po.idportalobject = pot.idportalobject ORDER BY po.po_ileft;";
			
			if ($cn->multi_query($query)) {
				$cn->next_result();
				$result = $cn->store_result();
				if ($result) {
					while ($row = $result->fetch_assoc()) {
						array_push($resarr, $row);
					}
					$result->close();
				}
			$query .= "DROP TEMPORARY TABLE IF EXISTS `po_temp`;";
			$cn->query($query);
			$query .= "DROP TEMPORARY TABLE IF EXISTS `po_temp_clone`;";
			$cn->query($query);
			$cn->close();
			return $resarr; 
			}			
		}
	}

	public function getPreparedObjectTreetoUser($idportalobject, $iduser, $idlanguage)
	{
		$cn = $this->_connect();
		if ( null === $cn ) {
			return null;
		}
		else {
			$resarr = array();
			$query  = "CALL load_objecttree( ".$idportalobject." , ".$iduser." );";
			$query .= "(SELECT po.* FROM portalobject as po, po_temp as pot WHERE po.idportalobject = pot.idportalobject ) UNION (SELECT po.* FROM portalobject as po, po_temp_tree as pottr WHERE po.idportalobject = pottr.idportalobject ) ORDER BY po_ileft;";
				
			/* execute multi query */
			if ($cn->multi_query($query)) {
				$cn->next_result();
				$result = $cn->store_result();
				if ($result) {
					while ($row = $result->fetch_assoc()) {
						array_push($resarr, $row);
					}
					$result->close();
				}
				/* close connection */
			}
			$query .= "DROP TEMPORARY TABLE IF EXISTS `po_temp`;";
			$cn->query($query);
			$cn->close();
			return $resarr;
		}
	}
	
	
	public function getIDObject($oguid)
	{
		$cn = $this->_connect();
		if ( null === $cn ) {
			return null;
		}
		else {
			$resarr = array();
			$query = "SELECT idportalobject FROM portalobject WHERE po_guid='".$oguid."';";
			$result = $cn->query($query);
			$ar = $result->fetch_assoc();
			if (sizeof($ar) > 0){
				return $ar['idportalobject'];
			} else { 
				return 0;
			}
			$cn->close();
		}
	}

	public function getIDReport($rguid)
	{
		$cn = $this->_connect();
		if ( null === $cn ) {
			return null;
		}
		else {
			$resarr = array();
			$query = "SELECT idportalreport FROM portalreport WHERE pr_guid='".$rguid."';";
			$result = $cn->query($query);
			$ar = $result->fetch_assoc();
			if (sizeof($ar) > 0){
				return $ar['idportalreport'];
			} else {
				return 0;
			}
			$cn->close();
		}
	}
	
	
	public function getUserRightsToObject($iduser, $oguid)
	{
		if (null === $iduser || $iduser == 0 ) {
			$ar = array("1" => "1");
			return $ar;  
		} else {
			$cn = $this->_connect();
			if ( null === $cn ) {
				return null;
			}
			else {
				$resarr = array();
				$query = "SELECT 1 FROM rightsobjects as ro, portalobject as po WHERE ro.idportalobject = po.idportalobject and ro.iduser = ".$iduser." and po.po_guid='".$oguid."';";
				$result = $cn->query($query);
				return $result->fetch_assoc();
				$cn->close();
			}
		}
	}
	
	public function getUserRightsToReport($iduser, $rguid)
	{
		if (null === $iduser || $iduser == 0 ) {
			$ar = array("1" => "1");
			return $ar;  
		} else {
			$cn = $this->_connect();
			if ( null === $cn ) {
				return null;
			}
			else {
				$resarr = array();
				$query = "SELECT 1 FROM rightsreports as rr, portalreport as pr WHERE rr.idportalreport = pr.idportalreport and rr.iduser = ".$iduser." and pr.pr_guid='".$rguid."';";
				$result = $cn->query($query);
				return $result->fetch_assoc();
				$cn->close();
			}
		}
	}

	public function userLogin($user_login, $user_Password)
	{
		return true;
	}
	
	public function getUserDataByUserID($iduser)
	{
		$cn = $this->_connect();
		if ( null === $cn ) {
			return null;
		}
		else {
			$resarr = array();
			$query = "SELECT * FROM user WHERE iduser = ".$iduser." ;";
			$result = $cn->query($query);
			return $result->fetch_assoc();
			$cn->close();
		}
	}

	public function isUserAdmin($iduser){
		$usr = 	self::getUserDataByUserID($iduser);
		if ($usr === null){
			return false;
		} else {
			return $usr['user_isadmin'] == 1; 
		}
	}
	
	public function getUserDataByUserLogin($user_login)
	{
			$cn = $this->_connect();
		if ( null === $cn ) {
			return null;
		}
		else {
			$resarr = array();
			$query = "SELECT * FROM user WHERE user_login = '".$user_login."' ;";
			$result = $cn->query($query);
			return $result->fetch_assoc();
			$cn->close();
		}
	}
}
?> 