pears.NetDashboardObjects

CREATE PROCEDURE "pears"."NetDashboardObjects"( IN "pWebUserID" CHAR(20),IN "pDashboardObjectID" CHAR(20) DEFAULT NULL,IN "pAll" SMALLINT DEFAULT NULL,IN "pDashboard" SMALLINT DEFAULT NULL,IN "pHomePage" SMALLINT DEFAULT NULL ) 
RESULT( "DashboardObjectID" CHAR(20),"UserName" CHAR(60),"Type" CHAR(50),"Title" CHAR(250),"Descriptor" long VARCHAR,"Shared" SMALLINT,"AccessLevel" SMALLINT,"OnDashboard" SMALLINT,"OnHomePage" SMALLINT ) 
// IQXWeb
BEGIN
  DECLARE "uStaffID" CHAR(20);
  DECLARE "uLevel" SMALLINT;
  SELECT "u"."staffid","isnull"("s"."ReportView",0) INTO "uStaffID","uLevel" FROM "iqxnetuser" AS "u" KEY JOIN "staff" AS "s" WHERE "u"."iqxnetuserid" = "pWebUserID";
  SELECT "d"."DashboardObjectID",'' AS "UserName","d"."Type","d"."Title","d"."Descriptor","d"."Shared","d"."AccessLevel","dd"."onDashboard","dd"."onHomePage" FROM "DashboardObject" AS "d" LEFT OUTER JOIN "DashboardDisplayedObject" AS "dd" ON "dd"."DashboardObjectID" = "d"."DashboardObjectID" AND "dd"."staffid" = "uStaffid" WHERE "d"."staffid" = "uStaffID" AND("d"."DashboardObjectID" = "pDashboardObjectID" OR "pAll" = 1 OR "pDashboard" = "dd"."onDashboard" OR "pHomePage" = "dd"."onHomePage") UNION ALL
  SELECT "d"."DashboardObjectID","s"."name" AS "UserName","d"."Type","d"."Title","d"."Descriptor","d"."Shared",NULL,"dd"."onDashboard","dd"."onHomePage" FROM "DashboardObject" AS "d" KEY JOIN "staff" AS "s","DashboardObject" AS "d" LEFT OUTER JOIN "DashboardDisplayedObject" AS "dd" ON "dd"."DashboardObjectID" = "d"."DashboardObjectID" AND "dd"."staffid" = "uStaffid" WHERE "d"."staffid" <> "uStaffID" AND "d"."Shared" = 1 AND "uLevel" >= "isnull"("d"."AccessLevel",0) AND("d"."DashboardObjectID" = "pDashboardObjectID" OR "pAll" = 1 OR "pDashboard" = "dd"."onDashboard" OR "pHomePage" = "dd"."onHomePage") AND "isnull"("s"."divisionid",'') = any(SELECT "DivisionID" FROM "DashboardStaffDivisions"())
    ORDER BY "UserName" ASC,"Title" ASC
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetDashboardObjects" IS 
{CREATE PROCEDURE pears.NetDashboardObjects( IN pWebUserID CHAR(20), IN pDashboardObjectID CHAR(20) DEFAULT NULL, IN pAll SMALLINT DEFAULT NULL , IN pDashboard SMALLINT DEFAULT NULL , IN pHomePage SMALLINT DEFAULT NULL ) 
RESULT(DashboardObjectID CHAR(20), UserName CHAR (60), TYPE CHAR(50), Title CHAR(250), Descriptor long VARCHAR, Shared SMALLINT, AccessLevel SMALLINT, OnDashboard SMALLINT, OnHomePage SMALLINT) 
// IQXWeb
BEGIN
  DECLARE uStaffID CHAR(20);
  DECLARE uLevel SMALLINT;
  SELECT u.staffid, isnull(s.ReportView,0) INTO uStaffID, uLevel FROM iqxnetuser u KEY JOIN staff s WHERE u.iqxnetuserid = pWebUserID;
  SELECT d.DashboardObjectID,'' AS UserName, d.Type, d.Title, d.Descriptor, d.Shared, d.AccessLevel, dd.onDashboard, dd.onHomePage FROM DashboardObject d LEFT OUTER JOIN DashboardDisplayedObject dd ON dd.DashboardObjectID=d.DashboardObjectID AND dd.staffid=uStaffid WHERE d.staffid=uStaffID AND (d.DashboardObjectID=pDashboardObjectID OR pAll=1 OR pDashboard=dd.onDashboard OR pHomePage=dd.onHomePage)
  UNION ALL
  SELECT d.DashboardObjectID,s.name AS UserName, d.Type, d.Title, d.Descriptor, d.Shared, NULL, dd.onDashboard, dd.onHomePage FROM DashboardObject d KEY JOIN staff s, DashboardObject d LEFT OUTER JOIN DashboardDisplayedObject dd ON dd.DashboardObjectID=d.DashboardObjectID AND dd.staffid=uStaffid WHERE d.staffid<>uStaffID AND d.Shared=1 AND uLevel>=isnull(d.AccessLevel,0) AND (d.DashboardObjectID=pDashboardObjectID OR pAll=1 OR pDashboard=dd.onDashboard OR pHomePage=dd.onHomePage) AND isnull(s.divisionid,'') IN (SELECT DivisionID FROM DashboardStaffDivisions())
  ORDER BY UserName, Title
END
}