Tagged: How to do price normalization
May 17, 2020 at 8:25 am #868
When creating a new itemized invoice, i.e.,
when an item is added, the price is displayed in localized format with thousands separator.
When saving the invoice, this creates an error “Incorrect decimal value: ‘6,068.83’ for column ‘unit_price’ at row 1” (log file enclosed).
The solution is to normalize the numbers before inserting them into the database.
Could you give a hint as to where this can be patched (I’m new to the structure of simple invoices).
- This topic was modified 1 year, 2 months ago by joncas.
Attachments:May 17, 2020 at 8:44 am #871
p.s.: Searching the files listed in the error log, it looks like the patch would be on in /modules/invoices/save.php
the “insert” case on line 71, one should use SiLocal::dbStd($_POST[“unit_price$i”]) instead of the unmodified $_POST[“unit_price$i”] (as the “edit” case does)
May 17, 2020 at 1:03 pm #873kdj3112Participant
- This reply was modified 1 year, 2 months ago by joncas.
Good to hear you found and fixed the problem,
If you have any further questions, feel free to ask.May 18, 2020 at 3:52 am #874
I think you addressed a symptom, not the problem. The existing code handles these values fine on my system. What server are you using (XAMPP, WAMP, Linux, etc.)? What version of PHP is running on that server?
Are you willing to share your phpinfo.php output? To do this, modify the phpinfo.php file that is in your primary directory and set the $secure variable to “true”. Then run it by entering your si URL with the file name. Ex: simepleinvoices/phpinfo.php if you use “simpleinvoices” to access your version of SI. Be sure to mask any personal info in this output before sharing it.
Note: Be sure to change the $secure variable back to “false” after getting this information.
May 18, 2020 at 4:52 am #876
- This reply was modified 1 year, 2 months ago by RRowley.
It may depend on the localization settings? This site is set to British English, so numbers display with a comma as thousands separator (see enclosed screenshot), and apparently this is passed on in the POST arguments because the sql error was complaining that this was being used in the insert command.
In your code the “edit” function already did use SiLocal::dbStd to filter that out, so it would seem logical that the insert should, too, no?
Enclosed also the phpinfo: this is a dedicated Linux server running Ubuntu 18.04
Attachments:June 4, 2020 at 5:59 pm #904
I changed my settings to en_GB in custom.config.php and in the INV PREF (SETTINGS) for the Invoice to en_GB also. This still works find. When I look at the php error log you sent in a zip file earlier, I see that the comma is being carried through to the database insert. When I look at this same command on my system (it is my production linux based system but running php 7.3) the comma that is displayed on my screen disappears from the $_POST array value for this field. So I thought maybe something in your PHP settings. However the PHPINFO information you provided, I couldn’t see any setting that would do this. Note that my development system is windows running xampp currently PHP 7.4 and it works fine there also.
Bottom line is I don’t know what is going on. My best suggestion at this point is to install the latest master_2019.2 version (updated on June 3) as a fresh install. That is, don’t load it over anything existing install. You should be able to rename your current directory, create a new directory named what your old one was and then unzip the downloaded version of master_2019.2 content into it. Then copy your config/custom.config.php file from the old install to the new install. If you have any special files such as extensions or in the custom directory then copy those over. Also, if you have your own invoice template, you need to copy that directory to the templates/invoices directory, and copy any special logo images in the templates/invoices/logos directory to the new directory.
Sorry I don’t have a more definitive answer. The removal of the comma is being handled behind the scenes so just recommending a scatter-gun approach to see if it changes thing.July 8, 2020 at 10:58 am #936
while creating new invoice i need to remove comma on price digit(7,000 -> 7000) then only it saves invoice otherwise throws following error
 => HY000
 => 1366
 => Incorrect decimal value: ‘7,000.00’ for column ‘unit_price’ at row 1
I am using following version
How to resolve it?July 8, 2020 at 11:11 pm #937
I’m glad someone else beside me has the same symptom!
I still believe that the posted price string needs to be normalized with SiLocal::dbStd($_POST[“unit_price$i”]) for processing amounts with 4 or more digits.July 13, 2020 at 2:28 am #939
Hi Joncas, have found the solution to this issue?, how to resolve it?July 13, 2020 at 2:41 am #940
What worked for me is what I wrote above (post #871) is to edit the file located at /modules/invoices/save.php
On line 71 (in my version),
(see enclosed image)
Attachments:July 13, 2020 at 6:55 am #942
Thank you so much Joncas now it working fine, checking remaining features like email, pdf ect, once again thanks to RRowley for his such a good effort and support.July 13, 2020 at 7:23 am #944
Great find. Just updated master_2019.2 version (v2019.2.28) on github to use the
SiLocal::dbStd(...)fix on both Itemized and Total style invoice unit prices. Also found and fixed an issue of Total style invoice handling of more than one tax item per line item used. The 2nd and on tax items we being ignored.
July 13, 2020 at 7:54 pm #953
- This reply was modified 1 year ago by RRowley.
Oh, good work,thank you so much i will try.July 13, 2020 at 8:29 pm #954
sir could you share link to download updated version?July 14, 2020 at 11:57 am #959
- You must be logged in to reply to this topic.