public void OnGet(string input) { StationList = new List<Models.Station>(); string sql; sql = string.Format(@" SELECT TOP 10 Stations.StationID, Stations.Name, SUM(DailyTotal) AS TotalRidership FROM Stations FULL JOIN Riderships ON Stations.StationID = Riderships.StationID GROUP BY Stations.StationID, Stations.Name ORDER BY TotalRidership DESC ; "); DataSet ds = DataAccessTier.DB.ExecuteNonScalarQuery(sql); foreach (DataRow row in ds.Tables[0].Rows) { Models.Station s = new Models.Station(); s.StationID = Convert.ToInt32(row["StationID"]); s.StationName = Convert.ToString(row["Name"]); s.DailyRider = Convert.ToInt32(row["TotalRidership"]); StationList.Add(s); } }
public List <Models.Station> FindAllStations() { var result = new List <Models.Station>(); var connection = new System.Data.SqlClient.SqlConnection(_connectionString); connection.Open(); var command = new System.Data.SqlClient.SqlCommand("", connection); command.CommandText = @" Select * from Station"; var reader = command.ExecuteReader(); while (reader.Read()) { Models.Station item = new Models.Station(); item.ID = reader["ID"].ToString(); item.LocationAddress = reader["LocationAddress"].ToString(); item.ObservatoryName = reader["ObservatoryName"].ToString(); item.LocationByTWD67 = reader["LocationByTWD67"].ToString(); item.CreateTime = DateTime.Parse(reader["CreateTime"].ToString()); result.Add(item); } connection.Close(); return(result); }
public List <Models.Station> FindAllStations() { var result = new List <Models.Station>(); var connection = new System.Data.SqlClient.SqlConnection(_connectionString); connection.Open(); var command = new System.Data.SqlClient.SqlCommand("", connection); command.CommandText = @" Select * from Station"; var reader = command.ExecuteReader(); while (reader.Read()) { Models.Station item = new Models.Station(); item.ReservoirName = reader["ReservoirName"].ToString(); item.ReservoirIdentifier = reader["ReservoirIdentifier"].ToString(); item.EffectiveCapacity = reader["EffectiveCapacity"].ToString(); item.DeadStorageLevel = reader["DeadStorageLevel"].ToString(); item.FullWaterLevel = reader["FullWaterLevel"].ToString(); item.CatchmentAreaRainfall = reader["CatchmentAreaRainfall"].ToString(); item.InflowVolume = reader["InflowVolume"].ToString(); item.OutflowTotal = reader["OutflowTotal"].ToString(); //item.CreateTime = DateTime.Parse(reader["CreateTime"].ToString()); result.Add(item); } connection.Close(); return(result); }
private static void removeSameDayStationsAndCategories(Models.Group group, Models.TimeSlot timeSlot, ref IList <Models.Station> eligibleStations) { int dayNum = timeSlot.Start.DayOfYear; if (dailyAssignments.ContainsKey(dayNum) && dailyAssignments[dayNum].ContainsKey(group)) { foreach (Models.Station station in dailyAssignments[dayNum][group]) { //remove all eligible stations with the same category as the current station if (station.Category != null) { for (int eligibleStationNum = 0; eligibleStationNum < eligibleStations.Count; eligibleStationNum++) { Models.Station eligibleStation = eligibleStations[eligibleStationNum]; if (eligibleStation.Category == station.Category) { eligibleStations.Remove(eligibleStation); eligibleStationNum--; } } } //remove the current station if it still exists if (eligibleStations.Contains(station)) { eligibleStations.Remove(station); } } } }
public static uint ScoreTopPicks(IEnumerable <Models.Activity> schedule, IEnumerable <Models.Group> groups) { uint[] scores = new uint[5] { 100, 70, 50, 35, 23 }; uint score = 0; foreach (Models.Group group in groups) { uint numGroupPicks = 0; for (int lcv = 0; lcv < scores.Length; lcv++) { Models.Station preference = new Models.Station(); if (lcv == 0) { preference = group.Preference1; } else if (lcv == 1) { preference = group.Preference2; } else if (lcv == 2) { preference = group.Preference3; } else if (lcv == 3) { preference = group.Preference4; } else if (lcv == 4) { preference = group.Preference5; } foreach (Models.Activity activity in schedule) { if (activity.Group == group && activity.Station == preference) { numGroupPicks++; score += scores[lcv]; break; } } //only score the group for their top three picks if (numGroupPicks == 3) { break; } } } return(score); }
public FieldNotes() { location = new Models.FieldLocation(); //Init as a new class station = new Models.Station(); metadata = new Models.Metadata(); earthmat = new Models.EarthMaterial(); sample = new Models.Sample(); favorite = new Models.Favorite(); document = new Models.Document(); structure = new Models.Structure(); paleoflow = new Models.Paleoflow(); fossil = new Models.Fossil(); mineral = new Models.Mineral(); mineralAlteration = new Models.MineralAlteration(); }
private Entities.Station MapJsonStation(int stationId, Models.InputJson.Json inputJson) { Models.Station jsonStation = inputJson.Stations.First(x => x.Id == stationId); Entities.Station entityStation = new Entities.Station { Id = stationId, Name = jsonStation.Name, Address = jsonStation.Address, Order = jsonStation.Order, PlannedOrder = jsonStation.PlannedOrder, IsActive = jsonStation.IsActive }; return(entityStation); }
//prevent activity pins from being assigned to non-webelos ranks private static void filterEligibleStations(Models.Group currentGroup, ref IList <Models.Station> eligibleStations) { if (currentGroup.Type.ID != 4) { for (int stationNum = 0; stationNum < eligibleStations.Count; stationNum++) { Models.Station station = eligibleStations[stationNum]; if (station.isActivityPin) { eligibleStations.Remove(station); stationNum--; } } } }
public void OnGet() { StationList = new List<Models.Station>(); // clear exception: EX = null; try { string sql; sql = string.Format(@" SELECT T1.StationID, Stations.Name, T1.NumRiders FROM (SELECT TOP 10 StationID, SUM(DailyTotal) AS NumRiders FROM Riderships GROUP BY StationID ORDER BY NumRiders DESC) AS T1 LEFT JOIN Stations ON T1.StationID = Stations.StationID "); DataSet ds = DataAccessTier.DB.ExecuteNonScalarQuery(sql); foreach (DataRow row in ds.Tables[0].Rows) { Models.Station s = new Models.Station(); s.StationID = Convert.ToInt32(row["StationID"]); s.StationName = Convert.ToString(row["Name"]); s.NumRiders = Convert.ToInt32(row["NumRiders"]); StationList.Add(s); } } catch(Exception ex) { EX = ex; } finally { // nothing at the moment } }
public Models.Station FindByID(string id) { Models.Station result = null; var connection = new System.Data.SqlClient.SqlConnection(_connectionString); connection.Open(); var command = new System.Data.SqlClient.SqlCommand("", connection); command.CommandText = string.Format(@" Select * from Station Where ID='{0}'", id); var reader = command.ExecuteReader(); var list = new List <YC.Models.Station>(); while (reader.Read()) { Models.Station item = new Models.Station(); item.ID = reader["ID"].ToString(); item.LocationAddress = reader["LocationAddress"].ToString(); item.ObservatoryName = reader["ObservatoryName"].ToString(); item.LocationByTWD67 = reader["LocationByTWD67"].ToString(); item.CreateTime = DateTime.Parse(reader["CreateTime"].ToString()); if (!string.IsNullOrEmpty(reader["LastRecordTime"].ToString())) { item.LastRecordTime = DateTime.Parse(reader["LastRecordTime"].ToString()); } if (!string.IsNullOrEmpty(reader["LastRecordWaterLevel"].ToString())) { item.LastRecordWaterLevel = double.Parse(reader["LastRecordWaterLevel"].ToString()); } list.Add(item); } connection.Close(); if (list.Count == 1) { result = list.Single(); } return(result); }
public void CreateStation(Models.Station station) { var connection = new System.Data.SqlClient.SqlConnection(); connection.ConnectionString = _connectionString; connection.Open(); var command = new System.Data.SqlClient.SqlCommand("", connection); command.CommandText = string.Format(@" INSERT INTO Station(ID, LocationAddress, ObservatoryName, LocationByTWD67, CreateTime) VALUES ('{0}','{1}','{2}','{3}','{4}') ", station.ID, station.LocationAddress, station.ObservatoryName, station.LocationByTWD67, station.CreateTime.ToString("yyyy/MM/dd")); command.ExecuteNonQuery(); connection.Close(); }
public void UpdateLastRecord(Models.Station station) { var connection = new System.Data.SqlClient.SqlConnection(); connection.ConnectionString = _connectionString; connection.Open(); var command = new System.Data.SqlClient.SqlCommand("", connection); command.CommandText = string.Format(@" UPDATE [dbo].[Station] SET [LastRecordTime] ='{0}' ,[LastRecordWaterLevel] ={1} WHERE [ID] = N'{2}' ", station.LastRecordTime.ToString("yyyy/MM/dd"), station.LastRecordWaterLevel, station.ID); command.ExecuteNonQuery(); connection.Close(); }
public void Update(Models.Station station) { var connection = new System.Data.SqlClient.SqlConnection(); connection.ConnectionString = _connectionString; connection.Open(); var command = new System.Data.SqlClient.SqlCommand("", connection); command.CommandText = string.Format(@" UPDATE [dbo].[Station] SET [LocationAddress]=N'{0}' ,[ObservatoryName]=N'{1}' ,[LocationByTWD67]=N'{2}' WHERE [ID] = N'{3}' ", station.LocationAddress, station.ObservatoryName, station.LocationByTWD67, station.ID); command.ExecuteNonQuery(); connection.Close(); }
private IEnumerable <Entities.Station> MapJsonStations(IEnumerable <int> stations, Models.InputJson.Json inputJson) { IList <Entities.Station> stationEntities = new List <Entities.Station>(); foreach (var stationId in stations) { Models.Station jsonStation = inputJson.Stations.First(x => x.Id == stationId); Entities.Station entityStation = new Entities.Station { Id = stationId, Name = jsonStation.Name, Address = jsonStation.Address, Order = jsonStation.Order, PlannedOrder = jsonStation.PlannedOrder, IsActive = jsonStation.IsActive }; stationEntities.Add(entityStation); } return(stationEntities); }
public void OnGet() { StationList = new List<Models.Station>(); // make input available to web page: //Input = input; // clear exception: EX = null; try { // // Do we have an input argument? If not, there's nothing to do: // // // Lookup movie(s) based on input, which could be id or a partial name: // string sql; // lookup station(s) by partial name match: //input = input.Replace("'", "''"); sql = string.Format(@" SELECT TOP 10 Stations.StationID, Stations.Name, AVG(DailyTotal) AS AvgDailyRidership, COUNT(DISTINCT StopID) AS numStops FROM Stations LEFT JOIN Riderships ON Stations.StationID = Riderships.StationID LEFT JOIN Stops ON Stations.StationID = Stops.StationID GROUP BY Stations.StationID, Stations.Name, Stops.StationID ORDER BY AvgDailyRidership DESC "); DataSet ds = DataAccessTier.DB.ExecuteNonScalarQuery(sql); foreach (DataRow row in ds.Tables[0].Rows) { Models.Station s = new Models.Station(); s.StationID = Convert.ToInt32(row["StationID"]); s.StationName = Convert.ToString(row["Name"]); // avg could be null if there is no ridership data: if (row["AvgDailyRidership"] == System.DBNull.Value) s.AvgDailyRidership = 0; else s.AvgDailyRidership = Convert.ToInt32(row["AvgDailyRidership"]); s.NumStops = Convert.ToInt32(row["numStops"]); StationList.Add(s); } //else } catch(Exception ex) { EX = ex; } finally { // nothing at the moment } }
public static bool generateAssignment( Models.Group assignedGroup, Models.TimeSlot timeSlot, IList <Models.TimeSlot> sortedTimeSlots, IList <Models.Station> eligibleStations, IList <Models.Activity> generatedSchedule) { if (eligibleStations.Count > 0) { int stationNumber = random.Next(eligibleStations.Count); Models.Station assignedStation = eligibleStations[stationNumber]; //we could be scheduling either an activity pin station or a regular station if (assignedStation.isActivityPin) { int timeSlotIndex = sortedTimeSlots.IndexOf(timeSlot); if (timeSlotIndex < 0 || sortedTimeSlots.Count <= (timeSlotIndex + 1)) { return(false); } int currentTimeSlotDayNum = sortedTimeSlots[timeSlotIndex].Start.DayOfYear; int nextTimeSlotDayNum = sortedTimeSlots[timeSlotIndex + 1].Start.DayOfYear; if (currentTimeSlotDayNum != nextTimeSlotDayNum || sortedTimeSlots[timeSlotIndex].isGeneral || sortedTimeSlots[timeSlotIndex + 1].isGeneral) { return(false); } activityNumber++; Models.Activity activity = new Models.Activity(); activity.ID = activityNumber; activity.Group = assignedGroup; activity.Station = assignedStation; activity.TimeSlot = sortedTimeSlots[timeSlotIndex]; generatedSchedule.Add(activity); Models.Activity activity1 = new Models.Activity(); activity1.ID = activityNumber; activity1.Group = assignedGroup; activity1.Station = assignedStation; activity1.TimeSlot = sortedTimeSlots[timeSlotIndex + 1]; generatedSchedule.Add(activity1); nextTimeSlotUnassignedStations.Remove(assignedStation); nextTimeSlotUnassignedGroups.Remove(assignedGroup); addAssignmentToDailyAssignments(activity); } else { activityNumber++; Models.Activity activity = new Models.Activity(); activity.ID = activityNumber; activity.Group = assignedGroup; activity.Station = assignedStation; activity.TimeSlot = timeSlot; generatedSchedule.Add(activity); addAssignmentToDailyAssignments(activity); } if (assignedGroup.Preference1 == assignedStation) { groupPreferenceGiven[assignedGroup][0] = true; } else if (assignedGroup.Preference2 == assignedStation) { groupPreferenceGiven[assignedGroup][1] = true; } else if (assignedGroup.Preference3 == assignedStation) { groupPreferenceGiven[assignedGroup][2] = true; } else if (assignedGroup.Preference4 == assignedStation) { groupPreferenceGiven[assignedGroup][3] = true; } else if (assignedGroup.Preference5 == assignedStation) { groupPreferenceGiven[assignedGroup][4] = true; } groupStationVisitRange[assignedGroup][assignedStation].decrementNumVisits(); groupStationAssignments[assignedGroup][assignedStation]++; unassignedGroups.Remove(assignedGroup); stationCapacities[assignedStation]--; if (stationCapacities[assignedStation] <= 0) { unassignedStations.Remove(assignedStation); } return(true); } return(false); }
private static bool groupCanVisitStationAgain(Models.Group group, Models.Station station) { return(groupStationVisitRange[group][station].numVisits > 0 || !groupStationVisitRange[group][station].numVisits.HasValue); }
public List <Models.Station> FindAllStationsWithGroups() { var result = new List <Models.Station>(); var connection = new System.Data.SqlClient.SqlConnection(_connectionString); connection.Open(); var command = new System.Data.SqlClient.SqlCommand("", connection); command.CommandText = @" Select s.ID as StationID,s.LocationAddress,s.ObservatoryName,s.LocationByTWD67,s.CreateTime,s.LastRecordTime,s.LastRecordWaterLevel, g.Id as GroupID,g.Name as GroupName from Station s left join StationGroup sg on s.ID=sg.StationID left join [Group] g on g.Id= sg.GroupID order by s.ID "; var reader = command.ExecuteReader(); while (reader.Read()) { Models.Station item = null; var dbID = reader["StationID"].ToString(); if (result.Any(x => x.ID == dbID)) { item = result.SingleOrDefault(x => x.ID == dbID); } else { item = new Models.Station(); result.Add(item); } item.ID = reader["StationID"].ToString(); item.LocationAddress = reader["LocationAddress"].ToString(); item.ObservatoryName = reader["ObservatoryName"].ToString(); item.LocationByTWD67 = reader["LocationByTWD67"].ToString(); item.CreateTime = DateTime.Parse(reader["CreateTime"].ToString()); if (!string.IsNullOrEmpty(reader["LastRecordTime"].ToString())) { item.LastRecordTime = DateTime.Parse(reader["LastRecordTime"].ToString()); } if (!string.IsNullOrEmpty(reader["LastRecordWaterLevel"].ToString())) { item.LastRecordWaterLevel = double.Parse(reader["LastRecordWaterLevel"].ToString()); } var groupID = reader["GroupID"].ToString(); var groupName = reader["GroupName"].ToString(); if (!string.IsNullOrEmpty(groupID)) { item.Groups.Add(new Models.Group() { Id = int.Parse(groupID), Name = groupName }); } } connection.Close(); return(result); }
public void OnGet(string input) { StationList = new List<Models.Station>(); // make input available to web page: Input = input; // clear exception: EX = null; try { // // Do we have an input argument? If not, there's nothing to do: // if (input == null) { // // there's no page argument, perhaps user surfed to the page directly? // In this case, nothing to do. // } else { // // Lookup movie(s) based on input, which could be id or a partial name: // string sql; // lookup station(s) by partial name match: input = input.Replace("'", "''"); sql = string.Format(@" SELECT tableA.StationID, tableA.Name, tableA.AvgDailyRidership, tableB.TotalStops, tableC.Accessibility FROM (SELECT Stations.StationID as StationID, Stations.Name as Name, AVG(DailyTotal) AS AvgDailyRidership FROM Stations LEFT JOIN Riderships ON Stations.StationID = Riderships.StationID WHERE Stations.Name LIKE '%{0}%' GROUP BY Stations.StationID, Stations.Name) as tableA, (SELECT Stations.StationID, count(Stops.StationID) as TotalStops FROM Stations LEFT JOIN Stops ON Stations.StationID = Stops.StationID GROUP BY Stations.StationID) as tableB, (SELECT Stations.StationID, CASE WHEN SUM(CAST(Stops.ADA AS INT)) = 0 THEN 'none' WHEN SUM(CAST(Stops.ADA AS INT)) = COUNT(Stops.StationID) THEN 'all' WHEN SUM(CAST(Stops.ADA AS INT)) <> COUNT(Stops.StationID) THEN 'some' END as Accessibility FROM Stations LEFT JOIN Stops ON Stations.StationID = Stops.StationID GROUP BY Stations.StationID) as tableC WHERE tableA.StationID = tableB.StationID AND tableB.StationID = tableC.StationID ORDER BY TableA.Name ASC; ", input); DataSet ds = DataAccessTier.DB.ExecuteNonScalarQuery(sql); foreach (DataRow row in ds.Tables[0].Rows) { Models.Station s = new Models.Station(); s.StationID = Convert.ToInt32(row["StationID"]); s.StationName = Convert.ToString(row["Name"]); s.TotalStops = Convert.ToInt32(row["TotalStops"]); // avg could be null if there is no ridership data: if (row["AvgDailyRidership"] == System.DBNull.Value) s.AvgDailyRidership = 0; else s.AvgDailyRidership = Convert.ToInt32(row["AvgDailyRidership"]); // Acessibility could be null if there is no ridership data: if (row["Accessibility"] == System.DBNull.Value) s.Accessibility = "none"; else s.Accessibility = Convert.ToString(row["Accessibility"]); StationList.Add(s); } }//else } catch(Exception ex) { EX = ex; } finally { // nothing at the moment } }
public void OnGet(string input) { StationList = new List<Models.Station>(); // make input available to web page: Input = input; // clear exception: EX = null; try { // // Do we have an input argument? If not, there's nothing to do: // if (input == null) { // // there's no page argument, perhaps user surfed to the page directly? // In this case, nothing to do. // } else { // // Lookup CTA(s) based on input, which could be id or a partial name: // string sql; // lookup station(s) by partial name match: input = input.Replace("'", "''"); sql = string.Format(@" SELECT Stations.StationID, Stations.Name, (SELECT AVG(DailyTotal) FROM Riderships WHERE Stations.StationID = Riderships.StationID ) AS AvgDailyRidership, (SELECT COUNT(StopID) FROM Stops WHERE Stations.StationID = Stops.StationID) AS NumOfStops, (SELECT SUM(CAST(ADA AS INT)) FROM Stops WHERE Stations.StationID = Stops.StationID) AS HandicapInfo FROM Stations, Riderships, Stops WHERE Stations.Name LIKE '%{0}%' GROUP BY Stations.StationID, Stations.Name ORDER BY Stations.Name ASC; ", input); DataSet ds = DataAccessTier.DB.ExecuteNonScalarQuery(sql); foreach (DataRow row in ds.Tables[0].Rows) { Models.Station s = new Models.Station(); s.StationID = Convert.ToInt32(row["StationID"]); s.StationName = Convert.ToString(row["Name"]); s.NumOfStops = Convert.ToInt32(row["NumOfStops"]); s.HandicapInfo = Convert.ToString(row["HandicapInfo"]); // avg could be null if there is no ridership data: if (row["AvgDailyRidership"] == System.DBNull.Value) s.AvgDailyRidership = 0; else s.AvgDailyRidership = Convert.ToInt32(row["AvgDailyRidership"]); //For handicap info if (row["HandicapInfo"] == System.DBNull.Value) s.HandicapInfo = "None"; else if (Int32.Parse(s.HandicapInfo) == s.NumOfStops) s.HandicapInfo = "All"; else if(Int32.Parse(s.HandicapInfo) < s.NumOfStops && Int32.Parse(s.HandicapInfo) != 0) s.HandicapInfo = "Some"; else s.HandicapInfo = "None"; StationList.Add(s); } }//else } catch(Exception ex) { EX = ex; } finally { // nothing at the moment } }
public static uint ScoreTopPicks(IEnumerable<Models.Activity> schedule, IEnumerable<Models.Group> groups) { uint[] scores = new uint[5] {100, 70, 50, 35, 23}; uint score = 0; foreach (Models.Group group in groups) { uint numGroupPicks = 0; for (int lcv = 0; lcv < scores.Length; lcv++) { Models.Station preference = new Models.Station(); if (lcv == 0) preference = group.Preference1; else if (lcv == 1) preference = group.Preference2; else if (lcv == 2) preference = group.Preference3; else if (lcv == 3) preference = group.Preference4; else if (lcv == 4) preference = group.Preference5; foreach (Models.Activity activity in schedule) { if (activity.Group == group && activity.Station == preference) { numGroupPicks++; score += scores[lcv]; break; } } //only score the group for their top three picks if (numGroupPicks == 3) break; } } return score; }
public void OnGet(string input) { StationList = new List<Models.Station>(); // make input available to web page: Input = input; // clear exception: EX = null; try { // // Do we have an input argument? If not, there's nothing to do: // if (input == null) { // // there's no page argument, perhaps user surfed to the page directly? // In this case, nothing to do. // } else { // // Lookup movie(s) based on input, which could be id or a partial name: // string sql; // lookup station(s) by partial name match: input = input.Replace("'", "''"); sql = string.Format(@" SELECT Stations.StationID, Stations.Name, AVG(DailyTotal) AS AvgDailyRidership, COUNT(DISTINCT Stops.Name) AS Stops, Stops.ADA AS ADA FROM Stations LEFT JOIN Riderships ON Stations.StationID = Riderships.StationID LEFT JOIN Stops ON Stations.StationID = Stops.StationID LEFT JOIN StopDetails on StopDetails.StopID = stops.StopID WHERE Stations.Name LIKE '%{0}%' GROUP BY Stations.StationID, Stations.Name, Stops.ADA ORDER BY Stations.Name ASC; ", input); DataSet ds = DataAccessTier.DB.ExecuteNonScalarQuery(sql); foreach (DataRow row in ds.Tables[0].Rows) { Models.Station s = new Models.Station(); s.StationID = Convert.ToInt32(row["StationID"]); s.StationName = Convert.ToString(row["Name"]); s.Stops = Convert.ToInt32(row["Stops"]); try{ if(Convert.ToInt32(row["ADA"]) == 0 || s.Stops == 0){ s.HandiCap = "None"; } else if (Convert.ToInt32(row["ADA"]) == 1){ s.HandiCap = "All"; } else{ s.HandiCap = "Some"; } }catch(Exception ex){ s.HandiCap = "None"; } // avg could be null if there is no ridership data: if (row["AvgDailyRidership"] == System.DBNull.Value) s.AvgDailyRidership = 0; else s.AvgDailyRidership = Convert.ToInt32(row["AvgDailyRidership"]); StationList.Add(s); } }//else } catch(Exception ex) { EX = ex; } finally { // nothing at the moment } }
public void OnGet(string input) { StationList = new List<Models.Station>(); // make input available to web page: Input = input; // clear exception: EX = null; try { // // Do we have an input argument? If not, there's nothing to do: // if (input == null) { // // there's no page argument, perhaps user surfed to the page directly? // In this case, nothing to do. // } else { // // Lookup movie(s) based on input, which could be id or a partial name: // string sql; // lookup station(s) by partial name match: input = input.Replace("'", "''"); sql = string.Format(@" SELECT StationList.StationID, StationList.Name, StationList.AvgDailyRidership, Stop_List.NumStops, Stop_List.HandicapAccessible FROM (SELECT Stations.StationID, Stations.Name, AVG(DailyTotal) AS AvgDailyRidership FROM Stations LEFT JOIN Riderships ON Stations.StationID = Riderships.StationID LEFT OUTER JOIN Stops ON Stations.StationID = Stops.StationID WHERE Stations.Name LIKE '%{0}%' GROUP BY Stations.StationID, Stations.Name) AS StationList LEFT JOIN (SELECT StationID, Count(StationID) AS NumStops, Sum(CAST(ADA AS int)) AS HandicapAccessible FROM Stops GROUP BY StationID) AS Stop_List ON StationList.StationID = Stop_List.StationID ORDER BY StationList.Name ASC ", input); DataSet ds = DataAccessTier.DB.ExecuteNonScalarQuery(sql); foreach (DataRow row in ds.Tables[0].Rows) { Models.Station s = new Models.Station(); s.StationID = Convert.ToInt32(row["StationID"]); s.StationName = Convert.ToString(row["Name"]); // avg could be null if there is no ridership data: if (row["AvgDailyRidership"] == System.DBNull.Value) s.AvgDailyRidership = 0; else s.AvgDailyRidership = Convert.ToInt32(row["AvgDailyRidership"]); //NumStops if (row["NumStops"] == System.DBNull.Value) s.NumStops = 0; else s.NumStops = Convert.ToInt32(row["NumStops"]); //Handicap Accessible if (row["HandicapAccessible"] == System.DBNull.Value) s.HandicapAccessible = "none"; else { int handicapInt = Convert.ToInt32(row["HandicapAccessible"]); if (handicapInt == 0) s.HandicapAccessible = "none"; else if (handicapInt == s.NumStops) s.HandicapAccessible = "all"; else s.HandicapAccessible = "some"; } StationList.Add(s); } }//else } catch(Exception ex) { EX = ex; } finally { // nothing at the moment } }
public static int deductStationRevisitedOnSameDayScore( IEnumerable <Models.Activity> schedule, IEnumerable <Models.Group> groups, IEnumerable <Models.Station> stations, IEnumerable <Models.TimeSlot> timeSlots) { DateTime a = DateTime.Now; int revisitedPenalty = 0; //assignments for station categories on each day of camp //example: on day 3, Group "Knights1" has been assigned to //category "arts and crafts" twice and category "shooting" once IDictionary <int, Dictionary <Models.Group, Dictionary <string, int> > > dailyCategoryAssignments = new Dictionary <int, Dictionary <Models.Group, Dictionary <string, int> > >(); //assignments for stations on each day of camp //example: on day 2, Group "Knights2" has been assigned to station IDictionary <int, Dictionary <Models.Group, Dictionary <Models.Station, int> > > dailyStationAssignments = new Dictionary <int, Dictionary <Models.Group, Dictionary <Models.Station, int> > >(); foreach (Models.Activity activity in schedule) { Models.Group group = activity.Group; Models.Station station = activity.Station; string stationCategory = activity.Station.Category; if (stationCategory == null) { continue; } int dayNum = activity.TimeSlot.Start.DayOfYear; //check if the same station category has already been assigned on the same day if (dailyCategoryAssignments.ContainsKey(dayNum)) { if (dailyCategoryAssignments[dayNum].ContainsKey(group)) { if (dailyCategoryAssignments[dayNum][group].ContainsKey(stationCategory)) { int numVisits = dailyCategoryAssignments[dayNum][group][stationCategory]; revisitedPenalty += numVisits; dailyCategoryAssignments[dayNum][group][stationCategory]++; } else { dailyCategoryAssignments[dayNum][group].Add(stationCategory, 1); } } else { Dictionary <string, int> stationAssignmentValue = new Dictionary <string, int>(); stationAssignmentValue.Add(stationCategory, 1); dailyCategoryAssignments[dayNum].Add(group, stationAssignmentValue); } } else { Dictionary <Models.Group, Dictionary <string, int> > groupAssignments = new Dictionary <Models.Group, Dictionary <string, int> >(); Dictionary <string, int> stationAssignmentValue = new Dictionary <string, int>(); stationAssignmentValue.Add(stationCategory, 1); groupAssignments.Add(group, stationAssignmentValue); dailyCategoryAssignments.Add(dayNum, groupAssignments); } //check if the current station has already been assigned on the same day if (dailyStationAssignments.ContainsKey(dayNum)) { if (dailyStationAssignments[dayNum].ContainsKey(group)) { if (dailyStationAssignments[dayNum][group].ContainsKey(station)) { int numVisits = dailyStationAssignments[dayNum][group][station]; revisitedPenalty += numVisits; dailyStationAssignments[dayNum][group][station]++; } else { dailyStationAssignments[dayNum][group].Add(station, 1); } } else { Dictionary <Models.Station, int> stationAssignmentValue = new Dictionary <Models.Station, int>(); stationAssignmentValue.Add(station, 1); dailyStationAssignments[dayNum].Add(group, stationAssignmentValue); } } else { Dictionary <Models.Group, Dictionary <Models.Station, int> > groupAssignments = new Dictionary <Models.Group, Dictionary <Models.Station, int> >(); Dictionary <Models.Station, int> stationAssignmentValue = new Dictionary <Models.Station, int>(); stationAssignmentValue.Add(station, 1); groupAssignments.Add(group, stationAssignmentValue); dailyStationAssignments.Add(dayNum, groupAssignments); } } DateTime b = DateTime.Now; TimeSpan c = b.Subtract(a); return(revisitedPenalty * -90); }
//public bool[][] array; //public Dictionary<DateTime, bool> datas; /// <summary> /// 根据给定的数据库记录,仅仅返回给定日9时到第二天8时的数据(即数据库中一条记录) /// </summary> /// 多条记录比较麻烦而且暂时没有需要。 /// <param name="station">站点信息</param> /// <param name="record">数据库中一条记录</param> public TransferStatisticRecord(Models.Station station, Models.M_status record) { //this.station = station; this.id = station.stationId; this.type = station.stationType; this.name = station.stationName; //datas = new Dictionary<DateTime, bool>(); datas = new List <HourData>(); //array = new bool[24][]; if (record != null) { //array[0] = new bool[2]; //array[0][0] = record.H0R == "1"; //array[0][1] = record.H0W == "1"; //array[1] = new bool[2]; //array[1][0] = record.H1R == "1"; //array[1][1] = record.H1W == "1"; //array[2] = new bool[2]; //array[2][0] = record.H2R == "1"; //array[2][1] = record.H2W == "1"; //array[3] = new bool[2]; //array[3][0] = record.H3R == "1"; //array[3][1] = record.H3W == "1"; //array[4] = new bool[2]; //array[4][0] = record.H4R == "1"; //array[4][1] = record.H4W == "1"; //array[5] = new bool[2]; //array[5][0] = record.H5R == "1"; //array[5][1] = record.H5W == "1"; //array[6] = new bool[2]; //array[6][0] = record.H6R == "1"; //array[6][1] = record.H6W == "1"; //array[7] = new bool[2]; //array[7][0] = record.H7R == "1"; //array[7][1] = record.H7W == "1"; //array[8] = new bool[2]; //array[8][0] = record.H8R == "1"; //array[8][1] = record.H8W == "1"; //array[9] = new bool[2]; //array[9][0] = record.H9R == "1"; //array[9][0] = record.H9W == "1"; //array[10] = new bool[2]; //array[10][0] = record.H10R == "1"; //array[10][1] = record.H10W == "1"; //array[11] = new bool[2]; //array[11][0] = record.H1R == "1"; //array[11][1] = record.H11W == "1"; //array[12] = new bool[2]; //array[12][0] = record.H12R == "1"; //array[12][1] = record.H12W == "1"; //array[13] = new bool[2]; //array[13][0] = record.H13R == "1"; //array[13][1] = record.H13W == "1"; //array[14] = new bool[2]; //array[14][0] = record.H14R == "1"; //array[14][1] = record.H14W == "1"; //array[15] = new bool[2]; //array[15][0] = record.H15R == "1"; //array[15][1] = record.H15W == "1"; //array[16] = new bool[2]; //array[16][0] = record.H16R == "1"; //array[16][1] = record.H16W == "1"; //array[17] = new bool[2]; //array[17][0] = record.H17R == "1"; //array[17][1] = record.H17W == "1"; //array[18] = new bool[2]; //array[18][0] = record.H18R == "1"; //array[18][1] = record.H18W == "1"; //array[19] = new bool[2]; //array[19][0] = record.H19R == "1"; //array[19][1] = record.H19W == "1"; //array[20] = new bool[2]; //array[20][0] = record.H20R == "1"; //array[20][1] = record.H20W == "1"; //array[21] = new bool[2]; //array[21][0] = record.H21R == "1"; //array[21][1] = record.H21W == "1"; //array[22] = new bool[2]; //array[22][0] = record.H22R == "1"; //array[22][1] = record.H22W == "1"; //array[23] = new bool[2]; //array[23][0] = record.H23R == "1"; //array[23][1] = record.H23W == "1"; datas.Add(new HourData(rain: record.H0R == "1", water: record.H0W == "1")); datas.Add(new HourData(rain: record.H1R == "1", water: record.H1W == "1")); datas.Add(new HourData(rain: record.H2R == "1", water: record.H2W == "1")); datas.Add(new HourData(rain: record.H3R == "1", water: record.H3W == "1")); datas.Add(new HourData(rain: record.H4R == "1", water: record.H4W == "1")); datas.Add(new HourData(rain: record.H5R == "1", water: record.H5W == "1")); datas.Add(new HourData(rain: record.H6R == "1", water: record.H6W == "1")); datas.Add(new HourData(rain: record.H7R == "1", water: record.H7W == "1")); datas.Add(new HourData(rain: record.H8R == "1", water: record.H8W == "1")); datas.Add(new HourData(rain: record.H9R == "1", water: record.H9W == "1")); datas.Add(new HourData(rain: record.H10R == "1", water: record.H10W == "1")); datas.Add(new HourData(rain: record.H11R == "1", water: record.H11W == "1")); datas.Add(new HourData(rain: record.H12R == "1", water: record.H12W == "1")); datas.Add(new HourData(rain: record.H13R == "1", water: record.H13W == "1")); datas.Add(new HourData(rain: record.H14R == "1", water: record.H14W == "1")); datas.Add(new HourData(rain: record.H15R == "1", water: record.H15W == "1")); datas.Add(new HourData(rain: record.H16R == "1", water: record.H16W == "1")); datas.Add(new HourData(rain: record.H17R == "1", water: record.H17W == "1")); datas.Add(new HourData(rain: record.H18R == "1", water: record.H18W == "1")); datas.Add(new HourData(rain: record.H19R == "1", water: record.H19W == "1")); datas.Add(new HourData(rain: record.H20R == "1", water: record.H20W == "1")); datas.Add(new HourData(rain: record.H21R == "1", water: record.H21W == "1")); datas.Add(new HourData(rain: record.H22R == "1", water: record.H22W == "1")); datas.Add(new HourData(rain: record.H23R == "1", water: record.H23W == "1")); } else { //数据库不存在该站点的数据,全部设为转储失败。 for (int i = 0; i < 24; i++) { datas.Add(new HourData(false, false)); //array[i] = new bool[2]; //array[i][0] = false; //array[i][1] = false; } } }
public void OnGet(string input) { StationList = new List<Models.Station>(); // make input available to web page: Input = input; // clear exception: EX = null; try { // // Do we have an input argument? If not, there's nothing to do: // if (input == null) { // // there's no page argument, perhaps user surfed to the page directly? // In this case, nothing to do. // } else { // // Lookup movie(s) based on input, which could be id or a partial name: // string sql; // lookup station(s) by partial name match: input = input.Replace("'", "''"); sql = string.Format(@" SELECT T3.StationID, T3.Name, T3.NumStops, T3.LineID, Lines.Color, T3.Position FROM (SELECT T2.StationID, T2.Name, T2.NumStops, StationOrder.LineID, StationOrder.Position FROM (SELECT T1.StationID, T1.NumStops, Stations.Name FROM(SELECT StationID, COUNT(*) AS NumStops FROM Stops GROUP BY StationID) AS T1 JOIN Stations ON T1.StationID = Stations.StationID) AS T2 RIGHT JOIN StationOrder ON T2.StationID = StationOrder.StationID) AS T3 RIGHT JOIN Lines ON T3.LineID = Lines.LineID WHERE Lines.Color = '{0}' ORDER BY T3.LineID, T3.Position ", input); DataSet ds = DataAccessTier.DB.ExecuteNonScalarQuery(sql); foreach (DataRow row in ds.Tables[0].Rows) { Models.Station s = new Models.Station(); s.StationID = Convert.ToInt32(row["StationID"]); s.StationName = Convert.ToString(row["Name"]); //NumStops if (row["NumStops"] == System.DBNull.Value) s.NumStops = 0; else s.NumStops = Convert.ToInt32(row["NumStops"]); StationList.Add(s); } }//else } catch(Exception ex) { EX = ex; } finally { // nothing at the moment } }