//modified to incorporate stock management on sale insert/update 24.9.2010
public SalePurchase SyncSale(SalePurchase sale)
{
OleDbCommand command = DBFunctions.GetInstance().GetCommand(
"Select ID, SaleQuantity From Sales Where ID=?");
command.Parameters.AddWithValue("ID", sale.ID);
OleDbDataReader reader = DBFunctions.GetInstance().GetReader(command);
if (reader.Read())
{
//sale present. So update it
//moving average rate of items incorporated. 25.8.2014
//stock management incorporated. 24.9.2010
//sale purchase modes incorporated. 1/4/2011
if (sale.Transaction.IsPurchase)
{
//subtract the previous purchase qtty to stock, then add new purchace qtty.
sale.Item.Stock = sale.Item.Stock - double.Parse(reader[1].ToString()) + sale.Quantity;
//in case of updating a purchase, revert the average changed due to
//previous item rate, apply change in average due to new rate.
//this has to be done before updating DB with new purchase rate.
//Get the previous purchase object from db and pass it on to the calculator.
SalePurchase prevPurchase = GetSaleBySaleIDFromDB(sale.ID);
sale.Item.MovingAvgRate = MovingAvgCache.GetInstance().GetMovingAvgRate(sale, prevPurchase);
MovingAvgCache.GetInstance().UpdateMovingAvgRate(sale.Item);
}
else
{
//add the previous salequantity to item's stock, then subtract the new salequantity
sale.Item.Stock = sale.Item.Stock + double.Parse(reader[1].ToString()) - sale.Quantity;
//if not a purchase, do not bother updating moving avg
}
StockCache.GetInstance().UpdateStock(sale.Item);
command = DBFunctions.GetInstance().GetCommand(
"Update Sales Set TransactionId=?, Item=?, SaleRate=?, SaleQuantity=?, Tax=?, SaleNumber=?,SaleUnit=?, LastUpdatedTime=Now() Where ID=?");
command.Parameters.AddWithValue("Transaction", sale.Transaction.ID);
command.Parameters.AddWithValue("Item", sale.Item.ID);
command.Parameters.AddWithValue("SaleRate", sale.SaleRate);
command.Parameters.AddWithValue("SaleQuantity", sale.Quantity);
command.Parameters.AddWithValue("Tax", sale.SaleTax);
command.Parameters.AddWithValue("SaleNumber", sale.Number);
command.Parameters.AddWithValue("SaleUnit", sale.SaleUnit.ID);
command.Parameters.AddWithValue("ID", sale.ID);
command.ExecuteNonQuery();
}
else
{
//sale not present. so insert it
command = DBFunctions.GetInstance().GetCommand(
"INSERT INTO SALES(TransactionId,Item,SaleRate,SaleQuantity,Tax,SaleNumber,SaleUnit,LastUpdatedTime) "
+ "VALUES(?,?,?,?,?,?,?,?)");
command.Parameters.AddWithValue("Transaction", sale.Transaction.ID);
command.Parameters.AddWithValue("Item", sale.Item.ID);
command.Parameters.AddWithValue("SaleRate", sale.SaleRate);
command.Parameters.AddWithValue("SaleQuantity", sale.Quantity);
command.Parameters.AddWithValue("Tax", sale.SaleTax);
command.Parameters.AddWithValue("SaleNumber", sale.Number);
command.Parameters.AddWithValue("SaleUnit", sale.SaleUnit.ID);
command.Parameters.Add("LastUpdatedTime", OleDbType.Date).Value = DateTime.Now;
command.ExecuteNonQuery();
//stock management incorporated. 24.9.2010
//sale/purchace incorporated 1.4.2011
if (sale.Transaction.IsPurchase)
{
//add new sale's sale qtty to the item stock.
sale.Item.Stock = sale.Item.Stock + sale.Quantity;
//incorporating moving avg rate of item 25.8.2014
//while inserting a new purchase, update moving averate rate.
sale.Item.MovingAvgRate = MovingAvgCache.GetInstance().GetMovingAvgRate(sale);
MovingAvgCache.GetInstance().UpdateMovingAvgRate(sale.Item);
}
else
{
//subtract new sale's sale qtty from the item stock.
sale.Item.Stock = sale.Item.Stock - sale.Quantity;
}
StockCache.GetInstance().UpdateStock(sale.Item);
//get back the id
command = DBFunctions.GetInstance().GetCommand(
"Select ID From Sales Where SaleNumber=? And TransactionId=?");
command.Parameters.AddWithValue("SaleNumber", sale.Number);
command.Parameters.AddWithValue("Transaction", sale.Transaction.ID);
reader = DBFunctions.GetInstance().GetReader(command);
if (reader.Read())
{
sale.ID = int.Parse(reader[0].ToString());
}
}
return(sale);
}
