private ItemCache()
{
lock (dummy)
{
//upgrade db with stock column if not done already
StockCache.GetInstance();
items.Clear();
Item item;
double doubleVar;
int intVar;
DBFunctions.GetInstance().OpenConnection();
OleDbDataReader reader = DBFunctions.GetInstance().GetReader("Select ID, ItemName, RetailRate, ItemUnit, TaxRate, Stock from Items");
while (reader.Read())
{
item = new Item();
item.ID = (int)reader[0];
item.Name = reader[1].ToString();
item.Rate = double.Parse(reader[2].ToString());
double.TryParse(reader[4].ToString(), out doubleVar);
item.TaxRate = doubleVar;
int.TryParse(reader[3].ToString(), out intVar);
item.Unit = new Unit();
item.Unit.ID = intVar;
//adding stock 24/9/2010
double.TryParse(reader[5].ToString(), out doubleVar);
item.Stock = doubleVar;
items.Add(item);
}
foreach (Item x in items)
{
x.Unit = UnitCache.GetInstance().GetUnitById(x.Unit.ID);
}
}
}
public void DeleteSale(int saleId)
{
// Update stock if the sale has to be deleted. Stock management. 24.9.2010
OleDbCommand command = DBFunctions.GetInstance().GetCommand(
"select Item, SaleQuantity from Sales Where ID=?");
command.Parameters.AddWithValue("ID", saleId);
OleDbDataReader reader = DBFunctions.GetInstance().GetReader(command);
if (reader.Read())
{
Item item = ItemCache.GetInstance().GetItemById(int.Parse(reader[0].ToString()));
//qtty of deleted sale will be added to the stock
item.Stock = item.Stock + double.Parse(reader[1].ToString());
StockCache.GetInstance().UpdateStock(item);
}
//then delete sale from the db
command = DBFunctions.GetInstance().GetCommand(
"Delete From Sales Where ID = ?");
command.Parameters.AddWithValue("ID", saleId);
if (command.ExecuteNonQuery() > 1)
{//to confirm that only one sale got deleted for that single ID.. this error will roll back everything
throw new Exception("Error while deleting sales. Sale ID:" + saleId);
}
}
public void DeleteSale(int saleID, bool updateStock)
{
Transaction tx;
OleDbCommand command;
OleDbDataReader reader;
double saleQuantity = 0.0;
if (updateStock)
{
// Update stock if the sale has to be deleted. Stock management. 24.9.2010
command = DBFunctions.GetInstance().GetCommand(
"select Item, SaleQuantity, TransactionId from Sales Where ID=?");
command.Parameters.AddWithValue("ID", saleID);
reader = DBFunctions.GetInstance().GetReader(command);
if (reader.Read())
{
Item item = ItemCache.GetInstance().GetItemById(int.Parse(reader[0].ToString()));
saleQuantity = double.Parse(reader[1].ToString());
tx = TransactionCache.GetInstance().GetTransactionById(int.Parse(reader[2].ToString()));
if (tx.IsPurchase)
{
//qtty of deleted purchase will be subtracted from the stock
item.Stock -= saleQuantity;
//Update moving avg cost of items before deleting purchase item. 25.8.2014.
MovingAvgCache.GetInstance().GetSuppressedMovingAvgRate(GetSaleBySaleIDFromDB(saleID));
}
else
{
//qtty of deleted sale will be added to the stock
item.Stock += saleQuantity;
}
StockCache.GetInstance().UpdateStock(item);
}
}
//then delete sale from the db
command = DBFunctions.GetInstance().GetCommand(
"Delete From Sales Where ID = ?");
command.Parameters.AddWithValue("ID", saleID);
if (command.ExecuteNonQuery() > 1)
{//to confirm that only one sale got deleted for that single ID.. this error will roll back everything
throw new ApplicationException("Error while deleting sales. Sale ID:" + saleID);
}
}
//modified to incorporate stock management on sale insert/update 24.9.2010
public Sale SyncSale(Sale 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
//stock management incorporated. 24.9.2010
//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.SaleQuantity;
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.SaleQuantity);
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.SaleQuantity);
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
//subtract new sale's sale qtty from the item stock.
sale.Item.Stock = sale.Item.Stock - sale.SaleQuantity;
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);
}
//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);
}