====== 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>
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetFilePermitted" IS 
{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 in (select personid from iqxnetuserlink where iqxnetuserid = pWebUserID) and payrollnumber is not null);
   set "EmployeeCodeList" = EmployeeCodeList+','+(select list(payrollnumber) from person where personid in (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>