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