/// <summary>
/// Vkládá informaci o nové opravě do databáze.
/// Vše je potřebné se nachází v objektu opravy (parametr <paramref name="service">service</paramref>),
/// včetné ID auta, ke kterému se vztahuje.
/// </summary>
/// <param name="service">Nová informace o opravě pro vložení do databáze</param>
/// <returns>True při úspěchu vložení</returns>
/// <exception cref="CarExpensesDatabaseException">Při chybě práce s databází</exception>
public bool addService(Service service)
{
using (OracleCommand cmdInsert = new OracleCommand())
{
try
{
string sqlInsert = "INSERT INTO \"service\" (\"car_id\", \"km\", \"cost\", \"service_type_id\", \"description\", \"date\") ";
sqlInsert += "values (:p_car_id, :p_km, :p_cost, :p_service_type_id, :p_description, to_date(:p_date, 'yyyy/mm/dd'))";
cmdInsert.CommandText = sqlInsert;
cmdInsert.Connection = connection;
OracleParameter pCarId = new OracleParameter();
pCarId.OracleDbType = OracleDbType.Decimal;
pCarId.Value = service.carId;
pCarId.ParameterName = "p_car_id";
OracleParameter pKm = new OracleParameter();
pKm.OracleDbType = OracleDbType.Decimal;
pKm.Value = service.km;
pKm.ParameterName = "p_km";
OracleParameter pCost = new OracleParameter();
pCost.OracleDbType = OracleDbType.Decimal;
pCost.Value = service.cost;
pCost.ParameterName = "p_cost";
OracleParameter pServiceTypeId = new OracleParameter();
pServiceTypeId.OracleDbType = OracleDbType.Decimal;
pServiceTypeId.Value = service.serviceTypeId;
pServiceTypeId.ParameterName = "p_service_type_id";
OracleParameter pDescription = new OracleParameter();
pDescription.Value = service.description;
pDescription.ParameterName = "p_description";
OracleParameter pDate = new OracleParameter();
//pDate.OracleDbType = OracleDbType.Date;
pDate.Value = service.date.ToString("yyyy/MM/dd");
pDate.ParameterName = "p_date";
cmdInsert.Parameters.Add(pCarId);
cmdInsert.Parameters.Add(pKm);
cmdInsert.Parameters.Add(pCost);
cmdInsert.Parameters.Add(pServiceTypeId);
cmdInsert.Parameters.Add(pDescription);
cmdInsert.Parameters.Add(pDate);
if ( cmdInsert.ExecuteNonQuery() > 0 )
{
return true;
}
else return false;
}
catch (OracleException ex)
{
switch (ex.Number)
{
case 1:
throw new CarExpensesDatabaseException("Service ID " + service.id + " already exists.");
//case 2291:
// throw new CarExpensesDatabaseException("Car ID " + gas.carId + " doesn't exist.");
case 12545:
throw new CarExpensesDatabaseException("The database is unavailable.");
default:
throw new CarExpensesDatabaseException("Database error: " + ex.Message.ToString());
}
}
catch (CarExpensesDatabaseException ex)
{
throw new CarExpensesDatabaseException(ex.Message.ToString());
}
catch (Exception ex)
{
throw new CarExpensesDatabaseException("Unexpected error: " + ex.Message.ToString());
}
}
}
/// <summary>
/// Získává všechny informace o opravách vztahující se ka autu danému parametrem <paramref name="carId">carId</paramref>
/// </summary>
/// <param name="carId">ID auta</param>
/// <returns>Seznam informací o opravách pro dané auto</returns>
/// <exception cref="CarExpensesDatabaseException">Při chybě práce s databází</exception>
public List<Service> getCarServices(int carId)
{
List<Service> serviceList = new List<Service>();
using (OracleCommand cmd = new OracleCommand())
{
try
{
cmd.Connection = connection;
cmd.CommandText = "SELECT * FROM \"service\" WHERE \"car_id\" = :p_car_id ORDER BY \"date\" DESC";
cmd.CommandType = System.Data.CommandType.Text;
OracleParameter pCarId = new OracleParameter();
pCarId.OracleDbType = OracleDbType.Decimal;
pCarId.Value = carId;
pCarId.ParameterName = "p_car_id";
cmd.Parameters.Add(pCarId);
using (OracleDataReader dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
while (dr.Read())
{
Service service = new Service();
service.id = Convert.ToInt32(dr["id"].ToString());
service.carId = Convert.ToInt32(dr["car_id"].ToString());
service.km = Convert.ToInt32(dr["km"].ToString());
service.serviceTypeId = Convert.ToInt32(dr["service_type_id"].ToString());
service.cost = Convert.ToInt32(dr["cost"].ToString());
service.date = Convert.ToDateTime(dr["date"].ToString());
service.description = dr["description"].ToString();
serviceList.Add(service);
}
}
return serviceList;
}
}
catch (OracleException ex)
{
switch (ex.Number)
{
case 12545:
throw new CarExpensesDatabaseException("The database is unavailable.");
default:
throw new CarExpensesDatabaseException("Database error: " + ex.Message.ToString());
}
}
catch (CarExpensesDatabaseException ex)
{
throw new CarExpensesDatabaseException(ex.Message.ToString());
}
catch (Exception ex)
{
throw new CarExpensesDatabaseException("Unexpected error: " + ex.Message.ToString());
}
}
}
/// <summary>
/// Získání informace o včech servisech auta
/// </summary>
/// <param name="carId">Identifikace auta, o kterém chceme informace získat</param>
/// <returns>ˇŘetězec s informacemi</returns>
/// <exception cref="CarExpensesException">Při chybě aplikace</exception>
public string getTotalServiceInfo(int carId)
{
ServiceResponse response = this.getCarServices(carId);
if (response.success)
{
Service service = new Service();
int count = 0;
foreach (Service s in response.serviceList)
{
count++;
service += s;
}
return "Total " + count + " services for " + service.cost.ToString("C") + "";
}
else
{
return "Unknown total services stats.";
}
}
/// <summary>
/// Přetížení oprerátoru, slouží ke sčítání ceny servisu
/// </summary>
/// <returns>Nový objekt servisu se sečtenýmy hodnotami ceny</returns>
public static Service operator +(Service s1, Service s2)
{
Service result = new Service();
result.cost = s1.cost + s2.cost;
result.description = "Total cost of services";
return result;
}
/// <summary>
/// Přidání nové opravy k aktuálně přihlášenému uživateli
/// </summary>
/// <param name="carId">Identifikace auta, které bylo opravováno</param>
/// <param name="km">Stav tachometru při opravě</param>
/// <param name="cost">Cena opravy</param>
/// <param name="serviceTypeId">Identifikace typu opravy</param>
/// <param name="description">Slovní popis opravy</param>
/// <param name="date">Datum opravy</param>
/// <returns>Objekt <see cref="Response"/>, kde je uložen stav akce a zpráva.</returns>
/// <exception cref="CarExpensesException">Při chybě aplikace</exception>
public Response addService(int carId, int km, int cost, int serviceTypeId, string description, DateTime date)
{
if (notLogged()) return new Response(false, "You are NOT logged in.");
if (km < 1 || cost < 1)
return new Response(false, "Km and cost can't be smaller than 1.");
Response response = new Response();
try
{
if (carDAO.userHasCar(user.id, carId) == false)
{
return new Response(false, "User ID " + user.id + " is not owner of car ID " + carId + ".");
}
Service service = new Service();
service.carId = carId;
service.km = km;
service.cost = cost;
service.serviceTypeId = serviceTypeId;
service.description = description;
service.date = date;
if (serviceDAO.addService(service) == true)
{
response.message = "Service was successfuly added.";
response.success = true;
}
else
{
response.message = "Service wasn't added.";
response.success = false;
}
}
catch (CarExpensesDatabaseException ex)
{
response.success = false;
response.message = ex.Message.ToString();
}
return response;
}