Index: openacs-4/packages/calendar/tcl/calendar-outlook-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/tcl/calendar-outlook-procs.tcl,v
diff -u -r1.6 -r1.7
--- openacs-4/packages/calendar/tcl/calendar-outlook-procs.tcl 10 Jul 2002 14:58:25 -0000 1.6
+++ openacs-4/packages/calendar/tcl/calendar-outlook-procs.tcl 10 Jul 2002 22:42:19 -0000 1.7
@@ -22,6 +22,15 @@
namespace eval calendar::outlook {
+ ad_proc -private adjust_timezone {
+ {-timestamp:required}
+ {-server_tz "US/Eastern"}
+ {-user_tz "US/Eastern"}
+ {-format "YYYY-MM-DD HH24:MI:SS"}
+ } {
+ return [db_string adjust_timezone {}]
+ }
+
ad_proc ics_timestamp_format {
{-timestamp:required}
} {
@@ -66,6 +75,10 @@
calendar::item::get -cal_item_id $cal_item_id -array cal_item
# If necessary, select recurrence information
+ # Convert some dates for timezone
+ set cal_item(full_start_date) [adjust_timezone -timestamp $cal_item(full_start_date) -format $date_format -user_tz "Universal"]
+ set cal_item(full_end_date) [adjust_timezone -timestamp $cal_item(full_end_date) -format $date_format -user_tz "Universal"]
+
# Here we have some fields
# start_time end_time title description
@@ -134,226 +147,4 @@
return $ics_event
}
-
- ad_proc cal_output_outlook_event {} "the proc to deliver outlook formatted calendar events, registered to .ics" {
- # grab the event_id
- ad_page_contract {
- converts data to .ics format for msoutlook
-
- @param item_id
- @param all_occurences_p
- @parem client_timezone
- @author Walter McGinnis (wtem@olywa.net), Don Baccus (dhogaza@pacifier.com)
- @creation-date 2001-06-13
- @cvs-id $Id$
-
- } {
- item_id:integer
- {all_occurences_p:boolean 0}
- client_timezone
- }
-
- set logged_in_user_id [ad_verify_and_get_user_id]
-
- # determine what the difference between the user's client timezone is
- # and gmt
- # then set up the math for oracle to do when selecting date_time
- # a note on client_timezone:
- # it is minutes difference from gmt
- # either negative or positive
- # east of gmt is negative
- # positive values don't return a "+" before them,
- # so we only have to look for "-"
- # the funny thing is that we want to substract positive values
- # and add positive ones
-
- regexp -- {(-)*([0-9]+)} $client_timezone match dash digits
-
- # there is an old Mac OS time_date issue
- # where the time is offset up to 1440
- # rather than having a neg. value for timezones east of gmt
- # if this is the case we reset the variables accordingly as a workaround
-
- # i think this correct, but it may be the problem
- if {$digits >= 720} {
- set digits [expr 1440 - $digits]
- set dash "-"
- }
-
- set hours [expr $digits / 60]
-
- # this sets up the sql for creating gmt times for the event's date_times
-
- # grap applicable data and translate calendar data to outlook format
-
- ## THIS NEEDS TO CHANGE
- db_1row cal_get_outlook_event "select
- to_char(i.start_date [cal_outlook_gmt_sql $hours $dash], 'YYYYMMDD-HH24MISS') as dtstart,
- to_char(i.end_date [cal_outlook_gmt_sql $hours $dash], 'YYYYMMDD-HH24MISS') as dtend,
- to_char(i.start_date + 1 [cal_outlook_gmt_sql $hours $dash], 'YYYYMMDD') as dtstart_plus_one,
- to_char(trunc(i.start_date) [cal_outlook_gmt_sql $hours $dash], 'YYYYMMDD-HH24MISS') as dtstart_midnight,
- to_char(trunc(i.start_date) + 1 [cal_outlook_gmt_sql $hours $dash], 'YYYYMMDD-HH24MISS') as dtstart_plus_one_midnight,
- nvl(to_char(ii.creation_date [cal_outlook_gmt_sql $hours $dash], 'YYYYMMDD'), to_char(i.creation_date, 'YYYYMMDD')) as creation_date,
- nvl(i.title, ii.title) as title,
- nvl(i.related_link_url, ii.related_link_url) as related_link_url,
- i.description as child_description,
- ii.description as parent_description,
- ii.html_p as parent_html_p,
- i.html_p as child_html_p,
- ii.interval_type, ii.every_nth_interval, ii.days_of_week,
- to_char(ii.repeat_until [cal_outlook_gmt_sql $hours $dash], 'YYYYMMDD') as repeat_until
- from cal_items i, cal_items ii
- where i.item_id = :item_id
- and i.parent_id = ii.item_id(+)
- "
-
- # resolve parent and child description, html_p
- if {[string equal $parent_html_p "t"] && ![empty_string_p $parent_description]} {
- set parent_description [ad_html_to_text $parent_description]
- }
-
- if {[string equal $child_html_p "t"] && ![empty_string_p $child_description]} {
- set child_description [ad_html_to_text $child_description]
- }
-
- # make sure that whitespace isn't being mistaken as a non-empty-string
- set parent_description [string trim $parent_description]
- set child_description [string trim $child_description]
-
- set description ""
- if {![empty_string_p $parent_description] && ![string equal $parent_description $child_description] && ![empty_string_p $child_description]} {
- # both are set, just combine them
- set description "$parent_description
- $child_description"
- } elseif {![empty_string_p $parent_description] && [empty_string_p $child_description]} {
- set description $parent_description
- } else {
- set description $child_description
- }
-
- if ![empty_string_p [string trim $related_link_url]] {
- set DESCRIPTION "$description
- $related_link_url"
- } else {
- set DESCRIPTION $description
- }
-
- # format date_times
-
- if { [empty_string_p $dtend] } {
-
- #DRB: If it's a recurring event we need to kludge around the fact
- #that we're not generating full timezone information by passing the
- #a DATETIME to Outlook for "no time specified" events. OpenACS 4
- #should fix this, read RFC2445 for information. With proper timezone
- #information, the DATE form should be used. This current hack, done
- #for Sloan, only works for folks who are in the same timezone as the
- #server.
-
- if { $all_occurences_p && ![empty_string_p $interval_type] } {
- set DTSTART ":[cal_ics_date_time_format $dtstart_midnight]"
- set DTEND ":[cal_ics_date_time_format $dtstart_plus_one_midnight]"
- } else {
- set DTSTART ";VALUE=DATE:[string range $dtstart 0 7]"
- set DTEND ";VALUE=DATE:[string range $dtstart_plus_one 0 7]"
- }
- } else {
- set DTSTART ":[cal_ics_date_time_format $dtstart]"
- set DTEND ":[cal_ics_date_time_format $dtend]"
- }
-
- # making the CLASS=Public for now, but perhaps it should default to private
- # this is ugly, but it might help get this working
-
- # DRB: removed the ORGANIZER:MAILTO: line as Outlook would complain when
- # importing a file containing the line if it weren't already open. When open
- # Outlook would import the file just fine - presumably there are two separate
- # paths within Outlook to handle the two cases.
-
- set ics_event "
- BEGIN:VCALENDAR\r\nPRODID:-//ArsDigita//ACES 3.4 MIMEDIR//EN\r\nVERSION:2.0\r\nMETHOD:PUBLISH\r\nBEGIN:VEVENT\r\nDTSTART$DTSTART\r\n"
-
- if { [info exists DTEND] } {
- append ics_event "DTEND$DTEND\r\n"
- }
-
- if { $all_occurences_p && ![empty_string_p $interval_type] } {
-
- set recur_rule "RRULE:FREQ="
-
- switch -glob $interval_type {
- day { append recur_rule "DAILY" }
- week { append recur_rule "WEEKLY" }
- *month* { append recur_rule "MONTHLY"}
- year { append recur_rule "YEARLY"}
- }
-
- if { $interval_type == "week" && ![empty_string_p $days_of_week] } {
-
- #DRB: Standard indicates ordinal week days are OK, but Outlook
- #only takes two-letter abbreviation form.
-
- append recur_rule ";BYDAY="
- set week_list [list "SU" "MO" "TU" "WE" "TH" "FR" "SA" "SU"]
- set sep ""
- set day_list [split $days_of_week " "]
- foreach day $day_list {
- append recur_rule "$sep[lindex $week_list $day]"
- set sep ","
- }
- }
-
- if { ![empty_string_p $every_nth_interval] } {
- append recur_rule ";INTERVAL=$every_nth_interval"
- }
-
- if { ![empty_string_p $repeat_until] } {
- #DRB: this should work with a DATE: type but doesn't with Outlook at least.
- append recur_rule ";UNTIL=$repeat_until"
- append recur_rule "T000000Z"
- }
-
- append ics_event "$recur_rule\r\n"
- }
-
- append ics_event "LOCATION:Not Listed\r\nTRANSP:OPAQUE\r\nSEQUENCE:0\r\nUID:$item_id\r\nDTSTAMP:$creation_date\r\nDESCRIPTION:$DESCRIPTION\r\nSUMMARY:$title\r\nPRIORITY:5\r\nCLASS:PUBLIC\r\n"
-
- db_foreach cal_item_db_row_map_fs_select "
- select /*+ RULE */ c.on_what_id as file_id,
- fs.file_title as file_title,
- fs.parent_id as parent_id,
- fs2.file_title as parent_title,
- fsvl.version_id as version_id,
- fsvl.client_file_name,
- fsvl.n_bytes,
- fsvl.url,
- lower(fsvl.file_type) as file_type
- from cal_item_db_row_map c, fs_files fs,
- fs_versions_latest fsvl, fs_files fs2
- where fs2.file_id(+) = fs.parent_id
- and fs2.deleted_p = 'f'
- and c.item_id=:item_id
- and c.on_what_id=fs.file_id
- and fs.file_id=fsvl.file_id
- and c.on_which_table='FS_FILES'
- and (ad_general_permissions.user_has_row_permission_p ( :logged_in_user_id, 'read', fsvl.version_id, 'FS_VERSIONS' ) = 't'
- or fs.owner_id = :logged_in_user_id)
- and fs.deleted_p='f'
- " {
- if { [empty_string_p $n_bytes] } {
- append ics_event "ATTACH:$url\r\n"
- } else {
- regsub -all {[^-_.0-9a-zA-Z]+} $client_file_name "_" pretty_file_name
- append ics_event "ATTACH:[ad_url]/file-storage/download/$version_id/$pretty_file_name\r\n"
- }
- }
-
- #append ics_event "LOCATION:Not Listed\r\nTRANSP:OPAQUE\r\nSEQUENCE:0\r\nUID:040000008200E00074C5B7101A82E00800000000502D2C0B6B82C0010000000000000000100 00000C738CA6EDDB4DD46850DE477678FF368\r\nDTSTAMP:$creation_date\r\nDESCRIPTION:$DESCRIPTION\r\nSUMMARY:$title\r\nPRIORITY:5\r\nCLASS:PUBLIC\r\nEND:VEVENT\r\nEND:VCALENDAR\r\n"
- append ics_event "END:VEVENT\r\nEND:VCALENDAR\r\n"
- ns_return 200 application/x-msoutlook $ics_event
- }
-
- # ad_register_proc GET /*.ics cal_output_outlook_event
- # ad_register_proc POST /*.ics cal_output_outlook_event
-
}
Index: openacs-4/packages/calendar/tcl/calendar-outlook-procs.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/tcl/calendar-outlook-procs.xql,v
diff -u -r1.2 -r1.3
--- openacs-4/packages/calendar/tcl/calendar-outlook-procs.xql 10 Jul 2002 14:58:25 -0000 1.2
+++ openacs-4/packages/calendar/tcl/calendar-outlook-procs.xql 10 Jul 2002 22:42:19 -0000 1.3
@@ -1,6 +1,16 @@
<?xml version="1.0"?>
<queryset>
+<fullquery name="calendar::outlook::adjust_timezone.adjust_timezone">
+<querytext>
+select to_char(timezone.utc_to_local(tz_id, utc_time), :format)
+from timezones,
+(select timezone.local_to_utc(tz_id, to_date(:timestamp,:format)) as utc_time
+from timezones where tz= :server_tz)
+where tz= :user_tz
+</querytext>
+</fullquery>
+
<fullquery name="calendar::outlook::format_item.select_recurrence">
<querytext>
select