Technical Notes On Unit Conversion

This document assumes you have read HowToUseMeasures.

Catalog

Querying a stock in any unit requires a table in the catalog in order to perform unit conversions.

The quantities in the stock table are always expressed in the management unit of the resource. SQL calculations involving the measure table must be performed in the base unit.

`measure` table

A measure table with 5 columns records all measurements for all variations of all resources. Schema:

CREATE TABLE measure (
  uid BIGINT UNSIGNED NOT NULL,
  resource_uid BIGINT UNSIGNED NOT NULL,
  variation VARCHAR(255),
  metric_type_uid BIGINT UNSIGNED NOT NULL,
  quantity REAL NOT NULL,
  PRIMARY KEY (uid, variation),
  KEY (metric_type_uid)
);

When joined with the stock table, the variation column is compared with stock.variation_text and they match if the former is a subset of the latter: both columns are strings representing sets of variation categories. For the stock table, it's a list of category paths separated by '\n'. variation is a regular expression.
The condition in SQL requests is: CONCAT(stock.variation_text,'\n') REGEXP measure.variation

The value of the 5th column (quantity) must be expressed in the base unit.

See also:

• portal_catalog/erp5_mysql_innodb/z_create_measure

• portal_catalog/erp5_mysql_innodb/z_drop_measure

Constraints

For each resource, the measure table must also contain a row associated to the management unit. In order the user doesn't need to create a measure for the management unit, a additional row is automatically inserted in the table if needed.

Because both measures and resources objects can fill measure and the way a resource behaves depends on the measures it contains, cataloging is performed in a special way.

Moreover, the number of rows for one measure and the contents of the variation column depend on how the resource and the measure are variated.

Cataloging

Indexing or unindexing a measure doesn't modify measure. It only triggers the reindexing of the related resource. All work is done when indexing or unindexing a resource.

z_catalog_measure_list has a getMeasureRowList parameter. getMeasureRowList is a method of Resource and it returns the list of all rows to insert into measure for the resource. getMeasureRowList calls in turn Measure.asCatalogRowList on every measure object: asCatalogRowList returns the list of all rows to insert into measure for the measure. getMeasureRowList may also return an additional row for the management unit.

The variation regex is computed in Measure.asCatalogRowList.

See also:

• portal_catalog/erp5_mysql_innodb/z_catalog_measure_list

• portal_catalog/erp5_mysql_innodb/z0_uncatalog_measure

• ERP5/Document/Resource.py

• ERP5/Document/Measure.py

Querying stock

portal_simulation has a method to get a converted inventory list: getConvertedInventoryList.

Resource_zGetInventoryList also contains specific code for unit conversion. It can return an additional column containing converted quantities:

  SUM(<dtml-var stock_table_id>.quantity * measure.quantity)
  / <dtml-sqlvar quantity_unit type=float> AS converted_quantity

See also:

• portal_catalog/erp5_mysql_innodb/z_related_metric_type

• portal_skins/erp5_core/Resource_zGetInventoryList

• ERP5/Tool/SimulationTool.py

Converting an amount of resource

Resource.convertQuantity is implemented to allow unit conversion, for example in order lines. It is used by Amount.getConvertedQuantity.

See also:

• ERP5/Document/Amount.py

• ERP5/Document/Resource.py

• ERP5/Document/Measure.py