Archive

Posts Tagged ‘XML’

Automated actions in OpenERP

December 28, 2011 Leave a comment

In OpenERP, as in any other ERP or system, you need to perform certain tasks regularly. Tasks such as performing a backup, calculating ABC categories, running the MRP planner, etc. How do you get this done in OpenERP? It’s quite easy and you don’t need your server administrator to get this done. Scheduling tasks in OpenERP is done via the Scheduled Actions in the Administration > Configuration > Scheduler menu.
Scheduled Actions view
In this section, you will be able to create new actions. So click on the New button in the view.

After clicking New you will see a form that allows you to enter information in two tabs, an Information tab and a Technical tab. In the first tab you will be able to enter the name of the action, whether it is active or not, and how regularly it is executed. In the technical tab, and you might need a programmer help for this, you have to enter the name of the object that will run the action; along with the function to schedule and its arguments. This is the tricky part of scheduling an action, since you need to get into the details of how the system works and you will need your programmer to dig into the object code. But it is worth it, once you understand how this works, you will be able to get many things accomplished.

ABC Analysis in OpenERP

December 26, 2011 Leave a comment

ABC analysis is very well covered in this Wikipedia article. So no point of talking about it again here. Thing is, how do you implement it in OpenERP.

Polish OpenERP screenshot

Image via Wikipedia

Truth is, it is not a great deal. You only need to pull the sale order lines for the past six months (for example, could be a year as well or the period under analysis), then by product summarize its sales (not taking into account any discounts) and then divide the sales number by the total sales amount, in order to figure out the product ABC Category. If you are an experienced programmer or data analyst, you can do this in minutes. If not, does not take long to figure out how to query the OpenERP database.

Thing is, this information is not integrated into OpenERP yet (and it makes sense, since you do not need this information for the MRP application. It’s nice to have it, but it is not critical). So I created a module that regularly categorizes OpenERP products according to its sales. I have not finished it yet and I need to upload it to LaunchPad, but I think I will do it this week.

What does this module do? Allows you to create ABC categories (you can have as many as you want) where you define the category and its range. Then regularly (as an scheduler action) categorizes the products taking into account their sales lines. Then updates the product abc_category field (created previously by the module installer).

I still need to polish the module and add a couple of files, such as a XML file with initial information (specially for the scheduler and categories). As soon as I get it done, I will upload it to LaunchPad and publish it.

Cheers!!!!

Business Intelligence, integrating OpenERP and Mondrian

Crystal Reports

Image via Wikipedia

These days we are implementing a custom module we developed for a client, and the customer asked us for hundreds, if not  thousands, of reports. So, faced with the option of devoting hundreds of development hours to create reports, we decided to implement an OLAP and reporting tool for the customer. And integrating that tool with OpenERP. After making a quick assessment among the different business intelligence tools available in the open source “market”, we decided to go with JasperServer and iReport.

You can download both tools from the JasperForge website. Installing them was quite simple and straightforward, and in no time I was running reports against the OpenERP database. iReport is a report generation tool, and reminds me of Crystal Reports. JasperServer is a report and OLAP server. It can display the reports created with iReport and work with Mondrian cubes (more on this later).

I will not go into the details on how to work with both tools. I would like to talk about how I generated a cube from an existing OpenERP database. Well, this is the “DON’T TRY THIS AT HOME” section. Doing what I will explain in the next paragraphs is a very bad idea in a production setting, since OLAP calculations are resource hungry on the server, and this could run out of resources pretty soon if you are servicing your users and OLAP calculations.

Well, after making the disclaimer, I will proceed into explaining how to create an OLAP (actually ROLAP since in no moment I created a physical cube) from the OpenERP database. The first step is to create the Data Source in JasperServer. It is quite straightforward and JasperServer comes with a Test button which allows you check whether the connection settings you just created are right or wrong.

The next step is to create an OLAP schema. An OLAP schema in JasperServer (and the Mondrian world) is a XML file which pretty much describes the OLAP cube. In this file you describe the fact table, the dimesions, its elements and aggregations. For example, below you will find an example I created for the sales order table in OpenERP:

<?xml version=”1.0″?>
<Schema name=”SaleOrderSchema”>
<Cube name=”SaleOrder”>
<Table name=”sale_order”/>
<Dimension name=”Cliente” foreignKey=”partner_id”>
<Hierarchy hasAll=”true” allMemberName=”Todos los clientes” primaryKey=”id”>
<Table name=”res_partner”/>
<Level name=”Cliente” column=”name” uniqueMembers=”true”/>
</Hierarchy>
</Dimension>
<Dimension name=”Picking Policy”>
<Hierarchy hasAll=”true” allMemberName=”Todas las policies”>
<!– No table element here.
Fact table is assumed. –>
<Level name=”Picking Policy” column=”picking_policy” uniqueMembers=”true” />
</Hierarchy>
</Dimension>
<Dimension name=”State”>
<Hierarchy hasAll=”true” allMemberName=”Todos los estados”>
<!– No table element here.
Fact table is assumed. –>
<Level name=”State” column=”state” uniqueMembers=”true” />
</Hierarchy>
</Dimension>
<Dimension name=”Order Policy”>
<Hierarchy hasAll=”true” allMemberName=”Todos las ordenes”>
<!– No table element here.
Fact table is assumed. –>
<Level name=”Order Policy” column=”order_policy” uniqueMembers=”true” />
</Hierarchy>
</Dimension>

<Measure name=”Cantidad” column=”id” aggregator=”count” formatString=”Standard”/>
<Measure name=”Monto” column=”amount_total” aggregator=”sum” formatString=”Standard”/>
</Cube>
</Schema>
In this example, I have a fact table which is based on the sale_order table with three inline dimensions (order policy, picking policy and state) and the partner dimension. The cube has two measures, which is quantity and amount. The next step is to create the analysis connection, which connects the OLAP schema with the datasource. And the final step is to create the view. An analysis view is a query that pulls the information from the database, this is done with MDX, which is a query language similar to SQL for pulling OLAP information. An example of an MDX query is listed below:

select {[Measures].[Cantidad], [Measures].[Monto]} ON COLUMNS,
NON EMPTY {([Picking Policy].[Todas las policies],[State].[Todos los estados],[Order Policy].[Todos las ordenes])} ON ROWS
from [SaleOrder]

After creating the view (and entering the MDX) you will be ready to work with the OLAP cube. Just click on the view and you will see an screen like this,

Now you will be able to slice and dice your information,

Cheers!

Configuration parameters in OpenERP

January 12, 2011 5 comments

One of the first things you need to configure when you start working with OpenERP is setting its parameters right. Parameters are default values taken by the system while it performs certain operations. For instance, which default pricelist to use while creating a Sales Order. Or the account to debit while creating an invoice.

You can find the Parameters list in the Administration >> Configuration >> Configuration Parameters menu option

You will see a list with over ten parameters; parameters such as account_expense, account_receivable, product_pricelist, etc. Just select a parameter and assign a value to it. I will give you an example. You want your new Sales Orders and Invoices to use a new Pricelist (named Promotional Pricelist). In order to do so, you can select the property_product_pricelist and assign it the value of Promotional Pricelist. Then save it. After that, all your new invoices and sales orders will use the Promotional Pricelist.

You can also create new properties you can use in your custome modules. Just enter a name for it, select the Company which will be using it, the fields the parameter will be applied to, and then which value (and value type) can be assigned to it.

Hope this information helps,

Connecting fingerprint readers to OpenERP

January 6, 2011 4 comments

A client asked us to connect its fingerprint reader to the Human Resources module in OpenERP. In order to do that we just released the Beta version of a new module, which is named “clock_reader”. This module is able to connect to the fingerprint reader F5, which is distributed by Digito-Uno in Buenos Aires. We understand, but we have not tested it yet, it should be able to connect to the fingerprint reader G3T, commercialized by Asensio Sistemas in Rosario, Argentina.

The module is able to read more than one fingerprint reader, which can be connected from different sites in the company; or might have many readers in only one input, in case you need to have a backup device or need more throughput.

Well, below are some module screenshots (in spanish):

The module creates a new “clock” object that is listed under the option “Clock” in the Human Resources > Configuration menu.

This object has the following attributes; the clock name, the URI to the reader (more on that later), the model or driver, a location and whether it is active or not.

In order to read the fingerprint reader, you need to select the menu item “Clock read” in the Human Resources > Attendances menu item.

After selecting that option, you will see a confirmation dialog asking you to confirm the action. Once the fingerprint reader is read, you will see a window with the attendances captured and the errors that might have ocurred in the process.

This module was designed to handle more than one fingerprint reader model, but so far we were able to test it only with the F5 model. If you would like to add a new model, you have to develop a new python class with the following interface:

  • def __init__(self, uri): Allows you to construct the clock class. The URI is an string that describes the port the application is connecting to. For instance, as the reader allows you an UDP connection, the URI to use is udp://192.168.1.20:9999
  • def test(self): Checks the reader connection. Returns false if it is not possible. In the clock class we created for the F5 reader it is convenient to set a timeout value, to avoid an infinite loop. We are using a 3 second timeout value.
  • def connect(self): Readies the class for the operations to follow. It can also return device information. For instance, the firmware version number.
  • def attendance(self): iterative function which in every iteration returns information on the reader ID, time and whether the record belongs to someone entering the premises or not. It might also return the input method, such as reader, card or keyboard.
  • def clean(self): erases the records stored in the reader.
  • def disconnect(self): disconnects from the reader.

With a class that complies with this interface, the capture system will capture every active reader and will add the attendances to the hr_attendance module in OpenERP.

Regarding the reverse-engineering needed to develop more fingerprint readers, you can look at two python programs at the test directory in the module.

The first file, research.py, has a function for reading the captures made with Wireshark or any other program that exports the captured information in XML PDML (readpackets). It also has functions for checking that the conection is not encrypted (plothistogram) and for searching patterns (dotplot and align).

The second file, server_f5.py, is a virtual clock that takes a capture (there are three examples in the data directory) and listens so the client you are programming can connect to and query it.

You can find the module at Launchpad:

https://code.launchpad.net/~cristian-rocha/openerp-argentina/trunk/

Pricelists in OpenERP

December 29, 2010 Leave a comment

A customer asked us on what he was able to do with pricelists on OpenERP, so we decided to take a look at that functionality. So this post will cover what I learnt today regarding pricelists in OpenERP.

As stated by the tipo you can find in the Pricelist list view; “A pricelist contains rules to be evaluated in order to compute the purchase or sale price for all the partners assigned to a pricelist. Pricelists have several versions (2010, 2011, Promotion of February 2010, etc.) and each version has several rules. Example: the customer price of this category of product will be based on the supplier price multiplied by 1.80”

You can find the pricelist functionality in the Sales > Configuration > Pricelists menu. Clicking on that item, will show you two options, Pricelists and Pricelist Versions.

OpenERP 6.0 Main Menu

OpenERP 6.0 Main Menu

Click on the New button and you will see the New Pricelist Form

In this form we will enter the pricelist name, whether it is a sales or purchase pricelist, whether it is active or not, and its currency. In this example I will enter “Brazilian pricelist”, select BRL as the currency, and click save. The idea is to have a pricelist that applies to the computer products sold on the brazilian currency, the Real.

After saving the pricelist, we are ready to enter the pricelist versions. The pricelist version determines which rules of the pricelist will be applied when a sale or purchase invoice is entered by the user. These rules can be simple or become quite complex. We will cover some of them.

First you need to enter the pricelist version name, along with its start date, end date and active status. After entering that information, you can enter the rules. These rules apply mostly to products and its categories.

We will enter a new rule to illustrate how this work. The idea is to change the sale price to 150% of the cost price for all items of the Computer Stuff category, in case the quantity sold is greater than 100 items.

We create the new rule by clicking on the New button in the Version form. A new form will popup where we will enter the rule name, we will select the product, product template and product category that applies to the rule, and we will enter the minimum quantity that applies to the rule and its computation rule. We save the form and that’s all we need to do. Doing so we can enter different rules that cover the complexities that might apply to pricelists.

You can also apply pricelists to partners. On the partner form, select the Sales & Purchases tab, and you will be able to select which pricelist applies to that customer/supplier.

Cheers!

Customizing printed invoices in OpenERP

December 27, 2010 3 comments

These are some things I learnt past week while I was configuring invoices.

Printed invoices in OpenERP are reports. So, if you modify the graphical design of your invoices, such as the logo, all the reports in the system will be modified with the changes you just made. Last week I had to configure the system to print invoices in preprinted forms. To do that I had to remove the header and footer from the invoices, but doing so I modified all the reports I had already created. Until I figured out what to do.

This is what I did. In the administration menu, I selected the Companies->Companies item.

 

In the companies list you have to select your company and click on the Header/Footer tab. In this tab you will be able to modify the XML code that is printed on every report. You can remove the header by deleting all the lines between the and tags.

<header>
</header>

This clears the header for the first page of the report. If you would like to remove the header for the inner pages in the report, you have to do the same in the XML code in the Internal Header/Footer tab. This will remove in all the reports the headers, but will not remove the logo. In order to do that, we have to select the General Information tab and remove the logo picture file over there.
After removing the redundant text, we have to generate the report for the invoice. The design of the report can be found in the file system of the server where OpenERP is installed. This design is a XML file named invoice.rml. You can find it in the addons directory. I found it with the following command in my server:
find . -iname invoice.rml
You can edit the file in order to change the layout and the elements printed in the invoice. I recommend making a backup copy before making any changes to the report file. The file format is RML, which stands for (I believe) Report Mark-up Language. It is a mark-up language developed by the development group at ReportLab. You can find more information on this format in this document.
This file has three important markers: template, stylesheet and story. We are interested on two of them, template and story. With the template marker we will describe the report layout, with the story marker we will describe the content itself.For instance, we can define sections for the current date, the customer and its address:
<pageTemplate id=”first”>
<frame id=”date” x1=”13.5cm” y1=”21.5cm” width=”5.5cm” height=”1.2cm”/>
<frame id=”partner” x1=”3.5cm” y1=”18.9cm” width=”15.5cm” height=”1.2cm”/>
<frame id=”address” x1=”4.0cm” y1=”18.0cm” width=”15.0cm” height=”1.2cm”/>
</pageTemplate>

 

In order to assign text to each section is:

<story>
<para style=’body’>31/12/2010</para>
<nextFrame/>
<condPageBreak height=”12″/><para style=’body’>Cristian Sebastian Rocha</para>
<nextFrame/>
<condPageBreak height=”12″/><para style=’body’>Av. Rivadavia 555</para>
</story>

We can retrieve the customer information from the system with the following python code:
<story>
<para style=’body’>[[ formatLang(o.date_invoice,date=True) ]]</para>
<nextFrame/>
<condPageBreak height=”12″/><para style=’body’>[[ o.partner_id.title or ” ]] [[ o.partner_id.name ]]</para>
<nextFrame/>
<condPageBreak height=”12″/><para style=’body’>[[ o.address_invoice_id.street ]]</para>
</story>
This is more than enough to print on pre-printed invoices forms.
Cheers!