| ERROR CREATING WHERE CLAUSE WHEN USING SEARCH [message #7230] |
Tue, 16 October 2018 02:30  |
edortizq
Messages: 82
Registered: August 2008
Location: Ecuador
|
Member |
|
|
I have the tables in the attached file tables.sql
And this code in detalleventa.class.inc:
function _cm_changeConfig ($where, $fieldarray) {
//$this->fieldspec['idtarifa']['noedit']='y';
$this->fieldspec['valortickets']['noedit']='y';
if ($GLOBALS['mode'] == 'search') {
unset($this->fieldspec['fechaventa']);
unset($this->fieldspec['nombrerazonsocial']['noedit']);
unset($this->fieldspec['idtarifa']['noedit']);
unset($this->fieldspec['usuarioturnocaja']['noedit']);
unset($this->fieldspec['cantidadtickets']['noedit']);
$this->fieldspec['date_from'] = array('type' => 'date',
'size' => 12);
$this->fieldspec['date_to'] = array('type' => 'date',
'size' => 12);
}
if ($GLOBALS['mode'] == 'list' || $GLOBALS['mode'] == 'read') {
$this->fieldspec['numfactura'] = array ('type'=> 'string',
'size'=> 18,
'nondb'=>'y');
}
if ($GLOBALS['mode'] == 'list' || $GLOBALS['mode'] == 'read' || $GLOBALS['mode'] == 'search' ) {
$this->fieldspec['venta.idturnocaja'] = array('type' => 'integer',
'size' => 11);
$this->fieldspec['usuarioturnocaja'] = array('type' => 'string',
'size' => 16);
$this->fieldspec['nombrerazonsocial'] = array('type' => 'string',
'size' => 64);
}
if ($GLOBALS['task_id'] == 'buzz_detalleventa(add5)'){
// insert some javascript to submit form when a different value is selected in this dropdown list
$this->fieldspec['idtarifa']['javascript'] = array('onchange' => 'this.form.submit();');
$this->fieldspec['cantidadtickets']['javascript'] = array('onchange' => 'this.form.submit();');
//$GLOBALS['act_buttons']['submitBtn'] = "GRABAR";
unset ($GLOBALS['act_buttons']['submitBtn']);
unset ($GLOBALS['act_buttons']['submitstay']);
$GLOBALS['act_buttons']['submitnext'] = "GRABAR-IMPRIMIR";
} // if
return $fieldarray;
}
function _cm_pre_getData ($where, $where_array, $parent_data=null) {
$previous_tast = getPreviousTask();
if ($GLOBALS['task_id'] == 'buzz_detalleventa(output4)' && $previous_tast == 'buzz_venta(list2)caja'){
$where = $_SESSION['whereCSV'];
}
$newWhere = NULL;
if (!empty($this->sql_search)) {
// convert from string to an associative array
$fieldarray = where2array($this->sql_search, false, false);
// deal with values which have ranges
if (!empty($fieldarray['date_from']) OR !empty($fieldarray['date_to'])) {
$fieldarray['fechaventa'] = rangeFromTo($fieldarray['date_from'], $fieldarray['date_to'], true);
unset($fieldarray['date_from']);
unset($fieldarray['date_to']);
} // if
$newWhere = array2where($fieldarray);
} // if
if ($GLOBALS['task_id'] == 'buzz_detalleventa(list1)'){
// insert some javascript to submit form when a different value is selected in this dropdown list
$this->sql_orderby = "fechaventa";
$this->sql_orderby_seq = "desc";
} // if
if ($GLOBALS['mode'] == 'list' || $GLOBALS['mode'] == 'read' || $GLOBALS['mode'] == 'search' || $GLOBALS['mode'] == 'csv') {
$this->sql_select = " iddetalleventa,venta.fechaventa,cliente.nombrerazonsocial,if (venta.tipocomprobante=1,'FACTURA','PASE A BORDO') as tipo,";
$this->sql_select .= 'formapago.descripcionformapago,venta.establecimiento,venta. puntoemision,venta.secuenciasri,';
$this->sql_select .= 'idtarifa,cantidadtickets,valortickets,venta.idturnocaja,tur nocaja.usuarioturnocaja';
$this->sql_from = 'detalleventa LEFT JOIN venta ON (detalleventa.idventa = venta.idventa)';
$this->sql_from .= ' LEFT JOIN cliente ON (venta.idcliente = cliente.idcliente)';
$this->sql_from .= ' LEFT JOIN formapago ON (venta.idformapago = formapago.idformapago)';
$this->sql_from .= ' LEFT JOIN turnocaja ON (venta.idturnocaja = turnocaja.idturnocaja)';
if (isset ($newWhere)) {
$this->sql_where = $newWhere;
$where = $newWhere;
}
}
return $where;
}
When I try a search over usuarioturnocaja field the framework creates the following mistaken sql statement:
SELECT SQL_CALC_FOUND_ROWS iddetalleventa, venta.fechaventa, cliente.nombrerazonsocial,
if(venta.tipocomprobante=1,'FACTURA','PASE A BORDO') AS tipo, formapago.descripcionformapago,
venta.establecimiento, venta.puntoemision, venta.secuenciasri, idtarifa, cantidadtickets, valortickets,
venta.idturnocaja, turnocaja.usuarioturnocaja
FROM detalleventa
LEFT JOIN venta ON (detalleventa.idventa = venta.idventa)
LEFT JOIN cliente ON (venta.idcliente = cliente.idcliente)
LEFT JOIN formapago ON (venta.idformapago = formapago.idformapago)
LEFT JOIN turnocaja ON (venta.idturnocaja = turnocaja.idturnocaja)
WHERE detalleventa.idtarifa= 2 AND detalleventa.usuarioturnocaja LIKE 'CAJA01%'
ORDER BY venta.fechaventa desc
LIMIT 10 OFFSET 0
Note that in the where clause the field usuarioturnocaja is qualified with the detalleventa table when it should be qualified with turnocaja table.
-
Attachment: tables.sql
(Size: 6.02KB, Downloaded 495 times)
[Updated on: Tue, 16 October 2018 02:32] Report message to a moderator |
|
|
|
| Re: ERROR CREATING WHERE CLAUSE WHEN USING SEARCH [message #7231 is a reply to message #7230] |
Tue, 16 October 2018 05:19  |
AJM
Messages: 2370
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
Within the condition "if ($GLOBALS['mode'] == 'list' || $GLOBALS['mode'] == 'read' || $GLOBALS['mode'] == 'search' ) {" you are manually inserting the column 'usuarioturnocaja' into the field specifications for the 'detalleventa' table, and the framework qualifies the column with that table name because it thinks that the column exists within that table. Try adding the property 'nondb' = 'y' and the framework will ignore that entry and not qualify the column name with the wrong table name.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
[Updated on: Wed, 17 October 2018 04:31] Report message to a moderator |
|
|
|
|
|
Radicore Forum
Search
Help
Members
Register
Login
Home
