public void AverageItemsPerReceipt(DateTime Start, DateTime End)
{
Int64 from = Utils.GetUnixTime(Start);
Int64 to = Utils.GetUnixTime(EndDate(End));
string sql = $"SELECT AVG(`number_of_products`) FROM `receipt` WHERE UNIX_TIMESTAMP(`datetime`) >= '{from}' AND UNIX_TIMESTAMP(`datetime`) <= '{to}'";
TableDecode Results = Mysql.RunQueryWithReturn(sql);
AverageProductPerReceipt = Convert.ToDecimal(Results.RowData[0].Values[0]);
}
public SaleTransaction GetTempProductsSaleTransaction()
{
if (Resolved)
{
string sql = $"SELECT * FROM `sale_transactions` WHERE `product_type` = 'temp_product' AND `product_id` = '{this.ID}'";
TableDecode getTransaction = Mysql.RunQueryWithReturn(sql);
SaleTransaction saleTrans = new SaleTransaction(getTransaction.RowData[0]);
return(saleTrans);
}
throw new TempProductResolvedException("Dette tempproduct er ikke resolved");
}
public static int GetNextID()
{
if (Mysql.ConnectionWorking == false)
{
return(0);
}
string sql = "SHOW TABLE STATUS LIKE 'temp_products'";
TableDecode Results = Mysql.RunQueryWithReturn(sql);
return(Convert.ToInt32(Results.RowData[0].Values[10]));
}
// Henter storage status fra databasen om hvilke lagere der har hvilket antal af produkter
public void GetStorageStatus()
{
string sql = $"SELECT * FROM `storage_status` WHERE `product_id` = '{ID}'";
try
{
TableDecode Results = Mysql.RunQueryWithReturn(sql);
foreach (var row in Results.RowData)
{
int StorageRoomID = Convert.ToInt32(row.Values[1]);
int Amount = Convert.ToInt32(row.Values[2]);
StorageRoom storgeRoom = new StorageRoom(StorageRoomID);
StorageWithAmount.TryAdd(storgeRoom.ID, Amount);
}
}
catch (EmptyTableException)
{
//Ignore EmptyTableException
}
}
// Bruges ikke mere
private static void _FixReceiptInDatabase()
{
string sql = "SELECT * FROM `receipt`";
TableDecode receipts = Mysql.RunQueryWithReturn(sql);
foreach (var receipt in receipts.RowData)
{
Receipt NewReceipt = new Receipt(receipt);
decimal price_of_all_transactions = 0;
int TotalProductCount = 0;
foreach (var transaction in NewReceipt.Transactions)
{
price_of_all_transactions += transaction.TotalPrice;
TotalProductCount += transaction.Amount;
}
NewReceipt.TotalPrice = price_of_all_transactions;
//NewReceipt.PaidPrice = price_of_all_transactions;
NewReceipt.NumberOfProducts = TotalProductCount;
NewReceipt.UpdateInDatabase();
}
}
public static int GetNextID()
{
if (Mysql.ConnectionWorking == false)
{
return(0);
}
string sql = "SHOW TABLE STATUS LIKE 'products'";
TableDecode Results = Mysql.RunQueryWithReturn(sql);
int product_AC = Convert.ToInt32(Results.RowData[0].Values[10]);
sql = "SHOW TABLE STATUS LIKE 'service_products'";
Results = Mysql.RunQueryWithReturn(sql);
int service_AC = Convert.ToInt32(Results.RowData[0].Values[10]);
int return_AC = 0;
if (service_AC != product_AC)
{
if (service_AC > product_AC)
{
return_AC = service_AC;
sql = $"ALTER TABLE products AUTO_INCREMENT={service_AC};";
Mysql.RunQuery(sql);
}
else
{
return_AC = product_AC;
sql = $"ALTER TABLE service_products AUTO_INCREMENT={product_AC};";
Mysql.RunQuery(sql);
}
}
else
{
return_AC = service_AC;
}
return(return_AC);
}
public static void GetIceCreamID()
{
if (Mysql.ConnectionWorking)
{
string sql = "SELECT `id` FROM `groups` WHERE `name` LIKE 'is'";
TableDecode Results = Mysql.RunQueryWithReturn(sql);
if (Results.RowCounter == 0)
{
Properties.Settings.Default.IcecreamID = -1;
Properties.Settings.Default.Save();
}
else
{
int IsID = Convert.ToInt32(Results.RowData[0].Values[0]);
Properties.Settings.Default.IcecreamID = IsID;
Properties.Settings.Default.Save();
}
}
else
{
Properties.Settings.Default.IcecreamID = -1;
Properties.Settings.Default.Save();
}
}
//Removes storage room from dictionary, and all products
public void DeleteStorageRoom(int id)
{
if (id != 1)
{
string CheckProductsStorageAmount = $"SELECT * FROM `storage_status` WHERE `amount` != '0' AND `storageroom` = '{id}'";
TableDecode Results = Mysql.RunQueryWithReturn(CheckProductsStorageAmount);
if (Results.RowCounter != 0)
{
MessageBox.Show("Du kan ikke slette lager rum som stadig har varer på lager!");
}
else
{
if (StorageRoomDictionary.ContainsKey(id))
{
StorageRoomDictionary[id].DeactivateStorageRoom();
foreach (Product product in ProductDictionary.Values)
{
int h = 0;
product.StorageWithAmount.TryRemove(id, out h);
}
StorageRoom outVal = null;
StorageRoomDictionary.TryRemove(id, out outVal);
string deleteQuery = $"DELETE FROM `storage_status` WHERE `storageroom` = '{id}'";
Mysql.RunQuery(deleteQuery);
}
else
{
MessageBox.Show("Det lager du forsøger at slette, eksistere ikke...");
}
}
}
else
{
MessageBox.Show("Du kan ikke slette butikken!");
}
}
