/// <summary>
/// Adds the item to the invoice
/// </summary>
/// <param name="itemID">ID of the item to add</param>
public void AddItemToInvoice(string invoiceNum, string itemID)
{
try
{
int iRet = 0;
DataSet ds = ExecuteSQLStatement(ClsQuery.getNextLineNum(invoiceNum), ref iRet);
string nextLine = ds.Tables[0].Rows[0].ItemArray[0].ToString();
int lineNum = 1;
Int32.TryParse(nextLine, out lineNum);
//Add line item
//If there are no line items set it to the first line
if (nextLine.Equals(""))
{
nextLine = "1";
}
lineNum++;
nextLine = lineNum.ToString();
ExecuteNonQuery(ClsQuery.insertLineItems(invoiceNum, nextLine, itemID));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// SQL Executer with return values
/// </summary>
/// <param name="sSQL"></param>
/// <param name="iRetVal"></param>
/// <returns></returns>
private DataSet ExecuteSQLStatement(string sSQL, ref int iRetVal)
{
try
{
DataSet ds = new DataSet();
using (OleDbConnection conn = new OleDbConnection(ClsQuery.getConnString()))
{
using (OleDbDataAdapter adapter = new OleDbDataAdapter())
{
//Open Connection
conn.Open();
adapter.SelectCommand = new OleDbCommand(sSQL, conn);
adapter.SelectCommand.CommandTimeout = 0;
//Fill dataset
adapter.Fill(ds);
}
}
iRetVal = ds.Tables[0].Rows.Count;
return(ds);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// Initialize window and constructor for clsquery
/// </summary>
public InventoryWindow()
{
InitializeComponent();
invQueryManager = new ClsQuery();
inVoManager = new InvoiceManager();
inManager = new InvoiceManager();
}
/// <summary>
/// Retreives an item from the database with the
/// item ID provided
/// </summary>
/// <param name="itemID">ID of item to retrieve</param>
/// <returns>returns an item as a dataset structure</returns>
public string[] RetrieveItem(string itemID)
{
try
{
//Make a new list
string[] itemDetails = new string[3];
int iRet = 0;
DataSet ds = ExecuteSQLStatement(ClsQuery.getItem(itemID), ref iRet);
foreach (DataRow dr in ds.Tables[0].Rows)
{
//Item Code
itemDetails[0] = dr[0].ToString();
//Item Desc
itemDetails[1] = dr[1].ToString();
//Item Price
itemDetails[2] = dr[2].ToString();
}
return(itemDetails);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// Removes the item from the invoice
/// </summary>
/// <param name="itemID">ID of the item to remove</param>
public void DeleteItemFromInvoice(string invoiceNum, string lineItemNum)
{
try
{
ExecuteNonQuery(ClsQuery.deleteInvoiceItem(invoiceNum, lineItemNum));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// Updates the item in the database
/// </summary>
/// <param name="invoiceNum"></param>
/// <param name="itemID"></param>
/// <param name="lineNum"></param>
public void updateItemInDB(string invoiceNum, string itemID, string lineNum)
{
try
{
ExecuteNonQuery(ClsQuery.updateInvoiceItem(invoiceNum, itemID, lineNum));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// Takes in an invoice in the form of a dataset and saves it
/// to the database
/// </summary>
/// <param name="invoiceDataSet">The invoice in a dataset structure</param>
public void SaveInvoice(string date, string total)
{
try
{
ExecuteNonQuery(ClsQuery.insertInvoice(date, total));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// Deletes the invoice with the corresponding invoice ID
/// from the database.
/// </summary>
/// <param name="invoiceNum">The invoice ID</param>
/// <returns></returns>
public void DeleteInvoice(string invoiceNum)
{
try
{
//Delete invoice line items
ExecuteNonQuery(ClsQuery.deleteAllInvoiceItems(invoiceNum));
//Delete Invoice
ExecuteNonQuery(ClsQuery.deleteInvoice(invoiceNum));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// Retreives an invoice from the database with the
/// ID provided
/// </summary>
/// <param name="invoiceNum">ID of invoice to retrieve</param>
/// <returns>Returns an invoice as a dataset structure</returns>
public List <String> RetrieveInvoice(string invoiceNum, ref List <String> invoiceData)
{
try
{
//Clear List
invoiceData.Clear();
int iRet = 0;
double total = 0;
//Items list
List <String> invoiceItems = new List <String>();
//Get Invoice
DataSet ds = ExecuteSQLStatement(ClsQuery.getInvoice("5001"), ref iRet);
foreach (DataRow dr in ds.Tables[0].Rows)
{
//Add invoice date
invoiceData.Add(dr[1].ToString());
//Add invoice total
invoiceData.Add(dr[2].ToString());
}
//Get Invoice Items
ds = ExecuteSQLStatement(ClsQuery.getInvoiceItems(invoiceNum), ref iRet);
foreach (DataRow dr in ds.Tables[0].Rows)
{
//Add price
int stringNum;
Int32.TryParse(dr[2].ToString(), out stringNum);
total += stringNum;
//Add item to list
invoiceItems.Add(string.Format("{0} - {1} - ${2}", dr[0], dr[1], dr[2]));
}
invoiceData.Add(total.ToString());
//Return invoice as dataset
return(invoiceItems);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// Connects to the database and calculates the next invoice ID then
/// returns it.
/// </summary>
/// <returns>Returns a new invoice ID as a string</returns>
public string GenerateInvoiceID()
{
try
{
int iRet = 0;
int mID = 0;
//Get Invoice Max ID from data
DataSet ds = ExecuteSQLStatement(ClsQuery.generateInvoiceID(), ref iRet);
Int32.TryParse(ds.Tables[0].Rows[0].ItemArray[0].ToString(), out mID);
string newID = (mID).ToString();
//return ID as string
return(newID);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// SQL executer with no expected return values
/// </summary>
/// <param name="sSQL"></param>
/// <returns></returns>
private int ExecuteNonQuery(string sSQL)
{
try
{
int iNumRows;
using (OleDbConnection conn = new OleDbConnection(ClsQuery.getConnString()))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand(sSQL, conn);
cmd.CommandTimeout = 0;
iNumRows = cmd.ExecuteNonQuery();
}
return(iNumRows);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// Connects to the database and generates a list of
/// items from the Inventory Table.
/// </summary>
/// <returns>List of Items from Inventory Table</returns>
public List <String> populateItemList()
{
try
{
//Make a new list
List <String> items = new List <String>();
int iRet = 0;
//Send query
DataSet ds = ExecuteSQLStatement(ClsQuery.getAllFromItemsDesc(), ref iRet);
//Populate list
foreach (DataRow dr in ds.Tables[0].Rows)
{
items.Add(dr[0].ToString());
}
//Return list
return(items);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// Fills labels and shows data for the item code selected.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cm_Item_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
try
{
//Make a new list
List <String> items = new List <String>();
int iRet = 0;
//Send query
DataSet ds = inVoManager.ExecuteSQLStatement(ClsQuery.getAllFromItemsDesc(), ref iRet);
//Populate list
foreach (DataRow dr in ds.Tables[0].Rows)
{
items.Add(dr[0].ToString());
}
//Return list
cm_Item.ItemsSource = items;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// Retrieves a list of Total Filters
/// </summary>
/// <returns></returns>
public List <string> filterTotalOptions()
{
try
{
//Create DataSet to hold query data
List <string> InvoiceTotal = new List <string>();
int iRet = 0;
DataSet ds = ExecuteSQLStatement(ClsQuery.getTotalOptions(), ref iRet);
//Get query results
foreach (DataRow dr in ds.Tables[0].Rows)
{
InvoiceTotal.Add(dr[0].ToString());
}
//return query results
return(InvoiceTotal);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}