private static List <ItemSoldInfo> LoadInfo(string whereClause = "", List <Tuple <string, string> > whereParams = null) { var items = new List <ItemSoldInfo>(); var currencies = Currency.GetKeyValueCurrencyList(); var dbHelper = new DatabaseHelper(); using (var conn = dbHelper.GetDatabaseConnection()) { using (var command = dbHelper.GetSQLiteCommand(conn)) { string query = "" + "SELECT isi.ID, DateTimeSold, QuantitySold, isi.Cost, isi.CostCurrencyID, isi.ProfitPerItem, isi.ProfitPerItemCurrencyID, " + " isi.InventoryItemID, isi.SoldByUserID, i.Name, i.Description, isi.Paid, isi.PaidCurrencyID, isi.Change, isi.ChangeCurrencyID, " + " it.ID AS ItemTypeID, it.Name AS ItemTypeName, it.Description AS ItemTypeDescription," + " it.IsDefault AS ItemTypeIsDefault, u.Name AS UserName " + "FROM ItemsSoldInfo isi JOIN InventoryItems i ON isi.InventoryItemID = i.ID " + " LEFT JOIN ItemTypes it ON i.ItemTypeID = it.ID " + " JOIN Users u ON isi.SoldByUserID = u.ID " + (string.IsNullOrWhiteSpace(whereClause) ? "" : whereClause + " ") + "ORDER BY lower(i.Name), isi.DateTimeSold"; command.CommandText = query; if (!string.IsNullOrEmpty(whereClause) && whereParams != null) { foreach (Tuple <string, string> keyValuePair in whereParams) { command.Parameters.AddWithValue(keyValuePair.Item1, keyValuePair.Item2); } } using (SQLiteDataReader reader = command.ExecuteReader()) { while (reader.Read()) { var item = new ItemSoldInfo(); item.ID = dbHelper.ReadInt(reader, "ID"); item.ItemName = dbHelper.ReadString(reader, "Name"); item.ItemDescription = dbHelper.ReadString(reader, "Description"); item.ItemType = new ItemType( dbHelper.ReadInt(reader, "ItemTypeID"), dbHelper.ReadString(reader, "ItemTypeName"), dbHelper.ReadString(reader, "ItemTypeDescription"), dbHelper.ReadBool(reader, "ItemTypeIsDefault")); item.InventoryItemID = dbHelper.ReadInt(reader, "InventoryItemID"); item.SoldByUserID = dbHelper.ReadInt(reader, "SoldByUserID"); item.SoldByUserName = dbHelper.ReadString(reader, "UserName"); string dateTimeSold = dbHelper.ReadString(reader, "DateTimeSold"); item.DateTimeSold = Convert.ToDateTime(dateTimeSold); // DateTime.ParseExact(dateTimeSold, //Utilities.DateTimeToDateOnlyStringFormat(), System.Globalization.CultureInfo.InvariantCulture); item.QuantitySold = dbHelper.ReadInt(reader, "QuantitySold"); item.Cost = dbHelper.ReadDecimal(reader, "Cost"); var costCurrencyID = dbHelper.ReadInt(reader, "CostCurrencyID"); item.CostCurrency = currencies.ContainsKey(costCurrencyID) ? currencies[costCurrencyID] : null; item.ProfitPerItem = dbHelper.ReadDecimal(reader, "ProfitPerItem"); var profitCurrencyID = dbHelper.ReadInt(reader, "ProfitPerItemCurrencyID"); item.ProfitPerItemCurrency = currencies.ContainsKey(profitCurrencyID) ? currencies[profitCurrencyID] : null; item.Paid = dbHelper.ReadDecimal(reader, "Paid"); var paidCurrencyID = dbHelper.ReadInt(reader, "PaidCurrencyID"); item.PaidCurrency = currencies.ContainsKey(paidCurrencyID) ? currencies[paidCurrencyID] : null; item.Change = dbHelper.ReadDecimal(reader, "Change"); var changeCurrencyID = dbHelper.ReadInt(reader, "ChangeCurrencyID"); item.ChangeCurrency = currencies.ContainsKey(changeCurrencyID) ? currencies[changeCurrencyID] : null; items.Add(item); } reader.Close(); } conn.Close(); } } return(items); }
public static DaySales GenerateDataForSingleDay(DateTime date, int userID = -1) { var totalDaySaleInfo = new DaySales(); totalDaySaleInfo.Date = date; totalDaySaleInfo.TotalIncome = 0; totalDaySaleInfo.TotalProfit = 0; totalDaySaleInfo.ItemsSold = new List <ReportItemSold>(); totalDaySaleInfo.TotalItemsSold = 0; var currencies = Currency.LoadCurrencies(); foreach (Currency currency in currencies) { if (currency.IsDefaultCurrency) { totalDaySaleInfo.Currency = currency; break; } } var data = ItemSoldInfo.LoadInfoForDate(date, userID); var itemIDToReportSold = new Dictionary <int, ReportItemSold>(); foreach (ItemSoldInfo singleItemInfo in data) { if (!itemIDToReportSold.ContainsKey(singleItemInfo.InventoryItemID)) { ReportItemSold itemSold = new ReportItemSold(); itemSold.InventoryItemID = singleItemInfo.InventoryItemID; itemSold.ItemType = singleItemInfo.ItemType; itemSold.Name = singleItemInfo.ItemName; itemSold.Description = singleItemInfo.ItemDescription; itemSold.QuantityPurchased = 0; itemSold.CostPerItem = singleItemInfo.Cost; // TODO: should be handled as an average! itemSold.CostCurrency = singleItemInfo.CostCurrency; itemSold.TotalCost = 0; itemSold.ProfitPerItem = singleItemInfo.ProfitPerItem; // TODO: should be handled as an average! itemSold.ProfitCurrency = singleItemInfo.ProfitPerItemCurrency; itemSold.TotalProfit = 0; itemIDToReportSold[singleItemInfo.InventoryItemID] = itemSold; totalDaySaleInfo.ItemsSold.Add(itemSold); } ReportItemSold itemSoldData = itemIDToReportSold[singleItemInfo.InventoryItemID]; itemSoldData.QuantityPurchased += singleItemInfo.QuantitySold; totalDaySaleInfo.TotalItemsSold += singleItemInfo.QuantitySold; if (itemSoldData.CostCurrency.ID == singleItemInfo.CostCurrency.ID) { itemSoldData.TotalCost += singleItemInfo.QuantitySold * singleItemInfo.Cost; } else { itemSoldData.TotalCost += singleItemInfo.QuantitySold * Utilities.ConvertAmount(singleItemInfo.Cost, singleItemInfo.CostCurrency, itemSoldData.CostCurrency); } if (itemSoldData.ProfitCurrency.ID == singleItemInfo.ProfitPerItemCurrency.ID) { itemSoldData.TotalProfit += singleItemInfo.QuantitySold * singleItemInfo.ProfitPerItem; } else { itemSoldData.TotalProfit += singleItemInfo.QuantitySold * Utilities.ConvertAmount(singleItemInfo.ProfitPerItem, singleItemInfo.ProfitPerItemCurrency, itemSoldData.ProfitCurrency); } // now add to total income/profit after finding item type money info ItemTypeMoneyInfo moneyInfo = null; if (singleItemInfo.ItemType != null) { if (!totalDaySaleInfo.ItemTypeIDToMoneyInfo.ContainsKey(singleItemInfo.ItemType.ID)) { var itemTypeMoneyInfo = new ItemTypeMoneyInfo(singleItemInfo.ItemType); itemTypeMoneyInfo.Currency = totalDaySaleInfo.Currency; totalDaySaleInfo.ItemTypeIDToMoneyInfo[singleItemInfo.ItemType.ID] = itemTypeMoneyInfo; totalDaySaleInfo.ItemTypeMoneyBreakdown.Add(itemTypeMoneyInfo); } moneyInfo = totalDaySaleInfo.ItemTypeIDToMoneyInfo[singleItemInfo.ItemType.ID]; } moneyInfo.TotalItemsSold += singleItemInfo.QuantitySold; if (totalDaySaleInfo.Currency.ID == singleItemInfo.CostCurrency.ID) { var amountIncrease = singleItemInfo.QuantitySold * singleItemInfo.Cost; totalDaySaleInfo.TotalIncome += amountIncrease; if (moneyInfo != null) { moneyInfo.TotalIncome += amountIncrease; } } else { var amountIncrease = singleItemInfo.QuantitySold * Utilities.ConvertAmount(singleItemInfo.Cost, singleItemInfo.CostCurrency, totalDaySaleInfo.Currency); totalDaySaleInfo.TotalIncome += amountIncrease; if (moneyInfo != null) { moneyInfo.TotalIncome += amountIncrease; } } if (totalDaySaleInfo.Currency.ID == singleItemInfo.ProfitPerItemCurrency.ID) { var amountIncrease = singleItemInfo.QuantitySold * singleItemInfo.ProfitPerItem; totalDaySaleInfo.TotalProfit += amountIncrease; if (moneyInfo != null) { moneyInfo.TotalProfit += amountIncrease; } } else { var amountIncrease = singleItemInfo.QuantitySold * Utilities.ConvertAmount(singleItemInfo.ProfitPerItem, singleItemInfo.ProfitPerItemCurrency, totalDaySaleInfo.Currency); totalDaySaleInfo.TotalProfit += amountIncrease; if (moneyInfo != null) { moneyInfo.TotalProfit += amountIncrease; } } } totalDaySaleInfo.ItemsSold.Sort((left, right) => left.Name.CompareTo(right.Name)); totalDaySaleInfo.ItemTypeMoneyBreakdown.Sort((left, right) => left.Type.Name.CompareTo(right.Type.Name)); return(totalDaySaleInfo); }