pears.CreateICSFile

CREATE FUNCTION "pears"."CreateICSFile"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "puid" CHAR(20),IN "pLocation" CHAR(200),IN "pnotes" long VARCHAR,IN "pStartTime" TIMESTAMP,
  IN "pEndTime" TIMESTAMP,IN "pdescription" CHAR(200),IN "ppriority" SMALLINT,
  IN "precurrance" SMALLINT,IN "ppopup" SMALLINT,IN "ppopuptype" CHAR(1) ) 
RETURNS long VARCHAR
BEGIN
  DECLARE "rv" long VARCHAR;
  DECLARE @createdstr CHAR(20);
  DECLARE @startstr CHAR(20);
  DECLARE @endstr CHAR(20);
  DECLARE @uid CHAR(20);
  DECLARE "crlf" CHAR(2);
  SET "rv" = '';
  SET "crlf" = "char"(13)+"char"(10);
  IF "pdescription" = '' THEN
    RETURN
  END IF;
  IF "pstarttime" IS NULL THEN
    RETURN
  END IF;
  IF "isnull"("puid",'') = '' THEN
    SET @uid = "uniquekey"('x')
  ELSE SET @uid = "puid"
  END IF;
  SET "rv" = 'BEGIN:VCALENDAR'+"crlf";
  SET "rv" = "rv"+'PRODID:-//IQX//EN'+"crlf";
  SET "rv" = "rv"+'VERSION:2.0'+"crlf";
  SET "rv" = "rv"+'BEGIN:VEVENT'+"crlf";
  SET "rv" = "rv"+'UID:'+@uid+"crlf";
  SELECT "dateformat"(CURRENT utc TIMESTAMP,'yyyymmddThhnn00'),"dateformat"("pstarttime",'yyyymmddThhnn00'),
    "dateformat"("isnull"("pendtime","pstarttime"),'yyyymmddThhnn00')
    INTO @createdstr,@startstr,@endstr;
  SET "rv" = "rv"+'CREATED:'+@createdstr+"crlf";
  IF "trim"("isnull"("pnotes",'')) <> '' THEN
    SET "rv" = "rv"+'DESCRIPTION:'+"pnotes"+"crlf"
  END IF;
  SET "rv" = "rv"+'DTSTAMP:'+@createdstr+"crlf";
  SET "rv" = "rv"+'DTSTART:'+@startstr+"crlf";
  SET "rv" = "rv"+'DTEND:'+@endstr+"crlf";
  IF "isnull"("plocation",'') <> '' THEN
    SET "rv" = "rv"+'LOCATION:'+"plocation"+"crlf"
  END IF;
  IF "isnull"("ppriority",0) > 0 THEN
    SET "rv" = "rv"+'PRIORITY:'+"string"("ppriority")+"crlf"
  END IF;
  IF "isnull"("precurrance",0) > 0 THEN
    SET "rv" = "rv"+'RRULE:FREQ=DAILY;COUNT='+"string"("precurrance")+';BYDAY=MO'+"crlf"
  END IF;
  SET "rv" = "rv"+'SEQUENCE:0'+"crlf";
  SET "rv" = "rv"+'SUMMARY:'+"pdescription"+"crlf";
  IF("isnull"("ppopup",0) > 0) AND("isnull"("ppopuptype",'') IN( 'D','M' ) ) THEN
    SET "rv" = "rv"+'BEGIN:VALARM'+"crlf";
    SET "rv" = "rv"+'TRIGGER:-PT'+"string"("ppopup")+"ppopuptype"+"crlf";
    SET "rv" = "rv"+'DESCRIPTION:Reminder'+"crlf";
    SET "rv" = "rv"+'END:VALARM'+"crlf"
  END IF;
  SET "rv" = "rv"+'END:VEVENT'+"crlf";
  SET "rv" = "rv"+'END:VCALENDAR';
  RETURN "rv"
END