/// <summary>
/// Retorna os dados de consumo de água do ano desejado
/// </summary>
internal static List <WaterConsumeData> GetWaterConsume(string connectionString, string deviceId, int devicePort, int year)
{
List <WaterConsumeData> datas = new List <WaterConsumeData>();
WaterConsumeData temp;
using (var conn = new MySqlConnection(connectionString))
{
using (var cmd = conn.CreateCommand())
{
try
{
conn.Open();
cmd.CommandText = "SELECT month(created_at) as 'mes', year(created_at) as 'ano', sum(data" + (devicePort + 1) + ") as 'consumo' FROM domus.data where device_id = '" + deviceId + "' and year(created_at) = '" + year + "' group by month(created_at);";
using (MySqlDataReader dataReader = cmd.ExecuteReader())
{
while (dataReader.Read())
{
temp = Maper.MapWaterConsumeData(dataReader);
datas.Add(temp);
}
}
return(datas);
}
catch (Exception e)
{
throw e;
}
}
}
}
/// <summary>
/// Retorna as configurações da cisterna
/// </summary>
public static IrrigationConfig GetIrrigationConfig(string connectionString)
{
IrrigationConfig config;
using (var conn = new MySqlConnection(connectionString))
{
using (var cmd = conn.CreateCommand())
{
try
{
conn.Open();
cmd.CommandText = "SELECT * FROM irrigation_config order by config_id desc limit 1";
using (MySqlDataReader dataReader = cmd.ExecuteReader())
{
dataReader.Read();
config = Maper.MapIrrigationConfig(dataReader);
}
return(config);
}
catch (MySqlException e)
{
throw e;
}
}
}
}
/// <summary>
/// Retorna o ultimo registro de chuva
/// </summary>
internal static Data GetLastRainData(string connectionString, string deviceId, string dataField)
{
Data data = null;
using (var conn = new MySqlConnection(connectionString))
{
using (var cmd = conn.CreateCommand())
{
try
{
conn.Open();
cmd.CommandText = "SELECT * FROM data WHERE device_id='" + deviceId + "' AND " + dataField + "= '1'";
using (MySqlDataReader dataReader = cmd.ExecuteReader())
{
while (dataReader.Read())
{
data = Maper.MapData(dataReader);
}
}
return(data);
}
catch (Exception e)
{
throw e;
}
}
}
}
/// <summary>
/// Retorna uma lista contendo todos os serviços cadastrados
/// </summary>
public static List <Service> GetAllServices(string connectionString)
{
List <Service> services = new List <Service>();
Service temp;
using (var conn = new MySqlConnection(connectionString))
{
using (var cmd = conn.CreateCommand())
{
try
{
conn.Open();
cmd.CommandText = "SELECT * FROM services";
using (MySqlDataReader dataReader = cmd.ExecuteReader())
{
while (dataReader.Read())
{
temp = Maper.MapService(dataReader);
services.Add(temp);
}
}
return(services);
}
catch (Exception e)
{
throw e;
}
}
}
}
/// <summary>
/// Busca na base as informações do usuario para login
/// </summary>
public static User LoginRequest(string connectionString, string username)
{
User user = null;
using (var conn = new MySqlConnection(connectionString))
{
using (var cmd = conn.CreateCommand())
{
try
{
conn.Open();
cmd.CommandText = "SELECT * FROM users WHERE username = '******' AND active = True";
using (MySqlDataReader dataReader = cmd.ExecuteReader())
{
dataReader.Read();
user = Maper.MapUser(dataReader);
}
return(user);
}
catch (MySqlException e)
{
throw e;
}
}
}
}
/// <summary>
/// Retorna uma lista contendo todos os usuários cadastrados
/// </summary>
public static List <User> GetAllUsers(string connectionString)
{
List <User> users = new List <User>();
User temp;
using (var conn = new MySqlConnection(connectionString))
{
using (var cmd = conn.CreateCommand())
{
try
{
conn.Open();
cmd.CommandText = "SELECT * FROM users";
using (MySqlDataReader dataReader = cmd.ExecuteReader())
{
while (dataReader.Read())
{
temp = Maper.MapUser(dataReader);
temp.Password = null;
users.Add(temp);
}
}
return(users);
}
catch (Exception e)
{
throw e;
}
}
}
}
/// <summary>
/// Retorna um dispositivo que corresponde o UID informado
/// </summary>
public static Device GetDeviceByUid(string connectionString, string uid)
{
Device device;
using (var conn = new MySqlConnection(connectionString))
{
using (var cmd = conn.CreateCommand())
{
try
{
conn.Open();
cmd.CommandText = "SELECT * FROM devices WHERE device_id = '" + uid + "'";
using (MySqlDataReader dataReader = cmd.ExecuteReader())
{
dataReader.Read();
device = Maper.MapDevice(dataReader);
}
return(device);
}
catch (MySqlException e)
{
throw e;
}
}
}
}
/// <summary>
/// Retorna um usuário que corresponde ao id informado
/// </summary>
public static User GetUserById(string connectionString, int userId)
{
User user = null;
using (var conn = new MySqlConnection(connectionString))
{
using (var cmd = conn.CreateCommand())
{
try
{
conn.Open();
cmd.CommandText = "SELECT * FROM users WHERE user_id = " + userId;
using (MySqlDataReader dataReader = cmd.ExecuteReader())
{
dataReader.Read();
user = Maper.MapUser(dataReader);
}
return(user);
}
catch (MySqlException e)
{
throw e;
}
}
}
}
