public void SavePCMPreferences(string Token, [FromBody] ClaimManagerFieldList pcmPreferences) { var query = $"UPDATE [Portal_Data_View] SET [FieldOrder] = @FieldOrder, [IsVisible] = @IsVisible WHERE [FieldID] = @FieldID;"; using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["OrgSysConnectionString"].ToString())) { con.Open(); foreach (var field in pcmPreferences.claimManagerFields) { using (var command = new SqlCommand(query, con)) { command.Parameters.AddWithValue("@FieldOrder", field.FieldOrder); command.Parameters.AddWithValue("@IsVisible", field.IsVisible); command.Parameters.AddWithValue("@FieldID", field.FieldID); command.ExecuteNonQuery(); } } } }
public HttpResponseMessage GetPortalClaimManagerData(string Token, string StatusString, [FromBody] ClaimManagerFieldList Fields) { try { var ImportID = GetClientImportID(Token); var employeeID = context.GetOrgsysEmployeeID(Token).SingleOrDefault()?.OrgsysEmployeeID; var filters = context.GetFilteredData(Token, "Claim").ToList(); var qservice = new QueryService("OSI_New.os_employees", "Claim", ImportID, Token, employeeID); if (StatusString == "open") { qservice.WhereClauseQueryList.Add($"OSI_New.os_claims.DateClosed is null "); //part of query, not permission } else { qservice.WhereClauseQueryList.Add($"OSI_New.os_claims.DateClosed is not null "); //part of query, not permission } if (filters.Count() > 0) //ensure user has some permissions { var dataView = context.GetPortalPortalDataView(Token, "Claim").ToList(); var query = qservice.BuildQuery(dataView, filters); using (MySqlCommand command = new MySqlCommand(query, OrgsysdbConn)) { command.CommandType = CommandType.Text; OrgsysdbConn.Open(); MySqlDataAdapter da = new MySqlDataAdapter(command); DataTable dt = new DataTable(); da.Fill(dt); OrgsysdbConn.Close(); return(Request.CreateResponse(HttpStatusCode.OK, JsonConvert.SerializeObject(dt, Formatting.None))); } } else { return(Request.CreateResponse(HttpStatusCode.NotFound)); } } 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)); } }