pears.NetDashboardObjectSet

CREATE PROCEDURE "pears"."NetDashboardObjectSet"( IN "pWebUserID" CHAR(20),IN "pDashboardObjectID" CHAR(20) DEFAULT NULL,IN "pType" CHAR(50) DEFAULT NULL,IN "pTitle" CHAR(250) DEFAULT NULL,IN "pDescriptor" long VARCHAR DEFAULT NULL,IN "pShared" SMALLINT DEFAULT NULL,IN "pAccessLevel" SMALLINT DEFAULT NULL,IN "pDashboard" SMALLINT DEFAULT NULL,IN "pHomePage" SMALLINT DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
// IQXWeb
BEGIN
  DECLARE "uStaffID" CHAR(20);
  SELECT "staffid" INTO "uStaffID" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID";
  IF "uStaffID" IS NULL THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF "pType" = 'DELETE' THEN
    DELETE FROM "DashboardObject" WHERE "DashboardObjectID" = "pDashboardObjectID" AND "StaffID" = "uStaffID";
    SELECT '0:~Success';
    RETURN
  END IF;
  IF "pDashboardObjectID" IS NULL THEN
    SET "pDashboardObjectID" = "uniquekey"('');
    INSERT INTO "DashboardObject"( "DashboardObjectID","StaffID","Type","Title","Descriptor","Shared","AccessLevel" ) VALUES( "pDashboardObjectID","uStaffID","pType","pTitle","pDescriptor","pShared","isnull"("pAccessLevel",0) ) 
  ELSE
    UPDATE "DashboardObject" SET "Title" = "isnull"("pTitle","Title"),"Descriptor" = "isnull"("pDescriptor","Descriptor"),"Shared" = "isnull"("pShared","Shared"),"AccessLevel" = "isnull"("pAccessLevel","AccessLevel") WHERE "DashboardObjectID" = "pDashboardObjectID" AND "StaffID" = "uStaffID"
  END IF;
  IF "pDashboard" IS NOT NULL THEN
    INSERT INTO "DashboardDisplayedObject"( "DashboardObjectID","StaffID","OnDashboard" ) ON existing UPDATE defaults off VALUES( "pDashboardObjectID","uStaffID","pDashboard" ) 
  END IF;
  IF "pHomePage" IS NOT NULL THEN
    INSERT INTO "DashboardDisplayedObject"( "DashboardObjectID","StaffID","OnHomePage" ) ON existing UPDATE defaults off VALUES( "pDashboardObjectID","uStaffID","pHomePage" ) 
  END IF;
  DELETE FROM "DashboardDisplayedObject" WHERE "DashboardObjectID" = "pDashboardObjectID" AND "StaffID" = "uStaffID" AND "OnDashboard" = 0 AND "OnHomePage" = 0;
  SELECT '0:'+"pDashboardObjectID"+'~Success'
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetDashboardObjectSet" IS 
{CREATE PROCEDURE pears.NetDashboardObjectSet( IN pWebUserID CHAR(20), IN pDashboardObjectID CHAR(20) DEFAULT NULL, IN pType CHAR(50) DEFAULT NULL, IN pTitle CHAR(250) DEFAULT NULL, IN pDescriptor long VARCHAR DEFAULT NULL, IN pShared SMALLINT DEFAULT NULL, IN pAccessLevel SMALLINT DEFAULT NULL, IN pDashboard SMALLINT DEFAULT NULL, IN pHomePage SMALLINT DEFAULT NULL) 
RESULT(pResult CHAR(250)) 
// IQXWeb
BEGIN
  DECLARE uStaffID CHAR(20);
  SELECT staffid INTO uStaffID FROM iqxnetuser WHERE iqxnetuserid = pWebUserID;
  IF uStaffID IS NULL THEN
     SELECT '99:~Permission denied';
     RETURN
  END IF;
  IF pType='DELETE' THEN
     DELETE FROM DashboardObject WHERE DashboardObjectID=pDashboardObjectID AND StaffID=uStaffID;
     SELECT '0:~Success';
     RETURN;
  END IF;
  IF pDashboardObjectID IS NULL THEN
    SET pDashboardObjectID=uniquekey('');
    INSERT INTO DashboardObject (DashboardObjectID,StaffID,TYPE,Title,Descriptor,Shared,AccessLevel) VALUES (pDashboardObjectID,uStaffID,pType,pTitle,pDescriptor,pShared,isnull(pAccessLevel,0))
  ELSE
    UPDATE DashboardObject SET Title=isnull(pTitle,Title), Descriptor=isnull(pDescriptor,Descriptor), Shared=isnull(pShared,Shared), AccessLevel=isnull(pAccessLevel,AccessLevel) WHERE DashboardObjectID=pDashboardObjectID AND StaffID=uStaffID
  END IF;
  IF pDashboard IS NOT NULL THEN
    INSERT INTO DashboardDisplayedObject (DashboardObjectID, StaffID, OnDashboard) ON existing UPDATE defaults off VALUES (pDashboardObjectID,uStaffID,pDashboard);
  END IF;
  IF pHomePage IS NOT NULL THEN
    INSERT INTO DashboardDisplayedObject (DashboardObjectID, StaffID, OnHomePage) ON existing UPDATE defaults off VALUES (pDashboardObjectID,uStaffID,pHomePage);
  END IF;
  DELETE FROM DashboardDisplayedObject WHERE DashboardObjectID=pDashboardObjectID AND StaffID=uStaffID AND OnDashboard=0 AND OnHomePage=0;
  SELECT '0:'+pDashboardObjectID+'~Success'
END
}