public static List <QuantityAdjustment> LoadQuantityAdjustmentsBetweenDates(InventoryItem item, DateTime startTime, DateTime endTime) { var adjustments = new List <QuantityAdjustment>(); if (item == null) { return(adjustments); } string query = "" + "SELECT qa.ID, AmountChanged, DateTimeChanged, AdjustedByUserID, u.Name AS UserName, Explanation, WasAdjustedForStockPurchase " + "FROM QuantityAdjustments qa JOIN Users u ON qa.AdjustedByUserID = u.ID " + "WHERE InventoryItemID = @id AND DateTimeChanged >= @startDate AND DateTimeChanged <= @endDate " + "ORDER BY DateTimeChanged"; var currencies = Currency.GetKeyValueCurrencyList(); var dbHelper = new DatabaseHelper(); var startDateForQueryString = startTime.ToString(Utilities.DateTimeToStringFormat()); var endDateForQueryString = endTime.ToString(Utilities.DateTimeToStringFormat()); using (var conn = dbHelper.GetDatabaseConnection()) { using (var command = dbHelper.GetSQLiteCommand(conn)) { command.CommandText = query; command.Parameters.AddWithValue("@id", item.ID); command.Parameters.AddWithValue("@startDate", startDateForQueryString); command.Parameters.AddWithValue("@endDate", endDateForQueryString); using (SQLiteDataReader reader = command.ExecuteReader()) { while (reader.Read()) { var adjustment = new QuantityAdjustment(); adjustment.ID = dbHelper.ReadInt(reader, "ID"); adjustment.AmountChanged = dbHelper.ReadInt(reader, "AmountChanged"); adjustment.DateTimeChanged = Convert.ToDateTime(dbHelper.ReadString(reader, "DateTimeChanged")); adjustment.UserID = dbHelper.ReadInt(reader, "AdjustedByUserID"); adjustment.InventoryItemID = item.ID; adjustment.UserName = dbHelper.ReadString(reader, "UserName"); adjustment.Explanation = dbHelper.ReadString(reader, "Explanation"); adjustment.WasAdjustedForStockPurchase = dbHelper.ReadBool(reader, "WasAdjustedForStockPurchase"); adjustments.Add(adjustment); } reader.Close(); } } conn.Close(); } return(adjustments); }
public static List <DetailedStockReportInfo> GetStockOnDates(DateTime firstDate, DateTime secondDate) { if (secondDate < firstDate) { return(new List <DetailedStockReportInfo>()); // dates are invalid } var firstStock = GetStockByDateTime(firstDate, false, false); var lastStock = GetStockByDateTime(secondDate, false, false); var output = new List <DetailedStockReportInfo>(); // this isn't very optimized for matching up the items, but it will do for now~ for (int i = 0; i < firstStock.Count; i++) { var firstStockItem = firstStock[i]; var reportInfo = new DetailedStockReportInfo(); reportInfo.Item = firstStockItem; reportInfo.StartStock = firstStockItem.Quantity; var adjustments = QuantityAdjustment.LoadQuantityAdjustmentsBetweenDates(firstStockItem, firstDate, secondDate); foreach (QuantityAdjustment adjustment in adjustments) { if (adjustment.WasAdjustedForStockPurchase) { reportInfo.AmountChangedFromPurchaseStockIncrease += adjustment.AmountChanged; } else { reportInfo.AmountFromOtherQuantityAdjustments += adjustment.AmountChanged; } } var secondStockItem = lastStock.Where(x => x.ID == firstStockItem.ID).FirstOrDefault(); if (secondStockItem != null) { reportInfo.EndStock = secondStockItem.Quantity; } output.Add(reportInfo); } output.Sort((left, right) => left.Item.Name.ToLower().CompareTo(right.Item.Name.ToLower())); return(output); }