====== pears.NetCandidateDocuments ======
<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"."NetCandidateDocuments"( in "pWebUserID" char(20),in "pPersonID" char(20) default null ) 
result( "DocType" char(50),"DocDescription" char(50),"LastUpdate" timestamp,"SpecialType" char(50),"DocumentID" char(20),"PersonID" char(20),"IsAuthorised" char(20),"SortOrder" integer ) 
// IQXWeb
begin
  declare "userClass" char(20);
  declare "isCandorAgency" smallint;
  if "iqxnethaspermission"("pwebuserid",'DOCCANDDOWNLOAD') = 0 then
    set "pPersonID" = null
  else
    set "userClass" = (select "iqxnetuserclassid" from "iqxnetuser" where "iqxnetuserid" = "pWebUserID");
    if "userClass" = 'CANDIDATE' then
      set "isCandorAgency" = 1;
      set "pPersonID" = "NetCandidateValidPersonID"("pWebUserID","pPersonID")
    else if "userClass" = 'AGENCY' then
        set "isCandorAgency" = 1;
        set "pPersonID" = (select "pay_employee"."personid"
            from "pay_employee" key join "company" as "agcomp" key join "employment" as "agemp" key join "iqxnetuserlink"
            where "pay_employee"."personid" = "ppersonid" and "iqxnetuserlink"."iqxnetuserid" = "pwebuserid")
      else
        set "isCandorAgency" = 0
      end if end if end if;
  select "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),'',"oledocument"."oledocumentid","oledocument"."ownerid",(if "blobstore"."publishtoweb" = 1 then 'Authorised' else 'Pending' endif),100+"isnull"("documenttype"."sortorder",0) as "ord"
    from "oledocument" join "blobstore" on "blobstore"."id" = "oledocument"."oledocumentid" and "blobstore"."class" = 'O'
      ,"oledocument" key left outer join "documenttype"
    where "oledocument"."WhenArchived" is null and "oledocument"."ownertype" = 'P' and "oledocument"."ownerid" = "ppersonid" and("oledocument"."rawfile" = 1 or "blobstore"."zipped" = 2) and "isnull"("oledocument"."defunct",0) = 0
    and("blobstore"."publishtoweb" = 1 or("isCandorAgency" = 1 and "blobstore"."changedby" = (select "userid" from "staff" where "staffid" = "userstaffid"))) union all
  select 'Primary','CV',"isnull"("blobstore"."changedat","blobstore"."createdat"),'PRIMARY_CV',null,"blobstore"."id",(if "blobstore"."publishtoweb" = 1 then 'Authorised' else 'Pending' endif),1 as "ord"
    from "blobstore" where "blobstore"."class" = 'V' and "blobstore"."id" = "pPersonID" and "blobstore"."zipped" = 2 and("isCandorAgency" = 1 or "blobstore"."publishtoweb" = 1) union all
  select 'Primary','Photo',"isnull"("blobstore"."changedat","blobstore"."createdat"),'PRIMARY_PHOTO',null,"blobstore"."id",(if("blobstore"."publishtoweb" = 1 or "isCandorAgency" = 1) then 'Authorised' else 'Pending' endif),2 as "ord"
    from "blobstore" where "blobstore"."class" = 'J' and "blobstore"."id" = "pPersonID" and("isCandorAgency" = 1 or "blobstore"."publishtoweb" = 1)
    order by "ord" asc
end /* DOC
2016-12-02 PC Test and doc
2017-11-03 MHS allowed cands and agencies to see files they uploaded even if not yet published
2017-12-11 PC IsAuthorised column added to display Pending or Authorised
2018-02-09 PC primary photo modified to always show approved
2018-06-01 MHS Exclude archived documents. Fix tests.
2018-07-11 MHS outer join to documenttype because T&Cs don't have an obvious way of getting a documenttype. Remove relevant oledocument rows before starting test.
2018-10-22 MHS IW-456 multi-cand fix and tests
2019-03-05 PC IW-873 add top 1 and order by on subqueries

Tests:
invalid pWebUserID
Valid pWebUserID, invalid pPersonID
Valid pWebUserID, pPersonID as candidate
Valid pWebUserID, pPersonID as agency

*/
/* TEST
call NetTestSetup('');
insert into documenttype (documenttypeid,description,class) on existing update values ('TEST','test','P');
delete from blobstore where id='TEST';
delete from oledocument where ownertype='P' and ownerid='TEST';
insert into blobstore (class,id) values ('O','TEST');
insert into oledocument (oledocumentid,description,documenttypeid,ownerid,ownertype,rawfile) on existing update values ('TEST','test','TEST','TEST','P',1);
select * from NetCandidateDocuments('test.candidate',null);
expect EOF;
select * from NetCandidateDocuments('test.candidate','TEST');
expect EOF;
update blobstore set publishtoweb=1 where id='TEST';
select * from NetCandidateDocuments('test.candidate','TEST');
expect DocType=test, DocDescription=test, DocumentID=TEST, PersonID=TEST;
update blobstore set class='V',zipped=2 where id='TEST';
select * from NetCandidateDocuments('test.candidate','TEST');
expect DocType=Primary, DocDescription=CV, SpecialType=PRIMARY_CV, PersonID=TEST;
update blobstore set class='J',zipped=0 where id='TEST';
select * from NetCandidateDocuments('test.candidate','TEST');
expect DocType=Primary, DocDescription=Photo, SpecialType=PRIMARY_PHOTO, PersonID=TEST;
insert into pay_employee (personid,secondaryagencyid) on existing update values ('TEST','TEST');
select * from NetCandidateDocuments('test.agency','TEST');
expect DocType=Primary, DocDescription=Photo, SpecialType=PRIMARY_PHOTO, PersonID=TEST;
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateDocuments" IS 
{create PROCEDURE pears."NetCandidateDocuments"(in pWebUserID char(20),in pPersonID char(20) default null)
result(DocType char(50),DocDescription char(50),LastUpdate timestamp,SpecialType char(50),DocumentID char(20),PersonID char(20),IsAuthorised char(20),SortOrder integer)
// IQXWeb
begin
  declare userClass char(20);
  declare isCandorAgency smallint;
  if iqxnethaspermission(pwebuserid,'DOCCANDDOWNLOAD') = 0 then
    set pPersonID=null
  else
    set userClass=(select iqxnetuserclassid from iqxnetuser where iqxnetuserid = pWebUserID);
    if userClass = 'CANDIDATE' then
      set isCandorAgency=1;
      set pPersonID=NetCandidateValidPersonID(pWebUserID,pPersonID);
    else if userClass = 'AGENCY' then
        set isCandorAgency=1;
        set pPersonID=(select pay_employee.personid
            from pay_employee key join company as agcomp key join employment as agemp key join iqxnetuserlink
            where pay_employee.personid = ppersonid and iqxnetuserlink.iqxnetuserid = pwebuserid)
      else
        set isCandorAgency=0
      end if
    end if
  end if;
  select documenttype.description,oledocument.description,isnull(blobstore.changedat,blobstore.createdat),'',oledocument.oledocumentid,oledocument.ownerid,(if blobstore.publishtoweb=1 then 'Authorised' else 'Pending' endif),100+isnull(documenttype.sortorder,0) as ord
    from oledocument join blobstore on blobstore.id = oledocument.oledocumentid and blobstore.class = 'O'
    ,oledocument key left outer join documenttype
    where oledocument.WhenArchived IS NULL and oledocument.ownertype = 'P' and oledocument.ownerid = ppersonid and (oledocument.rawfile = 1 or blobstore.zipped = 2) and isnull(oledocument.defunct,0)=0 
    and (blobstore.publishtoweb = 1 or (isCandorAgency=1 and blobstore.changedby=(select userid from staff where staffid=userstaffid))) union all
  select 'Primary','CV',isnull(blobstore.changedat,blobstore.createdat),'PRIMARY_CV',null,blobstore.id,(if blobstore.publishtoweb=1 then 'Authorised' else 'Pending' endif),1 as ord
    from blobstore where blobstore.class = 'V' and blobstore.id = pPersonID and blobstore.zipped = 2 and(isCandorAgency = 1 or blobstore.publishtoweb = 1) union all
  select 'Primary','Photo',isnull(blobstore.changedat,blobstore.createdat),'PRIMARY_PHOTO',null,blobstore.id,(if (blobstore.publishtoweb=1 or isCandorAgency = 1) then 'Authorised' else 'Pending' endif),2 as ord
    from blobstore where blobstore.class = 'J' and blobstore.id = pPersonID and(isCandorAgency = 1 or blobstore.publishtoweb = 1) order by
    ord asc
end

/* DOC
2016-12-02 PC Test and doc
2017-11-03 MHS allowed cands and agencies to see files they uploaded even if not yet published
2017-12-11 PC IsAuthorised column added to display Pending or Authorised
2018-02-09 PC primary photo modified to always show approved
2018-06-01 MHS Exclude archived documents. Fix tests.
2018-07-11 MHS outer join to documenttype because T&Cs don't have an obvious way of getting a documenttype. Remove relevant oledocument rows before starting test.
2018-10-22 MHS IW-456 multi-cand fix and tests
2019-03-05 PC IW-873 add top 1 and order by on subqueries

Tests:
    invalid pWebUserID
    Valid pWebUserID, invalid pPersonID
    Valid pWebUserID, pPersonID as candidate
    Valid pWebUserID, pPersonID as agency

*/

/* TEST
call NetTestSetup('');
insert into documenttype (documenttypeid,description,class) on existing update values ('TEST','test','P');
delete from blobstore where id='TEST';
delete from oledocument where ownertype='P' and ownerid='TEST';
insert into blobstore (class,id) values ('O','TEST');
insert into oledocument (oledocumentid,description,documenttypeid,ownerid,ownertype,rawfile) on existing update values ('TEST','test','TEST','TEST','P',1);
select * from NetCandidateDocuments('test.candidate',null);
expect EOF;
select * from NetCandidateDocuments('test.candidate','TEST');
expect EOF;
update blobstore set publishtoweb=1 where id='TEST';
select * from NetCandidateDocuments('test.candidate','TEST');
expect DocType=test, DocDescription=test, DocumentID=TEST, PersonID=TEST;
update blobstore set class='V',zipped=2 where id='TEST';
select * from NetCandidateDocuments('test.candidate','TEST');
expect DocType=Primary, DocDescription=CV, SpecialType=PRIMARY_CV, PersonID=TEST;
update blobstore set class='J',zipped=0 where id='TEST';
select * from NetCandidateDocuments('test.candidate','TEST');
expect DocType=Primary, DocDescription=Photo, SpecialType=PRIMARY_PHOTO, PersonID=TEST;
insert into pay_employee (personid,secondaryagencyid) on existing update values ('TEST','TEST');
select * from NetCandidateDocuments('test.agency','TEST');
expect DocType=Primary, DocDescription=Photo, SpecialType=PRIMARY_PHOTO, PersonID=TEST;
*/
}
</code>