Index: openacs-4/packages/calendar/www/view-list-display.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/www/view-list-display.tcl,v diff -u -r1.5 -r1.6 --- openacs-4/packages/calendar/www/view-list-display.tcl 13 Aug 2003 16:08:33 -0000 1.5 +++ openacs-4/packages/calendar/www/view-list-display.tcl 14 Aug 2003 15:44:08 -0000 1.6 @@ -62,8 +62,8 @@ set pretty_end_time [lc_time_fmt $ansi_end_date "%X"] set pretty_today [lc_time_fmt $ansi_today "%x"] - set start_date_seconds [clock scan $pretty_start_date] - set today_seconds [clock scan $pretty_today] + set start_date_seconds [clock scan [lc_time_fmt $ansi_start_date "%Y-%m-%d"]] + set today_seconds [clock scan [lc_time_fmt $ansi_today "%Y-%m-%d"]] # Adjust the display of no-time items if {[dt_no_time_p -start_time $pretty_start_date -end_time $pretty_end_date]} { Index: openacs-4/packages/calendar/www/view-month-display-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/www/Attic/view-month-display-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/calendar/www/view-month-display-oracle.xql 14 Aug 2003 15:44:08 -0000 1.1 @@ -0,0 +1,34 @@ + + + + oracle8.1.6 + + + + select to_char(start_date, :ansi_date_format) as ansi_start_date, + to_char(end_date, :ansi_date_format) as ansi_end_date, + nvl(e.name, a.name) as name, + nvl(e.description, a.description) as description, + nvl(e.status_summary, a.status_summary) as status_summary, + e.event_id as item_id, + cals.calendar_id, + cals.calendar_name + from acs_activities a, + acs_events e, + timespans s, + time_intervals t, + cal_items ci, + calendars cals + where e.timespan_id = s.timespan_id + and s.interval_id = t.interval_id + and e.activity_id = a.activity_id + and start_date between to_date(:first_date_of_month_system, :ansi_date_format) + and to_date(:last_date_in_month_system, :ansi_date_format) + and cals.package_id= :package_id + and (cals.private_p='f' or (cals.private_p='t' and cals.owner_id= :user_id)) + and cals.calendar_id = ci.on_which_calendar + and e.event_id = ci.cal_item_id + order by ansi_start_date + + + Index: openacs-4/packages/calendar/www/view-month-display-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/www/Attic/view-month-display-postgresql.xql,v diff -u -r1.4 -r1.5 --- openacs-4/packages/calendar/www/view-month-display-postgresql.xql 26 Jul 2003 21:08:16 -0000 1.4 +++ openacs-4/packages/calendar/www/view-month-display-postgresql.xql 14 Aug 2003 15:44:08 -0000 1.5 @@ -5,10 +5,8 @@ - select to_char(start_date, 'J') as julian_start_date, - to_char(start_date, 'YYYY-MM-DD HH24:MI:SS') as ansi_start_date, - to_char(start_date, 'HH24:MI') as ansi_start_time, - to_char(end_date, 'HH24:MI') as ansi_end_time, + select to_char(start_date, :ansi_date_format) as ansi_start_date, + to_char(end_date, :ansi_date_format) as ansi_end_date, coalesce(e.name, a.name) as name, coalesce(e.description, a.description) as description, coalesce(e.status_summary, a.status_summary) as status_summary, @@ -24,18 +22,14 @@ where e.timespan_id = s.timespan_id and s.interval_id = t.interval_id and e.activity_id = a.activity_id - and start_date between to_date(:first_julian_date_of_month,'J') - and to_date(:last_julian_date_in_month,'J') + and start_date between to_timestamp(:first_date_of_month_system, :ansi_date_format) + and to_timestamp(:last_date_in_month_system, :ansi_date_format) and cals.package_id= :package_id and (cals.private_p='f' or (cals.private_p='t' and cals.owner_id= :user_id)) and cals.calendar_id = ci.on_which_calendar and e.event_id = ci.cal_item_id order by ansi_start_date - - - - Index: openacs-4/packages/calendar/www/view-month-display.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/www/view-month-display.tcl,v diff -u -r1.3 -r1.4 --- openacs-4/packages/calendar/www/view-month-display.tcl 26 Jul 2003 21:22:49 -0000 1.3 +++ openacs-4/packages/calendar/www/view-month-display.tcl 14 Aug 2003 15:44:08 -0000 1.4 @@ -1,10 +1,19 @@ # Show a calendar month widget +# +# Parameters: +# +# date (YYYY-MM-DD) - optional if {![info exists date] || [empty_string_p $date]} { - set date [dt_systime] + # Default to todays date in the users (the connection) timezone + set server_now_time [dt_systime] + set user_now_time [lc_time_system_to_conn $server_now_time] + set date [lc_time_fmt $user_now_time "%x"] } dt_get_info $date +set ansi_date_format "YYYY-MM-DD HH24:MI:SS" + set package_id [ad_conn package_id] set user_id [ad_conn user_id] set today_date [dt_sysdate] @@ -16,6 +25,11 @@ multirow append weekday_names $weekday } +# Get the beginning and end of the month in the system timezone +set first_date_of_month [dt_julian_to_ansi $first_julian_date_of_month] +set first_date_of_month_system [lc_time_conn_to_system "$first_date_of_month 00:00:00"] +set last_date_in_month [dt_julian_to_ansi $last_julian_date_in_month] +set last_date_in_month_system [lc_time_conn_to_system "$last_date_in_month 23:59:59"] calendar::i18n_display_parameters @@ -41,6 +55,16 @@ set current_day $first_julian_date_of_month db_foreach select_monthly_items {} { + + # Convert from system timezone to user timezone + set ansi_start_date [lc_time_system_to_conn $ansi_start_date] + set ansi_end_date [lc_time_system_to_conn $ansi_end_date] + + set ansi_start_time [lc_time_fmt $ansi_start_date "%X"] + set ansi_end_time [lc_time_fmt $ansi_end_date "%X"] + + set julian_start_date [dt_ansi_to_julian_single_arg $ansi_start_date] + if {$current_day < $julian_start_date} { for {} {$current_day < $julian_start_date} {incr current_day} { array set display_information [calendar::get_month_multirow_information -current_day $current_day -today_julian_date $today_julian_date -first_julian_date_of_month $first_julian_date_of_month] Index: openacs-4/packages/calendar/www/view-one-day-display-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/www/Attic/view-one-day-display-oracle.xql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/calendar/www/view-one-day-display-oracle.xql 11 Aug 2003 12:14:16 -0000 1.2 +++ openacs-4/packages/calendar/www/view-one-day-display-oracle.xql 14 Aug 2003 15:44:08 -0000 1.3 @@ -2,54 +2,80 @@ oracle8.1.6 - + -select to_char(start_date, 'YYYY-MM-DD HH24:MI:SS') as ansi_start_date, - to_char(end_date, 'YYYY-MM-DD HH24:MI:SS') as ansi_end_date, - nvl(e.name, a.name) as name, + select nvl(e.name, a.name) as name, nvl(e.status_summary, a.status_summary) as status_summary, e.event_id as item_id, - (select type from cal_item_types where item_type_id= cal_items.item_type_id) as item_type, - on_which_calendar as calendar_id, - (select calendar_name from calendars - where calendar_id = on_which_calendar) - as calendar_name + (select type from cal_item_types where item_type_id= ci.item_type_id) as item_type, + cals.calendar_id, + cals.calendar_name from acs_activities a, acs_events e, timespans s, time_intervals t, - cal_items + cal_items ci, + calendars cals where e.timespan_id = s.timespan_id and s.interval_id = t.interval_id and e.activity_id = a.activity_id and start_date between - to_date(:current_date,:date_format) and - (to_date(:current_date,:date_format) + (24 - 1/3600)/24) -and cal_items.cal_item_id= e.event_id -and e.event_id -in ( - select cal_item_id - from cal_items - where on_which_calendar in ([join $calendar_id_list ","]) - ) - + to_date(:current_date_system,:ansi_date_format) and + (to_date(:current_date_system,:ansi_date_format) + (24 - 1/3600)/24) +and ci.cal_item_id = e.event_id +and to_char(start_date, 'HH24:MI') = '00:00' +and to_char(end_date, 'HH24:MI') = '00:00' +and cals.package_id= :package_id +and (cals.private_p='f' or (cals.private_p='t' and cals.owner_id= :user_id)) +and cals.calendar_id = ci.on_which_calendar +and e.event_id = ci.cal_item_id - + + + select to_char(start_date, :ansi_date_format) as ansi_start_date, + to_char(end_date, :ansi_date_format) as ansi_end_date, + nvl(e.name, a.name) as name, + nvl(e.status_summary, a.status_summary) as status_summary, + e.event_id as item_id, + (select type from cal_item_types where item_type_id= ci.item_type_id) as item_type, + cals.calendar_id, + cals.calendar_name +from acs_activities a, + acs_events e, + timespans s, + time_intervals t, + cal_items ci, + calendars cals +where e.timespan_id = s.timespan_id +and s.interval_id = t.interval_id +and e.activity_id = a.activity_id +and start_date between + to_date(:current_date_system,:ansi_date_format) and + (to_date(:current_date_system,:ansi_date_format) + (24 - 1/3600)/24) +and ci.cal_item_id = e.event_id +and (to_char(start_date, 'HH24:MI') <> '00:00' or + to_char(end_date, 'HH24:MI') <> '00:00') +and cals.package_id= :package_id +and (cals.private_p='f' or (cals.private_p='t' and cals.owner_id= :user_id)) +and cals.calendar_id = ci.on_which_calendar +and e.event_id = ci.cal_item_id +order by to_char(start_date,'HH24') + + + select to_char(to_date(:current_date, 'yyyy-mm-dd'), 'Day, DD Month YYYY') as day_of_the_week, -to_char(to_date(:current_date, 'yyyy-mm-dd') - cast('1 day' as interval), 'yyyy-mm-dd') +to_char((to_date(:current_date, 'yyyy-mm-dd') - 1), 'yyyy-mm-dd') as yesterday, -to_char(to_date(:current_date, 'yyyy-mm-dd') + cast('1 day' as interval), 'yyyy-mm-dd') +to_char((to_date(:current_date, 'yyyy-mm-dd') + 1), 'yyyy-mm-dd') as tomorrow from dual - - Index: openacs-4/packages/calendar/www/view-one-day-display-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/www/Attic/view-one-day-display-postgresql.xql,v diff -u -r1.6 -r1.7 --- openacs-4/packages/calendar/www/view-one-day-display-postgresql.xql 13 Aug 2003 16:08:33 -0000 1.6 +++ openacs-4/packages/calendar/www/view-one-day-display-postgresql.xql 14 Aug 2003 15:44:08 -0000 1.7 @@ -5,9 +5,7 @@ - select to_char(start_date, 'YYYY-MM-DD HH24:MI:SS') as ansi_start_date, - to_char(end_date, 'YYYY-MM-DD HH24:MI:SS') as ansi_end_date, - coalesce(e.name, a.name) as name, + select coalesce(e.name, a.name) as name, coalesce(e.status_summary, a.status_summary) as status_summary, e.event_id as item_id, (select type from cal_item_types where item_type_id= cal_items.item_type_id) as item_type, @@ -23,8 +21,8 @@ and s.interval_id = t.interval_id and e.activity_id = a.activity_id and start_date between - to_date(:current_date,:date_format) and - to_date(:current_date,:date_format) + cast('23 hours 59 minutes 59 seconds' as interval) + to_date(:current_date_system,:ansi_date_format) and + to_date(:current_date_system,:ansi_date_format) + cast('23 hours 59 minutes 59 seconds' as interval) and cal_items.cal_item_id= e.event_id and to_char(start_date, 'HH24:MI') = '00:00' and to_char(end_date, 'HH24:MI') = '00:00' @@ -37,10 +35,8 @@ - select to_char(start_date, 'HH24') as start_hour, - to_char(end_date, 'HH24') as end_hour, - to_char(start_date, 'HH24:MI') as start_time, - to_char(end_date, 'HH24:MI') as end_time, + select to_char(start_date, :ansi_date_format) as ansi_start_date, + to_char(end_date, :ansi_date_format) as ansi_end_date, coalesce(e.name, a.name) as name, coalesce(e.status_summary, a.status_summary) as status_summary, e.event_id as item_id, @@ -57,16 +53,16 @@ and s.interval_id = t.interval_id and e.activity_id = a.activity_id and start_date between - to_date(:current_date,:date_format) and - to_date(:current_date,:date_format) + cast('23 hours 59 minutes 59 seconds' as interval) + to_date(:current_date_system,:ansi_date_format) and + to_date(:current_date_system,:ansi_date_format) + cast('23 hours 59 minutes 59 seconds' as interval) and cal_items.cal_item_id= e.event_id -and to_char(start_date, 'HH24:MI') <> '00:00' -and to_char(end_date, 'HH24:MI') <> '00:00' +and (to_char(start_date, 'HH24:MI') <> '00:00' or + to_char(end_date, 'HH24:MI') <> '00:00') and cals.package_id= :package_id and (cals.private_p='f' or (cals.private_p='t' and cals.owner_id= :user_id)) and cals.calendar_id = ci.on_which_calendar and e.event_id = ci.cal_item_id -order by start_hour +order by to_char(start_date,'HH24') @@ -82,5 +78,4 @@ - Index: openacs-4/packages/calendar/www/view-one-day-display.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/www/view-one-day-display.tcl,v diff -u -r1.6 -r1.7 --- openacs-4/packages/calendar/www/view-one-day-display.tcl 13 Aug 2003 16:08:33 -0000 1.6 +++ openacs-4/packages/calendar/www/view-one-day-display.tcl 14 Aug 2003 15:44:08 -0000 1.7 @@ -1,12 +1,23 @@ # Show calendar items per one day. +# +# Parameters: +# +# date (YYYY-MM-DD) - optional -# This needs to be internationalized! -- Dirk -set date_format "YYYY-MM-DD HH24:MI" +set ansi_date_format "YYYY-MM-DD HH24:MI:SS" if {[empty_string_p $date]} { - set date [dt_sysdate] + # Default to todays date in the users (the connection) timezone + set server_now_time [dt_systime] + set user_now_time [lc_time_system_to_conn $server_now_time] + set date [lc_time_fmt $user_now_time "%x"] } + set current_date $date + +set current_date_system "$current_date 00:00:00" +#set current_date_system [lc_time_conn_to_system "$date 00:00:00"] + set package_id [ad_conn package_id] set user_id [ad_conn user_id] @@ -25,8 +36,16 @@ set day_items_per_hour {} db_foreach select_day_items_with_time {} { - set start_hour [string trimleft $start_hour "0"] - set end_hour [string trimleft $end_hour "0"] + # Convert to user's timezone + set ansi_start_date [lc_time_system_to_conn $ansi_start_date] + set ansi_end_date [lc_time_system_to_conn $ansi_end_date] + + set start_hour [lc_time_fmt $ansi_start_date "%H"] + set end_hour [lc_time_fmt $ansi_end_date "%H"] + + set start_time [lc_time_fmt $ansi_start_date "%H:%M"] + set end_time [lc_time_fmt $ansi_end_date "%H:%M"] + for { set item_current_hour $start_hour } { $item_current_hour < $end_hour } { incr item_current_hour } { if {$start_hour == $item_current_hour} { lappend day_items_per_hour [list $item_current_hour $name $item_id $calendar_name $status_summary $start_hour $end_hour $start_time $end_time] Index: openacs-4/packages/calendar/www/view-week-display-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/www/view-week-display-oracle.xql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/calendar/www/view-week-display-oracle.xql 26 Jul 2003 21:58:28 -0000 1.1 +++ openacs-4/packages/calendar/www/view-week-display-oracle.xql 14 Aug 2003 15:44:08 -0000 1.2 @@ -5,105 +5,69 @@ - select to_char(to_date(:current_date, 'yyyy-mm-dd'), 'D') + select to_char(to_date(:start_date, 'YYYY-MM-DD'), 'D') as day_of_the_week, - to_char(next_day(to_date(:current_date, 'yyyy-mm-dd')-7, 'SUNDAY')) + to_char(next_day(to_date(:start_date, 'YYYY-MM-DD')-7, 'SUNDAY')) as sunday_of_the_week, - to_char(next_day(to_date(:current_date, 'yyyy-mm-dd'), 'Saturday')) + to_char(next_day(to_date(:start_date, 'YYYY-MM-DD'), 'Saturday')) as saturday_of_the_week from dual -select to_char(start_date, 'J') as start_date_julian, - to_char(start_date,'HH24:MI') as start_date, - to_char(end_date,'HH24:MI') as end_date, - to_char(start_date, 'YYYY-MM-DD HH24:MI:SS') as ansi_start_date, - to_char(end_date, 'YYYY-MM-DD HH24:MI:SS') as ansi_end_date, +select to_char(start_date, :ansi_date_format) as ansi_start_date, + to_char(end_date, :ansi_date_format) as ansi_end_date, nvl(e.name, a.name) as name, nvl(e.status_summary, a.status_summary) as status_summary, e.event_id as item_id, - (select type from cal_item_types where item_type_id= cal_items.item_type_id) as item_type, - on_which_calendar as calendar_id, - (select calendar_name from calendars - where calendar_id = on_which_calendar) - as calendar_name + (select type from cal_item_types where item_type_id= ci.item_type_id) as item_type, + cals.calendar_id, + cals.calendar_name from acs_activities a, acs_events e, timespans s, time_intervals t, - cal_items + cal_items ci, + calendars cals where e.timespan_id = s.timespan_id and s.interval_id = t.interval_id and e.activity_id = a.activity_id -and e.event_id = cal_items.cal_item_id -and start_date >= to_date(:first_day_of_week_julian, 'J') -and start_date <= to_date(:first_day_of_week_julian + 7, 'J') -and e.event_id -in ( - select cal_item_id - from cal_items - where on_which_calendar in ([join $calendar_id_list ","]) - ) -order by start_date +and start_date between + to_date(:sunday_of_the_week_system, :ansi_date_format) and + to_date(:saturday_of_the_week_system, :ansi_date_format) +and cals.package_id= :package_id +and (cals.private_p='f' or (cals.private_p='t' and cals.owner_id= :user_id)) +and cals.calendar_id = ci.on_which_calendar +and e.event_id = ci.cal_item_id +order by to_char(start_date, 'J'), to_char(start_date,'HH24:MI') - + -select to_char(to_date(:start_date, 'yyyy-mm-dd'), 'D') -as day_of_the_week, -cast(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday') as date) -as sunday_date, -to_char(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday'),'J') -as sunday_julian, -cast(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday') + cast('1 day' as interval) as date) -as monday_date, -to_char(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday') + cast('1 day' as interval),'J') -as monday_julian, -cast(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday') + cast('2 days' as interval) as date) -as tuesday_date, -to_char(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday') + cast('2 days' as interval),'J') -as tuesday_julian, -cast(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday') + cast('3 days' as interval) as date) -as wednesday_date, -to_char(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday') + cast('3 days' as interval),'J') -as wednesday_julian, -cast(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday') + cast('4 days' as interval) as date) -as thursday_date, -to_char(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday') + cast('4 days' as interval),'J') -as thursday_julian, -cast(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday') + cast('5 days' as interval) as date) -as friday_date, -to_char(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday') + cast('5 days' as interval),'J') -as friday_julian, -cast(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday') + cast('6 days' as interval) as date) -as saturday_date, -to_char(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday') + cast('6 days' as interval),'J') -as saturday_julian, -cast(:start_date::timestamptz - cast('7 days' as interval) as date) as last_week, -to_char(:start_date::timestamptz - cast('7 days' as interval), 'Month DD, YYYY') as last_week_pretty, -cast(:start_date::timestamptz + cast('7 days' as interval) as date) as next_week, -to_char(:start_date::timestamptz + cast('7 days' as interval), 'Month DD, YYYY') as next_week_pretty +select to_char(to_date(:start_date, 'YYYY-MM-DD'), 'D') as day_of_the_week, +next_day(to_date(:start_date, 'YYYY-MM-DD') - 7, 'Sunday') as sunday_date, +to_char(next_day(to_date(:start_date, 'YYYY-MM-DD') - 7, 'Sunday'),'J') as sunday_julian, +next_day(to_date(:start_date, 'YYYY-MM-DD') - 7, 'Sunday') + 1 as monday_date, +to_char(next_day(to_date(:start_date, 'YYYY-MM-DD') - 7, 'Sunday') + 1,'J') as monday_julian, +next_day(to_date(:start_date, 'YYYY-MM-DD') - 7, 'Sunday') + 2 as tuesday_date, +to_char(next_day(to_date(:start_date, 'YYYY-MM-DD') - 7, 'Sunday') + 2,'J') as tuesday_julian, +next_day(to_date(:start_date, 'YYYY-MM-DD') - 7, 'Sunday') + 3 as wednesday_date, +to_char(next_day(to_date(:start_date, 'YYYY-MM-DD') - 7, 'Sunday') + 3,'J') as wednesday_julian, +next_day(to_date(:start_date, 'YYYY-MM-DD') - 7, 'Sunday') + 4 as thursday_date, +to_char(next_day(to_date(:start_date, 'YYYY-MM-DD') - 7, 'Sunday') + 4,'J') as thursday_julian, +next_day(to_date(:start_date, 'YYYY-MM-DD') - 7, 'Sunday') + 5 as friday_date, +to_char(next_day(to_date(:start_date, 'YYYY-MM-DD') - 7, 'Sunday') + 5,'J') as friday_julian, +next_day(to_date(:start_date, 'YYYY-MM-DD') - 7, 'Sunday') + 6 as saturday_date, +to_char(next_day(to_date(:start_date, 'YYYY-MM-DD') - 7, 'Sunday') + 6,'J') as saturday_julian, +to_char(to_date(:start_date) - 7, 'YYYY-MM-DD') as last_week, +to_char(to_date(:start_date) - 7, 'Month DD, YYYY') as last_week_pretty, +to_char(to_date(:start_date) + 7, 'YYYY-MM-DD') as next_week, +to_char(to_date(:start_date) + 7, 'Month DD, YYYY') as next_week_pretty from dual - - -select to_char(to_date(:start_date, 'yyyy-mm-dd'), 'Day, DD Month YYYY') -as day_of_the_week, -to_char(to_date(:start_date, 'yyyy-mm-dd') - cast('1 day' as interval), 'yyyy-mm-dd') -as yesterday, -to_char(to_date(:start_date, 'yyyy-mm-dd') + cast('1 day' as interval), 'yyyy-mm-dd') -as tomorrow -from dual - - - - - - Index: openacs-4/packages/calendar/www/view-week-display-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/www/view-week-display-postgresql.xql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/calendar/www/view-week-display-postgresql.xql 26 Jul 2003 21:13:41 -0000 1.3 +++ openacs-4/packages/calendar/www/view-week-display-postgresql.xql 14 Aug 2003 15:44:08 -0000 1.4 @@ -5,27 +5,20 @@ - select to_char(to_date(:start_date, 'yyyy-mm-dd'), 'D') + select to_char(to_date(:start_date, 'YYYY-MM-DD'), 'D') as day_of_the_week, - to_char(next_day(to_date(:start_date, 'yyyy-mm-dd')- '1 week'::interval, 'Sunday'), 'YYYY-MM-DD') + to_char(next_day(to_date(:start_date, 'YYYY-MM-DD')- '1 week'::interval, 'Sunday'), 'YYYY-MM-DD') as sunday_of_the_week, - to_char(next_day(to_date(:start_date, 'yyyy-mm-dd'), 'Saturday'), 'YYYY-MM-DD') + to_char(next_day(to_date(:start_date, 'YYYY-MM-DD'), 'Saturday'), 'YYYY-MM-DD') as saturday_of_the_week from dual -select to_char(start_date, 'J') as start_date_julian, - to_char(start_date, 'D') as day_of_week, - to_char(start_date, 'Day') as start_date_weekday, - to_char(start_date,'YYYY-MM-DD') as start_date, - to_char(end_date,'YYYY-MM-DD') as end_date, - to_char(start_date,'HH24:MI') as start_time, - to_char(end_date,'HH24:MI') as end_time, - to_char(start_date, 'YYYY-MM-DD HH24:MI:SS') as ansi_start_date, - to_char(end_date, 'YYYY-MM-DD HH24:MI:SS') as ansi_end_date, +select to_char(start_date, :ansi_date_format) as ansi_start_date, + to_char(end_date, :ansi_date_format) as ansi_end_date, coalesce(e.name, a.name) as name, coalesce(e.status_summary, a.status_summary) as status_summary, e.event_id as item_id, @@ -42,47 +35,47 @@ and s.interval_id = t.interval_id and e.activity_id = a.activity_id and start_date between - to_date(:sunday_of_the_week,'YYYY-MM-DD') and - to_date(:saturday_of_the_week,'YYYY-MM-DD') + to_date(:sunday_of_the_week_system, :ansi_date_format) and + to_date(:saturday_of_the_week_system, :ansi_date_format) and cals.package_id= :package_id and (cals.private_p='f' or (cals.private_p='t' and cals.owner_id= :user_id)) and cals.calendar_id = ci.on_which_calendar and e.event_id = ci.cal_item_id -order by start_date_julian, start_time +order by to_char(start_date, 'J'), to_char(start_date,'HH24:MI') -select to_char(to_date(:start_date, 'yyyy-mm-dd'), 'D') +select to_char(to_date(:start_date, 'YYYY-MM-DD'), 'D') as day_of_the_week, -cast(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday') as date) +cast(next_day(to_date(:start_date, 'YYYY-MM-DD') - cast('7 days' as interval), 'Sunday') as date) as sunday_date, -to_char(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday'),'J') +to_char(next_day(to_date(:start_date, 'YYYY-MM-DD') - cast('7 days' as interval), 'Sunday'),'J') as sunday_julian, -cast(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday') + cast('1 day' as interval) as date) +cast(next_day(to_date(:start_date, 'YYYY-MM-DD') - cast('7 days' as interval), 'Sunday') + cast('1 day' as interval) as date) as monday_date, -to_char(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday') + cast('1 day' as interval),'J') +to_char(next_day(to_date(:start_date, 'YYYY-MM-DD') - cast('7 days' as interval), 'Sunday') + cast('1 day' as interval),'J') as monday_julian, -cast(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday') + cast('2 days' as interval) as date) +cast(next_day(to_date(:start_date, 'YYYY-MM-DD') - cast('7 days' as interval), 'Sunday') + cast('2 days' as interval) as date) as tuesday_date, -to_char(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday') + cast('2 days' as interval),'J') +to_char(next_day(to_date(:start_date, 'YYYY-MM-DD') - cast('7 days' as interval), 'Sunday') + cast('2 days' as interval),'J') as tuesday_julian, -cast(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday') + cast('3 days' as interval) as date) +cast(next_day(to_date(:start_date, 'YYYY-MM-DD') - cast('7 days' as interval), 'Sunday') + cast('3 days' as interval) as date) as wednesday_date, -to_char(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday') + cast('3 days' as interval),'J') +to_char(next_day(to_date(:start_date, 'YYYY-MM-DD') - cast('7 days' as interval), 'Sunday') + cast('3 days' as interval),'J') as wednesday_julian, -cast(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday') + cast('4 days' as interval) as date) +cast(next_day(to_date(:start_date, 'YYYY-MM-DD') - cast('7 days' as interval), 'Sunday') + cast('4 days' as interval) as date) as thursday_date, -to_char(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday') + cast('4 days' as interval),'J') +to_char(next_day(to_date(:start_date, 'YYYY-MM-DD') - cast('7 days' as interval), 'Sunday') + cast('4 days' as interval),'J') as thursday_julian, -cast(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday') + cast('5 days' as interval) as date) +cast(next_day(to_date(:start_date, 'YYYY-MM-DD') - cast('7 days' as interval), 'Sunday') + cast('5 days' as interval) as date) as friday_date, -to_char(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday') + cast('5 days' as interval),'J') +to_char(next_day(to_date(:start_date, 'YYYY-MM-DD') - cast('7 days' as interval), 'Sunday') + cast('5 days' as interval),'J') as friday_julian, -cast(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday') + cast('6 days' as interval) as date) +cast(next_day(to_date(:start_date, 'YYYY-MM-DD') - cast('7 days' as interval), 'Sunday') + cast('6 days' as interval) as date) as saturday_date, -to_char(next_day(to_date(:start_date, 'yyyy-mm-dd') - cast('7 days' as interval), 'Sunday') + cast('6 days' as interval),'J') +to_char(next_day(to_date(:start_date, 'YYYY-MM-DD') - cast('7 days' as interval), 'Sunday') + cast('6 days' as interval),'J') as saturday_julian, cast(:start_date::timestamptz - cast('7 days' as interval) as date) as last_week, to_char(:start_date::timestamptz - cast('7 days' as interval), 'Month DD, YYYY') as last_week_pretty, @@ -92,19 +85,4 @@ - - -select to_char(to_date(:start_date, 'yyyy-mm-dd'), 'Day, DD Month YYYY') -as day_of_the_week, -to_char(to_date(:start_date, 'yyyy-mm-dd') - cast('1 day' as interval), 'yyyy-mm-dd') -as yesterday, -to_char(to_date(:start_date, 'yyyy-mm-dd') + cast('1 day' as interval), 'yyyy-mm-dd') -as tomorrow -from dual - - - - - - Index: openacs-4/packages/calendar/www/view-week-display.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/www/view-week-display.tcl,v diff -u -r1.3 -r1.4 --- openacs-4/packages/calendar/www/view-week-display.tcl 26 Jul 2003 21:22:49 -0000 1.3 +++ openacs-4/packages/calendar/www/view-week-display.tcl 14 Aug 2003 15:44:08 -0000 1.4 @@ -1,19 +1,33 @@ # Show calendar items per one week. +# +# Parameters: +# +# date (YYYY-MM-DD) - optional if {[empty_string_p $date]} { - set date [dt_sysdate] + # Default to todays date in the users (the connection) timezone + set server_now_time [dt_systime] + set user_now_time [lc_time_system_to_conn $server_now_time] + set date [lc_time_fmt $user_now_time "%x"] } +set package_id [ad_conn package_id] +set user_id [ad_conn user_id] + +set ansi_date_format "YYYY-MM-DD HH24:MI:SS" + set start_date $date + # If we were given no calendars, we assume we display the # private calendar. It makes no sense for this to be called with # no data whatsoever. if {[empty_string_p $calendar_id_list]} { set calendar_id_list [list [calendar_have_private_p -return_id 1 [ad_get_user_id]]] } -set package_id [ad_conn package_id] -set user_id [ad_conn user_id] +# Convert date from user timezone to system timezone +#set system_start_date [lc_time_conn_to_system "$date 00:00:00"] + db_1row select_weekday_info {} db_1row select_week_info {} @@ -25,7 +39,24 @@ # Loop through the calendars multirow create week_items name item_id start_date calendar_name status_summary day_of_week start_date_weekday start_time end_time no_time_p +# Convert date from user timezone to system timezone +set sunday_of_the_week_system [lc_time_conn_to_system "$sunday_of_the_week 00:00:00"] +set saturday_of_the_week_system [lc_time_conn_to_system "$saturday_of_the_week 00:00:00"] + db_foreach select_week_items {} { + # Convert from system timezone to user timezone + set ansi_start_date [lc_time_system_to_conn $ansi_start_date] + set ansi_end_date [lc_time_system_to_conn $ansi_end_date] + + set day_of_week [lc_time_fmt $ansi_start_date "%f"] + set start_date_weekday [lc_time_fmt $ansi_start_date "%A"] + + set start_date [lc_time_fmt $ansi_start_date "%x"] + set end_date [lc_time_fmt $ansi_end_date "%x"] + + set start_time [lc_time_fmt $ansi_start_date "%X"] + set end_time [lc_time_fmt $ansi_end_date "%X"] + if {$day_of_week > $current_weekday} { # need to add dummy entries to show all days for { } { $current_weekday < $day_of_week } { incr current_weekday } {