//CART
public void LoadCarts()
{
dataGridView_Carts.Rows.Clear();
var query = "SELECT ID, CustomerName, CardAmounts FROM Carts WHERE Status = 'Active' ORDER BY ID";
var cartData = DatabaseManager.RunQuery(query);
foreach (DataRow cart in cartData.Rows)
{
var cartID = Convert.ToString(cart[0]);
var cartCustomer = Convert.ToString(cart[1]);
var cardAmounts = Convert.ToString(cart[2]);
var amount = GetCardAmount(cardAmounts);
try
{
dataGridView_Carts.Rows.Add(cartID, cartCustomer, amount);
}
catch (Exception ex)
{
//TODO: Log this? Most likely because form is closed
}
}
}
int GetInventory(string name, string category)
{
var query = "SELECT INVENTORY FROM Non_Mtg WHERE NAME = \'" + name + "\' AND CATEGORY = \'" + category + "\'";
var dataTable = DatabaseManager.RunQuery(query);
return((int)dataTable.Rows[0]["inventory"]);
}
private void button_CompleteSale_Click(object sender, EventArgs e)
{
var idQuery = "SELECT CardIDs FROM Carts WHERE ID = '" + ID + "'";
var idResults = DatabaseManager.RunQuery(idQuery).Rows;
var idArray = idResults[0][0].ToString().Split('|');
var cardPrices = "";
for (int i = 0; i < dataGridView_Items.Rows.Count - 1; i++)
{
var row = dataGridView_Items.Rows[i];
if (i != 0)
{
cardPrices += "|";
}
cardPrices += row.Cells["Price"].Value.ToString();
var id = idArray[i + 1];
if (String.IsNullOrWhiteSpace(id))
{
continue;
}
int multiverseID;
var updateQuery = "";
if (int.TryParse(id, out multiverseID))
{
if (row.Cells["CardName"].Value.ToString().Contains("*F*"))
{
updateQuery = "UPDATE Mtg SET foilInventory = (foilInventory - " + row.Cells["Amount"].Value.ToString() + ") WHERE multiverseID = '" + multiverseID + "'";
}
else
{
updateQuery = "UPDATE Mtg SET inventory = (inventory - " + row.Cells["Amount"].Value.ToString() + ") WHERE multiverseID = '" + multiverseID + "'";
}
}
else
{
updateQuery = "UPDATE Non_Mtg SET inventory = (inventory - " + row.Cells["Amount"].Value.ToString() + ") WHERE MD5Hash = '" + id + "'";
}
DatabaseManager.RunQuery(updateQuery);
}
var query = "UPDATE Carts SET " +
"CardPrices = '" + cardPrices + "', " +
"Subtotal = '" + textBox_PriceSubtotal.Text + "', " +
"Taxes = '" + textBox_PriceTaxes.Text + "', " +
"Total = '" + textBox_PriceTotal.Text + "', " +
"Status = 'Sale Complete' " +
"WHERE ID = '" + ID + "'";
DatabaseManager.RunQuery(query);
SaveCustomerData();
CartManager.StatusChanged();
Close();
}
void UpdateInventory(string md5, int amount)
{
var query = "UPDATE Non_Mtg SET " +
"Inventory = '" + amount + "' " +
"WHERE MD5Hash = '" + md5 + "'";
DatabaseManager.RunQuery(query);
}
public void CartUpdated()
{
dataGridView_Items.Rows.Clear();
var query = "SELECT * FROM Carts WHERE (ID = " + ID + " AND Status = 'Active')";
var cartData = DatabaseManager.RunQuery(query);
ParseCartData(cartData);
UpdatePrice();
}
void RemoveOneFromInventory(string name, string category)
{
try
{
var query = "UPDATE Non_Mtg SET INVENTORY = INVENTORY - 1 WHERE NAME = \'" + name + "\' AND CATEGORY = \'" + category + "\'";
DatabaseManager.RunQuery(query);
}
catch (Exception ex)
{
}
}
void CreateColumnIfNotExist(Dictionary <string, object> values)
{
for (int i = 0; i < values.Count; i++)
{
var key = Regex.Replace(values.ElementAt(i).Key.ToString(), "'", "");
var query = "IF NOT EXISTS(SELECT * FROM sys.columns WHERE[name] = N'" +
key + "' AND[object_id] = OBJECT_ID(N'non_mtg')) BEGIN ALTER TABLE non_mtg ADD " +
key + " varchar(MAX) END";
DatabaseManager.RunQuery(query);
}
}
void SaveCustomerData()
{
var query = "UPDATE Carts SET " +
"CustomerName = '" + textBox_CustomerName.Text + "', " +
"CustomerEmail = '" + textBox_CustomerEmail.Text + "', " +
"CustomerNumber = '" + textBox_CustomerEmail.Text + "', " +
"LastUpdated = '" + DateTime.Now + "' " +
"WHERE ID = '" + ID + "'";
DatabaseManager.RunQuery(query);
Startup.GetInstance().UpdateCarts();
}
void ParseCartData(DataTable cartData)
{
var row = cartData.Rows[0];
var cardNames = row["CardNames"].ToString().Split(ITEM_DELIMITER);
var cardSets = row["CardExpansions"].ToString().Split(ITEM_DELIMITER);
var cardAmounts = row["CardAmounts"].ToString().Split(ITEM_DELIMITER);
var customerName = row["CustomerName"].ToString();
var customerPhone = row["CustomerNumber"].ToString();
var customerEmail = row["CustomerEmail"].ToString();
textBox_CustomerName.Text = customerName;
textBox_CustomerPhone.Text = customerPhone;
textBox_CustomerEmail.Text = customerEmail;
for (int i = 0; i < cardNames.Count(); i++)
{
var cardName = cardNames[i];
var cardSet = cardSets[i];
var cardAmount = cardAmounts[i];
if (String.IsNullOrWhiteSpace(cardName) && String.IsNullOrWhiteSpace(cardSet) && String.IsNullOrWhiteSpace(cardAmount))
{
continue;
}
var query = "";
if (cardName.Contains("*F*"))
{
query = "SELECT foilPrice FROM MtG WHERE (name = '" + cardName.Replace("'", "''").Replace(" *F*", "") + "' and expansion = '" + cardSet.Replace("'", "''") + "')";
}
else
{
query = "SELECT price FROM MtG WHERE (name = '" + cardName.Replace("'", "''") + "' and expansion = '" + cardSet.Replace("'", "''") + "')";
}
try
{
var results = DatabaseManager.RunQuery(query).Rows;
var price = results[0][0].ToString();
dataGridView_Items.Rows.Add(cardNames[i], cardSets[i], price, cardAmounts[i]);
}
catch (Exception ex)
{
//Not Magic, currently don't have price data
dataGridView_Items.Rows.Add(cardNames[i], cardSets[i], "0", cardAmounts[i]);
}
}
}
public static void DeleteCart(int cartID)
{
try
{
var query = "UPDATE Carts SET Status = 'Deleted' WHERE ID = " + cartID;
DatabaseManager.RunQuery(query);
StatusChanged();
}
catch (Exception ex)
{
Logger.LogError("Attempted to delete card", ex.Message, cartID.ToString());
}
}
string FindSport(string set)
{
var query = "SELECT * FROM SportList";
var results = DatabaseManager.RunQuery(query);
foreach (DataRow sport in results.Rows)
{
if (set.Contains(sport[0].ToString()))
{
return(sport[0].ToString());
}
}
return(String.Empty);
}
private void button_Search_Click(object sender, EventArgs e)
{
if (dataGridView_SearchParams.Rows.Count <= 1)
{
return;
}
var query = "SELECT * FROM Non_Mtg WHERE (";
for (int i = 0; i < dataGridView_SearchParams.Rows.Count - 1; i++)
{
var param = Convert.ToString(dataGridView_SearchParams.Rows[i].Cells["Parameter"].Value);
var type = Convert.ToString(dataGridView_SearchParams.Rows[i].Cells["Type"].Value);
var include = Convert.ToString(dataGridView_SearchParams.Rows[i].Cells["Inclusion"].Value);
var searchType = Convert.ToString(dataGridView_SearchParams.Rows[i].Cells["SearchType"].Value);
if (String.IsNullOrWhiteSpace(param) || String.IsNullOrWhiteSpace(type) || String.IsNullOrWhiteSpace(include) || String.IsNullOrWhiteSpace(searchType))
{
continue;
}
query += PrepSearchData(param, type, include, searchType);
if (i != dataGridView_SearchParams.Rows.Count - 2)
{
query += " AND ";
}
}
query += ")";
if (query.Contains("()"))
{
return;
}
var cards = DatabaseManager.RunQuery(query);
AddTableToRow(cards);
}
string CheckForDuplicates(Dictionary <string, object> values)
{
var allString = "";
var md5String = "";
for (int i = 0; i < values.Count; i++)
{
allString += values.ElementAt(i).ToString();
}
byte[] encodedPassword = new UTF8Encoding().GetBytes(allString);
byte[] hash = ((HashAlgorithm)CryptoConfig.CreateFromName("MD5")).ComputeHash(encodedPassword);
md5String = BitConverter.ToString(hash).Replace("-", string.Empty).ToLower();
var query = "SELECT * FROM Non_Mtg WHERE MD5Hash = '" + md5String + "'";
var duplicates = DatabaseManager.RunQuery(query);
if (duplicates.Rows.Count > 0)
{
return(String.Empty);
}
return(md5String);
}
public static void AddItemToCart(int cartID, string cardID, string itemName, string itemExpansion, int itemAmount)
{
//TODO: How do I want to do this? Take in a dictionary of the items I list? Maybe just the ID? I'll have to condense all cards into a single card table
var fetchQuery = "Select CardIDs, CardNames, CardExpansions, CardAmounts FROM Carts WHERE (ID = '" + cartID + "'" + " AND Status = 'Active')";
var fetchResults = DatabaseManager.RunQuery(fetchQuery);
var row = fetchResults.Rows[0];
var idString = row[0].ToString();
var nameString = row[1].ToString();
var expansionString = row[2].ToString();
var amountString = row[3].ToString();
var idSplit = idString.Split('|');
var nameSplit = nameString.Split('|');
var expansionSplit = expansionString.Split('|');
var splitAmount = amountString.Split('|');
var skipCheck = false;
var foundMatch = false;
if (nameSplit.Count() != expansionSplit.Count())
{
Logger.LogError("Searching for duplicates in cart: " + cartID, "The name field and expansion field have different amounts of entries.", "");
skipCheck = true;
}
if (!skipCheck)
{
for (int i = 0; i < idSplit.Count(); i++)
{
if (idSplit[i] == cardID)
{
if (nameSplit[i] == itemName)
{
var currentAmount = Convert.ToInt32(splitAmount[i]);
currentAmount += itemAmount;
splitAmount[i] = currentAmount.ToString();
foundMatch = true;
break;
}
}
}
}
if (foundMatch)
{
amountString = "";
for (int j = 0; j < splitAmount.Count(); j++)
{
if (j != 0)
{
amountString += "|";
}
amountString += splitAmount[j];
}
}
else
{
idString += "|" + cardID;
nameString += "|" + itemName;
expansionString += "|" + itemExpansion;
amountString += "|" + itemAmount;
}
var query = "UPDATE Carts SET " +
"CardIDs = '" + idString +
"', CardNames = '" + nameString.Replace("'", "''") +
"', CardExpansions = '" + expansionString.Replace("'", "''") +
"', CardAmounts = '" + amountString +
"', LastUpdated = '" + DateTime.Now +
"' WHERE ID = '" + cartID + "'";
DatabaseManager.RunQuery(query);
for (int i = 0; i < OpenCarts.Count; i++)
{
if (OpenCarts[i].GetID() == cartID)
{
OpenCarts[i].CartUpdated();
return;
}
}
StatusChanged();
}
//INVENTORY
void AddOneToInventory(string name, string category)
{
var query = "UPDATE Non_Mtg SET INVENTORY = INVENTORY + 1 WHERE NAME = \'" + name + "\' AND CATEGORY = \'" + category + "\'";
DatabaseManager.RunQuery(query);
}