public void OnGet()
{
DBString dB = new DBString();
string ConnectionString = dB.ConString();
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = @"SELECT * from ImgData ";
SqlDataReader reader = command.ExecuteReader(); //SqlDataReader is used to read record from a table
ImageRecord = new List <ImageModel>(); //this object of list is created to populate all records from the table
while (reader.Read())
{
ImageModel record = new ImageModel(); //a local var to hold a record temporarily
record.SerialNumber = reader.GetString(0); //getting the first field from the table
record.User = reader.GetString(1); //getting the third field from the table
record.FileName = reader.GetString(2); //getting the second field from the table
ImageRecord.Add(record); //adding the single record into the list
}
// Call Close when done reading.
reader.Close();
}
}
//Once button is cliocked onpost runs with id.
public IActionResult OnPost(int?id)
{
//creates the file path.
var FileToUpload = Path.Combine(webEnv.WebRootPath, "Files", imgUpload.FileName);
Console.WriteLine("File Name : " + FileToUpload);
//Coppys path to fstream which uploads to locaiton.
using (var FStream = new FileStream(FileToUpload, FileMode.Create))
{
imgUpload.CopyTo(FStream);//copy the file into FStream variable
}
//gets username to upload into db.
UserName = HttpContext.Session.GetString(Session1);
DBString dB = new DBString();
string ConnectionString = dB.ConString();
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
//Insert serialnumber filepath and username into database imgUpload.
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = @"INSERT INTO ImgData (SerialNumber, FileName, Username) VALUES (@SNum,@Fnam,@Unam)";
command.Parameters.AddWithValue("@SNum", id);
command.Parameters.AddWithValue("@Fnam", UserName);
command.Parameters.AddWithValue("@Unam", imgUpload.FileName);
Console.WriteLine(Products);
Console.WriteLine(FileToUpload);
Console.WriteLine(UserName);
command.ExecuteNonQuery();
}
//go back to homepage.
return(RedirectToPage("/index"));
}
public IActionResult OnPost()
{
if (!ModelState.IsValid)
{
return(Page());
}
DBString dB = new DBString();
string ConnectionString = dB.ConString();
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = @"INSERT INTO UserTable (UserName, FirstName, SecondName, Password, Role) VALUES (@UNam, @FNam, @SNam, @Pass, @Role)";
command.Parameters.AddWithValue("@UNam", Users.UserName);
command.Parameters.AddWithValue("@FNam", Users.FirstName);
command.Parameters.AddWithValue("@SNam", Users.SecondName);
command.Parameters.AddWithValue("@Pass", Users.Password);
command.Parameters.AddWithValue("@Role", Users.Role);
Console.WriteLine(Users.FirstName);
Console.WriteLine(Users.SecondName);
Console.WriteLine(Users.Password);
Console.WriteLine(Users.Role);
command.ExecuteNonQuery();
}
return(RedirectToPage("/Index"));
}
//On page load with id which is serial number from the page it was called from.
public IActionResult OnGet(int?id)
{
//Get session variables.
UserName = HttpContext.Session.GetString(Session1);
FirstName = HttpContext.Session.GetString(Session2);
sessionId = HttpContext.Session.GetString(Session3);
Role = HttpContext.Session.GetString(Session4);
//Redirect to login page if nobody is logged in.
if (string.IsNullOrEmpty(UserName) | string.IsNullOrEmpty(FirstName) | string.IsNullOrEmpty(sessionId))
{
return(RedirectToPage("/Main_Pages/User Pages/Login"));
}
//Gets the conenction string from the other class.
DBString dB = new DBString();
string ConnectionString = dB.ConString();
SqlConnection conn = new SqlConnection(ConnectionString);
//Opens up a sql connection.
conn.Open();
//Loads Product onto page where serial number matches id. Gets only serialnumber and name.
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = @"SELECT * from Products WHERE SerialNumber =@SNum";
command.Parameters.AddWithValue("@SNum", id);
Console.WriteLine("@The SerialNumber " + id);
//New List to store variables.
Products = new ProductModel();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Products.SerialNumber = reader.GetString(0);
Products.Name = reader.GetString(1);
}
reader.Close();
}
//load stock onto page too. Gets idnumber and amount.
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = @"SELECT * from Stock WHERE SerialNumber =@SNum";
command.Parameters.AddWithValue("@SNum", id);
Console.WriteLine("@The SerialNumber " + id);
Stock = new StockModel();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Stock.StockIdNumber = reader.GetInt32(0);
Stock.Amount = reader.GetInt32(3);
}
reader.Close();
}
return(Page());
}
public IActionResult OnGet(int?id)
{
UserName = HttpContext.Session.GetString(Session1);
FirstName = HttpContext.Session.GetString(Session2);
sessionId = HttpContext.Session.GetString(Session3);
Role = HttpContext.Session.GetString(Session4);
if (string.IsNullOrEmpty(UserName) | string.IsNullOrEmpty(FirstName) | string.IsNullOrEmpty(sessionId) | !(Role == "Admin"))
{
return(RedirectToPage("/Main_Pages/User Pages/Login"));
}
DBString dB = new DBString();
string ConnectionString = dB.ConString();
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
Product = new ProductModel();
Stock = new StockModel();
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = "SELECT * FROM Products WHERE SerialNumber = @SNum";
command.Parameters.AddWithValue("@SNum", id);
Console.WriteLine("@The SerialNumber " + id);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Product.SerialNumber = reader.GetString(0); //getting the first field from the table
Product.Name = reader.GetString(1); //getting the second field from the table
Product.Company = reader.GetString(2); //getting the third field from the table
Product.SalePrice = reader.GetString(3);
Product.Category = reader.GetString(4);
}
reader.Close();
}
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = "SELECT * FROM Stock WHERE SerialNumber = @SNum";
command.Parameters.AddWithValue("@SNum", id);
Console.WriteLine("@The SerialNumber " + id);
SqlDataReader readerr = command.ExecuteReader();
while (readerr.Read())
{
Stock.StockIdNumber = readerr.GetInt32(0); //getting the first field from the table
Stock.SerialIdNumber = readerr.GetString(1); //getting the second field from the table
Stock.PurchasePrice = readerr.GetInt32(2); //getting the third field from the table
Stock.Amount = readerr.GetInt32(3);
}
}
conn.Close();
return(Page());
}
public IActionResult OnPost()
{
if (string.IsNullOrEmpty(user.UserName) | string.IsNullOrEmpty(user.Password))
{
return(Page());
}
DBString dB = new DBString();
string ConnectionString = dB.ConString();
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
Console.WriteLine(user.FirstName);
Console.WriteLine(user.SecondName);
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = @"SELECT UserName AS Uname, FirstName AS Fname, Role As AorE FROM UserTable WHERE UserName = @UNam AND Password = @Pass";
command.Parameters.AddWithValue("@UNam", user.UserName);
command.Parameters.AddWithValue("@Pass", user.Password);
var reader = command.ExecuteReader();
while (reader.Read())
{
user.FirstName = reader.GetString(0);
user.SecondName = reader.GetString(1);
user.Role = reader.GetString(2);
}
}
if (!string.IsNullOrEmpty(user.FirstName))
{
sessionId = HttpContext.Session.Id;
HttpContext.Session.SetString("sessionId", sessionId);
HttpContext.Session.SetString("username", user.FirstName);
HttpContext.Session.SetString("fname", user.FirstName);
HttpContext.Session.SetString("Role", user.Role);
if (user.Role == "Employee")
{
return(RedirectToPage("/Main_Pages/User Pages/WelcomeUser"));
}
else
{
return(RedirectToPage("/Main_Pages/Admin Pages/WelcomeAdmin"));
}
}
else
{
message = "Invalid Username and Password!";
return(Page());
}
}
public IActionResult OnPost()
{
DBString dB = new DBString();
string ConnectionString = dB.ConString();
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
Console.WriteLine("Product SerialNumber : " + Product.SerialNumber);
Console.WriteLine("Product Name : " + Product.Name);
Console.WriteLine("Product Company : " + Product.Company);
Console.WriteLine("Product SalePrice : " + Product.SalePrice);
Console.WriteLine("Product Category : " + Product.Category);
Console.WriteLine("Stock Id Number :" + Stock.StockIdNumber);
Console.WriteLine("Purchase Price :" + Stock.PurchasePrice);
Console.WriteLine("Amount :" + Stock.Amount);
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = "UPDATE Products SET Name = @Name, Company = @Com, SalePrice = @SPri, Category = @Cat WHERE SerialNumber = @SNum";
command.Parameters.AddWithValue("@SNum", Product.SerialNumber);
command.Parameters.AddWithValue("@Name", Product.Name);
command.Parameters.AddWithValue("@Com", Product.Company);
command.Parameters.AddWithValue("@SPri", Product.SalePrice);
command.Parameters.AddWithValue("@Cat", Product.Category);
command.ExecuteNonQuery();
}
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = "UPDATE Stock SET PurchasePrice = @Pri, Amount = @Amn, StockIdNumber = @Sin WHERE SerialNumber = @Snum";
command.Parameters.AddWithValue("@Pri", Stock.PurchasePrice);
command.Parameters.AddWithValue("@Amn", Stock.Amount);
command.Parameters.AddWithValue("@Sin", Stock.StockIdNumber);
command.Parameters.AddWithValue("@Snum", Product.SerialNumber);
command.ExecuteNonQuery();
conn.Close();
}
return(RedirectToPage("/Index"));
}
public IActionResult OnPost()
{
if (!ModelState.IsValid)
{
return(Page());
}
DBString dB = new DBString();
string ConnectionString = dB.ConString();
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = @"INSERT INTO Products (SerialNumber, Name, Company, SalePrice, Category) VALUES (@SNum,@Name,@Con,@SPri,@Cat)";
command.Parameters.AddWithValue("@SNum", Products.SerialNumber);
command.Parameters.AddWithValue("@Name", Products.Name);
command.Parameters.AddWithValue("@Con", Products.Company);
command.Parameters.AddWithValue("@SPri", Products.SalePrice);
command.Parameters.AddWithValue("@Cat", Products.Category);
Console.WriteLine(Products.SerialNumber);
Console.WriteLine(Products.Name);
Console.WriteLine(Products.Company);
Console.WriteLine(Products.Category);
command.ExecuteNonQuery();
command.Parameters.Clear();
command.CommandText = @"INSERT INTO Stock (StockIdNumber, SerialNumber, PurchasePrice, Amount) VALUES (@Sidn,@Sedn,@Ppri,@Amnt)";
command.Parameters.AddWithValue("@Sidn", Stock.StockIdNumber);
command.Parameters.AddWithValue("@Sedn", Products.SerialNumber);
command.Parameters.AddWithValue("@Ppri", Stock.PurchasePrice);
command.Parameters.AddWithValue("@Amnt", Stock.Amount);
Console.WriteLine(Stock.StockIdNumber);
Console.WriteLine(Products.SerialNumber);
Console.WriteLine(Stock.PurchasePrice);
Console.WriteLine(Stock.Amount);
command.ExecuteNonQuery();
}
return(RedirectToPage("/Index"));
}
public void deletePicture(string userr, string FileName, string serialnumber)
{
Console.WriteLine("Record Id : " + userr);
Console.WriteLine("File Name : " + FileName);
DBString dB = new DBString();
string ConnectionString = dB.ConString();
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = "DELETE ImgData WHERE SerialNumber = @Id";
command.Parameters.AddWithValue("@Id", serialnumber);
command.ExecuteNonQuery();
}
conn.Close();
Console.WriteLine(FileName);
string RetrieveImage = Path.Combine(_env.WebRootPath, "Files", FileName);
System.IO.File.Delete(RetrieveImage);
Console.WriteLine("File has been deleted");
}
public IActionResult OnPost()
{
if (!string.IsNullOrEmpty(ImgDetails.FileName) && !string.IsNullOrEmpty(ImgDetails.User))
{
deletePicture(ImgDetails.User, ImgDetails.FileName, Product.SerialNumber);
}
DBString dB = new DBString();
string ConnectionString = dB.ConString();
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = "DELETE Stock WHERE SerialNumber = @SNum";
command.Parameters.AddWithValue("@SNum", Product.SerialNumber);
command.ExecuteNonQuery();
command.CommandText = "DELETE Products WHERE SerialNumber = @SNum";
command.ExecuteNonQuery();
}
conn.Close();
return(RedirectToPage("/Index"));
}
public void OnGet()
{
DBString dB = new DBString();
string ConnectionString = dB.ConString();
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = @"SELECT * FROM Products ";
Products = new List <ProductModel>(); //this object of list is created to populate all records from the table
SqlDataReader reader = command.ExecuteReader(); //SqlDataReader is used to read record from a table
while (reader.Read())
{
ProductModel record = new ProductModel(); //a local var to hold a record temporarily
record.SerialNumber = reader.GetString(0); //getting the first field from the table
record.Name = reader.GetString(1); //getting the second field from the table
record.Company = reader.GetString(2); //getting the third field from the table
record.SalePrice = reader.GetString(3);
record.Category = reader.GetString(4);
Products.Add(record); //adding the single record into the list
}
reader.Close();
}
SqlConnection connn = new SqlConnection(ConnectionString);
connn.Open();
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = @"SELECT * from Stock ";
SqlDataReader reader = command.ExecuteReader(); //SqlDataReader is used to read record from a table
Stock = new List <StockModel>(); //this object of list is created to populate all records from the table
while (reader.Read())
{
StockModel record = new StockModel(); //a local var to hold a record temporarily
record.StockIdNumber = reader.GetInt32(0); //getting the first field from the table
record.SerialIdNumber = reader.GetString(1); //getting the second field from the table
record.PurchasePrice = reader.GetInt32(2); //getting the third field from the table
record.Amount = reader.GetInt32(3);
Stock.Add(record); //adding the single record into the list
}
// Call Close when done reading.
reader.Close();
}
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = @"SELECT * from Products ORDER By SalePrice ";
SqlDataReader reader = command.ExecuteReader(); //SqlDataReader is used to read record from a table
TopProduct = new List <ProductModel>(); //this object of list is created to populate all records from the table
while (reader.Read())
{
ProductModel record = new ProductModel(); //a local var to hold a record temporarily
record.SerialNumber = reader.GetString(0); //getting the first field from the table
record.SalePrice = reader.GetString(3); //getting the third field from the table
TopProduct.Add(record); //adding the single record into the list
}
// Call Close when done reading.
reader.Close();
}
for (int i = 0; i < Stock.Count; i++)
{
; //this object of list is created to populate all records from the table
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.Parameters.AddWithValue("@SNum", Stock[i].SerialIdNumber);
Console.WriteLine("@The SerialNumber " + Stock[i].SerialIdNumber);
//SqlDataReader reader = command.ExecuteReader(); //SqlDataReader is used to read record from a table
command.CommandText = @"SELECT Count (Amount) from Stock";
AmtProduct = (Int32)command.ExecuteScalar();
command.CommandText = @"SELECT AVG(CAST(SalePrice as FLOAT)) from Products";
AvgSalePrice = (double)command.ExecuteScalar();
command.CommandText = @"SELECT AVG(CAST(PurchasePrice as FLOAT)) from Stock";
AvgPurchasePrice = (double)command.ExecuteScalar();
AvgProfit = AvgSalePrice - AvgPurchasePrice;
}
}
}
//public List<string> pricefilter { get; set; } = new List<string> { "50", "100", "250", "500", "100" };
public void OnGet()
{
DBString dB = new DBString();
string ConnectionString = dB.ConString();
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = @"SELECT * From Products";
if (!string.IsNullOrEmpty(Category) && !string.Equals(Category, "All"))
{
command.CommandText += " WHERE Category = @Cat";
command.Parameters.AddWithValue("@Cat", Convert.ToString(Category));
}
SqlDataReader reader = command.ExecuteReader(); //SqlDataReader is used to read record from a table
// command.CommandText = @"SELECT * FROM Products AS Variables ";
Products = new List <ProductModel>(); //this object of list is created to populate all records from the table
while (reader.Read())
{
ProductModel record = new ProductModel();
record.SerialNumber = reader.GetString(0);
record.Name = reader.GetString(1);
record.Company = reader.GetString(2);
record.SalePrice = reader.GetString(3);
record.Category = reader.GetString(4);
Products.Add(record); //adding the single record into the list
}
reader.Close();
}
//using (SqlCommand command = new SqlCommand())
//{
// command.Connection = conn;
// command.CommandText = @"SELECT * From Products";
// if (!string.IsNullOrEmpty(pricef) && !string.Equals(pricef,"All"))
// {
// command.CommandText += " WHERE SalePrice BETWEEN 0 AND " + pricef;
// command.Parameters.AddWithValue("@SPri", Convert.ToString(pricef));
// }
// SqlDataReader reader = command.ExecuteReader(); //SqlDataReader is used to read record from a table
// command.CommandText = @"SELECT * FROM Products ";
// Products = new List<ProductModel>(); //this object of list is created to populate all records from the table
// while (reader.Read())
// {
// ProductModel record = new ProductModel();
// record.SerialNumber = reader.GetString(0);
// record.Name = reader.GetString(1);
// record.Company = reader.GetString(2);
// record.SalePrice = reader.GetString(3);
// record.Category = reader.GetString(4);
// Products.Add(record); //adding the single record into the list
// }
// reader.Close();
//}
//using (SqlCommand command = new SqlCommand())
//{
// command.Connection = conn;
// command.CommandText = @"SELECT SerialNumber, Company, SalePrice FROM Products UNION SELECT StockIdNumber, PurchasePrice, Amount FROM Stock ORDER By SerialNumber ";
// SqlDataReader reader = command.ExecuteReader(); //SqlDataReader is used to read record from a table
// Products = new List<ProductModel>(); //this object of list is created to populate all records from the table
// while (reader.Read())
// {
// //ProductModel record = new ProductModel();
// //record.SerialNumber = reader.GetInt32(0).ToString();
// //record.Name = reader.GetInt32(1).ToString();
// //record.Company = reader.GetString(2);
// //record.SalePrice = reader.GetInt32(3).ToString();
// //record.Category = reader.GetString(4);
// //Products.Add(record); //adding the single record into the list
// }
// reader.Close();
//}
SqlConnection connn = new SqlConnection(ConnectionString);
connn.Open();
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = @"SELECT * from Stock ORDER BY SerialNumber ";
SqlDataReader reader = command.ExecuteReader(); //SqlDataReader is used to read record from a table
Stock = new List <StockModel>(); //this object of list is created to populate all records from the table
while (reader.Read())
{
StockModel record = new StockModel(); //a local var to hold a record temporarily
record.StockIdNumber = reader.GetInt32(0); //getting the first field from the table
record.SerialIdNumber = reader.GetString(1); //getting the second field from the table
record.PurchasePrice = reader.GetInt32(2); //getting the third field from the table
record.Amount = reader.GetInt32(3);
Stock.Add(record); //adding the single record into the list
}
// Call Close when done reading.
reader.Close();
}
}