Re: Calendar Scripts - Quite a complex one

Hi,
yes yes. U understood in a very correct way, as i have 2 tables -
appointments and recurrences. And we are not gonna use PHP.

For future dates, I am not gonna populate, instead I am gonna check for the
recurrences tables for ever appointments and based on the conditions, I am
gonna say how many time that appointment recure in that month and the
timestamp.

To process that I have get all the appointment data and its recurrence
pattern data into the cursor. Is there a way to get the records one by one
from the cursor and calculate it patterns.

CREATE OR REPLACE FUNCTION crm.fn_calendar_daily_activities(timestamp)
RETURNS refcursor AS
'DECLARE
cal_daily_date ALIAS FOR $1;
ref REFCURSOR;

BEGIN
OPEN ref FOR
SELECT ....................

RETURN ref;

END;'
LANGUAGE 'plpgsql' VOLATILE;

How to open the cursor here so that I could check its recurrences pattern.

Please shed some light.

Regards
kumar

----- Original Message -----
From: "Chris Travers" <chris(at)travelamericas(dot)com>
To: "Kumar" <sgnerd(at)yahoo(dot)com(dot)sg>; <josh(at)agliodbs(dot)com>; "Peter Eisentraut"
<peter_e(at)gmx(dot)net>; "psql" <pgsql-sql(at)postgresql(dot)org>
Sent: Wednesday, January 07, 2004 1:19 PM
Subject: Re: [SQL] Calendar Scripts - Quite a complex one

> Hi all;
>
> If I understand Kumar's post correctly, he is having some question
relating
> to the issue of even recurrance. I would highly suggest reading the
> ICalendar RFC (RFC 2445) as it has some interesting ideas on the subject.
> HERMES (my app with appointment/calendar functionality) doesn't yet
support
> appointment recurrance, and I have not formalized my approach to this.
> However, here is the general approach I have been looking at:
>
> 1: Have a separate table of recurrance rules (1:1 with appointments) or
have
> a recurrance datatype.
>
> 2: Build some functions to calculate dates and times when the appointment
> would recurr. You can also have a "Recur Until" field so you can limit
your
> searches this way.
>
> 3: Use a view to find recurring appointments on any given day.
>
> This avoids a very nasty problem in the prepopulation approach-- that of a
> cancelled recurring meeting. How do you cancel ALL appropriate instances
of
> the meeting while leaving those that occured in the past available for
> records?
>
> Kumar-- if you are working with PHP, I would be happy to work with you in
> this endevor so that the same functionality can exist in my open source
> (GPL'd) application. I think that the source for this would likely be one
> of those things that might be best LGPL'd if added to my app.
>
> Best Wishes,
> Chris Travers
>
> ----- Original Message -----
> From: "Kumar" <sgnerd(at)yahoo(dot)com(dot)sg>
> To: <josh(at)agliodbs(dot)com>; "Peter Eisentraut" <peter_e(at)gmx(dot)net>; "psql"
> <pgsql-sql(at)postgresql(dot)org>
> Sent: Wednesday, January 07, 2004 1:06 PM
> Subject: Re: [SQL] Calendar Scripts - Quite a complex one
>
>
> > Hi,
> >
> > The complexity comes while scheduling the appointments. Let us say, I
have
> > scheduled so many meetings in my calendar of various schedules like
daily,
> 3
> > days once, weekly, bi weekly. monthly, bi monthly, etc.
> >
> > While I open the calendar for end of this year (say Dec 2004), I need to
> > show those meetings in my calendar, but I have data until Jan 2004.
> >
> > What is the best way to show it. Populating the records from Jan 2004 to
> Dec
> > 2004 in the pgsql function and display it in the calendar, or just write
a
> > query to generate temporary records only for that Dec 2004 and not
storing
> > them at the database.
> >
> > Please shed some idea.
> >
> > Regards
> > Kumar
> >
> > ----- Original Message -----
> > From: "Josh Berkus" <josh(at)agliodbs(dot)com>
> > To: "Peter Eisentraut" <peter_e(at)gmx(dot)net>; "Kumar" <sgnerd(at)yahoo(dot)com(dot)sg>;
> > "psql" <pgsql-sql(at)postgresql(dot)org>
> > Sent: Wednesday, January 07, 2004 3:43 AM
> > Subject: Re: [SQL] Calendar Scripts - Quite a complex one
> >
> >
> > Peter,
> >
> > > You can probably lift out the complete calendar functionality from an
> > > existing groupware solution, say, www.egroupware.org. I'm not sure
> > > whether it's practical to do the calendar things in the database,
since
> > > you will also need a significant amount of intelligence in the client
> > > to display reasonable calendar graphics, for instance.
> >
> > But all of the appointments, holidays, etc can and should be stored in
the
> > database, and by using function programming one can automate generating
> all
> > of the raw data for the calendar graphics. We do this with our legal
> > calendaring app.
> >
> > --
> > -Josh Berkus
> > Aglio Database Solutions
> > San Francisco
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
> >
> >