Bug 4817: g/l account inquiry can timeout due to slow sql. @Braath Waate.

[fa-stable.git] / reporting / rep202.php

diff --git a/reporting/rep202.php b/reporting/rep202.php
index 5b47bec79afe1f9a36e6addf78273a56dcd6f571..04d1afa1d5365b735759ebde3e44376685740745 100644 (file)
--- a/reporting/rep202.php
+++ b/reporting/rep202.php
@@ -1,15 +1,15 @@
 <?php
 /**********************************************************************
     Copyright (C) FrontAccounting, LLC.
 <?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 
+       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
        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.  
+    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

     See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
 ***********************************************************************/
     See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
 ***********************************************************************/

-$page_security = 2;
+$page_security = 'SA_SUPPLIERANALYTIC';

 // ----------------------------------------------------------------
 // $ Revision: 2.0 $
 // Creator:    Joe Hunt
 // ----------------------------------------------------------------
 // $ Revision: 2.0 $
 // Creator:    Joe Hunt


@@ -29,35 +29,38 @@ print_aged_supplier_analysis();
 
 //----------------------------------------------------------------------------------------------------
 
 
 //----------------------------------------------------------------------------------------------------
 

-function get_invoices($supplier_id, $to)
+function get_invoices($supplier_id, $to, $all=true)

 {
        $todate = date2sql($to);
        $PastDueDays1 = get_company_pref('past_due_days');
        $PastDueDays2 = 2 * $PastDueDays1;
 
        // Revomed allocated from sql
 {
        $todate = date2sql($to);
        $PastDueDays1 = get_company_pref('past_due_days');
        $PastDueDays2 = 2 * $PastDueDays1;
 
        // Revomed allocated from sql

-    $value = "(".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount)";
-       $due = "IF (".TB_PREF."supp_trans.type=20 OR ".TB_PREF."supp_trans.type=21,".TB_PREF."supp_trans.due_date,".TB_PREF."supp_trans.tran_date)";
-       $sql = "SELECT ".TB_PREF."sys_types.type_name,
-               ".TB_PREF."supp_trans.reference,
-               ".TB_PREF."supp_trans.tran_date,
+       if ($all)
+       $value = "(trans.ov_amount + trans.ov_gst + trans.ov_discount)";
+    else
+       $value = "IF (trans.type=".ST_SUPPINVOICE." OR trans.type=".ST_BANKDEPOSIT.", 
+       (trans.ov_amount + trans.ov_gst + trans.ov_discount - trans.alloc),
+       (trans.ov_amount + trans.ov_gst + trans.ov_discount + trans.alloc))";
+       $due = "IF (trans.type=".ST_SUPPINVOICE." OR trans.type=".ST_SUPPCREDIT.",trans.due_date,trans.tran_date)";
+       $sql = "SELECT trans.type,
+               trans.reference,
+               trans.tran_date,

                $value as Balance,
                $value as Balance,

-               IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= 0,$value,0) AS Due,
-               IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= $PastDueDays1,$value,0) AS Overdue1,
-               IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= $PastDueDays2,$value,0) AS Overdue2
+               IF ((TO_DAYS('$todate') - TO_DAYS($due)) > 0,$value,0) AS Due,
+               IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $PastDueDays1,$value,0) AS Overdue1,
+               IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $PastDueDays2,$value,0) AS Overdue2

 
 

-               FROM ".TB_PREF."suppliers,
-                       ".TB_PREF."payment_terms,
-                       ".TB_PREF."supp_trans,
-                       ".TB_PREF."sys_types
+               FROM ".TB_PREF."suppliers supplier,
+                       ".TB_PREF."supp_trans trans

 
 

-               WHERE ".TB_PREF."sys_types.type_id = ".TB_PREF."supp_trans.type
-                       AND ".TB_PREF."suppliers.payment_terms = ".TB_PREF."payment_terms.terms_indicator
-                       AND ".TB_PREF."suppliers.supplier_id = ".TB_PREF."supp_trans.supplier_id
-                       AND ".TB_PREF."supp_trans.supplier_id = $supplier_id
-                       AND ".TB_PREF."supp_trans.tran_date <= '$todate'
-                       AND ABS(".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount) > 0.004
-                       ORDER BY ".TB_PREF."supp_trans.tran_date";
+               WHERE supplier.supplier_id = trans.supplier_id
+                       AND trans.supplier_id = $supplier_id
+                       AND trans.tran_date <= '$todate'
+                       AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_discount) > ".FLOAT_COMP_DELTA;
+       if (!$all)
+               $sql .= " AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_discount) - trans.alloc > ".FLOAT_COMP_DELTA;
+       $sql .= " ORDER BY trans.tran_date";

 
 
        return db_query($sql, "The supplier details could not be retrieved");
 
 
        return db_query($sql, "The supplier details could not be retrieved");


@@ -67,42 +70,53 @@ function get_invoices($supplier_id, $to)
 
 function print_aged_supplier_analysis()
 {
 
 function print_aged_supplier_analysis()
 {

-    global $comp_path, $path_to_root;
+    global $path_to_root, $systypes_array, $SysPrefs;

 
     $to = $_POST['PARAM_0'];
     $fromsupp = $_POST['PARAM_1'];
     $currency = $_POST['PARAM_2'];
 
     $to = $_POST['PARAM_0'];
     $fromsupp = $_POST['PARAM_1'];
     $currency = $_POST['PARAM_2'];

-       $summaryOnly = $_POST['PARAM_3'];
-    $graphics = $_POST['PARAM_4'];
-    $comments = $_POST['PARAM_5'];
-       $destination = $_POST['PARAM_6'];
+       $show_all = $_POST['PARAM_3'];
+       $summaryOnly = $_POST['PARAM_4'];
+    $no_zeros = $_POST['PARAM_5'];
+    $graphics = $_POST['PARAM_6'];
+    $comments = $_POST['PARAM_7'];
+       $orientation = $_POST['PARAM_8'];
+       $destination = $_POST['PARAM_9'];
+

        if ($destination)
                include_once($path_to_root . "/reporting/includes/excel_report.inc");
        else
                include_once($path_to_root . "/reporting/includes/pdf_report.inc");
        if ($destination)
                include_once($path_to_root . "/reporting/includes/excel_report.inc");
        else
                include_once($path_to_root . "/reporting/includes/pdf_report.inc");

+       $orientation = ($orientation ? 'L' : 'P');

        if ($graphics)
        {
                include_once($path_to_root . "/reporting/includes/class.graphic.inc");
                $pg = new graph();
        }
 
        if ($graphics)
        {
                include_once($path_to_root . "/reporting/includes/class.graphic.inc");
                $pg = new graph();
        }
 

-       if ($fromsupp == reserved_words::get_all_numeric())
+       if ($fromsupp == ALL_TEXT)

                $from = _('All');
        else
                $from = get_supplier_name($fromsupp);
                $from = _('All');
        else
                $from = get_supplier_name($fromsupp);

-    $dec = user_price_dec();
+       $dec = user_price_dec();

 
        if ($summaryOnly == 1)
                $summary = _('Summary Only');
        else
                $summary = _('Detailed Report');
 
        if ($summaryOnly == 1)
                $summary = _('Summary Only');
        else
                $summary = _('Detailed Report');

-       if ($currency == reserved_words::get_all())
+       if ($currency == ALL_TEXT)

        {
                $convert = true;
                $currency = _('Balances in Home Currency');
        }
        else
                $convert = false;
        {
                $convert = true;
                $currency = _('Balances in Home Currency');
        }
        else
                $convert = false;

+
+       if ($no_zeros) $nozeros = _('Yes');
+       else $nozeros = _('No');
+       if ($show_all) $show = _('Yes');
+       else $show = _('No');
+

        $PastDueDays1 = get_company_pref('past_due_days');
        $PastDueDays2 = 2 * $PastDueDays1;
        $nowdue = "1-" . $PastDueDays1 . " " . _('Days');
        $PastDueDays1 = get_company_pref('past_due_days');
        $PastDueDays2 = 2 * $PastDueDays1;
        $nowdue = "1-" . $PastDueDays1 . " " . _('Days');


@@ -116,19 +130,23 @@ function print_aged_supplier_analysis()
 
        $aligns = array('left', 'left', 'left', 'right', 'right', 'right', 'right',     'right');
 
 
        $aligns = array('left', 'left', 'left', 'right', 'right', 'right', 'right',     'right');
 

-    $params =   array(         0 => $comments,
-                                   1 => array('text' => _('End Date'), 'from' => $to, 'to' => ''),
-                                   2 => array('text' => _('Supplier'), 'from' => $from, 'to' => ''),
-                                   3 => array('text' => _('Currency'),'from' => $currency,'to' => ''),
-                       4 => array('text' => _('Type'), 'from' => $summary,'to' => ''));
+       $params =   array(      0 => $comments,
+                               1 => array('text' => _('End Date'), 'from' => $to, 'to' => ''),
+                               2 => array('text' => _('Supplier'), 'from' => $from, 'to' => ''),
+                               3 => array('text' => _('Currency'),'from' => $currency,'to' => ''),
+                               4 => array('text' => _('Type'), 'from' => $summary,'to' => ''),
+                    5 => array('text' => _('Show Also Allocated'), 'from' => $show, 'to' => ''),               
+                               6 => array('text' => _('Suppress Zeros'), 'from' => $nozeros, 'to' => ''));

 
        if ($convert)
                $headers[2] = _('currency');
 
        if ($convert)
                $headers[2] = _('currency');

-    $rep = new FrontReport(_('Aged Supplier Analysis'), "AgedSupplierAnalysis", user_pagesize());
+    $rep = new FrontReport(_('Aged Supplier Analysis'), "AgedSupplierAnalysis", user_pagesize(), 9, $orientation);
+    if ($orientation == 'L')
+       recalculate_cols($cols);

 
     $rep->Font();
     $rep->Info($params, $cols, $headers, $aligns);
 
     $rep->Font();
     $rep->Info($params, $cols, $headers, $aligns);

-    $rep->Header();
+    $rep->NewPage();

 
        $total = array();
        $total[0] = $total[1] = $total[2] = $total[3] = $total[4] = 0.0;
 
        $total = array();
        $total[0] = $total[1] = $total[2] = $total[3] = $total[4] = 0.0;


@@ -139,56 +157,61 @@ function print_aged_supplier_analysis()
        $pastdue1 = $PastDueDays1 + 1 . "-" . $PastDueDays2 . " " . _('Days');
        $pastdue2 = _('Over') . " " . $PastDueDays2 . " " . _('Days');
 
        $pastdue1 = $PastDueDays1 + 1 . "-" . $PastDueDays2 . " " . _('Days');
        $pastdue2 = _('Over') . " " . $PastDueDays2 . " " . _('Days');
 

-       $sql = "SELECT supplier_id, supp_name AS name, curr_code FROM ".TB_PREF."suppliers ";
-       if ($fromsupp != reserved_words::get_all_numeric())
-               $sql .= "WHERE supplier_id=".db_escape($fromsupp)." ";
-       $sql .= "ORDER BY supp_name";
+       $sql = "SELECT supplier_id, supp_name AS name, curr_code FROM ".TB_PREF."suppliers";
+       if ($fromsupp != ALL_TEXT)
+               $sql .= " WHERE supplier_id=".db_escape($fromsupp);
+       $sql .= " ORDER BY supp_name";

        $result = db_query($sql, "The suppliers could not be retrieved");
 
        while ($myrow=db_fetch($result))
        {
        $result = db_query($sql, "The suppliers could not be retrieved");
 
        while ($myrow=db_fetch($result))
        {

-               if (!$convert && $currency != $myrow['curr_code'])
+               if (!$convert && $currency != $myrow['curr_code']) continue;
+
+               if ($convert) $rate = get_exchange_rate_from_home_currency($myrow['curr_code'], $to);
+               else $rate = 1.0;
+
+               $supprec = get_supplier_details($myrow['supplier_id'], $to, $show_all);
+               if (!$supprec)

                        continue;
                        continue;

+               $supprec['Balance'] *= $rate;
+               $supprec['Due'] *= $rate;
+               $supprec['Overdue1'] *= $rate;
+               $supprec['Overdue2'] *= $rate;
+
+               $str = array($supprec["Balance"] - $supprec["Due"],
+                       $supprec["Due"]-$supprec["Overdue1"],
+                       $supprec["Overdue1"]-$supprec["Overdue2"],
+                       $supprec["Overdue2"],
+                       $supprec["Balance"]);
+
+               if ($no_zeros && floatcmp(array_sum($str), 0) == 0) continue;
+

                $rep->fontSize += 2;
                $rep->TextCol(0, 2,     $myrow['name']);
                $rep->fontSize += 2;
                $rep->TextCol(0, 2,     $myrow['name']);

-               if ($convert)
-               {
-                       $rate = get_exchange_rate_from_home_currency($myrow['curr_code'], $to);
-                       $rep->TextCol(2, 3,     $myrow['curr_code']);
-               }
-               else
-                       $rate = 1.0;
+               if ($convert) $rep->TextCol(2, 3,       $myrow['curr_code']);

                $rep->fontSize -= 2;
                $rep->fontSize -= 2;

-               $supprec = get_supplier_details($myrow['supplier_id'], $to);
-               foreach ($supprec as $i => $value)
-                       $supprec[$i] *= $rate;

                $total[0] += ($supprec["Balance"] - $supprec["Due"]);
                $total[1] += ($supprec["Due"]-$supprec["Overdue1"]);
                $total[2] += ($supprec["Overdue1"]-$supprec["Overdue2"]);
                $total[3] += $supprec["Overdue2"];
                $total[4] += $supprec["Balance"];
                $total[0] += ($supprec["Balance"] - $supprec["Due"]);
                $total[1] += ($supprec["Due"]-$supprec["Overdue1"]);
                $total[2] += ($supprec["Overdue1"]-$supprec["Overdue2"]);
                $total[3] += $supprec["Overdue2"];
                $total[4] += $supprec["Balance"];

-               $str = array($supprec["Balance"] - $supprec["Due"],
-                       $supprec["Due"]-$supprec["Overdue1"],
-                       $supprec["Overdue1"]-$supprec["Overdue2"],
-                       $supprec["Overdue2"],
-                       $supprec["Balance"]);

                for ($i = 0; $i < count($str); $i++)
                        $rep->AmountCol($i + 3, $i + 4, $str[$i], $dec);
                $rep->NewLine(1, 2);
                if (!$summaryOnly)
                {
                for ($i = 0; $i < count($str); $i++)
                        $rep->AmountCol($i + 3, $i + 4, $str[$i], $dec);
                $rep->NewLine(1, 2);
                if (!$summaryOnly)
                {

-                       $res = get_invoices($myrow['supplier_id'], $to);
+                       $res = get_invoices($myrow['supplier_id'], $to, $show_all);

                if (db_num_rows($res)==0)
                                continue;
                $rep->Line($rep->row + 4);
                        while ($trans=db_fetch($res))
                        {
                                $rep->NewLine(1, 2);
                if (db_num_rows($res)==0)
                                continue;
                $rep->Line($rep->row + 4);
                        while ($trans=db_fetch($res))
                        {
                                $rep->NewLine(1, 2);

-                       $rep->TextCol(0, 1,     $trans['type_name'], -2);
+                       $rep->TextCol(0, 1, $systypes_array[$trans['type']], -2);

                                $rep->TextCol(1, 2,     $trans['reference'], -2);
                                $rep->TextCol(2, 3,     sql2date($trans['tran_date']), -2);
                                foreach ($trans as $i => $value)
                                $rep->TextCol(1, 2,     $trans['reference'], -2);
                                $rep->TextCol(2, 3,     sql2date($trans['tran_date']), -2);
                                foreach ($trans as $i => $value)

-                                       $trans[$i] *= $rate;
+                                       $trans[$i] = (float)$trans[$i] * $rate;

                                $str = array($trans["Balance"] - $trans["Due"],
                                        $trans["Due"]-$trans["Overdue1"],
                                        $trans["Overdue1"]-$trans["Overdue2"],
                                $str = array($trans["Balance"] - $trans["Due"],
                                        $trans["Due"]-$trans["Overdue1"],
                                        $trans["Overdue1"]-$trans["Overdue2"],


@@ -221,28 +244,25 @@ function print_aged_supplier_analysis()
        $rep->NewLine();
        if ($graphics)
        {
        $rep->NewLine();
        if ($graphics)
        {

-               global $decseps, $graph_skin;

                $pg->x = array(_('Current'), $nowdue, $pastdue1, $pastdue2);
                $pg->title     = $rep->title;
                $pg->axis_x    = _("Days");
                $pg->axis_y    = _("Amount");
                $pg->graphic_1 = $to;
                $pg->type      = $graphics;
                $pg->x = array(_('Current'), $nowdue, $pastdue1, $pastdue2);
                $pg->title     = $rep->title;
                $pg->axis_x    = _("Days");
                $pg->axis_y    = _("Amount");
                $pg->graphic_1 = $to;
                $pg->type      = $graphics;

-               $pg->skin      = $graph_skin;
+               $pg->skin      = $SysPrefs->graph_skin;

                $pg->built_in  = false;
                $pg->built_in  = false;

-               $pg->fontfile  = $path_to_root . "/reporting/fonts/Vera.ttf";
-               $pg->latin_notation = ($decseps[$_SESSION["wa_current_user"]->prefs->dec_sep()] != ".");
-               $filename = $comp_path.'/'.user_company(). "/pdf_files/test.png";
+               $pg->latin_notation = ($SysPrefs->decseps[user_dec_sep()] != ".");
+               $filename = company_path(). "/pdf_files/". random_id().".png";

                $pg->display($filename, true);
                $w = $pg->width / 1.5;
                $h = $pg->height / 1.5;
                $x = ($rep->pageWidth - $w) / 2;
                $rep->NewLine(2);
                if ($rep->row - $h < $rep->bottomMargin)
                $pg->display($filename, true);
                $w = $pg->width / 1.5;
                $h = $pg->height / 1.5;
                $x = ($rep->pageWidth - $w) / 2;
                $rep->NewLine(2);
                if ($rep->row - $h < $rep->bottomMargin)

-                       $rep->Header();
+                       $rep->NewPage();

                $rep->AddImage($filename, $x, $rep->row - $h, $w, $h);
        }
     $rep->End();
 }
 
                $rep->AddImage($filename, $x, $rep->row - $h, $w, $h);
        }
     $rep->End();
 }
 

-?>
\ No newline at end of file