public List<RouteModel> GetRoutes()
{
string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
List<RouteModel> result = new List<RouteModel>();
List<PointsOfInterestModel> pois = GetPointsOfInterest();
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(
"SELECT RouteID, RouteName, RouteDesc, From_PointOfInterestID, To_PointOfInterestID FROM dbo.Route;",
connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
RouteModel model = new RouteModel();
model.RouteID = Convert.ToInt32(reader["RouteID"]);
model.RouteName = reader["RouteName"].ToString();
model.RouteDescription = reader["RouteDesc"].ToString();
model.FromID = (int)reader["From_PointOfInterestID"];
model.From = pois.First(p => p.PointsOfInterestID == (int)reader["From_PointOfInterestID"]).PointsOfInterestName;
model.ToID = (int)reader["To_PointOfInterestID"];
model.To = pois.First(p => p.PointsOfInterestID == (int)reader["From_PointOfInterestID"]).PointsOfInterestName;
result.Add(model);
}
}
reader.Close();
}
return result;
}
public JsonResult SaveRoute(string data)
{
RouteViewModel input = Newtonsoft.Json.JsonConvert.DeserializeObject<RouteViewModel>(data);
List<string> errors = new List<string>();
if (string.IsNullOrWhiteSpace(input.RouteName))
{
errors.Add("Route name is required");
}
if (input.FromID <= 0)
{
errors.Add("Starting location is required");
}
if (input.ToID <= 0)
{
errors.Add("Destination location is required");
}
if (input.FromID == input.ToID)
{
errors.Add("Starting and destination location should be different");
}
if (input.CheckPoints != null)
{
foreach (var item in input.CheckPoints)
{
if (string.IsNullOrWhiteSpace(item.CheckPointName))
{
errors.Add("All check points should have a name");
}
}
}
if (errors.Count > 0)
{
return Json(new
{
success = false,
errors = errors
});
}
var model = new RouteModel();
model.RouteName = input.RouteName;
model.RouteDescription = input.RouteDesc;
model.FromID = input.FromID;
model.ToID = input.ToID;
List<CheckPointsModel> checkPoints = new List<CheckPointsModel>();
foreach (var item in input.CheckPoints)
{
checkPoints.Add(new CheckPointsModel() { CheckPointName = item.CheckPointName, CheckPointDesc = item.CheckPointDesc });
}
_routeService.SaveRoute(model, checkPoints);
return Json(new
{
success = true
});
}
public bool SaveRoute(RouteModel model, List<CheckPointsModel> checkPoints)
{
try
{
string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
string query = "INSERT INTO dbo.Route (RouteName, RouteDesc, From_PointOfInterestID, To_PointOfInterestID) " +
"VALUES (@RouteName, @RouteDesc, @From_PointOfInterestID, @To_PointOfInterestID) ";
using (SqlConnection cn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(query, cn))
{
cmd.Parameters.Add("@RouteName", SqlDbType.VarChar, 50).Value = model.RouteName;
cmd.Parameters.Add("@RouteDesc", SqlDbType.VarChar, 1000).Value = model.RouteDescription;
cmd.Parameters.Add("@From_PointOfInterestID", SqlDbType.Int).Value = model.FromID;
cmd.Parameters.Add("@To_PointOfInterestID", SqlDbType.Int).Value = model.ToID;
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}
var routes = GetRoutes();
if (routes != null)
{
var routeID = routes.OrderByDescending(r => r.RouteID).First().RouteID;
foreach (var item in checkPoints)
{
item.RouteID = routeID;
SaveCheckPoint(item);
}
}
}
catch (Exception)
{
return false;
}
return true;
}