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