pears.NetCandidateShiftBook

CREATE PROCEDURE "pears"."NetCandidateShiftBook"( IN "pWebUserID" CHAR(20),IN "pPersonID" CHAR(20),IN "pTempShiftPlanID" CHAR(20) ) 
RESULT( "pResult" CHAR(250) ) 
BEGIN
  DECLARE "sid" CHAR(20);
  DECLARE "swebusername" CHAR(50);
  DECLARE "availability" CHAR(20);
  DECLARE "spopup" CHAR(100);
  SET "pPersonID" = "NetCandidateValidPersonID"("pWebUserID","pPersonID");
  IF "pPersonID" IS NULL THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  /* if IQXNetHasPermission(pWebUserID,'need a right?') = 0 then
select '99:~Permission denied';
return
end if; */
  IF NOT "pTempShiftPlanID" = any(SELECT "tempshiftplanid" FROM "IQXNetCandidateShifts" WHERE "personid" = "pPersonID") THEN
    -- PopulateCandidateShifts must have been run to populate IQXNetCandidateShifts with suitable and allowable shifts
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  SET "availability" = (SELECT "personavailableforplan"("ppersonid","p"."ShiftDate","p"."TimeFrom","p"."TimeTo","p"."RecoveryHours","p"."minutes",0,"v"."departmentid","p"."essentialskill","p"."essentialskillgradeid","p"."essentialskillchoicelist",NULL)
      FROM "tempshiftplan" AS "p" KEY JOIN "vacancy" AS "v" WHERE "p"."tempshiftplanid" = "pTempShiftPlanID");
  IF "isnull"("availability",'A') NOT LIKE '[AM]%' THEN
    SELECT '130:~You are not available for this shift';
    RETURN
  END IF;
  IF EXISTS(SELECT * FROM "tempshift" WHERE "tempshiftplanid" = "pTempShiftPlanID" AND("state" IN( 'P','B','W' ) OR("state" = 'C' AND "crefill" = 0))) THEN
    SELECT '131:~This shift has already been filled';
    RETURN
  END IF;
  SET "sid" = "uniquekey"('');
  SET "swebusername" = (SELECT "name" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pwebuserid");
  INSERT INTO "tempshift"( "TempShiftID","VacancyID","PersonID","ShiftDate","TimeFrom","TimeTo","State","TempShiftPlanID",
    "BreakMinutes","EssentialSkillGradeID","ReferenceCode","ClientConfirmed","TempConfirmed","StaffID",
    "ConfirmedWith","TempShiftTypeID","AnalysisCode","RecoveryHours" ) 
    SELECT "sid","v"."VacancyID","pPersonID","p"."ShiftDate","p"."TimeFrom","p"."TimeTo",'P',"pTempShiftPlanID",
      "p"."BreakMinutes","p"."EssentialSkillGradeID","p"."ReferenceCode",0,1,"userStaffID",
      "swebusername","p"."TempShiftTypeID","p"."AnalysisCode","p"."RecoveryHours"
      FROM "tempshiftplan" AS "p" KEY JOIN "vacancy" AS "v" KEY JOIN "employment" AS "e" WHERE "p"."tempshiftplanid" = "pTempShiftPlanID";
  SET "spopup" = (SELECT "string"("person"."name",' ',"dateformat"("p"."shiftdate",'Ddd dd/mm/yyyy')+' '+"dateformat"("p"."timefrom",'hh:nn')) FROM "tempshiftplan" AS "p","person" WHERE "p"."tempshiftplanid" = "pTempShiftPlanID" AND "person"."personid" = "ppersonid");
  CALL "IQXNetPopup"("pWebUserID",NULL,NULL,"pTempShiftPlanID",NULL,NULL,'TEMPBOOKING','Candidate booked shift',"spopup");
  SELECT '0:~Success'
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateShiftBook" IS 
{CREATE PROCEDURE pears."NetCandidateShiftBook"(IN pWebUserID CHAR(20), pPersonID CHAR(20), pTempShiftPlanID CHAR(20) )
RESULT( pResult CHAR(250) ) 
BEGIN
DECLARE sid CHAR(20);
DECLARE swebusername CHAR(50);
DECLARE availability CHAR(20);
DECLARE spopup CHAR(100);
 
SET pPersonID=NetCandidateValidPersonID(pWebUserID, pPersonID);
IF pPersonID IS NULL THEN
  SELECT '99:~Permission denied';
  RETURN
END IF;
/* if IQXNetHasPermission(pWebUserID,'need a right?') = 0 then
  select '99:~Permission denied';
  return
end if; */
IF NOT pTempShiftPlanID IN (SELECT tempshiftplanid FROM IQXNetCandidateShifts WHERE personid=pPersonID) THEN
  -- PopulateCandidateShifts must have been run to populate IQXNetCandidateShifts with suitable and allowable shifts
  SELECT '99:~Permission denied';
  RETURN
END IF;
 
SET availability = (SELECT personavailableforplan(ppersonid,p.ShiftDate,p.TimeFrom,p.TimeTo,p.RecoveryHours,p.minutes,0,v.departmentid,p.essentialskill,p.essentialskillgradeid,p.essentialskillchoicelist,NULL)
   FROM tempshiftplan p KEY JOIN vacancy v WHERE p.tempshiftplanid = pTempShiftPlanID);
IF NOT isnull(availability,'A') LIKE '[AM]%' THEN
  SELECT '130:~You are not available for this shift';
  RETURN
END IF;
IF EXISTS(SELECT * FROM tempshift WHERE tempshiftplanid = pTempShiftPlanID AND (state IN ('P','B','W') OR (state='C' AND crefill=0)) ) THEN
  SELECT '131:~This shift has already been filled';
  RETURN
END IF;
 
SET sid = uniquekey('');
SET swebusername = (SELECT name FROM iqxnetuser WHERE iqxnetuserid = pwebuserid);
 
INSERT INTO tempshift( TempShiftID,VacancyID,PersonID,ShiftDate,TimeFrom,TimeTo,State,TempShiftPlanID,
  BreakMinutes,EssentialSkillGradeID,ReferenceCode,ClientConfirmed,TempConfirmed,StaffID,
  ConfirmedWith,TempShiftTypeID,AnalysisCode,RecoveryHours ) 
  SELECT sid,v.VacancyID,pPersonID,p.ShiftDate,p.TimeFrom,p.TimeTo,'P',pTempShiftPlanID,
    p.BreakMinutes,p.EssentialSkillGradeID,p.ReferenceCode,0,1,userStaffID,
    swebusername,p.TempShiftTypeID,p.AnalysisCode,p.RecoveryHours
    FROM tempshiftplan AS p KEY JOIN vacancy v KEY JOIN employment e WHERE p.tempshiftplanid = pTempShiftPlanID;
SET spopup = (SELECT string(person.name, ' ', dateformat(p.shiftdate,'Ddd dd/mm/yyyy')+' '+dateformat(p.timefrom,'hh:nn')) FROM tempshiftplan p,person WHERE p.tempshiftplanid = pTempShiftPlanID AND person.personid = ppersonid);
CALL IQXNetPopup(pWebUserID,NULL,NULL,pTempShiftPlanID,NULL,NULL,'TEMPBOOKING','Candidate booked shift',spopup);
SELECT '0:~Success'
 
END
}