public IActionResult ListUsers()
{
List <User> list = DBUtl.GetList <User>(
@"SELECT * FROM Users");
return(View(list));
}
public IActionResult EventList()
{
List <Event> events = DBUtl.GetList <Event>(
@"SELECT * FROM Events");
return(View(events));
}
public IActionResult UpdateBooking(int Id, bool?isDelete)
{
ViewData["PetTypes"] = DBUtl.GetList(@"SELECT ID as value, Description as text from PetType ORDER BY Description");
string userId = HttpContext.User.FindFirst(ClaimTypes.NameIdentifier).Value;
List <PHBooking> lstBooking = DBUtl.GetList <PHBooking>(@"SELECT * FROM PHBooking WHERE Id = {0} AND BookedBy={1}", Id, userId);
PHBooking 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 Index()
{
string userID = HttpContext.User.FindFirst(ClaimTypes.NameIdentifier).Value;
List <PHBooking> model = DBUtl.GetList <PHBooking>("SELECT * FROM PHBooking WHERE BookedBy={0}", userID);
return(View(model));
}
public IActionResult Authenticate(Login login)
{
if (curUser() == null)
{
string sql = @"SELECT * FROM Al_Lecturer WHERE Name = '{0}' AND Password = HASHBYTES('SHA1', '{1}')";
var result = DBUtl.GetList(sql, login.UserId, login.Password);
if (result.Count > 0)
{
dynamic user = result[0];
login.Name = user.Name;
login.Password = null;
login.Id = user.Id;
HttpContext.Session.SetObject("Al_lecturer", login);
//return RedirectToAction("Index");
return(View("Index"));
}
ViewData["layout"] = "_Layout";
ViewData["msg"] = "Login failed";
return(View("Index"));
}
else
{
return(RedirectToAction("Index"));
}
}
public IActionResult List()
{
List <Company> company = DBUtl.GetList <Company>(
@"SELECT * FROM AppUser");
return(View(company));
}
public IActionResult EquipmentMaintCancel()
{
updateMaint();
List <Equipment> dt = DBUtl.GetList <Equipment>(@"SELECT * FROM Equipment WHERE Status = 'Maintenance'");
return(View("EquipmentMaintCancel", dt));
}
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 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 ViewBookingsByPetTypes()
{
ViewData["PetTypes"] = DBUtl.GetList("SELECT * FROM PetType ORDER BY Id");
List <Booking> model = DBUtl.GetList <Booking>("SELECT * FROM Booking");
return(View(model));
}
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 Delete(int id)
{
var user = DBUtl.GetList <Users>(@"SELECT * FROM Users WHERE User_id = '" + id + "'");
if (user.Count != 1)
{
TempData["Message"] = "User does not exist";
TempData["MsgType"] = "warning";
}
else
{
string delete = "DELETE FROM Users WHERE User_id={0}";
string unassign = "Update Equipment Set Assigned = 'False' Where Serial_no = '" + user[0].Serial_no + "'";
DBUtl.ExecSQL(unassign);
int res = DBUtl.ExecSQL(delete, id);
if (res == 1)
{
TempData["Message"] = "User Deleted";
TempData["MsgType"] = "success";
}
else
{
TempData["Message"] = DBUtl.DB_Message;
TempData["MsgType"] = "danger";
}
}
return(RedirectToAction("About"));
}
public string GetEquipment()
{
var list = DBUtl.GetList <Equipment>("Select * from Equipment WHERE Assigned = 'False' AND Type_desc = 'SAR-21' ");
var equip = list[0];
return(equip.Serial_no);
}
public IActionResult About()
{
updateLOA();
var list = DBUtl.GetList <Users>("SELECT User_id, full_name, rank, dob , unit, company, deployed_status FROM Users");
return(View("Index", list));
}
public IActionResult Create(Exercise e)
{
if (!ModelState.IsValid)
{
ViewData["Message"] = "Invalid Input";
ViewData["MsgType"] = "warning";
return(View("Create"));
}
else
{
string insert =
@"INSERT INTO Exercise(nric, Package_id, company, unit, description, start_date, end_date, archive, status, assigned_status)
Values ('{0}', '{1}', '{2}', '{3}', '{4}', '{5:yyyy-MM-dd}', '{6:yyyy-MM-dd}', '{7}', '{8}', '{9}')";
string nricSql = @"SELECT * FROM Users WHERE nric = '" + User.Identity.Name + "'";
List <Users> assigned = DBUtl.GetList <Users>(nricSql);
string nricFinal = assigned[0].nric;
bool archived = false;
bool assignedStatus = false;
int res = DBUtl.ExecSQL(insert, nricFinal, e.Package_id, e.company, e.unit, e.description, e.start_date, e.end_date, archived, "Pending", assignedStatus);
if (res == 1)
{
TempData["Message"] = "Exercise Created";
TempData["MsgType"] = "success";
}
else
{
TempData["Message"] = DBUtl.DB_Message;
TempData["MsgType"] = "danger";
}
return(RedirectToAction("Index"));
}
}
public int GetCurrentStocktake()
{
var user = DBUtl.GetList <Users>("SELECT * FROM Users WHERE nric = '" + User.Identity.Name + " ' ");
var stocktakingPerson = DBUtl.GetList <Stocktaking>(@"SELECT * FROM Stocktaking WHERE User_id ='" + user[0].User_id + "' ORDER BY date_created DESC");
var model = stocktakingPerson[0].Stocktaking_id;
return(model);
}
private List <User> GetListUsers()
{
string userSql = @"SELECT Username, FullName From Users";
List <User> lstuser = DBUtl.GetList <User>(userSql);
return(lstuser);
}
public IActionResult UpdateBooking()
{
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");
ViewData["PostTo"] = "UpdateBooking";
ViewData["ButtonText"] = "Update";
return(View("Edit"));
}
public IActionResult Index()
{
ViewData["Title"] = "List of Candidates";
List <Candidate> modelList =
DBUtl.GetList <Candidate>("SELECT * FROM Candidate");
return(View(modelList));
}
public IActionResult ViewArchive()
{
updatearchive();
List <Stocktaking> list = DBUtl.GetList <Stocktaking>(@"Select Stocktaking_id , total_equipment_quantity , total_accessories_quantity, date_created, u.full_name, diff_equip, diff_accessory from Stocktaking s
INNER JOIN Users u ON s.User_id = u.User_id
Where s.archive = 1 ORDER BY date_created DESC");
return(View("ViewArchive", list));
}
public IActionResult CreateBooking()
{
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");
// TODO Task 4g: Pass "CreateBooking" and "Create" as value of ViewData["PostTo"] and ViewData["ButtonText"] to Booking.cshtml
// TODO Task 4h: Verification: Navigate to SingRoom/CreateBooking to ensure the CreateBooking functionality still work.
return(View("Booking"));
}
public IActionResult ViewBookingsByPackage()
{
string userid = HttpContext.User.FindFirst(ClaimTypes.NameIdentifier).Value;
ViewData["PackageTypes"] = DBUtl.GetList("SELECT * FROM SRPackageType ORDER BY Description");
List <SRBooking> model = DBUtl.GetList <SRBooking>("SELECT * FROM SRBooking WHERE BookedBy = {0}", userid);
return(View(model));
}
public IActionResult ViewCurrentStocktake()
{
int stocktake_id = GetCurrentStocktake();
var currstocktake = DBUtl.GetList <Stocktaking>(@"Select Stocktaking_id , total_equipment_quantity , total_accessories_quantity, date_created, u.full_name, diff_equip, diff_accessory from Stocktaking s
INNER JOIN Users u ON s.User_id = u.User_id
Where Stocktaking_id = '" + stocktake_id + "'");
return(View("ViewCurrentStocktake", currstocktake));
}
public IActionResult ViewBookingsByPackage()
{
ViewData["PackageTypes"] = DBUtl.GetList("SELECT * FROM SRPackageType ORDER BY Description");
// TODO Task 2e: Modify the GetList so that only records of current-logged in user is retrieved.
// TODO Task 2f: Verification: Navigate to SingRoom/ViewBooingsByPackage to verify that only records of current user is displayed.
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 static List <Pokedex> GetAllPokedex()
{
string sql = @"SELECT * FROM Pokedex";
List <Pokedex> PokeList = new List <Pokedex>();
// TODO: P03 Task 2a - Get all Pokedex From Database
// PokeList = DBUtl ... ...
PokeList = DBUtl.GetList <Pokedex>(sql);
return(PokeList);
}
public IActionResult ViewBookingsByPetTypes()
{
ViewData["PetTypes"] =
DBUtl.GetList(@"SELECT *
FROM PHPetType
ORDER BY Id");
string userID = HttpContext.User.FindFirst(ClaimTypes.NameIdentifier).Value;
List <PHBooking> model = DBUtl.GetList <PHBooking>("SELECT * FROM PHBooking WHERE BookedBy={0}", userID);
return(View(model));
}
public IActionResult DishesIndex()
{
List <dynamic> model = DBUtl.GetList(
@"SELECT d.Id, d.Name, d.Price,
d.Recommended, d.Availability, c.Name as CuisineName
FROM Dish d, Cuisine c
WHERE d.CuisineId = c.Id");
ViewBag.Message = TempData["Message"];
return(View(model));
}
public IActionResult Loan()
{
updateStatus();
updatearchive();
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'");
return(View("Loan", loanList));
}
public IActionResult Index()
{
List <Exercise> dt = DBUtl.GetList <Exercise>(@"SELECT Exercise_id, E.Package_id, U.nric, E.company,
E.unit, P.Name, E.start_date, E.end_date,
E.description, E.status
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.status = 'Returned'");
return(View("Index", dt));
}
public IActionResult MaintainDetails()
{
if (curUser() == null)
{
return(this.Authenticate());
}
else
{
List <AssociateLecturer> model = DBUtl.GetList <AssociateLecturer>("SELECT * FROM associate_lecturer"); //WHERE type = {0}", curUser().al_id);
return(View(model));
}
}
