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