//Store all SO values
public List <so> ListSO()
{
projectEntities db = new projectEntities();
List <so> SO = db.soes.ToList();
return(SO);
}
//List all id of ListAT
public int[] ListATID()
{
using (projectEntities db = new projectEntities())
{
return(db.ats.Join(db.copiatts, x => x.at_id, y => y.at_id, (x, y) => new { x, y }).OrderBy(xy => xy.y.copiatt_id).Where(xy => xy.x.at_id != 0 && xy.y.at_id != 0).Select(xy => xy.x.at_id).ToArray());
}
}
private void buttonPrint_Click(object sender, EventArgs e)
{
if (!String.IsNullOrEmpty(textBoxInvoiceID.Text))
{
projectEntities project = new projectEntities();
int id = Convert.ToInt32(textBoxInvoiceID.Text);
string txtFilePath = @"../../txt/" + id + ".txt";
using (StreamWriter writer = File.CreateText(txtFilePath))
{
var printInvoice = project.invoices.Find(id);
writer.WriteLine(printInvoice.InvoiceID
+ "|" + printInvoice.TotalPrice
+ "|" + printInvoice.PaymentDate
+ "|" + printInvoice.TypeOfPayment
+ "|" + printInvoice.IsPaid
);
MessageBox.Show("Print Successful ( go to txt folder)");
}
}
else
{
MessageBox.Show("Select a row to print.");
}
}
private void DisplayBookInformation()
{
projectEntities project = new projectEntities();
var Display = from bo in project.books
join au in project.authors on bo.AuthorID equals au.AuthorID
join pu in project.publishers on bo.PublishID equals pu.PublisherID
join ca in project.categories on bo.CategoryID equals ca.CategoryID
select new
{
bo.BookID,
bo.ISBN,
bo.Title,
bo.UnitPrice,
bo.YearPublished,
bo.QOH,
au.AuthorID,
pu.PublisherID,
pu.Name,
ca.CategoryID,
ca.CategoryName
};
dataGridViewDisplayBook.DataSource = Display.ToList();
}
private void buttonDelete_Click(object sender, EventArgs e)
{
if (ValidateEmptyTextBox(1))
{
if (MessageBox.Show("Do You Want to Delete this Record", "Delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
projectEntities project = new projectEntities();
int invoiceID = Convert.ToInt32(textBoxInvoiceID.Text);
var checkInvoice = project.invoices.Find(invoiceID);
if (checkInvoice != null)
{
project.invoices.Remove(checkInvoice);
project.SaveChanges();
this.DisplayData();
MessageBox.Show("Record Is Updated.");
this.ClearTextBox();
}
}
}
else
{
MessageBox.Show("Please Select a Row For Delete Operation.");
this.ClearTextBox();
}
}
private void buttonCustomerOrderAdd_Click(object sender, EventArgs e)
{
if (ValidateEmptyTextBox(1))
{
try
{
projectEntities project = new projectEntities();
customerOrder cOrder = new customerOrder();
cOrder.CustomerID = Convert.ToInt32(textBoxCustomerID.Text);
cOrder.OrderDate = orderDate.Value.Date;
project.customerOrders.Add(cOrder);
project.SaveChanges();
this.DisplayCustomerOrder();
MessageBox.Show("Recored Inserted..");
this.ClearTextBoxCO();
}
catch (Exception ex)
{
MessageBox.Show("Recored Is Not Inserted..\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
//this.ClearTextBoxCO();
}
}
else
{
MessageBox.Show("Please Enter Values. ", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void buttonCustomerOrderDelete_Click(object sender, EventArgs e)
{
if (ValidateEmptyTextBox(1) && !string.IsNullOrEmpty(textBoxCustomerID.Text))
{
if (MessageBox.Show("Do You Want to Delete this Record", "Delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
try
{
projectEntities project = new projectEntities();
int customerOrderID = Convert.ToInt32(textBoxCustomerOrderID1.Text);
// var = customerOrder
var checkCustomerOrder = project.customerOrders.Find(customerOrderID);
if (checkCustomerOrder != null)
{
project.customerOrders.Remove(checkCustomerOrder);
project.SaveChanges();
this.DisplayCustomerOrder();
MessageBox.Show("Deletion Completed.");
this.ClearTextBoxCO();
}
}
catch (Exception ex)
{
MessageBox.Show("Deletion Is Not Completed.\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
this.ClearTextBoxCO();
}
}
}
else
{
MessageBox.Show("Please Select a Row For Delete Operation.");
this.ClearTextBoxCO();
}
}
public ActionResult UpdateData(HomeViewModel dbmodel)
{
try
{
projectEntities db = new projectEntities();
copiatt data = db.copiatts.SingleOrDefault(m => m.pi_id == dbmodel.pi_id);
if (data != null)
{
data.at_id = dbmodel.at_id;
data.course_id = dbmodel.course_id;
data.target = float.Parse(dbmodel.target);
db.SaveChanges();
}
}
catch (Exception ex)
{
throw ex;
}
//for message prompt
TempData["flag"] = 1;
TempData["MessageTitle"] = "Edit Data";
TempData["MessagePrompt"] = "Assessment Plan has been successfully updated";
return(RedirectToAction("AdminPage", "Home"));
}
//List target per SO and PI
public float[] ListTarget()
{
using (projectEntities db = new projectEntities())
{
return(db.copiatts.Where(x => x.target != 0).Select(x => x.target).ToArray());
}
}
//List courses per SO and PI
public string[] ListCourse()
{
using (projectEntities db = new projectEntities())
{
return(db.courses.Join(db.copiatts, x => x.course_id, y => y.course_id, (x, y) => new { x, y }).OrderBy(xy => xy.y.copiatt_id).Where(xy => xy.x.course_id != 0 && xy.y.course_id != 0).Select(xy => xy.x.coursename).ToArray());
}
}
//List PI per SO
public string[] ListPI()
{
using (projectEntities db = new projectEntities())
{
return(db.pis.Where(x => x.pi_id != 0).Select(x => x.pi_desc).ToArray());
}
}
private void buttonAdd_Click(object sender, EventArgs e)
{
if (ValidateEmptyTextBox(1))
{
try
{
projectEntities project = new projectEntities();
invoice invoice = new invoice();
invoice.InvoiceID = Convert.ToInt32(textBoxInvoiceID.Text);
invoice.TotalPrice = Convert.ToDouble(textBoxTotalPrice.Text);
invoice.TypeOfPayment = Convert.ToString(comboBoxTypeOfPayment.SelectedItem);
invoice.PaymentDate = paymentDate.Value.Date;
invoice.IsPaid = Convert.ToString(comboBoxIsPaid.SelectedItem);
project.invoices.Add(invoice);
project.SaveChanges();
this.DisplayData();
MessageBox.Show("Recored Inserted..");
this.ClearTextBox();
}
catch (Exception ex)
{
MessageBox.Show("Recored Is Not Inserted..\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
//this.ClearTextBoxCO();
}
}
else
{
MessageBox.Show("Please Enter Values. ", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void DisplayCustomerOrder()
{
projectEntities project = new projectEntities();
// Display -> customerOrder using Lambda
//var CODisplay = project.customerOrders.Select(x => new { x.CustomerOrderID, x.CustomerID, x.OrderDate });
// Display -> customerOrder and customer using Lambda
var CODisplay = project.customerOrders.Join(
project.customers, // Target Table Name join __X__ on _____ = _____
x1 => x1.CustomerID, // Before Condition of ON Cause join _____ on __X__ = _____
x2 => x2.CustomerID, // After Condition of ON Cause join _____ on _____ = __X__
(x1, x2) => new // Select
{
x1.CustomerOrderID,
x1.CustomerID,
x1.OrderDate,
x2.CustomerName
});
var displayCustomer = project.customers.Select(x => x);
// Display -> customerOrder using LINQ
//var CODisplay = from co in project.customerOrders
// select co;
// Display -> customerOrder and customer using LINQ
//var CODisplay = from co in project.customerOrders
// join cu in project.customers on co.CustomerID equals cu.CustomerID
// select new { co.CustomerOrderID,co.CustomerID,co.OrderDate,
// cu.CustomerName };
dataGridViewCustomerOrder.DataSource = CODisplay.ToList();
dataGridViewCOSearch.DataSource = displayCustomer.ToList();
}
//Store all AT available
public List <at> ListAT()
{
projectEntities db = new projectEntities();
List <at> AT = db.ats.ToList();
return(AT);
}
//Store all courses available
public List <course> ListCourse()
{
projectEntities db = new projectEntities();
List <course> CO = db.courses.ToList();
return(CO);
}
//Get all cases depending on the current AT
public string[] ATcase()
{
using (projectEntities db = new projectEntities())
{
int currAT = ViewBag.id;
return(db.atcases.Where(x => x.at_id == currAT).Select(x => x.cases).ToArray());
}
}
// GET: common/Grounds
public ActionResult Index()
{
projectEntities db = new projectEntities();
ViewBag.CategoryId = new SelectList(objBs.categoryBs.GetAll().ToList(), "CategoryId", "CategoryName");
ViewBag.LocationId = new SelectList(objBs.locationBs.GetAll().ToList(), "LocationId", "LocationName");
return(View());
}
//get all logs in db
public List <FileViewModels> ListLogs()
{
using (projectEntities db = new projectEntities())
{
return(db.outcomes.Join(db.users, x => x.acc_id, y => y.acc_id, (x, y) => new { x, y }).OrderByDescending(xy => xy.x.pid).Where(xy => xy.x.acc_id != 0 && xy.y.acc_id != 0).Select(xy => new FileViewModels {
pid = xy.x.pid, acc_id = xy.x.acc_id, fname = xy.y.FName, filename = xy.x.filename, lname = xy.y.LName, cdate = xy.x.cdate
}).ToList());
}
}
//Store PI depending on SO selected
//Uses PartialView
public ActionResult ListPI(int so_id)
{
projectEntities db = new projectEntities();
List <pi> PI = db.pis.Where(x => x.so_id == so_id).ToList();
ViewBag.PIOptions = new SelectList(PI, "pi_id", "pi_desc");
return(PartialView("PIOptionsPartial"));
}
//View Logs Page
public ActionResult Logs()
{
projectEntities db = new projectEntities();
List <FileViewModels> listFiles = ListLogs();
ViewBag.listlogs = listFiles;
ViewBag.Title = "View Logs";
return(View());
}
public ActionResult UpdatePass(AccountViewModel userModel)
{
try
{
projectEntities db = new projectEntities();
//Check if session still exists
if (Session["accID"] == null)
{
return(RedirectToAction("Login", "Account"));
}
int id = (int)Session["accID"];
string pw = (string)Session["pw"];
//Check for the current session account id in db
user data = db.users.SingleOrDefault(m => m.acc_id == id);
if (data != null)
{
if (pw != userModel.password)
{
userModel.PassErrorMsg = "Incorrect Password, Try Again.";
return(View("ChangePass", userModel));
}
else if (pw == userModel.newpassword)
{
userModel.PassErrorMsg = "Password is the same, Try Again.";
return(View("ChangePass", userModel));
}
else
{
//Update user password in db and current session
data.password = userModel.newpassword;
db.SaveChanges();
Session["pw"] = userModel.newpassword;
}
}
}
catch (Exception ex)
{
throw ex;
}
//For message prompt purposes
TempData["flag"] = 1;
TempData["MessageTitle"] = "Change Password";
TempData["MessagePrompt"] = "Password has been successfully changed";
//Redirection Check
if ((int)Session["type"] != 0)
{
return(RedirectToAction("MainPage", "Home"));
}
return(RedirectToAction("AdminPage", "Home"));
}
private void buttonBookOrderDelete_Click(object sender, EventArgs e)
{
if (ValidateEmptyTextBox(3))
{
if (MessageBox.Show("Do You Want to Delete this Record", "Delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
try
{
projectEntities project = new projectEntities();
var checkBookOrder = project.bookOrders.Where(x => x.CustomerOrderID == cOrderIDUpdate &&
x.BookID == bookIDUpdate && x.OrderQuantity == orderQuantityUpdate).SingleOrDefault();
if (checkBookOrder != null)
{
project.bookOrders.Remove(checkBookOrder);
// add Book quantity to QOH(book) when delete.
var checkBook = project.books.Find(bookIDUpdate);
checkBook.QOH = checkBook.QOH + orderQuantityUpdate;
// remove price of that book order in invoice table
var checkInvoice = project.invoices.Find(cOrderIDUpdate);
checkInvoice.TotalPrice = checkInvoice.TotalPrice - (checkBook.UnitPrice * orderQuantityUpdate);
project.SaveChanges();
MessageBox.Show("Deletion Completed.");
this.DisplayBookOrder();
this.ClearTextBoxBO();
}
else
{
MessageBox.Show("Deletion Is Not Completed.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
this.ClearTextBoxBO();
}
}
catch (Exception ex)
{
MessageBox.Show("Deletion Is Not Completed.\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
this.ClearTextBoxCO();
}
}
}
else
{
MessageBox.Show("Please Select a Row For Delete Operation.");
this.ClearTextBoxCO();
}
}
private void DisplayData()
{
projectEntities project = new projectEntities();
var display = project.invoices.Select(x => new
{
x.InvoiceID,
x.TotalPrice,
x.TypeOfPayment,
x.PaymentDate,
x.IsPaid
});
dataGridViewDisplay.DataSource = display.ToList();
}
//DeleteUser page
//return listUser that stores all users
public ActionResult DeleteUser(string searching)
{
projectEntities db = new projectEntities();
//for searching capabilities in delete page
List <HomeViewModel> listUser = db.users.Where(x => x.FName.StartsWith(searching) && x.type_id == 1 ||
x.MName.StartsWith(searching) && x.type_id == 1 ||
x.LName.StartsWith(searching) && x.type_id == 1 ||
x.username.StartsWith(searching) && x.type_id == 1 ||
searching == null && x.type_id == 1).Select(x => new HomeViewModel {
FName = x.FName, MName = x.MName, LName = x.LName, username = x.username, acc_id = x.acc_id
}).ToList();
ViewBag.UserList = listUser;
ViewBag.Title = "Delete Account";
return(View());
}
public ActionResult GetChartData()
{
List <record_estimate> data = new List <record_estimate>();
var dt = new VisualizationDataTable();
var chart = new ChartViewModel
{
Title = "Events",
Subtitle = "per date",
DataTable = dt
};
//Here MyDatabaseEntities is our dbContext
using (projectEntities dc = new projectEntities())
{
data = dc.record_estimate.ToList();
}
var sortbytime = data.OrderByDescending(x => x.Time).ToList();
dt.AddColumn("Date", "string");
dt.AddColumn("實際蒸汽壓力(kgf/cm2G)", "number");
dt.AddColumn("預測蒸汽壓力(kgf/cm2G)", "number");
int counter = 0;
foreach (var item in sortbytime)
{
dt.NewRow(item.Time, item.ActualPressure, item.EstimatePressure);
counter++;
if (counter >= 12)
{
break;
}
}
return(Content(JsonConvert.SerializeObject(chart), "application/json"));
}
//Returns generated files with respect to account id (ex. user 1 can't see the files generated by user 2)
//Searching capabilities for Download page.
public ActionResult Downloads(string searching)
{
projectEntities db = new projectEntities();
if (Session["accID"] == null)
{
return(RedirectToAction("Login", "Account"));
}
int accid = (int)Session["accID"];
List <FileViewModels> listFiles = db.outcomes.Where(x => x.filename.Contains(searching) && x.acc_id == accid || searching == null && x.acc_id == accid).OrderByDescending(x => x.pid).Select(x => new FileViewModels {
acc_id = x.acc_id, filename = x.filename, cdate = x.cdate
}).ToList();
ViewBag.filelist = listFiles;
ViewBag.Title = "Downloads";
return(View());
}
//Update
private void buttonUpdate_Click(object sender, EventArgs e)
{
if (ValidateEmptyTextBox(1) && !string.IsNullOrEmpty(textBoxBookID.Text))
{
if (MessageBox.Show("Do You Want to Update this Record", "Update", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
try
{
projectEntities project = new projectEntities();
int bookid = Convert.ToInt32(textBoxBookID.Text);
var checkBookID = project.books.Find(bookid);
if (checkBookID != null)
{
checkBookID.ISBN = textBoxISBN.Text;
checkBookID.Title = textBoxTitle.Text;
checkBookID.UnitPrice = Convert.ToDouble(textBoxPrice.Text);
checkBookID.YearPublished = publishYear.Value.Date;
checkBookID.QOH = Convert.ToInt32(textBoxQoh.Text);
checkBookID.CategoryID = Convert.ToInt32(textBoxCategoryID.Text);
checkBookID.AuthorID = Convert.ToInt32(textBoxAuthorID.Text);
checkBookID.PublishID = Convert.ToInt32(textBoxPublishID.Text);
project.SaveChanges();
this.DisplayBookInformation();
MessageBox.Show("Record Is Updated.");
this.ClearTextBox();
}
}
catch (Exception ex)
{
MessageBox.Show("Recored Is Not Updated..\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
else
{
MessageBox.Show("Please Select a Row For Update Operation.");
}
}
public ActionResult AddData(HomeViewModel model)
{
try
{
projectEntities db = new projectEntities();
user User = new user();
//Get all inputted information and save to db
User.type_id = model.SelectedType;
User.FName = model.FName;
User.MName = model.MName;
User.LName = model.LName;
User.username = model.username;
User.password = model.password;
db.users.Add(User);
db.SaveChanges();
string filepath = Server.MapPath("~/UserFiles/" + User.acc_id);
if (Directory.Exists(filepath))
{
}
else
{
//Add file folder for new user
DirectoryInfo di = Directory.CreateDirectory(filepath);
}
}
catch (Exception ex)
{
throw ex;
}
//for message prompt purposes
TempData["flag"] = 1;
TempData["MessageTitle"] = "Create Account";
TempData["MessagePrompt"] = "Account has been created";
return(RedirectToAction("AdminPage"));
}
//check identical usernames, for add user
public bool isAvailable(string uname)
{
projectEntities db = new projectEntities();
List <HomeViewModel> usernameList = db.users.Select(x => new HomeViewModel {
username = x.username
}).ToList();
var userinput = (from x in usernameList where x.username == uname select new { uname }).FirstOrDefault();
bool status;
if (userinput != null)
{
status = false;
}
else
{
status = true;
}
return(status);
}
public ActionResult Authenticate(AccountViewModel userModel)
{
ViewBag.Title = "Login Attempt";
using (projectEntities db = new projectEntities())
{
//Check db if username and password both exists
var userDetails = db.users.Where(x => x.username == userModel.username && x.password == userModel.password).FirstOrDefault();
if (userDetails == null)
{
userModel.LogErrorMsg = "Incorrect Username and/or Password.";
return(View("Login", userModel));
}
else
{
//Save all the user details in the current session
Session["accID"] = userDetails.acc_id;
Session["user"] = userDetails.username;
Session["pw"] = userDetails.password;
Session["type"] = userDetails.type_id;
Session["fn"] = userDetails.FName;
Session["mn"] = userDetails.MName;
Session["ln"] = userDetails.LName;
//Check Redirection page (0=admin, 1=user)
if (userDetails.type_id == 0)
{
return(RedirectToAction("AdminPage", "Home"));
}
else
{
Session["test"] = 1;
return(RedirectToAction("MainPage", "Home"));
}
}
}
}
