using System;
using System.Data;
using System.Configuration;
using System.Text;
using qminoa.DA;

namespace qminoa.BLL.PM
{

	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; }
		}

		public static ProjectsCollection GetProjects()
		{
			DataSet ds = SqlHelper.ExecuteDataset(
				ConfigurationSettings.AppSettings["ConnectionString"], 
				CommandType.StoredProcedure, "PM_ListAllProjects");  

			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;
		}

		public static ProjectsCollection GetProjects(int userID, string role)
		{
			DataSet ds = SqlHelper.ExecuteDataset(
				ConfigurationSettings.AppSettings["ConnectionString"],
				"PM_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 = Convert.ToString(r["UserName"]);
				prj.EstCompletionDate = Convert.ToDateTime(r["EstCompletionDate"]);
				prj.EstDuration = Convert.ToDecimal(r["EstDuration"]);
				projects.Add(prj);
			}
			return projects;
		}

		public static ProjectsCollection GetProjects(int queryUserID, int userID)
		{
			ProjectsCollection projects = new ProjectsCollection();
			DataSet ds = SqlHelper.ExecuteDataset(
				ConfigurationSettings.AppSettings["ConnectionString"], 
				"PM_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;
		}

		public static CategoriesCollection GetCategories(int projectID)
		{
			DataSet ds = SqlHelper.ExecuteDataset(
				ConfigurationSettings.AppSettings["ConnectionString"], 
				"PM_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;	
		}

		public static void Remove (int projectID)
		{
			SqlHelper.ExecuteNonQuery(ConfigurationSettings.AppSettings["ConnectionString"], 
				"PM_DeleteProject", projectID);
		}
		
		private bool Insert()
		{
			StringBuilder selectedMembers = new StringBuilder(_members.Count);
			int index = 1;
			foreach(PMUser user in _members)
			{
				selectedMembers.Append(user.UserID);

				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);

				if (index != _categories.Count)
				{
					categories.Append(";");
				}
				index++;
			}
			
			_projectID = Convert.ToInt32(SqlHelper.ExecuteScalar(
				ConfigurationSettings.AppSettings["ConnectionString"], "PM_AddProject", 
				_name,
				_description,
				_managerUserID,
				_estCompletionDate,
				_estDuration,
				selectedMembers.ToString(),
				categories.ToString()
				));

			return (_projectID > 0);
		}

		public bool Load()
		{
			DataSet ds = SqlHelper.ExecuteDataset(
				ConfigurationSettings.AppSettings["ConnectionString"], 
				"PM_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)
			{
				PMUser user = new PMUser();
				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;
		}

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

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

				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);

				if (index != _categories.Count)
				{
					categories.Append(";");
				}
				index++;
			}

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