pears.NetStandardDocumentList

CREATE PROCEDURE "pears"."NetStandardDocumentList"( IN "pWebUserID" CHAR(20) ) 
RESULT( "DocumentID" CHAR(20),"DocumentDescription" CHAR(100),"DocumentExtension" CHAR(10) ) 
//IQXWeb
BEGIN
  DECLARE "UserClass" CHAR(20);
  DECLARE LOCAL TEMPORARY TABLE "DivisionList"(
    "ID" CHAR(20) NOT NULL,) NOT transactional;
  DECLARE LOCAL TEMPORARY TABLE "DepartmentList"(
    "ID" CHAR(20) NOT NULL,) NOT transactional;
  SET "UserClass" = (SELECT top 1 "iqxnetuserclassid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID");
  CASE "UserClass"
  WHEN 'CANDIDATE' THEN
    INSERT INTO "DivisionList" SELECT "p"."divisionid" FROM "person" AS "p" KEY JOIN "iqxnetuserlink" AS "l" WHERE "l"."iqxnetuserid" = "pWebUserID" AND "p"."divisionid" IS NOT NULL;
    INSERT INTO "DepartmentList" SELECT "s"."departmentid" FROM "search" AS "s" KEY JOIN "person" AS "p" KEY JOIN "iqxnetuserlink" AS "l" WHERE "l"."iqxnetuserid" = "pWebUserID" AND("s"."temp" = 1 OR "s"."permanent" = 1) AND "s"."departmentid" IS NOT NULL
  WHEN 'CLIENT' THEN
    INSERT INTO "DivisionList" SELECT "c"."divisionid" FROM "company" AS "c" KEY JOIN "employment" AS "e" KEY JOIN "iqxnetuserlink" AS "l" WHERE "l"."iqxnetuserid" = "pWebUserID" AND "c"."divisionid" IS NOT NULL
  WHEN 'AGENCY' THEN
    INSERT INTO "DivisionList" SELECT "c"."divisionid" FROM "company" AS "c" KEY JOIN "employment" AS "e" KEY JOIN "iqxnetuserlink" AS "l" WHERE "l"."iqxnetuserid" = "pWebUserID" AND "c"."divisionid" IS NOT NULL
  END CASE;
  SELECT DISTINCT
    "b"."id",
    "g"."description",
    "isnull"("g"."fileextension","reverse"("left"("reverse"("g"."description"),"charindex"('.',"reverse"("g"."description"))-1)))
    FROM "blobstore" AS "b"
      JOIN "globaldocument" AS "g" ON "b"."id" = "g"."globaldocumentID" AND "b"."class" = 'G'
      LEFT OUTER JOIN "globaldocumentdetail" AS "dep" ON "g"."globaldocumentID" = "dep"."globaldocumentID" AND "dep"."doctype" = 'DEPT'
      ,"globaldocument" AS "g"
      LEFT OUTER JOIN "globaldocumentdetail" AS "div" ON "g"."globaldocumentID" = "div"."globaldocumentID" AND "div"."doctype" = 'DIV'
    WHERE("dep"."docid" = any(SELECT "id" FROM "DepartmentList") OR "dep"."docid" IS NULL)
    AND("div"."docid" = any(SELECT "id" FROM "DivisionList") OR "div"."docid" IS NULL)
    AND "isnull"("b"."publishtoweb",0) = 1
    AND("g"."iqxnetuserclassid" = "UserClass" OR "g"."iqxnetuserclassid" = '%' OR "g"."iqxnetuserclassid" IS NULL)
END /* DOC
2018-03-14 PC IW-108 and OP-42
2018-04-25 GJ Removed document type - as no longer required
2018-04-26 PC,MS optimise joins
2018-06-13 MS respect globaldocument.fileextension if supplied
2018-09-13 PC test routine
2018-10-23 MHS IW-456 multi-cand fix, including general re-write of proc and tests
2020-34-1 MHS IQ-1362 Changed to reflect new architecture. Will work with the globaldocumentdetail compatibility view
but tests will only work with IQX >= 2.20.4 because the new globaldocumentlink table must exist
*/
/* TEST
def t select * from NetStandardDocumentList('test.candidate') where DocumentID='TEST';
insert into blobstore (class,id,publishtoweb) on existing update values ('G','TEST',1);
insert into globaldocument (globaldocumentid,description,iqxnetuserclassid,fileextension) on existing update values ('TEST','TestDoc.txt',null,'txt');
delete from globaldocumentlink where globaldocumentid='TEST';
delete from search where personid='TEST';
update person set divisionid=null where personid='TEST';
do t;
expect DocumentID=TEST, DocumentDescription=TestDoc.txt, DocumentExtension=txt;
insert into globaldocumentlink (globaldocumentlinkid, globaldocumentID, departmentid) values (uniquekey(''),'TEST','~~');
do t;
expect EOF;
insert into search (searchid,temp,permanent,personid,departmentid) on existing update values ('TEST',1,1,'TEST','~~');
do t;
expect DocumentID=TEST, DocumentDescription=TestDoc.txt, DocumentExtension=txt;
insert into globaldocumentlink (globaldocumentlinkid, globaldocumentID, divisionid) values (uniquekey(''),'TEST','TEST');
do t;
expect EOF;
update person set divisionid='TEST' where personid='TEST';
do t;
expect DocumentID=TEST, DocumentDescription=TestDoc.txt, DocumentExtension=txt;
delete from search where searchid='TEST';
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetStandardDocumentList" IS 
{CREATE PROCEDURE pears."NetStandardDocumentList" (IN pWebUserID CHAR(20))
RESULT (DocumentID CHAR(20),DocumentDescription CHAR(100),DocumentExtension CHAR(10))
//IQXWeb
BEGIN
    DECLARE UserClass CHAR(20);
    DECLARE LOCAL TEMPORARY TABLE DivisionList(
    ID CHAR(20) NOT NULL) NOT transactional;
    DECLARE LOCAL TEMPORARY TABLE DepartmentList(
    ID CHAR(20) NOT NULL) NOT transactional;
 
    SET UserClass=(SELECT top 1 iqxnetuserclassid FROM iqxnetuser WHERE iqxnetuserid=pWebUserID);
    CASE UserClass
        WHEN 'CANDIDATE' THEN
         INSERT INTO DivisionList SELECT p.divisionid FROM person p KEY JOIN iqxnetuserlink l WHERE l.iqxnetuserid=pWebUserID AND p.divisionid IS NOT NULL;    
         INSERT INTO DepartmentList SELECT s.departmentid FROM SEARCH s KEY JOIN person p KEY JOIN iqxnetuserlink l WHERE l.iqxnetuserid=pWebUserID AND (s.temp=1 OR s.permanent=1) AND s.departmentid IS NOT NULL;
        WHEN 'CLIENT' THEN
         INSERT INTO DivisionList SELECT c.divisionid FROM company c KEY JOIN employment e KEY JOIN iqxnetuserlink l WHERE l.iqxnetuserid=pWebUserID AND c.divisionid IS NOT NULL;    
        WHEN 'AGENCY' THEN
         INSERT INTO DivisionList SELECT c.divisionid FROM company c KEY JOIN employment e KEY JOIN iqxnetuserlink l WHERE l.iqxnetuserid=pWebUserID AND c.divisionid IS NOT NULL;    
    END CASE;
 
    SELECT DISTINCT
        b.id,
        g.description,
        isnull(g.fileextension,reverse(LEFT(reverse(g.description),charindex('.',reverse(g.description))-1)))
    FROM 
        blobstore b
            JOIN globaldocument g ON b.id = g.globaldocumentID AND b.class='G'
                LEFT OUTER JOIN globaldocumentdetail dep ON g.globaldocumentID = dep.globaldocumentID AND dep.doctype='DEPT',
            globaldocument g 
                LEFT OUTER JOIN globaldocumentdetail div ON g.globaldocumentID = div.globaldocumentID AND div.doctype='DIV'
    WHERE
        (dep.docid IN (SELECT id FROM DepartmentList) OR dep.docid IS NULL)
        AND (div.docid IN (SELECT id FROM DivisionList) OR div.docid IS NULL)
        AND isnull(b.publishtoweb,0)=1
        AND (g.iqxnetuserclassid = UserClass OR g.iqxnetuserclassid = '%' OR g.iqxnetuserclassid IS NULL)
 
END
 
/* DOC
2018-03-14 PC IW-108 and OP-42
2018-04-25 GJ Removed document type - as no longer required
2018-04-26 PC,MS optimise joins
2018-06-13 MS respect globaldocument.fileextension if supplied
2018-09-13 PC test routine
2018-10-23 MHS IW-456 multi-cand fix, including general re-write of proc and tests
2020-34-1 MHS IQ-1362 Changed to reflect new architecture. Will work with the globaldocumentdetail compatibility view
          but tests will only work with IQX >= 2.20.4 because the new globaldocumentlink table must exist
*/
 
/* TEST
def t select * from NetStandardDocumentList('test.candidate') where DocumentID='TEST';
insert into blobstore (class,id,publishtoweb) on existing update values ('G','TEST',1);
insert into globaldocument (globaldocumentid,description,iqxnetuserclassid,fileextension) on existing update values ('TEST','TestDoc.txt',null,'txt');
delete from globaldocumentlink where globaldocumentid='TEST';
delete from search where personid='TEST';
update person set divisionid=null where personid='TEST';
do t;
expect DocumentID=TEST, DocumentDescription=TestDoc.txt, DocumentExtension=txt;
insert into globaldocumentlink (globaldocumentlinkid, globaldocumentID, departmentid) values (uniquekey(''),'TEST','~~');
do t;
expect EOF;
insert into search (searchid,temp,permanent,personid,departmentid) on existing update values ('TEST',1,1,'TEST','~~');
do t;
expect DocumentID=TEST, DocumentDescription=TestDoc.txt, DocumentExtension=txt;
insert into globaldocumentlink (globaldocumentlinkid, globaldocumentID, divisionid) values (uniquekey(''),'TEST','TEST');
do t;
expect EOF;
update person set divisionid='TEST' where personid='TEST';
do t;
expect DocumentID=TEST, DocumentDescription=TestDoc.txt, DocumentExtension=txt;
delete from search where searchid='TEST';
*/
}