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);
}