Need Salesman wise report (Page 1 of 2)

Thanks for solved the report file. The report is working well. But show massage: "Unknown report parameter type:ZONES".  How can I stop this unwanted massage?

to ignore ZONES issue, i think one can replace the ZONES by AREAS in the Reporting_main.php

you may try it...

No, there are no problems using existing AREAS, so why change this.

We, the developers, do not normally have resources to help with changing the core.

@dz

Your call to ZONES, you hace a missing underscore in front of the gettext.
I guess that this produces your problems.

Joe

hello @dz

I am trying to implement this in 2.3.25 but it is giving error.

Is it compatible with 2.3.25?

@apmuthu, I have successfully Implemented the code suggested by @dz. First of all I shall explain the reason to induce Zones instead of Sales Areas. The call to sales_areas_list function doesn't return a combo with 'No Sales Area Filter' value that is required for reporting. So instead of changing Core Function (that may affect other calls to function) he added a function zone_list.

After removing two bugs in rep102.php The below code is suggested along with the changes suggested by @dz in
1. reports_main.php
2. ui_lists.inc
3. reports_classes.inc

********rep102.php******
<?php
/**********************************************************************
    Copyright (C) Boxygen, 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>.
    Whole Page Modified by Faisal Reference https://frontaccounting.com/punbb/viewtopic.php?id=4832
***********************************************************************/
$page_security = 'SA_CUSTPAYMREP';
// ----------------------------------------------------------------
// $ Revision:    2.0 $
// Creator:    Joe Hunt
// date_:    2005-05-19
// Title:    Aged Customer Balances
// ----------------------------------------------------------------
$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 . "/gl/includes/gl_db.inc");
//----------------------------------------------------------------------------------------------------
print_aged_customer_analysis();
function get_invoices($customer_id, $to, $all=true)
{
    $todate = date2sql($to);
    $PastDueDays1 = get_company_pref('past_due_days');
    $PastDueDays2 = 2 * $PastDueDays1;
    // Revomed allocated from sql
    if ($all)
        $value = "(".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)";
    else       
        $value = "(".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 + ".TB_PREF."debtor_trans.alloc)";
    $due = "IF (".TB_PREF."debtor_trans.type=".ST_SALESINVOICE.",".TB_PREF."debtor_trans.due_date,".TB_PREF."debtor_trans.tran_date)";
    $sql = "SELECT ".TB_PREF."debtor_trans.type, ".TB_PREF."debtor_trans.reference,
        ".TB_PREF."debtor_trans.tran_date,
        $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
        FROM ".TB_PREF."debtors_master,
            ".TB_PREF."debtor_trans
        WHERE ".TB_PREF."debtor_trans.type <> ".ST_CUSTDELIVERY."
            AND ".TB_PREF."debtors_master.debtor_no = ".TB_PREF."debtor_trans.debtor_no
            AND ".TB_PREF."debtor_trans.debtor_no = $customer_id
            AND ".TB_PREF."debtor_trans.tran_date <= '$todate'
            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 ) > ".FLOAT_COMP_DELTA." ";
    if (!$all)
        $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 - ".TB_PREF."debtor_trans.alloc) > ".FLOAT_COMP_DELTA." "; 
    $sql .= "ORDER BY ".TB_PREF."debtor_trans.tran_date";
    return db_query($sql, "The customer details could not be retrieved");
}
//----------------------------------------------------------------------------------------------------
function print_aged_customer_analysis()
{
    global $path_to_root, $systypes_array;
        $to = $_POST['PARAM_0'];
        $fromcust = $_POST['PARAM_1'];
            $area = $_POST['PARAM_2'];
            $folk = $_POST['PARAM_3'];                   
        $currency = $_POST['PARAM_4'];
        $show_all = $_POST['PARAM_5'];
    $summaryOnly = $_POST['PARAM_6'];
        $no_zeros = $_POST['PARAM_7'];
        $graphics = $_POST['PARAM_8'];
        $comments = $_POST['PARAM_9'];
    $orientation = $_POST['PARAM_10'];
    $destination = $_POST['PARAM_11'];
    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 ($fromcust == ALL_TEXT)
        $from = _('All');
    else
        $from = get_customer_name($fromcust);
        $dec = user_price_dec();
    if ($summaryOnly == 1)
        $summary = _('Summary Only');
    else
        $summary = _('Detailed Report');
    if ($currency == ALL_TEXT)
    {
        $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');
    if ($fromcust == ALL_TEXT)
        $from = _('All');
    else
        $from = get_customer_name($fromcust);
        $dec = user_price_dec();
       
            if ($area == ALL_NUMERIC)
        $area = 0;
       
    if ($area == 0)
        $sarea = _('All Areas');
    else
        $sarea = get_area_name($area);
       
    if ($folk == ALL_NUMERIC)
        $folk = 0;
    if ($folk == 0)
        $salesfolk = _('All Sales Man');
    else
        $salesfolk = get_salesman_name($folk);
       
    $PastDueDays1 = get_company_pref('past_due_days');
    $PastDueDays2 = 2 * $PastDueDays1;
    $nowdue = "1-" . $PastDueDays1 . " " . _('Days');
    $pastdue1 = $PastDueDays1 + 1 . "-" . $PastDueDays2 . " " . _('Days');
    $pastdue2 = _('Over') . " " . $PastDueDays2 . " " . _('Days');
    $cols = array(0, 100, 130, 190,    250, 320, 385, 450,    515);
    $headers = array(_('Customer'),    '',    '',    _('Current'), $nowdue, $pastdue1, $pastdue2,
        _('Total Balance'));
    $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' => _('Customer'),    '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' => ''),
                    7 => array('text' => _('Zone'),         'from' => $sarea,         'to' => ''),                       
                    8 => array('text' => _('Sales Man'),         'from' => $salesfolk,     'to' => ''),               
                );
    if ($convert)
        $headers[2] = _('Currency');
    $rep = new FrontReport(_('Aged Customer Analysis'), "AgedCustomerAnalysis", user_pagesize(), 9, $orientation);
    if ($orientation == 'L')
        recalculate_cols($cols);
    $rep->Font();
    $rep->Info($params, $cols, $headers, $aligns);
    $rep->NewPage();
    $total = array(0,0,0,0, 0);
    $sql = "SELECT ".TB_PREF."debtors_master.debtor_no,
            ".TB_PREF."debtors_master.name,
            ".TB_PREF."debtors_master.curr_code 
        FROM ".TB_PREF."debtors_master
        INNER JOIN ".TB_PREF."cust_branch
            ON ".TB_PREF."debtors_master.debtor_no=".TB_PREF."cust_branch.debtor_no
        INNER JOIN ".TB_PREF."areas
            ON ".TB_PREF."cust_branch.area = ".TB_PREF."areas.area_code           
        INNER JOIN ".TB_PREF."salesman
            ON ".TB_PREF."cust_branch.salesman=".TB_PREF."salesman.salesman_code";
        if ($fromcust != ALL_TEXT )
            {
               // if ($area != 0 || $folk != 0) continue;
                $sql .= " WHERE ".TB_PREF."debtors_master.debtor_no=".db_escape($fromcust);
            }
   
        elseif ($area != 0)
            {
                if ($folk != 0)
                    $sql .= " WHERE ".TB_PREF."salesman.salesman_code=".db_escape($folk)."
                        AND ".TB_PREF."areas.area_code=".db_escape($area);
                else
                    $sql .= " WHERE ".TB_PREF."areas.area_code=".db_escape($area);
            }           
        elseif ($folk != 0 )
            {
                $sql .= " WHERE ".TB_PREF."salesman.salesman_code=".db_escape($folk);
            }           
       
    $sql .= " ORDER BY name";   
    $result = db_query($sql, "The customers could not be retrieved");
   

    while ($myrow=db_fetch($result))
    {
        if (!$convert && $currency != $myrow['curr_code'])
            continue;
        if ($convert) $rate = get_exchange_rate_from_home_currency($myrow['curr_code'], $to);
        else $rate = 1.0;
        $custrec = get_customer_details($myrow['debtor_no'], $to, $show_all);
        if (!$custrec)
            continue;
        $custrec['Balance'] *= $rate;
        $custrec['Due'] *= $rate;
        $custrec['Overdue1'] *= $rate;
        $custrec['Overdue2'] *= $rate;
        $str = array($custrec["Balance"] - $custrec["Due"],
            $custrec["Due"]-$custrec["Overdue1"],
            $custrec["Overdue1"]-$custrec["Overdue2"],
            $custrec["Overdue2"],
            $custrec["Balance"]);
        if ($no_zeros && floatcmp(array_sum($str), 0) == 0) continue;
        $rep->fontSize += 2;
        $rep->TextCol(0, 2, $myrow['name']);
        if ($convert) $rep->TextCol(2, 3,    $myrow['curr_code']);
        $rep->fontSize -= 2;
        $total[0] += ($custrec["Balance"] - $custrec["Due"]);
        $total[1] += ($custrec["Due"]-$custrec["Overdue1"]);
        $total[2] += ($custrec["Overdue1"]-$custrec["Overdue2"]);
        $total[3] += $custrec["Overdue2"];
        $total[4] += $custrec["Balance"];
        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['debtor_no'], $to, $show_all);
            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, $systypes_array[$trans['type']], -2);
                $rep->TextCol(1, 2,    $trans['reference'], -2);
                $rep->DateCol(2, 3, $trans['tran_date'], true, -2);
                if ($trans['type'] == ST_CUSTCREDIT || $trans['type'] == ST_CUSTPAYMENT || $trans['type'] == ST_BANKDEPOSIT)
                {
                    $trans['Balance'] *= -1;
                    $trans['Due'] *= -1;
                    $trans['Overdue1'] *= -1;
                    $trans['Overdue2'] *= -1;
                }
                foreach ($trans as $i => $value)
                    $trans[$i] *= $rate;
                $str = array($trans["Balance"] - $trans["Due"],
                    $trans["Due"]-$trans["Overdue1"],
                    $trans["Overdue1"]-$trans["Overdue2"],
                    $trans["Overdue2"],
                    $trans["Balance"]);
                for ($i = 0; $i < count($str); $i++)
                    $rep->AmountCol($i + 3, $i + 4, $str[$i], $dec);
            }
            $rep->Line($rep->row - 8);
            $rep->NewLine(2);
        }
    }
    if ($summaryOnly)
    {
        $rep->Line($rep->row  + 4);
        $rep->NewLine();
    }
    $rep->fontSize += 2;
    $rep->TextCol(0, 3, _('Grand Total'));
    $rep->fontSize -= 2;
    for ($i = 0; $i < count($total); $i++)
    {
        $rep->AmountCol($i + 3, $i + 4, $total[$i], $dec);
        if ($graphics && $i < count($total) - 1)
        {
            $pg->y[$i] = abs($total[$i]);
        }
    }
       $rep->Line($rep->row - 8);
       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->skin      = $graph_skin;
        $pg->built_in  = false;
        $pg->latin_notation = ($decseps[$_SESSION["wa_current_user"]->prefs->dec_sep()] != ".");
        $filename = company_path(). "/pdf_files/". uniqid("").".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)
            $rep->NewPage();
        $rep->AddImage($filename, $x, $rep->row - $h, $w, $h);
    }
    $rep->NewLine();
    $rep->End();
}

?>

You are right however for this report only zone_list function is used.

Is there any log where I can keep checking for any modifications in Master Files or I can Subscribe to changes notifications?

In the file includes/ui/ui_lists.inc, the function sales_areas_list():

function sales_areas_list($name, $selected_id=null)
{
    $sql = "SELECT area_code, description, inactive FROM ".TB_PREF."areas";
    return combo_input($name, $selected_id, $sql, 'area_code', 'description', array());
}

should be made to match it's peers (function sales_persons_list) as:

function sales_areas_list($name, $selected_id=null, $spec_opt=false)
{
    $opt = array();
    if ($spec_opt !== false)
        $opt = array('order'=>array('description'), 'spec_option' => $spec_opt, 'spec_id' => ALL_NUMERIC);
    $sql = "SELECT area_code, description, inactive FROM ".TB_PREF."areas";
    return combo_input($name, $selected_id, $sql, 'area_code', 'description', $opt);
}

and the lines in reporting/includes/reports_classes.inc:

                case 'AREAS':
                    return sales_areas_list($name);

should be changed to:

                case 'AREAS':
                    return sales_areas_list($name, null, _("No Sales Area Filter"));

and the string added to the empty.po for translations.

The "AREAS" is used only in the rep103.php as of now.
The "SALESTYPES" is used only in rep104.php and will need similar changes.

Your File Giving Following Errors

DATABASE ERROR : The customers could not be retrieved
error code : 1054
error message : Unknown column 'debtors_master.debtor_no' in 'field list'
sql that failed was : SELECT debtors_master.debtor_no, debtors_master.name, debtors_master.curr_code FROM 1_debtors_master INNER JOIN 1_cust_branch ON debtors_master.debtor_no=cust_branch.debtor_no INNER JOIN 1_areas ON cust_branch.area = areas.area_code INNER JOIN 1_salesman ON cust_branch.salesman=salesman.salesman_code WHERE salesman.salesman_code='1' ORDER BY name

@apmuthu there is a logical bug in this modification. It multiplies the display on report times the number of branches. If a Customer has 2 Branches then its information will be displayed 2 times and if it has 3 branches then its info will be displayed 3 times. on the same report

http://prntscr.com/dyra0n

You can check the screen shot

The said error occurs because you have the same salesman in 2 or more branches and the debtors_master does not specify a particular branch since it should be taken from the transaction itself....

Thanks @apmuthu it worked.

It shall be GROUP BY ".TB_PREF."debtors_master.debtor_no

Need Salesman wise report, please guide me step by step