login error with sql error at login

[tronx2100]

Bug

2023-06-18 21:14:46 ERR 86.56.49.43 DoliDBMysqli::query SQL Error query: SELECT product.ref, COUNT(product.ref) as nb, SUM(tl.total_ht) as total, AVG(tl.total_ht) as avg FROM llx_commande as c, llx_commandedet as tl, llx_product as product INNER JOIN llx_societe_commerciaux as sc ON c.fk_soc = sc.fk_soc AND sc.fk_user = 1 WHERE c.entity IN (1) AND c.rowid = tl.fk_commande AND tl.fk_product = product.rowid AND c.date_commande BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59' GROUP BY product.ref ORDER BY nb DESC 2023-06-18 21:14:46 ERR 86.56.49.43 DoliDBMysqli::query SQL Error message: DB_ERROR_NOSUCHFIELD Unknown column 'c.fk_soc' in 'on clause' 2023-06-18 21:14:46 ERR 86.56.49.43 Error url=/index.php?mainmenu=home&leftmenu=home, query_string=mainmenu=home&leftmenu=home, sql=SELECT product.ref, COUNT(product.ref) as nb, SUM(tl.total_ht) as total, AVG(tl.total_ht) as avg FROM llx_commande as c, llx_commandedet as tl, llx_product as product INNER JOIN llx_societe_commerciaux as sc ON c.fk_soc = sc.fk_soc AND sc.fk_user = 1 WHERE c.entity IN (1) AND c.rowid = tl.fk_commande AND tl.fk_product = product.rowid AND c.date_commande BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59' GROUP BY product.ref ORDER BY nb DESC, db_error=Unknown column 'c.fk_soc' in 'on clause'

in commandestats.class.php in function getAllByProduct

if (empty .... causes sql errors on login when user not hav $user->right>-societe->client->voir permission

change to

/ if (empty($user->rights->societe->client->voir) && !$user->socid) { $sql .= " INNER JOIN ".MAIN_DB_PREFIX."societe_commerciaux as sc ON c.fk_soc = sc.fk_soc AND sc.fk_user = ".((int) $user->id); } /

this comments the block and fix the sql error

Environment Version

17.0.1

Environment OS

linux debian

Environment Web server

nginx

Environment PHP

7.4

Environment Database

mariadb

Environment URL(s)

index.php

Expected and actual behavior

no sql error at login with all users

in commandestats.class.php in function getAllByProduct

Steps to reproduce the behavior

No response

Attached files

No response

[AlbanDurrheimer]

Hello,

I just encountered the same error. I'll test your fix, thank you.

[Albanvdc]

Same error but different config on connecting secretary account : Date: 20230703122450 Dolibarr: 17.0.2 - https://www.dolibarr.org/ Niveau de fonctionnalités: 0 PHP: 8.0.29 Server: Apache/2.4.52 (Ubuntu) OS: Linux dolibarr 5.15.0-76-generic #83-Ubuntu SMP Thu Jun 15 19:16:32 UTC 2023 x86_64 User Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:109.0) Gecko/20100101 Firefox/114.0

Url sollicitée: /index.php?mainmenu=home Referer: http://XXX.XXX.XXX.XXX Gestionnaire de menu: eldy_menu.php

Modules/Applications: user, bookmark, barcode, workflow, import, export, website, socialnetworks, notification, mailing, externalsite, geoipmaxmind, clicktodial, printing, receiptprinter, blockedlog, cron, fournisseur, supplier_proposal, holiday, reception, hrm, facture, tax, expensereport, salaries, societe, banque, loan, propal, paymentbybanktransfer, prelevement, commande, margin, expedition, product, comptabilite, service, contrat, stock, ficheinter, knowledgemanagement, productbatch, variants, bom, ticket, agenda, eventorganization, projet, emailcollector, categorie, resource, ecm, fckeditor Type gestionnaire de base de données: mysqli Requête dernier accès en base en erreur: SELECT product.ref, COUNT(product.ref) as nb, SUM(tl.total_ht) as total, AVG(tl.total_ht) as avg FROM llx_commande as c, llx_commandedet as tl, llx_product as product INNER JOIN llx_societe_commerciaux as sc ON c.fk_soc = sc.fk_soc AND sc.fk_user = 3 WHERE c.entity IN (1) AND c.rowid = tl.fk_commande AND tl.fk_product = product.rowid AND c.date_commande BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59' GROUP BY product.ref ORDER BY nb DESC Code retour dernier accès en base en erreur: DB_ERROR_NOSUCHFIELD Information sur le dernier accès en base en erreur: Unknown column 'c.fk_soc' in 'on clause'

[eldy]

You are probably using an external user, can you confirm ?

[tronx2100]

You are probably using an external user, can you confirm ?

in my case it is a internal user with limited rights without $user->rights->societe->client->voir

[Albanvdc]

no , classic user (secretary) . the user (secretary) is in the secretariat group, for the moment only the secretariat group has defined rights. I have not yet given specific rights to this user. But I confirm that it is an internal or classic user.

[AlbanDurrheimer]

You are probably using an external user, can you confirm ?

No, in my case, it's an internal user with full permissions and admin rights.

[github-actions[bot]]

This issue is stale because it has been open 1 year with no activity. If this is a bug, please comment to confirm it is still present on latest stable version. if this is a feature request, please comment to notify the request is still relevant and not yet covered by latest stable version. This issue may be closed automatically by stale bot in 10 days (you should still be able to re-open it if required).