public async Task <Asset> Create(Asset asset)
{
const string sql = @"INSERT INTO assets (id, model, description, purchased_at, maintenance_on)
VALUES (@Id, @Model, @Description, @PurchasedAt, @MaintenanceOn)";
using var connection = helper.GetConnection();
await connection.QueryAsync(sql, SqlParameters(asset));
return(asset);
}
public void Insert(MqttRequest mqttRequest)
{
string sql = @"INSERT
INTO ta_pegando_fogo.Medicao
(DispositivoId,
Temperatura,
Fumaca,
Gas,
UmidadeAr,
DataAtualizacao,
Risco)
VALUES
(@DispositivoId,
@Temperatura,
@Fumaca,
@Gas,
@Umidade,
@DataAtualizacao,
@Risco)";
var param = new DynamicParameters();
param.Add("@Temperatura", mqttRequest.measurement.Temperature, DbType.Double);
param.Add("@Fumaca", mqttRequest.measurement.Smoke, DbType.Double);
param.Add("@Gas", mqttRequest.measurement.Gas, DbType.Double);
param.Add("@Umidade", mqttRequest.measurement.AirHumidity, DbType.Double);
param.Add("@DataAtualizacao", DateTime.UtcNow, DbType.DateTime);
param.Add("@Risco", 0, DbType.Double);
param.Add("@DispositivoId", mqttRequest.measurement.IdDispositivo, DbType.Int32);
using var connection = _helper.GetConnection();
connection.Execute(sql, param);
}
public Task <int> Login(string cpf, string senha)
{
string sql = @"SELECT
IdCliente
FROM Cliente
WHERE
CpfCnpj = @CpfCnpj
AND
Senha = @Senha";
var param = new DynamicParameters();
param.Add("@CpfCnpj", cpf.Replace(".", "").Replace("-", "").Replace("/", ""), DbType.String);
param.Add("@Senha", senha, DbType.String);
using var connection = _helper.GetConnection();
return(connection.QueryFirstOrDefaultAsync <int>(sql, param));
}
public async Task <List <DeviceDto> > GetDevice(int idClient)
{
const string sql = @"SELECT
device.IdDispositivo,
device.IdCliente,
device.Latitude,
device.Longitude,
device.Apelido,
m.IdMedicao,
m.DispositivoId,
m.Temperatura,
m.Fumaca,
m.Gas,
m.UmidadeAr,
m.DataAtualizacao,
m.Risco
FROM
Dispositivo device
JOIN
Medicao m ON device.IdDispositivo = m.DispositivoId
WHERE
device.IdCliente = @IdCliente";
var param = new DynamicParameters();
param.Add("@IdCliente", idClient, DbType.Int32);
var deviceDtoDictionary = new Dictionary <int, DeviceDto>();
using var connection = _helper.GetConnection();
return((await connection.QueryAsync <DeviceDto, MeasurementDto, DeviceDto>(sql,
map: (deviceDto, measurementDto) =>
{
if (deviceDtoDictionary.TryGetValue(deviceDto.IdDispositivo, out DeviceDto device))
{
deviceDto = device;
}
else
{
if (deviceDto.Measurements == null)
{
deviceDto.Measurements = new List <MeasurementDto>();
}
deviceDtoDictionary.Add(deviceDto.IdDispositivo, deviceDto);
}
if (measurementDto != null)
{
var measurementList = deviceDto.Measurements.Find(x => x.IdMedicao == measurementDto.IdMedicao);
if (measurementList == null)
{
deviceDto.Measurements.Add(measurementDto);
}
}
return deviceDto;
},
param,
null,
true,
splitOn: "IdDispositivo,IdMedicao")).Distinct().ToList());
}
