====== pears.NetFilePermitted ======
<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 function "pears"."NetFilePermitted"( in "pWebUserID" char(20),in "pFilePath" char(250) ) 
returns integer
begin
  // IQXWeb
  declare "pPersonID" char(20);
  declare "pClassID" char(20);
  declare "EmployeeCodeList" char(254);
  declare "pClientCode" char(20);
  declare "DocPathRoot" char(254);
  declare "StartDate" date;
  declare "EndDate" date;
  declare "retval" integer;
  declare "BackSlash" char(2);
  return 1;
  set "BackSlash" = '\\';
  set "DocPathRoot" = (select "WPKLocalSwitchValue"('PDFINDB','Global','L',0,1))+'\\';
  set "StartDate" = '1900-01-01';
  set "EndDate" = '2100-01-01';
  set "pClassID" = (select first "iqxnetuserclassid" from "iqxnetuser" as "l" where "l"."iqxnetuserid" = "pWebUserID");
  if(select top 1 "isnull"("value",0) from "iqxnetswitch" where "iqxnetswitchid" = 'ESCAPEPATHWAY') = 1 then
    set "BackSlash" = '\\\\';
    set "DocPathRoot" = (select "replace"("DocPathRoot",'\\',"BackSlash"))
  end if;
  if "pClassID" = 'CANDIDATE' then
    set "EmployeeCodeList" = (select "list"(distinct "payrollnumber") as "payrollNo" from "pay_employment" where "personid" = any(select "personid" from "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID") and "payrollnumber" is not null);
    set "EmployeeCodeList" = "EmployeeCodeList"+','+(select "list"("payrollnumber") from "person" where "personid" = any(select "personid" from "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID") and "payrollnumber" is not null);
    select "count"() into "retval" from "AccordUserPayrollRuns"("EmployeeCodeList","StartDate","EndDate") where "PayslipFullPath" = "pFilePath";
    if "retval" > 0 then set "retval" = 1
    end if end if;
  if "pClassID" = 'CLIENT' then
    set "pClientCode" = (select top 1 "c"."clientcode" from "iqxnetuserlink" as "l" key join "employment" as "e" key join "company" as "c" where "l"."iqxnetuserid" = "pWebUserID" order by "c"."clientcode" asc);
    select "count"()
      into "retval"
      from "iqacdocument"
      where "accountcode" = "pClientCode"
      and "string"("DocPathRoot","accountcode","BackSlash","dateformat"("documentdate",'yyyymmdd'),"BackSlash","ourref",'.pdf') = "pFilePath";
    if "retval" > 0 then set "retval" = 1
    end if end if;
  if "pClassID" = 'OWNER' then
    set "pClientCodeList" = (select top 1 "c"."clientcode" from "iqxnetuserlink" as "l" key join "employment" as "e" key join "company" as "c" where "l"."iqxnetuserid" = "pWebUserID" order by "c"."clientcode" asc);
    select "count"()
      into "retval"
      from "iqacdocument"
      where "accountcode" in( "pClientCodeList" ) 
      and "string"("DocPathRoot","accountcode","BackSlash","dateformat"("documentdate",'yyyymmdd'),"BackSlash","ourref",'.pdf') = "pFilePath";
    if "retval" > 0 then set "retval" = 1
    end if end if;
  return "retval"
end /* DOC
2016-10-13 PC verify whether a file may be downloaded or not
IW-93
2017-06-29 PC modify to refer to pdf folder in general settings
2017-08-03 PC IW-267 double or single backslashes switchable by EscapeBackslash
2018-05-02 PC IW-477 remember to take out return 1
2018-10-22 MHS IW-456 multi-cand fix
2019-07-26 MHS reviewed (no code changes). This function is currently only used for candidates downloading Accord payslips. Invoice downloads are now handled by the explicit InvoiceID param in the download.

Inputs:
pWebUserID
pFilePath

Outputs:
integer

Tests:
unable to test locally owing to no local install of Accord payroll system

*/
/* TEST

*/
</code>