Elite SQL Queries

Elite 3E Self-maintaining

select Timekeeper.Number as UserID, Client.Number as ClientID, Matter.Number as MatterID, WorkDate, WorkHrs as AmountWorked
FROM Matter
INNER JOIN Client on Client.ClientIndex=Matter.Client
INNER JOIN Timecard ON Matter.MattIndex = Timecard.Matter
INNER JOIN Timekeeper ON Timecard.Timekeeper = Timekeeper.TkprIndex
--WHERE WorkDate > ''
iManage - trigger incremental repair of document manually for testing
INSERT INTO [worksite].[MHGROUP].[DOCHISTORY]
           ([DOCNUM]
           ,[VERSION]
           ,[ACTIVITY]
           ,[ACTIVITY_CODE]
           ,[ACTIVITY_DATETIME]
           ,[DURATION]
           ,[PAGES_PRINTED]
           ,[NUM1]
           ,[NUM2]
           ,[NUM3]
           ,[DATA1]
           ,[DATA2]
           ,[DOCUSER]
           ,[APPNAME]
           ,[LOCATION]
           ,[COMMENTS])
     VALUES
           (1150
           ,1
           ,'Change Security'
           ,'8'
           ,getdate()
           ,0
           ,0
           ,null
           ,null
           ,null
           ,null
           ,null
           ,'ADMIN'
           ,'MANAGE32'
           ,'LOON'
           ,null)
GO

Elite Webview Security Queries

--Note: The following queries assume you have both Webview and Working Timekeeper extensions enabled. Comment appropriately if you have only 1. Also note that Webview, unlike DTE Axiom (or DM and iManage) do not use ZZINT groups. And so to test client-level walls, you cannot simply have a blank matter. You must change the value of the @stringValue variable. If you want to improve the efficiency of the query, the where clause for matter-level walls should be an '=' (rather than like). But it works fine if you just use 'like'. I show the difference in the first query.

declare @client varchar(12)
declare @matter varchar(12)
declare @stringValue varchar(40)
declare @secGroupID varchar(40)
set @client = '1000'                                                        --CHANGE CLIENT NUMBER HERE
set @matter = '1001'                                                        --CHANGE MATTER NUMBER HERE
-- comment out whichever stringvalue you aren't using
--set @stringValue = @client               --USE THIS LINE TO TEST CLIENT-LEVEL SECURITY
set @stringValue = @client+'-'+@matter        --USE THIS LINE TO TEST CLIENT-MATTER SECURITY (NOTE - you may have to change the separator character to '.')


--Security Table (Webview)
select u.ufullname,s.*
from security s
join usmaster u on u.userid = s.username
where s.string_value = '' ---enter the client/matter number here (dbo.entities - financialSystemID)

--Mattwork table (Working timekeeper)
select MW.mmatter,M.maddr2,MW.mwtkinit,tk.tkfirst + ' ' + tk.tklast as 'UserName', MW.mwtitle,MW.mwtonly
from mattwork MW
join matter M on M.mmatter = MW.mmatter
join timekeep tk on tk.tkinit = mw.mwtkinit
where mw.mmatter like @stringValue
order by mw.mwtkinit

--Make sure the proper timekeepers are secured for a client-level wall.
--Not really necessary if the above query returns as expected
select distinct mwtkinit
from mattwork
where mmatter like @stringValue
order by mwtkinit