views:

69

answers:

0

I'm running into an issue writting this Lamda expression in VB. a1 is what is giving me syntax errors and a2 seems to be ok.

In addition, I have a class called UserInfo, how can I pass values to the class so that I can use when building my datatable?

Code that I'm trying to add:

Dim a1 = From rows In db.tPDMLinkUsages _
    Where (rows.ACCESSDATE >= DateTime.Today.AddDays(-90)) _
         Select New With _
         { _
            .ORGANIZATION = db.vWindchillUsers.Single(Function(v) v.USERNAME = rows.USERNAME).ORGANIZATION, _
            .ACCESSDATE = rows.ACCESSDATE _
         }
    Dim a2 = From rows In a1 _ 
    Group rows By u.ORGANIZATION Into uGroup _ 
    Select New With _
        { _
        .ORGANIZATION = uGroup.Key, _
        .AVERAGEDAILY = uGroup.Count() / 90.0, _
        .HIGHWATER= uGroup.GroupBy(Function(v) v.ACCESSDATE).Max(Function(g) g.Count())

Code for UserInfo.vb class file:

Imports Microsoft.VisualBasic

Public Class UserInfo
    Private _OrganizationName As String
    Private _UniqueHits As Integer
    Private _EMail As String
    Private _FullName As String
    Private _UserName As String
Public Property OrganizationName() As String
    Get
        Return _OrganizationName
    End Get
    Set(ByVal value As String)
        _OrganizationName = value
    End Set
End Property

Public Property UniqueHits() As Integer
    Get
        Return _UniqueHits
    End Get
    Set(ByVal value As Integer)
        _UniqueHits = value
    End Set
End Property

Public Property UserName() As String
    Get
        Return _UserName
    End Get
    Set(ByVal value As String)
        _UserName = value
    End Set
End Property

Public Property FullName() As String
    Get
        Return _FullName
    End Get
    Set(ByVal value As String)
        If value.Contains("@itt.com") Then
            Dim temp As String = value
            temp = temp.Replace("@itt.com", "")
            temp = temp.Replace(".", " ")
            temp = temp.ToUpperInvariant()

            _FullName = temp
        Else
            _FullName = String.Empty
        End If
    End Set
End Property

Public Property EMail() As String
    Get
        Return _EMail
    End Get
    Set(ByVal value As String)
        If value.Contains("@itt.com") Then
            _EMail = value
        Else
            _EMail = String.Empty
        End If
    End Set
End Property

End Class

Code that has worked for pulling usage for last 30 days. What I'd like to do is pull AVERAGE daily usage and daily high water mark for last 90 days.

       ' Build and execute all usage queries based off date.
    Dim dateNow As DateTime = DateTime.Today.Date
    Dim dateToday As Date = dateNow
    Dim date1Day As Date = dateNow.AddDays(-1)
    Dim date30Day As Date = dateNow.AddDays(-30)
    Dim date60Day As Date = dateNow.AddDays(-60)
    Dim date90Day As Date = dateNow.AddDays(-90)

    Dim db As New PDMLinkUsageDataContext()

    Dim last0To30Days = From rows In db.tPDMLinkUsages _
                            Join users In db.vWindchillUsers _
                            On rows.USERNAME Equals users.NAME _
                            Where rows.ACCESSDATE > date30Day _
                            And rows.ACCESSDATE <= dateToday _
                            And users.ORGANIZATION <> Nothing _
                            Select ORGANIZATION = users.ORGANIZATION, users.NAME _
                            Distinct _
                        Group By ORGANIZATION _
                        Into UNIQUEUSERS = Count() _
                        Select New UserInfo _
                            With { _
                                    .OrganizationName = ORGANIZATION, _
                                    .UniqueHits = UNIQUEUSERS _
                                 }