public IActionResult IndAvailability(Availability newAvailability)
{
if (curUser() != null)
{
if (ModelState.IsValid)
{
string sql = @"INSERT INTO Availabilty
(StartTime, AlId)
VALUES ('{0}', {1})";
if (DBUtl.ExecSQL(
sql,
newAvailability.StartTime, newAvailability.AlId)
== 1)
{
TempData["Msg"] = "Timeslot saved.";
}
else
{
TempData["Msg"] = "Failed to indicate timeslot.";
}
return(RedirectToAction("Index"));
}
else
{
TempData["Msg"] = "Invalid information entered!";
return(RedirectToAction("Index"));
}
}
else
{
return(RedirectToAction("Index"));
}
}
public IActionResult ListUsers()
{
List <User> list = DBUtl.GetList <User>(
@"SELECT * FROM Users");
return(View(list));
}
public IActionResult EditUser(User user)
{
if (!ModelState.IsValid)
{
ViewData["Message"] = "Invalid Input";
ViewData["MsgType"] = "warning";
return(View("EditUser"));
}
else
{
string update = @"UPDATE Users SET FullName='{1}', Email='{2}', Password=HASHBYTES('SHA1','{3}'), UserRole='{4}', Batch='{5:yyyy-MM-dd HH:mm}', CompanyName='{6}', ContactPerson='{7}', ContactNo='{8}' WHERE Username='******'";
Console.WriteLine(update);
int res = DBUtl.ExecSQL(update, user.Username, user.FullName, user.Email, user.Password, user.UserRole, user.Batch, user.CompanyName, user.ContactPerson, user.ContactNo);
Console.WriteLine(res);
if (res == 1)
{
TempData["Message"] = "User Updated";
TempData["MsgType"] = "success";
}
else
{
TempData["Message"] = DBUtl.DB_Message;
TempData["MsgType"] = "danger";
}
return(RedirectToAction("ListUsers"));
}
}
public IActionResult Edit(Edit edit)
{
if (!ModelState.IsValid)
{
ViewData["Message"] = "Invalid Input";
ViewData["MsgType"] = "warning";
return(View("Edit"));
}
else
{
string update = @"UPDATE AppUser SET CompanyType = {1}, RepName = '{2}', Contact_Num = {3}, CompanyName = '{4}', CompanyWebsite = '{5}', CompanyIndustry = '{6}', CompanySize = '{7}', Email = '{8}' WHERE UserName = '******'";
int res = DBUtl.ExecSQL(update, edit.UserName, edit.CompanyType, edit.RepName, edit.Contact_Num, edit.CompanyName, edit.CompanyWebsite, edit.CompanyIndustry, edit.CompanySize, edit.Email);
if (res == 1)
{
TempData["Message"] = "Company Updated";
TempData["MsgType"] = "success";
return(RedirectToAction("List"));
}
else
{
TempData["Message"] = DBUtl.DB_Message;
TempData["MsgType"] = "danger";
return(View("Edit"));
}
}
}
public IActionResult List()
{
List <Company> company = DBUtl.GetList <Company>(
@"SELECT * FROM AppUser");
return(View(company));
}
public IActionResult DeleteEquipment(string id)
{
string select = @"SELECT * FROM Equipment
WHERE Serial_no='{0}'";
DataTable ds = DBUtl.GetTable(select, id);
if (ds.Rows.Count != 1)
{
TempData["Message"] = "Equipment record no longer exists.";
TempData["MsgType"] = "warning";
}
else
{
string delete = "DELETE FROM Equipment WHERE Serial_no='{0}'";
int res = DBUtl.ExecSQL(delete, id);
if (res == 1)
{
TempData["Message"] = "Equipment Deleted";
TempData["MsgType"] = "success";
}
else
{
TempData["Message"] = "Please delete related records before deleting this record!";
TempData["MsgType"] = "danger";
}
}
return(RedirectToAction("Index"));
}
public IActionResult EquipmentMaintCancel()
{
updateMaint();
List <Equipment> dt = DBUtl.GetList <Equipment>(@"SELECT * FROM Equipment WHERE Status = 'Maintenance'");
return(View("EquipmentMaintCancel", dt));
}
public IActionResult Delete(int id)
{
string select = @"SELECT * FROM Performance WHERE Pid={0}";
DataTable ds = DBUtl.GetTable(select, id);
if (ds.Rows.Count != 1)
{
TempData["Message"] = "Performance does not exist";
TempData["MsgType"] = "warning";
}
else
{
string delete = "DELETE FROM Performance WHERE Pid={0}";
int res = DBUtl.ExecSQL(delete, id);
if (res == 1)
{
TempData["Message"] = "Performance Deleted";
TempData["MsgType"] = "success";
}
else
{
TempData["Message"] = DBUtl.DB_Message;
TempData["MsgType"] = "danger";
}
}
return(RedirectToAction("Index"));
}
public IActionResult Create(Performance perform)
{
if (!ModelState.IsValid)
{
ViewData["Message"] = "Invalid Input";
ViewData["MsgType"] = "warning";
return(View("Create"));
}
else
{
string insert =
@"INSERT INTO Performance(Title, Artist, PerformDT, Duration, Price, Chamber) VALUES
('{0}', '{1}', '{2:yyyy-MM-dd HH:mm}', {3}, {4}, '{5}')" ;
int res = DBUtl.ExecSQL(insert, perform.Title, perform.Artist, perform.PerformDT,
perform.Duration, perform.Price, perform.Chamber);
if (res == 1)
{
TempData["Message"] = "Performance Created";
TempData["MsgType"] = "success";
}
else
{
TempData["Message"] = DBUtl.DB_Message;
TempData["MsgType"] = "danger";
}
return(RedirectToAction("Index"));
}
}
public IActionResult CleanAll()
{
string sql = @"
DELETE FROM bill_transaction;
INSERT INTO prescription_archive
SELECT Prescription_id, Patient_id, Medicine_id, Dosage_id, Doctor_mcr, Doctor_name, Practicing_place_name, Practicing_address, Booking_appointment, Case_notes, Duration, Dosage_quantity, Instructions
FROM prescription;
DELETE FROM prescription;
DELETE FROM queue;
INSERT INTO patient_archive
SELECT Patient_id, Queue_id, Name, Nric, Gender, Date_of_birth, Race, Height, Weight, Allergy, Smoke, Alcohol, Has_travel, Has_flu, Has_following_symptoms, Address, Postal_code, Phone_no, Email, Remarks, Registered_datetime, Is_Urgent
FROM patient;
DELETE FROM patient;
DELETE FROM category1;
DELETE FROM category2;
DELETE FROM category3;
DELETE FROM category4;
DELETE FROM checkflag;
" ;
if (DBUtl.ExecSQL(sql) > 0)
{
TempData["Message"] = "Reset All Successful";
TempData["MsgType"] = "success";
}
else
{
TempData["Message"] = DBUtl.DB_Message;
TempData["MsgType"] = "danger";
}
return(RedirectToAction("Index"));
}
public IActionResult IndexAdmin()
{
string userid = HttpContext.User.FindFirst(ClaimTypes.NameIdentifier).Value;
List <SRBooking> model = DBUtl.GetList <SRBooking>("SELECT * FROM SRBooking WHERE BookedBy = {0}", userid);
return(View(model));
}
public IActionResult CleanMainQueue()
{
string sql = @"
DELETE FROM queue;
INSERT INTO patient_archive
SELECT Patient_id, Queue_id, Name, Nric, Gender, Date_of_birth, Race, Height, Weight, Allergy, Smoke, Alcohol, Has_travel, Has_flu, Has_following_symptoms, Address, Postal_code, Phone_no, Email, Remarks, Registered_datetime, Is_Urgent
FROM patient;
DELETE FROM patient;
DELETE FROM category1;
DELETE FROM category2;
DELETE FROM category3;
DELETE FROM category4;
DELETE FROM patientcheck;";
if (DBUtl.ExecSQL(sql) > 0)
{
TempData["Message"] = "Reset Main Queue Successful";
TempData["MsgType"] = "success";
}
else
{
TempData["Message"] = DBUtl.DB_Message;
TempData["MsgType"] = "danger";
}
return(RedirectToAction("Index"));
}
public IActionResult EditAccessories(Equipment_Accessories editAccessories)
{
if (!ModelState.IsValid)
{
ViewData["Message"] = "Please fill up all the blanks";
ViewData["MsgTye"] = "warning";
return(View());
}
string update = @"UPDATE Equipment_accessories SET Accessories_details='{1}', Quantity = '{2}',Storage_location='{3}' WHERE Equipment_accessories_id ='{0}'";
int res = DBUtl.ExecSQL(update, editAccessories.Equipment_accessories_id, editAccessories.Accessories_details, editAccessories.Quantity,
editAccessories.Storage_location);
if (res == 1)
{
TempData["Message"] = "Successfully updated Accessory";
TempData["MsgType"] = "success";
}
else
{
TempData["Message"] = DBUtl.DB_Message;
TempData["MsgType"] = "danger";
}
return(RedirectToAction("Index"));
}
public IActionResult AddAccessories(Equipment_Accessories newAccessories)
{
if (!ModelState.IsValid)
{
ViewData["Message"] = "Invalid Input";
ViewData["MsgType"] = "warning";
return(View("AddAccessories"));
}
else
{
string insert =
@"INSERT INTO Equipment_accessories(Accessories_details,Storage_location,Quantity)
VALUES('{0}','{1}','{2}')";
int result = DBUtl.ExecSQL(insert, newAccessories.Accessories_details,
newAccessories.Storage_location,
newAccessories.Quantity);
if (result == 1)
{
TempData["Message"] = "Accessory Created";
TempData["MsgType"] = "success";
}
else
{
TempData["Message"] = DBUtl.DB_Message;
TempData["MsgType"] = "danger";
}
return(RedirectToAction("Index"));
}
}
public IActionResult Edit(Order ord)
{
if (!ModelState.IsValid)
{
ViewData["Message"] = "Invalid Input";
ViewData["MsgType"] = "warning";
return(View("Edit"));
}
else
{
string edit =
@"UPDATE PurchaseOrder1
SET PONum='{1}', Descr='{2}',OrderDate='{3:yyyy-MM-dd}',RevisedDate='{4:yyyy-MM-dd}' WHERE PId={0}";
int res = DBUtl.ExecSQL(edit, ord.PId, ord.PONum, ord.Descr, ord.OrderDate, ord.RevisedDate);
if (res == 1)
{
TempData["Message"] = "Order Updated";
TempData["MsgType"] = "success";
}
else
{
TempData["Message"] = DBUtl.DB_Message;
TempData["MsgType"] = "danger";
}
return(RedirectToAction("Index"));
}
}
public IActionResult allot()
{
string venueSql = @"SELECT t.timeslot_id, E.class_id
FROM Timeslot t, exam_venue E WHERE
E.Timeslot_timeslot_id = t.timeslot_id
and E.associate_lecturer_al_id is null;";
string lecturerSql = @"SELECT ls.Timeslot_timeslot_id,
ls.associate_lecturer_al_id,ls.request_time, t.timeslot_id
FROM lect_slot ls, Timeslot t WHERE
ls.Timeslot_timeslot_id = t.timeslot_id;";
var lecturers = DBUtl.GetList(lecturerSql);
var venues = DBUtl.GetList(venueSql);
if (venues.Count > 0)
{
foreach (var i in venues)
{
int pos = search(lecturers, i.timeslot_id);
string updateSql = @"update exam_venue set associate_lecturer_al_id = {0}
where class_id = {1}";
int success = DBUtl.ExecSQL(updateSql, lecturers[pos].associate_lecturer_al_id, i.class_id);
lecturers.RemoveAt(pos);
//TempData["msg"] = DBUtl.DB_Message;
}
//TempData["msg"] = "Auto allocation success";
}
else
{
//TempData["error"] = "No venues to auto allocate";
}
return(RedirectToAction("AllocatedStatus"));
}
public IActionResult EditEquipment(Equipment EditEquip)
{
if (!ModelState.IsValid)
{
ViewData["Message"] = "Please fill up all the blanks";
ViewData["MsgTye"] = "warning";
return(View());
}
string update = @"UPDATE Equipment SET Equipment_name ='{1}', Storage_location = '{2}', Type_desc='{3}', Status = '{4}' WHERE Serial_no ='{0}'";
int res = DBUtl.ExecSQL(update, EditEquip.Serial_no, EditEquip.Equipment_name, EditEquip.Storage_location,
EditEquip.Type_desc, EditEquip.Status);
if (res == 1)
{
TempData["Message"] = "Successfully updated Equipment";
TempData["MsgType"] = "success";
}
else
{
TempData["Message"] = DBUtl.DB_Message;
TempData["MsgType"] = "danger";
}
return(RedirectToAction("Index"));
}
public IActionResult Authenticate(Login login)
{
if (curUser() == null)
{
string sql = @"SELECT * FROM associate_lecturer WHERE al_email = '{0}' AND al_password = HASHBYTES('SHA1', '{1}')";
var result = DBUtl.GetList(sql, login.UserId, login.al_password);
if (result.Count > 0)
{
dynamic user = result[0];
login.al_name = user.al_name;
login.al_password = null;
login.al_id = user.al_id;
login.type = user.type;
HttpContext.Session.SetObject("associate_lecturer", login);
return(View("home"));
}
ViewData["layout"] = "_Layout";
ViewData["msg"] = "Login failed";
return(View("Index"));
}
else
{
return(RedirectToAction("Index"));
}
}
public IActionResult CancelMaint(string id)
{
string select = @"SELECT * FROM Equipment
WHERE Serial_no='{0}'";
DataTable ds = DBUtl.GetTable(select, id);
if (ds.Rows.Count != 1)
{
TempData["Message"] = "Equipment record no longer exists.";
TempData["MsgType"] = "warning";
}
else
{
string update = "UPDATE Equipment SET Status = 'Available' WHERE Serial_no = '{0}' AND Status = 'Maintenance'";
int res = DBUtl.ExecSQL(update, id);
if (res == 1)
{
TempData["Message"] = "Maintenance Notice Cancelled";
TempData["MsgType"] = "success";
}
else
{
TempData["Message"] = "Something went wrong.";
TempData["MsgType"] = "danger";
}
}
return(RedirectToAction("EquipmentMaintCancel"));
}
public IActionResult CreateBooking(SRBooking newBk)
{
if (ModelState.IsValid)
{
// TODO Task 2c: Modify the ExecSQL so that BookedBy field is updated with current logged-user id.
// TODO Task 2d: Verification: Navigate to SingRoom/CreateBooking to verify that new records created can be displayed in the index view
string userID = HttpContext.User.FindFirst(ClaimTypes.NameIdentifier).Value;
if (DBUtl.ExecSQL(@"INSERT INTO SRBooking
(Name, SlotId, PackageTypeId, BookingDate, Hours,
AOSnack, AODrink, BookedBy)
VALUES ('{0}',{1},{2},'{3}',{4},'{5}','{6}',{7})",
newBk.Name, newBk.SlotId, newBk.PackageTypeId,
$"{newBk.BookingDate:dd MMMM yyyy}", newBk.Hours,
newBk.AOSnack, newBk.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 AddEquipment(Equipment newEquipment)
{
if (!ModelState.IsValid)
{
ViewData["Message"] = "Invalid Input";
ViewData["MsgType"] = "warning";
return(View("AddEquipment"));
}
else
{
string insert =
@"INSERT INTO Equipment(Serial_no,Equipment_name,Storage_location,Type_desc, Status, Assigned )
VALUES('{0}','{1}','{2}','{3}', 'Available', '{5}')";
int result = DBUtl.ExecSQL(insert, newEquipment.Serial_no, newEquipment.Equipment_name,
newEquipment.Storage_location, newEquipment.Type_desc, newEquipment.Status, newEquipment.Assigned);
if (result == 1)
{
TempData["Message"] = "Equipment added";
TempData["MsgType"] = "success";
}
else
{
TempData["Message"] = DBUtl.DB_Message;
TempData["MsgType"] = "danger";
}
return(RedirectToAction("Index"));
}
}
public IActionResult UpdateBooking(int Id, bool?isDelete)
{
ViewData["PackageTypes"] = DBUtl.GetList(@"SELECT ID as value, Description as text from SRPackageType ORDER BY Description");
ViewData["Slots"] = DBUtl.GetList(@"SELECT Id as value, Description as text FROM SRSlot ORDER BY Description");
string userId = HttpContext.User.FindFirst(ClaimTypes.NameIdentifier).Value;
List <SRBooking> lstBooking = DBUtl.GetList <SRBooking>(@"SELECT * FROM SRBooking WHERE Id = {0} AND BookedBy={1}", Id, userId);
SRBooking model = null;
if (lstBooking.Count > 0)
{
if (isDelete.HasValue == false || isDelete == false)
{
ViewData["PostTo"] = "UpdateBooking";
ViewData["ButtonText"] = "Update";
}
else
{
ViewData["PostTo"] = "DeleteBooking";
ViewData["ButtonText"] = "Delete";
}
model = lstBooking[0];
return(View("Booking", model));
}
else
{
TempData["Msg"] = $"Booking {Id} not found!";
return(RedirectToAction("Index"));
}
}
public IActionResult Delete(string name, string email)
{
string select = @"SELECT * FROM AppUser WHERE UserName='******'";
DataTable ds = DBUtl.GetTable(select, name);
if (ds.Rows.Count != 1)
{
TempData["Message"] = "Company Record No Longer Exists.";
TempData["MsgType"] = "warning";
}
else
{
string delete = "DELETE FROM AppUser WHERE UserName='******'";
string delete1 = "DELETE FROM Email WHERE Email='{0}'";
int res = DBUtl.ExecSQL(delete, name);
int res1 = DBUtl.ExecSQL(delete1, email);
if (res == 1)
{
TempData["Message"] = "Company Deleted.";
TempData["MsgType"] = "success";
}
else
{
TempData["Message"] = DBUtl.DB_Message;
TempData["MsgType"] = "danger";
}
}
return(RedirectToAction("List"));
}
public IActionResult Signup(MesahUser usr)
{
if (!ModelState.IsValid)
{
ViewData["Message"] = "Invalid Input";
ViewData["MsgType"] = "warning";
return(View("SignUp"));
}
else
{
string insert =
@"INSERT INTO MesahUser(UserId, UserPw, FullName, Email, Address, PostalCode, Phone, UserRole)
VALUES('{0}',HASHBYTES('SHA1','{1}'),'{2}','{3}','{4}','{5}','{6}','member')";
if (DBUtl.ExecSQL(insert, usr.UserId, usr.UserPw, usr.FullName, usr.Email, usr.Address, usr.PostalCode, usr.Phone, usr.UserRole) == 1)
{
ViewData["Message"] = "User Created";
ViewData["MsgType"] = "success";
}
else
{
ViewData["Message"] = DBUtl.DB_Message;
ViewData["MsgType"] = "danger";
}
return(View("Login"));
}
}
public IActionResult Activate(string un)
{
string sql = $"UPDATE Email SET EmailStatus = 1 WHERE Email = '{un}'";
int db = DBUtl.ExecSQL(sql);
return(View());
}
public IActionResult ViewBookingsByPackage()
{
ViewData["PackageTypes"] = DBUtl.GetList("SELECT * FROM SRPackageType ORDER BY Description");
List <SRBooking> model = DBUtl.GetList <SRBooking>("SELECT * FROM SRBooking");
return(View(model));
}
public IActionResult Login(UserLogin user)
{
if (!AuthenticateUser(user.Username, user.Password, out ClaimsPrincipal principal))
{
ViewData["Message"] = "Incorrect User ID or Password";
ViewData["MsgType"] = "warning";
return(View(LOGIN_VIEW));
}
else
{
HttpContext.SignInAsync(
CookieAuthenticationDefaults.AuthenticationScheme,
principal,
new AuthenticationProperties
{
IsPersistent = user.RememberMe
});
// Update the Last Login Timestamp of the User
DBUtl.ExecSQL(LASTLOGIN_SQL, user.Username);
if (TempData["returnUrl"] != null)
{
string returnUrl = TempData["returnUrl"].ToString();
if (Url.IsLocalUrl(returnUrl))
{
return(Redirect(returnUrl));
}
}
return(RedirectToAction(REDIRECT_ACTN, REDIRECT_CNTR));
}
}
private bool SecureValidUser(string uid,
string pw,
out ClaimsPrincipal principal)
{
string sql = "";
string returnUrl = ViewData["ReturnUrl"] as string;
sql = @"SELECT * FROM AppUser WHERE Id='{0}' AND Password = HASHBYTES('SHA1','{1}')";
DataTable ds = DBUtl.GetTable(sql, uid, pw);
principal = null;
if (ds.Rows.Count == 1)
{
string uname = ds.Rows[0]["Name"].ToString();
string userid = ds.Rows[0]["Id"].ToString();
string role = ds.Rows[0]["Role"].ToString();
principal =
new ClaimsPrincipal(
new ClaimsIdentity(
new Claim[] {
new Claim(ClaimTypes.NameIdentifier, userid),
new Claim(ClaimTypes.Name, uname),
new Claim(ClaimTypes.Role, role)
},
"Basic"));
return(true);
}
else
{
return(false);
}
}
public IActionResult EventList()
{
List <Event> events = DBUtl.GetList <Event>(
@"SELECT * FROM Events");
return(View(events));
}
private List <User> GetListUsers()
{
string userSql = @"SELECT Username, FullName From Users";
List <User> lstuser = DBUtl.GetList <User>(userSql);
return(lstuser);
}
