public JsonResult GetMaritalStatusRatio()
{
string userkey = ConfigurationManager.AppSettings["userkey"];
string uid = ConfigurationManager.AppSettings["uid"];
string LoginUser = (string)Session["LoginSAPID"];
try
{
Employer.Employer employer = new Employer.Employer();
DataTable dt = employer.FetchCompanYEmployees("1002", userkey, uid);
dt.TableName = "ExternalUsers";
dt.Columns.ToString();
var requestCategory = (from DataRow dr in dt.Rows
//where dr["eid"].ToString() == "113"
group dr by dr["Marital Status"] into g
select new
{
Mid = g.Key,
Count = g.Count()
}).ToList();
return(Json(new { data = requestCategory }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
LogError logerror = new LogError();
logerror.ErrorLog("", LoginUser, "", "Requests/GetRequestCategory", "Requests", "GetRequestCategory", "FetchRequestCategories Error", ex.Message.ToString(), 0);
return(Json(new { data = "Error has occured" }, JsonRequestBehavior.AllowGet));
}
}
public JsonResult GetCompanyEmployee([ModelBinder(typeof(DataTablesBinder))] IDataTablesRequest requestModel, String EmployerId)
{
string userkey = ConfigurationManager.AppSettings["userkey"];
string uid = ConfigurationManager.AppSettings["uid"];
string LoginUser = (string)Session["LoginSAPID"];
string _access_key = ConfigurationManager.AppSettings["Salt"];
try
{
Employer.Employer employer = new Employer.Employer();
DataTable dt = employer.FetchCompanYEmployees(EmployerId, userkey, uid);
dt.TableName = "CompanyEmployees";
Crypto.Service1 _crypt = new Crypto.Service1();
List <CompanyEmployee> companyEmployee = new List <CompanyEmployee>();
int startRec = requestModel.Start;
int pageSize = requestModel.Length;
List <CompanyEmployee> employeeCount = (from DataRow dr in dt.Rows
select new CompanyEmployee()
{
PIN = dr["P_I_N"].ToString()
}).ToList();
companyEmployee = (from DataRow dr in dt.Rows
select new CompanyEmployee()
{
dPIN = _crypt.Encryptfx(dr["P_I_N"].ToString(), _access_key),
PIN = dr["P_I_N"].ToString(),
FirstName = dr["First Name"].ToString(),
LastName = dr["Last Name"].ToString(),
MiddleName = dr["Middle Names"].ToString(),
PhoneNo = dr["Mobile 1"].ToString(),
DateOfBirth = dr["Date Of Birth"].ToString(),
DateOfEmployment = dr["Date Of Employment"].ToString(),
Email = dr["E-mail"].ToString(),
}).Skip(startRec).Take(pageSize).ToList();
var totalCount = employeeCount.Count();
var filteredCount = companyEmployee.Count();
if (requestModel.Search.Value != string.Empty)
{
var value = requestModel.Search.Value.ToLower().Trim();
employeeCount = (from DataRow dr in dt.Rows
where dr["P_I_N"].ToString().ToLower().Contains(value) || dr["First Name"].ToString().ToLower().Contains(value) ||
dr["Last Name"].ToString().ToLower().Contains(value) || dr["Mobile 1"].ToString().ToLower().Contains(value) ||
dr["E-mail"].ToString().ToLower().Contains(value)
select new CompanyEmployee()
{
PIN = dr["P_I_N"].ToString()
}).ToList();
companyEmployee = (from DataRow dr in dt.Rows
where dr["P_I_N"].ToString().ToLower().Contains(value) || dr["First Name"].ToString().ToLower().Contains(value) ||
dr["Last Name"].ToString().ToLower().Contains(value) || dr["Mobile 1"].ToString().ToLower().Contains(value) ||
dr["E-mail"].ToString().ToLower().Contains(value)
select new CompanyEmployee()
{
dPIN = _crypt.Encryptfx(dr["P_I_N"].ToString(), _access_key),
PIN = dr["P_I_N"].ToString(),
FirstName = dr["First Name"].ToString(),
LastName = dr["Last Name"].ToString(),
MiddleName = dr["Middle Names"].ToString(),
PhoneNo = dr["Mobile 1"].ToString(),
DateOfBirth = dr["Date Of Birth"].ToString(),
DateOfEmployment = dr["Date Of Employment"].ToString(),
Email = dr["E-mail"].ToString(),
}).Skip(startRec).Take(pageSize).ToList();
totalCount = employeeCount.Count();
filteredCount = companyEmployee.Count();
}
var sortedColumns = requestModel.Columns.GetSortedColumns();
var orderByString = String.Empty;
foreach (var column in sortedColumns)
{
orderByString += orderByString != String.Empty ? "," : "";
orderByString += (column.Data) + (column.SortDirection == Column.OrderDirection.Ascendant ? " asc" : " desc");
}
var data = companyEmployee.Select(emList => new
{
//dPIN = emList.dPIN,
PIN = emList.PIN,
FirstName = emList.FirstName,
LastName = emList.LastName,
MiddleName = emList.MiddleName,
PhoneNo = emList.PhoneNo,
DateOfBirth = emList.DateOfBirth,
DateOfEmployment = emList.DateOfEmployment,
Email = emList.Email
}).OrderBy(orderByString == string.Empty ? "PIN asc" : orderByString).ToList();
return(Json(new DataTablesResponse(requestModel.Draw, data, totalCount, totalCount), JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
LogError logerror = new LogError();
logerror.ErrorLog("", LoginUser, "", "Employee/GetCompanyEmployee", "Employee", "GetCompanyEmployee", "GetCompanyEmployee Error", ex.Message.ToString(), 0);
throw new Exception(ex.Message.ToString());
}
}
public ActionResult Index(Users users, string ReturnUrl)
{
string LoginType;
string ipaddress = Request.UserHostAddress;
string Agent = Request.UserAgent;
string BrowserUsed = Request.Browser.Browser;
string SessionID = HttpContext.Session.SessionID;
string userkey = ConfigurationManager.AppSettings["userkey"];
string uid = ConfigurationManager.AppSettings["uid"];
try
{
List <LoginStatus> loginStatus;
Employer.Employer employer = new Employer.Employer();
if ((users.Username.Trim().Length >= 5) && (users.Username.Trim().Contains("@") == true) && (users.Username.Trim().Contains(".") == true))
{
LoginType = "2";
DataTable dt = employer.ExternalLogin(users.Username, LoginType, users.Password, BrowserUsed, SessionID, Agent, ipaddress, userkey, uid);
dt.TableName = "ReturnedVal";
var response = dt.Columns.Count.ToString();
if (response == "2")
{
var loginError = (from DataRow dr in dt.Rows
select new LoginStatus()
{
ErrorMessage = dr["ErrorMessage"].ToString(),
LoginStat = dr["LoginStat"].ToString(),
}).ToList();
TempData["error"] = loginError[0].ErrorMessage;
return(View());
}
loginStatus = (from DataRow dr in dt.Rows
select new LoginStatus()
{
ErrorMessage = dr["ErrorMessage"].ToString(),
LoginStat = dr["LoginStat"].ToString(),
WebUserID = dr["WebUserID"].ToString(),
EMPLOYER_ID = dr["EMPLOYER_ID"].ToString(),
EnforceChange = dr["EnforceChange"].ToString(),
CUSTODIAN_ID = dr["CUSTODIAN_ID"].ToString(),
LastLogin = dr["LastLogin"].ToString(),
email = dr["email"].ToString(),
BrowserUsed = dr["BrowserUsed"].ToString(),
ROLE_ID = dr["ROLE_ID"].ToString(),
FULLNAME = dr["FULLNAME"].ToString(),
}).ToList();
if (loginStatus[0].EnforceChange == "Y")
{
ViewBag.Email = loginStatus[0].email;
Session["WebUserID"] = loginStatus[0].WebUserID;
Session["ROLE_ID"] = loginStatus[0].ROLE_ID;
return(RedirectToAction("ChangePassword"));
}
else if (loginStatus[0].LoginStat == "False")
{
TempData["error"] = loginStatus[0].ErrorMessage;
return(View());
}
else if (loginStatus[0].ROLE_ID == "3")
{
FormsAuthentication.SetAuthCookie(loginStatus[0].FULLNAME, false);
Session["LoginSAPID"] = users.Username;
DateTime dateTime10 = Convert.ToDateTime(loginStatus[0].LastLogin);
Session["LastLogin"] = dateTime10.ToString("dd-MMM-yyyy hh:mm");
Session["EMPLOYER_ID"] = loginStatus[0].EMPLOYER_ID;
Session["WebUserID"] = loginStatus[0].WebUserID;
Session["ROLE_ID"] = loginStatus[0].ROLE_ID;
DataTable dts = employer.FetchCompanYEmployees(loginStatus[0].EMPLOYER_ID, userkey, uid);
dts.TableName = "CompanyEmployees";
var companyEmployee = (from DataRow dr in dts.Rows
select new
{
Coyname = dr["Coyname"].ToString()
}).ToList();
Session["CompanyName"] = companyEmployee[0].Coyname;
DataTable dtz = employer.FetchEmployerContact(loginStatus[0].EMPLOYER_ID);
dtz.TableName = "EmpoyerContact";
dtz.Columns.ToString();
var EmpoyerContact = (from DataRow dr in dtz.Rows
select new
{
Email = dr["Email"].ToString(),
Address = dr["Address"].ToString(),
MobilePhone = dr["Mobile Phone"].ToString(),
Name = dr["Name"].ToString()
}).ToList();
Session["EmpoyerContactName"] = EmpoyerContact[0].Name;
Session["EmpoyerContactEmail"] = EmpoyerContact[0].Email;
Session["EmpoyerContactAddress"] = EmpoyerContact[0].Address;
Session["EmpoyerContactMobilePhone"] = EmpoyerContact[0].MobilePhone;
if (Url.IsLocalUrl(ReturnUrl) && ReturnUrl.Length > 1 && ReturnUrl.StartsWith("/") && !ReturnUrl.StartsWith("//") && !ReturnUrl.StartsWith("/\\"))
{
return(Redirect(ReturnUrl));
}
else
{
return(RedirectToAction("Index", "Home"));
}
}
else if (loginStatus[0].ROLE_ID == "4")
{
FormsAuthentication.SetAuthCookie(loginStatus[0].FULLNAME, false);
Session["LoginSAPID"] = users.Username;
DateTime dateTime10 = Convert.ToDateTime(loginStatus[0].LastLogin);
Session["LastLogin"] = dateTime10.ToString("dd-MMM-yyyy hh:mm");
Session["CUSTODIAN_ID"] = loginStatus[0].CUSTODIAN_ID;
Session["WebUserID"] = loginStatus[0].WebUserID;
Session["ROLE_ID"] = loginStatus[0].ROLE_ID;
DataTable dts = employer.FetchCompanYEmployees(loginStatus[0].EMPLOYER_ID, userkey, uid);
dts.TableName = "CompanyEmployees";
var companyEmployee = (from DataRow dr in dts.Rows
select new
{
Coyname = dr["Coyname"].ToString()
}).ToList();
Session["CompanyName"] = companyEmployee[0].Coyname;
if (Url.IsLocalUrl(ReturnUrl) && ReturnUrl.Length > 1 && ReturnUrl.StartsWith("/") && !ReturnUrl.StartsWith("//") && !ReturnUrl.StartsWith("/\\"))
{
return(Redirect(ReturnUrl));
}
else
{
return(RedirectToAction("Index", "Employee"));
}
}
else if (loginStatus[0].ROLE_ID == "7")
{
FormsAuthentication.SetAuthCookie(loginStatus[0].FULLNAME, false);
Session["LoginSAPID"] = users.Username;
DateTime dateTime10 = Convert.ToDateTime(loginStatus[0].LastLogin);
Session["LastLogin"] = dateTime10.ToString("dd-MMM-yyyy hh:mm");
Session["EMPLOYER_ID"] = loginStatus[0].EMPLOYER_ID;
Session["WebUserID"] = loginStatus[0].WebUserID;
Session["ROLE_ID"] = loginStatus[0].ROLE_ID;
DataTable dts = employer.FetchCompanYEmployees(loginStatus[0].EMPLOYER_ID, userkey, uid);
dts.TableName = "CompanyEmployees";
var companyEmployee = (from DataRow dr in dts.Rows
select new
{
Coyname = dr["Coyname"].ToString()
}).ToList();
Session["CompanyName"] = companyEmployee[0].Coyname;
DataTable dtz = employer.FetchEmployerContact(loginStatus[0].EMPLOYER_ID);
dtz.TableName = "EmpoyerContact";
dtz.Columns.ToString();
var EmpoyerContact = (from DataRow dr in dtz.Rows
select new
{
Email = dr["Email"].ToString(),
Address = dr["Address"].ToString(),
MobilePhone = dr["Mobile Phone"].ToString(),
Name = dr["Name"].ToString()
}).ToList();
Session["EmpoyerContactName"] = EmpoyerContact[0].Name;
Session["EmpoyerContactEmail"] = EmpoyerContact[0].Email;
Session["EmpoyerContactAddress"] = EmpoyerContact[0].Address;
Session["EmpoyerContactMobilePhone"] = EmpoyerContact[0].MobilePhone;
if (Url.IsLocalUrl(ReturnUrl) && ReturnUrl.Length > 1 && ReturnUrl.StartsWith("/") && !ReturnUrl.StartsWith("//") && !ReturnUrl.StartsWith("/\\"))
{
return(Redirect(ReturnUrl));
}
else
{
return(RedirectToAction("Index", "Home"));
}
}
}
else if ((users.Username.Trim().Length >= 10) && ((users.Username.Trim().All(char.IsDigit)) == true))
{
LoginType = "1";
DataTable dt = employer.ExternalLogin(users.Username, LoginType, users.Password, BrowserUsed, SessionID, Agent, ipaddress, userkey, uid);
dt.TableName = "ReturnedVal";
var response = dt.Columns.Count.ToString();
if (response == "2")
{
var loginError = (from DataRow dr in dt.Rows
select new LoginStatus()
{
ErrorMessage = dr["ErrorMessage"].ToString(),
LoginStat = dr["LoginStat"].ToString(),
}).ToList();
TempData["error"] = loginError[0].ErrorMessage;
return(View());
}
loginStatus = (from DataRow dr in dt.Rows
select new LoginStatus()
{
ErrorMessage = dr["ErrorMessage"].ToString(),
LoginStat = dr["LoginStat"].ToString(),
WebUserID = dr["WebUserID"].ToString(),
EMPLOYER_ID = dr["EMPLOYER_ID"].ToString(),
EnforceChange = dr["EnforceChange"].ToString(),
CUSTODIAN_ID = dr["CUSTODIAN_ID"].ToString(),
LastLogin = dr["LastLogin"].ToString(),
email = dr["email"].ToString(),
BrowserUsed = dr["BrowserUsed"].ToString(),
ROLE_ID = dr["ROLE_ID"].ToString(),
FULLNAME = dr["FULLNAME"].ToString()
}).ToList();
if (loginStatus[0].EnforceChange == "Y")
{
ViewBag.Email = loginStatus[0].email;
Session["WebUserID"] = loginStatus[0].WebUserID;
return(RedirectToAction("ChangePassword"));
}
else if (loginStatus[0].LoginStat == "False")
{
ViewBag.Email = loginStatus[0].email;
return(View());
}
else if (loginStatus[0].LoginStat == "False")
{
TempData["error"] = loginStatus[0].ErrorMessage;
return(View());
}
else if (loginStatus[0].ROLE_ID == "3")
{
FormsAuthentication.SetAuthCookie(loginStatus[0].FULLNAME, false);
Session["LoginSAPID"] = users.Username;
DateTime dateTime10 = Convert.ToDateTime(loginStatus[0].LastLogin);
Session["LastLogin"] = dateTime10.ToString("dd-MMM-yyyy hh:mm");
Session["EMPLOYER_ID"] = loginStatus[0].EMPLOYER_ID;
Session["WebUserID"] = loginStatus[0].WebUserID;
Session["ROLE_ID"] = loginStatus[0].ROLE_ID;
DataTable dts = employer.FetchCompanYEmployees(loginStatus[0].EMPLOYER_ID, userkey, uid);
dts.TableName = "CompanyEmployees";
var companyEmployee = (from DataRow dr in dts.Rows
select new
{
Coyname = dr["Coyname"].ToString()
}).ToList();
Session["CompanyName"] = companyEmployee[0].Coyname;
DataTable dtz = employer.FetchEmployerContact(loginStatus[0].EMPLOYER_ID);
dtz.TableName = "EmpoyerContact";
dtz.Columns.ToString();
var EmpoyerContact = (from DataRow dr in dtz.Rows
select new
{
Email = dr["Email"].ToString(),
Address = dr["Address"].ToString(),
MobilePhone = dr["Mobile Phone"].ToString(),
Name = dr["Name"].ToString()
}).ToList();
Session["EmpoyerContactName"] = EmpoyerContact[0].Name;
Session["EmpoyerContactEmail"] = EmpoyerContact[0].Email;
Session["EmpoyerContactAddress"] = EmpoyerContact[0].Address;
Session["EmpoyerContactMobilePhone"] = EmpoyerContact[0].MobilePhone;
if (Url.IsLocalUrl(ReturnUrl) && ReturnUrl.Length > 1 && ReturnUrl.StartsWith("/") && !ReturnUrl.StartsWith("//") && !ReturnUrl.StartsWith("/\\"))
{
return(Redirect(ReturnUrl));
}
else
{
return(RedirectToAction("Index", "Home"));
}
}
else if (loginStatus[0].ROLE_ID == "4")
{
FormsAuthentication.SetAuthCookie(loginStatus[0].FULLNAME, false);
Session["LoginSAPID"] = users.Username;
DateTime dateTime10 = Convert.ToDateTime(loginStatus[0].LastLogin);
Session["LastLogin"] = dateTime10.ToString("dd-MMM-yyyy hh:mm");
Session["CUSTODIAN_ID"] = loginStatus[0].CUSTODIAN_ID;
Session["WebUserID"] = loginStatus[0].WebUserID;
Session["ROLE_ID"] = loginStatus[0].ROLE_ID;
DataTable dts = employer.FetchCompanYEmployees(loginStatus[0].EMPLOYER_ID, userkey, uid);
dts.TableName = "CompanyEmployees";
var companyEmployee = (from DataRow dr in dts.Rows
select new
{
Coyname = dr["Coyname"].ToString()
}).ToList();
Session["CompanyName"] = companyEmployee[0].Coyname;
if (Url.IsLocalUrl(ReturnUrl) && ReturnUrl.Length > 1 && ReturnUrl.StartsWith("/") && !ReturnUrl.StartsWith("//") && !ReturnUrl.StartsWith("/\\"))
{
return(Redirect(ReturnUrl));
}
else
{
return(RedirectToAction("Index", "Employee"));
}
}
else if (loginStatus[0].ROLE_ID == "7")
{
FormsAuthentication.SetAuthCookie(loginStatus[0].FULLNAME, false);
Session["LoginSAPID"] = users.Username;
DateTime dateTime10 = Convert.ToDateTime(loginStatus[0].LastLogin);
Session["LastLogin"] = dateTime10.ToString("dd-MMM-yyyy hh:mm");
Session["EMPLOYER_ID"] = loginStatus[0].EMPLOYER_ID;
Session["WebUserID"] = loginStatus[0].WebUserID;
Session["ROLE_ID"] = loginStatus[0].ROLE_ID;
DataTable dts = employer.FetchCompanYEmployees(loginStatus[0].EMPLOYER_ID, userkey, uid);
dts.TableName = "CompanyEmployees";
var companyEmployee = (from DataRow dr in dts.Rows
select new
{
Coyname = dr["Coyname"].ToString()
}).ToList();
Session["CompanyName"] = companyEmployee[0].Coyname;
DataTable dtz = employer.FetchEmployerContact(loginStatus[0].EMPLOYER_ID);
dtz.TableName = "EmpoyerContact";
dtz.Columns.ToString();
var EmpoyerContact = (from DataRow dr in dtz.Rows
select new
{
Email = dr["Email"].ToString(),
Address = dr["Address"].ToString(),
MobilePhone = dr["Mobile Phone"].ToString(),
Name = dr["Name"].ToString()
}).ToList();
Session["EmpoyerContactName"] = EmpoyerContact[0].Name;
Session["EmpoyerContactEmail"] = EmpoyerContact[0].Email;
Session["EmpoyerContactAddress"] = EmpoyerContact[0].Address;
Session["EmpoyerContactMobilePhone"] = EmpoyerContact[0].MobilePhone;
if (Url.IsLocalUrl(ReturnUrl) && ReturnUrl.Length > 1 && ReturnUrl.StartsWith("/") && !ReturnUrl.StartsWith("//") && !ReturnUrl.StartsWith("/\\"))
{
return(Redirect(ReturnUrl));
}
else
{
return(RedirectToAction("Index", "Home"));
}
}
}
return(View());
}
catch (Exception ex)
{
LogError logerror = new LogError();
logerror.ErrorLog(users.Username, "", "", "Login/Index", "Login", "Index", "ExternalLogin Error", ex.Message.ToString(), 0);
return(View());
}
}
public JsonResult GetSexRatio()
{
string userkey = ConfigurationManager.AppSettings["userkey"];
string uid = ConfigurationManager.AppSettings["uid"];
string LoginUser = (string)Session["LoginSAPID"];
try
{
Employer.Employer employer = new Employer.Employer();
DataTable dt = employer.FetchCompanYEmployees("1002", userkey, uid);
dt.TableName = "ExternalUsers";
dt.Columns.ToString();
var employeeCount = (from DataRow dr in dt.Rows
select new
{
PIN = dr["P_I_N"].ToString()
}).ToList();
var totalCount = employeeCount.Count();
var sexCount = (from DataRow dr in dt.Rows
group dr by dr["SEX"] into g
select new
{
Mid = g.Key,
Count = g.Count()
}).ToList();
var stateCount = (from DataRow dr in dt.Rows
group dr by dr["State of Origin"] into g
select new
{
Mid = g.Key,
Count = g.Count()
}).ToList();
var religionCount = (from DataRow dr in dt.Rows
group dr by dr["Religion"] into g
select new
{
Mid = g.Key,
Count = g.Count()
}).ToList();
var maritalStatus = (from DataRow dr in dt.Rows
group dr by dr["Marital Status"] into g
select new
{
Mid = g.Key,
Count = g.Count()
}).ToList();
var statementOption = (from DataRow dr in dt.Rows
group dr by dr["Statement Option"] into g
select new
{
Mid = g.Key,
Count = g.Count()
}).ToList();
return(Json(new { sex = sexCount, state = stateCount, religion = religionCount, statement = statementOption, marital = maritalStatus }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
LogError logerror = new LogError();
logerror.ErrorLog("", LoginUser, "", "Requests/GetRequestCategory", "Requests", "GetRequestCategory", "FetchRequestCategories Error", ex.Message.ToString(), 0);
return(Json(new { data = "Error has occured" }, JsonRequestBehavior.AllowGet));
}
}
