/*Precondition:
Postcondition: Adds the stock send in into the newOrderedStock list, also add it to the datagrid to be viewed */
public void addStock(Stock newStock)
{
OrderedStock os = new OrderedStock(currOrder.orderID, newStock.stockID, 1, newStock.author, newStock.title, newStock.price, newStock.bookID, 0.00);
newOrderedStock.Add(os);
dataGridView1.Rows.Add(1, newStock.author, newStock.title, "$" + String.Format("{0:0.00}", newStock.price), newStock.bookID, "$0.00");
}
/*Precondition:
Postcondition: Returns a list of stock that was entered most recently that is the amount that was passed in */
public List<Stock> getRecentStock(int amountOfStock)
{
List<Stock> mostRecent = new List<Stock>();
//Check to make sure stock table exists
if (checkForTable("Stock"))
{
string searchQuery = "SELECT * FROM Stock LIMIT " + amountOfStock.ToString() + " OFFSET (SELECT COUNT(*) FROM Stock)-" + amountOfStock.ToString();
dbConnection.Open();
//Execute query
SQLiteCommand command = new SQLiteCommand(searchQuery, dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
//Loop over and store results
while (reader.Read())
{
Stock currStock = new Stock(Convert.ToInt32(reader[0]), Convert.ToInt32(reader[1]), reader[2].ToString(), reader[3].ToString(), reader[4].ToString(), reader[5].ToString(), reader[6].ToString(),
reader[7].ToString(), reader[8].ToString(), Convert.ToDouble(reader[9]), reader[10].ToString(), reader[11].ToString(), reader[12].ToString(), reader[13].ToString(), reader[14].ToString(), reader[15].ToString());
mostRecent.Add(currStock);
}
dbConnection.Close();
}
return mostRecent;
}
/*Precondition:
Postcondition: Inserts a single new stock into the SQLite database*/
public void insertStock(Stock newStock)
{
//Check to see if stock table exists
if (checkForTable("Stock"))
{
int quantity = newStock.quantity;
string note = SyntaxHelper.escapeSingleQuotes(newStock.note);
string author = SyntaxHelper.escapeSingleQuotes(newStock.author);
string title = SyntaxHelper.escapeSingleQuotes(newStock.title);
string subtitle = SyntaxHelper.escapeSingleQuotes(newStock.subtitle);
string publisher = SyntaxHelper.escapeSingleQuotes(newStock.publisher);
string description = SyntaxHelper.escapeSingleQuotes(newStock.description);
string comments = SyntaxHelper.escapeSingleQuotes(newStock.comments);
double price = newStock.price;
string subject = SyntaxHelper.escapeSingleQuotes(newStock.subject);
string catalogue = SyntaxHelper.escapeSingleQuotes(newStock.catalogue);
string initials = SyntaxHelper.escapeSingleQuotes(newStock.initials);
string sales = SyntaxHelper.escapeSingleQuotes(newStock.sales);
string bookID = SyntaxHelper.escapeSingleQuotes(newStock.bookID);
string dateEntered = SyntaxHelper.escapeSingleQuotes(newStock.dateEntered);
int stockID = newStock.stockID;
//Open DB and start transcation - transaction hugely increases speed of insert
dbConnection.Open();
string stockInsert = "";
//Build insert command
stockInsert = "INSERT INTO Stock VALUES(null, '" + quantity + "', '" + note + "', '" + author + "', '" + title + "', '" + subtitle + "', '" + publisher
+ "', '" + description + "', '" + comments + "', '" + price + "', '" + subject + "', '" + catalogue + "', '" + initials + "', '" + sales + "', '" + bookID +
"', '" + dateEntered + "')";
SQLiteCommand insertCommand = new SQLiteCommand(stockInsert, dbConnection);
insertCommand.ExecuteNonQuery();
//Close connection
dbConnection.Close();
}
}
/*Precondition:
Postcondition: Returns a list of all the stock that has a quantity greater than 0*/
public List<Stock> getAllStockInStock()
{
List<Stock> allStockInStock = new List<Stock>();
//Check to make sure stock table exists
if (checkForTable("Stock"))
{
dbConnection.Open();
string sql = "SELECT * FROM Stock WHERE quantity > 0";
SQLiteCommand command = new SQLiteCommand(sql, dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
//Loop over and store results
while (reader.Read())
{
Stock nextStock = new Stock(Convert.ToInt32(reader[0]), Convert.ToInt32(reader[1]), reader[2].ToString(), reader[3].ToString(), reader[4].ToString(), reader[5].ToString(), reader[6].ToString(),
reader[7].ToString(), reader[8].ToString(), Convert.ToDouble(reader[9]), reader[10].ToString(), reader[11].ToString(), reader[12].ToString(), reader[13].ToString(), reader[14].ToString(), reader[15].ToString());
allStockInStock.Add(nextStock);
}
dbConnection.Close();
}
return allStockInStock;
}
/*Precondition:
Postcondition: Returns the last stock that was entered into the database */
public Stock getLastStock()
{
Stock lastStock = null;
//Check to make sure orders table exists
if (checkForTable("Stock"))
{
dbConnection.Open();
int lastIDValue = 0;
//Search for the ID of the last stock entered
string sql = "SELECT MAX(stockID) FROM Stock";
SQLiteCommand command = new SQLiteCommand(sql, dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
//Loop over and store results
while (reader.Read())
{
lastIDValue = Convert.ToInt32(reader[0]);
}
//Select the last stock entry from the database
string getLastStock = "SELECT * FROM Stock WHERE stockID = " + lastIDValue;
SQLiteCommand getLastStockCommand = new SQLiteCommand(getLastStock, dbConnection);
SQLiteDataReader getLastStockReader = getLastStockCommand.ExecuteReader();
//Loop over and store results
while (getLastStockReader.Read())
{
lastStock = new Stock(Convert.ToInt32(getLastStockReader[0]), Convert.ToInt32(getLastStockReader[1]), getLastStockReader[2].ToString(), getLastStockReader[3].ToString(), getLastStockReader[4].ToString(), getLastStockReader[5].ToString(), getLastStockReader[6].ToString(),
getLastStockReader[7].ToString(), getLastStockReader[8].ToString(), Convert.ToDouble(getLastStockReader[9]), getLastStockReader[10].ToString(), getLastStockReader[11].ToString(), getLastStockReader[12].ToString(), getLastStockReader[13].ToString(), getLastStockReader[14].ToString(), getLastStockReader[15].ToString());
}
dbConnection.Close();
}
return lastStock;
}
/*Precondition:
Postcondition: Returns a list of all the stock */
public List<Stock> getAllStock()
{
List<Stock> foundStock = new List<Stock>();
//Check to make sure stock table exists
if (checkForTable("Stock"))
{
string searchQuery = "SELECT * FROM Stock";
dbConnection.Open();
//Execute query
SQLiteCommand command = new SQLiteCommand(searchQuery, dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
//Loop over and store results
while (reader.Read())
{
Stock currStock = new Stock(Convert.ToInt32(reader[0]), Convert.ToInt32(reader[1]), reader[2].ToString(), reader[3].ToString(), reader[4].ToString(), reader[5].ToString(), reader[6].ToString(),
reader[7].ToString(), reader[8].ToString(), Convert.ToDouble(reader[9]), reader[10].ToString(), reader[11].ToString(), reader[12].ToString(), reader[13].ToString(), reader[14].ToString(), reader[15].ToString());
foundStock.Add(currStock);
}
dbConnection.Close();
}
//Return results
return foundStock;
}
/*Precondition:
Postcondition: Updates the passed in stocks details, new details already added onto the stock, use the ID to update*/
public void updateStock(Stock stock)
{
if (checkForTable("Stock"))
{
int quantity = stock.quantity;
string note = SyntaxHelper.escapeSingleQuotes(stock.note);
string author = SyntaxHelper.escapeSingleQuotes(stock.author);
string title = SyntaxHelper.escapeSingleQuotes(stock.title);
string subtitle = SyntaxHelper.escapeSingleQuotes(stock.subtitle);
string publisher = SyntaxHelper.escapeSingleQuotes(stock.publisher);
string description = SyntaxHelper.escapeSingleQuotes(stock.description);
string comments = SyntaxHelper.escapeSingleQuotes(stock.comments);
double price = stock.price;
string subject = SyntaxHelper.escapeSingleQuotes(stock.subject);
string catalogue = SyntaxHelper.escapeSingleQuotes(stock.catalogue);
string initials = SyntaxHelper.escapeSingleQuotes(stock.initials);
string sales = SyntaxHelper.escapeSingleQuotes(stock.sales);
string bookID = SyntaxHelper.escapeSingleQuotes(stock.bookID);
string dateEntered = SyntaxHelper.escapeSingleQuotes(stock.dateEntered);
int stockID = stock.stockID;
//Apostrophies cause program to crash
string updateQuery = "UPDATE Stock SET quantity =" + quantity + ", note = '" + note + "', author = '" + author + "', title = '" + title +
"', subtitle = '" + subtitle + "', publisher = '" + publisher + "', description = '" + description + "', comments = '" + comments +
"', price = '" + price + "', subject = '" + subject + "', catalogue = '" + catalogue + "', initials = '" + initials + "', sales = '" + sales +
"', bookID = '" + bookID + "', dateEntered = '" + dateEntered + "' WHERE stockID = " + stockID;
dbConnection.Open();
SQLiteCommand updateCommand = new SQLiteCommand(updateQuery, dbConnection);
updateCommand.ExecuteNonQuery();
dbConnection.Close();
}
}
/*Precondition:
Postcondition: Returns the stock that has the ID that was passed in */
public Stock searchStock(int stockID)
{
Stock foundStock = null;
//Check to see if stock table exists
if (checkForTable("Stock"))
{
dbConnection.Open();
//Execute SQL query
string sql = "SELECT * FROM Stock WHERE stockID = " + stockID;
SQLiteCommand command = new SQLiteCommand(sql, dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
//Loop over and store results
while (reader.Read())
{
foundStock = new Stock(Convert.ToInt32(reader[0]), Convert.ToInt32(reader[1]), reader[2].ToString(), reader[3].ToString(), reader[4].ToString(), reader[5].ToString(), reader[6].ToString(),
reader[7].ToString(), reader[8].ToString(), Convert.ToDouble(reader[9]), reader[10].ToString(), reader[11].ToString(), reader[12].ToString(), reader[13].ToString(), reader[14].ToString(), reader[15].ToString());
}
dbConnection.Close();
}
//Return results
return foundStock;
}
/*Precondition:
Postcondition: Returns a list of the stock from the database that match the parameters passed in */
public List<Stock> searchStock(string author, string title, string subject, bool searchAllStock, bool exactPhrase)
{
//Create storage for stock that's found
List<Stock> foundStock = new List<Stock>();
//Check to see if stock table exists
if (checkForTable("Stock"))
{
author = SyntaxHelper.escapeSingleQuotes(author);
title = SyntaxHelper.escapeSingleQuotes(title);
dbConnection.Open();
//build up a query string based on the parameters passed in
string searchQuery = "SELECT * FROM Stock WHERE";
bool addAnds = false;
//Author included so add that to query
if (author != null)
{
if (author.Contains(','))
{
string[] splitAuthor = author.Split(',');
if (splitAuthor[1][0] == ' ')
splitAuthor[1] = splitAuthor[1].Remove(0, 1);
if (exactPhrase)
searchQuery += " author = '" + splitAuthor[0] + "' AND author = '" + splitAuthor[1] + "'";
else
searchQuery += " author LIKE '%" + splitAuthor[0] + "%' AND author LIKE '%" + splitAuthor[1] + "%'";
}
else if (author.Contains(' '))
{
string[] splitAuthor = author.Split(' ');
bool first = true;
foreach (string s in splitAuthor)
{
if (first)
{
if (exactPhrase)
searchQuery += " author = '" + s + "'";
else
searchQuery += " author LIKE '%" + s + "%'";
first = false;
}
else
{
if (exactPhrase)
searchQuery += " AND author = '" + s + "'";
else
searchQuery += " AND author LIKE '%" + s + "%'";
}
}
//searchQuery += " author LIKE '%" + splitAuthor[0] + "%' AND author LIKE '%" + splitAuthor[1] + "%'";
}
else
{
if (exactPhrase)
searchQuery += " author = '" + author + "'";
else
searchQuery += " author LIKE '%" + author + "%'";
}
addAnds = true;
}
//Title included so add that to query
if (title != null)
{
if (!exactPhrase)
{
string[] splitTitle = title.Split(' ');
foreach (string s in splitTitle)
{
if (addAnds)
{
if (exactPhrase)
searchQuery += " AND title = '" + s + "'";
else
searchQuery += " AND title LIKE '%" + s + "%'";
}
else
{
if (exactPhrase)
searchQuery += " title = '" + s + "'";
else
searchQuery += " title LIKE '%" + s + "%'";
addAnds = true;
}
}
}
else
{
if (addAnds)
{
if (exactPhrase)
searchQuery += " AND title = '" + title + "'";
else
searchQuery += " AND title LIKE '%" + title + "%'";
}
else
{
if (exactPhrase)
searchQuery += " title = '" + title + "'";
else
searchQuery += " title LIKE '%" + title + "%'";
addAnds = true;
}
}
}
//Subject included so add that to query
if (subject != null)
{
if (addAnds)
{
searchQuery += " AND subject = '" + subject + "'";
}
else
{
searchQuery += " subject LIKE '%" + subject + "%'";
}
}
//Make search non-case sensitive
searchQuery += " COLLATE NOCASE";
if (!searchAllStock)
searchQuery += " AND quantity > 0";
//Execute query
SQLiteCommand command = new SQLiteCommand(searchQuery, dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
//Loop over and store results
while (reader.Read())
{
Stock currStock = new Stock(Convert.ToInt32(reader[0]), Convert.ToInt32(reader[1]), reader[2].ToString(), reader[3].ToString(), reader[4].ToString(), reader[5].ToString(), reader[6].ToString(),
reader[7].ToString(), reader[8].ToString(), Convert.ToDouble(reader[9]), reader[10].ToString(), reader[11].ToString(), reader[12].ToString(), reader[13].ToString(), reader[14].ToString(), reader[15].ToString());
foundStock.Add(currStock);
}
dbConnection.Close();
}
//Return results
return foundStock;
}
/*Precondition:
Postcondition: A book has been found, autofill information and store book*/
public void addStock(Stock newStock)
{
orderedBooks.Add(newStock);
dataGridView1.Rows.Add(1, newStock.author, newStock.title, "$" + String.Format("{0:0.00}", newStock.price), newStock.bookID, "$0.00");
}
