/// <summary> /// Adds a new invoice to the database /// </summary> /// <param name="date"></param> /// <param name="totalCharge"></param> /// <param name="items"></param> public void addInvoice(string date, string totalCharge, ObservableCollection <clsItem> items) { try { db = new clsDataAccess(); int iRet = 0; string sSQL = "INSERT INTO Invoices( InvoiceDate, TotalCharge) VALUES(#" + date + "#, " + totalCharge + ")"; db.ExecuteNonQuery(sSQL); string sSQLInvoiceNumber = "SELECT MAX(InvoiceNum) FROM Invoices"; DataSet newInvoice = db.ExecuteSQLStatement(sSQLInvoiceNumber, ref iRet); string invoiceNumber = newInvoice.Tables[0].Rows[0][0].ToString(); int i = 1; foreach (clsItem item in items) { string sSQLLineItem = "INSERT INTO LineItems(InvoiceNum, LineItemNum, ItemCode) " + "VALUES('" + invoiceNumber + "', '" + i + "' , '" + item.ItemCode + "')"; db.ExecuteNonQuery(sSQLLineItem); i += 1; } } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); } }
/// <summary> /// Updates an existing invoice in the database /// </summary> /// <param name="invoiceNum"></param> /// <param name="totalCharge"></param> /// <param name="items"></param> public void updateInvoice(string invoiceNum, string totalCharge, ObservableCollection <clsItem> items) { try { db = new clsDataAccess(); string sSQL = "UPDATE Invoices " + "SET TotalCharge = '" + totalCharge + "' WHERE InvoiceNum = " + invoiceNum; db.ExecuteNonQuery(sSQL); sSQL = "Delete FROM LineItems WHERE InvoiceNum = " + invoiceNum; db.ExecuteNonQuery(sSQL); int i = 1; foreach (clsItem item in items) { string sSQLLineItem = "INSERT INTO LineItems(InvoiceNum, LineItemNum, ItemCode) " + "VALUES('" + invoiceNum + "', '" + i + "' , '" + item.ItemCode + "')"; db.ExecuteNonQuery(sSQLLineItem); i += 1; } } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); } }
/// <summary> /// Deletes an invoice from the database /// </summary> /// <param name="invoiceNum"></param> public void deleteInvoice(string invoiceNum) { try { string sSQL = "Delete FROM LineItems WHERE InvoiceNum = " + invoiceNum; db.ExecuteNonQuery(sSQL); sSQL = "Delete FROM Invoices WHERE InvoiceNum = " + invoiceNum; db.ExecuteNonQuery(sSQL); } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message); } }
/// <summary> /// This SQL statement deletes a new invoice from the invoices table in Invoice.mdb /// </summary> /// <returns>Insert SQL</returns> public static void deleteInvoice(int iInvoiceNum) { try { clsDataAccess ds = new clsDataAccess(); string sSQL1 = "DELETE FROM LineItems WHERE InvoiceNum = " + iInvoiceNum; ds.ExecuteNonQuery(sSQL1); string sSQL = "DELETE FROM Invoices WHERE InvoiceNum = " + iInvoiceNum; ds.ExecuteNonQuery(sSQL); } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " ->" + ex.Message); } }
/// <summary> /// This SQL inserts a new invoice into the invoices table in Invoice.mdb /// </summary> /// <returns>Insert SQL</returns> public static void addInvoice(string sDate) { try { clsDataAccess ds = new clsDataAccess(); string sSQL = "INSERT INTO Invoices (InvoiceDate) VALUES ('" + sDate + "');"; ds.ExecuteNonQuery(sSQL); } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " ->" + ex.Message); } }
/// <summary> /// This SQL statement updates date on Invoices. /// </summary> /// <param name="iInvoiceNum"></param> /// <param name="sDate"></param> /// <returns>Update SQL statement</returns> public static void updateInvoice(int iInvoiceNum, string sDate) { try { clsDataAccess ds = new clsDataAccess(); string sSQL = "UPDATE Invoices SET InvoiceDate = '" + sDate + "' WHERE InvoiceNum = " + iInvoiceNum + "; "; ds.ExecuteNonQuery(sSQL); } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " ->" + ex.Message); } }
/// <summary> /// This SQL statement updates the invoice line item /// </summary> /// <param name="iInvoiceNumber"></param> /// <param name="iLineItemNum"></param> /// <returns>SQL statement</returns> public static void updateLineItem(int iInvoiceNumber, int iLineItemNumber, string sItemCode) { try { clsDataAccess ds = new clsDataAccess(); string sSQL = "UPDATE LineItems SET ItemCode = '" + sItemCode + "'" + "WHERE InvoiceNum = " + iInvoiceNumber + " AND LineItemNum = " + iLineItemNumber + ";"; ds.ExecuteNonQuery(sSQL); } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " ->" + ex.Message); } }
/// <summary> /// This SQL statement inserts a line item into the LineItem table in Invoice.mdb /// </summary> /// <param name="sItemCode"></param> /// <returns>Insert Line item</returns> public static void addInvoiceLineItem(int iInvoiceNumber, int iLineItemNumber, string ItemCode) { try { clsDataAccess ds = new clsDataAccess(); string sSQL = "INSERT INTO LineItems (InvoiceNum, LineItemNum, ItemCode) " + "VALUES (" + iInvoiceNumber + "," + iLineItemNumber + ",'" + ItemCode + "');"; ds.ExecuteNonQuery(sSQL); } catch (Exception ex) { throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " ->" + ex.Message); } }