/// <summary>
/// Get all invoices and put them in the datagrid
/// </summary>
private void FillInvoiceDataGrid()
{
try
{
int iRet = 0;
ds = db.ExecuteSQLStatement(sql.SelectAllInvoices(), ref iRet);
dgInvoices.ItemsSource = new DataView(ds.Tables[0]);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// gets all invoices from the database
/// </summary>
/// <returns></returns>
public DataTable GetAllInvoices()
{
try
{
dt = _dataAccess.ExecuteSQLStatement(SQL_GET_INVOICES, ref rowCountThatNoOneCaresAbout).Tables[0];
return(dt);
}
catch (Exception selectException)
{
throw new InvoiceDataException("Unable to retrieve invoices from database.", selectException);
}
}
/// <summary>
/// This method executes the SQL statement that returns all items in a data set.
/// </summary>
/// <param name="sqlStatement"></param>
/// <returns></returns>
public DataSet getAllItems(String sqlStatement)
{
try
{
return(db.ExecuteSQLStatement(sqlStatement, ref iRet));
}
catch (Exception e)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + e.Message);
}
}
} //end populateInventory()
/// <summary>
/// If there is an invoice number, get the data from the database and populate the datatable and dataset.
/// If there is not in invoice given, set up the table to be able to accept inventory items.
/// </summary>
/// <param name="invoiceId"></param>
public void populateInvoice(String invoiceId)
{
///try catch block to handle exceptions
try
{
///if statement to check invoice ID and populate invoice
if (invoiceId != "")
{
///sets delete invoice to enabled
btnDeleteInvoice.IsEnabled = true;
///sets label with number
lblInvoiceNumber.Content = invoiceId;
///creates a string to hold invoice id info
String sQuery = mydb.SelectItemsOnInvoice(invoiceId);
///executes query
dtInvoice = db.FillSqlDataTable(sQuery);
///populates grid
dgInvoiceItems.ItemsSource = dtInvoice.DefaultView;
///creates a return variable
int iRet = 0;
///creates a data set
DataSet ds = new DataSet();
///calls a sql method
String sSQL = mydb.SelectInvoiceDateFromNum(invoiceId);
///places info in data set
ds = db.ExecuteSQLStatement(sSQL, ref iRet);
///creates temp variable to hold info
String date = ds.Tables[0].Rows[0][0].ToString();
///takes care of the date format
invoiceDatePicker.SelectedDate = DateTime.Parse(date);
//pulls data from database
//UPDATE INVOICE LABEL WITH INVOICEID - lblInvoiceId
}
else
{
///if all the above fails, then do this
btnDeleteInvoice.IsEnabled = false;
//fields should be clear and ready for input.
dtInvoice.Columns.Add("ItemDesc");
dtInvoice.Columns.Add("Cost");
///populatse invoice items
dgInvoiceItems.ItemsSource = dtInvoice.DefaultView;
}//end else
}
catch (Exception)
{///catch exceptions
MessageBox.Show(MethodInfo.GetCurrentMethod().DeclaringType.Name);
}//end catch
}//end populateInvoice()
/// <summary>
/// Method that resets the datagrid to reflect all values in the database.
/// </summary>
/// <returns></returns>
public DataSet ResetDataGrid()
{
try
{
//creates a dataset, queries all values from the database, fills the dataset with the results then returns the dataset.
getData = new clsDataAccess();
DataSet resetGrid = new DataSet();
string sSQL = "SELECT * FROM Invoices";
int iRet = 0;
resetGrid = getData.ExecuteSQLStatement(sSQL, ref iRet);
return(resetGrid);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " ->" + ex.Message);
}
}
/// <summary>
/// Gets the total count of Invoice charge amounts from the database to populate upon loading of the window.
/// </summary>
/// <returns></returns>
public int getChargeAmounts()
{
try
{
//creates a dataset, runs a query for all invoice charge amounts, then returns the count of the total number of invoice charge amounts.
getData = new clsDataAccess();
DataSet amountInvoice = new DataSet();
int num = 0;
string sSQL = "SELECT DISTINCT TotalCharge FROM Invoices";
amountInvoice = getData.ExecuteSQLStatement(sSQL, ref num);
return(num);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " ->" + ex.Message);
}
}
/// <summary>
/// Internal method for loading drop-down menu for editing an existing invoice.
/// </summary>
private void LoadInvoiceComBx()
{
try
{
int iRet = 0;
ds = db.ExecuteSQLStatement(sql.SelectAllInvoices(), ref iRet);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
InvoiceNumberComBx.Items.Add(ds.Tables[0].Rows[i][0].ToString());
}
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// Gets the currently selected invoice date to populate in the combo box when the window first opens.
/// </summary>
/// <param name="invoiceNum"></param>
/// <returns></returns>
public string GetDate(int DateNum)
{
try
{
//creates a dataset and a SQL query, then queries for the currently selected invoice date, then returns it to the window.
getData = new clsDataAccess();
DataSet GetDateDB = new DataSet();
int iRet = 0;
string sSQL = "SELECT DISTINCT Format(InvoiceDate, 'mm/dd/yyyy') FROM Invoices";
GetDateDB = getData.ExecuteSQLStatement(sSQL, ref iRet);
string DateResult = GetDateDB.Tables[0].Rows[DateNum][0].ToString();
return(DateResult);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " ->" + ex.Message);
}
}
/// <summary>
/// Gets the currently selected invoice charge amount to populate in the combo box when the window first opens.
/// </summary>
/// <param name="invoiceNum"></param>
/// <returns></returns>
public double GetCharge(int ChargeNum)
{
try
{
//creates a dataset and a SQL query, then queries for the currently selected invoice date, parses it to a double, then returns it to the window.
getData = new clsDataAccess();
DataSet GetChargeDB = new DataSet();
double charge = 0.0;
int iRet = 0;
string sSQL = "SELECT DISTINCT TotalCharge FROM Invoices ";
GetChargeDB = getData.ExecuteSQLStatement(sSQL, ref iRet);
string result = GetChargeDB.Tables[0].Rows[ChargeNum][0].ToString();
Double.TryParse(result, out charge);
return(charge);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " ->" + ex.Message);
}
}
/// <summary>
/// Gets the currently selected invoice number to populate in the combo box when the window first opens.
/// </summary>
/// <param name="invoiceNum"></param>
/// <returns></returns>
public int GetInvoice(int invoiceNum)
{
try
{
//creates a dataset and a SQL query, then queries for the currently selected invoice number, parses it, then returns it to the window.
getData = new clsDataAccess();
DataSet GetInvoiceDB = new DataSet();
int num = 0;
int Intresult = 0;
string sSQL = "SELECT InvoiceNum FROM Invoices";
GetInvoiceDB = getData.ExecuteSQLStatement(sSQL, ref num);
string result = GetInvoiceDB.Tables[0].Rows[invoiceNum][0].ToString();
Int32.TryParse(result, out Intresult);
return(Intresult);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " ->" + ex.Message);
}
}
/// <summary>
/// Delete an item from the inventory.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnDeleteItem_Click(object sender, RoutedEventArgs e)
{
try
{
// Prevent user from deleting an item that is in the current invoice.
// Display a warning message to the user.
// Delete item from database using SQL.
lblErrorCantDeleteItem.Visibility = Visibility.Hidden;
sSQL = mydb.CheckIfItemIsInAnInvoice(itemCode);
ds = db.ExecuteSQLStatement(sSQL, ref iRetVal);
if(iRetVal == 0)
{
///check to see what the message box is showing
if (MessageBox.Show("Are you sure you want to delete item: " + txtItemDesc.Text + "?", "Delete item?", MessageBoxButton.YesNo, MessageBoxImage.Warning) == MessageBoxResult.No)
{
//do no stuff
}
else
{
sSQL = mydb.DeleteInventoryItem(itemCode);
db.ExecuteNonQuery(sSQL);
EditWindow ew = new EditWindow();
ew.Show();
this.Close();
}
}
else
{
lblErrorCantDeleteItem.Visibility = Visibility.Visible;
}
}
catch (Exception)
{
MessageBox.Show(MethodInfo.GetCurrentMethod().DeclaringType.Name);
}
}
public SearchWindow()
{
InitializeComponent();
//automatically load drop down menu for Invoice ID
try
{
//Create a DataSet to hold the data
DataSet ds;
//Number of return values
int iRet = 0;
//Get all the values from the Invoice ID table
ds = db.ExecuteSQLStatement("SELECT InvoiceNum FROM Invoices", ref iRet);
//Loop through all the values returned
for (int i = 0; i < iRet; i++)
{
//Add InvoiceID's to the dropdown box
invoiceIDComboBox.Items.Add(ds.Tables[0].Rows[i][0].ToString());
}
}
catch (Exception)
{
MessageBox.Show(MethodInfo.GetCurrentMethod().DeclaringType.Name);
}
///automatically load drop down menu for Invoice Date
try
{
///Create a DataSet to hold the data
DataSet ds;
///Number of return values
int iRet = 0;
///Get all the values from the Invoice Date table
ds = db.ExecuteSQLStatement("SELECT InvoiceDate FROM Invoices", ref iRet);
///Loop through all the values returned
for (int i = 0; i < iRet; i++)
{
///Add Invoice Dates to the dropdown box
invoiceDateComboBox.Items.Add(ds.Tables[0].Rows[i][0]).ToString();
}
}
catch (Exception)
{
MessageBox.Show(MethodInfo.GetCurrentMethod().DeclaringType.Name);
}
///automatically load drop down menu for Invoice Total Charge
try
{
//Create a DataSet to hold the Total Charge
DataSet ds;
//Number of return values
int iRet = 0;
//Get all the values from the Invoice Total Charge
ds = db.ExecuteSQLStatement("SELECT TotalCharge FROM Invoices", ref iRet);
//Loop through all the values returned
for (int i = 0; i < iRet; i++)
{
//Add Invoice total Charge to the dropdown box
invoiceAmountComboBox.Items.Add(ds.Tables[0].Rows[i][0].ToString());
}
}
catch (Exception)
{
MessageBox.Show(MethodInfo.GetCurrentMethod().DeclaringType.Name);
}
}
/// <summary>
/// Updates the data grid based on the combo box selections made by the user.
/// </summary>
/// <param name="invoice"></param>
/// <param name="date"></param>
/// <param name="charge"></param>
/// <returns></returns>
public DataSet UpdateDataGrid(string invoice, string date, string charge)
{
try
{
//creates a dataset, a blank SQL string, then checks what conditions the combo boxes are in. Based on the combo box conditions it will select the appropriate query with the data selected by the user.
getData = new clsDataAccess();
DataSet UpdatedGrid = new DataSet();
string sSQL = "";
int iRet = 0;
if (invoice == null || date == null || charge == null)
{
sSQL = "SELECT * FROM Invoices";
}
else if (invoice != "" && date == "" && charge == "")
{
sSQL = "SELECT * FROM Invoices WHERE InvoiceNum = " + invoice;
}
else if (invoice == "" && date != "" && charge == "")
{
sSQL = "SELECT * FROM Invoices WHERE InvoiceDate = #" + date + "#";
}
else if (invoice == "" && date == "" && charge != "")
{
sSQL = "SELECT * FROM Invoices WHERE TotalCharge = " + charge;
}
else if (invoice != "" && date != "" && charge == "")
{
sSQL = "SELECT * FROM Invoices WHERE InvoiceNum = " + invoice + " AND InvoiceDate = #" + date + "#";
}
else if (invoice == "" && date != "" && charge != "")
{
sSQL = "SELECT * FROM Invoices WHERE InvoiceDate = #" + date + "#" + " AND TotalCharge =" + charge;
}
else if (invoice != "" && date == "" && charge != "")
{
sSQL = "SELECT * FROM Invoices WHERE InvoiceNum = " + invoice + " AND TotalCharge = " + charge;
}
else if (invoice != "" && date != "" && charge != "")
{
sSQL = "SELECT * FROM Invoices WHERE InvoiceNum = " + invoice + " AND InvoiceDate = #" + date + "#" + " AND TotalCharge = " + charge;
}
else if (invoice == "" && date == "" && charge == "")
{
sSQL = "SELECT * FROM Invoices";
}
//runs query then returns result.
UpdatedGrid = getData.ExecuteSQLStatement(sSQL, ref iRet);
return(UpdatedGrid);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " ->" + ex.Message);
}
}