<?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.
//
// 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','eticket','TpD7TFaYCRH9vREm');
$osticketDB=mysql_connect('localhost','osticket','TVQd6pG8vBwA5aar',true);
mysql_select_db('eticket',$eticketDB);
mysql_select_db('osticket',$osticketDB);


//  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)) {
	$eticketCount++;
	$username=$row['name'];
	$email=$row['email'];
	$phone=$row['phone'];
	// 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="INSERT INTO ost_ticket (ticket_id,ticketID,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);
	}
	$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";
	// spin through the eticket messages and create threads
	$sql="SELECT * FROM ticket_messages WHERE ticket='$ticketID' ORDER BY `timestamp`";
	$rs2=mysql_query($sql,$eticketDB);
	$thread_type='N';
	$hightimestamp='0000-00-00';
	$timestamp='0000-00-00';
	while ($msgRow=mysql_fetch_array($rs2,MYSQL_ASSOC)) {
		$timestamp=$msgRow['timestamp'];
		$body=$msgRow['message'];
		$body=mysql_real_escape_string($body);
		$sql="INSERT INTO ost_ticket_thread (id,pid,ticket_id,staff_id,thread_type,poster,source,title,body,ip_address,created,updated)
		VALUES (NULL,0,$ticket_id,$staff_id,'$thread_type','','Other','','$body','127.0.0.1','$timestamp','0000-00-00')";
		$result=mysql_query($sql,$osticketDB);
		if (!$result) {
    		$message  = 'Invalid query: ' . mysql_error() . "\n";
    		$message .= 'Whole query: ' . $sql;
    		die($message);
		}
	}
	// spin through the eticket answers
	$sql="SELECT * FROM ticket_answers WHERE ticket='$ticketID' ORDER BY `timestamp`";
	$thread_type='R';
	$hightimestamp=$timestamp;
	$timestamp='0000-00-00';
	while ($msgRow=mysql_fetch_array($rs2,MYSQL_ASSOC)) {
		$timestamp=$msgRow['timestamp'];
		$hightimestamp= (strtotime($timestamp) > strtotime($timestamp)) ? $timestamp : $hightimestamp;  // max of the dates
		$rep=$msgRow['rep'];
		if (isset($repStaff[$rep])) $staff_id=$repStaff[$rep];
		else $staff_id=0;  // if not found use 0
		$body=$msgRow['message'];
		$body=mysql_real_escape_string($body);
		$sql="INSERT INTO ost_ticket_thread (id,pid,ticket_id,staff_id,thread_type,poster,source,title,body,ip_address,created,updated)
		VALUES (NULL,0,$ticket_id,$staff_id,'$thread_type','','Other','','$body','127.0.0.1','$timestamp','0000-00-00')";
		$result=mysql_query($sql,$osticketDB);
		if (!$result) {
    		$message  = 'Invalid query: ' . mysql_error() . "\n";
    		$message .= 'Whole query: ' . $sql;
    		die($message);
		}
	}
	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);


}
echo "$eticketCount tickets converted";

?>