Free, Web Based Project Management with CreativePro Office

Forum Home | Invoicing

Multiple currency supprort

SebyM
2007-05-03 16:32:55		Here are my ideas on how to do it: - In the settings dialog, the user selects his/her main currency. This can be a listed currency or a custom currency (see previous post). - When adding a new invoice, the user selects the invoice currency (listed or custom) and sets the exchange rate to his main currency (manually or with help from a selector/widget). All items are written in using the invoice currency, and the invoice is saved. Every time the user looks at the invoice, he sees the invoice currency. Option #1: when the user gets a total (like Total Outstanding/Invoiced), each invoice is added to the total after being (not-visibly) transformed to the main currency (using the invoice exchange rate. Example (main currency is LEI): Invoice #1, USD 30,00 Invoice #2, LEI 120,00 Invoice #3, EUR 55,00 Total: LEI 377,30 Option #2: the user gets a total of each currency followed by a grand total in his main currency. Example (main currency is LEI): Invoice #1, USD 30,00 Invoice #2, LEI 120,00 Invoice #3, EUR 55,00 Invoice #4, USD 70,00 Invoice #5, LEI 70,05 Invoice #6, EUR 18,20 Totals: USD 100.05 + LEI 190.05 + EUR 73,2 GTotal: LEI: 676,238 This is a simple converter i wrote over the night. The exchange rate updates on the fly when running and it can be extended to support any number of currencies or any feed for an exchange rate: http://x.myl2.ro/sol/conv.php (ROL=old Romanian currency, 1RON(LEI)=10.000ROL). I will share this (source code) if you're interested. cheers
SebyM
2007-05-04 16:53:15		To add this option, the following are required:
- (default & custom currency) changes to the `users` table (or profile, where ever the currency is stored): `default_currency_id` should be a tinyint(2) signed value (this allows up to 99 currencies). if `default_currency_id` is 0 or higher, the user's default currency is the corresponding currency from a `currencies` table. if `default_currency_id` is '-1', then the user's default currency is his own custom currency (say 'LEI'), stored in a `custom_currency_sign`(varchar(3)) column.

- (custom currency on an invoice) i assume there are two tables, one with invoice headers (say `invoices`), one with invoice items (say `invoice_items`). the `invoices` table needs a column, `currency`(tinyint(2) signed default 0) that will store a number (-1 for custom or 0 or above for another currency), and a column `exchange_rate`(float(11,4) unsigned default 1) that will record the exchange rate between this invoice's currency and the user's default currency (example below).
- (summing up multiple invoices) when getting a total of several invoices with different currencies, this calculation would be made:
$total=0;
$sql="SELECT `invoices`.`invoice_id`, `invoices`.`exchange_rate`, Sum(`invoice_items`.`qty`*`invoice_items`.`itemprice`) AS `subtotal` FROM `invoices` Left Join `invoice_items` ON `invoice_items`.`invoice_id` = `invoices`.`invoice_id` GROUP BY `invoices`.`invoice_id`"
while ($row=mysql_fetch_array($sql)){
$total=$total + ($row['subtotal']*$row['exchange_rate']);
}

cheers
SebyM
2007-05-04 16:58:05		Here's another solutions where the subtotals and the exchange rate are multiplied in the sql query:

"SELECT `invoices`.`invoice_id`, `invoices`.`exchange_rate`, Sum(`invoice_items`.`qty`*`invoice_items`.`price`) AS `subtotal`,
Sum(`invoice_items`.`qty`*`invoice_items`.`price`*`invoices`.`exchange_rate`) AS `total` FROM `invoices` Left Join `invoice_items` ON `invoice_items`.`invoice_id` = `invoices`.`invoice_id` GROUP BY `invoices`.`invoice_id`"

remove "Sum(`invoice_items`.`qty`*`invoice_items`.`price`) AS `subtotal`," and the subtotals only the totals are returned.