====== pears.NetClientVacanciesLineStatusUpdate ======
<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"."NetClientVacanciesLineStatusUpdate"( in "pWebUserID" char(20),in "pLineNo" char(21),in "pTransactionType" char(1),in "pNote" char(255),in "pOurRef" char(20),in "pCancelReason" char(1) default null,in "pLocation" char(100) default null,in "pPersonID" char(20) default null ) 
result( "retval" char(250) ) 
// IQXWeb
begin
  declare "PorT" char(1);
  declare "LineNo" char(20);
  declare "VacID" char(20);
  declare "StaffID" char(20);
  declare "LShiftDate" date;
  declare "LTimeFrom" time;
  declare "LTimeTo" time;
  declare "sid" char(20);
  declare "LTempShiftPlanID" char(20);
  declare "LWhoCancelled" char(60);
  declare "RequiresConfirm" integer;
  declare "MustTempConfirm" integer;
  declare "Note" char(255);
  declare "IntNote" char(255);
  declare "ShiftRef" char(20);
  declare "Location" char(100);
  declare "EmploymentID" char(20);
  declare "PersID" char(20);
  declare "IntermediateString" char(150);
  set "PorT" = "left"("pLineNo",1); -- discover whether TempShiftPlan or TempShift
  set "LineNo" = "right"("pLineNo","length"("pLineNo")-1);
  case "PorT"
  when 'V' then
    (select "t"."vacancyid","shiftdate","timefrom","timeto","tempshiftplanid","e"."employmentid",null,"v"."refcode" into "VacID","LShiftDate","LTimeFrom","LTimeTo","LTempShiftPlanID","EmploymentID","PersID","ShiftRef" from "tempshiftplan" as "t" key join "vacancy" as "v" key join "employment" as "e" where "v"."vacancyid" = "LineNo")
  when 'P' then
    (select "t"."vacancyid","t"."shiftdate","t"."timefrom","t"."timeto","t"."tempshiftplanid","e"."employmentid","p"."personid","tsp"."shiftserialnumber" into "VacID","LShiftDate","LTimeFrom","LTimeTo","LTempShiftPlanID","EmploymentID","PersID","ShiftRef" from "tempshiftplan" as "tsp" key join "tempshift" as "t" key join "vacancy" key join "employment" as "e" left outer join "person" as "p" on "p"."personid" = "t"."personid" where "tsp"."TempShiftPlanID" = "LineNo")
  else
    return
  end case;
  set "PersID" = "isnull"("PersID","pPersonID");
  -- conFirm
  if "pTransactionType" = 'F' then
    call "IQXNetPopup"("pWebUserID","PersID","EmploymentID","VacID",null,null,'SHIFTSTATECHANGE','Shift Confirmed',"pNote");
    update "tempshift" set "clientconfirmed" = 1 where "tempshiftplanid" = "LineNo";
    (select "t"."tempconfirmed","p"."tempmustconfirm" into "RequiresConfirm","MustTempConfirm" from "tempshift" as "t" key join "tempshiftplan" as "p" where "p"."tempshiftplanid" = "LineNo");
    if "RequiresConfirm" = 0 and "MustTempConfirm" = 1 then select '1:~success#PProvisional:requires confirmation by temp'
    else select '1:~success#BConfirmed:awaits timesheet'
    end if end if;
  -- caNcel or Reject
  if "pTransactionType" in( 'N','R' ) then
    set "StaffID" = (select "staffid" from "vacancy" where "vacancyid" = "VacID");
    set "LWhoCancelled" = (select "Staffid" from "iqxnetuser" where "iqxnetuserid" = "pWebUserID");
    --set UWhoCancelled='Cancelled by '+isnull((select name from iqxnetuser where iqxnetuserid = pWebUserID),'not recorded');
    set "PersID" = (select "personid" from "tempshift" right outer join "tempshiftplan" as "p" where "p"."tempshiftplanid" = "LineNo");
    update "tempshift" set "state" = 'C',"cancelreason" = "pCancelReason","whencancelled" = current timestamp,"confirmedwith" = "LWhoCancelled","WhoCancelled" = "LWhoCancelled" where "tempshiftplanid" = "LineNo";
    if @@Rowcount = 0 then
      set "sid" = "uniquekey"('1');
      insert into "tempshift"( "tempshiftid","vacancyid","shiftdate","timefrom","timeto","state","tempshiftplanid","cancelreason","whencancelled","confirmedwith" ) values( "sid","VacID","LShiftDate","LTimeFrom","LTimeTo",'C',"LTempShiftPlanID","pCancelReason",current timestamp,"LWhoCancelled" ) 
    end if;
    if "pTransactionType" = 'R' then
      call "IQXNetPopup"("pWebUserID","pPersonID","EmploymentID","VacID",null,null,'SHIFTSTATECHANGE','Shift Candidate Rejected - '+"ShiftRef","pNote")
    else
      call "IQXNetPopup"("pWebUserID","pPersonID","EmploymentID","VacID",null,null,'SHIFTSTATECHANGE','Shift Cancelled - '+"ShiftRef","pNote")
    end if;
    select '1:~success#CCancelled:'+(select "name" from "TempShiftCancelReason" where "TempShiftCancelReasonID" = "pCancelReason")
  end if;
  -- reQuest
  if "pTransactionType" = 'Q' then
    set "Note" = "substring"("pNote","charindex"('Request:',"pNote")+9,"charindex"(';',"pNote")-10);
    call "IQXNetPopup"("pWebUserID","PersID","EmploymentID","VacID",null,null,'SHIFTSTATECHANGE','Requested candidate - '+"ShiftRef","pNote");
    select '0:~success'
  end if;
  -- coMment
  if "pTransactionType" = 'M' then
    set "Note" = "substring"("pNote","charindex"('Note:',"pNote")+6);
    set "Note" = "substring"("Note",1,149);
    set "IntermediateString" = "substring"("pNote","charindex"('Shift Ref',"pNote")+10);
    set "ShiftRef" = "substring"("IntermediateString",1,"charindex"('Note:',"IntermediateString")-3);
    call "IQXNetPopup"("pWebUserID","PersID","EmploymentID","VacID",null,null,'SHIFTSTATECHANGE','Shift Note Added - '+"ShiftRef","pNote");
    select '0:~success'
  end if;
  -- Location
  if "pTransactionType" = 'L' then
    set "Note" = "substring"("pNote","charindex"('Note:',"pNote")+9,"charindex"(';',"pNote")-10);
    set "IntermediateString" = "substring"("pNote","charindex"('Shift Ref',"pNote")+10);
    set "ShiftRef" = "substring"("IntermediateString",1,"charindex"('Note:',"IntermediateString")-3);
    update "tempshiftplan" set "ClientNote" = "pLocation" where "TempShiftPlanID" = "LTempShiftPlanID";
    call "IQXNetPopup"("pWebUserID","PersID","EmploymentID","VacID",null,null,'SHIFTSTATECHANGE','Shift Location modified - '+"ShiftRef","pNote"+'Location: '+"pLocation");
    select '0:~success'
  end if
end /* DOC
2016-11-29 PC doc and test
only using Request and Reject

F=Confirm shift (not used at the moment)
R=Reject candidate
N=Cancel shift (not used at the moment)
Q=Request candidate
M=Shift Note (not used at the moment)
L=Change location of shift (not used at the moment)

Tests:
all invalid
valid pWebUserID
valid pWebUserID,valid pLineNo
valid pWebUserID,valid pLineNo, invalid pTransactionType
valid pWebUserID,valid pLineNo, pTransactionType=Q, pNote, pPersonID for vacancy
valid pWebUserID,valid pLineNo, pTransactionType=R, pNote, pPersonID
valid pWebUserID,valid pLineNo, pTransactionType=Q, pNote, pPersonID for Tempshift
*/
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.client';
select * from NetClientVacanciesLineStatusUpdate(null,null,null,null,null,null,null,null);
expect EOF;
select * from NetClientVacanciesLineStatusUpdate('test.client',null,null,null,null,null,null,null);
expect EOF;
select * from NetClientVacanciesLineStatusUpdate('test.client','VTEST',null,null,null,null,null,null);
expect EOF;
select * from NetClientVacanciesLineStatusUpdate('test.client','VTEST','Q','TestNote',null,null,null,'test');
expect 0*;
select top 1 * from diary where description like 'Requested Candidate%' order by whenentered desc;
expect staffid=TEST,personid=test,vacancyID=TEST,EmploymentID=TEST,notes=TestNote;
select * from NetClientVacanciesLineStatusUpdate('test.client','VTEST','R','TestNote',null,null,null,'test');
expect retval=1:~success#CCancelled:;
select top 1 * from diary where description like 'Shift Candidate Rejected%' order by whenentered desc;
expect staffid=TEST,personid=test,vacancyID=TEST,EmploymentID=TEST,notes=TestNote;
select * from NetClientVacanciesLineStatusUpdate('test.client','PTEST','Q','TestNote',null,null,null,'test');
expect 0*;
select top 1 * from diary where description like 'Requested Candidate%' order by whenentered desc;
expect staffid=TEST,personid=test,vacancyID=TEST,EmploymentID=TEST,notes=TestNote;
select * from NetClientVacanciesLineStatusUpdate('test.client','PTEST','R','TestNote',null,null,null,'test');
expect retval=1:~success#CCancelled:;
select top 1 * from diary where description like 'Shift Candidate Rejected%' order by whenentered desc;
expect staffid=TEST,personid=test,vacancyID=TEST,EmploymentID=TEST,notes=TestNote;
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientVacanciesLineStatusUpdate" IS 
{create PROCEDURE pears."NetClientVacanciesLineStatusUpdate"( in "pWebUserID" char(20),in "pLineNo" char(21),in "pTransactionType" char(1),in "pNote" char(255),in "pOurRef" char(20),in "pCancelReason" char(1) default null,in "pLocation" char(100) default null,in pPersonID char(20) default null ) 
result( "retval" char(250) ) 
// IQXWeb
begin
  declare "PorT" char(1);
  declare LineNo char(20);
  declare "VacID" char(20);
  declare "StaffID" char(20);
  declare "LShiftDate" date;
  declare "LTimeFrom" time;
  declare "LTimeTo" time;
  declare "sid" char(20);
  declare "LTempShiftPlanID" char(20);
  declare "LWhoCancelled" char(60);
  declare "RequiresConfirm" integer;
  declare "MustTempConfirm" integer;
  declare "Note" char(255);
  declare "IntNote" char(255);
  declare "ShiftRef" char(20);
  declare "Location" char(100);
  declare "EmploymentID" char(20);
  declare "PersID" char(20);
  declare "IntermediateString" char(150);
  
  set "PorT" = "left"("pLineNo",1); -- discover whether TempShiftPlan or TempShift
  set LineNo = right(pLineNo,length(pLineNo)-1);
  case PorT
    when 'V' then
        (select "t"."vacancyid","shiftdate","timefrom","timeto","tempshiftplanid","e"."employmentid",null,v.refcode into "VacID","LShiftDate","LTimeFrom","LTimeTo","LTempShiftPlanID","EmploymentID","PersID",ShiftRef from "tempshiftplan" as "t" key join "vacancy" v key join "employment" as "e" where v.vacancyid = "LineNo");
    when 'P' then
        (select "t"."vacancyid","t"."shiftdate","t"."timefrom","t"."timeto","t"."tempshiftplanid","e"."employmentid","p"."personid",tsp.shiftserialnumber into "VacID","LShiftDate","LTimeFrom","LTimeTo","LTempShiftPlanID","EmploymentID","PersID",ShiftRef from "tempshiftplan" as "tsp" key join "tempshift" as "t" key join "vacancy" key join "employment" as "e" left outer join "person" as "p" on "p"."personid" = "t"."personid" where "tsp"."TempShiftPlanID" = "LineNo")
    else
        return;
    end case;
  set PersID = isnull(PersID,pPersonID);
  -- conFirm
  if "pTransactionType" = 'F' then
    call "IQXNetPopup"("pWebUserID","PersID","EmploymentID","VacID",null,null,'SHIFTSTATECHANGE','Shift Confirmed',"pNote");
    update "tempshift" set "clientconfirmed" = 1 where "tempshiftplanid" = "LineNo";
    (select "t"."tempconfirmed","p"."tempmustconfirm" into "RequiresConfirm","MustTempConfirm" from "tempshift" as "t" key join "tempshiftplan" as "p" where "p"."tempshiftplanid" = "LineNo");
    if "RequiresConfirm" = 0 and "MustTempConfirm" = 1 then select '1:~success#PProvisional:requires confirmation by temp'
    else select '1:~success#BConfirmed:awaits timesheet'
    end if end if;
  -- caNcel or Reject
  if "pTransactionType" in( 'N','R' ) then
    set "StaffID" = (select "staffid" from "vacancy" where "vacancyid" = "VacID");
    set "LWhoCancelled" = (select "Staffid" from "iqxnetuser" where "iqxnetuserid" = "pWebUserID");
    --set UWhoCancelled='Cancelled by '+isnull((select name from iqxnetuser where iqxnetuserid = pWebUserID),'not recorded');
    set "PersID" = (select "personid" from "tempshift" right outer join "tempshiftplan" p where p.tempshiftplanid = "LineNo");
    update "tempshift" set "state" = 'C',"cancelreason" = "pCancelReason","whencancelled" = current timestamp,"confirmedwith" = "LWhoCancelled","WhoCancelled" = "LWhoCancelled" where "tempshiftplanid" = "LineNo";
    if @@Rowcount = 0 then
      set "sid" = "uniquekey"('1');
      insert into "tempshift"( "tempshiftid","vacancyid","shiftdate","timefrom","timeto","state","tempshiftplanid","cancelreason","whencancelled","confirmedwith" ) values( "sid","VacID","LShiftDate","LTimeFrom","LTimeTo",'C',"LTempShiftPlanID","pCancelReason",current timestamp,"LWhoCancelled" ) 
    end if;
    if "pTransactionType" = 'R' then
      call "IQXNetPopup"("pWebUserID","pPersonID","EmploymentID","VacID",null,null,'SHIFTSTATECHANGE','Shift Candidate Rejected - '+"ShiftRef","pNote")
    else
      call "IQXNetPopup"("pWebUserID","pPersonID","EmploymentID","VacID",null,null,'SHIFTSTATECHANGE','Shift Cancelled - '+"ShiftRef","pNote")
    end if;
    select '1:~success#CCancelled:'+(select "name" from "TempShiftCancelReason" where "TempShiftCancelReasonID" = "pCancelReason")
  end if;
  -- reQuest
  if "pTransactionType" = 'Q' then
    set "Note" = "substring"("pNote","charindex"('Request:',"pNote")+9,"charindex"(';',"pNote")-10);
    call "IQXNetPopup"("pWebUserID","PersID","EmploymentID","VacID",null,null,'SHIFTSTATECHANGE','Requested candidate - '+"ShiftRef","pNote");
    select '0:~success'
  end if;
  -- coMment
  if "pTransactionType" = 'M' then
    set "Note" = "substring"("pNote","charindex"('Note:',"pNote")+6);
    set "Note" = "substring"("Note",1,149);
    set "IntermediateString" = "substring"("pNote","charindex"('Shift Ref',"pNote")+10);
    set "ShiftRef" = "substring"("IntermediateString",1,"charindex"('Note:',"IntermediateString")-3);
    call "IQXNetPopup"("pWebUserID","PersID","EmploymentID","VacID",null,null,'SHIFTSTATECHANGE','Shift Note Added - '+"ShiftRef","pNote");
    select '0:~success'
  end if;
  -- Location
  if "pTransactionType" = 'L' then
    set "Note" = "substring"("pNote","charindex"('Note:',"pNote")+9,"charindex"(';',"pNote")-10);
    set "IntermediateString" = "substring"("pNote","charindex"('Shift Ref',"pNote")+10);
    set "ShiftRef" = "substring"("IntermediateString",1,"charindex"('Note:',"IntermediateString")-3);
    update "tempshiftplan" set "ClientNote" = "pLocation" where "TempShiftPlanID" = "LTempShiftPlanID";
    call "IQXNetPopup"("pWebUserID","PersID","EmploymentID","VacID",null,null,'SHIFTSTATECHANGE','Shift Location modified - '+"ShiftRef","pNote"+'Location: '+"pLocation");
    select '0:~success'
  end if
end

/* DOC
2016-11-29 PC doc and test
    only using Request and Reject
 
    F=Confirm shift (not used at the moment)
    R=Reject candidate
    N=Cancel shift (not used at the moment)
    Q=Request candidate
    M=Shift Note (not used at the moment)
    L=Change location of shift (not used at the moment)

Tests:
    all invalid
    valid pWebUserID
    valid pWebUserID,valid pLineNo
    valid pWebUserID,valid pLineNo, invalid pTransactionType
    valid pWebUserID,valid pLineNo, pTransactionType=Q, pNote, pPersonID for vacancy
    valid pWebUserID,valid pLineNo, pTransactionType=R, pNote, pPersonID
    valid pWebUserID,valid pLineNo, pTransactionType=Q, pNote, pPersonID for Tempshift
*/

/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.client';
select * from NetClientVacanciesLineStatusUpdate(null,null,null,null,null,null,null,null);
expect EOF;
select * from NetClientVacanciesLineStatusUpdate('test.client',null,null,null,null,null,null,null);
expect EOF;
select * from NetClientVacanciesLineStatusUpdate('test.client','VTEST',null,null,null,null,null,null);
expect EOF;
select * from NetClientVacanciesLineStatusUpdate('test.client','VTEST','Q','TestNote',null,null,null,'test');
expect 0*;
select top 1 * from diary where description like 'Requested Candidate%' order by whenentered desc;
expect staffid=TEST,personid=test,vacancyID=TEST,EmploymentID=TEST,notes=TestNote;
select * from NetClientVacanciesLineStatusUpdate('test.client','VTEST','R','TestNote',null,null,null,'test');
expect retval=1:~success#CCancelled:;
select top 1 * from diary where description like 'Shift Candidate Rejected%' order by whenentered desc;
expect staffid=TEST,personid=test,vacancyID=TEST,EmploymentID=TEST,notes=TestNote;
select * from NetClientVacanciesLineStatusUpdate('test.client','PTEST','Q','TestNote',null,null,null,'test');
expect 0*;
select top 1 * from diary where description like 'Requested Candidate%' order by whenentered desc;
expect staffid=TEST,personid=test,vacancyID=TEST,EmploymentID=TEST,notes=TestNote;
select * from NetClientVacanciesLineStatusUpdate('test.client','PTEST','R','TestNote',null,null,null,'test');
expect retval=1:~success#CCancelled:;
select top 1 * from diary where description like 'Shift Candidate Rejected%' order by whenentered desc;
expect staffid=TEST,personid=test,vacancyID=TEST,EmploymentID=TEST,notes=TestNote;
*/
}
</code>