pears.AWRInsertWeekly

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
}