OpenERP steps for Pentaho Kettle/PDI

Pentaho Kettle / PDI is a powerful tool to transfer data between systems.

If you're running OpenERP in together with other applications, and need them to share data, this should be the first place to look at. Data is extracted using "input steps", transformed and then loaded using "output steps".

Kettle has specialized steps for OpenERP, that interact with the OpenERP server in the proper way - through the XML-RPC API. Unfortunately these steps are not yet available in the stable version.

You try them out using the latest development version. The development versions, however, are not well suited for usage in a production environment. Wouldn't it be nice if you could install these OpenERP steps in a Kettle stable version?

Well, I gave a try at this, and got them working with the 4.3.0-stable version. Here's the recipe:

Download the trunk version and copy the following files to the stable version:

• from \data-integration\plugins\kettle-openerp-plugin\*, and 
• from \data-integration\libext\webservices, the files:
• ws-commons-util-1.0.2.jar 
• xmlrpc-client-3.1.3.jar, and  
• xmlrpc-common-3.1.3.jar. 

 Now (re)start Spoon and a "!BaseStep.Category.OpenERP!" will be available containing three new steps: Delete, Input and Output.

Note that for some reason the "Test" button in the "Database Connection" dialog doesn't work correctly: it reports an error when testing the OpenERP server connection, even though the connection is correct. I believe that this is a bug that has been fixed in later development branches.

A big advantage for using the OpenERP API instead of the direct access to the database tables is the requests go through the ORM, so you get improved data consistency and take advantage of the ORM's create() and write() logic, and model default values.

On the downside, these steps don't handle XML Ids, only database (integer) ids, so you need to set your own strategy to identify records, such as using "reference" or "code" fields. If your record identification relies on XML Ids,  then import_data() base tools, such as import_sqlpush, may still be a good option.

Pushing data into OpenERP

The PushSQL module allows external apps to export data to OpenERP properly (through the ORM) just by using INSERT SQL statements.

The motivation for this came from a project where an ETL service was responsible for handling synchronization of data (such as departments, customers and contracts) across several databases and applications.

The ETL tool didn't have a simple way to make XML-RPC calls, and writing directly to OpenERP's tables is not a good solution.

This was solved using a relatively simple "hybrid" solution. The PushSQL module provides a database table for external apps to write data to imported to OpenERP, using plain SQL INSERT statements. A scheduled job regularly looks for new rows in this table and processes them, making the intended changes to OpenERP data.

The table , import_pushsql, has two mandatory columns: model and data. The first is the target model, such as res.partner. The second contains the data to import, using the format for CSV files, but using TAB instead of comma as the column separator.

For example, using PSQL:
INSERT INTO import_pushsql (model, data) VALUES ('res.partner', E'id\tname\tref\nres_partner_XXX\tDemo Customer\tXXX') 

Or, if using SQL Server:
INSERT INTO import_pushsql (model, data) VALUES ('res.partner',
'id' +char(9)+ 'name' +char(9)+ 'ref' +char(10)+ 'res_partner_XXX' +char(9)+ 'Demo Customer' +char(9)+ 'XXX'
)

The scheduled action uses this to run a standard import_data ORM method,  writes the result into the log column, and changes the state to 'done' if successful, or 'cancel' if an error was found.

Compared to the "pull" approach used by the import_odbc module, this "push" approach can provide a few advantages:

•  No need to install other database clients and middleware in OpenERP's server.
• It uses less resources on OpenERP server, because there is no need to connect to other servers.
• Event driven data sync, where an action in another system immediately triggers a write on OpenERP that can be processed within a few minutes.

There is also an obvious disadvantage: the process exporting the data does not have an immediate visibility on the outcome of the OpenERP transaction . There are ways to provide this feedback, but it's not immediate.

Overall, the PushSQL module can provide a very simple and straighforward way for business apps to safely send data to OpenERP, in scenarios where using the API is not an option or would be too cumbersome.

The import_pushsql can be downloaded either from OpenERP Apps or from Launchpad using: bzr branch lp:reis-openerp-addons

Import ODBC module steps up

The import_odbc module for OpenERP was recently improved and moved  to the OpenObject Extension community project. the original module is now split in two: base_external_dbsource and import_odbc.

The import_odbc code was refactored, but keeps the same funcionality as before. Documentation was improved and some examples are provided out of the box.

The base_external_dbsource was created to isolate the logic for connecting to external databases and executing SQL queries. It's used by import_odbc, but it also enables other modules to fetch foreign data on the fly.

Additionally, the connection types supported were extended. Support for ODBC and Oracle native connections is kept, and support for other native connections was added:

• MS SQL Server
• MySQL
• PostgreSQL
• SQLite

With this evolution, it would be more fair to name the module as "import_sql" instead of the "import_odbc", but the name was kept unchanged to avoid confusing current users.

You can get the latest version of these modules from the OpenObject Extension trunk branch:

bzr branch lp:openobject-extension

Share you experiences and feature wishlists with us!

Using codes to search and enter data in OpenERP forms

Using codes to refer to entities, such as customers or employees, is a common practice, and widely used in ERPs. To users familiar with this method it's natural to have the code displayed alongside with the description, and to enter data in a form field by directly typing a code.

This feature can be easilly added to OpenERP using the refcodes family of modules, available in Launchpad. For example, in the HR module an employee selection list might look like this:

Have fun.

Trigger-like Automated Actions

OpenERP's Automated Actions is a module usually associated with the CRM modules, allowing to automate sales process steps, without the need to write Python code.

Each automated action rule operates on an OpenERP model, any model. It's  behaviour is defined through three tabs:

• Conditions tell when the rule is fired.
• Actions tell what operations should be done, like setting a specific user as Responsible person, or running a Server Action.
• E-mail actions allows to define e-mail messages to be sent .

Conditions can be activated at scheduled moments. This is the sole purpose of the "Conditions on Time" section. The scheduler action evaluates these conditions to decide what rules to trigger. This allows you to configure actions like "send an e-mail reminder two days after last action date".


The remaining conditions are evaluated immediately when a record is saved. This allows you to configure actions like "send an e-mail when state is changed to In Progress".


While you get the feeling of having a lot of potential to easily customize a customer's very specific business rules, actually the module reveals to fall short on the expectations.
For instance, it's not possible to create conditions for:

• When a new Issue is created, sending an e-mail informing the new Issue;
• When the Responsible is changed, sending an e-mail informing the new responsible person.

Also, the e-mail actions definition is very confusing and limited. The e-mail Subject line is not configurable, and the message body can only use a small list of keyword to include data from the document.

The reis_base_action_rule_triggers module overcomes these limitations and unlocks a lot of the power of the standard module. This is done through two simple additions:

• Conditions can be defined by an "Evaluated expression", that can work on record's old and new values.
• Actions can send an e-mail using v6.1 Email templates, a lot more powerful than the "e-mail actions" options, making them obsolete.

In the condition expressions some variables are available (inspiration came from Oracle's database triggers):

• inserting, creating: boolean indicating it's a new record is being created;
• updating, writing: boolean indicating it's an existing record being changed;
• new: a dictionary with all the values after the record is changed;
• old: a dictionary with all the values before the record was changed;
• changed: a dictionary with only the values that actually changed, even if rewritten (empty if not updating);
• obj: browseable record object, allowing dot notation, with the new/changed record.

Here are some real examples used in a production environment for the Project Issue module:


New issue is created, or is reactivated:
inserting or changed.get('state') == 'draft'


Issue is closed or cancelled:
changed.get('state') in ('done', 'cancel')


Issue's Responsible changed:
old.get('user_id') and new.get('user_id')
and old.get('user_id') != new.get('user_id') 
and not new.get('date_open')  #date_open is written only by Open issue

I hope this turn out useful to more people, and would love to hear about your experience with this module.

How to: allow Project Users to record Time on Tasks for anyone

If you have hr_timesheet installed, users also in the Employee group will only be able to record time on tasks for themselves. If you need Project Users or Managers to be able to record time for other persons, just add the following Record Rule:

An OpenERP Apps and dependencies installer

While the apps.openerp.com site provides access to community modules, there is  no way to automatically download a module and all it's dependencies. The problem is even worse if the dependency modules are hosted in different branches.

For this, I created a tool to assist in downloading a module and all it's dependencies, in a way similar to Python's pip or Ubuntu's apt-get.

Here is a quick-start demo:

First, position your command line on your target "addons" directory. For demonstration purposes, let's use a temporary location:

mkdir /tmp/addons
cd /tmp/addons

Now install the "apps" tool and specify the OpenERP version you are working with:

bzr checkout --lightweight lp:~dreis-pt/+junk/apps
apps/init 6.1

Finally, use "apps/get" to download the modules you need. The tool will search in an index for their branch location, download and copy to the current addons directory. Let's try it with two different asterik related modules:

apps/get asterisk_click2dial asterisk360

The apps/get command depends on pre-genererated index files. The program to generate these indexes is also provided, but that's a long running operation, ment to be regularly executed by a server.

I know there are a lot of things to improve, but the current version is perfectly usable. I feel this could be an important tool for the community, so comments would be very welcome.

Connect to SQL Server from Ubuntu

Connecting an Ubuntu box to a MS SQL Server can by achieved through the FreeTDS ODBC driver.

Here goes a small recipe to install it:

sudo apt-get update

sudo apt-get install tdsodbc

sudo apt-get install unixodbc unixodbc-dev

sudo apt-get install python-setuptools

sudo easy_install pyodbc

sudo echo -e "[FreeTDS]\nDriver=/usr/lib/odbc/libtdsodbc.so" >> /etc/odbcinst.ini

Now you can open a Python interpreter and test the connection using pyodbc.
This may speed you up if you're trying to import SQL Server data to OpenERP.

Importing data from ODBC sources

With the import_odbc OpenERP  you can import data directly from other databases, such as Oracle or SQL Server, and schedule them to run regularly. Usage examples are to automatically update Customer data from a CRM app or Employee data from a HRIS/Payroll app.

The import is done using the standard import_data() ORM method, used when importing data through the user interface. So, it benefits from all its features, including relationship reconnecting (:id or /id columns).

Each import has a SQL statement used to build the equivalent for an import file. The column titles are given by the SQL's column names.

The first column in the SQL should provide a unique identifier for each record, and will be used to build it's xml_id. This id allows subsequent imports to update previously imported rows, instead of duplicating them. The xml_is is built using the form [MODEL_ID]_id_[UQ_ID]. For example: product_product_id_9999. This is important to remember when importing several tables with relationships between them. Columns titled "None" are ignored, so if you don't need to write the unique id to OpenERP, you can name the SQLs first column as "None".

For example, to keep the Products table updated with a product list in another company database, the SQL would look like this:
SELECT ID as "None", PRODUCT_CODE as "ref", PRODUCT_NAME as "name", 'product_category_id_'+CATEGORY_ID as "categ_id/id"
FROM T_PRODUCTS 
WHERE DATE_CHANGED >= ? 

The "?" is replaced by the last successful sync date. This date is updated after each successfull import run.
This way it's possible to import only the rows changed since last execution, so you can schedule frequent low-volume imports.

Recently a  feature was added to be able to tolerate to errors when trying to reconnect relationships. Using the previous example, if the product supplier's xml_id is not found, the product import would fail. But if you activate the "Ignore relationship errors" flag, the import would be retried without the "product_category_id/id" column, importing the product with an empty Category field.

You can find the import_odbc module in Launchpad's branch:
lp:~dreis-pt/addons-tko/reis.

Webkit reports in a X-less server

If you try to run webkit reports in linux system without an X-server (no system GUI), you will probably get error message. This can be caused by the wkhtmltopdf library, used by Webkit report to convert HTML into PDF, because needs to use an X-server to do the conversion.

To can confirm if this is your problem with this:
# echo test>in.txt; wkhtmltopdf in.txt out.pdf

wkhtmltopdf: cannot connect to X server

A workaround to this is to install the static version of the wkhtmltopdf library, (more details  info on ). The procedure, using a root account, is this:

# apt-get update
# apt-get remove wkhtmltopdf
# apt-get install openssl build-essential xorg libssl-dev
# cd /tmp
# wget http://wkhtmltopdf.googlecode.com/files/wkhtmltopdf-0.11.0_rc1-static-i386.tar.bz2 
# tar xvjf  wkhtmltopdf-0.11.0_rc1-static-i386.tar.bz2
# chown root:root wkhtmltopdf-i386
# mv wkhtmltopdf-i386 /usr/bin/wkhtmltopdf
# rm wkhtmltopdf-0.11.0_rc1-static-i386.tar.bz2

Now test it again. You should get this output:

# echo test>in.txt; wkhtmltopdf in.txt out.pdf

Loading pages (1/6)

Counting pages (2/6)

Resolving links (4/6)

Loading headers and footers (5/6)

Printing pages (6/6)

Done

Remeber you must provide the path to wkhtmltopdf in the Company form (at Settings/Administration » Companies » Companies » Webkit tab » Webkit Executable Path). Usualy it's /usr/bin/wkhtmltopdf, but you can confirm that with this command:

# which wkhtmltopdf

Configuring LDAP authentication with Active Directory

First install the python-ldap library:
[sudo] apt-get install python-ldap

Then install OpenERP's user_ldap module.
Go to Administration » Companies, select your company, find the "Configuration" tab, and on the "LDAP Configuration" box click "New" to add a LDAP configuration.

Complete the requested information:

• LDAP Server address: your.adserver.address
• LDAP Server port: 3268
• LDAP binddn: your-domain\a-username
• LDAP password: your-password
• LDAP base: DC=your-domain,DC=local
• LDAP filter: sAMAccountName=%s
• Create User: Yes
• Model User: Your template user

The parameters bindn and password can be ignored  if the AD server is configured to allow anonymous connections. In this case, we preferred to create a generic user to connect to the AD.

When someone tries to login to OpenERP for the first time, it's full name is retrieved from the AD and a new Openerp user is created copying from the template user. So, this template user should have assigned default access groups for everyone in the domain.

You might need to adjust some of these setting to your specific AD structure. I found Softerra's free LDAP browser to be useful to explore the AD structure, or just to test the LDAP configuration parameters.

OpenERP easy installation using SISalp's xoe script

SISalp has developed a script to automate OpenERP server installation, and was kind enough to share it to the public with GPL license. Let's use it to build an OpenERP server based on a clean Turnkey LAPP appliance.

First, update the virtual machine's system.
After installing the virtual machine, which should be pretty straightforward, you might want to update the system. Be aware that there is a known issue with the udev library, causing the update of Turnkey appliances process to freeze. To avoid it you should do the update using:


apt-get update
echo udev hold|dpkg --set-selections
apt-get upgrade



Second, install OpenERP, using SISalp's XOE script.
Following the instructions, logged in as root, execute:

cd /usr/local/bin
wget http://download.sisalp.net/install_xoe
chmod 755 install_xoe
./install_xoe

And that's it.

The script will create the PostgreSQL database, install the application server, the web server and register them as services. The monitoring and management of the servers can be done through the xoe utility. For example, use xoe --status to check which services are running.

I find very interesting the ability to setup and manage other servers, for training and testing purposes. The documentation also mentions the possibility of setting up backups and e-mail alerts to the system administrator. The tool is rich in features, but it would be nice to have a little more documentation available, such as a good guide for all these functions.

EDIT: install_xoe_openerp is deprecated - install_xoe should be used instead.