hello Janusz!! This gave solution to my problem, single step as a parameter, the query for the count to include/db_pager.inc
include/db_pager.inc line 59
var $sql_count; // sql count for complex sql query. Add AuraE
include/db_pager.inc line 305
function _init()
{
global $go_debug;
if ($this->ready == false ) {
if ($this->sql_count == null) //add AuraE
$sql = $this->_sql_gen(true);
else
$sql = $this->sql_count;
$result = db_query($sql, 'Error reading record set');
include/db_pager.inc line 454
function &new_db_pager($name, $sql, $coldef, $table = null, $key = null, $page_len = 0, $sql_count= null) //add AuraE
{
if (isset($_SESSION[$name]) &&
($_SERVER['REQUEST_METHOD'] == 'GET' || $_SESSION[$name]->sql != $sql)) {
unset($_SESSION[$name]); // kill pager if sql has changed
}
if (!isset($_SESSION[$name])) {
$_SESSION[$name] = new db_pager($sql, $name, $table, $page_len);
$_SESSION[$name]->main_tbl = $table;
$_SESSION[$name]->key = $key;
$_SESSION[$name]->set_sql($sql);
$_SESSION[$name]->set_columns($coldef);
$_SESSION[$name]->sql_count = $sql_count; //add AuraE
}
return $_SESSION[$name];
}
//////////////////////
My functions are follows:
+ inventory/includes/db/movement_type_db.inc line 87
function get_stock_movements_type($type, $StockLocation, $BeforeDate, $AfterDate)
{
$before_date = date2sql($BeforeDate);
$after_date = date2sql($AfterDate);
$sql = "SELECT sm.type , sm.trans_no, sm.reference, sm.tran_date, sm.person_id,
(if (sm.qty <0 || sm.type = ".ST_LOCTRANSFER.", (
SELECT ls.location_name
FROM ".TB_PREF."stock_moves sms, ".TB_PREF."locations ls
WHERE sms.loc_code = ls.loc_code
AND sms.trans_no = sm.trans_no
AND sms.tran_date = sm.tran_date
AND sms.qty <0
GROUP BY sms.trans_no), null)
) as loc_from,
(if (sm.qty >0 || sm.type = ".ST_LOCTRANSFER.", (
SELECT ls.location_name
FROM ".TB_PREF."stock_moves sms, ".TB_PREF."locations ls
WHERE sms.loc_code = ls.loc_code
AND sms.trans_no = sm.trans_no
AND sms.tran_date = sm.tran_date
AND sms.qty >0
GROUP BY sms.trans_no), null)
) as loc_to
FROM ".TB_PREF."stock_moves sm
WHERE ";
if (isset($_POST['trans_no']) && $_POST['trans_no'] != "")
{
// search orders with number like
$number_like = "%".$_POST['trans_no'];
$sql .= " sm.trans_no LIKE ".db_escape($number_like);
} else {
$sql .= " sm.tran_date >= '". $after_date . "'
AND sm.tran_date <= '" . $before_date . "'";
}
if ($StockLocation != ALL_TEXT)
$sql .= " AND sm.loc_code=".db_escape($StockLocation);
if ($type != ALL_TEXT)
$sql .= " AND sm.type = ".db_escape($type);
$sql.= " GROUP BY sm.trans_no
ORDER BY sm.tran_date, sm.trans_id";
return $sql;
}
function get_stock_movements_type_count($type, $StockLocation, $BeforeDate, $AfterDate)
{
$before_date = date2sql($BeforeDate);
$after_date = date2sql($AfterDate);
$sql = "SELECT COUNT(DISTINCT sm.trans_no)
FROM ".TB_PREF."stock_moves sm
WHERE ";
if (isset($_POST['trans_no']) && $_POST['trans_no'] != "")
{
// search orders with number like
$number_like = "%".$_POST['trans_no'];
$sql .= " sm.trans_no LIKE ".db_escape($number_like);
} else {
$sql .= " sm.tran_date >= '". $after_date . "'
AND sm.tran_date <= '" . $before_date . "'";
}
if ($StockLocation != ALL_TEXT)
$sql .= " AND sm.loc_code=".db_escape($StockLocation);
if ($type != ALL_TEXT)
$sql .= " AND sm.type = ".db_escape($type);
return $sql;
}
//////////////////////////////////////////
My file:
+ inventory/inquiry/stock_movements_type.php
<?php
/**********************************************************************
Copyright (C) FrontAccounting, LLC.
Released under the terms of the GNU General Public License, GPL,
as published by the Free Software Foundation, either version 3
of the License, or (at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
***********************************************************************/
$page_security = 'SA_ITEMSTRANSVIEW';
$path_to_root="../..";
include($path_to_root . "/includes/db_pager.inc");
include($path_to_root . "/includes/session.inc");
include($path_to_root . "/inventory/includes/stock_transfers_ui.inc");
include_once($path_to_root . "/reporting/includes/reporting.inc");
$js = "";
if ($use_popup_windows)
$js .= get_js_open_window(900, 500);
if ($use_date_picker)
$js .= get_js_date_picker();
page(_($help_context = "Inventory Movement Type"), false, false, "", $js);
if (isset($_GET['trans_no']))
{
$trans_no = $_GET['trans_no'];
}
//-----------------------------------------------------------------------------------
// Ajax updates
//
if (get_post('SearchStockMovesTrans'))
{
$Ajax->activate('trans_tbl');
}
if (get_post('_trans_no_changed')) // enable/disable selection controls
{
$disable = get_post('trans_no') !== '';
$Ajax->addDisable(true, 'OrdersAfterDate', $disable);
$Ajax->addDisable(true, 'OrdersToDate', $disable);
$Ajax->addDisable(true, 'StockLocation', $disable);
if ($disable)
$Ajax->addFocus(true, 'trans_no');
$Ajax->activate('trans_tbl');
}
//---------------------------------------------------------------------------------------------
start_form();
start_table(TABLESTYLE_NOBORDER);
start_row();
ref_cells(_("#:"), 'trans_no', '',null, '', true);
date_cells(_("from:"), 'OrdersAfterDate', '', null, -30);
date_cells(_("to:"), 'OrdersToDate');
locations_list_cells(_("into location:"), 'StockLocation', null, true);
end_row();
end_table();
start_table(TABLESTYLE_NOBORDER);
start_row();
if (!isset($_POST['filterType']))
$_POST['filterType'] = 0;
stock_moves_list_cells(null, 'filterType', $_POST['filterType'], true);
submit_cells('SearchStockMovesTrans', _("Search"),'',_('Select documents'), 'default');
end_row();
end_table(1);
//---------------------------------------------------------------------------------------------
function trans_view($trans)
{
return get_trans_view_str($trans["type"], $trans["trans_no"]);
}
function systype_name($dummy, $type)
{
global $systypes_array;
return $systypes_array[$type];
}
function person_id($row)
{
if (($row["type"] == ST_CUSTDELIVERY) || ($row["type"] == ST_CUSTCREDIT))
{
$cust_row = get_customer_details_from_trans($row["type"], $row["trans_no"]);
if (strlen($cust_row['name']) > 0)
$person = $cust_row['name'] . " (" . $cust_row['br_name'] . ")";
}
elseif ($row["type"] == ST_LOCTRANSFER || $row["type"] == ST_INVADJUST)
{
// get the adjustment type
$movement_type = get_movement_type($row["person_id"]);
$person = $movement_type["name"];
}
return $person;
}
//---------------------------------------------------------------------------------------------
$sql = get_stock_movements_type_first($_POST['filterType'], $_POST['StockLocation'], $_POST['OrdersToDate'],$_POST['OrdersAfterDate']);
$sql_count = get_stock_movements_type_first_count($_POST['filterType'], $_POST['StockLocation'], $_POST['OrdersToDate'],$_POST['OrdersAfterDate']);
$cols = array(
_("Type") => array('fun'=>'systype_name', 'ord'=>''),
_("#") => array('fun'=>'trans_view', 'ord'=>''),
_("Reference"),
_("Date"),
_("Detail")=> array('fun'=>'person_id', 'ord'=>''),
_("Location From"),
_("Location to")
);
//---------------------------------------------------------------------------------------------------
$table =& new_db_pager('trans_tbl', $sql, $cols, null, null, 0, $sql_count);
$table->width = "80%";
display_db_pager($table);
end_form();
end_page();
?>
//////////////////////////////
the function stock_moves_list_cells
+ includes/ui/ui_list.inc line 2456
function stock_moves_list_cells($label, $name, $selected=null)
{
global $all_items;
if ($label != null)
label_cell($label);
echo "<td>\n";
$allocs = array(
$all_items=>_("All Types"),
ST_CUSTCREDIT => _("Credit Notes"),
ST_CUSTDELIVERY => _("Delivery Notes"),
ST_LOCTRANSFER => _("Location Transfer"),
ST_INVADJUST => _("Inventory Adjustment")
);
echo array_selector($name, $selected, $allocs);
echo "</td>\n";
}
///////////////////////////////////////////////
I hope to help
Regards, AuraE
