public bool checkLogin(string login, string password)
{
Database.Database_DAO.Database db = new Database.Database_DAO.Database();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_SELECT_LOGIN);
command.Parameters.AddWithValue("@login", login);
SqlDataReader reader = db.Select(command);
Collection <Database.Client> Clients = Database.Database_DAO.ClientTable.Read(reader);
Database.Client Client = null;
if (Clients.Count == 1)
{
Client = Clients[0];
}
else
{
return(false);
}
reader.Close();
db.Close();
if (Client.password == password)
{
return(true);
}
else
{
return(false);
}
}
public static void UpdateAvailable(string plate)
{
Database db = new Database();
db.Connect();
db.BeginTransaction();
try
{
SqlCommand command = db.CreateCommand(SQL_SPEC_ORDER);
command.Parameters.AddWithValue("@truck_plate", plate);
//date is fixed for testing
command.Parameters.AddWithValue("@current_date", "2016-05-03");
SqlDataReader reader = db.Select(command);
Collection <Orders> orders = OrdersTable.Read(reader);
reader.Close();
Console.WriteLine("Changed orders: (order_id, start_date)");
foreach (Orders o in orders)
{
Console.WriteLine(o.order_id + " " + o.start_date);
SqlCommand command2 = db.CreateCommand(SQL_UPDATE_ORDER_TRUCK);
command2.Parameters.AddWithValue("@order_id", o.order_id);
db.ExecuteNonQuery(command2);
}
SqlCommand command3 = db.CreateCommand(SQL_UPDATE_TRUCK_AVAILABLE);
command3.Parameters.AddWithValue("@plate", plate);
db.ExecuteNonQuery(command3);
Console.WriteLine("Executed");
db.EndTransaction();
}catch (Exception e)
{
Console.WriteLine("Sth wrong");
db.Rollback();
}
db.Close();
}
/// <summary>
/// Update the record.
/// </summary>
/// <summary>
/// Select the records.
/// </summary>
public static Collection <Place> Select()
{
Database db = new Database();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_SELECT);
SqlDataReader reader = db.Select(command);
Collection <Place> places = Read(reader);
reader.Close();
db.Close();
Console.WriteLine("List of all the places");
foreach (Place p in places)
{
Console.WriteLine(p.place_id + " " + p.client_id + " " + p.city + " " + p.street + " " + p.numer + " " + p.ZIP);
}
return(places);
}
public static void Delete(int client_id)
{
Database db = new Database();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_future_orders);
command.Parameters.AddWithValue("@client_id", client_id);
command.Parameters.AddWithValue("@current_date", Convert.ToDateTime("2016-04-01"));
SqlDataReader reader = db.Select(command);
Collection <Orders> orders = Read(reader);
reader.Close();
foreach (Orders o in orders)
{
Console.WriteLine("order_id: " + o.order_id);
}
int selected_order_id = id();
SqlCommand command2 = db.CreateCommand("delete_order");
command2.CommandType = CommandType.StoredProcedure;
SqlParameter selected_order = new SqlParameter();
selected_order.ParameterName = "@order_id";
selected_order.DbType = DbType.Int32;
selected_order.Value = selected_order_id;
selected_order.Direction = ParameterDirection.Input;
command2.Parameters.Add(selected_order);
int ret = db.ExecuteNonQuery(command2);
Console.WriteLine("Deleted the " + selected_order_id + " order");
db.Close();
}
public static Collection <Orders> Select()
{
Database db = new Database();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_SELECT);
SqlDataReader reader = db.Select(command);
Collection <Orders> orders = Read(reader);
reader.Close();
db.Close();
Console.WriteLine("List of orders:");
foreach (Orders t in orders)
{
Console.WriteLine(t.order_id + " " + t.order_status);
}
return(orders);
}
public static Collection <Truck> Select()
{
Database db = new Database();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_SELECT);
SqlDataReader reader = db.Select(command);
Collection <Truck> trucks = Read(reader);
reader.Close();
db.Close();
Console.WriteLine("List of trucks:");
foreach (Truck t in trucks)
{
Console.WriteLine(t.plate + " " + t.driver_id + " " + t.brand + " " + t.model + " " + t.width + " " + t.height + " " + t.available);
}
return(trucks);
}
/// <summary>
/// Select the records.
/// </summary>
public static Collection <Driver> Select()
{
Database db = new Database();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_SELECT);
SqlDataReader reader = db.Select(command);
Collection <Driver> drivers = Read(reader);
reader.Close();
db.Close();
Console.WriteLine("All drivers: ");
foreach (Driver c in drivers)
{
Console.WriteLine(c.driver_id + " " + c.fname + " " + c.lname);
}
return(drivers);
}
public static Collection <Client> Select()
{
Database db = new Database();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_SELECT);
SqlDataReader reader = db.Select(command);
Collection <Client> clients = Read(reader);
reader.Close();
db.Close();
Console.WriteLine("All clients:");
foreach (Client c in clients)
{
Console.WriteLine(c.client_id + " " + c.fname + " " + c.lname + " " + c.email);
}
return(clients);
}
public static void Specific_places(int id)
{
Database db = new Database();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_THE_ORDERS);
command.Parameters.AddWithValue("@driver_id", id);
command.Parameters.AddWithValue("@current_date", Convert.ToDateTime("2016-05-20"));
//Current date is fixed because of the testing
SqlDataReader reader = db.Select(command);
Collection <Orders> orders = OrdersTable.Read(reader);
reader.Close();
Console.WriteLine("List of drivers places:");
foreach (Orders o in orders)
{
SqlCommand command2 = db.CreateCommand("place_details");
command2.CommandType = CommandType.StoredProcedure;
SqlParameter route = new SqlParameter();
route.ParameterName = "@route_id";
route.DbType = DbType.Int32;
route.Value = o.route_id;
route.Direction = ParameterDirection.Input;
command2.Parameters.Add(route);
SqlParameter p1city = new SqlParameter();
p1city.ParameterName = "@p1_city";
//p1city.SqlDbType = SqlDbType.VarChar;
p1city.DbType = DbType.String;
p1city.Size = 50;
p1city.Direction = ParameterDirection.Output;
command2.Parameters.Add(p1city);
SqlParameter p1street = new SqlParameter();
p1street.ParameterName = "@p1_street";
p1street.DbType = DbType.String;
p1street.Size = 50;
p1street.Direction = ParameterDirection.Output;
command2.Parameters.Add(p1street);
SqlParameter p1numer = new SqlParameter();
p1numer.ParameterName = "@p1_numer";
p1numer.DbType = DbType.String;
p1numer.Size = 50;
p1numer.Direction = ParameterDirection.Output;
command2.Parameters.Add(p1numer);
SqlParameter p2city = new SqlParameter();
p2city.ParameterName = "@p2_city";
p2city.DbType = DbType.String;
p2city.Size = 50;
p2city.Direction = ParameterDirection.Output;
command2.Parameters.Add(p2city);
SqlParameter p2street = new SqlParameter();
p2street.ParameterName = "@p2_street";
p2street.DbType = DbType.String;
p2street.Size = 50;
p2street.Direction = ParameterDirection.Output;
command2.Parameters.Add(p2street);
SqlParameter p2numer = new SqlParameter();
p2numer.ParameterName = "@p2_numer";
p2numer.DbType = DbType.String;
p2numer.Size = 50;
p2numer.Direction = ParameterDirection.Output;
command2.Parameters.Add(p2numer);
db.ExecuteNonQuery(command2);
string result1 = command2.Parameters["@p1_city"].Value.ToString();
string result2 = command2.Parameters["@p1_street"].Value.ToString();
string result3 = command2.Parameters["@p1_numer"].Value.ToString();
string result4 = command2.Parameters["@p2_city"].Value.ToString();
string result5 = command2.Parameters["@p2_street"].Value.ToString();
string result6 = command2.Parameters["@p2_numer"].Value.ToString();
Console.WriteLine(result1 + " " + result2 + " " + result3 + " - " + result4 + " " + result5 + " " + result6);
}
db.Close();
}
