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