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