public IEnumerable<ModuleStatusItem> GetDetailsByDistrict(Period period, string sdst, string PA) { throw new NotImplementedException();}
public IEnumerable<ModuleStatusItem> GetDetailsByArea(Period period, ReportingScope currentScope, string areaName) { if (currentScope == ReportingScope.Company) { string companyQuery = @" SELECT [Tracking].[Region], SUM(CAST(CASE WHEN [Id] IS NOT NULL AND [Responded] = 0 THEN 1 ELSE 0 END AS INT)) AS [NotStarted], SUM(CAST(CASE WHEN [Id] IS NOT NULL AND [Completed] = 1 THEN 1 ELSE 0 END AS INT)) AS [Completed], SUM(CAST(CASE WHEN [Id] IS NOT NULL AND [Responded] = 1 AND [Completed] != 1 THEN 1 ELSE 0 END AS INT)) AS [Started] FROM ( SELECT [Region].[Region], [Region].[RegionID], [QuizAssignment].[Id], CASE WHEN [Response].[EmployeeQuizAssignmentId] IS NOT NULL THEN 1 ELSE 0 END AS [Responded], [QuizAssignment].[Completed] FROM [dbo].[RegionDefinition] AS [Region] LEFT OUTER JOIN ( [dbo].[EmployeeQuizAssignments] AS [QuizAssignment] INNER JOIN [dbo].[QuizDefinition] AS [Quiz] ON [Quiz].[QuizDefinitionID] = [QuizAssignment].[QuizDefinitionID] INNER JOIN [dbo].[PeriodCalendar] AS [Calendar] ON [Calendar].[FirstDate] <= [QuizAssignment].[DueDate] AND [Calendar].[LastDate] >= [QuizAssignment].[DueDate] INNER JOIN [dbo].[EmployeeDetail] AS [Employee] ON [Employee].[EmployeeID] = [QuizAssignment].[EmployeeId] LEFT OUTER JOIN [dbo].[EmployeeAssignment] AS [Assignment] ON [Assignment].[EmployeeID] = [Employee].[EmployeeID] AND [Assignment].[Period] = [Calendar].[Period] AND [Assignment].[PeriodYear] = [Calendar].[PeriodYear] LEFT OUTER JOIN [dbo].[Response] ON [Response].[EmployeeQuizAssignmentId] = [QuizAssignment].[Id] ) ON [Region].[PA] = CASE WHEN [Assignment].[PA] IS NULL OR [Assignment].[PA] = '' THEN [Employee].[PA] ELSE [Assignment].[PA] END AND [Calendar].[Period] = @Period AND [Calendar].[PeriodYear] = @PeriodYear GROUP BY [Region].[Region], [Region].[RegionID], [QuizAssignment].Id, [Response].[EmployeeQuizAssignmentId], [QuizAssignment].[Completed] ) AS [Tracking] GROUP BY [Tracking].[Region], [Tracking].[RegionID] ORDER BY CASE WHEN [Tracking].[RegionID] = 1 THEN 1 WHEN [Tracking].[RegionID] = 2 THEN 3 WHEN [Tracking].[RegionID] = 3 THEN 2 WHEN [Tracking].[RegionID] = 4 THEN 4 WHEN [Tracking].[RegionID] = 5 THEN 5 END; "; IEnumerable<ModuleStatusItem> result = new List<ModuleStatusItem>(); result = DBHelper.ExecuteReader( new SqlConnection(DBHelper.ConnectionString), new SqlCommand(companyQuery), delegate(IDataReader reader) { List<ModuleStatusItem> Items = new List<ModuleStatusItem>(); while (reader.Read()) { Items.Add( new ModuleStatusItem( reader.GetString(reader.GetOrdinal("Region")), reader.GetInt32(reader.GetOrdinal("Completed")), reader.GetInt32(reader.GetOrdinal("Started")), reader.GetInt32(reader.GetOrdinal("NotStarted")))); } return Items; }, new SqlParameter() { ParameterName = "@Period", DbType = DbType.String, Size = 100, Value = period.Text }, new SqlParameter() { ParameterName = "@PeriodYear", DbType = DbType.Int16, Value = period.Year }); foreach (ModuleStatusItem item in result) { if (item.ID.LastIndexOf(' ') >= 0 && item.ID.Substring(item.ID.LastIndexOf(' ') + 1) == "Region") { item.ID = item.ID.Substring(0, item.ID.LastIndexOf(' ')); // No, because one of the regions has an embedded space in its name, South East } } return result; } else if (currentScope == ReportingScope.Region) { string regionQuery = @" SELECT Zone, SUM(CAST(CASE WHEN Id IS NOT NULL AND [Responded] = 0 THEN 1 ELSE 0 END AS INT)) AS [NotStarted], SUM(CAST(CASE WHEN Id IS NOT NULL AND [Completed] = 1 THEN 1 ELSE 0 END AS INT)) AS [Completed], SUM(CAST(CASE WHEN Id IS NOT NULL AND [Responded] = 1 AND [Completed] != 1 THEN 1 ELSE 0 END AS INT)) AS [Started] FROM ( SELECT Zone.[Abbrev] AS Zone, QuizAssignment.Id, CASE WHEN Response.[EmployeeQuizAssignmentId] IS NOT NULL THEN 1 ELSE 0 END AS [Responded], QuizAssignment.[Completed] FROM [dbo].[RegionDefinition] AS Region JOIN [dbo].[ZoneName] AS Zone ON Zone.[PA] = Region.[PA] LEFT OUTER JOIN ( [dbo].[EmployeeQuizAssignments] AS QuizAssignment JOIN [dbo].[QuizDefinition] AS Quiz ON Quiz.[QuizDefinitionID] = QuizAssignment.[QuizDefinitionID] JOIN [dbo].[PeriodCalendar] AS Calendar ON Calendar.[FirstDate] <= QuizAssignment.[DueDate] AND Calendar.[LastDate] >= QuizAssignment.[DueDate] JOIN [dbo].[EmployeeDetail] AS Employee ON Employee.[EmployeeID] = QuizAssignment.[EmployeeId] LEFT OUTER JOIN [dbo].[Response] ON Response.[EmployeeQuizAssignmentId] = QuizAssignment.[Id] LEFT OUTER JOIN [dbo].[EmployeeAssignment] AS Assignment ON Assignment.[EmployeeID] = Employee.[EmployeeID] AND Assignment.Period = Calendar.Period AND Assignment.PeriodYear = Calendar.PeriodYear ) ON Zone.[PA] = CASE WHEN Assignment.[PA] IS NULL OR Assignment.[PA] = '' THEN Employee.[PA] ELSE Assignment.[PA] END AND Calendar.[Period] = @Period AND Calendar.[PeriodYear] = @PeriodYear WHERE Region.[Region] = @AreaName GROUP BY Zone.[Abbrev], QuizAssignment.Id, Response.[EmployeeQuizAssignmentId], QuizAssignment.[Completed] ) AS Tracking GROUP BY Zone "; return DBHelper.ExecuteReader( new SqlConnection(DBHelper.ConnectionString), new SqlCommand(regionQuery), delegate(IDataReader reader) { List<ModuleStatusItem> Items = new List<ModuleStatusItem>(); while (reader.Read()) { Items.Add( new ModuleStatusItem( reader.GetString(reader.GetOrdinal("Zone")), reader.GetInt32(reader.GetOrdinal("Completed")), reader.GetInt32(reader.GetOrdinal("Started")), reader.GetInt32(reader.GetOrdinal("NotStarted")))); } return Items; }, new SqlParameter() { ParameterName = "@Period", DbType = DbType.String, Size = 100, Value = period.Text }, new SqlParameter() { ParameterName = "@PeriodYear", DbType = DbType.Int16, Value = period.Year }, new SqlParameter() { ParameterName = "@AreaName", DbType = DbType.String, Size = 100, Value = areaName }); } else if (currentScope == ReportingScope.Zone) { string zoneQuery = @"GetDetailsByZone"; return DBHelper.ExecuteReader( new SqlConnection(DBHelper.ConnectionString), new SqlCommand(zoneQuery){CommandType=CommandType.StoredProcedure}, delegate(IDataReader reader) { List<ModuleStatusItem> Items = new List<ModuleStatusItem>(); while (reader.Read()) { Items.Add( new ModuleStatusItem( reader.GetString(reader.GetOrdinal("District")), reader.GetInt32(reader.GetOrdinal("Completed")), reader.GetInt32(reader.GetOrdinal("Started")), reader.GetInt32(reader.GetOrdinal("NotStarted")))); } return Items; }, new SqlParameter() { ParameterName = "@Period", DbType = DbType.String, Size = 100, Value = period.Text }, new SqlParameter() { ParameterName = "@PeriodYear", DbType = DbType.Int16, Value = period.Year }, new SqlParameter() { ParameterName = "@AreaName", DbType = DbType.String, Size = 100, Value = areaName }); } else if (currentScope == ReportingScope.District) { string districtQuery = @"GetDetailsByDistrict"; return DBHelper.ExecuteReader(new SqlConnection(DBHelper.ConnectionString), new SqlCommand(districtQuery){CommandType = CommandType.StoredProcedure}, delegate(IDataReader reader) { List<ModuleStatusItem> Items = new List<ModuleStatusItem>(); while (reader.Read()) { Items.Add(new DistrictModuleStatusItem(reader.GetString(reader.GetOrdinal("EmployeeID")), string.Format("{0} {1}", reader.GetString(reader.GetOrdinal("Firstname")), reader.GetString(reader.GetOrdinal("Lastname"))), reader.GetString(reader.GetOrdinal("QuizTopic")), reader.GetInt32(reader.GetOrdinal("Completed")), reader.GetInt32(reader.GetOrdinal("Started")), reader.GetInt32(reader.GetOrdinal("NotStarted")), reader.GetDateTime(reader.GetOrdinal("DueDate")), reader.GetInt32(reader.GetOrdinal("Responses")), reader.GetInt32(reader.GetOrdinal("Correct")), reader.GetInt32(reader.GetOrdinal("NumCorrectRequired")), reader.GetString(reader.GetOrdinal("AssignedBy")))); } return Items; }, new SqlParameter() { ParameterName = "@Period", DbType = DbType.String, Size = 100, Value = period.Text }, new SqlParameter() { ParameterName = "@PeriodYear", DbType = DbType.Int16, Value = period.Year }, new SqlParameter() { ParameterName = "@AreaName", DbType = DbType.String, Value = areaName }); } throw new NotImplementedException("This method has not yet been implemented for the passed currentScope."); }