Ejemplo n.º 1
0
        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);
        }
Ejemplo n.º 2
0
        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);
        }