pears.DashboardObjects

CREATE PROCEDURE "pears"."DashboardObjects"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  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,"Interval" SMALLINT ) 
BEGIN
  DECLARE "uStaffID" CHAR(20);
  DECLARE "uLevel" SMALLINT;
  DECLARE "uDashInterval" SMALLINT;
  DECLARE "uHomeInterval" SMALLINT;
  SELECT "s"."staffid","isnull"("s"."ReportView",0),"s"."dashboardRefreshInterval","s"."homepageRefreshInterval" INTO "uStaffID","uLevel","uDashInterval","uHomeInterval" FROM "staff" AS "s" WHERE "s"."staffid" = "userstaffid";
  SELECT "d"."DashboardObjectID",'' AS "UserName","d"."Type","d"."Title","d"."Descriptor","d"."Shared","d"."AccessLevel","dd"."onDashboard","dd"."onHomePage",CASE WHEN "pDashboard" = 1 THEN "uDashInterval" WHEN "pHomePage" = 1 THEN "uHomeInterval" END
    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" = 1 AND "dd"."onDashboard" > 0) OR("pHomePage" = 1 AND "dd"."onHomePage" > 0)) UNION ALL
  SELECT "d"."DashboardObjectID","s"."name" AS "UserName","d"."Type","d"."Title","d"."Descriptor","d"."Shared",NULL,"dd"."onDashboard","dd"."onHomePage",CASE WHEN "pDashboard" = 1 THEN "uDashInterval" WHEN "pHomePage" = 1 THEN "uHomeInterval" END
    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" = 1 AND "dd"."onDashboard" > 0) OR("pHomePage" = 1 AND "dd"."onHomePage" > 0))
    AND "isnull"("s"."divisionid",'') = any(SELECT "DivisionID" FROM "DashboardStaffDivisions"())
    ORDER BY "UserName" ASC,"Title" ASC
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."DashboardObjects" IS 
{CREATE PROCEDURE DashboardObjects 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
( 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,INTERVAL SMALLINT ) 
BEGIN
  DECLARE uStaffID CHAR(20);
  DECLARE uLevel SMALLINT;
  DECLARE uDashInterval SMALLINT;
  DECLARE uHomeInterval SMALLINT;
  SELECT s.staffid,isnull(s.ReportView,0),s.dashboardRefreshInterval,s.homepageRefreshInterval INTO uStaffID,uLevel,uDashInterval,uHomeInterval FROM staff AS s WHERE s.staffid = userstaffid;
  SELECT d.DashboardObjectID,'' AS UserName,d.Type,d.Title,d.Descriptor,d.Shared,d.AccessLevel,dd.onDashboard,dd.onHomePage,CASE WHEN pDashboard=1 THEN uDashInterval WHEN pHomePage=1 THEN uHomeInterval END 
  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 = 1 AND dd.onDashboard > 0) OR (pHomePage = 1 AND dd.onHomePage > 0)) UNION ALL
  SELECT d.DashboardObjectID,s.name AS UserName,d.Type,d.Title,d.Descriptor,d.Shared,NULL,dd.onDashboard,dd.onHomePage,CASE WHEN pDashboard=1 THEN uDashInterval WHEN pHomePage=1 THEN uHomeInterval END 
  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 = 1 AND dd.onDashboard > 0) OR (pHomePage = 1 AND dd.onHomePage > 0)) 
  AND isnull(s.divisionid,'') = any(SELECT DivisionID FROM DashboardStaffDivisions())
    ORDER BY UserName ASC,Title ASC
END
}