yes multi-queries do NOT work in $db->getrows()/query()
To make it work lot of changes will have to be done starting in atk/db/atkMysqliDb.inc
But this is complicated and NOT DONE.
Code:
atk/db/atkMysqliDb.inc
:
:
protected function _query($query, $isSystemQuery)
{
if (atkconfig("debug") >= 0)
{
atkimport("atk.utils.atkdebugger");
atkDebugger::addQuery($query, $isSystemQuery);
}
//---- mysqli_multi_query for semi colon delimited but fetching rows logic differs - also it is known security injunction threat when used as generic query executon
//return @mysqli_multi_query($this->m_link_id, $query); //The @ symbol is the error control operator (AKA the "silence" or "shut-up" operator).
//end Thu.26.Oct.2017
return @mysqli_query($this->m_link_id, $query); //The @ symbol is the error control operator (AKA the "silence" or "shut-up" operator).
}
instead the multi-qeury was re-written as single query .... to pass @ params to the View
#2 seprate dummy fns() defined to create @params in the view as below
DELIMITER $$
DROP FUNCTION IF EXISTS f_store_param_to_views$$
-- Sun.22.Oct.2017 used in inv_itemstock_for_store_item
-- usage :
-- SELECT @pStore:=_latin1'T-Store' COLLATE latin1_general_ci p
-- ,@pItem_id:=_latin1'54126' COLLATE latin1_general_ci p2;
-- select * FROM inv_itemstock_for_store_item v where 1=1;
CREATE DEFINER=`root`@`localhost` FUNCTION f_store_param_to_views() RETURNS VARCHAR(20) CHARSET latin1
NO SQL
DETERMINISTIC
RETURN @pStore$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS f_item_id_param_to_views$$
-- Sun.22.Oct.2017 used in inv_itemstock_for_store_item
-- usage :
-- SELECT @pStore:=_latin1'T-Store' COLLATE latin1_general_ci p
-- ,@pItem_id:=_latin1'54126' COLLATE latin1_general_ci p2;
-- select * FROM inv_itemstock_for_store_item v where 1=1;
CREATE DEFINER=`root`@`localhost` FUNCTION f_item_id_param_to_views() RETURNS VARCHAR(20) CHARSET latin1
NO SQL
DETERMINISTIC
RETURN @pItem_id$$
DELIMITER ;
DELIMITER $$
DROP VIEW IF EXISTS `inv_itemstock_for_store_item`$$
CREATE ALGORITHM=UNDEFINED DEFINER=root@`localhost`
SQL SECURITY DEFINER VIEW `inv_itemstock_for_store_item` AS
-- Sun.22.Oct.2017 needs @pItem_id and @pStore
-- usage :
-- SELECT @pStore:=_latin1'T-Store' COLLATE latin1_general_ci p
-- ,@pItem_id:=_latin1'54126' COLLATE latin1_general_ci p2;
-- select *, balance_unchk+balance+balance_rej+balance_cut_pc+balance_second AS tot_sih FROM inv_itemstock_for_store_item v where 1=1;
SELECT
`i`.`sto_locn` AS `sto_locn`,
`i`.`store` AS `store`,
`i`.`item_id` AS `item_id`,
SUM(IF((`i`.`doctype` = _latin1'Grn'),`i`.`recd_qty`,IF((`i`.`doctype` = _latin1'Cr'),(-(1) * `i`.`chk_qty`),0))) AS `balance_unchk`,
SUM((IF((`i`.`doctype` IN (_latin1'Cr',_latin1'Jr',_latin1'Sa',_latin1'Si')),1,IF((`i`.`doctype` IN (_latin1'Iss',_latin1'St',_latin1'Mrn')),-(1),0)) * `i`.`accpt_qty`)) AS `balance`,
SUM((IF((`i`.`doctype` IN (_latin1'Cr',_latin1'Jr',_latin1'Sa',_latin1'Si')),1,IF((`i`.`doctype` IN (_latin1'Iss',_latin1'St',_latin1'Mrn')),-(1),0)) * `i`.`rej_qty`)) AS `balance_rej`,
SUM((IF((`i`.`doctype` IN (_latin1'Cr',_latin1'Jr',_latin1'Sa',_latin1'Si')),1,IF((`i`.`doctype` IN (_latin1'Iss',_latin1'St',_latin1'Mrn')),-(1),0)) * `i`.`cut_pc_qty`)) AS `balance_cut_pc`,
SUM((IF((`i`.`doctype` IN (_latin1'Cr',_latin1'Jr',_latin1'Sa',_latin1'Si')),1,IF((`i`.`doctype` IN (_latin1'Iss',_latin1'St',_latin1'Mrn')),-(1),0)) * `i`.`second_qty`)) AS `balance_second`
FROM `gen_inventory` `i`
WHERE `i`.`item_id` > 0
and i.store = f_store_param_to_views() -- <==== dummy fns() to define @Values
and i.item_id = f_item_id_param_to_views() -- <==== dummy fns() to define @Values
GROUP BY `i`.`sto_locn`,`i`.`store`,`i`.`item_id`
$$
DELIMITER ;
-- following works - multiQuery - it does NOT work in atk $db->getrows()/Query() etc
SELECT @pStore:=_latin1'T-Store' COLLATE latin1_general_ci p
,@pItem_id:=_latin1'54126' COLLATE latin1_general_ci p2;
select *, balance_unchk+balance+balance_rej+balance_cut_pc+balance_second AS tot_sih FROM inv_itemstock_for_store_item v where 1=1;
-- following does NOT work because after FROM table-v is used before and on-the-fly param is 2nd table the @values used in Fst table-v
select * FROM inv_itemstock_for_store_item v
,(SELECT @pStore:=_latin1'T-Store' COLLATE latin1_general_ci p
,@pItem_id:=_latin1'54126' COLLATE latin1_general_ci p2
) param
where 1=1
-- following works
select * FROM
(SELECT @pStore:=_latin1'T-Store' COLLATE latin1_general_ci p
,@pItem_id:=_latin1'54126' COLLATE latin1_general_ci p2
) param
,inv_itemstock_for_store_item v
where 1=1
in the atk php inc it is used as this
Code:
:
$item_id = $this->getAttribute('item_id')->value2db($record);
$store = $user[store];
// Note : @p shud be fst table and view using these @p shud be 2nd table
$qry = "
select v.*
,balance_unchk+balance+balance_rej+balance_cut_pc+balance_second AS tot_sih
FROM
(SELECT @pStore:=_latin1'$store' COLLATE latin1_general_ci p
,@pItem_id:=_latin1'$item_id' COLLATE latin1_general_ci p2
) param
,inv_itemstock_for_store_item v
where 1=1
";
$qty=$db->getrows($qry);
:
