public static SearchParameters CreateSearchParameters(string searchText, PortalQuery portalQuery, int startIndex = 1, int limit = 20, IList <string> favoriteItemIDs = null) { string queryString = string.Format("{0} ({1})", searchText, "type:\"web map\" NOT type:\"web mapping application\""); string sortField = ""; QuerySortOrder sortOrder = QuerySortOrder.Descending; switch (portalQuery) { case PortalQuery.Default: //searchParamters.QueryString = "sdfgsdfhh type:\"web map\" NOT \"web mapping application\""; break; case PortalQuery.Recent: sortField = "uploaded"; break; case PortalQuery.HighestRated: sortField = "avgrating"; break; case PortalQuery.MostComments: sortField = "numcomments"; break; case PortalQuery.MostPopular: sortField = "numviews"; break; case PortalQuery.Favorites: queryString = GenerateFavoriteQueryFromIDs(favoriteItemIDs); break; case PortalQuery.MyGroups: break; case PortalQuery.MyMaps: break; case PortalQuery.Title: sortField = "title"; sortOrder = QuerySortOrder.Ascending; break; case PortalQuery.Owner: sortField = "owner"; sortOrder = QuerySortOrder.Ascending; break; } SearchParameters searchParamters = new SearchParameters(queryString); searchParamters.StartIndex = startIndex; searchParamters.Limit = limit; searchParamters.SortField = sortField; searchParamters.SortOrder = sortOrder; return(searchParamters); }
/// <summary> /// Builds and executes the query. /// </summary> /// <param name="query"></param> public void ExecuteInsert(PortalQuery query) { using (var command = _connection.CreateCommand()) { command.Transaction = _transaction; command.CommandText = query.GetCommandText(); for (var i = 0; i < query.paramValueList.Count; i++) { command.Parameters.AddWithValue(query.paramValuePlaceList[i], query.paramValueList[i]); } command.ExecuteNonQuery(); } }
public static SearchParameters CreateSearchParameters(string searchText, PortalQuery portalQuery, int startIndex = 1, int limit = 20, IList<string> favoriteItemIDs = null) { string queryString = string.Format("{0} ({1})", searchText, "type:\"web map\" NOT type:\"web mapping application\""); string sortField = ""; QuerySortOrder sortOrder = QuerySortOrder.Descending; switch (portalQuery) { case PortalQuery.Default: //searchParamters.QueryString = "sdfgsdfhh type:\"web map\" NOT \"web mapping application\""; break; case PortalQuery.Recent: sortField = "uploaded"; break; case PortalQuery.HighestRated: sortField = "avgrating"; break; case PortalQuery.MostComments: sortField = "numcomments"; break; case PortalQuery.MostPopular: sortField = "numviews"; break; case PortalQuery.Favorites: queryString = GenerateFavoriteQueryFromIDs(favoriteItemIDs); break; case PortalQuery.MyGroups: break; case PortalQuery.MyMaps: break; case PortalQuery.Title: sortField = "title"; sortOrder = QuerySortOrder.Ascending; break; case PortalQuery.Owner: sortField = "owner"; sortOrder = QuerySortOrder.Ascending; break; } SearchParameters searchParamters = new SearchParameters(queryString); searchParamters.StartIndex = startIndex; searchParamters.Limit = limit; searchParamters.SortField = sortField; searchParamters.SortOrder = sortOrder; return searchParamters; }
public ChangePortalQueryMessage(PortalQuery portalQuery) { PQuery = portalQuery; }
public string SavePortalForm([FromBody] DataList list, string token) { MySqlTransaction transaction = null; var insertHistory = new List <PKInfo>(); var txIsCommitted = false; var claimIndex = 1; //default; this is updated if employee record exists string[] os_claimsEncryptedFields = { "EmployeeLast", "DateBirth", "Phone1", "Phone2", "Phone3", "SIN" }; //these os_claims fields are to be encrypted string[] blankRecordTables = { "OSI_New.os_claims_absences", "OSI_New.os_claimsextended", "OSI_New.os_employees_additional" }; int importId; int claimId = 0; using (var context = new OrgSys2017DataContext()) { //userId = context.GetUserIDSession(token).SingleOrDefault().UserID; //clientId = context.GetClientIDBySession(token).SingleOrDefault().ClientID; importId = context.GetClientImportID(token); } if (list.contrlData != null) { using (var connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["OldOrgsysConnectionString"].ConnectionString)) { try { connection.Open(); transaction = connection.BeginTransaction(); orgsysService = new OldOrgsysServices(connection, transaction); object managerId = null; object supervisorId = null; object referralId = null; foreach (var item in list.contrlData) { if (string.IsNullOrEmpty(item.TableName)) { continue; } if (item.Columns != null) { var tableRows = item.Columns.GroupBy(x => x.Row).OrderBy(rowNu => rowNu.Key); //may have multiple rows foreach (var row in tableRows) { var query = new PortalQuery(item.TableName); foreach (var col in row) { object parameterValue = col.Value; //some values have to be modified before insert if (item.TableName == "OSI_New.os_employees" & (Array.IndexOf(os_claimsEncryptedFields, col.ColumnName) >= 0)) { parameterValue = orgsysService.EncryptString(col.Value); } query.AddParameter(col.ColumnName, parameterValue); } //find fk reference and add as parameter if present var res = insertHistory.Find(x => x.PkTable == item.PKTable); if (res != null) { query.AddParameter(item.FKName, res.PkValue); } //orgsys pandering if (item.TableName == "OSI_New.os_employees") { //ColumnType is used here to differentiate between types of employees (manager, supervisor, employee) var employeeType = row.FirstOrDefault().ColumnType; //these will become parameters for checking if an employee exists string employeeDOB = null; var employeeFirst = item.Columns.FirstOrDefault(c => c.ColumnName == "EmployeeFirst" & c.ColumnType == employeeType).Value; var employeeLast = item.Columns.FirstOrDefault(c => c.ColumnName == "EmployeeLast" & c.ColumnType == employeeType).Value; var employeeDOBColumn = item.Columns.FirstOrDefault(c => c.ColumnName == "DateBirth" & c.ColumnType == employeeType); if (employeeDOBColumn != null) { employeeDOB = employeeDOBColumn.Value; } var employeeId = orgsysService.GetEmployeeID(importId, employeeFirst, employeeLast, employeeDOB); var isNewEmployee = employeeId == 0; switch (employeeType) { case "Manager": if (isNewEmployee) { query.AddParameter("IsManager", -1); query.AddParameter("CompanyID", importId); orgsysService.ExecuteInsert(query); managerId = orgsysService.GetLastInsertID(); } else { managerId = employeeId; } continue; //a new record has been inserted or one already exists; go to next iteration case "Supervisor": if (isNewEmployee) { query.AddParameter("IsSupervisor", -1); query.AddParameter("CompanyID", importId); orgsysService.ExecuteInsert(query); supervisorId = orgsysService.GetLastInsertID(); } else { supervisorId = employeeId; } continue; case "SubBy": if (isNewEmployee) { query.AddParameter("CompanyID", importId); orgsysService.ExecuteInsert(query); referralId = orgsysService.GetLastInsertID(); } else { referralId = employeeId; } continue; default: //handle as employee if (!isNewEmployee) { insertHistory.Add(new PKInfo("OSI_New.os_employees", employeeId)); claimIndex = orgsysService.GetClaimIndexByEmployeeID(employeeId); continue; } else { query.AddParameter("CompanyID", importId); } break; } } if (item.TableName == "OSI_New.os_employees_additional") { //os_employees_additional has a column for os_claims.id and os_employees.id var os_employeesDetail = insertHistory.Find(x => x.PkTable == "OSI_New.os_employees"); if (os_employeesDetail != null) { query.AddParameter("os_employeesID", os_employeesDetail.PkValue); } } if (item.TableName == "OSI_New.os_claims") { query.AddParameter("ClaimStatusID", 1); query.AddParameter("ReasonClosedID", 9); query.AddParameter("ClaimIndex", claimIndex); query.AddParameter("ManagerID", managerId); query.AddParameter("SupervisorID", supervisorId); query.AddParameter("ReferralSubmittedBy", referralId); orgsysService.ExecuteInsert(query); claimId = orgsysService.GetLastInsertID(); //need claimId for link with any documents insertHistory.Add(new PKInfo(item.TableName, claimId)); continue; } if (item.TableName == "OSI_New.os_employees_schedule") { query.AddParameter("sched_order", row.Key); } if (item.TableName == "OSI_New.os_claims_healthsafety") { query.ConcatenateParameters("witnessesdescr", ","); } orgsysService.ExecuteInsert(query); var id = orgsysService.GetLastInsertID(); insertHistory.Add(new PKInfo(item.TableName, id)); } } } //these tables require that at minimum, an empty record be inserted that references the claim foreach (var table in blankRecordTables) { if (insertHistory.Where(x => x.PkTable == table).Count() == 0) { var query = new PortalQuery(table); query.AddParameter("ClaimID", claimId); orgsysService.ExecuteInsert(query); } } transaction.Commit(); txIsCommitted = true; } catch (Exception e) { ExceptionLog.LogException(e); try { transaction.Rollback(); } catch (MySqlException rbe) { ExceptionLog.LogException(rbe); } } } } return(JsonConvert.SerializeObject(new { Submitted = txIsCommitted, ClaimID = claimId })); }
private async void PopulatePortalItemCollection(ObservableCollection<ArcGISPortalItem> portalCollection, PortalQuery portalQuery) { if (portalCollection == null || portalQuery == PortalQuery.MyGroups) return; FavoritesService currentFavoritesService = new FavoritesService(); await currentFavoritesService.SetFavoritesCollection(); SearchParameters sp = null; if (portalQuery == PortalQuery.Favorites) sp = SearchService.CreateSearchParameters("", portalQuery, 0, 20, currentFavoritesService.GetFavoritesIds()); else sp = SearchService.CreateSearchParameters("", portalQuery); IsLoadingData = true; IList<ArcGISPortalItem> portalItems = await PortalService.CurrentPortalService.GetPortalItems(sp); if (portalItems != null) { portalCollection.Clear(); foreach (ArcGISPortalItem pi in portalItems) { portalCollection.Add(pi); } } IsLoadingData = false; }
private async void PopulatePortalItemCollection(ObservableCollection <ArcGISPortalItem> portalCollection, PortalQuery portalQuery) { if (portalCollection == null || portalQuery == PortalQuery.MyGroups) { return; } FavoritesService currentFavoritesService = new FavoritesService(); await currentFavoritesService.SetFavoritesCollection(); SearchParameters sp = null; if (portalQuery == PortalQuery.Favorites) { sp = SearchService.CreateSearchParameters("", portalQuery, 0, 20, currentFavoritesService.GetFavoritesIds()); } else { sp = SearchService.CreateSearchParameters("", portalQuery); } IsLoadingData = true; IList <ArcGISPortalItem> portalItems = await PortalService.CurrentPortalService.GetPortalItems(sp); if (portalItems != null) { portalCollection.Clear(); foreach (ArcGISPortalItem pi in portalItems) { portalCollection.Add(pi); } } IsLoadingData = false; }
private void SetQuery(PortalQuery pQuery) { _portalQuery = pQuery; RaisePortalQueryPropertyAndSendMessage(); }
public string SaveJSON(string Token, [FromBody] DataList list) { int ClaimID = 0; ClaimModel model = new ClaimModel(); Connection con = new Connection(); var insertHistory = new List <PKInfo>(); if (list.contrlData == null) { return(JsonConvert.SerializeObject(new { Submitted = false })); } int userId; int clientId; var txCommitted = false; var archive = false; var claimRefNuValue = list .contrlData .Find(x => x.TableName == "Claims") .Columns .Find(c => c.ColumnName == "ClaimRefNu") .Value; list.contrlData .Find(x => x.TableName == "Claims") .Columns .Find(c => c.ColumnName == "DateCreation") .Value = DateTime.Now.ToString(); using (var context = new OrgSys2017DataContext()) { userId = context.GetUserIDSession(Token).SingleOrDefault().UserID; clientId = context.GetClientIDBySession(Token).SingleOrDefault().ClientID; } //done outside transaction to prevent escalation to MSDTC if (string.IsNullOrEmpty(claimRefNuValue)) { claimRefNuValue = model.UniqueClaimReference("1003"); list .contrlData .Find(x => x.TableName == "Claims") .Columns .Find(c => c.ColumnName == "ClaimRefNu") .Value = claimRefNuValue; } else { archive = true; } try { using (var tx = new TransactionScope()) { var conString = ConfigurationManager.ConnectionStrings["OrgSysConnectionString"].ToString(); using (var connection = new SqlConnection(conString)) { connection.Open(); foreach (var table in list.contrlData) { if (string.IsNullOrEmpty(table.TableName) | table.Columns.Count == 0 | table.Columns == null) { continue; } var tableRows = table.Columns.GroupBy(x => x.Row).OrderBy(rowNu => rowNu.Key); //may have multiple rows foreach (var row in tableRows) { var query = new PortalQuery(table.TableName); object columnDescription = ""; foreach (var col in row) { object val = String.IsNullOrEmpty(col.Value) ? DBNull.Value : (object)col.Value; query.AddParameter(col.ColumnName, val); columnDescription = col.ColumnType; } // if (table.TableName == "Claim_Documents") { query.AddParameter("ClaimRefNu", claimRefNuValue); query.AddParameter("UserId", User); query.AddParameter("Timestamp", DateTime.Now.ToString()); } if (table.TableName == "Claim_Dates") { query.AddParameter("DateDescription", columnDescription); } if (table.TableName == "Claim_Contacts") { query.AddParameter("ContactType", columnDescription); query.AddParameter("ClaimRefNu", claimRefNuValue); } if (table.TableName == "Claim_Emp_Schedule") { query.AddParameter("WeekNo", row.Key); } if (table.TableName == "Claim_Emp_OtherEarnings") { query.AddParameter("WeekNu", row.Key); } if (table.TableName == "Claim_Employee") { query.AddParameter("DemEmpID", 1); } if (table.TableName == "Claim_Emp_ContactTypeDetails") { query.AddParameter("ClaimReference", claimRefNuValue); } if (table.TableName == "Claim_ICDCM_Witness") { query.AddParameter("Witness", row.Key); query.AddParameter("ClaimRefNu", claimRefNuValue); } if (table.TableName == "Claim_Injury_Cause") { query.AddParameter("IsSafe", 1); } if (table.TableName == "Claim_Injury_BodyPart") { query.AddParameter("Partside", row.FirstOrDefault().Group); } if (table.TableName == "Claims") { query.AddParameter("Status", list.status); query.AddParameter("ClientID", clientId); query.AddParameter("UserSubmitted", userId); if (!archive) { query.AddParameter("Archived", 0); } } //find fk reference and add as parameter if present var res = insertHistory.Find(x => x.PkTable == table.PKTable); if (res != null) { query.AddParameter(table.FKName, res.PkValue); } var insertedId = query.ExecuteInsert(connection); insertHistory.Add(new PKInfo(table.TableName, insertedId)); if (table.TableName == "Claims") { ClaimID = insertedId; } } } } tx.Complete(); txCommitted = true; } if (txCommitted && archive) { //newClaimRefNu not set means it exists; archive //done outside transaction to prevent escalation to MSDTC model.ArchiveClaim(claimRefNuValue); } } catch (Exception e) { ExceptionLog.LogException(e); } return(JsonConvert.SerializeObject(new { Submitted = txCommitted, ClaimID = ClaimID })); }
public virtual Task <PortalQueryResponse> GetPortalItem(PortalQuery portalQuery, CancellationToken ct = default(CancellationToken)) { return(GetPortalData <PortalQueryResponse, PortalQuery>(portalQuery, ct)); }