<?php
require_once('files/lib/DBManager.class.php');

class TicketModel
{
	function TicketModel()
	{}
	
	/*----------------------------------------------------------------------------------------------------------------*/
	function get_list($type,$accountId,$start,$amount)
	{
		$data = null;	
		$sql  = null;
		
		try
		{
			$mysqli 	= @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
			$accountId  = (int)$accountId;
			$start      = (int)$start;
			$amount     = (int)$amount;
			
			if($type == 0)
				{
					$sql = "(SELECT t.id,t.parent_id,t.kategoria_id,t.status_id,t.priorytet_id,t.nadawca_id,t.odbiorca_id,t.temat,t.wiadomosc,t.data_wyslania,t.data_odebrania,
								   (SELECT k.nazwa FROM kategorie k WHERE k.id = t.kategoria_id) AS kategoria,
								   (SELECT COUNT(t1.id) FROM tickety t1 WHERE t1.parent_id=t.id) AS liczba_odpowiedzi,
								   (SELECT s.nazwa FROM statusy s WHERE s.id = t.status_id) AS status,
								   (SELECT p.nazwa FROM priorytety p WHERE p.id = t.priorytet_id) AS priorytet,
								   (SELECT COUNT(zal0.id) FROM zalaczniki zal0 WHERE zal0.ticket_id = t.id) as zalaczniki,
								   GET_USER_NAME_BY_ID(t.nadawca_id) AS nadawca,
								   GET_USER_NAME_BY_ID(t.odbiorca_id) AS odbiorca
							FROM tickety AS t 
							WHERE t.odbiorca_id= $accountId AND t.parent_id IS NULL) 
							ORDER BY t.data_wyslania DESC
							LIMIT $start,$amount";
				}
			else
				{
					//wyslane
					$sql = "SELECT t.id,t.parent_id,t.kategoria_id,t.status_id,t.priorytet_id,t.nadawca_id,t.odbiorca_id,t.temat,t.wiadomosc,t.data_wyslania,t.data_odebrania,
								   (SELECT k.nazwa FROM kategorie k WHERE k.id = t.kategoria_id) AS kategoria,
								   (SELECT COUNT(t0.id) FROM tickety t0 WHERE t0.parent_id=t.id) AS liczba_odpowiedzi,
								   (SELECT s.nazwa FROM statusy s WHERE s.id = t.status_id) AS status,
								   (SELECT p.nazwa FROM priorytety p WHERE p.id = t.priorytet_id) AS priorytet,
								   (SELECT COUNT(zal0.id) FROM zalaczniki zal0 WHERE zal0.ticket_id = t.id) as zalaczniki,
								   GET_USER_NAME_BY_ID(t.nadawca_id) AS nadawca,
								   GET_USER_NAME_BY_ID(t.odbiorca_id) AS odbiorca,
								   IS_NOT_NULL(t.data_odebrania) AS odebrano
							FROM tickety AS t 
							WHERE t.nadawca_id= $accountId  AND t.parent_id IS NULL
							ORDER BY t.data_wyslania DESC
							LIMIT $start,$amount";				
				}
				
			$result 	= $mysqli->query($sql);
				
			while($row  =  $result->fetch_assoc())
			   $data[]  = $row;
			   
			return $data;				
						
		}
		catch(Exception $ex)
		{
			return FALSE;			
		}
		return FALSE;
	}
	/*----------------------------------------------------------------------------------------------------------------*/
	function get_sended_list_of_ticket_id($accountId,$monthCondition=null)
	{
		$row 		= null;
		$sql  		= null;
		$result  	= null;
		
		try
		{
			$mysqli = @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
					
			if($monthCondition == null)
			{
				
				$sql  = "(SELECT t0.id FROM tickety AS t0 WHERE t0.nadawca_id= $accountId AND t0.parent_id IS NULL )
		 				 UNION
						 (SELECT t0.id FROM tickety AS t0 WHERE t0.nadawca_id= $accountId AND t0.parent_id IS NOT NULL
		                 AND t0.parent_id NOT IN (SELECT t1.id FROM tickety AS t1 WHERE t1.nadawca_id= $accountId AND t1.parent_id IS NULL)
						 GROUP BY t0.parent_id)
						 ORDER BY id DESC";
				//$query = $this->default->query($sql,array($accountId,$accountId,$accountId));
				$mysqli->query("SET NAMES 'utf8'");
				$result = $mysqli->query($sql);
			}
			else
			{
	
				$current_year  = (int)date('o');
				$current_month = (int)date('m');
				
				$yearCondition = $current_year; 
				if($current_month < $monthCondition)
					$yearCondition--; 
								
				$sql  = "(SELECT t0.id FROM tickety AS t0 WHERE t0.nadawca_id= ? AND t0.parent_id IS NULL AND MONTH(t0.data_wyslania) = ? AND YEAR(t0.data_wyslania) = ?)
		 				 UNION
						 (SELECT t0.id FROM tickety AS t0 WHERE t0.nadawca_id= ? AND t0.parent_id IS NOT NULL AND MONTH(t0.data_wyslania) = ? AND YEAR(t0.data_wyslania) = ? 
		                 AND t0.parent_id NOT IN (SELECT t1.id FROM tickety AS t1 WHERE t1.nadawca_id= ? AND t1.parent_id IS NULL)
						 GROUP BY t0.parent_id)
						 ORDER BY id DESC";
				//$query = $this->default->query($sql,array($accountId,$monthCondition,$yearCondition,$accountId,$monthCondition,$yearCondition,$accountId));
				$mysqli->query("SET NAMES 'utf8'");
				$result = $mysqli->query($sql);		
			}
			
			/*
			foreach ($query->result() as $row)
			{
				$data[] = $row->id;			
			}
			
			$query->free_result();
			return $data;
			*/
			$data = null;
			while($row = $result->fetch_assoc())
				$data[] = $row['id'];
			return $data;		
		}
		catch(Exception $e)
		{
			//echo 'Error'.$e->getMessage().'<br />';							
		}	
		return FALSE;		
	}	
/*----------------------------------------------------------------------------------------------------------------*/
	function get_received_list_of_ticket_id($accountId,$monthCondition = null)
	{
		$data 	 = null;
		$sql  	 = null;
		$result  = null;
		
		$mysqli = @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
		
			if($monthCondition == null)
			{
				$sql  = "(SELECT t0.id FROM tickety AS t0 WHERE t0.odbiorca_id = $accountId AND t0.parent_id IS NULL)
		 				 UNION
						 (SELECT t0.id FROM tickety AS t0 WHERE t0.odbiorca_id = $accountId AND t0.parent_id IS NOT NULL
		                 AND t0.parent_id NOT IN (SELECT t1.id FROM tickety AS t1 WHERE t1.odbiorca_id = $accountId AND t1.parent_id IS NULL)
						 GROUP BY t0.parent_id)
						 ORDER BY id DESC";
				$mysqli->query("SET NAMES 'utf8'");
				$result = $mysqli->query($sql);
			}
			else
			{
				
 			$current_year  = (int)date('o');
			$current_month = (int)date('m');
			
			$yearCondition = $current_year; 
			if($current_month < $monthCondition)
				$yearCondition--; 

				$sql  = "(SELECT t0.id FROM tickety AS t0 WHERE t0.odbiorca_id= $accountId AND t0.parent_id IS NULL AND MONTH(t0.data_wyslania) = $monthCondition AND YEAR(t0.data_wyslania) = $yearCondition)
		 				 UNION
						 (SELECT t0.id FROM tickety AS t0 WHERE t0.odbiorca_id= $accountId AND t0.parent_id IS NOT NULL  AND MONTH(t0.data_wyslania) = $monthCondition AND YEAR(t0.data_wyslania) = $yearCondition
		                 AND t0.parent_id NOT IN (SELECT t1.id FROM tickety AS t1 WHERE t1.odbiorca_id= $accountId AND t1.parent_id IS NULL)
						 GROUP BY t0.parent_id)
						 ORDER BY id DESC";

				$result = $mysqli->query($sql);

			}
		$data = null;
	    while ($row = $result->fetch_assoc()) {
		       $data[] = $row['id'];
		    }			
			
		return $data;					 
	}	
	//------------------------------------------------------------------------------------------------
	function get_sended_ticket_list($accountId,$start,$amount,$condition=null) //!!!
	{
		$ids = $this->get_sended_list_of_ticket_id($accountId,$condition);
		$data = null;
		for($i=$start;$i<($start+$amount);$i++)
		{
			if($i<count($ids))
				$data[] = $this->get_received_ticket_list_item((int)$ids[$i]);		
		}
		return $data;
	}	
	//------------------------------------------------------------------------------------------------
	function get_received_ticket_list($accountId,$start,$amount,$condition=null) //!!!
	{
		$ids = $this->get_received_list_of_ticket_id($accountId,$condition);
		$data = null;
		
		for($i=$start;$i<($start+$amount);$i++)
		{
			if($i<count($ids))			
				$data[] = $this->get_received_ticket_list_item((int)$ids[$i],$condition);		
		}
		
		return $data;
	}
	//------------------------------------------------------------------------------------------------
	function get_received_ticket_list_item($ticket_id,$monthCondition = null)
	{
		try		
		{
			$row 	= null;
			$sql  	= null;
			$result  = null;
			$mysqli = @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
			
			if($monthCondition == null)
			{
				$sql   = "SELECT t.id,t.parent_id,t.kategoria_id,t.status_id,t.priorytet_id,t.nadawca_id,t.odbiorca_id,t.temat,t.wiadomosc,t.data_wyslania,t.data_odebrania,
										IS_NOT_NULL(t.data_odebrania) AS odebrano,
									   (SELECT k.nazwa FROM kategorie k WHERE k.id = t.kategoria_id) AS kategoria,
									   (SELECT COUNT(t1.id) FROM tickety t1 WHERE t1.parent_id=t.id) AS liczba_odpowiedzi,
									   (SELECT s.nazwa FROM statusy s WHERE s.id = t.status_id) AS status,
									   (SELECT p.nazwa FROM priorytety p WHERE p.id = t.priorytet_id) AS priorytet,
									   (SELECT COUNT(zal0.id) FROM zalaczniki zal0 WHERE zal0.ticket_id = t.id) as zalaczniki,
									   GET_USER_NAME_BY_ID(t.nadawca_id) AS nadawca,									   
									   GET_USER_NAME_BY_ID(t.odbiorca_id) AS odbiorca,
									   GET_FULL_USER_NAME_BY_ID(t.nadawca_id) AS nadawca_pelne,
									   GET_FULL_USER_NAME_BY_ID(t.odbiorca_id) AS odbiorca_pelne
								FROM tickety AS t 
								WHERE t.id = $ticket_id";
				$mysqli->query("SET NAMES 'utf8'");				
				$result = $mysqli->query($sql);
						
			}
			else
			{
				$current_year  = (int)date('o');
				$current_month = (int)date('m');
				
				$yearCondition = $current_year; 
				if($current_month < $monthCondition)
					$yearCondition--; 
				
				$sql   = "SELECT t.id,t.parent_id,t.kategoria_id,t.status_id,t.priorytet_id,t.nadawca_id,t.odbiorca_id,t.temat,t.wiadomosc,t.data_wyslania,t.data_odebrania,
										IS_NOT_NULL(t.data_odebrania) AS odebrano,
									   (SELECT k.nazwa FROM kategorie k WHERE k.id = t.kategoria_id) AS kategoria,
									   (SELECT COUNT(t1.id) FROM tickety t1 WHERE t1.parent_id=t.id) AS liczba_odpowiedzi,
									   (SELECT s.nazwa FROM statusy s WHERE s.id = t.status_id) AS status,
									   (SELECT p.nazwa FROM priorytety p WHERE p.id = t.priorytet_id) AS priorytet,
									   (SELECT COUNT(zal0.id) FROM zalaczniki zal0 WHERE zal0.ticket_id = t.id) as zalaczniki,
									   GET_USER_NAME_BY_ID(t.nadawca_id) AS nadawca,
									   GET_USER_NAME_BY_ID(t.odbiorca_id) AS odbiorca
								FROM tickety AS t 
								WHERE t.id= $ticket_id AND MONTH(t.data_wyslania) = $monthCondition AND YEAR(t.data_wyslania) = $yearCondition";
				$mysqli->query("SET NAMES 'utf8'");				
				$result = $mysqli->query($sql);			
				
			}
			
			if($mysqli->error)
				throw new Exception($mysqli->error);	
				
			$row = $result->fetch_assoc();

            //JESLI TICKET MA PARENTA, TO POBIERZ STATUS, PRIORYTET Z OSTATNIEGO CHILDA
           if(empty($row['parent_id']) && ($row['liczba_odpowiedzi'] == 0))
               return $row;
           else
           {
                    $sql = "";
                    if(empty($row['parent_id']))
                        $sql = "SELECT t.status_id,t.priorytet_id,
                                       (SELECT s.nazwa FROM statusy s WHERE s.id = t.status_id) AS status,
                                       (SELECT p.nazwa FROM priorytety p WHERE p.id = t.priorytet_id) AS priorytet
                                FROM tickety AS t
                                WHERE t.parent_id = ".$row['id']."
                                ORDER BY t.id DESC
                                LIMIT 0,1";
                    else
                        $sql = "SELECT t.status_id,t.priorytet_id,data_wyslania,data_odebrania
                                       (SELECT s.nazwa FROM statusy s WHERE s.id = t.status_id) AS status,
                                       (SELECT p.nazwa FROM priorytety p WHERE p.id = t.priorytet_id) AS priorytet
                                FROM tickety AS t
                                WHERE t.parent_id = ".$row['parent_id']."
                                ORDER BY t.id DESC
                                LIMIT 0,1";

                    $result = $mysqli->query($sql);
                    if($result)
                    {
                        $child  = $result->fetch_assoc();

                        if(isset($child))
                        {
                            $row['status_id']      = $child['status_id'];
                            $row['priorytet_id']   = $child['priorytet_id'];
                            $row['status']         = $child['status'];
                            $row['priorytet']      = $child['priorytet'];
                            $row['data_wyslania']  = $child['data_wyslania'];
                            $row['data_odebrania'] = $child['data_odebrania'];
                        }
                        return $row;
                    }
           }
			
		}
		catch(Exception $e)
		{
			//echo 'Error'.$e->getMessage().'<br />';							
		}	
		return FALSE;		
	}	
	//------------------------------------------------------------------------------------------------
	public function get($ticketId)
	{
		try
		{
			$ticketId	= (int)$ticketId;	
			$mysqli 	= @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
			$sql = "SELECT t.id,t.parent_id,t.kategoria_id,t.status_id,t.priorytet_id,t.nadawca_id,t.odbiorca_id,t.temat,t.wiadomosc,t.data_wyslania,t.data_odebrania,
						   (SELECT k.nazwa FROM kategorie k WHERE k.id = t.kategoria_id) AS kategoria,
						   (SELECT s.nazwa FROM statusy s WHERE s.id = t.status_id) AS status,
						   (SELECT p.nazwa FROM priorytety p WHERE p.id = t.priorytet_id) AS priorytet,
						   (SELECT COUNT(zal0.id) FROM zalaczniki zal0 WHERE zal0.ticket_id = t.id) as zalaczniki,
						   GET_USER_NAME_BY_ID(t.nadawca_id) AS nadawca,
						   GET_USER_NAME_BY_ID(t.odbiorca_id) AS odbiorca
					FROM tickety AS t 
					WHERE t.id= $ticketId";
			$result 	= $mysqli->query($sql);	
			$row		=  $result->fetch_assoc();
			
			if(isset($row))
				return $row;
			else
				return null;	
		}
		catch(Exception $ex)
		{
			return FALSE;			
		}	
		return FALSE;	
	}
	//------------------------------------------------------------------------------------------------
	function get_tickets($account_id)
	{
		try
		{
			$data 		= null;
			$mysqli 	= @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);			
		
			$sql   = "SELECT t.id,t.parent_id,t.kategoria_id,t.status_id,t.priorytet_id,t.nadawca_id,t.odbiorca_id,t.temat,t.wiadomosc,t.data_wyslania,t.data_odebrania,
									IS_NOT_NULL(t.data_odebrania) AS odebrano,
								   (SELECT k.nazwa FROM kategorie k WHERE k.id = t.kategoria_id) AS kategoria,
								   (SELECT COUNT(t1.id) FROM tickety t1 WHERE t1.parent_id=t.id) AS liczba_odpowiedzi,
								   (SELECT s.nazwa FROM statusy s WHERE s.id = t.status_id) AS status,
								   (SELECT p.nazwa FROM priorytety p WHERE p.id = t.priorytet_id) AS priorytet,
								   (SELECT COUNT(zal0.id) FROM zalaczniki zal0 WHERE zal0.ticket_id = t.id) as zalaczniki,
								   GET_USER_NAME_BY_ID(t.nadawca_id) AS nadawca,
								   GET_USER_NAME_BY_ID(t.odbiorca_id) AS odbiorca,
								   GET_FULL_USER_NAME_BY_ID(t.nadawca_id) AS nadawca_pelne,
							       GET_FULL_USER_NAME_BY_ID(t.odbiorca_id) AS odbiorca_pelne								   
							FROM tickety AS t 
							WHERE (t.nadawca_id = $account_id OR t.odbiorca_id = $account_id) AND (t.parent_id IS NULL)
							ORDER BY data_wyslania DESC";
			$mysqli->query("SET NAMES 'utf8'");				
			$result = $mysqli->query($sql);
						
		
			while($row = $result->fetch_assoc())
			  $data[]  = $row;
			  	
			return $data;			
		}
		catch(Exception $ex)
		{
			return FALSE;			
		}
		return FALSE;
	}
	//------------------------------------------------------------------------------------------------
	function get_child_tickets_by($parent_id)
	{
		try
		{
			$data 		= null;
			$parent_id  = (int)$parent_id;
			$mysqli 	= @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
			$sql  = "SELECT t.id,t.parent_id,t.kategoria_id,t.status_id,t.priorytet_id,t.nadawca_id,t.odbiorca_id,t.temat,t.wiadomosc,t.data_wyslania,t.data_odebrania,
						   (SELECT k.nazwa FROM kategorie k WHERE k.id = t.kategoria_id) AS kategoria,
						   (SELECT s.nazwa FROM statusy s WHERE s.id = t.status_id) AS status,
						   (SELECT p.nazwa FROM priorytety p WHERE p.id = t.priorytet_id) AS priorytet,
						   (SELECT COUNT(zal0.id) FROM zalaczniki zal0 WHERE zal0.ticket_id = t.id) as zalaczniki,
						   GET_USER_NAME_BY_ID(t.nadawca_id) AS nadawca,
						   GET_USER_NAME_BY_ID(t.odbiorca_id) AS odbiorca
					FROM tickety AS t 
					WHERE t.parent_id= $parent_id
					ORDER BY data_wyslania ASC"; 	

			$result 	= $mysqli->query($sql);
				
			while($row = $result->fetch_assoc())
			  $data[]  = $row;
			  	
			return $data;
		}
		catch(Exception $ex)
		{
			return FALSE;			
		}
	}
	//------------------------------------------------------------------------------------------------
	function get_amount_of_sended_tickets($nadawca)
	{
		$data = null;
		$data = $this->get_sended_list_of_ticket_id($nadawca);
		return count($data);
	}
	//------------------------------------------------------------------------------------------------
	function get_amount_of_received_tickets($odbiorca,$condition)
	{
		$data = null;
		$data = $this->get_received_list_of_ticket_id($odbiorca,$condition);
		return count($data); 
	}	
	//------------------------------------------------------------------------------------------------
	function get_attachment_list($ticketId)
	{
		try
		{
			$data		= null;
			$ticketId	= (int)$ticketId;
			$mysqli 	= @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
			$sql   		= "SELECT * FROM zalaczniki WHERE ticket_id=$ticketId";
			
			$result 	= $mysqli->query($sql);
			$data       = array();
				
			while($row  =  $result->fetch_assoc())
			   $data[]  = $row;
			   
			return $data;			
		}
		catch(Exception $ex)
		{
			return FALSE;			
		}	
		return FALSE;
	}
	//------------------------------------------------------------------------------------------------
	function add($nadawca,$odbiorca,$kategoria,$status,$priorytet,$temat,$wiadomosc,$files,$parent_id=0)
	{
		
			try
			{
				$mysqli = @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
				
				// turn off auto-commit
				$sql1;
				
					$mysqli->autocommit(FALSE);
					if($parent_id == 0)
						$sql1 = "INSERT INTO tickety(nadawca_id,odbiorca_id,kategoria_id,status_id,priorytet_id,temat,wiadomosc,data_wyslania)
						         VALUES($nadawca,$odbiorca,$kategoria,$status,$priorytet,'$temat','$wiadomosc','".date('Y:m:d H:i:s')."')";
					else
						$sql1 = "INSERT INTO tickety(nadawca_id,parent_id,odbiorca_id,kategoria_id,status_id,priorytet_id,temat,wiadomosc,data_wyslania)
						         VALUES($nadawca,$parent_id,$odbiorca,$kategoria,$status,$priorytet,'$temat','$wiadomosc','".date('Y:m:d H:i:s')."')";
					
					$mysqli->query($sql1);
					if($mysqli->affected_rows!=1)
						throw new Exception('TICKET INSERT ERROR');
					
					$ticket_id = $mysqli->insert_id;

					if($files != null)
					{
						foreach($files as $key=>$value)
						{
							$sql2 = "INSERT INTO zalaczniki(ticket_id,sciezka) VALUES($ticket_id,'".$value['sciezka']."')";
							if(!$mysqli->query($sql2))
								throw new Exception('Insert zalacznki error.');							
						}
					}					
					
			}
			catch(Exception $e)
			{
				
				$mysqli->rollback();
				$mysqli->autocommit(TRUE);
				$mysqli->close();
				return FALSE;	
			}
		$mysqli->autocommit(TRUE);
		$mysqli->close();
		return TRUE;	
	}	
	//------------------------------------------------------------------------------------------------
	function get_account($account_id)
	{
		try
		{
			$data		= array();
			$mysqli 	= @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
			$sql        = 'SELECT * FROM konta WHERE id='.$account_id;
			$result 	= $mysqli->query($sql);	
			
			while($row =  $result->fetch_assoc())
			   $data[] = $row;
			
			if(isset($data[0]))
				return $data[0];
			else
				return $data;	   
				
		}	
		catch(Exception $ex)
		{
			return FALSE;			
		}	
		return FALSE;
	}
	//------------------------------------------------------------------------------------------------
	function get_status_list()
	{
		try
		{
			$data		= null;
			$mysqli 	= @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
			$sql        = 'SELECT * FROM statusy';
			$result 	= $mysqli->query($sql);	
			
			while($row =  $result->fetch_assoc())
			   $data[] = $row;
			   
			return $data;   
		}	
		catch(Exception $ex)
		{
			return FALSE;			
		}	
		return FALSE;
	}
	//------------------------------------------------------------------------------------------------
	function get_status_name_by_id($statusId)
	{
		try
		{
				$statusId 	= (int)$statusId;
				$mysqli 	= @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
				$sql  		= "SELECT nazwa FROM statusy WHERE id = $statusId";
				
				if($result 	= $mysqli->query($sql))
				{
					$row 		= $result->fetch_assoc();
					if(isset($row['nazwa']))
						return $row['nazwa'];
				}
				else
					return null;

		}
		catch(Exception $ex)
		{
			return FALSE;			
		}
		return TRUE;
	}
	//------------------------------------------------------------------------------------------------
	function get_category_list()
	{
		try
		{
			$data		= null;
			$mysqli 	= @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
			$sql        = 'SELECT * FROM kategorie';
			$result 	= $mysqli->query($sql);	
			
			while($row =  $result->fetch_assoc())
			   $data[] = $row;
			   
			return $data; 			
			
		}
		catch(Exception $ex)
		{
			return FALSE;			
		}	
		return FALSE;		
	}
	//------------------------------------------------------------------------------------------------
	function get_priority_list()
	{
		try
		{
			$data		= null;
			$mysqli 	= @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
			$sql        = 'SELECT * FROM priorytety';
			$result 	= $mysqli->query($sql);	
			
			while($row =  $result->fetch_assoc())
			   $data[] = $row;
			   
			return $data; 			
			
		}
		catch(Exception $ex)
		{
			return FALSE;			
		}	
		return FALSE;		
	}
	//------------------------------------------------------------------------------------------------
	//zwraca liste afiliantow
	function get_afiliant_list()
	{
		try
		{
			$mysqli 	= @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
			$sql        = $sql  = "SELECT DISTINCT a.id,a.imie,a.nazwisko, k.id as account_id FROM afilianci a JOIN konta k on a.id = k.origin_id WHERE k.origin_table='afilianci'";
			$result 	= $mysqli->query($sql);	
			
			while($row =  $result->fetch_assoc())	
				$data[] = $row;
				
			return $data;
		}
		catch(Exception $ex)
		{
			return FALSE;			
		}		
	}
	//------------------------------------------------------------------------------------------------
	function get_client_list()
	{
		try
		{
			$mysqli 	= @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
			$sql  = "SELECT DISTINCT u.id,u.name, k.id as account_id FROM user u JOIN konta k on u.id = k.origin_id WHERE k.origin_table='user'";
			$result 	= $mysqli->query($sql);	
			
			while($row =  $result->fetch_assoc())	
				$data[] = $row;
				
			return $data;
		}
		catch(Exception $ex)
		{
			return FALSE;			
		}	
	}
	//------------------------------------------------------------------------------------------------
	function get_employee_list()
	{
		try
		{
			$mysqli 	= @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
			$sql  = "SELECT DISTINCT a.id,a.name, k.id as account_id FROM admin a JOIN konta k on a.id = k.origin_id WHERE k.origin_table='admin'";
			$result 	= $mysqli->query($sql);	
			
			while($row =  $result->fetch_assoc())	
				$data[] = $row;
				
			return $data;
		}
		catch(Exception $ex)
		{
			return FALSE;			
		}
	}
	//------------------------------------------------------------------------------------------------
	function set_data_odebrania($ticket_id)
	{
		try
		{
			$ticket_id  = (int)$ticket_id;
			$mysqli 	= @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
			$sql        = 'UPDATE tickety SET data_odebrania=\''.date('Y:m:d H:i:s').'\' WHERE id='.$ticket_id;
			$result 	= $mysqli->query($sql);	
			
			if($mysqli->affected_rows)	
				return TRUE;	
			else
				return TRUE;	
		}
		catch(Exception $ex)
		{
			return FALSE;			
		}		
	}
	//------------------------------------------------------------------------------------------------
	public function get_user_email_by_account_id($account_id)
	{
		try
		{
			//get origin id, table
			$account_id = (int)$account_id;
			$data  		= null;
			$mysqli 	= $conn = @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
			$sql   		= "SELECT * FROM konta WHERE id = $account_id";
			$r 			= $mysqli->query($sql);	
			
			$row 		= $r->fetch_assoc();
			if(isset($row))
				$account = $row; 
			
			
			//switch and select correct email
			$sql2 = '';
			switch($account['origin_table'])
			{
				case 'user'			: $sql2='SELECT email FROM user_data WHERE user_id ='.$account['origin_id']; break;
				case 'afilianci'	: $sql2='SELECT email FROM afilianci WHERE id ='.$account['origin_id'];		 break;
				case 'admin'		: $sql2='SELECT email FROM admin WHERE id ='.$account['origin_id'];			 break;
				default				: return FALSE; 
			} 		
			
			//return data
			$result 	= $mysqli->query($sql2);	
			$row 		= $result->fetch_assoc();
			
			
			if(isset($row))
				return $row['email'];
			else
				return FALSE;	 	
		}
		catch(Exception $e)
		{
			return FALSE;			
		}
		return TRUE;
	}
	//------------------------------------------------------------------------------------------------
	function get_user_type_by_account_id($account_id)
	{
		try
		{
			$data 		= null;
			$account_id = (int)$account_id;
			
			$mysqli 	= $conn = @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
			$sql  		= 'SELECT origin_table FROM konta WHERE id='.$account_id;
			$result 	= $mysqli->query($sql);	
			
			$row 		= $result->fetch_assoc();
	
			if(isset($row))
			{
				switch($row['origin_table'])
				{
					case 'user'			 : $data = 'user'; 			break; 
					case 'afilianci'	 : $data = 'afiliant'; 		break;
					case 'admin'		 : $data = 'admin'; 		break;	
					default				 : return FALSE;			
				}
				return $data;
			}
			else
				return FALSE;
		}
		catch(Exception $ex)
		{
			return FALSE;			
		}
	}
	//------------------------------------------------------------------------------------------------
	function does_user_have_permision_to_ticket($ticket_id,$account_id)
	{
		try
		{
		
			$mysqli 	= @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
			$sql        = 'SELECT * FROM tickety WHERE (nadawca_id = ? OR odbiorca_id = ?) AND id = ?';
			$result 	= $mysqli->query($sql);	
			
			$data       = null;
			while($row =  $result->fetch_assoc())	
				$data[] = $row;
			if(isset($data))
			{
				if(count($data) == 1)
					return TRUE;				
			}	
		
		}
		catch(Exception $ex)
		{
			return FALSE;			
		}
		return FALSE;		
	}
	//------------------------------------------------------------------------------------------------
	//koduje stringa
	function encode($string)
	{
		return strtr(base64_encode(addslashes(gzcompress(serialize($string),9))), '+/=', '-_,');		
	}
	//------------------------------------------------------------------------------------------------
	//dekoduje strina
	function decode($string)
	{
		return unserialize(gzuncompress(stripslashes(base64_decode(strtr($string, '-_,', '+/=')))));		
	}
	//------------------------------------------------------------------------------------------------
	function get_ticket_owner($ticket_id)
	{
		try
		{
		
			$mysqli 	= @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
			$sql        = 'SELECT owner_id FROM ticket_owners WHERE ticket_id = '.$ticket_id;
			$result 	= $mysqli->query($sql);	
			
			$data      = null;
			while($row =  $result->fetch_assoc())		
				$data[] = $row;
				
			if(count($data) > 0)
			{
				$result = $data[0]['owner_id'];
				return $result;
			}
							
		}
		catch(Exception $ex)
		{
			return FALSE;			
		}
		return FALSE;		
	}
	//------------------------------------------------------------------------------------------------
	function get_ticket_owner_start_time($ticket_id)
	{
		try
		{
		
			$mysqli 	= @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
			$sql        = 'SELECT start_time FROM ticket_owners WHERE ticket_id = '.$ticket_id;
			$result 	= $mysqli->query($sql);	
			
			$row =  $result->fetch_assoc();
			if(isset($row))
			{
				$result = $row['start_time'];
				return $result;
			} 						
		
		}
		catch(Exception $ex)
		{
			return FALSE;			
		}
		return FALSE;
	}
	//------------------------------------------------------------------------------------------------
	function set_owner($account_id,$ticket_id)
	{
		try
		{
		
			$mysqli 	= @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
			$sql        = 'INSERT INTO ticket_owners(owner_id,ticket_id,start_time) VALUES('.$account_id.','.$ticket_id.',\''.date('Y:m:d H:i:s').'\')';
			$result 	= $mysqli->query($sql);	
			
			if($mysqli->affected_rows)		
				return TRUE;
		}
		catch(Exception $ex)
		{
			return FALSE;			
		}
		return FALSE;
	}
	//------------------------------------------------------------------------------------------------
	function update_ticket_owner($ticket_id)
	{
		try
		{
			$mysqli 	= @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
			$sql        = 'UPDATE ticket_owners SET start_time=\''.date('Y:m:d H:i:s').'\' WHERE ticket_id='.$ticket_id;
			$result 	= $mysqli->query($sql);	
			
			if($mysqli->affected_rows)
				return TRUE;		
		}
		catch(Exception $ex)
		{
			return FALSE;			
		}
		return FALSE;

	}
	//------------------------------------------------------------------------------------------------
	function get_minutes_to_end_session($ticket_id)
	{
		try
		{
			$start_time = $this->get_ticket_owner_start_time($ticket_id);
			
			$mysqli 	= @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
			$sql        = 'SELECT (60 - TIMESTAMPDIFF(MINUTE, \''.$start_time.'\', now())) AS differenece FROM ticket_owners WHERE ticket_id = '.$ticket_id;
			$result 	= $mysqli->query($sql);	
			
			$row =  $result->fetch_assoc();
			if(isset($row['differenece']))
				return $row['differenece'];
			else
				return 999;			
		
		}
		catch(Exception $ex)
		{
			return FALSE;			
		}
		return FALSE;		
	}
	//------------------------------------------------------------------------------------------------
	function clear_ticket_owners_session()
	{
		try
		{
			$mysqli 	= @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
			$sql        = 'DELETE FROM ticket_owners WHERE TIMESTAMPDIFF(MINUTE, start_time, now()) > 1';
			$result 	= $mysqli->query($sql);	
			
			if($mysqli->affected_rows)
				return TRUE;		
		
		}
		catch(Exception $ex)
		{
			return FALSE;			
		}
		return FALSE;
	}
	//------------------------------------------------------------------------------------------------
	public function delete_ticket_owner_session($ticket_id)
	{
		try
		{
			$mysqli 	= @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
			$sql        = 'DELETE FROM ticket_owners WHERE ticket_id='.$ticket_id;
			$result 	= $mysqli->query($sql);	
			
			if($mysqli->affected_rows)
				return TRUE;		
		
		}
		catch(Exception $ex)
		{
			return FALSE;			
		}
		return FALSE;		
	}
	//------------------------------------------------------------------------------------------------
	function get_email_alias($kategoria_id)
	{
		try
		{
		
			$mysqli 	= @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
			$sql 		= "SELECT * FROM kategorie WHERE id=".$kategoria_id;
			$result 	= $mysqli->query($sql);	
			
			$data       = null;

			$row 		=  $result->fetch_assoc();	
			if(!empty($row))
				return $row;
			else
				return FALSE;	
		
		}
		catch(Exception $ex)
		{
			return FALSE;			
		}					
	}
	//------------------------------------------------------------------------------------------------
    function editStatus($ticketId,$statusId)
    {
        
		try
		{
            
			$mysqli 	= @new mysqli(DB_HOST,DB_LOGIN,DB_PASSWORD,DB_NAME,DB_PORT);
			$sql        = 'UPDATE tickety SET status_id='.$statusId.' WHERE id='.$ticketId;

			$result 	= $mysqli->query($sql);

			if($mysqli->error)
				throw new Exception($mysqli->error);

			return TRUE;
		}
		catch(Exception $ex)
		{
			return FALSE;
		}
		return FALSE;
    }
    //------------------------------------------------------------------------------------------------
}
?>