General purpose dynamic formulas, conditions, cursors, filters and choices

When you want to design complex products for a specific industry, you may inevitably start to write more and more complex pieces of code to support the myriad features required by these products. What if these features changes in mid-course after going into production? Or some customers requires unique combinations of these product attributes? You would normally modify your code and provide a new version for your customers to upgrade to. Or worse, you may have different versions for each customer.

By using our Dynamic Formula, you can send them an SQL script instead. This script will modify the dynamic formulas used by the products in the database instead of your codebase. (It may be wiser to add a new formula and replace it in the product instead so that you can revert if anything goes wrong).

This will also allow you to provide different custom features to different customers using the same codebase. Just write variations of similar formulas and allow your customer to pick and choose.

Using our dynamic features will require a major redesign of your application, so it is best to gain a thorough understanding before you embark on your design. You need to figure out the what, when and how of each of the dynamics above.

What is a Dynamic Formula?

A Dynamic Formula is a python function that returns a python datatype, ie scalars (eg int, float, date), list or dict. It can also use the return values of other Dynamic Formulas. We call these Embedded Formulas. You can pass into this function a list of your model instances and named parameters (in a python dict). Which models and parameters to use depends on you, eg if you are designing a Payroll Computation, the model list could include the Employee instance and the parameters may have the Payroll Date.

When to use a Dynamic Formula?

Whenever you have computations that differ in their methodology, eg simple interest vs monthly rest, rather than having a flag as an attribute of your model, use a Dynamic Formula that returns a number instead. Pass in as parameters the required model instances, eg Invoice instance plus a Current Date (to compute interest until) as named parameter. You do this in your code, for example:

int_amt = my_model.interest_formula.execute(model_instances={'Invoice': inv_inst},
    params={'current_date': date.today()})

The above shows the code snippet where the interest_formula is called to compute the interest for a particular invoice. The model name (ie key in the model_instance dict) should not have the app_label.

The code snippet for that formula above should be using the fields of the Invoice instance (eg Balance Unpaid Amount, Last Interest Date etc) plus the named parameters to compute the interest. It should also use the Party (ie Customer) field in Invoice to get the interest rate to use.

How to use a Dynamic Formula?

First, you have to design your application, ie its models and transactions. Identify which transactions will benefit from the use of Dynamic Formulas. Then add a dynamic_formula foreign key field to your relevant models and call our dynamic formula execute() method, like above, in your transaction posting code.

Defining a formula is straight-forward.

Maintain Dynamic Formulas

Give your formula a unique code and description. The description should describe the methodology used. The description will be used in drop-down list of your models to select a formula. Then you must select a Result Type, ie for interest computation, this should be a float or Decimal.

Next you can specify the model(s) to be used as input to your formula. You can leave this blank if your formula does not use any. The Model used must be a comma separated list of app_label.model_name, eg “myapp.Invoice, myapp.Customer”. When you call execute(), you must pass in instances of this model in the model_instances dict, using just the model_name as key (without the app_label).

The Update by Administrators only flag is used to protect Maymyo’s formulas from being accessed by non-administrators. We use Automatic Filters for this to prevent non-administrators from accessing these formulas. Please have a look at the Model Restriction definition for infra.DynamicFormula.

Embedded Formulas

When you have many formulas which are long and complex computation, many steps may be similar. In that case, it would be wiser to split them into many formulas and reuse their results as Embedded Formulas in specific formulas which are different for each customer.

Using the results of other formulas requires 3 steps. The first is to define that formula. The second is to update their unique formula codes (as a comma separated list) in the target formula’s Embedded Formula field. Then in the Code Snippet, use the results of the embedded formula, let’s say ‘FORMULA-ONE’ as follows:

int_amt = computed_values['FORMULA-ONE']['result_1'] * int_rate / ... rest of computation ...

When we see an Embedded Formula list, we will execute them first and save their values (your embedded formula’s results will be the value in the computed_values dict. The key will be the formula code .So in the above example, the embedded formula FORMULA-ONE returns a dict with {‘result_1’: 123, ‘result_2’: 234, ...}. If your embedded formula returns a scalar (eg float), then you can simply use computed_values[‘FORMULA-ONE’]. The Embedded Formula can be recursive, ie calling another embedded formula.

The Code Snippet

This is where you insert your python code that does what your formula is supposed to. It must be a function named compute_formula like snippet below:

def compute_formula(model_instances, params, computed_values):
    from infra.objects.app_registry import get_posting_date
    from infra.models import Calendar
    from infra.objects.app_calendar import get_month_first_business_day, add_months
    try:
        cal_code = params['PARAM-2']
        cal = Calendar.objects.get(calendar_code=cal_code)
    except Exception:
        cal = None
    biz_day = get_posting_date(cal)
    try:
        adj_months = int(params['PARAM-1'])
        biz_day = add_months(biz_day, adj_months)
    except KeyError:
        pass
    return get_month_first_business_day(biz_day, cal)

It must use 3 parameters, named exactly (no differences in parameter names allowed) as above. The model_instances is for you to pass in a dict of instances as per your formula’s Models used. params is a dict of the arbitrary values you choose to pass in. The imports must be relative to your PYTHONPATH (of the OS user who runs Maymyo). If your formula is executed in the Task Queue, then it will use the Environment Parameter Set of the Application Command or Task Queue or the user who runs Maymyo daemons, in that order. (If however, the daemons are started up automatically by the Operating System, then we will be using snippets/envvars.ini.)

The above code will compute the current posting’s date minus an input adj_months, if any, first business day of the month. adj_months will be a positive or negative integer, eg 1 or -3, meaning next month’s and prior 3 month’s first business day respectively.

Your code snippet must return a value of the same datatype as specified in your Formula’s Result Type. If not, an exception (uncaught resulting in an ugly stack trace which will scare your users) will be raised.

You may wonder where did the named params PARAM-1 and PARAM-2 came from. It is passed in by our code. This formula is used to compute an automatic parameter value (to be submitted to reports or commands executed in Scheduled Jobs where it is not convenient to for a user to input a value at the scheduled time) for an Application Parameter. When we define that parameter value, we would use “1,CAL-CODE” and specify the formula above in its Dynamic Formula field. This is done in “Maintain Application Parameter Sets”. Please have a look at the code in infra.objects.app_parameter_set‘s get_formula_params function. By the way, the second parameter is the Calendar Code to use to compute the first business day (otherwise the Application Default will be used). You can see from the code that both parameters are optional, when PARAM-1 not passed in we will use the current posting date.

It is better to code your snippet using a Text Editor or your favorite IDE, then copy and paste. Upon saving, we will compile your snippet and raise a validation error if your code fails. However, we cannot protect you from runtime errors.

Be familiar with Maymyo first

Before you start writing any code snippets, it pays to be familiar with Maymyo, ie its models and object methods besides the functions in bin.helpers. For example, if you need something to do with dates, there may already be a method available in infra.objects.app_calendar or infra.objects.app_datetimes.

Dynamic Conditions

Dynamic Conditions are similar to Dynamic Formulas except for 2 things :-

1. It must return a Boolean
2. It cannot used embedded conditions.

Use it like a Dynamic Formula that returns a Boolean, eg when deciding to allow or disallow certain transactions. Usually you will use the named parameters to pass in attributes of your transaction to check against a model instance, eg a User to see if she can be allowed to enter a certain amount.

Where it is used in Maymyo

We use Dynamic Conditions in :-

1. Application Job and its Job Steps, to decide whether to allow execution of the Job or any of its steps. The typical conditions we use will test if a particular closing date is a End of Business Day, Week, Month and the like. We can then setup a big job that combines End of Day, Week, Month, Quarter and Year closings to their own steps and run this job daily. If current day is a end of week, then only the End of Day and Week steps will run, the others will be skipped. every day. It will then only run those tasks when the current day meet its condition.
2. Application Resource access control, to decide if a user can access a resource, ie Programs, Reports, Commands, Business Rules and Events.
3. Workflow document routing, to route a document depending on its attributes, eg when more than 1 Million, than route to Senior Manager, otherwise can be posted immediately.
4. Archiving of Model’s data. Decide when to archive and purge its data (in conjunction with a Dynamic Filter to select data to archive or purge).
5. Application Events, to decide which Registrant should be informed of an event based on its attributes. Registrants can choose to be informed only when certain transaction attributes are above a threshold value.

Maintain Dynamic Conditions

The above shows an example of a Dynamic Condition that test where the Current Posting Date is the End of Quarter or not.

Dynamic Cursors

Cursors are code snippets that yield rows of data, which can either be selected using SQL directly from the database (SQL Cursor Type) or using django’s ORM (python Cursor Type). The row returned is a dict with the field name (or column name) as key.

We use it to generate text reports against our models. Please have a look at infra/custom/text_engine.py. This is a Reporting Engine that produces text files, either CSV or fixed-format. We will explain how we use them.

Let’s look at the Code Snippet of 2 existing cursors that we use. Both of them produces exactly the same set of rows against our ValueSet and ValueSetMember models (they are parent and child).

SQL Cursor Type:

SELECT vs.value_set_code, vs.value_set_description, vs.maximum_length, vs.is_app_constant,
    vm.value_code, vm.value_description, vm.attribute_1, vm.attribute_2,
    vm.attribute_3, vm.attribute_4, vm.attribute_5
FROM :db_name.if_value_set AS vs
JOIN :db_name.if_value_set_member AS vm
ON (vm.value_set_id = vs.id)
WHERE <CONDITIONS>
ORDER BY vs.value_set_code, vm.value_code

python Cursor Type:

from infra.models import ValueSet, ValueSetMember
from django.forms.models import model_to_dict
def yield_rows(pos_params, named_params, filter_predicates, limit):
    qs = ValueSet.objects.filter(**filter_predicates)
    for vs in qs:
        vm = vs.valuesetmember_set.all()
        for row in vm.values():
            # Add ValueSet fields
            row.update(model_to_dict(vs))
            yield dict(row)

Let’s start with the SQL one first. The snippet must be a SELECT statement against the database tables, which needs to be prefixed with the database name (:db_name, a named parameter passed in). The database table name is usually named by django automatically, using the app_label_model_name, eg myapp_mymodel. You should use standard SQL 92 syntax so that it will run against all the databases supported. You must always provide a WHERE <CONDITIONS> as a placeholder for Maymyo to replace with Auto-filtering criteria plus user entered parameter values prompted when the report is run from the menu. If your query has a where clause, then you should use WHERE (myfield = 'field_value' OR ....) AND <CONDITIONS>. Always enclose your Where Clause with (). Each column in the SELECT statement will be returned in a dict as {'column_name': column_value,,,}.

For the python cursor type, you will notice that we use a generate function which must be named yield_rows. This function must accept 4 parameters as shown above. The code you see above is uses django ORM. We import the necessary models and the model_to_dict function from django.forms. Because we are returning a row that combines a child with its parent, we have 2 for loops to combine the child row’s values() dict with the parent (row.update(model_to_dict(vs)) above). Then the function must yield a dict for each row.

So you can use either of the above cursor in a for loop, eg:

dyn_cur = DynamicCursor.objects.get(cursor_code='MY-CURSOR')
for row in dyn_cur.get_cursor(pos_params, named_params, conditions):
    ... do something with row

Your code will have to prepare the positional parameters (pos_params), named parameters (named_params) and conditions, which is a dict with the field_name as key and a list of [operator, search_value] as value. Maymyo will construct the conditions dict from the values prompted from the users when the report is run. See our infra.objects.app_parameter_set.prepare_parameters for this.

So take look at our infra.custom.text_engine, which is instantiated by an Application Report which uses a TextEngine Reporting Engine. Its run_report method is called after a user has entered the parameters (eg to select a subset of rows to print) and selected the output format. The relevant section of the code that uses the dynamic cursor is

# Get Cursor returns a row which is a dict of name:value
for row in app_report.dynamic_cursor.get_cursor(pos_params, named_params, conditions):
    if output_format.value_code == 'CSV':
        field_values = [row[field] for field in field_order]
        csv_writer.writerow(map(stringify_value, field_values))
    else:
        # Handle as text
        for field in field_order:
            # Right pad field value
            output_handle.write(stringify_value(row[field]).ljust(field_defs[field][1]))
        # add a newline to end each row
        output_handle.write("\n")

The for loop will call the dynamic cursor’s get_cursor method to yield rows. In the above code, we output each row in either CSV or fixed-format text to an output file (using its handle output_handle).

So you would have to put on your thinking cap to see how you can use Dynamic Cursors in your application. It will be most useful for a python based reporting tool like Dabo, which we have not enough time to explore.

Dynamic Filters

The Code Snippet of a Dynamic Filter must return a dict of filter predicates that matches the parameters you can use with QuerySet’s filter() method, eg {'field__operator': search_value,,,}. Each Dynamic Filter must have a single Queried Model. Just like the other dynamics, you can pass in model_instances to your snippet. By using Dynamic Filter, you can allow your users (we mean their Administrators) to customise your application, instead of changing your code.

Maymyo uses Dynamic Filters in :-

1. Auto-Filtering of accessible rows by Users. A Model’s or User’s default restriction can be specified using a dynamic filter instead of being a literal value (or list).
2. Archiving and Purging, the rows to be archived and purged for each model uses a dynamic filter, so that the window of available rows can be customised. Some may prefer 2 years of transactions kept online (instead of the standard 2 months).
3. Dynamic Choice, normally its Queried Model will select all rows. We can filter it by applying a Dynamic Filter so that it will return a subset of rows.

Let’s look at an example that we use in Auto-filtering:

def get_predicates(model_instances, params):
    from django.utils.translation import ugettext as _
    from common.objects.branch import get_user_branches
    from infra.custom import CustomException
    # Get the user that is passed in
    usr = model_instances.get('User')
    if usr is None:
        raise CustomException(_('User must be passed in to get his Branches'))
    # Get user's accessible Branches
    branch_list = get_user_branches(usr)
    if branch_list:
        if len(branch_list) == 1:
            # Return a = predicate
            return {'branch': branch_list[0]}
        else:
            # Return a proper in predicate
            return {'branch__in': branch_list}
    else:
        return {'branch': -1}

The above Dynamic Filter is used to return a filter predicate for a User’s accessible Branches. Some transactional models may have a Branch field and we want to allow a User to see only those Branches in her accessible list. The above code snippet will return a filter predicate to use against that transactional model.

As usual, the function name is fixed, same as its parameters. The function must return a dict that looks like {'field__operator': search_value,,,,}. It can also be an empty dict or a dict with multiple elements (as long as the key has a field of the Queried Model). The field__operator syntax is exactly what is used by QuerySet’s filter() method. You can also use related fields, eg field__related_field__operator.

Back to our code snippet above. After the necessary imports, we retrieve the User instance passed in by the caller (from the model_instances dict). Then we get the user’s accesible branches by calling the function get_user_branches which returns a list. If the list has 1 element, then we use the exact operator, ie {'branch': branch_list[0]}. When more than 1 element, then we use the in operator and supply the branch_list unchanged. Finally, when the user cannot access any branches, then we return a predicate that queries for a branch with id -1, which should return 0 rows (as there are no ids which are negative).

If you want to know how get_user_branches really works; from the User instance, we know which Business Unit she belongs to (she must belong to one). She may also have a list of accessible Business Units (in addition to her home Business Unit) defined in her User Profile. These becomes a list of Business Units. Now each Business Unit may have children Business Units. The code will traverse down each accessible Business Unit. Finally, we know that a Branch is actually a physical Business Unit, ie we will return all occurences of Business Units which are also a Branch.

Dynamic Choices

Dynamic Choices returns a list of [code, description] pairs to be used in ChoiceFields, which uses the HTML Select form widget. You can use it for your model’s field whose choices can only be decided at runtime.

We use it for :-

1. Application Parameter prompted choices rather than entered values
2. Message Type’s Reply Value, eg user can only reply Yes or No only rather than entered values.

If you use it, you should dynamically change your form field’s widget, like what we did for a MessageQueue’s reply_value field in its __init__ method, as below:

# Reply Value to be turned into choice field?
if instance.message_type.dynamic_choice:
    self.fields['reply_value'].widget = forms.Select()
    self.fields['reply_value'].widget.choices = instance.message_type.dynamic_choice.get_choices()

The block of code is from infra.forms.message_type, at about line 176, in the ActionMessageQueueForm class. When it detects the message’s Message Type uses a Dynamic Choice, then it will change its form widget to use the Select widget and change its choices to whatever is returned by the Dynamic Choice. When the user selects a choice, the code will be saved in the Select input field.

You choose to sort by its Description (default is by code, which the user cannot see) and change the sort order to Descending. There is no Code Snippet for Dynamic Choice. You will need to specify which fields of the Queried Model will yield the code and description and we will construct the code automatically.