#!/usr/bin/env ruby
# 
# Query a local PostgreSQL/RDFdb for project management data
#
# danbri
# save as .vcs for import into Palm Desktop (Windows)

ENV['RUBYRDF']='/home/danbri/s-rubyrdf/' if !ENV['RUBYRDF']
require ENV['RUBYRDF']+'squish/squish'  # 
require ENV['RUBYRDF']+'basicrdf'  	# no sys-wide installer yet.
require 'dbi'

DBI_DRIVER = 'DBI:Pg:test1'
DBI_USER = 'danbri'
DBI_PASS=''

service = DBIDataService.new(DBI_DRIVER,DBI_USER,DBI_PASS)
sq = 'SELECT  ?doc, ?due, ?name, ?desc, ?dnum, ?st, 
	WHERE 
	(rdf::type ?doc pm::DeliverableSpec)
	(pm::name ?doc ?name) 
	(pm::number ?doc ?dnum) 
	(pm::description ?doc ?desc)
        (pm::mainStartDate ?doc ?st) 
	(pm::relMonthDue ?doc ?due) 
	 USING pm for http://www.w3.org/2002/02/esw/pm# rdf for http://www.w3.org/1999/02/22-rdf-syntax-ns#'
  query = SquishQuery.new()
  DBI.connect( DBI_DRIVER, DBI_USER, DBI_PASS) do | dbh |


puts "BEGIN:VCALENDAR"
puts "VERSION:1.0"
puts "PRODID: esw-rdf-caltest"

def time2vc (t) 
  return "#{t.year}#{t.month}#{t.day}T#{t.hour}#{t.min}#{t.sec}Z" 
end

  dbh.select_all( query.parseFromText(sq).toSQLQuery  ) do | row |  
    r = ResultRow.new(row) 

    due = r.due
    base = r.st
    year, month = base.split(/-/)
     start = Date::new year.to_i, month.to_i, 1 
    delivdate = start>>(due.to_i)
    alarmdate = delivdate - 14
    delivdate = delivdate + 28
    puts "BEGIN:VEVENT
SUMMARY: #{r.name} (SWAD-Europe deliverable)
DTSTAMP:#{ time2vc Time::now} 
UID:mailto:www-esw+genid#{time2vc Time::now}@w3.org
ORGANIZER:mailto:www-esw+calsched@w3.org
DTSTART:#{ delivdate.to_s.gsub!(/-/,'') }T090000Z
DTEND:#{ delivdate.to_s.gsub!(/-/,'') }T100000Z
DALARM: #{ alarmdate.to_s.gsub!(/-/,'') }T090000Z
STATUS:CONFIRMED
CATEGORIES:ESW 
DESCRIPTION:#{ r.desc } (month due: #{due} url: todo!)
END:VEVENT\n"
   end
	puts "END:VCALENDAR"
	puts
end

=begin
sample vcal
 
BEGIN:VCALENDAR
PRODID:-//libbytest//2002-04-28
BEGIN:VEVENT
DTSTAMP:20020428T120000Z
UID:eusw@example.com
ORGANIZER:MAILTO:libby.miller@bristol.ac.uk
DTSTART:20020428T143000Z
DTEND:20020428T150000Z
STATUS:CONFIRMED
CATEGORIES:SWAD-E
MARY:swad-e meeting
DESCRIPTION:lots of fun at the swad-e meeting
in bristol\ncool
LOCATION:RDFWeb HQ, Bristol, UK
END:VEVENT
END:VCALENDAR

RDF iew:
http://www.w3.org/2002/02/esw/pm#
       pm:DeliverableSpec
	pm:number
	pm:name
	pm:workpackage
	pm:relMonthDue
	pm:description
	pm:mainStartDate
	pm:delivNumber
	pm:monthDue
	pm:workPackage
	pm:duration


# lessons learned:
# bad data (even if RDF parses ok) can screw up database.
# suspicion: lots of empty strings in the graph makes pgsql jam up.
# todo testcase: this query/data, but where some propeties have "" value.
#

=end