• stefan
  • Senior Member
  • Offline
  • From: Bulgaria
  • Registered: 01/14/2015
  • Posts: 64

Topic: Not exactly bug -Reference Auto Increment when prefix size is bigger

I need to change the size of the prefix of the reflines. I have changed the size of DB cell and it is joined, recorded and displayed with the number correctly.
However, when the prefix is with more than 5 characters, the number after the prefix does not auto increase. I can't find where to change the prefix size for the auto increment purposes. It seems to be done in /includes/references.inc but I am not good enough to handle this my self.

I need the long prefixes for Sales Orders and Delivery Notes

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

Re: Not exactly bug -Reference Auto Increment when prefix size is bigger

Are you referring to FA 2.4RC1 Setup => Transaction References ?

If so, you might want to increase the prefix size from the default of 5 in the table:

ALTER TABLE `0_reflines` CHANGE `prefix` `prefix` CHAR(10) DEFAULT '' NOT NULL;

apmuthu's Website

  • stefan
  • Senior Member
  • Offline
  • From: Bulgaria
  • Registered: 01/14/2015
  • Posts: 64

Re: Not exactly bug -Reference Auto Increment when prefix size is bigger

Yes, it's 2.4.rc1 reflines table.

It is increased to 12. All refs are recorded and recalled correctly, but the auto increment of reflines with prefix longer than 5 characters is not working.

It looks like this and with the short prefixes works perfect, so i guess this is hard-coded somewhere else about the auto increment, and I can't find where.

Here is the DB table config:

`prefix` varchar(12) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',

  • stefan
  • Senior Member
  • Offline
  • From: Bulgaria
  • Registered: 01/14/2015
  • Posts: 64

Re: Not exactly bug -Reference Auto Increment when prefix size is bigger

This query in includes/references.inc returns 0 when prefix is longer... When prefix is short it returns number

// retrieve last ref number in the refline from original transaction table
$sql = "SELECT MAX(CAST(SUBSTR($ref_fld, ".(strlen($prefix)+1).",LENGTH($ref_fld)-".(strlen($postfix)+strlen($prefix)).") AS UNSIGNED))"
                ." FROM `$trans_table` tbl
                    LEFT JOIN ".TB_PREF."voided v ON tbl.`$tno_fld`=v.id AND v.type=$type"
                ." WHERE ISNULL(v.id)"
                .($type_fld ? " AND tbl.`$type_fld`=$type" : '')
                ." AND `$ref_fld` REGEXP ".db_escape('^'.preg_quote($prefix).'[0-9]*'.preg_quote($postfix).'$');

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

Re: Not exactly bug -Reference Auto Increment when prefix size is bigger

The prefix field is defined as CHAR in the original sql - why did you change it to VARCHAR?

Display the actual SQL statement after replacement of the variables and compare with the $ref_fld value. Refer Troubleshooting FA in the wiki.

apmuthu's Website

6 Reply by stefan (edited by stefan 03/26/2017 11:46:51 am)

  • stefan
  • Senior Member
  • Offline
  • From: Bulgaria
  • Registered: 01/14/2015
  • Posts: 64

Re: Not exactly bug -Reference Auto Increment when prefix size is bigger

"The prefix field is defined as CHAR in the original sql - why did you change it to VARCHAR?" - To avoid blocking problems with cyrilic alphabet characters.

In the actual statement the prefix is displayed 1:1

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

Re: Not exactly bug -Reference Auto Increment when prefix size is bigger

What is the sql statement after the variables get populated? Does the evaluation of the expressions match the desired sql output?

apmuthu's Website

  • stefan
  • Senior Member
  • Offline
  • From: Bulgaria
  • Registered: 01/14/2015
  • Posts: 64

Re: Not exactly bug -Reference Auto Increment when prefix size is bigger

Solution: replace 'strlen' with 'mb_strlen' and everything works fine...

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

Re: Not exactly bug -Reference Auto Increment when prefix size is bigger

@joe: can we add this in?

apmuthu's Website

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

Re: Not exactly bug -Reference Auto Increment when prefix size is bigger

@stefan: The changing of the prefix from CHAR to VARCHAR mitigates some cryllic alphabet issues.  Also when the number of characters exceeds 4 or so, the mb_strlen mitigates it.

How will it affect those without mb_strlen (any situations) and whether it can be incorporated wherever strlen is used in both FA 2.3 and 2.4? Any changes here will have to be carried through to everywhere it is used.

Please note that mb_strlen is used in both A 2.3 and 2.4 in the following files:

reporting/includes/class.graphic.inc
reporting/includes/Workbook.php

The usage in the last file above is:

    $strlen = function_exists('mb_strlen') ? mb_strlen($str, 'UTF-16LE') : (strlen($str) / 2);

whilst in the former it is:

    $width = mb_strlen($string, "UTF-8");

@itronics: Using VARCHAR results in slightly bigger memory usage, and slower processing (though probably both issues are hardly noticed).Using VARCHAR results in slightly bigger memory usage, and slower processing (though probably both issues are hardly noticed).

@itronics: Regarding mb_strlen function, it should be used indeed, but it is available only when mbstring php extension is enabled, so to avoid problems the conditional call should be used as it is done in Workbook.php.

References:

* What Every Programmer Absolutely, Positively Needs to Know About Encodings and Character Sets to Work With Text

* php.ini mbstring overload

apmuthu's Website