//----------------------------------------------------------------------------------------------
//Static
//----------------------------------------------------------------------------------------------
#region static
public static List <ImageCar> GetList(NpgsqlConnection connection, Vehicle vehicle)
{
List <ImageCar> images = new List <ImageCar>();
if (vehicle.CarId.HasValue)
{
NpgsqlCommand command = new NpgsqlCommand($"Select image_id, picture, kind, main, description, accident from {TABLE} where car_id = :cid", connection);
command.Parameters.AddWithValue("cid", vehicle.CarId.Value);
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
ImageCar imageCar = new ImageCar(connection, vehicle);
imageCar.ImageId = reader.GetInt64(0);
imageCar.Picture = reader.IsDBNull(1) ? null : (byte[])reader.GetValue(1);
imageCar.Kind = reader.IsDBNull(2) ? null : reader.GetString(2);
imageCar.Main = reader.IsDBNull(3) ? false : reader.GetBoolean(3);
imageCar.Description = reader.IsDBNull(4) ? null : reader.GetString(4);
imageCar.Accident = reader.IsDBNull(5) ? false : reader.GetBoolean(5);
MemoryStream memoryStream = new MemoryStream(imageCar.Picture);
imageCar.Image = Image.FromStream(memoryStream);
memoryStream.Close();
images.Add(imageCar);
}
reader.Close();
}
return(images);
}
public static ImageCar GetMain(NpgsqlConnection connection, long?CarId)
{
ImageCar image = null;
if (CarId.HasValue)
{
NpgsqlCommand command = new NpgsqlCommand($"Select image_id, picture, kind, main, description from {TABLE} where car_id = :cid and main = true", connection);
command.Parameters.AddWithValue("cid", CarId.Value);
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
image = new ImageCar(connection);
image.ImageId = reader.GetInt64(0);
image.Picture = reader.IsDBNull(1) ? null : (byte[])reader.GetValue(1);
image.Kind = reader.IsDBNull(2) ? null : reader.GetString(2);
image.Main = reader.IsDBNull(3) ? false : reader.GetBoolean(3);
image.Description = reader.IsDBNull(4) ? null : reader.GetString(4);
image.Accident = reader.IsDBNull(5) ? false : reader.GetBoolean(5);
MemoryStream memoryStream = new MemoryStream(image.Picture);
image.Image = Image.FromStream(memoryStream);
memoryStream.Close();
}
reader.Close();
}
return(image);
}
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);
}