#!/usr/bin/perl
#
# WordPress - Del.icio.us Synchronization script
#
# 2.3 Update by: Edward de Leau, http://edward.de.leau.net
# 2.3.1 Update: when posting multiple postings each one can have
#       a unique title and slug (EDL)
# 2.3.2 Update by Chris Craig http://chriscraig.net to exclude private posts
# 2.3.3 Fixes some weird character output by using Unicode (EDL)
#
# ------------------------------------------------------------------------
#
# This perl script allows you to daily synchronize you WordPress
# with your del.icio.us bookmarks. You can synchronize it either as
# seperate posts or as 1 daily links posting. You can style the links
# and you can add a category, synchronize the tags too and even
# put the del.icio.us tagslinks in the posting too.
#
# It was originally written by Stephen:
# http://stephen.evilcoder.com/archives/2005/02/27/daily-delicious-links-perl-script
#
# With the release of WordPress 2.3 however, the category table
# was no longer used and we have now the options to synchronize
# the tags too, which is quite handy. I also added the option to
# either post 1 post or multiple posts.
#
# Instructions:
#
# 0. make sure you have all the required Perl packages installed incl. Net::Delicious
# 1. fill in the fields under "user-configurable variables"
# 2. crontab the script to run at each day at Midnight GMT
#    e.g. for Dreamhost or MediaTemple at 15:50
#
# ------------------------------------------------------------------------
# For more information/questions/remarks: see my posting on this script
#
#
# ------------------------------------------------------------------------
#

use lib "/home/29931/data/scripts/delicious";
use strict;
use DateTime;
use DateTime::TimeZone;
use Net::Delicious;
use DBI;
use Encode;
use utf8;

# ------------------------------------------------------------------------
# time variables
# ------------------------------------------------------------------------
my $wp_timezone  = "US/Pacific";                         # the timezone you have set in Wordpress
my $time_local   = DateTime->now;
my $time_wp      = $time_local->clone()->set_time_zone($wp_timezone);
my $time_gmt     = $time_local->clone()->set_time_zone('GMT');

# The following depending on how you like it e.g. "%B %d, %G"
my $time_wp_day  = $time_wp->strftime("%G-%m-%d");
my $time_gmt_day = $time_gmt->strftime("%G-%m-%d");

# ------------------------------------------------------------------------
# user-configurable variables
# ------------------------------------------------------------------------

# your del.icio.us username
my $del_username        = "XXXXXXXXXX";                          

# your del.icio.us password
my $del_password        = "XXXXXXXXXX";                          

# WordPress database server
my $db_host             = "XXXXXXXXXX";  

# WordPress database user
my $db_user             = "XXXXXXXXXX";                            

# WordPress database password
my $db_pass             = "XXXXXXXXXX";                           

# WordPress database
my $db_name             = "XXXXXXXXXX";                 

# 1 - show private posts. 0 - don't show private posts.
my $del_showprivate		= 0;								

# WordPress database prefix
my $prefix              = "wp";                                 

# WordPress userid to post as
my $wp_userid           = "1";                                  

# title of post
my $post_title          = "Links for " . $time_wp_day;          

# allow comments? (open|closed)  
my $allowcomments       = "open";                               

# allow pings? (open|closed)
my $allowpings          = "open";                               

# post slug
my $post_name           = "daily-links";                        

# taxonomy_term id for the category
my $taxonomy_term_id    = "14";                                 

# all in 1 post ("single") or multiple posts ("multiple")
my $post                = "multiple";                           

# string to put before multiple posts title
my $multipleTitle      =  "Eds links for ";                     

# ------------------------------------------------------------------------
# initialize Net::Delicious objects
# ------------------------------------------------------------------------

my $del =
 Net::Delicious->new( { user => $del_username, pswd => $del_password } );
die "Unable to connect to del.icio.us.\n" unless $del;

# ------------------------------------------------------------------------
# Get posts from del.icio.us
# ------------------------------------------------------------------------

my @posts = $del->posts( { dt => $time_gmt_day } );

# ------------------------------------------------------------------------
# Initialize WordPress database
# ------------------------------------------------------------------------

my $dbh = DBI->connect( "DBI:mysql:$db_name:$db_host", $db_user, $db_pass )
  or die "Couldn't connect to database: " . DBI->errstr;

my $sql = qq{SET NAMES 'utf8';};
$dbh->do($sql);
  
# ------------------------------------------------------------------------
# Set db table parameters
# ------------------------------------------------------------------------

my $tb_posts                    = $prefix . "_posts";
my $tb_wp_terms                 = $prefix . "_terms";
my $tb_wp_term_taxonomy         = $prefix . "_term_taxonomy";
my $tb_wp_terms_relationships   = $prefix . "_term_relationships";
my $post_tag                    = "post_tag";

# ------------------------------------------------------------------------
# Set db table SQL procedures for the POSTS table
# ------------------------------------------------------------------------

my $sth         = $dbh->prepare(
    "INSERT INTO $tb_posts
	(post_author, post_date, post_date_gmt, post_content,
	 post_title, post_status, comment_status, ping_status,
	 post_name, post_modified, post_modified_gmt)
    VALUES (?,?,?,?,?,?,?,?,?,?,?)")
  or die "Couldn't prepare statement: " . dbh->errstr;

my $gth = $dbh->prepare("SELECT ID FROM $tb_posts ORDER BY ID DESC LIMIT 1")
  or die "Couldn't prepare statement: " . dbh->errstr;
  
# ------------------------------------------------------------------------
# Set db table SQL procedures for the WP_TERMS table
# ------------------------------------------------------------------------

my $wts    = $dbh->prepare(
    "SELECT term_id FROM $tb_wp_terms WHERE name = ?")
  or die "Couldn't prepare statement: " . dbh->errstr;

my $wti    = $dbh->prepare(
    "INSERT INTO $tb_wp_terms
    (name, slug) VALUES (?,?)")
  or die "Couldn't prepare statement: " . dbh->errstr;

# ------------------------------------------------------------------------
# Set db table SQL procedures for the WP_TERM_TAXONOMY table
# ------------------------------------------------------------------------

my $wtts    = $dbh->prepare(
    "SELECT term_taxonomy_id FROM $tb_wp_term_taxonomy WHERE term_id = ? AND taxonomy = '$post_tag'")
  or die "Couldn't prepare statement: " . dbh->errstr;

my $wtti    = $dbh->prepare(
    "INSERT INTO $tb_wp_term_taxonomy
    (term_id, taxonomy, count) VALUES (?,?,1)")
  or die "Couldn't prepare statement: " . dbh->errstr;

my $wtts_count    = $dbh->prepare(
    "SELECT count FROM $tb_wp_term_taxonomy WHERE term_taxonomy_id = ? AND taxonomy = '$post_tag'")
  or die "Couldn't prepare statement: " . dbh->errstr;

my $wtti_count    = $dbh->prepare(
    "UPDATE $tb_wp_term_taxonomy
     SET count = ?
     WHERE term_taxonomy_id = ?
     ")
  or die "Couldn't prepare statement: " . dbh->errstr;

# ------------------------------------------------------------------------
# Set db table SQL procedures for the WP_RELATIONS table
# ------------------------------------------------------------------------

my $wri =
 $dbh->prepare(
    "INSERT INTO $tb_wp_terms_relationships
    (object_id,term_taxonomy_id)
    VALUES (?,?)")
  or die "Couldn't prepare statement: " . dbh->errstr;

# ------------------------------------------------------------------------
# Generate the HTML for the posting(s)
# ------------------------------------------------------------------------

my $html;
my $line;
my $termid;
my $taxcount;
my $term_taxonomy_id;
my @taglist;
my $postObject_id;
my $post_namer;
my $postcounter;

$postcounter = 0;
$html = "";

foreach (@posts) {
  if ($_->shared() || $del_showprivate){
    $line = htmlPosting();
    if ( $post eq "single"  ) {
        # Save this line for later
        $html .= $line;
    } else {
        # Just Post the Post
        $html = $line;
        if ( $html ) {
            $postcounter = $postcounter + 1;
            $post_namer = $post_name . $postcounter;
            $postObject_id = postPost();
        }
    }

    # now parse the tags
    if ($_->tags) {
       foreach ( ( split /\s+/, $_->tags ) ) {
        # check if it exists
        $wts->execute($_);
        $termid = $wts->fetchrow_array();
        $wts->finish;
        # if it does not exist add it
        if ( !$termid ) {
            $wti->execute($_,$_);
            $wti->finish;
            $wts->execute($_);
            $termid = $wts->fetchrow_array();
            $wts->finish;
        }
        # check if it exists in the taxonomy database
        $wtts->execute($termid);
        $term_taxonomy_id = $wtts->fetchrow_array();
        $wtts->finish;
        # if it exists add count else add it
        if ( $term_taxonomy_id ) {
            $wtts_count->execute($term_taxonomy_id);
            $taxcount = $wtts_count->fetchrow_array();
            $wtts_count->finish;
            $taxcount = $taxcount + 1;
            $wtti_count->execute($taxcount,$term_taxonomy_id);
            $wtti_count->finish;
        } else {
            $wtti->execute($termid,$post_tag);
            $wtti->finish;
        }
        $wtts->execute($termid);
        $term_taxonomy_id = $wtts->fetchrow_array();
        $wtts->finish;
        # for a single post save the $term_taxonomy_id's for later
        # for a multi post immediately assign the tags
        if ( $post eq "single"  ) {
            push(@taglist, $term_taxonomy_id);
        } else {
            $wri->execute($postObject_id,$term_taxonomy_id);
            $wri->finish;
        }
       }    
    }
    if ( $post ne "single"  ) {
        # now also add the category
        $wri->execute($postObject_id,$taxonomy_term_id);
        $wri->finish;
    }
  }
}
my $taglistitem;
if ( $post eq "single" && $html ) {
    $post_namer = $post_name;
    $postObject_id = postPost();
    # for each post parse all tags
    foreach $taglistitem(@taglist) {
            $wri->execute($postObject_id,$taglistitem);
            $wri->finish;
    }
    # now also add the category
    $wri->execute($postObject_id,$taxonomy_term_id);
    $wri->finish;
}

############################################################################
sub htmlPosting     #11/14/07 12:38
############################################################################
{
        my $line;
        my ( $href, $description, $tags, $extended ) =
            ( $_->href, $_->description, $_->tags, $_->extended );
		$description = decode_utf8( $description );
		$extended = decode_utf8( $extended );
        my $line =
            "<p>\n" . "<a class=\"deliciouslink\" href=\"$href\" title=\"$description\" target=\"_blank\">$description</a>";
        if ($extended) {
            $line .= "\n$extended";
        }
        # --------------------------------------------------------------------
        # If you include this then you will show the del.icio.us links under
        # the del.icio.us link, however that seems a bit pointless if
        # you are also using WordPress's Tagging system.
        # --------------------------------------------------------------------
        #
        #if ($tags) {
        #    $line .= "\n(tags:";
        #    foreach ( ( split /\s+/, $tags ) ) {
        #        $line .= " <a class=\"delicioustag\" href=\"http://del.icio.us/$del_username/$_\">$_</a>";
        #    }
        #    $line .= ")";
        #}
        # --------------------------------------------------------------------
        $line .= "</p>\n";    # close HTML tag
        return $line;

}   ##htmlPosting

############################################################################
sub postPost        #11/14/07 1:02
############################################################################
 {
    # we need the date in "YYYY-MM-DD HH:MM:SS" format
    my $now     = $time_wp->strftime("%G-%m-%d %H-%M-%S");
    my $now_gmt = $time_gmt->strftime("%G-%m-%d %H-%M-%S");

    if ( $post ne "single"  ) {
        # $post_title = $multipleTitle . $time_wp_day . ": " .  $_->description;
		$post_title = $_->description;
        # you can change the above to e.g. only description
        # if you do not want to indicate the links date
    }

    # put the post into the WP database
    $sth->execute(
        $wp_userid,  $now,      $now_gmt,       $html,
        $post_title, 'publish', $allowcomments, $allowpings,
        $post_namer,  $now,      $now_gmt
);
    $sth->finish;

    # get the post ID
    $gth->execute();
    my $post_id = $gth->fetchrow_array();
    $gth->finish;

    return $post_id;
}   ##postPost

$dbh->disconnect;