public IActionResult DeleteBooking(SRBooking uBook)
{
string userId = HttpContext.User.FindFirst(ClaimTypes.NameIdentifier).Value;
if (DBUtl.ExecSQL(@"DELETE FROM SRBooking WHERE Id ={0} AND BookedBy={1}", uBook.Id, userId) == 1)
{
TempData["Msg"] = $"Booking{uBook.Id} deleted";
}
else
{
TempData["Msg"] = DBUtl.DB_Message;
}
return(RedirectToAction("Index"));
}
private void updatearchive()
{
var list = DBUtl.GetList <Exercise>("SELECT * FROM Exercise");
DateTime currentdate = DateTime.Now;
foreach (var a in list)
{
DateTime enddate = a.end_date;
if (enddate < currentdate)
{
var update = "UPDATE Exercise SET archive = '{0}' WHERE Exercise_id = '{1}'";
DBUtl.ExecSQL(update, true, a.Exercise_id);
}
}
}
public IActionResult ResetPassword(ResetPassword rp)
{
if (!ModelState.IsValid)
{
ViewData["Message"] = "Invalid Input";
ViewData["MsgType"] = "warning";
return(View("RPwd"));
}
else
{
string email = rp.Email.ToString();
string password = rp.UserPw.ToString();
string select = @"SELECT * FROM Users WHERE Email='{0}'";
DataTable dt = DBUtl.GetTable(select, email);
if (dt.Rows.Count == 1)
{
string uname = dt.Rows[0]["Username"].ToString();
string cfmEmail = dt.Rows[0]["Email"].ToString();
if (email.Equals(cfmEmail))
{
string update = @"UPDATE Users SET Password=HASHBYTES('SHA1','{1}') WHERE Username='******'";
int res = DBUtl.ExecSQL(update, uname, password, cfmEmail);
if (res == 1)
{
ViewData["Message"] = "Password has been reset successfully.";
ViewData["MsgType"] = "success";
return(View("RPwdCfm"));
}
else
{
ViewData["Message"] = "Password reset unsuccessful.";
ViewData["MsgType"] = "warning";
return(View("RPwd"));
}
}
else
{
ViewData["Message"] = "Email cannot be verified.";
ViewData["MsgType"] = "danger";
return(View("RPwd"));
}
}
else
{
return(View("RPwd"));
}
}
}
public IActionResult ChangeUsername(UserUpdate userUpdate)
{
var userid = User.FindFirst(ClaimTypes.NameIdentifier).Value;
//int num_affected = _dbContext.Database.ExecuteSqlInterpolated($"UPDATE MesahUser SET UserId = {userUpdate.NewUsername} WHERE UserId = {userid}");
string sql = @"UPDATE MesahUser SET UserId = '{1}' WHERE UserId= '{0}'";
if (DBUtl.ExecSQL(sql, userid, userUpdate.NewUsername) == 1)
{
return(RedirectToAction("Login"));
}
else
{
ViewData["Msg"] = "Failed to update username!";
return(View());
}
}
//public IActionResult CleanMissedQueue()
//{
// return View();
//}
public IActionResult CleanMedicineBatch()
{
string sql = @"DELETE FROM medicine;";
if (DBUtl.ExecSQL(sql) == 1)
{
TempData["Message"] = "Reset Medicine Inventory Successful";
TempData["MsgType"] = "success";
}
else
{
TempData["Message"] = DBUtl.DB_Message;
TempData["MsgType"] = "danger";
}
return(RedirectToAction("Index"));
}
public IActionResult Create(Candidate cdd,
IFormFile photo)
{
if (!IsValidCandidate(cdd))
{
return(View(cdd));
}
else if (photo == null)
{
ViewData["Msg"] = "Please Upload a Photo";
return(View(cdd));
}
else
{
string sql = @"INSERT Candidate(RegNo, Name,
Gender, Height,
BirthDate, Race,
Clearance, PicFile)
VALUES ({0},'{1}','{2}',{3},'{4}',
'{5}','{6}','{7}')";
cdd.PicFile = Path.GetFileName(photo.FileName);
if (DBUtl.ExecSQL(sql,
cdd.RegNo, cdd.Name, cdd.Gender, cdd.Height,
String.Format("{0:yyyy-MM-dd}", cdd.BirthDate),
cdd.Race, cdd.Clearance, cdd.PicFile) != 1)
{
// Database Insert Error
ViewData["Msg"] = DBUtl.DB_Message;
return(View(cdd));
}
else
{
// Database Insert Successful
string fname = "candidates/" + cdd.PicFile;
if (UploadFile(photo, fname))
{
// File Upload Successful
return(RedirectToAction("Index"));
}
else
{
ViewData["Msg"] = "File Upload Error";
return(View(cdd));
}
}
}
}
public void updateLOA()
{
var list = DBUtl.GetList <Users>("SELECT * FROM Users");
DateTime currentdate = DateTime.Now;
foreach (var a in list)
{
if (a.loa_start_date != null)
{
if (a.loa_end_date == currentdate)
{
var update = "UPDATE Users SET loa_start_date = '{0}', deployed_status = 'Standby' WHERE User_id = '{1}'";
DBUtl.ExecSQL(update, null, a.User_id);
}
}
}
}
private void updatearchive()
{
var list = DBUtl.GetList <Stocktaking>("Select * from Stocktaking");
DateTime firstdate = DateTime.Now;
foreach (var a in list)
{
DateTime seconddate = a.date_created;
String diff = (firstdate - seconddate).TotalDays.ToString();
double archivable = Double.Parse(diff);
if (archivable > 30)
{
var update = "Update Stocktaking Set archive = '{0}' Where Stocktaking_id = '{1}'";
DBUtl.ExecSQL(update, true, a.Stocktaking_id);
}
}
}
public IActionResult Add(Pokedex poke, IFormFile picture)
{
if (!IsValidPokedex(poke))
{
return(View(poke));
}
else if (picture == null)
{
ViewData["Msg"] = "Please Upload a Picture";
return(View(poke));
}
else
{
string sql = @"INSERT Pokedex(Id, Name, Type1, Type2,
Attack, Defence, Stamina)
VALUES ({0},'{1}','{2}','{3}',{4},{5},{6})";
int rowsAffected = 0;
//TODO: P03 Task 5 - Call DBUtl.ExecSQL to INSERT record to Database
// rowsAffected = DBUtl.ExecSQL(.........
rowsAffected = DBUtl.ExecSQL(sql, poke.Id, poke.Name, poke.Type1, poke.Type2, poke.Attack, poke.Defence, poke.Stamina);
if (rowsAffected != 1)
{
// Database Insert Error
ViewData["Msg"] = DBUtl.DB_Message;
return(View(poke));
}
else
{
// Database Insert Successful
string fname = $"images/{poke.Id}-200.png";
if (UploadFile(picture, fname))
{
// File Upload Successful
return(RedirectToAction("Index"));
}
else
{
ViewData["Msg"] = "File Upload Error";
return(View(poke));
}
}
}
}
public IActionResult ResetPW(ResetPW reset)
{
if (!ModelState.IsValid)
{
ViewData["Message"] = "Invalid Input";
ViewData["MsgType"] = "warning";
return(View("ResetPW"));
}
else
{
//string UserName = reset.UserName.ToString();
string UserName = TempData["un"].ToString();
string password = reset.User_PW_New.ToString();
string cfmpassword = reset.ConfirmPasswordNew.ToString();
string sql = @"SELECT * FROM AppUser WHERE UserName='******'";
string select = String.Format(sql, UserName);
DataTable dt = DBUtl.GetTable(select);
if (password.Equals(cfmpassword))
{
string update = @"UPDATE AppUser SET User_PW = HASHBYTES('SHA1','{1}') WHERE UserName = '******'";
int res = DBUtl.ExecSQL(update, UserName, password);
if (res == 1)
{
ViewData["Message"] = "Password reset successful!";
ViewData["MsgType"] = "success";
return(View("ResetPWActivate"));
}
else
{
ViewData["Message"] = "Password reset unsuccessful.";
ViewData["MsgType"] = "warning";
return(View("ResetPW"));
}
}
else
{
ViewData["Message"] = "Username cannot be verified.";
ViewData["MsgType"] = "danger";
}
return(View("ResetPW"));
}
}
private void updateMaint()
{
var list = DBUtl.GetList <Maintenance>("SELECT * FROM Maintenance WHERE maint_type = '{0}'", true);
DateTime currentdate = DateTime.Now;
foreach (var a in list)
{
DateTime enddate = a.End_date;
if (enddate < currentdate)
{
var updateEq = "UPDATE Equipment SET Status = 'Available' WHERE Status = 'Maintenance' AND Serial_no = '{0}' AND m_end_date < '{1}'";
DBUtl.ExecSQL(updateEq, a.Serial_no, currentdate);
var update = "UPDATE Maintenance SET archive = '{0}' WHERE End_date < '{1}'";
DBUtl.ExecSQL(update, true, currentdate);
}
}
}
public IActionResult Delete(string id)
{
string delete = "DELETE FROM UserRegister WHERE UserId='{0}'";
int res = DBUtl.ExecSQL(delete, id);
if (res == 1)
{
TempData["Message"] = "User Record Deleted";
TempData["MsgType"] = "success";
}
else
{
TempData["Message"] = DBUtl.DB_Message;
TempData["MsgType"] = "danger";
}
return(RedirectToAction("Users"));
}
public void updateStatus()
{
var loanList = DBUtl.GetList <Exercise>(@"SELECT * FROM Exercise E
INNER JOIN Users U ON E.nric = U.nric
INNER JOIN Package P ON E.Package_id = P.Package_id
WHERE E.archive = 0
AND status != 'Returned'");
foreach (var a in loanList)
{
if (a.assigned_status == false)
{
string newStatus = Status(a);
string sqlstatement = "UPDATE Exercise SET status = '{0}', assigned_status = '{1}' WHERE Exercise_id = '{2}'";
var abc = DBUtl.ExecSQL(sqlstatement, newStatus, true, a.Exercise_id);
}
}
}
public IActionResult Register(User usr)
{
if (!ModelState.IsValid)
{
ViewData["Message"] = "Invalid Input";
ViewData["MsgType"] = "warning";
return(View("RegisterUser"));
}
else
{
string insert = @"INSERT INTO User(Username, Password, FullName, Email, UserRole) VALUES('{0}', HASHBYTES('SHA1', '{1}', '{2}', '{3}', 'Startup')";
if (DBUtl.ExecSQL(insert, usr.Username, usr.Password, usr.FullName, usr.Email) == 1)
{
string template = @"Hi {0}, <br/><br/>
Welcome to StartUp Accelerator!
Your username is <b>{1}</b> and password is <b>{2}</b>.
<br/><br/>Admin";
string title = "Registration Successful - Welcome";
string message = String.Format(template, usr.FullName, usr.Username, usr.Password);
string result = "";
bool outcome = false;
outcome = EmailUtl.SendEmail(usr.Email, title, message, out result);
if (outcome)
{
ViewData["Message"] = "User Successfully Registered";
ViewData["MsgType"] = "success";
}
else
{
ViewData["Message"] = result;
ViewData["MsgType"] = "warning";
}
}
else
{
ViewData["Message"] = DBUtl.DB_Message;
ViewData["MsgType"] = "danger";
}
return(View("RegisterUser"));
}
}
public IActionResult AddDish(Dish newDish)
{
if (ModelState.IsValid)
{
if (DBUtl.ExecSQL(@"INSERT INTO Dish (Id, Name, Price, Recommended, Availability, CuisineId) VALUES
({0}, '{1}', {2}, {3}, {4}, {5})", newDish.Id, newDish.Name, newDish.Price, newDish.Recommended, newDish.Availability, newDish.CuisineId) == 1)
{
TempData["Msg"] = "New dish added.";
}
else
{
TempData["Msg"] = "Failed to add new dish" + DBUtl.DB_Message;
}
}
else
{
TempData["Msg"] = "Invalid information entered!";
}
return(RedirectToAction("Index"));
}
public IActionResult ChangePassword(PasswordUpdate pw)
{
var userid = User.FindFirst(ClaimTypes.NameIdentifier).Value;
//var npw_bytes = System.Text.Encoding.ASCII.GetBytes(pw.NewPassword);
//var cpw_bytes = System.Text.Encoding.ASCII.GetBytes(pw.CurrentPassword);
//if (_dbContext.Database.ExecuteSqlInterpolated($"UPDATE MesahUser SET UserPw = HASHBYTES('SHA1', {npw_bytes}) WHERE UserId={userid} AND UserPw = HASHBYTES('SHA1', {cpw_bytes})") == 1)
string sql = @"UPDATE MesahUser
SET UserPw = HASHBYTES('SHA1', '{1}') WHERE UserId= '{0}' AND UserPw = HASHBYTES('SHA1', '{2}')";
if (DBUtl.ExecSQL(sql, userid, pw.NewPassword, pw.CurrentPassword) == 1)
{
ViewData["Msg"] = "Password Successfully Updated!";
}
else
{
ViewData["Msg"] = "Failed to update password!";
}
return(View());
}
public IActionResult EditProfile(MesahUser mesah)
{
string userid = User.FindFirst(ClaimTypes.NameIdentifier).Value;
string sql = @"UPDATE MesahUser
SET FullName ='{1}', Email ='{2}',
Address = '{3}', PostalCode = '{4}', Phone ='{5}'
WHERE UserId = '{0}'";
if (DBUtl.ExecSQL(sql, userid, mesah.FullName, mesah.Email, mesah.Address, mesah.PostalCode, mesah.Phone) == 1)
{
ViewData["Message"] = "Profile Updated";
ViewData["MsgType"] = "success";
}
else
{
ViewData["Message"] = DBUtl.DB_Message;
ViewData["MsgType"] = "danger";
}
return(View("EditProfile"));
}
public IActionResult EditUser(string id, MesahUser mesah)
{
string userid = User.FindFirst(ClaimTypes.NameIdentifier).Value;
string sql = @"UPDATE MesahUser
SET FullName ='{1}', UserRole ='{2}',
Email = '{3}', Phone ='{4}'
WHERE UserId = '{0}'";
if (DBUtl.ExecSQL(sql, id, mesah.FullName, mesah.UserRole, mesah.Email, mesah.Phone) == 1)
{
ViewData["Message"] = "Profile Updated";
ViewData["MsgType"] = "success";
}
else
{
ViewData["Message"] = DBUtl.DB_Message;
ViewData["MsgType"] = "danger";
}
return(RedirectToAction("ShowUsers"));
}
public IActionResult Update(Performance perform)
{
// TODO: L11 Task 4 : Complete HttpPost Update action
// Check the ModelState
// If not valid, display the message "Invalid Input" in the same View
// Otherwise,
// Write SQL Update statement
// Execute the statement with model's properties
// Check for success
// If success, redirect to the Index page with "Performance Updated"
// Otherwise, redirect to the Index page with db error message
if (!ModelState.IsValid)
{
ViewData["Message"] = "Invalid Input";
ViewData["Msgtype"] = "warning";
return(View("Update"));
}
else
{
String update = @"UPDATE Performance SET Title='{1}', Artist='{2}', PerformDT='{3:yyyy-MM-DD HH:mm}', Duration={4}, Price={5}, Chamber='{6}' WHERE Pid={0}";
int res = DBUtl.ExecSQL(update, perform.Pid, perform.Title, perform.Artist, perform.PerformDT, perform.Duration, perform.Price, perform.Chamber);
if (res == 1)
{
TempData["Message"] = "Performance Updated";
TempData["Msgtype"] = "success";
}
else
{
TempData["Message"] = DBUtl.DB_Message;
TempData["Msgtype"] = "danger";
}
return(RedirectToAction("Index"));
}
return(null); // Obviously Wrong
}
public IActionResult AddBooking(Booking newBook)
{
if (ModelState.IsValid)
{
if (DBUtl.ExecSQL(@"INSERT INTO Booking (NRIC, OwnerName, PetName, PetTypeId, CheckInDate, Days, FeedFreq, FTCanned, FTDry, FTSoft)
VALUES ('{0}', '{1}', '{2}', {3}, '{4}', {5}, '{6}', '{7}', '{8}', '{9}')",
newBook.NRIC, newBook.OwnerName, newBook.PetName, newBook.PetTypeId, $"{newBook.CheckInDate:yyyy-MM-dd}", newBook.Days, newBook.FeedFreq, newBook.FTCanned, newBook.FTDry, newBook.FTSoft) == 1)
{
TempData["Msg"] = "New booking added.";
}
else
{
TempData["Msg"] = "Failed to add new booking." + DBUtl.DB_Message;
}
return(RedirectToAction("Index"));
}
else
{
TempData["Msg"] = "Invalid information entered";
return(RedirectToAction("Index"));
}
}
public IActionResult UpdateBooking(SRBooking uBook)
{
if (ModelState.IsValid)
{
string userId = HttpContext.User.FindFirst(ClaimTypes.NameIdentifier).Value;
if (DBUtl.ExecSQL(@"UPDATE SRBooking
SET Name='{0}', SlotId={1}, PackageTypeId={2},BookingDate='{3}',Hours={4},AOSnack='{5}',AODrink='{6}' WHERE Id = {7} AND BookedBy={8}",
uBook.Name, uBook.SlotId, uBook.PackageTypeId, $"{uBook.BookingDate:dd MMMM yyyy}", uBook.Hours, uBook.AOSnack, uBook.AODrink, uBook.Id, userId) == 1)
{
TempData["Msg"] = $"Booking{uBook.Id} updated";
}
else
{
TempData["Msg"] = DBUtl.DB_Message;
}
return(RedirectToAction("Index"));
}
else
{
TempData["Msg"] = "Invalid information entered!";
return(RedirectToAction("Index"));
}
}
public IActionResult UpdateBooking(PHBooking newBook)
{
if (ModelState.IsValid)
{
string userId = HttpContext.User.FindFirst(ClaimTypes.NameIdentifier).Value;
if (DBUtl.ExecSQL(@"UPDATE PHBooking
SET NRIC='{0}', OwnerName='{1}', PetName='{2}', PetTypeId={3}, CheckInDate='{4}', Days={5}, FeedFreq='{6}', FTCanned='{7}', FTDry='{8}', FTSoft='{9}' WHERE Id = {10} AND BookedBy={11}",
newBook.NRIC, newBook.OwnerName, newBook.PetName, newBook.PetTypeId, $"{newBook.CheckInDate:yyyy-MM-dd}", newBook.Days, newBook.FeedFreq, newBook.FTCanned, newBook.FTDry, newBook.FTSoft) == 1)
{
TempData["Msg"] = $"Booking{newBook.Id} updated";
}
else
{
TempData["Msg"] = DBUtl.DB_Message;
}
return(RedirectToAction("Index"));
}
else
{
TempData["Msg"] = "Invalid information entered!";
return(RedirectToAction("Index"));
}
}
public IActionResult CreateBooking(SRBooking newSRBooking)
{
// TODO Task 3d Use ModelState.IsValid to guard against invalid input. Pass the message "Invalid information entered" to Index action when ModelState.IsValid is false
if (ModelState.IsValid)
{
if (DBUtl.ExecSQL(@"INSERT INTO SRBooking (Name, SlotId, PackageTypeId, BookingDate, Hours, AOSnack, AODrink)
VALUES ('{0}', {1}, {2}, '{3}', {4}, '{5}', '{6}')",
newSRBooking.Name, newSRBooking.SlotId, newSRBooking.PackageTypeId, $"{newSRBooking.BookingDate:yyyy-MM-dd}", newSRBooking.Hours, newSRBooking.AOSnack, newSRBooking.AODrink) == 1)
{
TempData["Msg"] = "New booking added.";
}
else
{
TempData["Msg"] = "Failed to add new booking.";
}
return(RedirectToAction("Index"));
}
else
{
TempData["Msg"] = "Invalid information entered";
return(RedirectToAction("Index"));
}
}
public IActionResult Register(TSHUsers usr)
{
if (!ModelState.IsValid)
{
ViewData["Message"] = "Invalid Input";
ViewData["MsgType"] = "warning";
return(View("UserRegister"));
}
else
{
string insert =
@"INSERT INTO TSHUsers(UserId, UserPw, FullName, Email, UserRole)
VALUES('{0}', HASHBYTES('SHA1','{1}'), '{2}', '{3}', 'member')";
if (DBUtl.ExecSQL(insert, usr.UserId, usr.UserPw, usr.FullName, usr.Email) == 1)
{
string template = @"Hi {0},<br/><br/>
Welcome to TSH!
Your userid is <b>{1}</b> and password is <b>{2}</b>.
<br/><br/>Manager";
string title = "Registration Successul - Welcome";
string message = String.Format(template, usr.FullName, usr.UserId, usr.UserPw);
string result;
if (EmailUtl.SendEmail(usr.Email, title, message, out result))
{
ViewData["Message"] = "User Successfully Registered";
ViewData["MsgType"] = "success";
}
else
{
ViewData["Message"] = result;
ViewData["MsgType"] = "warning";
}
}
return(View("UserRegister"));
}
}
public IActionResult UpdateBooking(SRBooking newSRBooking)
{
string userid = HttpContext.User.FindFirst(ClaimTypes.NameIdentifier).Value;
if (ModelState.IsValid)
{
if (DBUtl.ExecSQL(@"INSERT INTO SRBooking (Name, SlotId, PackageTypeId, BookingDate, Hours, AOSnack, AODrink,BookedBy)
VALUES ('{0}', {1}, {2}, '{3}', {4}, '{5}', '{6}',{7})",
newSRBooking.Name, newSRBooking.SlotId, newSRBooking.PackageTypeId, $"{newSRBooking.BookingDate:dd MMMM yyyy}", newSRBooking.Hours, newSRBooking.AOSnack, newSRBooking.AODrink, userid) == 1)
{
TempData["Msg"] = "New booking added.";
}
else
{
TempData["Msg"] = DBUtl.DB_Message;
}
return(RedirectToAction("Index"));
}
else
{
TempData["Msg"] = "Invalid information entered!";
return(RedirectToAction("Index"));
}
}
public IActionResult ConductMaint(Equipment e)
{
if (!ModelState.IsValid)
{
ViewData["Message"] = "Invalid Input";
ViewData["MsgType"] = "warning";
return(View());
}
else
{
bool archive = false;
bool maint_type = true;
string statUpdate =
@"INSERT INTO Maintenance(Serial_no, Start_date, End_date, description, maint_type, archive)
VALUES('{0}', '{1:yyyy-MM-dd}','{2:yyyy-MM-dd}', '{3}', '{4}', '{5}')";
int plswork = DBUtl.ExecSQL(statUpdate, e.Serial_no, e.m_start_date, e.m_end_date, "Equipment Maintenance", maint_type, archive);
string insert =
@"UPDATE Equipment SET Status = '{0}', m_start_date = '{1:yyyy-MM-dd}', m_end_date = '{2:yyyy-MM-dd}' WHERE Serial_no = '{3}' AND Status = 'Available'";
int result = DBUtl.ExecSQL(insert, "Maintenance", e.m_start_date, e.m_end_date, e.Serial_no);
if (result == 1 && plswork == 1)
{
TempData["Message"] = "Sent for Maintenance";
TempData["MsgType"] = "success";
}
else
{
TempData["Message"] = DBUtl.DB_Message;
TempData["MsgType"] = "danger";
}
return(RedirectToAction("EquipmentMaint"));
}
}
public IActionResult DeleteProducts(int id)
{
string sql = @"SELECT * FROM Product
WHERE ProductID={0}";
string select = String.Format(sql, id);
DataTable ds = DBUtl.GetTable(select);
if (ds.Rows.Count != 1)
{
TempData["Message"] = "Product record no longer exists.";
TempData["MsgType"] = "warning";
}
else
{
string photoFile = ds.Rows[0]["picture"].ToString();
string fullpath = Path.Combine(_env.WebRootPath, "FoodPics/" + photoFile);
System.IO.File.Delete(fullpath);
string delete = @"DELETE FROM Product WHERE ProductID={0}";
int res = DBUtl.ExecSQL(delete, id);
if (res == 1)
{
TempData["Message"] = "Product Deleted";
TempData["MsgType"] = "success";
}
else
{
TempData["Message"] = DBUtl.DB_Message;
TempData["MsgType"] = "danger";
}
}
return(RedirectToAction("ListOfProducts"));
}
public ActionResult MassAdd(IFormFile postedFile)
{
if (postedFile != null)
{
try
{
string fileExtension = Path.GetExtension(postedFile.FileName);
//Validate uploaded file and return error.
if (fileExtension != ".csv")
{
ViewBag.Message = "Please select the csv file with .csv extension";
return(View());
}
var accessory = new List <Equipment_Accessories>();
using (var sreader = new StreamReader(postedFile.OpenReadStream()))
{
//First line is header. If header is not passed in csv then we can neglect the below line.
//Loop through the records
while (!sreader.EndOfStream)
{
string[] rows = sreader.ReadLine().Split(',');
accessory.Add(new Equipment_Accessories
{
Equipment_accessories_id = int.Parse(rows[0].ToString()),
Accessories_details = rows[1].ToString(),
Storage_location = rows[2].ToString(),
Quantity = int.Parse(rows[3].ToString()),
});
}
}
int count = 0;
bool exists = false;
foreach (Equipment_Accessories u in accessory)
{
List <Equipment_Accessories> list = DBUtl.GetList <Equipment_Accessories>("SELECT * FROM Equipment_accessories");
foreach (var a in list)
{
if (u.Equipment_accessories_id == (a.Equipment_accessories_id))
{
exists = true;
}
}
if (exists == false)
{
string insert =
@"INSERT INTO Equipment(Accessories_details, Storage_location , Quantity )
Values ('{0}' , '{1}' , '{2}')";
int res = DBUtl.ExecSQL(insert, u.Accessories_details, u.Storage_location, u.Quantity);
if (res == 1)
{
count++;
}
}
else
{
TempData["Message"] = "Accessory already exists";
TempData["MsgType"] = "danger";
}
}
if (count == accessory.Count)
{
TempData["Message"] = "All accessory have been created";
TempData["MsgType"] = "success";
}
else
{
TempData["Message"] = "Not all accessory have been created";
TempData["MsgType"] = "danger";
}
return(RedirectToAction("Index"));
}
catch (Exception ex)
{
ViewBag.Message = ex.Message;
}
}
else
{
ViewBag.Message = "Please select the file first to upload.";
}
return(View());
}
public IActionResult ReturnProcess(IFormFile postedFile)
{
if (postedFile != null)
{
try
{
string fileExtension = Path.GetExtension(postedFile.FileName);
//Validate uploaded file and return error.
if (fileExtension != ".csv")
{
ViewBag.Message = "Please select the csv file with .csv extension";
return(View());
}
var exercise = new List <Exercise>();
var userr = new List <Users>();
using (var sreader = new StreamReader(postedFile.OpenReadStream()))
{
//First line is header. If header is not passed in csv then we can neglect the below line.
string[] headers = sreader.ReadLine().Split(',');
//Loop through the records
while (!sreader.EndOfStream)
{
string[] rows = sreader.ReadLine().Split(',');
userr.Add(new Users
{
User_id = Int32.Parse(rows[0].ToString()),
Serial_no = rows[1].ToString(),
nric = rows[2].ToString(),
password = rows[3].ToString(),
full_name = rows[4].ToString(),
dob = DateTime.Parse(rows[5].ToString()),
rank = rows[6].ToString(),
unit = rows[7].ToString(),
company = rows[8].ToString(),
role = rows[9].ToString()
});;
exercise.Add(new Exercise
{
Exercise_id = Int32.Parse(rows[10].ToString()),
nric = rows[11].ToString(),
company = rows[8].ToString(),
unit = rows[7].ToString(),
description = rows[12].ToString(),
start_date = DateTime.Parse(rows[13].ToString()),
end_date = DateTime.Parse(rows[14].ToString()),
archive = Boolean.Parse(rows[15]),
Package_id = Int32.Parse(rows[16].ToString())
});;
}
}
var userList = DBUtl.GetList <Users>("SELECT * FROM Users WHERE User_id = " + userr[0].User_id + "");
var exerciseList = DBUtl.GetList <Exercise>("SELECT * FROM Exercise WHERE Exercise_id = " + exercise[0].Exercise_id + "");
var roleList = DBUtl.GetList <Users>("SELECT * FROM Users WHERE nric = '" + exerciseList[0].nric + "'");
var packageList = DBUtl.GetList <Package>("SELECT * FROM Package WHERE Package_id = " + exercise[0].Package_id + "");
var equipmentList = DBUtl.GetList <Equipment>("SELECT * FROM Equipment WHERE Type_desc = '" + packageList[0].Type_desc + "'");
var accessoryList = DBUtl.GetList <Equipment_Accessories>("SELECT * FROM Equipment_accessories WHERE Equipment_accessories_id = " + packageList[0].Equipment_accessories_id + "");
var userListCheck = DBUtl.GetList <Users>("SELECT * FROM Users");
int usersNo = userList.Count;
int quantPerPack = 0;
if (packageList[0].Equipment_accessories_id.Equals(1))
{
quantPerPack += 1;
}
else if (packageList[0].Equipment_accessories_id.Equals(2))
{
quantPerPack += 5;
}
else if (packageList[0].Equipment_accessories_id.Equals(3))
{
quantPerPack += 1;
}
int totalAcc = quantPerPack * usersNo;
if (accessoryList[0].Quantity > totalAcc)
{
string accLoan = "UPDATE Equipment_accessories SET Quantity = (Quantity + {0}) WHERE Equipment_accessories_id = {1}";
int accUpdate = DBUtl.ExecSQL(accLoan, totalAcc, packageList[0].Equipment_accessories_id);
}
if (packageList[0].Type_desc == "SAR-21")
{
var packList = DBUtl.GetList <Equipment>("SELECT * FROM Equipment WHERE Type_desc = 'SAR-21' AND Status = 'Unavailable' AND Assigned = '{0}'", true);
int loops = packList.Count;
int x = 0;
while (x < loops)
{
var updateEq = DBUtl.ExecSQL(@"UPDATE Equipment
SET Status = 'Available'
WHERE Assigned = '{0}' AND Status = 'Unavailable' AND Serial_no = '{1}'", true, packList[x].Serial_no);
x++;
}
int deploying = DBUtl.ExecSQL("UPDATE Users SET deployed_status = 'Standby' WHERE User_id = '{0}' AND deployed_status = 'Deployed'", userr[0].User_id);
}
else if (packageList[0].Type_desc == "AK-47")
{
var packList = DBUtl.GetList <Equipment>("SELECT * FROM Equipment WHERE Type_desc = 'AK-47' AND Status = 'Unavailable' AND Assigned = '{0}'", true);
int loops = packList.Count;
int x = 0;
while (x < loops)
{
var updateEq = DBUtl.ExecSQL(@"UPDATE Equipment
SET Status = 'Available'
WHERE Assigned = '{0}' AND Status = 'Unavailable' AND Serial_no = '{1}'", true, packList[x].Serial_no);
x++;
}
int deploying = DBUtl.ExecSQL("UPDATE Users SET deployed_status = 'Standby' WHERE User_id = '{0}' AND deployed_status = 'Deployed'", userr[0].User_id);
}
else if (packageList[0].Type_desc == "SIG Sauer P226")
{
var packList = DBUtl.GetList <Equipment>("SELECT * FROM Equipment WHERE Type_desc = 'SIG Sauer P226' AND Status = 'Unavailable' AND Assigned = '{0}'", true);
int loops = packList.Count;
int x = 0;
while (x < loops)
{
var updateEq = DBUtl.ExecSQL(@"UPDATE Equipment
SET Status = 'Available', Assigned = '{0}'
WHERE Assigned = '{0}' AND Status = 'Unavailable' AND Serial_no = '{1}'", true, packList[x].Serial_no);
x++;
}
int deploying = DBUtl.ExecSQL("UPDATE Users SET deployed_status = 'Standby' WHERE User_id = '{0}' AND deployed_status = 'Deployed'", userr[0].User_id);
}
var deployCheck = DBUtl.GetList <Users>("SELECT * FROM Users WHERE company = '{0}' AND unit = '{1}' AND deployed_status = 'Deployed'", exercise[0].company, exercise[0].unit);
if (deployCheck.Count == 0)
{
int exLoaned = DBUtl.ExecSQL("UPDATE Exercise SET status = 'Returned' WHERE Exercise_id = '{0}'", exercise[0].Exercise_id);
}
return(RedirectToAction("Loan"));
}
catch (Exception ex)
{
ViewBag.Message = ex.Message;
}
}
else
{
ViewBag.Message = "Please select the file first to upload.";
}
return(View());
}
public int packAvail(int packid, int users)
{
int entries = 0;
if (packid.Equals(1))
{
var pack1 = DBUtl.GetList <Equipment>("SELECT * FROM Equipment WHERE Type_desc = 'SAR-21' AND Status = 'Available' AND Assigned = 1");
var pack1two = DBUtl.GetList <Equipment>("SELECT * FROM Equipment WHERE Type_desc = 'SAR-21' AND Status = 'Available' AND Assigned = 0");
entries = pack1.Count;
int availcounter = pack1two.Count;
if (entries == 0)
{
if (availcounter == 0)
{
entries = 0;
}
else
{
int x = 0;
while (x <= users - 1)
{
var updateEq = DBUtl.ExecSQL(@"UPDATE Equipment
SET Assigned = '{0}'
WHERE Serial_no = '{1}'", true, pack1two[x].Serial_no);
x++;
}
entries = availcounter;
}
}
}
else if (packid.Equals(2))
{
var pack2 = DBUtl.GetList <Equipment>("SELECT * FROM Equipment WHERE Type_desc = 'SIG Sauer P226' AND Status = 'Available' AND Assigned = 1");
var pack2two = DBUtl.GetList <Equipment>("SELECT * FROM Equipment WHERE Type_desc = 'SIG Sauer P226' AND Status = 'Available' AND Assigned = 0");
entries = pack2.Count;
int availcounter = pack2two.Count;
if (entries == 0)
{
if (availcounter == 0)
{
entries = 0;
}
else
{
int x = 0;
while (x <= users - 1)
{
var updateEq = DBUtl.ExecSQL(@"UPDATE Equipment
SET Assigned = '{0}'
WHERE Serial_no = '{1}'", true, pack2two[x].Serial_no);
x++;
}
entries = availcounter;
}
}
}
else if (packid.Equals(3))
{
var pack3 = DBUtl.GetList <Equipment>("SELECT * FROM Equipment WHERE Type_desc = 'SAR-21' AND Status = 'Available' AND Assigned = 1");
var pack3two = DBUtl.GetList <Equipment>("SELECT * FROM Equipment WHERE Type_desc = 'SAR-21' AND Status = 'Available' AND Assigned = 0");
entries = pack3.Count;
int availcounter = pack3two.Count;
if (entries == 0)
{
if (availcounter == 0)
{
entries = 0;
}
else
{
int x = 0;
while (x <= users - 1)
{
var updateEq = DBUtl.ExecSQL(@"UPDATE Equipment
SET Assigned = '{0}'
WHERE Serial_no = '{1}'", true, pack3two[x].Serial_no);
x++;
}
entries = availcounter;
}
}
}
else if (packid.Equals(4))
{
var pack4 = DBUtl.GetList <Equipment>("SELECT * FROM Equipment WHERE Type_desc = 'AK-47' AND Status = 'Available' AND Assigned = 1");
var pack4two = DBUtl.GetList <Equipment>("SELECT * FROM Equipment WHERE Type_desc = 'AK-47' AND Status = 'Available' AND Assigned = 0");
entries = pack4.Count;
int availcounter = pack4two.Count;
if (entries == 0)
{
if (availcounter == 0)
{
entries = 0;
}
else
{
int x = 0;
while (x <= users - 1)
{
var updateEq = DBUtl.ExecSQL(@"UPDATE Equipment
SET Assigned = '{0}'
WHERE Serial_no = '{1}'", true, pack4two[x].Serial_no);
x++;
}
entries = availcounter;
}
}
}
return(entries);
}