/// <summary>
/// Provides the information for the InventoryWindow from the database
/// </summary>
/// <returns></returns>
public ObservableCollection <clsItemsLogic> theInventory()
{
ObservableCollection <clsItemsLogic> inv = new ObservableCollection <clsItemsLogic>();
try
{
int num = 0;
ds = db.ExecuteSQLStatement(SQLQueries.SelectAllInventory(), ref num);
for (int i = 0; i < num; i++)
{
clsItemsLogic inventory = new clsItemsLogic();
inventory.InventoryLetter = ds.Tables[0].Rows[i][0].ToString();
inventory.ItemDesc = ds.Tables[0].Rows[i][1].ToString();
inventory.ItemCost = Convert.ToDecimal(ds.Tables[0].Rows[i][2].ToString());
inv.Add(inventory);
}
}
catch (Exception ex)
{
//Just throw the exception
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." +
MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
return(inv);
}
/// <summary>
/// Gets all items.
/// </summary>
/// <returns>A list of item objects.</returns>
public ObservableCollection <Items.clsItem> GetItems()
{
try
{
int retVal = 0;
string itemsSQL = sql.SelectItems();
ds = data.ExecuteSQLStatement(itemsSQL, ref retVal);
itemsList = new ObservableCollection <Items.clsItem>();
Items.clsItem item;
// fills itemsList with queried items
for (int i = 0; i < retVal; i++)
{
item = new Items.clsItem(ds.Tables[0].Rows[i][0].ToString(), ds.Tables[0].Rows[i][2].ToString(), ds.Tables[0].Rows[i][1].ToString());
itemsList.Add(item);
}
return(itemsList);
}
catch (Exception ex)
{
//Just throw the exception
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." +
MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
}//constructor
public ObservableCollection <Invoice> getInvoices()
{
ObservableCollection <Invoice> invoices = new ObservableCollection <Invoice>();
try
{
//set up for query
DataSet ds;
int iRef = 0;
string query = "SELECT InvoiceNum, InvoiceDate, TotalCost FROM Invoices";
//execute query
ds = db.ExecuteSQLStatement(query, ref iRef);
//create and store objects
for (int i = 0; i < iRef; i++)
{
string invoiceNum = ds.Tables[0].Rows[i]["InvoiceNum"].ToString();
string invoiceDate = ds.Tables[0].Rows[i]["InvoiceDate"].ToString();
string totalCost = ds.Tables[0].Rows[i]["TotalCost"].ToString();
Invoice invoice = new Invoice(Int32.Parse(invoiceNum), invoiceDate, Convert.ToDouble(totalCost));
invoices.Add(invoice);
}
}
catch (Exception ex)
{
System.IO.File.AppendAllText("C:\\Error.txt", Environment.NewLine +
"HandleError Exception: " + ex.Message);
}
return(invoices);
}
//will have a method that queries the DB for the flights, loops through them, and for each flight creates an object of clsFlight and adds it to a generic list of clsFlight objects. This list will then be returned by this method.
/// <summary>
/// Method for generating the flights class and storing them in their own flightdetails class
/// </summary>
/// <returns></returns>
public List <clsFlight> getFlights()
{
List <clsFlight> flights = new List <clsFlight>();
try
{
//Create a DataSet to hold the data
DataSet ds = new DataSet();
//Number of return values
int iRet = 0;
//Get all the values from the Authors table
ds = db.ExecuteSQLStatement("SELECT * FROM flight", ref iRet);
for (int i = 0; i < iRet; i++)
{
FlightDetails = new clsFlight();
FlightDetails.getFlightNumber = ds.Tables[0].Rows[i][0].ToString();
FlightDetails.getFlightNumber = ds.Tables[0].Rows[i][1].ToString();
FlightDetails.getAircraftType = ds.Tables[0].Rows[i]["Aircraft_Type"].ToString();
flights.Add(FlightDetails);
}
}
catch (Exception ex)
{
MessageBox.Show(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
//return false;
}
return(flights.Cast <clsFlight>().ToList());
}
/// <summary>
/// Grab all rows from items desc database table.
/// </summary>
/// <returns></returns>
public List <clsItemDescObj> SelectItemDescData()
{
try
{
int rowsReturned = 0;
string sSql = "SELECT * FROM ItemDesc ORDER BY ItemCode";
DataSet ds = da.ExecuteSQLStatement(sSql, ref rowsReturned);
List <clsItemDescObj> itemList = new List <clsItemDescObj> {
};
if (rowsReturned == 0)
{
throw new Exception("No itmes were found.");
}
int row = 0;
while (row < rowsReturned)
{
clsItemDescObj item = new clsItemDescObj();
item.sItemCode = ds.Tables[0].Rows[row]["ItemCode"].ToString();
item.sItemDesc = ds.Tables[0].Rows[row]["ItemDesc"].ToString();
item.sCost = ds.Tables[0].Rows[row]["Cost"].ToString();
itemList.Add(item);
row++;
}
return(itemList);
}
catch (Exception ex)
{
System.IO.File.AppendAllText("C:\\Error.txt", Environment.NewLine +
"HandleError Exception: " + ex.Message);
// return empty string because something is wrong
return(null);
}
}
/// <summary>
/// returns dataset of the invoice number searched for
/// </summary>
/// <param name="InvoiceNumber"></param>
/// <returns></returns>
public DataSet SelectInvoice(int InvoiceNumber)
{
int iRet = 0;
ds = db.ExecuteSQLStatement($"SELECT InvoiceNum, InvoiceDate, TotalCost FROM Invoices WHERE InvoiceNum = {InvoiceNumber}", ref iRet);
return(ds);
}
/// <summary>
/// Runs the provided SQL string and fills the invoice variable with the results.
/// </summary>
/// <param name="sSQL"></param>
/// <returns></returns>
public List <clsItems> getItems(string sSQL)
{
try
{
DataSet ds = new DataSet();
items = new List <clsItems>();
clsItems item;
int numRows = 0;
ds = da.ExecuteSQLStatement(sSQL, ref numRows);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
item = new clsItems();
item.ItemCode = ds.Tables[0].Rows[i][0].ToString();
item.ItemDesc = ds.Tables[0].Rows[i][1].ToString();
item.ItemCost = Convert.ToDouble(ds.Tables[0].Rows[i][2]);
items.Add(item);
}
return(items);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." +
MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// Method for generating the drop down list of equipment
/// </summary>
/// <returns></returns>
public List <clsMusicEquipment> getItems()
{
List <clsMusicEquipment> musicEquipment = new List <clsMusicEquipment>();
try
{
DataSet ds = new DataSet();
int iRet = 0;
ds = db.ExecuteSQLStatement(mainSQL.SelectItemsToBeAddedToInvoice(), ref iRet);
for (int i = 0; i < iRet; i++)
{
Equipment = new Main.clsMusicEquipment();
Equipment.getEqipmentID = ds.Tables[0].Rows[i][0].ToString();
Equipment.getEquipmentName = ds.Tables[0].Rows[i][1].ToString();
Equipment.getEquipmentDescription = ds.Tables[0].Rows[i][2].ToString();
Equipment.getEquipmentCost = ds.Tables[0].Rows[i][3].ToString();
musicEquipment.Add(Equipment);
}
}
catch (Exception ex)
{
MessageBox.Show(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
return(musicEquipment.Cast <clsMusicEquipment>().ToList());
}
/// <summary>
/// This function returns a list of clsItem object
/// </summary>
/// <returns></returns>
public BindingList <clsItem> populateChooseItem()
{
try
{
DataSet ds = new DataSet();
int iRet = 0;
ds = db.ExecuteSQLStatement(SQLQueries.SelectAllItems(), ref iRet);
BindingList <clsItem> ItemList = new BindingList <clsItem>();
for (int i = 0; i < iRet; i++)
{
clsItem Item = new clsItem();
Item.ItemCode = ds.Tables[0].Rows[i][0].ToString();
Item.ItemDesc = ds.Tables[0].Rows[i][1].ToString();
Item.Cost = Convert.ToDecimal(ds.Tables[0].Rows[i][2].ToString());
ItemList.Add(Item);
}
return(ItemList);
}
catch (Exception ex)
{
//Just throw the exception
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." +
MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// runs the method to get the invoice numbers from the database
/// </summary>
/// <returns></returns>
public void getInvoiceNums()
{
try
{
invoiceNums = new ObservableCollection <InvoiceInfo>();
DataSet ds;
int res = 0;
ds = db.ExecuteSQLStatement(searchSQL.getInvoiceNums(), ref res);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
invoiceNums.Add(new InvoiceInfo
{
InvoiceNumber = ds.Tables[0].Rows[i]["InvoiceNum"].ToString()
});
}
}
catch (Exception ex)
{
//Just throw the exception
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." +
MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// Returns a list of all of the invoices in the database
/// </summary>
/// <returns></returns>
public ObservableCollection <clsMainLogic> GetInvoices()
{
ObservableCollection <clsMainLogic> result = new ObservableCollection <clsMainLogic>();
try
{
int iRet = 0;
DataSet ds = dataAccess.ExecuteSQLStatement(sql.SelectAllInvoices(), ref iRet);
for (int i = 0; i < iRet; ++i)
{
clsMainLogic invoice = new clsMainLogic();
invoice.InvoiceNum = Convert.ToInt32(ds.Tables[0].Rows[i][0].ToString());
invoice.InvoiceDate = Convert.ToDateTime(ds.Tables[0].Rows[i][1].ToString());
invoice.TotalCharge = Convert.ToDecimal(ds.Tables[0].Rows[i][2].ToString());
result.Add(invoice);
}
}
catch (Exception ex)
{
//Just throw the exception
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." +
MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
return(result);
}
/// <summary>
/// gets invoices
/// </summary>
/// <returns></returns>
public List <clsLineItems> ListItems()
{
try
{
List <clsLineItems> listItems = new List <clsLineItems>();
int rows = 0;
DataSet rawdata = db.ExecuteSQLStatement(Query.GetItems(), ref rows);
for (int x = 0; x < rows; x++)
{
Double.TryParse(rawdata.Tables[0].Rows[x][2].ToString(), out double total);
listItems.Add(new clsLineItems
{
ItemCode = rawdata.Tables[0].Rows[x][0].ToString(),
ItemDesc = rawdata.Tables[0].Rows[x][1].ToString(),
Cost = total
});
}
return(listItems);
}
catch (Exception e)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + e.Message);
}
}
/// <summary>
/// Gets the invoice based on an invoice ID
/// </summary>
/// <param name="invoiceID">Invoice ID to load</param>
/// <returns>The invoice as a clsInvoiceObj</returns>
public clsInvoiceObj GetInvoice(string invoiceID)
{
try
{
int rowsReturned = 0;
string sql = sqlProvider.GetInvoiceQuery(invoiceID);
clsInvoiceObj invoice;
DataSet ds = da.ExecuteSQLStatement(sql, ref rowsReturned);
if (rowsReturned > 1)
{
throw new Exception("More than one invoice was found for the invoice ID, expected one.");
}
else if (rowsReturned == 0)
{
throw new Exception("No invoice was found for the ID, expected one.");
}
else
{
invoice = new clsInvoiceObj();
invoice.sInvoiceNum = ds.Tables[0].Rows[0]["InvoiceNum"].ToString();
invoice.sInvoiceDate = ds.Tables[0].Rows[0]["InvoiceDate"].ToString();
invoice.sTotalCost = ds.Tables[0].Rows[0]["TotalCost"].ToString();
}
return(invoice);
}
catch (Exception ex)
{
System.IO.File.AppendAllText("C:\\Error.txt", Environment.NewLine +
"HandleError Exception: " + ex.Message);
return(null);
}
}
/// <summary>
/// This is the SQL statement to get all invoices.
/// </summary>
/// <returns>List of invoices</returns>
public List <Invoice> GetAllInvoices()
{
try
{
string sSQL = "SELECT * FROM Items";
int iRet = 0;
DataSet ds = new DataSet();
List <Invoice> invoices = new List <Invoice>();
ds = db.ExecuteSQLStatement(sSQL, ref iRet);
for (int i = 0; i < iRet; i++)
{
Invoice invoiceItem = new Invoice
{
invoiceId = ds.Tables[0].Rows[i][0].ToString(),
invoiceDate = ds.Tables[0].Rows[i][1].ToString(),
invoiceTotal = ds.Tables[0].Rows[i][2].ToString()
};
invoices.Add(invoiceItem);
}
return(invoices);
}
catch (Exception ex)
{
throw new Exception("Unable to get all items." + ex.ToString());
}
}
/// <summary>
/// Query to retrieve all the Items from Database
/// </summary>
/// <returns>A list of all items</returns>
public List <Item> GetAllItems()
{
try
{
List <Item> items = new List <Item>();
//Sql Statement
string Sql = "SELECT * FROM ItemDesc ORDER BY ItemCode";
int retVal = 0;
DS = DataAccess.ExecuteSQLStatement(Sql, ref retVal);
//Loop through DS and add Items
foreach (DataRow dr in DS.Tables[0].Rows)
{
items.Add(new Item(Convert.ToChar(dr[0]), dr[1].ToString(), (float)Convert.ToDouble(dr[2])));
}
return(items);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " --> " + ex.Message);
}
}
/// <summary>
/// Method for getting all Items in ItemsDesc table
/// </summary>
/// <returns>Observable Collection of Item objects</returns>
public ObservableCollection <clsItem> getItems()
{
try
{
string sSQL = clsSQL.GetAllItems();
int iRet = 0;
ds = clsDA.ExecuteSQLStatement(sSQL, ref iRet);
ObservableCollection <clsItem> ItemList = new ObservableCollection <clsItem>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
ItemList.Add(new clsItem {
sItemCode = dr[0].ToString(), sItemDesc = dr[1].ToString(), sCost = dr[2].ToString()
});
}
return(ItemList);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." +
MethodInfo.GetCurrentMethod().Name + " ->" + ex.Message);
}
}
/// <summary>
/// Gets sorted dataset of invoice costs
/// </summary>
/// <returns></returns>
public DataSet GetSortedCosts()
{
try {
int numInvoices = 0;
// get ordered costs
dataSet = dataAccess.ExecuteSQLStatement("SELECT TotalCost FROM Invoices ORDER BY TotalCost", ref numInvoices);
return(dataSet);
} catch (Exception ex) {
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + " " + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// Method to get all items.
/// </summary>
public DataSet getAllItems()
{
try
{
ds = db.ExecuteSQLStatement(clsItemsSQL.GetAllItems(), ref iRet);
}
catch (Exception ex) {
throw new Exception("Unable to get all items. " + ex.ToString());
}
return(ds);
}
/// <summary>
/// deletes an item in the database if that item is not in an invoice
/// </summary>
/// <param name="code">the items code</param>
/// <returns>if something was deleted</returns>
public bool DeleteItem(string code)
{
try
{
int iRet = 0;
DataSet ds = new DataSet();
try
{
ds = data.ExecuteSQLStatement(SQL.InAInvoice(code), ref iRet);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
if (iRet == 0)
{
try
{
int del = data.ExecuteNonQuery(SQL.DeleteItem(code));
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
wnd.ShowError("Deleted");
return(true);
}
else
{
string sError = "This Item is in Invoice ";
bool bFirst = true;
for (int i = 0; i < iRet; i++)
{
if (!bFirst)
{
sError += ", ";
}
sError += ds.Tables[0].Rows[i][0].ToString();
}
wnd.ShowError(sError);
return(false);
}
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// Gets all the invoices in the database
/// Creates an Invoice class object and adds to a list
/// </summary>
/// <returns>List of all the invoices objects in the database</returns>
public static List <Invoice> GetAllInvoices()
{
try
{
int i = 0; // Temp ref integer, unused but required
DataSet ds = _data.ExecuteSQLStatement(clsMainSQL.GetAllInvoices, ref i); // The DataSet of all the invoices
List <Invoice> invoices = new List <Invoice>(); // The list of invoices to be returned
foreach (DataRow row in ds.Tables[0].Rows) // Each row (each individual invoice) in the Invoices table
{
var invoice = new Invoice() // Temp invoice object to be added to the list
{
InvoiceNum = row[0].ToString(),
InvoiceDate = row[1].ToString().Substring(0, row[1].ToString().IndexOf(' ')),
TotalCost = Convert.ToDecimal(row[2]).ToString("c"),
Items = ""
};
DataSet ds1 = _data.ExecuteSQLStatement(clsMainSQL.GetItemsByInvoiceNum(row[0].ToString()), ref i); // The DataSet of all the items associated with an invoice
foreach (DataRow row1 in ds1.Tables[0].Rows) // Each row (each individual item) in the LineItems table
{
invoice.Items += row1[0].ToString() + ", ";
}
invoice.Items = invoice.Items.TrimEnd(' ', ',');
invoices.Add(invoice);
}
return(invoices);
}
catch
{
throw new Exception("Could not get all invoices");
}
}
/// <summary>
/// Runs the provided SQL string and fills the items variable with the results;
/// </summary>
/// <param name="sSQL"></param>
/// <returns></returns>
public List <clsItems> getItems()
{
try
{
DataSet ds = new DataSet();
int iRef = 0;
itemsResult = new List <clsItems>();
var query = sql.SelectItems();
clsItems items;
ds = db.ExecuteSQLStatement(query, ref iRef);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
items = new clsItems();
items.ItemCode = ds.Tables[0].Rows[i][0].ToString();
items.ItemDesc = ds.Tables[0].Rows[i][1].ToString();
items.ItemCost = Convert.ToDouble(ds.Tables[0].Rows[i][2].ToString());
itemsResult.Add(items);
}
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." +
MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
return(itemsResult);
}
/// <summary>
/// Loads the flight information from the database and stores it in the flight dataset
/// </summary>
private void LoadFlights()
{
// SQL Query Statement
string sSQL = "SELECT Flight_ID, Flight_Number + ' - ' + Aircraft_Type AS Flight FROM FLIGHT";
// Execute SQL Query
Flights = db.ExecuteSQLStatement(sSQL, ref iRet);
// Update Flight selection combobox
cbFlight.DisplayMember = "Flight";
// Sets combobox datasource
cbFlight.DataSource = Flights.Tables[0];
// Sets selected index to initial position
cbFlight.SelectedIndex = -1;
}
/// <summary>
/// Select all items from database
/// </summary>
/// <returns>all items</returns>
public List <clsItem> Items()
{
try
{
// Initialize variablse
int iRetVal = 0;
List <clsItem> lstItems = new List <clsItem>();
// SQL query string
string sSQLQuery = clsItemsSQL.SelectAllItems();
// query the database
dsItems = db.ExecuteSQLStatement(sSQLQuery, ref iRetVal);
// temp variables
string sName;
string sDescription;
int iCost;
bool bNumValid;
foreach (DataRow drRow in dsItems.Tables[0].Rows)
{
sName = drRow.ItemArray[0].ToString();
sDescription = drRow.ItemArray[1].ToString();
iCost = 0;
bNumValid = Int32.TryParse(drRow.ItemArray[2].ToString(), out iCost);
// if parsing of string to int valid
if (bNumValid)
{
lstItems.Add(new clsItem {
sItemName = sName, sItemDesc = sDescription, iItemCost = iCost
});
}
}
// comparer object for sorting the item code
clsItemComparer itemComparer = new clsItemComparer();
lstItems.Sort(itemComparer);
// return the items list
return(lstItems);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." +
MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// get our invoice IDS for our Search Window combo-box
/// </summary>
/// <returns>items</returns>
public List <Invoice> getInvoices()
{
try
{
DataSet ds;
int iRef = 0;
//SQL Query to extract the Invoice ID from the INvoices table
string query = "SELECT InvoiceNum, InvoiceDate, TotalCost FROM Invoices";
ds = db.ExecuteSQLStatement(query, ref iRef);
//iterate through all the rows
for (int i = 0; i < iRef; i++)
{
//get the invoice ID as a string
string sinvoiceID = ds.Tables[0].Rows[i]["InvoiceNum"].ToString();
//get the invoice date as a string
string sinvoiceDate = ds.Tables[0].Rows[i]["InvoiceDate"].ToString();
//get the invoice cost as a string
string sinvoiceCost = (ds.Tables[0].Rows[i]["TotalCost"]).ToString();
//conver the invoice ID to an int
int invoiceID = Convert.ToInt32(sinvoiceID);
//convert the total cost to a double
double totalInvoiceCost = Convert.ToDouble(sinvoiceCost);
//create an Invoice object
//Invoice inv = new Invoice(invoiceID, invoiceDate, totalCost);
//add to our invoiceID list
InvoiceList.Add(new Invoice {
InvoiceNumber = invoiceID, InvoiceDate = sinvoiceDate, TotalCost = totalInvoiceCost
});
}
//return our list
return(InvoiceList);
}
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);
}
}
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>
/// List to get GetLineItems() data
/// </summary>
/// <returns></returns>
public List <clsLineItems> GetLineItems(int n)
{
List <clsLineItems> lstLineItems = new List <clsLineItems>();
db = new clsDataAccess();
ds = db.ExecuteSQLStatement(MainSQL.LoadLineItems(n), ref iRet);
for (int i = 0; i < iRet; i++)
{
lstLineItems.Add(new clsLineItems
{
sInvoiceNum = ds.Tables[0].Rows[i]["InvoiceNum"].ToString(),
sLineItemNum = ds.Tables[0].Rows[i]["LineItemNum"].ToString(),
sItemCode = ds.Tables[0].Rows[i]["ItemCode"].ToString()
});
/*
* foreach (DataRow dr in ds.Tables[0].Rows)
* {
* another way to loop
* }
*/
}
return(lstLineItems);
}
/// <summary>
/// Get invoices function builds a query string and executes it and returns a list of invoices.
/// </summary>
/// <param name="dateTime"></param>
/// <param name="totalCharges"></param>
/// <param name="invoiceId"></param>
/// <returns></returns>
public List <clsInvoice> GetInvoices(DateTime?dateTime, double?totalCharges, int?invoiceId)
{
try
{
string queryString = clsSearchSQL.GetInvoices(dateTime, totalCharges, invoiceId);
int numReturned = 0;
DataSet invoiceDataSet = clsDataAccess.ExecuteSQLStatement(queryString, ref numReturned);
List <clsInvoice> clsInvoicesList = new List <clsInvoice>();
for (int i = 0; i < numReturned; i++)
{
var invoice = new clsInvoice();
invoice.Id = int.Parse(invoiceDataSet.Tables[0].Rows[i][0].ToString());
invoice.InvoiceDate = (DateTime)invoiceDataSet.Tables[0].Rows[i][1];
invoice.TotalCost = double.Parse(invoiceDataSet.Tables[0].Rows[i][2].ToString());
clsInvoicesList.Add(invoice);
}
return(clsInvoicesList);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// method to get a List of Items from the database
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public List <clsItem> GetItems(clsDataAccess db)
{
try
{
ItemsList = new List <clsItem>();
sSQL = "select ItemCode, ItemDesc, Cost from ItemDesc";
ds = db.ExecuteSQLStatement(sSQL, ref iRetVal);
for (int i = 0; i < iRetVal; i++)
{
clsItem Item = new clsItem();
Item.Code = (string)ds.Tables[0].Rows[i][0];
Item.Description = (string)ds.Tables[0].Rows[i][1];
Item.Cost = float.Parse(ds.Tables[0].Rows[i][2].ToString());
ItemsList.Add(Item);
}
return(ItemsList);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." +
MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// Load All the Equipment
/// </summary>
public void getTable()
{
try
{
DataSet ds;
string sSQL;
sSQL = "Select * FROM EQUIPMENT";
ds = db.ExecuteSQLStatement(sSQL, ref iReturn_Value);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// Get list of LineItems
/// </summary>
/// <returns></returns>
internal List <Item> GetLineItems()
{
List <Item> items = new List <Item>();
//try
//{
DataSet ds = new DataSet();
string sSQL = String.Format(clsMainSQL.GetLineItems);
int iRet = 0;
clsData = new clsDataAccess();
ds = clsData.ExecuteSQLStatement(sSQL, ref iRet);
for (int i = 0; i < iRet; i++)
{
items.Add(new Item()
{
Code = ds.Tables[0].Rows[i][0].ToString(),
Description = ds.Tables[0].Rows[i][1].ToString(),
Cost = int.Parse(ds.Tables[0].Rows[i][2].ToString())
});
}
//}
//catch (Exception ex)
//{
// throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
//}
return(items);
}
/// <summary>
/// Method to check if an item is contained in any invoice
/// </summary>
/// <param name="db"></param>
/// <param name="code"></param>
/// <returns></returns>
public string ItemInvoice(clsDataAccess db, string code)
{
try
{
string sInvoices = "";
sSQL = "select distinct(InvoiceNum) from LineItems where ItemCode = '" + code + "'";
ds = db.ExecuteSQLStatement(sSQL, ref iRetVal);
for (int i = 0; i < iRetVal; i++)
{
sInvoices += string.Concat(ds.Tables[0].Rows[i][0].ToString());
if ((i + 1) != iRetVal)
{
sInvoices = string.Concat(", ");
}
}
return(sInvoices);
}
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>
/// 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);
}
}