private static Stock UpdateStock(LocalInvoiceItem item, bool useAverageCost, bool updateRRP, Stock foundStock)
{
DateTime now = DateTime.Now;
decimal cost = useAverageCost ? CalculateNewAverageCost(foundStock.quantity, foundStock.cost_ex, item.cost_ex, item.quantity) : item.cost_ex;
decimal RRP = item.RRP / 1.1M;
double newQuantity = item.quantity + foundStock.quantity;
string RMDBLocation = Properties.Settings.Default.RMDBLocation;
try
{
OleDbConnection RMDBconnection = null;
RMDBconnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; User Id=; Password=; Data Source=" + RMDBLocation);
RMDBconnection.Open();
OleDbCommand updateCommand = RMDBconnection.CreateCommand();
string commandTextRRPUpdate = @"UPDATE Stock
SET cost = ? , quantity = ?, date_modified = ?, sell = ?
WHERE stock_id = ?";
string commandTextNoRRPUpdate = @"UPDATE Stock
SET cost = ? , quantity = ?, date_modified = ?
WHERE stock_id = ?";
updateCommand.CommandText = updateRRP ? commandTextRRPUpdate : commandTextNoRRPUpdate;
updateCommand.Parameters.Add("@cost", OleDbType.Currency).Value = cost;
updateCommand.Parameters.Add("@quantity", OleDbType.Double).Value = newQuantity;
updateCommand.Parameters.Add("@date_modified", OleDbType.Date).Value = DateTime.Now;
if (updateRRP)
{
updateCommand.Parameters.Add("@sell", OleDbType.Currency).Value = RRP;
updateCommand.Parameters.Add("@stock_id", OleDbType.Double).Value = foundStock.stock_id;
}
else
{
updateCommand.Parameters.Add("@stock_id", OleDbType.Double).Value = foundStock.stock_id;
}
updateCommand.ExecuteNonQuery();
RMDBconnection.Close();
}
catch (Exception ex)
{
throw;
}
return foundStock;
}
private static void CreateAuditEntries(Stock foundStock, LocalInvoiceItem item, int goods_id)
{
DateTime audit_date = DateTime.Now;
if (foundStock.cost_ex != item.cost_ex)
{
decimal changeInExistingStockValue = CalculateExistingStockValueChange(foundStock, item);
CreateAuditItem(audit_date, goods_id, "VC", foundStock.stock_id, 0, changeInExistingStockValue);
}
CreateAuditItem(audit_date, goods_id, "GR", foundStock.stock_id, item.quantity, 0);
}
private static Stock GetStock(string barcode)
{
string RMDBLocation = Properties.Settings.Default.RMDBLocation;
Stock foundStock = null;
try
{
OleDbConnection RMDBconnection = null;
OleDbDataReader dbReader = null;
RMDBconnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; User Id=; Password=; Data Source=" + RMDBLocation);
RMDBconnection.Open();
OleDbCommand selectCmd = RMDBconnection.CreateCommand();
//Get customers.
string commandText = "SELECT stock_id, sell, quantity , cost from Stock where Barcode = ?";
selectCmd.CommandText = commandText;
selectCmd.Parameters.Add("@Barcode", OleDbType.VarChar).Value = barcode;
dbReader = selectCmd.ExecuteReader();
if (dbReader.HasRows)
{
dbReader.Read();
foundStock = new Stock();
foundStock.stock_id = dbReader.GetDouble(0);
foundStock.sell = dbReader.GetDecimal(1);
foundStock.quantity = dbReader.GetDouble(2);
foundStock.cost_ex = dbReader.GetDecimal(3);
}
dbReader.Close();
RMDBconnection.Close();
}
catch (Exception ex)
{
throw;
}
return foundStock;
}
private static decimal CalculateExistingStockValueChange(Stock foundStock, LocalInvoiceItem item)
{
decimal existingStockValueAtOldCost = (decimal)foundStock.quantity * foundStock.cost_ex;
decimal incomingStockValue = (decimal)item.quantity * item.cost_ex;
decimal newAverageCost = (existingStockValueAtOldCost + incomingStockValue) / (decimal)(foundStock.quantity + item.quantity);
decimal existingStockValueAtNewAverageCost = (decimal)foundStock.quantity * newAverageCost;
decimal changeInExistingStockValue = existingStockValueAtNewAverageCost - existingStockValueAtOldCost;
return changeInExistingStockValue;
}
