pears.calcdiaryextradays

CREATE PROCEDURE "pears"."calcdiaryextradays"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "sstaffid" CHAR(20),IN "dfrom" DATE,IN "dto" DATE ) 
BEGIN
  DECLARE "getdiary" dynamic scroll cursor FOR SELECT "diaryid","dateadd"("day",1,"date"("diaryfrom")) AS "Day2","diaryto" FROM "diary"
      WHERE "staffid" = "sstaffid" AND "day2" <= "dto" AND "diaryto" >= "dfrom" AND "date"("diaryto") > "date"("diaryfrom")
      ORDER BY "diaryfrom" ASC FOR READ ONLY;
  DECLARE "gid" CHAR(20);
  DECLARE "gfrom" DATE;
  DECLARE "gto" TIMESTAMP;
  DECLARE "testdate" DATE;
  DECLARE "xminutes" SMALLINT;
  DELETE FROM "diaryextraday";
  OPEN "getdiary";
  fetch FIRST "getdiary" INTO "gid","gfrom","gto";
  "outerloop": loop
    IF sqlcode <> 0 THEN leave "outerloop"
    END IF;
    SET "testdate" = "gfrom";
    IF "testdate" < "dfrom" THEN
      SET "testdate" = "dfrom"
    END IF;
    "innerloop":
    while "testdate" <= "date"("gto") AND "testdate" <= "dto" loop
      IF "testdate" < "date"("gto") THEN
        SET "xminutes" = 24*60
      ELSE
        SET "xminutes" = "datepart"("hour","gto")*60+"datepart"("minute","gto")
      END IF;
      INSERT INTO "diaryextraday"( "diaryid","diarydate","diaryminutes" ) VALUES( "gid","testdate","xminutes" ) ;
      SET "testdate" = "dateadd"("day",1,"testdate")
    END loop "innerloop";
    fetch NEXT "getdiary" INTO "gid","gfrom",
      "gto"
  END loop "outerloop"
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."calcdiaryextradays" IS 
{CREATE PROCEDURE calcdiaryextradays 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
(IN sstaffid CHAR(20),IN dfrom DATE,IN dto DATE)
BEGIN
  DECLARE getdiary dynamic scroll cursor FOR SELECT diaryid,dateadd(DAY,1,"date"(diaryfrom)) AS Day2,diaryto FROM diary WHERE
      staffid = sstaffid AND day2 <= dto AND diaryto >= dfrom AND "date"(diaryto) > "date"(diaryfrom) ORDER BY
      diaryfrom ASC FOR READ ONLY;
  DECLARE gid CHAR(20);
  DECLARE gfrom DATE;
  DECLARE gto TIMESTAMP;
  DECLARE testdate DATE;
  DECLARE xminutes SMALLINT;
  DELETE FROM diaryextraday;
  OPEN getdiary;
  fetch FIRST getdiary INTO gid,gfrom,gto;
  outerloop: loop
    IF sqlcode <> 0 THEN leave outerloop
    END IF;
    SET testdate=gfrom;
    IF testdate < dfrom THEN
      SET testdate=dfrom
    END IF;
    innerloop:
    while testdate <= "date"(gto) AND testdate <= dto loop
      IF testdate < "date"(gto) THEN
        SET xminutes=24*60
      ELSE
        SET xminutes=datepart(HOUR,gto)*60+datepart(MINUTE,gto)
      END IF;
      INSERT INTO diaryextraday(diaryid,diarydate,diaryminutes) VALUES(gid,testdate,xminutes);
      SET testdate=dateadd(DAY,1,testdate)
    END loop innerloop;
    fetch NEXT getdiary INTO gid,gfrom,
      gto
  END loop outerloop END
}