====== pears.AWRInsertWeekly ======
<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"."AWRInsertWeekly"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "StartDate" date,in "enddate" date,in "AWRtype" char(1),in "IDtype" char(1),in "PersonorCompanyid" char(20),in @AWRJobMasterid char(20),in @notes long varchar,in "tsid" char(20) default null ) 
begin
  declare "AWRAct" integer;
  declare "StartPeriod" integer;
  declare "EndPeriod" integer;
  // init
  set "StartPeriod" = "weekcontaining"("startdate");
  set "EndPeriod" = "weekcontaining"("enddate");
  if "IDType" = 'P' then
    select "AWRAction" into "AWRAct" from "tempshiftunavailablereason" where "tempshiftunavailablereasonid" = "AWRtype"
  end if;
  // loop
  while "StartPeriod" <= "EndPeriod" loop
    case "IDtype" when 'C' then
      if not exists(select * from "AWRWeeklyDetail" where "companyid" = "PersonorCompanyid" and "period" = "StartPeriod" and "qualtype" = 1 and "AWRreason" = "AWRtype") then
        insert into "AWRWeeklyDetail"( "AWRWeeklyDetailID","companyid","period","QualType",
          "AWRreason","WhoEntered","notes" ) values( "uniquekey"('x'),"PersonorCompanyid","StartPeriod",1,
          "AWRtype","userstaffid",@notes ) 
      end if when 'P' then
      if not exists(select * from "AWRWeeklyDetail" where "personid" = "PersonorCompanyid" and "period" = "StartPeriod" and "qualtype" = "AWRAct" and "AWRreason" = "AWRtype") then
        insert into "AWRWeeklyDetail"( "AWRWeeklyDetailID","personid","period","QualType",
          "AWRreason","WhoEntered","notes" ) values( "uniquekey"('x'),"PersonorCompanyid","StartPeriod","AWRAct",
          "AWRtype","userstaffid",@notes ) 
      end if when 'H' then
      if not exists(select * from "AWRWeeklyDetail" where "personid" = "PersonorCompanyid" and "period" = "StartPeriod" and "qualtype" = 1 and "AWRreason" = '¬') then
        insert into "AWRWeeklyDetail"( "AWRWeeklyDetailID","personid","period","QualType",
          "AWRreason","WhoEntered","notes" ) values( "uniquekey"('x'),"PersonorCompanyid","StartPeriod",1,'¬',
          "userstaffid",@notes ) 
      end if when 'T' then
      insert into "AWRWeeklyDetail"( "AWRWeeklyDetailID","AWRJobMasterid","period","QualType",
        "AWRreason","WhoEntered","notes","TempTimesheetID" ) values( "uniquekey"('x'),@AWRJobMasterid,"StartPeriod",2,'`',
        "userstaffid",@notes,"tsid" ) 
    when 'O' then
      if not exists(select * from "AWRWeeklyDetail" where "AWRJobMasterid" = @AWRJobMasterid and "period" = "StartPeriod" and "qualtype" = 2 and "AWRreason" = "AWRType") then
        insert into "AWRWeeklyDetail"( "AWRWeeklyDetailID","AWRJobMasterid","period","QualType",
          "AWRreason","WhoEntered","notes" ) values( "uniquekey"('x'),@AWRJobMasterid,"StartPeriod",2,
          "AWRtype","userstaffid",@notes ) 
      end if
    end case;
    set "StartPeriod" = "weekcontaining"("dateadd"("week",1,"weekenddate"("truncnum"("StartPeriod"/100,0),"remainder"("StartPeriod",100))))
  end loop
-- AWRtype = Unavailability type
-- IDtype = P - person, C - company, O - other agency work H - holiday T - timesheet
-- person types insert personid jury service etc records
-- company types insert companyid shutdown/strike records
-- use passed one for other agency work and insert records
-- called by 
-- 1 trigger on tempshift unavailability entry
-- 2 company add AWR data button
-- 3 person add AWR data button
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."AWRInsertWeekly" IS 
{create procedure AWRInsertWeekly 

/* Application Maintained Function / Procedure - DO NOT EDIT*/

(in StartDate date,in enddate date,in AWRtype char(1),in IDtype char(1),in PersonorCompanyid char(20),in @AWRJobMasterid char(20), in @notes long varchar, in tsid char(20) default null)
begin
  declare AWRAct integer;
  declare StartPeriod integer;
  declare EndPeriod integer;
  // init
  set StartPeriod=weekcontaining(startdate);
  set EndPeriod=weekcontaining(enddate);
  if IDType = 'P' then
    select AWRAction into AWRAct from tempshiftunavailablereason where tempshiftunavailablereasonid = AWRtype
  end if;
  // loop
  while StartPeriod <= EndPeriod loop
    case IDtype when 'C' then
      if not exists(select * from AWRWeeklyDetail where companyid = PersonorCompanyid and period = StartPeriod and qualtype = 1 and AWRreason = AWRtype) then
        insert into AWRWeeklyDetail( AWRWeeklyDetailID,companyid,period,QualType,
          AWRreason,WhoEntered, notes) values( uniquekey('x'),PersonorCompanyid,StartPeriod,1,
          AWRtype,userstaffid, @notes) 
      end if when 'P' then
      if not exists(select * from AWRWeeklyDetail where personid = PersonorCompanyid and period = StartPeriod and qualtype = AWRAct and AWRreason = AWRtype) then
        insert into AWRWeeklyDetail( AWRWeeklyDetailID,personid,period,QualType,
          AWRreason,WhoEntered, notes) values( uniquekey('x'),PersonorCompanyid,StartPeriod,AWRAct,
          AWRtype,userstaffid, @notes) 
      end if when 'H' then
      if not exists(select * from AWRWeeklyDetail where personid = PersonorCompanyid and period = StartPeriod and qualtype = 1 and AWRreason = '¬') then
        insert into AWRWeeklyDetail( AWRWeeklyDetailID,personid,period,QualType,
          AWRreason,WhoEntered, notes) values( uniquekey('x'),PersonorCompanyid,StartPeriod,1,'¬',
          userstaffid, @notes) 
      end if when 'T' then
        insert into AWRWeeklyDetail( AWRWeeklyDetailID,AWRJobMasterid,period,QualType,
          AWRreason,WhoEntered, notes, TempTimesheetID) values( uniquekey('x'),@AWRJobMasterid,StartPeriod,2,'`',
          userstaffid, @notes, tsid) 
      when 'O' then
      if not exists(select * from AWRWeeklyDetail where AWRJobMasterid = @AWRJobMasterid and period = StartPeriod and qualtype = 2 and AWRreason = AWRType) then
        insert into AWRWeeklyDetail( AWRWeeklyDetailID,AWRJobMasterid,period,QualType,
          AWRreason,WhoEntered,notes) values( uniquekey('x'),@AWRJobMasterid,StartPeriod,2,
          AWRtype,userstaffid, @notes) 
      end if
    end case
    ;
    set StartPeriod=weekcontaining(dateadd(week,1,weekenddate(truncnum(StartPeriod/100,0),remainder(StartPeriod,100))))
  end loop
-- AWRtype = Unavailability type
-- IDtype = P - person, C - company, O - other agency work H - holiday T - timesheet
-- person types insert personid jury service etc records
-- company types insert companyid shutdown/strike records
-- use passed one for other agency work and insert records
-- called by 
-- 1 trigger on tempshift unavailability entry
-- 2 company add AWR data button
-- 3 person add AWR data button
end
}
</code>