search

Dolibarr / dolibarr

Dolibarr ERP CRM is a modern software package to manage your company or foundation's activity (contacts, suppliers, invoices, orders, stocks, agenda, accounting, ...). it's an open source Web application (written in PHP) designed for businesses of any sizes, foundations and freelancers.
https://www.dolibarr.org
GNU General Public License v3.0
5.46k stars 2.79k forks source link

Bug: SQL error when filtering customer invoices #6234

Closed marcosgdf closed 7 years ago

marcosgdf commented 7 years ago

Bug

Dolibarr ha detectado un error técnico. Esta información puede ser útil para el diagnóstico: Fecha: 20170101195644 Dolibarr: 4.0.3 Nivel de funciones: 0 PHP: 5.6.29-0+deb8u1 Server: Apache OS: Linux vps52380 2.6.32-042stab120.11 #1 SMP Wed Nov 16 12:05:45 MSK 2016 x86_64 UserAgent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/55.0.2883.95 Safari/537.36

URL solicitada: /compta/facture/list.php Referer: /compta/facture/list.php?leftmenu=customers_bills Gestor de menú: eldy_menu.php

Tipo de gestor de base de datos: mysqli Último error en solicitud de acceso de base de datos: SELECT f.rowid as facid, f.facnumber, f.ref_client, f.type, f.note_private, f.note_public, f.increment, f.fk_mode_reglement, f.total as total_ht, f.tva as total_vat, f.total_ttc, f.datef as df, f.date_lim_reglement as datelimite, f.paye as paye, f.fk_statut, f.datec as date_creation, f.tms as date_update, s.rowid as socid, s.nom as name, s.town, s.zip, s.fk_pays, s.client, s.code_client, typent.code as typent_code, state.code_departement as state_code, state.nom as state_name, SUM(pf.amount) as dynamount_payed FROM llx_societe as s LEFT JOIN llx_c_country as country on (country.rowid = s.fk_pays) LEFT JOIN llx_c_typent as typent on (typent.id = s.fk_typent) LEFT JOIN llx_c_departements as state on (state.rowid = s.fk_departement), llx_facture as f LEFT JOIN llx_paiement_facture as pf ON pf.fk_facture = f.rowid WHERE f.fk_soc = s.rowid AND f.entity = 1 AND (f.total_vat = 0) AND f.datef BETWEEN '20160101000000' AND '20161231235959' GROUP BY f.rowid, f.facnumber, ref_client, f.type, f.note_private, f.note_public, f.increment, f.fk_mode_reglement, f.total, f.tva, f.total_ttc, f.datef, f.date_lim_reglement, f.paye, f.fk_statut, f.datec, f.tms, s.rowid, s.nom, s.town, s.zip, s.fk_pays, s.code_client, s.client, typent.code, state.code_departement, state.nom ORDER BY f.datef DESC, f.rowid DESC LIMIT 26 Código de retorno de error para la última petición de acceso a la base de datos: DB_ERROR_NOSUCHFIELD Información del último error de petición de acceso de base de datos: Unknown column 'f.total_vat' in 'where clause'

gnovaro commented 7 years ago

It is a badly formulated query you can only filter by the name of a column in a where and not with an alias.

Try change f.total_vat by f.tva in WHERE clause. You can only use column aliases in GROUP BY, ORDER BY, or HAVING clauses.