pears.NetCandidateDocuments

CREATE PROCEDURE "pears"."NetCandidateDocuments"( IN "pWebUserID" CHAR(20),IN "pPersonID" CHAR(20) DEFAULT NULL ) 
RESULT( "DocType" CHAR(50),"DocDescription" CHAR(50),"LastUpdate" TIMESTAMP,"SpecialType" CHAR(50),"DocumentID" CHAR(20),"PersonID" CHAR(20),"IsAuthorised" CHAR(20),"SortOrder" INTEGER ) 
// IQXWeb
BEGIN
  DECLARE "userClass" CHAR(20);
  DECLARE "isCandorAgency" SMALLINT;
  IF "iqxnethaspermission"("pwebuserid",'DOCCANDDOWNLOAD') = 0 THEN
    SET "pPersonID" = NULL
  ELSE
    SET "userClass" = (SELECT "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 "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
        SET "isCandorAgency" = 0
      END IF END IF END IF;
  SELECT "documenttype"."description","oledocument"."description","isnull"("blobstore"."changedat","blobstore"."createdat"),'',"oledocument"."oledocumentid","oledocument"."ownerid",(IF "blobstore"."publishtoweb" = 1 THEN 'Authorised' ELSE 'Pending' endif),100+"isnull"("documenttype"."sortorder",0) AS "ord"
    FROM "oledocument" JOIN "blobstore" ON "blobstore"."id" = "oledocument"."oledocumentid" AND "blobstore"."class" = 'O'
      ,"oledocument" KEY LEFT OUTER JOIN "documenttype"
    WHERE "oledocument"."WhenArchived" IS NULL AND "oledocument"."ownertype" = 'P' AND "oledocument"."ownerid" = "ppersonid" AND("oledocument"."rawfile" = 1 OR "blobstore"."zipped" = 2) AND "isnull"("oledocument"."defunct",0) = 0
    AND("blobstore"."publishtoweb" = 1 OR("isCandorAgency" = 1 AND "blobstore"."changedby" = (SELECT "userid" FROM "staff" WHERE "staffid" = "userstaffid"))) UNION ALL
  SELECT 'Primary','CV',"isnull"("blobstore"."changedat","blobstore"."createdat"),'PRIMARY_CV',NULL,"blobstore"."id",(IF "blobstore"."publishtoweb" = 1 THEN 'Authorised' ELSE 'Pending' 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) UNION ALL
  SELECT 'Primary','Photo',"isnull"("blobstore"."changedat","blobstore"."createdat"),'PRIMARY_PHOTO',NULL,"blobstore"."id",(IF("blobstore"."publishtoweb" = 1 OR "isCandorAgency" = 1) THEN 'Authorised' ELSE 'Pending' endif),2 AS "ord"
    FROM "blobstore" WHERE "blobstore"."class" = 'J' AND "blobstore"."id" = "pPersonID" AND("isCandorAgency" = 1 OR "blobstore"."publishtoweb" = 1)
    ORDER BY "ord" ASC
END /* DOC
2016-12-02 PC Test and doc
2017-11-03 MHS allowed cands and agencies to see files they uploaded even if not yet published
2017-12-11 PC IsAuthorised column added to display Pending or Authorised
2018-02-09 PC primary photo modified to always show approved
2018-06-01 MHS Exclude archived documents. Fix tests.
2018-07-11 MHS outer join to documenttype because T&Cs don't have an obvious way of getting a documenttype. Remove relevant oledocument rows before starting test.
2018-10-22 MHS IW-456 multi-cand fix and tests
2019-03-05 PC IW-873 add top 1 and order by on subqueries
 
Tests:
invalid pWebUserID
Valid pWebUserID, invalid pPersonID
Valid pWebUserID, pPersonID as candidate
Valid pWebUserID, pPersonID as agency
 
*/
/* TEST
call NetTestSetup('');
insert into documenttype (documenttypeid,description,class) on existing update values ('TEST','test','P');
delete from blobstore where id='TEST';
delete from oledocument where ownertype='P' and ownerid='TEST';
insert into blobstore (class,id) values ('O','TEST');
insert into oledocument (oledocumentid,description,documenttypeid,ownerid,ownertype,rawfile) on existing update values ('TEST','test','TEST','TEST','P',1);
select * from NetCandidateDocuments('test.candidate',null);
expect EOF;
select * from NetCandidateDocuments('test.candidate','TEST');
expect EOF;
update blobstore set publishtoweb=1 where id='TEST';
select * from NetCandidateDocuments('test.candidate','TEST');
expect DocType=test, DocDescription=test, DocumentID=TEST, PersonID=TEST;
update blobstore set class='V',zipped=2 where id='TEST';
select * from NetCandidateDocuments('test.candidate','TEST');
expect DocType=Primary, DocDescription=CV, SpecialType=PRIMARY_CV, PersonID=TEST;
update blobstore set class='J',zipped=0 where id='TEST';
select * from NetCandidateDocuments('test.candidate','TEST');
expect DocType=Primary, DocDescription=Photo, SpecialType=PRIMARY_PHOTO, PersonID=TEST;
insert into pay_employee (personid,secondaryagencyid) on existing update values ('TEST','TEST');
select * from NetCandidateDocuments('test.agency','TEST');
expect DocType=Primary, DocDescription=Photo, SpecialType=PRIMARY_PHOTO, PersonID=TEST;
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateDocuments" IS 
{CREATE PROCEDURE pears."NetCandidateDocuments"(IN pWebUserID CHAR(20),IN pPersonID CHAR(20) DEFAULT NULL)
RESULT(DocType CHAR(50),DocDescription CHAR(50),LastUpdate TIMESTAMP,SpecialType CHAR(50),DocumentID CHAR(20),PersonID CHAR(20),IsAuthorised CHAR(20),SortOrder INTEGER)
// IQXWeb
BEGIN
  DECLARE userClass CHAR(20);
  DECLARE isCandorAgency SMALLINT;
  IF iqxnethaspermission(pwebuserid,'DOCCANDDOWNLOAD') = 0 THEN
    SET pPersonID=NULL
  ELSE
    SET userClass=(SELECT 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 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
        SET isCandorAgency=0
      END IF
    END IF
  END IF;
  SELECT documenttype.description,oledocument.description,isnull(blobstore.changedat,blobstore.createdat),'',oledocument.oledocumentid,oledocument.ownerid,(IF blobstore.publishtoweb=1 THEN 'Authorised' ELSE 'Pending' endif),100+isnull(documenttype.sortorder,0) AS ord
    FROM oledocument JOIN blobstore ON blobstore.id = oledocument.oledocumentid AND blobstore.class = 'O'
    ,oledocument KEY LEFT OUTER JOIN documenttype
    WHERE oledocument.WhenArchived IS NULL AND oledocument.ownertype = 'P' AND oledocument.ownerid = ppersonid AND (oledocument.rawfile = 1 OR blobstore.zipped = 2) AND isnull(oledocument.defunct,0)=0 
    AND (blobstore.publishtoweb = 1 OR (isCandorAgency=1 AND blobstore.changedby=(SELECT userid FROM staff WHERE staffid=userstaffid))) UNION ALL
  SELECT 'Primary','CV',isnull(blobstore.changedat,blobstore.createdat),'PRIMARY_CV',NULL,blobstore.id,(IF blobstore.publishtoweb=1 THEN 'Authorised' ELSE 'Pending' 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) UNION ALL
  SELECT 'Primary','Photo',isnull(blobstore.changedat,blobstore.createdat),'PRIMARY_PHOTO',NULL,blobstore.id,(IF (blobstore.publishtoweb=1 OR isCandorAgency = 1) THEN 'Authorised' ELSE 'Pending' endif),2 AS ord
    FROM blobstore WHERE blobstore.class = 'J' AND blobstore.id = pPersonID AND(isCandorAgency = 1 OR blobstore.publishtoweb = 1) ORDER BY
    ord ASC
END
 
/* DOC
2016-12-02 PC Test and doc
2017-11-03 MHS allowed cands and agencies to see files they uploaded even if not yet published
2017-12-11 PC IsAuthorised column added to display Pending or Authorised
2018-02-09 PC primary photo modified to always show approved
2018-06-01 MHS Exclude archived documents. Fix tests.
2018-07-11 MHS outer join to documenttype because T&Cs don't have an obvious way of getting a documenttype. Remove relevant oledocument rows before starting test.
2018-10-22 MHS IW-456 multi-cand fix and tests
2019-03-05 PC IW-873 add top 1 and order by on subqueries
 
Tests:
    invalid pWebUserID
    Valid pWebUserID, invalid pPersonID
    Valid pWebUserID, pPersonID as candidate
    Valid pWebUserID, pPersonID as agency
 
*/
 
/* TEST
call NetTestSetup('');
insert into documenttype (documenttypeid,description,class) on existing update values ('TEST','test','P');
delete from blobstore where id='TEST';
delete from oledocument where ownertype='P' and ownerid='TEST';
insert into blobstore (class,id) values ('O','TEST');
insert into oledocument (oledocumentid,description,documenttypeid,ownerid,ownertype,rawfile) on existing update values ('TEST','test','TEST','TEST','P',1);
select * from NetCandidateDocuments('test.candidate',null);
expect EOF;
select * from NetCandidateDocuments('test.candidate','TEST');
expect EOF;
update blobstore set publishtoweb=1 where id='TEST';
select * from NetCandidateDocuments('test.candidate','TEST');
expect DocType=test, DocDescription=test, DocumentID=TEST, PersonID=TEST;
update blobstore set class='V',zipped=2 where id='TEST';
select * from NetCandidateDocuments('test.candidate','TEST');
expect DocType=Primary, DocDescription=CV, SpecialType=PRIMARY_CV, PersonID=TEST;
update blobstore set class='J',zipped=0 where id='TEST';
select * from NetCandidateDocuments('test.candidate','TEST');
expect DocType=Primary, DocDescription=Photo, SpecialType=PRIMARY_PHOTO, PersonID=TEST;
insert into pay_employee (personid,secondaryagencyid) on existing update values ('TEST','TEST');
select * from NetCandidateDocuments('test.agency','TEST');
expect DocType=Primary, DocDescription=Photo, SpecialType=PRIMARY_PHOTO, PersonID=TEST;
*/
}