public static List <VehicleEntrance> SelectAll(int limit = 1000)
{
List <VehicleEntrance> list = new List <VehicleEntrance>();
string sql = string.Format("SELECT * FROM VehicleEntrance LIMIT {0}", limit);
using (MySqlCommand command = new MySqlCommand(sql, ConnectMysql.GetInstance()))
{
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
int id = reader.GetInt32(0);
double hourPrice = reader.GetDouble(1);
string board = reader.GetString(2);
DateTime dateIn = reader.GetDateTime(3);
DateTime dateOut = reader.GetDateTime(4);
VehicleEntrance ve = new VehicleEntrance(id, board, dateIn);
ve.HourPrice = hourPrice;
if (!ve.InvalidDatetime(dateOut))
{
ve.DateOut = dateOut;
}
list.Add(ve);
}
}
}
return(list);
}
public static VehicleEntrance Select(string vehicleBoard)
{
VehicleEntrance vehicle = null;
string sql = string.Format("SELECT * FROM VehicleEntrance WHERE Board = \"{0}\"", vehicleBoard);
using (MySqlCommand command = new MySqlCommand(sql, ConnectMysql.GetInstance()))
{
using (MySqlDataReader reader = command.ExecuteReader())
{
if (!reader.Read())
{
throw new NotExecuteCommandSql("Erro na leitura de uma data do banco ou a placa do veiculo não existe na base");
}
int id = reader.GetInt32(0);
double hourPrice = reader.GetDouble(1);
DateTime dateTimeInitial = reader.GetDateTime(3);
DateTime dateTimeFinal = reader.GetDateTime(4);
double priceCharged = reader.GetDouble(5);
vehicle = new VehicleEntrance(id, vehicleBoard, dateTimeInitial);
if (!vehicle.InvalidDatetime(dateTimeFinal))
{
vehicle.DateOut = dateTimeFinal;
}
vehicle.HourPrice = hourPrice;
vehicle.PriceCharged = priceCharged;
}
}
return(vehicle);
}
public static VehicleEntrance Select(int id)
{
if (id == 0)
{
throw new NotFoundIDEntity("A chave primaria não pode ser igual a 0");
}
VehicleEntrance vehicle = null;
string sql = string.Format("SELECT * FROM VehicleEntrance WHERE id = {0}", id);
using (MySqlCommand command = new MySqlCommand(sql, ConnectMysql.GetInstance()))
{
using (MySqlDataReader reader = command.ExecuteReader())
{
if (!reader.Read())
{
throw new NotExecuteCommandSql("Erro na leitura de uma data do banco ou a placa do veiculo não existe na base");
}
double hourPrice = reader.GetDouble(1);
string board = reader.GetString(2);
DateTime dateTimeInitial = reader.GetDateTime(3);
DateTime dateTimeFinal = reader.GetDateTime(4);
double priceCharged = reader.GetDouble(5);
vehicle = new VehicleEntrance(id, board, dateTimeInitial);
vehicle.HourPrice = hourPrice;
if (!vehicle.InvalidDatetime(dateTimeFinal))
{
vehicle.DateOut = dateTimeFinal;
}
}
}
return(vehicle);
}
public void test01()
{
MySqlConnection conn = ConnectMysql.GetInstance();
Assert.IsNotNull(conn);
Assert.AreEqual(conn, ConnectMysql.GetInstance());
}
public static ValidityDateControl Select(int id)
{
if (id == 0)
{
throw new NotFoundIDEntity("A chave primaria não pode ser igual a 0");
}
ValidityDateControl dateControl = null;
string sql = string.Format(
"SELECT * FROM ValidityDateControl WHERE id = \"{0}\""
, id);
using (MySqlCommand command = new MySqlCommand(sql, ConnectMysql.GetInstance()))
{
using (MySqlDataReader reader = command.ExecuteReader())
{
if (!reader.Read())
{
throw new NotExecuteCommandSql("Erro na leitura de uma data do banco ou a chave primária não existe na base");
}
double hourPrice = reader.GetDouble(1);
DateTime dateTimeInitial = reader.GetDateTime(2);
DateTime dateTimeFinal = reader.GetDateTime(3);
dateControl = new ValidityDateControl(id, hourPrice, dateTimeInitial, dateTimeFinal);
}
}
return(dateControl);
}
public static ValidityDateControl Select(DateTime InitialDate)
{
ValidityDateControl dateControl = null;
string initialDateString = InitialDate.ToString("yyyy-MM-dd HH:mm:ss");
string sql = string.Format(
"SELECT * FROM ValidityDateControl WHERE InitialDate = \"{0}\""
, initialDateString);
using (MySqlCommand command = new MySqlCommand(sql, ConnectMysql.GetInstance()))
{
using (MySqlDataReader reader = command.ExecuteReader())
{
if (!reader.Read())
{
throw new NotExecuteCommandSql("Erro na leitura de uma data do banco");
}
int id = reader.GetInt32(0);
double hourPrice = reader.GetDouble(1);
DateTime dateTimeInitial = reader.GetDateTime(2);
DateTime dateTimeFinal = reader.GetDateTime(3);
dateControl = new ValidityDateControl(id, hourPrice, dateTimeInitial, dateTimeFinal);
}
}
return(dateControl);
}
public static void DeleteAll()
{
string sql = string.Format("DELETE FROM ValidityDateControl");
using (MySqlCommand command = new MySqlCommand(sql, ConnectMysql.GetInstance()))
{
command.CommandText = sql;
command.ExecuteNonQuery();
}
}
public static void DeleteByID(int id)
{
if (id == 0)
{
throw new NotFoundIDEntity("A objeto excluido não possue uma chave primária");
}
string sql = string.Format("DELETE FROM VehicleEntrance WHERE id = {0}", id);
using (MySqlCommand command = new MySqlCommand(sql, ConnectMysql.GetInstance()))
{
command.CommandText = sql;
command.ExecuteNonQuery();
}
}
public static void Insert(VehicleEntrance vehicle)
{
string initialDate = vehicle.DateIn.ToString("yyyy-MM-dd HH:mm:ss");
string finalDate = vehicle.DateOut.ToString("yyyy-MM-dd HH:mm:ss");
string sql = string.Format(
"INSERT INTO VehicleEntrance (HourPrice, Board, DateIn, DateOut, PriceCharged) VALUES ({0}, \"{1}\", \"{2}\", \"{3}\", {4});"
, MySqlHelper.DoubleQuoteString(vehicle.HourPrice.ToString()).Replace(",", "."), vehicle.Board, initialDate, finalDate, MySqlHelper.DoubleQuoteString(vehicle.PriceCharged.ToString()).Replace(",", "."));
using (MySqlCommand command = new MySqlCommand(sql, ConnectMysql.GetInstance()))
{
command.CommandText = sql;
command.ExecuteNonQuery();
}
}
public static void Insert(ValidityDateControl dateControl)
{
string initialDate = dateControl.InitialDate.ToString("yyyy-MM-dd HH:mm:ss");
string finalDate = dateControl.FinalDate.ToString("yyyy-MM-dd HH:mm:ss");
string sql = string.Format(
"INSERT INTO ValidityDateControl (HourPrice, InitialDate, FinalDate) VALUES ({0}, \"{1}\", \"{2}\");"
, dateControl.HourPrice.ToString().Replace(",", "."), initialDate, finalDate);
using (MySqlCommand command = new MySqlCommand(sql, ConnectMysql.GetInstance()))
{
command.CommandText = sql;
command.ExecuteNonQuery();
}
}
public static void Delete(ValidityDateControl dateControl)
{
if (dateControl.ID == 0)
{
throw new NotFoundIDEntity("A objeto excluido não possue uma chave primária");
}
string sql = string.Format("DELETE FROM ValidityDateControl WHERE id = {0}", dateControl.ID);
using (MySqlCommand command = new MySqlCommand(sql, ConnectMysql.GetInstance()))
{
command.CommandText = sql;
command.ExecuteNonQuery();
}
}
public static void Update(VehicleEntrance vehicle)
{
if (vehicle.ID == 0)
{
throw new NotFoundIDEntity("A objeto atualizado não possue uma chave primária");
}
string initialDate = vehicle.DateIn.ToString("yyyy-MM-dd HH:mm:ss");
string finalDate = vehicle.DateOut.ToString("yyyy-MM-dd HH:mm:ss");
string sql = string.Format(
"UPDATE VehicleEntrance SET HourPrice={0}, Board=\"{1}\", DateIn=\"{2}\", DateOut=\"{3}\", PriceCharged={4} WHERE id = {5};"
, MySqlHelper.DoubleQuoteString(vehicle.HourPrice.ToString()).Replace(",", "."), vehicle.Board, initialDate, finalDate, MySqlHelper.DoubleQuoteString(vehicle.PriceCharged.ToString()).Replace(",", "."), vehicle.ID);
using (MySqlCommand command = new MySqlCommand(sql, ConnectMysql.GetInstance()))
{
command.CommandText = sql;
command.ExecuteNonQuery();
}
}
public static void Update(ValidityDateControl dateControl)
{
if (dateControl.ID == 0)
{
throw new NotFoundIDEntity("A objeto atualizado não possue uma chave primária");
}
string initialDate = dateControl.InitialDate.ToString("yyyy-MM-dd HH:mm:ss");
string finalDate = dateControl.FinalDate.ToString("yyyy-MM-dd HH:mm:ss");
string sql = string.Format(
"UPDATE ValidityDateControl SET HourPrice={0}, InitialDate=\"{1}\", FinalDate=\"{2}\" WHERE id = {3};"
, dateControl.HourPrice.ToString().Replace(",", "."), initialDate, finalDate, dateControl.ID);
using (MySqlCommand command = new MySqlCommand(sql, ConnectMysql.GetInstance()))
{
command.CommandText = sql;
command.ExecuteNonQuery();
}
}
public static List <ValidityDateControl> SelectAll(int limit = 1000)
{
List <ValidityDateControl> listDateControl = new List <ValidityDateControl>();
string sql = string.Format("SELECT * FROM ValidityDateControl LIMIT {0}", limit);
using (MySqlCommand command = new MySqlCommand(sql, ConnectMysql.GetInstance()))
{
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
int id = reader.GetInt32(0);
double hourPrice = reader.GetDouble(1);
DateTime dateTimeInitial = reader.GetDateTime(2);
DateTime dateTimeFinal = reader.GetDateTime(3);
listDateControl.Add(new ValidityDateControl(id, hourPrice, dateTimeInitial, dateTimeFinal));
}
}
}
return(listDateControl);
}
void Start()
{
sql = GameObject.Find("Camera").GetComponent <ConnectMysql>();
}
