====== pears.NetClientCreateBooking ======
<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"."NetClientCreateBooking"( in "pWebUserID" char(20),in "pPersonID" char(20),in "pLineID" char(20),in "pIsShift" char(1) ) 
result( "pResult" char(250) ) 
// IQXWeb
begin
  declare "sid" char(20);
  declare "swebusername" char(50);
  declare "availability" char(20);
  declare "spopup" char(100);
  declare local temporary table "IDs"(
    "ID" char(20) null,
    ) not transactional;
  insert into "IDs"( "ID" ) 
    select distinct "e"."companyid" from "employment" as "e" key join "iqxnetuserlink" as "i" where "i"."iqxnetuserid" = "pWebUserID";
  if "pIsShift" = '0' then
    if not "pLineID" = 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
  else if not "pLineID" = any(select "p"."tempshiftplanid" from "tempshiftplan" as "p" key join "vacancy" as "v" key join "employment" as "e" join "IDs" on "e"."CompanyID" = "IDs"."ID") then
      select '99:~Permission denied';
      return
    end if end if;
  if "IQXNetHasPermission"("pWebUserID",'FILLJOBS') = 0 then
    select '99:~Permission denied';
    return
  end if;
  set "sid" = "uniquekey"("ppersonid");
  set "swebusername" = (select "name" from "iqxnetuser" where "iqxnetuserid" = "pwebuserid");
  if "pIsShift" = '0' then
    if(select "EmployeeAcceptable"("ppersonid","e"."companyid",1,"v"."vacancyid") from "vacancy" as "v" key join "employment" as "e" where "v"."vacancyid" = "pLineID") is not null then
      select '110:~Employee is not suitable for this vacancy';
      return
    end if;
    insert into "employment"( "temp","employmentid","personid","companyid","startdate","leavedate","position","department" ) 
      select "v"."temp","sid","ppersonid","e"."companyid","v"."startdate","v"."finishdate","v"."position","v"."clientdepartment"
        from "vacancy" as "v" key join "employment" as "e" where "v"."vacancyid" = "pLineID";
    insert into "placement"( "placementid","placedate","employmentid","vacancyid","departmentid","staffid","temp","daysperweek","salary",
      "TheirRef","ContractRef","Currency","WorkMonday","WorkTuesday","WorkWednesday","WorkThursday","WorkFriday","WorkSaturday","WorkSunday",
      "WorkNormalHours","WorkStartTime" ) 
      select "sid",current date,"sid","pLineID","v"."departmentid","userstaffid","v"."temp",5,"v"."salary","v"."theirref","v"."contractref","v"."currency",
        "v"."WorkMonday","v"."WorkTuesday","v"."WorkWednesday","v"."WorkThursday","v"."WorkFriday","v"."WorkSaturday","v"."WorkSunday",
        "v"."WorkNormalHours","v"."WorkStartTime"
        from "vacancy" as "v" where "v"."vacancyid" = "pLineID";
    call "IQXNetPopup"("pWebUserID",null,null,"pLineID","sid",null,'CLIENTBOOKING','Client made placement',null)
  else
    if(select "EmployeeAcceptable"("ppersonid","e"."companyid",1,"v"."vacancyid") from "tempshiftplan" as "p" key join "vacancy" as "v" key join "employment" as "e" where "p"."tempshiftplanid" = "pLineID") is not null then
      select '110:~Employee is not suitable for this shift';
      return
    end if;
    /*    if (select isnull(TempShiftAllowed(p.tempshiftplanid,ppersonid,v.vacancyid),'') from tempshiftplan as p key join vacancy v key join employment e where p.tempshiftplanid = pLineID) != '' then
select '111:~Employee is not suitable for this shift';
return;
end if;   
// NetClientRequirementVerifiedCandidates does NOT call TempShiftAllowed so this shouldn't either by common sense. May need review.
*/
    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" = "pLineID");
    if "isnull"("availability",'A') not like '[AM]%' then
      select '112:~Candidate not suitable or not available';
      return
    end if;
    if exists(select * from "tempshift" where "state" in( 'P','B' ) and "tempshiftplanid" = "pLineID") then
      select '113:~Shift already filled';
      return
    end if;
    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',"pLineID",
        "p"."BreakMinutes","p"."EssentialSkillGradeID","p"."ReferenceCode",1,0,"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" = "pLineID";
    set "spopup" = (select "dateformat"("p"."shiftdate",'Ddd dd/mm/yyyy')+' '+"dateformat"("p"."timefrom",'hh:nn')+' with '+"person"."name" from "tempshiftplan" as "p","person"
        where "p"."tempshiftplanid" = "pLineID" and "person"."personid" = "ppersonid");
    call "IQXNetPopup"("pWebUserID",null,null,"pLineID",null,null,'CLIENTBOOKING','Client filled shift',"spopup")
  end if;
  select '0:~Success'
end /* DOC

2019-01-18 PC IW-788 allow client to create booking, based on NetVacancyFill
2019-02-18 PC/VHS Modify to prevent multiple bookings
2019-03-07 PC IW-884 EmployeeAcceptable and TempshiftAllowed moved ahead of main functions
2019-06-19 MHS tidied up and fixed tests
*/
/* TEST
call nettestsetup('');
delete from withholds where personid='test';
delete from tempshift where personid='test' and tempshiftid<>'test';
delete from tempshift where tempshiftplanid = 'TEST1';
def test select * from NetClientCreateBooking('test.client','test', 'test',0);
do test;
expect 0*;
insert into withholds (withholdsid,personid,vacancyid) values (uniquekey(''),'test','test');
do test;
expect 110*;
update employment set startdate=current date - 2, leavedate=current date - 1 where personid='TEST';
insert into tempshiftplan (tempshiftplanid,vacancyid,description,shiftdate,timefrom,timeto) on existing update defaults off values ('TEST','TEST','t1',current date,'09:00','17:00');
insert into tempshift (tempshiftid,vacancyid,personid,shiftdate,timefrom,timeto,state,tempshiftplanid) on existing update defaults off values ('TEST','TEST','TEST',current date,'09:00','17:00','B','TEST');
insert into tempshiftplan (tempshiftplanid,vacancyid,description,shiftdate,timefrom,timeto) on existing update defaults off values ('TEST1','TEST','t2',current date,'09:00','17:00');
def test select * from NetClientCreateBooking('test.client','test', 'test1',1);
do test;
expect 110*;
delete from withholds where personid='test';
do test;
expect 112*;
update tempshiftplan set shiftdate=current date + 1 where tempshiftplanid='TEST1';
do test;
expect 0*;
update tempshift set personid=null where tempshiftplanid = 'TEST1';
do test;
expect 113*;
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientCreateBooking" IS 
{create PROCEDURE pears.NetClientCreateBooking( in pWebUserID char(20),in pPersonID char(20), in pLineID char(20), in pIsShift char(1))
result( pResult char(250) ) 
// IQXWeb
begin
  declare sid char(20);
  declare swebusername char(50);
  declare availability char(20);
  declare spopup char(100);
  declare local temporary table IDs(
    ID char(20) null,
    ) not transactional;
  insert into IDs( ID ) 
    select distinct e.companyid from employment as e key join iqxnetuserlink as i where i.iqxnetuserid = pWebUserID;
    if pIsShift='0' then
        if not pLineID = 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;
    else
        if not pLineID = any(select p.tempshiftplanid from tempshiftplan as p key join vacancy as v key join employment as e join IDs on e.CompanyID = IDs.ID) then
            select '99:~Permission denied';
            return
        end if;
    end if;
  if IQXNetHasPermission(pWebUserID,'FILLJOBS') = 0 then
    select '99:~Permission denied';
    return
  end if;
  set sid = uniquekey(ppersonid);
  set swebusername = (select name from iqxnetuser where iqxnetuserid = pwebuserid);

  if pIsShift = '0' then
    if (select EmployeeAcceptable(ppersonid,e.companyid,1,v.vacancyid) from vacancy as v key join employment as e where v.vacancyid = pLineID) is not null then
        select '110:~Employee is not suitable for this vacancy';
        return;
    end if;
    insert into employment( temp,employmentid,personid,companyid,startdate,leavedate,position,department ) 
      select v.temp,sid,ppersonid,e.companyid,v.startdate,v.finishdate,v.position,v.clientdepartment
        from vacancy as v key join employment as e where v.vacancyid = pLineID;
    insert into placement( placementid,placedate,employmentid,vacancyid,departmentid,staffid,temp,daysperweek,salary,
      TheirRef,ContractRef,Currency,WorkMonday,WorkTuesday,WorkWednesday,WorkThursday,WorkFriday,WorkSaturday,WorkSunday,
      WorkNormalHours,WorkStartTime ) 
      select sid,current date,sid,pLineID,v.departmentid,userstaffid,v.temp,5,v.salary,v.theirref,v.contractref,v.currency,
        v.WorkMonday,v.WorkTuesday,v.WorkWednesday,v.WorkThursday,v.WorkFriday,v.WorkSaturday,v.WorkSunday,
        v.WorkNormalHours,v.WorkStartTime
        from vacancy as v where v.vacancyid = pLineID;
    call IQXNetPopup(pWebUserID,null,null,pLineID,sid,null,'CLIENTBOOKING','Client made placement',null);
  else
    if (select EmployeeAcceptable(ppersonid,e.companyid,1,v.vacancyid) from tempshiftplan as p key join vacancy v key join employment e where p.tempshiftplanid = pLineID) is not null then
        select '110:~Employee is not suitable for this shift';
        return;
    end if;
/*    if (select isnull(TempShiftAllowed(p.tempshiftplanid,ppersonid,v.vacancyid),'') from tempshiftplan as p key join vacancy v key join employment e where p.tempshiftplanid = pLineID) != '' then
        select '111:~Employee is not suitable for this shift';
        return;
    end if;   
   // NetClientRequirementVerifiedCandidates does NOT call TempShiftAllowed so this shouldn't either by common sense. May need review.
    */

    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 v where p.tempshiftplanid = pLineID);
    if not isnull(availability,'A') like '[AM]%' then
      select '112:~Candidate not suitable or not available';
      return
    end if;
    if exists(select * from tempshift where state in ('P','B') and tempshiftplanid = pLineID) then
      select '113:~Shift already filled';
      return
    end if;
    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',pLineID,
        p.BreakMinutes,p.EssentialSkillGradeID,p.ReferenceCode,1,0,userStaffID,
        swebusername,p.TempShiftTypeID,p.AnalysisCode,p.RecoveryHours
        from tempshiftplan as p key join vacancy v key join employment e where p.tempshiftplanid = pLineID;
    set spopup = (select dateformat(p.shiftdate,'Ddd dd/mm/yyyy')+' '+dateformat(p.timefrom,'hh:nn')+' with '+person.name from tempshiftplan as p,person
        where p.tempshiftplanid = pLineID and person.personid = ppersonid);
    call IQXNetPopup(pWebUserID,null,null,pLineID,null,null,'CLIENTBOOKING','Client filled shift',spopup);
  end if;
  select '0:~Success'
end

/* DOC

2019-01-18 PC IW-788 allow client to create booking, based on NetVacancyFill
2019-02-18 PC/VHS Modify to prevent multiple bookings
2019-03-07 PC IW-884 EmployeeAcceptable and TempshiftAllowed moved ahead of main functions
2019-06-19 MHS tidied up and fixed tests
*/

/* TEST
call nettestsetup('');
delete from withholds where personid='test';
delete from tempshift where personid='test' and tempshiftid<>'test';
delete from tempshift where tempshiftplanid = 'TEST1';
def test select * from NetClientCreateBooking('test.client','test', 'test',0);
do test;
expect 0*;
insert into withholds (withholdsid,personid,vacancyid) values (uniquekey(''),'test','test');
do test;
expect 110*;
update employment set startdate=current date - 2, leavedate=current date - 1 where personid='TEST';
insert into tempshiftplan (tempshiftplanid,vacancyid,description,shiftdate,timefrom,timeto) on existing update defaults off values ('TEST','TEST','t1',current date,'09:00','17:00');
insert into tempshift (tempshiftid,vacancyid,personid,shiftdate,timefrom,timeto,state,tempshiftplanid) on existing update defaults off values ('TEST','TEST','TEST',current date,'09:00','17:00','B','TEST');
insert into tempshiftplan (tempshiftplanid,vacancyid,description,shiftdate,timefrom,timeto) on existing update defaults off values ('TEST1','TEST','t2',current date,'09:00','17:00');
def test select * from NetClientCreateBooking('test.client','test', 'test1',1);
do test;
expect 110*;
delete from withholds where personid='test';
do test;
expect 112*;
update tempshiftplan set shiftdate=current date + 1 where tempshiftplanid='TEST1';
do test;
expect 0*;
update tempshift set personid=null where tempshiftplanid = 'TEST1';
do test;
expect 113*;
*/
}
</code>