    using System;
    using System.Data;
    using System.Configuration;
    using System.Text;
    using 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 CategoriesCollection _categories;
            private string                _description;
            private DateTime            _estCompletionDate;
            private decimal                _estDuration;
            private int                    _managerUserID;
            private string                _managerUserName;
            private UsersCollection        _members;
            private string                _name;
            private int                    _projectID;

            public Project()
            {
            }

            public Project(int projectID)
            {
                _projectID = projectID;
            }

            public Project(
                int projectID,
                string name,
                string description,
                int managerUserID,
                DateTime estCompletionDate,
                decimal estDuration)
            {
                _projectID = projectID;
                _name = name;
                _description = description;
                _managerUserID = managerUserID;
                _estCompletionDate = estCompletionDate;
                _estDuration = estDuration;
            }

            public CategoriesCollection Categories
            {
                get{ return _categories; }
                set{ _categories = value; }
            }

            public string Description
            {
                get{ return _description; }
                set{ _description = value; }
            }

            public DateTime EstCompletionDate
            {
                get{ return _estCompletionDate; }
                set{ _estCompletionDate = value; }
            }

            public decimal EstDuration
            {
                get{ return _estDuration; }
                set{ _estDuration = value; }
            }

            public int ManagerUserID
            {
                get{ return _managerUserID; }
                set{ _managerUserID = value; }
            }

            public string ManagerUserName
            {
                get{ return _managerUserName; }
                set{ _managerUserName = value; }
            }

            public UsersCollection Members
            {
                get{ return _members; }
                set{ _members = value; }
            }

            public string Name
            {
                get{ return _name; }
                set{ _name = value; }
            }

            public int ProjectID
            {
                get{ return _projectID; }
                set{ _projectID = value; }
            }

            //*********************************************************************
            //
            // Retrieves the list of all the projects
            //
            //*********************************************************************

            public static ProjectsCollection GetProjects()
            {
                DataSet ds = SqlHelper.ExecuteDataset(
                    ConfigurationSettings.AppSettings[Web.Global.CfgKeyConnString],
                    CommandType.StoredProcedure, "TT_ListAllProjects");

                // Separate Data into a Collection of projects
                ProjectsCollection projects = new ProjectsCollection();
                foreach(DataRow r in ds.Tables[0].Rows)
                {
                    Project prj = 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);
                }
                return projects;
            }

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

            public static ProjectsCollection GetProjects(int userID, string role)
            {
                string firstName = string.Empty;
                string lastName = string.Empty;

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

                ProjectsCollection projects = new ProjectsCollection();
                foreach(DataRow r in ds.Tables[0].Rows)
                {
                    Project prj = 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"]), ref firstName, ref lastName);
                    prj.EstCompletionDate = Convert.ToDateTime(r["EstCompletionDate"]);
                    prj.EstDuration = Convert.ToDecimal(r["EstDuration"]);
                    projects.Add(prj);
                }
                return projects;
            }

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

            public static ProjectsCollection GetProjects(int queryUserID, int userID)
            {
                ProjectsCollection projects = new ProjectsCollection();
                DataSet ds = SqlHelper.ExecuteDataset(
                    ConfigurationSettings.AppSettings[Web.Global.CfgKeyConnString],
                    "TT_ListProjectsWithMembership", queryUserID, userID);

                foreach(DataRow r in ds.Tables[0].Rows)
                {
                    Project prj = 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);
                }
                return projects;
            }

            //*********************************************************************
            //
            // 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 static CategoriesCollection GetCategories(int projectID)
            {
                DataSet ds = SqlHelper.ExecuteDataset(
                    ConfigurationSettings.AppSettings[Web.Global.CfgKeyConnString],
                    "TT_ListCategories", projectID);

                CategoriesCollection categories = new CategoriesCollection();
                foreach(DataRow r in ds.Tables[0].Rows)
                {
                    Category cat = 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);
                }
                return categories;
            }

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

            public static void Remove (int projectID)
            {
                SqlHelper.ExecuteNonQuery(ConfigurationSettings.AppSettings[Web.Global.CfgKeyConnString],
                    "TT_DeleteProject", projectID);
            }

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

            private bool Insert()
            {
                StringBuilder selectedMembers = new StringBuilder(_members.Count);
                int index = 1;
                foreach(TTUser user in _members)
                {
                    selectedMembers.Append(user.UserID);

                    // Don't append separator if this is the last item
                    if (index != _members.Count)
                    {
                        selectedMembers.Append(",");
                    }
                    index++;
                }

                StringBuilder categories = new StringBuilder(_categories.Count);
                index = 1;
                foreach(Category cat in _categories)
                {
                    string categoryString =
                        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)
                    {
                        categories.Append(";");
                    }
                    index++;
                }

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

                return (_projectID > 0);
            }

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

            public bool Load()
            {
                // The Get Projects stored procedure returns a dataset with 3 tables: Projects, Members, and Categories.
                DataSet ds = SqlHelper.ExecuteDataset(
                    ConfigurationSettings.AppSettings[Web.Global.CfgKeyConnString],
                    "TT_GetProject", _projectID);

                if (ds.Tables[0].Rows.Count < 1)
                    return false;

                _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();
                foreach(DataRow row in ds.Tables[1].Rows)
                {
                    TTUser user = new TTUser();
                    user.UserID = Convert.ToInt32(row["UserID"]);
                    user.UserName = Convert.ToString(row["UserName"]);
                    _members.Add(user);
                }

                _categories = new CategoriesCollection();
                foreach(DataRow row in ds.Tables[2].Rows)
                {
                    Category cat = 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);
                }

                return true;
            }

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

            public bool Save()
            {
                if (_projectID == 0)
                    return Insert();
                else if (_projectID > 0)
                    return Update();
                else
                {
                    _projectID = 0;
                    return false;
                }
            }

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

            private bool Update()
            {
                StringBuilder selectedMembers = new StringBuilder(_members.Count);
                int index = 1;
                foreach(TTUser user in _members)
                {
                    selectedMembers.Append(user.UserID);

                    // Don't append separator if this is the last item
                    if (index != _members.Count)
                    {
                        selectedMembers.Append(",");
                    }
                    index++;
                }

                StringBuilder categories = new StringBuilder(_categories.Count);
                index = 1;
                foreach(Category cat in _categories)
                {
                    string categoryString =
                        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)
                    {
                        categories.Append(";");
                    }
                    index++;
                }

                try
                {
                    SqlHelper.ExecuteNonQuery(
                        ConfigurationSettings.AppSettings[Web.Global.CfgKeyConnString], "TT_UpdateProject",
                        _projectID,
                        _name,
                        _description,
                        _managerUserID,
                        _estCompletionDate,
                        _estDuration,
                        selectedMembers.ToString(),
                        categories.ToString()
                        );
                }
                catch
                {
                    return false;
                }
                return true;
            }
        }
    }