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();
}
private void SetQuery(PortalQuery pQuery)
{
_portalQuery = pQuery;
RaisePortalQueryPropertyAndSendMessage();
}
public ChangePortalQueryMessage(PortalQuery portalQuery)
{
PQuery = portalQuery;
}
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));
}
