pears.IQXNetAuthenticate

CREATE PROCEDURE "pears"."IQXNetAuthenticate"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "SettingsID" CHAR(20),IN "UID" CHAR(50),IN "PWD" CHAR(50),IN "CallType" CHAR(1),IN "ProcName" CHAR(100),IN "pKey" CHAR(50) ) 
RESULT( "IQXNetUserID" CHAR(20),"Name" CHAR(50),"IQXNetUserClassID" CHAR(20),"StaffID" CHAR(20),"DatabaseUser" CHAR(50),"ReportsFolder" CHAR(250),"PasswordOk" tinyint,"PermissionOk" tinyint,"Expired" tinyint,"Rights" long VARCHAR,"Switches" long VARCHAR,"LastLoggedIn" TIMESTAMP ) 
BEGIN
  SELECT "u"."IQXNetUserID",
    "u"."Name",
    "u"."IQXNetUserClassID",
    "isnull"("u"."Staffid","c"."DefaultStaffID","s"."DefaultStaffID") AS "StaffID",
    "isnull"("c"."DatabaseUser","s"."DefaultDatabaseUser") AS "DatabaseUser",
    "isnull"("c"."ReportsFolder","s"."DefaultReportsFolder") AS "ReportsFolder",
    (IF CAST("PWD" AS BINARY) = "decrypt"("u"."Password","pKey") THEN 1 ELSE 0 endif) AS "PasswordOk",
    (CASE "CallType" WHEN 'S' THEN "c"."Selects"
    WHEN 'P' THEN "IQXNetAllowed"("ProcName","c"."Calls")
    WHEN 'W' THEN "IQXNetAllowed"("ProcName","c"."Services")
    WHEN 'J' THEN "IQXNetAllowed"("ProcName","c"."Jobs")
    WHEN 'A' THEN "IQXNetHasPermission"("u"."IQXNetUserID",'LOGIN') END) AS "PermissionOk",
    (IF "isnull"("u"."ExpiryDate",CURRENT DATE+1) <= CURRENT DATE THEN 1 ELSE 0 endif) AS "Expired",
    (IF "CallType" = 'A' THEN
      (SELECT "list"("a"."IQXNetRightID")
        FROM "IQXNetRightAllowed" AS "a"
        WHERE "isnull"((SELECT FIRST "Assigned" FROM "IQXNetRightAssigned" WHERE "IQXNetRightID" = "a"."IQXNetRightID" AND "IQXNetUserID" = "u"."IQXNetUserID"),0)
        +"isnull"((SELECT FIRST 1 FROM "IQXNetRightDefault" WHERE "IQXNetRightID" = "a"."IQXNetRightID" AND "IQXNetUserClassID" = "a"."IQXNetUserClassID" AND "ID" = "a"."ID"),0) > 0
        AND "a"."IQXNetUserClassID" = "u"."IQXNetUserClassID" AND "a"."ID" = '*')
    ELSE
      NULL
    endif) AS "Rights",
    (IF "CallType" = 'A' THEN
      (SELECT "list"("IQXNetSwitchID") FROM "IQXNetSwitch" WHERE "IQXNetOverrideSwitchValue"("u"."IQXNetUserClassID","u"."IQXNetUserID","IQXNetSwitchID","Value") > 0)
    ELSE
      NULL
    endif) AS "Switches","u"."LastLoggedIn"
    FROM "IQXNetUser" AS "u" KEY JOIN "IQXNetUserClass" AS "c" JOIN "IQXNetSettings" AS "s" ON "s"."IQXNetSettingsID" = "SettingsID"
    WHERE "u"."LoginID" = "UID"
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."IQXNetAuthenticate" IS 
{CREATE PROCEDURE IQXNetAuthenticate 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
(IN SettingsID CHAR(20),IN UID CHAR(50),IN PWD CHAR(50),IN CallType CHAR(1),IN ProcName CHAR(100),IN pKey CHAR(50))
RESULT(IQXNetUserID CHAR(20),Name CHAR(50),IQXNetUserClassID CHAR(20),StaffID CHAR(20),DatabaseUser CHAR(50),ReportsFolder CHAR(250),PasswordOk tinyint,PermissionOk tinyint,Expired tinyint,Rights long VARCHAR,Switches long VARCHAR,LastLoggedIn TIMESTAMP)
BEGIN
  SELECT u.IQXNetUserID,
    u.Name,
    u.IQXNetUserClassID,
    isnull(u.Staffid,c.DefaultStaffID,s.DefaultStaffID) AS StaffID,
    isnull(c.DatabaseUser,s.DefaultDatabaseUser) AS DatabaseUser,
    isnull(c.ReportsFolder,s.DefaultReportsFolder) AS ReportsFolder,
    (IF CAST(PWD AS BINARY) = decrypt(u.Password,pKey) THEN 1 ELSE 0 endif) AS PasswordOk,
    (CASE CallType WHEN 'S' THEN c.Selects
    WHEN 'P' THEN IQXNetAllowed(ProcName,c.Calls)
    WHEN 'W' THEN IQXNetAllowed(ProcName,c.Services)
    WHEN 'J' THEN IQXNetAllowed(ProcName,c.Jobs)
    WHEN 'A' THEN IQXNetHasPermission(u.IQXNetUserID,'LOGIN')
    END) AS PermissionOk,
    (IF isnull(u.ExpiryDate,CURRENT DATE+1) <= CURRENT DATE THEN 1 ELSE 0 endif) AS Expired,
    (IF CallType = 'A' THEN
      (SELECT list(a.IQXNetRightID) FROM
        IQXNetRightAllowed AS a WHERE
        isnull((SELECT FIRST Assigned FROM IQXNetRightAssigned WHERE IQXNetRightID = a.IQXNetRightID AND IQXNetUserID = u.IQXNetUserID),0)+
        isnull((SELECT FIRST 1 FROM IQXNetRightDefault WHERE IQXNetRightID = a.IQXNetRightID AND IQXNetUserClassID = a.IQXNetUserClassID AND ID = a.ID),0) > 0 AND
        a.IQXNetUserClassID = u.IQXNetUserClassID AND a.ID = '*')
    ELSE
      NULL
    endif) AS Rights,
    (IF CallType = 'A' THEN
      (SELECT list(IQXNetSwitchID) FROM IQXNetSwitch WHERE IQXNetOverrideSwitchValue(u.IQXNetUserClassID,u.IQXNetUserID,IQXNetSwitchID,VALUE)>0)
    ELSE
      NULL
    endif) AS Switches, u.LastLoggedIn 
    FROM IQXNetUser AS u KEY JOIN IQXNetUserClass AS c JOIN IQXNetSettings AS s ON s.IQXNetSettingsID = SettingsID WHERE
    u.LoginID = UID
END
}