Topic: Retrieving data from 3 query(s) for a new report

Great work!!!

Is there a way to use the following query(s) to generate a new report by combining them as one result set?

a)get_sql_for_supplier_inquiry()
b)get_sql_for_customer_inquiry() and
c)get_work_order_issues()?

i desire only the references in these query to be outputted in either pdf or xcel. with the print header customer, supplier and manufacturing order by date.

Any clue will be appreciated.

Re: Retrieving data from 3 query(s) for a new report

Hi, I figured why this post hasnt received any response so i digged and came up with the following. it just didnt print anything, i cant fugure where my mistakes were even after setting my debug to 2.

<?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_SALESANALYTIC';


$path_to_root="..";

include_once($path_to_root . "/includes/session.inc");
include_once($path_to_root . "/includes/date_functions.inc");
include_once($path_to_root . "/sales/includes/db/cust_trans_db.inc");
include_once($path_to_root . "/inventory/includes/inventory_db.inc");
include_once($path_to_root . "/manufacturing/includes/db/work_order_issues_db.inc");
include_once($path_to_root . "/purchasing/includes/db/supp_trans_db.inc");
include_once($path_to_root . "/includes/ui.inc");
// ----------------------------------------------------------------
// $ Revision:    2.0 $
// Creator:    tee
// date_:    2013-01-09
// Title:    Inventory DailyRefs Report
// ----------------------------------------------------------------
//----------------------------------------------------------------------------------------------------

print_dailyref();

//----------------------------------------------------------------------------------------------------

function print_dailyref()
{
    global $path_to_root,$systypes_array;
   
    //include_once($path_to_root . "/reporting/includes/pdf_report.inc");
   
    $from = $_POST['PARAM_0'];
    $to = $_POST['PARAM_1'];
    $comments = $_POST['PARAM_2'];
    $destination = $_POST['PARAM_3'];
    if ($destination)
        include_once($path_to_root . "/reporting/includes/excel_report.inc");
    else
        include_once($path_to_root . "/reporting/includes/pdf_report.inc");
   
    $from = date2sql($from);
    $to = date2sql($to);
   
       
    $cols = array(0,  175,  350,  515);

    $headers = array(_('Type'), _('Date'), _('Reference #'));
   
    $aligns = array('left',    'left', 'right');

    $params = array('comments' => $comments);
       
    for ($i = $from; $i <= $to; $i++)
    {
       
        $rep = new FrontReport(_('Inventory DailyRef Report'), "InventoryDailyRefReport", user_pagesize());
       
        $rep->Font();
        $rep->Info($params, $cols, $headers, $aligns);
       
        $rep->NewPage();
       
        if (!exists_customer_trans($i))
                continue;
       
        $res = get_sql_for_customer_inquiry($i);
   
        while ($myrow = db_query($res))
        {
            if(
            $rep->NewLine();
            $rep->TextCol(0, 1,$systypes_array[$myrow['type']]);
            $rep->TextCol(1, 2,$myrow['date']);
            $rep->TextCol(2, 3,$myrow['reference']);
            $rep->NewLine();
        }
        if (!exists_supp_trans($i))
            continue;
        $res2 = get_sql_for_supplier_inquiry($i);
       
        while($myrow2 = db_fetch($res2))
        {
            $rep->NewLine();
            $rep->TextCol(0, 1,$systypes_array[$myrow2['type']]);
            $rep->TextCol(1, 2,$myrow2['date']);
            $rep->TextCol(2, 3,$myrow2['reference']);
            $rep->NewLine();
        }
       
       
        if (!exists_work_order_issue($issue_no,$i)
            continue;
           
        $res3 = get_work_order_issue($issue_no,$i);
       
        while($myrow3 = db_fetch($res3))
        {
            $rep->NewLine();
            $rep->TextCol(0, 1,$myrow2$systypes_array[$myrow3['type']]);
            $rep->TextCol(1, 2,$myrow2['date']);
            $rep->TextCol(2, 3,$myrow2['reference']);
            $rep->NewLine();
        }
       
    }
   
    $rep->Line($rep->row  - 4);
    $rep->Line();
    $rep->End();
}

?>

Re: Retrieving data from 3 query(s) for a new report

Hi All,

this is the only error i got

Parse error: syntax error, unexpected T_VARIABLE in C:\xampp\htdocs\fa\reporting\rep320.php on line 105_

which was $res3= get_work_order_issue($issue_no,$i);

what am i missing? Any help will be appreciated

Re: Retrieving data from 3 query(s) for a new report

i have fixed it. cheers.

this is a flexible architecture.....