====== pears.NetAgencyCurrentRequirementCandidateSubmit ======
<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"."NetAgencyCurrentRequirementCandidateSubmit"( in "pWebUserID" char(20),in "pPersonID" char(20),in "pLineID" char(22),in "pNote" long varchar default null ) 
result( "pResult" char(250) ) 
// IQXWeb
begin
  declare "SecAgID" char(20);
  declare "sname" char(100);
  declare "RefCode" char(20);
  declare "LineId" char(20);
  declare "LineType" char(1);
  declare "dStaffId" char(20);
  declare "dStaffName" char(60);
  declare "NoteText" char(255);
  if "IQXNetHasPermission"("pWebUserID",'SUBMITCANDIDATES') = 0 then
    select '99:~Permission denied';
    return
  end if;
  set "SecAgID" = (select top 1 "employment"."companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" order by "isnull"("employment"."leavedate",current date) asc);
  set "LineType" = "left"("pLineID",1);
  if "LineType" = 'V' then
    set "LineID" = "right"("pLineID",20);
    set "dStaffID" = (select "staffid" from "vacancy" where "vacancyid" = "LineID")
  end if;
  if "LineType" = 'P' then
    set "LineID" = (select "TempshiftPlanID" from "tempshiftplan" where "tempshiftplanid" = "right"("pLineID",20));
    set "dStaffID" = (select "staffid" from "tempshiftplan" where "tempshiftplanid" = "LineID")
  end if;
  set "dStaffName" = (select "name" from "staff" where "staffid" = "dStaffID");
  set "NoteText" = 'Candidate submitted at '+cast("now"() as char)+' by '+"dStaffName"+(if "isnull"("pNote",'') <> '' then "pNote" else null endif);
  if "LineType" = 'V' then
    if not "LineId" = any(select "vacancyid" from "cascadedvacancy" where "secondaryagencyid" = "secagid") then
      select '98:~Permission denied';
      return
    end if;
    set "sname" = (select "name" from "person" where "personid" = "ppersonid");
    if(select(if "count"() = 0 then
          'FALSE'
        else
          'TRUE'
        endif) as "IsPresent"
        from "progress" as "pr"
          left outer join "status" as "s" on "s"."status" = "pr"."status" where "pr"."personid" = "pPersonID"
        and "pr"."vacancyid" = "LineID"
        and "s"."type" = 'R'
        and "s"."final" = 0) = 'FALSE' then
      insert into "progress"( "progressid","vacancyid","personid","staffid","status","actiondate","note" ) values( "uniquekey"("ppersonid"+"LineID"),"LineID","ppersonid","dStaffID",'A',current date,"NoteText" ) 
    else
      select '99:~'+"sname"+' has already been submitted';
      return
    end if end if;
  if "LineType" = 'P' then
    if(select "count"() from "cascadedshift" where "secondaryagencyid" = "secagid" and "tempshiftplanid" = "LineID") = 0 then
      select '99:~Permission denied';
      return
    end if;
    insert into "tempshiftprogress"( "tempshiftprogressid","tempshiftplanid","personid","staffid","status","note" ) values( "uniquekey"("ppersonid"+"LineID"),"LineID","ppersonid","dStaffID",'A',"NoteText" ) 
  end if;
  select '0:~Success'
end /* DOC
2016-11-28 PC Test and doc
2018-06-07 PC revise tests
2019-03-05 PC IW-873 add top 1 and order by on subqueries

Tests:
all valid but not cascaded
all valid and cascaded for P
repeat all valid for V

*/
/* TEST
call NetTestSetup('');
delete from cascadedshift where cascadedshiftid='TEST';
delete from cascadedvacancy where vacancyid like 'TEST%';
delete from tempshiftplan where tempshiftplanid='TESTTESTTESTTESTTEST';
delete from progress where vacancyid='TESTTESTTESTTESTTEST';
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.candidate','test.client','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.agency';
insert into iqxnetrightallowed (iqxnetrightid,iqxnetuserclassid,id,maintainonweb) on existing update defaults off values ('SUBMITCANDIDATES','AGENCY','*','1');
insert into vacancy (vacancyid,departmentid,employmentid,status,position,staffid) on existing update defaults off values ('TESTTESTTESTTESTTEST','~~','TEST','[','test2','test');
select * from NetAgencyCurrentRequirementCandidateSubmit('test.agency','TEST','VTESTTESTTESTTESTTEST');
expect 98*;
insert into tempshiftplan (tempshiftplanid,vacancyid,description) values ('TESTTESTTESTTESTTEST','TESTTESTTESTTESTTEST','test2');
insert into cascadedshift (cascadedshiftid,tempshiftplanid,secondaryagencyid,cascadetime,cascadelevel,method) values ('TEST','TESTTESTTESTTESTTEST','TEST',now(*),1,9); 
select * from NetAgencyCurrentRequirementCandidateSubmit('test.agency','TEST','PTest99');
expect 99*;
select * from NetAgencyCurrentRequirementCandidateSubmit('test.agency','TEST','PTESTTESTTESTTESTTEST');
expect 0*;
select * from tempshiftprogress where tempshiftplanid='TESTTESTTESTTESTTEST';
expect PersonID=TEST;
expect EOF;
update tempshiftprogress set status='R' where tempshiftplanid='TESTTESTTESTTESTTEST';
insert into cascadedvacancy (CascadedVacancyID,VacancyID,SecondaryAgencyID,CascadeTime,CascadeLevel,method) values ('TEST2','TESTTESTTESTTESTTEST','TEST',now(*),1,1);
select * from NetAgencyCurrentRequirementCandidateSubmit('test.agency','TEST','VTESTTESTTESTTESTTEST');
expect 0:~Success;
select * from NetAgencyCurrentRequirementCandidateSubmit('test.agency','TEST','VTESTTESTTESTTESTTEST');
expect 99:~test*;
delete from cascadedvacancy where vacancyid like 'TEST%';
delete from cascadedshift where cascadedshiftid='TEST';
delete from tempshiftplan where tempshiftplanid='TESTTESTTESTTESTTEST';
delete from progress where vacancyid='TESTTESTTESTTESTTEST';

*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetAgencyCurrentRequirementCandidateSubmit" IS 
{create PROCEDURE pears."NetAgencyCurrentRequirementCandidateSubmit"( in "pWebUserID" char(20),in "pPersonID" char(20),in "pLineID" char(22), in pNote long varchar default null) 
result( "pResult" char(250) ) 
// IQXWeb
begin
  declare SecAgID char(20);
  declare sname char(100);
  declare RefCode char(20);
  declare LineId char(20);
  declare LineType char(1);
  declare dStaffId char(20);
  declare dStaffName char(60);
  declare NoteText char(255);
  if "IQXNetHasPermission"("pWebUserID",'SUBMITCANDIDATES') = 0 then
    select '99:~Permission denied';
    return
  end if;
  
  set SecAgID = (select top 1 "employment"."companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" order by isnull(employment.leavedate,current date));
  set LineType=left(pLineID,1);
  if LineType='V' then 
    set LineID = right(pLineID,20); 
    set dStaffID = (select "staffid" from "vacancy" where "vacancyid" = "LineID");
  end if;
  if LineType='P' then 
    set LineID = (select TempshiftPlanID from tempshiftplan where tempshiftplanid=right(pLineID,20)); 
    set dStaffID = (select "staffid" from tempshiftplan where tempshiftplanid = "LineID");
  end if;
  
  set dStaffName=(select name from staff where staffid=dStaffID);

  set NoteText='Candidate submitted at '+ cast(now(*) as char) + ' by ' + dStaffName + (if isnull(pNote,'')!='' then pNote else null endif);
  if LineType='V' then 
    if not "LineId" = any(select "vacancyid" from "cascadedvacancy" where "secondaryagencyid" = secagid) then
      select '98:~Permission denied';
      return
    end if;
    set sname = (select "name" from "person" where "personid" = "ppersonid");
    if(select(if "count"() = 0 then 
    	'FALSE'
    else 
	    'TRUE'
    endif) as "IsPresent" 
  from 
	"progress" as "pr" 
	left outer join "status" as "s" on "s"."status" = "pr"."status" where "pr"."personid" = "pPersonID" 
	and "pr"."vacancyid" = "LineID" 
	and "s"."type" = 'R' 
	and "s"."final" = 0) = 'FALSE' 
	then
    	insert into "progress"( "progressid","vacancyid","personid","staffid","status","actiondate",note ) values( "uniquekey"("ppersonid"+"LineID"),"LineID","ppersonid","dStaffID",'A',current date,NoteText ) 
    else
    	select '99:~'+sname+' has already been submitted';
    	return
    end if 
  end if;
  if LineType='P' then 
	if (select count(*) from "cascadedshift" where "secondaryagencyid" = secagid and tempshiftplanid=LineID ) = 0 then
    	select '99:~Permission denied';
    	return
    end if;
    insert into "tempshiftprogress"( "tempshiftprogressid","tempshiftplanid","personid","staffid","status",note ) values( "uniquekey"("ppersonid"+"LineID"),"LineID","ppersonid","dStaffID",'A' ,NoteText) 
  end if;
  select '0:~Success';
end

/* DOC
2016-11-28 PC Test and doc
2018-06-07 PC revise tests
2019-03-05 PC IW-873 add top 1 and order by on subqueries

Tests:
    all valid but not cascaded
    all valid and cascaded for P
    repeat all valid for V

*/

/* TEST
call NetTestSetup('');
delete from cascadedshift where cascadedshiftid='TEST';
delete from cascadedvacancy where vacancyid like 'TEST%';
delete from tempshiftplan where tempshiftplanid='TESTTESTTESTTESTTEST';
delete from progress where vacancyid='TESTTESTTESTTESTTEST';
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.candidate','test.client','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.agency';
insert into iqxnetrightallowed (iqxnetrightid,iqxnetuserclassid,id,maintainonweb) on existing update defaults off values ('SUBMITCANDIDATES','AGENCY','*','1');
insert into vacancy (vacancyid,departmentid,employmentid,status,position,staffid) on existing update defaults off values ('TESTTESTTESTTESTTEST','~~','TEST','[','test2','test');
select * from NetAgencyCurrentRequirementCandidateSubmit('test.agency','TEST','VTESTTESTTESTTESTTEST');
expect 98*;
insert into tempshiftplan (tempshiftplanid,vacancyid,description) values ('TESTTESTTESTTESTTEST','TESTTESTTESTTESTTEST','test2');
insert into cascadedshift (cascadedshiftid,tempshiftplanid,secondaryagencyid,cascadetime,cascadelevel,method) values ('TEST','TESTTESTTESTTESTTEST','TEST',now(*),1,9); 
select * from NetAgencyCurrentRequirementCandidateSubmit('test.agency','TEST','PTest99');
expect 99*;
select * from NetAgencyCurrentRequirementCandidateSubmit('test.agency','TEST','PTESTTESTTESTTESTTEST');
expect 0*;
select * from tempshiftprogress where tempshiftplanid='TESTTESTTESTTESTTEST';
expect PersonID=TEST;
expect EOF;
update tempshiftprogress set status='R' where tempshiftplanid='TESTTESTTESTTESTTEST';
insert into cascadedvacancy (CascadedVacancyID,VacancyID,SecondaryAgencyID,CascadeTime,CascadeLevel,method) values ('TEST2','TESTTESTTESTTESTTEST','TEST',now(*),1,1);
select * from NetAgencyCurrentRequirementCandidateSubmit('test.agency','TEST','VTESTTESTTESTTESTTEST');
expect 0:~Success;
select * from NetAgencyCurrentRequirementCandidateSubmit('test.agency','TEST','VTESTTESTTESTTESTTEST');
expect 99:~test*;
delete from cascadedvacancy where vacancyid like 'TEST%';
delete from cascadedshift where cascadedshiftid='TEST';
delete from tempshiftplan where tempshiftplanid='TESTTESTTESTTESTTEST';
delete from progress where vacancyid='TESTTESTTESTTESTTEST';

*/
}
</code>