#! /usr/bin/env perl

# Chr. Clemens Lee <clemens@kclee.de>
# 2004-10-22
#
# http://www.kclee.de/clemens/unix/#webcal2pdb
# Version: $Revision: 1.5 $ $Date: 2004/10/24 10:49:58 $
#
# Licence: GNU GPL

# This perl script exports calendar events from a WebCalendar
# (http://webcalendar.sourceforge.net/) PostgreSQL database and creates a Palm DatebookDB.pdb
# file that can be uploaded to a Palm Pilot.

# Besides a running WebCalendar installation (current version is 0.9.44) using a PostgreSQL
# database and a Palm Pilot you need two perl libraries installed: 
#    Palm and Pg
# On Debian you can install these with the commands:
#    apt-get install libpalm-perl
#    apt-get install libpgperl
# In order to have access to the relevant tables on the database, grant select rights to the user
# that you want to invoke this script from. Assuming you are on a Linux system, do the following:
#    su - www-data
#    psql webcalendar
#    grant select on webcal_entry to MY_LINUX_USER_ID;
#    grant select on webcal_entry_user to MY_LINUX_USER_ID;
#    \q
#    exit
# Now you can invoke webcal2pdb.pl:
#    ./webcal2pdb.pl MY_WEBCALENDAR_USER_ID
# If everything works allright you should see output like:
#    Creating Palm datebook
#    Opening WebCalendar database
#    713 records created
#    Writing DatebookDB.pdb
#    Done
# There is now a file DatebookDB.pdb in the current directory.
#
# I am using jpilot 0.99.7 on Linux and do now the following to upload this calendar file to my Palm.
#
# !!!BE CAREFUL!!! IF YOU DO ANYTHING WRONG OR YOU DON'T KNOW WHAT YOU ARE DOING, YOU CAN EASILY
# LOOSE DATA ON YOUR PALM PILOT OR YOUR LOCAL PALM DATABASE!!!
#
# Also be aware that you ALWAYS overwrite all your calendar data on your Palm Pilot.
# So make sure to only update data on your WebCalendar, never on the Palm itself.
#
# First time you do this, make a normal sync of your Palm Pilot and copy all your local Palm data
# aside for backup purposes. E.g. for the first time, do:
#    cd ~
#    cp -a .jpilot .jpilot.bak
# Once you are feeling comfortable in what you are doing the follow steps will do:
#    connect Palm Pilot to the PC
#    cd ~/.jpilot
#    cp DatebookDB.pdb DatebookDB.pdb.bak
#    cp ~/webcal2pdb/DatebookDB.pdb .
#    jpilot
#    press sync button on Palm Pilot
#    jpilot -> File -> Restore Handheld
#    select only DatabookDB.pdb!!! Be careful, if you select more, you overwright your other data
#       Be aware all your calendar data on the Palm Pilot will be overwritten in any case!!!
#    press OK etc.
# If you want you can now also do a normal Palm sync while you are at it...
#
# Shortcomings: this script does not copy duration times of events and does not work with
#               other databases like mysql or Oracle. It should be easy to add these features,
#               but I have no need and time to implement this myself.
#
# Thanks to the people who have developed WebCalendar and the Palm perl library.


use Palm::Datebook;
use Pg;


$user = $ARGV[0];
if ( $#ARGV == -1 )
{
 print "ERROR: No WebCalendar user on the command line provided! E.g.: \"webcal2pdb[.pl] my_webcal_name\"\n";
 exit( 2 );
}

print "Creating Palm datebook\n";

$pdb = new Palm::Datebook;


print "Opening WebCalendar database\n";

$conn = Pg::connectdb("dbname=webcalendar");
Pg::doQuery($conn, "select t1.cal_date, t1.cal_time, t1.cal_name from webcal_entry t1, webcal_entry_user t2 where t2.cal_login = '" . $user . "' and t1.cal_id = t2.cal_id", \@ary);

$count = 0;
for $i ( 0 .. $#ary )
{
    $count = $count + 1;
    $record = $pdb->new_Record;

    $date = $ary[$i][0];
    $time = $ary[$i][1];
    $name = $ary[$i][2];

	 $year = $date;
	 $year =~ s/^(....).*/$1/;
    $month = $date;
    $month =~ s/^....(..).*/$1/;
    $day = $date;
    $day =~ s/^......(..)/$1/;

	 $record->{year} = $year;
	 $record->{month} = $month;
	 $record->{day} = $day;

	 $hour = $time;
	 $hour =~ s/^(..).*/$1/;
    $minute = $time;
	 $minute =~ s/^..(..).*/$1/;
	 $record->{start_hour} = $hour;
	 $record->{start_minute} = $minute;
	 $record->{end_hour} = $hour;
	 $record->{end_minute} = $minute;

	 $record->{description} = $name;

	 $pdb->append_Record( $record );
}
print "$count records created\n";

print "Writing DatebookDB.pdb\n";

$pdb->Write( "DatebookDB.pdb" );


print "Done\n";