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"));
        }
Beispiel #2
0
        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);
                }
            }
        }
Beispiel #3
0
 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());
            }
        }
Beispiel #5
0
        //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"));
        }
Beispiel #6
0
 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));
             }
         }
     }
 }
Beispiel #7
0
        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);
                }
            }
        }
Beispiel #9
0
        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));
                    }
                }
            }
        }
Beispiel #10
0
        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"));
            }
        }
Beispiel #11
0
        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);
                }
            }
        }
Beispiel #12
0
        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"));
        }
Beispiel #13
0
        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);
                }
            }
        }
Beispiel #14
0
        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"));
            }
        }
Beispiel #15
0
        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"));
        }
Beispiel #18
0
        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"));
            }
        }
Beispiel #25
0
        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"));
            }
        }
Beispiel #26
0
        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"));
            }
        }
Beispiel #27
0
        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());
        }
Beispiel #29
0
        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());
        }
Beispiel #30
0
        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);
        }