pears.NetClientAddShiftsWeek

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
Original SQL

CREATE PROCEDURE "pears"."NetClientAddShiftsWeek"( IN "pWebUserID" CHAR(20),IN "pvacancyid" CHAR(20),IN "pshifttemplateid" CHAR(20),IN "pstartdate" CHAR(25),IN "penddate" CHAR(25) DEFAULT '',
  IN "ptimefrom" CHAR(25) DEFAULT NULL,IN "ptimeto" CHAR(25) DEFAULT NULL,IN "pBreak" CHAR(20) DEFAULT NULL,IN "pTheirRef" CHAR(50) DEFAULT NULL,
  IN "pDescription" long VARCHAR DEFAULT NULL,IN "pSkills" CHAR(100) DEFAULT NULL,IN "pGrade" CHAR(50) DEFAULT NULL,IN "pNote" CHAR(50) DEFAULT NULL,
  IN "pTitle" CHAR(100) DEFAULT NULL,IN "pMonday" CHAR(10) DEFAULT NULL,IN "pTuesday" CHAR(10) DEFAULT NULL,IN "pWednesday" CHAR(10) DEFAULT NULL,IN "pThursday" CHAR(10) DEFAULT NULL,
  IN "pFriday" CHAR(10) DEFAULT NULL,IN "pSaturday" CHAR(10) DEFAULT NULL,IN "pSunday" CHAR(10) DEFAULT NULL,
  IN "pOrderReason" CHAR(50) DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
BEGIN
  DECLARE "swebusername" CHAR(50);
  DECLARE "sid" CHAR(20);
  DECLARE "cnt" INTEGER;
  DECLARE "idow" INTEGER;
  DECLARE "ddate" DATE;
  DECLARE "dstart" DATE;
  DECLARE "dend" DATE;
  DECLARE "tfrom" TIME;
  DECLARE "tto" TIME;
  DECLARE "ibreak" INTEGER;
  DECLARE "CanAuthorise" SMALLINT;
  DECLARE LOCAL TEMPORARY TABLE "IDs"(
    "ID" CHAR(20) NULL,
    ) NOT transactional;
  INSERT INTO "IDs"( "ID" ) 
    SELECT DISTINCT "e"."companyid" FROM "iqxnetuserlink" AS "l" KEY JOIN "employment" AS "e" WHERE "l"."iqxnetuserid" = "pWebUserID";
  IF NOT "pvacancyid" = any(SELECT "v"."vacancyid" FROM "vacancy" AS "v" KEY JOIN "employment" AS "e" JOIN "IDs" ON "e"."companyid" = "IDs"."ID") THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF "IQXNetHasPermission"("pWebUserID",'ORDERJOBS') = 0 THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  SET "swebusername" = (SELECT "name" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pwebuserid");
  SET "dstart" = "iqxnetstringtodate"("pstartdate");
  IF "dstart" IS NULL THEN
    SELECT '500:~Invalid start date';
    RETURN
  END IF;
  SET "dend" = "iqxnetstringtodate"("penddate");
  IF "dend" IS NULL THEN
    SET "dend" = "dstart"+6
  END IF;
  SET "tfrom" = "iqxnetstringtotime"("ptimefrom");
  IF "tfrom" IS NULL THEN
    SELECT '500:~Invalid start time';
    RETURN
  END IF;
  SET "tto" = "iqxnetstringtotime"("ptimeto");
  IF "tto" IS NULL THEN
    SELECT '500:~Invalid end time';
    RETURN
  END IF;
  SET "ibreak" = "isnull"("iqxnetstringtointeger"("pbreak"),0);
  SET "CanAuthorise" = "IQXNetHasPermission"("pWebUserID",'AUTHORISEJOBS');
  SET "ddate" = "dstart";
  while "ddate" <= "dend" loop
    SET "idow" = "dow"("ddate");
    CASE "idow"
    WHEN 1 THEN SET "cnt" = "isnull"("iqxnetstringtointeger"("psunday"),0)
    WHEN 2 THEN SET "cnt" = "isnull"("iqxnetstringtointeger"("pmonday"),0)
    WHEN 3 THEN SET "cnt" = "isnull"("iqxnetstringtointeger"("ptuesday"),0)
    WHEN 4 THEN SET "cnt" = "isnull"("iqxnetstringtointeger"("pwednesday"),0)
    WHEN 5 THEN SET "cnt" = "isnull"("iqxnetstringtointeger"("pthursday"),0)
    WHEN 6 THEN SET "cnt" = "isnull"("iqxnetstringtointeger"("pfriday"),0)
    WHEN 7 THEN SET "cnt" = "isnull"("iqxnetstringtointeger"("psaturday"),0)
    END CASE;
    while "cnt" > 0 loop
      SET "sid" = "uniquekey"("cnt");
      INSERT INTO "tempshiftplan"( "TempShiftPlanID","VacancyID","TimeFrom","TimeTo","BreakMinutes","ShiftDate","Moveable","Description","ClientNote","EssentialSkill","EssentialSkillChoiceList","EssentialSkillGradeID","ReferenceCode","ReferenceRequired","ClientMustConfirm","TempMustConfirm","OrderedBy","TempShiftTypeID","AnalysisCode","RecoveryHours","TempShiftOrderReasonID" ) 
        SELECT "sid","pvacancyid","tfrom","tto","ibreak","ddate","Moveable","pDescription","pNote","string"("taglocation",';',"tagid",';',"tagchoiceid",';',"gradetagid"),"replace"("pskills","char"(9),';'),"pgrade","ucase"("pTheirRef"),"ReferenceRequired","ClientMustConfirm","TempMustConfirm","sWebUserName","TempShiftTypeID","AnalysisCode","RecoveryHours","nullif"("pOrderReason",'')
          FROM "tempshifttemplate" WHERE "tempshifttemplateid" = "pshifttemplateid";
      CALL "NetSetShiftPlanCascade"("sid");
      IF "CanAuthorise" = 0 THEN
        INSERT INTO "tempshift"( "tempshiftid","vacancyid","shiftdate","timefrom","timeto","state","tempshiftplanid","cancelreason" ) VALUES
          ( "sid","pvacancyid","ddate","tfrom","tto",'C',"sid",'~' ) 
      END IF;
      SET "cnt" = "cnt"-1
    END loop;
    SET "ddate" = "dateadd"("day",1,"ddate")
  END loop;
  IF "CanAuthorise" = 1 THEN
    CALL "IQXNetPopup"("pWebUserID",NULL,NULL,"pvacancyid",NULL,NULL,'ADDSHIFTS','New shift requirement(s) added',NULL)
  END IF;
  SELECT '0:~Success'
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientAddShiftsWeek" IS 
{CREATE PROCEDURE pears."NetClientAddShiftsWeek"( IN "pWebUserID" CHAR(20),IN "pvacancyid" CHAR(20),IN "pshifttemplateid" CHAR(20),IN "pstartdate" CHAR(25),IN "penddate" CHAR(25) DEFAULT '',
  IN "ptimefrom" CHAR(25) DEFAULT NULL,IN "ptimeto" CHAR(25) DEFAULT NULL,IN "pBreak" CHAR(20) DEFAULT NULL,IN "pTheirRef" CHAR(50) DEFAULT NULL,
  IN "pDescription" long VARCHAR DEFAULT NULL,IN "pSkills" CHAR(100) DEFAULT NULL,IN "pGrade" CHAR(50) DEFAULT NULL,IN "pNote" CHAR(50) DEFAULT NULL,
  IN "pTitle" CHAR(100) DEFAULT NULL,IN "pMonday" CHAR(10) DEFAULT NULL,IN "pTuesday" CHAR(10) DEFAULT NULL,IN "pWednesday" CHAR(10) DEFAULT NULL,IN "pThursday" CHAR(10) DEFAULT NULL,
  IN "pFriday" CHAR(10) DEFAULT NULL,IN "pSaturday" CHAR(10) DEFAULT NULL,IN "pSunday" CHAR(10) DEFAULT NULL,
  IN "pOrderReason" CHAR(50) DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
BEGIN
  DECLARE "swebusername" CHAR(50);
  DECLARE "sid" CHAR(20);
  DECLARE "cnt" INTEGER;
  DECLARE "idow" INTEGER;
  DECLARE "ddate" DATE;
  DECLARE "dstart" DATE;
  DECLARE "dend" DATE;
  DECLARE "tfrom" TIME;
  DECLARE "tto" TIME;
  DECLARE "ibreak" INTEGER;
  DECLARE "CanAuthorise" SMALLINT;
  DECLARE LOCAL TEMPORARY TABLE "IDs"(
    "ID" CHAR(20) NULL,
    ) NOT transactional;
  INSERT INTO "IDs"( "ID" ) 
    SELECT DISTINCT "e"."companyid" FROM "iqxnetuserlink" AS "l" KEY JOIN "employment" AS "e" WHERE "l"."iqxnetuserid" = "pWebUserID";
  IF NOT "pvacancyid" = any(SELECT "v"."vacancyid" FROM "vacancy" AS "v" KEY JOIN "employment" AS "e" JOIN "IDs" ON "e"."companyid" = "IDs"."ID") THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF "IQXNetHasPermission"("pWebUserID",'ORDERJOBS') = 0 THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  SET "swebusername" = (SELECT "name" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pwebuserid");
  SET "dstart" = "iqxnetstringtodate"("pstartdate");
  IF "dstart" IS NULL THEN
    SELECT '500:~Invalid start date';
    RETURN
  END IF;
  SET "dend" = "iqxnetstringtodate"("penddate");
  IF "dend" IS NULL THEN
    SET "dend" = "dstart"+6
  END IF;
  SET "tfrom" = "iqxnetstringtotime"("ptimefrom");
  IF "tfrom" IS NULL THEN
    SELECT '500:~Invalid start time';
    RETURN
  END IF;
  SET "tto" = "iqxnetstringtotime"("ptimeto");
  IF "tto" IS NULL THEN
    SELECT '500:~Invalid end time';
    RETURN
  END IF;
  SET "ibreak" = "isnull"("iqxnetstringtointeger"("pbreak"),0);
  SET "CanAuthorise" = "IQXNetHasPermission"("pWebUserID",'AUTHORISEJOBS');
  SET "ddate" = "dstart";
  while "ddate" <= "dend" loop
    SET "idow" = "dow"("ddate");
    CASE "idow"
    WHEN 1 THEN SET "cnt" = "isnull"("iqxnetstringtointeger"("psunday"),0)
    WHEN 2 THEN SET "cnt" = "isnull"("iqxnetstringtointeger"("pmonday"),0)
    WHEN 3 THEN SET "cnt" = "isnull"("iqxnetstringtointeger"("ptuesday"),0)
    WHEN 4 THEN SET "cnt" = "isnull"("iqxnetstringtointeger"("pwednesday"),0)
    WHEN 5 THEN SET "cnt" = "isnull"("iqxnetstringtointeger"("pthursday"),0)
    WHEN 6 THEN SET "cnt" = "isnull"("iqxnetstringtointeger"("pfriday"),0)
    WHEN 7 THEN SET "cnt" = "isnull"("iqxnetstringtointeger"("psaturday"),0)
    END CASE;
    while "cnt" > 0 loop
      SET "sid" = "uniquekey"("cnt");
      INSERT INTO "tempshiftplan"( "TempShiftPlanID","VacancyID","TimeFrom","TimeTo","BreakMinutes","ShiftDate","Moveable","Description","ClientNote","EssentialSkill","EssentialSkillChoiceList","EssentialSkillGradeID","ReferenceCode","ReferenceRequired","ClientMustConfirm","TempMustConfirm","OrderedBy","TempShiftTypeID","AnalysisCode","RecoveryHours","TempShiftOrderReasonID" ) 
        SELECT "sid","pvacancyid","tfrom","tto","ibreak","ddate","Moveable","pDescription","pNote","string"("taglocation",';',"tagid",';',"tagchoiceid",';',"gradetagid"),"replace"("pskills","char"(9),';'),"pgrade","ucase"("pTheirRef"),"ReferenceRequired","ClientMustConfirm","TempMustConfirm","sWebUserName","TempShiftTypeID","AnalysisCode","RecoveryHours","nullif"("pOrderReason",'')
          FROM "tempshifttemplate" WHERE "tempshifttemplateid" = "pshifttemplateid";
      CALL "NetSetShiftPlanCascade"("sid");
      IF "CanAuthorise" = 0 THEN
        INSERT INTO "tempshift"( "tempshiftid","vacancyid","shiftdate","timefrom","timeto","state","tempshiftplanid","cancelreason" ) VALUES
          ( "sid","pvacancyid","ddate","tfrom","tto",'C',"sid",'~' ) 
      END IF;
      SET "cnt" = "cnt"-1
    END loop;
    SET "ddate" = "dateadd"("day",1,"ddate")
  END loop;
  IF "CanAuthorise" = 1 THEN
    CALL "IQXNetPopup"("pWebUserID",NULL,NULL,"pvacancyid",NULL,NULL,'ADDSHIFTS','New shift requirement(s) added',NULL)
  END IF;
  SELECT '0:~Success'
END
}