/// <summary>
/// This SQL statement gets item names of all available items
/// </summary>
/// <param name="sItemCode"></param>
/// <returns>Item names</returns>
public static int getNewInvoiceNumber()
{
try
{
clsDataAccess ds = new clsDataAccess();
int id;
string sSQL = "SELECT Max(InvoiceNum) AS InvoiceNumber FROM Invoices; ";
id = Int32.Parse(ds.ExecuteScalarSQL(sSQL));
return(id);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." +
MethodInfo.GetCurrentMethod().Name + " ->" + ex.Message);
}
}
/// <summary>
/// This SQL statement gets item names of all available items
/// </summary>
/// <param name="sItemCode"></param>
/// <returns>Item names</returns>
public static clsInvoice getInvoice(int invoiceNumber)
{
try
{
//local variables to store returned data
clsDataAccess ds = new clsDataAccess();
clsInvoice invoice = new clsInvoice();
DataSet dataSet = new DataSet();
ObservableCollection <clsItem> list = new ObservableCollection <clsItem>();
//set invoice object attribute invoice number
invoice.setInvoiceNumber(invoiceNumber);
//get invoice date using invoice number
string sSQL = "SELECT InvoiceDate FROM Invoices WHERE InvoiceNum = " + invoiceNumber;
invoice.setInvoiceDate(ds.ExecuteScalarSQL(sSQL).ToString());
//get line items
int iRef = 0;
sSQL = "SELECT LineItems.ItemCode, ItemDesc.ItemDesc, ItemDesc.Cost FROM ItemDesc " +
"INNER JOIN LineItems ON ItemDesc.ItemCode = LineItems.ItemCode WHERE LineItems.InvoiceNum = " + invoiceNumber;
dataSet = ds.ExecuteSQLStatement(sSQL, ref iRef);
foreach (DataRow dr in dataSet.Tables[0].Rows)
{
clsItem item = new clsItem();
item.sItemCode = dr[0].ToString();
item.sItemDesc = dr[1].ToString();
item.sCost = dr[2].ToString();
list.Add(item);
}
invoice.setLineItemList(list);
return(invoice);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." +
MethodInfo.GetCurrentMethod().Name + " ->" + ex.Message);
}
}