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.");
}