.. _dyn_fml: ========================================================================== 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. .. image:: ../_static/dyn_fml/dyn_fml.png 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 :ref:`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*. .. _dyn_cond: Dynamic Conditions ------------------ Dynamic Conditions are similar to Dynamic Formulas except for 2 things :- #. It must return a Boolean #. 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 :- #. 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. #. Application Resource access control, to decide if a user can access a resource, ie Programs, Reports, Commands, Business Rules and Events. #. 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. #. 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). #. 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. .. image:: ../_static/dyn_fml/dyn_cond.png 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. .. _dyn_cursor: 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 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 `` 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 ``. 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. .. _dyn_filter: 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 :- #. :ref:`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). #. 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). #. 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. .. _dyn_choice: 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 :- #. Application Parameter prompted choices rather than entered values #. 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.