• tcpip
  • New member
  • Offline
  • Registered: 06/24/2015
  • Posts: 1

Topic: Print Customer Balance On Invoice

Hello All!!

Can anyone help me to add on invoice printout the customer balance???

thanks

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: Print Customer Balance On Invoice

This could be tricky - any copy that is printed out will possibly have the balance value (dynamically computed at print time) as on the print out date and not on the invoice creation date.

apmuthu's Website

  • Registered: 12/25/2007
  • Posts: 2,446

Re: Print Customer Balance On Invoice

Sales Invoice is not for that !!
Can't you attach Customer Balance report to the invocie instead ???

If not, you will have to customize sales invoice report for your needs.

Janusz

  • From: USA, CA, Valencia
  • Registered: 12/31/2017
  • Posts: 56

Re: Print Customer Balance On Invoice

I have a service industry and am trying to create a "service ticket/invoice" with the total customer balance at the time of printing. I have imported the function get_open_balance from rep101 and am trying to use it but it returns an array. Maybe the array needs to be added up before displaying?

Here is the funcion I imported to the begining of my version of rep107
The parts with variable $to were disabled since it is not passed in an invoice

function get_open_balance($debtorno)
{
    #if($to) 
    #    $to = date2sql($to);

     $sql = "SELECT SUM(IF(t.type = ".ST_SALESINVOICE." OR (t.type = ".ST_JOURNAL." AND t.ov_amount>0),
         -abs(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount), 0)) AS charges,";
     $sql .= "SUM(IF(t.type != ".ST_SALESINVOICE." AND NOT(t.type = ".ST_JOURNAL." AND t.ov_amount>0),
         abs(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) * -1, 0)) AS credits,";
     $sql .= "SUM(IF(t.type != ".ST_SALESINVOICE." AND NOT(t.type = ".ST_JOURNAL." AND t.ov_amount>0), t.alloc * -1, t.alloc)) AS Allocated,";

     $sql .=    "SUM(IF(t.type = ".ST_SALESINVOICE.", 1, -1) *
             (abs(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) - abs(t.alloc))) AS OutStanding
        FROM ".TB_PREF."debtor_trans t
        WHERE t.debtor_no = ".db_escape($debtorno)
        ." AND t.type <> ".ST_CUSTDELIVERY;
    # if ($to)
    #    $sql .= " AND t.tran_date < '$to'";
    #$sql .= " GROUP BY debtor_no"; 

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

On the end of the invoice printing side I have this:
The lines are commented becuse it is broken.

$rep->NewLine();
                #$bal = get_open_balance($myrow['debtor_no']); // added for total balance not working function returns an array.
                #$DisplayCustTotal = number_format2($bal,$dec); // added for total balance
            $DisplayTotal = number_format2($sign*($myrow["ov_freight"] + $myrow["ov_gst"] +
                $myrow["ov_amount"]+$myrow["ov_freight_tax"]),$dec);
            $rep->Font('bold');
            if (!$myrow['prepaid']) $rep->Font('bold');
                $rep->TextCol(3, 6, $rep->formData['prepaid'] ? _("TOTAL ORDER VAT INCL.") : _("THIS INVOICE"), - 2);
            $rep->TextCol(6, 7, $DisplayTotal, -2);
            $rep->NewLine(); // added for total balance
            $rep->TextCol(3, 6, $rep->formData['prepaid'] ? _("TOTAL ORDER VAT INCL.") : _("TOTAL BALANCE"), - 2); // added for total balance
            #$rep->TextCol(6, 7, $DisplayCustTotal, -2); // added for total balance
            if ($rep->formData['prepaid'])
            {
                $rep->NewLine();
                $rep->Font('bold');
                $rep->TextCol(3, 6, $rep->formData['prepaid']=='final' ? _("THIS INVOICE") : _("TOTAL INVOICE"), - 2);
                $rep->TextCol(6, 7, number_format2($myrow['prep_amount'], $dec), -2);
            }
            $words = price_in_words($rep->formData['prepaid'] ? $myrow['prep_amount'] : $myrow['Total']
                , array( 'type' => ST_SALESINVOICE, 'currency' => $myrow['curr_code']));
            if ($words != "")
            {
                $rep->NewLine(1);
                $rep->TextCol(1, 7, $myrow['curr_code'] . ": " . $words, - 2);
            }
            $rep->Font();
            if ($email == 1)
            {
                $rep->End($email);
            }

I am not a very good programmer and I have no experience in PHP. Thank you in advance you guys are lifesavers.

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: Print Customer Balance On Invoice

The function needs 2 arguments and the second one $to is missing. It should be computed to be the current date/time within the function if the argument is not given.

apmuthu's Website

6 Reply by trafficpest (edited by trafficpest 01/17/2018 07:10:33 am)

  • From: USA, CA, Valencia
  • Registered: 12/31/2017
  • Posts: 56

Re: Print Customer Balance On Invoice

@Apmuthu

Ok I got it working Thank You for the help. I'll send you some money on Pay Pal for your help on Monday (Waiting to verify the new Bank Account) I'll send it to the Email on you whois for the domain.

So, Here is the code for anyone that wants it. I am no coder so someone might want to clean it up, but it works.
It will print the clients current balance for all invoices up to the day it s printed. For example if today is 1/12/18 it will includeinvoices with dates from 1/5/18 and 1/12/18 but not an invoice made for the future 1/16/18.

add this funcion from rep101.php to the top of your /reporting/rep107.php where all the funcions are

function get_open_balance($debtorno, $to)
{
    if($to)
        $to = date2sql($to);

     $sql = "SELECT SUM(IF(t.type = ".ST_SALESINVOICE." OR (t.type = ".ST_JOURNAL." AND t.ov_amount>0),
         -abs(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount), 0)) AS charges,";
     $sql .= "SUM(IF(t.type != ".ST_SALESINVOICE." AND NOT(t.type = ".ST_JOURNAL." AND t.ov_amount>0),
         abs(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) * -1, 0)) AS credits,";
     $sql .= "SUM(IF(t.type != ".ST_SALESINVOICE." AND NOT(t.type = ".ST_JOURNAL." AND t.ov_amount>0), t.alloc * -1, t.alloc)) AS Allocated,";

     $sql .=    "SUM(IF(t.type = ".ST_SALESINVOICE.", 1, -1) *
             (abs(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) - abs(t.alloc))) AS OutStanding
        FROM ".TB_PREF."debtor_trans t
        WHERE t.debtor_no = ".db_escape($debtorno)
        ." AND t.type <> ".ST_CUSTDELIVERY;
    if ($to)
        $sql .= " AND t.tran_date < '$to+1'";
    $sql .= " GROUP BY debtor_no";

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

Then add this above

                        $accumulate = 0;                    
                $bal = get_open_balance($myrow['debtor_no'],today()); 
                $init[0] = $init[1] = 0.0;
            $init[0] = round2(abs($bal['charges']), $dec);
            $init[1] = round2(Abs($bal['credits']), $dec);
            $init[2] = round2($bal['Allocated'], $dec);
            $init[3] = $init[0] - $init[1];
            $accumulate += $init[3];

and this below

$rep->TextCol(6, 7, $DisplayTotal, -2);
            $rep->NewLine(); // added for total balance
            $rep->TextCol(3, 6, $rep->formData['prepaid'] ? _("TOTAL ORDER VAT INCL.") : _("TOTAL BALANCE"), - 2); 
            $DisplayGrand = number_format2($init[3],$dec);
            $rep->TextCol(6, 7, $DisplayGrand, -2); // added for total balance

This line of code

            $DisplayTotal = number_format2($sign*($myrow["ov_freight"] + $myrow["ov_gst"] +
                $myrow["ov_amount"]+$myrow["ov_freight_tax"]),$dec);
            $rep->Font('bold');
            if (!$myrow['prepaid']) $rep->Font('bold');
                $rep->TextCol(3, 6, $rep->formData['prepaid'] ? _("TOTAL ORDER VAT INCL.") : _("THIS INVOICE"), - 2);
  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: Print Customer Balance On Invoice

@trafficpest: The "add this above ... below", etc are not discernible. Kindly post the whole modified rep107.php file so that a diff file can be prepared. Thanks for the "money" offer - don't bother.

@joe: there is an error in the get_invoice_range() if it straddles fiscal years with the default "pattern" of references. The year comes last in the default pattern ({001}/{YEAR})and the actual invoice number comes in first so if we choose 001/2016 to 001/2018, we miss out on 002/2016, 002/2017, etc.

The $sql in get_invoice_range() we get will be:

SELECT trans.trans_no, trans.reference
        FROM &TB_PREF&debtor_trans trans 
            LEFT JOIN &TB_PREF&voided voided ON trans.type=voided.type AND trans.trans_no=voided.id
        WHERE trans.type=10 AND ISNULL(voided.id) 
        AND trans.reference>='001/2016' 
        AND trans.reference<='001/2018' 
        ORDER BY trans.tran_date, trans.reference

We inspect the above by temporarily putting in:
file_put_contents('D:/WebServers/XAMPP/htdocs/frontac24/tmp/log_file.txt', $sql, FILE_APPEND);
in line 42 of reporting/rep107.php

To alleviate this, we can change the default pattern to be {YEAR}/{001} but will that lead to other problems?

apmuthu's Website

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: Print Customer Balance On Invoice

The fix for the invoice range is not to rely on any pattern but to rely on the transaction number and type directly. Hence, replace lines 39-40 of reporting/rep107.php:

            ." AND trans.reference>=".db_escape(get_reference(ST_SALESINVOICE, $from))
            ." AND trans.reference<=".db_escape(get_reference(ST_SALESINVOICE, $to))

with:

            ." AND trans.trans_no BETWEEN ".db_escape($from)." AND ".db_escape($to)

This will result in the $sql in the get range function to be like:

SELECT trans.trans_no, trans.reference
FROM &TB_PREF&debtor_trans trans 
    LEFT JOIN &TB_PREF&voided voided ON trans.type=voided.type AND trans.trans_no=voided.id
WHERE trans.type=10 AND ISNULL(voided.id) 
    AND trans.trans_no BETWEEN '1' AND '5' 
ORDER BY trans.tran_date, trans.reference

@joe: see if fit to commit.

Post's attachments

CorrectInvRangePrt.pdf 8.2 kb, 14 downloads since 2018-01-13 

You don't have the permssions to download the attachments of this post.

apmuthu's Website

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,085

Re: Print Customer Balance On Invoice

The error in getting sales invoice sequences is fixed and committed.

/Joe

  • From: USA, CA, Valencia
  • Registered: 12/31/2017
  • Posts: 56

Re: Print Customer Balance On Invoice

Ok here is a stock file with only the print invoice balance modifacations.

<?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 = $_POST['PARAM_0'] == $_POST['PARAM_1'] ?
    'SA_SALESTRANSVIEW' : 'SA_SALESBULKREP';
// ----------------------------------------------------------------
// $ Revision:    2.0 $
// Creator:    Joe Hunt
// date_:    2005-05-19
// Title:    Print Invoices
// ----------------------------------------------------------------
$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 . "/includes/data_checks.inc");
include_once($path_to_root . "/sales/includes/sales_db.inc");

//----------------------------------------------------------------------------------------------------
function get_invoice_range($from, $to)
{
    global $SysPrefs;

    $ref = ($SysPrefs->print_invoice_no() == 1 ? "trans_no" : "reference");

    $sql = "SELECT trans.trans_no, trans.reference
        FROM ".TB_PREF."debtor_trans trans 
            LEFT JOIN ".TB_PREF."voided voided ON trans.type=voided.type AND trans.trans_no=voided.id
        WHERE trans.type=".ST_SALESINVOICE
            ." AND ISNULL(voided.id)"
            ." AND trans.reference>=".db_escape(get_reference(ST_SALESINVOICE, $from))
            ." AND trans.reference<=".db_escape(get_reference(ST_SALESINVOICE, $to))
        ." ORDER BY trans.tran_date, trans.$ref";

    return db_query($sql, "Cant retrieve invoice range");
}

print_invoices();

//---------------------------------------------------------------------------------------------------- added from rep101 to get current balance


function get_open_balance($debtorno, $to)
{
    if($to)
        $to = date2sql($to);

     $sql = "SELECT SUM(IF(t.type = ".ST_SALESINVOICE." OR (t.type = ".ST_JOURNAL." AND t.ov_amount>0),
         -abs(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount), 0)) AS charges,";
     $sql .= "SUM(IF(t.type != ".ST_SALESINVOICE." AND NOT(t.type = ".ST_JOURNAL." AND t.ov_amount>0),
         abs(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) * -1, 0)) AS credits,";
     $sql .= "SUM(IF(t.type != ".ST_SALESINVOICE." AND NOT(t.type = ".ST_JOURNAL." AND t.ov_amount>0), t.alloc * -1, t.alloc)) AS Allocated,";

     $sql .=    "SUM(IF(t.type = ".ST_SALESINVOICE.", 1, -1) *
             (abs(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) - abs(t.alloc))) AS OutStanding
        FROM ".TB_PREF."debtor_trans t
        WHERE t.debtor_no = ".db_escape($debtorno)
        ." AND t.type <> ".ST_CUSTDELIVERY;
    if ($to)
        $sql .= " AND t.tran_date < '$to+1'"; // Has +1 to include any invoices created today in the balance
    $sql .= " GROUP BY debtor_no";

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


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

function print_invoices()
{
    global $path_to_root, $SysPrefs;
    
    $show_this_payment = true; // include payments invoiced here in summary

    include_once($path_to_root . "/reporting/includes/pdf_report.inc");

    $from = $_POST['PARAM_0'];
    $to = $_POST['PARAM_1'];
    $currency = $_POST['PARAM_2'];
    $email = $_POST['PARAM_3'];
    $pay_service = $_POST['PARAM_4'];
    $comments = $_POST['PARAM_5'];
    $customer = $_POST['PARAM_6'];
    $orientation = $_POST['PARAM_7'];

    if (!$from || !$to) return;

    $orientation = ($orientation ? 'L' : 'P');
    $dec = user_price_dec();

     $fno = explode("-", $from);
    $tno = explode("-", $to);
    $from = min($fno[0], $tno[0]);
    $to = max($fno[0], $tno[0]);

    //-------------code-Descr-Qty--uom--tax--prc--Disc-Tot--//
    $cols = array(4, 60, 225, 300, 325, 385, 450, 515);

    // $headers in doctext.inc
    $aligns = array('left',    'left',    'right', 'center', 'right', 'right', 'right');

    $params = array('comments' => $comments);

    $cur = get_company_Pref('curr_default');

    if ($email == 0)
        $rep = new FrontReport(_('INVOICE'), "InvoiceBulk", user_pagesize(), 9, $orientation);
    if ($orientation == 'L')
        recalculate_cols($cols);

    $range = get_invoice_range($from, $to);
    while($row = db_fetch($range))
    {
            if (!exists_customer_trans(ST_SALESINVOICE, $row['trans_no']))
                continue;
            $sign = 1;
            $myrow = get_customer_trans($row['trans_no'], ST_SALESINVOICE);

            if ($customer && $myrow['debtor_no'] != $customer) {
                continue;
            }
            if ($currency != ALL_TEXT && $myrow['curr_code'] != $currency) {
                continue;
            }
            $baccount = get_default_bank_account($myrow['curr_code']);
            $params['bankaccount'] = $baccount['id'];

            $branch = get_branch($myrow["branch_code"]);
            $sales_order = get_sales_order_header($myrow["order_"], ST_SALESORDER);
            if ($email == 1)
            {
                $rep = new FrontReport("", "", user_pagesize(), 9, $orientation);
                $rep->title = _('INVOICE');
                $rep->filename = "Invoice" . $myrow['reference'] . ".pdf";
            }    
            $rep->currency = $cur;
            $rep->Font();
            $rep->Info($params, $cols, null, $aligns);

            $contacts = get_branch_contacts($branch['branch_code'], 'invoice', $branch['debtor_no'], true);
            $baccount['payment_service'] = $pay_service;
            $rep->SetCommonData($myrow, $branch, $sales_order, $baccount, ST_SALESINVOICE, $contacts);
            $rep->SetHeaderType('Header2');
            $rep->NewPage();
            // calculate summary start row for later use
            $summary_start_row = $rep->bottomMargin + (15 * $rep->lineHeight);

            if ($rep->formData['prepaid'])
            {
                $result = get_sales_order_invoices($myrow['order_']);
                $prepayments = array();
                while($inv = db_fetch($result))
                {
                    $prepayments[] = $inv;
                    if ($inv['trans_no'] == $row['trans_no'])
                    break;
                }

                if (count($prepayments) > ($show_this_payment ? 0 : 1))
                    $summary_start_row += (count($prepayments)) * $rep->lineHeight;
                else
                    unset($prepayments);
            }

               $result = get_customer_trans_details(ST_SALESINVOICE, $row['trans_no']);
            $SubTotal = 0;
            while ($myrow2=db_fetch($result))
            {
                if ($myrow2["quantity"] == 0)
                    continue;

                $Net = round2($sign * ((1 - $myrow2["discount_percent"]) * $myrow2["unit_price"] * $myrow2["quantity"]),
                   user_price_dec());
                $SubTotal += $Net;
                $DisplayPrice = number_format2($myrow2["unit_price"],$dec);
                $DisplayQty = number_format2($sign*$myrow2["quantity"],get_qty_dec($myrow2['stock_id']));
                $DisplayNet = number_format2($Net,$dec);
                if ($myrow2["discount_percent"]==0)
                      $DisplayDiscount ="";
                else
                      $DisplayDiscount = number_format2($myrow2["discount_percent"]*100,user_percent_dec()) . "%";
                $c=0;
                $rep->TextCol($c++, $c,    $myrow2['stock_id'], -2);
                $oldrow = $rep->row;
                $rep->TextColLines($c++, $c, $myrow2['StockDescription'], -2);
                $newrow = $rep->row;
                $rep->row = $oldrow;
                if ($Net != 0.0 || !is_service($myrow2['mb_flag']) || !$SysPrefs->no_zero_lines_amount())
                {
                    $rep->TextCol($c++, $c,    $DisplayQty, -2);
                    $rep->TextCol($c++, $c,    $myrow2['units'], -2);
                    $rep->TextCol($c++, $c,    $DisplayPrice, -2);
                    $rep->TextCol($c++, $c,    $DisplayDiscount, -2);
                    $rep->TextCol($c++, $c,    $DisplayNet, -2);
                }
                $rep->row = $newrow;
                //$rep->NewLine(1);
                if ($rep->row < $summary_start_row)
                    $rep->NewPage();
            }

            $memo = get_comments_string(ST_SALESINVOICE, $row['trans_no']);
            if ($memo != "")
            {
                $rep->NewLine();
                $rep->TextColLines(1, 3, $memo, -2);
            }

               $DisplaySubTot = number_format2($SubTotal,$dec);

            // set to start of summary line:
            $rep->row = $summary_start_row;
            if (isset($prepayments))
            {
                // Partial invoices table
                $rep->TextCol(0, 3,_("Prepayments invoiced to this order up to day:"));
                $rep->TextCol(0, 3,    str_pad('', 150, '_'));
                $rep->cols[2] -= 20;
                $rep->aligns[2] = 'right';
                $rep->NewLine(); $c = 0; $tot_pym=0;
                $rep->TextCol(0, 3,    str_pad('', 150, '_'));
                $rep->TextCol($c++, $c, _("Date"));
                $rep->TextCol($c++, $c,    _("Invoice reference"));
                $rep->TextCol($c++, $c,    _("Amount"));

                foreach ($prepayments as $invoice)
                {
                    if ($show_this_payment || ($invoice['reference'] != $myrow['reference']))
                    {
                        $rep->NewLine();
                        $c = 0; $tot_pym += $invoice['prep_amount'];
                        $rep->TextCol($c++, $c,    sql2date($invoice['tran_date']));
                        $rep->TextCol($c++, $c,    $invoice['reference']);
                        $rep->TextCol($c++, $c, number_format2($invoice['prep_amount'], $dec));
                    }
                    if ($invoice['reference']==$myrow['reference']) break;
                }
                $rep->TextCol(0, 3,    str_pad('', 150, '_'));
                $rep->NewLine();
                $rep->TextCol(1, 2,    _("Total payments:"));
                $rep->TextCol(2, 3,    number_format2($tot_pym, $dec));
            }


            $doctype = ST_SALESINVOICE;
            $rep->row = $summary_start_row;
            $rep->cols[2] += 20;
            $rep->cols[3] += 20;
            $rep->aligns[3] = 'left';

            $rep->TextCol(3, 6, _("Sub-total"), -2);
            $rep->TextCol(6, 7,    $DisplaySubTot, -2);
            $rep->NewLine();
            if ($myrow['ov_freight'] != 0.0)
            {
                   $DisplayFreight = number_format2($sign*$myrow["ov_freight"],$dec);
                $rep->TextCol(3, 6, _("Shipping"), -2);
                $rep->TextCol(6, 7,    $DisplayFreight, -2);
                $rep->NewLine();
            }    
            $tax_items = get_trans_tax_details(ST_SALESINVOICE, $row['trans_no']);
            $first = true;
            while ($tax_item = db_fetch($tax_items))
            {
                if ($tax_item['amount'] == 0)
                    continue;
                $DisplayTax = number_format2($sign*$tax_item['amount'], $dec);

                if ($SysPrefs->suppress_tax_rates() == 1)
                    $tax_type_name = $tax_item['tax_type_name'];
                else
                    $tax_type_name = $tax_item['tax_type_name']." (".$tax_item['rate']."%) ";

                if ($myrow['tax_included'])
                {
                    if ($SysPrefs->alternative_tax_include_on_docs() == 1)
                    {
                        if ($first)
                        {
                            $rep->TextCol(3, 6, _("Total Tax Excluded"), -2);
                            $rep->TextCol(6, 7,    number_format2($sign*$tax_item['net_amount'], $dec), -2);
                            $rep->NewLine();
                        }
                        $rep->TextCol(3, 6, $tax_type_name, -2);
                        $rep->TextCol(6, 7,    $DisplayTax, -2);
                        $first = false;
                    }
                    else
                        $rep->TextCol(3, 6, _("Included") . " " . $tax_type_name . _("Amount") . ": " . $DisplayTax, -2);
                }
                else
                {
                    $rep->TextCol(3, 6, $tax_type_name, -2);
                    $rep->TextCol(6, 7,    $DisplayTax, -2);
                }
                $rep->NewLine();
            }

            $rep->NewLine();

//-----------------------------------------------------------added to fetch and calculate total balance
                $accumulate = 0;                    
                $bal = get_open_balance($myrow['debtor_no'],today()); 
                $init[0] = $init[1] = 0.0;
            $init[0] = round2(abs($bal['charges']), $dec);
            $init[1] = round2(Abs($bal['credits']), $dec);
            $init[2] = round2($bal['Allocated'], $dec);
            $init[3] = $init[0] - $init[1];
            $accumulate += $init[3];
//-----------------------------------------------------------End added to fetch and calculate total balance

            $DisplayTotal = number_format2($sign*($myrow["ov_freight"] + $myrow["ov_gst"] +
                $myrow["ov_amount"]+$myrow["ov_freight_tax"]),$dec);
            $rep->Font('bold');
            if (!$myrow['prepaid']) $rep->Font('bold');
                $rep->TextCol(3, 6, $rep->formData['prepaid'] ? _("TOTAL ORDER VAT INCL.") : _("TOTAL INVOICE"), - 2);
            $rep->TextCol(6, 7, $DisplayTotal, -2);

//-------------------------------------------- added to display total balance            
            $rep->NewLine(); 
            $rep->TextCol(3, 6, $rep->formData['prepaid'] ? _("TOTAL ORDER VAT INCL.") : _("TOTAL BALANCE"), - 2); 
            $DisplayGrand = number_format2($init[3],$dec);
            $rep->TextCol(6, 7, $DisplayGrand, -2); 
//-----------------------------------------------------------End added to display total balance    

            if ($rep->formData['prepaid'])
            {
                $rep->NewLine();
                $rep->Font('bold');
                $rep->TextCol(3, 6, $rep->formData['prepaid']=='final' ? _("THIS INVOICE") : _("TOTAL INVOICE"), - 2);
                $rep->TextCol(6, 7, number_format2($myrow['prep_amount'], $dec), -2);
            }
            $words = price_in_words($rep->formData['prepaid'] ? $myrow['prep_amount'] : $myrow['Total']
                , array( 'type' => ST_SALESINVOICE, 'currency' => $myrow['curr_code']));
            if ($words != "")
            {
                $rep->NewLine(1);
                $rep->TextCol(1, 7, $myrow['curr_code'] . ": " . $words, - 2);
            }
            $rep->Font();
            if ($email == 1)
            {
                $rep->End($email);
            }
    }
    if ($email == 0)
        $rep->End();
}

I was pretty busy with work.

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: Print Customer Balance On Invoice

You will need to incorporate this fix as well.

Attachment has it done.

@joe: Want to include it in the core?

Post's attachments

Sales_Inv_Cum_Bal_patch.zip 8.5 kb, 14 downloads since 2018-01-17 

You don't have the permssions to download the attachments of this post.

apmuthu's Website

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,085

Re: Print Customer Balance On Invoice

No, I guess this should be an extension.

/Joe

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: Print Customer Balance On Invoice

@trafficpest: If the Balance is 0 (zero) then it is best suppressed.
@joe: Can it be an option in the existing one?.

Post's attachments

modded_rep107_zero_val.png 19 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.

apmuthu's Website

  • Alaa
  • Alaa
  • Senior Member
  • Offline
  • From: Jordan
  • Registered: 03/27/2015
  • Posts: 156

Re: Print Customer Balance On Invoice

seems like there is a bug with this code, when you print an invoice for a customer who have a credit note the balance w'll be incorrect.