convert figures to words

lammy · May 25, 2008

Hi,

This post is more about converting 'figures to words' than specifically to osCAffiliate

osCommerce with a range of added contributions is a perfect cart system. osCAffiliate is one among those...

osCAffiliate, though an excellent contribution lacks facilities to export the query result like 'billed affiliate'. a proper list exported as an excel file [.xls] or open office Calc file [.ods] [os/free too!] or as a text file[.csv] will help the web master/store owner to use it for mass updating, mass pay and use this data with integrated systems like mail merge to create pay checks in large numbers with little effort.

I have tested an additional [external] file like one below..

Named it affpay2.php, ran it AFTER affiliates were billed in admin/affiliate_payment.php

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

<?php

//require('includes/application_top.php');

//require(DIR_WS_CLASSES . 'currencies.php');

//$currencies = new currencies();

header("Content-type: application/octet-stream");

header("Content-Disposition: attachment; filename=affpay.ods");

header("Pragma: no-cache");

header("Expires: 0");

// line 6 - filename=affpay.ods - a blank open office spreadsheet file

// part from Register Globals easy contribution

// Register Globals MOD - http://www.magic-seo-url.com

if (version_compare(phpversion(), "4.1.0", "<") === true) {

$_GET &= $HTTP_GET_VARS;

$_POST &= $HTTP_POST_VARS;

$_SERVER &= $HTTP_SERVER_VARS;

$_FILES &= $HTTP_POST_FILES;

$_ENV &= $HTTP_ENV_VARS;

if (isset($HTTP_COOKIE_VARS)) $_COOKIE &= $HTTP_COOKIE_VARS;

}

if (!ini_get("register_globals")) {

extract($_GET, EXTR_SKIP);

extract($_POST, EXTR_SKIP);

extract($_COOKIE, EXTR_SKIP);

}

// eof part from Register Globals easy contribution

mysql_connect("localhost","root","testpass");

@mysql_select_db("oscommerce") or die("Unable to select database");

// start model from admin/affiliate_payment.php

// as an external file table names [from] in usual format

$sql = ("select aa.affiliate_email_address AS A_Mail, aa.affiliate_firstname AS F_Name, aa.affiliate_lastname AS L_Name, aa.affiliate_street_address AS Street, aa.affiliate_suburb AS Suburb, aa.affiliate_city AS City, aa.affiliate_postcode AS P_code, zo.zone_name AS State, p.affiliate_payment_total AS Pay_Aff

from affiliate_sales a, affiliate_payment p, affiliate_affiliate aa, zones zo

WHERE aa.affiliate_id = a.affiliate_id

and aa.affiliate_id = p.affiliate_id

and aa.affiliate_zone_id = zo.zone_id

and a.affiliate_billing_status = '1'

and p.affiliate_payment_status = '0'

and (To_days(NOW()) - TO_DAYS(a.affiliate_payment_date) < 2)

group by p.affiliate_id

HAVING p.affiliate_payment_total >= 200

order by aa.affiliate_id DESC ");

// bof export results

$export = mysql_query($sql);

$count = mysql_num_fields($export);

for ($i = 0; $i < $count; $i++) {

$header .= mysql_field_name($export, $i)."\t";

}

while($row = mysql_fetch_row($export)) {

$line = '';

foreach($row as $value) {

if ((!isset($value)) OR ($value == "")) {

$value = "\t";

} else {

$value = str_replace('"', '""', $value);

$value = '"' . $value . '"' . "\t";

}

$line .= $value;

}

$data .= trim($line)."\n";

}

$data = str_replace("\r", "", $data);

if ($data == "") {

$data = "\n(0) Records Found!\n";

}

print "$header\n$data";

// eof export results

?>

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

script export all affiliates with a.affiliate_billing_status='1', i.e. affiliate atready billed.

now the issue is that script export calculated result as figures, in the case of check writing it has to be expressed in words too like

345 = three hundred and fourty five

searching the net, i was able to find some good scripts ... like 1], 2], 3], 4].

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

======================================================

1]

The PHP function to convert number into words.

============================================

<?php

/*

** Function: convert_number

** Arguments: int

** Returns: string

** Description:

** Converts a given integer (in range [0..1T-1], inclusive) into

** alphabetical format ("one", "two", etc.).

*/

function convert_number($number)

{

if (($number < 0) || ($number > 999999999))

{

return "$number";

}

$Gn = floor($number / 1000000); /* Millions (giga) */

$number -= $Gn * 1000000;

$kn = floor($number / 1000); /* Thousands (kilo) */

$number -= $kn * 1000;

$Hn = floor($number / 100); /* Hundreds (hecto) */

$number -= $Hn * 100;

$Dn = floor($number / 10); /* Tens (deca) */

$n = $number % 10; /* Ones */

$res = "";

if ($Gn)

{

$res .= convert_number($Gn) . " Million";

}

if ($kn)

{

$res .= (empty($res) ? "" : " ") .

convert_number($kn) . " Thousand";

}

if ($Hn)

{

$res .= (empty($res) ? "" : " ") .

convert_number($Hn) . " Hundred";

}

$ones = array("", "One", "Two", "Three", "Four", "Five", "Six",

"Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen",

"Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eightteen",

"Nineteen");

$tens = array("", "", "Twenty", "Thirty", "Fourty", "Fifty", "Sixty",

"Seventy", "Eigthy", "Ninety");

if ($Dn || $n)

{

if (!empty($res))

{

$res .= " and ";

}

if ($Dn < 2)

{

$res .= $ones[$Dn * 10 + $n];

}

else

{

$res .= $tens[$Dn];

if ($n)

{

$res .= "-" . $ones[$n];

}

if (empty($res))

{

$res = "zero";

}

return $res;

}

?>

================================================

//Call function to convert the numbers

<?

$amt = 12345 ;

echo $amt." = ". convert_number($amt);

// produces Twelve Thousand Three Hundred and Fourty-Five

?>

Source: http://www.zend.com/code/codex.php?id=1540&single=1

======================================================

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

2]

js example

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

Lots of programming involves calculations with numbers. The problem comes when you want to display the result of your calculations and just printing the plain number isn't going to be good enough for what you want. You can of course format the number by adding commas and so forth but sometimes a number as a number isn't appropriate and you need the equivalent of the number in words. Converting a number into words isn't exactly the most straightforward of tasks but it can be done using javascript as the following number to word conversion form demonstrates.

If you want to be able to do these conversions on your site then the you will need a Javascript that can do the conversion for you. The simplest way to get one is to take a copy of mine. The first step to obtain it is to select the code from the text box below (there is a highlight all button beneath it to make this easier) and copy it into a file called toword.js.

// Convert numbers to words

// copyright 25th July 2006, by Stephen Chapman http://javascript.about.com

// permission to use this Javascript on your web page is granted

// provided that all of the code (including this copyright notice) is

// used exactly as shown (you can change the numbering system if you wish)

// American Numbering System

var th = ['','thousand','million', 'billion','trillion'];

// uncomment this line for English Number System

// var th = ['','thousand','million', 'milliard','billion'];

Next you link the script into the head of your page using the following code:

<script type="text/javascript" src="toword.js">

</script>

The final step is to call the script to perform the conversion to words for you. To get a number converted to words you just need to call the function passing it the number you want to convert and the corresponding words will be returned.

var words = toWords(num);

Note that this function can convert numbers as big as 999,999,999,999,999 with as many decimal places as you like into words. If you try to convert a number bigger than that it will return "too big". Numbers, commas, spaces, and a single period for the decimal point are all acceptable content for the number but it will return "not a number" if it contains anything else. If you want to convert negativ numbers of currency values to words you should remove those symbols from the number first and convert those to words separately.

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

3]

// un-comment used partsand use

# * NUMBERS TO WORDS by Milorad Ivovic (ivovic.net) - GPL

# $number is given as a string and can be any length,

# but only the last 9 digits will be processed.

# $format can be:

# l - lowercase (default)

# t - makeshift title case

# f - capitalise first character only */

# function n2w($number, $format='l') {

# if (is_numeric($number)) $original = $number;

# else return 0;

# while ($number) {

# // Trim numbers that are too large

# if (strlen($number)>=10) $number = substr($number,1);

# // Process millions

# if (strlen($number)==7) {

# $thisdigit=substr($number,0,1);

# if ($thisdigit) $words .= n2w_digit($thisdigit).' million';

# elseif (substr($original,-8,1) || substr($original,-9,1)) $words .= ' million';

# if (substr($number,1,1)) $words .= ', ';

# $number = substr($number,1);

# }

# // Process thousands

# if (strlen($number)==4) {

# $thisdigit=substr($number,0,1);

# if ($thisdigit) $words .= n2w_digit($thisdigit).' thousand';

# elseif (substr($original,-5,1) || substr($original,-6,1)) $words .= ' thousand';

# if (substr($number,1,1)) $words .= ', ';

# $number = substr($number,1);

# }

# // Process hundreds, hundreds of thousands, and hundreds of millions

# if (strlen($number)==3 || strlen($number)==6 || strlen($number)==9) {

# $thisdigit=substr($number,0,1);

# if ($thisdigit) $words .= n2w_digit($thisdigit).' hundred';

# if (substr($number,1,1) || substr($number,2,1)) $words .= ' and ';

# $number = substr($number,1);

# }

# // Process tens, tens of thousands, and tens of millions

# if (strlen($number)==2 || strlen($number)==5 || strlen($number)==8) {

# $thisdigit = substr($number,0,1);

# if ($thisdigit>1) { // not teens

# $words .= n2w_tens($thisdigit);

# // hyphenate unless next digit is zero

# if (substr($number,1,1)) $words .= '-';

# $number = substr($number,1);

# }

# elseif ($thisdigit==1) { // dealing with teens

# $words .= n2w_teens(substr($number,0,2));

# if (strlen($original)==5) $words .= ' thousand';

# if (strlen($original)==8) $words .= ' million';

# $number = substr($number,2); // trim by 2 digits

# }

# else $number = substr($number,1); // no tens, as in '405'

# }

# if (strlen($number)==1) {

# $thisdigit = substr($number,0,1);

# if (strlen($original)==1 || $thisdigit) $words .= n2w_digit($thisdigit);

# $number = ''; // kill the number, end the loop.

# }

# if ($words) { // output (returns, does not echo)

# if ($format=='f') return ucfirst($words);

# elseif ($format=='t') return ucwords($words);

# else return $words;

# }

# else return 0;

# }

# function n2w_tens($ten) {

# if ($ten=='2') return 'twenty';

# if ($ten=='3') return 'thirty';

# if ($ten=='4') return 'forty';

# if ($ten=='5') return 'fifty';

# if ($ten=='6') return 'sixty';

# if ($ten=='7') return 'seventy';

# if ($ten=='8') return 'eighty';

# if ($ten=='9') return 'ninety';

# }

# function n2w_teens($teen) {

# if ($teen=='10') return 'ten';

# if ($teen=='11') return 'eleven';

# if ($teen=='12') return 'twelve';

# if ($teen=='13') return 'thirteen';

# if ($teen=='14') return 'fourteen';

# if ($teen=='15') return 'fifteen';

# if ($teen=='16') return 'sixteen';

# if ($teen=='17') return 'seventeen';

# if ($teen=='18') return 'eighteen';

# if ($teen=='19') return 'nineteen';

# }

# function n2w_digit($digit) {

# if ($digit=='0') return 'zero';

# if ($digit=='1') return 'one';

# if ($digit=='2') return 'two';

# if ($digit=='3') return 'three';

# if ($digit=='4') return 'four';

# if ($digit=='5') return 'five';

# if ($digit=='6') return 'six';

# if ($digit=='7') return 'seven';

# if ($digit=='8') return 'eight';

# if ($digit=='9') return 'nine';

------------------------------------------------->

What it Does

It simply converts numbers into their respective word-forms. Usage is as follows:

n2w(string $number [, string $format])

The optional format parameter is a single character (either 'l', 't' or 'f') which tells the function whether you'd like your words in lower case (default), title case, or to capitalise just the first letter.

For example:

n2w('32562798','f');

returns:

Thirty-two million, five hundred and sixty-two thousand, seven hundred and ninety-eight

The function does not output anything, so you will probably want to 'echo' its results, unless you plan to process it further.

You can give it a number of any length but it will only use the last nine digits of it, which should really be plenty for every concievable purpose. It breaks down the number by treating it as a string of characters which is progressively trimmed. Although it ivolves looping through the string, it doesn't have to do that for every digit, as each loop triggers a bunch of the conditionals.

The function correctly skips zero values unless given as a single digit. For instance:

n2w('0');

returns:

zero

and:

n2w('2008');

returns:

two thousand and eight

===============================================

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

4]

http://pear.php.net/package-info.php?package=Numbers_Words

=================================================================

Now can anybody help to use these scripts with a database [select] result.

In this case, convert 'Pay_Aff' from the test query [line 40] from figures to words and export as an additional column.

I have tested scripts 1] and 2], working ok when used like 'n2w('2008');'

produced an error [function restated] when tried to use in sql query select part as

n2w(p.affiliate_payment_total) AS Pay_words

and

($Pay_word['Pay_words'] = $amt) ;

hope somebody could help with this

thanks

arietis · May 25, 2008

Now can anybody help to use these scripts with a database [select] result.
In this case, convert 'Pay_Aff' from the test query [line 40] from figures to words and export as an additional column.

I have tested scripts 1] and 2], working ok when used like 'n2w('2008');'

produced an error [function restated] when tried to use in sql query select part as

n2w(p.affiliate_payment_total) AS Pay_words

and

($Pay_word['Pay_words'] = $amt) ;

hope somebody could help with this

thanks

that n2w() function is a php function. what you're trying to do is use a php function in an sql select. it's not going to work. while php knows about mysql and can use it - mysql doesn't know anything about php and certainly cannot use a function defined in php.

you need to do this in one of two ways: 1) do your sql select as normal, get the value out of the database and then, in php, use the n2w($data['Pay_words']) function and output the results. 2) write an sql stored procedure.

my guess is option 1 would be easier. i haven't done too much work with stored procedures but translating and testing the php into mysql is probably more trouble than its worth.

lammy · May 27, 2008

that n2w() function is a php function. what you're trying to do is use a php function in an sql select. it's not going to work. while php knows about mysql and can use it - mysql doesn't know anything about php and certainly cannot use a function defined in php.

you need to do this in one of two ways: 1) do your sql select as normal, get the value out of the database and then, in php, use the n2w($data['Pay_words']) function and output the results. 2) write an sql stored procedure.

my guess is option 1 would be easier. i haven't done too much work with stored procedures but translating and testing the php into mysql is probably more trouble than its worth.

Thanks arietis for replying,

Actually i used this "n2w($data['Pay_words'])" model, but came up with a redeclare error, found a solution ....

---------

Fatal error: Cannot redeclare class NameOfYourFileHere in path\to\the\NameOfYourFileHere.php on line XX

This error sometimes appears after installing hacks or making certain other edits to your cart's files. Each page must process the code contained in the path/to/NameOfYourFileHere.php file once. If a page tries to process this code a second time, the error above appears.

Fortunately, this is an easy error to fix. First, you need to locate this line in your NameOfYourFileHere.php file.

CODE:

class NameOfYourFileHere {

Now add this code before the line you just found...

CODE:

if ( class_exists('NameOfYourFileHere') )

{

return;

}

-----------

may be this could be used with some optimization

hope somebody will outline a better solution

convert figures to words

Recommended Posts

lammy

arietis

lammy

Archived

Browse

Activity