Fixed excel report redirection (regresion after customer selector in reports improvem...

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

diff --git a/reporting/rep108.php b/reporting/rep108.php
index e07e49fe91f307b4bb4291a7b99ef6dbf4fe8c99..b78f5596d57c0576b953c5f4b845b7e3a6c34da7 100644 (file)
--- a/reporting/rep108.php
+++ b/reporting/rep108.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_CUSTSTATREP';

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


@@ -22,6 +22,7 @@ include_once($path_to_root . "/includes/session.inc");
 include_once($path_to_root . "/includes/date_functions.inc");
 include_once($path_to_root . "/includes/data_checks.inc");
 include_once($path_to_root . "/sales/includes/sales_db.inc");
 include_once($path_to_root . "/includes/date_functions.inc");
 include_once($path_to_root . "/includes/data_checks.inc");
 include_once($path_to_root . "/sales/includes/sales_db.inc");

+include_once($path_to_root . "/includes/db/crm_contacts_db.inc");

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


@@ -29,18 +30,23 @@ print_statements();
 
 //----------------------------------------------------------------------------------------------------
 
 
 //----------------------------------------------------------------------------------------------------
 

-function getTransactions($debtorno, $date)
+function getTransactions($debtorno, $date, $outstanding)

 {
 {

-    $sql = "SELECT ".TB_PREF."debtor_trans.*, ".TB_PREF."sys_types.type_name,
-                               (".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount)
+    $sql = "SELECT ".TB_PREF."debtor_trans.*,
+                               (".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight +
+                               ".TB_PREF."debtor_trans.ov_freight_tax + ".TB_PREF."debtor_trans.ov_discount)

                                AS TotalAmount, ".TB_PREF."debtor_trans.alloc AS Allocated,
                                AS TotalAmount, ".TB_PREF."debtor_trans.alloc AS Allocated,

-                               ((".TB_PREF."debtor_trans.type = 10)
+                               ((".TB_PREF."debtor_trans.type = ".ST_SALESINVOICE.")

                                        AND ".TB_PREF."debtor_trans.due_date < '$date') AS OverDue
                                        AND ".TB_PREF."debtor_trans.due_date < '$date') AS OverDue

-                       FROM ".TB_PREF."debtor_trans, ".TB_PREF."sys_types
-                       WHERE ".TB_PREF."debtor_trans.tran_date <= '$date' AND ".TB_PREF."debtor_trans.debtor_no = $debtorno
-                               AND ".TB_PREF."debtor_trans.type = ".TB_PREF."sys_types.type_id
-                               AND ".TB_PREF."debtor_trans.type <> 13
-                               ORDER BY ".TB_PREF."debtor_trans.tran_date";
+                       FROM ".TB_PREF."debtor_trans
+                       WHERE ".TB_PREF."debtor_trans.tran_date <= '$date' AND ".TB_PREF."debtor_trans.debtor_no = ".db_escape($debtorno)."
+                               AND ".TB_PREF."debtor_trans.type <> ".ST_CUSTDELIVERY."
+                               AND (".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight +
+                               ".TB_PREF."debtor_trans.ov_freight_tax + ".TB_PREF."debtor_trans.ov_discount) != 0";
+       if ($outstanding)
+               $sql .= " AND ABS(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight +
+                               ".TB_PREF."debtor_trans.ov_freight_tax + ".TB_PREF."debtor_trans.ov_discount) - alloc <> 0";
+       $sql .= " ORDER BY ".TB_PREF."debtor_trans.tran_date";

 
     return db_query($sql,"No transactions were returned");
 }
 
     return db_query($sql,"No transactions were returned");
 }


@@ -49,13 +55,13 @@ function getTransactions($debtorno, $date)
 
 function print_statements()
 {
 
 function print_statements()
 {

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

 
        include_once($path_to_root . "/reporting/includes/pdf_report.inc");
 
        $customer = $_POST['PARAM_0'];
        $currency = $_POST['PARAM_1'];
 
        include_once($path_to_root . "/reporting/includes/pdf_report.inc");
 
        $customer = $_POST['PARAM_0'];
        $currency = $_POST['PARAM_1'];

-       $bankaccount = $_POST['PARAM_2'];
+       $outstanding = $_POST['PARAM_2'];

        $email = $_POST['PARAM_3'];
        $comments = $_POST['PARAM_4'];
 
        $email = $_POST['PARAM_3'];
        $comments = $_POST['PARAM_4'];
 


@@ -67,10 +73,7 @@ function print_statements()
 
        $aligns = array('left', 'left', 'left', 'left', 'right', 'right', 'right', 'right');
 
 
        $aligns = array('left', 'left', 'left', 'left', 'right', 'right', 'right', 'right');
 

-       $params = array('comments' => $comments,
-                                       'bankaccount' => $bankaccount);
-
-       $baccount = get_bank_account($params['bankaccount']);
+       $params = array('comments' => $comments);

 
        $cur = get_company_pref('curr_default');
        $PastDueDays1 = get_company_pref('past_due_days');
 
        $cur = get_company_pref('curr_default');
        $PastDueDays1 = get_company_pref('past_due_days');


@@ -79,13 +82,14 @@ function print_statements()
        if ($email == 0)
        {
                $rep = new FrontReport(_('STATEMENT'), "StatementBulk", user_pagesize());
        if ($email == 0)
        {
                $rep = new FrontReport(_('STATEMENT'), "StatementBulk", user_pagesize());

+               $rep->SetHeaderType('Header2');

                $rep->currency = $cur;
                $rep->Font();
                $rep->Info($params, $cols, null, $aligns);
        }
 
                $rep->currency = $cur;
                $rep->Font();
                $rep->Info($params, $cols, null, $aligns);
        }
 

-       $sql = "SELECT debtor_no, name AS DebtorName, address, tax_id, email, curr_code, curdate() AS tran_date, payment_terms FROM ".TB_PREF."debtors_master";
-       if ($customer != reserved_words::get_all_numeric())
+       $sql = "SELECT debtor_no, name AS DebtorName, address, tax_id, curr_code, curdate() AS tran_date FROM ".TB_PREF."debtors_master";
+       if ($customer != ALL_TEXT)

                $sql .= " WHERE debtor_no = ".db_escape($customer);
        else
                $sql .= " ORDER by name";
                $sql .= " WHERE debtor_no = ".db_escape($customer);
        else
                $sql .= " ORDER by name";


@@ -97,30 +101,29 @@ function print_statements()
 
                $myrow['order_'] = "";
 
 
                $myrow['order_'] = "";
 

-               $TransResult = getTransactions($myrow['debtor_no'], $date);
+               $TransResult = getTransactions($myrow['debtor_no'], $date, $outstanding);
+               $baccount = get_default_bank_account($myrow['curr_code']);
+               $params['bankaccount'] = $baccount['id'];

                if (db_num_rows($TransResult) == 0)
                        continue;
                if ($email == 1)
                {
                        $rep = new FrontReport("", "", user_pagesize());
                if (db_num_rows($TransResult) == 0)
                        continue;
                if ($email == 1)
                {
                        $rep = new FrontReport("", "", user_pagesize());

+                       $rep->SetHeaderType('Header2');

                        $rep->currency = $cur;
                        $rep->Font();
                        $rep->title = _('STATEMENT');
                        $rep->filename = "Statement" . $myrow['debtor_no'] . ".pdf";
                        $rep->Info($params, $cols, null, $aligns);
                }
                        $rep->currency = $cur;
                        $rep->Font();
                        $rep->title = _('STATEMENT');
                        $rep->filename = "Statement" . $myrow['debtor_no'] . ".pdf";
                        $rep->Info($params, $cols, null, $aligns);
                }

-               $rep->Header2($myrow, null, null, $baccount, 12);
+               $contacts = get_customer_contacts($myrow['debtor_no'], 'invoice');
+               //= get_branch_contacts($branch['branch_code'], 'invoice', $branch['debtor_no']);
+               $rep->SetCommonData($myrow, null, null, $baccount, ST_STATEMENT, $contacts);
+               $rep->NewPage();

                $rep->NewLine();
                $linetype = true;
                $rep->NewLine();
                $linetype = true;

-               $doctype = 12;
-               if ($rep->currency != $myrow['curr_code'])
-               {
-                       include($path_to_root . "/reporting/includes/doctext2.inc");
-               }
-               else
-               {
-                       include($path_to_root . "/reporting/includes/doctext.inc");
-               }
+               $doctype = ST_STATEMENT;
+               include($path_to_root . "/reporting/includes/doctext.inc");

                $rep->fontSize += 2;
                $rep->TextCol(0, 8, $doc_Outstanding);
                $rep->fontSize -= 2;
                $rep->fontSize += 2;
                $rep->TextCol(0, 8, $doc_Outstanding);
                $rep->fontSize -= 2;


@@ -131,12 +134,12 @@ function print_statements()
                        $DisplayAlloc = number_format2($myrow2["Allocated"],$dec);
                        $DisplayNet = number_format2($myrow2["TotalAmount"] - $myrow2["Allocated"],$dec);
 
                        $DisplayAlloc = number_format2($myrow2["Allocated"],$dec);
                        $DisplayNet = number_format2($myrow2["TotalAmount"] - $myrow2["Allocated"],$dec);
 

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

                        $rep->TextCol(1, 2,     $myrow2['reference'], -2);
                        $rep->TextCol(2, 3,     sql2date($myrow2['tran_date']), -2);
                        $rep->TextCol(1, 2,     $myrow2['reference'], -2);
                        $rep->TextCol(2, 3,     sql2date($myrow2['tran_date']), -2);

-                       if ($myrow2['type'] == 10)
+                       if ($myrow2['type'] == ST_SALESINVOICE)

                                $rep->TextCol(3, 4,     sql2date($myrow2['due_date']), -2);
                                $rep->TextCol(3, 4,     sql2date($myrow2['due_date']), -2);

-                       if ($myrow2['type'] == 10)
+                       if ($myrow2['type'] == ST_SALESINVOICE || $myrow2['type'] == ST_BANKPAYMENT)

                                $rep->TextCol(4, 5,     $DisplayTotal, -2);
                        else
                                $rep->TextCol(5, 6,     $DisplayTotal, -2);
                                $rep->TextCol(4, 5,     $DisplayTotal, -2);
                        else
                                $rep->TextCol(5, 6,     $DisplayTotal, -2);


@@ -144,7 +147,7 @@ function print_statements()
                        $rep->TextCol(7, 8,     $DisplayNet, -2);
                        $rep->NewLine();
                        if ($rep->row < $rep->bottomMargin + (10 * $rep->lineHeight))
                        $rep->TextCol(7, 8,     $DisplayNet, -2);
                        $rep->NewLine();
                        if ($rep->row < $rep->bottomMargin + (10 * $rep->lineHeight))

-                               $rep->Header2($myrow, null, null, $baccount, 12);
+                               $rep->NewPage();

                }
                $nowdue = "1-" . $PastDueDays1 . " " . $doc_Days;
                $pastdue1 = $PastDueDays1 + 1 . "-" . $PastDueDays2 . " " . $doc_Days;
                }
                $nowdue = "1-" . $PastDueDays1 . " " . $doc_Days;
                $pastdue1 = $PastDueDays1 + 1 . "-" . $PastDueDays2 . " " . $doc_Days;


@@ -158,14 +161,14 @@ function print_statements()
                        number_format2($CustomerRecord["Balance"],$dec));
                $col = array($rep->cols[0], $rep->cols[0] + 110, $rep->cols[0] + 210, $rep->cols[0] + 310,
                        $rep->cols[0] + 410, $rep->cols[0] + 510);
                        number_format2($CustomerRecord["Balance"],$dec));
                $col = array($rep->cols[0], $rep->cols[0] + 110, $rep->cols[0] + 210, $rep->cols[0] + 310,
                        $rep->cols[0] + 410, $rep->cols[0] + 510);

-               $rep->row = $rep->bottomMargin + (8 * $rep->lineHeight - 6);
+               $rep->row = $rep->bottomMargin + (10 * $rep->lineHeight - 6);

                for ($i = 0; $i < 5; $i++)
                        $rep->TextWrap($col[$i], $rep->row, $col[$i + 1] - $col[$i], $str[$i], 'right');
                $rep->NewLine();
                for ($i = 0; $i < 5; $i++)
                        $rep->TextWrap($col[$i], $rep->row, $col[$i + 1] - $col[$i], $str2[$i], 'right');
                if ($email == 1)
                for ($i = 0; $i < 5; $i++)
                        $rep->TextWrap($col[$i], $rep->row, $col[$i + 1] - $col[$i], $str[$i], 'right');
                $rep->NewLine();
                for ($i = 0; $i < 5; $i++)
                        $rep->TextWrap($col[$i], $rep->row, $col[$i + 1] - $col[$i], $str2[$i], 'right');
                if ($email == 1)

-                       $rep->End($email, $doc_Statement . " " . $doc_as_of . " " . sql2date($date), $myrow, 12);
+                       $rep->End($email, $doc_Statement . " " . $doc_as_of . " " . sql2date($date), $myrow, ST_STATEMENT);

 
        }
        if ($email == 0)
 
        }
        if ($email == 0)