webERP Forum
Purchases from Suppliers - No Data - SOLVED - Printable Version

+- webERP Forum (http://www.weberp.org/forum)
+-- Forum: webERP Discussion (/forumdisplay.php?fid=1)
+--- Forum: Problems / Bugs? (/forumdisplay.php?fid=8)
+--- Thread: Purchases from Suppliers - No Data - SOLVED (/showthread.php?tid=7943)



Purchases from Suppliers - No Data - SOLVED - VortecCPI - 11-29-2017 09:48 PM

Main Menu > Purchases > Purchases from Suppliers produces SQL with this:

WHERE supptrans.trandate>='11/29/2016' AND supptrans.trandate<='11/29/2017'

Apparently the ConvertSQLDate function is not working here so I am looking into it now.

I will post my findings and fix when I figure it out...
The fix was very simple...

Change this:

64: $PeriodFrom = ConvertSQLDate($_POST['PeriodFrom']);
65: $PeriodTo = ConvertSQLDate($_POST['PeriodTo']);

To this:

64: $PeriodFrom = FormatDateForSQL($_POST['PeriodFrom']);
65: $PeriodTo = FormatDateForSQL($_POST['PeriodTo']);

Can one of you check my work and merge this into the trunk?


RE: Purchases from Suppliers - Date Format Issue - afcouling - 11-29-2017 10:01 PM

I can't reproduce this error, but I am using the date format dd/mm/yyyy.
I will test with your mods later today.

Andy.


RE: Purchases from Suppliers - No Data - SOLVED - VortecCPI - 11-29-2017 10:37 PM

Thank you Andy.

I have to assume my USA-based locale and date format is causing the issue.


RE: Purchases from Suppliers - No Data - SOLVED - falkoner - 11-29-2017 11:26 PM

Actually looking at it Paul is correct and the code is wrong, the question is not so much why his doesn't work but why ours with d/m/Y format does work?

Long answer: Looking at the code the answer to the question is in the way the ConvertSQLDate() works. It is meant to be fed Y/m/d format date and if the default format is d/m/Y it reverses the order and gives a Y/m/d format date, but if the default format is m/d/Y it also creates a Y/m/d format date. However if the function is fed an incorrect date (in the format d/m/Y for Andy and m/d/Y for Paul), as happens in this script, it still works for Andy as it simply reverses it to an SQL format, but for Paul it creates an incorrect format.

Short answer: Paul's fix is correct, and if it is applied everyone's code will still work.

Tim


RE: Purchases from Suppliers - No Data - SOLVED - VortecCPI - 11-29-2017 11:54 PM

Thank you for having a look at this Tim. I always feel much better when a webERP expert verifies my thoughts and proposed solutions.

Especially when it is you!


RE: Purchases from Suppliers - No Data - SOLVED - phil - 12-02-2017 05:37 PM

I've applied your fix - many thanks Paul


RE: Purchases from Suppliers - No Data - SOLVED - VortecCPI - 12-03-2017 12:19 AM

As always thank you both for your help on this project.