public static MessageResult Phonenumber(StaffModel staff, string AddedBy)
{
try
{
using (SqlConnection conn = new SqlConnection(DBConn))
{
querry = $@"Update tblStaff set PhoneNumber = @PhoneNumber where UserID = @Id
";
using (SqlCommand cmd = new SqlCommand(querry, conn))
{
conn.Open();
cmd.Parameters.AddWithValue("@UserID", staff.UserID);
cmd.Parameters.AddWithValue("@PhoneNumber", staff.PhoneNumber);
cmd.Parameters.AddWithValue("@UpdatedAt", DateTime.Now);
cmd.Parameters.AddWithValue("@UpdatedBy", AddedBy);
cmd.ExecuteScalar();
mes.Status = "success";
mes.Message = "Phone number updated successfully";
OcelotLog.AuditLogs($"{Constant.GetUserID()} at {DateTime.Now} updated {staff.UserID} to {staff.PhoneNumber}.", "StaffSQL", "Phonenumber");
}
}
}
catch (Exception e)
{
mes.Status = "warning";
mes.Message = "Failed! reload and try again later";
OcelotLog.ErrorLogs(e.Message.ToString(), "StaffSQL", "Phonenumber");
}
return(mes);
}
public ActionResult LogOut()
{
var _singIn = new SignInService();
_singIn.IdentitySignout();
OcelotLog.AuditLogs($"{Constant.GetUserID()} at {DateTime.Now} signed out.", "AccountCOntroller", "Login");
return(RedirectToAction("Index"));
}
public static MessageResult AddUser(UsersListModel user)
{
var mes = new MessageResult();
using (SqlConnection conn = new SqlConnection(DBConn))
{
conn.Open();
SqlCommand command = conn.CreateCommand();
SqlTransaction transaction = conn.BeginTransaction("ADDUSER");
command.Connection = conn;
command.Transaction = transaction;
try
{
Byte[] Password;
Byte[] Keys;
Byte[] IV;
AccountService.EncryptStringToBytes(user.Password.Trim(),
out Password, out Keys, out IV);
command.Parameters.AddWithValue("@FirstName", user.FirstName);
command.Parameters.AddWithValue("@LastName", user.LastName);
command.Parameters.AddWithValue("@Email", user.Email);
command.Parameters.AddWithValue("@PhoneNumber", user.PhoneNumber);
command.Parameters.AddWithValue("@DateAdded", DateTime.Now);
command.Parameters.AddWithValue("@Position", 1);
command.CommandText = $@"insert into Users(FirstName,LastName,Email,PhoneNumber,DateAdded,Position)
OUTPUT Inserted.UserID values(@FirstName,@LastName,@Email,@PhoneNumber,@DateAdded,@Position)" ;
int UserID = Convert.ToInt32(command.ExecuteScalar());
command.Parameters.AddWithValue("@UserID", UserID);
command.Parameters.AddWithValue("@Password", Password);
command.Parameters.AddWithValue("@Keys", Keys);
command.Parameters.AddWithValue("@IV", IV);
command.Parameters.AddWithValue("@LoginType", 1);
command.Parameters.AddWithValue("@Status", 1);
command.Parameters.AddWithValue("@AddedBy", Constant.GetUserID());
command.CommandText = $@"Insert into UserCredential(UserID,Password,Keys,IV,LoginType,Status,DateAdded,AddedBy)
values(@UserID, @Password, @Keys, @IV, @LoginType, @Status, @DateAdded, @AddedBy)";
command.ExecuteNonQuery();
transaction.Commit();
OcelotLog.AuditLogs($"{UserID} at {DateTime.Now} added.", "UserSQL", "AddUser");
mes.Status = "success";
mes.Message = "User registered successfully";
}
catch (Exception e)
{
transaction.Rollback();
OcelotLog.ErrorLogs(e.Message.ToString(), "UserSQL", "AddUser");
mes.Status = "warning";
mes.Message = "Failed! this normaly works";
}
}
return(mes);
}
public static List <StaffModel> GetStaffs()
{
var staffs = new List <StaffModel>();
try
{
using (SqlConnection conn = new SqlConnection(DBConn))
{
querry = $@"select a.id,UPPER(CONCAT(FirstName,' ',MiddleName,' ',LastName)) as StaffName,UserID,
FirstName,MiddleName,LastName,a.DateAdded,PhoneNumber,b.Department,a.PayrollNumber,
( case a.UserType when 1 then 'DRIVER' when 2 then 'CC' else 'User' end)UserType
from tblStaff as a
left join tblDepartments as b on convert(nvarchar(50),b.ID) = a.DepartmentID";
using (SqlCommand cmd = new SqlCommand(querry, conn))
{
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
var staff = new StaffModel();
staff.FirstName = rdr["FirstName"].ToString();
staff.StaffName = rdr["StaffName"].ToString();
staff.MiddleName = rdr["MiddleName"].ToString();
staff.PhoneNumber = rdr["PhoneNumber"].ToString();
staff.PayrollNumber = rdr["PayrollNumber"].ToString();
staff.UserType = rdr["UserType"].ToString();
staff.UserID = Convert.ToInt32(rdr["UserID"]);
staff.LastName = rdr["LastName"].ToString();
staff.DateAdded = rdr["DateAdded"].ToString();
staff.DepartmentName = rdr["Department"].ToString();
staffs.Add(staff);
}
}
}
}
catch (Exception e)
{
OcelotLog.ErrorLogs(e.Message.ToString(), "StaffSQL", "GetStaffs");
}
OcelotLog.AuditLogs($"{Constant.GetUserID()} at {DateTime.Now} fetched", "StaffSQL", "GetStaffs");
return(staffs);
}
public ActionResult Login(LoginModel login)
{
if (AccountService.IsUser(login) && AccountService.IsLoggedIn(login))
{
// loggen in
var _signIn = new SignInService();
_signIn.IdentitySignin(AccountService.User(login.Email));
OcelotLog.AuditLogs($"{Constant.GetUserID()} at {DateTime.Now} signed in.", "AccountCOntroller", "Login");
return(RedirectToAction("../Vendor/Index"));
}
else
{
@TempData["LOGIN"] = "******";
@TempData["message"] = "Invalid email or password";
@TempData["email"] = login.Email;
OcelotLog.ErrorLogs($"{login.Email} at {DateTime.Now} attempted signed in.", "AccountCOntroller", "Login");
return(RedirectToAction("Index"));
}
}
public static MessageResult AddStaff(StaffModel staff, string AddedBy)
{
String ID = Guid.NewGuid().ToString();
int UserID;
if (ConfirmStaffDoesntExist(staff.PayrollNumber.Trim()))
{
Crypting.EncryptStringToBytes("12345", out Password, out Keys, out IV);
using (SqlConnection conn = new SqlConnection(DBConn))
{
querry = $@"Insert into tblStaff(Id,FirstName,MiddleName,LastName,PayrollNumber,DateAdded,AddedBy,
PhoneNumber,DepartmentID) output INSERTED.UserID
values(@Id,@FirstName,@MiddleName,@LastName,@PayrollNumber,@DateAdded,@AddedBy,@PhoneNumber,
@DepartmentID)";
conn.Open();
SqlCommand cmd = conn.CreateCommand();
SqlTransaction transaction = conn.BeginTransaction("AddStaff");
cmd.Connection = conn;
cmd.Transaction = transaction;
try
{
cmd.Parameters.AddWithValue("@Id", ID.ToString());
cmd.Parameters.AddWithValue("@FirstName", staff.FirstName);
cmd.Parameters.AddWithValue("@MiddleName", staff.MiddleName);
cmd.Parameters.AddWithValue("@LastName", staff.LastName);
cmd.Parameters.AddWithValue("@PayrollNumber", staff.PayrollNumber);
cmd.Parameters.AddWithValue("@DateAdded", DateTime.Now);
cmd.Parameters.AddWithValue("@AddedBy", AddedBy);
cmd.Parameters.AddWithValue("@Password", Password);
cmd.Parameters.AddWithValue("@Keys", Keys);
cmd.Parameters.AddWithValue("@IV", IV);
cmd.Parameters.AddWithValue("@PhoneNumber", staff.PhoneNumber);
cmd.Parameters.AddWithValue("@DepartmentID", staff.DepartmentID);
cmd.CommandText = string.Format(querry);
UserID = (int)cmd.ExecuteScalar();
cmd.Parameters.AddWithValue("@UserID", UserID);
cmd.CommandText = string.Format($@"insert into UserCredential(UserID,Password,Keys,IV)
values(@UserID,@Password,@Keys,@IV)");
cmd.ExecuteScalar();
transaction.Commit();
mes.Status = "success";
mes.Message = "Staff added successfully";
OcelotLog.AuditLogs($"{Constant.GetUserID()} at {DateTime.Now} added.", "StaffSQL", "AddStaff");
}
catch (Exception e)
{
transaction.Rollback();
mes.Status = "warning";
mes.Message = "Failed! reload and try again later";
OcelotLog.ErrorLogs(e.Message.ToString(), "StaffSQL", "AddStaff");
}
}
}
else
{
mes.Status = "info";
mes.Message = $@"Staff with staff number:{staff.PayrollNumber} already exists";
OcelotLog.AuditLogs($"{Constant.GetUserID()} at {DateTime.Now} tried to add.", "StaffSQL", "AddStaff");
}
return(mes);
}
