/// <summary> /// Updates an invoice in the database to have all the given products attached to it. /// </summary> /// <param name="invoice">The invoice ID number</param> /// <param name="invoiceProducts">The products to attach to the invoice</param> public static void updateInvoice(Invoice invoice, List <Product> invoiceProducts) { try { double invoiceTotal = 0; foreach (Product product in invoiceProducts) { if (product.needDeleted) { dataAccess.ExecuteNonQuery(SQLStrings.removeLineItem(invoice.ID, product.ProductCode)); continue; } invoiceTotal += product.ProductCost; if (!product.inDB) { dataAccess.ExecuteNonQuery(SQLStrings.insertLineItem(invoice.ID, product.ProductCode)); } } dataAccess.ExecuteNonQuery(SQLStrings.updateInvoice(invoice.ID, invoice.Date.ToShortDateString(), invoiceTotal)); } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + ":" + MethodInfo.GetCurrentMethod().Name + "->" + ex.Message); } }
/// <summary> /// Checks to see it ItemCode already exists in database returns bool /// </summary> /// <param name="ItemCode"></param> /// <returns></returns> public static bool canInsert(string ItemCode) { int iRowCount = 0; dataAccess.ExecuteSQLStatement(SQLStrings.checkProductExists(ItemCode), ref iRowCount); if (iRowCount > 0) { return(false); } else { return(true); } }
/// <summary> /// Gets all products codes attached to an invoice. /// </summary> /// <returns>A List of product codes</returns> public static List <string> getAllProductsInUse() { int numRows = 0; DataSet ds = dataAccess.ExecuteSQLStatement(SQLStrings.getAllProductsInUse(), ref numRows); List <string> list = new List <string>(); for (int i = 0; i < numRows; i++) { string itemCode = ds.Tables[0].Rows[i][0].ToString(); list.Add(itemCode); } return(list); }
/// <summary> /// Gets all invoice numbers for a given product code. /// </summary> /// <param name="productCode">The product code</param> /// <returns>A List of invoice numbers</returns> public static List <int> getAllInvoiceIDsByProduct(string productCode) { int numRows = 0; DataSet ds = dataAccess.ExecuteSQLStatement(SQLStrings.getInvoiceIDsByProductCode(productCode), ref numRows); List <int> list = new List <int>(); for (int i = 0; i < numRows; i++) { int invoiceNum = Int32.Parse(ds.Tables[0].Rows[i][0].ToString()); list.Add(invoiceNum); } return(list); }
/// <summary> /// Gets a list of all products. /// </summary> /// <returns>A list of all products.</returns> public static List <Product> getProductList() { int numRows = 0; DataSet ds = dataAccess.ExecuteSQLStatement(SQLStrings.getAllProducts(), ref numRows); if (numRows == 0) { return(null); } List <Product> products = new List <Product>(); foreach (DataRow row in ds.Tables[0].Rows) { Product p = new Product(); p.ProductCode = row[0].ToString(); p.ProductDescription = row[1].ToString(); p.ProductCost = double.Parse(row[2].ToString()); products.Add(p); } return(products); }
//-------------------------------------------------------------------------------------------------------------------------------- //-----------------------------------------------------End Search Methods--------------------------------------------------------- //-------------------------------------------------------------------------------------------------------------------------------- #endregion #region Main Window Methods //-------------------------------------------------------------------------------------------------------------------------------- //--------------------------------------------------Begin Main Window Methods----------------------------------------------------- //-------------------------------------------------------------------------------------------------------------------------------- /// <summary> /// Gets the invoice with the given invoice number. /// </summary> /// <param name="id">The invoice number</param> /// <returns>The invoice</returns> public static Invoice getInvoiceByID(int id) { try { int numRows = 0; DataSet ds = dataAccess.ExecuteSQLStatement(SQLStrings.getInvoiceForId(id), ref numRows); if (numRows > 1) { throw new Exception("Query returns multiple invoices for id " + id); } Invoice invoice = new Invoice(); invoice.ID = int.Parse(ds.Tables[0].Rows[0][0].ToString()); invoice.Date = DateTime.Parse(ds.Tables[0].Rows[0][1].ToString()); DataSet invoiceProducts = dataAccess.ExecuteSQLStatement(SQLStrings.getLineItemsForInvoice(id), ref numRows); if (numRows == 0) //no products on invoice { return(invoice); } List <Product> products = new List <Product>(); double totalCost = 0; foreach (DataRow row in invoiceProducts.Tables[0].Rows) { Product product = new Product(); product.ProductCode = row[1].ToString(); product.ProductDescription = row[2].ToString(); product.ProductCost = double.Parse(row[3].ToString()); product.inDB = true; totalCost += product.ProductCost; products.Add(product); } invoice.products = products; return(invoice); } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + ":" + MethodInfo.GetCurrentMethod().Name + "->" + ex.Message); } }
/// <summary> /// Adds an ivoice to the database with the given products attached to it. /// </summary> /// <param name="date">The invoice date</param> /// <param name="prodList">The products attached to the invoice</param> /// <returns></returns> public static int addInvoice(DateTime date, List <Product> prodList) { double invoiceTotal = 0; foreach (Product p in prodList) { if (!p.needDeleted) { invoiceTotal += p.ProductCost; } } dataAccess.ExecuteNonQuery(SQLStrings.insertInvoice(date.ToShortDateString(), invoiceTotal)); int newId = int.Parse(dataAccess.ExecuteScalarSQL(SQLStrings.getNewInvoice())); foreach (Product p in prodList) { if (!p.needDeleted) { dataAccess.ExecuteNonQuery(SQLStrings.insertLineItem(newId, p.ProductCode)); } } return(newId); }
/// <summary> /// Gets a list of all invoice totals. /// </summary> /// <returns>A list of all invoice totals.</returns> public static BindingList <double> getInvoiceTotals() { try { int numRows = 0; DataSet ds = dataAccess.ExecuteSQLStatement(SQLStrings.getAllInvoiceTotals(), ref numRows); BindingList <double> list = new BindingList <double>(); for (int i = 0; i < numRows; i++) { double total = Double.Parse(ds.Tables[0].Rows[i][0].ToString()); list.Add(total); } return(list); } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); } }
/// <summary> /// returns a DataSet of all item from the ItemDesc table /// </summary> /// <param name="iRowCount"></param> /// <returns></returns> public static DataSet getProductDataSet(ref int iRowCount) { DataSet ds = new DataSet(); return(ds = dataAccess.ExecuteSQLStatement(SQLStrings.getAllProducts(), ref iRowCount)); }
/// <summary> /// Deletes items form ItemDesc based on ItemCode /// </summary> /// <param name="itemCode"></param> public static void deleteProductItemDesc(string itemCode) { dataAccess.ExecuteNonQuery(SQLStrings.deleteProductItemDesc(itemCode)); }
/// <summary> /// Deletes an invoice. /// </summary> /// <param name="id">The invoice number of the invoice to be deleted</param> public static void deleteInvoice(int id) { dataAccess.ExecuteNonQuery(SQLStrings.removeLineItems(id)); dataAccess.ExecuteNonQuery(SQLStrings.deleteInvoice(id)); }
/// <summary> /// Updated items in ItemDesc based on ItemCode /// </summary> /// <param name="sItemCode"></param> /// <param name="sItemDesc"></param> /// <param name="sCost"></param> public static void updateProductItemDesc(string sItemCode, string sItemDesc, string sCost) { dataAccess.ExecuteNonQuery(SQLStrings.updateProductItemDesc(sItemCode, sItemDesc, sCost)); }
//-------------------------------------------------------------------------------------------------------------------------------- //-----------------------------------------------------Begin Product Methods------------------------------------------------------ //-------------------------------------------------------------------------------------------------------------------------------- /// <summary> /// Adds a product. /// </summary> /// <param name="sItemCode">The item code</param> /// <param name="sItemDesc">The item description</param> /// <param name="sCost">The item cost</param> public static void addProduct(string sItemCode, string sItemDesc, string sCost) { dataAccess.ExecuteNonQuery(SQLStrings.insertProduct(sItemCode, sItemDesc, sCost)); Console.WriteLine("Data was inserted"); }
//-------------------------------------------------------------------------------------------------------------------------------- //------------------------------------------------------End Product Methods------------------------------------------------------- //-------------------------------------------------------------------------------------------------------------------------------- #endregion #region Search Methods //-------------------------------------------------------------------------------------------------------------------------------- //-----------------------------------------------------Begin Search Methods------------------------------------------------------- //-------------------------------------------------------------------------------------------------------------------------------- /// <summary> /// Gets a DataSet of all invoices based on the search filters. /// </summary> /// <param name="id">Invoice ID.</param> /// <param name="date">Invoice Date.</param> /// <param name="total">Invoice Total.</param> /// <returns>A DataSet of all invoices based on the search filters.</returns> public static DataSet getInvoiceList(int id, DateTime?date, double total) { try { int numRows = 0; string query = ""; if (id == -1) { if (date == null) { if (total == -1.0) //no id, no date, no total { query = SQLStrings.getAllInvoices(); } else //no id, no date, total { query = SQLStrings.getInvoicesForTotal(total); } } else { if (total == -1.0) //no id, date, no total { query = SQLStrings.getInvoiceForDate(((DateTime)date).ToShortDateString()); } else //no id, date, total { query = SQLStrings.getInvoice(((DateTime)date).ToShortDateString(), total); } } } else { if (date == null) { if (total == -1.0) //id, no date, no total { query = SQLStrings.getInvoiceForId(id); } else //id, no date, total { query = SQLStrings.getInvoiceByIDTotal(id, total); } } else { if (total == -1.0) //id, date, no total { query = SQLStrings.getInvoiceByIDDate(id, ((DateTime)date).ToShortDateString()); } else //id, date, total { query = SQLStrings.getInvoiceByIDDateTotal(id, ((DateTime)date).ToShortDateString(), total); } } } return(dataAccess.ExecuteSQLStatement(query, ref numRows)); } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); } }