public ActionResult ReportPotholePost(PotholeViewModel viewModel)
{
if (!Request.IsAuthenticated)
{
return(RedirectToAction("Index"));
}
//Pothole pothole = new Pothole();
//pothole.UserName = userName;
Pothole p = new Pothole();
p.UserName = viewModel.UserName;
p.PotholeDesc = viewModel.PotholeDesc;
p.Latitude = viewModel.Latitude;
p.Longitude = viewModel.Longitude;
p.Severity = viewModel.Severity;
p.Street1 = viewModel.Street1;
p.Street2 = viewModel.Street2;
p.LocationDesc = viewModel.LocationDesc;
p.ReportedDate = viewModel.ReportedDate;
p.InspectedDate = viewModel.InspectedDate;
p.RepairedDate = viewModel.RepairedDate;
p.IsValidated = Convert.ToBoolean(viewModel.IsValidated);
bool confirm = potholeDAL.ReportPothole(p);
return(RedirectToAction("Index", "Home"));
}
public PotholeViewModel SearchValidPotHoles(PotholeViewModel viewModel)
{
List <Pothole> allPotholes = new List <Pothole>();
try
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
//string query = BuildSqlCommand(viewModel, conn);
SqlCommand cmd = BuildSqlCommand(viewModel, conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Pothole p = MapRows(reader);
allPotholes.Add(p);
}
}
}
catch (SqlException ex)
{
throw;
}
viewModel.PotholeList = allPotholes;
return(viewModel);
}
public ActionResult Index(PotholeViewModel viewModel)
{
PotholeViewModel newModel = new PotholeViewModel();
newModel.Roles = GetRoles();
return(View(newModel));
}
public ActionResult ReportPothole(PotholeViewModel viewModel)
{
if (!Request.IsAuthenticated)
{
return(RedirectToAction("Index"));
}
PotholeViewModel newModel = new PotholeViewModel();
newModel.Roles = GetRoles();
return(View(newModel));
}
public ActionResult AdminPotholeEdit(PotholeViewModel viewModel)
{
if (!Request.IsAuthenticated || User.IsInRole("citizen") || User.IsInRole("crew_member"))
{
return(RedirectToAction("Index"));
}
if (viewModel == null)
{
viewModel = new PotholeViewModel();
}
return(View(viewModel));
}
public IHttpActionResult SearchValidPotHoles(bool isvalidated, string userName, string street1, string street2, string locationDesc, string potholeDesc, int?severity)
{
PotholeViewModel tempModel = new PotholeViewModel();
tempModel.IsValidated = isvalidated;
tempModel.UserName = userName;
tempModel.Street1 = street1;
tempModel.Street2 = street2;
tempModel.LocationDesc = locationDesc;
tempModel.PotholeDesc = potholeDesc;
tempModel.Severity = severity;
var pothole = potholeDAL.SearchValidPotHoles(tempModel).PotholeList;
return(Ok(pothole));
}
public IHttpActionResult SearchValidPotHoles(bool?isvalidated, string userName, string street1, string street2, string locationDesc, string potholeDesc, int?severity, int?potholeId, string latitude, string longitude, string reportedDate, string inspectedDate, string repairedDate)
{
PotholeViewModel tempModel = new PotholeViewModel();
tempModel.IsValidated = isvalidated;
tempModel.UserName = userName;
tempModel.Street1 = street1;
tempModel.Street2 = street2;
tempModel.LocationDesc = locationDesc;
tempModel.PotholeDesc = potholeDesc;
tempModel.Severity = severity;
//tempModel.ReportedDate = reportedDate;
//tempModel.InspectedDate = inspectedDate;
//tempModel.RepairedDate = repairedDate;
//tempModel.Latitude = latitude;
//tempModel.Longitude = longitude;
tempModel.PotholeId = potholeId;
var returnPothole = potholeDAL.SearchValidPotHoles(tempModel).PotholeList;
return(Ok(returnPothole));
}
private SqlCommand BuildSqlCommand(PotholeViewModel viewModel, SqlConnection conn)
{
SqlCommand cmd = new SqlCommand();
string query = "SELECT PotHole.PotHole_Id, PotHole.UserId, Users.UserName, PotHole.PotHoleDesc, PotHole.Lat, PotHole.Long, PotHole.Severity, PotHole.Street1, PotHole.Street2, PotHole.LocationDesc, PotHole.DateReported, PotHole.InspectedDate, PotHole.RepairDate, PotHole.IsValidated FROM PotHole JOIN Users ON PotHole.UserId = Users.UserId WHERE 1=1";
if (viewModel.PotholeId != null)
{
query += " AND PotHole_Id = @potholeId";
cmd.Parameters.AddWithValue("@potholeId", viewModel.PotholeId);
}
if (viewModel.UserId != null)
{
query += " AND UserId = @userId";
cmd.Parameters.AddWithValue("@userId", viewModel.UserId);
}
if (!String.IsNullOrEmpty(viewModel.UserName))
{
viewModel.UserName = "******" + viewModel.UserName + "%";
query += " AND users.UserName LIKE @userName";
cmd.Parameters.AddWithValue("@userName", viewModel.UserName);
}
//-------------------------------------------
if (!String.IsNullOrEmpty(viewModel.PotholeDesc))
{
viewModel.PotholeDesc = "%" + viewModel.PotholeDesc + "%";
query += " AND PotHoleDesc LIKE @potholeDesc";
cmd.Parameters.AddWithValue("@potholeDesc", viewModel.PotholeDesc);
}
if (viewModel.Latitude != null)
{
query += " AND Lat = @latitude";
cmd.Parameters.AddWithValue("@latitude", viewModel.Latitude);
}
if (viewModel.Longitude != null)
{
query += " AND Long = @longitude";
cmd.Parameters.AddWithValue("@longitude", viewModel.Longitude);
}
if (viewModel.Severity != null && viewModel.Severity != -1)
{
query += " AND Severity = @severity";
cmd.Parameters.AddWithValue("@severity", viewModel.Severity);
}
if (!String.IsNullOrEmpty(viewModel.Street1))
{
viewModel.Street1 = "%" + viewModel.Street1 + "%";
query += " AND Street1 LIKE @street1";
cmd.Parameters.AddWithValue("@street1", viewModel.Street1);
}
if (!String.IsNullOrEmpty(viewModel.Street2))
{
viewModel.Street2 = "%" + viewModel.Street2 + "%";
query += " AND Street2 LIKE @street2";
cmd.Parameters.AddWithValue("@street2", viewModel.Street2);
}
//----------------------------------------------
if (!String.IsNullOrEmpty(viewModel.LocationDesc))
{
viewModel.LocationDesc = "%" + viewModel.LocationDesc + "%";
query += " AND LocationDesc LIKE @locationDesc";
cmd.Parameters.AddWithValue("@locationDesc", viewModel.LocationDesc);
}
if (viewModel.ReportedDate != null)
{
query += " AND DateReported = @reportedDate";
cmd.Parameters.AddWithValue("@reportedDate", viewModel.ReportedDate);
}
if (viewModel.InspectedDate != null)
{
query += " AND InspectedDate = @inspectedDate";
cmd.Parameters.AddWithValue("@inspectedDate", viewModel.InspectedDate);
}
if (viewModel.RepairedDate != null)
{
query += " AND RepairDate = @repairedDate";
cmd.Parameters.AddWithValue("@repairedDate", viewModel.RepairedDate);
}
else if (viewModel.RepairedDate == null)
{
query += " AND RepairDate IS NULL";
}
if (viewModel.IsValidated != null)
{
query += " AND IsValidated = @isValidated";
cmd.Parameters.AddWithValue("@isValidated", viewModel.IsValidated);
}
query += " ORDER BY Severity DESC;";
cmd.CommandText = query;
cmd.Connection = conn;
return(cmd);
}
