<?php
class Report_Model extends Model{
	public function __construct() {
		parent::__construct();
	}
	
	function getLogs(){
		$data = DAOFactory::getTblLogsDAO()->queryAllOrderBy('id desc LIMIT 50');
		
		return $data;
	}
	
	function submit_db(){
		$dbName = Model::getDatabasepre($_POST['ub']).$_POST['databaseName'];
		
		$databases = DAOFactory::getTblDatabaseListDAO()->queryByDatabaseName($dbName);
		
		if(isset($databases) && !empty($databases)){
			echo 'Sorry, database already exist!';
			exit;
		}
		
		$databases = new TblDatabaseList();
		
		$databases->ub = $_POST['ub'];
		$databases->databaseName = $dbName;
		$databases->status = 'queue';
		$databases->dateCreated = date('Y-m-d H:i:s');
		// $databases->dateUsed = '0000-00-00 00:00:00';
		
		DAOFactory::getTblDatabaseListDAO()->insert($databases);
		
		Model::setLogs('admin transaction', 'created DB:'.$dbName);
		
	}
	
	function searchDatabase(){
		$search = $_REQUEST['dbName'];
		$type = $_REQUEST['ub'];
		$status = $_REQUEST['status'];
		$count = $_REQUEST['counts'];
		$page = $_REQUEST['page'];
		
		$data = DAOFactory::getTblDatabaseListDAO()->searchDatabaseList($search, $type, $status, $count, $page);
		
		return $data;
	}
	function searchDatabaseListAll(){
		$search = $_REQUEST['dbName'];
		$type = $_REQUEST['ub'];
		$status = $_REQUEST['status'];
		
		$data = DAOFactory::getTblDatabaseListDAO()->searchDatabaseListAll($search, $type, $status);
		
		return $data;
	}
	
	function dbUpdates()
	{
		$path = PATHNAME;
		
		if($this->updatedDb() == 1)
		{
		
			$uploaddir = $path.SLASH.'database_update'.SLASH. strtoupper($_POST['ub'] .SLASH);
			if(!is_dir($uploaddir))
			{
				mkdir($uploaddir, 0777, true);
			}
			
			$uploadfile = $uploaddir . basename($_FILES['update-file']['name']);
			
			$ext = pathinfo($uploadfile, PATHINFO_EXTENSION);
			if($ext == 'sql')
			{
				if (move_uploaded_file($_FILES['update-file']['tmp_name'], $uploadfile)){
					$version = date('mdy-Hi');
				
					$newFile = $uploaddir.$_POST['ub'].'_'.$version.'.sql';
					rename ($uploadfile,$newFile);
					
					$request = array('name' => $_POST['ub'].'_'.$version.'.sql','product'=> strtoupper($_POST['ub']) ,'version'=>$version,'type'=>'updates');
					$this->saveDBversion($request);
					
					Model::setLogs('admin transaction', 'Uploaded updates on DB for '.strtoupper($_POST['ub']).'('.$_POST['ub'].'_'.(date('mdy-Hi')).'.sql)');
					
				} else {
				   echo 'Database update failed!';
				}
			}
			else
			{
				echo 'Database update failed!';
			}
		}
		else
		{
			echo 'Database update failed!';
		}

	}
	function updatedDb()
	{
		$path = PATHNAME;
		$dbExist = $path.'databases'.SLASH. $_POST['ub'].'.sql';
		
		if(file_exists($dbExist))
		{
			$bckUpdir = $path.'databases'.SLASH. strtoupper($_POST['ub']).SLASH;
			if(!is_dir($bckUpdir))
			{	
				mkdir($bckUpdir, 0775, true);
				
			}
		}
		$version = date('mdy-Hi');
		$tmpDir = $bckUpdir.'tmp'.SLASH;
		if(!is_dir($tmpDir))
		{
			mkdir($tmpDir, 0775, true);
		}
		
		$tmpDir .= basename($_FILES['database-file']['name']);
		
		if (move_uploaded_file($_FILES['database-file']['tmp_name'], $tmpDir)){
			rename ($tmpDir,$bckUpdir.'tmp'.SLASH.$_POST['ub'].'_'.$version.'.sql');
			
			$request = array('name' => $_POST['ub'].'_'.$version.'.sql','product'=> strtoupper($_POST['ub']) ,'version'=>$version,'type'=>'databases');
			$this->saveDBversion($request);
			Model::setLogs('admin transaction', 'Uploaded updated DB for '.strtoupper($_POST['ub']));
			
			return 1;
		} else {
		   return 0;
		}
		
		exit;
	}
	
	function modifyDb()
	{
		$path = PATHNAME.'database_update';
		
		$dir = scandir($path);
		$folder = '';
		
		
		$dbv = DAOFactory::getTblDbVersionDAO()->getLatestApproved(strtoupper($_POST['type']),'updates');
		
		if(empty($dbv))
		{
			
			echo 'No updates found!';
			exit;
		}
		
		$dbv = $dbv[0];
		foreach($dir as $p)
		{
			if(strtoupper($p) == strtoupper($_POST['type']))
			{
				$folder =  $path.SLASH.$p;
			}
		}
		
		
		if($folder != '' && !empty($dbv))
		{
			$x = scandir($folder);
			$file = '';
			
			foreach($x as $a)
			{
				if($a == $dbv['file_name'])
				{
					$file = $a;
				}
			}
			
			if(!empty($x) && !empty($file))
			{
				
				
				$ext = pathinfo($file, PATHINFO_EXTENSION);
				
				if($ext == 'sql')
				{
					$data = $this->url_get_contents(MAIN_URL.'database_update/'. strtoupper($_POST['type']) .'/'.$file);
					$this->multi_query_executor($data,$_POST['name']);
					$xp = explode('_',$file);
					$version = substr($xp[1], 0, -4);
					
					$db = DAOFactory::getTblDatabaseListDAO()->queryByDatabaseName($_POST['name']);
					$db[0]->version = $version;
					DAOFactory::getTblDatabaseListDAO()->update($db[0]);
					
					
					
					Model::setLogs('admin transaction', 'Updated DB: '.$_POST['name'].'(v.'.$version.')');
					
					
					
				}
				else
				{
					echo 'No updates found!';
				}
			}
		}
		else
		{
			echo 1;
			echo 'No updates found!';
		}
	}
	
	
	
	function multi_query_executor($sql,$dbname)
	{
		
		$conn = new mysqli(HOST, GENERATEDDATABASEUSER, GENERATEDDATABASEPASS);
		
		$conn->select_db(GENERATEDDATABASEUSER_PRE.$dbname);
		if ($conn->multi_query($sql) === TRUE) {
			$conn->close();
		} else {
			echo "Error Setting up Data Tables on ".$dbname." : " . $conn->error;
			$conn->close();
			exit;
		}
	}
	
	function saveDBversion($data)
	{
		$user = Session::getSession('userDbUser');
		
		$db = new TblDbVersion();
		$db->userId = $user['id'];
		$db->fileName = $data['name'];
		$db->product = $data['product'];
		$db->dbVersion = $data['version'];
		$db->dbStatus = 'pending';
		$db->dbType = $data['type'];
		$db->approvedId = 0;
		$db->createdAt = date('Y-m-d H:i:s');
		$db->updatedAt = date('Y-m-d H:i:s');
		$versionId = DAOFactory::getTblDbVersionDAO()->insert($db);
		
		if($data['type'] == 'updates')
		{
			$insert = new TblUpdateDescription();
			$insert->tblVersionId = $versionId;
			$insert->description = $_POST['description'];
			
			DAOFactory::getTblUpdateDescriptionDAO()->insert($insert);
		}
	}
	function getDbVersion()
	{
		$data = DAOFactory::getTblDbVersionDAO()->getDbVersion();
		return $data;
	}
	function getFileContent()
	{
		$file = DAOFactory::getTblDbVersionDAO()->load($_POST['fileId']);
		$description = DAOFactory::getTblUpdateDescriptionDAO()->queryByTblVersionId($_POST['fileId']);
		$path = MAIN_URL;
		$dir = PATHNAME;
		
		$file_check = '';
		
		if($file->dbType == 'updates')
		{
			$path .= 'database_update/'.strtoupper($file->product).'/'.$file->fileName;
			$file_check = $dir.'database_update'. SLASH .strtoupper($file->product).SLASH.$file->fileName;
		}
		else
		{
			$path .= 'databases/'.$file->product.'/tmp/'.$file->fileName;
			$file_check = $dir.'databases'. SLASH .strtoupper($file->product).SLASH.'tmp'.SLASH.$file->fileName;
		}
		
		// exit;
		if(file_exists($file_check))
		{
			$data = $this->url_get_contents($path);
			$array = array('file_name' => $file->fileName,'query' => $data,'description' => !empty($description) ? $description[0]->description : '');
			return $array;
		}
		else
		{
			return '';
		}
		
			
		
	}
	function responseToUpload()
	{
		$user = Session::getSession('userDbUser');
	
		$file = DAOFactory::getTblDbVersionDAO()->load($_POST['fileId']);
		$oldS = $file->dbStatus;
		
		$file->dbStatus = $_POST['type'] == 'file-approve' ? 'approved' : 'disapproved';
		$file->approvedId = $user['id'];
		$file->updatedAt = date('Y-m-d H:i:s');
		
		
		
		if($file->dbType == 'databases' && $file->dbStatus == 'approved')
		{
			$this->moveUpdatedVersionDB($file->product,$file->fileName);
		}
		
		DAOFactory::getTblDbVersionDAO()->update($file);
		
		Model::setLogs('admin transaction', 'Change '.$file->fileName.' status from '.$oldS.' to '.$file->dbStatus);
		echo ucwords($file->dbStatus);
	}
	function moveUpdatedVersionDB($product,$file)
	{
		$path = PATHNAME;
		$dbExist = $path.'databases'.SLASH . strtolower($product).'.sql';
		
		if(file_exists($dbExist))
		{
			$bckUpdir = $path.'databases'.SLASH .strtoupper($product).SLASH.'tmp'.SLASH;
			if(!is_dir($bckUpdir))
			{	
				mkdir($bckUpdir, 0775, true);
			}
			$bckUpdir .= $file;
			
			
			if (copy($bckUpdir, $path.'databases'.SLASH.$file))
			{
				$dbbckup = $path.'databases'.SLASH .strtoupper($product). SLASH .strtolower($product).'.sql';
				$version = date('mdy-Hi');
				
				copy($dbExist,$dbbckup);
				rename($dbbckup,$path.'databases'. SLASH .strtoupper($product). SLASH .strtolower($product).'_'.$version.'.sql');
				unlink($dbExist);
				
				rename($path.'databases'. SLASH .$file,$path.'databases'. SLASH .strtolower($product).'.sql');
				unlink($bckUpdir);
				return 1;
			}
			else
			{
				return 0;
			}
		}
	}
	
	function modifyAllDb()
	{
		$path = PATHNAME.'database_update';
		
		$dir = scandir($path);
		$folder = '';
		
		
		$dbv = DAOFactory::getTblDbVersionDAO()->getLatestApproved(strtoupper($_POST['type']),'updates');
		
		
		if(empty($dbv))
		{
			
			echo 'No updates found!';
			exit;
		}
		
		$dbv = $dbv[0];
		foreach($dir as $p)
		{
			if(strtoupper($p) == strtoupper($_POST['type']))
			{
				$folder =  $path.SLASH.$p;
			}
		}
		
		
		if($folder != '' && !empty($dbv))
		{
			$x = scandir($folder);
			$file = '';
			
			foreach($x as $a)
			{
				if($a == $dbv['file_name'])
				{
					$file = $a;
				}
			}
			
			if(!empty($x) && !empty($file))
			{
				
				
				$ext = pathinfo($file, PATHINFO_EXTENSION);
				
				if($ext == 'sql')
				{
					$data = $this->url_get_contents(MAIN_URL.'database_update/'. strtoupper($_POST['type']) .'/'.$file);
					$ubUsers = DAOFactory::getTblDatabaseListDAO()->getDbUpdates($_POST['type']);
					$success = 0;
					
					
					foreach($ubUsers as $uu)
					{
						$checkDb = $this->dbCheckOnMain($_POST['type'],$uu->databaseName);
						// if(!empty($checkDb))
						// {
							$this->multi_query_executor($data,$uu->databaseName);
							$xp = explode('_',$file);
							$version = substr($xp[1], 0, -4);
							
							$uu->version = $version;
							DAOFactory::getTblDatabaseListDAO()->update($uu);
							Model::setLogs('admin transaction', 'Updated DB: '.$uu->databaseName.'(v.'.$version.')');
							$success++;
						// }
						// else
						// {
							// $uu->status = 'removed';
							// DAOFactory::getTblDatabaseListDAO()->update($uu);
							// Model::setLogs('admin transaction', 'Removed DB: '.$uu->databaseName);
						// }
					}
					
					echo '<br/>'.$success.' out of '.count($ubUsers).' databases has been updated';
					
				}
				else
				{
					echo 'No updates found!';
				}
			}
		}
		else
		{
			echo 'No updates found!';
		}
	}
	
	function dbCheckOnMain($type,$name)
	{
		$mainDb = '';
		switch($type)
		{
			case 'umd':
				$mainDb = UB_UMD_MAIN;
				break;
			case 'unp':
				$mainDb = UB_UNP_MAIN;
				break;
			case 'utd':
				$mainDb = UB_UTD_MAIN;
				break;
			case 'urt':
				$mainDb = UB_URT_MAIN;
				break;
			case 'usv':
				$mainDb = UB_USV_MAIN;
				break;
			default:
				// return false;
		}
		
		$name = str_replace('unlibook_','',$name);
		$db = DAOFactory::getTblDatabaseListDAO()->getVerifiedDatabase($mainDb,$name);
		return $db;
	}
	
	function getSqlContent()
	{
		$url = TP.$_POST['path'];
		$data = $this->url_get_contents($url);
		print_r($data);
	}
	
	function searchClient()
	{
		$ub = isset($_REQUEST['ub']) ? $_REQUEST['ub'] : '';
		$limit = isset($_REQUEST['counts']) ? $_REQUEST['counts'] : '20';
		$page = isset($_REQUEST['page']) ? $_REQUEST['page'] : '1';
		$from = ($page - 1) * $limit;
		$ubName = explode('.',$ub);
		
		$conn = $this->customSqlConnectOpen($ubName[0]);
		
		$sql = "select * from `".$ubName[1]."` limit ".$from.",".$limit."";
		
		$result = $conn->query($sql);
		$i = 0;
		$databaseList = array();
			
		if ($result->num_rows > 0) {
			while ($row = $result->fetch_assoc()) {
				$databaseList[$i] = $row;
				$i++;
			}
		} else {
			// echo "0 results";
		}
		return $databaseList;
		$conn->close();
	}
	function searchClientCount()
	{
		$ub = isset($_REQUEST['ub']) ? $_REQUEST['ub'] : '';
		$ubName = explode('.',$ub);
		
		$conn = $this->customSqlConnectOpen($ubName[0]);
		
		$sql = "select * from `".$ubName[1]."` ";
		
		$result = $conn->query($sql);
		$i = 0;
		$databaseList = array();
			
		if ($result->num_rows > 0) {
			while ($row = $result->fetch_assoc()) {
				$databaseList[$i] = $row;
				$i++;
			}
		} else {
			// echo "0 results";
		}
		return $databaseList;
		$conn->close();
	}
	
	function getClientLogContent()
	{
		$datefrom = isset($_REQUEST['datefrom']) ? $_REQUEST['datefrom'] : '';
		$dateto = isset($_REQUEST['dateto']) ? $_REQUEST['dateto'] : '';
		$dbName = isset($_REQUEST['dbName']) ? $_REQUEST['dbName'] : '';
		
		$conn = $this->customSqlConnectOpen($dbName);
		
		if($datefrom != '')
		{
			$sql = "select * from `tbl_users_log` where `date_time` between '$datefrom' and '$dateto' order by `date_time` desc";
		}
		else
		{
			$sql = "select * from `tbl_users_log` order by `date_time` desc limit 50";
		}
		
		$result = $conn->query($sql);
		$i = 0;
		$data = array();
		if ($result->num_rows > 0) {
			while($row = $result->fetch_assoc()) {
				$data[$i] = $row;
				$i++;
			}
		} else {
			// echo "0 results";
		}
		
		return $data;
		$conn->close();
	}
	
	function countSummary()
	{
		$dbName = isset($_REQUEST['dbName']) ? $_REQUEST['dbName'] : '';
		$conn = $this->customSqlConnectOpen($dbName);
		
		$sql = "select count(*) from `tbl_users_log` where `date_time` like '%".date('Y-m-d')."%'";
		
		$result = $conn->query($sql);
		
		if ($result->num_rows > 0) {
			while($row = $result->fetch_assoc()) {
				$data = $row;
			}
		} else {
			// echo "0 results";
		}
		echo $data['count(*)'];
	}
	
	function customSqlConnectOpen($dbName)
	{
		$conn = new mysqli(DATABASE_HOST,DATABASE_USER,DATABASE_PASS,$dbName);
		if ($conn->connect_error) {
			die("Connection failed: " . $conn->connect_error);
		}
		return $conn;
	}

	function getDbVersionList()
	{
		$data = DAOFactory::getTblDbVersionDAO()->getDbVersionList();
		return $data;
	}
	
}
?>