pears.NetAgencyStandardDocumentList

CREATE PROCEDURE "pears"."NetAgencyStandardDocumentList"( IN "pWebUserID" CHAR(20) ) 
RESULT( "DocDescription" CHAR(100),"DocumentType" CHAR(50),"GlobalDocumentID" CHAR(20) ) 
// IQXWeb
BEGIN
  DECLARE "UserType" CHAR(20);
  DECLARE "UserAgencyID" CHAR(20);
  DECLARE "UserDivision" CHAR(250);
  DECLARE "UserDepartment" CHAR(250);
  SELECT "secondaryagencyid",
    "iqxnetuserclassid",
    "p"."divisionid",
    (SELECT "list"("s"."departmentid") FROM "search" AS "s" WHERE "s"."personid" = "p"."personid") INTO "UserAgencyID","UserType","UserDivision","UserDepartment"
    FROM "iqxnetuser" AS "u"
      KEY JOIN "iqxnetuserlink"
      KEY JOIN "person" AS "p"
      KEY JOIN "pay_employee"
      ,"person" AS "p"
      LEFT OUTER JOIN "division" AS "dv" ON "dv"."divisionid" = "p"."divisionid"
    WHERE "u"."iqxnetuserid" = "pWebUserID";
  SELECT "g"."description" AS "DocDescription",
    "dt"."description" AS "DocType",
    "g"."globaldocumentid",
    (SELECT "list"("DocId") FROM "globaldocumentdetail" WHERE "doctype" = 'div' AND "globaldocumentid" = "g"."globaldocumentid") AS "DivisionID",
    (SELECT "list"("docid") FROM "globaldocumentdetail" WHERE "doctype" = 'dept' AND "globaldocumentid" = "g"."globaldocumentid" AND "charindex"("docid","UserDepartment") > 0) AS "DepartmentID",
    (SELECT "list"("DocId") FROM "globaldocumentdetail" WHERE "doctype" = 'dept' AND "globaldocumentid" = "g"."globaldocumentid") AS "GlobalDepartmentList"
    FROM "globaldocument" AS "g"
      JOIN "blobstore" AS "b" ON "b"."id" = "g"."globaldocumentID"
      ,"globaldocument" AS "g"
      JOIN "documenttype" AS "dt" ON "dt"."documenttypeid" = "g"."documenttypeid"
    WHERE("isnull"("g"."iqxnetuserclassid","UserType") = "UserType" OR "isnull"("UserType",'') = '')
    AND "isnull"("b"."publishtoweb",0) = 1
    AND "isnull"("g"."defunct",0) = 0
    AND("charindex"("UserDivision","DivisionID") > 0 OR "isnull"("Divisionid",'') = '')
    AND("length"("DepartmentID") > 0 OR "isnull"("GlobalDepartmentList",'') = '')
END /* DOC
2018-02-16 PC OP-42 list general agency documents
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetAgencyStandardDocumentList" IS 
{CREATE PROCEDURE pears."NetAgencyStandardDocumentList"(IN pWebUserID CHAR(20))
RESULT(DocDescription CHAR(100),DocumentType CHAR(50),GlobalDocumentID CHAR(20))
// IQXWeb
BEGIN
    DECLARE UserType CHAR(20);
    DECLARE UserAgencyID CHAR(20);
    DECLARE UserDivision CHAR(250);
    DECLARE UserDepartment CHAR(250);
 
    SELECT 
        secondaryagencyid, 
        iqxnetuserclassid,
        p.divisionid,
        (SELECT list(s.departmentid) FROM SEARCH s WHERE s.personid=p.personid) INTO UserAgencyID,UserType,UserDivision,UserDepartment 
    FROM 
        iqxnetuser u 
            KEY JOIN iqxnetuserlink 
            KEY JOIN person p 
            KEY JOIN pay_employee,
        person p
            LEFT OUTER JOIN division dv ON dv.divisionid=p.divisionid
    WHERE
        u.iqxnetuserid=pWebUserID;
 
    SELECT 
        g.description AS DocDescription,
        dt.description AS DocType,
        g.globaldocumentid,
        (SELECT list(DocId) FROM globaldocumentdetail WHERE doctype='div' AND globaldocumentid=g.globaldocumentid) AS DivisionID,
        (SELECT list(docid) FROM globaldocumentdetail WHERE doctype='dept' AND globaldocumentid=g.globaldocumentid AND charindex(docid,UserDepartment) > 0) AS DepartmentID,
        (SELECT list(DocId) FROM globaldocumentdetail WHERE doctype='dept' AND globaldocumentid=g.globaldocumentid) AS GlobalDepartmentList
    FROM 
        globaldocument g 
            JOIN blobstore b ON b.id=g.globaldocumentID,
        globaldocument g
            JOIN documenttype dt ON dt.documenttypeid=g.documenttypeid
    WHERE
        (isnull(g.iqxnetuserclassid,UserType)=UserType OR isnull(UserType,'')='')
        AND isnull(b.publishtoweb,0)=1
        AND isnull(g.defunct,0)=0
        AND (charindex(UserDivision,DivisionID) > 0 OR isnull(Divisionid,'')='')
        AND (LENGTH(DepartmentID) > 0 OR isnull(GlobalDepartmentList,'')='')
 
END
 
/* DOC
2018-02-16 PC OP-42 list general agency documents
*/
}