webERP Forum

Full Version: Unexpected changes in prices end date
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Hi all:

I found some ghosts in the machine, but I'm not able to shut them down.

FACTS:
1) webERP assumes that prices without end date are those with enddate = '0000-00-00'. All scripts check that enddate = '0000-00-00'.
2) webERP updated to 4.10.1
3) We use the standard definition for prices table:
Code:
    CREATE TABLE `prices` (
      `stockid` varchar(20) NOT NULL DEFAULT '',
      `typeabbrev` char(2) NOT NULL DEFAULT '',
      `currabrev` char(3) NOT NULL DEFAULT '',
      `debtorno` varchar(10) NOT NULL DEFAULT '',
      `price` decimal(20,4) NOT NULL DEFAULT '0.0000',
      `branchcode` varchar(10) NOT NULL DEFAULT '',
      `startdate` date NOT NULL DEFAULT '0000-00-00',
      `enddate` date NOT NULL DEFAULT '9999-12-31',
      PRIMARY KEY (`stockid`,`typeabbrev`,`currabrev`,`debtorno`,`branchcode`,`startdate`,`enddate`),
      KEY `CurrAbrev` (`currabrev`),
      KEY `DebtorNo` (`debtorno`),
      KEY `StockID` (`stockid`),
      KEY `TypeAbbrev` (`typeabbrev`),
      CONSTRAINT `prices_ibfk_1` FOREIGN KEY (`stockid`) REFERENCES `stockmaster` (`stockid`),
      CONSTRAINT `prices_ibfk_2` FOREIGN KEY (`currabrev`) REFERENCES `currencies` (`currabrev`),
      CONSTRAINT `prices_ibfk_3` FOREIGN KEY (`typeabbrev`) REFERENCES `salestypes` (`typeabbrev`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

GHOST ACTION:
All end prices set to '0000-00-00' are changed automatically with no human intervention to '2050-12-31'. It breaks the webERP scripts of sequence of prices, etc.

Then via PHPmyAdmin we run:
UPDATE prices
SET enddate = '0000-00-00'
WHERE enddate = '2050-12-31'

After few minutes, all prices whose enddate was 0000-00-00 are changed (by some unknown process) to enddate = '2050-12-31'.

Again to PHPmyadmin and back to the unknown change (ad infinitum).

During these minutes no price script was executed.
webERP scripts set date = '2050-12-31' in 2 lines in MRP module. We do not run this module. Anyway, MRP does not interact with prices table.

webERP running under
Apache version 2.2.23
PHP version 5.2.17
MySQL version 5.5.23-55

What is going on? Anyone faced the same kind of ghosts?
Found the ghost!

It happened from time to time, and it never happened to a backup database. So, checked the differences and found out that weberpCART Joomla! module is updating the prices table in weberp.

Guilty lines are:
1392 to 1394 in models/cartcatalog.php

Code:
                $query = "UPDATE  " . $weberp['database'] . ".prices SET enddate = '2050-12-31' WHERE enddate = '0000-00-00'";
                $errorMessage = 'Error Code-SM' .  __LINE__  . ' Unable to update end dates in prices file. ';
                $this->_data =& modwebERPHelper::getInsertUpdate($query,'webERP',$errorMessage,null,0);

and lines 204-206 in models/stockinformation.php
(same code as before).

I unpublished the weberpCART from Joomla! and now webERP works OK, but cart is not working.

Problem found but solution not yet. Why does weberpCART need to change the prices in webERP? I seem to remember that it was a patch to allow weberpCART work with the new configuration of prices (with startdate and enddate), but another solution is needed, I guess.