Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » RADICORE development » Bug Reports » ERROR CREATING WHERE CLAUSE WHEN USING SEARCH (ERROR CREATING WHERE CLAUSE WHEN USING SEARCH)
ERROR CREATING WHERE CLAUSE WHEN USING SEARCH [message #7230] Tue, 16 October 2018 02:30 Go to next message
edortizq is currently offline  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 259 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 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2297
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.

[Updated on: Wed, 17 October 2018 04:31]

Report message to a moderator

Re: ERROR CREATING WHERE CLAUSE WHEN USING SEARCH [message #7232 is a reply to message #7231] Tue, 16 October 2018 09:22 Go to previous message
edortizq is currently offline  edortizq
Messages: 82
Registered: August 2008
Location: Ecuador
Member

Thanks!it's working now!
Previous Topic: Regex and Sql Queries containing +
Next Topic: LIST3 - no paging area for "middle" as of 2.10
Goto Forum:
  


Current Time: Tue May 17 09:21:09 EDT 2022

Total time taken to generate the page: 0.01987 seconds