CSV export 5 digits behind decimal point limit

  • Posts: 86
  • Thank you received: 4
  • Hikashop Business
8 years 6 months ago #238010

-- HikaShop version -- : 2.6.2
-- Joomla version -- : 3.5.1
-- PHP version -- : 5.4.45-0
-- Browser(s) name and version -- : Firefox 45.0.2

Afternoon,

when I use product export to csv function my product code numbers are cutoff and limited to 5 digits after the decimal point.

So the product code 828.052054321 will end up as 828.05205 in the export file, but a product code like 828.052154321A will be exported unchanged.

In the file administrator/components/com_hikashop/helpers/spreadsheet.php I did change the $floatValue from 5 to 6 and the product code is cutoff at 6 digits after the decimal point.

$floatValue = (float)hikashop_toFloat($value);
if($floatValue == (int)$floatValue)
$this->buffer .= (int)$value;
else
$this->buffer .= number_format($floatValue, 5, $this->decimal_separator, '');

So for some reason you do treat the product code default as a numeric value on export and not as a text value, I suppose this is true for other fields as well.

I do not know the logic behind this, but if I need to change the $floatValue after each update it will be annoying and in the end someone will forget it and end up with incorrect product codes on export and break the system completely if he decides to import it again.

Please let me know if I am doing something wrong, or that there is a permanent fix since we are working to a production version and all the small problems and inconsistencies are amounting to a big problem.

And yes I did check this with notepad++

Rene

P.S. the system is also preforming calculations on export because a product code like 6.319799
is rounded to 6.31980 so it's actually unusable..

Last edit: 8 years 6 months ago by EnerW.

Please Log in or Create an account to join the conversation.

  • Posts: 82863
  • Thank you received: 13372
  • MODERATOR
8 years 6 months ago #238023

Hi,

We actually don't treat the product code a a numeric value.
The spreadsheet helper doesn't know the type of each field and guesses it based on what is in it.
You're actually in the worst case scenario where the content of your field looks exactly like a float number with too many decimals and thus it's being rounded so that Excel can display it properly.

The spreadsheet helper was made to handle such case. So It would require a big rewrite to be able to specify the type of each field and then have it generate the CSV based on that to do a proper solution.
As a simple solution, I guess you could change the line:

if( is_numeric($value) && (preg_match('[^0-9]',$value) || ltrim($value, '0') === (string)$value) || '0' === (string)$value) {
which decides whether the content is a number or a text.
For example:
if( is_numeric($value) && !preg_match('[0-9]{3}\.[0-9]{9}',$value) && (preg_match('[^0-9]',$value) || ltrim($value, '0') === (string)$value) || '0' === (string)$value) {

Please Log in or Create an account to join the conversation.

  • Posts: 86
  • Thank you received: 4
  • Hikashop Business
8 years 6 months ago #238045

Morning Nicolas,

either there is more then one Nicolas at Hikashop, or you don't sleep at all :)

Your suggestion is appreciated, but {3} is not sufficient since we use 1 to 4 leading digits.

And we we also have something like ABC.1234567890123 and 10.10.207

I will try:

if( is_numeric($value) && !preg_match('[0-9]{1,4}\.[0-9]{1,9}',$value) && (preg_match('[^0-9]',$value) || ltrim($value, '0') === (string)$value) || '0' === (string)$value) {

If it does work, would it mean that I have to change it on each Hikashop update?

I know that not getting involved in how they would set up the part numbers in the past would get to me sometime, it's like Dumbledore from Harry Potter “But I think I could be safe with a nice toffee... Hum! Alas! Earwax.”

Rene

Please Log in or Create an account to join the conversation.

  • Posts: 86
  • Thank you received: 4
  • Hikashop Business
8 years 6 months ago #238070

Morning Nicolas,

nope no success, the $floatValue is already removing the digits, but the problem is much worse then I thought.

If I take the product code 828.5020 the product code will be mangled to 828.502 and if I set the $floatValue to more digits then it will be adding zero's to the product code.

Is there a reason for me to keep the float function and if not what should I remove to keep all things functioning?

I do not care about the setting of the decimal separator if it is a decimal point it will be fine.

At this point I am dead in the water I am afraid…

Please Log in or Create an account to join the conversation.

  • Posts: 86
  • Thank you received: 4
  • Hikashop Business
8 years 6 months ago #238139

Evening Nicolas

I did some cut and paste and it seems to working for what I will use it for

function writeNumber($row, $col, $value, $lastOne) {
if( $this->format == 1 ) {
$this->currLine = $row;
$len = strlen($value);
$this->buffer .= pack("sssss", 0x203, 14, $row, $col, 0x0);
$this->buffer .= pack("d", $value);
} else {
if( $this->currLine < $row )
$this->newLine();
$this->currLine = $row;
if( empty($value) ) {
$value = '""';
} elseif( strpos($value, '"') !== false ) {
$value = '"' . str_replace('"','""',$value) . '"';
} elseif( $this->forceQuote || (strpos($value, $this->separator) !== false) || (strpos($value, "\n") !== false) || (trim($value) != $value) ) {
$value = '"' . $value . '"';
}
$this->buffer .= $value;
if(!$lastOne)
$this->buffer .= $this->separator;
}
}

But I suppose that I will be replacing the administrator/components/com_hikashop/helpers/spreadsheet.php file on each update.

Rene

Please Log in or Create an account to join the conversation.

  • Posts: 82863
  • Thank you received: 13372
  • MODERATOR
8 years 6 months ago #238144

Hi,

You can also do it like that then:

if( is_numeric($value) && !is_float($value) && (preg_match('[^0-9]',$value) || ltrim($value, '0') === (string)$value) || '0' === (string)$value) {

But yes, that would require doing the change on each update, unless you create a class override of the spreadsheet helper in your backend template. There is a section of the developer documentation explaining how to create sujch class override:
www.hikashop.com/support/documentation/6...ntation.html#classes

Please Log in or Create an account to join the conversation.

  • Posts: 86
  • Thank you received: 4
  • Hikashop Business
8 years 6 months ago #238233

Afternoon Nicolas,

so if I understand you correctly I just copy my version of spreadsheet.php to:

administrator/templates/my_template/html/com_hikashop/administrator/helpers.spreadsheet.override.php

and change the class in the file to: class hikashopSpreadsheetHelperOverride

Please Log in or Create an account to join the conversation.

  • Posts: 82863
  • Thank you received: 13372
  • MODERATOR
8 years 6 months ago #238237

Hi,

It's administrator/templates/my_template/html/com_hikashop/administrator/helpers/spreadsheet.override.php

And yes, that's it. You can check that it's working by renaming the original file. If the export still works after that, it means that it's using the override file.

Please Log in or Create an account to join the conversation.

Time to create page: 0.086 seconds
Powered by Kunena Forum