public ReportingArea(string districtCode, string zoneCode, string zoneName, int? regionID, string regionName, ReportingScope scope)
{
DistrictCode = districtCode;
ZoneCode = zoneCode;
ZoneName = zoneName;
RegionID = regionID;
RegionName = regionName;
Scope = scope;
}
public object EffectiveAreaNameAndScope(out ReportingScope effectiveScope)
{
switch (Scope)
{
case ReportingScope.NationalAccount:
case ReportingScope.RegionalAccount:
case ReportingScope.Company:
effectiveScope = ReportingScope.Company;
return null;
case ReportingScope.Region:
if (!string.IsNullOrEmpty(RegionName))
{
effectiveScope = ReportingScope.Region;
return RegionName;
}
effectiveScope = ReportingScope.Company;
return null;
case ReportingScope.Zone:
if (!string.IsNullOrEmpty(ZoneName))
{
effectiveScope = ReportingScope.Zone;
return ZoneName;
}
goto case ReportingScope.Region;
case ReportingScope.District:
if (!string.IsNullOrEmpty(DistrictCode))
{
effectiveScope = ReportingScope.District;
return DistrictCode;
}
goto case ReportingScope.Zone;
default:
effectiveScope = Scope;
return null;
//case ReportingScope.District:
// return DistrictCode;
//case ReportingScope.Zone:
// return ZoneCode;
//case ReportingScope.Region:
// return RegionID.HasValue ? (object)RegionID : (object)ZoneCode;
//default:
// return null;
}
}
/// <summary>
/// Gets my work data for resources.
/// </summary>
/// <param name="resources">The resources.</param>
/// <param name="reportingScope">The reporting scope.</param>
/// <param name="startDate">The start date.</param>
/// <param name="dueDate">The due date.</param>
/// <returns></returns>
private DataTable GetMyWorkDataForResources(IEnumerable <SPFieldUserValue> resources,
ReportingScope reportingScope,
DateTime startDate, DateTime dueDate)
{
DataTable dataTable;
using (var myWorkReportData = new MyWorkReportData(Web.Site.ID))
{
dataTable =
myWorkReportData.GetData(new Dictionary <string, IEnumerable <object> >
{
{ "AssignedToId", from r in resources select(object) r.LookupId },
{ "StartDate", new[] { (object)startDate } },
{ "DueDate", new[] { (object)dueDate } },
}, reportingScope, Web);
}
return(dataTable);
}
public string ParentAreaNameFor(string area, ReportingScope parentScope)
{
if (parentScope == ReportingScope.Region)
{
string zone = area;
string query = @"
SELECT Region.[Region]
FROM [dbo].[RegionDefinition] AS Region
JOIN [dbo].[ZoneName] AS Zone
ON Zone.[PA] = Region.[PA]
WHERE Zone.[Abbrev] = @ZoneName
GROUP BY Region.[Region]
";
return
DBHelper.ExecuteReader(
new SqlConnection(DBHelper.ConnectionString),
new SqlCommand(query),
delegate(IDataReader reader)
{
if (reader.Read())
{
return reader.GetString(reader.GetOrdinal("Region"));
}
return null;
},
new SqlParameter() { ParameterName = "@ZoneName", DbType = DbType.String, Size = 100, Value = area });
}
else if (parentScope == ReportingScope.Zone)
{
string district = area;
string query = @"
SELECT Zone.[Abbrev] AS Zone
FROM [dbo].[ZoneName] AS Zone
JOIN [dbo].[EmployeeAssignment] Assignment
ON Assignment.[PA] = Zone.[PA]
WHERE (Assignment.[SDst] = @DistrictName OR (Assignment.[SDst] +'-'+ Assignment.[PA])=@DistrictName)
GROUP BY Zone.[Abbrev]
";
return
DBHelper.ExecuteReader(
new SqlConnection(DBHelper.ConnectionString),
new SqlCommand(query),
delegate(IDataReader reader)
{
if (reader.Read())
{
return reader.GetString(reader.GetOrdinal("Zone"));
}
return null;
},
new SqlParameter() { ParameterName = "@DistrictName", DbType = DbType.String, Size = 100, Value = area });
}
throw new NotImplementedException("This method has not yet been implemented for the passed currentScope.");
}
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.");
}
public IEnumerable<Period> GetPeriodsByScope(ReportingScope currentScope)
{
string periodQuery = @"
SELECT Period.[Period], Period.[PeriodYear]
FROM [dbo].[EmployeeQuizAssignments] AS Assignment
JOIN [dbo].[PeriodCalendar] AS Period
ON Period.[FirstDate] <= Assignment.[DueDate]
AND Period.[LastDate] >= Assignment.[DueDate]
WHERE Assignment.Active = 1
GROUP BY Period.[Period], Period.[PeriodYear], [Period].[FirstDate]
ORDER BY [Period].[FirstDate]
";
return
DBHelper.ExecuteReader(new SqlConnection(DBHelper.ConnectionString), new SqlCommand(periodQuery), LoadPeriods);
}
/// <summary>
/// Copied from Training and modified to fit the usage for this application. Removed code is commented out.
/// </summary>
public string GetAreaName(ReportingScope scope, string ID)
{
string result = "Area Name not found";
if ((scope == ReportingScope.District || scope == ReportingScope.ReportsTo || scope == ReportingScope.NationalAccount || scope == ReportingScope.RegionalAccount)
&& ID.Length > 10 && ID.Substring(0, 10) == "EmployeeID")
{
// We receive an employee ID from ORC, need to get SDst.
ID = ID.Substring(ID.LastIndexOf('=') + 1);//.PadLeft(8, '0');
if (scope == ReportingScope.District)
{
//EmployeeDetail emp = EmployeeDetail(ID);
//ID = emp.SDst;
string districtQuery = @"
SELECT SDst
FROM dbo.EmployeeDetail
WHERE EmployeeID = @EmployeeID";
string SDst =
DBHelper.ExecuteReader(new SqlConnection(DBHelper.ConnectionString), new SqlCommand(districtQuery),
delegate(IDataReader reader)
{
if (reader.Read() && !reader.IsDBNull(reader.GetOrdinal("SDst")))
{
return reader.GetString(reader.GetOrdinal("SDst"));
}
return null;
},
new SqlParameter() { ParameterName = "@EmployeeID", DbType = DbType.String, Size = 100, Value = ID });
if (SDst != null)
{
ID = SDst;
}
}
}
StringBuilder sb = new StringBuilder();
switch (scope)
{
case ReportingScope.Company:
result = "Company: ";
sb.AppendLine("SELECT TOP(1)");
sb.AppendLine(" CompanyCode");
sb.AppendLine("FROM");
sb.AppendLine(" EmployeeDetail");
sb.AppendLine("WHERE");
sb.AppendLine(" CoCd = @ID;");
break;
case ReportingScope.Region:
result = "Region: ";
// ID is PersNo or EmployeeID
sb.AppendLine("SELECT");
sb.AppendLine(" rd.Region");
sb.AppendLine("FROM");
sb.AppendLine(" EmployeeDetail AS ed");
sb.AppendLine("INNER JOIN");
sb.AppendLine(" RegionDefinition AS rd");
sb.AppendLine(" ON rd.PA = ed.PA");
sb.AppendLine("WHERE");
sb.AppendLine(" ed.EmployeeID = @ID;");
break;
case ReportingScope.Zone:
result = "Zone: ";
sb.AppendLine("SELECT TOP(1)");
sb.AppendLine(" PersonnelArea");
sb.AppendLine("FROM");
sb.AppendLine(" EmployeeDetail");
sb.AppendLine("INNER JOIN");
sb.AppendLine(" ZoneName");
sb.AppendLine(" ON ZoneName.PA = EmployeeDetail.PA");
sb.AppendLine("WHERE");
sb.AppendLine(" PersonnelArea <> ''");
sb.AppendLine(" AND ZoneName.Abbrev = @ID");
sb.AppendLine("UNION ALL");
sb.AppendLine("SELECT TOP(1)");
sb.AppendLine(" PersonnelArea");
sb.AppendLine("FROM");
sb.AppendLine(" EmployeeDetail");
sb.AppendLine("WHERE");
sb.AppendLine(" PersonnelArea <> ''");
sb.AppendLine(" AND PA = @ID");
break;
case ReportingScope.District:
result = "District: " + ID;
break;
case ReportingScope.ReportsTo:
result = "Reports to: " + ID;
break;
case ReportingScope.NationalAccount:
result = "National Account: ";
sb.AppendLine("SELECT TOP(1)");
sb.AppendLine(" rpt.Account");
sb.AppendLine("FROM");
sb.AppendLine(" EmployeeDetail AS ed");
sb.AppendLine("INNER JOIN");
sb.AppendLine(" Reporting AS rpt");
sb.AppendLine(" ON rpt.EmployeeID = ed.EmployeeID");
sb.AppendLine("WHERE");
sb.AppendLine(" ed.EmployeeID = @ID OR ed.PersNo = @ID");
break;
case ReportingScope.RegionalAccount:
result = "Regional Account: ";
sb.AppendLine("SELECT TOP(1)");
sb.AppendLine(" rpt.Account");
sb.AppendLine("FROM");
sb.AppendLine(" EmployeeDetail AS ed");
sb.AppendLine("INNER JOIN");
sb.AppendLine(" Reporting AS rpt");
sb.AppendLine(" ON rpt.EmployeeID = ed.EmployeeID");
sb.AppendLine("WHERE");
sb.AppendLine(" ed.EmployeeID = @ID OR ed.PersNo = @ID");
break;
}
if (sb.Length > 0)
{
string name =
DBHelper.ExecuteReader(
new SqlConnection(DBHelper.ConnectionString),
new SqlCommand(sb.ToString()),
delegate(IDataReader reader)
{
object scalarResult = null;
if (reader.Read())
{
scalarResult = reader[0];
}
if (scalarResult == null && scope == ReportingScope.Region)
{
// Sometime ID is EmployeeID and sometimes PersNo :(. Got nothing with EmployeeID, so get PersNo instead
StringBuilder sb2 = new StringBuilder();
sb2.AppendLine("SELECT");
sb2.AppendLine(" rd.Region");
sb2.AppendLine("FROM");
sb2.AppendLine(" EmployeeDetail AS ed");
sb2.AppendLine("INNER JOIN");
sb2.AppendLine(" RegionDefinition AS rd");
sb2.AppendLine(" ON rd.PA = ed.PA");
sb2.AppendLine("WHERE");
sb2.AppendLine(" ed.PersNo = @ID;");
scalarResult =
DBHelper.ExecuteReader(
new SqlConnection(DBHelper.ConnectionString),
new SqlCommand(sb2.ToString()),
(Func<IDataReader, object>)delegate(IDataReader subReader)
{
if (subReader.Read())
{
return subReader[0];
}
return null;
},
new SqlParameter() { ParameterName = "@ID", DbType = DbType.String, Size = 100, Value = ID });
}
return scalarResult != null ? scalarResult.ToString() : string.Empty;
},
new SqlParameter() { ParameterName = "@ID", DbType = DbType.String, Value = ID });
result += name;
//using (SqlConnection conn = new SqlConnection(DBHelper.ConnectionString))
//{
// using (SqlCommand cmd = new SqlCommand())
// {
// cmd.Connection = conn;
// cmd.CommandText = sb.ToString();
// cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.VarChar)).Value = ID;
// if (cmd.Connection.State != ConnectionState.Open)
// {
// cmd.Connection.Open();
// }
// object scalarResult = cmd.ExecuteScalar();
// if (scalarResult == null && scope == ReportingScope.Region)
// {
// // Sometime ID is EmployeeID and sometimes PersNo :(. Got nothing with EmployeeID, so get PersNo instead
// sb.Clear();
// sb.AppendLine("SELECT");
// sb.AppendLine(" rd.Region");
// sb.AppendLine("FROM");
// sb.AppendLine(" EmployeeDetail AS ed");
// sb.AppendLine("INNER JOIN");
// sb.AppendLine(" RegionDefinition AS rd");
// sb.AppendLine(" ON rd.PA = ed.PA");
// sb.AppendLine("WHERE");
// sb.AppendLine(" ed.PersNo = @ID;");
// if (cmd.Connection.State != ConnectionState.Open)
// {
// cmd.Connection.Open();
// }
// scalarResult = cmd.ExecuteScalar();
// }
// if (scalarResult != null)
// {
// result += scalarResult.ToString();
// }
// else
// {
// result += "";
// }
// }
//}
}
return result;
}
public string ParentAreaNameFor(string area, ReportingScope parentScope)
{
string sproc;
switch (parentScope)
{
case ReportingScope.Region:
sproc = "ReportingGetParentAreaNameForRegion";
break;
case ReportingScope.Zone:
sproc = "ReportingGetParentAreaNameForZone";
break;
default:
throw new NotImplementedException("This method has not yet been implemented for the passed currentScope.");
}
using (var connection = DBHelper.DefaultConnection)
{
return connection.CreateCommand()
.ForProcedure(sproc, p => p.WithParam("@AreaName", area, size: 100, unicode: false))
.ExecuteReader(LoadAreaName);
}
}
public IEnumerable<ModuleStatusItem> GetDetailsByArea(Period period, ReportingScope currentScope, string areaName)
{
switch (currentScope)
{
case ReportingScope.Company:
return ExecGetDetailsByArea(period, areaName, @"ReportingGetDetailsByAreaCompany", LoadGetDetailsByAreaDefault);
case ReportingScope.Region:
return ExecGetDetailsByArea(period, areaName, @"ReportingGetDetailsByAreaRegion", LoadGetDetailsByAreaDefault);
case ReportingScope.Zone:
return ExecGetDetailsByArea(period, areaName, @"ReportingGetDetailsByAreaZone", LoadGetDetailsByAreaDefault);
case ReportingScope.District:
return ExecGetDetailsByArea(period, areaName, @"ReportingGetDetailsByAreaDistrict", LoadGetDetailsByAreaDistrict);
default:
throw new NotImplementedException("This method has not yet been implemented for the passed currentScope.");
}
}
/// <summary>
/// Copied from Training and modified to fit the usage for this application. Removed code is commented out.
/// </summary>
public string GetAreaName(ReportingScope scope, string ID)
{
string result = "Area Name not found";
if ((scope == ReportingScope.District || scope == ReportingScope.ReportsTo || scope == ReportingScope.NationalAccount || scope == ReportingScope.RegionalAccount)
&& ID.Length > 10 && ID.Substring(0, 10) == "EmployeeID")
{
// We receive an employee ID from ORC, need to get SDst.
ID = ID.Substring(ID.LastIndexOf('=') + 1);//.PadLeft(8, '0');
if (scope == ReportingScope.District)
{
using (var connection = DBHelper.DefaultConnection)
{
var SDst = connection
.CreateCommand()
.ForProcedure(@"ReportingGetAreaName",
x => x.WithParam("@EmployeeID", ID, size: 100, unicode: false))
.ExecuteReader(
reader =>
{
if (reader.Read() && !reader.IsDBNull(reader.GetOrdinal("SDst")))
{
return reader.GetString("SDst");
}
return null;
});
if (SDst != null)
{
ID = SDst;
}
}
}
}
StringBuilder sb = new StringBuilder();
string sproc = "";
switch (scope)
{
case ReportingScope.Company:
result = "Company: ";
sproc = "[ReportingGetCompanyCodeByCoCd]";
break;
case ReportingScope.Region:
result = "Region: ";
// ID is PersNo or EmployeeID
sproc = "[ReportingGetRegionByEmployee]";
break;
case ReportingScope.Zone:
result = "Zone: ";
sproc = "[ReportingGetZoneByPA]";
break;
case ReportingScope.District:
result = "District: " + ID;
break;
case ReportingScope.ReportsTo:
result = "Reports to: " + ID;
break;
case ReportingScope.NationalAccount:
result = "National Account: ";
throw new NotImplementedException();
sproc = "[ReportingGetAccountByEmployee]";
break;
case ReportingScope.RegionalAccount:
result = "Regional Account: ";
throw new NotImplementedException();
sproc = "[ReportingGetAccountByEmployee]";
break;
}
if (sproc.Length > 0)
{
string name =
DBHelper.ExecuteReader(delegate(IDataReader reader)
{
object scalarResult = null;
if (reader.Read())
{
scalarResult = reader[0];
}
if (scalarResult == null && scope == ReportingScope.Region)
{
// Sometime ID is EmployeeID and sometimes PersNo :(. Got nothing with EmployeeID, so get PersNo instead
scalarResult =
DBHelper.ExecuteReader((Func<IDataReader, object>)delegate(IDataReader subReader)
{
if (subReader.Read())
{
return subReader[0];
}
return null;
},
new SqlConnection(DBHelper.ConnectionString),
new SqlCommand("ReportingGetRegionByPersNo"),
new SqlParameter() { ParameterName = "@ID", DbType = DbType.String, Size = 100, Value = ID });
}
return scalarResult != null ? scalarResult.ToString() : string.Empty;
},
new SqlConnection(DBHelper.ConnectionString),
new SqlCommand(sproc),
new SqlParameter() { ParameterName = "@ID", DbType = DbType.String, Value = ID });
result += name;
}
return result;
}
public IEnumerable<Period> GetPeriodsByScope(ReportingScope currentScope, string ID)
{
using (var connection = DBHelper.DefaultConnection)
{
return connection.CreateCommand()
.ForProcedure(@"ReportingGetPeriodsByScope", p => p.WithParam("@ID", ID))
.ExecuteReader(LoadPeriods);
}
}
public string ParentAreaOf(string lowerArea, ReportingScope scope)
{
return _ReportingRepository.ParentAreaNameFor(lowerArea, scope);
}
/// <summary>
/// Gets the data.
/// </summary>
/// <param name="filterValues">The filter values.</param>
/// <param name="reportingScope">The reporting scope.</param>
/// <param name="spWeb">The sp web.</param>
/// <returns></returns>
public DataTable GetData(Dictionary <string, IEnumerable <object> > filterValues, ReportingScope reportingScope,
SPWeb spWeb)
{
string sql = BuildSelectSql(filterValues, reportingScope, spWeb);
var dataTable = new DataTable();
using (var cmd = new SqlCommand(sql, _DAO.GetClientReportingConnection))
{
using (var sqlDataReader = cmd.ExecuteReader())
{
dataTable.Load(sqlDataReader);
}
}
return(dataTable);
}
/// <summary>
/// Builds the select SQL.
/// </summary>
/// <param name="filterValues">The filter values.</param>
/// <param name="reportingScope">The reporting scope.</param>
/// <param name="spWeb">The sp web.</param>
/// <returns></returns>
protected string BuildSelectSql(Dictionary <string, IEnumerable <object> > filterValues,
ReportingScope reportingScope, SPWeb spWeb)
{
var filters = new List <string>();
switch (reportingScope)
{
case ReportingScope.Site:
filters.Add(string.Format("[SiteId] = N'{0}'", spWeb.Site.ID));
break;
case ReportingScope.Web:
filters.Add(string.Format("[WebId] = N'{0}'", spWeb.ID));
break;
case ReportingScope.Recursive:
filters.Add(
string.Format(
"([WebUrl] LIKE N'{0}%' OR [WebUrl] = N'{0}' OR [WebUrl] = N'/' AND [SiteId] = N'{1}')",
spWeb.SafeServerRelativeUrl(), spWeb.Site.ID));
break;
}
foreach (var keyValuePair in filterValues)
{
string columnName = keyValuePair.Key.Replace("'", string.Empty);
if (columnName.ToLower().Equals("startdate") || columnName.ToLower().Equals("duedate"))
{
continue;
}
if (!keyValuePair.Value.Any())
{
throw new Exception(string.Format("No filter values specified for {0}", columnName));
}
List <string> values;
string columnType = GetColumnType(columnName).ToLower();
if (columnType.Contains("int") || columnType.Contains("float") || columnType.Contains("decimal"))
{
values = keyValuePair.Value.Distinct().Select(v => v.ToString().Replace("'", string.Empty)).ToList();
}
else
{
values =
keyValuePair.Value.Distinct().Select(
v => string.Format("N'{0}'", v.ToString().Replace("'", string.Empty))).ToList();
}
if (values.Count == 0)
{
throw new ApplicationException(string.Format("No valid filter values specified for the column: {0}",
columnName));
}
filters.Add(string.Format("[{0}] IN ({1})", columnName, string.Join(",", values.ToArray())));
}
var startDate = new DateTime(1800, 1, 1);
var dueDate = new DateTime(9999, 12, 31);
if (filterValues.ContainsKey("StartDate"))
{
object sDate = filterValues["StartDate"].FirstOrDefault();
if (sDate != null)
{
string value = sDate.ToString();
if (!string.IsNullOrEmpty(value))
{
DateTime date;
if (DateTime.TryParse(value, out date))
{
startDate = date;
}
}
}
}
if (filterValues.ContainsKey("DueDate"))
{
object dDate = filterValues["DueDate"].FirstOrDefault();
if (dDate != null)
{
string value = dDate.ToString();
if (!string.IsNullOrEmpty(value))
{
DateTime date;
if (DateTime.TryParse(value, out date))
{
dueDate = date;
}
}
}
}
startDate = startDate.Date;
dueDate = dueDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59);
filters.Add(
string.Format("([StartDate] <= '{0:yyyy-MM-dd HH:mm:ss}' AND [DueDate] >= '{1:yyyy-MM-dd HH:mm:ss}')",
dueDate, startDate));
return(string.Format(@"SELECT DISTINCT * FROM [LSTMyWork] WHERE {0}",
string.Join(" AND ", filters.ToArray())));
}
