database:procedures:pears_netoledocuments



pears.NetOLEDocuments

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.

Original SQL

CREATE PROCEDURE "pears"."NetOLEDocuments"( IN "pWebUserID" CHAR(20),IN "pOwnerType" CHAR(1),IN "pOwnerID" CHAR(20) ) 
RESULT( "DocType" CHAR(50),"DocDescription" CHAR(50),"LastUpdate" TIMESTAMP,"DocumentID" CHAR(20),"SortOrder" INTEGER ) 
-- 20120821 standard document list
BEGIN
  DECLARE "userClass" CHAR(20);
  SET "userClass" = (SELECT FIRST "iqxnetuserclassid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID");
  IF "pownertype" = 'P' THEN -- Candidate docs
    IF "iqxnethaspermission"("pwebuserid",'DOCCANDDOWNLOAD') = 0 THEN
      SELECT NULL,NULL,NULL,NULL,NULL FROM "dummy" WHERE 1 = 0;
      RETURN
    END IF;
    IF "userclass" = 'CANDIDATE' THEN -- Own published docs only  
      SELECT "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),"oledocument"."oledocumentid","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" = "pownertype" AND("oledocument"."rawfile" = 1 OR "blobstore"."zipped" = 2)
        AND "oledocument"."ownerid" = any(SELECT "personid" FROM "iqxnetuserlink" WHERE "personid" = "pownerid" AND "iqxnetuserid" = "pwebuserid")
        AND "blobstore"."publishtoweb" = 1
    ELSE IF "userclass" = 'AGENCY' THEN -- Can see published docs for any candidate owned by the agency 
        SELECT "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),"oledocument"."oledocumentid","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" = "pownertype" AND("oledocument"."rawfile" = 1 OR "blobstore"."zipped" = 2)
          AND "oledocument"."ownerid" = any(SELECT "person"."personid" FROM "person" KEY JOIN "pay_employee" KEY JOIN "company" AS "agcomp" KEY JOIN "employment" AS "agemp" KEY JOIN "iqxnetuserlink" AS "l" WHERE "person"."personid" = "pownerid" AND "l"."iqxnetuserid" = "pwebuserid")
          AND "blobstore"."publishtoweb" = 1
      ELSE IF "userclass" = 'CLIENT' THEN -- Can see PUBLISHED docs for any candidate
          SELECT "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),"oledocument"."oledocumentid","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" = "pownertype" AND("oledocument"."rawfile" = 1 OR "blobstore"."zipped" = 2)
            AND "oledocument"."ownerid" = "pownerid"
            AND "blobstore"."publishtoweb" = 1
        ELSE
          SELECT NULL,NULL,NULL,NULL,NULL FROM "dummy" WHERE 1 = 0
        END IF END IF END IF END IF;
  IF "pownertype" = 'C' THEN -- Company docs
    IF "userclass" = 'CLIENT' OR "userclass" = 'AGENCY' THEN -- Can see docs belonging to the company
      SELECT "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),"oledocument"."oledocumentid","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" = "pownertype" AND("oledocument"."rawfile" = 1 OR "blobstore"."zipped" = 2)
        AND "oledocument"."ownerid" = any(SELECT "e"."companyid" FROM "iqxnetuserlink" AS "l" KEY JOIN "employment" AS "e" WHERE "e"."companyid" = "pownerid" AND "l"."iqxnetuserid" = "pwebuserid")
        AND "blobstore"."publishtoweb" = 1
    ELSE -- Candidates can not presently access company docs
      SELECT NULL,NULL,NULL,NULL,NULL FROM "dummy" WHERE 1 = 0
    END IF END IF;
  IF "pownertype" = 'V' THEN -- Vacancy docs
    IF "userclass" = 'CANDIDATE' THEN -- Can access any vacancy for which short-listed
      SELECT "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),"oledocument"."oledocumentid","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" = "pownertype" AND("oledocument"."rawfile" = 1 OR "blobstore"."zipped" = 2)
        AND "oledocument"."ownerid" = any(SELECT "r"."vacancyid" FROM "iqxnetuserlink" AS "l" KEY JOIN "person" AS "p" KEY JOIN "progress" AS "r" WHERE "r"."vacancyid" = "pownerid" AND "l"."iqxnetuserid" = "pwebuserid")
        AND "blobstore"."publishtoweb" = 1
    ELSE IF "userclass" = 'AGENCY' THEN -- Can access any vacancy cascaded to the agency
        SELECT "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),"oledocument"."oledocumentid","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" = "pownertype" AND("oledocument"."rawfile" = 1 OR "blobstore"."zipped" = 2)
          AND "oledocument"."ownerid" = any(SELECT "c"."vacancyid" FROM "iqxnetuserlink" AS "l" KEY JOIN "employment" KEY JOIN "company" KEY JOIN "cascadedvacancy" AS "c" WHERE "c"."vacancyid" = "pownerid" AND "l"."iqxnetuserid" = "pwebuserid")
          AND "blobstore"."publishtoweb" = 1
      ELSE IF "userclass" = 'CLIENT' THEN -- CLients own vacancies
          SELECT "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),"oledocument"."oledocumentid","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" = "pownertype" AND("oledocument"."rawfile" = 1 OR "blobstore"."zipped" = 2)
            AND "oledocument"."ownerid" = any(SELECT "v"."vacancyid" FROM "iqxnetuserlink" AS "l" KEY JOIN "employment" AS "e" KEY JOIN "company" KEY JOIN "employment" AS "ve" KEY JOIN "vacancy" AS "v" WHERE "v"."vacancyid" = "pownerid" AND "l"."iqxnetuserid" = "pwebuserid")
            AND "blobstore"."publishtoweb" = 1
        ELSE
          SELECT NULL,NULL,NULL,NULL,NULL FROM "dummy" WHERE 1 = 0
        END IF END IF END IF END IF;
  IF "pownertype" = 'L' THEN -- Placement docs
    IF "userclass" = 'CANDIDATE' THEN -- Candidates own placements
      SELECT "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),"oledocument"."oledocumentid","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" = "pownertype" AND("oledocument"."rawfile" = 1 OR "blobstore"."zipped" = 2)
        AND "oledocument"."ownerid" = any(SELECT "pl"."placementid" FROM "iqxnetuserlink" AS "l" KEY JOIN "person" AS "p" KEY JOIN "employment" KEY JOIN "placement" AS "pl" WHERE "pl"."placementid" = "pownerid" AND "l"."iqxnetuserid" = "pwebuserid")
        AND "blobstore"."publishtoweb" = 1
    ELSE IF "userclass" = 'AGENCY' THEN -- Agency candidates placements
        SELECT "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),"oledocument"."oledocumentid","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" = "pownertype" AND("oledocument"."rawfile" = 1 OR "blobstore"."zipped" = 2)
          AND "oledocument"."ownerid" = any(SELECT "pl"."placementid" FROM "iqxnetuserlink" AS "l" KEY JOIN "employment" AS "agemp" KEY JOIN "company" AS "agcomp" KEY JOIN "pay_employee" KEY JOIN "person" KEY JOIN "employment" KEY JOIN "placement" AS "pl" WHERE "pl"."placementid" = "pownerid" AND "l"."iqxnetuserid" = "pwebuserid")
          AND "blobstore"."publishtoweb" = 1
      ELSE IF "userclass" = 'CLIENT' THEN -- Client's own placements
          SELECT "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),"oledocument"."oledocumentid","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" = "pownertype" AND("oledocument"."rawfile" = 1 OR "blobstore"."zipped" = 2)
            AND "oledocument"."ownerid" = any(SELECT "pl"."placementid" FROM "iqxnetuserlink" AS "l" KEY JOIN "employment" AS "e" KEY JOIN "company" KEY JOIN "employment" AS "ple" KEY JOIN "placement" AS "pl" WHERE "pl"."placementid" = "pownerid" AND "l"."iqxnetuserid" = "pwebuserid")
            AND "blobstore"."publishtoweb" = 1
        ELSE
          SELECT NULL,NULL,NULL,NULL,NULL FROM "dummy" WHERE 1 = 0
        END IF END IF END IF END IF;
  IF "pownertype" = 'R' THEN -- Progress docs
    IF "userclass" = 'CANDIDATE' THEN -- Candidates own progress
      SELECT "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),"oledocument"."oledocumentid","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" = "pownertype" AND("oledocument"."rawfile" = 1 OR "blobstore"."zipped" = 2)
        AND "oledocument"."ownerid" = any(SELECT "r"."progressid" FROM "iqxnetuserlink" AS "l" KEY JOIN "person" AS "p" KEY JOIN "progress" AS "r" WHERE "r"."progressid" = "pownerid" AND "l"."iqxnetuserid" = "pwebuserid")
        AND "blobstore"."publishtoweb" = 1
    ELSE IF "userclass" = 'AGENCY' THEN -- Agency candidates progress
        SELECT "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),"oledocument"."oledocumentid","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" = "pownertype" AND("oledocument"."rawfile" = 1 OR "blobstore"."zipped" = 2)
          AND "oledocument"."ownerid" = any(SELECT "r"."progressid" FROM "iqxnetuserlink" AS "l" KEY JOIN "employment" AS "agemp" KEY JOIN "company" AS "agcomp" KEY JOIN "pay_employee" KEY JOIN "person" KEY JOIN "progress" AS "r" WHERE "r"."progressid" = "pownerid" AND "l"."iqxnetuserid" = "pwebuserid")
          AND "blobstore"."publishtoweb" = 1
      ELSE IF "userclass" = 'CLIENT' THEN -- Progress for clients vacancies
          SELECT "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),"oledocument"."oledocumentid","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" = "pownertype" AND("oledocument"."rawfile" = 1 OR "blobstore"."zipped" = 2)
            AND "oledocument"."ownerid" = any(SELECT "r"."progressid" FROM "iqxnetuserlink" AS "l" KEY JOIN "employment" AS "e" KEY JOIN "company" KEY JOIN "employment" AS "ve" KEY JOIN "vacancy" AS "v" KEY JOIN "progress" AS "r" WHERE "r"."progressid" = "pownerid" AND "l"."iqxnetuserid" = "pwebuserid")
            AND "blobstore"."publishtoweb" = 1
        ELSE
          SELECT NULL,NULL,NULL,NULL,NULL FROM "dummy" WHERE 1 = 0
        END IF
      END IF
    END IF
  END IF
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOLEDocuments" IS 
{CREATE PROCEDURE pears."NetOLEDocuments"( IN "pWebUserID" CHAR(20),IN "pOwnerType" CHAR(1),IN "pOwnerID" CHAR(20) ) 
RESULT( "DocType" CHAR(50),"DocDescription" CHAR(50),"LastUpdate" TIMESTAMP,"DocumentID" CHAR(20),"SortOrder" INTEGER ) 
-- 20120821 standard document list
BEGIN
  DECLARE "userClass" CHAR(20);
  SET "userClass" = (SELECT FIRST "iqxnetuserclassid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID");
  IF "pownertype" = 'P' THEN -- Candidate docs
    IF "iqxnethaspermission"("pwebuserid",'DOCCANDDOWNLOAD') = 0 THEN
      SELECT NULL,NULL,NULL,NULL,NULL FROM "dummy" WHERE 1 = 0;
      RETURN
    END IF;
    IF "userclass" = 'CANDIDATE' THEN -- Own published docs only  
      SELECT "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),"oledocument"."oledocumentid","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" = "pownertype" AND("oledocument"."rawfile" = 1 OR "blobstore"."zipped" = 2)
        AND "oledocument"."ownerid" = any(SELECT "personid" FROM "iqxnetuserlink" WHERE "personid" = "pownerid" AND "iqxnetuserid" = "pwebuserid")
        AND "blobstore"."publishtoweb" = 1
    ELSE IF "userclass" = 'AGENCY' THEN -- Can see published docs for any candidate owned by the agency 
        SELECT "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),"oledocument"."oledocumentid","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" = "pownertype" AND("oledocument"."rawfile" = 1 OR "blobstore"."zipped" = 2)
          AND "oledocument"."ownerid" = any(SELECT "person"."personid" FROM "person" KEY JOIN "pay_employee" KEY JOIN "company" AS "agcomp" KEY JOIN "employment" AS "agemp" KEY JOIN "iqxnetuserlink" AS "l" WHERE "person"."personid" = "pownerid" AND "l"."iqxnetuserid" = "pwebuserid")
          AND "blobstore"."publishtoweb" = 1
      ELSE IF "userclass" = 'CLIENT' THEN -- Can see PUBLISHED docs for any candidate
          SELECT "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),"oledocument"."oledocumentid","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" = "pownertype" AND("oledocument"."rawfile" = 1 OR "blobstore"."zipped" = 2)
            AND "oledocument"."ownerid" = "pownerid"
            AND "blobstore"."publishtoweb" = 1
        ELSE
          SELECT NULL,NULL,NULL,NULL,NULL FROM "dummy" WHERE 1 = 0
        END IF
      END IF
    END IF END IF;
  IF "pownertype" = 'C' THEN -- Company docs
    IF "userclass" = 'CLIENT' OR "userclass" = 'AGENCY' THEN -- Can see docs belonging to the company
      SELECT "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),"oledocument"."oledocumentid","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" = "pownertype" AND("oledocument"."rawfile" = 1 OR "blobstore"."zipped" = 2)
        AND "oledocument"."ownerid" = any(SELECT "e"."companyid" FROM "iqxnetuserlink" AS "l" KEY JOIN "employment" AS "e" WHERE "e"."companyid" = "pownerid" AND "l"."iqxnetuserid" = "pwebuserid")
        AND "blobstore"."publishtoweb" = 1
    ELSE -- Candidates can not presently access company docs
      SELECT NULL,NULL,NULL,NULL,NULL FROM "dummy" WHERE 1 = 0
    END IF END IF;
  IF "pownertype" = 'V' THEN -- Vacancy docs
    IF "userclass" = 'CANDIDATE' THEN -- Can access any vacancy for which short-listed
      SELECT "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),"oledocument"."oledocumentid","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" = "pownertype" AND("oledocument"."rawfile" = 1 OR "blobstore"."zipped" = 2)
        AND "oledocument"."ownerid" = any(SELECT "r"."vacancyid" FROM "iqxnetuserlink" AS "l" KEY JOIN "person" AS "p" KEY JOIN "progress" AS "r" WHERE "r"."vacancyid" = "pownerid" AND "l"."iqxnetuserid" = "pwebuserid")
        AND "blobstore"."publishtoweb" = 1
    ELSE IF "userclass" = 'AGENCY' THEN -- Can access any vacancy cascaded to the agency
        SELECT "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),"oledocument"."oledocumentid","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" = "pownertype" AND("oledocument"."rawfile" = 1 OR "blobstore"."zipped" = 2)
          AND "oledocument"."ownerid" = any(SELECT "c"."vacancyid" FROM "iqxnetuserlink" AS "l" KEY JOIN "employment" KEY JOIN "company" KEY JOIN "cascadedvacancy" AS "c" WHERE "c"."vacancyid" = "pownerid" AND "l"."iqxnetuserid" = "pwebuserid")
          AND "blobstore"."publishtoweb" = 1
      ELSE IF "userclass" = 'CLIENT' THEN -- CLients own vacancies
          SELECT "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),"oledocument"."oledocumentid","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" = "pownertype" AND("oledocument"."rawfile" = 1 OR "blobstore"."zipped" = 2)
            AND "oledocument"."ownerid" = any(SELECT "v"."vacancyid" FROM "iqxnetuserlink" AS "l" KEY JOIN "employment" AS "e" KEY JOIN "company" KEY JOIN "employment" AS "ve" KEY JOIN "vacancy" AS "v" WHERE "v"."vacancyid" = "pownerid" AND "l"."iqxnetuserid" = "pwebuserid")
            AND "blobstore"."publishtoweb" = 1
        ELSE
          SELECT NULL,NULL,NULL,NULL,NULL FROM "dummy" WHERE 1 = 0
        END IF
      END IF
    END IF END IF;
  IF "pownertype" = 'L' THEN -- Placement docs
    IF "userclass" = 'CANDIDATE' THEN -- Candidates own placements
      SELECT "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),"oledocument"."oledocumentid","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" = "pownertype" AND("oledocument"."rawfile" = 1 OR "blobstore"."zipped" = 2)
        AND "oledocument"."ownerid" = any(SELECT "pl"."placementid" FROM "iqxnetuserlink" AS "l" KEY JOIN "person" AS "p" KEY JOIN "employment" KEY JOIN "placement" AS "pl" WHERE "pl"."placementid" = "pownerid" AND "l"."iqxnetuserid" = "pwebuserid")
        AND "blobstore"."publishtoweb" = 1
    ELSE IF "userclass" = 'AGENCY' THEN -- Agency candidates placements
        SELECT "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),"oledocument"."oledocumentid","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" = "pownertype" AND("oledocument"."rawfile" = 1 OR "blobstore"."zipped" = 2)
          AND "oledocument"."ownerid" = any(SELECT "pl"."placementid" FROM "iqxnetuserlink" AS "l" KEY JOIN "employment" AS "agemp" KEY JOIN "company" AS "agcomp" KEY JOIN "pay_employee" KEY JOIN "person" KEY JOIN "employment" KEY JOIN "placement" AS "pl" WHERE "pl"."placementid" = "pownerid" AND "l"."iqxnetuserid" = "pwebuserid")
          AND "blobstore"."publishtoweb" = 1
      ELSE IF "userclass" = 'CLIENT' THEN -- Client's own placements
          SELECT "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),"oledocument"."oledocumentid","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" = "pownertype" AND("oledocument"."rawfile" = 1 OR "blobstore"."zipped" = 2)
            AND "oledocument"."ownerid" = any(SELECT "pl"."placementid" FROM "iqxnetuserlink" AS "l" KEY JOIN "employment" AS "e" KEY JOIN "company" KEY JOIN "employment" AS "ple" KEY JOIN "placement" AS "pl" WHERE "pl"."placementid" = "pownerid" AND "l"."iqxnetuserid" = "pwebuserid")
            AND "blobstore"."publishtoweb" = 1
        ELSE
          SELECT NULL,NULL,NULL,NULL,NULL FROM "dummy" WHERE 1 = 0
        END IF
      END IF
    END IF END IF;
  IF "pownertype" = 'R' THEN -- Progress docs
    IF "userclass" = 'CANDIDATE' THEN -- Candidates own progress
      SELECT "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),"oledocument"."oledocumentid","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" = "pownertype" AND("oledocument"."rawfile" = 1 OR "blobstore"."zipped" = 2)
        AND "oledocument"."ownerid" = any(SELECT "r"."progressid" FROM "iqxnetuserlink" AS "l" KEY JOIN "person" AS "p" KEY JOIN "progress" AS "r" WHERE "r"."progressid" = "pownerid" AND "l"."iqxnetuserid" = "pwebuserid")
        AND "blobstore"."publishtoweb" = 1
    ELSE IF "userclass" = 'AGENCY' THEN -- Agency candidates progress
        SELECT "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),"oledocument"."oledocumentid","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" = "pownertype" AND("oledocument"."rawfile" = 1 OR "blobstore"."zipped" = 2)
          AND "oledocument"."ownerid" = any(SELECT "r"."progressid" FROM "iqxnetuserlink" AS "l" KEY JOIN "employment" AS "agemp" KEY JOIN "company" AS "agcomp" KEY JOIN "pay_employee" KEY JOIN "person" KEY JOIN "progress" AS "r" WHERE "r"."progressid" = "pownerid" AND "l"."iqxnetuserid" = "pwebuserid")
          AND "blobstore"."publishtoweb" = 1
      ELSE IF "userclass" = 'CLIENT' THEN -- Progress for clients vacancies
          SELECT "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),"oledocument"."oledocumentid","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" = "pownertype" AND("oledocument"."rawfile" = 1 OR "blobstore"."zipped" = 2)
            AND "oledocument"."ownerid" = any(SELECT "r"."progressid" FROM "iqxnetuserlink" AS "l" KEY JOIN "employment" AS "e" KEY JOIN "company" KEY JOIN "employment" AS "ve" KEY JOIN "vacancy" AS "v" KEY JOIN "progress" AS "r" WHERE "r"."progressid" = "pownerid" AND "l"."iqxnetuserid" = "pwebuserid")
            AND "blobstore"."publishtoweb" = 1
        ELSE
          SELECT NULL,NULL,NULL,NULL,NULL FROM "dummy" WHERE 1 = 0
        END IF
      END IF
    END IF
  END IF
END
}
  • database/procedures/pears_netoledocuments.txt
  • Last modified: 2026/08/07 19:24
  • by 127.0.0.1