Private Const DEBUG_MODE =0

Private Const cenLicenses = 8
Private Const cenUsers = 16
Private Const cenDat = 32
Private Const cenDef = 64
Private Const cenWeb = 256
Private Const cenDSEnsureOpenConnection = 1
Private Const SINGLE_QUOTE = "'"

Private Const FLD_STAKEHOLDERS = "Stakeholders"
Private Const FLD_BUSINESS_OWNER = "Business_Owner"
Private Const FLD_CUSTOM_APPROVERS = "Custom_Approvers"

Private Const TABLE_USER = "tblUser"
Private Const FLD_USER_NAME = "tUserName"
Private Const FLD_NAME = "tName"
Private Const FLD_ID = "nID"
Private Const cenMultiChoice = 104

Private Const FEATURE_ACCESS_ALL_DEPARTMENTS=131
Private Const FEATURE_ACCESS_ALL_LOCATIONS=132
Private Const FEATURE_ACCESS_ALL_COMPANIES=133
Private Const ROLES_MAIN_TABLE="[Roles01_Dat].dbo.[tblDts]"
Private Const FLD_ITEM_LEVEL="Item_Level"

Private Const FLD_USER_LOCATION = "Location"

Private Const EMP_JOB_MODE_SINGLE = "1"

'************************ NO ARGUMENT MACROS *****************************

'---------------------------------------------------------------------------
'NAME:          GetMacroToday
'DESCRIPTION:   Get today's date.
'---------------------------------------------------------------------------
Public Function GetMacroToday(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
        Dim varDate
        
        varDate = Date
        GetMacroToday = Year(varDate) & "/" & Month(varDate) & "/" & Day(varDate)
End Function

'---------------------------------------------------------------------------
'NAME:          GetMacro1stDayWeek
'DESCRIPTION:   Get the date of the first day of the current week
'---------------------------------------------------------------------------
Public Function GetMacro1stDayWeek(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
        Dim varDate
        Dim intWeekDay
			
			intWeekDay=Weekday(Date)
        varDate = DateAdd("d",-(intWeekDay-1),Date)
        GetMacro1stDayWeek = Year(varDate) & "/" & Month(varDate) & "/" & Day(varDate)
End Function


'---------------------------------------------------------------------------
'NAME:          GetMacro31DaysAgo
'DESCRIPTION:   Get the date 31 days ago
'---------------------------------------------------------------------------
Public Function GetMacro31DaysAgo(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

        Dim varDate
        
        varDate = DateAdd("d", -31, Date)
        GetMacro31DaysAgo = Year(varDate) & "/" & Month(varDate) & "/" & Day(varDate)

End Function

'---------------------------------------------------------------------------
'NAME:          GetMacro31DaysFromNow
'DESCRIPTION:   Get the date 31 days from now
'---------------------------------------------------------------------------
Public Function GetMacro31DaysFromNow(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

        Dim varDate
        
        varDate = DateAdd("d", 31, Date)
        GetMacro31DaysFromNow = Year(varDate) & "/" & Month(varDate) & "/" & Day(varDate)

End Function


'---------------------------------------------------------------------------
'NAME:          GetMacroThisYear
'DESCRIPTION:   Get the first day and last day of this year.

'---------------------------------------------------------------------------
Public Function GetMacroThisYear(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
                                
		GetMacroThisYear = Year(Date) & "/1/1"

End Function

Public Function GetMacroLastDayYear(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
                                
    Dim varLastDayOfYear
    Dim intYear
   
    GetMacroLastDayYear= Year(Date) & "/12/31"

End Function


'---------------------------------------------------------------------------
'NAME:          GetMacroThisMonth
'ARGUMENTS:     strName As String - Macro name
'               intID as long  - Record ID
'               strWhere As Strin - String where condition
'DESCRIPTION:   Get the first day and last day of this month.
'---------------------------------------------------------------------------
'
Public Function GetMacroThisMonth(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    Dim intMonth
    Dim intYear
        
    ' get the current month and year
    intYear = DatePart("yyyy", Date)
    intMonth = DatePart("m", Date)

		GetMacroThisMonth = intYear & "/" & intMonth & "/1"

End Function


Public Function GetMacroLastDayMonth(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

   
    Dim varLastDayOfMonth
    Dim intMonth
    Dim intYear
        
    ' get the current month and year
    intYear = DatePart("yyyy", Date)
    intMonth = DatePart("m", Date)
   
    ' next month minus one day
    varLastDayOfMonth = DateSerial(intYear, intMonth + 1, 0)

    GetMacroLastDayMonth= Year(varLastDayOfMonth) & "/" & Month(varLastDayOfMonth) & "/" & Day(varLastDayOfMonth) 

End Function


'---------------------------------------------------------------------------
'NAME:          GetMacroThisQuarter
'DESCRIPTION:   Get the first day and last day of this quarter.
'---------------------------------------------------------------------------
'
Public Function GetMacroThisQuarter(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    Dim varFirstDayOfQuarter
    
    varFirstDayOfQuarter = GetDayOFQuarter(True)
		GetMacroThisQuarter = Year(varFirstDayOfQuarter) & "/" & Month(varFirstDayOfQuarter) & "/" & Day(varFirstDayOfQuarter) 

End Function

Public Function GetMacroLastDayQuarter(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

     Dim varLastDayOFQuarter
        
     varLastDayOFQuarter = GetDayOFQuarter(False)
		GetMacroLastDayQuarter = varLastDayOFQuarter
End Function




'---------------------------------------------------------------------------
'NAME:          GetMacroThisYearRange
'DESCRIPTION:   Get the first day and last day of this year.

'---------------------------------------------------------------------------
Public Function GetMacroThisYearRange(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
                                
    Dim varFirstDayOfYear
    Dim varLastDayOfYear
    Dim intYear

    intYear = DatePart("yyyy", Date)

    varFirstDayOfYear = DateSerial(intYear, 1, 1)

    varLastDayOfYear = DateSerial(intYear, 12, 31)

    GetMacroThisYearRange = "BETWEEN #" & varFirstDayOfYear & "# AND #" & varLastDayOfYear & "#"

End Function



'---------------------------------------------------------------------------
'NAME:          GetMacroThisMonthRange
'ARGUMENTS:     strName As String - Macro name
'               intID as long  - Record ID
'               strWhere As Strin - String where condition
'DESCRIPTION:   Get the first day and last day of this month.
'---------------------------------------------------------------------------
'
Public Function GetMacroThisMonthRange(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    Dim varFirstDayOfMonth
    Dim varLastDayOfMonth
    Dim intMonth
    Dim intYear
        
    ' get the current month and year
    intYear = DatePart("yyyy", Date)
    intMonth = DatePart("m", Date)

    varFirstDayOfMonth = DateSerial(intYear, intMonth, 1)
   
    ' next month minus one day
    varLastDayOfMonth = DateSerial(intYear, intMonth + 1, 0)

    GetMacroThisMonthRange = "BETWEEN #" & varFirstDayOfMonth & "# AND #" & varLastDayOfMonth & "#"

End Function


'---------------------------------------------------------------------------
'NAME:          GetMacroThisQuarterRange
'DESCRIPTION:   Get the first day and last day of this quarter.
'---------------------------------------------------------------------------
'
Public Function GetMacroThisQuarterRange(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    Dim varFirstDayOfQuarter
    Dim varLastDayOFQuarter
    
    varFirstDayOfQuarter = GetDayOFQuarter(True)
    varLastDayOFQuarter = GetDayOFQuarter(False)
    GetMacroThisQuarterRange = "BETWEEN  #" & varFirstDayOfQuarter & "# AND #" & varLastDayOFQuarter & "#"

End Function

'---------------------------------------------------------------------------
'NAME:          GetDayOFQuarter
'ARGUMENTS:     fFirst  - To indicate whether to get first or the last day
'DESCRIPTION:   Get the first or last day of the quarter.
'---------------------------------------------------------------------------
'
Private Function GetDayOFQuarter(fFirst)
   
    Dim strDay
    Dim intQuarter
    
    intQuarter = DatePart("q", Date)
    
    Select Case intQuarter
        Case 1
            ' Get first or last day of january if first quarter
            strDay = GetDayOfMonth(fFirst, 1)
        Case 2
            ' Get first or last day of april if second quarte
            strDay = GetDayOfMonth(fFirst, 4)
        Case 3
            ' Get first or last day of the july if third quarter
            strDay = GetDayOfMonth(fFirst, 7)
        Case 4
            ' Get first or last day of the october if fourth quarter
            strDay = GetDayOfMonth(fFirst, 10)
    End Select
    
    GetDayOFQuarter = strDay

End Function


'---------------------------------------------------------------------------
'NAME:          GetDayOfMonth
'ARGUMENTS:     fFirst - To indicate whether to get first or last day
'               intMonth - Month
'DESCRIPTION:   Get the first or last day of the quarter given the month.
'---------------------------------------------------------------------------
'
Private Function GetDayOfMonth(fFirst, intMonth)
    
    Dim varDay
    Dim intYear

    intYear = DatePart("yyyy", Date)

    If fFirst Then
        ' Get the first day of the first month of the quarter
        varDay = DateSerial(intYear, intMonth, 1)

    Else
        ' Get the last day of the quarter
        ' Increase three month to get last month of the quarter If the quarter is not the last quarter of the year
        varDay = DateSerial(intYear, intMonth + 3, 0)

    End If  ' If fFirst Then
    
    GetDayOfMonth = varDay

End Function




'************************ DTS RECORD MACROS *****************************

'---------------------------------------------------------------------------
'NAME:          GetMacroUser
'DESCRIPTION:   Get the name current user.
'---------------------------------------------------------------------------
'
Public Function GetMacroUser(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
    
    Dim strLogonUser
    
    If Not oCensusApplication Is Nothing Then
        
        If Not recRevisionHistory Is Nothing Then
                        ' Logon Account Name
                        strLogonUser = GetUpdatedByAccount(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
                        
                        strLogonUser = GetFullName(oCensusApplication, strLogonUser)
                        
                        GetMacroUser = EscapeStr(strLogonUser)
                        
                        ' and set the new value so that we have an expression like:
            '               'User'='User' =>TRUE
            '               'User'='' =>FALSE
            oExpression.ModifiedValue = EscapeStr(oExpression.Value)
            
                Else
                        ' Full Name
                        strLogonUser = oCensusApplication.User
                        
                        GetMacroUser = GetFullName(oCensusApplication, strLogonUser)
                        
        End If
    End If
    
End Function

'---------------------------------------------------------------------------
'NAME:          GetMacroOriginatorName
'DESCRIPTION:   Get the name of the originator of the specified DTS record.

'---------------------------------------------------------------------------
'
Public Function GetMacroOriginatorName(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
    
    Dim rstMainTable
    Dim rstUsers
    Dim ORIGINATOR_FIELD_NAME
    Dim MAIN_DTS_TABLE_NAME
    Dim USER_TABLE_NAME
    Dim strSQL

    ORIGINATOR_FIELD_NAME = "nOriginatorID"
    MAIN_DTS_TABLE_NAME = "tblDts"
    USER_TABLE_NAME = "tblUser"
    
    ' Get the originator ID from the specified dts record in the main DTS table
    strSQL = "SELECT [" & ORIGINATOR_FIELD_NAME & "] FROM [" & MAIN_DTS_TABLE_NAME & "] WHERE nID = " & lngDtsRecordID
    
    Set rstMainTable = oCensusApplication.CurrentProject.DataStores.Item(32).GetConnection(1).Execute( _
                strSQL, , 1)
    
    If Not rstMainTable.EOF Then
    
        'only continue if there is a record found in the main table
        If Not IsNull(rstMainTable.Fields(ORIGINATOR_FIELD_NAME).Value) Then
            
            strSQL = "SELECT tName FROM [" & USER_TABLE_NAME & "] WHERE (nID > 0) AND fDeleted = 0 AND nID = " & _
                        rstMainTable.Fields(ORIGINATOR_FIELD_NAME).Value
                        
            Set rstUsers = oCensusApplication.CurrentProject.DataStores.Item(32). _
                            GetConnection(1).Execute(strSQL, , 1)
            
            If Not rstUsers.EOF Then
                If Not IsNull(rstUsers.Fields(0).Value) Then
                    GetMacroOriginatorName = rstUsers.Fields(0).Value
                            
                End If
            End If
            
        End If
        
    End If
    
End Function

'---------------------------------------------------------------------------
'NAME:          GetMacroGroupList
'DESCRIPTION:   Get all the names of the groups.

'---------------------------------------------------------------------------
'
Public Function GetMacroGroupList(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    If Not oCensusApplication Is Nothing Then
        Dim objCOMHlpr
        Dim objGroupMgr
        Dim objIGroupMgr
        Dim arrGroupNames

        Set objCOMHlpr = CreateObject("MqScrCOM.CMqCOMIntHpr")
        Set objGroupMgr = CreateObject("MqCenX20.CMqGroupMgr")
        Set objIGroupMgr = objCOMHlpr.GetInt(objGroupMgr, "IMqGroupMgr")

        objIGroupMgr.Initialize oCensusApplication.DataStores(cenUsers)

        arrGroupNames = objIGroupMgr.GetGroups(True)

        If IsArray(arrGroupNames) Then
                GetMacroGroupList = Join(arrGroupNames, ",")
        End If

        Set objCOMHlpr = Nothing
        Set objGroupMgr = Nothing
        Set objIGroupMgr = Nothing
    End If
            
End Function


'************************ DTS HISTORY MACROS *****************************

Public Function GetMacroOwner(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    Dim rstOwnerID
    Dim lngLastRevisionNumber
    Dim strSQL
        
    If oCensusApplication Is Nothing Then
        Exit Function
    End If
    
    strSQL = "SELECT nUserID FROM tblDts WHERE nID=" & lngDtsRecordID
    
    Set rstOwnerID = oCensusApplication.CurrentProject.DataStores.Item(cenDat). _
                GetConnection(cenDSEnsureOpenConnection).Execute(strSQL, , adCmdText)
    
    If Not rstOwnerID.EOF Then
        If Not IsNull(rstOwnerID.Fields(0).Value) Then
            
            GetMacroOwner = rstOwnerID.Fields(0).Value
        End If
    End If
    
End Function

Public Function GetMacroOriginator(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    Dim rstOwnerID
    Dim lngLastRevisionNumber
    Dim strSQL
    
    If oCensusApplication Is Nothing Then
        Exit Function
    End If

    strSQL = "SELECT nOriginatorID FROM tblDts WHERE nID=" & lngDtsRecordID
    
    'use the originator of the last revision in the given set
    Set rstOwnerID = oCensusApplication.CurrentProject.DataStores.Item(cenDat). _
                    GetConnection(cenDSEnsureOpenConnection).Execute(strSQL, , adCmdText)
    
    If Not rstOwnerID.EOF Then
        If Not IsNull(rstOwnerID.Fields(0).Value) Then
            GetMacroOriginator = rstOwnerID.Fields(0).Value
        End If
    End If
    
End Function

Private Sub GetRevisionNumberRange(recRevisionHistoryRecord, lngFirstRevisionNumber, lngLastRevisionNumber)

        'get the first and last revision numbers for the given set of rev. history records
        recRevisionHistoryRecord.MoveFirst
        
        'get the first one
         If Not IsNull(recRevisionHistoryRecord("nRevisionNumber").Value) Then

        lngFirstRevisionNumber = recRevisionHistoryRecord("nRevisionNumber").Value

         End If

        'get the last one
        recRevisionHistoryRecord.MoveLast

         If Not IsNull(recRevisionHistoryRecord("nRevisionNumber").Value) Then

        lngLastRevisionNumber = recRevisionHistoryRecord("nRevisionNumber").Value

         End If
            
End Sub

Public Function GetMacroPreviousOwner(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
        
    Dim rstOwnerEmail
    Dim lngLastRevisionNumber
    Dim OWNER_FIELD_ID
    Dim strCurrentOwner
    Dim blnFound
    
    If oCensusApplication Is Nothing Then
        Exit Function
    End If

    If recRevisionHistory Is Nothing Then
        GetMacroPreviousOwner = ""
    Else

        OWNER_FIELD_ID = 11
        
        'get the previous owner if it was modified in this set of changes
        recRevisionHistory.Filter = "nModifiedDtsFieldID = " & OWNER_FIELD_ID
    
        If Not recRevisionHistory.EOF Then
            If Not IsNull(recRevisionHistory.Fields("tPreviousValue").Value) Then
                Set rstOwnerEmail = oCensusApplication.DataStores.Item(16). _
                        GetConnection(1).Execute( _
                        "SELECT nID FROM tblUser WHERE tName=" & EscapeStr(recRevisionHistory.Fields("tPreviousValue").Value), , 1)
                        
                If Not rstOwnerEmail.EOF Then
                
                    GetMacroPreviousOwner = rstOwnerEmail.Fields(0).Value
                
                End If
                
            End If
        End If
        
        'remove the filter
        recRevisionHistory.Filter = ""
    End If
    
End Function

'----------------------------------------------------------------------------------------
'NAME:          GetUpdatedByAccount
'DESCRIPTION:   Get the name of the logon account that updated a certain record in history.
'----------------------------------------------------------------------------------------
'
Public Function GetUpdatedByAccount(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
    
    Const USER_FIELD = "tName"
    
    If oCensusApplication Is Nothing Then
        Exit Function
    End If

    If recRevisionHistory Is Nothing Then
        Exit Function
    End If

    'get the tName field (user account that made the changes in this set)
    If Not recRevisionHistory.EOF Then
        If Not IsNull(recRevisionHistory.Fields(USER_FIELD).Value) Then
                        
                GetUpdatedByAccount = recRevisionHistory.Fields(USER_FIELD).Value
            
        End If
    End If
    
End Function


Public Function GetMacroDevOwner(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    Dim rstOwnerID
    Dim lngLastRevisionNumber
    
    If oCensusApplication Is Nothing Then
        Exit Function
    End If
    
    'use the owner of the last revision in the given set
    Set rstOwnerID = oCensusApplication.CurrentProject.DataStores.Item(32). _
                    GetConnection(1).Execute( _
                    "SELECT Dev_Owner FROM tblDts WHERE nID=" & lngDtsRecordID, , 1)
    
    If Not rstOwnerID.EOF Then
        If Not IsNull(rstOwnerID.Fields(0).Value) Then
        GetMacroDevOwner = rstOwnerID.Fields(0).Value
                    
        End If
    End If
    
End Function

'---------------------------------------------------------------------------
'NAME:          GetMacroSubmitter
'DESCRIPTION:   Get the name of the Submitter of the specified DTS record.
'---------------------------------------------------------------------------
Public Function GetMacroSubmitter(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    Dim rstSubmitterID
    
    If oCensusApplication Is Nothing Then
        Exit Function
    End If

    'use the submitter in the given set
    Set rstSubmitterID = oCensusApplication.CurrentProject.DataStores.Item(32). _
        GetConnection(1).Execute( _
        "SELECT nSubmitterID FROM [" & GetMainTableName(oCensusApplication.CurrentProject) & "] WHERE nID=" & lngDtsRecordID, , adCmdText)

    If Not rstSubmitterID.EOF Then
        If Not IsNull(rstSubmitterID.Fields(0).Value) Then
        GetMacroSubmitter = rstSubmitterID.Fields(0).Value
        End If
    End If

End Function

'************************ EXPRESSION MACROS *****************************




'************************ HISTORY-EXPRESSION MACROS *****************************
'-------------------------------------------------------------------------------------------------------
'NAME:          GetMacroIsUserInGroup
'DESCRIPTION:   True if the user account that updated certain record belongs to the specified group.
'--------------------------------------------------------------------------------------------------------
'
Public Function GetMacroIsUserInGroup(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
    
    Dim strGroupName
    Dim strUpdatedByAccount
    Dim objUserMgr
    Dim objIUserMgr
    Dim objCOMHlpr
    Dim strExpressionValue
        
    strUpdatedByAccount = GetUpdatedByAccount(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    If Len(strUpdatedByAccount) <> 0 Then
    
                
        Set objCOMHlpr = CreateObject("MqScrCOM.CMqCOMIntHpr")
        Set objUserMgr = CreateObject("MqCenX20.CMqUserMgr")
        
        objUserMgr.Initialize oCensusApplication.DataStores(cenUsers)
        
        Set objIUserMgr = objCOMHlpr.GetInt(objUserMgr, "IMqUserMgr")
        
        strExpressionValue = oExpression.Value
        
        If objIUserMgr.IsMemberOfGroup(CStr(strUpdatedByAccount), strExpressionValue) Then
            strGroupName = strExpressionValue
        Else
            strGroupName = ""
        End If

        'if the group is valid, return it...
        GetMacroIsUserInGroup = EscapeStr(strExpressionValue)

        ' and set the new value so that we have an expression like:
        '               'Users'='Users' =>TRUE
        '               'Users'='' =>FALSE
        oExpression.ModifiedValue = EscapeStr(strGroupName)
    
    End If

    Set objCOMHlpr = Nothing
    Set objUserMgr = Nothing
    Set objIUserMgr = Nothing

End Function

Private Function EscapeStr(strString)
        EscapeStr = SINGLE_QUOTE & Replace(strString, SINGLE_QUOTE, SINGLE_QUOTE & SINGLE_QUOTE) & SINGLE_QUOTE
End Function

Private Function GetFullName(oCensusApplication, strLogonName)

        Dim rstUsers
    Dim strSQL
                        
        strSQL = "SELECT tName FROM [tblUser] WHERE fDeleted = 0 AND tUserName =" & EscapeStr(strLogonName)
        
        Set rstUsers = oCensusApplication.DataStores.Item(CLng(cenUsers)).GetConnection(CLng(cenDSEnsureOpenConnection)).Execute( _
            strSQL)
    
        If Not rstUsers.EOF Then
                If Not IsNull(rstUsers.Fields(0).Value) Then
            GetFullName = rstUsers.Fields(0).Value
                End If
        End If
                        
        If Not rstUsers Is Nothing Then
            rstUsers.Close
            Set rstUsers = Nothing
        End If
        
End Function

'-------------------------------------------------------------------------
'NAME:          GetMacroIsNull
'DESCRIPTION:   To query for null values .
'--------------------------------------------------------------------------
Public Function GetMacroIsNull(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

  oExpression.ModifiedOperator = " Is Null "
  oExpression.ModifiedValue = "  "
  oExpression.FieldID = -19740203

End Function

'-------------------------------------------------------------------------
'NAME:          GetMacroNotIsNull
'DESCRIPTION:   To query for non-null values .
'--------------------------------------------------------------------------
Public Function GetMacroNotIsNull(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
        oExpression.ModifiedOperator = "Is Not Null"
        oExpression.ModifiedValue = "  "
        oExpression.FieldID = -19740203
End Function

'---------------------------------------------------------------------------
'NAME:          GetMacroMyWorkTeam
'DESCRIPTION:   Get the ID of the current user's work team
'---------------------------------------------------------------------------
Public Function GetMacroMyWorkTeam(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    Dim strLogonUser
    
    strLogonUser = oCensusApplication.User

    GetMacroMyWorkTeam = GetUserWorkTeam(oCensusApplication, strLogonUser)

End Function

Private Function GetUserWorkTeam(oCensusApplication, strLogonName)

    Dim rstUsers
    Dim rstWorkTeam
    Dim strSQL
    Dim lngWorkTeamID
    Dim lngWorkTeamFieldID
    Dim objWorkTeamField
    Const FLD_WORK_TEAM = "nWorkTeam"
    Const FLD_ASSIGNED_WORK_TEAM = "nAssignedWorkTeam"

    lngWorkTeamID = 0
                        
    strSQL = "SELECT [" & FLD_WORK_TEAM & "] FROM [" & TABLE_USER & _
                "] WHERE [" & FLD_USER_NAME & "] =" & EscapeStr(strLogonName)

    Set rstUsers = oCensusApplication.DataStores.Item(CLng(cenUsers)).GetConnection( _
                        CLng(cenDSEnsureOpenConnection)).Execute(strSQL)

    If Not rstUsers.EOF Then
        If Not IsNull(rstUsers.Fields(FLD_WORK_TEAM).Value) Then
            lngWorkTeamID = CLng(rstUsers.Fields(FLD_WORK_TEAM).Value)
        End If
    End If
    
    If lngWorkTeamID > 0 Then

	lngWorkTeamFieldID = oCensusApplication.CurrentProject.Fields.FindNextFieldIDByName(FLD_ASSIGNED_WORK_TEAM, 0)

	If lngWorkTeamFieldID > 0 Then
		If oCensusApplication.CurrentProject.Fields.Exists(CLng(lngWorkTeamFieldID)) Then
			Set objWorkTeamField = oCensusApplication.CurrentProject.Fields.Item(CLng(lngWorkTeamFieldID))
        		strSQL = "SELECT [" & objWorkTeamField.ValueTableNameField & "] FROM [" & _
					objWorkTeamField.FieldValueTable & _
                        		"] WHERE [" & objWorkTeamField.ValueTableIDField & "] =" & lngWorkTeamID

        		Set rstWorkTeam = oCensusApplication.CurrentProject.GetAllDataStores.Item(CLng(objWorkTeamField.ValueTableDSType)).GetConnection( _
                                CLng(cenDSEnsureOpenConnection)).Execute(strSQL)

        		If Not rstWorkTeam.EOF Then
     				If Not IsNull(rstWorkTeam.Fields(objWorkTeamField.ValueTableNameField).Value) Then
                			GetUserWorkTeam = rstWorkTeam.Fields(objWorkTeamField.ValueTableNameField).Value
            			End If
        		End If

        		If Not rstWorkTeam Is Nothing Then
            			rstWorkTeam.Close
            			Set rstWorkTeam = Nothing
        		End If

			Set objWorkTeamField = Nothing	
		End If
	End If

    End If

    If Not rstUsers Is Nothing Then
        rstUsers.Close
        Set rstUsers = Nothing
    End If

End Function

'---------------------------------------------------------------------------
'NAME:          GetMacroIssueNumber
'DESCRIPTION:   Get the ID of the current issue
'---------------------------------------------------------------------------
Public Function GetMacroIssueNumber(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    GetMacroIssueNumber = lngDtsRecordID
    
End Function

'---------------------------------------------------------------------------
'NAME:          GetMacroProjectName
'DESCRIPTION:   Get the name of the current project
'---------------------------------------------------------------------------
Public Function GetMacroProjectName(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    GetMacroProjectName = oCensusApplication.CurrentProject.ProjectName
    
End Function

'************************ FUCTIONS FOR REQUIREMENTS PROJECT *****************************
'-------------------------------------------------------------------------------------------------------
'NAME:          GetMacroStakeholders
'DESCRIPTION:   Returns a semi colon separated list of selected Stakeholder IDs.
'--------------------------------------------------------------------------------------------------------
'
Public Function GetMacroStakeholders(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

	Dim strRelRecID
	Dim strVal
	
	If Not oCensusApplication Is Nothing Then

		strVal = GetDtsFieldValue(oCensusApplication, lngDtsRecordID, FLD_STAKEHOLDERS,0, true)
		
	End If
	
    GetMacroStakeholders = strVal
    
End Function


Public Function GetMacroBusinessOwner(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

	Dim strRelRecID
	Dim strVal

	
	If Not oCensusApplication Is Nothing Then

		strVal = GetDtsFieldValue(oCensusApplication, lngDtsRecordID, FLD_BUSINESS_OWNER,0, true)
		
	End If
	
    GetMacroBusinessOwner = strVal  
    
End Function


Private Function GetDtsFieldValue(oCensusApplication, lngDtsRecordID, strFieldName, lngFldID, bReturnValueIDs)

	Dim oDtsFld
	Dim strVal
	
	Set oDtsFld = GetDtsField(oCensusApplication, strFieldName, lngFldID)

	If Not oDtsFld Is Nothing Then
	
		strVal=GetFieldValue(oCensusApplication, lngDtsRecordID, oDtsFld)
	
		If bReturnValueIDs AND oDtsFld.FieldDataType=cenMultiChoice Then
			LogMsg "Converting Dts Field value to IDs from :" & strVal

			strVal=ConvertNamesToIDs(oCensusApplication,strVal, oDtsFld)
		End if
	
	Else
		LogMsg "Dts Field not found for name:" & strFieldName & " and ID:" & lngFldID
	End If
	
	GetDtsFieldValue=strVal
	
End Function

Private Function GetDtsField(oCensusApplication, strFieldName, lngFldID)

	'On Error Resume Next
	
	Dim oDtsFields
	Dim lFldID
	
	Set oDtsFields= oCensusApplication.CurrentProject.Fields
	
	If Len(strFieldName)>0 Then
		lFldID = oDtsFields.FindNextFieldIDByName(CStr(strFieldName), 0)
		
	Else
		lFldID=lngFldID
    End If
    
    LogMsg "Converting field " & strFieldName & " to field ID " & lFldID
    

    If oDtsFields.Exists(Clng(lFldID)) Then
        Set GetDtsField = oDtsFields.Item(Clng(lFldID))
    End If

End Function

Public Function GetFieldValue(oCensusApplication, lngDtsRecordID, oDtsFld)

    Dim strVal
    Dim strSQL

	strVal=""

    If Not oCensusApplication Is Nothing  _
    	AND Not oDtsFld Is Nothing Then


	    strSQL = "SELECT [" & oDtsFld.FieldName & "] FROM [" & oDtsFld.TableName & "] WHERE nID=" & lngDtsRecordID 
	    
	    'use the owner of the last revision in the given set
	    Set rst = oCensusApplication.CurrentProject.DataStores.Item(oDtsFld.TableDSType). _
	                    GetConnection(1).Execute(strSQL, , 1)
	
	    
	    If Not rst.EOF Then
	
	        If Not IsNull(rst.Fields(0).Value) Then
	            strVal = rst.Fields(0).Value
	
	        End If
	    End If

		LogMsg "Query:" & strSQL & " resulted in " & strVal
		
    	GetFieldValue = strVal
    	
    End If
        
End Function

Private Function ConvertNamesToIDs(oCensusApplication,strNames, oDtsFld )

    ' From a list of semi colon separated names, for each name, lookup the ID and return a list of IDs (separated by ;)
    Dim rstNames
    Dim strOneName
    Dim strAllNameIDs
    Dim strOneNameID
    Dim nStart
    Dim strSQL 

    nStart = 1

    While nStart > 0

        strOneName = GetToken(strNames, nStart)

		LogMsg "Converting account [" & strOneName & "] to ID"
		
        strSQL = "SELECT [" & oDtsFld.ValueTableIDField & "] FROM [" & oDtsFld.FieldValueTable & "] WHERE [" & oDtsFld.ValueTableNameField & "]=" & EscapeStr(strOneName)


        Set rstNames = oCensusApplication.DataStores.Item(CLng(oDtsFld.ValueTableDSType)).GetConnection(CLng(cenDSEnsureOpenConnection)).Execute( _
                                            strSQL)

        If Not rstNames.EOF Then
        
            If Not IsNull(rstNames.Fields(0).Value) Then
                strOneNameID = rstNames.Fields(0).Value
                If Len(strOneNameID) > 0 Then
               		
               		LogMsg "Converted account [" & strOneName & "] to " & strOneNameID
               		
                	strAllNameIDs = strAllNameIDs & strOneNameID & ";"
                End If
            End If

		Else
			LogMsg "No record found for account [" & strOneName & "] in " & oDtsFld.FieldValueTable
        End If
        
    Wend
    
    If Right(strAllNameIDs , 1) = ";" Then
    	strAllNameIDs = Left(strAllNameIDs , Len(strAllNameIDs ) - 1)
    End If

	ConvertNamesToIDs=strAllNameIDs

End Function



Public Function GetMacroReqNotifSubject(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
    
    
    Dim lngLastRevisionNumber
    Dim rstValue
    Dim variablename
    Dim strSubject
    Dim strProductID, strFeature1ID, strFeature2ID
    
    strSubject = "Requirements Notification - Issue " & lngDtsRecordID
    
    If oCensusApplication Is Nothing Then
        Exit Function
    End If

    'Get the value for the field from the table of the last revision in the given set.
    Set rstValue = oCensusApplication.CurrentProject.DataStores.Item(cenDat). _
                 GetConnection(1).Execute( _
                "SELECT Product, Feature, SubFeature FROM tblDts WHERE nID=" & lngDtsRecordID, , 1)
   
    If Not rstValue.EOF Then
        'strSubject = strSubject & rstValue.Fields("Product").Value

        strProductID = rstValue.Fields("Product").Value
        If strProductID <> "" Then
                strSubject = strSubject & " - " & LookupDictionaryString(oCensusApplication, "tblProducts", strProductID)

        End If

        strFeature1ID = rstValue.Fields("Feature").Value
        If strFeature1ID <> "" Then
                strSubject = strSubject & " - " & LookupDictionaryString(oCensusApplication, "tblFeatures", strFeature1ID)

        End If
        
        strFeature2ID = rstValue.Fields("SubFeature").Value
        If strFeature2ID <> "" Then
                strSubject = strSubject & " - " & LookupDictionaryString(oCensusApplication, "tblSubFeatures", strFeature2ID)

        End If

    End If
    
    GetMacroReqNotifSubject = strSubject
    
End Function

'-------------------------------------------------------------------------------------------------------
'NAME:          GetToken
'DESCRIPTION:   Breaks a semi-colon separated list into token and keeps returning tokens until done.
'--------------------------------------------------------------------------------------------------------

Private Function GetToken(strText, nStart)

    Dim nPos
    Dim strToken
    
    nPos = InStr(nStart, strText, ";")
    If nPos > 0 Then
        strToken = Mid(strText, nStart, nPos - nStart)
        nStart = nPos + 1
    ElseIf nStart > 0 Then
        strToken = Mid(strText, nStart, Len(strText) - nStart + 1)
        nStart = -1
    End If

    GetToken = strToken

End Function


'-------------------------------------------------------------------------------------------------------
'NAME:          LookupDictionaryString
'DESCRIPTION:   Looks up the value of a dicitionary item given its ID
'--------------------------------------------------------------------------------------------------------

Private Function LookupDictionaryString(oCensusApplication, strTable, nID)

        Dim strSQL
        Dim rstDictionary
        Dim strValue

        strSQL = "SELECT tName FROM [" & strTable & "] WHERE nID = " & nID


        Set rstDictionary = oCensusApplication.CurrentProject.DataStores.Item(cenDef).GetConnection(1).Execute( _
                                                                                strSQL, , 1)

        
    
        If Not rstDictionary.EOF Then
                strValue = rstDictionary.Fields(0).Value
        
        End If

        LookupDictionaryString = strValue

End Function

'---------------------------------------------------------------------------
'NAME:          GetMacroContainsUser
'DESCRIPTION:   Get an expression to be used with a LIKE operator in order
'               to find matches of the current user name in a text field.
'               Example of expression:
'                       MyMemoField     Like    <ContainsUser>
'---------------------------------------------------------------------------
'

Public Function GetMacroContainsUser(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
    
    Dim strLogonUser
    
    ' Full Name
    strLogonUser = oCensusApplication.User
    
    GetMacroContainsUser = "%" & strLogonUser & "%"
    
End Function


'---------------------------------------------------------------------------
'NAME:          GetMacroWorkTeamMembers
'DESCRIPTION:   Get the IDs of the user which belong to the WorkTeam of the issue
'---------------------------------------------------------------------------
Public Function GetMacroWorkTeamMembers(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    Dim strResult
    Dim rsIssue
    Dim rsMembers
    Dim nWorkTeamID
    Dim bMoreThanOne

    Dim strSQL

    If oCensusApplication Is Nothing Then
        Exit Function
    End If
        
    strSQL = "SELECT nAssignedWorkTeam FROM [tblDts] WHERE nID = " & lngDtsRecordID
    
    Set rsIssue = oCensusApplication.CurrentProject.DataStores.Item(CLng(cenDat)). _
                    GetConnection(CLng(cenDSEnsureOpenConnection)).Execute(strSQL, , 1)

    If Not rsIssue.EOF Then

        nWorkTeamID = rsIssue.Fields(0).Value

		If Not IsNull(nWorkTeamID) Then
			Set rsMembers = oCensusApplication.DataStores.Item(CLng(cenUsers)). _
			        GetConnection(CLng(cenDSEnsureOpenConnection)).Execute( _
			        "SELECT nID FROM tblUser WHERE nWorkTeam = " & rsIssue.Fields(0).Value, , 1)

			bMoreThanOne = False

			While Not rsMembers.EOF

			    If bMoreThanOne Then
			        strResult = strResult & ";"
			    End If
        
			    strResult = strResult & rsMembers.Fields(0).Value

			    bMoreThanOne = True

			    rsMembers.MoveNext
        
			Wend
		End If
    End if

    GetMacroWorkTeamMembers = strResult

End Function
 

Private Sub LogMsg(strMsg)
        Dim moEventLogger
        
        'This "IF" is in case we make the variable private to the module and we do not create it and destroy it every call
        If DEBUG_MODE =1 then
            Set moEventLogger = CreateObject("MqSysutils40.CMqEventLogger")
        
            moEventLogger.InitializeEventLogger

	        If Not IsNull(strMsg) Then
	                moEventLogger.LogEvent CStr(strMsg)
	        End If
	        
		End If
		
End Sub

'---------------------------------------------------------------------------
'NAME:          GetMacroUserCompany
'DESCRIPTION:   Get the current user's company
'---------------------------------------------------------------------------
Public Function GetMacroUserCompany(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    Dim strLogonUser
    
    If Not oCensusApplication Is Nothing Then
        
        If Not recRevisionHistory Is Nothing Then
            ' Logon Account Name
            strLogonUser = GetUpdatedByAccount(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
                        
        Else
            ' current user
            strLogonUser = oCensusApplication.User                        
        End If
    End If

    GetMacroUserCompany = GetUserCompany(oCensusApplication, strLogonUser)

End Function

Private Function GetUserCompany(oCensusApplication, strLogonName)

    Dim rstCompany
    Dim strSQL
    
    Const FLD_COMPANY_NAME = "nCompanyName"
                        
    strSQL = "SELECT [" & FLD_NAME & "] FROM [" & TABLE_USER & _
                        "] WHERE [" & FLD_ID & "] = (SELECT [" & FLD_COMPANY_NAME & "] FROM [" & TABLE_USER & _
                "] WHERE [" & FLD_USER_NAME & "] =" & EscapeStr(strLogonName) & ")"

    Set rstCompany = oCensusApplication.DataStores.Item(CLng(cenUsers)).GetConnection( _
                        CLng(cenDSEnsureOpenConnection)).Execute(strSQL)

    If Not rstCompany.EOF Then
        If Not IsNull(rstCompany.Fields(FLD_NAME).Value) Then
            GetUserCompany = CStr(rstCompany.Fields(FLD_NAME).Value)
        End If
    End If
    
    If Not rstCompany Is Nothing Then
        rstCompany.Close
        Set rstCompany = Nothing
    End If

End Function

'---------------------------------------------------------------------------
'NAME:          GetMacroEncIssueNumber
'DESCRIPTION:   Get the encrypted ID of the current issue
'---------------------------------------------------------------------------
Public Function GetMacroEncIssueNumber(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

	Dim objIssueMgr

	Set objIssueMgr = CreateObject("MqCensusWeb65.CMqIssueMgr")

    	GetMacroEncIssueNumber = objIssueMgr.EncryptIssueID(lngDtsRecordID, oCensusApplication.CurrentProject, oCensusApplication)

	Set objIssueMgr = Nothing
    
End Function  

'---------------------------------------------------------------------------
'NAME:          GetFirstSurveyViewBaseURL
'DESCRIPTION:   Get the URL of the first available Survey view for the 
'               current issue project.
'               Since the macro cannot receive parameters such as view id 
'               or view name, it only returns the first match. 
'               The returned value should be something like:
'                   http://server/vit/
'           
'               Usually to construct a final URL like: 
'               http://localhost/issuetracker/logon.asp?View=HelpDesk%2FSurvey&amp;EID=&lt;Encrypted Issue Number&gt;
'
'---------------------------------------------------------------------------
Public Function GetFirstSurveyViewBaseURL(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    Dim strSQL
    Dim rsView

    Const FLD_URL = "tURL"
    Const FLD_PROJ_ID = "nProjectID"
	Const FLD_FEATURE_ID = "nFeatureId"
    Const TBL_WEB_VIEWS = "tblWebViews"
    Const WV_TYPE_SURVEY = 4218945

    ' 
    ' Find the URL of the first Survey view for this project
    '
    strSQL = "SELECT TOP 1 [" & FLD_URL & _
                "] FROM [" & TBL_WEB_VIEWS & _
                "] WHERE [" & FLD_FEATURE_ID & "]=" & WV_TYPE_SURVEY & " AND [" & FLD_PROJ_ID & "]=" & oCensusApplication.CurrentProject.ProjectID

    Set rsView = oCensusApplication.DataStores(cenWeb).GetConnection(cenDSEnsureOpenConnection).Execute(strSQL, , adCmdText)

    If Not rsView.EOF Then

        If Not IsNull(rsView.Fields(FLD_URL).Value) Then
            GetFirstSurveyViewBaseURL = rsView.Fields(FLD_URL).Value
        End If

    End If

    If Not rsView Is Nothing Then
        rsView.Close
        Set rsView = Nothing
    End If


End Function

'---------------------------------------------------------------------------
'NAME:          GetMacroMyOrgLevel
'DESCRIPTION:   Get the value of the current user's org level
'---------------------------------------------------------------------------
Public Function GetMacroMyOrgLevel(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    Dim strLogonUser
    Dim strOrgLevels
    Dim lngUserID
    Dim arrOrgLevels
    Dim strField
    Dim strOper
    Dim strValue
	Dim strOrgLvl
    strLogonUser = oCensusApplication.User
    
    Const NO_LEVELS_FOUND = "-9999"
    Const FLD_NAME_CURRENT_USER_RECORD = "[tblUser].[nID]"
    lngUserID = GetIDByLogonName(oCensusApplication, strLogonUser)
    
    strOrgLevels = GetUserOrgLevels(oCensusApplication, lngUserID, NO_LEVELS_FOUND)
	
	strField = oExpression.ModifiedFieldName
	If strField = "" Then
		strField = oExpression.FieldName
	End If
		
	strOper = oExpression.ModifiedOperator
	If strOper = "" Then
		strOper = oExpression.Operator
	End If

	If strOrgLevels <> "" Then
		If strOrgLevels = NO_LEVELS_FOUND Then
			'If the user doesn't have anything in tblOrgLevel, then return just their record
			strField = FLD_NAME_CURRENT_USER_RECORD
			strValue = lngUserID
		Else
			arrOrgLevels = Split(strOrgLevels, ",")
				
			For lngCount = LBound(arrOrgLevels) To UBound(arrOrgLevels)
				strOrgLvl = arrOrgLevels(lngCount)
				If lngCount = 0 Then
					strValue = "'" & strOrgLvl & "%'"
				Else
					strValue = strValue & " OR " & strField & " " & strOper & " '" & strOrgLvl & "%'"
				End If
				
				If strOrgLvl = "" Then
					strValue = strValue & GetAllOrgLevelQryVal(strField, strOper) 
				End If
			Next
		End If
	Else
		strValue = "'%'" & GetAllOrgLevelQryVal(strField, strOper)
	End If

	oExpression.ModifiedValue = strValue & ")"
	oExpression.ModifiedFieldName = "(" & strField
	oExpression.FieldID = -19740203
		
	GetMacroMyOrgLevel = strValue & ")"

End Function

Private Function GetAllOrgLevelQryVal(ByVal strField, ByVal strOper)
	GetAllOrgLevelQryVal = " OR " & strField & " " & strOper & " '' OR " & strField & " IS NULL"
End Function

Private Function GetUserOrgLevels(oCensusApplication, lngUserID, strResultIfNone)

    Dim rstUsers
    Dim strSQL
    Dim strOrgLevels

    Const FLD_ORG_LEVEL = "Org_Level"
    Const FLD_ID = "nID"
	Const TABLE_ORG_LEVEL = "tblUser_OrgLevel"
	
	Const adClipString = 2
	strOrgLevel = ""
                        
    strSQL = "SELECT [" & FLD_ORG_LEVEL & "] FROM [" & TABLE_ORG_LEVEL & _
                "] WHERE [" & FLD_ID & "] =" & lngUserID

    Set rstUsers = oCensusApplication.DataStores.Item(CLng(cenUsers)).GetConnection( _
                        CLng(cenDSEnsureOpenConnection)).Execute(strSQL)

    If Not rstUsers.EOF Then
        If Not IsNull(rstUsers.Fields(FLD_ORG_LEVEL).Value) Then
            strOrgLevels = rstUsers.GetString(CLng(adClipString), -1, "", ",")
        End If
    Else
		strOrgLevels = strResultIfNone
    End If
    
    If Right(strOrgLevels, 1) = "," Then
		strOrgLevels = Left(strOrgLevels, Len(strOrgLevels) - 1)
    End If
        
    GetUserOrgLevels = strOrgLevels

    If Not rstUsers Is Nothing Then
        rstUsers.Close
        Set rstUsers = Nothing
    End If

End Function

'---------------------------------------------------------------------------
'NAME:          GetMacroResourceRequestor
'DESCRIPTION:   Get the name of the requestor of the resource but from the external HD tblUser.
'---------------------------------------------------------------------------
'
Public Function GetMacroResourceRequestor(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
    
    Dim lngRequestorID
    Dim strFullName
    
    strFullName = ""
    
    If Not oCensusApplication Is Nothing Then
        
        lngRequestorID = GetResourceRequestorID(oCensusApplication, lngDtsRecordID)
       
        If lngRequestorID <> 0 Then
	        strFullName = GetFullNameByID(oCensusApplication, lngRequestorID)
		End If
    End If
    
    GetMacroResourceRequestor = strFullName 
    
End Function

Private Function GetResourceRequestorID(oCensusApplication, lngActionID)

	Dim strSQL 
	Dim rsRes
	Dim lngUserID
	
	lngUserID = 0
	
	
	strSQL = "SELECT [nSubmitterID] FROM [tblDts] WHERE [nID] = (SELECT [nResID] FROM [tblActRes] WHERE [nActID]=" & lngActionID & ")"
	
	Set rsRes = oCensusApplication.CurrentProject.GetAllDataStores.Item(2048).GetConnection(CLng(cenDSEnsureOpenConnection)).Execute(strSQL)
    
    If Not rsRes.EOF Then
        If Not IsNull(rsRes.Fields(0).Value) Then
			lngUserID = rsRes.Fields(0).Value
        End If
    End If
                        
    If Not rsRes Is Nothing Then
        rsRes.Close
        Set rsRes = Nothing
    End If
    
    GetResourceRequestorID = lngUserID
    
End Function

Private Function GetFullNameByID(oCensusApplication, lngUserID)

    CONST FLD_FULL_NAME = "tName"

    GetFullNameByID = GetFieldValueFromUsers(oCensusApplication, FLD_FULL_NAME, lngUserID)
    
End Function


Private Function GetFieldValueFromUsers(oCensusApplication, sFldName, lngUserID)

    Dim rstUsers
    Dim strSQL
    Dim strName
    
    strName = ""
                        
    strSQL = "SELECT [" & sFldName & "] FROM [tblUser] WHERE nID = " & lngUserID
        
    Set rstUsers = oCensusApplication.DataStores.Item(CLng(cenUsers)).GetConnection(CLng(cenDSEnsureOpenConnection)).Execute(strSQL)
    
    If Not rstUsers.EOF Then
        If Not IsNull(rstUsers.Fields(0).Value) Then
			strName = rstUsers.Fields(0).Value
        End If
    End If
                        
    If Not rstUsers Is Nothing Then
        rstUsers.Close
        Set rstUsers = Nothing
    End If
    
    GetFieldValueFromUsers = strName
    
End Function


Private Function GetIDByLogonName(oCensusApplication, strLogonName)

    GetIDByLogonName = GetUserIDByFieldValue(oCensusApplication, FLD_USER_NAME, strLogonName)
    
End Function


Private Function GetIDByFullName(oCensusApplication, strFullName)
    
    GetIDByFullName = GetUserIDByFieldValue(oCensusApplication, FLD_NAME, strFullName)
    
End Function

Private Function GetUserIDByFieldValue(oCensusApplication, strField, strFieldValue)

    Dim rstUsers
    Dim strSQL
    Dim lngID
	
    lngID = 0
    strName = ""
    
    strSQL = "SELECT [" & FLD_ID & "] FROM [" & TABLE_USER & _
                "] WHERE [" & strField & "] =" & EscapeStr(strFieldValue) 
                   
    Set rstUsers = oCensusApplication.DataStores.Item(CLng(cenUsers)).GetConnection(CLng(cenDSEnsureOpenConnection)).Execute(strSQL)
    
    If Not rstUsers.EOF Then
        If Not IsNull(rstUsers.Fields(0).Value) Then
			lngID = rstUsers.Fields(0).Value
        End If
    End If
                        
    If Not rstUsers Is Nothing Then
        rstUsers.Close
        Set rstUsers = Nothing
    End If
    
    GetUserIDByFieldValue = lngID
    
End Function


'---------------------------------------------------------------------------
'NAME:          GetMacroRequestorDir
'DESCRIPTION:   Get the name of the requestor's department's director but from the external HD tblUser.
'---------------------------------------------------------------------------
'
Public Function GetMacroRequestorDir(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
    
    Dim strUserName
    Dim lngDepartment
    
    strUserName = ""
    
    If Not oCensusApplication Is Nothing Then
        
        lngRequestorID = GetResourceRequestorID(oCensusApplication, lngDtsRecordID)
        
        If lngRequestorID <> 0 Then
            
            lngDepartment = GetDepartment(oCensusApplication, lngRequestorID)
            
            If lngDepartment <> 0 Then
				strUserName = GetDepartmentDirectorName(oCensusApplication, lngDepartment)
			End If
		End If
    End If

	If strUserName = "" Then
		'if we didn't get the director, return the requestor
		 If lngRequestorID <> 0 Then
        	strUserName = GetFullNameByID(oCensusApplication, lngRequestorID)
		End If
	End If
	
	GetMacroRequestorDir = strUserName
    
End Function

Private Function GetJobMode(ByVal oCensusApplication)
	
	Dim objSettingMgr
	Set objSettingMgr = CreateObject("MqCenX20.CMqSetting")

	Dim strJobMode
	strJobMode = EMP_JOB_MODE_SINGLE
	
	GetJobMode = objSettingMgr.GetSettingValue(oCensusApplication.CurrentProject.GetAllDataStores.Item(cenLicenses).GetConnection(CLng(cenDSEnsureOpenConnection)), 319)
		
End Function
		
Private Function GetJobTable(ByVal oCensusApplication)

	Const TBL_USER = "tblUser"
	Const TBL_USER_EMPLOYMENT = "tblUser_Employment"

	Dim strUserTable 
	strUserTable = ""
	
	Dim strJobMode    
	strJobMode = GetJobMode(oCensusApplication)
	
	If strJobMode = EMP_JOB_MODE_SINGLE Then
		strUserTable = TBL_USER
	Else
		strUserTable = TBL_USER_EMPLOYMENT
	End If
	
	GetJobTable = strUserTable
End Function

Private Function GetDepartment(oCensusApplication, lngUserID)

    Dim rstUsers
	Dim strSQL
    Dim lngDepartment
    
    lngDepartment = 0
    
	Dim strJobMode
	strJobMode = GetJobMode(oCensusApplication)
	
	If strJobMode = EMP_JOB_MODE_SINGLE Then
		strSQL = "SELECT Department FROM [tblUser] WHERE fDeleted = 0 AND nID = " & lngUserID
	Else
		strSQL = "SELECT B.Department FROM [tblUser] AS A LEFT JOIN [tblUser_Employment] AS B ON A.nPrimaryEmp=B.nCompositeID " & _
					"WHERE fDeleted = 0 AND A.nID = " & lngUserID
	End If
	
    Set rstUsers = oCensusApplication.DataStores.Item(CLng(cenUsers)).GetConnection(CLng(cenDSEnsureOpenConnection)).Execute(strSQL)
    
    If Not rstUsers.EOF Then
        If Not IsNull(rstUsers.Fields(0).Value) Then
			lngDepartment = rstUsers.Fields(0).Value
		End If
    End If
                        
    If Not rstUsers Is Nothing Then
        rstUsers.Close
        Set rstUsers = Nothing
    End If
    
    GetDepartment = lngDepartment
    
End Function

Private Function GetDepartmentDirectorName(oCensusApplication, lngDeptID)

    Dim rstUsers
	Dim strSQL
    Dim strDirName
    
    Const CATEGORY_DIRECTOR = 1
    
    strSQL = "SELECT tName FROM [tblUser] WHERE fDeleted = 0 AND Department =" & lngDeptID & " AND [nCategoryID] = " & CATEGORY_DIRECTOR
        
    Set rstUsers = oCensusApplication.DataStores.Item(CLng(cenUsers)).GetConnection(CLng(cenDSEnsureOpenConnection)).Execute(strSQL)
    
    If Not rstUsers.EOF Then
        If Not IsNull(rstUsers.Fields(0).Value) Then
			strDirName = rstUsers.Fields(0).Value
		End If
    End If
                        
    If Not rstUsers Is Nothing Then
        rstUsers.Close
        Set rstUsers = Nothing
    End If
    
    GetDepartmentDirectorName = strDirName
    
End Function

'---------------------------------------------------------------------------
'NAME:          GetMacroResourceOwner
'DESCRIPTION:   Get the name of the owner of the resource.
'---------------------------------------------------------------------------
'
Public Function GetMacroResourceOwner(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
    
    Dim lngOwnerID
	Dim strSQL
	Dim rsRes
	Dim strOwner
	
	lngOwnerID = 0
    strOwner = ""
    
    If Not oCensusApplication Is Nothing Then
        
		strSQL = "SELECT [nUserID] FROM [tblDts] WHERE [nID] = (SELECT [nResID] FROM [tblActRes] WHERE [nActID]=" & lngDtsRecordID & ")"
	
		Set rsRes = oCensusApplication.CurrentProject.GetAllDataStores.Item(2048).GetConnection(CLng(cenDSEnsureOpenConnection)).Execute(strSQL)
    
		If Not rsRes.EOF Then
		    If Not IsNull(rsRes.Fields(0).Value) Then
				lngOwnerID = rsRes.Fields(0).Value
		    End If
		End If
		                    
		If Not rsRes Is Nothing Then
		    rsRes.Close
		    Set rsRes = Nothing
		End If
    End If
    
    
    If lngOwnerID > 0 Then
		strOwner = GetFullNameByID(oCensusApplication, lngOwnerID)
    End If
    
    GetMacroResourceOwner = strOwner
    
End Function


'---------------------------------------------------------------------------
'NAME:          GetMacroResourceEmployeeMgr
'DESCRIPTION:   Get the ID of the manager of the employee who the resource is for.
'---------------------------------------------------------------------------
'
Public Function GetMacroResourceEmployeeMgr(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
    
    Dim lngEmployee
	Dim lngMgr
	Dim lngDept
	Dim strMgr
	
	lngMgr = 0
    strMgr = ""
    
    If Not oCensusApplication Is Nothing Then
        
        lngEmployee = GetResourceEmployeeByAction(oCensusApplication, lngDtsRecordID)
        
        If lngEmployee > 0 Then
			lngDept = GetDepartment(oCensusApplication, lngEmployee)
			
			If lngDept > 0 Then
				lngMgr = GetResponsible(oCensusApplication, lngDept)
			End If
        End If
    End If
    
    If lngMgr > 0 Then
		strMgr = GetFullNameByID(oCensusApplication, lngMgr)
    End If
    
    GetMacroResourceEmployeeMgr = strMgr
    
End Function

Private Function GetResourceEmployeeByAction(oCensusApplication, lngActionID)
	
	Dim strSQL
	Dim rsRes
	Dim lngEmployee
	
	lngEmployee = 0
	
	strSQL = "SELECT [nEmpID] FROM [tblEmpRes] WHERE [nResID] = (SELECT [nResID] FROM [tblActRes] WHERE [nActID]=" & lngActionID & ")"
	
	Set rsRes = oCensusApplication.CurrentProject.GetAllDataStores.Item(2048).GetConnection(CLng(cenDSEnsureOpenConnection)).Execute(strSQL)
    
	If Not rsRes.EOF Then
	    If Not IsNull(rsRes.Fields(0).Value) Then
			lngEmployee = rsRes.Fields(0).Value
	    End If
	End If
		                    
	If Not rsRes Is Nothing Then
	    rsRes.Close
	    Set rsRes = Nothing
	End If

	GetResourceEmployeeByAction = lngEmployee
	
End Function

Private Function GetResponsible(oCensusApplication, lngRoleID)
	
	Dim strSQL
	Dim rsRole
	Dim lngResponsible
	
	lngResponsible = 0
	
	strSQL = "SELECT [Responsible] FROM [tblDts] WHERE [nID] = " & lngRoleID
	
	Set rsRole = oCensusApplication.CurrentProject.GetAllDataStores.Item(4096).GetConnection(CLng(cenDSEnsureOpenConnection)).Execute(strSQL)
    
	If Not rsRole.EOF Then
	    If Not IsNull(rsRole.Fields(0).Value) Then
			lngResponsible = rsRole.Fields(0).Value
	    End If
	End If
		                    
	If Not rsRole Is Nothing Then
	    rsRole.Close
	    Set rsRole = Nothing
	End If

	GetResponsible = lngResponsible
	
End Function

Private Function GetResponsibleOfDep (oCensusApplication, lngDepID)
	
	Dim strSQL
	Dim rsRole
	Dim lngResponsible
	
	lngResponsible = 0
	
	strSQL = "SELECT [Responsible] FROM [Department] WHERE [nID] = " & lngDepID
	
	Set rsRole = oCensusApplication.CurrentProject.GetAllDataStores.Item(cenUsers).GetConnection(CLng(cenDSEnsureOpenConnection)).Execute(strSQL)
    
	If Not rsRole.EOF Then
	    If Not IsNull(rsRole.Fields(0).Value) Then
			lngResponsible = rsRole.Fields(0).Value
	    End If
	End If
		                    
	If Not rsRole Is Nothing Then
	    rsRole.Close
	    Set rsRole = Nothing
	End If

	GetResponsibleOfDep = lngResponsible
	
End Function


'---------------------------------------------------------------------------
'NAME:          GetMacroDelegateFor
'DESCRIPTION:   Get the people that the user is a delegate for
'				Valid for expression objects in queries where the macro replaces the expression value.
'---------------------------------------------------------------------------
Public Function GetMacroDelegateFor(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
    
	Dim rstUsers
	Dim strSQL
    Dim strDelegateFor
    Dim strLogonUser
    Dim lngUserID
    Dim strField
    Dim strOper
    Dim arrDelegates
    Dim lngCount
    Dim strValue
    Dim strCondOneDelegate
    
    strDelegateFor = ""
    
    strLogonUser = oCensusApplication.User
    
    lngUserID = GetIDByLogonName(oCensusApplication, strLogonUser)
    
    strSQL = "SELECT [Delegate_For] FROM [tblUser] WHERE fDeleted = 0 AND [nID] =" & lngUserID 
    
    Set rstUsers = oCensusApplication.DataStores.Item(CLng(cenUsers)).GetConnection(CLng(cenDSEnsureOpenConnection)).Execute(strSQL)
    
    If Not rstUsers.EOF Then
        If Not IsNull(rstUsers.Fields(0).Value) Then
			strDelegateFor = rstUsers.Fields(0).Value
		End If
    End If
                        
    If Not rstUsers Is Nothing Then
        rstUsers.Close
        Set rstUsers = Nothing
    End If
    
    If strDelegateFor <> "" Then
    
		strField = oExpression.ModifiedFieldName
		If strField = "" Then
			strField = oExpression.FieldName
		End If
			
		strOper = oExpression.ModifiedOperator
		If strOper = "" Then
			strOper = oExpression.Operator
		End If

		arrDelegates = Split(strDelegateFor, ";")
				
		For lngCount = LBound(arrDelegates) To UBound(arrDelegates)
	
			strCondOneDelegate = GetCondsForMCField(strField, strOper, arrDelegates(lngCount))
			
			If lngCount = 0 Then
				strValue = strCondOneDelegate
			Else
				strValue = strValue & " OR " & strField & " " & strOper & strCondOneDelegate
			End If
		Next
		
		oExpression.ModifiedValue = strValue & ")"
		oExpression.ModifiedFieldName = "(" & strField
		oExpression.FieldID = -19740203

		GetMacroDelegateFor = strValue & ")"
	Else
		GetMacroDelegateFor = ""
	End If
    
End Function

Private Function GetCondsForMCField(strField, strOper, strValue)

	GetCondsForMCField = "'" & strValue & "'" & _
							" OR " & strField & " " & strOper & " '" & strValue & ";%'" & _
							" OR " & strField & " " & strOper & " '%;" & strValue & ";%'" & _
							" OR " & strField & " " & strOper & " '%;" & strValue & "'"
							
End Function

'---------------------------------------------------------------------------
'NAME:          GetMacroLastDayWeek
'DESCRIPTION:   Get the date of the last day of the current week
'---------------------------------------------------------------------------
Public Function GetMacroLastDayWeek(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
        Dim varDate
		Dim intWeekday
		Dim firstDayOfWeek
		Dim lastDayOfWeek
		intWeekday = Weekday(Date)
		firstDayOfWeek= DateAdd("d", -(intWeekday-1), Date)
		lastDayOfWeek = DateAdd("d", 6, firstDayOfWeek)	

        GetMacroLastDayWeek = Year(lastDayOfWeek) & "/" & Month(lastDayOfWeek) & "/" & Day(lastDayOfWeek)
End Function


'---------------------------------------------------------------------------
'NAME:          GetMacroLastDayNextWeek
'DESCRIPTION:   Get the date of the last day of the next week
'---------------------------------------------------------------------------
Public Function GetMacroLastDayNextWeek(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
	Dim varDate
	Dim intWeekday
	Dim firstDayOfWeek
	Dim firstDayOfNextWeek
	Dim lastDayOfNextWeek

	intWeekday = Weekday(Date)
	firstDayOfWeek= DateAdd("d", -(intWeekday-1), Date)

	firstDayOfNextWeek = DateAdd("d", 7, firstDayOfWeek)	
	lastDayOfNextWeek = DateAdd("d", 6, firstDayOfNextWeek )	

	GetMacroLastDayNextWeek = Year(lastDayOfNextWeek) & "/" & Month(lastDayOfNextWeek) & "/" & Day(lastDayOfNextWeek)
End Function


'---------------------------------------------------------------------------
'NAME:          GetMacroFirstDayNextWeek
'DESCRIPTION:   Get the date of the first day of the next week
'---------------------------------------------------------------------------
Public Function GetMacroFirstDayNextWeek(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
	Dim varDate
	Dim intWeekday
	Dim firstDayOfWeek
	Dim firstDayOfNextWeek

	intWeekday = Weekday(Date)
	firstDayOfWeek= DateAdd("d", -(intWeekday-1), Date)

	firstDayOfNextWeek = DateAdd("d", 7, firstDayOfWeek)	

	GetMacroFirstDayNextWeek = Year(firstDayOfNextWeek) & "/" & Month(firstDayOfNextWeek) & "/" & Day(firstDayOfNextWeek)
End Function

'---------------------------------------------------------------------------
'NAME:          GetMacroLastDayLastWeek
'DESCRIPTION:   Get the date of the last day of last week
'---------------------------------------------------------------------------
Public Function GetMacroLastDayLastWeek(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
	Dim varDate
	Dim intWeekday
	Dim firstDayOfWeek
	Dim lastDayOfNextWeek

	intWeekday = Weekday(Date)
	firstDayOfWeek= DateAdd("d", -(intWeekday-1), Date)

	lastDayOfNextWeek = DateAdd("d", -7+6, firstDayOfWeek)	

	GetMacroLastDayLastWeek = Year(lastDayOfNextWeek) & "/" & Month(lastDayOfNextWeek) & "/" & Day(lastDayOfNextWeek)
End Function


'---------------------------------------------------------------------------
'NAME:          GetMacroFirstDayLastWeek
'DESCRIPTION:   Get the date of the first day of last week
'---------------------------------------------------------------------------
Public Function GetMacroFirstDayLastWeek(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
	Dim varDate
	Dim intWeekday
	Dim firstDayOfWeek
	Dim firstDayOfNextWeek

	intWeekday = Weekday(Date)
	firstDayOfWeek= DateAdd("d", -(intWeekday-1), Date)

	firstDayOfNextWeek = DateAdd("d", -7, firstDayOfWeek)	

	GetMacroFirstDayLastWeek = Year(firstDayOfNextWeek) & "/" & Month(firstDayOfNextWeek) & "/" & Day(firstDayOfNextWeek)
End Function

'---------------------------------------------------------------------------
'NAME:          GetMacroFirstDayLastMonth
'DESCRIPTION:   Get First Day Last Month.
'---------------------------------------------------------------------------

Public Function GetMacroFirstDayLastMonth(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    Dim lastMonth 
        
    ' get the date last month
    lastMonth = DateAdd("m", -1, Date)
    
    GetMacroFirstDayLastMonth = Year(lastMonth) & "/" & Month(lastMonth) & "/1"

End Function

'---------------------------------------------------------------------------
'NAME:          GetMacroLastDayLastMonth
'DESCRIPTION:   Get Last Day Last Month.
'---------------------------------------------------------------------------

Public Function GetMacroLastDayLastMonth(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

   
    Dim varLastDayOfMonth
    Dim intMonth
    Dim intYear
     
    ' get the current month and year
    intYear = DatePart("yyyy", Date)
    intMonth = DatePart("m", Date)
   
    ' this month minus one day
    varLastDayOfMonth = DateSerial(intYear, intMonth, 0)

    GetMacroLastDayLastMonth= Year(varLastDayOfMonth) & "/" & Month(varLastDayOfMonth) & "/" & Day(varLastDayOfMonth) 

   
End Function

'---------------------------------------------------------------------------
'NAME:          GetFirstSSVBaseURL
'DESCRIPTION:   Get the first self-service view base URL.
'               Since the macro cannot receive parameters such as view id
'               or view name, it only returns the first match.
'               The returned value should be something like:
'                   http://server/vit/
'
'---------------------------------------------------------------------------
Public Function GetFirstSSVBaseURL(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    Dim strSQL
    Dim rsView

    Const FLD_URL = "tURL"
    Const FLD_PROJ_ID = "nProjectID"
    Const FLD_FEATURE_ID = "nFeatureId"
    Const TBL_WEB_VIEWS = "tblWebViews"
	Const WV_TYPE_SS = 8191
    
     'Find the URL of the first self-service view for this project
    
    strSQL = "SELECT TOP 1 [" & FLD_URL & _
                "] FROM [" & TBL_WEB_VIEWS & _
                "] WHERE [" & FLD_FEATURE_ID & "]=" & WV_TYPE_SS & " AND [" & FLD_PROJ_ID & "]=" & oCensusApplication.CurrentProject.ProjectID

    Set rsView = oCensusApplication.DataStores(cenWeb).GetConnection(cenDSEnsureOpenConnection).Execute(strSQL, , adCmdText)

    If Not rsView.EOF Then

        If Not IsNull(rsView.Fields(FLD_URL).Value) Then
            GetFirstSSVBaseURL = rsView.Fields(FLD_URL).Value
        End If

   End If

    If Not rsView Is Nothing Then
        rsView.Close
        Set rsView = Nothing
    End If


End Function

'---------------------------------------------------------------------------
'NAME:          GetMacroMyDepartment
'DESCRIPTION:   Get the value of the current user's departments
'---------------------------------------------------------------------------
Public Function GetMacroMyDepartment(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    Dim strLogonUser
    Dim strDepts
    Dim lngUserID
    Dim arrDepts
    Dim strField
    Dim strOper
    Dim strValue
    strLogonUser = oCensusApplication.User

    Const NO_DEPTS_FOUND = "-9999"
    Const FLD_NAME_CURRENT_USER_RECORD = "[tblUser].[nID]"
    lngUserID = GetIDByLogonName(oCensusApplication, strLogonUser)
    
    strDepts = GetUserDepts(oCensusApplication, lngUserID, NO_LEVELS_FOUND)
	
	strField = oExpression.ModifiedFieldName
	If strField = "" Then
		strField = oExpression.FieldName
	End If
		
	strOper = oExpression.ModifiedOperator
	If strOper = "" Then
		strOper = oExpression.Operator
	End If

	If strDepts = "" Or strDepts = NO_DEPTS_FOUND Then
		'If the user doesn't have any departments, then return just their record
		strField = FLD_NAME_CURRENT_USER_RECORD
		strValue = lngUserID
	Else
		arrDepts = Split(strDepts, ",")
				
		For lngCount = LBound(arrDepts) To UBound(arrDepts)
			If lngCount = 0 Then
				strValue = "'" & arrDepts(lngCount) & "'"
			Else
				strValue = strValue & " OR " & strField & " " & strOper & " '" & arrDepts(lngCount) & "'"
			End If
		Next
	End If
		
	oExpression.ModifiedValue = strValue & ")"
	oExpression.ModifiedFieldName = "(" & strField
	oExpression.FieldID = -19740203
		
	GetMacroMyDepartment = strValue & ")"

End Function

Private Function GetUserDepts(oCensusApplication, lngUserID, strResultIfNone)

    Dim rstUsers
    Dim strSQL
    Dim strDepts

    Const FLD_DEPT = "Department"
    Const FLD_ID = "nID"
	
	Const adClipString = 2
	strOrgLevel = ""
                        
    strSQL = "SELECT [" & FLD_DEPT & "] FROM [" & GetJobTable(oCensusApplication) & _
                "] WHERE [" & FLD_ID & "] =" & lngUserID

    Set rstUsers = oCensusApplication.DataStores.Item(CLng(cenUsers)).GetConnection( _
                        CLng(cenDSEnsureOpenConnection)).Execute(strSQL)

    If Not rstUsers.EOF Then
		Do While Not rstUsers.EOF
			If Not IsNull(rstUsers.Fields(FLD_DEPT).Value) Then
			    strDepts = GetDeptName(CLng(rstUsers.Fields(FLD_DEPT).Value), oCensusApplication) & "," 
			End If
			rstUsers.MoveNext
		Loop
    Else
		strDepts = strResultIfNone
    End If
    
    If Right(strDepts, 1) = "," Then
		strDepts = Left(strDepts, Len(strDepts) - 1)
    End If
       
    GetUserDepts = strDepts

    If Not rstUsers Is Nothing Then
        rstUsers.Close
        Set rstUsers = Nothing
    End If

End Function

Private Function GetDeptName(lngDeptID, oCensusApplication)
	
	Dim strSQL
	Dim rsDept
	
	strSQL = "SELECT [tName] FROM [Department] WHERE [nID]=" & lngDeptID
	
	Set rsDept = oCensusApplication.CurrentProject.GetAllDataStores.Item(cenUsers).GetConnection( _
                        CLng(cenDSEnsureOpenConnection)).Execute(strSQL)
	
	If Not rsDept.EOF Then
		If Not IsNull(rsDept.Fields(0).Value) Then
			GetDeptName = rsDept.Fields(0).Value
		End If
	End If
	
	If Not rsDept Is Nothing Then
		rsDept.Close
		Set rsDept = Nothing
	End If
	

End Function



'---------------------------------------------------------------------------
'NAME:          GetMacro30DaysFromNow
'DESCRIPTION:   Get the date 30 days from now
'---------------------------------------------------------------------------
Public Function GetMacro30DaysFromNow(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

        Dim varDate
        
        varDate = DateAdd("d", 30, Date)
        GetMacro30DaysFromNow = Year(varDate) & "/" & Month(varDate) & "/" & Day(varDate)

End Function

'---------------------------------------------------------------------------
'NAME:          GetMacro60DaysFromNow
'DESCRIPTION:   Get the date 60 days from now
'---------------------------------------------------------------------------
Public Function GetMacro60DaysFromNow(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

        Dim varDate
       
        varDate = DateAdd("d", 60, Date)
        GetMacro60DaysFromNow = Year(varDate) & "/" & Month(varDate) & "/" & Day(varDate)

End Function


Public Function GetMacroEmployee(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    Dim rstEmployeeID
    Dim lngLastRevisionNumber
    Dim strSQL
    
    If oCensusApplication Is Nothing Then
        Exit Function
   End If

    strSQL = "SELECT Employee FROM tblDts WHERE nID=" & lngDtsRecordID
       
    'use the employee of the last revision in the given set
    Set rstEmployeeID = oCensusApplication.CurrentProject.DataStores.Item(cenDat). _
                    GetConnection(cenDSEnsureOpenConnection).Execute(strSQL, , adCmdText)
    
    If Not rstEmployeeID.EOF Then
        If Not IsNull(rstEmployeeID.Fields(0).Value) Then
            GetMacroEmployee = rstEmployeeID.Fields(0).Value
        End If
    End If
    
End Function

Public Function GetMacroManager(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    Dim rstManagerID
    Dim lngLastRevisionNumber
    Dim strSQL
    
    If oCensusApplication Is Nothing Then
        Exit Function
    End If

    strSQL = "SELECT Manager FROM tblDts WHERE nID=" & lngDtsRecordID _
    
    'use the manager of the last revision in the given set
    Set rstManagerID = oCensusApplication.CurrentProject.DataStores.Item(cenDat). _
                    GetConnection(cenDSEnsureOpenConnection).Execute(strSQL, , adCmdText)
    
    If Not rstManagerID.EOF Then
        If Not IsNull(rstManagerID.Fields(0).Value) Then
            GetMacroManager = rstManagerID.Fields(0).Value
        End If
    End If
    
End Function  

Private Function GetMainTableName(ByVal objProject)
    
    Dim lngCount
    Dim varKeys
    Dim strMainTableName
    Const TBL_DTS_DEFAULT = "tblDts"
	Const cenRelationshipTypeMainTable = -1
    
	strMainTableName = TBL_DTS_DEFAULT
    
    
    If Not objProject Is Nothing Then
		
		If Not objProject.Fields Is Nothing Then
			varKeys = objProject.Fields.Keys

			For lngCount = LBound(varKeys) To UBound(varKeys)
				If objProject.Fields.Item(CLng(varKeys(lngCount))).RelationShipType = cenRelationshipTypeMainTable Then
					strMainTableName = objProject.Fields.Item(CLng(varKeys(lngCount))).TableName
					Exit For
				End If
			Next
		End If
    End If
    
    GetMainTableName = strMainTableName
    
End Function

'---------------------------------------------------------------------------
'NAME:          GetMacroActionRequestor
'DESCRIPTION:   Get the name of the requestor of the action
'---------------------------------------------------------------------------
'
Public Function GetMacroActionRequestor(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
    
    Dim lngRequestorID
    
    If Not oCensusApplication Is Nothing Then
        
        lngRequestorID = GetActionRequestorID(oCensusApplication, lngDtsRecordID)
       
    End If
    
    GetMacroActionRequestor = lngRequestorID 
    
End Function

Private Function GetActionRequestorID(oCensusApplication, lngActionID)

	Dim strSQL 
	Dim rsAct
	Dim lngUserID
	
	lngUserID = 0
	
	
	strSQL = "SELECT [nSubmitterID] FROM [tblDts] WHERE [nID] = " & lngActionID
	
	Set rsAct = oCensusApplication.CurrentProject.GetAllDataStores.Item(cenDat).GetConnection(CLng(cenDSEnsureOpenConnection)).Execute(strSQL)
    
    If Not rsAct.EOF Then
        If Not IsNull(rsAct.Fields(0).Value) Then
			lngUserID = rsAct.Fields(0).Value
        End If
    End If
                        
    If Not rsAct Is Nothing Then
        rsAct.Close
        Set rsAct = Nothing
    End If
    
    GetActionRequestorID = lngUserID
    
End Function 


'---------------------------------------------------------------------------
'NAME:          GetMacroEmployeeMgr
'DESCRIPTION:   Get the ID of the manager of the employee
'---------------------------------------------------------------------------
'
Public Function GetMacroEmployeeMgr(oCensusApplication, lngEmployee, oExpression, recRevisionHistory)
    
	Dim lngMgr
    Dim lngDept
    
	lngMgr = 0
    
    If Not oCensusApplication Is Nothing Then
    	
		If lngEmployee > 0 Then
			lngDept = GetDepartment(oCensusApplication, lngEmployee)
			
			If lngDept > 0 Then
				lngMgr = GetResponsibleOfDep(oCensusApplication, lngDept)
			End If
        End If
    End If

    GetMacroEmployeeMgr = lngMgr
    
End Function

' Called in the context of the Actions project but passing an ID of an asset specification
' Return the ID of the Vizor account of the manager
Public Function GetMacroEmployeeMgr_RequestedAsset(oCensusApplication, lngRequestedAssetID, oExpression, recRevisionHistory)

	Dim lngEmployee

	lngEmployee = GetOwner(oCensusApplication, lngRequestedAssetID)

	GetMacroEmployeeMgr_RequestedAsset = GetMacroEmployeeMgr(oCensusApplication, lngEmployee, oExpression, recRevisionHistory)

End Function

' Called in the context of the Actions project but passing an ID of an asset specification
' Return the ID of the Vizor accounts added in the multichoice Custom Approvers, which is a field per asset specification
Public Function GetMacroCustomApprovers_RequestedAsset(oCensusApplication, lngRequestedAssetID, oExpression, recRevisionHistory)

	If Not oCensusApplication Is Nothing Then

		' Get the value of the approvers field from the asset specification (use the name not the field id and do not get the IDs, but the name values )
		GetMacroCustomApprovers_RequestedAsset = GetDtsFieldValue(oCensusApplication, lngRequestedAssetID, FLD_CUSTOM_APPROVERS, 0, true)
		
	End If


End Function


Private Function GetOwner(oCensusApplication, lngDtsRecordID)

    	Dim rstOwnerID
    	Dim strSQL
        Dim lngOwner
    
	lngOwner = 0

        strSQL = "SELECT [nUserID] FROM tblDts WHERE nID=" & lngDtsRecordID
    
    	Set rstOwnerID = oCensusApplication.CurrentProject.DataStores.Item(cenDat). _
                		GetConnection(cenDSEnsureOpenConnection).Execute(strSQL, , adCmdText)
    
    	If Not rstOwnerID.EOF Then
        	If Not IsNull(rstOwnerID.Fields(0).Value) Then
            
            		lngOwner = rstOwnerID.Fields(0).Value
        	End If
    	End If
    
	GetOwner = lngOwner

End Function

'---------------------------------------------------------------------------
'NAME:          GetMacroAccessibleToMe
'DESCRIPTION:   Get the current user's Accessible To value
'---------------------------------------------------------------------------
Public Function GetMacroAccessibleToMe(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    Dim strLogonUser
    
    If Not oCensusApplication Is Nothing Then
        
        If Not recRevisionHistory Is Nothing Then
            ' Logon Account Name
            strLogonUser = GetUpdatedByAccount(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
                        
        Else
            ' current user
            strLogonUser = oCensusApplication.User                        
        End If
    End If

    GetMacroAccessibleToMe = GetUserAccessibleTo(oCensusApplication, strLogonUser)

End Function

Private Function GetUserAccessibleTo(oCensusApplication, strLogonName)

    Dim rstAccessibleTo
    Dim strSQL
    
    Const FLD_ACCESSIBLE_TO = "Accessible_To"
                        
    strSQL = "SELECT [" & FLD_NAME & "] FROM [" & TABLE_USER & _
                        "] WHERE [" & FLD_ID & "] = (SELECT [" & FLD_ACCESSIBLE_TO & "] FROM [" & TABLE_USER & _
                "] WHERE [" & FLD_USER_NAME & "] =" & EscapeStr(strLogonName) & ")"
			
    Set rstAccessibleTo = oCensusApplication.DataStores.Item(CLng(cenUsers)).GetConnection( _
                        CLng(cenDSEnsureOpenConnection)).Execute(strSQL)

    If Not rstAccessibleTo.EOF Then
        If Not IsNull(rstAccessibleTo.Fields(FLD_NAME).Value) Then
            GetUserAccessibleTo = CStr(rstAccessibleTo.Fields(FLD_NAME).Value)
        End If
    End If
    
    If Not rstAccessibleTo Is Nothing Then
        rstAccessibleTo.Close
        Set rstAccessibleTo = Nothing
    End If

End Function

'---------------------------------------------------------------------------
'NAME:          GetMacroAccessibleToMeID
'DESCRIPTION:   Get the current user's Accessible To value
'---------------------------------------------------------------------------
Public Function GetMacroAccessibleToMeID(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    Dim strLogonUser
    
    If Not oCensusApplication Is Nothing Then
        
        If Not recRevisionHistory Is Nothing Then
            ' Logon Account Name
            strLogonUser = GetUpdatedByAccount(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
                        
        Else
            ' current user
            strLogonUser = oCensusApplication.User                        
        End If
    End If

    GetMacroAccessibleToMeID = GetUserAccessibleToID(oCensusApplication, strLogonUser)

End Function

Private Function GetUserAccessibleToID(oCensusApplication, strLogonName)

    Dim rstAccessibleTo
    Dim strSQL
    Dim strResult
	
	strResult = 0
	
    Const FLD_ACCESSIBLE_TO = "Accessible_To"
    
	If UserCanAccessAll(strLogonName, oCensusApplication) Then
		strResult = FLD_ACCESSIBLE_TO	
	Else
		strSQL = "SELECT [" & FLD_ACCESSIBLE_TO & "] FROM [" & TABLE_USER & _
					"] WHERE [" & FLD_USER_NAME & "] =" & EscapeStr(strLogonName)
					
		Set rstAccessibleTo = oCensusApplication.DataStores.Item(CLng(cenUsers)).GetConnection( _
							CLng(cenDSEnsureOpenConnection)).Execute(strSQL)

		If Not rstAccessibleTo.EOF Then
			If Not IsNull(rstAccessibleTo.Fields(FLD_ACCESSIBLE_TO).Value) Then
				strResult = CLng(rstAccessibleTo.Fields(FLD_ACCESSIBLE_TO).Value)
			End If
		End If
		
		If Not rstAccessibleTo Is Nothing Then
			rstAccessibleTo.Close
			Set rstAccessibleTo = Nothing
		End If
	End If
		
	GetUserAccessibleToID = strResult

End Function

Private Function UserCanAccessAll(ByVal strLogonName, ByVal oCensusApplication)
	
	Dim blnReturn
	blnReturn = False
	
	Dim arrExceptionAccounts
	Const MAIL_AGENT_USER = "mailagent"
	Const ISSUE_AGENT_USER = "issueagent"
	
	arrExceptionAccounts = Array(MAIL_AGENT_USER, ISSUE_AGENT_USER)
	
	If UserIsAdmin(strLogonName, oCensusApplication) Then
		blnReturn = True
	Else
		blnReturn = IsValueInArray(arrExceptionAccounts, LCase(strLogonName))
	End If
	
	UserCanAccessAll = blnReturn
	
End Function

Private Function UserIsAdmin(ByVal strUserName, ByVal oCensusApplication)
	Dim blnReturn
	
	blnReturn = False
	
	If Not oCensusApplication Is Nothing Then
        Dim objCOMHlpr
        Dim objGroupMgr
        Dim objIGroupMgr
        Dim arrGroupNames
		Dim lngCount
		
		Const ADMINS_GROUP_NAME = "Admins"
		
        Set objCOMHlpr = CreateObject("MqScrCOM.CMqCOMIntHpr")
        Set objGroupMgr = CreateObject("MqCenX20.CMqGroupMgr")
        Set objIGroupMgr = objCOMHlpr.GetInt(objGroupMgr, "IMqGroupMgr")

        objIGroupMgr.Initialize oCensusApplication.DataStores(cenUsers)

        arrGroupNames = objIGroupMgr.GetGroups(False, strUserName)

		blnReturn = IsValueInArray(arrGroupNames, ADMINS_GROUP_NAME)

        Set objCOMHlpr = Nothing
        Set objGroupMgr = Nothing
        Set objIGroupMgr = Nothing
    End If
	
	UserIsAdmin = blnReturn
End Function

Private Function IsValueInArray(ByVal arrArray, ByVal strValue)

	Dim blnReturn
	blnReturn = False
	
	If IsArray(arrArray) Then
		For lngCount = LBound(arrArray) To UBound(arrArray)
			If arrArray(lngCount) = strValue Then
				blnReturn = True
				Exit For
			End If
		Next
	End If
	
	IsValueInArray = blnReturn
	
End Function

'-------------------------------------------------------------------------------------------------------
'NAME:          GetMacroLevel1Approvers
'DESCRIPTION:   Returns a semi colon separated list of selected Approver IDs.
'--------------------------------------------------------------------------------------------------------
'
Public Function GetMacroLevel1Approvers(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    Dim rstApprovers
    Dim lngLastRevisionNumber
    Dim strApprovers
    Dim strApproversID
    Dim strSQL

    If oCensusApplication Is Nothing Then
        Exit Function
    End If

    strSQL = "SELECT Voters_ID1 FROM tblDts WHERE nID=" & lngDtsRecordID
    
    'use the approver of the last revision in the given set
    Set rstApprovers = oCensusApplication.CurrentProject.DataStores.Item(32). _
                    GetConnection(1).Execute(strSQL, , 1)

    
    If Not rstApprovers.EOF Then

        If Not IsNull(rstApprovers.Fields(0).Value) Then
            strApprovers = rstApprovers.Fields(0).Value

            ' For each Approver, lookup the nUserID and create a ' separated list of IDs
            Dim rstUsers
            Dim strApprover
            Dim strApproverID
            Dim nStart

            nStart = 1
    
            While nStart > 0
    
                    strApprover = GetToken(strApprovers, nStart)
            
                    strSQL = "SELECT nID FROM [tblUser] WHERE fDeleted = 0 AND tName =" & EscapeStr(strApprover)
    
            
                    Set rstUsers = oCensusApplication.DataStores.Item(CLng(cenUsers)).GetConnection(CLng(cenDSEnsureOpenConnection)).Execute( _
                                                        strSQL)
        
                    If Not rstUsers.EOF Then
                            If Not IsNull(rstUsers.Fields(0).Value) Then
                                    strApproverID = rstUsers.Fields(0).Value
                                    If Len(strApproverID) > 0 Then
                                            strApproversID = strApproversID & strApproverID & ";"
                                    End If
                            End If
    
                    End If
            Wend
        If Right(strApproversID, 1) = ";" Then
                strApproversID = Left(strApproversID, Len(strApproversID) - 1)
        End If

        End If
    End If


    GetMacroLevel1Approvers = strApproversID
    
End Function


'-------------------------------------------------------------------------------------------------------
'NAME:          GetMacroLevel2Approvers
'DESCRIPTION:   Returns a semi colon separated list of selected Approver IDs.
'--------------------------------------------------------------------------------------------------------
'
Public Function GetMacroLevel2Approvers(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    Dim rstApprovers
    Dim lngLastRevisionNumber
    Dim strApprovers
    Dim strApproversID
    Dim strSQL

    If oCensusApplication Is Nothing Then
        Exit Function
    End If

    strSQL = "SELECT Voters_ID2 FROM tblDts WHERE nID=" & lngDtsRecordID
    
    'use the approver of the last revision in the given set
    Set rstApprovers = oCensusApplication.CurrentProject.DataStores.Item(32). _
                    GetConnection(1).Execute(strSQL, , 1)

    
    If Not rstApprovers.EOF Then

        If Not IsNull(rstApprovers.Fields(0).Value) Then
            strApprovers = rstApprovers.Fields(0).Value

            ' For each Approver, lookup the nUserID and create a ' separated list of IDs
            Dim rstUsers
            Dim strApprover
            Dim strApproverID
            Dim nStart

            nStart = 1
    
            While nStart > 0
    
                    strApprover = GetToken(strApprovers, nStart)
            
                    strSQL = "SELECT nID FROM [tblUser] WHERE fDeleted = 0 AND tName =" & EscapeStr(strApprover)
    
            
                    Set rstUsers = oCensusApplication.DataStores.Item(CLng(cenUsers)).GetConnection(CLng(cenDSEnsureOpenConnection)).Execute( _
                                                        strSQL)
        
                    If Not rstUsers.EOF Then
                            If Not IsNull(rstUsers.Fields(0).Value) Then
                                    strApproverID = rstUsers.Fields(0).Value
                                    If Len(strApproverID) > 0 Then
                                            strApproversID = strApproversID & strApproverID & ";"
                                    End If
                            End If
    
                    End If
            Wend
        If Right(strApproversID, 1) = ";" Then
                strApproversID = Left(strApproversID, Len(strApproversID) - 1)
        End If

        End If
    End If


    GetMacroLevel2Approvers = strApproversID
    
End Function


'-------------------------------------------------------------------------------------------------------
'NAME:          GetMacroLevel3Approvers
'DESCRIPTION:   Returns a semi colon separated list of selected Approver IDs.
'--------------------------------------------------------------------------------------------------------
'
Public Function GetMacroLevel3Approvers(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    Dim rstApprovers
    Dim lngLastRevisionNumber
    Dim strApprovers
    Dim strApproversID
    Dim strSQL

    If oCensusApplication Is Nothing Then
        Exit Function
    End If

    strSQL = "SELECT Voters_ID3 FROM tblDts WHERE nID=" & lngDtsRecordID
    
    'use the approver of the last revision in the given set
    Set rstApprovers = oCensusApplication.CurrentProject.DataStores.Item(32). _
                    GetConnection(1).Execute(strSQL, , 1)

    
    If Not rstApprovers.EOF Then

        If Not IsNull(rstApprovers.Fields(0).Value) Then
            strApprovers = rstApprovers.Fields(0).Value

            ' For each Approver, lookup the nUserID and create a ' separated list of IDs
            Dim rstUsers
            Dim strApprover
            Dim strApproverID
            Dim nStart

            nStart = 1
    
            While nStart > 0
    
                    strApprover = GetToken(strApprovers, nStart)
            
                    strSQL = "SELECT nID FROM [tblUser] WHERE fDeleted = 0 AND tName =" & EscapeStr(strApprover)
    
            
                    Set rstUsers = oCensusApplication.DataStores.Item(CLng(cenUsers)).GetConnection(CLng(cenDSEnsureOpenConnection)).Execute( _
                                                        strSQL)
        
                    If Not rstUsers.EOF Then
                            If Not IsNull(rstUsers.Fields(0).Value) Then
                                    strApproverID = rstUsers.Fields(0).Value
                                    If Len(strApproverID) > 0 Then
                                            strApproversID = strApproversID & strApproverID & ";"
                                    End If
                            End If
    
                    End If
            Wend
        If Right(strApproversID, 1) = ";" Then
                strApproversID = Left(strApproversID, Len(strApproversID) - 1)
        End If

        End If
    End If


    GetMacroLevel3Approvers = strApproversID
    
End Function


'---------------------------------------------------------------------------
'NAME:          GetMacro90DaysFromNow
'DESCRIPTION:   Get the date 90 days from now
'---------------------------------------------------------------------------
Public Function GetMacro90DaysFromNow(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

        Dim varDate
        
        varDate = DateAdd("d", 90, Date)
        GetMacro90DaysFromNow = Year(varDate) & "/" & Month(varDate) & "/" & Day(varDate)

End Function


'---------------------------------------------------------------------------
'NAME:          GetMacroDelegateForIDs
'DESCRIPTION:   Get the IDs of the people that the user is a delegate for in a comma delimited list
'---------------------------------------------------------------------------
Public Function GetMacroDelegateForIDs(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
    
	Dim rstUsers
	Dim strSQL
    Dim strDelegateFor
    Dim strLogonUser
    Dim lngUserID
    Dim arrDelegates
    Dim lngCount
    Dim strDelegates
	
    strDelegateFor = ""
    
    strLogonUser = oCensusApplication.User
    
    lngUserID = GetIDByLogonName(oCensusApplication, strLogonUser)
    
    strSQL = "SELECT [Delegate_For] FROM [tblUser] WHERE fDeleted = 0 AND [nID] =" & lngUserID 
    
    Set rstUsers = oCensusApplication.DataStores.Item(CLng(cenUsers)).GetConnection(CLng(cenDSEnsureOpenConnection)).Execute(strSQL)
    
    If Not rstUsers.EOF Then
        If Not IsNull(rstUsers.Fields(0).Value) Then
			strDelegateFor = rstUsers.Fields(0).Value
		End If
    End If
                        
    If Not rstUsers Is Nothing Then
        rstUsers.Close
        Set rstUsers = Nothing
    End If
    
    If strDelegateFor <> "" Then
    
		arrDelegates = Split(strDelegateFor, ";")
				
		For lngCount = LBound(arrDelegates) To UBound(arrDelegates)
	
			arrDelegates(lngCount) = GetIDByFullName(oCensusApplication, arrDelegates(lngCount))
			
		Next
		
		strDelegateFor = Join(arrDelegates, ",")
		
		If strDelegateFor = "" Then
			GetMacroDelegateForIDs = "0"
		End If
		
		GetMacroDelegateForIDs = strDelegateFor
	Else
		GetMacroDelegateForIDs = "0"
	End If
    
End Function

'---------------------------------------------------------------------------
'NAME:          GetMacroUserID
'DESCRIPTION:   Get the ID of the current user.
'---------------------------------------------------------------------------
'
Public Function GetMacroUserID(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
       
	GetMacroUserID = GetIDByLogonName(oCensusApplication, oCensusApplication.User)
    
End Function

'---------------------------------------------------------------------------
'NAME:          GetMacroAssetResponsible
'DESCRIPTION:   Get the Responsible person for the Asset.
'---------------------------------------------------------------------------
Public Function GetMacroAssetResponsible(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
	Dim strSQL
	Dim rsAsset
	Dim lngResponsible
	lngResponsible = 0
	
	'get the responsible for the asset's asset type
	strSQL = "SELECT [Responsible] FROM [tblDts] WHERE [nID] = (SELECT [Level_2] FROM [tblDts] WHERE [nID] = " & lngDtsRecordID & ")"
	
	Set rsAsset = oCensusApplication.CurrentProject.GetAllDataStores.Item(32).GetConnection(CLng(cenDSEnsureOpenConnection)).Execute(strSQL)
    	    
	If Not rsAsset.EOF Then
	    If Not IsNull(rsAsset.Fields(0).Value) Then
			lngResponsible = rsAsset.Fields(0).Value
	    End If
	End If

	If Not rsAsset Is Nothing Then
	    rsAsset.Close
	    Set rsAsset = Nothing
	End If
	GetMacroAssetResponsible = lngResponsible
End Function

'---------------------------------------------------------------------------
'NAME:          GetMacroCurrentLicense
'DESCRIPTION:   Get the current license from the session
'---------------------------------------------------------------------------
Public Function GetMacroCurrentLicense(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
	Dim lngCurrentLicense
	Dim strCurrentLicense
	Dim strSQL 
	Dim rsLic
	
	
	lngCurrentLicense = GetLicenseID(oCensusApplication) 'get from session
	strCurrentLicense = ""
	
	strSQL = "SELECT [tName] FROM [License] WHERE [nID]=" & lngCurrentLicense
	
	Set rsLic = oCensusApplication.DataStores.Item(CLng(cenUsers)).GetConnection(CLng(cenDSEnsureOpenConnection)).Execute(strSQL, , 1)

	If Not rsLic.EOF Then
		If Not IsNull(rsLic.Fields(0).Value) Then
			strCurrentLicense = CStr(rsLic.Fields(0).Value)
		End If
	End If
	
	GetMacroCurrentLicense = strCurrentLicense
	
	If Not rsLic Is Nothing Then
		rsLic.Close
		Set rsLic = Nothing
	End If
End Function

'---------------------------------------------------------------------------
'NAME:          GetLicenseID
'DESCRIPTION:   Get License ID From Session
'---------------------------------------------------------------------------
Private Function GetLicenseID(oCensusApplication)

    Dim lngLicenseID
	lngLicenseID = 0
	If Not oCensusApplication.Properties Is Nothing Then
		lngLicenseID = IfNullL(oCensusApplication.Properties.Item("1"))
	End If
	
    GetLicenseID = lngLicenseID
End Function  

'---------------------------------------------------------------------------
'NAME:          GetMacroUserAccessibleCompanies
'DESCRIPTION:   Get the current user's accessible companies
'---------------------------------------------------------------------------
Public Function GetMacroUserAccessibleCompanies(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

    Dim strAccessibleCompanyNameList 

    If Not oCensusApplication Is Nothing Then
        

	strAccessibleCompanyNameList = GetUserAccessibleCompanyNames(oCensusApplication, oCensusApplication.User)

	If strAccessibleCompanyNameList <> "" Then
        	oExpression.ModifiedOperator = "IN (" & strAccessibleCompanyNameList & ")"
        	oExpression.ModifiedValue = ""
        	oExpression.FieldID = -19740203
	Else
		GetMacroUserAccessibleCompanies = 0	
	End if
    End If

End Function

Private Function GetUserAccessibleCompanyNames(oCensusApplication, strLogonName)

    Dim rstCompany
    Dim strSQL
    Dim strAccessibleCompanyNames
    
    Const MC_DELIM = ";"
    Const SQL_LIKE_DELIM = ","
    Const FLD_ACCESSIBLE_COMPANIES = "Accessible_Companies"
    strAccessibleCompanyNames = ""
                        
    strSQL = "SELECT [" & FLD_ACCESSIBLE_COMPANIES & "] FROM [" & TABLE_USER & _
                        "] WHERE [" & FLD_USER_NAME & "] =" & EscapeStr(strLogonName)

    Set rstCompany = oCensusApplication.DataStores.Item(CLng(cenUsers)).GetConnection( _
                        CLng(cenDSEnsureOpenConnection)).Execute(strSQL)

    If Not rstCompany.EOF Then
        If Not IsNull(rstCompany.Fields(FLD_ACCESSIBLE_COMPANIES).Value) Then
            strAccessibleCompanyNames = CStr(rstCompany.Fields(FLD_ACCESSIBLE_COMPANIES).Value)
        End If
    End If
    
    If Not rstCompany Is Nothing Then
        rstCompany.Close
        Set rstCompany = Nothing
    End If


    If strAccessibleCompanyNames <> "" Then
	'convert to the names, with a comma between each to be used in a like condition

	strAccessibleCompanyNames = Replace(strAccessibleCompanyNames, "'", "''")
	strAccessibleCompanyNames = Replace(strAccessibleCompanyNames, MC_DELIM, "'" & SQL_LIKE_DELIM & "'")
	strAccessibleCompanyNames = "'" & strAccessibleCompanyNames & "'"

    End If

    GetUserAccessibleCompanyNames = strAccessibleCompanyNames

End Function


'---------------------------------------------------------------------------
'NAME:          GetMacroCurrentAsset
'DESCRIPTION:   Get the current asset from the session
'---------------------------------------------------------------------------
Public Function GetMacroCurrentAsset(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
	Dim lngCurrentAsset
	
	lngCurrentAsset = GetAssetID(oCensusApplication) 'get from session
	
	'use the field name (for the nID) instead of changing to the tName
	
	oExpression.ModifiedFieldName = oExpression.FieldName
	oExpression.FieldID = -19740203
	
	LogMsg "GetMacroCurrentAsset lngCurrentAsset:" & lngCurrentAsset
	GetMacroCurrentAsset = CStr(lngCurrentAsset)

End Function

'---------------------------------------------------------------------------
'NAME:          GetAssetID
'DESCRIPTION:   Get Asset ID From Session
'---------------------------------------------------------------------------
Private Function GetAssetID(oCensusApplication)
	On Error Resume Next
    Dim lngAssetID
	lngAssetID = 0
	If Not oCensusApplication.Properties Is Nothing Then
		lngAssetID = IfNullL(oCensusApplication.Properties.Item("2"))
	End If
	
    GetAssetID = lngAssetID
End Function 
 
' Used in WHERE conditions like 
'    Allocated to Department = <Is Accessible Department>
Public Function IsAccessibleDepartment(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

	Dim strLogonUser
	Dim lngUserID
	
	If (oCensusApplication.Features.Exists(FEATURE_ACCESS_ALL_DEPARTMENTS)) Then
	
		' Create an expression like 1=1
		oExpression.ModifiedFieldName=1

		IsAccessibleDepartment=1

		' Set the ModifiedValue so it's not formatted after by the query engine
		oExpression.ModifiedValue= IsAccessibleDepartment 

	Else

		' Create an expression like Allocated to Department='Finance'
		strLogonUser = oCensusApplication.User
		lngUserID = GetIDByLogonName(oCensusApplication, strLogonUser)

		IsAccessibleDepartment = GetDeptName( GetDepartment( oCensusApplication, lngUserID), oCensusApplication)

	End If



	LogMsg "IsAccessibleDepartment  :" & IsAccessibleDepartment 	

	
End Function



' Used in WHERE conditions like 
'    [Current Location] = <Is Accessible Location>
Public Function IsAccessibleLocation(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

	Dim strLogonUser
	Dim lngUserID
	
	LogMsg "IsAccessibleLocation for Rec:" & lngDtsRecordID

	If (oCensusApplication.Features.Exists(FEATURE_ACCESS_ALL_LOCATIONS)) Then
	
		' Create an expression like 1=1
		oExpression.ModifiedFieldName=1

		IsAccessibleLocation = 1

	Else

		' Create an expression like [Current Location] ='Finance' but getting all the accessible locations in a subquery
		strLogonUser = oCensusApplication.User
		LogMsg "IsAccessibleLocation oExpression.FieldID:" & oExpression.FieldID

		lngUserID = GetIDByLogonName(oCensusApplication, strLogonUser)

		LogMsg "IsAccessibleLocation Userid:" & lngUserID 

		' [Current Location] IN (subquery to get the names of the accessible locations)
		oExpression.ModifiedOperator=" IN "
		IsAccessibleLocation = GetMyLocationsSubquery( oCensusApplication, lngDtsRecordID, oExpression)


	End If

	' Set the ModifiedValue so it's not formatted after by the query engine
	oExpression.ModifiedValue=IsAccessibleLocation


	LogMsg "IsAccessibleLocation :" & IsAccessibleLocation	

	
End Function

' Used in WHERE conditions like 
'    Company = <Is Accessible Company>
Public Function IsAccessibleCompany(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

	Dim strLogonUser
	Dim lngUserID
	
	If (oCensusApplication.Features.Exists(FEATURE_ACCESS_ALL_COMPANIES)) Then
	
		' Create an expression like 1=1
		oExpression.ModifiedFieldName=1

		IsAccessibleCompany = 1

		' Set the ModifiedValue so it's not formatted after by the query engine
		oExpression.ModifiedValue=IsAccessibleCompany 


	Else

		' Create an expression like Company='ACME'
		strLogonUser = oCensusApplication.User
		lngUserID = GetIDByLogonName(oCensusApplication, strLogonUser)

		IsAccessibleCompany = GetUserCompany(oCensusApplication, strLogonUser)
	

	End If

	LogMsg "IsAccessibleCompany :" & IsAccessibleCompany 	

	
End Function


Private Function GetMyLocationsSubquery(oCensusApplication, lngDtsRecordID, oExpression)

	' Query locations and get a list of all the locations that are inside my location, also including my location
	' WHERE Level_<Item_Level of Employee Location > = <nID of Employee Location> OR nID = <nID of Employee Location>
	Dim lngLocID
	Dim lngUserID
	Dim strSQL
	Dim lngLocationLevel
	
	' Get the ID of the logged-in user
	lngUserID = GetIDByLogonName(oCensusApplication, oCensusApplication.User )

	LogMsg "GetMyLocationsSubquery Userid:" & lngUserID 
	
	LogMsg "The current user ID is:" &  lngUserID 
	
	' Get the ID of the location of this user
	lngLocID = GetUserLocationID(oCensusApplication, lngUserID)

	LogMsg "GetMyLocationsSubquery Locid:" & lngLocID 

	LogMsg "Location ID is:" &  lngLocID 
	
	' Get the level of that location
	lngLocationLevel = GetLocationLevel(oCensusApplication, lngLocID )
	
	LogMsg "Location Level is:" &  lngLocationLevel 
		
	' Create a subquery with a WHERE like:
	' WHERE Level_<Item_Level of Employee Location > = <nID of Employee Location> OR nID = <nID of Employee Location>	
	GetMyLocationsSubquery="(SELECT tBriefDescription FROM " & ROLES_MAIN_TABLE & " WHERE nID=" & lngLocID & " OR [Level_" & lngLocationLevel & "]=" & lngLocID & " )"	


End Function

' Get the ID of the location of the current user
Private Function GetUserLocationID(oCensusApplication, lngUserID)

	' Get the ID of the location of the user
	GetUserLocationID=GetFieldValueFromUsers (oCensusApplication, FLD_USER_LOCATION, lngUserID)
	
End Function


Private Function GetLocationLevel( oCensusApplication, lngLocID )

	GetLocationLevel=GetFieldValueFromRoles(oCensusApplication, FLD_ITEM_LEVEL, lngLocID )

End Function

Private Function GetFieldValueFromRoles(oCensusApplication, sFldName, lngRoleID)

    Dim rstFldVal
    Dim strSQL
    Dim strName
    
    strName = ""
                        
    strSQL = "SELECT [" & sFldName & "] FROM " & ROLES_MAIN_TABLE & " WHERE nID = " & lngRoleID
        
    Set rstFldVal = oCensusApplication.CurrentProject.DataStores.Item(CLng(cenDat)).GetConnection(CLng(cenDSEnsureOpenConnection)).Execute(strSQL)
    
    If Not rstFldVal.EOF Then
        If Not IsNull(rstFldVal.Fields(0).Value) Then
			strName = rstFldVal.Fields(0).Value
        End If
    End If
                        
    If Not rstFldVal Is Nothing Then
        rstFldVal.Close
        Set rstFldVal = Nothing
    End If
    
    GetFieldValueFromRoles = strName
    
End Function

'---------------------------------------------------------------------------
'NAME:          GetMacro7DaysFromNow
'DESCRIPTION:   Get the date 7 days from now
'---------------------------------------------------------------------------
Public Function GetMacro7DaysFromNow(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

        Dim varDate
        
        varDate = DateAdd("d", 7, Date)
        GetMacro7DaysFromNow = Year(varDate) & "/" & Month(varDate) & "/" & Day(varDate)

End Function

'---------------------------------------------------------------------------
'NAME:          GetMacro30DaysFromNow
'DESCRIPTION:   Get the date 30 days from now
'---------------------------------------------------------------------------
Public Function GetMacro30DaysFromNow(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

        Dim varDate
        
        varDate = DateAdd("d", 30, Date)
        GetMacro30DaysFromNow = Year(varDate) & "/" & Month(varDate) & "/" & Day(varDate)

End Function

'---------------------------------------------------------------------------
'NAME:          GetSessionValID
'DESCRIPTION:   Get a value ID From the Session
'---------------------------------------------------------------------------
Private Function GetSessionValID(oCensusApplication, sValueName)

    Dim lngValID
    
	lngValID = 0
	
	' For it to be a string
	sValueName=CStr(sValueName)
	
	If Not oCensusApplication.Properties Is Nothing Then
		
		If oCensusApplication.Properties.Exists(sValueName) Then
		
			LogMsg "Getting the property with name:" & sValueName

			lngValID = oCensusApplication.Properties.Item(sValueName)
			
		End If
		
	End If
	
    GetSessionValID = lngValID 
End Function 


'---------------------------------------------------------------------------
'NAME:          GetMacroSessionParam
'DESCRIPTION:   Get a generic parameter value from the session. The name of the param is specified by the field name of the expression
'---------------------------------------------------------------------------
Public Function GetMacroSessionParam(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
	Dim lngVal
	

	lngVal = GetSessionValID(oCensusApplication, CStr(oExpression.FieldID)) 'get from session

	LogMsg "GetMacroSessionParam. Getting the value from the session for property/field: " & oExpression.FieldID & ". Got value: " & lngVal 
	
	Dim oDtsFld
	Dim strVal
	
	Set oDtsFld = GetDtsField(oCensusApplication, "", oExpression.FieldID)

	If Not oDtsFld Is Nothing Then

		If Not oDtsFld.ChoiceValues Is Nothing Then

			strVal = oDtsFld.ChoiceValues.GetNameFromID(Clng(lngVal))
		
		End If
			
		LogMsg "GetMacroSessionParam. Converted value is:" & strVal
	
	End If
	
	GetMacroSessionParam = CStr(strVal)

End Function

'---------------------------------------------------------------------------
'NAME:          GetMacroSessionParamOpt
'DESCRIPTION:   Get a generic parameter value from the session. 
' 				The name of the param is specified by the field name of the expression
'				If the session parameter is not set or empty, the condition returns TRUE
'---------------------------------------------------------------------------
Public Function GetMacroSessionParamOpt(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

	Dim strVal
	
	strVal = GetMacroSessionParam(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)

	If Len(strVal)>0 Then

	Else
	
		' Use the field name (for the nID) instead of changing to the tName
		oExpression.ModifiedFieldName = "1"
		strVal = "1"
		oExpression.ModifiedValue= strVal
		
	
	End If
	
	GetMacroSessionParamOpt = CStr(strVal)

End Function

'---------------------------------------------------------------------------
'NAME:          GetMacroLinkedEmployee
'DESCRIPTION:   Get the current employee from the session
'---------------------------------------------------------------------------
Public Function GetMacroLinkedEmployee(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
	Dim lngCurrentEmployee
	
	lngCurrentEmployee = GetLinkedEmployeeID(oCensusApplication) 'get from session
	
	GetMacroLinkedEmployee = GetFullNameByID(oCensusApplication, lngCurrentEmployee)

End Function

'---------------------------------------------------------------------------
'NAME:          GetLinkedEmployeeID
'DESCRIPTION:   Get Employee ID From Session
'---------------------------------------------------------------------------
Private Function GetLinkedEmployeeID(oCensusApplication)

    Dim lngEmployeeID
	lngEmployeeID = 0
	If Not oCensusApplication.Properties Is Nothing Then
		lngEmployeeID = oCensusApplication.Properties.Item("3")
	End If
	
    GetLinkedEmployeeID = lngEmployeeID
End Function 

'---------------------------------------------------------------------------
'NAME:          GetMacroAssetTypeAbbrev
'DESCRIPTION:   Get the abbreviation for the current asset type
'---------------------------------------------------------------------------
Public Function GetMacroAssetTypeAbbrev(oCensusApplication, lngDtsRecordID, oExpression, recTicketData)
	Dim lngCurrentAssetType
	Dim strAbbrev
	
	strAbbrev = ""
	
	lngCurrentAssetType = IfNullL(recTicketData.Fields("1094").Value)
		
	If lngCurrentAssetType > 0 Then
	
		strAbbrev = IfNull(GetDtsFieldValue(oCensusApplication, CLng(lngCurrentAssetType), "Abbreviation", 1209, False))
		
		If strAbbrev = "" Then
			strAbbrev = IfNull(GetDtsFieldValue(oCensusApplication, CLng(lngCurrentAssetType), "tBriefDescription", 30, False))
		End If
	End If
	
	GetMacroAssetTypeAbbrev = strAbbrev

End Function

'---------------------------------------------------------------------------
'NAME:          GetMacroPurchasedDate
'DESCRIPTION:   Get the purchased date of the asset in the proper format
'---------------------------------------------------------------------------
Public Function GetMacroPurchasedDate(oCensusApplication, lngDtsRecordID, oExpression, recTicketData)
	Dim lngCurrentAssetType
	Dim strPurchasedDate
	Dim strYear
	Dim strMonth
	Dim strDay
	
	strPurchasedDate = ""
	
	lngCurrentAssetType = IfNullL(recTicketData.Fields("1094").Value)
	If lngCurrentAssetType > 0 Then
	
		strPurchasedDate = IfNull(recTicketData.Fields("916").Value)
		If strPurchasedDate <> "" Then
			strYear = DatePart("yyyy", CDate(strPurchasedDate))
			strMonth = DatePart("m", CDate(strPurchasedDate))
			strDay = DatePart("d", CDate(strPurchasedDate))
			
			If strMonth < 10 Then
				strMonth = "0" & strMonth
			End If
			
			If strDay < 10 Then
				strDay = "0" & strDay
			End If
			
			strPurchasedDate = Right(CStr(strYear), 2) & _
								strMonth & _
								strDay
		End If
	End If
	
	GetMacroPurchasedDate = strPurchasedDate

End Function

'---------------------------------------------------------------------------
'NAME:          GetMacroAssetTypeCount
'DESCRIPTION:   Get the next available number for the current asset type
' and increase it by 1
'---------------------------------------------------------------------------
Public Function GetMacroAssetTypeCount(oCensusApplication, lngDtsRecordID, oExpression, recTicketData)
	Dim lngCurrentAssetType
	Dim lngATCount
	Dim strATCount
	
	lngATCount = ""
	
	lngCurrentAssetType = IfNullL(recTicketData.Fields("1094").Value)
	
	If lngCurrentAssetType > 0 Then
	
		lngATCount = IfNullL(GetDtsFieldValue(oCensusApplication, CLng(lngCurrentAssetType), "Asset_Type_Count", 1210, False))
		
		lngATCount = lngATCount + 1
		
		oCensusApplication.CurrentProject.Record.Update "Asset_Type_Count=" & lngATCount, CLng(lngCurrentAssetType)
		
		If lngATCount < 10 Then
			strATCount = "0000" & lngATCount 
		ElseIf lngATCount < 100 Then
			strATCount = "000" & lngATCount 
		ElseIf lngATCount < 1000 Then
			strATCount = "00" & lngATCount 
		ElseIf lngATCount < 10000 Then
			strATCount = "0" & lngATCount 
		Else
			strATCount = lngATCount
		End If
		
	End If
	
	GetMacroAssetTypeCount = strATCount

End Function

Private Function IfNull(varField)
On Error Resume Next
    If IsNull(varField) Or IsEmpty(varField) Then
        IfNull = ""
    Else
        IfNull = CStr(varField)
    End If

End Function

Private Function IfNullL(varField)
On Error Resume Next
    If IsNull(varField) Or IsEmpty(varField) Then
        IfNullL = 0
    Else
        IfNullL = CLng(varField)
    End If

End Function



Public Function GetContactDepartmentManager(oCensusApplication, lngRecord , _
	oExpression, recRevisionHistory)
    
	' Get the contact of the record
	Dim iContactID
	Dim lngDept
	Dim lngMgr

	iContactID = GetMacroOriginator( oCensusApplication, lngRecord , oExpression, recRevisionHistory)

	if iContactID > 0 Then
		lngDept = GetDepartment(oCensusApplication, iContactID)
	
	End If

	If lngDept > 0 Then

		GetContactDepartmentManager = GetResponsibleOfDep (oCensusApplication, lngDept)
	End If


End Function


