/// <summary>
/// Добавляет новый заказ.
/// </summary>
/// <param name="NewOrder"></param>
public void AddNewOrderDB(OrderClass NewOrder)
{
using (SQLiteConnection DBConnection = new SQLiteConnection("data source=" + DBFileName))
{
int LastAddedOrder;
DBConnection.Open();
using (SQLiteCommand Command = new SQLiteCommand(DBConnection))
{
Command.CommandText = @"INSERT INTO orders (date, carID, driverID, duration, clientID, address) VALUES ('" +
NewOrder.DataRequest + "','" +
NewOrder.CarRequest.IDCar + "','" +
NewOrder.DriverRequest.DriverDBID + "','" +
NewOrder.TimeRequest + "','" +
NewOrder.CustomerRequest.IDcustomer + "','" +
NewOrder.AddressRequest.ToUpper() + "');";
MyDBLogger("Create order with SQL-command: " + Command.CommandText);
Command.ExecuteNonQuery();
Command.CommandText = @"SELECT ID from orders ORDER by ID DESC LIMIT 1;";
MyDBLogger("Get last order with SQL-command: " + Command.CommandText);
using (SQLiteDataReader Reader = Command.ExecuteReader())
{
Reader.Read();
LastAddedOrder = Reader.GetInt32(0);
MyDBLogger("Last record Orders ID is: " + LastAddedOrder);
}
if (NewOrder.KidsChair)
{
Command.CommandText = @"INSERT INTO additionalServicesBinding (orderID, additionalServicesID) VALUES (" + LastAddedOrder + "," + 1 + ");";
MyDBLogger("Order service (KidsChair) SQL-command: " + Command.CommandText);
Command.ExecuteNonQuery();
}
if (NewOrder.WinterTires)
{
Command.CommandText = @"INSERT INTO additionalServicesBinding (orderID, additionalServicesID) VALUES (" + LastAddedOrder + "," + 2 + ");";
MyDBLogger("Order service (WinterTires) SQL-command: " + Command.CommandText);
Command.ExecuteNonQuery();
}
if (NewOrder.SportFastenings)
{
Command.CommandText = @"INSERT INTO additionalServicesBinding (orderID, additionalServicesID) VALUES (" + LastAddedOrder + "," + 3 + ");";
MyDBLogger("Order service (SportFastenings) SQL-command: " + Command.CommandText);
Command.ExecuteNonQuery();
}
if (NewOrder.Gps)
{
Command.CommandText = @"INSERT INTO additionalServicesBinding (orderID, additionalServicesID) VALUES (" + LastAddedOrder + "," + 4 + ");";
MyDBLogger("Order service (Gps) SQL-command: " + Command.CommandText);
Command.ExecuteNonQuery();
}
}
}
}
/// <summary>
/// Возвращает экземпляр заказа по ID.
/// </summary>
/// <param name="OrderID"></param>
/// <returns></returns>
public OrderClass ReadOrderDB(string OrderID)
{
OrderClass ReadOrder = new OrderClass();
ReadOrder.IDRequest = OrderID;
using (SQLiteConnection DBConnection = new SQLiteConnection("data source=" + DBFileName))
{
DBConnection.Open();
using (SQLiteCommand Command = new SQLiteCommand(DBConnection))
{
Command.CommandText = @"SELECT date, duration, address, carID, clientID, driverID FROM orders WHERE ID = '" + ReadOrder.IDRequest + "';";
MyDBLogger("Select Order by ID: " + Command.CommandText);
using (SQLiteDataReader Reader = Command.ExecuteReader())
{
Reader.Read();
ReadOrder.DataRequest = Reader.GetString(0);
ReadOrder.TimeRequest = Reader.GetValue(1).ToString();
ReadOrder.AddressRequest = Reader.GetString(2);
ReadOrder.CarRequest = ReadCarDB(Reader.GetValue(3).ToString());
ReadOrder.CustomerRequest = ReadCustomerDB(Reader.GetValue(4).ToString());
ReadOrder.DriverRequest = ReadDriverDB(Reader.GetValue(5).ToString());
}
// Считываем дополнительные услуги
Command.CommandText = @"SELECT additionalServicesID FROM additionalServicesBinding WHERE orderID = '" + ReadOrder.IDRequest + "';";
MyDBLogger("Select additional services for order: " + Command.CommandText);
using (SQLiteDataReader Reader = Command.ExecuteReader())
{
while (Reader.Read())
{
if (Reader.GetInt32(0) == 1)
{
ReadOrder.KidsChair = true;
}
if (Reader.GetInt32(0) == 2)
{
ReadOrder.WinterTires = true;
}
if (Reader.GetInt32(0) == 3)
{
ReadOrder.SportFastenings = true;
}
if (Reader.GetInt32(0) == 4)
{
ReadOrder.Gps = true;
}
}
}
}
}
return(ReadOrder);
}
/// <summary>
/// Метод для редактирования заказов.
/// </summary>
/// <param name="OrderToEdit"></param>
public void EditOrderDB(OrderClass OrderToEdit)
{
using (SQLiteConnection DBConnection = new SQLiteConnection("data source=" + DBFileName))
{
DBConnection.Open();
using (SQLiteCommand Command = new SQLiteCommand(DBConnection))
{
Command.CommandText = @"UPDATE orders SET " +
"date = '" + OrderToEdit.DataRequest + "', " +
"carID = '" + OrderToEdit.CarRequest.IDCar + "', " +
"driverID = '" + OrderToEdit.DriverRequest.DriverDBID + "', " +
"duration = '" + OrderToEdit.TimeRequest + "', " +
"clientID = '" + OrderToEdit.CustomerRequest.IDcustomer + "', " +
"address = '" + OrderToEdit.AddressRequest.ToUpper() + "' " +
"WHERE ID = '" + OrderToEdit.IDRequest + "';";
MyDBLogger("Edit order with SQL-command: " + Command.CommandText);
Command.ExecuteNonQuery();
/// Удаляем доп. услуги.
Command.CommandText = @"DELETE FROM additionalServicesBinding WHERE driverID = '" + OrderToEdit.IDRequest + "';";
MyDBLogger("Delete additional services with SQL-command: " + Command.CommandText);
/// Устанавливаем доп. услуги.
if (OrderToEdit.KidsChair)
{
Command.CommandText = @"INSERT INTO additionalServicesBinding (orderID, additionalServicesID) VALUES (" + OrderToEdit.IDRequest + "," + 1 + ");";
MyDBLogger("Order service (KidsChair) SQL-command: " + Command.CommandText);
Command.ExecuteNonQuery();
}
if (OrderToEdit.WinterTires)
{
Command.CommandText = @"INSERT INTO additionalServicesBinding (orderID, additionalServicesID) VALUES (" + OrderToEdit.IDRequest + "," + 2 + ");";
MyDBLogger("Order service (WinterTires) SQL-command: " + Command.CommandText);
Command.ExecuteNonQuery();
}
if (OrderToEdit.SportFastenings)
{
Command.CommandText = @"INSERT INTO additionalServicesBinding (orderID, additionalServicesID) VALUES (" + OrderToEdit.IDRequest + "," + 3 + ");";
MyDBLogger("Order service (SportFastenings) SQL-command: " + Command.CommandText);
Command.ExecuteNonQuery();
}
if (OrderToEdit.Gps)
{
Command.CommandText = @"INSERT INTO additionalServicesBinding (orderID, additionalServicesID) VALUES (" + OrderToEdit.IDRequest + "," + 4 + ");";
MyDBLogger("Order service (Gps) SQL-command: " + Command.CommandText);
Command.ExecuteNonQuery();
}
}
}
}
/// <summary>
/// Метод для удаления заказов.
/// </summary>
/// <param name="OrderToEdit"></param>
public void DeleteOrderDB(OrderClass OrderToDelete)
{
using (SQLiteConnection DBConnection = new SQLiteConnection("data source=" + DBFileName))
{
DBConnection.Open();
using (SQLiteCommand Command = new SQLiteCommand(DBConnection))
{
Command.CommandText = @"DELETE FROM orders WHERE ID = " + OrderToDelete.IDRequest + ";";
MyDBLogger("Delete Order by ID: " + Command.CommandText);
Command.ExecuteNonQuery();
}
}
}