/// <summary>
/// Get course information given a complete / incomplete UofT course code
/// Note that the Activities property will be set to null
/// </summary>
/// <param name="query">A complete / incomplete UofT course code</param>
/// <param name="type">How the data is being retrieved</param>
/// <returns>Course information</returns>
public static List <Course> GetCourses(string query, CourseQueryType type)
{
query = query.ToLower();
List <Course> courses = new List <Course>();
if (type == CourseQueryType.CourseCode && query.Length >= 3)
{
using (UofTDataContext db = new UofTDataContext())
{
List <DataContext.Course> dbCourses = db.Courses.ToList();
foreach (DataContext.Course c in dbCourses)
{
if (c.Code.ToLower().Contains(query))
{
Course newCourse = new Course()
{
CourseCode = c.Code,
Activities = null,
Term = c.Term.ToString(),
Title = ""
};
courses.Add(newCourse);
}
}
}
}
return(courses);
}
/// <summary>
/// Get course information given a complete / incomplete UofT course code
/// Note that the Activities property will be set to null
/// </summary>
/// <param name="query">A complete / incomplete UofT course code</param>
/// <param name="type">How the data is being retrieved</param>
/// <returns>Course information</returns>
public List <Course> GetCourses(string courseCodeQuery)
{
courseCodeQuery = courseCodeQuery.ToLower();
List <Course> courses = new List <Course>();
if (courseCodeQuery.Length == 3)
{
using (UofTDataContext db = new UofTDataContext())
{
List <DataContext.usp_GetCourseInfoResult> foundCourses = db.usp_GetCourseInfo(courseCodeQuery).ToList();
foreach (DataContext.usp_GetCourseInfoResult course in foundCourses)
{
courses.Add(new Course()
{
CourseCode = course.Code,
Term = course.Term.ToString(),
Title = course.Title,
Description = course.Description,
Campus = course.Campus
});
}
}
}
return(courses);
}
/// <summary>
/// Adds the distance for two buildings in the database
/// </summary>
/// <param name="db">The database context</param>
/// <param name="building1">The first building</param>
/// <param name="building2">The second building</param>
private void AddBuildingDistances(UofTDataContext db, Building building1, Building building2)
{
Console.WriteLine("Getting data " + building1.Address + " -> " + building2.Address);
// Getting distance info (like walk durations, etc)
BuildingDistanceCalculator calculator = new BuildingDistanceCalculator();
DataModels.BuildingDistance buildingDistance = calculator.GetInfoBetweenBuildings("walking", building1.Address, building2.Address);
BuildingDistance newBuildingDistance = new BuildingDistance()
{
Building = building1,
Building1 = building2,
WalkingDistance = buildingDistance.WalkingDistance,
WalkingDuration = buildingDistance.WalkDuration,
CyclingDistance = buildingDistance.CyclingDistance,
CyclingDuration = buildingDistance.CyclingDuration,
TransitDistance = buildingDistance.PublicTransitDistance,
TransitDuration = buildingDistance.PublicTransitDuration,
DrivingDistance = buildingDistance.DrivingDistance,
DrivingDuration = buildingDistance.DrivingDuration
};
Console.WriteLine("Inserting new data " + building1.BuildingCode + " -> " + building2.BuildingCode);
db.BuildingDistances.InsertOnSubmit(newBuildingDistance);
db.SubmitChanges();
}
/// <summary>
/// Updates the distances between any two buildings in the database
/// </summary>
/// <param name="db">The database context</param>
/// <param name="existingDistances">The existing distances</param>
private void UpdateBuildingDistances(UofTDataContext db, BuildingDistance existingDistances)
{
Building building1 = existingDistances.Building;
Building building2 = existingDistances.Building1;
Console.WriteLine("Attempting to update data " + building1.BuildingCode + " -> " + building2.BuildingCode);
// Getting distance info (like walk durations, etc)
BuildingDistanceCalculator calculator = new BuildingDistanceCalculator();
DataModels.BuildingDistance buildingDistance = calculator.GetInfoBetweenBuildings("walking", building1.Address, building2.Address);
// Making changes to the data
existingDistances.WalkingDistance = buildingDistance.WalkingDistance;
existingDistances.WalkingDuration = buildingDistance.WalkDuration;
existingDistances.CyclingDistance = buildingDistance.CyclingDistance;
existingDistances.CyclingDuration = buildingDistance.CyclingDuration;
existingDistances.TransitDistance = buildingDistance.PublicTransitDistance;
existingDistances.TransitDuration = buildingDistance.PublicTransitDuration;
existingDistances.DrivingDistance = buildingDistance.DrivingDistance;
existingDistances.DrivingDuration = buildingDistance.DrivingDuration;
db.SubmitChanges();
Console.WriteLine("Successful! Has updated data for " + building1.BuildingCode + " -> " + building2.BuildingCode);
}
/// <summary>
/// Insert new building distances into the database for every new building that exists in the Buildings table.
/// If {skipExistingData} param is true:
/// - it will skip the pair of buildings whose distances already exists in the database.
/// - it will add the distances for any pair of buildings whose distances do not exist in the database
///
/// If {skipExistingData} param is false:
/// - it will update the distances already existing in the database
/// - it will add the distances for any pair of buildings whose distances do not exist in the database
/// </summary>
/// <param name="skipExistingData">Whether to skip any existing data or not</param>
public void InsertBuildingDistancesToDatabase(bool skipExistingData)
{
using (UofTDataContext db = new UofTDataContext())
{
List <Building> buildings = db.Buildings.ToList();
for (int i = 0; i < buildings.Count; i++)
{
for (int j = i + 1; j < buildings.Count; j++)
{
Building building1 = buildings[i];
Building building2 = buildings[j];
Console.WriteLine("Attempting to insert data " + building1.BuildingCode + " -> " + building2.BuildingCode);
// Getting pre-existing data for these two buildings
var existingData = from row in db.BuildingDistances
where (row.BuildingID1 == building1.BuildingID && row.BuildingID2 == building2.BuildingID) ||
(row.BuildingID1 == building2.BuildingID && row.BuildingID2 == building1.BuildingID)
select row;
if (existingData != null && existingData.ToList().Count == 1 && !skipExistingData)
{
Console.WriteLine("Updating building distances " + building1.BuildingCode + " -> " + building2.BuildingCode);
foreach (BuildingDistance row in existingData.ToList())
{
UpdateBuildingDistances(db, row);
}
}
else if (existingData == null || existingData.ToList().Count == 0)
{
AddBuildingDistances(db, building1, building2);
}
}
}
}
}
/// <summary>
/// Deletes all the records in the BuildingDistance table
/// </summary>
public void DeleteAllRecords()
{
using (UofTDataContext db = new UofTDataContext())
{
db.ExecuteCommand("DELETE FROM BuildingDistances");
db.ExecuteCommand("DBCC CHECKIDENT ('BuildingDistances', RESEED, 0)");
}
}
/// <summary>
/// Updates all the distances already stored in the database
/// </summary>
public void UpdateBuildingDistanceInfo()
{
using (UofTDataContext db = new UofTDataContext())
{
List <BuildingDistance> rows = db.BuildingDistances.ToList();
foreach (BuildingDistance row in rows)
{
UpdateBuildingDistances(db, row);
}
}
}
/// <summary>
/// Get building info from a building code
/// </summary>
/// <param name="buildingCode">The building code</param>
/// <returns>Building information</returns>
public Building GetBuilding(string buildingCode)
{
using (UofTDataContext db = new UofTDataContext())
{
var building = (from b in db.Buildings
where b.BuildingCode == buildingCode
select b).ToList();
if (building.Count != 1)
{
throw new Exception("There is more than one or no buildings with building code " + buildingCode);
}
return(new Building(building[0]));
}
}
/// <summary>
/// Get complete course information (including the activities, sections, sessions, etc)
/// of a course
/// </summary>
/// <param name="courseCode">A complete UofT course code</param>
/// <returns>Course information</returns>
public Course GetCourseDetails(string courseCode)
{
using (UofTDataContext db = new UofTDataContext())
{
List <DataContext.Course> oldCourses = (from c in db.Courses
where c.Code == courseCode
select c).ToList();
if (oldCourses.Count() != 1)
{
throw new Exception("There is more than one or no course with course code " + courseCode);
}
DataContext.Course oldCourse = oldCourses[0];
return(CourseFactory.GetCourse(oldCourse));
}
}
/// <summary>
/// Deletes all building records in the database and repopulates it with new data
/// </summary>
public void RedoBuildingList()
{
db.ExecuteCommand("DELETE FROM BuildingDistances");
db.ExecuteCommand("DELETE FROM Building");
Browser.Initialize();
Browser.WebInstance.Url = "http://map.utoronto.ca/c/buildings";
using (UofTDataContext db = new UofTDataContext())
{
IWebElement buildingsList = Browser.WebInstance.FindElement(By.ClassName("buildinglist"));
IReadOnlyList <IWebElement> buildingElements = buildingsList.FindElements(By.TagName("li"));
foreach (IWebElement building in buildingElements)
{
string[] description = building.FindElement(By.XPath("./dl/dt")).Text.Split('|');
string address = building.FindElement(By.XPath("./dl/dd[1]")).Text.Trim();
string buildingName = description[0].Trim();
string buildingCode = description[1].Trim();
Building newBuilding = new Building
{
Address = address + " Toronto, Canada",
BuildingCode = buildingCode,
BuildingName = buildingName,
Latitude = null,
Longitude = null
};
db.Buildings.InsertOnSubmit(newBuilding);
}
db.SubmitChanges();
}
Browser.Close();
}