• Example
• Problem 1 (solved)
• Problem 2 (solved)
• Problem 3: linear? (solved)
• Problem 4: packs (solved)
• Problem 5 (solved)
• Problem 6 (solved)
• Problem 7 (solved)
• Problem 8: variation (solved)
• Problem 9: price

• quantity_unit (base category)
  • example
• metric_type (base category)
  • example
  • remaining questions
• Products
  • example
• SQL
  • measure (measurement ?)
    • example of table
    • example of variation regex
  • pack
    • naming proposal
    • example

• SQL
  • A simple case without pack
  • With packs

The problems related to Unit Conversion¶

Example¶

Let's suppose we have the following products:

• 'Shortbreads (*8)':
• ref: 10008
• default unit: unit (1 box)
• contents: 8 biscuits
• net weight: 150g
• gross weight: 162g
• volume: ~100cm³

• 'Shortbreads (*12)':
• ref: 10012
• contents: 12 biscuits
• net weight: 225g
• ...

• 'SomePack':
• ref: 88888
• contents: 2 'Shortbreads (*8)', 1 'Shortbreads (*12)', 1 'SomeSoda'
• ...

Here are questions we want to answer:

• 1. How many 10008 do we have in stock? (in the default managment unit)
• 2. How many kg (gross weight) of 10008 do we have? Same question for net weight.
• How is the occupation of our stock?
• 3. How many 'Shortbreads (*8)' including packs?
• 4. How many shortbreads?
• 5. What is the (gross) weight of our stock of 10008, 10012 and 88888?

This example can be more complicated: What if there are several flavours of shortbreads and we want to answer the above questions, depending on whether we ignore or not flavours.

Problem 1 (solved)¶

'biscuits', 'net weight', 'gross weight', 'box', 'volume': what are they?

Problem 2 (solved)¶

The quantity unit on resources should allow multiple categories (as it is for apparel) so that a given product can be sold using its default unit or any other acceptable unit.

Problem 3: linear? (solved)¶

For a given product, there is always a constant ratio between 2 of its properties. And for any product containing shortbreads, the ratio between the number of biscuits and the net weight is also the same.

However, we lose linearity for certain properties if we consider, for example, a box of 12 and another of 8: for these 2 products, the ratio between net weight and gross weight varies.

Problem 4: packs (solved)¶

How to describe packs? Should we build a graph of products? Should we settle for computing sums of the subproducts properties manually?

Problem 5 (solved)¶

We want to compute totals in only one SQL request. What information do we need in the SQL database? When and where do we have to perform unit conversion?

Problem 6 (solved)¶

A 'Shortbread' resource may already exist. This is the case if we produce them. Why not seeing 'Shortbreads (*8)' as a pack of 8 'Shortbread'?

Problem 7 (solved)¶

Let's suppose we have a 'Shortbread' product. We could get the (net) weight of shortbreads in our stock of 'Shortbreads (*8)' and 'Shortbreads (*12)' by getting the number of 'Shortbread' first and then look up the properties of the 'Shortbread' to perform conversion.

However, question 5 prevents us to do that. Let's rephrase it in a more generic way: « How to count several unrelated resources in a given common property? » If there is no common resource, we are forced to keep in SQL all conversion factors of all products.

Problem 8: variation (solved)¶

The model must handle variants of products, even when they are optional.

Problem 9: price¶

Should be also handle price like other properties?

ex: What if we want to buy stuff for a certain amount of euros?

Draft¶

All unit conversions is done in Python:

• before our SQL request, so that all computation is done in the same unit, whatever the product.
• after our SQL request, to display the result in the unit requested by the user.

We don't do (and we shouldn't) unit conversion between 2 different physical quantities.

This model handles non-optional and optional variations.

quantity_unit (base category)¶

This base category has a clear usage: it is only a database of measurement units. It means that it is filled independantly from any product. In a first time, it would include units with a SI prefix.

Units used for a given physical quantity are grouped in the same subcategory.

=> quantity_unit//

Each unit has a quantity field containing the ratio in respect to a base unit (of the same physical quantity). The ratio is 1 for a base one.

example¶¶¶¶

• quantity_unit/unit/unit (quantity = 1)
• quantity_unit/unit/dozen (quantity = 12)
• quantity_unit/mass/pound (quantity = .45359237)
• quantity_unit/mass/g (quantity = .001)
• quantity_unit/mass/kg (quantity = 1)

metric_type (base category)¶

This new base category defines any « property » a product might have. It allows to quantify a product in different metrics, even if the same physical unit is used for several metrics.

The schema must be similar to quantity_unit so that it is possible to determine relevant units for each metric type.

=> metric_type//

example¶¶¶¶

• metric_type/mass/Net_weight
• metric_type/mass/Gross_weight
• metric_type/volume/Volume
• metric_type/unit/item/food/Shortbread

NOTE: There is probably no point in distinguishing 'Gross weight' from generic 'Mass' (or 'Weight').

remaining questions¶

metric_type/volume/Volume, metric_type/unit/Unit (and so on) could be simplified respectively to metric_type/volume, metric_type/unit, etc.

• Are node categories valid metric types?
• Are « first level » categories (ex: metric_type/volume) valid metric types?

Products¶

Each product has a list of measures, including a default one. A measure consists of a metric_type, a quantity_unit and a value (quantity).

example¶¶¶¶

'Shortbreads (*8)' (default unit in bold)

It seems the first line would be almost always there. Should it be implicit?

SQL¶¶

measure (measurement ?)¶

A new measure table with 4 columns allows computing totals, for one or several products, and for one property that is common to these products. Schema:

  create table measure (
        product_uid int,
        variation varchar(255),
        metric_type_uid int,
        primary key (product_uid, variation, metric_type_uid),
        value float
  );

The variation column is compared with the variation_text column of the stock table 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 still a list of category paths separated by '\n'. In the new table, the variation column is a regular expression. So the condition is: stock.variation_text REGEXP measure.variation

REGEXP are used instead of LIKE because of optional varations. Another solution is to change the format of stock.variation_text. Given a foo/bar option, the string foo/ would be added to variation_text if the option is missing, so that foo/_% matches the presence of a foo option, foo/ matches the absence and foo/% matches all cases.

The unit of the 4th column (value) must be a base one.

example¶¶¶¶ of table

example¶¶¶¶ of variation regex

Given a product with 3 variation axes, logo/erp5 being optional:

• ^colour/[^\n]+\n(logo/erp5\n)?size/small$ matches any 'size/small' variant
• ^colour/[^\n]+\nsize/[^\n]+$ matches variants without logo

pack¶

Another pack table allows conversion from a number of certain products to a number of other products: the table records the contents of each composed product. It allows computing totals for packs according to a property of one of its component(s).

  create table pack (
        product_uid int,
        product_variation varchar(255),
        component_uid int,
  component_variation varchar(255),
        primary key (product_uid, product_variation, component_uid, component_variation),
        value float
  );

The product_variation column is like sub_quant's variation column. The component_variation column is like stock's variation_text column.

naming proposal¶

• pack¶ | bundle | assortment
• composed_product

example¶¶¶¶

The following table contain a fourth row in case there exists a 'Shortbread' product.

Implementation¶

SQL¶¶

getInventoryList('Shortbreads (*8)'):

  SELECT
    ...

  FROM
    stock LEFT JOIN catalog AS section ON (section.uid = 
  stock.section_uid),
    catalog AS catalog, catalog as node, catalog as resource

  WHERE
    (stock.resource_uid = '1499') AND (catalog.uid = stock.uid)
    AND node.uid = stock.node_uid
    AND resource.uid = stock.resource_uid

  GROUP BY
      stock.node_uid, stock.uid, stock.resource_uid

A simple case without pack¶

If we want the net weight, the request becomes:

  SELECT
    SUM(stock.quantity * measure.value)
    ...

  FROM
    stock LEFT JOIN catalog AS section ON (section.uid = 
  stock.section_uid),
    catalog AS catalog, catalog as node, catalog as resource
    , measure

  WHERE
    stock.resource_uid IN (/* list of product uid we want to take into 
  account */)
    /* AND, optionally, a condition on stock.variation_text */
      AND catalog.uid = stock.uid
    AND node.uid = stock.node_uid
    AND resource.uid = stock.resource_uid

    AND resource.uid = measure.product_uid
    AND stock.variation_text REGEXP measure.variation
    AND measure.metric_type_uid IN (SELECT uid FROM catalog
      WHERE portal_type = 'Category' AND relative_url = 
   'metric_type/mass/Net_weight')

  GROUP BY
      stock.node_uid /*, stock.uid */ /*, stock.resource_uid */

With packs¶

If we have we have a 'Shortbread' product with an associated mass, we can get the mass of shortbreads in the stock of 'SomePack':

  SELECT
    SUM(stock.quantity * pack.value * measure.value)
    ...

  FROM
    stock LEFT JOIN catalog AS section ON (section.uid = 
  stock.section_uid),
    catalog AS catalog, catalog as node, catalog as resource
    , measure, pack

  WHERE
    stock.resource_uid IN (/* 'SomePack' uid */)
    /* AND, optionally, a condition on stock.variation_text */
      AND catalog.uid = stock.uid
    AND node.uid = stock.node_uid
    AND resource.uid = stock.resource_uid

    AND resource.uid = pack.product_uid
    AND stock.variation_text REGEXP pack.product_variation
    AND pack.component_uid = /* 'Shortbread' uid */
    /* AND, optionally, a condition on pack.component_variation */

    AND pack.component_uid = measure.product_uid
    AND pack.component_variation REGEXP measure.variation
    AND measure.metric_type_uid IN (SELECT uid FROM catalog
      WHERE portal_type = 'Category' AND relative_url = 
  'metric_type/mass/Weight')

  GROUP BY
      stock.node_uid /*, stock.uid */ /*, stock.resource_uid */

Related Articles¶