Topic: modify function set_sql

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!!

Re: modify function set_sql

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

Re: modify function set_sql

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