====== pears.NetCandidateDocumentsFiltered ======
<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"."NetCandidateDocumentsFiltered"( in "pWebUserID" char(20),in "pPersonID" char(20) default null,in "pExcludeList" char(100) default null,in "pHidePhoto" integer default 0 ) 
result( "DocType" char(50),"DocDescription" char(50),"LastUpdate" timestamp,"SpecialType" char(50),"DocumentID" char(20),"PersonID" char(20),"PublishToWeb" char(8),"SortOrder" integer ) 
-- 20111013 modified to ignore Reference document type
-- 20111205 modified to accept Candidate data from Candidate details popup on Client/Current Requirements tab, Filled By column
-- 20120612 remove photo if agency.
-- 20130618 download link disappears if PublishToWeb not set
-- 20130813 modified to ignore parameterised list of doc types
begin
  declare "userClass" char(20);
  declare "isCandorAgency" smallint;
  declare local temporary table "expandedExcludeList"(
    "DocType" char(20) null,
    ) on commit delete rows;
  declare "TableIndex" integer;
  declare "TempString" char(100);
  declare "LoopCounter" integer;
  declare "StringFragment" char(20);
  set "TempString" = "pExcludeList";
  set "LoopCounter" = 0;
  set "TableIndex" = "length"("TempString");
  while "TableIndex" > 0 and "LoopCounter" < 10 loop
    set "TableIndex" = "charindex"(':',"TempString");
    if "TableIndex" = 0 then set "TableIndex" = "length"("TempString")+1;
      set "LoopCounter" = 11
    end if;
    set "StringFragment" = "substring"("TempString",0,"TableIndex"-1);
    insert into "expandedExcludeList"( "DocType" ) values( "StringFragment" ) ;
    set "TempString" = "substring"("TempString","TableIndex"+1);
    set "LoopCounter" = "LoopCounter"+1
  end loop;
  if "iqxnethaspermission"("pwebuserid",'DOCCANDDOWNLOAD') = 0 then
    set "pPersonID" = null
  else
    set "userClass" = (select first "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 first "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 if "userClass" = 'Client' then
          set "isCandorAgency" = 2
        else
          set "isCandorAgency" = 0
        end if end if end if end if;
  select "documenttype"."description",
    "oledocument"."description",
    "isnull"("blobstore"."changedat","blobstore"."createdat"),'',
    "oledocument"."oledocumentid",
    "oledocument"."ownerid",
    (if "blobstore"."publishtoweb" <> 0 then 'Download' endif),
    100+"isnull"("documenttype"."sortorder",0) as "ord"
    from "oledocument"
      join "blobstore" on "blobstore"."id" = "oledocument"."oledocumentid" and "blobstore"."class" = 'O',"oledocument"
      key join "documenttype"
    where "oledocument"."ownertype" = 'P'
    and "oledocument"."ownerid" = "ppersonid"
    and("oledocument"."rawfile" = 1 or "blobstore"."zipped" = 2)
    and(("isCandorAgency" = 1 or "blobstore"."publishtoweb" = 1) or("isCandorAgency" = 2 and "blobstore"."publishtoweb" = 1))
    and not "documenttype"."description" = any(select "DocType" from "expandedExcludeList") union all
  select 'Primary','CV',
    "isnull"("blobstore"."changedat","blobstore"."createdat"),'PRIMARY_CV',
    null,
    "blobstore"."id",
    (if "blobstore"."publishtoweb" <> 0 then 'Download' 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) or("isCandorAgency" = 2 and "blobstore"."publishtoweb" = 1)) union all
  select 'Primary','Photo',
    "isnull"("blobstore"."changedat","blobstore"."createdat"),'PRIMARY_PHOTO',
    null,
    "blobstore"."id",
    (if "blobstore"."publishtoweb" <> 0 then 'Download' endif),
    2 as "ord"
    from "blobstore"
    where "blobstore"."class" = 'J'
    and "blobstore"."id" = "pPersonID"
    and(("userClass" = 'AGENCY' and "blobstore"."publishtoweb" = 1 and "pHidePhoto" = 0) or("userClass" = 'CANDIDATE') or("userClass" = 'Client' and "blobstore"."publishtoweb" = 1))
    order by "ord" asc
end /* DOC
2018-10-22 MHS IW-456 multi-cand fix and tests
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateDocumentsFiltered" IS 
{create PROCEDURE pears."NetCandidateDocumentsFiltered"( in "pWebUserID" char(20),in "pPersonID" char(20) default null,in "pExcludeList" char(100) default null,in "pHidePhoto" integer default 0 ) 
result( "DocType" char(50),"DocDescription" char(50),"LastUpdate" timestamp,"SpecialType" char(50),"DocumentID" char(20),"PersonID" char(20),"PublishToWeb" char(8),"SortOrder" integer ) 
-- 20111013 modified to ignore Reference document type
-- 20111205 modified to accept Candidate data from Candidate details popup on Client/Current Requirements tab, Filled By column
-- 20120612 remove photo if agency.
-- 20130618 download link disappears if PublishToWeb not set
-- 20130813 modified to ignore parameterised list of doc types
begin
  declare "userClass" char(20);
  declare "isCandorAgency" smallint;
  declare local temporary table "expandedExcludeList"(
    "DocType" char(20) null,
    ) on commit delete rows;
  declare "TableIndex" integer;
  declare "TempString" char(100);
  declare "LoopCounter" integer;
  declare "StringFragment" char(20);
  set "TempString" = "pExcludeList";
  set "LoopCounter" = 0;
  set "TableIndex" = "length"("TempString");
  while "TableIndex" > 0 and "LoopCounter" < 10 loop
    set "TableIndex" = "charindex"(':',"TempString");
    if "TableIndex" = 0 then set "TableIndex" = "length"("TempString")+1;
      set "LoopCounter" = 11
    end if;
    set "StringFragment" = "substring"("TempString",0,"TableIndex"-1);
    insert into "expandedExcludeList"( "DocType" ) values( "StringFragment" ) ;
    set "TempString" = "substring"("TempString","TableIndex"+1);
    set "LoopCounter" = "LoopCounter"+1
  end loop;
  if "iqxnethaspermission"("pwebuserid",'DOCCANDDOWNLOAD') = 0 then
    set "pPersonID" = null
  else
    set "userClass" = (select first "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 first "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 if "userClass" = 'Client' then
          set "isCandorAgency" = 2
        else
          set "isCandorAgency" = 0
        end if
      end if end if end if;
  select "documenttype"."description",
    "oledocument"."description",
    "isnull"("blobstore"."changedat","blobstore"."createdat"),'',
    "oledocument"."oledocumentid",
    "oledocument"."ownerid",
    (if "blobstore"."publishtoweb" <> 0 then 'Download' endif),
    100+"isnull"("documenttype"."sortorder",0) as "ord"
    from "oledocument"
      join "blobstore" on "blobstore"."id" = "oledocument"."oledocumentid" and "blobstore"."class" = 'O',"oledocument"
      key join "documenttype"
    where "oledocument"."ownertype" = 'P'
    and "oledocument"."ownerid" = "ppersonid"
    and("oledocument"."rawfile" = 1 or "blobstore"."zipped" = 2)
    and(("isCandorAgency" = 1 or "blobstore"."publishtoweb" = 1) or("isCandorAgency" = 2 and "blobstore"."publishtoweb" = 1))
    and not "documenttype"."description" = any(select "DocType" from "expandedExcludeList") union all
  select 'Primary','CV',
    "isnull"("blobstore"."changedat","blobstore"."createdat"),'PRIMARY_CV',
    null,
    "blobstore"."id",
    (if "blobstore"."publishtoweb" <> 0 then 'Download' 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) or("isCandorAgency" = 2 and "blobstore"."publishtoweb" = 1)) union all
  select 'Primary','Photo',
    "isnull"("blobstore"."changedat","blobstore"."createdat"),'PRIMARY_PHOTO',
    null,
    "blobstore"."id",
    (if "blobstore"."publishtoweb" <> 0 then 'Download' endif),
    2 as "ord"
    from "blobstore"
    where "blobstore"."class" = 'J'
    and "blobstore"."id" = "pPersonID"
    and(("userClass" = 'AGENCY' and "blobstore"."publishtoweb" = 1 and "pHidePhoto" = 0) or("userClass" = 'CANDIDATE') or("userClass" = 'Client' and "blobstore"."publishtoweb" = 1))
    order by "ord" asc
end

/* DOC
2018-10-22 MHS IW-456 multi-cand fix and tests
*/
}
</code>