Radicore Forum: Bug Reports » ERROR CREATING WHERE CLAUSE WHEN USING SEARCH

Home » RADICORE development » Bug Reports » ERROR CREATING WHERE CLAUSE WHEN USING SEARCH (ERROR CREATING WHERE CLAUSE WHEN USING SEARCH)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

[Message index]

		ERROR CREATING WHERE CLAUSE WHEN USING SEARCH By: edortizq on Tue, 16 October 2018 02:30
		Re: ERROR CREATING WHERE CLAUSE WHEN USING SEARCH By: AJM on Tue, 16 October 2018 05:19
		Re: ERROR CREATING WHERE CLAUSE WHEN USING SEARCH By: edortizq on Tue, 16 October 2018 09:22

Previous Topic:	Regex and Sql Queries containing +
Next Topic:	LIST3 - no paging area for "middle" as of 2.10

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

] [

]

Current Time: Sat Nov 30 05:00:11 EST 2024

Total time taken to generate the page: 0.01423 seconds