MySQL Statements

Using my Dolibarr
, , ,
1

Does anybody have any sql statements they have used for their business in Dolibarr?

I will share mine also for the greater good of the Dolibarr community.

If you have any to share, you are welcome to add a comment to this post using the general format below:

<title of report or what the mysql statement will produce when run>
<state dolibarr version>
<display mysql statement>

My statements are all read-only, so no data will be affected.

1 Like
2

Products Without Categories
Dolibarr v14.0.2

SELECT DISTINCT
    u.rowid AS "Category ID",
    u.label AS "Category Label",
    u.description AS "Category Description",
    p.ref AS "Product Code",
    p.label AS "Product Name",
    p.rowid AS "Product ID"
FROM
    llx_categorie AS u
JOIN llx_categorie_product AS cp
ON
    cp.fk_categorie = u.rowid
RIGHT JOIN llx_product AS p
ON
    p.rowid = cp.fk_product
WHERE
    u.label IS NULL AND `fk_product_type` = 0
ORDER BY
    p.ref
1 Like
3

Products | Incorrect Dimensions
Dolibarr v14.0.2

SELECT
    `ref` AS "Product Code",
    `label` AS "Product Name",
    `LENGTH` AS "Length",
    IF(
        `length_units` = -2,
        "CM",
        "Convert VALUE and UNIT to CM"
    ) AS "Length Unit",
    `width` AS "Width",
    IF(
        `width_units` = -2,
        "CM",
        "Convert VALUE and UNIT to CM"
    ) AS "Width Unit",
    `height` AS "Height",
    IF(
        `height_units` = -2,
        "CM",
        "Convert VALUE and UNIT to CM"
    ) AS "Height Unit",
    `weight` AS "Weight",
    IF(
        `weight_units` = 0,
        "KG",
        "Convert VALUE and UNIT to KG"
    ) AS "Weight Unit"
FROM
    `llx_product`
WHERE
    `fk_product_type` = 0 AND(
        `length_units` != -2 OR `width_units` != -2 OR `height_units` != -2 OR `weight_units` != 0 OR `length` != NULL
    ) AND(
        `width` != NULL OR `height` != NULL OR `length` != 0 OR `width` != 0 OR `height` != 0
    )
ORDER BY
    `ref`
4

Product Manufacturing Countries
This table shows the total number of products associated to each country.
Dolibarr v14.0.2

SELECT 
    llx_product.fk_country AS 'Database Country Code',
    llx_c_country.label AS 'Country',
    COUNT(*) AS 'N° of Products'
FROM
    llx_product
        INNER JOIN
    llx_c_country ON llx_product.fk_country = llx_c_country.rowid
GROUP BY fk_country
ORDER BY COUNT(*) DESC;