Imports Act.Framework.ComponentModel
Imports Act.Shared.Collections

Public Class Form1
    Dim framework As Act.Framework.ActFramework = New Act.Framework.ActFramework
    Dim ACTUserName As String = "Chris Huffman"
    Dim ACTUserPassword As String = ""
    Dim PADFile As String = "C:\Users\Public\Documents\ACT\ACT Data\Databases\ACT2012Demo.PAD"

    Private Sub rbShowDataReader_Click(sender As System.Object, e As System.EventArgs) Handles rbShowDataReader.Click
        Call ShowExplanation("rbShowDataReader")
    End Sub

    Private Sub rbContactList_Click(sender As System.Object, e As System.EventArgs) Handles rbContactList.Click
        Call ShowExplanation("rbContactList")
    End Sub

    Private Sub rbLookup_Click(sender As System.Object, e As System.EventArgs) Handles rbLookup.Click
        Call ShowExplanation("rbLookup")
    End Sub

    Private Sub rbPickList_Click(sender As System.Object, e As System.EventArgs) Handles rbPickList.Click
        Call ShowExplanation("rbPickList")
    End Sub

    Private Sub ShowExplanation(rbOption As String)
        'Clear any previous contents
        Me.lblExplanation.Text = String.Empty
        Me.DataGridView1.DataSource = Nothing

        Select Case rbOption
            Case "rbShowDataReader"
                Me.lblExplanation.Text =
                    "Displays the TBL_ATTACHMENT table from the ACT2012Demo database using the ACTDataReader SQL login with " &
                    "password ""password"". Assumes this has been created in the ACT7 database instance after the database was installed. " &
                    "The account that installed the ACT7 instance is automatically granted sysadmin (sa) rights."
            Case "rbContactList"
                Me.lblExplanation.Text =
                    "Displays the contact list available for view by Chris Huffman in ACT2012Demo, the administrator of the database."
            Case "rbLookup"
                Me.lblExplanation.Text =
                    "Displays the contact list available for view by Chris Huffman in ACT2012 Demo with an address in Los Angeles, CA. " &
                    "Demonstrates a lookup for State equal CA which is subsequently narrowed to the City field equals Los Angeles, " &
                    "sorted by Last Name ascending then First Name ascending. For those contacts in the resulting lookup, changes field " &
                    "USER10 to the current data and time if it is blank and Chris Huffman is the Record Manager for that contact."
            Case "rbPickList"
                Me.lblExplanation.Text =
                    "Displays the picklist contents for the City field."
        End Select
    End Sub

    Public Class ContactIdentity
        Public Property CompanyName As String
        Public Property FirstName As String
        Public Property LastName As String
        Public Property Phone As String
        Public Property MobilePhone As String
        Public Property City As String
        Public Property State As String
    End Class

    Private Sub PerformSelectedAction(rbOption As String)

        Select Case rbOption
            Case "rbContactList"
                framework.LogOn(PADFile, ACTUserName, ACTUserPassword)
                Dim companyField = framework.Contacts.GetFieldDescriptor(name:="TBL_CONTACT.COMPANYNAME", isReal:=True)
                Dim sortByCompanyNameAscending() As SortCriteria = {New SortCriteria(companyField, System.ComponentModel.ListSortDirection.Ascending)}
                Dim contacts = framework.Contacts.GetContacts(sortByCompanyNameAscending)

                'Build the list for display, log off, display it
                Dim contactList As New List(Of ContactIdentity)
                For i = 0 To contacts.Count - 1
                    Dim contactInfo = New ContactIdentity With {
                        .CompanyName = contacts(i).Company,
                        .FirstName = contacts(i).FirstName,
                        .LastName = contacts(i).LastName,
                        .Phone = contacts(i).Fields.Item(name:="TBL_CONTACT.BUSINESS_PHONE", isReal:=True),
                        .MobilePhone = contacts(i).Fields("MOBILE_PHONE", Act.Framework.MutableEntities.FieldNameType.Alias)
                        }
                    contactList.Add(contactInfo)
                Next
                framework.LogOff()
                Me.DataGridView1.DataSource = contactList
            Case "rbShowDataReader"
                Dim dataContext As New ACT2012Demo(GetConnectionString)
                Dim attachments = (From item In dataContext.TBL_ATTACHMENT Select item Order By item.DISPLAYNAME).ToList()
                Me.DataGridView1.DataSource = attachments
            Case "rbLookup"
                framework.LogOn(PADFile, ACTUserName, ACTUserPassword)
                Dim columnState As Act.Framework.Lookups.CriteriaColumn =
                    framework.Lookups.GetCriteriaColumn("TBL_CONTACT", "BUSINESS_STATE", isReal:=True)
                Dim equalOperator As Act.Framework.Lookups.OperatorEnum = Act.Framework.Lookups.OperatorEnum.EqualTo
                Dim valueState = "CA"
                Dim criteriaState As Act.Framework.Lookups.Criteria() = New Act.Framework.Lookups.Criteria() {
                        New Act.Framework.Lookups.Criteria(
                            Act.Framework.Lookups.LogicalOperator.End, leftParen:=CByte(0), rightParen:=CByte(0),
                            col:=columnState, opEnum:=equalOperator, Val:=valueState)}
                Dim lookupState As Act.Framework.Lookups.ContactLookup =
                    framework.Lookups.LookupContactsReplace(criteriaState, includePrivate:=True, includeUsers:=True)
                Dim contactsState = lookupState.GetContacts(SortCriteria:=Nothing)

                'We have the list of Contacts for CA, now narrow it those living in Los Angeles

                'You can then narrow the lookup by city, sorted by Last Name as follows:
                Dim columnCity = framework.Lookups.GetCriteriaColumn("TBL_CONTACT", "BUSINESS_CITY", isReal:=True)
                Dim valueCity = "Los Angeles"
                Dim lookupCity = framework.Lookups.LookupContactsNarrow(
                    contactsState, valueCity, Act.Framework.Lookups.OperatorEnum.EqualTo, columnCity)
                Dim lastNameField = framework.Contacts.GetFieldDescriptor(name:="TBL_CONTACT.LASTNAME", isReal:=True)
                Dim firstNameField = framework.Contacts.GetFieldDescriptor(name:="TBL_CONTACT.FIRSTNAME", isReal:=True)
                Dim User10Field = framework.Contacts.GetFieldDescriptor(name:="TBL_CONTACT.USER10", isReal:=True)
                Dim sortByLastFirstNameAscending() As SortCriteria = {
                    New SortCriteria(lastNameField, System.ComponentModel.ListSortDirection.Ascending),
                    New SortCriteria(firstNameField, System.ComponentModel.ListSortDirection.Ascending)}
                Dim contactsStateCity = lookupCity.GetContacts(sortByLastFirstNameAscending)


                Dim displayList As New List(Of ContactIdentity)
                For i = 0 To contactsStateCity.Count - 1
                    displayList.Add(
                        New ContactIdentity With {
                            .CompanyName = contactsStateCity(i).Company,
                            .LastName = contactsStateCity.Item(i).LastName,
                            .FirstName = contactsStateCity.Item(i).FirstName,
                            .City = contactsStateCity(i).Fields("CITY",
                                                                Act.Framework.MutableEntities.FieldNameType.Alias),
                            .State = contactsStateCity(i).Fields("STATE",
                                                                 Act.Framework.MutableEntities.FieldNameType.Alias),
                        .Phone = contactsStateCity(i).Fields("PHONE",
                                                             Act.Framework.MutableEntities.FieldNameType.Alias),
                        .MobilePhone = contactsStateCity(i).Fields("MOBILE_PHONE",
                                                                   Act.Framework.MutableEntities.FieldNameType.Alias)
                        })

                    'For illustration purposes, change the User10 field to the current date/time if blank
                    If contactsStateCity(i).Fields("USER10", Act.Framework.MutableEntities.FieldNameType.Alias) =
                        String.Empty AndAlso contactsStateCity(i).IsRequestingUserRecordManager Then
                        User10Field.SetValue(contactsStateCity(i), Now.ToString)
                        contactsStateCity(i).Update()
                    End If
                Next
                Me.DataGridView1.DataSource = displayList
                framework.LogOff()
            Case "rbPickList"
                framework.LogOn(PADFile, ACTUserName, ACTUserPassword)
                Dim cityField = framework.Contacts.GetFieldDescriptor(name:="TBL_CONTACT.BUSINESS_CITY", isReal:=True)
                Dim pickList = framework.PickLists.GetPickList(cityField)
                Me.DataGridView1.DataSource = pickList.Items
                framework.LogOff()
            Case Else
                Me.lblExplanation.Text = "Selection Not Implemented"
        End Select
    End Sub

    Private Function GetConnectionString() As String
        Return "Data Source=(local)\ACT7; Initial Catalog=ACT2012Demo; Persist Security Info=True;" &
                "User ID=ACTDataReader; Password=password;"
    End Function

    'Using LINQ-to-SQL for ACT! database access:
    'C:\Program Files\Microsoft Visual Studio 10.0\VC>
    '    sqlmetal /server:(local)\ACT7
    '    /database:ACT2012Demo /views /functions /sprocs /code:C:\Article\ACT2012DemoDB.vb
    '    /provider:SQL2008>C:\Article\ACT2012Demo.txt

    Private Sub btnPerformSelectedAction_Click(sender As System.Object, e As System.EventArgs) Handles btnPerformSelectedAction.Click
        If Me.rbShowDataReader.Checked Then PerformSelectedAction("rbShowDataReader")
        If Me.rbContactList.Checked Then PerformSelectedAction("rbContactList")
        If Me.rbLookup.Checked Then PerformSelectedAction("rbLookup")
        If Me.rbPickList.Checked Then PerformSelectedAction("rbPickList")
    End Sub

End Class
