Search options

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

Thanks Janusz

Then, I will seek how to solve it. There are companies that sell here without an invoice, only with sales note, simplified tax regime.

thanks again!

I also I have doubts about what it means in sys_types type_no

Can you guide Joe or Janusz?

Attempt to put data into a temporary table, waiting for permission to go to the table after stock_moves. This table must have its own autoincrement.

Thanks

to rcuadra:
ABC functions for the references are in:    (las funciones para el ABC con las referencias estan en: )
includes/db/references_db.inc

other files involved are:  (otros archivos involucrados son:)
includes/types.inc
includes/sysnames.inc
includes/systypes.inc

as I said above, not clear to me that means type_no and where you put that as a reference
(como dije arriba, no me queda claro que significa type_no y donde se pone ese valor como referencia)

sorry for my English

thank you very kind to answer, and found the response of get_price and add_to_order in sales/includes/ui/sales_order_ui.inc

I missed in the kit, but following the two functions I found

sorry, I did not complete my question

thank you very much