/// <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);
}
}
/// <summary>
/// This method populates the drops on the right side of the Search Form
/// This needs to be separate from UpdateList because UpdateList has
/// limiters, where these always show all items.
/// </summary>
public void PopulateDrops()
{
string sSQL; //Holds an SQL statement
int iRet = 0; //Number of return values
DataSet ds = new DataSet();
clsInvoice invoice;
sSQL = "SELECT DISTINCT InvoiceNum, InvoiceDate, TotalCharge " +
"FROM Invoices";
ds = db.ExecuteSQLStatement(sSQL, ref iRet);
//update dropboxes
for (int i = 0; i < iRet; i++)
{
invoice = new clsInvoice();
//Invoice #
invoice.InvoiceNum = ds.Tables[0].Rows[i][0].ToString();
//Date
invoice.InvoiceDate = ds.Tables[0].Rows[i]["InvoiceDate"].ToString();
//Charge
invoice.TotalCharge = ds.Tables[0].Rows[i]["TotalCharge"].ToString();
drpInvoice.Items.Add(invoice.InvoiceNum);
drpDate.Items.Add(invoice.InvoiceDate);
drpCharge.Items.Add(invoice.TotalCharge);
}
}
/// <summary>
/// Recieves the invoice that was selected in the search window and fills out the window with the invoice information
/// </summary>
/// <param name="invoice"></param>
public void insertSelectedInvoice(clsInvoice invoice)
{
try
{
lblInvoiceNumber.Visibility = Visibility.Visible;
txtInvoice.Visibility = Visibility.Visible;
txtInvoice.Text = invoice.InvoiceNum;
txtDate.Text = invoice.InvoiceDate.ToString();
txtCost.Text = invoice.TotalCharge;
selectItem.Items.Clear();
selectItem.IsEnabled = false;
cmdAdd.IsEnabled = false;
cmdDeleteItem.IsEnabled = false;
cmdSave.IsEnabled = false;
cmdEdit.IsEnabled = true;
cmdDelete.IsEnabled = true;
listItems.Items.Clear();
ObservableCollection <clsItem> col_Items = new ObservableCollection <clsItem>();
col_Items = SQLStatements.invoiceItems(txtInvoice.Text);
foreach (clsItem item in col_Items)
{
listItems.Items.Add(item);
}
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// This button will take the selected Invoice and send it back to the main menu to
/// open up for edit
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cmdSelectInvoice_Click(object sender, RoutedEventArgs e)
{
try
{
if (listInvoice.SelectedItem != null)
{
clsInvoice selectedInvoice = (clsInvoice)listInvoice.SelectedItem;
wnd_mainWindow.insertSelectedInvoice(selectedInvoice);
this.Close();
}
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// Recieves the invoice that was selected in the search window and fills out the window with the invoice information
/// </summary>
/// <param name="invoice"></param>
public void insertSelectedInvoice(clsInvoice invoice)
{
try
{
lblInvoiceNumber.Visibility = Visibility.Visible;
txtInvoice.Visibility = Visibility.Visible;
txtInvoice.Text = invoice.InvoiceNum;
txtDate.Text = invoice.InvoiceDate.ToString();
txtCost.Text = invoice.TotalCharge;
selectItem.Items.Clear();
selectItem.IsEnabled = false;
cmdAdd.IsEnabled = false;
cmdDeleteItem.IsEnabled = false;
cmdSave.IsEnabled = false;
cmdEdit.IsEnabled = true;
cmdDelete.IsEnabled = true;
listItems.Items.Clear();
ObservableCollection<clsItem> col_Items = new ObservableCollection<clsItem>();
col_Items = SQLStatements.invoiceItems(txtInvoice.Text);
foreach (clsItem item in col_Items)
{
listItems.Items.Add(item);
}
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// This method Updates the list on the left side of the form, showing a list of all invoices based on the selections
/// on the right side dropboxes.
/// </summary>
/// <param name="Date">Date</param>
/// <param name="Charge">TotalCharge</param>
/// <param name="InvoiceNum">Invoice Number</param>
public void UpdateList(string Date, string Charge, string InvoiceNum)
{
string sSQL; //Holds an SQL statement
int iRet = 0; //Number of return values
DataSet ds = new DataSet();
clsInvoice invoice;
//Empties the list so that duplicates are not shown
listInvoice.Items.Clear();
//If the form is just opened, the defaults, show all invoices
if (drpDate.SelectedIndex == -1 && drpCharge.SelectedIndex == -1 && drpInvoice.SelectedIndex == -1)
{
sSQL = "SELECT InvoiceNum, InvoiceDate, TotalCharge " +
"FROM Invoices";
ds = db.ExecuteSQLStatement(sSQL, ref iRet);
}
//If only the date is not empty
else if (Date != "" && Charge == "" && InvoiceNum == "")
{
sSQL = "SELECT InvoiceNum, InvoiceDate, TotalCharge " +
"FROM Invoices " +
"WHERE InvoiceDate = #" + Date + "#";
ds = db.ExecuteSQLStatement(sSQL, ref iRet);
}
//if only the charge
else if (Charge != "" && Date == "" && InvoiceNum == "")
{
sSQL = "SELECT InvoiceNum, InvoiceDate, TotalCharge " +
"FROM Invoices " +
"WHERE TotalCharge =" + Charge;
ds = db.ExecuteSQLStatement(sSQL, ref iRet);
}
//if only the invoicenum
else if (InvoiceNum != "" && Charge == "" && Date == "")
{
sSQL = "SELECT InvoiceNum, InvoiceDate, TotalCharge " +
"FROM Invoices " +
"WHERE InvoiceNum =" + InvoiceNum;
ds = db.ExecuteSQLStatement(sSQL, ref iRet);
}
//if the invoicenum and date
else if (InvoiceNum != "" && Date != "" && Charge == "")
{
sSQL = "SELECT InvoiceNum, InvoiceDate, TotalCharge " +
"FROM Invoices " +
"WHERE InvoiceDate = #" + Date + "#" +
" AND InvoiceNum =" + InvoiceNum;
ds = db.ExecuteSQLStatement(sSQL, ref iRet);
}
//if the charge and date
else if (Charge != "" && Date != "" && InvoiceNum == "")
{
sSQL = "SELECT InvoiceNum, InvoiceDate, TotalCharge " +
"FROM Invoices " +
"WHERE InvoiceDate = #" + Date + "#" +
" AND TotalCharge =" + Charge;
ds = db.ExecuteSQLStatement(sSQL, ref iRet);
}
//if the invoicenum and charge
else if (InvoiceNum != "" && Charge != "" && Date == "")
{
sSQL = "SELECT InvoiceNum, InvoiceDate, TotalCharge " +
"FROM Invoices " +
"WHERE TotalCharge =" + Charge +
" AND InvoiceNum =" + InvoiceNum;
ds = db.ExecuteSQLStatement(sSQL, ref iRet);
}
//if everything
else if (InvoiceNum != "" && Date != "" && Charge != "")
{
sSQL = "SELECT InvoiceNum, InvoiceDate, TotalCharge " +
"FROM Invoices " +
"WHERE InvoiceDate = #" + Date + "#" +
" AND TotalCharge =" + Charge +
" AND InvoiceNum =" + InvoiceNum;
ds = db.ExecuteSQLStatement(sSQL, ref iRet);
}
//insurance
else
{
sSQL = "SELECT InvoiceNum, InvoiceDate, TotalCharge " +
"FROM Invoices";
ds = db.ExecuteSQLStatement(sSQL, ref iRet);
}
//update the table
for (int i = 0; i < iRet; i++)
{
invoice = new clsInvoice();
//date
invoice.InvoiceNum = ds.Tables[0].Rows[i][0].ToString();
//invoice #
invoice.InvoiceDate = ds.Tables[0].Rows[i]["InvoiceDate"].ToString();
//charge
invoice.TotalCharge = ds.Tables[0].Rows[i]["TotalCharge"].ToString();
listInvoice.Items.Add(invoice);
}
//Reset the drop boxes so that a new search can be completed without closing the window
drpDate.SelectedIndex = -1;
drpCharge.SelectedIndex = -1;
drpInvoice.SelectedIndex = -1;
}
