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