<?php
class Ticket extends Model
{
	function Ticket()
	{
		parent::Model();
		$this->default = $this->load->database('default', TRUE);		
	}
	//------------------------------------------------------------------------------------------------
	function get_list($type,$accountId,$start,$amount)
	{
		$data = null;	
		$sql  = null;
		
		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= ? AND t.parent_id IS NULL) 
						ORDER BY t.data_wyslania DESC
						LIMIT ?,?";
			}
		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= ?  AND t.parent_id IS NULL
						ORDER BY t.data_wyslania DESC
						LIMIT ?,?";				
			}	
		
		$query = $this->default->query($sql,array($accountId,$start,$amount));
		foreach ($query->result() as $row)
		{
			$data[] = $row;			
		}
		
		$query->free_result();
		

		
		return $data;		
	}
	//------------------------------------------------------------------------------------------------
	function get_sended_list_of_ticket_id($accountId,$monthCondition=null)
	{
		$data = null;
		$sql  = null;
		$query = null;
		
		
		if($monthCondition == null)
		{
			$sql  = "(SELECT t0.id FROM tickety AS t0 WHERE t0.nadawca_id= ? AND t0.parent_id IS NULL )
	 				 UNION
					 (SELECT t0.id FROM tickety AS t0 WHERE t0.nadawca_id= ? AND t0.parent_id IS NOT NULL
	                 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,$accountId,$accountId));
		}
		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));			
		}
		
		foreach ($query->result() as $row)
		{
			$data[] = $row->id;			
		}
        
		$query->free_result();
		return $data;					 
	}	
	//------------------------------------------------------------------------------------------------
	function get_received_list_of_ticket_id($accountId,$monthCondition = null)
	{
		$data = null;
		$sql  = null;
		$query  = null;
		
		
			if($monthCondition == null)
			{
				$sql  = "(SELECT t0.id,t0.parent_id FROM tickety AS t0 WHERE t0.odbiorca_id= ? AND t0.parent_id IS NULL)
		 				 UNION
						 (SELECT t0.id,t0.parent_id FROM tickety AS t0 WHERE t0.odbiorca_id= ? AND t0.parent_id IS NOT NULL
		                 AND t0.parent_id NOT IN (SELECT t1.id FROM tickety AS t1 WHERE t1.odbiorca_id= ? AND t1.parent_id IS NULL)
						 GROUP BY t0.parent_id)
						 ORDER BY id DESC";
				$query = $this->default->query($sql,array($accountId,$accountId,$accountId));
			}else
			{
				
 			$current_year  = (int)date('o');
			$current_month = (int)date('m');
			
			$yearCondition = $current_year; 
			if($current_month < $monthCondition)
				$yearCondition--;

    			$sql  = "(SELECT t0.id,t0.parent_id FROM tickety AS t0 WHERE t0.odbiorca_id= ? AND t0.parent_id IS NULL AND MONTH(t0.data_wyslania) = ? AND YEAR(t0.data_wyslania) = ?)
		 				 UNION
						 (SELECT t0.id,t0.parent_id FROM tickety AS t0 WHERE t0.odbiorca_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.odbiorca_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));

			}
		 
		foreach ($query->result() as $row)
		{
			$data[]        = $row->id;
		}

		
		$query->free_result();
		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_ticket_list_item((int)$ids[$i],$condition);
		}
		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_ticket_list_item((int)$ids[$i],$condition);
		}
		
		return $data;
	}
	//------------------------------------------------------------------------------------------------
	function get_ticket_list_item($ticket_id,$monthCondition = null)
	{
		$row 	= null;
		$sql  	= null;
		$query  = null;
		
		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
							FROM tickety AS t 
							WHERE t.id= ?";
			$query = $this->default->query($sql,array($ticket_id));
					
		}
		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= ? AND MONTH(t.data_wyslania) = ? AND YEAR(t.data_wyslania) = ?";
			$query = $this->default->query($sql,array($ticket_id,$monthCondition,$yearCondition));			
			
		}
		$row = $query->result();
        //JESLI TICKET MA PARENTA, TO POBIERZ STATUS, PRIORYTET,DATA ODEBRANIA,DATA WYSŁANIA Z OSTATNIEGO CHILDA
		if(isset($row[0]))
        {
            if(empty($row[0]->parent_id) && ($row[0]->liczba_odpowiedzi == 0))
                return $row[0];
            else
            {
                $sql = "SELECT t.status_id,t.priorytet_id,data_odebrania,data_wyslania,
								IS_NOT_NULL(t.data_odebrania) AS odebrano,
                               (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 = ?
                        ORDER BY t.id DESC
                        LIMIT 0,1";
                if(empty($row[0]->parent_id))
                    $query = $this->default->query($sql,array($row[0]->id));
                else
                    $query = $this->default->query($sql,array($row[0]->parent_id));

                $child = $query->result();
                
                if(isset($child))
                {
                    $row[0]->status_id          = $child[0]->status_id;
                    $row[0]->priorytet_id       = $child[0]->priorytet_id;
                    $row[0]->status             = $child[0]->status;
                    $row[0]->priorytet          = $child[0]->priorytet;
                    $row[0]->data_odebrania     = $child[0]->data_odebrania;
                    $row[0]->data_wyslania      = $child[0]->data_wyslania;
					$row[0]->odebrano      		= $child[0]->odebrano;
                }
                return $row[0];
            }
        }
		else
			return null;	
	}
	//------------------------------------------------------------------------------------------------
	function get($ticketId)
	{
		$data 		= 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,
					   (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= ?"; 
			
		$query = $this->default->query($sql,array($ticketId));
		foreach ($query->result() as $row)
		{
			$data[] = $row;			
		}
		$query->free_result();
		
		
		if(isset($data[0]))
			return $data[0];
		else
			return null;					
	}
	//------------------------------------------------------------------------------------------------
	//Funkcja sprawdza czy ticket o danym id, lub tickety z nim związane(childs) byly odebrane przez odbiorce
	function was_ticket_thread_read($ticket_id,$odbiorca_id)
	{
		$result = true;
		$ticket = $this->get($ticket_id);
		if(!empty($ticket) and empty($ticket->data_odebrania))
		{
			$result = false;
		}
		
		$parent_id     = (empty($ticket->parent_id) ? $ticket->id : $ticket->parent_id);
		$child_tickets = $this->get_child_tickets($parent_id);
		if(!empty($child_tickets))
		{
			foreach($child_tickets as $k=>$v)
			{
				if(($v->odbiorca_id == $odbiorca_id) and (empty($v->data_odebrania)))
					$result = false;	
			}
		}
		return $result;
	}
	//------------------------------------------------------------------------------------------------
	function get_child_tickets($parent_id)
	{
		$data = 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,
					   (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= ?
				ORDER BY data_wyslania ASC"; 
			
		$query = $this->default->query($sql,array($parent_id));
		foreach ($query->result() as $row)
		{
			$data[] = $row;			
		}
		$query->free_result();
		
		return $data;	
	}
	//------------------------------------------------------------------------------------------------
	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)
	{
		$data  = null;
		$sql   = "SELECT * FROM zalaczniki WHERE ticket_id=?";
		$query = $this->default->query($sql,array($ticketId));
		 	
		foreach($query->result() as $row)
		{
			$data[] = $row;			
		}
		$query->free_result();
		return $data;
	}
	//------------------------------------------------------------------------------------------------
	function add($nadawca,$odbiorca,$kategoria,$status,$priorytet,$temat,$wiadomosc,$files,$parent_id=0)
	{
		
			try
			{
				$this->db->trans_start();	
				
				if($parent_id == 0)
				{
					//query1	
					$data = array(
				               'nadawca_id' => $nadawca,
				               'odbiorca_id' => $odbiorca,
				               'kategoria_id' => $kategoria,
				               'status_id' => $status,
				               'priorytet_id' => $priorytet,
				               'temat' => $temat,
				               'wiadomosc' => $wiadomosc,
							   'data_wyslania' => date('Y:m:d H:i:s'),
			   						   						   
	            	);
				}
				else
				{
					//query1	
					$data = array(
				               'nadawca_id' => $nadawca,
							   'parent_id' => $parent_id,
				               'odbiorca_id' => $odbiorca,
				               'kategoria_id' => $kategoria,
				               'status_id' => $status,
				               'priorytet_id' => $priorytet,
				               'temat' => $temat,
				               'wiadomosc' => $wiadomosc,
							   'data_wyslania' => date('Y:m:d H:i:s'),
			   						   						   
	            	);					
				}
					
				$this->default->set($data);
				$this->default->insert('tickety'); 
				
				$ticket_id = $this->default->insert_id();
				
				if($files != null)
				{
					foreach($files as $key=>$value)
					{
						$this->default->set('ticket_id',$ticket_id);
						$this->default->set('sciezka','/files/uploads/'.$value);
						$this->default->insert('zalaczniki');
					}
				}
				
				$this->db->trans_complete();
				if ($this->db->trans_status() === FALSE)
				{
					$this->db->trans_rollback();
				    return FALSE;
				} 			
				else
				{
					$this->db->trans_commit();
					return $ticket_id;			
				}				
			}
			catch(Exception $e)
			{
				$this->db->trans_rollback();
				return FALSE;
			}
	}
	//------------------------------------------------------------------------------------------------
	/*
	function set_data_odebrania($ticket_id)
	{
		$data = array(
		               'data_odebrania' => 'NOW()'
		             );
		$this->default->where('id', $id); 
		$this->default->update('tickety', $ticket_id);			
	}*/
	//------------------------------------------------------------------------------------------------
	function get_status_list()
	{
		$data = null;	
		$query = $this->default->get('statusy'); 
		foreach ($query->result() as $row)
		{
			$data[] = $row;			
		}
		$query->free_result();
		return $data;		
	}
	//------------------------------------------------------------------------------------------------
	function get_status_name_by_id($ticketId)
	{
		$data = null;
		$sql  = "SELECT nazwa FROM statusy WHERE id = ?";
			
		$query = $this->default->query($sql,array($ticketId)); 
		foreach ($query->result() as $row)
		{
			$data[] = $row;			
		}
		$query->free_result();
		
		if(isset($data[0]))
			return $data[0]->nazwa;
		else
			return null;			
	}
	//------------------------------------------------------------------------------------------------
	function get_category_list()
	{
		$data = null;	
		$query = $this->default->get('kategorie'); 
		foreach ($query->result() as $row)
		{
			$data[] = $row;			
		}
		$query->free_result();
		return $data;		
	}
	//------------------------------------------------------------------------------------------------
	function get_priority_list()
	{
		$data = null;	
		$query = $this->default->get('priorytety'); 
		foreach ($query->result() as $row)
		{
			$data[] = $row;			
		}
		$query->free_result();
		return $data;		
	}
	//------------------------------------------------------------------------------------------------
	//zwraca liste afiliantow
	function get_afiliant_list()
	{
		$data  = null;	
		$sql  = "SELECT DISTINCT a.id,a.nazwa,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'";
		 
		//$query = $this->default->get('afilianci');
		$query = $this->default->query($sql); 
		foreach ($query->result() as $row)
		{
			$data[] = $row;			
		}
		$query->free_result();
		return $data;		
		
	}
	//------------------------------------------------------------------------------------------------
	function get_client_list()
	{
		$data = null;	
		$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'";
		
		$query = $this->default->query($sql); 
		foreach ($query->result() as $row)
		{
			$data[] = $row;			
		}
		$query->free_result();
		return $data;		
		
	}
	//------------------------------------------------------------------------------------------------
	function get_employee_list($type=null)
	{
		$data = null;
        $sql  = '';
        if(is_null($type))
            $sql  = "SELECT DISTINCT a.id,a.name,a.email, k.id as account_id FROM admin a JOIN konta k on a.id = k.origin_id WHERE k.origin_table='admin'";
        else
            $sql  = "SELECT DISTINCT a.id,a.name,a.email, k.id as account_id FROM admin a JOIN konta k on a.id = k.origin_id WHERE a.type='".$type."' AND k.origin_table='admin'";
			
		$query = $this->default->query($sql); 
		foreach ($query->result() as $row)
		{
			$data[] = $row;			
		}
		$query->free_result();
		return $data;		
		
	}
	//------------------------------------------------------------------------------------------------
    function get_manager($manager_id)
	{
		$data = null;
		$sql  = "SELECT DISTINCT a.id,a.name,a.email, k.id as account_id FROM admin a JOIN konta k on a.id = k.origin_id WHERE k.origin_table='admin' AND a.id=".$manager_id;
			
		$query = $this->default->query($sql); 
		foreach ($query->result() as $row)
		{
			$data[] = $row;			
		}
		$query->free_result();
		return $data;		
		
	}
    //------------------------------------------------------------------------------------------------
	function set_data_odebrania($ticket_id)
	{
		$data = array('data_odebrania' => date('Y:m:d H:i:s'));		
		$this->default->where('id', $ticket_id);
		$this->default->update('tickety', $data);
	}
	//------------------------------------------------------------------------------------------------
	function get_user_email_by_account_id($account_id)
	{
		//get origin id, table
		$data  = null;
		$sql   = "SELECT * FROM konta WHERE id = ?";
		$query = $this->default->query($sql,array($account_id));
		
		$rows = $query->result();
		if(isset($rows[0]))
			$account = $rows[0]; 
		
		
		//switch and select correct email
		$sql2 = '';
		switch($account->origin_table)
		{
			case 'user'			: $sql2='SELECT email FROM user_data WHERE user_id =?'; break;
			case 'afilianci'	: $sql2='SELECT email FROM afilianci WHERE id =?'; break;
			case 'admin'		: $sql2='SELECT email FROM admin WHERE id= ?'; break;
			default				: return FALSE; 
		} 		
		
		//return data
		$query = $this->default->query($sql2,array($account->origin_id));
		$rows = $query->result();
		
		
		if(isset($rows[0]))
			return $rows[0]->email;
		else
			return FALSE;	 			
		
	}
	//------------------------------------------------------------------------------------------------
	function get_user_type_by_account_id($account_id)
	{
		$data 	= null;
		$sql  	= 'SELECT origin_table FROM konta WHERE id=?';
		$query 	= $this->default->query($sql,array($account_id));
		
		$rows = $query->result();

		if(isset($rows) && count($rows) == 1)
		{
			switch($rows[0]->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;
	}
	//------------------------------------------------------------------------------------------------
	function does_user_have_permision_to_ticket($ticket_id,$account_id)
	{
		$data  = null;
		$sql   = 'SELECT * FROM tickety WHERE (nadawca_id = ? OR odbiorca_id = ?) AND id = ?';
		$query = $this->default->query($sql,array($account_id,$account_id,$ticket_id));
		
		$rows = $query->result();
		if(isset($rows))
		{
			if(count($rows) == 1)
				return TRUE;			
		}
		
		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)
	{
		$data 	= null;
		$sql  	= 'SELECT owner_id FROM ticket_owners WHERE ticket_id = ?';
		$query  = $this->default->query($sql,array($ticket_id)); 
		
		$rows = $query->result();
			if(count($rows) > 0)
			{
				$result = $rows[0]->owner_id;
				$query->free_result(); 
				return $result;
			}
		$query->free_result();
		return null;
	}
	//------------------------------------------------------------------------------------------------
	function get_ticket_owner_start_time($ticket_id)
	{
		$data   = null;
		$sql    = 'SELECT start_time FROM ticket_owners WHERE ticket_id = ?';
		$query  = $this->default->query($sql,array($ticket_id));
		
		$rows = $query->result();
		if(isset($rows[0]))
			{
				$result = $rows[0]->start_time;
				$query->free_result();
				return $result;
			} 
		else
			return false;			
	}
	//------------------------------------------------------------------------------------------------
	function set_owner($account_id,$ticket_id)
	{
		$data = null;
		$data = array(
	               'owner_id' 	=> $account_id,
	               'ticket_id' 	=> $ticket_id,
				   'start_time' => date('Y:m:d H:i:s'),
    			);					
				$this->default->set($data);
		return  $this->default->insert('ticket_owners');
	}
	//------------------------------------------------------------------------------------------------
	function update_ticket_owner($ticket_id)
	{
		$data = null;
		$data = array(
				   'start_time' => date('Y:m:d H:i:s'),
    			);					
			   $this->default->where('ticket_id', $ticket_id);
		return $this->default->update('ticket_owners', $data);
	}
	//------------------------------------------------------------------------------------------------
	function get_minutes_to_end_session($ticket_id)
	{
		$start_time = $this->get_ticket_owner_start_time($ticket_id);
		
		$sql  	= 'SELECT (30 - TIMESTAMPDIFF(MINUTE, ?, now())) AS differenece FROM ticket_owners WHERE ticket_id = ?';
		$query  = $this->default->query($sql,array($start_time,$ticket_id));
			
		$result = $query->result();
		
		if(isset($result[0]))
			return $result[0]->differenece;
		else
			return false;		
	}
	//------------------------------------------------------------------------------------------------
	function clear_ticket_owners_session()
	{
		$data 	= null;
		$sql  	= 'DELETE FROM ticket_owners WHERE TIMESTAMPDIFF(MINUTE, start_time, now()) > 1';
		$query  = $this->default->query($sql);
	}
	//------------------------------------------------------------------------------------------------
	function delete_ticket_owner_session($ticket_id)
	{
		$this->default->delete('ticket_owners', array('ticket_id' => $ticket_id));  		
	}
	//------------------------------------------------------------------------------------------------
	function send_email($ticket_id,$ticket_status,$ticket_kategoria,$do,$user_type)
	{
		//$do 			- tablica maili do których zostanie wyslana wiadomosc 
		//$user_type	- nazwa okreslajaca nadawce [user | afiliant | admin]
	
		require_once 'files/swift-4.0.5/lib/swift_required.php';
		
		$this->config->load('global_settings');	
		$global_settings 		=  $this->config->item('global_settings');
				
		//filtr .. jesli ticket leci do admina to wyslij na alias 
		//         jesli odbiorca sa wszyscy admini to nie wysyłaj na alias
		
		//jak do == -1 to odbiorca sa wszyscy uzytkownicy z wybranej grupy
		
		if($user_type == 'admin' && $do != -1)
        {
            $to[] = $do;
			$to[] = $global_settings['alias_emails'][$ticket_kategoria][0];
            $do   = $to;
        }

		
		
		 $transport = Swift_SmtpTransport::newInstance($global_settings['smtp']['smtp_serwer'],$global_settings['smtp']['smtp_port'])
		  ->setUsername($global_settings['smtp']['smtp_user'])
		  ->setPassword($global_settings['smtp']['smtp_password']);
		
		$mailer = Swift_Mailer::newInstance($transport);
		
		
		$user_controller = null;
		switch($user_type)
		{
			case 'user'  		: $user_controller = 'admin'; break;
			case 'afiliant'		: $user_controller = 'afiliant'; break;
			case 'admin'		: $user_controller = 'superadmin'; break;		
		}
		
			//kodowanie parametrow
			$user_controller_encoded = $this->encode($user_controller);
			$ticket_id_encoded 		 = $this->encode($ticket_id);
		
		
		//Create a message
		$body =  'Wiadomość w systemie networds została '.substr($ticket_status,0,strlen($ticket_status)-1).'a.'.'<br/>'; 
		//$body .= 'Nadawca: '.'<br/>';
		
		if($user_controller != null)
			$body .= 'Wiadomość jest dostępna pod adresem: <br/><a href="'.base_url().'tickets/show/'.$ticket_id_encoded.'/'.$user_controller_encoded.'">'.base_url().'tickets/show/'.$ticket_id_encoded.'/'.$user_controller_encoded.'</a><br/><br/>';
			 
		$body .= 'Email został automatycznie wygenerowany przez system Networds.';
		
	
		$message = Swift_Message::newInstance('Wiadomość Networds: '.$ticket_id)
		  ->setFrom(array($global_settings['smtp']['smtp_user'] => 'Networds System'))
		  ->setTo($do)
		  ->setBody($body, 'text/html', 'utf-8');
		  ;
		
		$headers = $message->getHeaders();
		$headers->addTextHeader('Reply-To',$global_settings['smtp']['smtp_user']);
		     
		//Send the message
		$result = $mailer->send($message);
		return $result; 		
	}
	//------------------------------------------------------------------------------------------------
    function edit_status($ticket_id,$status_id)
    {
		$data = array('status_id' => $status_id);		
		$this->default->where('id', $ticket_id);
		return $this->default->update('tickety', $data);
    }
    //------------------------------------------------------------------------------------------------
    function get_info_about_unreaded_tickets($account_id)
    {
		$sql  	= 'SELECT COUNT(id) as liczba FROM tickety WHERE odbiorca_id = ? AND data_odebrania IS NULL
                   GROUP BY parent_id';
		$query  = $this->default->query($sql,array($account_id));

		$result = $query->result();

		if(!empty($result[0]))
			return "Liczba nieodebranych wiadomości: ".$result[0]->liczba."<br/>Zobacz <a href=\"".base_url()."tickets"."\"><b>tutaj</b></a>";
		else
			return 0;
    }
    //------------------------------------------------------------------------------------------------
}
?>