public List<Carpark> GetCarparks(string name, int id)
{
var carparks = new List<Carpark>();
var recent = true;
var getThem = false;
try
{
_connection = new MySqlConnection(ConnectionString);
_connection.Open();
var sql = string.Format("SELECT * from Roadwatch_Parking where City = '{0}' ", name);
using (var cmd = new MySqlCommand(sql, _connection))
{
using (var r = cmd.ExecuteReader())
{
if (r.HasRows)
{
while (r.Read())
{
//if (DateTime.Now.Subtract(r.GetDateTime("Updated")).TotalDays > 30)
//{
// recent = false;
// break;
//}
var cp = new Carpark
{
Access = r.GetString("Access"),
City = r.GetString("City"),
CityId = r.GetInt32("City_Id"),
District = r.GetString("District"),
Hours = r.GetString("Hours"),
Id = r.GetInt32("Id"),
Location = r.GetString("Location"),
Name = r.GetString("Name"),
Tarrifs = r.GetString("Tarriffs"),
Url = r.GetString("Url")
};
carparks.Add(cp);
}
}
else
{
getThem = true;
}
}
}
}
catch (Exception ex)
{
var sf = new StackFrame();
var methodBase = sf.GetMethod();
Database.InsertRoadwatchErrorToDb(methodBase.Name, ex.Message, ex.ToString());
}
if (!recent || getThem)
{
var web = new HtmlWeb();
var doc = web.Load("http://www.theaa.ie/AA/AA-Roadwatch/Car-parking/" + name + "/");
var nodes = doc.DocumentNode.SelectNodes("//div[@class='parkingList']");
foreach (var node in nodes)
{
var parks = node.ChildNodes[3].ChildNodes[3].ChildNodes; //.SelectNodes("//tr");
foreach (var n in parks.Where(p => p.Name == "tr"))
{
var cp = new Carpark();
cp.District = CleanString(node.ChildNodes[1].InnerText);
cp.Name = CleanString(n.ChildNodes[1].InnerText);
cp.Location = CleanString(n.ChildNodes[3].InnerText);
cp.Url = "http://www.theaa.ie" + n.ChildNodes[1].ChildNodes[1].Attributes[0].Value;
cp.Access = CleanString(GetDetails(cp.Url));
cp.Tarrifs = CleanString(tariffs);
cp.Hours = CleanString(openingHours);
cp.City = name;
cp.CityId = id;
carparks.Add(cp);
}
}
if (carparks.Count > 0)
{
foreach (var cp in carparks)
{
//InsertCarparkToDb(cp);
}
}
GetCarparks(name, id);
}
return carparks;
}
private void InsertCarparkToDb(Carpark cp)
{
_connection = new MySqlConnection(ConnectionString);
_connection.Open();
try
{
const string query =
"INSERT INTO Roadwatch_Parking (City, District, Name, Location, Url, Access, Hours, Tarriffs, City_Id, Updated)" +
" VALUES (@city, @district, @name, @location, @url, @access, @hours, @tarriffs, @cityid, @updated);";
using (var cmd = new MySqlCommand(query, _connection))
{
// Start using the passed values in our parameters:
cmd.Parameters.AddWithValue("@city", cp.City);
cmd.Parameters.AddWithValue("@district", cp.District);
cmd.Parameters.AddWithValue("@name", cp.Name);
cmd.Parameters.AddWithValue("@location", cp.Location);
cmd.Parameters.AddWithValue("@url", cp.Url);
cmd.Parameters.AddWithValue("@access", cp.Access);
cmd.Parameters.AddWithValue("@hours", cp.Hours);
cmd.Parameters.AddWithValue("@tarriffs", cp.Tarrifs);
cmd.Parameters.AddWithValue("@cityid", cp.CityId);
cmd.Parameters.AddWithValue("@updated", DateTime.Now);
// Execute the query
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
var sf = new StackFrame();
var methodBase = sf.GetMethod();
Database.InsertErrorToDb(methodBase.Name, ex.Message, ex.ToString());
}
}
