public static List <Client> GetAllClientsWhoOrdered(Object obj)
{
if (!File.Exists(BaseRepo.DbFIle))
{
BaseRepo.CreateDatabase();
}
var clients = new List <Client>();
using (var cnn = BaseRepo.DbConnection())
{
var objectInOrders =
cnn.Query <OrderObject>("SELECT * FROM OrderObject WHERE ObjectId = @ObjectId",
new { ObjectId = obj.Id }).ToList();
var clientRepository = new ClientRepository();
var orderRepository = new OrderRepository();
foreach (var objectInOrder in objectInOrders)
{
var clientId = orderRepository.GetOrder(objectInOrder.OrderId).ClientId;
var client = clientRepository.GetClient(clientId);
if (!clients.Contains(client))
{
clients.Add(client);
}
}
}
return(clients);
}
public static List <Client> GetAllClients()
{
if (!File.Exists(BaseRepo.DbFIle))
{
BaseRepo.CreateDatabase();
}
using (var cnn = BaseRepo.DbConnection())
{
cnn.Open();
string sql =
"SELECT * FROM Client ORDER BY Name";
return(cnn.Query <Client>(sql).AsList());
}
}
public static List <Client> GetAllClientsContaining(string str)
{
if (!File.Exists(BaseRepo.DbFIle))
{
BaseRepo.CreateDatabase();
}
using (var cnn = BaseRepo.DbConnection())
{
cnn.Open();
string like = "'%" + str + "%'";
string sql =
"SELECT * FROM Client WHERE Name LIKE " + like
+ " ORDER BY Name"
+ " LIMIT 10;";
return(cnn.Query <Client>(sql).AsList());
}
}
public static InternetClient GetInternetClient(Client client)
{
if (!File.Exists(BaseRepo.DbFIle))
{
BaseRepo.CreateDatabase();
}
using (var cnn = BaseRepo.DbConnection())
{
cnn.Open();
var ipAddress = cnn.Query <string>("SELECT IpAddress FROM InternetClient WHERE ClientId = @ClientId",
new { ClientId = client.Id }).FirstOrDefault();
if (ipAddress != default)
{
return(Map(client, ipAddress));
}
}
return(null);
}
public static bool placeOrder(Order order)
{
if (!File.Exists(BaseRepo.DbFIle))
{
BaseRepo.CreateDatabase();
}
using (var cnn = BaseRepo.DbConnection())
{
cnn.Open();
using (var transaction = cnn.BeginTransaction())
{
var objectRepository = new ObjectRepository();
foreach (var orderObject in order.Objects)
{
var obj = cnn.Query <Object>(
@"SELECT * FROM Object WHERE Id = @id", new { id = orderObject.ObjectId }).FirstOrDefault();
if (obj.InStock < orderObject.Amount)
{
transaction.Rollback();
return(false);
}
else
{
cnn.Execute(@"UPDATE Object SET InStock = @InStock WHERE ID = @Id;",
new { InStock = obj.InStock - orderObject.Amount, Id = obj.Id });
}
}
cnn.Execute(@"UPDATE PlaceOrder SET Completed = @Completed WHERE ID = @Id;",
new { Completed = 1, Id = order.Id });
transaction.Commit();
}
}
return(true);
}