Question

Hi Community!

How are you?

How could I put together a query similar to that with ESQ on the server side? Can I use aggregation functions?

 SELECT Max(l.CreatedOn), l.SmrRecordId 
   FROM SmrLoggingRecord l
     JOIN [dbo].[Case] c
        ON l.SmrRecordId = c.Id
        AND c.UpriEstadoCasoId = '78346E42-83AD-4790-B901-DB750A1D157E'
  WHERE l.SmrColumnCaption = 'Estado del Caso' 
  GROUP BY SmrRecordId

King Regards!

Ezequiel

 

 

Like

1 comments

Hi,

There is good description here how to write such queries. When reading pay attention to "Adding columns to query" section there. You can not only use aggregation in queries but also can use aggregation in filters

Log in or register to comment
Question

Hello, 

I am trying to do something like this:

Grab information to current page, based off of value in Lookup field.

Example would be, on the current page when the User (Contact) lookup is filled in, also grab the contacts Address (String value), Phone Number (String value), and Account name (Lookup value) and fill it in on the current page. 

I am fairly sure I need to run a client side ESQ, and a few joins to get the information I am looking for, but I have not been successful yet. 

Any help is appreciated!

Like

1 comments

Please investigate how to work with ESQ

https://academy.bpmonline.com/documents/technic-sdk/7-11/use-entitysche…

Then check the article by the link below. It shows how to select aggregate data.

https://community.bpmonline.com/questions/esq-query-find-role

Then check the next article. There you'll find how to fill in a lookup value. 

https://community.bpmonline.com/questions/update-query-change-lookup-va…

If you want to set a text or integer value, you need to use the following syntax:

this.set("fieldname", value); in a replacing client module.

Please find more information about modules in the article by the link below.

https://academy.bpmonline.com/documents/technic-sdk/7-11/adding-calcula…

Log in or register to comment
Question

Hello community!

I need get the title of the column form ESQ. The query not have the tile localization.

How can get it? Any function or ESQ query to have that?

 

Regards,

 

Like

3 comments

It should be something like this

var userConnection = Get<UserConnection>("UserConnection");
var activityQuery = new EntitySchemaQuery(userConnection.EntitySchemaManager,"Activity");
activityQuery.AddAllSchemaColumns();

var activityId = new Guid("0afe839a-ff8c-4543-a7b8-29e4aab9787a");
var filter = activityQuery.CreateFilterWithParameters(FilterComparisonType.Equal, "Id", activityId);
activityQuery.Filters.Add(filter);

var activityEntities = activityQuery.GetEntityCollection(userConnection);

foreach (var activityEntity in activityEntities)
{
    var titleColumnCaption = activityEntity.Schema.Columns.GetByName("Title").Caption.Value;
}
return true;

Eugene Podkovka, This is only for c#? is posible in javascript?

In JS columns are already loaded. You can get access to the columns in the following way.

https://academy.bpmonline.com/documents/technic-sdk/7-11/use-entitysche…

Log in or register to comment
Question

Hi community!

How are you?

I hope you can help me with the following

 

I have a query with ESQ on "EmployeeMiniPage" to validate that the "NroLegajo" field in the Employee entity is not repeated. When I apply certain permissions on a record so that only certain users can see it, the query not consider that record when I log in with a user who does not have permissions, and therefore allows me to enter an existing "NroLegajo" that belongs to that record. To solve this I set the "QueryJoinRightLevel" system variable with value "2" (Disabled) but it does not work. 

 

I reassigned and denied permissions on the registry to do the test and when I logged in with the user who does not have permissions on the registry I can continue entering a repeated value in the field "NroLegajo"

Any idea?

Is there an alternative a ESQ?

King Regards,

Ezequiel

Like

14 comments

Dear Ezequiel,

In order to omit rights check you can create an ESQ on the server side by the means of C#. However, instead of using UserConnection, you can use SystemUserConnection, which would let you execute the functionality no matter under what user.

"Script task" business process element or service will perfectly fit and cover the task. Choose the means more comfortable for you.

Here is how to obtain SystemUserConnection:

private SystemUserConnection SystemUserConnection {
			get {
				return _systemUserConnection ?? (_systemUserConnection = (SystemUserConnection)AppConnection.SystemUserConnection);
			}

Here is an article of how to build ESQ on server side. Though, its pretty much the same as on the client side:

https://academy.bpmonline.com/documents/technic-sdk/7-11/use-entitysche…

Hope you find it helpful.

Regards,

Anastasia

Hi Anastasia!

Thanks you for your answer!

How could I validate in the Employee registration that the "Legajo" field value is not repeated in the way you are indicating?

 Can I call a business process from EmployeeMiniPage to return an answer?

I need that validation along with others before saving the employee!

King Regards!

Ezequiel

 

Dear Ezequiel,

In order to implement such functionality on the page, you need to do the following;

1. Create a webservice, which brushes through the Employee table for duplicates. Please see more details on how to write a service here:

https://academy.bpmonline.com/documents/technic-sdk/7-8/how-call-config…

2. Create a new virtual boolean attribute. We will use it in our further steps.

            "ESQCompleted": {
                dataValueType: Terrasoft.DataValueType.BOOLEAN,
                type: Terrasoft.ViewModelColumnType.VIRTUAL_COLUMN
                value: false
            }

3. Override a basic save() method and firstly insert a validation, that if this.get("ESQCompleted") true, than we call parent function, if not, than run service call, as in the article.

4. In the response, based on the result, you either show information dialog regarding existing duplicate, or set attribute to true and call save method again.

Regards, 

Anastasia

Dear Anastasia,

Sorry, for the delay in the response. When working with the service I found several errors, I could not include the SystemUserConnection variable because it was throwing errors and when working with UserConnection as a test I also get several errors. Am i missing a reference?. I attach the code

namespace Terrasoft.Configuration.Test
{
	using System;
	using System.ServiceModel;
	using System.ServiceModel.Web;
	using System.ServiceModel.Activation;
	using System.Collections.Generic;
	using System.Collections.ObjectModel;
	using System.Data;
	using Terrasoft.Common;
	using Terrasoft.Core;
	using Terrasoft.Core.DB;
	using Terrasoft.Core.Entities; 
	// Service class is marked with [ServiceContract] compulsory attributes and
	// [AspNetCompatibilityRequirements] with parameters.
	[ServiceContract]
	[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]
	public class EmployeeService
	{
		/*private SystemUserConnection SystemUserConnection {
			get {
				return _systemUserConnection ?? (_systemUserConnection = (SystemUserConnection)AppConnection.SystemUserConnection);
			}
		}*/
		// Service methods are marked with compulsory attributes [OperationContract] and [WebInvoke] with parameters.
		[OperationContract]
		[WebInvoke(Method = "POST", RequestFormat = WebMessageFormat.Json, 
			BodyStyle = WebMessageBodyStyle.Wrapped, ResponseFormat = WebMessageFormat.Json)]
		public string ObtenerCantidadLegajosRepetidos(string nroLegajo, string cuitEmpresa)
		{
			//var result = nroLegajo + " + output string";
			var result = "T";
			entitySchemaManager = UserConnection.EntitySchemaManager;
			var employeeSchema = entitySchemaManager.GetInstanceByName("Employee"); 
			var esqEmployee = new EntitySchemaQuery(entitySchemaManager, employeeSchema.Name);
 
			var colId = esqEmployee.AddColumn("Id");
 
			//Agrego filtros en el query
			var filtroCUIT = esqEmployee.CreateFilterWithParameters(FilterComparisonType.Equal,"Account.UsrCUIT", cuitEmpresa);
			var filtroLegajo = esqEmployee.CreateFilterWithParameters(FilterComparisonType.Equal,"UsrNroLegajo", nroLegajo);
 
 
			// Adding created filters to query collection.
			esqEmployee.Filters.Add(filtroCUIT);
			esqEmployee.Filters.Add(filtroLegajo);
 
			// Execution of cache to database and getting resultant collections of objects.
			// Query results will be placed in cache after completion of this operation.
			var employeeCollection = esqEmployee.GetEntityCollection(UserConnection);
			if (employeeCollection == null || employeeCollection.Count == 0)
			{
				result = "F";
			}
			return result;
		}
	}
}

The errors that appear to me are the following:

I hope you can help me!

King Regards,

Ezequiel

 

Dear Ezequiel,

The reason for an error with UserConnection, is that you are missing "var" in variable declaration:

var entitySchemaManager = UserConnection.EntitySchemaManager;

As for the SystemUserConnection, please add the _systemUserConnection property declaration before the code I have previously indicated, like this: 

private SystemUserConnection _systemUserConnection;
private SystemUserConnection SystemUserConnection {
	get {
	     return _systemUserConnection ?? (_systemUserConnection = 
        (SystemUserConnection)AppConnection.SystemUserConnection);
	}
}

Hope this will solve the issue.

Regards,

Anastasia

Dear Anastasia!

How are you? Thank you for your answer!
Could it be that I'm missing a reference? I attached image

Regards!

Ezequiel

Dear Ezequiel,

Please try to make variable, which you assign SystemUserConnection, of a static type. In case this won't help, please share the whole code.

Regards,

Anastasia

Dear Anastasia,

I continue with the problem. I attached code.

namespace Terrasoft.Configuration.Test
{
	using System;
	using System.ServiceModel;
	using System.ServiceModel.Web;
	using System.ServiceModel.Activation;
	using System.Collections.Generic;
	using System.Collections.ObjectModel;
	using System.Data;
	using Terrasoft.Common;
	using Terrasoft.Core;
	using Terrasoft.Core.DB;
	using Terrasoft.Core.Entities; 
 
	[ServiceContract]
	[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]
	public class EmployeeService
	{
		private static SystemUserConnection _systemUserConnection;
		private static SystemUserConnection SystemUserConnection {
			get {
				return _systemUserConnection ?? (_systemUserConnection = (SystemUserConnection)AppConnection.SystemUserConnection);
			}
		}
 
		[OperationContract]
		[WebInvoke(Method = "POST", RequestFormat = WebMessageFormat.Json, 
			BodyStyle = WebMessageBodyStyle.Wrapped, ResponseFormat = WebMessageFormat.Json)]
		public string ObtenerCantidadLegajosRepetidos(string nroLegajo, string cuitEmpresa)
		{
 
			//var result = nroLegajo + " + output string";
			var result = "T";
			var entitySchemaManager = SystemUserConnection.EntitySchemaManager;
			var employeeSchema = entitySchemaManager.GetInstanceByName("Employee"); 
			var esqEmployee = new EntitySchemaQuery(entitySchemaManager, employeeSchema.Name);
 
			var colId = esqEmployee.AddColumn("Id");
 
			var filtroCUIT = esqEmployee.CreateFilterWithParameters(FilterComparisonType.Equal,"Account.UsrCUIT", cuitEmpresa);
			var filtroLegajo = esqEmployee.CreateFilterWithParameters(FilterComparisonType.Equal,"UsrNroLegajo", nroLegajo);
 
			esqEmployee.Filters.Add(filtroCUIT);
			esqEmployee.Filters.Add(filtroLegajo);
 
			var employeeCollection = esqEmployee.GetEntityCollection(SystemUserConnection);
			if (employeeCollection == null || employeeCollection.Count == 0)
			{
				result = "F";
			}
			return result;
		}
	}
}

Thanks you for your help!

King Regards!

 

Dear Anastasia!

 

I have to say that removing the definition of SystemUserConnection and using UserConnection I get a similar error

King Regards!

Ezequiel

Dear Ezequiel,

Please find the modified code for your service. I have successfully tested it on my side:

namespace Terrasoft.Configuration.Test
{
	using System;
	using System.ServiceModel;
	using System.ServiceModel.Web;
	using System.ServiceModel.Activation;
	using System.Collections.Generic;
	using System.Collections.ObjectModel;
	using System.Data;
	using System.Web;
	using Terrasoft.Common;
	using Terrasoft.Core;
	using Terrasoft.Core.DB;
	using Terrasoft.Core.Entities; 
 
	[ServiceContract]
	[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]
	public class EmployeeService
	{
 
		[OperationContract]
		[WebInvoke(Method = "POST", RequestFormat = WebMessageFormat.Json, 
			BodyStyle = WebMessageBodyStyle.Wrapped, ResponseFormat = WebMessageFormat.Json)]
		public string ObtenerCantidadLegajosRepetidos(string nroLegajo, string cuitEmpresa)
		{
			var appConnection = HttpContext.Current.Application["AppConnection"] as AppConnection;
 
			//var result = nroLegajo + " + output string";
			var result = "T";
			var entitySchemaManager = appConnection.SystemUserConnection.EntitySchemaManager;
			var employeeSchema = entitySchemaManager.GetInstanceByName("Employee"); 
			var esqEmployee = new EntitySchemaQuery(entitySchemaManager, employeeSchema.Name);
 
			var colId = esqEmployee.AddColumn("Id");
 
			var filtroCUIT = esqEmployee.CreateFilterWithParameters(FilterComparisonType.Equal,"Account.UsrCUIT", cuitEmpresa);
			var filtroLegajo = esqEmployee.CreateFilterWithParameters(FilterComparisonType.Equal,"UsrNroLegajo", nroLegajo);
 
			esqEmployee.Filters.Add(filtroCUIT);
			esqEmployee.Filters.Add(filtroLegajo);
 
			var employeeCollection = esqEmployee.GetEntityCollection(appConnection.SystemUserConnection);
			if (employeeCollection == null || employeeCollection.Count == 0)
			{
				result = "F";
			}
			return result;
		}
	}
}

 

Dear Andrey,

You have to use only UserConnection in GetEntityCollection method. Here is a signature of a method:
public EntityCollection GetEntityCollection(UserConnection userConnection)

Peter Vdovukhin,

Dear Peter,

Thank you for answer! May be you know how to get UserConnection on start appliacation (without users)? 

Dear Andrey,

Could you create a new topic with this question? It will be available for search and may be helpful for others.

Could you try instead of:

var SysAdminUnitCollection = esqSysAdminUnit.GetEntityCollection(appConnection.SystemUserConnection)

write:

var SysAdminUnitCollection = esqSysAdminUnit.GetEntityCollection((SystemUserConnection)appConnection.SystemUserConnection)

The thing is that SystemUserConnection inherits from UserConnection so you can pass SystemUserConnection instead of UserConnection

 

Log in or register to comment
Question

Hi Community!

How are you?

I hope you can help me!

 

I want to make a query with filters where the equivalent in sql server for example would be: "..Where field = 'Administrator' OR  field like 'Ope%' or field like 'Supe%'

 

 

Is there any way to include combinations of AND and OR with ESQ? Is there an alternative?

King regards,

Ezequiel

Like

4 comments

Dear Ezequiel,

You can create the filter that would include AND and OR at the same time and would work as described. 

You need to build the filter as shown in the following example:

                var filters = Ext.create("Terrasoft.FilterGroup");
                filters.logicalOperation = this.Terrasoft.LogicalOperatorType.OR;
                filters.addItem(select.createColumnFilterWithParameter(Terrasoft.ComparisonType.START_WITH, "UsrPresents",
                               present.value));
                select.filters = filters;

 

Lisa

Hi Lisa! 

Thank you for you answer!

sorry, I expressed myself badly, I need to add a filter. The complete filter would be:

Where Id = 'some id' and (name = 'Administrator' OR  name like 'Ope%' or name like 'Supe%')

Can be done?

Regards,

Ezequiel

I show the example below

var esq = Ext.create("Terrasoft.EntitySchemaQuery", {
				rootSchemaName: "SysUserInRole"
});
esq.addColumn("[SysFuncRoleInOrgRole:OrgRole:SysRole].FuncRole.Name", "RolFuncionaAsociado");
var esqFilter = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "SysUser.Id", userId);
var esqFilter2 = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "[SysFuncRoleInOrgRole:OrgRole:SysRole].FuncRole.Name", nombreRol);

The second filter is who should have the OR combination

Regards,

Ezequiel

You can use my example above to create the correct filtration. Just make sure you add all the necessary filters to the collection and then combine them in the way you need. You can use the following example to add the filters to the collection:

// Adding created filters to query collection. 
esqCities.Filters.Add(esqFirstFilter);
esqCities.Filters.Add(esqSecondFilter);

More useful examples can be found in our Academy here - https://academy.bpmonline.com/documents/technic-sdk/7-11/use-entityschemaquery-creation-queries-database.

Lisa

Log in or register to comment
Question

Hi everyone!

How are you?

I hope you can help me

I want know if a user have associated a functional role in his organization role.

Example: User: "apaez", Organization Role : "Operador Arcor", Functional Role: "Operador Empresa"

The user "apaez" is asocciatted the "Operador Arcor" Organization Role and "Operador Arcor" Organization Role is associatted "Operador Empresa" Functional Role

The query that builds in SQLServer is the following:

SELECT * FROM SysUserInRole ur
  JOIN SysFuncRoleInOrgRole a ON ur.SysRoleId = a.OrgRoleId
  JOIN VwSysRole sr ON a.FuncRoleId = sr.Id
Where sr.Name = 'Operador Empresa'
AND ur.SysUserId = '20abeba5-5327-45aa-a5c2-07c41ac1fdf2'

 

How can I replicate it in ESQ (Client)?

King Regards,

Ezequiel!

 

 

Like

2 comments

Maybe this example can help

	function getUserSaveRights(callback, renderTo, scope) {
		var currentUser = Terrasoft.SysValue.CURRENT_USER.value;
		var sysAdmins = ConfigurationConstants.SysAdminUnit.Id.SysAdministrators;
		var esq = Ext.create("Terrasoft.EntitySchemaQuery", {
			rootSchemaName: "SysUserInRole"
		});
		esq.addColumn("SysRole");
		esq.addColumn("SysUser");
		esq.filters.add("SysUser", Terrasoft.createColumnFilterWithParameter(
			Terrasoft.ComparisonType.EQUAL, "SysUser", currentUser));
		esq.filters.add("SysRole", Terrasoft.createColumnFilterWithParameter(
			Terrasoft.ComparisonType.EQUAL, "SysRole", sysAdmins));
		esq.getEntityCollection(function(response) {
			if (response &amp;&amp; response.success) {
				var result = response.collection;
				var isSysAdmin = (result.collection.length !== 0);
				callback.call(scope, renderTo, isSysAdmin);
			}
		}, this);
	}

 

Federico,

Thanks for you help!

I was able to solve the query in the following way:

var esq = Ext.create("Terrasoft.EntitySchemaQuery", {
						rootSchemaName: "SysUserInRole"
					});
esq.addColumn("[SysFuncRoleInOrgRole:OrgRole:SysRole].FuncRole.Name", "RolFuncionaAsociado");
 
var esqFilter = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "SysUser.Id", userId);
var esqFilter2 = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "[SysFuncRoleInOrgRole:OrgRole:SysRole].FuncRole.Name", nombreRol);
esq.filters.add("esqFilter", esqFilter);
esq.filters.add("esqFilter2", esqFilter2);
esq.getEntityCollection(function (result) {
   if (!result.success || result.collection.collection.length == 0) {
		// error processing/logging, for example
		this.showInformationDialog("Data query error");
		return;
   }
   debugger;
   this.set(nombreRol, true);
   return;
}, this);

King Regards!

Ezequiel

Log in or register to comment
Question

Hello!

I have to do a validation in the Employee Registration, there can't be two employees with the same file number. For that, add a validation on the page and use ESQ to verify the data in the database.

The problem is that the result of the validation method is always executed before the result that ESQ GetEntityCollection() returns. I need to establish the error message after evaluating the result of the query.

Is there any way or alternative of waiting for the result of the ESQ and then validating to establish the error message?

I appreciate your help.

I Attach the code:

validarNroLegajo: function() {
    var invalidMessage = "";
	var repetidos = 0;
	//Creo consulta para Empleado
	var consultaEmpleado = this.Ext.create("Terrasoft.EntitySchemaQuery", {
		rootSchemaName: "Employee"
	});
	//Cuento NroLegajos
	consultaEmpleado.addAggregationSchemaColumn("UsrNroLegajo", Terrasoft.AggregationType.COUNT, "NroLegajoRepetido", Terrasoft.AggregationEvalType.ALL);
	//Filtro por Nro de legajo
	var filtroNroLegajo = consultaEmpleado.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "UsrNroLegajo", this.get("UsrNroLegajo"));
	//Filtro por Id de empleado
	var filtroId = consultaEmpleado.createColumnFilterWithParameter(Terrasoft.ComparisonType.NOT_EQUAL, "Id", this.get("Id"));
	//Agrego filtros a la consulta
	consultaEmpleado.filters.add("filtroNroLegajo", filtroNroLegajo);
	consultaEmpleado.filters.add("filtroId", filtroId);
	//debugger;
	consultaEmpleado.getEntityCollection(function(result) {
		debugger;
		if (result.success) {
				repetidos = result.collection.collection.items["0"].values.NroLegajoRepetido;
		}
	}, this);
	debugger;
	if (repetidos &gt; 0)
	{
		invalidMessage = this.get("Resources.Strings.ValidacionNroLegajo");
	}
	return {
		// Validation error message displayed in the data window
		// when saving a page.
		fullInvalidMessage: invalidMessage,
		// Validation error message displayed under the control item.
		invalidMessage: invalidMessage
	};
}

 

Regards,

 

Like

2 comments

Dear Ezequiel,

As you have already noticed, ESQ functions are asynchronous functions, therefore the validarNroLegajo function is executed before response received.   
In order to ensure, that a particular function or methods are executed based on the ESQ response, please call the function with invalid messages in the ESQ callback.

You can also create a virtual attribute, which you'll set to "true" in the ESQ callback. Such approach is also suitable, if you need to proceed with some actions based on the ESQ result.
 

//create an attribute
attributes: {
    "NumberDoesNotExist": {
        "type": Terrasoft.ViewModelColumnType.VIRTUAL_COLUMN,
        "dataValueType": Terrasoft.DataValueType.BOOLEAN,
        "value": false
    }
}
 
//set attribute in the ESQ callback
 
 
...
select.getEntityCollection(function(response) {
    if (response.success) {
      var collection = result.collection;
      if (collection &amp;&amp; collection.collection.length === 0) {
       this.set("NumberDoesNotExist", true);
      } else {
        this.showInformationDialog("Error!");
      }
    }
}

Another option if to check during save() method based on the attribute, you can do the following:

save: function() {
    if(this.get("NumberDoesNotExist")) {
        this.callParent(arguments);
    } else {
        this.showInformationDialog("Error!");
    }
}

Hope you find this helpful.

Regards, 

Anastasia

Dear Anastasia, thanks for your help. I could solve the problem! Regards!

Log in or register to comment