Thread Rating:
  • 1 Vote(s) - 5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
RecurringSalesOrdersProcess
08-02-2016, 05:56 AM,
#1
RecurringSalesOrdersProcess
Using latest WebERP, I was presented with this error today when trying to process recurring sales orders.

INFORMATION Message : The number of recurring orders to process is : 1

Recurring order 3 for MFPW - MFPW is being processed
Date calculated for the next recurrence was: 2016-07-31
Database Error 1292 : The order cannot be added because
Incorrect date value: '0000-00-00' for column 'confirmeddate' at row 1
Reply
08-02-2016, 06:20 AM,
#2
RE: RecurringSalesOrdersProcess
(08-02-2016, 05:56 AM)robkermit Wrote: Using latest WebERP, I was presented with this error today when trying to process recurring sales orders.

INFORMATION Message : The number of recurring orders to process is : 1

Recurring order 3 for MFPW - MFPW is being processed
Date calculated for the next recurrence was: 2016-07-31
Database Error 1292 : The order cannot be added because
Incorrect date value: '0000-00-00' for column 'confirmeddate' at row 1

This is because of a problem I have brought up dozens of times over many years. MySQL can be set to run in many different modes and one of them blocks zero date. Your installation is one of these. To correct the issue change the SQL at line 103 of RecurringSalesOrdersProcess.php to be

$HeaderSQL = "INSERT INTO salesorders (
orderno,
debtorno,
branchcode,
customerref,
comments,
orddate,
ordertype,
shipvia,
deliverto,
deladd1,
deladd2,
deladd3,
deladd4,
deladd5,
deladd6,
contactphone,
contactemail,
freightcost,
fromstkloc,
confirmeddate,
deliverydate )
VALUES (
'" . $OrderNo . "',
'" . $RecurrOrderRow['debtorno'] . "',
'" . $RecurrOrderRow['branchcode'] . "',
'". $RecurrOrderRow['customerref'] ."',
'". $RecurrOrderRow['comments'] ."',
'" . $DelDate . "',
'" . $RecurrOrderRow['ordertype'] . "',
'" . $RecurrOrderRow['shipvia'] ."',
'" . $RecurrOrderRow['deliverto'] . "',
'" . $RecurrOrderRow['deladd1'] . "',
'" . $RecurrOrderRow['deladd2'] . "',
'" . $RecurrOrderRow['deladd3'] . "',
'" . $RecurrOrderRow['deladd4'] . "',
'" . $RecurrOrderRow['deladd5'] . "',
'" . $RecurrOrderRow['deladd6'] . "',
'" . $RecurrOrderRow['contactphone'] . "',
'" . $RecurrOrderRow['contactemail'] . "',
'" . $RecurrOrderRow['freightcost'] ."',
'" . $RecurrOrderRow['fromstkloc'] ."',
CURRENT_DATE;
'" . $DelDate . "')";

This should cure the problem. Though you may find other issues in other scripts.

Thanks
Tim
Reply
08-02-2016, 07:38 AM, (This post was last modified: 08-02-2016, 07:42 AM by robkermit.)
#3
RE: RecurringSalesOrdersProcess
Hello.

The problem, from my understanding, is due to an update in MySQL. I updated my server from Ubuntu 14.04.x to 16.04.1 LTS which includes MySQL 5.7 (I believe).

I fixed some of it, I think, by actually modifying the tables and changing the 0000 dates to NULL.

Then I got a problem about the tpe value being too long.

And when I tried to print an invoice, it was just a blank page.

I made an export of the database before changing the tables and tried importing it into the "webERP Portable" on Windows which I could never get to work at all.

I guess my only solution to continue using WebERP is to set up Ubuntu 14.04 in a Virtual Machine and bring in WebERP and the database to it until the WebERP team corrects the issues with their software to work with modern MySQL/software. Or find another software solution.

I am thanking my lucky stars right now that I have paper copies of all the invoices generated from WebERP. Smile
Also I guess it should be worth mentioning that I looked in my.nf for NO_ZERO_IN_DATE to disable, and it is not there. New MySQL does not allow NO_ZERO_IN_DATE any longer. I sure hope the WebERP team is aware of this and is making plans to correct the problems in their software.
Reply
08-02-2016, 08:06 AM,
#4
RE: RecurringSalesOrdersProcess
I'm running 16.04.1 on my servers and don't see those issues but come to think of it I am running the mariadb-server package. I did notice they had messed around a lot with the settings between the LTS releases. 16.04.1 comes with PHP 7.0 by default. webERP needs some changes to work properly under 7.0. I posted about it a while ago if you search you should find the changes.

I still think the recurring order problem has to do with the sql mode NO_ZERO_DATE - https://dev.mysql.com/doc/refman/5.5/en/..._zero_date

Tim
Reply
08-02-2016, 08:52 AM,
#5
RE: RecurringSalesOrdersProcess
Thanks for your response.

I done the "do-system-upgrade" option in Ubuntu to update, so I'm not 100% sure exactly what changes were made. The last thing I want to do is go hacking the configuration files to make the software work when everything else on the server works fine.

The WebERP team sure has their work cut out of for them to get this software complaint with the new MySQL/PHP.

I will most likely put 14.04 in a VMWare for the purpose of running WebERP for the meantime and being able to process invoicing (1st day of the month is invoice day), but look forward to moving it back to my web server so others on my team will be able to access it. I hope the WebERP corrects the problems with this software because it is a good piece of software instead of abandoning it. Or another team picks up and forks the software for better support.
Reply
08-03-2016, 01:19 AM,
#6
RE: RecurringSalesOrdersProcess
There is not so much work that needs doing, all my servers run 16.04.1 and my code runs fine on it. I have tested the issues you have, and they work OK so the changes I have suggested in the past seem to cure any issues.

Tim
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)