[WebERP-developers] Improving the Freight Calculation Costs

[webERP D. <web...@li...>]

Hi all:

I need to improve the freight calculation system in webERP, for those (like
me) not having an API with the preferred courier system to get online,
update, error-free freight quotes.

Currently we have the table freightcosts

CREATE TABLE IF NOT EXISTS `freightcosts` (
  `shipcostfromid` int(11) NOT NULL AUTO_INCREMENT,
  `locationfrom` varchar(5) NOT NULL DEFAULT '',
  `destination` varchar(40) NOT NULL DEFAULT '',
  `shipperid` int(11) NOT NULL DEFAULT '0',
  `cubrate` double NOT NULL DEFAULT '0',
  `kgrate` double NOT NULL DEFAULT '0',
  `maxkgs` double NOT NULL DEFAULT '999999',
  `maxcub` double NOT NULL DEFAULT '999999',
  `fixedprice` double NOT NULL DEFAULT '0',
  `minimumchg` double NOT NULL DEFAULT '0',
  PRIMARY KEY (`shipcostfromid`),
  KEY `Destination` (`destination`),
  KEY `LocationFrom` (`locationfrom`),
  KEY `ShipperID` (`shipperid`),
  KEY `Destination_2` (`destination`,`locationfrom`,`shipperid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;


I propose to improve the destination part. We can split it in 2 fields:
destinationcountry
destinationzone

Function CalcFreightCost will get all 6 address fields as parameters.

Country should be use in the city (zone) search then:

   - If a destination country has the same rate for all its cities, then 1
   record will be enough to get it (no need to maintain all cities).
   - If a destination country has different rates for different zones,
   regions, states, zips, etc, then will be eaiser to find out
   - It will not mix up (as current version does with Christchurch city in
   UK and a city of the same name in NZ). Hundreds of similar city names
   problems. Same for the same ZIP code in different countries.

 Those using an API can code an if statement at the beginning of
CalcFreightCost and use the webERP calculation as last resort.

Any other ideas?

Regards,
Ricard