pears.NetFilePermitted

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