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
}
}
