/// <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(); }