iVend Database Database : GetUserRights Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pUserKey

In

Reference key of the Security User

VarWChar

50

@pSiteId

In

to identify the store

Integer

4

@pLocalisation

In

For future use

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

CfgEnterprise

Table

Stores the basic configuration entries for the system . These settings are mandatory for the system to work properly.

1

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

/**

Begin tran

select * from SecSecurityUser

EXEC GetUserRights 1

Rollback

*/

CREATE PROCEDURE [dbo].[GetUserRights]

(

@pUserKey   nvarchar(50),

@pSiteId   INT,

@pLocalisation nVarchar(50) = ''

)

AS

BEGIN

SET NOCOUNT ON

DECLARE @lPackageType INT,

  @lLocalization INT

SELECT @lPackageType = 3--ISNULL(Information6, 1)  FROM SysSystemInformation

SELECT @lLocalization = ISNULL(Localization, -1) FROM CfgEnterprise

CREATE TABLE [dbo].[#tmp_UserRights]

(

[SecuritySubFunctionKey] NVARCHAR(50) NOT NULL PRIMARY KEY,

[Id] nvarchar(40) NOT NULL,

[AccessLevel] [int] NOT NULL

)

Insert Into #tmp_UserRights (SecuritySubFunctionKey, Id, AccessLevel)

SELECT DISTINCT f.SecuritySubFunctionKey, f.Id, MIN(s.AccessLevel)

FROM SecSecurityUser u, SecSecurityRole r, SecSecuritySubFunction f, SecSecurityRoleSecuritySubFunction s

WHERE u.SecurityRoleKey = r.SecurityRoleKey

AND  r.SecurityRoleKey = s.SecurityRoleKey

AND  s.SecuritySubFunctionKey = f.SecuritySubFunctionKey

AND  u.SecurityUserKey = @pUserKey

GROUP BY f.SecuritySubFunctionKey, f.Id

Declare @lStoreType int, @lERPType int

Select @lERPType = ERPSystemType

From CfgEnterprise

-- If @lSiteId > 1  -- DO THESE OVERRRIDES ONLY FOR NON ENTERPRISES

-- BEGIN

Select @lStoreType = OwnerShipType

From RtlStore s, InvWarehouse w

Where s.WarehouseKey = w.WarehouseKey

And  s.SiteId = @pSiteId

Update #tmp_UserRights

Set  AccessLevel = Case When s.AccessLevel < #tmp_UserRights.AccessLevel Then s.AccessLevel Else #tmp_UserRights.AccessLevel End

From SecCustomSecuritySubFunction s

Where #tmp_UserRights.SecuritySubFunctionKey = s.SecuritySubFunctionKey

AND  StoreType IN(0, @lStoreType)

AND  ERPType = CASE WHEN @lERPType IS NULL THEN 1 ELSE @lERPType END

And IsNull(Localisation, '') = @pLocalisation

And @pUserKey != '2'

--load security rights as per the Package Type

Update #tmp_UserRights

Set  AccessLevel = Case When s.AccessLevel < #tmp_UserRights.AccessLevel Then s.AccessLevel Else #tmp_UserRights.AccessLevel End

From SecCustomSecurityFeatureSubFunction s

Where #tmp_UserRights.SecuritySubFunctionKey = s.SecuritySubFunctionKey

AND  FeatureType = @lPackageType

AND  Localization = 0

--this is to load the security right as per the localization

Update #tmp_UserRights

Set  AccessLevel = Case When s.AccessLevel < #tmp_UserRights.AccessLevel Then s.AccessLevel Else #tmp_UserRights.AccessLevel End

From SecCustomSecurityFeatureSubFunction s

Where #tmp_UserRights.SecuritySubFunctionKey = s.SecuritySubFunctionKey

AND  Localization = @lLocalization

-- END

-- Select *

-- From SecCustomSecuritySubFunction

-- Where StoreType = @lStoreType

-- And  IsNull(Localisation, '') = @pLocalisation

Select *

From #tmp_UserRights

RETURN

END