Re: modify function set_sql

2011-05-20T19:40:43Z

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

//////////////////////////////////////////
My file:
+ inventory/inquiry/stock_movements_type.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 .
***********************************************************************/
$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 "\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 "\n";
}

///////////////////////////////////////////////
I hope to help
Regards, AuraE

Re: modify function set_sql

2011-05-16T08:58:52Z

Unfortunatelly the sql processing in paging class is not bulletproof, and fails in this case. You will have to experiment with the class code to find solution. If you will find something better, please send the code to be integrated in FA.
Janusz

modify function set_sql

2011-05-12T19:02:05Z

I need a query like this:

SELECT sm.type , sm.trans_no, sm.reference, sm.tran_date, sm.person_id,
(if (sm.qty <0 || sm.type = 16, (
select ls.location_name
   FROM 0_stock_moves sms, 0_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 = 16, (
select ls.location_name
from 0_stock_moves sms, 0_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 0_stock_moves sm, 0_locations l
WHERE sm.loc_code = l.loc_code AND sm.tran_date >= '2010-01-01'
AND sm.tran_date <= '2011-05-10' GROUP BY sm.trans_no
   ORDER BY sm.tran_date, sm.trans_id

but is divided incorrectly by function set_sql of db_pager.inc, as there are more than one GROUP.

Any suggestions?

is to create a report of all transactions affecting stock_moves by trans_no, and show locations affected (location from and location to)

Thanks and sorry for my English!!

FrontAccounting forum — modify function set_sql

Re: modify function set_sql

Re: modify function set_sql

modify function set_sql