pears.NetFilePermitted

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
 
*/
}