<?php
// eticket->osticket
// Instructions:
//  1) In osTicket, define all reps that are defined in eTicket as staff.   Use the same userid.
//  2) Make sure all the categories defined in eTicket have an equivalent topic in osTicket
//  3) Change the connection information for the databases appropriately in this script.
//
//
// 1) En osTicket, definir todas las repeticiones que se definen en eTicket como personal. Utilice el mismo ID de usuario.
// 2) Asegúrese de que todas las categorías definidas en el boleto electrónico tienen un tema equivalente en osTicket
// 3) Cambie la información de conexión de las bases de datos adecuada en este script.
//
//
//
// Caveats:   Attachments are not transferred.
// This is tested with osTicket release 1.8 and eTicket 1.7.3
// Use this at your own risk

// open databases
$eticketDB=mysql_connect('localhost','root','test');
$osticketDB=mysql_connect('localhost','root','test',true);
//mysql_select_db('eticket',$eticketDB);//local
mysql_select_db('e_helpdesk',$eticketDB);//copia producción
mysql_select_db('osticket',$osticketDB);

$MigrateTickets = new MgrateTickets();

//  form_id for for issue summary
$form_id_issue=5;  // not sure this would change, but it could.

// create a staff_id (osticket) to rep_id (eticket) equivalence based on user names
$repStaff=array();   // set up array so that $repStaff[eticketrep]=osticket_staff
$sql="SELECT * FROM ticket_reps";
$rs=mysql_query($sql,$eticketDB);
while ($row=mysql_fetch_array($rs,MYSQL_ASSOC)) {
	$username=$row['username'];
	$eticketRep=$row['ID'];
	$sql="SELECT staff_id FROM ost_staff WHERE username='$username'";
	$rs2=mysql_query($sql,$osticketDB);
	if (mysql_num_rows($rs2)==0) {
		die("Unable to find $username in osticket staff with $sql");
	}
	$staffRow=mysql_fetch_array($rs2,MYSQL_ASSOC);
	$staff_id=$staffRow['staff_id'];
	$repStaff[$eticketRep]=$staff_id;
}
// create a category->help topic equivalence
$catTopic=array();
$sql="SELECT * FROM ticket_categories";
$rs=mysql_query($sql,$eticketDB);
while ($row=mysql_fetch_array($rs,MYSQL_ASSOC)) {
	$catname=$row['name'];
	$catID=$row['ID'];
	$sql="SELECT topic_id FROM ost_help_topic WHERE topic LIKE '$catname'";
	$rs2=mysql_query($sql,$osticketDB);
	if (mysql_num_rows($rs2)==0) {
		die("Unable to find $catname in osticket staff");
	}
	$topicRow=mysql_fetch_array($rs2,MYSQL_ASSOC);
	$topic_id=$topicRow['topic_id'];
	$catTopic[$catID]=$topic_id;
}
$eticketCount=0;
$sql="SELECT * FROM tickets";
$rs=mysql_query($sql,$eticketDB);
while ($row=mysql_fetch_array($rs,MYSQL_ASSOC)) {
	$subject = $row['subject'];
	$username=$row['name'];
	$email=$row['email'];
	$phone=$row['phone'];
	$priority_id =$row['priority'];
	// get osticket user number based on email
	$sql="SELECT * from ost_user_email WHERE address='$email'";
	$rs2=mysql_query($sql,$osticketDB);
	if (mysql_num_rows($rs2))  { // we found the guy
		$userRow=mysql_fetch_array($rs2,MYSQL_ASSOC);
		$user_id=$userRow['user_id'];
		$email_id=$userRow['id'];
	}
	else {  // let's add him
		$sql="INSERT INTO ost_user_email VALUES (NULL,0,'$email')";
		mysql_query($sql,$osticketDB);
		$email_id=mysql_insert_id();
		$username=mysql_real_escape_string($username);
		$sql="INSERT INTO ost_user  VALUES (NULL,$email_id,'$username',CURDATE(),CURDATE())";
		mysql_query($sql,$osticketDB);
		$user_id=mysql_insert_id();
		$sql="UPDATE ost_user_email SET user_id=$user_id WHERE id=$email_id";
		mysql_query($sql,$osticketDB);
	}
	// assignments
	$ticketID=$row['ID'];  // reuse etickets random number.
	if (isset($catTopic[$row['cat']])) 	$topic_id=$catTopic[$row['cat']];
	else $topic_id=0;  // if not found, use 0
	if (isset($repStaff[$row['rep']])) $staff_id=$repStaff[$row['rep']];
	else $staff_id=0;  // if not found use 0
	switch ($row['status']) {
		case 'new':
			$status='open';
			$isanswered=0;
			$closed='NULL';
			break;
		case 'closed':
			$status='closed';
			$isanswered=1;
			break;
		case 'awaitingcustomer':
			$status='open';
			$isanswered=1;
			$closed='NULL';
			break;
		case 'onhold':
			$status='open';
			$isanswered=1;
			$closed='NULL';
			break;
			case 'onhold':
		$status='custreplied';
			$status='open';
			$isanswered=1;
			$closed='NULL';
			break;
		default:
			;
	} // switch
	$created=$row['timestamp'];
	$subject=mysql_real_escape_string($row['subject']);

	$sql="select * from ost_ticket where number = $ticketID";
	$result=mysql_query($sql,$osticketDB);
	$arrResult = mysql_fetch_array($result,MYSQL_ASSOC);
 	if(!$arrResult){
		
		$sql="INSERT INTO ost_ticket (ticket_id,number,user_id,user_email_id,dept_id,sla_id,topic_id,staff_id,team_id,ip_address,status,
				source,isoverdue,isanswered,duedate,reopened,created,updated)
		    VALUES (NULL,$ticketID,$user_id,$email_id,2,1,$topic_id,$staff_id,0,'127.0.0.1','$status',
				'Other',0,$isanswered,NULL,NULL,'$created','0000-00-00')";
		$result=mysql_query($sql,$osticketDB);
		if (!$result) {
	    	$message  = 'Invalid query: ' . mysql_error() . "\n";
	    	$message .= 'Whole query: ' . $sql;
	    	die($message);
		}
		
		switch ($priority_id) {
			case '1':
				$priority = 'Low';
				break;
			case '2':
				$priority = 'Normal';
				break;
			case '3':
				$priority = 'High';
				break;
		}
		//add subjetc
		$ticket_id=mysql_insert_id($osticketDB);
		$sql="INSERT INTO ost_ticket__cdata(ticket_id,subject,priority,priority_id)
			  VALUES($ticket_id,'$subject','$priority',$priority_id)";
		$result=mysql_query($sql,$osticketDB);
		if (!$result) {
			$message  = 'Invalid query: ' . mysql_error() . "\n";
			$message .= 'Whole query: ' . $sql;
			die($message);
		}
		//--------------start collect all de thread
		
		$sql="select a.ID, a.ticket, a.message, a.headers, a.timestamp, b.email from ticket_messages a
		join tickets b on a.ticket =  b.ID where ticket =  $ticketID order by a.timestamp asc";
		$queryMessages = mysql_query($sql,$eticketDB);
		while($arrayMessages=mysql_fetch_array($queryMessages,MYSQL_ASSOC)){
			//consult to get user id
		
			$user = $MigrateTickets->datasuser($arrayMessages['email'],$osticketDB);
		
			$userId = $user["user_id"];
			$ticketId = $arrayMessages['ID'];
			$numberTicket = $arrayMessages['ticket'];
			$Message = $arrayMessages['message'];
			$body=mysql_real_escape_string($Message);
			$timestampMsg = $arrayMessages['timestamp'];
			$tIp = $MigrateTickets->getip($numberTicket,$eticketDB);
			//add mesajes
			$sql="INSERT INTO ost_ticket_thread (id,pid,ticket_id,staff_id,user_id,thread_type,poster,source,title,body,ip_address,created,updated)
			VALUES (0, 0, $ticket_id, 0, $userId, 'M', '','','','$body','$tIp','$timestampMsg','0000-00-00')";
			mysql_query($sql,$osticketDB);
		}
		
		$sql="select * from ticket_answers
		where ticket = $ticketID order by timestamp asc";
		$queryAnswers = mysql_query($sql,$eticketDB);
		while($arrysAnswers=mysql_fetch_array($queryAnswers,MYSQL_ASSOC)){
			$ticketId = $arrysAnswers['ID'];
			$numberTicket = $arrysAnswers['ticket'];
			$repId = $arrysAnswers['rep'];
			$Message = $arrysAnswers['message'];
			$body=mysql_real_escape_string($Message);                                                                                       
			$timestampMsg = $arrysAnswers['timestamp'];
			//
			$pid = $MigrateTickets->getpid($arrysAnswers['reference'],$eticketDB,$osticketDB);
			//add mesajes
			$sql="INSERT INTO ost_ticket_thread (id,pid,ticket_id,staff_id,user_id,thread_type,poster,source,title,body,ip_address,created,updated)
		     VALUES (0, $pid, $ticket_id, $repId, 0, 'R', '','','','$body','127.0.0.1','$timestampMsg','0000-00-00')";
			mysql_query($sql,$osticketDB);
		}
		
		//-------------------end  collect all de thread
		
		//$ticket_id=mysql_insert_id($osticketDB);
		// add issue summary
		mysql_query("INSERT INTO ost_form_entry VALUES (NULL,2,$ticket_id,'T',1,'$created','$created')"); // get form entry
		$form_entry=mysql_insert_id($osticketDB);
		mysql_query("INSERT INTO ost_form_entry_values VALUES ($form_entry,$form_id_issue,'$subject',NULL)");
	
		echo "<br/> ticket $ticket_id for $ticketID created";
		
		if ($status=='closed') {
			if (empty($timestamp)) {
				$result=mysql_query("UPDATE ost_ticket SET closed = '$created' WHERE ticket_id='$ticket_id'",$osticketDB);
				if (!$result) {
	    			$message  = 'Invalid query: ' . mysql_error() . "\n";
	    			$message .= 'Whole query: ' . $sql;
	    			die($message);
				}
			}
			else {
				$result=mysql_query("UPDATE ost_ticket SET closed = '$timestamp' WHERE ticket_id='$ticket_id'",$osticketDB);
				if (!$result) {
	    			$message  = 'Invalid query: ' . mysql_error() . "\n";
	    			$message .= 'Whole query: ' . $sql;
	    			die($message);
				}
	
			}
		}
		if (!empty($timestamp) && $thread_type=='R') mysql_query("UPDATE ost_titcket SET lastresponse = '$timestamp',updated='$timestamp' WHERE ticket_id='$ticket_id'",$osticketDB);
		$eticketCount++;
		
	}
}
//$ressArtt = $MigrateTickets->migrateAttachments($eticketDB,$osticketDB);
echo "<br/><br/> $eticketCount tickets converted";


class MgrateTickets{
	/**
	 * Return data fron an user whit his email registered.
	 */
	public function datasuser($mail,$osticketDB){
		$sql="select * from ost_user_email where address ='$mail'";
		$query = mysql_query($sql,$osticketDB);
		$resDatos=mysql_fetch_array($query,MYSQL_ASSOC);
		return $resDatos;
		
	}
	/**
	 * Return the pid from a answer before to insert.
	 */
	public function getpid($ticketId, $eticketDB,$osticketDB){
		//locate the reference of this answer from ticket_messages
		$result=mysql_query("select * from ticket_messages where ID = $ticketId",$eticketDB);
		$arrysAnswer=mysql_fetch_array($result,MYSQL_ASSOC);
		$created = $arrysAnswer['timestamp'];
		//locate the ID parent 
		$result=mysql_query("select id from ost_ticket_thread where created = '$created'",$osticketDB);
		$pid=mysql_fetch_array($result,MYSQL_ASSOC);
		return $pid['id'];
	}
	
	public function getip($numberTicket,$eticketDB){
		$result=mysql_query("select * from tickets where ID = $numberTicket",$eticketDB);
		$arrysAnswer=mysql_fetch_array($result,MYSQL_ASSOC);
		return $tIp = $arrysAnswer['ip'];
	}
	
	public function migrateAttachments($eticketDB,$osticketDB){
		$resEtickets = mysql_query("select * from ticket_attachments",$eticketDB);
		while($arryresAttachs=mysql_fetch_array($resEtickets,MYSQL_ASSOC)){
			$ticketNumber = $arryresAttachs['ticket'];
			$refTicket = $arryresAttachs['ref'];
			$fileName = $arryresAttachs['filename'];
			$type = $arryresAttachs['type'];
			
			if($type == 'q'){
				
			}
			$resAtt=mysql_query("select * from eticket.ticket_attachments where ticket = $ticketNumber",$eticketDB);
			$arryresAtt=mysql_fetch_array($resAtt,MYSQL_ASSOC);
			
			$resMesgs=mysql_query("select * from ticket_messages where ticket = $ticketNumber",$eticketDB);
			$arryresMesgs=mysql_fetch_array($resMesgs,MYSQL_ASSOC);
			
			$resAns=mysql_query("select * from ticket_answers where ticket =  $ticketNumber",$eticketDB);
			$arryresAns=mysql_fetch_array($resAns,MYSQL_ASSOC);	
		}
		return true;
	}
}
?>