public void AddOrder(InternetOrder order)
{
if (!File.Exists(BaseRepo.DbFIle))
{
BaseRepo.CreateDatabase();
}
using (var cnn = BaseRepo.DbConnection())
{
cnn.Open();
string sql =
"INSERT INTO Order (ClientId, Completed, IpAddress) Values (@ClientId, @Completed, @IpAddress);";
cnn.Execute(sql,
new { ClientId = order.ClientId, Completed = 0, IpAddress = order.IpAddress });
SQLiteCommand Command = new SQLiteCommand("select last_insert_rowid()", cnn);
Int64 LastRowID64 = (Int64)Command.ExecuteScalar();
int orderId = (int)LastRowID64;
sql = "INSERT INTO OrderObject (Amount, ObjectId, OrderId) Values (@Amount, @ObjectId, @OrderId);";
foreach (var orderObj in order.Objects)
{
cnn.Execute(sql, new { Amount = orderObj.Amount, ObjectId = orderObj.ObjectId, OrderId = orderId });
}
}
}
public List <Order> GetAllOrders()
{
if (!File.Exists(BaseRepo.DbFIle))
{
BaseRepo.CreateDatabase();
}
var orders = new List <Order>();
using (var cnn = BaseRepo.DbConnection())
{
cnn.Open();
var ordersWithoutProducts = cnn.Query <Order>("SELECT * FROM PlaceOrder").ToList();
foreach (Order order in ordersWithoutProducts)
{
order.Objects = cnn.Query <OrderObject>("SELECT * FROM OrderObject WHERE OrderId = @OrderId",
new { OrderId = order.Id })
.ToList();
orders.Add(order);
}
}
return(orders);
}
public void IncreaseInStockAmount(int id, int number)
{
var obj = GetObject(id);
if (obj != null)
{
int newNumber = obj.InStock + number;
using (var cnn = BaseRepo.DbConnection())
{
cnn.Open();
string sql = "UPDATE Object SET InStock = @InStock WHERE Id = @Id";
cnn.Execute(sql, new { InStock = newNumber, Id = id });
}
}
}
public Object GetObject(int id)
{
if (!File.Exists(BaseRepo.DbFIle))
{
return(null);
}
using (var cnn = BaseRepo.DbConnection())
{
cnn.Open();
var result = cnn.Query <Object>(
@"SELECT * FROM Object WHERE Id = @id", new { id }).FirstOrDefault();
return(result);
}
}
public void AddObject(Object obj)
{
if (!File.Exists(BaseRepo.DbFIle))
{
BaseRepo.CreateDatabase();
}
using (var cnn = BaseRepo.DbConnection())
{
cnn.Open();
string sql =
"INSERT INTO Object (Description, Price, InStock) Values (@Description, @Price, @InStock);";
cnn.Execute(sql, obj);
}
}
public void AddClient(Client client)
{
if (!File.Exists(BaseRepo.DbFIle))
{
BaseRepo.CreateDatabase();
}
using (var cnn = BaseRepo.DbConnection())
{
cnn.Open();
string sql =
"INSERT INTO Client (Name, Address) Values (@Name, @Address);";
cnn.Execute(sql, client);
}
}
public List <Object> GetAllObjects()
{
if (!File.Exists(BaseRepo.DbFIle))
{
BaseRepo.CreateDatabase();
}
var objects = new List <Object>();
using (var cnn = BaseRepo.DbConnection())
{
cnn.Open();
objects = cnn.Query <Object>("SELECT * FROM Object").ToList();
}
return(objects);
}
public static int GetNumberOfObjects(int orderId)
{
var amount = 0;
if (!File.Exists(BaseRepo.DbFIle))
{
return(amount);
}
using (var cnn = BaseRepo.DbConnection())
{
var sql =
"SELECT SUM(Amount) FROM OrderObject WHERE OrderId = @OrderId";
amount = (int)cnn.Query <Int64>(sql, new { orderId }).FirstOrDefault();
}
return(amount);
}
public void AddInternetClient(InternetClient InternetClient)
{
if (!File.Exists(BaseRepo.DbFIle))
{
BaseRepo.CreateDatabase();
}
using (var cnn = BaseRepo.DbConnection())
{
cnn.Open();
string sql =
"INSERT INTO Client (Name, Address) Values (@Name, @Address);";
cnn.Execute(sql, InternetClient);
SQLiteCommand Command = new SQLiteCommand("select last_insert_rowid()", cnn);
Int64 LastRowID64 = (Int64)Command.ExecuteScalar();
int id = (int)LastRowID64;
sql = "INSERT INTO InternetClient (ClientId, IpAddress) Values (@ClientId, @IpAddress)";
cnn.Execute(sql, new { ClientId = id, IpAddress = InternetClient.IpAddress, });
}
}
public static int GetTotalPrice(int orderId)
{
var total = 0;
if (!File.Exists(BaseRepo.DbFIle))
{
return(total);
}
using (var cnn = BaseRepo.DbConnection())
{
var sql =
"SELECT * FROM OrderObject WHERE OrderId = @OrderId";
var objectRepository = new ObjectRepository();
foreach (var orderObj in cnn.Query <OrderObject>(sql, new { orderId }).ToList())
{
total += orderObj.Amount * objectRepository.GetObject(orderObj.ObjectId).Price;
}
}
return(total);
}
