Search options (Page 25 of 26)

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

I am using your auto complete in 2.3.25

Please suggest any changes to make it usable in 2.4 as well

Hello @apmuthu What is this ISN shown in the image? Is this something similar to batch number? I can't find it in the 2.3.25. Is it in 2.4

Regards.

@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();
}

?>

Its Perfect Now.
Thanks @Joe and @apmuthu
Regards.

Hello apmuthu,

Can you please elaborate it? Is there any documentation or support related to SMS integration? Which SMS Gateway Shall I use? Do I need to add any Module/Plugin in Frontaccounting?

Your Response is highly appreciated.

Regards.

Hell @joe could you check it further?

I found that my server is limiting all emails if the email address defined in the company setup is not from the domain hosted on the same server.

I did one modification in the line 47 of /reporting/includes/class.mail.inc and my problem got solved

I replaced the Line 47 with next two lines.

Normally in routine case a Direct Invoice is created first and GRN is Auto Created.

In case of auto GRN my suggestion is that it shall be voided automatically just like it is done in Sales Module where if Direct Invoice is voided the related Auto Delivery Note is automatically voided.

However if GRN was made manually and then it is converted into Supplier Invoice later then it shall be left for manual decision to delete GRN also with invoice or not.

This is my suggestion.