====== pears.NetCandidateShiftBook ======
<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"."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
}
</code>