    Imports System
    Imports System.Data
    Imports System.Configuration
    Imports System.Text
    Imports ASPNET.StarterKit.TimeTracker.DataAccessLayer

    Namespace ASPNET.StarterKit.TimeTracker.BusinessLogicLayer

        '*********************************************************************
        '
        ' Project Class
        '
        ' The Project Class is used to represent a project along with its members and categories.
        '
        '*********************************************************************

        Public Class Project
            Private _categories As CategoriesCollection
            Private _description As String
            Private _estCompletionDate As DateTime
            Private _estDuration As Decimal
            Private _managerUserID As Integer
            Private _managerUserName As String
            Private _members As UsersCollection
            Private _name As String
            Private _projectID As Integer

            Public Sub New()
            End Sub 'New

            Public Sub New(ByVal projectID As Integer)
                _projectID = projectID
            End Sub 'New

            Public Sub New(ByVal projectID As Integer, ByVal name As String, ByVal description As String, ByVal managerUserID As Integer, ByVal estCompletionDate As DateTime, ByVal estDuration As Decimal)
                _projectID = projectID
                _name = name
                _description = description
                _managerUserID = managerUserID
                _estCompletionDate = estCompletionDate
                _estDuration = estDuration
            End Sub 'New

            Public Property Categories() As CategoriesCollection
                Get
                    Return _categories
                End Get
                Set(ByVal Value As CategoriesCollection)
                    _categories = Value
                End Set
            End Property

            Public Property Description() As String
                Get
                    Return _description
                End Get
                Set(ByVal Value As String)
                    _description = Value
                End Set
            End Property

            Public Property EstCompletionDate() As DateTime
                Get
                    Return _estCompletionDate
                End Get
                Set(ByVal Value As DateTime)
                    _estCompletionDate = Value
                End Set
            End Property

            Public Property EstDuration() As Decimal
                Get
                    Return _estDuration
                End Get
                Set(ByVal Value As Decimal)
                    _estDuration = Value
                End Set
            End Property

            Public Property ManagerUserID() As Integer
                Get
                    Return _managerUserID
                End Get
                Set(ByVal Value As Integer)
                    _managerUserID = Value
                End Set
            End Property

            Public Property ManagerUserName() As String
                Get
                    Return _managerUserName
                End Get
                Set(ByVal Value As String)
                    _managerUserName = Value
                End Set
            End Property

            Public Property Members() As UsersCollection
                Get
                    Return _members
                End Get
                Set(ByVal Value As UsersCollection)
                    _members = Value
                End Set
            End Property

            Public Property Name() As String
                Get
                    Return _name
                End Get
                Set(ByVal Value As String)
                    _name = Value
                End Set
            End Property

            Public Property ProjectID() As Integer
                Get
                    Return _projectID
                End Get
                Set(ByVal Value As Integer)
                    _projectID = Value
                End Set
            End Property

            '*********************************************************************
            '
            ' Retrieves the list of all the projects
            '
            '*********************************************************************

            Public Overloads Shared Function GetProjects() As ProjectsCollection
                Dim ds As DataSet = SqlHelper.ExecuteDataset(ConfigurationSettings.AppSettings(Web.Global.CfgKeyConnString), CommandType.StoredProcedure, "TT_ListAllProjects")

                ' Separate Data into a Collection of projects
                Dim projects As New ProjectsCollection()
                Dim r As DataRow
                For Each r In ds.Tables(0).Rows
                    Dim prj As New Project()
                    prj.ProjectID = Convert.ToInt32(r("ProjectID"))
                    prj.Name = r("ProjectName").ToString()
                    prj.Description = r("Description").ToString()
                    prj.ManagerUserID = Convert.ToInt32(r("ManagerUserID"))
                    prj.EstCompletionDate = Convert.ToDateTime(r("EstCompletionDate"))
                    prj.EstDuration = Convert.ToDecimal(r("EstDuration"))
                    projects.Add(prj)
                Next r
                Return projects
            End Function 'GetProjects

            '*********************************************************************
            '
            ' Retrieves a list of projects based on the user's role
            '
            '*********************************************************************

            Public Overloads Shared Function GetProjects(ByVal userID As Integer, ByVal role As String) As ProjectsCollection
                Dim firstName As String = String.Empty
                Dim lastName As String = String.Empty

                Dim ds As DataSet = SqlHelper.ExecuteDataset(ConfigurationSettings.AppSettings(Web.Global.CfgKeyConnString), "TT_ListProjects", userID, Convert.ToInt32(role))

                Dim projects As New ProjectsCollection()
                Dim r As DataRow
                For Each r In ds.Tables(0).Rows
                    Dim prj As New Project()
                    prj.ProjectID = Convert.ToInt32(r("ProjectID"))
                    prj.Name = r("ProjectName").ToString()
                    prj.Description = r("Description").ToString()
                    prj.ManagerUserID = Convert.ToInt32(r("ManagerUserID"))
                    prj.ManagerUserName = TTUser.GetDisplayName(Convert.ToString(r("UserName")), firstName, lastName)
                    prj.EstCompletionDate = Convert.ToDateTime(r("EstCompletionDate"))
                    prj.EstDuration = Convert.ToDecimal(r("EstDuration"))
                    projects.Add(prj)
                Next r
                Return projects
            End Function 'GetProjects

            '*********************************************************************
            '
            ' Retrieves a list of projects based on project membership
            '
            '*********************************************************************

            Public Overloads Shared Function GetProjects(ByVal queryUserID As Integer, ByVal userID As Integer) As ProjectsCollection
                Dim projects As New ProjectsCollection()
                Dim ds As DataSet = SqlHelper.ExecuteDataset(ConfigurationSettings.AppSettings(Web.Global.CfgKeyConnString), "TT_ListProjectsWithMembership", queryUserID, userID)

                Dim r As DataRow
                For Each r In ds.Tables(0).Rows
                    Dim prj As New Project()
                    prj.ProjectID = Convert.ToInt32(r("ProjectID"))
                    prj.Name = r("Name").ToString()
                    prj.Description = r("Description").ToString()
                    prj.ManagerUserID = Convert.ToInt32(r("ManagerUserID"))
                    prj.EstCompletionDate = Convert.ToDateTime(r("EstCompletionDate"))
                    prj.EstDuration = Convert.ToDecimal(r("EstDuration"))
                    projects.Add(prj)
                Next r
                Return projects
            End Function 'GetProjects

            '*********************************************************************
            '
            ' The GetCategories method retrieves the list of categories for the project associated with
            ' the entered project id.  This method returns a collection of Categories objects.
            '
            '*********************************************************************

            Public Shared Function GetCategories(ByVal projectID As Integer) As CategoriesCollection
                Dim ds As DataSet = SqlHelper.ExecuteDataset(ConfigurationSettings.AppSettings(Web.Global.CfgKeyConnString), "TT_ListCategories", projectID)

                Dim categories As New CategoriesCollection()
                Dim r As DataRow
                For Each r In ds.Tables(0).Rows
                    Dim cat As New Category()
                    cat.CategoryID = Convert.ToInt32(r("CategoryID"))
                    cat.ProjectID = projectID
                    cat.Name = r("Name").ToString()
                    cat.Abbreviation = r("Abbreviation").ToString()
                    cat.EstDuration = Convert.ToDecimal(r("EstDuration"))
                    categories.Add(cat)
                Next r
                Return categories
            End Function 'GetCategories

            '*********************************************************************
            '
            ' Remove static method
            '
            ' Removes a project from database.
            ' This method relis on cascading delete to delete the associated categories,
            ' projectmembers, and time entries
            '
            '*********************************************************************

            Public Shared Sub Remove(ByVal projectID As Integer)
                SqlHelper.ExecuteNonQuery(ConfigurationSettings.AppSettings(Web.Global.CfgKeyConnString), "TT_DeleteProject", projectID)
            End Sub 'Remove

            '*********************************************************************
            '
            ' Inserts a project along with its members and categories into the databse
            '
            '*********************************************************************

            Private Function Insert() As Boolean
                Dim selectedMembers As New StringBuilder(_members.Count)
                Dim index As Integer = 1
                Dim user As TTUser
                For Each user In _members
                    selectedMembers.Append(user.UserID)

                    ' Don't append separator if this is the last item
                    If index <> _members.Count Then
                        selectedMembers.Append(",")
                    End If
                    index += 1
                Next user

                Dim categories As New StringBuilder(_categories.Count)
                index = 1
                Dim cat As Category
                For Each cat In _categories
                    Dim categoryString As String = String.Format("{0},{1},{2}", cat.Name, cat.Abbreviation, cat.EstDuration)
                    categories.Append(categoryString)

                    ' Don't append separator if this is the last item
                    If index <> _categories.Count Then
                        categories.Append(";")
                    End If
                    index += 1
                Next cat

                _projectID = Convert.ToInt32(SqlHelper.ExecuteScalar(ConfigurationSettings.AppSettings(Web.Global.CfgKeyConnString), "TT_AddProject", _name, _description, _managerUserID, _estCompletionDate, _estDuration, selectedMembers.ToString(), categories.ToString()))

                Return _projectID > 0
            End Function 'Insert

            '*********************************************************************
            '
            ' Populates the object with the project info from the database.
            '
            '*********************************************************************

            Public Function Load() As Boolean
                ' The Get Projects stored procedure returns a dataset with 3 tables: Projects, Members, and Categories.
                Dim ds As DataSet = SqlHelper.ExecuteDataset(ConfigurationSettings.AppSettings(Web.Global.CfgKeyConnString), "TT_GetProject", _projectID)

                If ds.Tables(0).Rows.Count < 1 Then
                    Return False
                End If
                _name = Convert.ToString(ds.Tables(0).Rows(0)("Name"))
                _description = Convert.ToString(ds.Tables(0).Rows(0)("Description"))
                _managerUserID = Convert.ToInt32(ds.Tables(0).Rows(0)("ManagerUserID"))
                _estCompletionDate = Convert.ToDateTime(ds.Tables(0).Rows(0)("EstCompletionDate"))
                _estDuration = Convert.ToDecimal(ds.Tables(0).Rows(0)("estDuration"))

                _members = New UsersCollection()
                Dim row As DataRow
                For Each row In ds.Tables(1).Rows
                    Dim user As New TTUser()
                    user.UserID = Convert.ToInt32(row("UserID"))
                    user.UserName = Convert.ToString(row("UserName"))
                    _members.Add(user)
                Next row

                _categories = New CategoriesCollection()
                For Each row In ds.Tables(2).Rows
                    Dim cat As New Category()
                    cat.ProjectID = _projectID
                    cat.CategoryID = Convert.ToInt32(row("CategoryID"))
                    cat.Name = Convert.ToString(row("Name"))
                    cat.Abbreviation = Convert.ToString(row("CategoryShortName"))
                    cat.EstDuration = Convert.ToDecimal(row("EstDuration"))
                    _categories.Add(cat)
                Next row

                Return True
            End Function 'Load

            '*********************************************************************
            '
            ' Calls Insert or Save based on the _projectID
            '
            '*********************************************************************

            Public Function Save() As Boolean
                If _projectID = 0 Then
                    Return Insert()
                Else
                    If _projectID > 0 Then
                        Return Update()
                    Else
                        _projectID = 0
                        Return False
                    End If
                End If
            End Function 'Save

            '*********************************************************************
            '
            ' Updates a project along with its members and categories.
            '
            '*********************************************************************

            Private Function Update() As Boolean
                Dim selectedMembers As New StringBuilder(_members.Count)
                Dim index As Integer = 1
                Dim user As TTUser
                For Each user In _members
                    selectedMembers.Append(user.UserID)

                    ' Don't append separator if this is the last item
                    If index <> _members.Count Then
                        selectedMembers.Append(",")
                    End If
                    index += 1
                Next user

                Dim categories As New StringBuilder(_categories.Count)
                index = 1
                Dim cat As Category
                For Each cat In _categories
                    Dim categoryString As String = String.Format("{0},{1},{2},{3}", cat.CategoryID, cat.Name, cat.Abbreviation, cat.EstDuration)
                    categories.Append(categoryString)

                    ' Don't append separator if this is the last item
                    If index <> _categories.Count Then
                        categories.Append(";")
                    End If
                    index += 1
                Next cat

                Try
                    SqlHelper.ExecuteNonQuery(ConfigurationSettings.AppSettings(Web.Global.CfgKeyConnString), "TT_UpdateProject", _projectID, _name, _description, _managerUserID, _estCompletionDate, _estDuration, selectedMembers.ToString(), categories.ToString())
                Catch
                End Try
                Return True
            End Function 'Update
        End Class 'Project
    End Namespace 'ASPNET.StarterKit.TimeTracker.BusinessLogicLayer