pears.NetCandidateDocumentsFiltered

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