public static List <Vehicle> GetAvailableVehicles()
{
List <Vehicle> allVehicles = new List <Vehicle>();
NpgsqlCommand command = new NpgsqlCommand();
NpgsqlConnection connection = new NpgsqlConnection(ConfigurationManager.AppSettings["Connection"]);
connection.Open();
command.Connection = connection;
command.CommandText = $"Select car_id, location_id, modell, brand, hp, price, feature1, feature2, feature3, feature4, notavailable, reserved, in_use, locked from instacar.car where deleted = false and notavailable = false " +
$"except " +
$"Select c.car_id, c.location_id, c.modell, c.brand, c.hp, c.price, c.feature1, c.feature2, c.feature3, c.feature4, " +
$"c.notavailable, c.reserved, c.in_use, c.locked from {TABLE} as c inner join {TABLERENT} as r on r.car_id = c.car_id " +
$"where (dateend > current_Timestamp or dateend is null) and c.notavailable = false and c.deleted = false;";
command.Parameters.AddWithValue("t", DateTime.Now);
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
allVehicles.Add(new Vehicle(connection)
{
CarId = reader.GetInt64(0),
LocationId = reader.GetInt64(1),
Modell = reader.IsDBNull(2) ? null : reader.GetString(2),
Brand = reader.IsDBNull(3) ? null : reader.GetString(3),
HP = reader.IsDBNull(4) ? 0 : reader.GetInt64(4),
Price = reader.IsDBNull(5) ? 0 : reader.GetDouble(5),
Feature1 = reader.IsDBNull(6) ? 0 : reader.GetInt64(6),
Feature2 = reader.IsDBNull(7) ? 0 : reader.GetInt64(7),
Feature3 = reader.IsDBNull(8) ? 0 : reader.GetInt64(8),
Feature4 = reader.IsDBNull(9) ? 0 : reader.GetInt64(9),
NotAvailable = reader.IsDBNull(10) ? true : reader.GetBoolean(10),
Reserved = reader.IsDBNull(11) ? null : (DateTime?)reader.GetDateTime(11),
InUse = reader.IsDBNull(12) ? true : reader.GetBoolean(12),
Locked = reader.IsDBNull(13) ? true : reader.GetBoolean(13)
});
}
reader.Close();
connection.Close();
foreach (Vehicle v in allVehicles)
{
v.MainImage = ImageCar.GetMainImageByte(v.CarId);
}
return(allVehicles);
}
//----------------------------------------------------------------------------------------------
//Static
//----------------------------------------------------------------------------------------------
#region static
public static List <Vehicle> GetAllVehicles()
{
NpgsqlConnection connection = new NpgsqlConnection(ConfigurationManager.AppSettings["Connection"]);
connection.Open();
List <Vehicle> allVehicles = new List <Vehicle>();
Vehicle vehicle = null;
NpgsqlCommand command = new NpgsqlCommand();
command.Connection = connection;
command.CommandText = $"Select * from {TABLE} where deleted = false;";
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
allVehicles.Add(
vehicle = new Vehicle(connection)
{
CarId = reader.GetInt64(0),
LocationId = reader.GetInt64(1),
Modell = reader.IsDBNull(2) ? null : reader.GetString(2),
Brand = reader.IsDBNull(3) ? null : reader.GetString(3),
HP = reader.IsDBNull(4) ? 0 : reader.GetInt64(4),
Price = reader.IsDBNull(5) ? 0 : reader.GetDouble(5),
Feature1 = reader.IsDBNull(6) ? 0 : reader.GetInt64(6),
Feature2 = reader.IsDBNull(7) ? 0 : reader.GetInt64(7),
Feature3 = reader.IsDBNull(8) ? 0 : reader.GetInt64(8),
Feature4 = reader.IsDBNull(9) ? 0 : reader.GetInt64(9),
NotAvailable = reader.IsDBNull(10) ? true : reader.GetBoolean(10),
Reserved = reader.IsDBNull(11) ? null : (DateTime?)reader.GetDateTime(11),
InUse = reader.IsDBNull(12) ? true : reader.GetBoolean(12),
Locked = reader.IsDBNull(13) ? true : reader.GetBoolean(13)
}
);
}
reader.Close();
connection.Close();
foreach (Vehicle v in allVehicles)
{
v.MainImage = ImageCar.GetMainImageByte(v.CarId);
}
return(allVehicles);
}