/// <summary>
/// Handling for updating items in database
/// </summary>
/// <param name="itemCode"></param>
/// <param name="Title"></param>
/// <param name="Author"></param>
/// <param name="Price"></param>
public void setItem(string itemCode, string Title, string Author, double Price)
{
if (!(Title.Length > 0) || !(Author.Length > 0) || (Price <= 0))
{
throw new Exception("Incorrect. Please recheck input values");
}
dataAccess.ExecuteScalarSQL("UPDATE [" + tableName + "] SET Title = '" + Title +
"', Author = '" + Author + "', Price = " + Price + " WHERE ItemCode = '" + itemCode + "'");
getNewData();
}
public string GetBookAuth(string BookSelected)
{
db = new clsDataAccess();
string sSQLBook_Name = "SELECT BooK_Author FROM Books WHERE Book_Name = \"" + BookSelected + "\";";
int max = Convert.ToInt32(db.ExecuteScalarSQL("SELECT MAX(ID) FROM Books;"));
DataSet ds;
string Book_author = db.ExecuteScalarSQL(sSQLBook_Name);
return(Book_author);
}
/// <summary>
/// Creates a new invoice in Invoices table.
/// </summary>
/// <param name="i"></param>
public void CreateNew(ref int invNum, InvoiceCls i)
{
//else, insert the Invoice info and then find the highest number
data.ExecuteNonQuery("INSERT INTO Invoices(InvoiceDate, TotalAmount) VALUES('" +
i.date + "', " + i.totalCost + ")");
string highest = data.ExecuteScalarSQL("SELECT TOP 1 InvoiceNumber FROM" +
" Invoices ORDER BY InvoiceNumber DESC");
//And set invNum to it
highest = Int32.Parse(highest).ToString();
//Console.WriteLine(highest + " is highest");
i.invNum = Int32.Parse(highest);
}
/// <summary>
/// Deletes an items by the item code
/// </summary>
/// <param name="itemCode">This is the item code the user wants to delete</param>
public static void DeleteByItemCode(string itemCode)
{
try
{
int i = 0;
DataSet ds = _data.ExecuteSQLStatement(clsItemsSQL.GetLineItemsByItemCode(itemCode), ref i);
if (ds.Tables[0].Rows.Count != 0)
{
var codes = "";
foreach (DataRow row in ds.Tables[0].Rows)
{
codes += row.ItemArray[0] + ", ";
}
codes = codes.Trim(new char[] { ' ', ',' });
MessageBox.Show("Cannot delete an item that is in an invoice! Item in invoice(s): " + codes);
return;
}
_data.ExecuteScalarSQL(clsItemsSQL.DeleteItemsByItemCode(itemCode));
}
catch
{
throw new Exception("Could not delete item " + itemCode);
}
}
public List <clsBook> GetBookName()
{
Books = new List <clsBook>();
int value = 0;
db = new clsDataAccess();
string sSQLBook_Name = "SELECT * FROM Books;";
int max = Convert.ToInt32(db.ExecuteScalarSQL("SELECT MAX(ID) FROM Books;"));
DataSet ds;
ds = db.ExecuteSQLStatement(sSQLBook_Name, ref value);
for (int i = 0; i < max; i++)
{
Book = new clsBook();
Book.sID = ds.Tables[0].Rows[i][0].ToString();
Book.sBookName = ds.Tables[0].Rows[i]["Book_Name"].ToString();
Book.sBookAuthor = ds.Tables[0].Rows[i]["Book_Author"].ToString();
Book.dPrice = Convert.ToDouble(ds.Tables[0].Rows[i]["Book_Price"].ToString());
Book.sGenre = ds.Tables[0].Rows[i]["Book_Genre"].ToString();
Book.sImageName = ds.Tables[0].Rows[i]["Image_Name"].ToString();
Books.Add(Book);
}
return(Books);
}
/// <summary>
/// Add a new invoice with line items to the database
/// </summary>
/// <param name="invoice">The invoice to add</param>
/// <param name="items">The items to add</param>
/// <returns>New Invoice ID</returns>
public int addNewInvoice(Invoice invoice, ObservableCollection <Item> items)
{
int newInvoiceNumber = 0;
try
{
int iRef = 0;
string query = "";
//insert the invoice
query = "INSERT INTO Invoices(InvoiceDate, TotalCost) VALUES(#" + invoice.InvoiceDate + "#, " + invoice.TotalCost + " )";
iRef = db.ExecuteNonQuery(query);
//Get the ID back
query = "SELECT MAX(InvoiceNum) FROM Invoices";
string temp = db.ExecuteScalarSQL(query);
invoice.InvoiceNumber = Int32.Parse(temp);
for (int i = 0; i < items.Count(); i++)
{
//insert the line Items
query = "INSERT INTO LineItems (InvoiceNum, LineItemNum, ItemCode) VALUES(" + invoice.InvoiceNumber + "," + (i + 1) + ",'" + items.ElementAt(i).ItemCode + "')";
iRef = db.ExecuteNonQuery(query);
}
}
catch (Exception ex)
{
System.IO.File.AppendAllText("C:\\Error.txt", Environment.NewLine +
"HandleError Exception: " + ex.Message);
}
return(newInvoiceNumber);
}
/// <summary>
/// Saves a working invoice to the Invoices database
/// </summary>
/// <param name="invoiceDate"></param>
/// <param name="totalCost"></param>
public void SaveInvoice(string invoiceDate, string totalCost)
{
try
{
string insertInvoiceSQL = sql.InsertInvoice(invoiceDate, totalCost);
data.ExecuteNonQuery(insertInvoiceSQL);
string invoiceNumber = data.ExecuteScalarSQL("SELECT MAX(InvoiceNum) FROM Invoices");
selectedInvoice = Int32.Parse(invoiceNumber);
string insertLineItemSQL;
dgItems = new ObservableCollection <Items.clsItem>();
// insert a line item for each item on the invoice
for (int i = 0; i < WorkingInvoiceItems.Count; i++)
{
dgItems.Add(WorkingInvoiceItems[i]);
insertLineItemSQL = sql.InsertLineItem(Int32.Parse(invoiceNumber), (i + 1), WorkingInvoiceItems[i].Code);
data.ExecuteNonQuery(insertLineItemSQL);
}
}
catch (Exception ex)
{
//Just throw the exception
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." +
MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// Adds an invoice to the data base
/// </summary>
/// <param name="invoiceDate">The date to be added with the new invoice</param>
/// <param name="invoiceItems">The list of items to be added and associated with the new invoice</param>
public static void AddInvoice(DateTime invoiceDate, List <string> invoiceItems)
{
try
{
int i = 0; // Temp ref integer, unused but required
List <string> itemCodes = new List <string>(); // The list of item codes to be inserted and associated with the invoice
decimal totalCost = 0; // Temp cost to be calculated and inserted
foreach (var item in invoiceItems) // Each row (each individual item) in the list
{
DataSet ds = _data.ExecuteSQLStatement(clsMainSQL.GetItemDescRowByItemDesc(item), ref i); // The DataSet that holds the row item
itemCodes.Add(ds.Tables[0].Rows[0][0].ToString());
totalCost += Convert.ToDecimal(ds.Tables[0].Rows[0][2]);
}
_data.ExecuteNonQuery(clsMainSQL.InsertInvoice(invoiceDate, totalCost)); // Insert the new invoice
string newInvoiceNum = _data.ExecuteScalarSQL(clsMainSQL.GetNewestInvoiceNum); // The new invoice num inserted
int lineNum = 0; // The temp line num for the line item
foreach (var item in itemCodes) // Each line item associated with the new invoice
{
_data.ExecuteScalarSQL(clsMainSQL.InsertLineItems(newInvoiceNum, ++lineNum, item));
}
}
catch
{
throw new Exception("Could not add new invoice");
}
}
/// <summary>
/// Checks for the given code in the database
/// </summary>
/// <param name="code"></param>
/// <returns>Returns the code if in the database</returns>
public string CheckCode(string code)
{
try
{
//Sql statement
string sql = "SELECT TOP 1 ItemCode FROM ItemDesc WHERE ItemCode = '" + code + "'";
string result = DataAccess.ExecuteScalarSQL(sql);
return(result);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " --> " + ex.Message);
}
}
/// <summary>
/// Update current invoice to database
/// </summary>
public void UpdateDatabase()
{
try
{
RemoveBlankLines();
int numRows = int.Parse(db.ExecuteScalarSQL(sql.CountItems(CurrentInvoice.Number)));
if (numRows < CurrentInvoice.LineItems.Count) //if there are more line items than rows in databse, insert the new lines
{
for (int i = numRows; i < CurrentInvoice.LineItems.Count; i++)
{
db.ExecuteNonQuery(sql.InsertToLineItems(CurrentInvoice.Number, CurrentInvoice.LineItems[i].Position, CurrentInvoice.LineItems[i].ItemOnLine.Code));
}
}
else if (CurrentInvoice.LineItems.Count < numRows) //if there are fewer line items, delete the remainder
{
for (int i = CurrentInvoice.LineItems.Count + 1; i <= numRows; i++)
{
db.ExecuteNonQuery(sql.DeleteLineItem(CurrentInvoice.Number, i));
}
}
//update the other lines in case order changed
db.ExecuteNonQuery(sql.UpdateInvoices(CurrentInvoice.Number, CurrentInvoice.Date));
foreach (LineItem line in CurrentInvoice.LineItems)
{
db.ExecuteNonQuery(sql.UpdateLineItems(CurrentInvoice.Number, line.Position, line.ItemOnLine.Code));
}
CurrentInvoice = GetInvoice(sql.SelectMostRecentInvoice());
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "."
+ MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// create new item
/// </summary>
/// <returns></returns>
internal Item newItem()
{
try
{
string maxId = db.ExecuteScalarSQL(sql.getMaxIdFromItems());
string nextId = nextAlphaNumeric(maxId);
Item newItem = new Item(nextId, "", 0);
newItem.newRecord = true;
return(newItem);
}
catch (Exception ex)
{
throw new Exception(ExceptionChain(MethodInfo.GetCurrentMethod(), ex));
}
}
/// <summary>
/// check if item code is already used
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public string checkId(Item item)
{
try
{
clsDataAccess db = new clsDataAccess();
string code = item.ItemCode;
string checkid;
checkid = "SELECT ItemCode FROM ItemDesc WHERE ItemCode = '" + code + "';";
string result = db.ExecuteScalarSQL(checkid);
return(result);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// creates a new invoice
/// </summary>
/// <param name="date"></param>
/// <param name="cost"></param>
/// <param name="listItems"></param>
/// <returns></returns>
public int CreateInvoice(string date, string cost, List <clsLineItems> listItems)
{
try
{
db.ExecuteNonQuery(Query.CreateInvoice(date, cost));
Int32.TryParse(db.ExecuteScalarSQL(Query.GetInvoiceId(date, cost)), out int id);
CreateLineItems(id, listItems);
return(id);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType + "." +
MethodInfo.GetCurrentMethod().Name + "->" + ex.Message);
}
}
/// <summary>
/// Runs to update the passenger seat on passenger combobox selection change
/// </summary>
/// <param name="sender">cbPassenger</param>
/// <param name="e">cbPassneger Args</param>
private void cbPassenger_SelectedIndexChanged(object sender, EventArgs e)
{
// Creates a temp combobox and sets it equal to the sender
ComboBox temp = (ComboBox)sender;
// Initial combobox position
if (temp.SelectedIndex == -1)
{
// Sets seat number to blank
lblPassengerSeatNum.Text = "";
}
else
{
// SQL Query Statement
string sSQL = "SELECT Seat_Number FROM Flight_Passenger_Link where Passenger_ID = " + Passengers.Tables[0].Rows[cbPassenger.SelectedIndex][0].ToString();
// Execute Query
lblPassengerSeatNum.Text = db.ExecuteScalarSQL(sSQL);
}
}
/// <summary>
/// Adds a new invoice to the database,
/// and sets the generated invoiceNum
/// </summary>
/// <param name="date"></param>
/// <returns></returns>
public void AddNewInvoice(Invoice inv)
{
try
{
DataAccess.ExecuteNonQuery(DataSQL.InsertInvoice(inv.InvoiceDate, 0));
//gets the invoice num and sets the property on the object
if (int.TryParse(DataAccess.ExecuteScalarSQL(DataSQL.SelectLatestInvoiceNum()), out int result))
{
inv.InvoiceNum = result;
}
}
catch (Exception ex)
{
//Throw a custom-trace exception
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." +
MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// Calls the database to get the next ID that would be added
/// </summary>
/// <returns>The ID to use for the next invoice to be added</returns>
public string GetMaxInvoiceID()
{
try
{
string sql = sqlProvider.GetMaxInvoiceIDQuery();
string returnedValue = da.ExecuteScalarSQL(sql);
if (string.IsNullOrWhiteSpace(returnedValue))
{
//Since the database is empty, assume this is the first row to be added
return("1");
}
return((Convert.ToInt32(returnedValue)).ToString());
}
catch (Exception ex)
{
System.IO.File.AppendAllText("C:\\Error.txt", Environment.NewLine +
"HandleError Exception: " + ex.Message);
return("");
}
}
/// <summary>
/// Inserts a new invoice into the database and then adds all the orders using the newest InvoiceId.
/// </summary>
/// <param name="invoiceDate">UserSelectedDate</param>
/// <param name="orders"></param>
internal void AddNewInvoice(DateTime invoiceDate, BindingList <clsOrder> orders)
{
try
{
double totalCost = CalculateTotalCost(orders);
dataAccess.ExecuteNonQuery(MainSQL.SQLAddNewInvoice(invoiceDate, totalCost));
int newestId = Int32.Parse(dataAccess.ExecuteScalarSQL(
MainSQL.SQLGetNewestInvoiceId()));
foreach (clsOrder order in orders)
{
dataAccess.ExecuteNonQuery(MainSQL.SQLAddOrderToInvoice(
newestId, order.CardId, order.Quantity));
}
Update();
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// This function returns the invoice number of the latest invoice
/// </summary>
/// <returns></returns>
public int getLatestInvoiceNum()
{
return(Convert.ToInt32(db.ExecuteScalarSQL(SQLQueries.SelectTheLatestInvNum())));
}
/// <summary>
/// Returns number of flights listed in the DB
/// </summary>
/// <returns></returns>
public static int GetNumFlights()
{
return(Int32.Parse(dataAccess.ExecuteScalarSQL("SELECT Count(Flight_ID) FROM Flight")));
}
