public bool MoveConcessionItem(ConcessionsModel item)
{
bool success = true;
string queryString = "UPDATE ConcessionsTable SET Item_Loc = @itemLoc WHERE Item_Name = @itemName";
using (SqlConnection connection = new SqlConnection(connectionString))
{
//here is where we connect to the database and perform the SQL command
SqlCommand command = new SqlCommand(queryString, connection);
//thesee statements replace the @itemName and @itemAmount in the queryString with their appropriate variables
command.Parameters.Add("@itemName", System.Data.SqlDbType.VarChar, 50).Value = item.itemName;
command.Parameters.Add("@itemLoc", System.Data.SqlDbType.Int).Value = item.itemLoc;
//basically a test to make sure it worked, and catch exception
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
success = true;
return(success);
}
public ActionResult ConcessionsMakeSaleView()
{
string sql = "SELECT * FROM ConcessionsTable";
var model = new List <ConcessionsModel>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(sql, connection);
connection.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
var item = new ConcessionsModel();
item.id = (int)rdr["Id"];
item.itemName = (string)rdr["Item_Name"];
item.itemAmount = (int)rdr["Item_Amount"];
item.itemLoc = (string)rdr["Item_Loc"];
item.itemPrice = (double)rdr["Item_Price"];
model.Add(item);
}
}
return(View(model));
}
public ActionResult ConcessionsAddItem(ConcessionsModel item, string itemName, int id)
{
string queryString;
bool success = true;
//this is the SQL statement to update our item amount. @itemAmount and @itemName are replaced using the function following
if (item.itemAmount > 0 && item.itemAmount <= 500)
{
queryString = "UPDATE ConcessionsTable SET Item_Amount += @itemAmount WHERE Id = @id2";
using (SqlConnection connection = new SqlConnection(connectionString))
{
//here is where we connect to the database and perform the SQL command
SqlCommand command = new SqlCommand(queryString, connection);
//thesee statements replace the @itemName and @itemAmount in the queryString with their appropriate variables
command.Parameters.Add("@id2", System.Data.SqlDbType.Int).Value = id;
command.Parameters.Add("@itemAmount", System.Data.SqlDbType.Int).Value = item.itemAmount;
//basically a test to make sure it worked, and catch exception
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
}
else
{
success = false;
}
if (success)
{
return(View("ConcessionsView"));
}
else
{
ViewBag.Error = "Error: Item amount must be a positive integer less than 500.";
ViewBag.itemName = itemName;
ViewBag.id = id;
return(View("ConcessionsAddItemView"));
}
}
public bool ConcessionsRemoveItemAmount(ConcessionsModel item)
{
string queryString;
bool success = true;
//this is the SQL statement to update our item amount. @itemAmount and @itemName are replaced using the function following
if (item.itemAmount > 0)
{
queryString = "UPDATE ConcessionsTable SET Item_Amount -= @itemAmount WHERE Item_Name = @itemName";
using (SqlConnection connection = new SqlConnection(connectionString))
{
//here is where we connect to the database and perform the SQL command
SqlCommand command = new SqlCommand(queryString, connection);
//thesee statements replace the @itemName and @itemAmount in the queryString with their appropriate variables
command.Parameters.Add("@itemName", System.Data.SqlDbType.VarChar, 50).Value = item.itemName;
command.Parameters.Add("@itemAmount", System.Data.SqlDbType.Int).Value = item.itemAmount;
//basically a test to make sure it worked, and catch exception
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
success = true;
}
else
{
success = false;
}
return(success);
}
public ActionResult ConcessionsCartAddItem(ConcessionsModel item, string itemName, int id, double itemPrice)
{
string queryString1;
string queryString;
bool success = true;
//this is the SQL statement to update our item amount. @itemAmount and @itemName are replaced using the function following
if (item.itemAmount > 0)
{
queryString1 = "Select Item_Amount FROM ConcessionsTable WHERE Id = @id1";
using (SqlConnection connection = new SqlConnection(connectionString))
{
//here is where we connect to the database and perform the SQL command
SqlCommand command = new SqlCommand(queryString1, connection);
command.Parameters.Add("@id1", System.Data.SqlDbType.Int).Value = id;
//basically a test to make sure it worked, and catch exception
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
reader.Read();
int current_val = (int)reader["Item_Amount"];
if (current_val < item.itemAmount)
{
ViewBag.Error = "Error: There is not enough of that item";
ConcessionsCartAddItemView(itemName, id, item.itemPrice);
return(View("ConcessionsCartAddItemView"));
}
connection.Close();
if (success)
{
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
using (SqlConnection connection = new SqlConnection(connectionString))
{
queryString = "UPDATE ConcessionsTable SET Item_Amount -= @itemAmount WHERE Id = @id";
//here is where we connect to the database and perform the SQL command
SqlCommand command = new SqlCommand(queryString, connection);
//thesee statements replace the @itemName and @itemAmount in the queryString with their appropriate variables
command.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = id;
command.Parameters.Add("@itemAmount", System.Data.SqlDbType.Int).Value = item.itemAmount;
//basically a test to make sure it worked, and catch exception
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
reader.Read();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
ViewBag.Error = "";
GlobalConcessionsCartModel.cart.Add(item);
GlobalConcessionsCartModel.total += itemPrice * item.itemAmount;
ConcessionsCartView();
return(View("ConcessionsCartView"));
}
else
{
ViewBag.Error = "Error: Can not add negative item amounts";
ConcessionsCartAddItemView(itemName, id, item.itemPrice);
return(View("ConcessionsCartAddItemView"));
}
}
public ActionResult ConcessionsRemoveItem(ConcessionsModel item, string itemName, int id)
{
string queryString2;
string queryString1;
bool success = true;
//this is the SQL statement to update our item amount. @itemAmount and @itemName are replaced using the function following
if (item.itemAmount > 0)
{
queryString1 = "Select Item_Amount FROM ConcessionsTable WHERE Id = @id1";
queryString2 = "UPDATE ConcessionsTable SET Item_Amount -= @itemAmount WHERE Id = @id2";
using (SqlConnection connection = new SqlConnection(connectionString))
{
//here is where we connect to the database and perform the SQL command
SqlCommand command = new SqlCommand(queryString1, connection);
command.Parameters.Add("@id1", System.Data.SqlDbType.Int).Value = id;
//basically a test to make sure it worked, and catch exception
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
reader.Read();
int current_val = (int)reader["Item_Amount"];
if (current_val < item.itemAmount)
{
success = false;
ViewBag.Error = "Error: Cannot remove more than current value. Current value is: " + current_val;
}
if (success)
{
connection.Close();
connection.Open();
command.CommandText = queryString2;
//thesee statements replace the @itemName and @itemAmount in the queryString with their appropriate variables
command.Parameters.Add("@id2", System.Data.SqlDbType.Int).Value = id;
command.Parameters.Add("@itemAmount", System.Data.SqlDbType.Int).Value = item.itemAmount;
command.ExecuteNonQuery();
connection.Close();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
}
else
{
ViewBag.Error = "Error: Item amount must be a positive integer.";
success = false;
}
if (success)
{
return(View("ConcessionsView"));
}
else
{
ViewBag.itemName = itemName;
ViewBag.id = id;
return(View("ConcessionsRemoveItemView"));
}
}
public ActionResult ConcessionsMoveItem(ConcessionsModel item, string itemName, int id)
{
int current_amount;
string queryString;
bool success = true;
queryString = "SELECT * FROM ConcessionsTable WHERE Id = @id";
using (SqlConnection connection = new SqlConnection(connectionString))
{
//here is where we connect to the database and perform the SQL command
SqlCommand command = new SqlCommand(queryString, connection);
//thesee statements replace the @itemName and @itemAmount in the queryString with their appropriate variables
command.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = id;
//basically a test to make sure it worked, and catch exception
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
reader.Read();
current_amount = (int)reader["Item_Amount"];
connection.Close();
connection.Open();
command.CommandText = "SELECT * FROM ConcessionsTable WHERE Item_Name = @itemName1";
command.Parameters.Add("@itemName1", System.Data.SqlDbType.VarChar, 50).Value = itemName;
string location;
double itemPrice = 0;
bool exists;
exists = false;
int existingID = 0;
reader = command.ExecuteReader();
while (reader.Read())
{
location = (string)reader["Item_Loc"];
itemPrice = (double)reader["Item_Price"];
if (location == item.itemLoc)
{
exists = true;
existingID = (int)reader["Id"];
break;
}
}
connection.Close();
connection.Open();
if (exists)
{
if (item.itemAmount == current_amount)
{
// Change the SQL Command and execute
command.CommandText = "DELETE FROM ConcessionsTable WHERE Id = @id2;";
command.Parameters.Add("@id2", System.Data.SqlDbType.Int).Value = id;
command.ExecuteNonQuery();
command.CommandText = "UPDATE ConcessionsTable SET Item_Amount += @itemAmount WHERE Id = @existingID";
command.Parameters.Add("@itemAmount", System.Data.SqlDbType.Int).Value = item.itemAmount;
command.Parameters.Add("@existingID", System.Data.SqlDbType.Int).Value = existingID;
command.ExecuteNonQuery();
}
else if (item.itemAmount < current_amount && item.itemAmount > 0)
{
command.CommandText = "UPDATE ConcessionsTable SET Item_Amount += @itemAmount WHERE Id = @existingID";
command.Parameters.Add("@itemAmount", System.Data.SqlDbType.Int).Value = item.itemAmount;
command.Parameters.Add("@existingID", System.Data.SqlDbType.Int).Value = existingID;
command.ExecuteNonQuery();
command.CommandText = "UPDATE ConcessionsTable SET Item_Amount -= @itemAmount2 WHERE Id = @id2";
command.Parameters.Add("@itemAmount2", System.Data.SqlDbType.Int).Value = item.itemAmount;
command.Parameters.Add("@id2", System.Data.SqlDbType.Int).Value = id;
command.ExecuteNonQuery();
}
else
{
success = false;
ViewBag.Error = "Error: Amount to be moved must be positive and less than or equal to the current amount.\nCurrent amount: " + current_amount.ToString();
}
}
else
{
if (item.itemAmount == current_amount)
{
// Change the SQL Command and execute
command.CommandText = "UPDATE ConcessionsTable SET Item_Loc = @itemLoc WHERE Id = @id2";
command.Parameters.Add("@itemLoc", System.Data.SqlDbType.VarChar, 50).Value = item.itemLoc;
command.Parameters.Add("@id2", System.Data.SqlDbType.Int).Value = id;
command.ExecuteNonQuery();
}
else if (item.itemAmount < current_amount && item.itemAmount > 0)
{
command.CommandText = "INSERT INTO ConcessionsTable values(@iN, @iA, @iL, @iP)";
command.Parameters.Add("@iN", System.Data.SqlDbType.VarChar, 50).Value = itemName;
command.Parameters.Add("@iA", System.Data.SqlDbType.Int).Value = item.itemAmount;
command.Parameters.Add("@iL", System.Data.SqlDbType.VarChar, 50).Value = item.itemLoc;
command.Parameters.Add("@iP", System.Data.SqlDbType.Float).Value = itemPrice;
command.ExecuteNonQuery();
command.CommandText = "UPDATE ConcessionsTable SET Item_Amount -= @itemAmount WHERE Id = @id2";
command.Parameters.Add("@itemAmount", System.Data.SqlDbType.Int).Value = item.itemAmount;
command.Parameters.Add("@id2", System.Data.SqlDbType.Int).Value = id;
command.ExecuteNonQuery();
}
else
{
success = false;
ViewBag.Error = "Error: Amount to be moved must be positive and less than or equal to the current amount.";
ViewBag.Error2 = "Current amount: " + current_amount.ToString();
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
if (success)
{
return(View("ConcessionsView"));
}
else
{
ViewBag.itemName = itemName;
ViewBag.id = id;
return(View("ConcessionsUpdateLocation"));
}
}
public ActionResult ConcessionsUpdateLocationView(ConcessionsModel concessionsModel)
{
return(View("ConcessionsUpdateLocation"));
}
