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