140 lines
4.4 KiB
VB.net
140 lines
4.4 KiB
VB.net
Public Class ClassDatatables
|
|
|
|
#Region "TBDD_GROUP"
|
|
Public Shared Function AddGroupToProfile(GroupId As Integer, ProfileId As Integer) As Boolean
|
|
Try
|
|
Dim oSQL = $"
|
|
INSERT INTO TBDD_USRGRP_DOKTYPE
|
|
(DOCTYPE_ID, GROUP_ID, ADDED_WHO)
|
|
VALUES ({ProfileId}, {GroupId}, '{Environment.UserName}')
|
|
"
|
|
Return DATABASE_ECM.ExecuteNonQuery(oSQL)
|
|
Catch ex As Exception
|
|
LOGGER.Error(ex)
|
|
Return False
|
|
End Try
|
|
End Function
|
|
|
|
Public Shared Function RemoveGroupFromProfile(GroupId As Integer, ProfileId As Integer) As Boolean
|
|
Try
|
|
Dim oSQL = $"DELETE FROM TBDD_USRGRP_DOKTYPE WHERE DOCTYPE_ID = {ProfileId} AND GROUP_ID = {GroupId}"
|
|
Return DATABASE_ECM.ExecuteNonQuery(oSQL)
|
|
Catch ex As Exception
|
|
LOGGER.Error(ex)
|
|
Return False
|
|
End Try
|
|
End Function
|
|
|
|
Public Shared Function GetAssignedGroups(ProfileId As Integer) As DataTable
|
|
Try
|
|
Dim oSQL As String = $"
|
|
SELECT GUID, NAME FROM TBDD_GROUPS
|
|
WHERE GUID IN
|
|
(
|
|
SELECT GROUP_ID
|
|
FROM TBDD_USRGRP_DOKTYPE
|
|
WHERE DOCTYPE_ID = {ProfileId}
|
|
)
|
|
"
|
|
Dim oDatatable As DataTable = DATABASE_ECM.GetDatatable(oSQL)
|
|
|
|
Return oDatatable
|
|
Catch ex As Exception
|
|
LOGGER.Error(ex)
|
|
Return Nothing
|
|
End Try
|
|
End Function
|
|
|
|
Public Shared Function GetAvailableGroups(ProfileId As Integer) As DataTable
|
|
Try
|
|
Dim oSQL As String = $"
|
|
SELECT GUID, NAME FROM TBDD_GROUPS
|
|
WHERE GUID NOT IN
|
|
(
|
|
SELECT GROUP_ID
|
|
FROM TBDD_USRGRP_DOKTYPE
|
|
WHERE DOCTYPE_ID = {ProfileId}
|
|
)
|
|
"
|
|
Dim oDatatable As DataTable = DATABASE_ECM.GetDatatable(oSQL)
|
|
|
|
Return oDatatable
|
|
Catch ex As Exception
|
|
LOGGER.Error(ex)
|
|
Return Nothing
|
|
End Try
|
|
End Function
|
|
#End Region
|
|
|
|
#Region "TBDD_USER"
|
|
|
|
Public Shared Function AddUserToProfile(UserId As Integer, ProfileId As Integer) As Boolean
|
|
Try
|
|
Dim oSQL = $"
|
|
INSERT INTO TBDD_USER_DOKTYPE
|
|
(DOCTYPE_ID, USER_ID, ADDED_WHO)
|
|
VALUES ({ProfileId}, {UserId}, '{Environment.UserName}')
|
|
"
|
|
Return DATABASE_ECM.ExecuteNonQuery(oSQL)
|
|
Catch ex As Exception
|
|
LOGGER.Error(ex)
|
|
Return False
|
|
End Try
|
|
End Function
|
|
|
|
Public Shared Function RemoveUserFromProfile(UserId As Integer, ProfileId As Integer) As Boolean
|
|
Try
|
|
Dim oSQL = $"DELETE FROM TBDD_USER_DOKTYPE WHERE DOCTYPE_ID = {ProfileId} AND USER_ID = {UserId}"
|
|
Return DATABASE_ECM.ExecuteNonQuery(oSQL)
|
|
Catch ex As Exception
|
|
LOGGER.Error(ex)
|
|
Return False
|
|
End Try
|
|
End Function
|
|
|
|
Public Shared Function GetAssignedUsers(ProfileId As Integer) As DataTable
|
|
Try
|
|
Dim oSQL As String = $"
|
|
SELECT GUID, EMAIL, NAME + ', ' + PRENAME AS NAME FROM TBDD_USER
|
|
WHERE GUID IN (
|
|
SELECT USER_ID
|
|
FROM TBDD_USER_DOKTYPE
|
|
WHERE DOCTYPE_ID = {ProfileId}
|
|
)
|
|
"
|
|
Dim oDatatable As DataTable = DATABASE_ECM.GetDatatable(oSQL)
|
|
|
|
Return oDatatable
|
|
Catch ex As Exception
|
|
LOGGER.Error(ex)
|
|
Return Nothing
|
|
End Try
|
|
End Function
|
|
|
|
Public Shared Function GetAvailableUsers(ProfileId As Integer) As DataTable
|
|
Try
|
|
Dim oSQL As String = $"
|
|
SELECT GUID, EMAIL, NAME + ', ' + PRENAME AS NAME FROM TBDD_USER
|
|
WHERE GUID NOT IN (
|
|
SELECT USER_ID
|
|
FROM TBDD_USER_DOKTYPE
|
|
WHERE DOCTYPE_ID = {ProfileId}
|
|
)
|
|
AND GUID IN (SELECT USER_ID FROM [dbo].[FNDD_GET_USER_FOR_MODULE] ('fileFLOW', 1))
|
|
"
|
|
Dim oDatatable As DataTable = DATABASE_ECM.GetDatatable(oSQL)
|
|
|
|
Return oDatatable
|
|
Catch ex As Exception
|
|
LOGGER.Error(ex)
|
|
Return Nothing
|
|
End Try
|
|
End Function
|
|
#End Region
|
|
|
|
|
|
|
|
|
|
|
|
End Class
|