====== pears.NetStandardDocumentList ======
<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"."NetStandardDocumentList"( in "pWebUserID" char(20) ) 
result( "DocumentID" char(20),"DocumentDescription" char(100),"DocumentExtension" char(10) ) 
//IQXWeb
begin
  declare "UserClass" char(20);
  declare local temporary table "DivisionList"(
    "ID" char(20) not null,) not transactional;
  declare local temporary table "DepartmentList"(
    "ID" char(20) not null,) not transactional;
  set "UserClass" = (select top 1 "iqxnetuserclassid" from "iqxnetuser" where "iqxnetuserid" = "pWebUserID");
  case "UserClass"
  when 'CANDIDATE' then
    insert into "DivisionList" select "p"."divisionid" from "person" as "p" key join "iqxnetuserlink" as "l" where "l"."iqxnetuserid" = "pWebUserID" and "p"."divisionid" is not null;
    insert into "DepartmentList" select "s"."departmentid" from "search" as "s" key join "person" as "p" key join "iqxnetuserlink" as "l" where "l"."iqxnetuserid" = "pWebUserID" and("s"."temp" = 1 or "s"."permanent" = 1) and "s"."departmentid" is not null
  when 'CLIENT' then
    insert into "DivisionList" select "c"."divisionid" from "company" as "c" key join "employment" as "e" key join "iqxnetuserlink" as "l" where "l"."iqxnetuserid" = "pWebUserID" and "c"."divisionid" is not null
  when 'AGENCY' then
    insert into "DivisionList" select "c"."divisionid" from "company" as "c" key join "employment" as "e" key join "iqxnetuserlink" as "l" where "l"."iqxnetuserid" = "pWebUserID" and "c"."divisionid" is not null
  end case;
  select distinct
    "b"."id",
    "g"."description",
    "isnull"("g"."fileextension","reverse"("left"("reverse"("g"."description"),"charindex"('.',"reverse"("g"."description"))-1)))
    from "blobstore" as "b"
      join "globaldocument" as "g" on "b"."id" = "g"."globaldocumentID" and "b"."class" = 'G'
      left outer join "globaldocumentdetail" as "dep" on "g"."globaldocumentID" = "dep"."globaldocumentID" and "dep"."doctype" = 'DEPT'
      ,"globaldocument" as "g"
      left outer join "globaldocumentdetail" as "div" on "g"."globaldocumentID" = "div"."globaldocumentID" and "div"."doctype" = 'DIV'
    where("dep"."docid" = any(select "id" from "DepartmentList") or "dep"."docid" is null)
    and("div"."docid" = any(select "id" from "DivisionList") or "div"."docid" is null)
    and "isnull"("b"."publishtoweb",0) = 1
    and("g"."iqxnetuserclassid" = "UserClass" or "g"."iqxnetuserclassid" = '%' or "g"."iqxnetuserclassid" is null)
end /* DOC
2018-03-14 PC IW-108 and OP-42
2018-04-25 GJ Removed document type - as no longer required
2018-04-26 PC,MS optimise joins
2018-06-13 MS respect globaldocument.fileextension if supplied
2018-09-13 PC test routine
2018-10-23 MHS IW-456 multi-cand fix, including general re-write of proc and tests
2020-34-1 MHS IQ-1362 Changed to reflect new architecture. Will work with the globaldocumentdetail compatibility view
but tests will only work with IQX >= 2.20.4 because the new globaldocumentlink table must exist
*/
/* TEST
def t select * from NetStandardDocumentList('test.candidate') where DocumentID='TEST';
insert into blobstore (class,id,publishtoweb) on existing update values ('G','TEST',1);
insert into globaldocument (globaldocumentid,description,iqxnetuserclassid,fileextension) on existing update values ('TEST','TestDoc.txt',null,'txt');
delete from globaldocumentlink where globaldocumentid='TEST';
delete from search where personid='TEST';
update person set divisionid=null where personid='TEST';
do t;
expect DocumentID=TEST, DocumentDescription=TestDoc.txt, DocumentExtension=txt;
insert into globaldocumentlink (globaldocumentlinkid, globaldocumentID, departmentid) values (uniquekey(''),'TEST','~~');
do t;
expect EOF;
insert into search (searchid,temp,permanent,personid,departmentid) on existing update values ('TEST',1,1,'TEST','~~');
do t;
expect DocumentID=TEST, DocumentDescription=TestDoc.txt, DocumentExtension=txt;
insert into globaldocumentlink (globaldocumentlinkid, globaldocumentID, divisionid) values (uniquekey(''),'TEST','TEST');
do t;
expect EOF;
update person set divisionid='TEST' where personid='TEST';
do t;
expect DocumentID=TEST, DocumentDescription=TestDoc.txt, DocumentExtension=txt;
delete from search where searchid='TEST';
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetStandardDocumentList" IS 
{create PROCEDURE pears."NetStandardDocumentList" (in pWebUserID char(20))
result (DocumentID char(20),DocumentDescription char(100),DocumentExtension char(10))
//IQXWeb
begin
    declare UserClass char(20);
    declare local temporary table DivisionList(
    ID char(20) not null) not transactional;
    declare local temporary table DepartmentList(
    ID char(20) not null) not transactional;

    set UserClass=(select top 1 iqxnetuserclassid from iqxnetuser where iqxnetuserid=pWebUserID);
    case UserClass
        when 'CANDIDATE' then
         insert into DivisionList select p.divisionid from person p key join iqxnetuserlink l where l.iqxnetuserid=pWebUserID and p.divisionid is not null;    
         insert into DepartmentList select s.departmentid from search s key join person p key join iqxnetuserlink l where l.iqxnetuserid=pWebUserID and (s.temp=1 or s.permanent=1) and s.departmentid is not null;
        when 'CLIENT' then
         insert into DivisionList select c.divisionid from company c key join employment e key join iqxnetuserlink l where l.iqxnetuserid=pWebUserID and c.divisionid is not null;    
        when 'AGENCY' then
         insert into DivisionList select c.divisionid from company c key join employment e key join iqxnetuserlink l where l.iqxnetuserid=pWebUserID and c.divisionid is not null;    
    end case;
    
    select distinct
        b.id,
        g.description,
        isnull(g.fileextension,reverse(left(reverse(g.description),charindex('.',reverse(g.description))-1)))
    from 
        blobstore b
            join globaldocument g on b.id = g.globaldocumentID and b.class='G'
                left outer join globaldocumentdetail dep on g.globaldocumentID = dep.globaldocumentID and dep.doctype='DEPT',
            globaldocument g 
                left outer join globaldocumentdetail div on g.globaldocumentID = div.globaldocumentID and div.doctype='DIV'
    where
        (dep.docid in (select id from DepartmentList) or dep.docid is null)
        and (div.docid in (select id from DivisionList) or div.docid is null)
        and isnull(b.publishtoweb,0)=1
        and (g.iqxnetuserclassid = UserClass or g.iqxnetuserclassid = '%' or g.iqxnetuserclassid is null)
    
end

/* DOC
2018-03-14 PC IW-108 and OP-42
2018-04-25 GJ Removed document type - as no longer required
2018-04-26 PC,MS optimise joins
2018-06-13 MS respect globaldocument.fileextension if supplied
2018-09-13 PC test routine
2018-10-23 MHS IW-456 multi-cand fix, including general re-write of proc and tests
2020-34-1 MHS IQ-1362 Changed to reflect new architecture. Will work with the globaldocumentdetail compatibility view
          but tests will only work with IQX >= 2.20.4 because the new globaldocumentlink table must exist
*/

/* TEST
def t select * from NetStandardDocumentList('test.candidate') where DocumentID='TEST';
insert into blobstore (class,id,publishtoweb) on existing update values ('G','TEST',1);
insert into globaldocument (globaldocumentid,description,iqxnetuserclassid,fileextension) on existing update values ('TEST','TestDoc.txt',null,'txt');
delete from globaldocumentlink where globaldocumentid='TEST';
delete from search where personid='TEST';
update person set divisionid=null where personid='TEST';
do t;
expect DocumentID=TEST, DocumentDescription=TestDoc.txt, DocumentExtension=txt;
insert into globaldocumentlink (globaldocumentlinkid, globaldocumentID, departmentid) values (uniquekey(''),'TEST','~~');
do t;
expect EOF;
insert into search (searchid,temp,permanent,personid,departmentid) on existing update values ('TEST',1,1,'TEST','~~');
do t;
expect DocumentID=TEST, DocumentDescription=TestDoc.txt, DocumentExtension=txt;
insert into globaldocumentlink (globaldocumentlinkid, globaldocumentID, divisionid) values (uniquekey(''),'TEST','TEST');
do t;
expect EOF;
update person set divisionid='TEST' where personid='TEST';
do t;
expect DocumentID=TEST, DocumentDescription=TestDoc.txt, DocumentExtension=txt;
delete from search where searchid='TEST';
*/
}
</code>