ID:               47418
 Updated by:       paj...@php.net
 Reported By:      cu19 at gmx dot de
 Status:           Feedback
 Bug Type:         *Math Functions
 Operating System: Win7
 PHP Version:      5.3.1
 New Comment:

It could be an encoding issue then (before it gets formatted). Can you
zip the script and the excel file you use and post a link to it here? Or
drop me a mail.


Previous Comments:
------------------------------------------------------------------------

[2010-02-12 14:15:30] tdietsche at comcast dot net

I am not sure what you mean by "isolate the value which causes problems
and pass it directly to number_format". If I just assign 19 to a
variable (either as a string or as a number) and do this, it works fine.
It is only when it comes from a result row value of a number-formatted
excel spreadsheet cell that it shows the problem, for me.

You could try a much simpler spreadsheet but I gave all the details of
the one I am using that shows the problem, in case for some reason the
spreadsheet details are related somehow (probably not though).

I don't have time to install a new version of php right now, maybe
later. I have already spent more time on this than I can afford.

One other note, I see in playing around I changed some variable names
in one function, it should really read:

function FmtAmt($amt) {
  // another workaround for the total
  if ((string)$amt == '18.:') $amt = '19';  
  $cred = '    ';
  if ($amt < 0) {
    $amt  = 0 - $amt;
    $cred = 'CR';
  }
  // uncomment this and comment out next line to see bug
  // $ret = number_format($amt, 2) . $cred;  
  $ret = stupid_php($amt) . $cred;
  return $ret;
}

------------------------------------------------------------------------

[2010-02-12 14:07:58] paj...@php.net

Can you try to isolate the value which causes problems and pass it
directly to number_format? That will be easier to debug and fix.

Can you aslo try using 5.3.2RC2 (VC9 builds if possible, via CLI or
using apachelounge.com's apache).

------------------------------------------------------------------------

[2010-02-12 13:32:28] tdietsche at comcast dot net

In case it might help, here is my php config (on WinXP SP3):


  PHP Version 5.3.1


System  Windows NT DIY2004 5.1 build 2600 (Windows XP Professional
Service Pack 3) i586
Build Date      Nov 19 2009 10:16:45
Compiler        MSVC6 (Visual C++ 6.0)
Architecture    x86
Configure Command       cscript /nologo configure.js
"--enable-snapshot-build" "--disable-isapi" "--enable-debug-pack"
"--disable-isapi"
"--with-pdo-oci=D:\php-sdk\oracle\instantclient10\sdk,shared"
"--with-oci8=D:\php-sdk\oracle\instantclient10\sdk,shared"
"--with-oci8-11g=D:\php-sdk\oracle\instantclient11\sdk,shared"
"--enable-object-out-dir=../obj/"
Server API      Apache 2.0 Handler
Virtual Directory Support       enabled
Configuration File (php.ini) Path       C:\WINDOWS
Loaded Configuration File       C:\PHP\php.ini
Scan this dir for additional .ini files         (none)
Additional .ini files parsed    (none)
PHP API         20090626
PHP Extension   20090626
Zend Extension  220090626
Zend Extension Build    API220090626,TS,VC6
PHP Extension Build     API20090626,TS,VC6
Debug Build     no
Thread Safety   enabled
Zend Memory Manager     enabled
Zend Multibyte Support  disabled
IPv6 Support    enabled
Registered PHP Streams  php, file, glob, data, http, ftp, zip,
compress.zlib, compress.bzip2, https, ftps, phar
Registered Stream Socket Transports     tcp, udp, ssl, sslv3, sslv2, tls
Registered Stream Filters       convert.iconv.*, string.rot13,
string.toupper, string.tolower, string.strip_tags, convert.*,
consumed,
dechunk, zlib.*, bzip2.*

------------------------------------------------------------------------

[2010-02-12 13:19:29] tdietsche at comcast dot net

/* 
set up an Excel 97-2003 spreadsheet with a worksheet 'Nov'
and the row headers below on row 3
then add a row 14 with "John Smith" in Tenant, "11" in Lot, and "19" in
Late Fee.
Late fee should be number format with 2 decimals and comma thousands
separator format.
Create an odbc system DSN called "rent_2009" linked to it.
Then run this script against it, I have stripped it down but I think it
will show the problem.
note this will NOT show the problem if you just supply variable values
directly, it has to come
from excel and has something to do with the (string) casting of values
that excel thinks are numbers.
somehow the float and string are getting goofed up. Even when I fixed
the value, the total it was
being added to still had the problem until I hardwired a second test on
"18.:0" to fix that. Jeez.

(row headers):
Tenant
Lot
Prev Bal
Rent
LP Gas
Taxes
Garbage
Elec CR
Mowing
Late Fee
Misc
Deposit
New Bal
Check
Cash
Mail
Bal Fwd
Last Mon
This Mon
Change
Factor
Gallons
Rate/Gal
LP Amt
NOTES
Misc Desc

*/
$result    = '';
$month_total = 0;
$totals  = array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);

function FmtFieldAmt($tot_num, $field_name) {
  global $result, $month_total, $totals;
  $res = odbc_result($result, $field_name);
  $fld_amt = GetDec($res);
  $fmt_amt = FmtAmt($fld_amt);
  $month_total      += $fmt_amt;
  $totals[$tot_num] += $fmt_amt;
  return $fmt_amt;
}
function GetDec($value) {
  if ($value == null || (string)$value == '')
    return 0;
  else
    return $value;
}
function FmtAmt($amt) {
  if ((string)$amt == '18.:') $amt = '19';  // another workaround for
the total
  $cred = '&nbsp;&nbsp;&nbsp;&nbsp;';
  if ($amt < 0) {
    $amt  = 0 - $amt;
    $cred = 'CR';
  }
  // $amt = number_format($amt, 2) . $cred;  // uncomment this comment
out next line to see bug
  $ret = stupid_php($amt) . $cred;
  return $ret;
}
// this is my workaround which works if the value doesn't already have
the stupid colon in it
function stupid_php($val) {
  $len = strlen($val);
  if ($len == 0)
    return $val;
  if (strpos($val, '.') == false)
    return $val . '.00';
  // for 123.4  len=5 pos=3 return 123.45
  if (strpos($val, '.') == $len - 2)
    return $val . '0';
  if (strpos($val, '.') < $len - 2)
    return round($val, 2);
  return $val;
}
function jsi_db_error($query, $errno, $error) {
  exit ('**Database Error (' .$errno. ') ' .$error. ' Query=[' .$query.
'] **');
}

$db_name = 'rent_2009';
$link  = odbc_connect($db_name, 'user', 'pw') 
          or jsi_db_error('Connect', odbc_errormsg(), odbc_error() );
$query = 'SELECT * FROM [Nov$A3:Z43] WHERE Lot = 11';
$result = odbc_exec($link, $query) or jsi_db_error($query,
odbc_errormsg(), odbc_error());
echo FmtFieldAmt(6, 'Late Fee');
echo FmtAmt($totals[6]);

------------------------------------------------------------------------

[2010-02-12 11:55:25] cu19 at gmx dot de

That's nothing different, but exactly the same problem as I described
from the beginning.

This is the function that I currently use as a workaround. It works
fine, but I would prefer php doing this itself...

function my_number_format ($number, $decimals=0, $dec_point=',',
$thousands_sep='.') {
        $string = number_format($number, $decimals, $dec_point,
$thousands_sep);
        $pos = strlen($string) - 1;
        $increase = false;
        while ($pos >= 0) {
                if (($increase) and (is_numeric($string{$pos}))) {
                        $string{$pos} = chr(ord($string{$pos}) + 1);
                        $increase = false;
                }
                if ($string{$pos} == ':') {
                        $string{$pos} = '0';
                        $increase = true;
                }
                $pos--;
        }
        if ($increase)
                $string = '1' . $string;
        return $string;
}

------------------------------------------------------------------------

The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
    http://bugs.php.net/47418

-- 
Edit this bug report at http://bugs.php.net/?id=47418&edit=1

Reply via email to