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 List <Sale> GetSalesByTransactionId(int transactionID)
{
Transaction transaction = TransactionCache.GetInstance()
.GetTransactionById(transactionID);
Sale sale;
List <Sale> sales = new List <Sale>();
OleDbCommand command = DBFunctions.GetInstance().GetCommand(
"Select ID, Item, SaleRate, SaleQuantity, Tax, SaleNumber, SaleUnit " +
"From Sales Where TransactionId=? Order By SaleNumber");
command.Parameters.AddWithValue("Transaction", transactionID);
OleDbDataReader reader = DBFunctions.GetInstance().GetReader(command);
while (reader.Read())
{
sale = new Sale();
sale.ID = int.Parse(reader[0].ToString());
sale.Transaction = transaction;
sale.SaleRate = double.Parse(reader[2].ToString());
sale.SaleQuantity = double.Parse(reader[3].ToString());
sale.SaleTax = double.Parse(reader[4].ToString());
sale.Number = int.Parse(reader[5].ToString());
sale.SaleUnit = new Unit();
if (reader[6].ToString() == string.Empty)
{
sale.SaleUnit.ID = -1;
}
else
{
sale.SaleUnit.ID = int.Parse(reader[6].ToString());
}
sale.Item = new Item();
sale.Item.ID = int.Parse(reader[1].ToString());
sales.Add(sale);
}
foreach (Sale s in sales)
{
s.Item = ItemCache.GetInstance()
.GetItemById(s.Item.ID);
if (s.SaleUnit.ID < 0)
{
s.SaleUnit = s.Item.Unit;
}
else
{
s.SaleUnit = UnitCache.GetInstance().GetUnitById(s.SaleUnit.ID);
}
}
return(sales);
}
private SalePurchase GetSaleBySaleIDFromDB(int salePurchaseID)
{
SalePurchase saleInDb = new SalePurchase();
OleDbCommand command = DBFunctions.GetInstance().GetCommand(
"Select ID, Item, SaleRate, SaleQuantity, Tax, SaleNumber, SaleUnit, TransactionId " +
"From Sales Where ID=?");
command.Parameters.AddWithValue("ID", salePurchaseID);
OleDbDataReader reader = DBFunctions.GetInstance().GetReader(command);
while (reader.Read())
{
saleInDb.ID = int.Parse(reader[0].ToString());
saleInDb.SaleRate = double.Parse(reader[2].ToString());
saleInDb.Quantity = double.Parse(reader[3].ToString());
saleInDb.SaleTax = double.Parse(reader[4].ToString());
saleInDb.Number = int.Parse(reader[5].ToString());
saleInDb.SaleUnit = new Unit();
if (reader[6].ToString() == string.Empty)
{
saleInDb.SaleUnit.ID = -1;
}
else
{
saleInDb.SaleUnit.ID = int.Parse(reader[6].ToString());
}
saleInDb.Item = new Item();
saleInDb.Item.ID = int.Parse(reader[1].ToString());
}
//fill up child objects
saleInDb.Item = ItemCache.GetInstance()
.GetItemById(saleInDb.Item.ID);
if (saleInDb.SaleUnit.ID < 0)
{
saleInDb.SaleUnit = saleInDb.Item.Unit;
}
else
{
saleInDb.SaleUnit = UnitCache.GetInstance().GetUnitById(saleInDb.SaleUnit.ID);
}
return(saleInDb);
}
private Item GetItemFromDbByName(string name)
{
Item item = new Item();
item.Name = name;
OleDbCommand command = DBFunctions.GetInstance().GetCommand("Select ID,RetailRate,ItemUnit,TaxRate, Stock From Items Where ItemName = LCase(?)");
command.Parameters.AddWithValue("ItemName", item.Name);
OleDbDataReader reader = DBFunctions.GetInstance().GetReader(command);
if (reader.Read())
{
item.ID = (int)reader[0];
item.Rate = double.Parse(reader[1].ToString());
item.Unit = UnitCache.GetInstance().GetUnitById((int)reader[2]);
item.TaxRate = double.Parse(reader[3].ToString());
item.Stock = double.Parse(reader[4].ToString());
}
else
{
item = null;
}
return(item);
}
public string GetNumberOfItemsSold(Item item, DateTime startTime, DateTime endTime)
{
StringBuilder s = new StringBuilder();
startTime = new DateTime(startTime.Year, startTime.Month, startTime.Day, 0, 0, 0);
endTime = new DateTime(endTime.AddDays(1.0).Year, endTime.AddDays(1.0).Month, endTime.AddDays(1.0).Day, 0, 0, 0);
OleDbCommand command = DBFunctions.GetInstance().GetCommand(
"Select Sum(SaleQuantity), S.SaleUnit From Sales as S, Transactions as T " +
"Where T.TransactionTime>=? And T.TransactionTime<? And T.ID=S.TransactionId And S.Item=? Group By S.ID, S.SaleUnit");
command.Parameters.Add("startTime", OleDbType.Date).Value = startTime;
command.Parameters.Add("endTime", OleDbType.Date).Value = endTime;
command.Parameters.AddWithValue("ID", item.ID);
OleDbDataReader reader = DBFunctions.GetInstance().GetReader(command);
while (reader.Read())
{
s.Append(reader[0].ToString());
s.Append(" ");
s.Append(UnitCache.GetInstance().GetUnitById(int.Parse(reader[1].ToString())).Name);
s.Append(". ");
}
return(s.ToString());
}