public static DataTable GetGroupedItemsForManagerViewDataTable(int GroupBy,
int PrincipalId, int ManagerId, int ProjectId, int categoryId,
bool ShowActive, ArrayList alTypes,
DateTime dtCompleted1, DateTime dtCompleted2,
DateTime dtUpcoming1, DateTime dtUpcoming2,
DateTime dtCreated1, DateTime dtCreated2,
PrimaryKeyId orgUid, PrimaryKeyId contactUid, bool showChildTodo)
{
DataTable result = new DataTable();
result.Columns.Add(new DataColumn("GroupId", typeof(string)));
result.Columns.Add(new DataColumn("GroupName", typeof(string)));
result.Columns.Add(new DataColumn("ItemId", typeof(int)));
result.Columns.Add(new DataColumn("Title", typeof(string)));
result.Columns.Add(new DataColumn("ItemType", typeof(int)));
result.Columns.Add(new DataColumn("ManagerId", typeof(int)));
result.Columns.Add(new DataColumn("ProjectId", typeof(int)));
result.Columns.Add(new DataColumn("ProjectTitle", typeof(string)));
result.Columns.Add(new DataColumn("StateId", typeof(int)));
result.Columns.Add(new DataColumn("FinishDate", typeof(DateTime)));
result.Columns.Add(new DataColumn("StartDate", typeof(DateTime)));
result.Columns.Add(new DataColumn("CompletionTypeId", typeof(int)));
result.Columns.Add(new DataColumn("PercentCompleted", typeof(int)));
result.Columns.Add(new DataColumn("PriorityId", typeof(int)));
result.Columns.Add(new DataColumn("PriorityName", typeof(string)));
result.Columns.Add(new DataColumn("ActualStartDate", typeof(DateTime)));
result.Columns.Add(new DataColumn("ActualFinishDate", typeof(DateTime)));
result.Columns.Add(new DataColumn("CreationDate", typeof(DateTime)));
result.Columns.Add(new DataColumn("TaskTime", typeof(int)));
result.Columns.Add(new DataColumn("TotalMinutes", typeof(int)));
result.Columns.Add(new DataColumn("TotalApproved", typeof(int)));
result.Columns.Add(new DataColumn("ContainerName", typeof(string)));
result.Columns.Add(new DataColumn("ContainerType", typeof(int)));
result.Columns.Add(new DataColumn("IsChildToDo", typeof(bool)));
result.Columns.Add(new DataColumn("IsOverdue", typeof(bool)));
result.Columns.Add(new DataColumn("IsNewMessage", typeof(bool)));
result.Columns.Add(new DataColumn("ContactUid", typeof(Guid)));
result.Columns.Add(new DataColumn("OrgUid", typeof(Guid)));
result.Columns.Add(new DataColumn("ClientUid", typeof(Guid)));
result.Columns.Add(new DataColumn("ClientName", typeof(string)));
result.Columns.Add(new DataColumn("CategoryId", typeof(int)));
result.Columns.Add(new DataColumn("CategoryName", typeof(string)));
DataRow dr;
SortedList<object, string> alGroup = new SortedList<object, string>();
Hashtable htPrincipals = new Hashtable();
int TimeZoneId = Security.CurrentUser.TimeZoneId;
DateTime UserDate = DBCommon.GetLocalDate(TimeZoneId, DateTime.UtcNow); // current User's datetime
DateTime dtUpcoming1User = dtUpcoming1;
DateTime dtUpcoming2User = dtUpcoming2;
DateTime dtCompleted1User = dtCompleted1;
DateTime dtCompleted2User = dtCompleted2;
DateTime dtCreated1User = dtCreated1;
DateTime dtCreated2User = dtCreated2;
dtCompleted1 = DBCommon.GetUTCDate(Security.CurrentUser.TimeZoneId, dtCompleted1);
dtCompleted2 = DBCommon.GetUTCDate(Security.CurrentUser.TimeZoneId, dtCompleted2);
dtUpcoming1 = DBCommon.GetUTCDate(Security.CurrentUser.TimeZoneId, dtUpcoming1);
dtUpcoming2 = DBCommon.GetUTCDate(Security.CurrentUser.TimeZoneId, dtUpcoming2);
dtCreated1 = DBCommon.GetUTCDate(Security.CurrentUser.TimeZoneId, dtCreated1);
dtCreated2 = DBCommon.GetUTCDate(Security.CurrentUser.TimeZoneId, dtCreated2);
if (GroupBy != 1)
{
#region ToDo & Tasks
if (alTypes.Contains((int)ObjectTypes.Task) || alTypes.Contains((int)ObjectTypes.ToDo))
{
DataTable dt;
if (showChildTodo)
{
if (GroupBy == 5)
dt = DBToDo.GetListToDoAndTasksForManagerViewWithChildTodoWithCategories(PrincipalId,
Security.CurrentUser.TimeZoneId, Security.CurrentUser.LanguageId,
ManagerId, ProjectId, categoryId, ShowActive,
dtCompleted1, dtCompleted2,
dtUpcoming1, dtUpcoming2,
dtCreated1, dtCreated2,
orgUid, contactUid);
else
dt = DBToDo.GetListToDoAndTasksForManagerViewWithChildTodo(PrincipalId,
Security.CurrentUser.TimeZoneId, Security.CurrentUser.LanguageId,
ManagerId, ProjectId, categoryId, ShowActive,
dtCompleted1, dtCompleted2,
dtUpcoming1, dtUpcoming2,
dtCreated1, dtCreated2,
orgUid, contactUid);
}
else
{
if (GroupBy == 5)
dt = DBToDo.GetListToDoAndTasksForManagerViewWithCategories(PrincipalId,
Security.CurrentUser.TimeZoneId, Security.CurrentUser.LanguageId,
ManagerId, ProjectId, categoryId, ShowActive,
dtCompleted1, dtCompleted2,
dtUpcoming1, dtUpcoming2,
dtCreated1, dtCreated2,
orgUid, contactUid);
else
dt = DBToDo.GetListToDoAndTasksForManagerViewDataTable(PrincipalId,
Security.CurrentUser.TimeZoneId, Security.CurrentUser.LanguageId,
ManagerId, ProjectId, categoryId, ShowActive,
dtCompleted1, dtCompleted2,
dtUpcoming1, dtUpcoming2,
dtCreated1, dtCreated2,
orgUid, contactUid);
}
foreach (DataRow _dr in dt.Rows)
if (alTypes.Contains((int)_dr["ItemType"]))
{
// Exclude child todo if container is not visible
if (showChildTodo && !alTypes.Contains((int)_dr["ContainerType"]))
{
continue;
}
dr = result.NewRow();
dr["GroupId"] = "0";
dr["GroupName"] = "";
dr["ItemId"] = (int)_dr["ItemId"];
dr["Title"] = _dr["Title"].ToString();
dr["ItemType"] = (int)_dr["ItemType"];
dr["ManagerId"] = (int)_dr["ManagerId"];
if (_dr["ProjectId"] != DBNull.Value)
dr["ProjectId"] = (int)_dr["ProjectId"];
else
dr["ProjectId"] = 0;
if (_dr["ProjectTitle"] != DBNull.Value)
dr["ProjectTitle"] = _dr["ProjectTitle"].ToString();
else
dr["ProjectTitle"] = "";
dr["StateId"] = (int)_dr["StateId"];
dr["IsOverdue"] = (bool)_dr["IsOverdue"]; ;
if (_dr["StartDate"] != DBNull.Value)
dr["StartDate"] = (DateTime)_dr["StartDate"];
if (_dr["FinishDate"] != DBNull.Value)
dr["FinishDate"] = (DateTime)_dr["FinishDate"];
if (_dr["ActualStartDate"] != DBNull.Value)
dr["ActualStartDate"] = (DateTime)_dr["ActualStartDate"];
if (_dr["ActualFinishDate"] != DBNull.Value)
dr["ActualFinishDate"] = (DateTime)_dr["ActualFinishDate"];
dr["CreationDate"] = (DateTime)_dr["CreationDate"];
dr["TaskTime"] = (int)_dr["TaskTime"];
dr["TotalMinutes"] = (int)_dr["TotalMinutes"];
dr["TotalApproved"] = (int)_dr["TotalApproved"];
dr["CompletionTypeId"] = (int)_dr["CompletionTypeId"];
dr["PercentCompleted"] = (int)_dr["PercentCompleted"];
dr["PriorityId"] = (int)_dr["PriorityId"];
dr["PriorityName"] = _dr["PriorityName"].ToString();
if (showChildTodo)
{
dr["ContainerName"] = _dr["ContainerName"].ToString();
dr["ContainerType"] = (int)_dr["ContainerType"];
dr["IsChildToDo"] = (bool)_dr["IsChildToDo"];
}
else
{
dr["ContainerName"] = "";
dr["ContainerType"] = 0;
dr["IsChildToDo"] = false;
}
dr["IsNewMessage"] = false;
Guid clientUid = Guid.Empty;
if (_dr["ContactUid"] != DBNull.Value)
clientUid = (Guid)_dr["ContactUid"];
if (_dr["OrgUid"] != DBNull.Value)
clientUid = (Guid)_dr["OrgUid"];
dr["ClientUid"] = clientUid;
dr["ContactUid"] = _dr["ContactUid"];
dr["OrgUid"] = _dr["OrgUid"];
dr["ClientName"] = _dr["ClientName"];
if (GroupBy == 5)
{
if (_dr["CategoryId"] != DBNull.Value)
dr["CategoryId"] = _dr["CategoryId"];
else
dr["CategoryId"] = 0;
dr["CategoryName"] = _dr["CategoryName"];
}
result.Rows.Add(dr);
if (GroupBy == 2 && !alGroup.Keys.Contains((int)dr["ManagerId"]))
{
int managerId = (int)dr["ManagerId"];
alGroup.Add(managerId, User.GetUserName(managerId));
}
if (GroupBy == 3 && !alGroup.Keys.Contains((int)dr["ProjectId"]))
{
alGroup.Add((int)dr["ProjectId"], dr["ProjectTitle"].ToString());
}
if (GroupBy == 4 && !alGroup.Keys.Contains(clientUid))
{
alGroup.Add(clientUid, dr["ClientName"].ToString());
}
if (GroupBy == 5 && !alGroup.Keys.Contains((int)dr["CategoryId"]))
{
alGroup.Add((int)dr["CategoryId"], dr["CategoryName"].ToString());
}
}
}
#endregion
#region Documents
if (alTypes.Contains((int)ObjectTypes.Document))
{
DataTable dt;
if (showChildTodo)
{
if (GroupBy == 5)
dt = DBDocument.GetListDocumentsForManagerViewWithChildTodoWithCategories(PrincipalId,
Security.CurrentUser.TimeZoneId, Security.CurrentUser.LanguageId, ManagerId,
ProjectId, categoryId, ShowActive,
dtCompleted1, dtCompleted2,
dtUpcoming1, dtUpcoming2,
dtCreated1, dtCreated2,
orgUid, contactUid);
else
dt = DBDocument.GetListDocumentsForManagerViewWithChildTodo(PrincipalId,
Security.CurrentUser.TimeZoneId, Security.CurrentUser.LanguageId, ManagerId,
ProjectId, categoryId, ShowActive,
dtCompleted1, dtCompleted2,
dtUpcoming1, dtUpcoming2,
dtCreated1, dtCreated2,
orgUid, contactUid);
}
else
{
if (GroupBy == 5)
dt = DBDocument.GetListDocumentsForManagerViewWithCategories(PrincipalId,
Security.CurrentUser.TimeZoneId, Security.CurrentUser.LanguageId, ManagerId,
ProjectId, categoryId, ShowActive,
dtCompleted1, dtCompleted2,
dtCreated1, dtCreated2,
orgUid, contactUid);
else
dt = DBDocument.GetListDocumentsForManagerViewDataTable(PrincipalId,
Security.CurrentUser.TimeZoneId, Security.CurrentUser.LanguageId, ManagerId,
ProjectId, categoryId, ShowActive,
dtCompleted1, dtCompleted2,
dtCreated1, dtCreated2,
orgUid, contactUid);
}
foreach (DataRow _dr in dt.Rows)
{
dr = result.NewRow();
dr["GroupId"] = "0";
dr["GroupName"] = "";
dr["ItemId"] = (int)_dr["ItemId"];
dr["Title"] = _dr["Title"].ToString();
dr["ItemType"] = (int)_dr["ItemType"];
dr["ManagerId"] = (int)_dr["ManagerId"];
if (_dr["ProjectId"] != DBNull.Value)
dr["ProjectId"] = (int)_dr["ProjectId"];
else
dr["ProjectId"] = 0;
if (_dr["ProjectTitle"] != DBNull.Value)
dr["ProjectTitle"] = _dr["ProjectTitle"].ToString();
else
dr["ProjectTitle"] = "";
dr["StateId"] = (int)_dr["StateId"];
dr["IsOverdue"] = false;
if (_dr["StartDate"] != DBNull.Value)
dr["StartDate"] = (DateTime)_dr["StartDate"];
if (_dr["FinishDate"] != DBNull.Value)
dr["ActualFinishDate"] = (DateTime)_dr["FinishDate"];
dr["CreationDate"] = (DateTime)_dr["CreationDate"];
dr["TaskTime"] = (int)_dr["TaskTime"];
dr["TotalMinutes"] = (int)_dr["TotalMinutes"];
dr["TotalApproved"] = (int)_dr["TotalApproved"];
dr["CompletionTypeId"] = (int)_dr["CompletionTypeId"];
dr["PercentCompleted"] = (int)_dr["PercentCompleted"];
dr["PriorityId"] = (int)_dr["PriorityId"];
dr["PriorityName"] = _dr["PriorityName"].ToString();
if (showChildTodo)
{
dr["ContainerName"] = _dr["ContainerName"].ToString();
dr["ContainerType"] = (int)_dr["ItemType"];
}
else
{
dr["ContainerName"] = "";
dr["ContainerType"] = 0;
}
dr["IsChildToDo"] = false;
dr["IsNewMessage"] = false;
Guid clientUid = Guid.Empty;
if (_dr["ContactUid"] != DBNull.Value)
clientUid = (Guid)_dr["ContactUid"];
if (_dr["OrgUid"] != DBNull.Value)
clientUid = (Guid)_dr["OrgUid"];
dr["ClientUid"] = clientUid;
dr["ContactUid"] = _dr["ContactUid"];
dr["OrgUid"] = _dr["OrgUid"];
dr["ClientName"] = _dr["ClientName"];
if (GroupBy == 5)
{
if (_dr["CategoryId"] != DBNull.Value)
dr["CategoryId"] = _dr["CategoryId"];
else
dr["CategoryId"] = 0;
dr["CategoryName"] = _dr["CategoryName"];
}
result.Rows.Add(dr);
if (GroupBy == 2 && !alGroup.Keys.Contains((int)dr["ManagerId"]))
{
int managerId = (int)dr["ManagerId"];
alGroup.Add(managerId, User.GetUserName(managerId));
}
if (GroupBy == 3 && !alGroup.Keys.Contains((int)dr["ProjectId"]))
{
alGroup.Add((int)dr["ProjectId"], dr["ProjectTitle"].ToString());
}
if (GroupBy == 4 && !alGroup.Keys.Contains(clientUid))
{
alGroup.Add(clientUid, dr["ClientName"].ToString());
}
if (GroupBy == 5 && !alGroup.Keys.Contains((int)dr["CategoryId"]))
{
alGroup.Add((int)dr["CategoryId"], dr["CategoryName"].ToString());
}
}
}
#endregion
#region Issues
if (alTypes.Contains((int)ObjectTypes.Issue))
{
DataTable dt;
if (showChildTodo)
{
if (GroupBy == 5)
dt = DBIncident.GetListIncidentsForManagerViewWithChildTodoWithCategories(PrincipalId,
Security.CurrentUser.TimeZoneId, Security.CurrentUser.LanguageId, ManagerId,
ProjectId, categoryId, ShowActive,
dtCompleted1, dtCompleted2,
dtUpcoming1, dtUpcoming2,
dtCreated1, dtCreated2,
orgUid, contactUid);
else
dt = DBIncident.GetListIncidentsForManagerViewWithChildTodo(PrincipalId,
Security.CurrentUser.TimeZoneId, Security.CurrentUser.LanguageId, ManagerId,
ProjectId, categoryId, ShowActive,
dtCompleted1, dtCompleted2,
dtUpcoming1, dtUpcoming2,
dtCreated1, dtCreated2,
orgUid, contactUid);
}
else
{
if (GroupBy == 5)
dt = DBIncident.GetListIncidentsForManagerViewWithCategories(PrincipalId,
Security.CurrentUser.TimeZoneId, Security.CurrentUser.LanguageId, ManagerId,
ProjectId, categoryId, ShowActive,
dtCompleted1, dtCompleted2,
dtCreated1, dtCreated2,
orgUid, contactUid);
else
dt = DBIncident.GetListIncidentsForManagerViewDataTable(PrincipalId,
Security.CurrentUser.TimeZoneId, Security.CurrentUser.LanguageId, ManagerId,
ProjectId, categoryId, ShowActive,
dtCompleted1, dtCompleted2,
dtCreated1, dtCreated2,
orgUid, contactUid);
}
foreach (DataRow _dr in dt.Rows)
{
dr = result.NewRow();
dr["GroupId"] = "0";
dr["GroupName"] = "";
dr["ItemId"] = (int)_dr["ItemId"];
dr["Title"] = _dr["Title"].ToString();
dr["ItemType"] = (int)_dr["ItemType"];
dr["ManagerId"] = (int)_dr["ManagerId"];
if (_dr["ProjectId"] != DBNull.Value)
dr["ProjectId"] = (int)_dr["ProjectId"];
else
dr["ProjectId"] = 0;
if (_dr["ProjectTitle"] != DBNull.Value)
dr["ProjectTitle"] = _dr["ProjectTitle"].ToString();
else
dr["ProjectTitle"] = "";
dr["StateId"] = (int)_dr["StateId"];
dr["IsOverdue"] = (bool)_dr["IsOverdue"];
if (_dr["StartDate"] != DBNull.Value)
dr["StartDate"] = (DateTime)_dr["StartDate"];
if (_dr["FinishDate"] != DBNull.Value)
dr["FinishDate"] = (DateTime)_dr["FinishDate"];
if (_dr["ActualFinishDate"] != DBNull.Value)
dr["ActualFinishDate"] = (DateTime)_dr["ActualFinishDate"];
if (_dr["ActualStartDate"] != DBNull.Value)
dr["ActualStartDate"] = (DateTime)_dr["ActualStartDate"];
dr["CreationDate"] = (DateTime)_dr["CreationDate"];
dr["TaskTime"] = (int)_dr["TaskTime"];
dr["TotalMinutes"] = (int)_dr["TotalMinutes"];
dr["TotalApproved"] = (int)_dr["TotalApproved"];
dr["CompletionTypeId"] = (int)_dr["CompletionTypeId"];
dr["PercentCompleted"] = (int)_dr["PercentCompleted"];
dr["PriorityId"] = (int)_dr["PriorityId"];
dr["PriorityName"] = _dr["PriorityName"].ToString();
if (showChildTodo)
{
dr["ContainerName"] = _dr["ContainerName"].ToString();
dr["ContainerType"] = (int)_dr["ItemType"];
}
else
{
dr["ContainerName"] = "";
dr["ContainerType"] = 0;
}
dr["IsChildToDo"] = false;
dr["IsNewMessage"] = (bool)_dr["IsNewMessage"];
Guid clientUid = Guid.Empty;
if (_dr["ContactUid"] != DBNull.Value)
clientUid = (Guid)_dr["ContactUid"];
if (_dr["OrgUid"] != DBNull.Value)
clientUid = (Guid)_dr["OrgUid"];
dr["ClientUid"] = clientUid;
dr["ContactUid"] = _dr["ContactUid"];
dr["OrgUid"] = _dr["OrgUid"];
dr["ClientName"] = _dr["ClientName"];
if (GroupBy == 5)
{
if (_dr["CategoryId"] != DBNull.Value)
dr["CategoryId"] = _dr["CategoryId"];
else
dr["CategoryId"] = 0;
dr["CategoryName"] = _dr["CategoryName"];
}
result.Rows.Add(dr);
if (GroupBy == 2 && !alGroup.Keys.Contains((int)dr["ManagerId"]))
{
int managerId = (int)dr["ManagerId"];
alGroup.Add(managerId, User.GetUserName(managerId));
}
if (GroupBy == 3 && !alGroup.Keys.Contains((int)dr["ProjectId"]))
{
alGroup.Add((int)dr["ProjectId"], dr["ProjectTitle"].ToString());
}
if (GroupBy == 4 && !alGroup.Keys.Contains(clientUid))
{
alGroup.Add(clientUid, dr["ClientName"].ToString());
}
if (GroupBy == 5 && !alGroup.Keys.Contains((int)dr["CategoryId"]))
{
alGroup.Add((int)dr["CategoryId"], dr["CategoryName"].ToString());
}
}
}
#endregion
#region Events
if (alTypes.Contains((int)ObjectTypes.CalendarEntry))
{
DataTable dt;
if (GroupBy == 5)
dt = DBEvent.GetListEventsForManagerViewWithCategories(PrincipalId,
Security.CurrentUser.TimeZoneId, Security.CurrentUser.LanguageId, ManagerId,
ProjectId, categoryId, ShowActive,
dtCompleted1, dtCompleted2,
dtUpcoming1, dtUpcoming2,
dtCreated1, dtCreated2,
orgUid, contactUid);
else
dt = DBEvent.GetListEventsForManagerViewDataTable(PrincipalId,
Security.CurrentUser.TimeZoneId, Security.CurrentUser.LanguageId, ManagerId,
ProjectId, categoryId, ShowActive,
dtCompleted1, dtCompleted2,
dtUpcoming1, dtUpcoming2,
dtCreated1, dtCreated2,
orgUid, contactUid);
foreach (DataRow _dr in dt.Rows)
{
if (!(bool)_dr["HasRecurrence"])
{
dr = result.NewRow();
dr["GroupId"] = "0";
dr["GroupName"] = "";
dr["ItemId"] = (int)_dr["ItemId"];
dr["Title"] = _dr["Title"].ToString();
dr["ItemType"] = (int)_dr["ItemType"];
dr["ManagerId"] = (int)_dr["ManagerId"];
if (_dr["ProjectId"] != DBNull.Value)
dr["ProjectId"] = (int)_dr["ProjectId"];
else
dr["ProjectId"] = 0;
if (_dr["ProjectTitle"] != DBNull.Value)
dr["ProjectTitle"] = _dr["ProjectTitle"].ToString();
else
dr["ProjectTitle"] = "";
dr["StateId"] = (int)_dr["StateId"];
dr["IsOverdue"] = false;
if (_dr["StartDate"] != DBNull.Value)
dr["StartDate"] = (DateTime)_dr["StartDate"];
if (_dr["FinishDate"] != DBNull.Value)
dr["FinishDate"] = (DateTime)_dr["FinishDate"];
dr["CreationDate"] = (DateTime)_dr["CreationDate"];
dr["TaskTime"] = (int)_dr["TaskTime"];
dr["TotalMinutes"] = (int)_dr["TotalMinutes"];
dr["TotalApproved"] = (int)_dr["TotalApproved"];
dr["CompletionTypeId"] = (int)_dr["CompletionTypeId"];
dr["PercentCompleted"] = (int)_dr["PercentCompleted"];
dr["PriorityId"] = (int)_dr["PriorityId"];
dr["PriorityName"] = _dr["PriorityName"].ToString();
if (showChildTodo)
{
dr["ContainerName"] = _dr["Title"].ToString();
dr["ContainerType"] = (int)_dr["ItemType"];
}
else
{
dr["ContainerName"] = "";
dr["ContainerType"] = 0;
}
dr["IsChildToDo"] = false;
dr["IsNewMessage"] = false;
Guid clientUid = Guid.Empty;
if (_dr["ContactUid"] != DBNull.Value)
clientUid = (Guid)_dr["ContactUid"];
if (_dr["OrgUid"] != DBNull.Value)
clientUid = (Guid)_dr["OrgUid"];
dr["ClientUid"] = clientUid;
dr["ContactUid"] = _dr["ContactUid"];
dr["OrgUid"] = _dr["OrgUid"];
dr["ClientName"] = _dr["ClientName"];
if (GroupBy == 5)
{
if (_dr["CategoryId"] != DBNull.Value)
dr["CategoryId"] = _dr["CategoryId"];
else
dr["CategoryId"] = 0;
dr["CategoryName"] = _dr["CategoryName"];
}
result.Rows.Add(dr);
if (GroupBy == 2 && !alGroup.Keys.Contains((int)dr["ManagerId"]))
{
int managerId = (int)dr["ManagerId"];
alGroup.Add(managerId, User.GetUserName(managerId));
}
if (GroupBy == 3 && !alGroup.Keys.Contains((int)dr["ProjectId"]))
{
alGroup.Add((int)dr["ProjectId"], dr["ProjectTitle"].ToString());
}
if (GroupBy == 4 && !alGroup.Keys.Contains(clientUid))
{
alGroup.Add(clientUid, dr["ClientName"].ToString());
}
if (GroupBy == 5 && !alGroup.Keys.Contains((int)dr["CategoryId"]))
{
alGroup.Add((int)dr["CategoryId"], dr["CategoryName"].ToString());
}
}
else // Recurrence
{
int StartTime;
int EndTime;
CalendarEntry.Recurrence recurrence;
using (IDataReader r_reader = DBCommon.GetRecurrence((int)ObjectTypes.CalendarEntry, (int)_dr["ItemId"]))
{
r_reader.Read();
recurrence = new CalendarEntry.Recurrence(
(byte)r_reader["Pattern"],
(byte)r_reader["SubPattern"],
(byte)r_reader["Frequency"],
(byte)r_reader["Weekdays"],
(byte)r_reader["DayOfMonth"],
(byte)r_reader["WeekNumber"],
(byte)r_reader["MonthNumber"],
(int)r_reader["EndAfter"],
(DateTime)_dr["StartDate"],
(DateTime)_dr["FinishDate"],
(int)r_reader["TimeZoneId"]);
StartTime = (int)r_reader["StartTime"];
EndTime = (int)r_reader["EndTime"];
}
// Get new StartDate and FinishDate for recurrence TimeZone (not UserTimeOffset)
DateTime eventStartDate = DateTime.UtcNow;
using (IDataReader r_reader = DBEvent.GetEventDates((int)_dr["ItemId"], recurrence.TimeZoneId))
{
r_reader.Read();
recurrence.StartDate = ((DateTime)r_reader["StartDate"]).Date;
recurrence.FinishDate = ((DateTime)r_reader["FinishDate"]).Date;
eventStartDate = (DateTime)r_reader["StartDate"];
}
eventStartDate = DBCommon.GetUTCDate(recurrence.TimeZoneId, eventStartDate);
// from_date, to_date - in UTC
DateTime from_date = DateTime.UtcNow.Date.AddDays(-1);
DateTime to_date = DateTime.UtcNow.Date.AddDays(2);
if (dtUpcoming2 < DateTime.MaxValue.AddDays(-1))
to_date = dtUpcoming2;
if (dtCompleted1 > DateTime.MinValue.AddDays(1))
from_date = dtCompleted1;
ArrayList dates = CalendarEntry.GetRecurDates(from_date, to_date, StartTime, eventStartDate, recurrence);
foreach (DateTime d in dates) // Dates in UTC (�� � �������������, ��� ������� ���������� � 00:00. ������� ���� ��� �������� StartTime)
{
DateTime UserDt = DBCommon.GetLocalDate(TimeZoneId, d); // from UTC to User's time
DateTime _StartDate = UserDt.AddMinutes(StartTime); // Start Date in User's time
DateTime _FinishDate = UserDt.AddMinutes(EndTime); // Finish Date in User's time
dr = result.NewRow();
if (_StartDate > UserDate)
{
if (dtUpcoming1 >= DateTime.MaxValue.AddDays(-1)) // if we don't need upcoming
continue;
if (_StartDate < dtUpcoming1User || _StartDate > dtUpcoming2User) // out of range
continue;
dr["StateId"] = (int)ObjectStates.Upcoming;
}
else if (_FinishDate < UserDate)
{
if (dtCompleted2 <= DateTime.MinValue.AddDays(1)) // if we don't need completed
continue;
if (_FinishDate < dtCompleted1User || _FinishDate > dtCompleted2User) // out of range
continue;
dr["StateId"] = (int)ObjectStates.Completed;
}
else
{
if (!ShowActive)
continue;
dr["StateId"] = (int)ObjectStates.Active;
}
dr["IsOverdue"] = false;
dr["GroupId"] = "0";
dr["GroupName"] = "";
dr["ItemId"] = (int)_dr["ItemId"];
dr["Title"] = _dr["Title"].ToString();
dr["ItemType"] = (int)_dr["ItemType"];
dr["ManagerId"] = (int)_dr["ManagerId"];
if (_dr["ProjectId"] != DBNull.Value)
dr["ProjectId"] = (int)_dr["ProjectId"];
else
dr["ProjectId"] = 0;
if (_dr["ProjectTitle"] != DBNull.Value)
dr["ProjectTitle"] = _dr["ProjectTitle"].ToString();
else
dr["ProjectTitle"] = "";
dr["StartDate"] = _StartDate;
dr["FinishDate"] = _FinishDate;
dr["CreationDate"] = (DateTime)_dr["CreationDate"];
dr["TaskTime"] = (int)_dr["TaskTime"];
dr["TotalMinutes"] = (int)_dr["TotalMinutes"];
dr["TotalApproved"] = (int)_dr["TotalApproved"];
dr["CompletionTypeId"] = (int)_dr["CompletionTypeId"];
dr["PercentCompleted"] = (int)_dr["PercentCompleted"];
dr["PriorityId"] = (int)_dr["PriorityId"];
dr["PriorityName"] = _dr["PriorityName"].ToString();
if (showChildTodo)
dr["ContainerName"] = _dr["Title"].ToString();
else
dr["ContainerName"] = "";
dr["IsChildToDo"] = false;
dr["IsNewMessage"] = false;
Guid clientUid = Guid.Empty;
if (_dr["ContactUid"] != DBNull.Value)
clientUid = (Guid)_dr["ContactUid"];
if (_dr["OrgUid"] != DBNull.Value)
clientUid = (Guid)_dr["OrgUid"];
dr["ClientUid"] = clientUid;
dr["ContactUid"] = _dr["ContactUid"];
dr["OrgUid"] = _dr["OrgUid"];
dr["ClientName"] = _dr["ClientName"];
if (GroupBy == 5)
{
if (_dr["CategoryId"] != DBNull.Value)
dr["CategoryId"] = _dr["CategoryId"];
else
dr["CategoryId"] = 0;
dr["CategoryName"] = _dr["CategoryName"];
}
result.Rows.Add(dr);
if (GroupBy == 2 && !alGroup.Keys.Contains((int)dr["ManagerId"]))
{
int managerId = (int)dr["ManagerId"];
alGroup.Add(managerId, User.GetUserName(managerId));
}
if (GroupBy == 3 && !alGroup.Keys.Contains((int)dr["ProjectId"]))
{
alGroup.Add((int)dr["ProjectId"], dr["ProjectTitle"].ToString());
}
if (GroupBy == 4 && !alGroup.Keys.Contains(clientUid))
{
alGroup.Add(clientUid, dr["ClientName"].ToString());
}
if (GroupBy == 5 && !alGroup.Keys.Contains((int)dr["CategoryId"]))
{
alGroup.Add((int)dr["CategoryId"], dr["CategoryName"].ToString());
}
}
}
}
}
#endregion
}
else
{
#region Objects
DataTable dt;
if (showChildTodo)
dt = DBToDo.GetListToDoAndTasksForManagerViewGroupedByUserWithChildTodo(PrincipalId,
Security.CurrentUser.TimeZoneId, Security.CurrentUser.LanguageId, ManagerId,
ProjectId, categoryId, ShowActive,
dtCompleted1, dtCompleted2,
dtUpcoming1, dtUpcoming2,
dtCreated1, dtCreated2,
orgUid, contactUid);
else
dt = DBToDo.GetListToDoAndTasksForManagerViewGroupedByUser(PrincipalId,
Security.CurrentUser.TimeZoneId, Security.CurrentUser.LanguageId, ManagerId,
ProjectId, categoryId, ShowActive,
dtCompleted1, dtCompleted2,
dtUpcoming1, dtUpcoming2,
dtCreated1, dtCreated2,
orgUid, contactUid);
foreach (DataRow _dr in dt.Rows)
{
if (alTypes.Contains((int)_dr["ItemType"]))
{
// Exclude child todo if container is not visible
if (showChildTodo && !alTypes.Contains((int)_dr["ContainerType"]))
{
continue;
}
if (!(bool)_dr["HasRecurrence"])
{
dr = result.NewRow();
dr["GroupId"] = _dr["PrincipalId"].ToString();
dr["GroupName"] = _dr["LastName"].ToString() + " " + _dr["FirstName"].ToString();
dr["ItemId"] = (int)_dr["ItemId"];
dr["Title"] = _dr["Title"].ToString();
dr["ItemType"] = (int)_dr["ItemType"];
dr["ManagerId"] = (int)_dr["ManagerId"];
if (_dr["ProjectId"] != DBNull.Value)
dr["ProjectId"] = (int)_dr["ProjectId"];
else
dr["ProjectId"] = 0;
if (_dr["ProjectTitle"] != DBNull.Value)
dr["ProjectTitle"] = _dr["ProjectTitle"].ToString();
else
dr["ProjectTitle"] = "";
dr["StateId"] = (int)_dr["StateId"];
dr["IsOverdue"] = (bool)_dr["IsOverdue"];
if (_dr["StartDate"] != DBNull.Value)
dr["StartDate"] = (DateTime)_dr["StartDate"];
if (_dr["FinishDate"] != DBNull.Value)
dr["FinishDate"] = (DateTime)_dr["FinishDate"];
if (_dr["ActualStartDate"] != DBNull.Value)
dr["ActualStartDate"] = (DateTime)_dr["ActualStartDate"];
if (_dr["ActualFinishDate"] != DBNull.Value)
dr["ActualFinishDate"] = (DateTime)_dr["ActualFinishDate"];
dr["CreationDate"] = (DateTime)_dr["CreationDate"];
dr["TaskTime"] = (int)_dr["TaskTime"];
dr["TotalMinutes"] = (int)_dr["TotalMinutes"];
dr["TotalApproved"] = (int)_dr["TotalApproved"];
dr["CompletionTypeId"] = (int)_dr["CompletionTypeId"];
dr["PercentCompleted"] = (int)_dr["PercentCompleted"];
dr["PriorityId"] = (int)_dr["PriorityId"];
dr["PriorityName"] = _dr["PriorityName"].ToString();
if (showChildTodo)
{
dr["ContainerName"] = _dr["ContainerName"].ToString();
dr["ContainerType"] = (int)_dr["ContainerType"];
dr["IsChildToDo"] = (bool)_dr["IsChildToDo"];
}
else
{
dr["ContainerName"] = "";
dr["ContainerType"] = 0;
dr["IsChildToDo"] = false;
}
dr["IsNewMessage"] = (bool)_dr["IsNewMessage"];
Guid clientUid = Guid.Empty;
if (_dr["ContactUid"] != DBNull.Value)
clientUid = (Guid)_dr["ContactUid"];
if (_dr["OrgUid"] != DBNull.Value)
clientUid = (Guid)_dr["OrgUid"];
dr["ClientUid"] = clientUid;
dr["ContactUid"] = _dr["ContactUid"];
dr["OrgUid"] = _dr["OrgUid"];
dr["ClientName"] = _dr["ClientName"];
result.Rows.Add(dr);
if (!htPrincipals.ContainsKey(int.Parse(dr["GroupId"].ToString())))
htPrincipals.Add(int.Parse(dr["GroupId"].ToString()), dr["GroupName"]);
}
else // Recurrence
{
int StartTime;
int EndTime;
CalendarEntry.Recurrence recurrence;
using (IDataReader r_reader = DBCommon.GetRecurrence((int)ObjectTypes.CalendarEntry, (int)_dr["ItemId"]))
{
r_reader.Read();
recurrence = new CalendarEntry.Recurrence(
(byte)r_reader["Pattern"],
(byte)r_reader["SubPattern"],
(byte)r_reader["Frequency"],
(byte)r_reader["Weekdays"],
(byte)r_reader["DayOfMonth"],
(byte)r_reader["WeekNumber"],
(byte)r_reader["MonthNumber"],
(int)r_reader["EndAfter"],
(DateTime)_dr["StartDate"],
(DateTime)_dr["FinishDate"],
(int)r_reader["TimeZoneId"]);
StartTime = (int)r_reader["StartTime"];
EndTime = (int)r_reader["EndTime"];
}
// Get new StartDate and FinishDate for recurrence TimeZone (not UserTimeOffset)
DateTime eventStartDate = DateTime.UtcNow;
using (IDataReader r_reader = DBEvent.GetEventDates((int)_dr["ItemId"], recurrence.TimeZoneId))
{
r_reader.Read();
recurrence.StartDate = ((DateTime)r_reader["StartDate"]).Date;
recurrence.FinishDate = ((DateTime)r_reader["FinishDate"]).Date;
eventStartDate = (DateTime)r_reader["StartDate"];
}
eventStartDate = DBCommon.GetUTCDate(recurrence.TimeZoneId, eventStartDate);
// from_date, to_date - in UTC
DateTime from_date = DateTime.UtcNow.Date.AddDays(-1);
DateTime to_date = DateTime.UtcNow.Date.AddDays(2);
if (dtUpcoming2 < DateTime.MaxValue.AddDays(-1))
to_date = dtUpcoming2;
if (dtCompleted1 > DateTime.MinValue.AddDays(1))
from_date = dtCompleted1;
ArrayList dates = CalendarEntry.GetRecurDates(from_date, to_date, StartTime, eventStartDate, recurrence);
foreach (DateTime d in dates) // Dates in UTC (�� � �������������, ��� ������� ���������� � 00:00. ������� ���� ��� �������� StartTime)
{
DateTime UserDt = DBCommon.GetLocalDate(TimeZoneId, d); // from UTC to User's time
DateTime _StartDate = UserDt.AddMinutes(StartTime); // Start Date in User's time
DateTime _FinishDate = UserDt.AddMinutes(EndTime); // Finish Date in User's time
dr = result.NewRow();
if (_StartDate > UserDate)
{
if (dtUpcoming1 >= DateTime.MaxValue.AddDays(-1)) // if we don't need upcoming
continue;
if (_StartDate < dtUpcoming1User || _StartDate > dtUpcoming2User) // out of range
continue;
dr["StateId"] = (int)ObjectStates.Upcoming;
}
else if (_FinishDate < UserDate)
{
if (dtCompleted2 <= DateTime.MinValue.AddDays(1)) // if we don't need completed
continue;
if (_FinishDate < dtCompleted1User || _FinishDate > dtCompleted2User) // out of range
continue;
dr["StateId"] = (int)ObjectStates.Completed;
}
else
{
if (!ShowActive)
continue;
dr["StateId"] = (int)ObjectStates.Active;
}
dr["IsOverdue"] = false;
dr["GroupId"] = _dr["PrincipalId"].ToString();
dr["GroupName"] = _dr["LastName"].ToString() + " " + _dr["FirstName"].ToString();
dr["ItemId"] = (int)_dr["ItemId"];
dr["Title"] = _dr["Title"].ToString();
dr["ItemType"] = (int)_dr["ItemType"];
dr["ManagerId"] = (int)_dr["ManagerId"];
if (_dr["ProjectId"] != DBNull.Value)
dr["ProjectId"] = (int)_dr["ProjectId"];
else
dr["ProjectId"] = 0;
if (_dr["ProjectTitle"] != DBNull.Value)
dr["ProjectTitle"] = _dr["ProjectTitle"].ToString();
else
dr["ProjectTitle"] = "";
dr["StartDate"] = _StartDate;
dr["FinishDate"] = _FinishDate;
if (_dr["ActualStartDate"] != DBNull.Value)
dr["ActualStartDate"] = (DateTime)_dr["ActualStartDate"];
if (_dr["ActualFinishDate"] != DBNull.Value)
dr["ActualFinishDate"] = (DateTime)_dr["ActualFinishDate"];
dr["CreationDate"] = (DateTime)_dr["CreationDate"];
dr["TaskTime"] = (int)_dr["TaskTime"];
dr["TotalMinutes"] = (int)_dr["TotalMinutes"];
dr["TotalApproved"] = (int)_dr["TotalApproved"];
dr["CompletionTypeId"] = (int)_dr["CompletionTypeId"];
dr["PercentCompleted"] = (int)_dr["PercentCompleted"];
dr["PriorityId"] = (int)_dr["PriorityId"];
dr["PriorityName"] = _dr["PriorityName"].ToString();
if (showChildTodo)
{
dr["ContainerName"] = _dr["ContainerName"].ToString();
dr["ContainerType"] = (int)_dr["ContainerType"];
dr["IsChildToDo"] = (bool)_dr["IsChildToDo"];
}
else
{
dr["ContainerName"] = "";
dr["ContainerType"] = 0;
dr["IsChildToDo"] = false;
}
dr["IsNewMessage"] = false;
Guid clientUid = Guid.Empty;
if (_dr["ContactUid"] != DBNull.Value)
clientUid = (Guid)_dr["ContactUid"];
if (_dr["OrgUid"] != DBNull.Value)
clientUid = (Guid)_dr["OrgUid"];
dr["ClientUid"] = clientUid;
dr["ContactUid"] = _dr["ContactUid"];
dr["OrgUid"] = _dr["OrgUid"];
dr["ClientName"] = _dr["ClientName"];
result.Rows.Add(dr);
if (!htPrincipals.ContainsKey(int.Parse(dr["GroupId"].ToString())))
htPrincipals.Add(int.Parse(dr["GroupId"].ToString()), dr["GroupName"]);
}
}
}
}
#endregion
}
if (GroupBy > 1)
{
#region Grouping
DataTable dt_clone = result.Clone();
string str_grouping = "";
if (GroupBy == 2)
str_grouping = "ManagerId";
else if (GroupBy == 3)
str_grouping = "ProjectId";
else if (GroupBy == 4)
str_grouping = "ClientUid";
else if (GroupBy == 5)
str_grouping = "CategoryId";
foreach (KeyValuePair<object, string> kvp in alGroup)
{
string group_name = kvp.Value;
if (GroupBy == 3 && kvp.Key.ToString() == "0")
group_name = Common.GetWebResourceString("{IbnFramework.Project:NoProject}");
if (GroupBy == 4 && kvp.Key.ToString() == Guid.Empty.ToString())
group_name = Common.GetWebResourceString("{IbnFramework.Project:NoClient}");
if (GroupBy == 5 && kvp.Key.ToString() == "0")
group_name = Common.GetWebResourceString("{IbnFramework.Project:NoCategory}");
dr = dt_clone.NewRow();
dr["GroupId"] = kvp.Key.ToString();
dr["GroupName"] = group_name;
dr["ItemId"] = 0;
dr["Title"] = "";
dr["ItemType"] = 0;
dr["ManagerId"] = 0;
dr["ProjectId"] = 0;
dr["ProjectTitle"] = "";
dr["StateId"] = 0;
dr["CompletionTypeId"] = 0;
dr["PercentCompleted"] = 0;
dr["PriorityId"] = 0;
dr["PriorityName"] = "";
dr["ContainerName"] = "";
dr["ContainerType"] = 0;
dr["IsChildToDo"] = false;
dr["IsOverdue"] = false;
dr["IsNewMessage"] = false;
dt_clone.Rows.Add(dr);
// if showChildTodo is true, then we can get the duplicating rows for incidents
// so we should eliminate such rows
int prevItemId = 0;
int prevItemType = 0;
DataRow[] dr_items = result.Select(str_grouping + "='" + kvp.Key.ToString() + "'", "ContainerName, ContainerType, IsChildToDo, Title");
foreach (DataRow dr1 in dr_items)
{
// eliminate duplicating rows
if (showChildTodo)
{
if (prevItemId == (int)dr1["ItemId"] && prevItemType == (int)dr1["ItemType"])
continue;
prevItemId = (int)dr1["ItemId"];
prevItemType = (int)dr1["ItemType"];
}
DataRow _dr = dt_clone.NewRow();
_dr.ItemArray = (Object[])dr1.ItemArray.Clone();
_dr["GroupId"] = kvp.Key.ToString();;
_dr["GroupName"] = group_name;
dt_clone.Rows.Add(_dr);
}
}
#endregion
return dt_clone;
}
else if (GroupBy == 1) //by user
{
#region Grouping
DataTable dt_clone = result.Clone();
foreach (int Id in htPrincipals.Keys)
{
string group_name = htPrincipals[Id].ToString();
dr = dt_clone.NewRow();
dr["GroupId"] = Id.ToString();
dr["GroupName"] = group_name;
dr["ItemId"] = 0;
dr["Title"] = "";
dr["ItemType"] = 0;
dr["ManagerId"] = 0;
dr["ProjectId"] = 0;
dr["ProjectTitle"] = "";
dr["StateId"] = 0;
dr["CompletionTypeId"] = 0;
dr["PercentCompleted"] = 0;
dr["PriorityId"] = 0;
dr["PriorityName"] = "";
dr["ContainerName"] = "";
dr["ContainerType"] = 0;
dr["IsChildToDo"] = false;
dr["IsOverdue"] = false;
dr["IsNewMessage"] = false;
dt_clone.Rows.Add(dr);
// Sorting is defined in SQL
DataRow[] dr_items = result.Select("GroupId='" + Id + "'");
foreach (DataRow dr1 in dr_items)
{
DataRow _dr = dt_clone.NewRow();
_dr.ItemArray = (Object[])dr1.ItemArray.Clone();
dt_clone.Rows.Add(_dr);
}
}
#endregion
return dt_clone;
}
else
{
// if showChildTodo is true, then we can get the duplicating rows for incidents
// so we should eliminate such rows
if (showChildTodo)
{
#region eliminate duplicating rows
DataTable dt_clone = result.Clone();
int prevItemId = 0;
int prevItemType = 0;
DataRow[] dr_items = result.Select("1=1", "ContainerName, ContainerType, IsChildToDo, Title");
foreach (DataRow dr1 in dr_items)
{
if (prevItemId == (int)dr1["ItemId"] && prevItemType == (int)dr1["ItemType"])
continue;
prevItemId = (int)dr1["ItemId"];
prevItemType = (int)dr1["ItemType"];
DataRow _dr = dt_clone.NewRow();
_dr.ItemArray = (Object[])dr1.ItemArray.Clone();
dt_clone.Rows.Add(_dr);
}
#endregion
return dt_clone; ;
}
else
{
return result;
}
}
}
/// <summary>
/// ItemId, Title, PriorityId, PriorityName, ItemType, CreationDate, StartDate, FinishDate,
/// PercentCompleted, IsCompleted, ManagerId, ReasonId, ProjectId, ProjectTitle, StateId,
/// CompletionTypeId, ActualStartDate, ActualFinishDate, TaskTime, TotalMinutes, TotalApproved,
/// IsOverdue, IsNewMessage, ContactUid, OrgUid, ClientName
/// </summary>
/// <returns></returns>
public static DataTable GetGroupedItemsForResourceViewDataTable(int GroupBy, int ResId,
int ManagerId, int ProjectId, int categoryId,
bool ShowActive, ArrayList alTypes,
DateTime dtCompleted, DateTime dtUpcoming, PrimaryKeyId orgUid, PrimaryKeyId contactUid)
{
DataTable result = new DataTable();
result.Columns.Add(new DataColumn("GroupId", typeof(string)));
result.Columns.Add(new DataColumn("GroupName", typeof(string)));
result.Columns.Add(new DataColumn("ItemId", typeof(int)));
result.Columns.Add(new DataColumn("Title", typeof(string)));
result.Columns.Add(new DataColumn("ItemType", typeof(int)));
result.Columns.Add(new DataColumn("ManagerId", typeof(int)));
result.Columns.Add(new DataColumn("ProjectId", typeof(int)));
result.Columns.Add(new DataColumn("ProjectTitle", typeof(string)));
result.Columns.Add(new DataColumn("StateId", typeof(int)));
result.Columns.Add(new DataColumn("StartDate", typeof(DateTime)));
result.Columns.Add(new DataColumn("FinishDate", typeof(DateTime)));
result.Columns.Add(new DataColumn("CompletionTypeId", typeof(int)));
result.Columns.Add(new DataColumn("PercentCompleted", typeof(int)));
result.Columns.Add(new DataColumn("PriorityId", typeof(int)));
result.Columns.Add(new DataColumn("PriorityName", typeof(string)));
result.Columns.Add(new DataColumn("ActualStartDate", typeof(DateTime)));
result.Columns.Add(new DataColumn("ActualFinishDate", typeof(DateTime)));
result.Columns.Add(new DataColumn("TaskTime", typeof(int)));
result.Columns.Add(new DataColumn("TotalMinutes", typeof(int)));
result.Columns.Add(new DataColumn("TotalApproved", typeof(int)));
result.Columns.Add(new DataColumn("IsOverdue", typeof(bool)));
result.Columns.Add(new DataColumn("IsNewMessage", typeof(bool)));
result.Columns.Add(new DataColumn("ContactUid", typeof(Guid)));
result.Columns.Add(new DataColumn("OrgUid", typeof(Guid)));
result.Columns.Add(new DataColumn("ClientUid", typeof(Guid)));
result.Columns.Add(new DataColumn("ClientName", typeof(string)));
result.Columns.Add(new DataColumn("CategoryId", typeof(int)));
result.Columns.Add(new DataColumn("CategoryName", typeof(string)));
DataRow dr;
SortedList<object, string> alGroup = new SortedList<object, string>();
int TimeZoneId = Security.CurrentUser.TimeZoneId;
DateTime UserDate = DBCommon.GetLocalDate(TimeZoneId, DateTime.UtcNow); // current User's datetime
DateTime dtUpcomingUser = dtUpcoming;
DateTime dtCompletedUser = dtCompleted;
dtCompleted = DBCommon.GetUTCDate(Security.CurrentUser.TimeZoneId, dtCompleted);
dtUpcoming = DBCommon.GetUTCDate(Security.CurrentUser.TimeZoneId, dtUpcoming);
#region ToDo & Tasks
if (alTypes.Contains((int)ObjectTypes.Task) || alTypes.Contains((int)ObjectTypes.ToDo))
{
DataTable dt;
if (GroupBy == 5)
dt = DBToDo.GetListToDoAndTasksForResourceViewWithCategories(ResId,
Security.CurrentUser.TimeZoneId, Security.CurrentUser.LanguageId, ManagerId,
ProjectId, categoryId, ShowActive, dtCompleted, dtUpcoming, orgUid, contactUid);
else
dt = DBToDo.GetListToDoAndTasksForResourceViewDataTable(ResId,
Security.CurrentUser.TimeZoneId, Security.CurrentUser.LanguageId, ManagerId,
ProjectId, categoryId, ShowActive, dtCompleted, dtUpcoming, orgUid, contactUid);
foreach (DataRow _dr in dt.Rows)
if (alTypes.Contains((int)_dr["ItemType"]))
{
dr = result.NewRow();
dr["GroupId"] = "";
dr["GroupName"] = "";
dr["ItemId"] = (int)_dr["ItemId"];
dr["Title"] = _dr["Title"].ToString();
dr["ItemType"] = (int)_dr["ItemType"];
dr["ManagerId"] = (int)_dr["ManagerId"];
if (_dr["ProjectId"] != DBNull.Value)
dr["ProjectId"] = (int)_dr["ProjectId"];
else
dr["ProjectId"] = 0;
if (_dr["ProjectTitle"] != DBNull.Value)
dr["ProjectTitle"] = _dr["ProjectTitle"].ToString();
else
dr["ProjectTitle"] = "";
if (_dr["StartDate"] != DBNull.Value)
dr["StartDate"] = (DateTime)_dr["StartDate"];
if (_dr["FinishDate"] != DBNull.Value)
dr["FinishDate"] = (DateTime)_dr["FinishDate"];
if (_dr["ActualStartDate"] != DBNull.Value)
dr["ActualStartDate"] = (DateTime)_dr["ActualStartDate"];
if (_dr["ActualFinishDate"] != DBNull.Value)
dr["ActualFinishDate"] = (DateTime)_dr["ActualFinishDate"];
dr["TaskTime"] = (int)_dr["TaskTime"];
dr["TotalMinutes"] = (int)_dr["TotalMinutes"];
dr["TotalApproved"] = (int)_dr["TotalApproved"];
dr["CompletionTypeId"] = (int)_dr["CompletionTypeId"];
int iPercCompleted = (int)_dr["PercentCompleted"];
dr["PercentCompleted"] = iPercCompleted;
dr["StateId"] = (iPercCompleted == 100) ? 5 : (int)_dr["StateId"];
dr["IsOverdue"] = (iPercCompleted == 100) ? false : (bool)_dr["IsOverdue"];
dr["PriorityId"] = (int)_dr["PriorityId"];
dr["PriorityName"] = _dr["PriorityName"].ToString();
dr["IsNewMessage"] = false;
Guid clientUid = Guid.Empty;
if (_dr["ContactUid"] != DBNull.Value)
clientUid = (Guid)_dr["ContactUid"];
if (_dr["OrgUid"] != DBNull.Value)
clientUid = (Guid)_dr["OrgUid"];
dr["ClientUid"] = clientUid;
dr["ContactUid"] = _dr["ContactUid"];
dr["OrgUid"] = _dr["OrgUid"];
dr["ClientName"] = _dr["ClientName"];
if (GroupBy == 5)
{
if (_dr["CategoryId"] != DBNull.Value)
dr["CategoryId"] = _dr["CategoryId"];
else
dr["CategoryId"] = 0;
dr["CategoryName"] = _dr["CategoryName"];
}
result.Rows.Add(dr);
if (GroupBy == 2 && !alGroup.Keys.Contains((int)dr["ManagerId"]))
{
int managerId = (int)dr["ManagerId"];
alGroup.Add(managerId, User.GetUserName(managerId));
}
if (GroupBy == 3 && !alGroup.Keys.Contains((int)dr["ProjectId"]))
{
alGroup.Add((int)dr["ProjectId"], dr["ProjectTitle"].ToString());
}
if (GroupBy == 4 && !alGroup.Keys.Contains(clientUid))
{
alGroup.Add(clientUid, dr["ClientName"].ToString());
}
if (GroupBy == 5 && !alGroup.Keys.Contains((int)dr["CategoryId"]))
{
alGroup.Add((int)dr["CategoryId"], dr["CategoryName"].ToString());
}
}
}
#endregion
#region Documents
if (alTypes.Contains((int)ObjectTypes.Document))
{
DataTable dt;
if (GroupBy == 5)
dt = DBDocument.GetListDocumentsForResourceViewWithCategories(ResId,
Security.CurrentUser.TimeZoneId, Security.CurrentUser.LanguageId, ManagerId,
ProjectId, categoryId, ShowActive, dtCompleted, dtUpcoming, orgUid, contactUid);
else
dt = DBDocument.GetListDocumentsForResourceViewDataTable(ResId,
Security.CurrentUser.TimeZoneId, Security.CurrentUser.LanguageId, ManagerId,
ProjectId, categoryId, ShowActive, dtCompleted, dtUpcoming, orgUid, contactUid);
foreach (DataRow _dr in dt.Rows)
{
dr = result.NewRow();
dr["GroupId"] = "";
dr["GroupName"] = "";
dr["ItemId"] = (int)_dr["ItemId"];
dr["Title"] = _dr["Title"].ToString();
dr["ItemType"] = (int)_dr["ItemType"];
dr["ManagerId"] = (int)_dr["ManagerId"];
if (_dr["ProjectId"] != DBNull.Value)
dr["ProjectId"] = (int)_dr["ProjectId"];
else
dr["ProjectId"] = 0;
if (_dr["ProjectTitle"] != DBNull.Value)
dr["ProjectTitle"] = _dr["ProjectTitle"].ToString();
else
dr["ProjectTitle"] = "";
dr["StateId"] = (int)_dr["StateId"];
dr["IsOverdue"] = false;
if (_dr["StartDate"] != DBNull.Value)
dr["StartDate"] = (DateTime)_dr["StartDate"];
if (_dr["FinishDate"] != DBNull.Value)
dr["ActualFinishDate"] = (DateTime)_dr["FinishDate"];
dr["TaskTime"] = (int)_dr["TaskTime"];
dr["TotalMinutes"] = (int)_dr["TotalMinutes"];
dr["TotalApproved"] = (int)_dr["TotalApproved"];
dr["CompletionTypeId"] = (int)_dr["CompletionTypeId"];
dr["PercentCompleted"] = (int)_dr["PercentCompleted"];
dr["PriorityId"] = (int)_dr["PriorityId"];
dr["PriorityName"] = _dr["PriorityName"].ToString();
dr["IsNewMessage"] = false;
Guid clientUid = Guid.Empty;
if (_dr["ContactUid"] != DBNull.Value)
clientUid = (Guid)_dr["ContactUid"];
if (_dr["OrgUid"] != DBNull.Value)
clientUid = (Guid)_dr["OrgUid"];
dr["ClientUid"] = clientUid;
dr["ContactUid"] = _dr["ContactUid"];
dr["OrgUid"] = _dr["OrgUid"];
dr["ClientName"] = _dr["ClientName"];
if (GroupBy == 5)
{
if (_dr["CategoryId"] != DBNull.Value)
dr["CategoryId"] = _dr["CategoryId"];
else
dr["CategoryId"] = 0;
dr["CategoryName"] = _dr["CategoryName"];
}
result.Rows.Add(dr);
if (GroupBy == 2 && !alGroup.Keys.Contains((int)dr["ManagerId"]))
{
int managerId = (int)dr["ManagerId"];
alGroup.Add(managerId, User.GetUserName(managerId));
}
if (GroupBy == 3 && !alGroup.Keys.Contains((int)dr["ProjectId"]))
{
alGroup.Add((int)dr["ProjectId"], dr["ProjectTitle"].ToString());
}
if (GroupBy == 4 && !alGroup.Keys.Contains(clientUid))
{
alGroup.Add(clientUid, dr["ClientName"].ToString());
}
if (GroupBy == 5 && !alGroup.Keys.Contains((int)dr["CategoryId"]))
{
alGroup.Add((int)dr["CategoryId"], dr["CategoryName"].ToString());
}
}
}
#endregion
#region Issues
if (alTypes.Contains((int)ObjectTypes.Issue))
{
DataTable dt;
if (GroupBy == 5)
dt = DBIncident.GetListIncidentsForResourceViewWithCategories(ResId,
Security.CurrentUser.TimeZoneId, Security.CurrentUser.LanguageId, ManagerId,
ProjectId, categoryId, ShowActive, dtCompleted, dtUpcoming, orgUid, contactUid);
else
dt = DBIncident.GetListIncidentsForResourceViewDataTable(ResId,
Security.CurrentUser.TimeZoneId, Security.CurrentUser.LanguageId, ManagerId,
ProjectId, categoryId, ShowActive, dtCompleted, dtUpcoming, orgUid, contactUid);
foreach (DataRow _dr in dt.Rows)
{
dr = result.NewRow();
dr["GroupId"] = "";
dr["GroupName"] = "";
dr["ItemId"] = (int)_dr["ItemId"];
dr["Title"] = _dr["Title"].ToString();
dr["ItemType"] = (int)_dr["ItemType"];
dr["ManagerId"] = (int)_dr["ManagerId"];
if (_dr["ProjectId"] != DBNull.Value)
dr["ProjectId"] = (int)_dr["ProjectId"];
else
dr["ProjectId"] = 0;
if (_dr["ProjectTitle"] != DBNull.Value)
dr["ProjectTitle"] = _dr["ProjectTitle"].ToString();
else
dr["ProjectTitle"] = "";
dr["StateId"] = (int)_dr["StateId"];
dr["IsOverdue"] = (bool)_dr["IsOverdue"];
if (_dr["StartDate"] != DBNull.Value)
dr["StartDate"] = (DateTime)_dr["StartDate"];
if (_dr["FinishDate"] != DBNull.Value)
dr["FinishDate"] = (DateTime)_dr["FinishDate"];
if (_dr["ActualFinishDate"] != DBNull.Value)
dr["ActualFinishDate"] = (DateTime)_dr["ActualFinishDate"];
if (_dr["ActualStartDate"] != DBNull.Value)
dr["ActualStartDate"] = (DateTime)_dr["ActualStartDate"];
dr["TaskTime"] = (int)_dr["TaskTime"];
dr["TotalMinutes"] = (int)_dr["TotalMinutes"];
dr["TotalApproved"] = (int)_dr["TotalApproved"];
dr["CompletionTypeId"] = (int)_dr["CompletionTypeId"];
dr["PercentCompleted"] = (int)_dr["PercentCompleted"];
dr["PriorityId"] = (int)_dr["PriorityId"];
dr["PriorityName"] = _dr["PriorityName"].ToString();
dr["IsNewMessage"] = (bool)_dr["IsNewMessage"];
Guid clientUid = Guid.Empty;
if (_dr["ContactUid"] != DBNull.Value)
clientUid = (Guid)_dr["ContactUid"];
if (_dr["OrgUid"] != DBNull.Value)
clientUid = (Guid)_dr["OrgUid"];
dr["ClientUid"] = clientUid;
dr["ContactUid"] = _dr["ContactUid"];
dr["OrgUid"] = _dr["OrgUid"];
dr["ClientName"] = _dr["ClientName"];
if (GroupBy == 5)
{
if (_dr["CategoryId"] != DBNull.Value)
dr["CategoryId"] = _dr["CategoryId"];
else
dr["CategoryId"] = 0;
dr["CategoryName"] = _dr["CategoryName"];
}
result.Rows.Add(dr);
if (GroupBy == 2 && !alGroup.Keys.Contains((int)dr["ManagerId"]))
{
int managerId = (int)dr["ManagerId"];
alGroup.Add(managerId, User.GetUserName(managerId));
}
if (GroupBy == 3 && !alGroup.Keys.Contains((int)dr["ProjectId"]))
{
alGroup.Add((int)dr["ProjectId"], dr["ProjectTitle"].ToString());
}
if (GroupBy == 4 && !alGroup.Keys.Contains(clientUid))
{
alGroup.Add(clientUid, dr["ClientName"].ToString());
}
if (GroupBy == 5 && !alGroup.Keys.Contains((int)dr["CategoryId"]))
{
alGroup.Add((int)dr["CategoryId"], dr["CategoryName"].ToString());
}
}
}
#endregion
#region Events
if (alTypes.Contains((int)ObjectTypes.CalendarEntry))
{
DataTable dt;
if (GroupBy == 5)
dt = DBEvent.GetListEventsForResourceViewWithCategories(ResId,
Security.CurrentUser.TimeZoneId, Security.CurrentUser.LanguageId, ManagerId,
ProjectId, categoryId, ShowActive, dtCompleted, dtUpcoming, orgUid, contactUid);
else
dt = DBEvent.GetListEventsForResourceViewDataTable(ResId,
Security.CurrentUser.TimeZoneId, Security.CurrentUser.LanguageId, ManagerId,
ProjectId, categoryId, ShowActive, dtCompleted, dtUpcoming, orgUid, contactUid);
foreach (DataRow _dr in dt.Rows)
{
if (!(bool)_dr["HasRecurrence"])
{
dr = result.NewRow();
dr["GroupId"] = "";
dr["GroupName"] = "";
dr["ItemId"] = (int)_dr["ItemId"];
dr["Title"] = _dr["Title"].ToString();
dr["ItemType"] = (int)_dr["ItemType"];
dr["ManagerId"] = (int)_dr["ManagerId"];
if (_dr["ProjectId"] != DBNull.Value)
dr["ProjectId"] = (int)_dr["ProjectId"];
else
dr["ProjectId"] = 0;
if (_dr["ProjectTitle"] != DBNull.Value)
dr["ProjectTitle"] = _dr["ProjectTitle"].ToString();
else
dr["ProjectTitle"] = "";
dr["StateId"] = (int)_dr["StateId"];
dr["IsOverdue"] = false;
if (_dr["StartDate"] != DBNull.Value)
dr["StartDate"] = (DateTime)_dr["StartDate"];
if (_dr["FinishDate"] != DBNull.Value)
dr["FinishDate"] = (DateTime)_dr["FinishDate"];
dr["TaskTime"] = (int)_dr["TaskTime"];
dr["TotalMinutes"] = (int)_dr["TotalMinutes"];
dr["TotalApproved"] = (int)_dr["TotalApproved"];
dr["CompletionTypeId"] = (int)_dr["CompletionTypeId"];
dr["PercentCompleted"] = (int)_dr["PercentCompleted"];
dr["PriorityId"] = (int)_dr["PriorityId"];
dr["PriorityName"] = _dr["PriorityName"].ToString();
dr["IsNewMessage"] = false;
Guid clientUid = Guid.Empty;
if (_dr["ContactUid"] != DBNull.Value)
clientUid = (Guid)_dr["ContactUid"];
if (_dr["OrgUid"] != DBNull.Value)
clientUid = (Guid)_dr["OrgUid"];
dr["ClientUid"] = clientUid;
dr["ContactUid"] = _dr["ContactUid"];
dr["OrgUid"] = _dr["OrgUid"];
dr["ClientName"] = _dr["ClientName"];
if (GroupBy == 5)
{
if (_dr["CategoryId"] != DBNull.Value)
dr["CategoryId"] = _dr["CategoryId"];
else
dr["CategoryId"] = 0;
dr["CategoryName"] = _dr["CategoryName"];
}
result.Rows.Add(dr);
if (GroupBy == 2 && !alGroup.Keys.Contains((int)dr["ManagerId"]))
{
int managerId = (int)dr["ManagerId"];
alGroup.Add(managerId, User.GetUserName(managerId));
}
if (GroupBy == 3 && !alGroup.Keys.Contains((int)dr["ProjectId"]))
{
alGroup.Add((int)dr["ProjectId"], dr["ProjectTitle"].ToString());
}
if (GroupBy == 4 && !alGroup.Keys.Contains(clientUid))
{
alGroup.Add(clientUid, dr["ClientName"].ToString());
}
if (GroupBy == 5 && !alGroup.Keys.Contains((int)dr["CategoryId"]))
{
alGroup.Add((int)dr["CategoryId"], dr["CategoryName"].ToString());
}
}
else // Recurrence
{
int StartTime;
int EndTime;
CalendarEntry.Recurrence recurrence;
using (IDataReader r_reader = DBCommon.GetRecurrence((int)ObjectTypes.CalendarEntry, (int)_dr["ItemId"]))
{
r_reader.Read();
recurrence = new CalendarEntry.Recurrence(
(byte)r_reader["Pattern"],
(byte)r_reader["SubPattern"],
(byte)r_reader["Frequency"],
(byte)r_reader["Weekdays"],
(byte)r_reader["DayOfMonth"],
(byte)r_reader["WeekNumber"],
(byte)r_reader["MonthNumber"],
(int)r_reader["EndAfter"],
(DateTime)_dr["StartDate"],
(DateTime)_dr["FinishDate"],
(int)r_reader["TimeZoneId"]);
StartTime = (int)r_reader["StartTime"];
EndTime = (int)r_reader["EndTime"];
}
// Get new StartDate and FinishDate for recurrence TimeZone (not UserTimeOffset)
DateTime eventStartDate = DateTime.UtcNow;
using (IDataReader r_reader = DBEvent.GetEventDates((int)_dr["ItemId"], recurrence.TimeZoneId))
{
r_reader.Read();
recurrence.StartDate = ((DateTime)r_reader["StartDate"]).Date;
recurrence.FinishDate = ((DateTime)r_reader["FinishDate"]).Date;
eventStartDate = (DateTime)r_reader["StartDate"];
}
eventStartDate = DBCommon.GetUTCDate(recurrence.TimeZoneId, eventStartDate);
// from_date, to_date - in UTC
DateTime from_date = DateTime.UtcNow.Date.AddDays(-1);
DateTime to_date = DateTime.UtcNow.Date.AddDays(2);
if (dtUpcoming < DateTime.MaxValue.AddDays(-1))
to_date = dtUpcoming;
if (dtCompleted > DateTime.MinValue.AddDays(1))
from_date = dtCompleted;
ArrayList dates = CalendarEntry.GetRecurDates(from_date, to_date, StartTime, eventStartDate, recurrence);
foreach (DateTime d in dates) // Dates in UTC (�� � �������������, ��� ������� ���������� � 00:00. ������� ���� ��� �������� StartTime)
{
DateTime UserDt = DBCommon.GetLocalDate(TimeZoneId, d); // from UTC to User's time
DateTime _StartDate = UserDt.AddMinutes(StartTime);
DateTime _FinishDate = UserDt.AddMinutes(EndTime);
dr = result.NewRow();
if (_StartDate > UserDate)
{
if (_StartDate > dtUpcomingUser) // if we don't need upcoming
continue;
dr["StateId"] = (int)ObjectStates.Upcoming;
}
else if (_FinishDate < UserDate)
{
if (_FinishDate < dtCompletedUser) // if we don't need completed
continue;
dr["StateId"] = (int)ObjectStates.Completed;
}
else
{
if (!ShowActive)
continue;
dr["StateId"] = (int)ObjectStates.Active;
}
dr["IsOverdue"] = false;
dr["GroupId"] = "";
dr["GroupName"] = "";
dr["ItemId"] = (int)_dr["ItemId"];
dr["Title"] = _dr["Title"].ToString();
dr["ItemType"] = (int)_dr["ItemType"];
dr["ManagerId"] = (int)_dr["ManagerId"];
if (_dr["ProjectId"] != DBNull.Value)
dr["ProjectId"] = (int)_dr["ProjectId"];
else
dr["ProjectId"] = 0;
if (_dr["ProjectTitle"] != DBNull.Value)
dr["ProjectTitle"] = _dr["ProjectTitle"].ToString();
else
dr["ProjectTitle"] = "";
dr["StartDate"] = _StartDate;
dr["FinishDate"] = _FinishDate;
dr["TaskTime"] = (int)_dr["TaskTime"];
dr["TotalMinutes"] = (int)_dr["TotalMinutes"];
dr["TotalApproved"] = (int)_dr["TotalApproved"];
dr["CompletionTypeId"] = (int)_dr["CompletionTypeId"];
dr["PercentCompleted"] = (int)_dr["PercentCompleted"];
dr["PriorityId"] = (int)_dr["PriorityId"];
dr["PriorityName"] = _dr["PriorityName"].ToString();
dr["IsNewMessage"] = false;
Guid clientUid = Guid.Empty;
if (_dr["ContactUid"] != DBNull.Value)
clientUid = (Guid)_dr["ContactUid"];
if (_dr["OrgUid"] != DBNull.Value)
clientUid = (Guid)_dr["OrgUid"];
dr["ClientUid"] = clientUid;
dr["ContactUid"] = _dr["ContactUid"];
dr["OrgUid"] = _dr["OrgUid"];
dr["ClientName"] = _dr["ClientName"];
if (GroupBy == 5)
{
if (_dr["CategoryId"] != DBNull.Value)
dr["CategoryId"] = _dr["CategoryId"];
else
dr["CategoryId"] = 0;
dr["CategoryName"] = _dr["CategoryName"];
}
result.Rows.Add(dr);
if (GroupBy == 2 && !alGroup.Keys.Contains((int)dr["ManagerId"]))
{
int managerId = (int)dr["ManagerId"];
alGroup.Add(managerId, User.GetUserName(managerId));
}
if (GroupBy == 3 && !alGroup.Keys.Contains((int)dr["ProjectId"]))
{
alGroup.Add((int)dr["ProjectId"], dr["ProjectTitle"].ToString());
}
if (GroupBy == 4 && !alGroup.Keys.Contains(clientUid))
{
alGroup.Add(clientUid, dr["ClientName"].ToString());
}
if (GroupBy == 5 && !alGroup.Keys.Contains((int)dr["CategoryId"]))
{
alGroup.Add((int)dr["CategoryId"], dr["CategoryName"].ToString());
}
}
}
}
}
#endregion
if (GroupBy > 1)
{
#region Grouping
DataTable dt_clone = result.Clone();
string str_grouping = "";
if (GroupBy == 2)
str_grouping = "ManagerId";
else if (GroupBy == 3)
str_grouping = "ProjectId";
else if (GroupBy == 4)
str_grouping = "ClientUid";
else if (GroupBy == 5)
str_grouping = "CategoryId";
foreach (KeyValuePair<object, string> kvp in alGroup)
{
string group_name = kvp.Value;
if (GroupBy == 3 && kvp.Key.ToString() == "0")
group_name = Common.GetWebResourceString("{IbnFramework.Project:NoProject}");
if (GroupBy == 4 && kvp.Key.ToString() == Guid.Empty.ToString())
group_name = Common.GetWebResourceString("{IbnFramework.Project:NoClient}");
if (GroupBy == 5 && kvp.Key.ToString() == "0")
group_name = Common.GetWebResourceString("{IbnFramework.Project:NoCategory}");
dr = dt_clone.NewRow();
dr["GroupId"] = kvp.Key.ToString();
dr["GroupName"] = group_name;
dr["ItemId"] = 0;
dr["Title"] = "";
dr["ItemType"] = 0;
dr["ManagerId"] = 0;
if (GroupBy == 3) // By Project
dr["ProjectId"] = (int)kvp.Key;
else
dr["ProjectId"] = 0;
dr["ProjectTitle"] = "";
dr["StateId"] = 0;
dr["CompletionTypeId"] = 0;
dr["PercentCompleted"] = 0;
dr["PriorityId"] = 0;
dr["PriorityName"] = "";
dr["IsOverdue"] = false;
dr["IsNewMessage"] = false;
dt_clone.Rows.Add(dr);
DataRow[] dr_items = result.Select(str_grouping + "='" + kvp.Key.ToString() + "'");
foreach (DataRow dr1 in dr_items)
{
DataRow _dr = dt_clone.NewRow();
_dr.ItemArray = (Object[])dr1.ItemArray.Clone();
_dr["GroupId"] = kvp.Key.ToString();
_dr["GroupName"] = group_name;
dt_clone.Rows.Add(_dr);
}
}
#endregion
return dt_clone;
}
else
{
return result;
}
}
/// <summary>
/// GroupId, GroupName, ItemId, Title, ItemType, ManagerId, ProjectId, StateId,
/// StartDate, FinishDate, CompletionTypeId, PercentCompleted,
/// PriorityId, PriorityName, IsOverdue, IsNewMessage, ItemCode
/// </summary>
/// <returns></returns>
public static DataTable GetGroupedItemsByCategoryDataTable(int PrincipalId,
int ManagerId, int ProjectId, bool ShowActive, ArrayList alTypes,
DateTime dtCompleted, DateTime dtUpcoming)
{
DataTable result = new DataTable();
result.Columns.Add(new DataColumn("GroupId", typeof(int)));
result.Columns.Add(new DataColumn("GroupName", typeof(string)));
result.Columns.Add(new DataColumn("ItemId", typeof(int)));
result.Columns.Add(new DataColumn("Title", typeof(string)));
result.Columns.Add(new DataColumn("ItemType", typeof(int)));
result.Columns.Add(new DataColumn("ManagerId", typeof(int)));
result.Columns.Add(new DataColumn("ProjectId", typeof(int)));
result.Columns.Add(new DataColumn("StateId", typeof(int)));
result.Columns.Add(new DataColumn("FinishDate", typeof(DateTime)));
result.Columns.Add(new DataColumn("StartDate", typeof(DateTime)));
result.Columns.Add(new DataColumn("CompletionTypeId", typeof(int)));
result.Columns.Add(new DataColumn("PercentCompleted", typeof(int)));
result.Columns.Add(new DataColumn("PriorityId", typeof(int)));
result.Columns.Add(new DataColumn("PriorityName", typeof(string)));
result.Columns.Add(new DataColumn("IsOverdue", typeof(bool)));
result.Columns.Add(new DataColumn("IsNewMessage", typeof(bool)));
result.Columns.Add(new DataColumn("ItemCode", typeof(string)));
DataRow dr;
Hashtable htCategories = new Hashtable();
int TimeZoneId = Security.CurrentUser.TimeZoneId;
DateTime UserDate = DBCommon.GetLocalDate(TimeZoneId, DateTime.UtcNow); // current User's datetime
dtCompleted = DBCommon.GetUTCDate(Security.CurrentUser.TimeZoneId, dtCompleted);
dtUpcoming = DBCommon.GetUTCDate(Security.CurrentUser.TimeZoneId, dtUpcoming);
DataTable dt = DBToDo.GetListToDoAndTasksForManagerViewGroupedByCategory(PrincipalId,
Security.CurrentUser.TimeZoneId, Security.CurrentUser.LanguageId, ManagerId,
ProjectId, ShowActive, dtCompleted, dtUpcoming);
foreach (DataRow _dr in dt.Rows)
{
if (alTypes.Contains((int)_dr["ItemType"]))
{
if (!(bool)_dr["HasRecurrence"])
{
dr = result.NewRow();
dr["GroupId"] = (int)_dr["CategoryId"];
dr["GroupName"] = _dr["CategoryName"].ToString();
dr["ItemId"] = (int)_dr["ItemId"];
dr["Title"] = _dr["Title"].ToString();
dr["ItemType"] = (int)_dr["ItemType"];
dr["ManagerId"] = (int)_dr["ManagerId"];
if (_dr["ProjectId"] != DBNull.Value)
dr["ProjectId"] = (int)_dr["ProjectId"];
else
dr["ProjectId"] = 0;
dr["StateId"] = (int)_dr["StateId"];
dr["IsOverdue"] = (bool)_dr["IsOverdue"];
if (_dr["StartDate"] != DBNull.Value)
dr["StartDate"] = (DateTime)_dr["StartDate"];
if (_dr["FinishDate"] != DBNull.Value)
dr["FinishDate"] = (DateTime)_dr["FinishDate"];
dr["CompletionTypeId"] = (int)_dr["CompletionTypeId"];
dr["PercentCompleted"] = (int)_dr["PercentCompleted"];
dr["PriorityId"] = (int)_dr["PriorityId"];
dr["PriorityName"] = _dr["PriorityName"].ToString();
dr["IsNewMessage"] = (bool)_dr["IsNewMessage"];
dr["ItemCode"] = (string)_dr["ItemCode"];
result.Rows.Add(dr);
if (!htCategories.ContainsKey((int)dr["GroupId"]))
htCategories.Add((int)dr["GroupId"], dr["GroupName"]);
}
else // Recurrence
{
int StartTime;
int EndTime;
CalendarEntry.Recurrence recurrence;
using (IDataReader r_reader = DBCommon.GetRecurrence((int)ObjectTypes.CalendarEntry, (int)_dr["ItemId"]))
{
r_reader.Read();
recurrence = new CalendarEntry.Recurrence(
(byte)r_reader["Pattern"],
(byte)r_reader["SubPattern"],
(byte)r_reader["Frequency"],
(byte)r_reader["Weekdays"],
(byte)r_reader["DayOfMonth"],
(byte)r_reader["WeekNumber"],
(byte)r_reader["MonthNumber"],
(int)r_reader["EndAfter"],
(DateTime)_dr["StartDate"],
(DateTime)_dr["FinishDate"],
(int)r_reader["TimeZoneId"]);
StartTime = (int)r_reader["StartTime"];
EndTime = (int)r_reader["EndTime"];
}
// Get new StartDate and FinishDate for recurrence TimeZone (not UserTimeOffset)
DateTime eventStartDate = DateTime.UtcNow;
using (IDataReader r_reader = DBEvent.GetEventDates((int)_dr["ItemId"], recurrence.TimeZoneId))
{
r_reader.Read();
recurrence.StartDate = ((DateTime)r_reader["StartDate"]).Date;
recurrence.FinishDate = ((DateTime)r_reader["FinishDate"]).Date;
eventStartDate = (DateTime)r_reader["StartDate"];
}
eventStartDate = DBCommon.GetUTCDate(recurrence.TimeZoneId, eventStartDate);
// from_date, to_date - in UTC
DateTime from_date = DateTime.UtcNow.Date.AddDays(-1);
DateTime to_date = DateTime.UtcNow.Date.AddDays(2);
if (dtUpcoming < DateTime.MaxValue.AddDays(-1))
to_date = dtUpcoming;
if (dtCompleted > DateTime.MinValue.AddDays(1))
from_date = dtCompleted;
ArrayList dates = CalendarEntry.GetRecurDates(from_date, to_date, StartTime, eventStartDate, recurrence);
foreach (DateTime d in dates) // Dates in UTC (�� � �������������, ��� ������� ���������� � 00:00. ������� ���� ��� �������� StartTime)
{
DateTime UserDt = DBCommon.GetLocalDate(TimeZoneId, d); // from UTC to User's time
DateTime _StartDate = UserDt.AddMinutes(StartTime);
DateTime _FinishDate = UserDt.AddMinutes(EndTime);
dr = result.NewRow();
if (_StartDate > UserDate)
{
if (dtUpcoming >= DateTime.MaxValue.AddDays(-1)) // if we don't need upcoming
continue;
dr["StateId"] = (int)ObjectStates.Upcoming;
}
else if (_FinishDate < UserDate)
{
if (dtCompleted <= DateTime.MinValue.AddDays(1)) // if we don't need completed
continue;
dr["StateId"] = (int)ObjectStates.Completed;
}
else
{
if (!ShowActive)
continue;
dr["StateId"] = (int)ObjectStates.Active;
}
dr["IsOverdue"] = false;
dr["GroupId"] = (int)_dr["CategoryId"];
dr["GroupName"] = _dr["CategoryName"].ToString();
dr["ItemId"] = (int)_dr["ItemId"];
dr["Title"] = _dr["Title"].ToString();
dr["ItemType"] = (int)_dr["ItemType"];
dr["ManagerId"] = (int)_dr["ManagerId"];
if (_dr["ProjectId"] != DBNull.Value)
dr["ProjectId"] = (int)_dr["ProjectId"];
else
dr["ProjectId"] = 0;
dr["StartDate"] = _StartDate;
dr["FinishDate"] = _FinishDate;
dr["CompletionTypeId"] = (int)_dr["CompletionTypeId"];
dr["PercentCompleted"] = (int)_dr["PercentCompleted"];
dr["PriorityId"] = (int)_dr["PriorityId"];
dr["PriorityName"] = _dr["PriorityName"].ToString();
dr["IsNewMessage"] = false;
dr["ItemCode"] = String.Empty;
result.Rows.Add(dr);
if (!htCategories.ContainsKey((int)dr["GroupId"]))
htCategories.Add((int)dr["GroupId"], dr["GroupName"]);
}
}
}
}
#region Grouping
DataTable dt_clone = result.Clone();
ArrayList alCats = new ArrayList();
foreach (int Id in htCategories.Keys)
alCats.Add(Id);
alCats.Sort();
foreach (int Id in alCats)
{
string group_name = htCategories[Id].ToString();
dr = dt_clone.NewRow();
dr["GroupId"] = Id;
dr["GroupName"] = group_name;
dr["ItemId"] = 0;
dr["Title"] = "";
dr["ItemType"] = 0;
dr["ManagerId"] = Id;
dr["ProjectId"] = 0;
dr["StateId"] = 0;
dr["CompletionTypeId"] = 0;
dr["PercentCompleted"] = 0;
dr["PriorityId"] = 0;
dr["PriorityName"] = "";
dr["IsOverdue"] = false;
dr["IsNewMessage"] = false;
dr["ItemCode"] = String.Empty;
dt_clone.Rows.Add(dr);
DataRow[] dr_items = result.Select("GroupId=" + Id);
foreach (DataRow dr1 in dr_items)
{
DataRow _dr = dt_clone.NewRow();
_dr.ItemArray = (Object[])dr1.ItemArray.Clone();
dt_clone.Rows.Add(_dr);
}
}
#endregion
return dt_clone;
}