Hello,

It can be achieved with the help of the business process. First of all you should add the "boolean" type field on the contact's page and on the activity page and name it for example "Is processed". After that you should create a business process with the "Simple" start event that will read "Contact" object, after that there should be "Read data" element that will read "Activity" object with the data read mode set as "Calculate function" and specify the function value as "Sum" by column "Duration (minutes)" and filter activities by field "Owner" that should have Id from the first "Read data" element that reads "Contacts" object. After that "Modified data" element should be added for the "Activity" object that should change the value of boolean field to "True". Then should go "Modify data" element that will modify data in contact section and will set value of the previous element to the specified field and that will change the value of the boolean field added at the beginning to "True" value and this element should be also interconnected via activity Id in the "How to filter records" block. After that you should make a cycle of this process and it should look like on the screenshot http://prntscr.com/kyx20n (conditional flow 2 should satisfy the condition that boolean field for "Contact" object is "False", conditional flow 4 should satisfy the condition that boolean field for "Activity" object is "False" so to read all activities and all contacts). Don't forget to filter activities by Start and End dates so to get all activities for the last 60 days.

Best regards,

Oscar

Dear Carlos,

There is no Execute method in EntitySchemaQuery class. Could you write here a code example to show the context of your question?

I meant the literal Query class :) The one from which for example Delete inherits. Example code 1:

 

var query = new Delete(userConnection);
query.From("UsrBook");
query.Where("UsrAuthor").IsEqual(Column.Parameter(authorId));
query.Execute();

Example code 2:

 

using (DBExecutor exec = userConnection.EnsureDBConnection())
{
    var query = new Delete(userConnection);
    query.From("UsrBook");
    query.Where("UsrAuthor").IsEqual(Column.Parameter(authorId));
    query.Execute(exec);
}

Here you will find Query class documentation. Click on Execute method to see all overloads and theirs description.

Peter Vdovukhin,

I saw that already but it doesn't explain much. What's exactly the difference between:

Executes the query text and returns the number of rows affected when executing the query.

and:

Executes the query text, using the specified DBExecutor instance, and returns the number of rows affected when executing the query.

?

What is this DBExecutor class for if it's not necessary to execute queries?

DBExecutor is a class that manages connection to the database. It is called always when you work with queries. By default every time you execute ESQ query, Select, Delete or any other query a new instance of this class is created. You can pass an existing DBExecutor instance just for optimization in order not to create an extra object or for using transactions. For example, if you need to run 3 queries in a row, you can create one DBExecutor and use it again in all these quires. How to create a new instance of this class and use it correctly you can read here

Ok, thanks, that clears things up :)

Welcome. As a bonus take an implementation of these two methods in a Query class:

public virtual int Execute() {

            string sqlText = GetSqlText();

            using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {

                return HasParameters ? dbExecutor.Execute(sqlText, Parameters) :

                    dbExecutor.Execute(sqlText);

            }

        }

public virtual int Execute(DBExecutor dbExecutor) {

            string sqlText = GetSqlText();

            return HasParameters ? dbExecutor.Execute(sqlText, Parameters) :

                dbExecutor.Execute(sqlText);

        }

Peter Vdovukhin,

So I want to add couple of objects to database using EntitySchemaQuery and I want to reuse the same database connection. Is what I'm doing here correct?

using (dbExecutor = userConnection.EnsureDBConnection())
{
    var schema = dbExecutor.UserConnection.EntitySchemaManager.GetInstanceByName("UsrExampleSchema");
    Entity entity1 = schema.CreateEntity(dbExecutor.UserConnection);
    entity1.SetDefColumnValues();
    entity1.CreateInsert().Execute(dbExecutor);
 
    Entity entity2 = schema.CreateEntity(dbExecutor.UserConnection);
    entity2.SetDefColumnValues();
    entity2.CreateInsert().Execute(dbExecutor);
}

Or is it possible to avoid using CreateInsert method and use DBExecutor directly with EntitySchemaQuery?

Dear Carlos,

Your code seems too complex and I cannot predict if it works. You can try it to check the correctness of this code. The one thing I can tell: you cannot directly use Execute method on Entity.

 Please, be advised that in 99.9% cases you do not need a dbExecutor class. First, when using dbExecutor directly with Select, Update, Create, Delete classes you bypass any events such as running business processes or validation at application level so you may finish with incorrect object state in the DB. You also bypass rights check because you execute a direct sql query.

Second, the overhead of using every time a new dbExecutor is very small

In your case just use entity.save() without dealing with dbExecutor at all.

So after refactoring your code will look like this:

 

var schema = UserConnection.EntitySchemaManager.GetInstanceByName("UsrExampleSchema");
    Entity entity1 = schema.CreateEntity(UserConnection);
    entity1.SetDefColumnValues();
    entity1.Save();
 
    Entity entity2 = schema.CreateEntity(UserConnection);
    entity2.SetDefColumnValues();
    entity2.Save();

You do need dbExecutor when work only with Update, Select, Create, Delete classes. It is mandatory.

Peter Vdovukhin,

Ok, thanks.

Yes, you can.

Just push the result columns to the esq, and set sorting by specifying the index of the column and the sort order

var entitySchemaQuery = Ext.create("Terrasoft.EntitySchemaQuery", {
	rootSchemaName: entitySchemaName
});
 
var modifiedOnColumn = entitySchemaQuery.addColumn("ModifiedOn", "ModifiedOn");
 
modifiedOnColumn.orderPosition = 0;
modifiedOnColumn.orderDirection = Terrasoft.OrderDirection.ASC;

Anastasia Botezat,

thanks so much anastasia. i can order that page.