//public async Task<IEnumerable<Cotacao>> Getcotacaoes()
//{
// string sql = "select * from [cotacaoDB].[dbo].[cotacao]";
// string sqlcotacaoFriend = "select Name from cotacaoFriends where cotacaoId = @cotacaoId";
// IList<Cotacao> listcotacao = new List<Cotacao>();
// using (var connectionDb = connection.Connection())
// {
// connectionDb.Open();
// var result = await connectionDb.QueryAsync<dynamic>(sql);
// if (result.Any())
// {
// foreach (var item in result.ToList())
// {
// var cotacao = new Cotacao((int)item.cotacaoId, (string)item.Name, (string)item.Power);
// var resultcotacaoFriend = await connectionDb.QueryAsync<dynamic>(sqlcotacaoFriend,
// new
// {
// cotacaoId = cotacao.cotacaoId
// });
// if (result.Any())
// {
// foreach (var friend in resultcotacaoFriend.ToList())
// {
// cotacao.AddcotacaoFriend((string)friend.Name);
// }
// }
// listcotacao.Add(cotacao);
// }
// }
// }
// return listcotacao;
//}
//public async Task<Cotacao> Insertcotacao(Cotacao cotacao)
//{
// string sql = "Insert into [cotacaoDB].[dbo].[cotacao] ([cotacaoId],[Name],[Power]) values (@cotacaoId, @Name, @Power)";
// using (var connectionDb = connection.Connection())
// {
// connectionDb.Open();
// var cotacaoResult = await connectionDb.ExecuteAsync(sql,
// new
// {
// cotacaoId = cotacao.cotacaoId,
// Name = cotacao.Name,
// Power = cotacao.Power
// });
// if (cotacao.cotacaoFriend.Any())
// {
// string sqlcotacaoFriend = "Insert into [cotacaoDB].[dbo].[cotacaoFriends] ([cotacaoId],[Name]) values (@cotacaoId, @Name)";
// foreach (var friend in cotacao.cotacaoFriend)
// {
// var cotacaoFriendResult = await connectionDb.ExecuteAsync(sqlcotacaoFriend,
// new
// {
// cotacaoId = cotacao.cotacaoId,
// Name = friend
// });
// }
// }
// return cotacao;
// }
//}
//public async Task<Cotacao> Updatecotacao(Cotacao cotacao)
//{
// string sql = "UPDATE [cotacaoDB].[dbo].[cotacao] SET[Name] = @Name, [Power]= @Power WHERE [cotacaoId] = @cotacaoId;";
// using (var connectionDb = connection.Connection())
// {
// connectionDb.Open();
// //var cotacaoResult = await connectionDb.ExecuteAsync(sql,
// // new
// // {
// // cotacaoId = cotacao.cotacaoId,
// // Name = cotacao.Name,
// // Power = cotacao.Power
// // });
// //return cotacao;
// }
//}
//public async Task<Cotacao> Getcotacao(int cotacaoId)
//{
// string sql = "select * from [cotacaoDB].[dbo].[cotacao] where [cotacaoId] = @cotacaoId";
// string sqlcotacaoFriend = "select Name from cotacaoFriends where cotacaoId = @cotacaoId";
// IList<Cotacao> listcotacao = new List<Cotacao>();
// using (var connectionDb = connection.Connection())
// {
// connectionDb.Open();
// var result = await connectionDb.QueryFirstOrDefaultAsync<Cotacao>(sql,
// new
// {
// cotacaoId = cotacaoId
// });
// if (result != null)
// {
// var resultcotacaoFriend = await connectionDb.QueryAsync<dynamic>(sqlcotacaoFriend,
// new
// {
// cotacaoId = result.cotacaoId
// });
// foreach (var friend in resultcotacaoFriend.ToList())
// {
// result.AddcotacaoFriend((string)friend.Name);
// }
// }
// return result;
// }
//}
//public async Task<bool> Deletcotacao(int cotacaoId)
//{
// string sql = "Delete [cotacaoDB].[dbo].[cotacao] where [cotacaoId] = @cotacaoId";
// string sqlFriendcotacao = "Delete [cotacaoDB].[dbo].[cotacaoFriends] where [cotacaoId] = @cotacaoId";
// using (var connectionDb = connection.Connection())
// {
// connectionDb.Open();
// await connectionDb.QueryAsync<cotacao>(sqlFriendcotacao,
// new
// {
// cotacaoId = cotacaoId
// });
// await connectionDb.QueryAsync<cotacao>(sql,
// new
// {
// cotacaoId = cotacaoId
// });
// return true;
// }
//}
public async Task <IEnumerable <Cotacao> > GetCotacaos(int cotacaoId)
{
string sqlcotacao = $"Select * From Cotacao {(cotacaoId > 0 ? $"Where IdCotacao = {cotacaoId}" : "")}";
IList <Cotacao> listcotacao = new List <Cotacao>();
using (var connectionDb = connection.Connection())
{
connectionDb.Open();
var result = await connectionDb.QueryAsync <Cotacao>(sqlcotacao);
if (result.Any())
{
foreach (var cotacao in result.ToList())
{
string sqlcotacaoItem = $"Select * From CotacaoItem {(cotacao.IdCotacao > 0 ? $"Where IdCotacao = {cotacao.IdCotacao}" : "")}";
cotacao.cotacaoItens = new List <CotacaoItem>();
var resultcotacaoItem = await connectionDb.QueryAsync <CotacaoItem>(sqlcotacaoItem);
if (result.Any())
{
foreach (var cotacaoItem in resultcotacaoItem.ToList())
{
cotacao.cotacaoItens.Add(cotacaoItem);
}
}
listcotacao.Add(cotacao);
}
}
}
return(listcotacao);
}
public async Task <User> GetByUsernameAsync(String username)
{
using (IDbConnection connection = _factory.Connection( )) {
return(await connection.QuerySingleOrDefaultAsync <User> ($"SELECT * from users WHERE username = @username", new {
username = username
}));
}
}
public async Task <IEnumerable <M3EmpresaCad> > Get()
{
string sql = $"Select IdEmpresa, NmRazaoSocial From [config].[CadEmpresas]";
using var connectionDb = _connection.Connection(_connectionString);
connectionDb.Open();
return(await connectionDb.QueryAsync <M3EmpresaCad>(sql));
}
public async Task <IEnumerable <Device> > Get()
{
string sql = $"SELECT d.Device_ID, " +
$"d.FreeBatteryPercentage, d.DEVICE_NAME, d.LastConnection, u.unit_group_name, d.FreeMemoryPercentage, d.FreeStoragePercentage, d.DEVICE_EXT_UNIT, dp.MDM_PROP_VALUE " +
$"from[device] d inner join[unit_group] u on u.[UNIT_GROUP_ID] = d.[UNIT_GROUP_ID] " +
$"left join[DEVICE_MDM_PROPERTY] dp on d.[DEVICE_ID] = dp.[DEVICE_ID] and dp.[MDM_PROP_KEY] = 'TELEPHONY_OPERATOR' " +
$"where d.[DEVICE_EXT_UNIT] in ('rde', 'rd6', 'army') and d.[ENABLED] = 1;";
using var connectionDb = _connection.Connection(_connectionString);
connectionDb.Open();
var result = await connectionDb.QueryAsync(sql);
return(new List <Device>());
//return connectionDb.QueryAsync<Device, Group, DeviceProperty>(
// "SELECT * " +
// "FROM dbo.Estados E " +
// "INNER JOIN dbo.Regioes R ON R.IdRegiao = E.IdRegiao " +
// "ORDER BY E.NomeEstado",
// map: (d, u, dp) =>
// {
// d.Group = u;
// d.DeviceProperty = dp;
// return d;
// },
// splitOn: "SiglaEstado,IdRegiao");
}
public IResultBag AddPartida(Partida partida)
{
try
{
using (var connection = _conn.Connection())
{
connection.Open();
connection.ExecuteAsync("INSERT INTO Pontuacao(Pontos, Dia) VALUES(@Pontos, @Dia)", partida);
//connection.Query($"");
return(new ResultBag("Adicionado com sucesso!", true));
}
}
catch (Exception ex)
{
return(new ResultBag(ex.Message, false));
}
}
public Task <BankInfo> GetBank(int id)
{
return(Task.Run(() =>
{
using (var db = _conn.Connection())
{
db.Open();
var info = db.QueryFirstOrDefaultAsync <BankInfo>(BankCommand._GetById, new
{
ID = id
});
return info.GetAwaiter().GetResult();
}
}));
}
public IEnumerable <Vehicle> GetVehiclesBySelectedInfo(Vehicle vehicle)
{
var vehicles = new List <Vehicle>()
{
vehicle
};
var sql = @"select *
from vehicles v
inner join standardequipment se on se.vehicleid = v.vehicleid
inner join engineinfo e on e.vehicleid = v.vehicleid
inner join dealers d on d.dealerid = v.dealerid
where ";
if (vehicle.Make != "Any")
{
sql = $"{ sql } v.Make = '{ vehicle.Make }' and ";
}
if (vehicle.Model != "Any")
{
sql = $"{ sql } v.Model = '{ vehicle.Model }' and ";
}
if (vehicle.BodyType != "Any")
{
sql = $"{ sql } v.BodyType = '{ vehicle.BodyType }' and ";
}
sql = $"{ sql } v.Price > { vehicle.MinPrice } and v.Price < { vehicle.MaxPrice }";
using (IDbConnection conn = _conn.Connection())
{
conn.Open();
vehicles = conn.Query <Vehicle, StandardEquipment, EngineInfo, Dealer, Vehicle> (
sql,
(vehicle, standardEquipment, engineInfo, dealer) =>
{
vehicle.StandardEquipment = standardEquipment;
vehicle.Dealer = dealer;
vehicle.EngineInfo = engineInfo;
return(vehicle);
},
splitOn: "VehicleID, DealerID").Distinct().OrderBy(x => x.Price).ToList();
}
return(vehicles);
}
public User Get(User userinfo)
{
User user = null;
var sql = $"select * from users u where u.username = \'{userinfo.UserName}\' and u.password = \'{userinfo.Password}\';";
using (IDbConnection conn = _conn.Connection())
{
conn.Open();
user = conn.QueryFirstOrDefault <User>(sql);
}
return(user);
}
public async Task <IEnumerable <BookSoldItem> > List(Guid salesmanId)
{
var sql = @"SELECT CAST(id as VARCHAR) as Id, CAST(user_id as VARCHAR) as UserId,
title as Title,
author as Author,
price as Price,
sold_date as SoldDate
FROM books_sold WHERE CAST(user_id as VARCHAR) = @userId";
using (IDbConnection connection = _factory.Connection( )) {
return(await connection.QueryAsync <BookSoldItem> (sql, new { userId = salesmanId.ToString( ) }));
}
}
public async Task <IEnumerable <Hero> > GetHeroes()
{
string sql = "select * from [HeroDB].[dbo].[Hero]";
string sqlHeroFriend = "select Name from HeroFriends where HeroId = @HeroId";
IList <Hero> listHero = new List <Hero>();
using (var connectionDb = _connection.Connection())
{
connectionDb.Open();
var result = await connectionDb.QueryAsync <dynamic>(sql);
if (result.Any())
{
foreach (var item in result.ToList())
{
var hero = new Hero(Guid.Parse(item.HeroId), (string)item.Name, (string)item.Power);
var resultHeroFriend = await connectionDb.QueryAsync <dynamic>(sqlHeroFriend,
new
{
HeroId = hero.HeroId
});
if (result.Any())
{
foreach (var friend in resultHeroFriend.ToList())
{
hero.AddHeroFriend((string)friend.Name);
}
}
listHero.Add(hero);
}
}
}
return(listHero);
}
public IResultBag AddJogo(JogoBasquete jogoBasquete)
{
try
{
using (var conn = _connection.Connection())
{
conn.Open();
var sql = new StringBuilder();
sql.AppendLine(" INSERT INTO Jogos ");
sql.AppendLine(" (DataJogo, QtdPontos) ");
sql.AppendLine(" VALUES ");
sql.AppendLine(" (@DataJogo, @QtdPontos) ");
conn.ExecuteAsync(sql.ToString(), jogoBasquete);
}
return(new ResultBag(true));
}
catch (Exception ex)
{
return(new ResultBag(ex.Message, false));
}
}
public async Task <IEnumerable <SalemanListItem> > List()
{
using (IDbConnection connection = _factory.Connection( )) {
connection.Open( );
var sql = @"SELECT
users.id as Id,
users.username as Username,
user_profiles.id as userId,
user_profiles.email as Email,
user_profiles.address as Address
FROM users LEFT JOIN user_profiles ON user_profiles.user_id = users.id
WHERE users.is_deleted = 0 AND users.role = @role";
return(await connection.QueryAsync <SalemanListItem> (sql, new { role = (int)UserRoleEnum.Salesman }));
}
}
public async Task <DateTime> CurrentDateAsync()
{
using (var connection = _factory.Connection( )) {
return(await connection.QuerySingleAsync <DateTime> ("SELECT now()"));
}
}
public BaseRepository(IConnectionFactory connectionFactory)
{
_connectionFactory = connectionFactory;
conn = _connectionFactory.Connection();
}
public GamesRepository(IConnectionFactory factory)
{
_connection = factory.Connection();
}