<?php
# PHPslash 0.6x -> Drupal 4.5.2 import script.
#
#  WARNING WARNING WARNING
#  This is nothing but a DIRTY HACK. No style guide, no nothing.
#  Use at your own risk. Keep out of reach of small children.
#  May explode spontaneously. Only use where appropriate. Do not eat.
#
#  I don't care much about PHP security here because this script
#  should not be run by anybody else than admins anyway. So DELETE IT
#  after importing your data!
#  (addslashes, etc)
#
# Things to import: sections, topics, stories, and comments.
# Copy this script to the DRUPAL INSTALLATION ROOT DIRECTORY. The
# script will INCLUDE the DRUPAL database login info files and use
# some Drupal include scripts. Then just run it in your browser - you
# will get a HTML form where you can configure how to import your data.
#
# Things to remember:
#
# - User accounts will be IGNORED. Every story will get the user ID
#   specified.
# - Topics and Sections will become two "Vocabularies" in the Drupal
#   taxonomy with their respective terms as contents.
# - PHPslash stories will become Drupal nodes with "story" type.
#   Currently this script assumes that no two identical story topics
#   are posted at the exact same time.
# - PHPslash comments will be converted to Drupal comments.
# - All text will be auto-converted from ISO8859-1 to UTF-8
#   (Drupal default).
#
# TODO:
# - Threading of comments is still broken (UPDATE: seems to work! Test it!)
# - PHPslash defines a "front page" article as one belonging to a certain
#   section (I called it "home"). Automatically promote these articles,
#   and none others. I had no need for this feature so I didn't write it.
# - Rewriting of URLs within articles ("article.php3?story_id=XX") to the
#   new Drupal format, including new node IDs, has yet to be done. Using
#   mod_rewrite does not work (so easily), because the node IDs are not
#   carried over to Drupal (which makes keeping old articles in Drupal
#   possible).
#

$uid = $_POST[uid];    # all stories in Drupal will get this user_id!
$cmt = $_POST[cmt];    # 0:disable comments 1: read-only comments  2: read/write
$fmt = $_POST[fmt];    # story format: 1:filtered HTML, 2: php code, 3: full HTML
$promote = $_POST[promote]; # promote all old articles to front page?

##########################################################################
# nothing really configurable below here
##########################################################################

# NB: Using two db connections conflicts with the Drupal DB functions.
# So: read EVERYTHING from phpslash first, then close DB connection,
# then use the Drupal functions!

function load_phpslash() {
    global $jsql, $SECTIONS, $TOPICS,
        $STORIES, $STORYTOPICS, $STORYSECTIONS, $COMMENTS;
    print "<b>1. Loading PHPslash database</b><br/>";

    # Connect to PHPslash database.
    $jsql = mysql_connect($_POST[slash_h], $_POST[slash_u], $_POST[slash_p])
        or die("database connect error");
    mysql_select_db($_POST[slash_d], $jsql) or die("database use error");

    #
    # 1. load topics/sections
    #
    $res = mysql_query("SELECT section_id AS id, section_name AS name,
        description AS text FROM psl_section", $jsql)
        or die("db exec error:".mysql_error());
    while($row = mysql_fetch_assoc($res)) { $SECTIONS[] = $row; }
    print "<br>".count($SECTIONS)." sections loaded.";
    
    $res = mysql_query("SELECT topic_id AS id, topic_name AS name,
        alt_text AS text FROM psl_topic", $jsql)
        or die("db exec error:".mysql_error());
    while($row = mysql_fetch_assoc($res)) { $TOPICS[] = $row; }
    print "<br>".count($TOPICS)." topics loaded.";

    #
    # 2. load stories, story topics and story sections
    #
    $res = mysql_query("SELECT story_id, title, dept, user_id, hits,
        UNIX_TIMESTAMP(time) AS time, intro_text,
        body_text, topic_cache FROM psl_story")
        or die("db exec error:".mysql_error());
    while($row = mysql_fetch_assoc($res)) { $STORIES[] = $row; }
    print "<br>".count($STORIES)." stories loaded. ";
    
    $res = mysql_query("SELECT lut_id, topic_id, story_id FROM psl_topic_lut")
        or die("db exec error:".mysql_error());
    while($row = mysql_fetch_assoc($res)) { $STORYTOPICS[] = $row; }
    print "<br>".count($STORYTOPICS)." story/topic relations loaded. ";
    
    $res = mysql_query("SELECT lut_id, section_id, story_id FROM psl_section_lut")
        or die("db exec error:".mysql_error());
    while($row = mysql_fetch_assoc($res)) { $STORYSECTIONS[] = $row; }
    print "<br>".count($STORYSECTIONS)." story/section relations loaded. ";
    
    #
    # 3. Load comments
    #
    $res = mysql_query("SELECT comment_id, parent_id, story_id,
        UNIX_TIMESTAMP(date) AS date,
        name, email, subject, comment_text FROM psl_comment", $jsql);
    while($row = mysql_fetch_assoc($res)) { $COMMENTS[] = $row; }
    print "<br>".count($COMMENTS)." comments loaded.";
}


##########################################################################
#exit;



# Sections. Import directly from
#   psl_section: section_id, section_name, description, artcount
# in Drupal:
#    1. Create Vocabulary "PHPslash Section"
#    2. foreach section in phpslash:psl_section, insert "term" in this vocab
#    3. ditto with "PHPslash Topic"
function create_vocab($name, $desc, $data) {
    global $ID2TID;

    $result = db_query("SELECT name from {vocabulary} WHERE name='$name'");
    if(db_num_rows($result)==0) {
        $voc->name = $name;
        $voc->description = $desc;
        $voc->nodes = array("story", "page");
        $voc->multiple = 1; $voc->required = 0;
        $voc->relations = 1;
        $voc->hierarchy = 1;
        $voc->weight = 0;
        taxonomy_save_vocabulary(object2array($voc));
        print "<br>created $name vocabulary.";
    } else {
        print "<br>$name vocab exists already.";
    }

    # get Vocab ID out of DB
    $vocab = db_fetch_object(db_query(
        "SELECT v.vid FROM {vocabulary} v WHERE v.name = '$name'"));
    $term->vid = $vocab->vid;
    $term->parent = 0;
    $term->weight = 0;
    print " (vocabulary ID is ".$vocab->vid.")";

    # save topics/sections
    foreach($data as $row) {
          $term->name = utf8_encode($row['name']);
          $term->description = utf8_encode($row['text']);
        $result = db_query("SELECT tid,name FROM {term_data}
            WHERE vid=".$vocab->vid." AND name = '".$term->name."'");
        $termrow = db_fetch_array($result);
        if(db_num_rows($result)==0) {
            $editedterm = taxonomy_save_term(object2array($term));
            print "<br>&nbsp;&nbsp; -- created $name \"".$row['name']."\", tid=".$editedterm['tid'].".";
            $ID2TID[$row['id']] = $editedterm['tid'];
        } else {
            print "<br>&nbsp;&nbsp; -- exists: $name \"".$row['name']."\", tid=".$termrow['tid'].".";
            $ID2TID[$row['id']] = $termrow['tid'];
        }
    }

    print "<br/>### Updating sequences table (vocabulary_vid)";
    $r = db_query("SELECT max(vid) as vid FROM vocabulary");
    $a = db_fetch_array($r); $id = $a[vid];
    db_query("UPDATE sequences SET id=$id WHERE name='vocabulary_vid'");

    print "<br/>### Updating sequences table (term_data_tid)";
    $r = db_query("SELECT max(tid) as tid FROM term_data");
    $a = db_fetch_array($r); $id = $a[tid];
    db_query("UPDATE sequences SET id=$id WHERE name='term_data_tid'");


}


# insert stories.
# phpslash:
#    psl_story: story_id, user_id, title, dept, time, intro_text, body_text, hits
#    psl_topic_lut: lut_id, topic_id, story_id
#    psl_section_lut: lut_id, section_id, story_id
# drupal:
#   node: type="story", title=title, uid=$uid, status=1, created=time,
#    changed=time, comment=$cmt, format=$fmt, teaser=intro_text,
#    body="intro_text<!--break-->body_text"
#  node_counter: nid=node.nid, totalcount=hits, daycount=hits, timestamp=now()
#  term_node: nid=node.nid, tid=
#
function create_stories() {
    global $TOPICS, $SECTIONS, $STORIES, $STORYTOPICS, $STORYSECTIONS,
        $ID2TID, $STORY2NODE, $cmt, $uid, $fmt, $jsql, $promote;

    $node->uid = $uid;
    $node->type = "story";
    $node->status = 1;
    $node->promote = $promote;
    $node->comment = $cmt;

    print "<br><br>Creating stories ...";
    reset($STORIES); foreach($STORIES as $S) {
        $node->title = utf8_encode(stripslashes($S['title']));
        $result = db_query("SELECT nid, title FROM node
            WHERE title = '".addslashes($node->title)."' AND created=".$S['time']);
        if(db_num_rows($result)==0) {
            $node->created = $S['time'];
            $node->changed = $S['time'];
            $node->comment = $cmt;
            $node->format = $fmt;
            $node->teaser = utf8_encode(stripslashes($S['intro_text']));
            $node->body = utf8_encode(stripslashes($S['intro_text']))
             ."\n\n<!--break-->\n\n". utf8_encode(stripslashes($S['body_text']));
            $nid = node_save($node);
            db_query("INSERT INTO {url_alias} (src, dst) VALUES
              ('%s', '%s')", "node/view/$nid", "$nid");
            db_query("REPLACE INTO {node_counter} (nid, totalcount) VALUES
                (%d, %d)", $nid, $S[hits]);
            print "<br>&nbsp;&nbsp; -- create($nid): ".$S['title'];
        } else {
            $o = db_fetch_object($result); $nid = $o->nid;
            print "<br>&nbsp;&nbsp; -- exists($nid): ".$S['title'];
        }

        # make connection between PHPslash story_id and Drupal node_id:
        $STORY2NODE[$S['story_id']] = $nid;
    }


    # walk $STORYTOPICS: (lut_id, topic_id, story_id)
    # for each entry, put an entry (nid, tid) in term_node,
    #    where nid = $STORY2NODE[story_id],
    #    and tid = $ID2TID[topic_id]
    print "<br><br>Creating story / topic relations ...";
    #echo "<pre>";var_dump($STORY2NODE); var_dump($ID2TID); echo "</pre>";
    foreach($STORYTOPICS as $stopic) {
        $result = db_query("SELECT nid,tid FROM {term_node}
            WHERE nid=".$STORY2NODE[$stopic[story_id]]."
            AND tid=".$ID2TID[$stopic[topic_id]]);
        if(db_num_rows($result)>0) {
            $txt = "exists:";
        } else {
            $txt = "insert ";
            db_query("INSERT INTO {term_node} (nid, tid) VALUES (%d, %d)",
                $STORY2NODE[$stopic[story_id]],
                $ID2TID[$stopic[topic_id]]);
        }
        print "<br>&nbsp;&nbsp; -- $txt story ($stopic[story_id]|".$STORY2NODE[$stopic[story_id]].") / topic ($stopic[topic_id]|".$ID2TID[$stopic[topic_id]].") ...";
    }

    # ditto for sections
    print "<br><br>Creating story / section relations ...";
    #echo "<pre>";var_dump($STORY2NODE); var_dump($ID2TID); echo "</pre>";
    foreach($STORYSECTIONS as $stopic) {
        $result = db_query("SELECT nid,tid FROM {term_node}
            WHERE nid=".$STORY2NODE[$stopic[story_id]]."
            AND tid=".$ID2TID[$stopic[section_id]]);
        if(db_num_rows($result)>0) {
            $txt = "exists:";
        } else {
            $txt = "insert ";
            db_query("INSERT INTO {term_node} (nid, tid) VALUES (%d, %d)",
                $STORY2NODE[$stopic[story_id]],
                $ID2TID[$stopic[section_id]]);
        }
        print "<br>&nbsp;&nbsp; -- $txt story ($stopic[story_id]|".$STORY2NODE[$stopic[story_id]].") / section ($stopic[section_id]|".$ID2TID[$stopic[section_id]].") ...";
    }
    
    # update sequences table
    print "<br/>### Updating sequences table (node_nid)";
    $r = db_query("SELECT max(nid) as nid FROM node");
    $a = db_fetch_array($r); $id = $a[nid];
    db_query("UPDATE sequences SET id=$id WHERE name='node_id'");


    
}


# phpslash:psl_comment(comment_id, parent_id, story_id, user_id, date, name, email,
#    ip, subject, comment_text, pending
# drupal:cid, pid, nid, uid, subject, comment, hostname, timestamp, format, name, mail
function create_comments() {
    global $COMMENTS, $STORY2NODE;

    # get biggest used comment ID
    $cid1 = db_next_id("{comments}_cid");    

    print "<br><br>Creating comments ...";
    foreach($COMMENTS as $c) {
        $r = db_query("SELECT cid FROM {comments}
            WHERE timestamp=$c[date]");
        if(db_num_rows($r)>0) {
            print "<br>&nbsp;&nbsp; -- exists: $c[date] / story=$c[story_id] / $c[subject]";
            continue;
        }
        if($STORY2NODE[$c[story_id]]==0) {
            print "<br>&nbsp;&nbsp; -- skipped: $c[date] story=$c[story_id] / $c[subject]  // (no story)";
            continue;
        }
        
        #
        # This threading code was stolen without permission
        # from Drupal's comment.module:post_comment()
        #
        print "<br>&nbsp;&nbsp; -- insert $c[date] / story=$c[story_id] / $c[subject]";
        
        unset($max, $decimals, $units, $thread);
        if($c[parent_id]==0) {    # first level comment, no parents
            $max = db_result(db_query("SELECT MAX(thread) FROM {comments}
                WHERE nid = %d", $STORY2NODE[$c[story_id]]));
            $max = rtrim($max, "/");
            $decimals = (string)substr($max, 0, strlen($max)-1);
            $units = substr($max, -1, 1);
            if($units) {$units++;} else {$units=1;}
            if($units==10) $units='90';
            $thread = "$decimals$units/";
            print " // (pid=$c[parent_id], max=$max, thread=$thread) ";
        } else {        # comment has parent comments
            $parent = db_fetch_object(db_query("SELECT * from {comments}
                WHERE cid=%d", $cid1+$c[parent_id]));
            $parent->thread = (string)rtrim((string)$parent->thread, "/");
            $max = db_result(db_query("SELECT MAX(thread) FROM {comments}
                WHERE thread LIKE '%s.%%' AND nid=%d", $parent->thread,
                $STORY2NODE[$c[story_id]]));
            if($max=='') {
                $thread = "$parent->thread.1/"; # first child
            } else {
                # Get second-to-last value in thread.
                $max = rtrim($max, "/");
                $parts = explode(".", $max);
                $parent_depth = count(explode(".", $parent->thread));
                $last = $parts[$parent_depth];
                # increase thread value (see comments.module comments)
                $decimals = (string)substr($last, 0, strlen($last)-1);
                $units = substr($last, -1, 1);
                $units++;
                if($units==10) $units='90';
                $thread = "$parent->thread.".$decimals.$units."/";
            }
            print " // (pid=$c[parent_id], max=$max, thread=$thread) ";
        }    
        
        $r = db_query("INSERT INTO {comments} (cid, pid, nid, subject,
            comment, hostname, timestamp, format, thread, name, mail)
            VALUES (%d, %d, %d, '%s', '%s', '%s', %d, %d, '%s', '%s', '%s')",
            $cid1 + $c[comment_id],
            ($c[parent_id]==0) ? 0 : ($cid1 + $c[parent_id]),
            $STORY2NODE[$c[story_id]],
            utf8_encode($c[subject]),
            utf8_encode($c[comment_text]),
            $c[ip],
            $c[date],
            1,
            $thread,
            $c[name],
            $c[email]
        );
    }
    
    #
    # update comment stats.
    #
    print "<br/>### Updating comments counts (comment_statistics) (";
    $r = db_query("select nid, count(*) as c, max(timestamp) as t
        from comments group by nid");
    while($data = db_fetch_array($r)) {
        $COMMENTCOUNT[$data[nid]] = $data[c];
        $COMMENTLAST[$data[nid]] = $data[t];
    }
    foreach($COMMENTCOUNT as $n=>$c) {
        db_query("REPLACE INTO node_comment_statistics
            (nid, last_comment_timestamp, last_comment_uid, comment_count)
            VALUES (%d, %d, %d, %d)", $n,  $COMMENTLAST[$n],
            $_POST[uid], $COMMENTCOUNT[$n]);
        echo "$n:$c, ";
    }
    echo ")";
    
    # update sequences table
    print "<br/>### Updating sequences table (comments_cid)";
    $r = db_query("SELECT max(cid) as cid FROM comments");
    $a = db_fetch_array($r); $id = $a[cid];
    db_query("UPDATE sequences SET id=$id WHERE name='comments_cid'");

}



##########################################################################
##########################################################################
#
# MAIN PROGRAM
#
if($_POST[go]) {
    load_phpslash();
    
    # drupal includes
    print "<hr/><br/><b>2. Create Drupal data</b><br/>";
    include_once "includes/bootstrap.inc";
    include_once "includes/common.inc";

    db_query("TRUNCATE TABLE cache");
    
    if($_POST[trunc]) {
        db_query("TRUNCATE TABLE node");
        db_query("TRUNCATE TABLE comments");
        db_query("TRUNCATE TABLE node_comment_statistics");
        db_query("TRUNCATE TABLE cache");
        db_query("TRUNCATE TABLE node_counter");
        db_query("TRUNCATE TABLE vocabulary");
        db_query("TRUNCATE TABLE url_alias");
        db_query("TRUNCATE TABLE term_data");
        db_query("TRUNCATE TABLE term_node");
    }
    
    create_vocab('PHPslash Sections', 'Imported Sections from PHPslash', $SECTIONS);
    create_vocab('PHPslash Topics', 'Imported Topics from PHPslash', $TOPICS);
    create_stories();
    create_comments();
    
} else {

    print "<h1>PHPslash to Drupal importer</h1>\n";
    $tmp = file($_SERVER[SCRIPT_FILENAME]); #var_dump($tmp);
    $tmp[0] = "#\n";
    echo "<pre>";
    foreach($tmp as $t) {
        if(eregi("^[^#]", $t)) break;
        print htmlspecialchars($t);
    }
    echo "</pre>";
}

?>    
    <hr/><form method=post name=f>
    <p>Make it easy for yourself: have your browser save this form content for easy reload.<br/>
    <h3>PHPslash (0.6x) database info:</h3>
    <input type=text name=slash_h value=hostname>
    <input type=text name=slash_d value=database>
    <input type=text name=slash_u value=username>
    <input type=password name=slash_p value=password></p>
    
    <h3>Misc settings:</h3><p>
    trunc = <input style='background-color:#ff8888;' name=trunc size=1 value=0>
        <b>DELETE</b> existing vocabularies/terms/stories/sections/comments? (0/1)<br/>
    uid = <input name=uid size=1 value=1> default uid for imported stories<br/>
    cmt = <input name=cmt size=1 value=1> 0:disable comments 1: read-only comments  2: read/write<br/>
    fmt = <input name=fmt size=1 value=3> story format: 1:filtered HTML, 2: php code, 3: full HTML<br/>
    promote = <input name=promote size=1 value=1> promote all old articles to front page (1/0)?</p>
    
    <input type=hidden name=go value=1>
    <input type=submit>
    </form>

<?
# vim:set ts=3 nowrap:
?>