xpath("/account/firm|/account/clients/client");
for ($i = 0; $i < sizeof($company); $i++) {
$out = "INSERT INTO ${dbprefix}companies (client_of_id, name, homepage, address, address2, city, state, zipcode, country, phone_number, fax_number, timezone, created_on, created_by_id, updated_on, updated_by_id) VALUES (";
if ($i == 0) {
$out .= "0,";
} else {
$out .= "1,";
}
$out .= sqlT($company[$i]->{'name'}[0]) . ", ";
$out .= sqlT($company[$i]->{'web-address'}) . ", ";
$out .= sqlT($company[$i]->{'address-one'}) . ", ";
$out .= sqlT($company[$i]->{'address-two'}) . ", ";
$out .= sqlT($company[$i]->{'city'}) . ", ";
$out .= sqlT($company[$i]->{'state'}) . ", ";
$out .= sqlT($company[$i]->{'zip'}) . ", ";
$out .= sqlT($company[$i]->{'country'}) . ", ";
$out .= sqlT($company[$i]->{'phone-number-office'}) . ", ";
$out .= sqlT($company[$i]->{'phone-number-fax'}) . ", ";
$out .= lookupTZ($company[$i]->{'time-zone-id'}) . ", ";
$out .= sqlT($date_created_on) . ", ";
$out .= "1, ";
$out .= sqlT($date_created_on) . ", ";
if ($i == 0) {
$out .= "0";
} else {
$out .= "1";
}
$out .= ");\n";
$sql[] = $out;
$companies[(integer)$company[$i]->{'id'}] = $i + 1;
$companytz[(integer)$company[$i]->{'id'}] = lookupTZ($company[$i]->{'time-zone-id'});
}
echo 'Usernames and Passwords
';
$sql[] = "DELETE FROM ${dbprefix}users;\n";
$sql[] = "ALTER TABLE ${dbprefix}users AUTO_INCREMENT = 1;\n";
$users = $bc->xpath("/account/firm/people/person|/account/clients/client/people/person");
for ($i = 0; $i < sizeof($users); $i++) {
$out = "INSERT INTO ${dbprefix}users (company_id, username, email, token, salt, twister, display_name, title, office_number, fax_number, mobile_number, home_number, timezone, created_on, created_by_id, updated_on, last_activity, is_admin, auto_assign) VALUES (";
$out .= $companies[(integer)$users[$i]->{'client-id'}] . ", ";
$indomain = strpos($users[$i]->{'email-address'}, $user_domain);
if ($indomain != '') {
$username = substr($users[$i]->{'email-address'}, 0, $indomain);
} else {
$username = $users[$i]->{'email-address'};
}
if (array_search($username, $usernames)) {
$username = $username . '_' . $users[$i]->{'id'};
$out .= sqlT($username) . ", ";
$email = $users[$i]->{'email-address'} . '_' . $users[$i]->{'id'};
$out .= sqlT($email) . ", ";
echo 'Duplicate user "' . $users[$i]->{'id'} . '" created with username as "' . $username . '" and email address as "' . $email . '"
';
} else {
$out .= sqlT($username) . ", ";
$out .= sqlT($users[$i]->{'email-address'}) . ", ";
}
if ($i == 0) {
echo "Admin user is: $username
";
echo "Admin pass is: admin
";
$out .= sqlT($admin_token) . ", ";
$out .= sqlT($admin_salt) . ", ";
$out .= "'', ";
} else {
$pass = substr(sha1(uniqid(rand(), true)), rand(0, 25), 8);
echo "$username:$pass
";
$salt = substr(sha1(uniqid(rand(), true)), rand(0, 25), 13);
$token = sha1($salt . $pass);
$out .= sqlT($token) . ", ";
$out .= sqlT($salt) . ", ";
$out .= "'', ";
}
$out .= sqlT($users[$i]->{'name'}) . ", ";
$out .= sqlT($users[$i]->{'title'}) . ", ";
$phone = $users[$i]->{'phone-number-office'};
if ($users[$i]->{'phone-number-office'} != '') {
$phone .= " x" . $users[$i]->{'phone-number-office-ext'};
}
$out .= sqlT($phone) . ", ";
$out .= sqlT($users[$i]->{'phone-number-fax'}) . ", ";
$out .= sqlT($users[$i]->{'phone-number-mobile'}) . ", ";
$out .= sqlT($users[$i]->{'phone-number-home'}) . ", ";
$out .= $companytz[(integer)$users[$i]->{'client-id'}] . ", ";
$out .= sqlD($users[$i]->{'created-at'}) . ", ";
$out .= "0, ";
$out .= sqlD($users[$i]->{'updated-at'}) . ", ";
$out .= sqlT(date('Y-m-d H:i:s')) . ", ";
if ($users[$i]->{'administrator'}) {
$out .= $users[$i]->{'administrator'} . ", ";
} else {
$out .= "false, ";
}
$out .= $users[$i]->{'has-access-to-new-projects'};
$out .= ");\n";
$sql[] = $out;
$people[(integer)$users[$i]->{'id'}] = $i + 1;
$people_company[(integer)$users[$i]->{'id'}] = $companies[(integer)$users[$i]->{'client-id'}];
$usernames[sizeof($usernames)] = $username;
}
$sql[] = "DELETE FROM ${dbprefix}user_im_values;\n";
for ($i = 0; $i < sizeof($users); $i++) {
if ($users[$i]->{'im-handle'} != '') {
$out = "INSERT INTO ${dbprefix}user_im_values (user_id, im_type_id, value, is_default) VALUES (";
$out .= $people[(integer)$users[$i]->{'id'}] . ", ";
$out .= lookupIM($users[$i]->{'im-service'}) . ", ";
$out .= sqlT($users[$i]->{'im-handle'}) . ", ";
$out .= "1";
$out .= ");\n";
$sql[] = $out;
}
}
$sql[] = "DELETE FROM ${dbprefix}projects;\n";
$sql[] = "ALTER TABLE ${dbprefix}projects AUTO_INCREMENT = 1;\n";
$sql[] = "DELETE FROM ${dbprefix}project_companies;\n";
$sql[] = "DELETE FROM ${dbprefix}project_users;\n";
$project = $bc->xpath("/account/projects/project");
for ($i = 0; $i < sizeof($project); $i++) {
$out = "INSERT INTO ${dbprefix}projects (name, completed_on, completed_by_id, created_on, created_by_id, updated_on, updated_by_id) VALUES (";
$out .= sqlT($project[$i]->{'name'}[0]) . ", ";
if ($project[$i]->{'status'} == 'archived') {
$out .= sqlD($project[$i]->{'last-changed-on'}) . ", ";
$out .= "1,";
} else {
$out .= "DEFAULT, ";
$out .= "0, ";
}
$out .= sqlT($project[$i]->{'created-on'}) . ", ";
$out .= "1, ";
$out .= sqlD($project[$i]->{'last-changed-on'}) . ", ";
$out .= "1";
$out .= ");\n";
$sql[] = $out;
$projects[(integer)$project[$i]->{'id'}] = $i + 1;
}
$sql[] = "DELETE FROM ${dbprefix}project_companies;\n";
for ($i = 0; $i < sizeof($project); $i++) {
$out = "INSERT INTO ${dbprefix}project_companies (project_id, company_id) VALUES (";
$out .= $i + 1 . ", ";
$out .= $companies[(integer)$project[$i]->{'company'}->{'id'}];
$out .= ");\n";
$sql[] = $out;
}
$sql[] = "DELETE FROM ${dbprefix}project_users;\n";
for ($i = 0; $i < sizeof($project); $i++) {
$project_person = $project[$i]->xpath("participants/person");
for ($j = 0; $j < sizeof($project_person); $j++) {
$out = "INSERT INTO ${dbprefix}project_users (project_id, user_id) VALUES (";
$out .= $i + 1 . ", ";
$out .= $people[(integer)$project_person[$j]];
$out .= ");\n";
$sql[] = $out;
}
}
$sql[] = "DELETE FROM ${dbprefix}project_milestones;\n";
$sql[] = "ALTER TABLE ${dbprefix}project_milestones AUTO_INCREMENT = 1;\n";
$project_milestone = $bc->xpath("/account/projects/project/milestones/milestone");
for ($i = 0; $i < sizeof($project_milestone); $i++) {
$out = "INSERT INTO ${dbprefix}project_milestones (project_id, name, due_date, assigned_to_user_id, completed_on, completed_by_id, created_on, created_by_id) VALUES (";
$out .= $projects[(integer)$project_milestone[$i]->{'project-id'}] . ", ";
$out .= sqlT($project_milestone[$i]->{'title'}) . ", ";
$out .= sqlD($project_milestone[$i]->{'deadline'}) . ", ";
if ($project_milestone[$i]->{'responsible-party-type'} == 'Person') {
$out .= $people[(integer)$project_milestone[$i]->{'responsible-party-id'}] . ", ";
} else if ($project_milestone[$i]->{'responsible-party-type'} == 'Company') {
$out .= $companies[(integer)$project_milestone[$i]->{'responsible-party-id'}] . ", ";
} else {
$out .= "DEFAULT, ";
}
if ($project_milestone[$i]->{'completed'} == 'true') {
$out .= sqlD($project_milestone[$i]->{'completed-on'}) . ", ";
$out .= $people[(integer)$project_milestone[$i]->{'completer-id'}] . ", ";
} else {
$out .= "DEFAULT, ";
$out .= "DEFAULT, ";
}
$out .= sqlD($project_milestone[$i]->{'created-on'}) . ", ";
$out .= $people[(integer)$project_milestone[$i]->{'creator-id'}];
$out .= ");\n";
$sql[] = $out;
$milestones[(integer)$project_milestone[$i]->{'id'}] = $i + 1;
}
$sql[] = "DELETE FROM ${dbprefix}project_task_lists;\n";
$sql[] = "ALTER TABLE ${dbprefix}project_task_lists AUTO_INCREMENT = 1;\n";
$project_todo = $bc->xpath("/account/projects/project/todo-lists/todo-list");
for ($i = 0; $i < sizeof($project_todo); $i++) {
$out = "INSERT INTO ${dbprefix}project_task_lists (milestone_id, project_id, name, completed_on, completed_by_id) VALUES (";
if ((integer)$project_todo[$i]->{'milestone-id'} === 0) {
$out .= "DEFAULT, ";
} elseif (array_key_exists((integer)$project_todo[$i]->{'milestone-id'}, $milestones)) {
$out .= $milestones[(integer)$project_todo[$i]->{'milestone-id'}] . ", ";
} else {
$out .= "DEFAULT, ";
}
$out .= $projects[(integer)$project_todo[$i]->{'project-id'}] . ", ";
$out .= sqlT($project_todo[$i]->{'name'}[0]) . ", ";
if ((integer)$project_todo[$i]->{'uncompleted-count'} == 0) {
$completed = $project_todo[$i]->xpath("todo-items/todo-item[completed-at|completer-id]");
$completed_at = '';
for ($j = 0; $j < sizeof($completed); $j++) {
if ($completed_at <= $completed[$j]->{'completed-at'}) {
$completed_at = $completed[$j]->{'completed-at'};
$completer_id = (integer)$completed[$j]->{'completer-id'};
}
}
$out .= sqlD($completed_at) . ", ";
$out .= $people[$completer_id];
} else {
$out .= "DEFAULT, ";
$out .= "DEFAULT";
}
$out .= ");\n";
$sql[] = $out;
$tasklists[(integer)$project_todo[$i]->{'id'}] = $i + 1;
}
$sql[] = "DELETE FROM ${dbprefix}project_tasks;\n";
$sql[] = "ALTER TABLE ${dbprefix}project_tasks AUTO_INCREMENT = 1;\n";
$project_todoitem = $bc->xpath("/account/projects/project/todo-lists/todo-list/todo-items/todo-item");
for ($i = 0; $i < sizeof($project_todoitem); $i++) {
$out = "INSERT INTO ${dbprefix}project_tasks (task_list_id, text, assigned_to_company_id, assigned_to_user_id, completed_on, completed_by_id, created_on, created_by_id, `order`) VALUES (";
$out .= $tasklists[(integer)$project_todoitem[$i]->{'todo-list-id'}] . ", ";
$out .= sqlT($project_todoitem[$i]->{'content'}) . ", ";
if ($project_todoitem[$i]->{'responsible-party-type'} == 'Person') {
$out .= $people_company[(integer)$project_todoitem[$i]->{'responsible-party-id'}] . ", ";
$out .= $people[(integer)$project_todoitem[$i]->{'responsible-party-id'}] . ", ";
} else if ($project_todoitem[$i]->{'responsible-party-type'} == 'Company') {
$out .= $companies[(integer)$project_todoitem[$i]->{'responsible-party-id'}] . ", ";
$out .= "0, ";
} else {
$out .= "DEFAULT, ";
$out .= "DEFAULT, ";
}
if ($project_todoitem[$i]->{'completed'} == 'true') {
$out .= sqlD($project_todoitem[$i]->{'completed-at'}) . ", ";
$out .= $people[(integer)$project_todoitem[$i]->{'completer-id'}] . ", ";
} else {
$out .= "DEFAULT, ";
$out .= "DEFAULT, ";
}
$out .= sqlD($project_todoitem[$i]->{'created-at'}) . ", ";
$out .= $people[(integer)$project_todoitem[$i]->{'creator-id'}] . ", ";
$out .= (integer)$project_todoitem[$i]->{'position'};
$out .= ");\n";
$sql[] = $out;
}
$post_category = $bc->xpath("/account/projects/project/post-categories/post-category");
for ($i = 0; $i < sizeof($post_category); $i++) {
$categories[(integer)$post_category[$i]->{'id'}] = (string)$post_category[$i]->{'name'}[0];
}
$sql[] = "DELETE FROM ${dbprefix}project_messages;\n";
$sql[] = "ALTER TABLE ${dbprefix}project_messages AUTO_INCREMENT = 1;\n";
$project_post = $bc->xpath("/account/projects/project/posts/post");
for ($i = 0; $i < sizeof($project_post); $i++) {
$out = "INSERT INTO ${dbprefix}project_messages (milestone_id, project_id, title, text, additional_text, comments_enabled, created_on, created_by_id) VALUES (";
if ((integer)$project_post[$i]->{'milestone-id'} === 0) {
$out .= "DEFAULT, ";
} elseif (array_key_exists((integer)$project_post[$i]->{'milestone-id'}, $milestones)) {
$out .= $milestones[(integer)$project_post[$i]->{'milestone-id'}] . ", ";
} else {
$out .= "DEFAULT, ";
}
$out .= $projects[(integer)$project_post[$i]->{'project-id'}] . ", ";
$out .= sqlT($project_post[$i]->{'title'}) . ", ";
$out .= sqlT($project_post[$i]->{'body'}) . ", ";
$out .= sqlT($project_post[$i]->{'extended-body'}) . ", ";
$out .= "1, ";
$out .= sqlD($project_post[$i]->{'posted-on'}) . ", ";
if (array_key_exists((integer)$project_post[$i]->{'author-id'}, $people)) {
$out .= $people[(integer)$project_post[$i]->{'author-id'}];
} else {
$out .= "DEFAULT";
}
$out .= ");\n";
$sql[] = $out;
$posts[(integer)$project_post[$i]->{'id'}] = $i + 1;
}
$sql[] = "DELETE FROM ${dbprefix}tags;\n";
$sql[] = "ALTER TABLE ${dbprefix}tags AUTO_INCREMENT = 1;\n";
for ($i = 0; $i < sizeof($project_post); $i++) {
$out = "INSERT INTO ${dbprefix}tags (project_id, tag, rel_object_id, rel_object_manager, created_on, created_by_id) VALUES (";
$out .= $projects[(integer)$project_post[$i]->{'project-id'}] . ", ";
$out .= sqlT($categories[(integer)$project_post[$i]->{'category-id'}]) . ", ";
$out .= $posts[(integer)$project_post[$i]->{'id'}] . ", ";
$out .= sqlT('ProjectMessages') . ", ";
$out .= sqlD($project_post[$i]->{'posted-on'}) . ", ";
if (array_key_exists((integer)$project_post[$i]->{'author-id'}, $people)) {
$out .= $people[(integer)$project_post[$i]->{'author-id'}];
} else {
$out .= "DEFAULT";
}
$out .= ");\n";
$sql[] = $out;
}
$sql[] = "DELETE FROM ${dbprefix}comments;\n";
$sql[] = "ALTER TABLE ${dbprefix}comments AUTO_INCREMENT = 1;\n";
$post_comment = $bc->xpath("/account/projects/project/posts/post/comments/comment");
for ($i = 0; $i < sizeof($post_comment); $i++) {
$out = "INSERT INTO ${dbprefix}comments (rel_object_id, rel_object_manager, text, created_on, created_by_id) VALUES (";
$out .= $posts[(integer)$post_comment[$i]->{'commentable-id'}] . ", ";
$out .= sqlT('ProjectMessages') . ", ";
$out .= sqlT($post_comment[$i]->{'body'}) . ", ";
$out .= sqlD($post_comment[$i]->{'created-at'}) . ", ";
if (array_key_exists((integer)$post_comment[$i]->{'author-id'}, $people)) {
$out .= $people[(integer)$post_comment[$i]->{'author-id'}];
} else {
$out .= "DEFAULT";
}
$out .= ");\n";
$sql[] = $out;
}
$sql[] = "DELETE FROM ${dbprefix}message_subscriptions;\n";
$subscriptions = Array();
for ($i = 0; $i < sizeof($post_comment); $i++) {
if (array_key_exists((integer)$post_comment[$i]->{'author-id'}, $people)) {
if (!array_key_exists((integer)$post_comment[$i]->{'commentable-id'}, $subscriptions)) {
$subscriptions[(integer)$post_comment[$i]->{'commentable-id'}] = Array();
}
if (!array_key_exists((integer)$post_comment[$i]->{'author-id'}, $subscriptions[(integer)$post_comment[$i]->{'commentable-id'}])) {
$subscriptions[(integer)$post_comment[$i]->{'commentable-id'}][(integer)$post_comment[$i]->{'author-id'}] = true;
}
}
}
foreach ($subscriptions as $message=>$users) {
foreach ($users as $user=>$value) {
$out = "INSERT INTO ${dbprefix}message_subscriptions (message_id, user_id) VALUES (";
$out .= $posts[$message] . ", ";
$out .= $people[$user];
$out .= ");\n";
$sql[] = $out;
}
}
$sql[] = "DELETE FROM ${dbprefix}application_logs;\n";
$sql[] = "ALTER TABLE ${dbprefix}application_logs AUTO_INCREMENT = 1;\n";
$sql[] = "COMMIT WORK;\n";
echo 'SQL
';
echo '';
foreach ($sql as $line) {
echo $line;
// WARNING THIS WILL DESTROY YOUR DATA
// Uncomment the lines below if you are sure you want this script to update your database.
/*
$result = mysql_query($line, $conn);
if (!$result) {
echo $line . "\n";
echo 'Could not execute: ' . mysql_error();
$result = mysql_query("ROLLBACK WORK", $conn);
die('Rolled back');
}
*/
}
echo '';
mysql_close($conn);
echo 'Script Completed';
function sqlT($input) {
global $conn;
return "'" . mysql_real_escape_string($input, $conn) . "'";
}
function sqlD($input) {
$input = str_replace('T', ' ', $input);
$input = str_replace('Z', '', $input);
return "'" . $input . "'";
}
function lookupTZ($tz) {
switch ($tz) {
case 'Eastern Time (US & Canada)':
return -5;
case 'Central Time (US & Canada)';
return -6;
default:
return 0;
}
}
function lookupIM($im) {
switch ($im) {
case 'ICQ':
return 1;
case 'AOL':
return 2;
case 'MSN':
return 3;
case 'Yahoo':
return 4;
case 'Skype':
return 5;
case 'Jabber':
return 6;
case 'Google':
return 6;
default:
return 0;
}
}
?>