public HttpStatusCode UpdateExpiryDateForUser(string token, string UserGroupID, string DateTo) { var context = new OrgSys2017DataContext(); int UserGroupInt = -1; DateTime DateToParsed = DateTime.Parse(DateTo); Int32.TryParse(UserGroupID, out UserGroupInt); var UserGroupQuery = from UG in context.User_Groups where UG.GroupID == UserGroupInt select UG; foreach (User_Group user in UserGroupQuery) { user.DateTo = DateToParsed; } try { context.SubmitChanges(); return(HttpStatusCode.OK); } catch (Exception e) { Console.WriteLine(e); return(HttpStatusCode.InternalServerError); } }
// Database Context for OrgSys2017 Database protected FormController() { if (context == null) { context = new OrgSys2017DataContext(); } }
public static bool LogUser(int UserID, bool Success, string message) { try{ //if successful login OrgSys2017DataContext con = new OrgSys2017DataContext(); if (Success == true) { //Create new Log object Log ulog = new Log(); ulog.UserID = UserID; ulog.LogDateTime = DateTime.Now; ulog.Event = message; //add row to Log database con.Logs.InsertOnSubmit(ulog); con.SubmitChanges(); return(true); } else //if unsuccessful login //Create new Log object { Log ulog = new Log(); ulog.UserID = UserID; ulog.LogDateTime = DateTime.Now; ulog.Event = message; //add row to Log database con.Logs.InsertOnSubmit(ulog); con.SubmitChanges(); return(false); } } catch (Exception ex) { ExceptionLogger.LogException(ex); return(false); } }
/// <summary> /// Resolves the filter value to that of what will be used in the query. /// </summary> /// <param name="filterValue"></param> /// <param name="context"></param> /// <param name="token"></param> /// <returns></returns> public static object ResolveFilterValue(GetFilteredDataResult filter, OrgSys2017DataContext context, string token) { object value; var userId = context.GetUserIDSession(token).SingleOrDefault()?.UserID; switch (filter.FilterValue) //substitutes current user id when needed, allows for dynamic query { case "UserID": value = userId; break; case "DIVISION_TREE": var resultList = new List <string>(); var userDivisionId = context.GetUserClientDivision(token); context.GetClientDivisions(userDivisionId) .ToList() .ForEach(x => resultList.Add("" + x.ClientID)); value = $"({string.Join(",", resultList)})"; break; case "DIVISION": value = context.GetUserClientDivision(token); break; default: value = filter.FilterValue; break; } return(filter.isFilterValueColumn.Value ? value : $"'{value}'"); }
/// <summary> /// Returns an SQL query as a string, where this query only returns the data which the user has permissions to view. /// </summary> /// <param name="fields"></param> /// <param name="permissions"></param> /// <returns></returns> public string BuildQueryFromPermissions(List <GetPortalPortalDataViewResult> fields, List <GetFilteredDataResult> permissions) { var groupedFieldsByTable = fields.OrderBy(f => f.TableOrder).GroupBy(x => x.TableOrder); using (var context = new OrgSys2017DataContext()) { var userId = context.GetUserIDSession(Token).SingleOrDefault()?.UserID; foreach (var group in groupedFieldsByTable) { foreach (var field in group) { if (!field.IsPresented) { continue; //skips fields that are not for display } if (field.IsEncrypted) { SelectColumnList.Add($"fn_DecryptString({field.TableName}.{field.ColumnName}) AS {field.ColumnAlias}"); } else { SelectColumnList.Add($"{field.TableName}.{field.ColumnName} AS {field.ColumnAlias}"); } } var item = group.First(); if (item.TableName != "Claims" && item.TableName != "User_Profiles" && item.TableName != "Claim_Documents") { //PKName and PKTable are coming from Table_Order table JoinTableList.Add($"LEFT JOIN {item.TableName} ON {item.PKTable}.{item.PKName} = {item.TableName}.{item.FKName}"); } } foreach (var filter in permissions) { object value = ResolveFilterValue(filter, context, Token); WhereClausePermissionList.Add($"{filter.TableName}.{filter.ColumnName} {filter.Operator} {value}"); } } var query = $" SELECT DISTINCT {string.Join(", ", SelectColumnList)} FROM {TableName} {string.Join(" ", JoinTableList)} WHERE "; if (WhereClauseQueryList.Count > 0 && permissions.Count > 0) { query += string.Join(" AND ", WhereClauseQueryList) + " AND "; //add WHEREs that are part of the query itself, not permissions } else if (WhereClauseQueryList.Count > 0) { query += $" {string.Join(" AND ", WhereClauseQueryList)} ;"; } if (permissions.Count > 0) { query += $" ({string.Join(" OR ", WhereClausePermissionList)}) ;"; } return(query); }
public HttpResponseMessage GetClientLogo(string token) { try { using (context = new OrgSys2017DataContext()) { var logoFileName = context.GetClientLogo(token).SingleOrDefault().LogoPath; var filePath = $@"\\OSI-DEV01\umbrella\logos\{logoFileName}"; var mimeType = Path.GetExtension(filePath) == "png" ? "image/png" : "image/jpeg"; var logoBytes = File.ReadAllBytes(filePath); var base64Data = Convert.ToBase64String(logoBytes); var response = new HttpResponseMessage(HttpStatusCode.OK); response.Content = new StringContent(JsonConvert.SerializeObject(new { imageBase64 = $"data:{mimeType};base64,{base64Data}" })); return(response); } } catch (Exception e) { ExceptionLog.LogException(e); return(new HttpResponseMessage(HttpStatusCode.InternalServerError)); } }
//Logs the exception if thrown into the database public static void LogException(Exception ex) { OrgSys2017DataContext con = new OrgSys2017DataContext(); //Grabs UserID to reference who caused the error int ID = Convert.ToInt32(System.Web.HttpContext.Current.Session["UserID"]); int UserID = 0; if (ID != 0) { UserID = ID; } else { UserID = 0; } //Grab the URL page to find the error String exepurl = context.Current.Request.Url.ToString(); //Create new error object Log err = new Log(); err.UserID = UserID; err.Description = ex.Message.ToString(); err.LogDateTime = DateTime.Now; err.BrowserURL = exepurl; err.Event = ex.GetType().Name.ToString(); //add row to Logs database con.Logs.InsertOnSubmit(err); con.SubmitChanges(); }
public HttpResponseMessage GetClaimFileDataForUser(string Token) { try { using (context = new OrgSys2017DataContext()) { var filters = context.GetFilteredData(Token, "Document")?.ToList(); var userRoleName = context.GetUserRole(Token).FirstOrDefault().RoleName; var qservice = new QueryService("Claim_Documents", "Document", Token); if (filters == null && userRoleName != "OSIUser") { return(Request.CreateResponse(HttpStatusCode.Forbidden)); } var dataView = context.GetPortalPortalDataView(Token, "Document").ToList(); var query = qservice.BuildQuery(dataView, filters); var con = new Connection(); var result = con.SelectData(query); return(Request.CreateResponse(HttpStatusCode.OK, result)); } } catch (Exception e) { ExceptionLog.LogException(e); return(Request.CreateResponse(HttpStatusCode.InternalServerError)); } }
public string GetDataViewV2(string token, string viewType) { using (context = new OrgSys2017DataContext()) { return(JsonConvert.SerializeObject(context.GetPortalPortalDataViewV2(token, viewType, Request.Headers.GetValues("Language").First()), Formatting.None)); } }
public HttpResponseMessage GetUserProfile(string token) { try { using (OrgSys2017DataContext context = new OrgSys2017DataContext()) { UserProfile profile = new UserProfile(); var USER_ID = context.GetUserIDSession(token).SingleOrDefault().UserID; //gets the two most recent sessions by ther user var LastLoginTime = (from Session in context.Sessions where Session.UserID == USER_ID.ToString() orderby Session.SessionID descending select Session).Take(2).ToList(); var UserName = (from User_Profile in context.User_Profiles where User_Profile.UserID == USER_ID select User_Profile.EmpFirstName + " " + User_Profile.EmpLastName); profile.USER_ID = USER_ID; profile.Name = UserName.First(); profile.previousSessions = LastLoginTime; //return a json object of the 2 most recent sessions return(Request.CreateResponse(HttpStatusCode.OK, JsonConvert.SerializeObject(profile))); } } catch (Exception e) { ExceptionLog.LogException(e); return(Request.CreateResponse(HttpStatusCode.BadRequest)); } }
public HttpResponseMessage UpdateSessionClient(string token, int clientId) { try { using (var con = new OrgSys2017DataContext()) { var isSessionActive = con.CheckIfTokenValid(token) == 10001; if (!isSessionActive) { return(Request.CreateResponse(HttpStatusCode.Unauthorized)); } var session = con.Sessions.Single(x => x.SessionToken == token); var user = con.Users.Single(x => x.UserID == int.Parse(session.UserID)); var isInternalUser = user.UserType == 1; //all internal users have a default clientId of 0 in session table var hasPermission = (isInternalUser && clientId == 0) ? true : con.ClientDivisionUserViews.Any(x => x.UserID == user.UserID && x.RootClientID == clientId); if (!hasPermission || !isInternalUser) { return(Request.CreateResponse(HttpStatusCode.Forbidden)); } con.Sessions.Single(x => x.SessionToken == token).ClientID = clientId + ""; con.SubmitChanges(); return(Request.CreateResponse(HttpStatusCode.OK)); } } catch (Exception e) { ExceptionLog.LogException(e); return(Request.CreateResponse(HttpStatusCode.InternalServerError)); } }
public HttpResponseMessage GetPortalClaimManagerData(string Token, string StatusString, [FromBody] ClaimManagerFieldList Fields) { try { var status = ""; switch (StatusString) { case "open": status = "(9, 19)"; break; case "closed": status = "(29)"; break; case "draft": status = "(0)"; break; default: status = ""; break; } var qservice = new QueryService("Claims", "Claim", Token); //if the same table needs to be joined multiple times, the correct aliases must be referenced in [PermissionDataFilter] table //qservice.JoinTableList.Add($" LEFT JOIN [Claim_UserAssigned] ON [Claim_UserAssigned].[ClaimReferenceNumber] = [Claims].[ClaimRefNu] "); qservice.JoinTableList.Add($" INNER JOIN [User_Profiles] ON [User_Profiles].[UserID] = [Session].[UserID] "); qservice.WhereClauseQueryList.Add($" [Claims].ClientID = [ClientDivisionUserView].[ClientID] "); qservice.WhereClauseQueryList.Add($" [Claims].[Status] IN {status} "); qservice.WhereClauseQueryList.Add($" [Claims].Archived = 0 "); qservice.WhereClauseQueryList.Add($" Claims.Description IN(SELECT SL.Abbreviation FROM User_Service_Permission as USP INNER JOIN Services_LookUp as SL on USP.ServiceTypeID = SL.ServiceID WHERE USP.UserID = Session.UserID AND Claims.ClientID = USP.ClientID)"); qservice.SelectColumnList.Add("[Claims].ClaimID"); qservice.SelectColumnList.Add("[Claims].Description"); context = new OrgSys2017DataContext(); var filters = context.GetFilteredData(Token, "Claim")?.ToList(); var UserRoleName = context.GetUserRole(Token).FirstOrDefault()?.RoleName; //at this time, users are only assigned a single role if ((filters == null && UserRoleName != "OSIUser") || UserRoleName == null) { return(Request.CreateResponse(HttpStatusCode.Forbidden)); } var dataView = context.GetPortalPortalDataView(Token, "Claim").ToList(); var query = qservice.BuildQueryFromPermissions(dataView, filters); var con = new Connection(); var result = con.SelectData(query); var response = Request.CreateResponse(); response.Content = new StringContent(result); return(response); } catch (Exception e) { ExceptionLog.LogException(e); return(Request.CreateResponse(HttpStatusCode.InternalServerError)); } }
/// <summary> /// /// Constructor /// </summary> /// protected BusinessObjectBase() { if (Context == null) { Context = new OrgSys2017DataContext(); } }
public string GetServices() { using (context = new OrgSys2017DataContext()) { return(JsonConvert.SerializeObject(context.GetServices())); } }
public string GetClientDetails(string token) { if (context.CheckIfTokenValid(token) == 10001) { var importId = 0; using (var context = new OrgSys2017DataContext()) { importId = context.GetClientImportID(token); } using (var command = OrgsysdbConn.CreateCommand()) { OrgsysdbConn.Open(); command.CommandText = "PORTALORG_GetClientDetails"; command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@importId", importId); var da = new MySqlDataAdapter(command); var dt = new DataTable(); da.Fill(dt); OrgsysdbConn.Close(); return(JsonConvert.SerializeObject(dt, Formatting.None)); } } else { return(""); } }
public string GetDashboardReportData(string token) { var EmployeeID = -1; using (var context = new OrgSys2017DataContext()) { var USER_ID = context.GetUserIDSession(token).SingleOrDefault().UserID; EmployeeID = (from User in context.Users where User.UserID == USER_ID select User.OrgsysEmployeeID).FirstOrDefault().Value; } //Default values for 'EmployeeID' are stored as 0 in OSI_New.os_claims, so we do not want to return those results if (EmployeeID == 0) { EmployeeID = -1; } using (MySqlCommand command = OrgsysdbConn.CreateCommand()) { OrgsysdbConn.Open(); command.CommandText = "SELECT COUNT(*) AS ClaimCount FROM OSI_New.os_claims WHERE EmployeeID = @EmployeeID AND ArchivedRecord = 0;"; command.Parameters.AddWithValue("@EmployeeID", EmployeeID); MySqlDataAdapter da = new MySqlDataAdapter(command); var ClaimCount = command.ExecuteScalar(); OrgsysdbConn.Close(); return(JsonConvert.SerializeObject(ClaimCount, Formatting.None)); } }
//On page load it selects all the questions, answers,etc from the Wiki table and converts it to json protected void Page_Load(object sender, EventArgs e) { OrgSys2017DataContext con = new OrgSys2017DataContext(); var categoriesDis = con.Wikis.Select(x => x.category).Distinct().ToList();//selects distinct categories //Converts list to JSON jsonCategoriesDis = JsonConvert.SerializeObject(categoriesDis); jsonResult = JsonConvert.SerializeObject(con.Wikis);//converts whole table to json objects }
//grab the salt that is used for this user's password provided public static string GetSalt(string UserID) { try { OrgSys2017DataContext con = new OrgSys2017DataContext(); var Salt = JsonConvert.SerializeObject(con.GetSalt(UserID), Formatting.None); var SaltReturn = JsonConvert.DeserializeObject <List <Device> >(Salt); return(SaltReturn[0].SHASalt); } catch (Exception ex) { ExceptionLog.LogException(ex); return("0"); } }
public static string GetOldHash(int HashID) { try { OrgSys2017DataContext con = new OrgSys2017DataContext(); var Hash = JsonConvert.SerializeObject(con.SessionTransfer_GET(HashID), Formatting.None); var HashReturn = JsonConvert.DeserializeObject <List <OldHash> >(Hash); return(HashReturn[0].SHAHash); } catch (Exception ex) { ExceptionLog.LogException(ex); return("0"); } }
public HttpResponseMessage SessionBrowserInfoUpdate([FromBody] string browserInfo, string token) { BrowserInfo bInfo = JsonConvert.DeserializeObject <BrowserInfo>(browserInfo); using (var _ctx = new OrgSys2017DataContext()) { Session session = _ctx.Sessions.FirstOrDefault(i => i.SessionToken == token); session.Browser = bInfo.Browser; session.IPAdress = bInfo.IPAddress; _ctx.SubmitChanges(); } return(new HttpResponseMessage() { StatusCode = HttpStatusCode.OK }); }
public HttpResponseMessage RecoverPassword([FromBody] string UserName) { try { using (var context = new OrgSys2017DataContext()) { var user = context.GetUserInfo(UserName).SingleOrDefault(); if (user != null) { if (user.Email == "") { return(Request.CreateResponse(HttpStatusCode.NotFound)); } var token = MembershipProvider.createToken(); context.CreatePasswordRecoveryAttempt(token, user.ClientID, user.UserID); using (var mail = new EmailController()) { var origin = Request.Headers.GetValues("Origin").FirstOrDefault(); var email = new { To = user.Email, From = "*****@*****.**", Subject = "OSI Portal - Recover Password", Body = $"Hello, </br></br>Please click <a href='https://umbrella02.orgsoln.com/Orgsys_Forms/ExternalPasswordConfig?recoverytoken={token}'>here</a> to create a new password.</br>" + "This link will expire in 2 hours.</br></br></br>Thank you" }; mail.Post(null, JsonConvert.SerializeObject(new { emailData = email })); return(Request.CreateResponse(HttpStatusCode.OK)); } } else { return(Request.CreateResponse(HttpStatusCode.NotFound)); } } } catch (Exception e) { ExceptionLog.LogException(e); return(Request.CreateResponse(HttpStatusCode.InternalServerError)); } }
public bool hasAuthorizedRole(OrgSys2017DataContext context, string token) { var roles = context.GetRolesByUser(token).ToList(); foreach (var userRole in roles) { if (userRole.UserType == 1) //is internal user { break; } if (userRole.Role_ID != 2) //is not a portal manager; assumes portal user only has one role { return(false); } } return(true); }
public void RemoveSessionByToken(string token) { using (var _ctx = new OrgSys2017DataContext()) { try { if (_ctx.Sessions.Count(i => i.SessionToken == token) > 0) { _ctx.ArchiveSession(token); } } catch (Exception e) { _ctx.ChangeConflicts.ResolveAll(RefreshMode.OverwriteCurrentValues); _ctx.SubmitChanges(); } } }
public HttpResponseMessage DownloadClaimFile(string token, int documentId) { try { using (context = new OrgSys2017DataContext()) { if (context.CheckIfTokenValid(token) != 10001) { return(Request.CreateResponse(HttpStatusCode.Forbidden)); } var doc = context.Claim_Documents.Where(d => d.DocID == documentId); if (doc.Count() == 1) { string DocName = doc.Single().DocName; string DocType = doc.Single().DocType; string DocExt = doc.Single().DocExt; string DocGuid = doc.Single().StoredGuid; HttpContext httpContext = HttpContext.Current; FileInfo file = new FileInfo(path + DocGuid); httpContext.Response.Clear(); httpContext.Response.ClearHeaders(); httpContext.Response.AddHeader("Content-Disposition", $"attachment; filename={DocName}"); //httpContext.Response.AddHeader("Content-length", file.Length.ToString()); httpContext.Response.ContentType = DocType; httpContext.Response.Flush(); httpContext.Response.TransmitFile(file.FullName); httpContext.Response.End(); return(Request.CreateResponse(HttpStatusCode.OK, DocName + "." + DocExt, Configuration.Formatters.JsonFormatter)); } else { return(Request.CreateResponse(HttpStatusCode.NotFound)); } } } catch (Exception e) { ExceptionLog.LogException(e); return(new HttpResponseMessage(HttpStatusCode.BadRequest)); } }
public HttpResponseMessage SessionTracker(string token, bool ActivePing) { HttpResponseMessage message = new HttpResponseMessage(); using (var _ctx = new OrgSys2017DataContext()) { try { if (_ctx.Sessions.Count(i => i.SessionToken == token) > 0) { if (ActivePing) { _ctx.UpdateSessionLastValidDate(token); message.StatusCode = HttpStatusCode.OK; message.Content = new StringContent(""); } else { DateTime LastActiveDate = _ctx.Sessions.Single(i => i.SessionToken == token).DateLastActive ?? DateTime.MinValue; if (!(LastActiveDate.AddMinutes(60) > DateTime.Now)) { RemoveSessionByToken(token); message.StatusCode = HttpStatusCode.Unauthorized; message.Content = new StringContent("Expired"); } message.StatusCode = HttpStatusCode.OK; message.Content = new StringContent(""); } } else { message.StatusCode = HttpStatusCode.Unauthorized; message.Content = new StringContent("Kicked"); } } catch { _ctx.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges); _ctx.SubmitChanges(); } } return(message); }
public string Post([FromBody] ValidateCodeClass registrationCode) { string Code = ""; try { using (var db = new OrgSys2017DataContext()) { db.User_Registrations.InsertOnSubmit(registrationCode.codeData.obj_Con_valid); db.SubmitChanges(); Code = registrationCode.codeData.obj_Con_valid.RegistrationCode; } } catch (Exception ex) { ExceptionLog.LogException(ex); } return(Code); }
/// <summary> /// Executed before action is called when [OSIAuthenticationFilter] is used in a controller action /// </summary> /// <param name="actionContext"> http context of action</param> /// <param name="cancellationToken"></param> public override Task OnAuthorizationAsync(HttpActionContext actionContext, System.Threading.CancellationToken cancellationToken) { using (var ctx = new OrgSys2017DataContext()) { if (actionContext.Request.Headers.TryGetValues("Authentication", out IEnumerable <string> values) && ctx.CheckIfTokenValid(values.FirstOrDefault()) == 10001) { //User is Authorized, complete execution return(Task.FromResult <object>(actionContext)); } else if (SkipAuthorization(actionContext)) { //User is Authorized, complete execution return(Task.FromResult <object>(actionContext)); } else { actionContext.Response = actionContext.Request.CreateResponse(HttpStatusCode.Unauthorized, "The token provided is not valid."); return(Task.FromResult <object>(null)); } } }
//Logs the exception if thrown into the database public static void LogException(Exception ex) { OrgSys2017DataContext con = new OrgSys2017DataContext(); int ID = 0; //Grabs UserID to reference who caused the error try { ID = Convert.ToInt32(System.Web.HttpContext.Current.Session["UserID"]); } catch (Exception exception) { //No Session or Debug Session. Ignore Error } int UserID = 0; if (ID != 0) { UserID = ID; } else { UserID = 0; } //Grab the URL page to find the error String exepurl = context.Current.Request.Url.ToString(); //Create new error object Log err = new Log(); err.UserID = UserID; var message = ex.Message.ToString(); err.Description = (message.Length > 500) ? message.Substring(0, 499) : message; //if message exceeds db limit err.LogDateTime = DateTime.Now; err.BrowserURL = exepurl; err.Event = ex.GetType().Name.ToString(); //add row to Logs database con.Logs.InsertOnSubmit(err); con.SubmitChanges(); }
public bool ValidateRecoveryToken(string token) { try { if (!string.IsNullOrEmpty(token)) { using (var context = new OrgSys2017DataContext()) { var isActive = context.ValidatePasswordRecoveryAttempt(token, "") == 1; return(isActive); } } else { return(false); } } catch (Exception e) { ExceptionLog.LogException(e); return(false); } }
public HttpResponseMessage UploadClientLogo(string Token) { var file = HttpContext.Current.Request.Files[0]; if (file != null) { //prepend unique identifier to file name var fileName = Guid.NewGuid().ToString().Substring(0, 8) + "_" + file.FileName; var filePath = $@"\\OSI-DEV01\umbrella\logos\{fileName}"; try { file.SaveAs(filePath); //update the reference to the logo in db using (var context = new OrgSys2017DataContext()) { var clientId = context.GetClientIDBySession(Token).SingleOrDefault().ClientID; context.Clients.Where(c => c.ClientID == clientId).SingleOrDefault().LogoPath = fileName; context.SubmitChanges(); } return(new HttpResponseMessage(HttpStatusCode.OK)); } catch (Exception e) { ExceptionLog.LogException(e); return(new HttpResponseMessage(HttpStatusCode.InternalServerError)); } } else { return(new HttpResponseMessage(HttpStatusCode.BadRequest)); } }