The ITL language is primarily intended for ETL workflows in the Data Warehouse, but ITL is not limit to ETL. If you load data into the Data Warehouse you probably want to use that data. Normally we use standard interfaces like ODBC, JDBC from GUI applications of users choice like MS Excel or Qlikview. But we also ship data to other applications and send mails and then ITL comes handy, since it is actually more of a job control system than just a language or an ETL processor. In this post I show how we generate PHP code on the fly to format HTML tables.
In ITL workflows are organised in jobs, jobs of type ‘sql’ execute sql queries, if the query produce a result table an sql converter post process the result e.g:
This job calls the HTML03.php sqlconverter to post process the sql query, creating an html table that looks like this:
I think this is a fairly nice table, the intention is not to create a dazzling web app , just to jazz up emails a bit. But this vanilla table did not satisfy the developers for very long. ‘How can I color the cells?’ Since I do not know HTML/CSS well I didn’t have an answer. After some reading I created ‘color classes’ in a CSS, that was easy the problem was how to implement logic in the job above to allow for flexible coloring of cells. I decided to utilize plain PHP code:
This is what I came up with, in <cellfunction> you can override the default <td> tag for any tuple/cell in the HTML table. This is just plain PHP code, and applied on the table above it looks like this:
If this is better than the first default table is a matter of taste, this is just a very simple example since you have PHP to back you up, you are free to format the cells to your taste. Inline CSS code is probably a wiser choice than my predefined classes, but I didn’t know of inline CSS when I started coloring the cells. (My knowledge of CSS/HTML is still 'vague'.)
Now I just had to grab the code from the job and apply it to all cells, I decided to create an anonymous function like this:
As you see I complement the code snippet from the job and assign this to the $f variable at the top, then I eval the string which results in the $cellfunc variable points to a closure which is called at the bottom of this code. To my surprise I could not create the closure directly but I had to eval the expression which I feel is a clumsy way to generate the closure.Now when we can create strawberry tables, toppings is requested, ‘I want to format rows depending on...’ I suspect when I can offer toppings someone will ask for mixins. But to achieve mixins I would probably need to hijack the PHPcompiler in some way to generate the mixin PHP code. Here you can read the next post about dynamic PHP code.