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 <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(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(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 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(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 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 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(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(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 <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 <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);
}
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);
}