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