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