====== pears.NetCandidateDoTermsAndConditions ======
<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"."NetCandidateDoTermsAndConditions"( in "pWebUserID" char(20),in "pStage" char(1),in "pDocumentID" char(20) default null,in "pDivisionID" char(20) default null,in "pDepartmentID" char(2) default null,in "pPersonID" char(20) default null ) 
result( "pResult" long varchar ) 
// IQXNet
begin
  declare "SourceDocID" char(20);
  declare "NewDocID" char(20);
  set "pPersonID" = "NetCandidateValidPersonID"("pWebUserID","pPersonID");
  if "pPersonID" is null then
    select '97:~Unique candidate link required';
    return
  end if;
  if "pStage" = 'A' and not "pDocumentID" = any(select "oledocumentid" from "oledocument" where "ownertype" = 'P' and "ownerid" = "pPersonID") then
    select '99:~Permission denied';
    return
  end if;
  if "pStage" = 'O' then
    set "SourceDocID" = (select top 1 "globaldocumentid"
        from(select top 1 "globaldocumentid",
            if exists(select * from "globaldocumentdetail" where "globaldocumentid" = "gd"."globaldocumentid" and "doctype" = 'DIV' and "docid" = "pDivisionID") then 1 else 0 endif as "matchDiv",
            if exists(select * from "globaldocumentdetail" where "globaldocumentid" = "gd"."globaldocumentid" and "doctype" = 'DEPT' and "docid" = "pDepartmentID") then 1 else 0 endif as "matchDept",
            if exists(select * from "globaldocumentdetail" where "globaldocumentid" = "gd"."globaldocumentid" and "doctype" = 'DIV') then 0 else 1 endif as "globalDiv",
            if exists(select * from "globaldocumentdetail" where "globaldocumentid" = "gd"."globaldocumentid" and "doctype" = 'DEPT') then 0 else 1 endif as "globalDept",
            "matchDiv"+"matchDept" as "score"
            from "globaldocument" as "gd"
            where "description" = 'WEBFETCHTERMS' and("matchDiv" = 1 or "globalDiv" = 1) and("matchDept" = 1 or "globalDept" = 1) and "isnull"("defunct",0) = 0 and "iqxnetuserclassid" = 'CANDIDATE'
            order by "score" desc) as "docs" order by "globaldocumentid" asc);
    if "SourceDocID" is null then
      select '0:~No available T&Cs';
      return
    end if;
    set "NewDocID" = "uniquekey"('');
    if "BlobstoreCopy"('G',"SourceDocID",'O',"NewDocID",'IQXWeb') <> 0 then
      select '5:~Unable to generate T&Cs';
      return
    end if;
    insert into "oledocument"( "oledocumentid","description","ownertype","ownerid","rawfile","fileextension" ) select "NewDocID",'T&Cs Offered '+"dateformat"(current timestamp,'dd/mm/yyyy hh:nn'),'P',"pPersonID",1,"fileextension" from "globaldocument" where "globaldocumentid" = "SourceDocID";
    update "blobstore" set "publishtoweb" = 1 where "class" = 'O' and "id" = "NewDocID";
    select '0:'+"NewDocID"+'~Success';
    return
  else if "pStage" = 'A' then
      update "oledocument" set "description" = 'T&Cs Accepted '+"dateformat"(current timestamp,'dd/mm/yyyy hh:nn') where "oledocumentid" = "pDocumentID";
      select '0:'+"pDocumentID"+'~Success';
      return
    else if "pStage" = 'R' then
        delete from "phone" where "who" = 'P' and "whoid" = "pPersonID";
        delete from "tagvalue" where "taglocation" = 'P' and "id" = "pPersonID";
        delete from "iqxnetuserlink" where "personid" = "pPersonID";
        delete from "iqxnetuser" where "iqxnetuserid" = "pWebUserID";
        delete from "blobstore" where "class" = 'O' and "id" = any(select "oledocumentid" from "oledocument" where "ownertype" = 'P' and "ownerid" = "pPersonID");
        delete from "oledocument" where "ownertype" = 'P' and "ownerid" = "pPersonID";
        delete from "person" where "personid" = "pPersonID";
        select '0:~Success';
        return
      else
        select '3:~Invalid pStage';
        return
      end if
    end if
  end if
end /* DOC
pStage: O=Offer, A=Accept, R=Refuse
2018-07-24 PC include improved error handler
2018-10-22 MHS IW-456 multi-cand fix and tests
2018-12-12 GJ Removed pwebuserid check and replaced with pPersonID
2019-03-05 PC IW-873 add top 1 and order by on subqueries
2020-6-9 MHS added extra 'top 1' to ensure correct selection of specific matching doc over general fallback
*/
/* TEST
call NetTestSetup('');
delete from blobstore where class='O' and id in (select oledocumentid from oledocument where ownertype='P' and ownerid='TEST');
delete from oledocument where ownertype='P' and ownerid='TEST';
select count(*) from oledocument where ownertype='P' and ownerid='TEST';
expect 0;
CREATE OR REPLACE VARIABLE iqblobtemp long binary = 'abc';
select blobstorewrite('G','TESTTCGLOB',0,'WebTest');
expect 0;
insert into globaldocument (globaldocumentid,description,iqxnetuserclassid,fileextension) on existing update values ('TESTTCGLOB','WEBFETCHTERMS','CANDIDATE','pdf');
select pResult from NetCandidateDoTermsAndConditions('test.candidate','O');
expect 0:*;
select count(*) from oledocument where ownertype='P' and ownerid='TEST' and description like '%offered%';
expect 1;
select pResult from NetCandidateDoTermsAndConditions('test.candidate','A',(select first oledocumentid from oledocument where ownertype='P' and ownerid='TEST'));
expect 0:*;
select count(*) from oledocument where ownertype='P' and ownerid='TEST' and description like '%accepted%';
expect 1;
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateDoTermsAndConditions" IS 
{create PROCEDURE pears."NetCandidateDoTermsAndConditions"(in pWebUserID char(20), in pStage char(1), in pDocumentID char(20) default null, in pDivisionID char(20) default null, in pDepartmentID char(2) default null, in pPersonID char(20) default null )
RESULT(pResult long varchar )
// IQXNet
BEGIN
declare SourceDocID char(20);
declare NewDocID char(20);
set pPersonID=NetCandidateValidPersonID(pWebUserID,pPersonID);
if pPersonID is null then
   select '97:~Unique candidate link required';
   return;
end if;
if pStage = 'A' and pDocumentID not in (select oledocumentid from oledocument where   ownertype='P' and ownerid=pPersonID) then
  select '99:~Permission denied';
  return
end if;

if pStage='O' then
  set SourceDocID = (select top 1 globaldocumentid from
    (select top 1 globaldocumentid, 
      if exists(select * from globaldocumentdetail where globaldocumentid=gd.globaldocumentid and doctype='DIV' and docid=pDivisionID) then 1 else 0 endif as matchDiv,  
      if exists(select * from globaldocumentdetail where globaldocumentid=gd.globaldocumentid and doctype='DEPT' and docid=pDepartmentID) then 1 else 0 endif as matchDept,  
      if exists(select * from globaldocumentdetail where globaldocumentid=gd.globaldocumentid and doctype='DIV') then 0 else 1 endif as globalDiv,  
      if exists(select * from globaldocumentdetail where globaldocumentid=gd.globaldocumentid and doctype='DEPT') then 0 else 1 endif as globalDept,
      matchDiv + matchDept as score
    from globaldocument gd
    where 
      description='WEBFETCHTERMS' and (matchDiv=1 or globalDiv=1) and (matchDept=1 or globalDept=1) and isnull(defunct,0)=0 and iqxnetuserclassid='CANDIDATE'
    order by score desc ) as docs order by globaldocumentid); 
  if SourceDocID is null then
    select '0:~No available T&Cs';
    return;
  end if;
  set NewDocID=uniquekey('');
  if BlobstoreCopy('G',SourceDocID,'O',NewDocID,'IQXWeb')<>0 then
    select '5:~Unable to generate T&Cs';
    return;
  end if;
  insert into oledocument(oledocumentid,description,ownertype,ownerid,rawfile,fileextension) select NewDocID,'T&Cs Offered '+dateformat(current timestamp, 'dd/mm/yyyy hh:nn'),'P',pPersonID,1,fileextension from globaldocument where globaldocumentid=SourceDocID;
  update blobstore set publishtoweb=1 where class='O' and id=NewDocID;
  select '0:'+NewDocID+'~Success';
  return;
else if pStage='A' then
  update oledocument set description='T&Cs Accepted '+dateformat(current timestamp, 'dd/mm/yyyy hh:nn') where oledocumentid=pDocumentID;
  select '0:'+pDocumentID+'~Success';
  return;
else if pStage='R' then
  delete from phone where who='P' and whoid=pPersonID;
  delete from tagvalue where taglocation='P' and id=pPersonID;
  delete from iqxnetuserlink where personid=pPersonID;
  delete from iqxnetuser where iqxnetuserid=pWebUserID;
  delete from blobstore where class='O' and id in (select oledocumentid from oledocument where ownertype='P' and ownerid=pPersonID);
  delete from oledocument where ownertype='P' and ownerid=pPersonID;
  delete from person where personid=pPersonID;
  select '0:~Success';
  return;
else
  select '3:~Invalid pStage';
  return;
end if;
end if;
end if;
END

/* DOC
pStage: O=Offer, A=Accept, R=Refuse
2018-07-24 PC include improved error handler
2018-10-22 MHS IW-456 multi-cand fix and tests
2018-12-12 GJ Removed pwebuserid check and replaced with pPersonID
2019-03-05 PC IW-873 add top 1 and order by on subqueries
2020-6-9 MHS added extra 'top 1' to ensure correct selection of specific matching doc over general fallback
*/

/* TEST
call NetTestSetup('');
delete from blobstore where class='O' and id in (select oledocumentid from oledocument where ownertype='P' and ownerid='TEST');
delete from oledocument where ownertype='P' and ownerid='TEST';
select count(*) from oledocument where ownertype='P' and ownerid='TEST';
expect 0;
CREATE OR REPLACE VARIABLE iqblobtemp long binary = 'abc';
select blobstorewrite('G','TESTTCGLOB',0,'WebTest');
expect 0;
insert into globaldocument (globaldocumentid,description,iqxnetuserclassid,fileextension) on existing update values ('TESTTCGLOB','WEBFETCHTERMS','CANDIDATE','pdf');
select pResult from NetCandidateDoTermsAndConditions('test.candidate','O');
expect 0:*;
select count(*) from oledocument where ownertype='P' and ownerid='TEST' and description like '%offered%';
expect 1;
select pResult from NetCandidateDoTermsAndConditions('test.candidate','A',(select first oledocumentid from oledocument where ownertype='P' and ownerid='TEST'));
expect 0:*;
select count(*) from oledocument where ownertype='P' and ownerid='TEST' and description like '%accepted%';
expect 1;
*/
}
</code>