public Payment GetPaymentByTransactionId(int transactionId)
{
double doubleVar;
int intVar;
Payment payment = null;
OleDbCommand command = DBFunctions.GetInstance().GetCommand(
"Select P.ID, P.Amount, P.BankMoney From Payments As P Where P.TransactionId=?");
command.Parameters.AddWithValue("Transaction", transactionId);
OleDbDataReader reader = DBFunctions.GetInstance().GetReader(command);
if (reader.Read())
{
payment = new Payment();
if (int.TryParse(reader[0].ToString(), out intVar))
{
payment.ID = intVar;
}
if (double.TryParse(reader[1].ToString(), out doubleVar))
{
payment.Amount = doubleVar;
}
payment.BankMoney = bool.Parse(reader[2].ToString());
reader.Close();
payment.Transaction = TransactionCache.GetInstance().GetTransactionById(transactionId);
}
return(payment);
}
public List <Payment> GetPaymentsByTime(DateTime startTime, DateTime endTime)
{
List <Payment> payments = new List <Payment>();
Payment payment;
OleDbCommand command = DBFunctions.GetInstance().GetCommand(
"Select P.ID, Amount, TransactionId From Payments As P, Transactions As T " +
"Where P.TransactionId=T.ID And T.TransactionTime>=? And T.TransactionTime<?");
command.Parameters.Add("startTime", OleDbType.Date).Value = startTime;
command.Parameters.Add("endTime", OleDbType.Date).Value = endTime;
OleDbDataReader reader = DBFunctions.GetInstance().GetReader(command);
while (reader.Read())
{
payment = new Payment();
payment.ID = int.Parse(reader[0].ToString());
payment.Amount = double.Parse(reader[1].ToString());
payment.Transaction = new Transaction();
payment.Transaction.ID = int.Parse(reader[2].ToString());
payments.Add(payment);
}
foreach (Payment p in payments)
{
p.Transaction = TransactionCache.GetInstance().GetTransactionById(p.Transaction.ID);
}
return(payments);
}
public static TransactionCache GetInstance()
{
if (instance == null)
{
instance = new TransactionCache();
}
return(instance);
}
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);
}
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);
}
}
public void Clear()
{
//nothing stored in instance to clear
instance = null;
}