public ActionResult Create(Waybil waybil)
{
try
{
SelectList bus = new SelectList(waybilDAO.GetAllBus());
ViewBag.AllBus = bus;
IEnumerable <Route> route = waybilDAO.GetAllRoute();
ViewBag.AllRoute = new SelectList(route, "id", "routeName");
IEnumerable <Driver> drivers = waybilDAO.GetAllDriver();
ViewBag.AllDriver = new SelectList(drivers, "personnelNumber", "LastName");
IEnumerable <Conductor> conductors = waybilDAO.GetAllConductor();
ViewBag.AllConductor = new SelectList(conductors, "personnelNumber", "LastName");
if (waybilDAO.AddWaybil(waybil))
{
return(RedirectToAction("Index"));
}
else
{
return(View("Create"));
}
}
catch
{
return(View("Create"));
}
}
public ActionResult Edit(int id, Waybil waybil)
{
try
{
IEnumerable <string> bus = waybilDAO.GetAllBusPlusOnRoat(id);
ViewBag.AllBus = new SelectList(bus);
IEnumerable <Route> route = waybilDAO.GetAllRoute();
ViewBag.AllRoute = new SelectList(route, "id", "routeName");
IEnumerable <Driver> drivers = waybilDAO.GetAllDriverPlusOnRoat(id);
ViewBag.AllDriver = new SelectList(drivers, "personnelNumber", "LastName");
IEnumerable <Conductor> conductors = waybilDAO.GetAllConductorPlusOnRoat(id);
ViewBag.AllConductor = new SelectList(conductors, "personnelNumber", "LastName");
if (waybilDAO.UpdateWaybill(id, waybil))
{
return(RedirectToAction("Index"));
}
else
{
return(View("Edit"));
}
}
catch
{
return(View("Edit"));
}
}
public bool UpdateWaybill(int id, Waybil waybil)
{
bool result = true;
Connect();
try
{
string sql = "UPDATE Waybil SET date=@date,RouteId=@RouteId, " +
"DriverId=@DriverId, ConductorId=@ConductorId, BusId=@BusId WHERE id=@Id";
SqlCommand cmd_SQL = new SqlCommand(sql, Connection);
cmd_SQL.Parameters.AddWithValue("@Id", id);
cmd_SQL.Parameters.AddWithValue("@date", waybil.Date);
cmd_SQL.Parameters.AddWithValue("@DriverId", waybil.DriverId);
cmd_SQL.Parameters.AddWithValue("@ConductorId", waybil.ConductorId);
cmd_SQL.Parameters.AddWithValue("@BusId", waybil.BusId);
cmd_SQL.Parameters.AddWithValue("@RouteId", waybil.RoutId);
cmd_SQL.ExecuteNonQuery();
}
catch (Exception e)
{
log.Error("ERROR: " + e.Message);
}
finally
{
Disconnect();
}
return(result);
}
public bool AddWaybil(Waybil waybil)
{
bool result = true;
Connect();
log.Info("Вызывается метод который добавляет новую запись в таблицу Путевых листов");
try
{
string sql = "INSERT INTO Waybil (RouteId, DriverId, ConductorId, BusId, Date) VALUES (@RouteId, @DriverId, @ConductorId, @BusId, @Date);";
SqlCommand cmd_SQL = new SqlCommand(sql, Connection);
cmd_SQL.Parameters.AddWithValue("@RouteId", waybil.RoutId);
cmd_SQL.Parameters.AddWithValue("@DriverId", waybil.DriverId);
cmd_SQL.Parameters.AddWithValue("@ConductorId", waybil.ConductorId);
cmd_SQL.Parameters.AddWithValue("@BusId", waybil.BusId);
cmd_SQL.Parameters.AddWithValue("@Date", waybil.Date);
cmd_SQL.ExecuteNonQuery();
}
catch (SqlException e)
{
log.Error("ERROR: " + e.Message);
result = false;
}
finally
{
Disconnect();
}
if (result)
{
Connect();
try
{
string sql = "UPDATE Bus SET Status=@Status where numberPlate='@BusId';";
sql += "UPDATE Conductor SET onRoute=@onRoute1 where Id=@ConductorId;";
sql += "UPDATE Driver SET onRoute=@onRoute2 where Id=@DriverId;";
SqlCommand cmd_SQL = new SqlCommand(sql, Connection);
cmd_SQL.Parameters.AddWithValue("@BusId", waybil.BusId);
cmd_SQL.Parameters.AddWithValue("@ConductorId", waybil.ConductorId);
cmd_SQL.Parameters.AddWithValue("@DriverId", waybil.DriverId);
cmd_SQL.Parameters.AddWithValue("@Status", true);
cmd_SQL.Parameters.AddWithValue("@onRoute1", true);
cmd_SQL.Parameters.AddWithValue("@onRoute2", true);
cmd_SQL.ExecuteNonQuery();
}
catch (SqlException e)
{
log.Error("ERROR: " + e.Message);
result = false;
}
finally
{
Disconnect();
}
}
return(result);
}
public bool DeleteWaybil(int id)
{
bool result = true;
log.Info("Вызывается метод который удаляет запись в таблице Путевых листов");
WaybillDAO waybillD = new WaybillDAO();
Waybil waybil = waybillD.GetById(id);
Connect();
try
{
string sql = "UPDATE Bus SET Status=@Status where numberPlate='@BusId';";
sql += "UPDATE Conductor SET onRoute=@onRoute1 where Id=@ConductorId;";
sql += "UPDATE Driver SET onRoute=@onRoute2 where Id=@DriverId;";
SqlCommand cmd_SQL = new SqlCommand(sql, Connection);
cmd_SQL.Parameters.AddWithValue("@BusId", waybil.BusId);
cmd_SQL.Parameters.AddWithValue("@ConductorId", waybil.ConductorId);
cmd_SQL.Parameters.AddWithValue("@DriverId", waybil.DriverId);
cmd_SQL.Parameters.AddWithValue("@Status", false);
cmd_SQL.Parameters.AddWithValue("@onRoute1", false);
cmd_SQL.Parameters.AddWithValue("@onRoute2", false);
cmd_SQL.ExecuteNonQuery();
}
catch (SqlException e)
{
log.Error("ERROR: " + e.Message);
result = false;
}
finally
{
Disconnect();
}
if (result)
{
Connect();
try
{
string sql = "DELETE FROM Waybil WHERE Id =@id";
SqlCommand cmd_SQL = new SqlCommand(sql, Connection);
cmd_SQL.Parameters.AddWithValue("@id", id);
cmd_SQL.ExecuteNonQuery();
}
catch (SqlException e)
{
log.Error("ERROR: " + e.Message);
result = false;
}
finally
{
Disconnect();
}
}
return(result);
}
public List <Waybil> GetAllWaybil()
{
Connect();
log4net.Config.DOMConfigurator.Configure();
log.Info("Вызывается метод который возвращает список все путевые листы.");
List <Waybil> waybilList = new List <Waybil>();
SqlCommand commandRead = new SqlCommand("SELECT*FROM Waybil;", Connection);
SqlDataReader reader = commandRead.ExecuteReader();
try
{
if (reader.HasRows)
{
while (reader.Read())
{
Waybil waybil = new Waybil();
waybil.Id = Convert.ToInt32(reader["id"]);
waybil.RoutId = Convert.ToInt32(reader["RouteId"]);
waybil.DriverId = Convert.ToInt32(reader["DriverId"]);
waybil.ConductorId = Convert.ToInt32(reader["ConductorId"]);
waybil.BusId = Convert.ToString(reader["BusId"]);
waybil.Date = Convert.ToDateTime(reader["Date"]);
waybil.Route = new RouteDAO().GetById(waybil.RoutId);
waybil.Driver = new DriverDAO().GetById(waybil.DriverId);
waybil.Conductor = new ConductorDAO().GetById(waybil.ConductorId);
waybil.Bus = new BusDAO().GetById(waybil.BusId);
waybilList.Add(waybil);
}
}
}
catch (SqlException e)
{
log.Error("ERROR: " + e.Message);
}
finally
{
reader.Close();
Disconnect();
}
return(waybilList);
}
public Waybil GetById(int id)
{
Connect(); Waybil waybil = new Waybil();
string query = "SELECT*FROM Waybil where id=@id";
SqlCommand commandRead = new SqlCommand(query, Connection);
commandRead.Parameters.AddWithValue("@id", id);
SqlDataReader reader = commandRead.ExecuteReader();
try
{
if (reader.HasRows)
{
while (reader.Read())
{
waybil.Id = Convert.ToInt32(reader["id"]);
waybil.RoutId = Convert.ToInt32(reader["RouteId"]);
waybil.DriverId = Convert.ToInt32(reader["DriverId"]);
waybil.ConductorId = Convert.ToInt32(reader["ConductorId"]);
waybil.BusId = Convert.ToString(reader["BusId"]);
waybil.Date = Convert.ToDateTime(reader["Date"]);
waybil.Route = new RouteDAO().GetById(waybil.RoutId);
waybil.Driver = new DriverDAO().GetById(waybil.DriverId);
waybil.Conductor = new ConductorDAO().GetById(waybil.ConductorId);
waybil.Bus = new BusDAO().GetById(waybil.BusId);
}
}
}catch (SqlException e)
{
log.Error("ERROR: " + e.Message);
}
finally
{
reader.Close();
Disconnect();
}
return(waybil);
}
