David Alger

<?php
/**
 * mdd-backup.php
 *
 * @author     http://davidalger.com/
 * @copyright  Copyright (c) 2011 David Alger & Classy Llama Studios, LLC
 * @license    http://opensource.org/licenses/osl-3.0.php  Open Software License (OSL 3.0)
 */
 
/*
 * Information needed to connect to the database the data from MyDoneDone will be stored in.
 */
define('DB_CONN', 'unix_socket=/usr/local/zend/mysql/tmp/mysql.sock'); // I'm using a socket, but you should be able to use any type of connection here.
define('DB_NAME', 'portalcl_portal_dev'); // update this with your database name
define('DB_USER', 'root'); // update this with your database user
define('DB_PASS', 'root'); // update this with the password for the given database user
 
/*
 * Connection informtion for the MyDoneDone account owner.
 */
define('MDD_WSDL', 'https://classyllama.mydonedone.com/api/DoneDone.asmx?WSDL'); // change this to the URL for your account WSDL
define('MDD_USER', ''); // set this to the username of the account owner
define('MDD_PASS', ''); // set this to the password on your user account
 
/**
 * Quick and dirty method to easily print an error message and die if an error occurs while reading/writing to the database.
 *
 * @param string $message 
 * @return void
 */
function db_fail($message) {
    global $db;
    echo "\n\nDB FAILURE: $message\n";
    var_dump($db->errorInfo());
    die;
}
 
/**
 * Prints a message to the shell..
 *
 * @param string $message 
 * @return void
 */
function status($message) {
    echo $message."\n";
}
 
/*
 * Initialize the database tables that will store the data.
 */
try {
    // connect to database
    $db = new PDO('mysql:'.DB_CONN.';dbname='.DB_NAME, DB_USER, DB_PASS);
 
    // drop tables if we need to
    $sql = <<<SQL
        DROP TABLE IF EXISTS `mdd_issue_history`;
        DROP TABLE IF EXISTS `mdd_issue`;
        DROP TABLE IF EXISTS `mdd_user`;
        DROP TABLE IF EXISTS `mdd_project`;
SQL;
    $db->exec($sql);
 
    // create project table
    $sql = <<<SQL
        CREATE TABLE IF NOT EXISTS `mdd_project` (
            `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
            `name` VARCHAR(255),
            PRIMARY KEY (`id`)
        )ENGINE=INNODB;
SQL;
    $db->exec($sql);
 
    // create user table
    $sql = <<<SQL
        CREATE TABLE IF NOT EXISTS `mdd_user` (
            `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
            `name` varchar(255),
            `company` varchar(255),
            PRIMARY KEY (`id`)
        )ENGINE=INNODB;
SQL;
    $db->exec($sql);
 
    // create issue table
    $sql = <<<SQL
        CREATE TABLE IF NOT EXISTS `mdd_issue` (
            `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
            `project_id` INT(10) UNSIGNED NOT NULL,
            `created_at` DATETIME,
            `updated_at` DATETIME,
            `title` VARCHAR(255),
            `description` TEXT,
            `creator_user_id` INT(10) UNSIGNED NOT NULL,
            `resolver_user_id` INT(10) UNSIGNED NOT NULL,
            `STATUS` VARCHAR(255),
            `priority` VARCHAR(255),
            PRIMARY KEY (`id`),
            FOREIGN KEY (`project_id`) REFERENCES `mdd_project` (`id`),
            FOREIGN KEY (`creator_user_id`) REFERENCES `mdd_user` (`id`),
            FOREIGN KEY (`resolver_user_id`) REFERENCES `mdd_user` (`id`)
        )ENGINE=INNODB;
SQL;
    $db->exec($sql);
 
    // create issue history table
    $sql = <<<SQL
        CREATE TABLE IF NOT EXISTS `mdd_issue_history` (
            `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
            `issue_id` INT (10) UNSIGNED NOT NULL,
            `title` VARCHAR(255),
            `description` TEXT,
            `created_at` DATETIME,
            `creator_name` VARCHAR(255),
            PRIMARY KEY (`id`),
            FOREIGN KEY (`issue_id`) REFERENCES `mdd_issue` (`id`)
        )ENGINE=INNODB;
SQL;
    $db->exec($sql);
 
} catch (Exception $e) {
    die($e->getMessage());
}
 
/*
 * MyDoneDone API Connection
 */
 
// init wsdl
$client = new SoapClient(MDD_WSDL);
 
// authenticate with API
$res = $client->login(
    array(
        'username_or_email' => MDD_USER,
        'password' => MDD_PASS,
    )
);
 
// fail if login wasn't succesfull
if ($res->LoginResult !== true) {
    die('Login failed!');
}
 
// user cache array
$users = array();
 
// load the project list
$projects = $client->getProjects();
$projects = $projects->GetProjectsResult->ProjectInfo;
 
// iterate all projects collecting data
foreach ($projects as $project) {
    // insert project record into db
    if ($db->exec("INSERT INTO `mdd_project` (`name`) VALUES (".$db->quote($project->Name).")") === false) {
        db_fail('Insert to mdd_project failed.');
    };
    $pid = $db->lastInsertId();
    status("Created project $pid with name of {$project->Name}");
 
    // load project issues
    $issues = $client->getIssuesInProject(array(
        'project_id' => $project->ID,
        'should_load_issue_details' => true,
    ));
    $issues = $issues->GetIssuesInProjectResult->IssueInfo;
 
    // skip processing if there are no issues.
    if (!is_array($issues) || (is_array($issues) && count($issues) == 0)) {
        continue;
    }
 
    // iterate issues
    foreach ($issues as $issue) {
        // create creator user if not exists
        if (!isset($users[$issue->Creator->ID])) {
            $sql = "INSERT INTO `mdd_user` (`name`, `company`) VALUES (".$db->quote($issue->Creator->Name).", ".$db->quote($issue->Creator->CompanyName).")";
            if ($db->exec($sql) === false) {
                db_fail('Insert into mdd_issue');
            }
            $users[$issue->Creator->ID] = $db->lastInsertId();
            status("Created user {$users[$issue->Creator->ID]} with name of {$issue->Creator->Name} for {$issue->Creator->CompanyName}");
        }
 
        // create resolver user if not exists
        if (!isset($users[$issue->Resolver->ID])) {
            $sql = "INSERT INTO `mdd_user` (`name`, `company`) VALUES (".$db->quote($issue->Resolver->Name).", ".$db->quote($issue->Resolver->CompanyName).")";
            if ($db->exec($sql) === false) {
                db_fail('Insert into mdd_issue');
            }
            $users[$issue->Resolver->ID] = $db->lastInsertId();
            status("Created user {$users[$issue->Resolver->ID]} with name of {$issue->Resolver->Name} for {$issue->Resolver->CompanyName}");
        }
 
        // insert issue id
        $sql = "INSERT INTO `mdd_issue` (`project_id`, `created_at`, `updated_at`, `title`, `description`, `creator_user_id`, `resolver_user_id`, `status`, `priority`)
            VALUES ('$pid', '{$issue->CreateDate}', '{$issue->UpdateDate}', ".$db->quote($issue->Title).", ".$db->quote($issue->Description).", '{$users[$issue->Creator->ID]}', '{$users[$issue->Resolver->ID]}',  '{$issue->IssueStatus}', '{$issue->PriorityLevel}')";
        if ($db->exec($sql) === false) {
            db_fail('Insert into mdd_issue failed.');
        }
        $issueId = $db->lastInsertId();
        status("Created issue $issueId for project $pid with title of {$issue->Title}");
 
        // grab history into convenience var
        $history = $issue->History->History;
 
        // skip if there are no records
        if (!is_array($history) || (is_array($history) && count($history) == 0)) {
            continue;
        }
 
        // iterate issue history records
        foreach ($history as $record) {
            // insert history record
            $sql = "INSERT INTO `mdd_issue_history` (`issue_id`, `title`, `description`, `created_at`, `creator_name`)
                VALUES ('$issueId', ".$db->quote($record->Title).", ".$db->quote($record->Description).", ".$db->quote($record->CreateDate).", ".$db->quote($record->CreatorName).")";
            if ($db->exec($sql) === false) {
                db_fail('Insert into mdd_issue_history failed.');
            }
            status("Created history record for issue $issueId with title of {$record->Title}");
        }
    }
}
 
// deauthenticate
$client->logout();