database:procedures:pears_netcandidatediarymultiadd



pears.NetCandidateDiaryMultiAdd

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

Original SQL

CREATE PROCEDURE "pears"."NetCandidateDiaryMultiAdd"( IN "pWebUserID" CHAR(20),IN "pShiftType" CHAR(10),IN "pDateFrom" CHAR(50),IN "pMonTick" CHAR(3) DEFAULT 'off',
  IN "pTimeFromMon" CHAR(25) DEFAULT NULL,IN "pTimeToMon" CHAR(25) DEFAULT NULL,IN "pTueTick" CHAR(3) DEFAULT 'off',
  IN "pTimeFromTue" CHAR(25) DEFAULT NULL,IN "pTimeToTue" CHAR(25) DEFAULT NULL,IN "pWedTick" CHAR(3) DEFAULT 'off',
  IN "pTimeFromWed" CHAR(25) DEFAULT NULL,IN "pTimeToWed" CHAR(25) DEFAULT NULL,IN "pThuTick" CHAR(3) DEFAULT 'off',
  IN "pTimeFromThu" CHAR(25) DEFAULT NULL,IN "pTimeToThu" CHAR(25) DEFAULT NULL,IN "pFriTick" CHAR(3) DEFAULT 'off',
  IN "pTimeFromFri" CHAR(25) DEFAULT NULL,IN "pTimeToFri" CHAR(25) DEFAULT NULL,IN "pSatTick" CHAR(3) DEFAULT 'off',
  IN "pTimeFromSat" CHAR(25) DEFAULT NULL,IN "pTimeToSat" CHAR(25) DEFAULT NULL,IN "pSunTick" CHAR(3) DEFAULT 'off',
  IN "pTimeFromSun" CHAR(25) DEFAULT NULL,IN "pTimeToSun" CHAR(25) DEFAULT NULL,IN "pPersonID" CHAR(20) DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
// IQXWeb
BEGIN
  DECLARE "dstart" DATE;
  DECLARE "ddate" DATE;
  DECLARE "dend" DATE;
  DECLARE "tFrom" TIME;
  DECLARE "tTo" TIME;
  DECLARE "CanAuthorise" SMALLINT;
  DECLARE "pvacancyid" CHAR(20);
  DECLARE "thelog" CHAR(20);
  SET "pPersonID" = "NetCandidateValidPersonID"("pWebUserID","pPersonID");
  IF "pPersonID" IS NULL THEN
    SELECT '99:~Unique candidate link required';
    RETURN
  END IF;
  SET "dstart" = "iqxnetstringtodate"("pDateFrom");
  IF "dstart" IS NULL THEN
    SELECT '500:~Invalid start date';
    RETURN
  END IF;
  IF "substring"("pDateFrom",0,3) <> 'Mon' THEN
    SELECT '500:~Start date must be a Monday';
    RETURN
  END IF;
  IF "pshifttype" NOT IN( 'H','A','U' ) THEN //Holiday/Available/Unavailable
    SELECT '101:~invalid shift type';
    RETURN
  END IF;
  SET "CanAuthorise" = "IQXNetHasPermission"("pWebUserID",'AUTHORISEJOBS');
  SET "ddate" = "dstart";
  IF "pMonTick" = 'on' THEN
    SET "tFrom" = "iqxnetstringtotime"("pTimeFromMon");
    SET "tTo" = "iqxnetstringtotime"("pTimeToMon");
    INSERT INTO "tempshift"( "tempshiftid","personid","shiftdate","timefrom","timeto","state" ) VALUES( "uniquekey"("ddate"),"pPersonID","ddate","tFrom","tTo","pshifttype" ) 
  END IF;
  SET "ddate" = "ddate"+1;
  IF "pTueTick" = 'on' THEN
    SET "tFrom" = "iqxnetstringtotime"("pTimeFromTue");
    SET "tTo" = "iqxnetstringtotime"("pTimeToTue");
    INSERT INTO "tempshift"( "tempshiftid","personid","shiftdate","timefrom","timeto","state" ) VALUES( "uniquekey"("ddate"),"pPersonID","ddate","tFrom","tTo","pshifttype" ) 
  END IF;
  SET "ddate" = "ddate"+1;
  IF "pWedTick" = 'on' THEN
    SET "tFrom" = "iqxnetstringtotime"("pTimeFromWed");
    SET "tTo" = "iqxnetstringtotime"("pTimeToWed");
    INSERT INTO "tempshift"( "tempshiftid","personid","shiftdate","timefrom","timeto","state" ) VALUES( "uniquekey"("ddate"),"pPersonID","ddate","tFrom","tTo","pshifttype" ) 
  END IF;
  SET "ddate" = "ddate"+1;
  IF "pThuTick" = 'on' THEN
    SET "tFrom" = "iqxnetstringtotime"("pTimeFromThu");
    SET "tTo" = "iqxnetstringtotime"("pTimeToThu");
    INSERT INTO "tempshift"( "tempshiftid","personid","shiftdate","timefrom","timeto","state" ) VALUES( "uniquekey"("ddate"),"pPersonID","ddate","tFrom","tTo","pshifttype" ) 
  END IF;
  SET "ddate" = "ddate"+1;
  IF "pFriTick" = 'on' THEN
    SET "tFrom" = "iqxnetstringtotime"("pTimeFromFri");
    SET "tTo" = "iqxnetstringtotime"("pTimeToFri");
    INSERT INTO "tempshift"( "tempshiftid","personid","shiftdate","timefrom","timeto","state" ) VALUES( "uniquekey"("ddate"),"pPersonID","ddate","tFrom","tTo","pshifttype" ) 
  END IF;
  SET "ddate" = "ddate"+1;
  IF "pSatTick" = 'on' THEN
    SET "tFrom" = "iqxnetstringtotime"("pTimeFromSat");
    SET "tTo" = "iqxnetstringtotime"("pTimeToSat");
    INSERT INTO "tempshift"( "tempshiftid","personid","shiftdate","timefrom","timeto","state" ) VALUES( "uniquekey"("ddate"),"pPersonID","ddate","tFrom","tTo","pshifttype" ) 
  END IF;
  SET "ddate" = "ddate"+1;
  IF "pSunTick" = 'on' THEN
    SET "tFrom" = "iqxnetstringtotime"("pTimeFromSun");
    SET "tTo" = "iqxnetstringtotime"("pTimeToSun");
    INSERT INTO "tempshift"( "tempshiftid","personid","shiftdate","timefrom","timeto","state" ) VALUES( "uniquekey"("ddate"),"pPersonID","ddate","tFrom","tTo","pshifttype" ) 
  END IF;
  IF "CanAuthorise" = 1 THEN
    CALL "IQXNetPopup"("pWebUserID","pPersonID",NULL,"pvacancyid",NULL,NULL,'ADDSHIFTS','New shift requirement(s) added',"thelog")
  END IF;
  SELECT '0:~Success'
END /* DOC
2016-12-02 PC does not appear to be used by IQXWeb
2018-10-10 MHS IW-456 multi-cand fix 
*/
/* TEST
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateDiaryMultiAdd" IS 
{CREATE PROCEDURE pears."NetCandidateDiaryMultiAdd"(IN pWebUserID CHAR(20),IN pShiftType CHAR(10),IN pDateFrom CHAR(50),IN pMonTick CHAR(3) DEFAULT 'off',IN pTimeFromMon CHAR(25) DEFAULT
NULL,IN pTimeToMon CHAR(25) DEFAULT NULL,IN pTueTick CHAR(3) DEFAULT 'off',IN pTimeFromTue CHAR(25) DEFAULT
NULL,IN pTimeToTue CHAR(25) DEFAULT NULL,IN pWedTick CHAR(3) DEFAULT 'off',IN pTimeFromWed CHAR(25) DEFAULT
NULL,IN pTimeToWed CHAR(25) DEFAULT NULL,IN pThuTick CHAR(3) DEFAULT 'off',IN pTimeFromThu CHAR(25) DEFAULT
NULL,IN pTimeToThu CHAR(25) DEFAULT NULL,IN pFriTick CHAR(3) DEFAULT 'off',IN pTimeFromFri CHAR(25) DEFAULT
NULL,IN pTimeToFri CHAR(25) DEFAULT NULL,IN pSatTick CHAR(3) DEFAULT 'off',IN pTimeFromSat CHAR(25) DEFAULT
NULL,IN pTimeToSat CHAR(25) DEFAULT NULL,IN pSunTick CHAR(3) DEFAULT 'off',IN pTimeFromSun CHAR(25) DEFAULT
NULL,IN pTimeToSun CHAR(25) DEFAULT NULL, IN pPersonID CHAR(20) DEFAULT NULL)
RESULT(pResult CHAR(250))
// IQXWeb
BEGIN
  DECLARE dstart DATE;
  DECLARE ddate DATE;
  DECLARE dend DATE;
  DECLARE tFrom TIME;
  DECLARE tTo TIME;
  DECLARE CanAuthorise SMALLINT;
  DECLARE pvacancyid CHAR(20);
  DECLARE thelog CHAR(20);
 
  SET pPersonID=NetCandidateValidPersonID(pWebUserID,pPersonID);
  IF pPersonID IS NULL THEN
     SELECT '99:~Unique candidate link required';
     RETURN;
  END IF;
 
  SET dstart=iqxnetstringtodate(pDateFrom);
  IF dstart IS NULL THEN
    SELECT '500:~Invalid start date';
    RETURN
  END IF;
  IF SUBSTRING(pDateFrom,0,3) <> 'Mon' THEN
    SELECT '500:~Start date must be a Monday';
    RETURN
  END IF;
  IF pshifttype NOT IN( 'H','A','U') THEN //Holiday/Available/Unavailable
    SELECT '101:~invalid shift type';
    RETURN
  END IF;
  SET CanAuthorise=IQXNetHasPermission(pWebUserID,'AUTHORISEJOBS');
  SET ddate=dstart;
  IF pMonTick = 'on' THEN
    SET tFrom=iqxnetstringtotime(pTimeFromMon);
    SET tTo=iqxnetstringtotime(pTimeToMon);
    INSERT INTO tempshift( tempshiftid,personid,shiftdate,timefrom,timeto,state) VALUES( uniquekey(ddate),pPersonID,ddate,tFrom,tTo,pshifttype) 
  END IF;
  SET ddate=ddate+1;
  IF pTueTick = 'on' THEN
    SET tFrom=iqxnetstringtotime(pTimeFromTue);
    SET tTo=iqxnetstringtotime(pTimeToTue);
    INSERT INTO tempshift( tempshiftid,personid,shiftdate,timefrom,timeto,state) VALUES( uniquekey(ddate),pPersonID,ddate,tFrom,tTo,pshifttype) 
  END IF;
  SET ddate=ddate+1;
  IF pWedTick = 'on' THEN
    SET tFrom=iqxnetstringtotime(pTimeFromWed);
    SET tTo=iqxnetstringtotime(pTimeToWed);
    INSERT INTO tempshift( tempshiftid,personid,shiftdate,timefrom,timeto,state) VALUES( uniquekey(ddate),pPersonID,ddate,tFrom,tTo,pshifttype) 
  END IF;
  SET ddate=ddate+1;
  IF pThuTick = 'on' THEN
    SET tFrom=iqxnetstringtotime(pTimeFromThu);
    SET tTo=iqxnetstringtotime(pTimeToThu);
    INSERT INTO tempshift( tempshiftid,personid,shiftdate,timefrom,timeto,state) VALUES( uniquekey(ddate),pPersonID,ddate,tFrom,tTo,pshifttype) 
  END IF;
  SET ddate=ddate+1;
  IF pFriTick = 'on' THEN
    SET tFrom=iqxnetstringtotime(pTimeFromFri);
    SET tTo=iqxnetstringtotime(pTimeToFri);
    INSERT INTO tempshift( tempshiftid,personid,shiftdate,timefrom,timeto,state) VALUES( uniquekey(ddate),pPersonID,ddate,tFrom,tTo,pshifttype) 
  END IF;
  SET ddate=ddate+1;
  IF pSatTick = 'on' THEN
    SET tFrom=iqxnetstringtotime(pTimeFromSat);
    SET tTo=iqxnetstringtotime(pTimeToSat);
    INSERT INTO tempshift( tempshiftid,personid,shiftdate,timefrom,timeto,state) VALUES( uniquekey(ddate),pPersonID,ddate,tFrom,tTo,pshifttype) 
  END IF;
  SET ddate=ddate+1;
  IF pSunTick = 'on' THEN
    SET tFrom=iqxnetstringtotime(pTimeFromSun);
    SET tTo=iqxnetstringtotime(pTimeToSun);
    INSERT INTO tempshift( tempshiftid,personid,shiftdate,timefrom,timeto,state) VALUES( uniquekey(ddate),pPersonID,ddate,tFrom,tTo,pshifttype) 
  END IF;
  IF CanAuthorise = 1 THEN
    CALL IQXNetPopup(pWebUserID,pPersonID,NULL,pvacancyid,NULL,NULL,'ADDSHIFTS','New shift requirement(s) added',thelog)
  END IF;
  SELECT '0:~Success'
END
 
/* DOC
2016-12-02 PC does not appear to be used by IQXWeb
2018-10-10 MHS IW-456 multi-cand fix 
*/
 
/* TEST
 
*/
}
  • database/procedures/pears_netcandidatediarymultiadd.txt
  • Last modified: 2026/08/07 19:24
  • by 127.0.0.1