public ObservableCollection <clsItem> itemsCollection()
{
clsDataAccess db;
ObservableCollection <clsItem> col_Items;
db = new clsDataAccess();
string sSQL; //Holds an SQL statement
int iRet = 0; //Number of return values
DataSet ds = new DataSet(); //Holds the return values
clsItem items; //Used to load the return values into the combo box
sSQL = "SELECT ItemCode, ItemDesc, Cost " + "FROM ItemDesc";
col_Items = new ObservableCollection <clsItem>();
ds = db.ExecuteSQLStatement(sSQL, ref iRet);
//Creates Flight objects based on the data pulled from the query than adds the object to a list
for (int i = 0; i < iRet; i++)
{
items = new clsItem();
items.ItemCode = ds.Tables[0].Rows[i][0].ToString();
items.ItemDesc = ds.Tables[0].Rows[i]["ItemDesc"].ToString();
items.Cost = ds.Tables[0].Rows[i]["Cost"].ToString();
col_Items.Add(items);
}
return(col_Items);
}
/// <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>
/// Returns the full list of invoices from the database
/// </summary>
/// <returns></returns>
public ObservableCollection <clsInvoice> invoicesCollection()
{
try
{
string sSQL; //Holds an SQL statement
int iRet = 0; //Number of return values
ds = new DataSet();
db = new clsDataAccess();
ObservableCollection <clsInvoice> col_Invoices = new ObservableCollection <clsInvoice>();
clsInvoice invoice;
sSQL = "SELECT InvoiceNum, InvoiceDate, TotalCharge " +
"FROM Invoices";
ds = db.ExecuteSQLStatement(sSQL, ref iRet);
for (int i = 0; i < iRet; i++)
{
invoice = new clsInvoice();
invoice.InvoiceNum = ds.Tables[0].Rows[i][0].ToString();
invoice.InvoiceDate = ds.Tables[0].Rows[i]["InvoiceDate"].ToString();
invoice.TotalCharge = ds.Tables[0].Rows[i]["TotalCharge"].ToString();
col_Invoices.Add(invoice);
}
return(col_Invoices);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
DataSet ds; // Dataset to hold the returned data from queries
/// <summary>
/// Returns the full list of jewelry items from the database
/// </summary>
/// <returns></returns>
public ObservableCollection <clsItem> itemsCollection()
{
try
{
db = new clsDataAccess();
ObservableCollection <clsItem> col_Items = new ObservableCollection <clsItem>();
string sSQL; //Holds an SQL statement
int iRet = 0; //Number of return values
ds = new DataSet(); //Holds the return values
clsItem items; //Used to load the return values into the combo box
sSQL = "SELECT ItemCode, ItemDesc, Cost " + "FROM ItemDesc";
ds = db.ExecuteSQLStatement(sSQL, ref iRet);
//Creates item objects based on the data pulled from the query than adds the object to a list
for (int i = 0; i < iRet; i++)
{
items = new clsItem();
items.ItemCode = ds.Tables[0].Rows[i][0].ToString();
items.ItemDesc = ds.Tables[0].Rows[i]["ItemDesc"].ToString();
items.Cost = ds.Tables[0].Rows[i]["Cost"].ToString();
col_Items.Add(items);
}
return(col_Items);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// Returns the collection of items from a certain invoice
/// </summary>
/// <param name="invoiceNumber"></param>
/// <returns></returns>
public ObservableCollection <clsItem> invoiceItems(string invoiceNumber)
{
try
{
db = new clsDataAccess();
ObservableCollection <clsItem> col_Items = new ObservableCollection <clsItem>();
List <string> itemCode = new List <string>();
string sSQL; //Holds an SQL statement
int iRet = 0; //Number of return values
ds = new DataSet(); //Holds the return values
clsItem items; //Used to load the return values into the combo box
sSQL = "SELECT ItemCode FROM LineItems "
+ "WHERE InvoiceNum = " + invoiceNumber;
ds = db.ExecuteSQLStatement(sSQL, ref iRet);
// adds all of the different item codes on the invoice to a list
for (int i = 0; i < iRet; i++)
{
itemCode.Add(ds.Tables[0].Rows[i]["ItemCode"].ToString());
}
if (itemCode.Count > 0)
{
foreach (string code in itemCode)
{
sSQL = "SELECT ItemCode, ItemDesc, Cost FROM ItemDesc "
+ "WHERE ItemCode = '" + code + "'";
ds = db.ExecuteSQLStatement(sSQL, ref iRet);
items = new clsItem();
items.ItemCode = ds.Tables[0].Rows[0][0].ToString();
items.ItemDesc = ds.Tables[0].Rows[0]["ItemDesc"].ToString();
items.Cost = ds.Tables[0].Rows[0]["Cost"].ToString();
col_Items.Add(items);
}
}
return(col_Items);
}
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);
}
}