public void Delete(params int[] idsToDelete)
{
if (idsToDelete.Count() == 0)
{
return;
}
StringBuilder sqlCommandBuilder = new StringBuilder("DELETE FROM rent WHERE id IN (");
for (int i = 0; i < idsToDelete.Count(); i++)
{
sqlCommandBuilder.Append(idsToDelete[i]);
if (i != idsToDelete.Count() - 1)
{
sqlCommandBuilder.Append(", ");
}
else
{
sqlCommandBuilder.Append(")");
}
}
using (DbConnection connection = DBUtils.GetMySQLDBConnection())
{
DBUtils.ExecuteCommand(sqlCommandBuilder.ToString(), connection);
}
}
public List <BlackList> GetBlackLists(int?userId = null, bool?banned = null, int?id = null)
{
StringBuilder sqlCommandBuilder = new StringBuilder("SELECT * FROM public.black_list");
if (id != null)
{
sqlCommandBuilder.Append(" WHERE id=" + id);
}
else if (userId != null && banned != null)
{
sqlCommandBuilder.Append(String.Format(" WHERE user_id='{0}'", userId));
}
else if (userId != null)
{
sqlCommandBuilder.Append(" WHERE user_id=" + userId);
}
else if (banned != null)
{
sqlCommandBuilder.Append(" WHERE banned=" + (banned.Value ? "1" : "0"));
}
List <BlackList> userDetailsList = new List <BlackList>();
using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
{
DbDataReader reader = DBUtils.ExecuteCommand(sqlCommandBuilder.ToString(), connection);
BlackList blackListToAdd = null;
try
{
while (reader.Read())
{
blackListToAdd = new BlackList()
{
Id = Convert.ToInt32(reader["id"].ToString()),
Warnings = Convert.ToInt32(reader["warnings"].ToString()),
Banned = reader["banned"].ToString().Equals("1"),
UserId = Convert.ToInt32(reader["user_id"].ToString()),
};
userDetailsList.Add(blackListToAdd);
}
}
catch (FormatException ex)
{
DebugLog.WriteLine(ex);
}
finally
{
reader.Close();
}
}
return(userDetailsList);
}
public List <Invoice> GetInvoices(int?userId = null, DateTime?date = null, int?id = null)
{
StringBuilder sqlCommandBuilder = new StringBuilder("SELECT * FROM invoice");
if (id != null)
{
sqlCommandBuilder.Append(" WHERE id=" + id);
}
else if (userId != null & date != null)
{
sqlCommandBuilder.Append(String.Format(" WHERE user_id='{0}' AND date LIKE '%{1}%'", userId, date.Value.ToShortDateString()));
}
else if (userId != null)
{
sqlCommandBuilder.Append(" WHERE user_id=" + userId);
}
else if (date != null)
{
sqlCommandBuilder.Append(String.Format(" WHERE date LIKE '%{0}%'", date.Value.ToShortDateString()));
}
List <Invoice> invoiceList = new List <Invoice>();
using (DbConnection connection = DBUtils.GetMySQLDBConnection())
{
DbDataReader reader = DBUtils.ExecuteCommand(sqlCommandBuilder.ToString(), connection);
Invoice invoiceToAdd = null;
try
{
while (reader.Read())
{
invoiceToAdd = new Models.RentInfo.Invoice()
{
Id = Convert.ToInt32(reader["id"].ToString()),
Date = Convert.ToDateTime(reader["date"].ToString()),
TotalAmount = Convert.ToDouble(reader["total_amount"].ToString()),
VatAmount = Convert.ToDouble(reader["vat_amount"].ToString()),
UserId = Convert.ToInt32(reader["user_id"].ToString()),
};
SetRents(invoiceToAdd);
invoiceList.Add(invoiceToAdd);
}
}
catch (FormatException ex)
{
DebugLog.WriteLine(ex);
}
finally
{
reader.Close();
}
}
return(invoiceList);
}
public void Update(Damage damage)
{
string sqlCommand = String.Format("UPDATE damage SET fine_amount='{0}', paid=b'{1}', date='{2}', vehicle_id='{3}', user_id='{4}' WHERE id='{5}'",
damage.FineAmount, damage.Paid ? "1" : "0", damage.Date.ToShortDateString(), damage.VehicleId, damage.UserId, damage.Id);
using (DbConnection connection = DBUtils.GetMySQLDBConnection())
{
DBUtils.ExecuteCommand(sqlCommand, connection);
}
}
public void Update(Invoice invoice)
{
string sqlCommand = String.Format("UPDATE invoice SET date='{0}', total_amount='{1}', vat_amount='{2}', user_id='{3}' WHERE id={4}",
invoice.Date, invoice.TotalAmount, invoice.VatAmount, invoice.UserId, invoice.Id);
using (DbConnection connection = DBUtils.GetMySQLDBConnection())
{
DBUtils.ExecuteCommand(sqlCommand, connection);
}
}
public void Update(InvoiceDetail invoiceDetail)
{
string sqlCommand = String.Format("UPDATE invoice_detail SET invoice_id='{0}', rents_id='{1}' WHERE id={2}",
invoiceDetail.InvoiceId, invoiceDetail.RentId, invoiceDetail.Id);
using (DbConnection connection = DBUtils.GetMySQLDBConnection())
{
DBUtils.ExecuteCommand(sqlCommand, connection);
}
}
public void Update(BlackList blackList)
{
string sqlCommand = String.Format("UPDATE public.black_list SET warnings='{0}', banned=b'{1}', user_id='{2}' WHERE id='{3}'",
blackList.Warnings, blackList.Banned ? "1" : "0", blackList.UserId, blackList.Id);
using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
{
DBUtils.ExecuteCommand(sqlCommand, connection);
}
}
public List <InvoiceDetail> GetInvoiceDetails(int?invoiceId = null, int?rentId = null, int?id = null)
{
StringBuilder sqlCommandBuilder = new StringBuilder("SELECT * FROM invoice_detail");
if (id != null)
{
sqlCommandBuilder.Append(" WHERE id=" + id);
}
else if (invoiceId != null & rentId != null)
{
sqlCommandBuilder.Append(String.Format(" WHERE invoice_id='{0}' AND rentId='{1}'", invoiceId, rentId));
}
else if (invoiceId != null)
{
sqlCommandBuilder.Append(" WHERE invoice_id=" + invoiceId);
}
else if (rentId != null)
{
sqlCommandBuilder.Append(String.Format(" WHERE rentId='{0}'", rentId));
}
List <InvoiceDetail> invoiceDetailList = new List <InvoiceDetail>();
using (DbConnection connection = DBUtils.GetMySQLDBConnection())
{
DbDataReader reader = DBUtils.ExecuteCommand(sqlCommandBuilder.ToString(), connection);
InvoiceDetail invoiceDetailToAdd = null;
try
{
while (reader.Read())
{
invoiceDetailToAdd = new Models.RentInfo.InvoiceDetail()
{
Id = Convert.ToInt32(reader["id"].ToString()),
InvoiceId = Convert.ToInt32(reader["invoice_id"].ToString()),
RentId = Convert.ToInt32(reader["rents_id"].ToString()),
};
invoiceDetailList.Add(invoiceDetailToAdd);
}
}
catch (FormatException ex)
{
DebugLog.WriteLine(ex);
}
finally
{
reader.Close();
}
}
return(invoiceDetailList);
}
public void Update(User user)
{
string sqlCommand = String.Format("UPDATE public.user SET username='******', password='******', name='{2}', surname='{3}', user_details_id='{4}'," +
"function='{5}' WHERE id='{6}'", user.Username, user.Password, user.Name, user.Surname,
user.UserDetailsId, user.UserFunction, user.Id);
using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
{
DBUtils.ExecuteCommand(sqlCommand, connection);
}
}
public void Update(Vehicle vehicle)
{
string sqlCommand = String.Format("UPDATE vehicle SET user_id={0}, type='{1}', price_day='{2}', price_hour='{3}'," +
"damaged=b'{4}', description='{5}', next_revision='{6}', license_category='{7}', name='{8}' WHERE id={9}",
vehicle.UserId == null ? "null" : vehicle.UserId.ToString(), vehicle.Type, vehicle.PriceDay, vehicle.PriceHour,
vehicle.Damaged ? "1" : "0", vehicle.Description, vehicle.NextRevision.ToShortDateString(), vehicle.LicenseCategory, vehicle.Name, vehicle.Id);
using (DbConnection connection = DBUtils.GetMySQLDBConnection())
{
DBUtils.ExecuteCommand(sqlCommand, connection);
}
}
public List <UserDetails> GetUserDetails(int?userId = null, int?id = null)
{
StringBuilder sqlCommandBuilder = new StringBuilder("SELECT * FROM user_details");
if (id != null)
{
sqlCommandBuilder.Append(" WHERE id=" + id);
}
else if (userId != null)
{
sqlCommandBuilder.Append(String.Format(" WHERE user_id='{0}'", userId));
}
List <UserDetails> damageList = new List <UserDetails>();
using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
{
DbDataReader reader = DBUtils.ExecuteCommand(sqlCommandBuilder.ToString(), connection);
UserDetails userdetailsToAdd = null;
try
{
while (reader.Read())
{
userdetailsToAdd = new UserDetails()
{
Id = Convert.ToInt32(reader["id"].ToString()),
Email = reader["email"].ToString(),
Street = reader["street"].ToString(),
City = reader["city"].ToString(),
ZipCode = reader["zipcode"].ToString(),
Country = reader["country"].ToString(),
Premium = reader["premium"].ToString().Equals("1"),
UserId = Convert.ToInt32(reader["user_id"].ToString()),
BirthDate = Convert.ToDateTime(reader["birth_date"].ToString())
};
damageList.Add(userdetailsToAdd);
}
}
catch (FormatException ex)
{
DebugLog.WriteLine(ex);
}
finally
{
reader.Close();
}
}
return(damageList);
}
public void Update(UserDetails userDetails)
{
string sqlCommand = String.Format("UPDATE user_details SET email='{0}', street='{1}', city='{2}', zipcode='{3}', country='{4}'," +
"premium=b'{5}', user_id='{6}', birth_date='{7}' WHERE id='{8}'",
userDetails.Email, userDetails.Street, userDetails.City, userDetails.ZipCode, userDetails.Country,
userDetails.Premium ? "1" : "0", userDetails.UserId, userDetails.BirthDate.ToShortDateString(), userDetails.Id);
using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
{
DBUtils.ExecuteCommand(sqlCommand, connection);
}
}
public void Update(BankAccount bankAccount)
{
string sqlCommand = String.Format("UPDATE public.bank_account SET security_number='{0}', card_type='{1}', bank_name='{2}'," +
" user_id='{3}', iban='{4}', expiry_date='{5}' WHERE id='{6}'",
bankAccount.SecurityNumber, bankAccount.CardType, bankAccount.BankName, bankAccount.UserId,
bankAccount.Iban, bankAccount.ExpiryDate.ToShortDateString(), bankAccount.Id);
using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
{
DBUtils.ExecuteCommand(sqlCommand, connection);
}
}
public void Update(Rent rent)
{
string sqlCommand = String.Format("UPDATE rent SET begin_time='{0}', end_time='{1}', returned=b'{2}', discount='{3}'," +
"total_amount='{4}', paid=b'{5}', vehicle_id='{6}', user_id='{7}' WHERE id={8}",
rent.BeginTime.ToString(), rent.EndTime != null ? rent.EndTime.Value.ToString() : "", rent.Returned ? "1" : "0",
rent.Discount, rent.TotalAmount, rent.Paid ? "1" : "0", rent.VehicleId, rent.UserId, rent.Id);
using (DbConnection connection = DBUtils.GetMySQLDBConnection())
{
DBUtils.ExecuteCommand(sqlCommand, connection);
}
}
public List <BankAccount> GetBankAccounts(int?userId = null, int?id = null)
{
StringBuilder sqlCommandBuilder = new StringBuilder("SELECT * FROM public.bank_account");
if (id != null)
{
sqlCommandBuilder.Append(" WHERE id=" + id);
}
else if (userId != null)
{
sqlCommandBuilder.Append(" WHERE user_id=" + userId);
}
List <BankAccount> bankAccountList = new List <BankAccount>();
using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
{
DbDataReader reader = DBUtils.ExecuteCommand(sqlCommandBuilder.ToString(), connection);
BankAccount bankAccountToAdd = null;
try
{
while (reader.Read())
{
bankAccountToAdd = new BankAccount()
{
Id = Convert.ToInt32(reader["id"].ToString()),
SecurityNumber = Convert.ToInt32(reader["security_number"].ToString()),
CardType = (BankAccount.CardTypes)Enum.Parse(typeof(BankAccount.CardTypes), reader["card_type"].ToString()),
BankName = reader["bank_name"].ToString(),
Iban = reader["iban"].ToString(),
ExpiryDate = Convert.ToDateTime(reader["expiry_date"].ToString()),
UserId = Convert.ToInt32(reader["user_id"].ToString()),
};
bankAccountList.Add(bankAccountToAdd);
}
}
catch (FormatException ex)
{
DebugLog.WriteLine(ex);
}
finally
{
reader.Close();
}
}
return(bankAccountList);
}
public void Insert(Invoice invoice)
{
string sqlCommand = String.Format("INSERT INTO invoice (date, total_amount, vat_amount, user_id)" +
"VALUES ('{0}','{1}','{2}','{3}'); SELECT last_insert_id()",
invoice.Date.ToString(), invoice.TotalAmount, invoice.VatAmount, invoice.UserId);
using (DbConnection connection = DBUtils.GetMySQLDBConnection())
{
DbDataReader reader = DBUtils.ExecuteCommand(sqlCommand, connection);
if (reader.Read())
{
int id = -1;
int.TryParse(reader.GetValue(0).ToString(), out id);
invoice.Id = id;
}
}
}
public void Insert(InvoiceDetail invoiceDetail)
{
string sqlCommand = String.Format("INSERT INTO invoice_detail (invoice_id, rents_id)" +
"VALUES ('{0}','{1}'); SELECT last_insert_id()",
invoiceDetail.InvoiceId, invoiceDetail.RentId);
using (DbConnection connection = DBUtils.GetMySQLDBConnection())
{
DbDataReader reader = DBUtils.ExecuteCommand(sqlCommand, connection);
if (reader.Read())
{
int id = -1;
int.TryParse(reader.GetValue(0).ToString(), out id);
invoiceDetail.Id = id;
}
}
}
public void Insert(Damage damage)
{
string sqlCommand = String.Format("INSERT INTO damage (fine_amount, paid, date, vehicle_id, user_id)" +
"VALUES ('{0}',b'{1}','{2}','{3}','{4}'); SELECT last_insert_id()",
damage.FineAmount, damage.Paid ? "1" : "0", damage.Date.ToShortDateString(), damage.VehicleId, damage.UserId);
using (DbConnection connection = DBUtils.GetMySQLDBConnection())
{
DbDataReader reader = DBUtils.ExecuteCommand(sqlCommand, connection);
if (reader.Read())
{
int id = -1;
int.TryParse(reader.GetValue(0).ToString(), out id);
damage.Id = id;
}
}
}
public void Insert(User user)
{
string sqlCommand = String.Format("INSERT INTO public.user (username, password, name, surname, user_details_id, function)" +
"VALUES ('{0}','{1}','{2}','{3}','{4}','{5}') RETURNING id",
user.Username, user.Password, user.Name, user.Surname, user.UserDetailsId, user.UserFunction);
using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
{
DbDataReader reader = DBUtils.ExecuteCommand(sqlCommand, connection);
if (reader.Read())
{
int id = -1;
int.TryParse(reader.GetValue(0).ToString(), out id);
user.Id = id;
}
}
}
public void Insert(BlackList blackList)
{
string sqlCommand = String.Format("INSERT INTO public.black_list (warnings, banned, user_id)" +
"VALUES ('{0}',b'{1}','{2}') RETURNING id",
blackList.Warnings, blackList.Banned ? "1" : "0", blackList.UserId);
using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
{
DbDataReader reader = DBUtils.ExecuteCommand(sqlCommand, connection);
if (reader.Read())
{
int id = -1;
int.TryParse(reader.GetValue(0).ToString(), out id);
blackList.Id = id;
}
}
}
public void Insert(BankAccount bankAccount)
{
string sqlCommand = String.Format("INSERT INTO public.bank_account (security_number, card_type, bank_name, user_id, iban, expiry_date)" +
"VALUES ('{0}','{1}','{2}','{3}','{4}','{5}') RETURNING id",
bankAccount.SecurityNumber, bankAccount.CardType, bankAccount.BankName, bankAccount.UserId,
bankAccount.Iban, bankAccount.ExpiryDate.ToShortDateString());
using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
{
DbDataReader reader = DBUtils.ExecuteCommand(sqlCommand, connection);
if (reader.Read())
{
int id = -1;
int.TryParse(reader.GetValue(0).ToString(), out id);
bankAccount.Id = id;
}
}
}
public void Insert(Rent rent)
{
string sqlCommand = String.Format("INSERT INTO rent (begin_time, end_time, returned, discount, total_amount, paid, vehicle_id, user_id)" +
"VALUES ('{0}','{1}',b'{2}','{3}','{4}',b'{5}','{6}','{7}'); SELECT last_insert_id()",
rent.BeginTime.ToString(), rent.EndTime != null ? rent.EndTime.Value.ToString() : "", rent.Returned ? "1" : "0",
rent.Discount, rent.TotalAmount, rent.Paid ? "1" : "0", rent.VehicleId, rent.UserId);
using (DbConnection connection = DBUtils.GetMySQLDBConnection())
{
DbDataReader reader = DBUtils.ExecuteCommand(sqlCommand, connection);
if (reader.Read())
{
int id = -1;
int.TryParse(reader.GetValue(0).ToString(), out id);
rent.Id = id;
}
}
}
public void Insert(UserDetails userDetails)
{
string sqlCommand = String.Format("INSERT INTO user_details (email, street, city, zipcode, country, premium, user_id, birth_date)" +
"VALUES ('{0}','{1}','{2}','{3}','{4}',b'{5}','{6}','{7}') RETURNING id",
userDetails.Email, userDetails.Street, userDetails.City, userDetails.ZipCode, userDetails.Country,
userDetails.Premium ? "1" : "0", userDetails.UserId, userDetails.BirthDate.ToShortDateString());
using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
{
DbDataReader reader = DBUtils.ExecuteCommand(sqlCommand, connection);
if (reader.Read())
{
int id = -1;
int.TryParse(reader.GetValue(0).ToString(), out id);
userDetails.Id = id;
}
}
}
public void Insert(Vehicle vehicle)
{
string sqlCommand = String.Format("INSERT INTO vehicle (user_id, type, price_day, price_hour, damaged," +
"description, next_revision, license_category, name)" +
"VALUES ({0},'{1}','{2}','{3}',b'{4}','{5}','{6}','{7}','{8}'); SELECT last_insert_id()",
vehicle.UserId == null ? "null" : vehicle.UserId.ToString(), vehicle.Type,
vehicle.PriceDay, vehicle.PriceHour, vehicle.Damaged ? "1" : "0",
vehicle.Description, vehicle.NextRevision.ToShortDateString(),
vehicle.LicenseCategory, vehicle.Name);
using (DbConnection connection = DBUtils.GetMySQLDBConnection())
{
DbDataReader reader = DBUtils.ExecuteCommand(sqlCommand, connection);
if (reader.Read())
{
int id = -1;
int.TryParse(reader.GetValue(0).ToString(), out id);
vehicle.Id = id;
}
}
}
public List <Rent> GetRents(int?userId = null, int?vehicleId = null, int?id = null)
{
StringBuilder sqlCommandBuilder = new StringBuilder("SELECT * FROM rent");
if (id != null)
{
sqlCommandBuilder.Append(" WHERE id=" + id);
}
else if (userId != null & vehicleId != null)
{
sqlCommandBuilder.Append(String.Format(" WHERE user_id='{0}' AND vehicle_id='{1}'", userId, vehicleId));
}
else if (userId != null)
{
sqlCommandBuilder.Append(" WHERE user_id=" + userId);
}
else if (vehicleId != null)
{
sqlCommandBuilder.Append(String.Format(" WHERE vehicle_id='{0}'", vehicleId));
}
List <Rent> rentList = new List <Rent>();
using (DbConnection connection = DBUtils.GetMySQLDBConnection())
{
DbDataReader reader = DBUtils.ExecuteCommand(sqlCommandBuilder.ToString(), connection);
Rent rentToAdd = null;
DateTime endTime = new DateTime(1, 1, 1);
try
{
while (reader.Read())
{
rentToAdd = new Models.RentInfo.Rent()
{
Id = Convert.ToInt32(reader["id"].ToString()),
BeginTime = Convert.ToDateTime(reader["begin_time"].ToString()),
Returned = reader["returned"].ToString().Equals("1"),
Discount = Convert.ToDouble(reader["discount"].ToString()),
TotalAmount = Convert.ToDouble(reader["total_amount"].ToString()),
Paid = reader["paid"].ToString().Equals("1"),
VehicleId = Convert.ToInt32(reader["vehicle_id"].ToString()),
UserId = Convert.ToInt32(reader["user_id"].ToString())
};
if (DateTime.TryParse(reader["end_time"].ToString(), out endTime))
{
rentToAdd.EndTime = endTime;
}
rentList.Add(rentToAdd);
}
}
catch (FormatException ex)
{
DebugLog.WriteLine(ex);
}
finally
{
reader.Close();
}
}
return(rentList);
}
/// <summary>
///
/// </summary>
/// <param name="username"></param>
/// <param name="function"></param>
/// <param name="id"></param>
/// <returns>empty list if nothing in database</returns>
public List <User> GetUsers(string username = null, string function = null, int?id = null)
{
StringBuilder sqlCommandBuilder = new StringBuilder("SELECT * FROM public.user");
if (id != null)
{
sqlCommandBuilder.Append(" WHERE id=" + id);
}
else if (function != null)
{
sqlCommandBuilder.Append(String.Format(" WHERE function = '{0}'", function));
}
else if (username != null)
{
sqlCommandBuilder.Append(String.Format(" WHERE username = '******'", username));
}
List <User> userList = new List <User>();
using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
{
DbDataReader reader = DBUtils.ExecuteCommand(sqlCommandBuilder.ToString(), connection);
User userToAdd = null;
try
{
while (reader.Read())
{
userToAdd = new User()
{
Id = Convert.ToInt32(reader["id"].ToString()),
Username = reader["username"].ToString(),
Password = reader["password"].ToString(),
Name = reader["name"].ToString(),
Surname = reader["surname"].ToString(),
UserFunction = (User.Function)Enum.Parse(typeof(User.Function), reader["function"].ToString()),
UserDetailsId = Convert.ToInt32(reader["user_details_id"].ToString()),
};
try
{
userToAdd.UserDetails = new UserDetailsDAO().GetUserDetails(userId: userToAdd.Id).First();
}
catch (Exception)
{
DebugLog.WriteLine("Exception didnt find userdetails for user with id " + userToAdd.Id);
}
userList.Add(userToAdd);
}
}
catch (FormatException ex)
{
DebugLog.WriteLine(ex);
}
finally
{
reader.Close();
}
}
return(userList);
}
public List <Vehicle> GetVehicles(int?userId = null, bool?damaged = null, int?id = null)
{
StringBuilder sqlCommandBuilder = new StringBuilder("SELECT * FROM vehicle");
if (id != null)
{
sqlCommandBuilder.Append(" WHERE id=" + id);
}
else if (userId != null & damaged != null)
{
sqlCommandBuilder.Append(String.Format(" WHERE user_id={0} AND damaged=b'{1}'", userId, damaged.Value ? "1" : "0"));
}
else if (userId != null)
{
sqlCommandBuilder.Append(" WHERE user_id=" + userId);
}
else if (damaged != null)
{
sqlCommandBuilder.Append(String.Format(" WHERE damaged=b'{0}'", damaged.Value ? "1" : "0"));
}
List <Vehicle> vehicleList = new List <Vehicle>();
using (DbConnection connection = DBUtils.GetMySQLDBConnection())
{
DbDataReader reader = DBUtils.ExecuteCommand(sqlCommandBuilder.ToString(), connection);
Vehicle vehicleToAdd = null;
int newUserId = -1;
try
{
while (reader.Read())
{
vehicleToAdd = new Models.RentInfo.Vehicle()
{
Id = Convert.ToInt32(reader["id"].ToString()),
Type = reader["type"].ToString(),
PriceDay = Convert.ToDouble(reader["price_day"].ToString()),
PriceHour = Convert.ToDouble(reader["price_hour"].ToString()),
Damaged = reader["damaged"].ToString().Equals("1"),
Description = reader["description"].ToString(),
NextRevision = Convert.ToDateTime(reader["next_revision"].ToString()),
LicenseCategory = (Vehicle.LicenseCategories)Enum.Parse(typeof(Vehicle.LicenseCategories), reader["license_category"].ToString()),
Name = reader["name"].ToString(),
};
if (int.TryParse(reader["user_id"].ToString(), out newUserId))
{
vehicleToAdd.UserId = newUserId;
}
vehicleList.Add(vehicleToAdd);
}
}
catch (FormatException ex)
{
DebugLog.WriteLine(ex);
}
finally
{
reader.Close();
}
}
return(vehicleList);
}
public List <Damage> GetDamages(int?userId = null, bool?paid = null, int?vehicleId = null)
{
StringBuilder sqlCommandBuilder = new StringBuilder("SELECT * FROM damage");
if (userId != null && paid != null && vehicleId != null)
{
sqlCommandBuilder.Append(String.Format(" WHERE vehicle_id='{0}' AND user_id='{1}' AND paid=b'{2}'", vehicleId, userId, paid.Value ? "1" : "0"));
}
else if (userId != null & paid != null)
{
sqlCommandBuilder.Append(String.Format(" WHERE user_id='{0}' AND paid = b'{1}'", userId, paid.Value ? "1" : "0"));
}
else if (userId != null && vehicleId != null)
{
sqlCommandBuilder.Append(String.Format(" WHERE user_id='{0}' AND vehicle_id = '{1}'", userId, vehicleId));
}
else if (vehicleId != null && paid != null)
{
sqlCommandBuilder.Append(String.Format(" WHERE vehicle_id='{0}' AND paid = b'{1}'", vehicleId, paid.Value ? "1" : "0"));
}
else if (userId != null)
{
sqlCommandBuilder.Append(" WHERE user_id=" + userId);
}
else if (vehicleId != null)
{
sqlCommandBuilder.Append(" WHERE vehicle_id=" + userId);
}
else if (paid != null)
{
sqlCommandBuilder.Append(String.Format(" WHERE paid='{0}'", paid.Value ? "1" : "0"));
}
List <Damage> damageList = new List <Damage>();
using (DbConnection connection = DBUtils.GetMySQLDBConnection())
{
DbDataReader reader = DBUtils.ExecuteCommand(sqlCommandBuilder.ToString(), connection);
Damage damageToAdd = null;
try
{
while (reader.Read())
{
damageToAdd = new Models.RentInfo.Damage()
{
Id = Convert.ToInt32(reader["id"].ToString()),
FineAmount = Convert.ToDouble(reader["fine_amount"].ToString()),
Paid = reader["paid"].ToString().Equals("1"),
Date = Convert.ToDateTime(reader["date"].ToString()),
VehicleId = Convert.ToInt32(reader["vehicle_id"].ToString()),
UserId = Convert.ToInt32(reader["user_id"].ToString()),
};
damageList.Add(damageToAdd);
}
}
catch (FormatException ex)
{
DebugLog.WriteLine(ex);
}
finally
{
reader.Close();
}
}
return(damageList);
}