<?php
/*
 * Title: Textpattern Importer (Experimental)
 * 
 * Description:
 *      Reads a textpattern database and imports articles from a certain section as well as
 *      (optionally) all the comments associated with that article
 * 
 * Author: 
 *      John Boiles
 * 
 * Version: 
 *      1.0.1
 *
 * Warning(!!):
 *      This script worked for me, but BACKUP YOUR DATABASE before using it. Most likely the worst
 *      That could happen is you'll have way too many documents in the wrong place, but backup your
 *      modx database before running this snippet just to be safe. You've been warned.
 *
 * Notes:
 *      * Make sure your textpattern database is accessible from the server running this snippent
 *        Most servers restrict SQL access only to localhost
 *      * I had trouble previewing the xml data when logged into the manager. QM kept trying to
 *        insert itself in my xml file!
 *      * I tested this on Textpattern 4.0.6, I bet it works on other version too. 
 *      * The Jot tables must be created to import comments. To create these tables, simply run the
 *        Jot snippet. Tables will be created automatically by Jot.
 *      * (For the future): It'd be great to be able to make web users from the texpattern comment
 *        names. Then you could preserve things like post counts.
 * Usage:
     1. Create a snippet titled TxpToModx
     2. Paste the contents of this file into that snippet
     3. Change configuration variables (below) to fit your situation
     4. Create a document that includes the snippet call [!TxpToModx!]
     5. Set that document to a blank template, content type: text/xml, uncheck rich text
     6. Preview your document
     7. If everything goes as planned you should see xml of all the articles and comments that will be
        imported
     8. When you're ready to take the dive in, change your snippet call from [!TxpToModx!] to
        [!TxpToModx?importItems=`1`!]
     9. Preview your document again. The script will be executed and (hopefully) all your articles will
        be imported
 *
 * Changelog
 * 9 Sep 2009 - Changed the comments code so that jot_fields is updated with the name and email rather than customfields
 */

require_once('assets/libs/docmanager/document.class.inc.php');

/******* SETTINGS ******* SETTINGS ******* SETTINGS ******* SETTINGS ******* SETTINGS *******/

//This paremeter must be set for the actual import to take place, otherwise everything will only be
//Echoed on screen
(isset($importItems)) ? $importItems : $importItems = false;

//TXP Settings
//The section from which to grab articles
$txpSection = 'mysection';
$txpServer = 'myserver.com';
$txpDatabase = 'mydatabasename';
$txpUsername = 'myusername';
$txpPassword = 'mypassword';

//MODx Settings
//The parent document where imported documents will go
$modxParentDoc = 7;
//The template that will be used for all the imported documents
$modxTemplate = 'My Templates Name';
//Publish imported documents?
$modxPublish = true;   
//The id of the modx creator (I believe 1 is usually siteadmin)
$modxCreatorId = 1;    
    
//General Settings
//Copy comments and add at {{Comments}} footer to each document
$includeComments = true;

/******* END SETTINGS ******* END SETTINGS ******* END SETTINGS ******* END SETTINGS *******/


//Convert SQL datetime to unixtime
function datetimeToUnixtime($datetime){
    $result = mysql_query("select unix_timestamp('$datetime')");
    $unixtime = mysql_fetch_array($result);
    return $unixtime[0];
}

//Make sure the MODx parent document is a container
$parentDoc = new Document($modxParentDoc,'*');
$parentDoc->Set('isfolder','1');
$parentDoc->Save();

//Connect to TXP database
$db = mysql_connect($txpServer, $txpUsername, $txpPassword) or die(mysql_error());
mysql_select_db($txpDatabase, $db) or die(mysql_error()); 

//use (txp) textpattern->Section to limit which articles we import
$sql = "select * from textpattern where Section='$txpSection'";
$result = mysql_query($sql);

//For each article found
echo '<?xml version="1.0" encoding="ISO-8859-1"?>';
echo '<textpattern>';
while($row = mysql_fetch_array($result)) {
    
    //convert txp's datetime to unixtime
    $createdUnixtime = datetimeToUnixtime($row['Posted']);

    echo '<article>';
    echo '<Title>'.$row['Title'].'</Title>';
    echo '<Excerpt_html>'.$row['Excerpt_html'].'</Excerpt_html>';
    echo '<Body_html>'.strip_tags($row['Body_html']).'</Body_html>';
    echo '<Posted>'.$row['Posted'].'</Posted>';
    echo '<Posted-unixtime>'.$createdUnixtime.'</Posted-unixtime>';
    echo '<comments_count>'.$row['comments_count'].'</comments_count>';
    echo '<url_title>'.$row['url_title'].'</url_title>';
    
    if($importItems){
    
    $doc = new Document();
    $doc->Set('parent',$modxParentDoc);
    $doc->Set('pagetitle',mysql_escape_string($row['Title']));    
    $doc->Set('alias',$row['url_title']);
    if($includeComments){
        $doc->Set('content',mysql_escape_string($row['Body_html']).'{{Comments}}');
    } else {
        $doc->Set('content',$row['Body_html']);
    }
    $doc->Set('template',$modxTemplate);
    $doc->Set('createdon',$createdUnixtime);
    $doc->Set('createdby',$modxCreatorId);
    $doc->Set('introtext',mysql_escape_string($row['Excerpt_html']));
    if($modxPublish){$doc->Set('published',1);}
    $doc->Save();
    }

    //Copy comments from textpattern to modx (Jot)
    if($includeComments){
        $txpId = $row['ID'];
        $sql = "select * from txp_discuss where parentid='$txpId'";
        $comments = mysql_query($sql);
        while($comment = mysql_fetch_array($comments)) {
            //Need to strip <p> tags that surround txp comments
            $commentContent = strip_tags($comment['message']);
            $commentUnixtime = datetimeToUnixtime($comment['posted']);

            /* we also need to deal with comments*/
            echo '<comment>'; //begin comment
            echo '<visible>'.$comment['visible'].'</visible>';
            echo '<ip>'.$comment['ip'].'</ip>';
            echo '<message>'.$commentContent.'</message>';
            //This preg gets rid of an & that was in a name, i think all amperstands are already
            //correctly handled for the text fields
            echo '<name>'.preg_replace('/(&amp;|&)/i', '&amp;', $comment['name']).'</name>';
            //echo '<name>'.$comment['name'].'</name>';
            echo '<email>'.$comment['email'].'</email>';
            echo '<posted>'.$comment['posted'].'</posted>';
            echo '<posted-unixtime>'.$commentUnixtime.'</posted-unixtime>';
            echo '</comment>'; //end comment
            
            if($importItems){
                $fields = array('title' => 'From '.mysql_escape_string($comment['name']),
                                'tagid' => '',
                                'published' => '1',
                                'uparent' => $doc->Get('id'),
                                'parent' => 0,
                                'flags' => '',
                                'secip' => $comment['ip'],
                                'sechash' => '',
                                'content' => mysql_escape_string($commentContent),
                                'customfields' => '<custom><name>'.mysql_escape_string($comment['name']).'</name><email>'.mysql_escape_string($comment['email']).'</email></custom>',
                                'mode' => '0',
                                'createdby' => '0',
                                'createdon' => $commentUnixtime);
                $newcommentid = $modx->db->insert($fields,$modx->getFullTableName('jot_content'));
                
                //make new items in modx_jot_fields that hold the name/email (untested)
                $comment = array('id' => $newcommentid,
                                 'label' => 'name',
                                 'content' => mysql_escape_string($comment['name']));
                $modx->db->insert($comment,$modx->getFullTableName('jot_fields'));
                $comment = array('id' => $newcommentid,
                                 'label' => 'email',
                                 'content' => mysql_escape_string($comment['email']));
                $modx->db->insert($comment,$modx->getFullTableName('jot_fields'));    
            }
        }
    }
    echo '</article>'; //end article

}

echo '</textpattern>';


?>