Auto Assign Tickets based on source email.

brusacco

Well I was needing this on my site.

1st. Create DB

CREATE TABLE `clasipar-tickets`.`ost_ticket_auto_assign` (

`auto_assign_id` int(11) NOT NULL AUTO_INCREMENT,

`email` varchar(120) NOT NULL,

`staff_id` int(10) NOT NULL,

`created` datetime DEFAULT NULL,

`updated` datetime DEFAULT NULL,

PRIMARY KEY (`auto_assign_id`)

) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COMMENT='Tickets auto asignados por el Admin'

Its not perfect, but its a start(mod nos finish yet)

Then add a new class:

<?php

/*********************************************************************

class.assign.php

**********************************************************************/

class Assign{

var $id;

var $email;

var $created;

var $updated;

var $staff_id;

function Assign($email){

$this->load($email);

}

function create($email,$staff_id)

{

//We are ready son...hold on to the rails.

$sql= 'INSERT INTO '.TICKET_AUTO_ASSIGN_TABLE.' SET created=NOW() '.

',email='.db_input($email).

',staff_id='.db_input($staff_id);

db_query($sql);

}

function load($email) {

$sql =' SELECT * FROM '.TICKET_AUTO_ASSIGN_TABLE. ' WHERE email='.db_input($email);

if(($res=db_query($sql)) && db_num_rows($res)):

$row=db_fetch_array($res);

$this->id =$row;

$this->email =$row;

$this->created =$row;

$this->updated =$row;

$this->staff_id =$row;

$this->row=$row;

return true;

endif;

return false;

}

function getStaffId() {

return $this->staff_id;

}

This is just a basic "assign" class, Ill work more on this later.

Then in open.php after creating the ticket:

if(($ticket=Ticket:($_POST,$errors,SOURCE))){

Add this:

if(($auto=new Assign($ticket->getEmail())))

{

$ticket->setStaffId($auto->getStaffId());

}

Well this one will look on the AUTO_ASSIGN table to see if there is already a mail->staff assigment, if so it will assign the ticket to it.

Lots of work here to finish this one, but Im on it. Just publish these to get IDEAS and opinions.

Thanks.

brusacco

Well have to add things like:

1)When the Manager assign a ticket to a staff, ill have to create a new email->staff assigment

And soo on ... thanks.

brusacco

<?php

/*********************************************************************

class.assign.php

**********************************************************************/

class Assign{

var $id;

var $email;

var $created;

var $updated;

var $staff_id;

function Assign($email){

$this->load($email);

}

function create($staff_id,$email)

{

$sql= 'INSERT INTO '.TICKET_AUTO_ASSIGN_TABLE.' SET created=NOW() '.

',email='.db_input($email).

',staff_id='.db_input($staff_id);

return (db_query($sql) && db_affected_rows())?true;

}

function load($email) {

$sql =' SELECT * FROM '.TICKET_AUTO_ASSIGN_TABLE. ' WHERE email='.db_input($email);

if(($res=db_query($sql)) && db_num_rows($res)):

$row=db_fetch_array($res);

$this->id =$row;

$this->email =$row;

$this->created =$row;

$this->updated =$row;

$this->staff_id =$row;

$this->row=$row;

return true;

endif;

return false;

}

function getStaffId() {

return $this->staff_id;

}

function getId() {

return $this->id;

}

function setStaffId($staffId) {

$sql= 'UPDATE '.TICKET_AUTO_ASSIGN_TABLE.' SET staff_id='.db_input($staffId).' WHERE auto_assign_id='.db_input($this->getId());

return (db_query($sql) && db_affected_rows())?true;

}

?>

brusacco

Well this is a working version of the AutoAssign Class.

Database stay the same, added some indexes.

<?php

/*********************************************************************

class.assign.php

**********************************************************************/

class Assign{

var $id;

var $email;

var $created;

var $updated;

var $staff_id;

//--------------------------------------------------------------------------------------------------------------------------------------------------------------------

//

//--------------------------------------------------------------------------------------------------------------------------------------------------------------------

function Assign($email){

$this->load($email);

}

//--------------------------------------------------------------------------------------------------------------------------------------------------------------------

//

//--------------------------------------------------------------------------------------------------------------------------------------------------------------------

function create($staff_id,$email)

{

$sql= 'INSERT INTO '.TICKET_AUTO_ASSIGN_TABLE.' SET created=NOW() '.

', email='.db_input($email).

', staff_id='.db_input($staff_id);

return (db_query($sql) && ($id=db_insert_id()))?$id;

}

//--------------------------------------------------------------------------------------------------------------------------------------------------------------------

//

//--------------------------------------------------------------------------------------------------------------------------------------------------------------------

function load($email) {

$sql =' SELECT * FROM '.TICKET_AUTO_ASSIGN_TABLE. ' WHERE email='.db_input($email);

if(($res=db_query($sql)) && db_num_rows($res)):

$row=db_fetch_array($res);

$this->id =$row;

$this->email =$row;

$this->created =$row;

$this->updated =$row;

$this->staff_id =$row;

$this->row=$row;

return true;

endif;

return false;

}

		//--------------------------------------------------------------------------------------------------------------------------------------------------------------------
	//
	//--------------------------------------------------------------------------------------------------------------------------------------------------------------------
	function getStaffId() {
	        	return $this->staff_id;
        	
    	}
    	
    	
	//--------------------------------------------------------------------------------------------------------------------------------------------------------------------
	//
	//--------------------------------------------------------------------------------------------------------------------------------------------------------------------
	function getEmail() {
	        	return $this->email;
        	
    	}    	
    	
	//--------------------------------------------------------------------------------------------------------------------------------------------------------------------
	//
	//--------------------------------------------------------------------------------------------------------------------------------------------------------------------    	
	function getId() {
	        	return $this->id;
        	
    	}    	
    
    
    	//--------------------------------------------------------------------------------------------------------------------------------------------------------------------
    	//
    	//--------------------------------------------------------------------------------------------------------------------------------------------------------------------
	function setStaff($staffId) {
			$sql= 'UPDATE '.TICKET_AUTO_ASSIGN_TABLE.' SET staff_id='.db_input($staffId).' WHERE auto_assign_id='.db_input($this->getId());
      		return (db_query($sql)  && db_affected_rows())?true;
      		
    	}    	
    	
    	
    	//--------------------------------------------------------------------------------------------------------------------------------------------------------------------
    	//
    	//--------------------------------------------------------------------------------------------------------------------------------------------------------------------
	function exist($email) {
	        	return db_num_rows(db_query('SELECT auto_assign_id FROM '.TICKET_AUTO_ASSIGN_TABLE.' WHERE email='.db_input($email)))?true;
        
    	}    	
}

?>

brusacco

Modified version of setStaffId, in the Ticket Class:

//--------------------------------------------------------------------------------------------------------------------------------------------------------------------

//set staff ID...assign/unassign/release (staff id can be 0)

//--------------------------------------------------------------------------------------------------------------------------------------------------------------------

function setStaffId($staffId){

// mind

if(Assign:($this->getEmail()))

{

$assign = new Assign($this->getEmail());

$assign->setStaff($staffId);

}

else

{

Assign:($staffId,$this->getEmail());

}

$sql= 'UPDATE '.TICKET_TABLE.' SET staff_id='.db_input($staffId).' WHERE ticket_id='.db_input($this->getId());

return (db_query($sql) && db_affected_rows())?true;

}