5. Adding support for AdvancedCriteria

Description

In this example the last sample will be modified to make use of the FilterBuilder and the underlying AdvancedCriteria system, to build functionality resembling this showcase sample (but using our supplyItem DataSource, instead of the one in the webpage)

Prerequisites

As this sample extends the previous one, in order to be able to run it, please ensure you have the latest build of SmartClient (at least version 8.2p). This can be downloaded from here.

Adding FilterBuilder to front-end

Modify Scripts/ui.js to include the relevant code for creating the FilterBuilder:

isc.FilterBuilder.create({
    ID: "advancedFilter",
    dataSource: "supplyItem",
    topOperator: "and"
});

The ListGrid also requires additional code to add the FilterBuilder. This will require adding a vertical layout (VStack), together with the grid and the button needed to add for applying the filter on the ListGrid. Also going to add a horizontal layout (HStack) which will contain the two already existing buttons used for saving all data and creating a new record:

isc.ListGrid.create({
    ID: "supplyItemGrid",
    width: 700, height: 224, alternateRecordStyles: true,
    dataSource: supplyItem,
    autoFetchData:true,
    dataPageSize:20,
    canEdit:true,
    canRemoveRecords:true,
    autoSaveEdits: false
});

isc.IButton.create({
    ID: "filterButton",
    title: "Filter",
    click: function () {
        supplyItemGrid.filterData(advancedFilter.getCriteria());
    }
});

isc.HStack.create({
    membersMargin: 10,
    ID: "gridButtons",
    members: [
        isc.IButton.create({
            top: 250,
            title: "Edit New",
            click: "supplyItemGrid.startEditingNew()"
        }),

        isc.IButton.create({
            top: 250,
            left: 100,
            title: "Save all",
            click: "supplyItemGrid.saveAllEdits()"
        })
    ]
});

isc.VStack.create({
    membersMargin: 10,
    members: [advancedFilter, filterButton, supplyItemGrid, gridButtons]
});

Also note, the filter has been removed top of the grid, as it is being replaced with the FilterBuilder.

Parsing the AdvancedCriteria

The AdvancedCriteria built by the FilterBuilder is sent in the JSON payload when doing a fetch() request. It is formatted like this:

// an AdvancedCriteria
{
    _constructor:"AdvancedCriteria",
    operator:"and",
    criteria:[
        // this is a Criterion
        { fieldName:"salary", operator:"lessThan", value:"80000" },
        { fieldName:"salary", operator:"lessThan", value:"80000" },
            ...  possibly more criterions ..
        { operator:"or", criteria:[
            { fieldName:"title", operator:"iContains", value:"Manager" },
            { fieldName:"reports", operator:"notNull" }
            {operator:"and", criteria: [
                .. some more criteria or criterion here
            ]}
        ]}
        },
        { operator:"or", criteria:[
            { fieldName:"title", operator:"iContains", value:"Manager" },
            { fieldName:"reports", operator:"notNull" }
        ]}
            .. possibly more criterions or criterias
    ]
}

As you can see it is a tree structure, with it's leafs being criterion and the nodes being criteria. To deserialize this JSON payload into an object, use the following class, called AdvancedCriteria, to simulate the tree structure:

public class AdvancedCriteria
{
    public string _constructor { get; set; }
    public string Operator { get; set; }
    public string fieldName { get; set; }
    public object value { get; set; }
    public object start { get; set; }
    public object end { get; set; }

    public AdvancedCriteria[] criteria { get; set; }
}

If the the criteria member is null, then it is a leaf, otherwise it is a node which has sub-criterias or sub-criterions.

Changes to DSRequest

Store an AdvancedCriteria property in the DSRequest object by adding a new property to it:

    public AdvancedCriteria advancedCriteria { get; set; }

The rest of the DSRequest's code remains unchanged. This new property will be initialized from the RPCManager which will load advanced criteria if the request has one during the parsing of the DSRequest objects.

Firstly, a helper method is needed in the RPCManager to decide if the DSRequest has AdvancedCriteria or not:

bool hasAdvancedCriteria(DSRequest<Dictionary<string, object>> req)
{
    return req.data.ContainsKey("_constructor") &&
        "AdvancedCriteria".Equals(req.data["_constructor"]) &&
        req.data.ContainsKey("operator");
}

Define a method which iterates over the list of requests in the transaction and for each load the advanced criteria if the request has one:

public void parseAdvancedCriterias()
{
    // find our request in the list of requests and set it's property to the deserialized
    // AdvancedCriteria object
    for (int i = 0; i < requestsAsMap.Length; i++)
    {
        if (hasAdvancedCriteria(requestsAsMap[i]))
        {
            DSRequest<AdvancedCriteria> crit = convertRequest<AdvancedCriteria>(requestsAsMap[i]);
            requestsAsMap[i].advancedCriteria = crit.data;

            break;
        }
    }
}

Now modify the processTransaction in RPCManager to parse the advanced criteria:

private ActionResult processTransaction()
{
    // retrieve the requests with data in form of supplyItem
    TransactionRequest<Dictionary<string, object>> transactionRequest = parseTransactionRequest<Dictionary<string, object>>();
    requestsAsMap = new DSRequest<Dictionary<string, object>>[transactionRequest.operations.Length];
    transactionRequest.operations.CopyTo(requestsAsMap, 0);
    requestsAsJSON = new string[transactionRequest.operations.Length];
    transactionRequest.strOperations.CopyTo(requestsAsJSON, 0);

    // parse advanced criterias, if any
    parseAdvancedCriterias();

    .. rest of the procesTransaction code
}

Refactor the existing executeFetch() method and move out the existing criteria building code into a separate method called buildStandardCriteria():

virtual public DSResponse executeFetch<T>(DSRequest<T> request)
{
    DSRequest<Dictionary<string, object>> req = request as DSRequest<Dictionary<string, object>>;
    DSRequest<supplyItem> itmreq = request.RpcManager.convertRequest<supplyItem>(req);

    var query = request.RpcManager.Session.CreateCriteria<supplyItem>();

    if (req.advancedCriteria == null)
    {
        buildStandardCriteria(itmreq, req, ref query);
    }
    else
    {
        buildAdvancedCriteria(req, ref query);
    }

    // add sorting
    if (req.sortBy != null)
    {
        // add the sorting
        foreach (string column in req.sortBy)
        {
            // if column name is with -, then ordering is descending, otherwise ascending
            if (column.StartsWith("-"))
            {
                // if sort is descending, then we have to remove the '-' from the beginning to get te correct
                // column name
                query.AddOrder(Order.Desc(column.Substring(1)));
            }
            else
            {
                query.AddOrder(Order.Asc(column));
            }
        }
    }

    // create a response object
    DSResponse dsresponse = new DSResponse();

    // set start row and number of rows on the query itself
    if (req.endRow != 0)
    {
        query.SetMaxResults(req.endRow - req.startRow);
        query.SetFirstResult(req.startRow);
    }

    // get the requested number of objects
    var products = query.List<supplyItem>();

    // change projection to get the total number of rows
    // we need to clear the result range and the ordering for this to work
    query.SetProjection(Projections.RowCount());
    query.SetFirstResult(0);
    query.SetMaxResults(int.MaxValue);
    query.ClearOrders();

    // set total rows using the projection
    dsresponse.totalRows = (int)query.UniqueResult<int>();

    // set the response data
    dsresponse.data = products;
    dsresponse.startRow = req.startRow;
    dsresponse.endRow = dsresponse.startRow + products.Count();

    // sanity check, if no rows, return 0
    if (dsresponse.endRow < 0)
    {
        dsresponse.endRow = 0;
    }

    dsresponse.status = 0;

    return dsresponse;
}

void buildStandardCriteria(object req, DSRequest<Dictionary<string, object>> reqmap, ref ICriteria query)
{
    // build the criteria
    if (reqmap.data.Keys.Count != 0)
    {
        foreach (string key in reqmap.data.Keys)
        {
            Dictionary<string, object> field = getField(key);

            // make sure the field is in the DataSource
            if (field != null)
            {
                // get the property value
                object data = req.GetType().GetProperty("data").GetValue(req, null);
                PropertyInfo pi = data.GetType().GetProperty(key);
                object val = pi.GetValue(data, null);

                string type = field["type"] as string;

                if (type.Equals("text") || type.Equals("link") || type.Equals("enum") ||
                    type.Equals("image") || type.Equals("ntext"))
                {
                    query.Add(Restrictions.Like(key, "%" + val + "%"));
                    break;
                }
                else
                {
                    query.Add(Restrictions.Eq(key, val));
                    break;
                }
            }
        }
    }
}

Note a new method called buildAdvancedCriteria() is required which builds the NHibernate criteria from the AdvancedCriteria delivered by the FilterBuilder:

void buildAdvancedCriteria(DSRequest<Dictionary<string, object>> req, ref ICriteria query)
{
    if (req.advancedCriteria != null)
    {
        AbstractCriterion restriction = buildCriterion(req.advancedCriteria);
        query.Add(restriction);
    }
}

For parsing the tree itself, introduce a recursive function:

AbstractCriterion buildCriterion(AdvancedCriteria criteria)
{
    string fn = criteria.fieldName;
    string op = criteria.Operator;
    object val = criteria.value;

    // fix type of value to the type specified in field declaration, if required
    if (fn != null)
    {
        fixValueType(fn, ref val);
    }

    // case when simple criterion
    if (criteria.criteria == null)
    {
        if ("equals".Equals(op))
        {
            return Restrictions.Eq(fn, val);
        }
        else if ("notEqual".Equals(op))
        {
            return Restrictions.Not(Restrictions.Eq(fn, val));
        }
        if ("iEquals".Equals(op))
        {
            return Restrictions.Eq(fn, val).IgnoreCase();
        }

            .. some operators removed for keeping code short ..

        else if ("between".Equals(op))
        {
            //greaterThan + lessThan + and
            return Restrictions.And(
                Restrictions.Gt(fn, criteria.start),
                Restrictions.Lt(fn, criteria.end)
                );
        }
        if (criteria.Operator.Equals("betweenInclusive"))
        {
            return Restrictions.And(Restrictions.Ge(criteria.fieldName, criteria.start),
                Restrictions.Le(criteria.fieldName, criteria.end));
        }

        return null;
    }
    else
    {
        // composed criteria
        List<AbstractCriterion> crits = new List<AbstractCriterion>();

        // build the list of subcriterias or criterions
        foreach (AdvancedCriteria crit in criteria.criteria)
        {
            AbstractCriterion itm = buildCriterion(crit);
            crits.Add(itm);
        }

        // return this criteria, using the right operator

        if ("and".Equals(criteria.Operator))
        {
            Conjunction cj = Restrictions.Conjunction();
            foreach (AbstractCriterion c in crits)
            {
                cj.Add(c);
            }

            return cj;
        }

        if ("or".Equals(criteria.Operator))
        {
            Disjunction dj = Restrictions.Disjunction();
            foreach (AbstractCriterion c in crits)
            {
                dj.Add(c);
            }

            return dj;
        }

        if ("not".Equals(criteria.Operator))
        {
            Disjunction dj = Restrictions.Disjunction();
            foreach (AbstractCriterion c in crits)
            {
                dj.Add(Restrictions.Not(c));
            }

            return dj;
        }

    }

    return null;
}

If criterion exist (criteria property in this case will be null), simply build the NHibernate AbstractCriteria and return it (this is the condition to exit from recursivity). If the criteria property is not null (the else branch), thencall the function again for each child criterion or criteria, and then assemble back everything using the specified operator before returning it to the caller.

In order to keep the previous code block and article short, some of the operators were removed from the article. Please check the source code in the attached Visual Studio solution to see the full list of implemented criterion operators .You will notice that the regexp, iregexp, inSet, notInSet, endsWithField, startsWithField and containsField operators are not implemented, as their implementation is not trivial and would go beyond the scope of providing a simple code example. There is also a helper function called fixValueType, which is used to convert the value stored in the criteria to the type required by the DataSource:

void fixValueType(string fn, ref object val)
{
    Dictionary<string, object> field = getField(fn);

    if (field.ContainsKey("type"))
    {
        string type = field["type"] as string;

        if ("date".Equals(type))
        {
            val = JsonConvert.DeserializeObject("\"" + val as string + "\"", typeof(DateTime));
        }

        if (val.GetType() == typeof(long))
        {
            long? lval = val as long?;
            if ("float".Equals(type))
            {
                val = (float)lval.Value;
            }
            if ("double".Equals(type))
            {
                val = (double)lval.Value;
            }
            if ("int".Equals(type))
            {
                val = (int)lval.Value;
            }
        }
        if (val.GetType() == typeof(int))
        {
            int? ival = val as int?;
            if ("float".Equals(type))
            {
                val = (float)ival.Value; ;
            }
            if ("double".Equals(type))
            {
                val = (double)ival.Value; ;
            }
        }
    }
}

At this point you should be able to run the sample and use the FilterBuilder and see it affect the grid entries.

A visual studio solution with the complete source code can be downloaded from here.