/// <summary>
/// Get Payment Total For Day
/// </summary>
/// <param name="day">Day of query</param>
/// <param name="bankMoney">True for Bank Money, False for Hard Money</param>
/// <returns></returns>
public double GetPaymentTotalForDay(DateTime day, bool bankMoney)
{
OleDbCommand command = DBFunctions.GetInstance().GetCommand(
"Select Sum(P.Amount) From Payments As P, Transactions As T " +
"Where T.TransactionTime>=? And T.TransactionTime<? And T.ID=P.TransactionId And P.BankMoney=?");
command.Parameters.Add("beginTime", OleDbType.Date).Value = day.Date;
command.Parameters.Add("endTime", OleDbType.Date).Value = day.AddDays(1).Date;
command.Parameters.AddWithValue("BankMoney", bankMoney);
OleDbDataReader reader = DBFunctions.GetInstance().GetReader(command);
if (reader.Read())
{
double doubleVar = 0.0;
if (double.TryParse(reader[0].ToString(), out doubleVar))
{
return(doubleVar);
}
else
{
return(0);
}
}
else
{
return(0);
}
}
public List <Transaction> GetTransactionsByItem(int itemID)
{
DateTime timeVar;
Transaction tx;
List <Transaction> transactionsWithGivenItem = new List <Transaction>();
OleDbCommand command = DBFunctions.GetInstance().GetCommand(
"Select S.TransactionId, T.TransactionTime, T.Customer, T.SaleOrPurchase, T.Remarks, T.Project " +
"From Sales As S, Transactions As T Where S.Item=? And S.TransactionId=T.ID Order By T.TransactionTime");
command.Parameters.AddWithValue("Item", itemID);
OleDbDataReader reader = DBFunctions.GetInstance().GetReader(command);
while (reader.Read())
{
tx = new Transaction();
tx.ID = int.Parse(reader[0].ToString());
if (DateTime.TryParse(reader[1].ToString(), out timeVar))
{
tx.TransactionTime = timeVar;
}
tx.Customer = new Customer();
tx.Customer.ID = int.Parse(reader[2].ToString());
tx.IsPurchase = (int.Parse(reader[3].ToString()) == 0);//0 if purchase
tx.Remark = reader[4].ToString();
tx.ParentProject.ID = int.Parse(reader[5].ToString());
transactionsWithGivenItem.Add(tx);
}
return(transactionsWithGivenItem);
}
private Project GetProjectFromDbByName(string name)
{
Project project = new Project();
project.Name = name;
OleDbCommand command = DBFunctions.GetInstance().GetCommand(
"Select ID,Name, Status, Comment, StartDate, EndDate From Projects Where Name = ?");
command.Parameters.AddWithValue("Name", name);
OleDbDataReader reader = DBFunctions.GetInstance().GetReader(command);
if (reader.Read())
{
project.ID = (int)reader[0];
project.Name = reader[1].ToString();
project.Status = ParseStatus(reader[2].ToString());
project.Comment = reader[3].ToString();
project.StartDate = DateTime.Parse(reader[4].ToString());
project.EndDate = DateTime.Parse(reader[5].ToString());
}
else
{
project = null;
}
return(project);
}
public int[] GetTransactionIdsByCustomerId(int customerID)
{
int intVar;
List <int> transactionIds = new List <int>();
OleDbCommand command = DBFunctions.GetInstance().GetCommand(
"Select T.ID From Transactions As T, Customers As C Where T.Customer=C.ID And C.ID=?");
command.Parameters.AddWithValue("ID", customerID);
OleDbDataReader reader = DBFunctions.GetInstance().GetReader(command);
while (reader.Read())
{
if (int.TryParse(reader[0].ToString(), out intVar))
{
transactionIds.Add(intVar);
}
}
if (transactionIds.Count > 0)
{
return(transactionIds.ToArray());
}
else
{
return(null);
}
}
/// <summary>
/// FInds out deleted purchases in transaction and delete them from db
/// Then removes them from the cache.
/// </summary>
/// <param name="purchases">New list of purchases in the transaction</param>
public void MarkRemovals(List <Sale> sales, int transactionId)
{
int intVar = 0;
Dictionary <int, bool> markedSales = new Dictionary <int, bool>(sales.Count);
OleDbCommand command = DBFunctions.GetInstance().GetCommand(
"Select S.ID From Sales As S Where S.TransactionId = ?");
command.Parameters.AddWithValue("Transaction", transactionId);
OleDbDataReader reader = DBFunctions.GetInstance().GetReader(command);
while (reader.Read())
{
if (int.TryParse(reader[0].ToString(), out intVar))
{
//search inside updated sales list and check if it is still present
//if not, mark its value as false.
markedSales.Add(intVar, sales.FindIndex(delegate(Sale p) { return(p.ID == intVar); }) >= 0);
}
}
reader.Close();
foreach (KeyValuePair <int, bool> markedSale in markedSales)
{
if (!markedSale.Value) //if the dictionary item is marked as false
{
DeleteSale(markedSale.Key);
}
}
}
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 double GetPendingAmountBefore(int customerID, DateTime beforeDate)
{
double paymentBefore = 0.0;
double saleBefore = 0.0;
double doubleVar = 0.0;
OleDbCommand command = DBFunctions.GetInstance().GetCommand(
"Select Sum(S.SaleQuantity*S.SaleRate) From Sales As S, Transactions As T " +
"Where T.Customer=? And S.TransactionId=T.ID And T.TransactionTime<?");
command.Parameters.AddWithValue("CustomerId", customerID);
command.Parameters.Add("TransactionTime", OleDbType.Date).Value = beforeDate.Date;
OleDbDataReader reader = DBFunctions.GetInstance().GetReader(command);
if (reader.Read())
{
if (double.TryParse(reader[0].ToString(), out doubleVar))
{
saleBefore = doubleVar;
}
}
command = DBFunctions.GetInstance().GetCommand(
"Select Sum(P.Amount) From Payments As P, Transactions As T " +
"Where P.TransactionId=T.ID And T.Customer=? And T.TransactionTime<?");
command.Parameters.AddWithValue("ID", customerID);
command.Parameters.Add("TransactionTime", OleDbType.Date).Value = beforeDate.Date;
reader = DBFunctions.GetInstance().GetReader(command);
if (reader.Read())
{
if (double.TryParse(reader[0].ToString(), out doubleVar))
{
paymentBefore = doubleVar;
}
}
return(saleBefore - paymentBefore);
}
public string GetItemSaleDetail(Item selectedItem, 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 I.ItemName, S.SaleRate, S.SaleQuantity, S.SaleRate*S.SaleQuantity, C.CustomerName, T.TransactionTime, S.SaleUnit " +
"from Transactions T, Items I, Customers C, Sales S Where S.Item=? and TransactionTime>=? and TransactionTime<? and " +
"I.ID=S.Item and T.ID=S.TransactionId and C.ID=T.Customer");
command.Parameters.AddWithValue("itemId", selectedItem.ID);
command.Parameters.Add("startTime", OleDbType.Date).Value = startTime;
command.Parameters.AddWithValue("endTime", OleDbType.Date).Value = endTime;
OleDbDataReader reader = DBFunctions.GetInstance().GetReader(command);
while (reader.Read())
{
s.Append(" " + reader[0].ToString());
s.Append("\t");
s.Append(" " + reader[1].ToString());
s.Append("\t");
s.Append(" " + reader[2].ToString());
s.Append("\t");
s.Append(" " + reader[3].ToString());
s.Append("\t");
s.Append(" " + (reader[4].ToString().Trim() == string.Empty? "cash" : reader[4].ToString()));
s.Append("\t");
s.Append(" " + reader[5].ToString());
s.Append("\t");
s.Append(" " + reader[6].ToString());
s.Append("\n");
}
return(s.ToString());
}
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);
}
private TransactionCache()
{
//check if db is updated for sale and purchase
if (!DBFunctions.GetInstance().CheckIfColumnExistsInTable("SaleOrPurchase", "Transactions"))
{
//db needs to be upgraded
DBFunctions.GetInstance().OpenConnection();
OleDbCommand command = DBFunctions.GetInstance().GetCommand(
"ALTER TABLE Transactions ADD COLUMN SaleOrPurchase NUMBER");
command.ExecuteNonQuery();
//now default all non-set previous values to sale
command = DBFunctions.GetInstance().GetCommand("Update Transactions " +
"Set SaleOrPurchase=?");
command.Parameters.AddWithValue("SaleOrPurchase", 1);
command.ExecuteNonQuery();
}
//check if db is updated for projects
if (!DBFunctions.GetInstance().CheckIfColumnExistsInTable("Project", "Transactions"))
{
//get the id of default project
int defaultProjectId = ProjectCache.GetInstance().GetProjectByName(string.Empty).ID;
//db needs to be upgraded
DBFunctions.GetInstance().OpenConnection();
OleDbCommand command = DBFunctions.GetInstance().GetCommand(
"ALTER TABLE Transactions ADD COLUMN Project NUMBER");
command.ExecuteNonQuery();
//now default set default project for everything.
command = DBFunctions.GetInstance().GetCommand("Update Transactions " +
"Set Project=?");
command.Parameters.AddWithValue("Project", defaultProjectId);
command.ExecuteNonQuery();
}
}
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);
}
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 <Expense> GetExpenses(DateTime startDate, DateTime endDate)
{
Expense expense;
List <Expense> expenses;
OleDbCommand command = DBFunctions.GetInstance().GetCommand(
"Select ID, Amount, ExpenseItem, Remark, LastUpdatedTime, Serial From Expenses Where LastUpdatedTime>=? And LastUpdatedTime<?");
command.Parameters.Add("StartDate", OleDbType.Date).Value = startDate;
command.Parameters.Add("EndDate", OleDbType.Date).Value = endDate;
OleDbDataReader reader = DBFunctions.GetInstance().GetReader(command);
expenses = new List <Expense>();
while (reader.Read())
{
expense = new Expense();
expense.ID = int.Parse(reader[0].ToString());
expense.Amount = double.Parse(reader[1].ToString());
expense.Remark = reader[3].ToString();
expense.TimeStamp = DateTime.Parse(reader[4].ToString());
expense.Number = int.Parse(reader[5].ToString());
expense.ExpenseItem = GetExpenseItemById(int.Parse(reader[2].ToString()));
expenses.Add(expense);
}
return(expenses);
}
private Assembly GetAssemblyFromDbByParentName(string name)
{
Assembly assembly = new Assembly();
assembly.Parent.Name = name;
OleDbCommand command = DBFunctions.GetInstance().GetCommand(
"Select ID,Parent,Children From Assemblies Where Parent = ?");
command.Parameters.AddWithValue("Parent", ItemCache.GetInstance().GetItemByName(assembly.Parent.Name).ID);
OleDbDataReader reader = DBFunctions.GetInstance().GetReader(command);
if (reader.Read())
{
assembly.ID = (int)reader[0];
assembly.Parent = ItemCache.GetInstance().GetItemById((int)reader[1]);
string [] childrenIds = reader[2].ToString().Split(';');
foreach (string childId in childrenIds)
{
if (childId.Length > 0)
{
assembly.Children.Add(ItemCache.GetInstance().GetItemById(int.Parse(childId)));
}
}
}
else
{
assembly = null;
}
return(assembly);
}
public string GetItemsSold(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 I.ItemName, S.SaleRate, Sum(SaleQuantity), S.SaleUnit From Items as I, Sales as S, Transactions as T " +
"Where T.TransactionTime>=? And T.TransactionTime<? And T.ID=S.TransactionId And S.Item=I.ID Group By I.ItemName, S.SaleRate, S.SaleUnit");
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())
{
s.Append(" " + reader[0].ToString());
s.Append("\t");
s.Append(" " + reader[1].ToString());
s.Append("\t");
s.Append(" " + reader[2].ToString());
s.Append("\t");
s.Append(" " + reader[3].ToString());
s.Append("\n");
}
return(s.ToString());
}
public DailyStatus SyncDayStatus(DailyStatus dailyStatus)
{
if (GetDayStat(dailyStatus.OpenDate) == null)
{
//insert
OleDbCommand command = DBFunctions.GetInstance().GetCommand(
"Insert Into DailyStatus(OpenDate, DayStartCash, DayEndCash) " +
"Values(?,?,?)");
command.Parameters.AddWithValue("OpenDate", dailyStatus.OpenDate.Date.ToString());
command.Parameters.AddWithValue("DayStartCash", dailyStatus.DayStartCash);
command.Parameters.AddWithValue("DayEndCash", dailyStatus.DayEndCash);
command.ExecuteNonQuery();
//dailyStatuses.Add(dailyStatus);
}
else
{
//update
OleDbCommand command = DBFunctions.GetInstance().GetCommand(
"Update DailyStatus Set DayStartCash=?, DayEndCash=? Where OpenDate=?");
command.Parameters.AddWithValue("startCash", dailyStatus.DayStartCash);
command.Parameters.AddWithValue("endCash", dailyStatus.DayEndCash);
command.Parameters.Add("openDate", OleDbType.Date).Value = dailyStatus.OpenDate.Date;
command.ExecuteNonQuery();
DailyStatus d = GetDayStat(dailyStatus.OpenDate);
d = dailyStatus;
}
return(dailyStatus);
}
public string GetExpenseDetail(string expenseName, DateTime startTime, DateTime endTime)
{
StringBuilder s = new StringBuilder();
OleDbCommand command = DBFunctions.GetInstance().GetCommand(
"Select I.Item, E.LastUpdatedTime, E.Amount, E.Remark From ExpenseItems I, Expenses E " +
"Where E.LastUpdatedTime>=? And E.LastUpdatedTime<? And I.ID = E.ExpenseItem And Trim(LCase(I.Item))=?");
command.Parameters.Add("startTime", OleDbType.Date).Value = startTime.Date;
command.Parameters.Add("endTime", OleDbType.Date).Value = endTime.AddDays(1.0).Date;
command.Parameters.AddWithValue("item", expenseName.ToLower().Trim());
OleDbDataReader reader = DBFunctions.GetInstance().GetReader(command);
while (reader.Read())
{
s.Append(" " + reader[0].ToString());
s.Append("\t");
s.Append(" " + reader[1].ToString());
s.Append("\t");
s.Append(" " + reader[2].ToString());
s.Append("\t");
s.Append(" " + reader[3].ToString());
s.Append("\n");
}
return(s.ToString());
}
private CustomerCache()
{
lock (dummy)
{
customers.Clear();
Customer customer;
DBFunctions.GetInstance().OpenConnection();
OleDbDataReader reader = DBFunctions.GetInstance().GetReader(
"Select ID, CustomerName, PhoneNumber, CustomerAddress from Customers");
while (reader.Read())
{
customer = new Customer();
customer.ID = (int)reader[0];
customer.Name = reader[1].ToString();
customer.Phone = reader[2].ToString();
customer.Address = reader[3].ToString();
customers.Add(customer);
}
//check if default customer exists in the set, if not, create it and add it to db and cache
if (GetCustomerByName(string.Empty) == null)
{
customer = new Customer();
customer.Name = string.Empty;
customer.Address = string.Empty;
customer.Phone = string.Empty;
InsertNewCustomer(customer);
}
}
}
public void DeleteExpenseById(int id)
{
OleDbCommand command = DBFunctions.GetInstance().GetCommand(
"Delete From Expenses Where ID=?");
command.Parameters.AddWithValue("ID", id);
command.ExecuteNonQuery();
}
public void UpdateStock(Item item)
{
OleDbCommand command = DBFunctions.GetInstance().GetCommand("Update Items Set Stock=? Where ID=?");
command.Parameters.AddWithValue("Stock", item.Stock);
command.Parameters.AddWithValue("ID", item.ID);
command.ExecuteNonQuery();
}
internal void UpdateMovingAvgRate(Item item)
{
OleDbCommand command = DBFunctions.GetInstance().GetCommand("Update Items Set AvgCost=? Where ID=?");
command.Parameters.AddWithValue("AvgCost", item.MovingAvgRate);
command.Parameters.AddWithValue("ID", item.ID);
command.ExecuteNonQuery();
}
public void DeleteCustomer(int selectedCustomerID)
{
OleDbCommand command = DBFunctions.GetInstance().GetCommand(
"Delete From Customers Where ID=?");
command.Parameters.AddWithValue("ID", selectedCustomerID);
command.ExecuteNonQuery();
}
private void InsertUnitToDB(Unit unit)
{
DBFunctions.GetInstance().OpenConnection();
OleDbCommand command = DBFunctions.GetInstance().GetCommand("Insert Into Units " +
"(UnitName, LastUpdatedTime) Values(LCase(?), Now())");
command.Parameters.AddWithValue("UnitName", unit.Name);
command.ExecuteNonQuery();
}
private void InsertAssemblyToDb(Assembly assembly)
{
DBFunctions.GetInstance().OpenConnection();
OleDbCommand command = DBFunctions.GetInstance().GetCommand("Insert Into Assemblies " +
"(Parent,Children) Values(?,?)");
command.Parameters.AddWithValue("Parent", assembly.Parent.ID);
command.Parameters.AddWithValue("Children", assembly.ChildrenIds);
command.ExecuteNonQuery();
}
public void DeletePayment(int paymentId)
{
OleDbCommand command = DBFunctions.GetInstance().GetCommand("Delete From Payments Where ID=?");
command.Parameters.AddWithValue("ID", paymentId);
if (command.ExecuteNonQuery() > 1)
{
throw new ApplicationException("Error While Deleting Payment");
}
}
public void DeleteItem(int selectedItemID)
{
//remove from db
DBFunctions.GetInstance().OpenConnection();
OleDbCommand command = DBFunctions.GetInstance().GetCommand("Delete From Items Where Id=?");
command.Parameters.AddWithValue("Id", selectedItemID);
command.ExecuteNonQuery();
//remove from cache
items.Remove(GetItemById(selectedItemID));
}
public void UpdateCustomer(Customer changedCustomer)
{
OleDbCommand command = DBFunctions.GetInstance().GetCommand(
"Update Customers Set CustomerName=?, CustomerAddress=?, PhoneNumber=? Where ID=?");
command.Parameters.AddWithValue("CustomerName", changedCustomer.Name);
command.Parameters.AddWithValue("Address", changedCustomer.Address);
command.Parameters.AddWithValue("PhoneNumber", changedCustomer.Phone);
command.Parameters.AddWithValue("ID", changedCustomer.ID);
command.ExecuteNonQuery();
}
public void DeleteTransaction(int transactionId)
{
OleDbCommand command = DBFunctions.GetInstance().GetCommand(
"Delete From Transactions Where ID=?");
command.Parameters.AddWithValue("ID", transactionId);
if (command.ExecuteNonQuery() > 1)
{
throw new Exception("Error while deleting transaction. Tx ID:" + transactionId);
}
}
private void InsertNewCustomerToDb(Customer customer)
{
DBFunctions.GetInstance().OpenConnection();
OleDbCommand command = DBFunctions.GetInstance().GetCommand("Insert Into Customers " +
"(CustomerName, CustomerAddress, PhoneNumber, LastUpdatedTime) Values (LCase(?),?,?,Now())");
command.Parameters.AddWithValue("CustomerName", customer.Name);
command.Parameters.AddWithValue("CustomerAddress", customer.Address);
command.Parameters.AddWithValue("PhoneNumber", customer.Phone);
command.ExecuteNonQuery();
}
public void ReplaceCustomer(int toBeReplacedID, int newID)
{
//update
DBFunctions.GetInstance().OpenConnection();
OleDbCommand command = DBFunctions.GetInstance().GetCommand("Update Transactions " +
"Set Customer=? Where Customer=?");
command.Parameters.AddWithValue("CustomerID", newID);
command.Parameters.AddWithValue("OldID", toBeReplacedID);
command.ExecuteNonQuery();
}