Create a gist now

Instantly share code, notes, and snippets.

@mkuron /.gitignore
Last active Jan 3, 2016

What would you like to do?
Migrate JIRA to osTicket
Zend
*.pem
*.pub
<?php
$html = true;
require_once "jira.inc.php";
require_once "ost.inc.php";
define('PUBKEY', file_get_contents("jira.pub"));
define('PRIVKEY', file_get_contents("jira.pem"));
$server = "https://www.physcip.uni-stuttgart.de/jira";
$_SERVER['HTTP_HOST'] = 'green.physcip.uni-stuttgart.de';
$_SERVER['REQUEST_URI'] = 'jira-test.php';
define('PROJECT', 'TECH');
define('MAILDOMAIN', 'physcip.uni-stuttgart.de');
define('MYSQL_PASS', 'xxxxxx');
/////////////////////////
if ($html)
{
$expand = 'renderedFields';
$fields = 'renderedFields';
}
else
{
$expand = '';
$fields = 'fields';
}
$mysqli = new mysqli('127.0.0.1','root',MYSQL_PASS,'osticket', 3306);
if ($mysqli->connect_errno) {
die("Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error . "\n");
}
$mysqli->autocommit(FALSE);
$mysqli->begin_transaction();
$mysqli->autocommit(FALSE);
$mysqli->query('ALTER TABLE ost_ticket_thread ADD COLUMN jira_id BIGINT NULL AFTER updated, ADD UNIQUE INDEX jira_id (jira_id ASC)');
$mysqli->query('ALTER TABLE ost_file ADD COLUMN jira_id BIGINT NULL AFTER created, ADD UNIQUE INDEX jira_id (jira_id ASC)');
$query = array('jql' => "project = " . PROJECT . " order by key", 'startAt' => '0', 'maxResults' => 1000, 'fields' => '', 'expand' => $expand, 'oauth_token' => '');
$url = $server . "/rest/api/2/search";
$json = jira_request($server, $url, $query, PUBKEY, PRIVKEY);
$json = json_decode($json);
function find_or_create_user($email, $name)
{
global $mysqli;
// find customer
$stmt = $mysqli->prepare('SELECT user_id FROM ost_user_email WHERE address=?;');
$stmt->bind_param("s", $email);
$stmt->execute();
$stmt->bind_result($user_id);
$stmt->fetch();
$stmt->close();
if ($user_id !== NULL)
return $user_id;
// create customer
$stmt = $mysqli->prepare('INSERT INTO ost_user (name,created,updated) VALUES (?,NOW(),NOW())');
$stmt->bind_param("s", $name);
$stmt->execute();
$user_id = $stmt->insert_id;
$stmt->close();
$stmt = $mysqli->prepare('INSERT INTO ost_user_email (user_id, address) VALUES (?,?)');
$stmt->bind_param("is", $user_id, $email);
$stmt->execute();
$email_id = $stmt->insert_id;
$stmt->close();
$stmt = $mysqli->prepare('UPDATE ost_user SET default_email_id=? WHERE id=?');
$stmt->bind_param("ii", $email_id, $user_id);
$stmt->execute();
$stmt->close();
return $user_id;
}
function find_staff($user)
{
global $mysqli;
$stmt = $mysqli->prepare('SELECT staff_id FROM ost_staff WHERE username=?');
$stmt->bind_param("s", $user);
$stmt->execute();
$stmt->bind_result($staff_id);
$stmt->fetch();
$stmt->close();
return $staff_id;
}
function find_or_create_ticket($key, $user_id, $status_id, $staff_id, $duedate, $created, $updated, $title, $priority, $priority_name)
{
global $mysqli;
// find ticket
$stmt = $mysqli->prepare('SELECT ticket_id FROM ost_ticket WHERE number=?');
$stmt->bind_param("s", $key);
$stmt->execute();
$stmt->bind_result($ticket_id);
$stmt->fetch();
$stmt->close();
if ($ticket_id !== NULL)
return $ticket_id;
$stmt = $mysqli->prepare('INSERT INTO ost_ticket (number,user_id,status_id,staff_id,source,duedate,created,updated,topic_id,dept_id) VALUES (?,?,?,?,"Other",?,?,?,1,1)') or die($mysqli->error);
$stmt->bind_param("siiisss", $key,$user_id,$status_id,$staff_id,$duedate,$created,$updated) or die($stmt->error);
$stmt->execute() or die($stmt->error);
$ticket_id = $stmt->insert_id;
$stmt->close();
$stmt = $mysqli->prepare('INSERT INTO ost_form_entry (form_id, object_id, object_type,created, updated) VALUES (2,?,"T",?,?)') or die($mysqli->error);
$stmt->bind_param("iss", $ticket_id, $created, $updated);
$stmt->execute() or die($stmt->error);
$entry_id = $stmt->insert_id;
$stmt->close();
$stmt = $mysqli->prepare('INSERT INTO ost_form_entry_values (entry_id,field_id,value) VALUES (?,20,?)') or die($mysqli->error);
$stmt->bind_param("is", $entry_id, $title);
$stmt->execute() or die($stmt->error);
$stmt->close();
$stmt = $mysqli->prepare('INSERT INTO ost_form_entry_values (entry_id,field_id,value,value_id) VALUES (?,22,?,?)') or die($mysqli->error);
$stmt->bind_param("isi", $entry_id, $priority_name, $priority);
$stmt->execute() or die($stmt->error);
$stmt->close();
return $ticket_id;
}
function create_comment_if_needed($ticket_id, $staff_id, $user_id, $authorName, $comment, $created, $updated, $type, $jira_id)
{
global $mysqli;
$stmt = $mysqli->prepare('SELECT id FROM ost_ticket_thread WHERE jira_id=?');
$stmt->bind_param("i", $jira_id);
$stmt->execute();
$stmt->bind_result($comment_id);
$stmt->fetch();
$stmt->close();
if ($comment_id !== NULL)
return NULL;
$stmt = $mysqli->prepare('INSERT INTO ost_ticket_thread (ticket_id,staff_id,user_id,poster,source,body,format,created,updated,thread_type,jira_id) VALUES (?,?,?,?,"Other",?,"html",?,?,?,?)') or die($mysqli->error);
$stmt->bind_param("iiisssssi", $ticket_id, $staff_id, $user_id, $authorName, $comment, $created, $updated, $type, $jira_id) or die($stmt->error);
$stmt->execute() or die($stmt->error);
$comment_id = $stmt->insert_id;
$stmt->close();
return $comment_id;
}
$issues = $json->{'issues'};
for ($i = 0; $i < count($issues); ++$i)
{
$issue = $issues[$i];
//print_r($issue);
// issues
$key = $issue->{'key'};
echo "$key\n";
$title = $issue->{'fields'}->{'summary'};
$type = $issue->{'fields'}->{'issuetype'}->{'name'};
$status = $issue->{'fields'}->{'status'}->{'name'};
switch ($status)
{
case "Resolved":
case "Closed":
$status_id = 3;
break;
case "Open":
$status_id = 1;
break;
default:
$status_id = 1;
}
$assignee = $issue->{'fields'}->{'assignee'}->{'name'};
$assigneeMail = $issue->{'fields'}->{'assignee'}->{'emailAddress'};
$assigneeName = $issue->{'fields'}->{'assignee'}->{'displayName'};
$resolution = $issue->{'fields'}->{'resolution'}->{'name'};
$reporter = $issue->{'fields'}->{'reporter'}->{'name'};
$reporterMail = $issue->{'fields'}->{'reporter'}->{'emailAddress'};
$reporterName = $issue->{'fields'}->{'reporter'}->{'displayName'};
$created = $issue->{'fields'}->{'created'};
$updated = $issue->{'fields'}->{'updated'};
$duedate = $issue->{'fields'}->{'duedate'};
$priority = $issue->{'fields'}->{'priority'}->{'name'};
switch ($priority)
{
case "Trivial":
case "Minor":
$priority_id = 1;
break;
case "Major":
$priority_id = 2;
break;
case "Critical":
$priority_id = 3;
break;
case "Blocker":
$priority_id = 4;
break;
default:
$priority_id = 2;
}
$user_id = find_or_create_user($reporterMail, $reporterName);
$staff_id = find_staff($assignee);
$ticket_id = find_or_create_ticket($key, $user_id, $status_id, $staff_id, $duedate, $created, $updated, $title, $priority_id, $priority);
$commentsAll = array();
$commentsAll[] = array(
'title' => $title,
'jira_id' => -1*$issue->id,
'comment' => $issue->{$fields}->{'description'},
'created' => $created,
'updated' => $created,
'author' => $reporter,
'authorMail' => $reporterMail,
'authorName' => $reporterName,
'type' => 'M',
);
$query = array('fields' => 'id', 'fields' => 'summary,comment,issuelinks,attachment,subtasks', 'startAt' => '0', 'maxResults' => 1000, 'expand' => $expand, 'oauth_token' => '');
$json = jira_request($server, $issue->{'self'}, $query, PUBKEY, PRIVKEY);
$json = json_decode($json);
$query = array('fields' => 'id', 'fields' => 'summary,comment,issuelinks,attachment,subtasks', 'startAt' => '0', 'maxResults' => 1000, 'expand' => '', 'oauth_token' => '');
$json2 = jira_request($server, $issue->{'self'}, $query, PUBKEY, PRIVKEY);
$json2 = json_decode($json2);
// comments
$comments = $json->{$fields}->{'comment'}->{'comments'};
$comments2 = $json2->{'fields'}->{'comment'}->{'comments'};
for ($j = 0; $j < count($comments); ++$j)
{
$comment = $comments[$j];
$comment2 = $comments2[$j];
$commentsAll[] = array(
'jira_id' => $comment->id,
'comment' => $comment->{'body'},
'created' => $comment2->{'created'},
'updated' => $comment2->{'updated'},
'author' => $comment->{'author'}->{'name'},
'authorMail' => $comment->{'author'}->{'emailAddress'},
'authorName' => $comment->{'author'}->{'displayName'},
'type' => 'R',
);
}
foreach ($commentsAll as &$c)
{
if (strpos($c['authorMail'], '@' . MAILDOMAIN) !== FALSE && $c['type'] != 'M')
{
$c['staff_id'] = find_staff($c['author']);
$c['user_id'] = 0;
}
else
{
$c['user_id'] = find_or_create_user($c['authorMail'], $c['authorName']);
$c['staff_id'] = 0;
}
$comment_id = create_comment_if_needed($ticket_id, $c['staff_id'], $c['user_id'], $c['authorName'], $c['comment'],$c['created'],$c['updated'],$c['type'],$c['jira_id']);
if ($comment_id === NULL) // comment already exists
continue;
$stmt = $mysqli->prepare('UPDATE ost_ticket_thread SET title=? WHERE id=?');
$stmt->bind_param("si", $c['title'], $comment_id);
$stmt->execute();
$stmt->close();
if ($c['author'] == $reporter)
{
$stmt = $mysqli->prepare('UPDATE ost_ticket SET lastmessage=? WHERE ticket_id=?');
$stmt->bind_param("si", $c['created'], $ticket_id);
$stmt->execute();
$stmt->close();
}
else
{
$stmt = $mysqli->prepare('UPDATE ost_ticket SET lastresponse=? WHERE ticket_id=?');
$stmt->bind_param("si", $c['created'], $ticket_id);
$stmt->execute();
$stmt->close();
}
}
unset($c);
//print_r($commentsAll);
// links
$links = $json->{'fields'}->{'issuelinks'};
for ($j = 0; $j < count($links); ++$j)
{
$link = $links[$j];
if (isset($link->{'outwardIssue'}) && isset($link->{'inwardIssue'}))
die('doubleward Issue found');
if (isset($link->{'outwardIssue'}))
{
$linkIssue = $link->{'outwardIssue'}->{'key'};
$linkText = $link->{'type'}->{'outward'};
$linkId = ($link->{'outwardIssue'}->{'id'})*1e2;
}
if (isset($link->{'inwardIssue'}))
{
$linkIssue = $link->{'inwardIssue'}->{'key'};
$linkText = $link->{'type'}->{'inward'};
$linkId = ($link->{'inwardIssue'}->{'id'})*1e4;
}
$body = $linkText . ' <a href="https://www.physcip.uni-stuttgart.de/jira/browse/' . $linkIssue . '">' . $linkIssue . '</a>';
$comment_id = create_comment_if_needed($ticket_id, $commentsAll[0]['staff_id'], $commentsAll[0]['user_id'], $commentsAll[0]['authorName'], $body, $commentsAll[0]['created'], $commentsAll[0]['created'], $commentsAll[0]['type'], $linkId);
}
// subtasks
$tasks = $json->{'fields'}->{'subtasks'};
for ($j = 0; $j < count($tasks); ++$j)
{
$task = $tasks[$j];
$linkIssue = $task->{'key'};
$linkText = 'Subtask';
$linkId = ($task->{'id'})*1e6;
$body = $linkText . ' <a href="https://www.physcip.uni-stuttgart.de/jira/browse/' . $linkIssue . '">' . $linkIssue . '</a>';
$comment_id = create_comment_if_needed($ticket_id, $commentsAll[0]['staff_id'], $commentsAll[0]['user_id'], $commentsAll[0]['authorName'], $body, $commentsAll[0]['created'], $commentsAll[0]['created'], $commentsAll[0]['type'], $linkId);
}
// attachments
$files = $json->{'fields'}->{'attachment'};
for ($j = 0; $j < count($files); ++$j)
{
$file = $files[$j];
$data = jira_request($server, $file->{'content'}, $query, PUBKEY, PRIVKEY);
$info = array(
'id' => $file->{'id'},
'name' => $file->{'filename'},
'mime' => $file->{'mimeType'},
'size' => $file->{'size'},
'date' => $file->{'created'},
'author' => $file->{'author'}->{'name'},
'authorMail' => $file->{'author'}->{'emailAddress'},
'authorName' => $file->{'author'}->{'displayName'},
'data' => $data,
);
$stmt = $mysqli->prepare('SELECT id FROM ost_file WHERE jira_id=?');
$stmt->bind_param("i", $info['id']);
$stmt->execute();
$stmt->bind_result($file_id);
$stmt->fetch();
$stmt->close();
if ($file_id !== NULL)
continue;
$stmt = $mysqli->prepare('SELECT id FROM ost_ticket_thread WHERE ticket_id=? AND thread_type="M" AND jira_id<0');
$stmt->bind_param("i", $ticket_id);
$stmt->execute();
$stmt->bind_result($comment_id);
$stmt->fetch();
$stmt->close();
list($key, $hash) = _getKeyAndHash($data);
$fspath = 'attachments/' . $key{0};
if (!file_exists('attachments'))
mkdir('attachments');
if (!file_exists($fspath))
mkdir($fspath);
file_put_contents($fspath . '/' . $key, $data);
$stmt = $mysqli->prepare('INSERT INTO ost_file (bk,type,size,`key`,signature,name,created,jira_id) VALUES ("F",?,?,?,?,?,?,?)') or die($mysqli->error);
$stmt->bind_param("sissssi", $info['mime'], $info['size'], $key, $hash, $info['name'], $info['date'], $info['id']) or die($stmt->error);
$stmt->execute();
$file_id = $stmt->insert_id;
$stmt->close();
$stmt = $mysqli->prepare('INSERT INTO ost_ticket_attachment (ticket_id,file_id,ref_id,created) VALUES (?,?,?,?)') or die($mysqli->error);
$stmt->bind_param("iiis", $ticket_id, $file_id, $comment_id, $info['date']) or die($stmt->error);
$stmt->execute();
$stmt->close();
}
}
echo "\n";
$mysqli->commit();
$mysqli->query('DROP TABLE ost_ticket__cdata');
$mysqli->query('DROP TABLE ost__search');
$mysqli->close();
?>
<?php
/*
openssl genrsa -out jira.pem 1024
openssl rsa -in jira.pem -pubout -out jira.pub
In JIRA:
Administration, Plugins, Application Links: add a new one.
Incoming Authentication: Paste jira.pub as Public Key. Set Consumer Key and Consumer Name to Dashboard. Allow 2-Legged OAuth, Execute as admin
*/
set_include_path(dirname(__FILE__) . ':' . get_include_path());
require_once 'Zend/Oauth.php';
require_once 'Zend/Oauth/Consumer.php';
require_once 'Zend/Crypt/Rsa/Key/Private.php';
require_once 'Zend/Crypt/Rsa/Key/Public.php';
function jira_request($server, $url, $query, $pubkey, $privkey)
{
$privkey = new Zend_Crypt_Rsa_Key_Private($privkey);
$pubkey = new Zend_Crypt_Rsa_Key_Public($pubkey);
$selfurl = isset($_SERVER['HTTPS']) ? 'https://' : 'http://';
$selfurl .= $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI'];
$oauth_config = array(
'consumerKey' => 'Dashboard',
'rsaPrivateKey' => $privkey,
'rsaPublicKey' => $pubkey,
'signatureMethod' => 'RSA-SHA1',
'siteUrl' => $server . '/plugins/servlet/oauth',
'callbackUrl' => $selfurl,
'requestScheme' => Zend_Oauth::REQUEST_SCHEME_QUERYSTRING,
);
$oauth = new Zend_Oauth_Consumer($oauth_config);
$oauth->setSignatureMethod('RSA-SHA1');
$oauth->setRsaPrivateKey($privkey);
$oauth->setRsaPublicKey($pubkey);
$token = new Zend_Oauth_Token_Access(); // 2-legged doesn't require a token but Zend_Oauth needs it!
$oauth->setToken($token);
$client = $token->getHttpClient($oauth_config, $url);
$client->setUri($url);
$client->setMethod(Zend_Http_Client::GET);
$client->setParameterGet($query);
// $client->setConfig(array('adapter' => 'Zend_Http_Client_Adapter_Curl'));
$client->setConfig(array('ssltransport' => 'tls'));
return $client->request()->getBody();
}
?>
<?php
$html = true;
require_once "jira.inc.php";
require_once 'ost.inc.php';
define('PUBKEY', file_get_contents("jira.pub"));
define('PRIVKEY', file_get_contents("jira.pem"));
$server = "http://green.physcip.uni-stuttgart.de:8080/jira";
$_SERVER['HTTP_HOST'] = 'green.physcip.uni-stuttgart.de';
$_SERVER['REQUEST_URI'] = 'jira-test.php';
define('MYSQL_PASS', 'xxxxxx');
$projectmap = array('KB' => 1, 'KBE' => 2);
/////////////////////////
if ($html)
{
$expand = 'renderedFields';
$fields = 'renderedFields';
}
else
{
$expand = '';
$fields = 'fields';
}
$mysqli = new mysqli('127.0.0.1','root',MYSQL_PASS,'osticket', 3306);
if ($mysqli->connect_errno) {
die("Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error . "\n");
}
$mysqli->autocommit(FALSE);
$mysqli->begin_transaction();
$mysqli->autocommit(FALSE);
$mysqli->query('ALTER TABLE ost_faq ADD COLUMN jira_id BIGINT NULL AFTER updated, ADD UNIQUE INDEX jira_id (jira_id ASC)');
foreach ($projectmap as $project => $cid)
{
$query = array('jql' => "project = " . $project . " order by key", 'startAt' => '0', 'maxResults' => 1000, 'fields' => '', 'expand' => $expand, 'oauth_token' => '');
$url = $server . "/rest/api/2/search";
$json = jira_request($server, $url, $query, PUBKEY, PRIVKEY);
$json = json_decode($json);
$issues = $json->{'issues'};
for ($i = 0; $i < count($issues); ++$i)
{
$issue = $issues[$i];
//print_r($issue);
// issues
$id = $issue->{'id'};
$key = $issue->{'key'};
echo "$key\n";
$question = $issue->{'fields'}->{'summary'};
$answer = $issue->{$fields}->{'description'};
$created = $issue->{'fields'}->{'created'};
$updated = $issue->{'fields'}->{'updated'};
// find question
$stmt = $mysqli->prepare('SELECT faq_id FROM ost_faq WHERE jira_id=?');
$stmt->bind_param("i", $id);
$stmt->execute();
$stmt->bind_result($faq_id);
$stmt->fetch();
$stmt->close();
if ($faq_id === NULL)
{
$stmt = $mysqli->prepare('INSERT INTO ost_faq (category_id,ispublished,question,answer,created,updated,jira_id) VALUES (?,1,?,?,?,?,?)') or die($mysqli->error);
$stmt->bind_param("issssi", $cid,$question,$answer,$created,$updated,$id) or die($stmt->error);
$stmt->execute() or die($stmt->error);
$faq_id = $stmt->insert_id;
$stmt->close();
}
// files
$query = array('fields' => 'id', 'fields' => 'summary,comment,issuelinks,attachment,subtasks', 'startAt' => '0', 'maxResults' => 1000, 'expand' => $expand, 'oauth_token' => '');
$json = jira_request($server, $issue->{'self'}, $query, PUBKEY, PRIVKEY);
$json = json_decode($json);
$files = $json->{'fields'}->{'attachment'};
for ($j = 0; $j < count($files); ++$j)
{
$file = $files[$j];
$data = jira_request($server, $file->{'content'}, $query, PUBKEY, PRIVKEY);
$info = array(
'id' => $file->{'id'},
'name' => $file->{'filename'},
'mime' => $file->{'mimeType'},
'size' => $file->{'size'},
'date' => $file->{'created'},
'author' => $file->{'author'}->{'name'},
'authorMail' => $file->{'author'}->{'emailAddress'},
'authorName' => $file->{'author'}->{'displayName'},
'data' => $data,
);
$stmt = $mysqli->prepare('SELECT id FROM ost_file WHERE jira_id=?');
$stmt->bind_param("i", $info['id']);
$stmt->execute();
$stmt->bind_result($file_id);
$stmt->fetch();
$stmt->close();
if ($file_id !== NULL)
continue;
list($key, $hash) = _getKeyAndHash($data);
$fspath = 'attachments/' . $key{0};
if (!file_exists('attachments'))
mkdir('attachments');
if (!file_exists($fspath))
mkdir($fspath);
file_put_contents($fspath . '/' . $key, $data);
$stmt = $mysqli->prepare('INSERT INTO ost_file (bk,type,size,`key`,signature,name,created,jira_id) VALUES ("F",?,?,?,?,?,?,?)') or die($mysqli->error);
$stmt->bind_param("sissssi", $info['mime'], $info['size'], $key, $hash, $info['name'], $info['date'],$info['id']) or die($stmt->error);
$stmt->execute();
$file_id = $stmt->insert_id;
$stmt->close();
$stmt = $mysqli->prepare('INSERT INTO ost_attachment (object_id,file_id,type) VALUES (?,?,"F")') or die($mysqli->error);
$stmt->bind_param("ii", $faq_id, $file_id) or die($stmt->error);
$stmt->execute();
$stmt->close();
}
}
}
echo "\n";
$mysqli->commit();
$mysqli->close();
?>
<?php
// source: https://github.com/osTicket/osTicket-1.8/blob/b1c845bf0591b1f5da593a55e462b07e5a4ee5de/include/class.file.php#L259
function _getKeyAndHash($data=false, $file=false)
{
if ($file) {
$sha1 = base64_encode(sha1_file($data, true));
$md5 = base64_encode(md5_file($data, true));
}
else {
$sha1 = base64_encode(sha1($data, true));
$md5 = base64_encode(md5($data, true));
}
// Use 5 chars from the microtime() prefix and 27 chars from the
// sha1 hash. This should make a sufficiently strong unique key for
// file content. In the event there is a sha1 collision for data, it
// should be unlikely that there will be a collision for the
// microtime hash coincidently. Remove =, change + and / to chars
// better suited for URLs and filesystem paths
$prefix = base64_encode(sha1(microtime(), true));
$key = str_replace(
array('=','+','/'),
array('','-','_'),
substr($prefix, 0, 5) . $sha1);
// The hash is a 32-char value where the first half is from the last
// 16 chars from the SHA1 hash and the last 16 chars are the last 16
// chars from the MD5 hash. This should provide for better
// resiliance against hash collisions and attacks against any one
// hash algorithm. Since we're using base64 encoding, with 6-bits
// per char, we should have a total hash strength of 192 bits.
$hash = str_replace(
array('=','+','/'),
array('','-','_'),
substr($sha1, 0, 16) . substr($md5, 0, 16));
return array($key, $hash);
}
?>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment