====== pears.calcdiaryextradays ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
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
}
</code>