Database driven shipping

Hi

I've a working live eCart5 with 3 shipping rules, one for UK, one for Europe and one for the rest of the world. I need to add DHL shipping based on type of delivery, where to and based on the weight(s) of item(s). DHL UK have said they don't use an API solution and have supplied two spreadsheets instead. One is for 3 domestic tariffs and the other for 3 international tariffs over 9 zones.

I've gone back to the tutorials "Database-driven Sales Tax and Shipping" and can't see how anything links to a database.

Is there another tutorial to follow or should I simple work out how the spreadsheet is calculating each value and add the calculation into the shipping part of the eCart object?

If it's the latter can you walk me through how to do this?

Thanks

B

Hi

there are no tutorials for this. you will have to build it yourself.

if i were you i would just build the database out of the spread sheets.
create the recordst on you checkout page (ie at some point after you have collected the location info needed to determine which dhl record(s) to display to the user).

you will have to make this dhl shipping as a shipping option, as well as whatever you other shipping options are. (ie if there are shipping other than dhl offered).


set the selection from the user equal to 2 shipping fields. SHIPPINGNAME and SHIPPINGCOST

in ecart object, set the shipping rule to be equal to session variable SHIPPINGCOST.
on your pages where ever shipping displays to the user, add the display the session SHIPPINGNAME


SOOO lets say you have a package to locationX and weightsY
pull records from the dhl table that are for locationX and weightY
you will have options to display to the user of Express $20, Ground $10, Parcel $5 (or whatever)

your sessions that you create based on thier selection would be
SHIPPINGNAME = DHL Express
SHIPPINGCOST = 20

if I know how your order process flows i can help you better.


and maybe someone else will have a better idea for you.

Hi Sam

Sorry I haven't replied sooner.

Many thanks for the solution. I'm going to start to implement your method in the coming days and will post any issues, questions or results here.

Thanks Again

B

OK, in eCart's Archived Documentation there is a solution recipe for ecart 3.7 Database-driven Sales Tax and Shipping.

It talks about: Shipping cost = base rate + (increment x total weight) - increment

Using a table called ShipRates (ref Blue Sky Footwear) with the columns ShipID, ShipRate, ShipType, ShipState ShipInc and ShipZone and uses an extra page "customer_info.php". Does this recipe apply to eCart5?

Meanwhile, I've simplified the DHL database as follows (note: now only one method of delivery):

DHL Next day UK Domestic:
Times:- Next day UK
Flat Charge 1kg-10kg: charged at £6.75 (inc tax).
0.5kg thereafter: £0.26
Fuel surcharge: at present the fuel surcharge is 13% of the parcel value, and has to be added on at the end, this percentage changes each month, and so needs to be editable.

DHL Express Worldwide:
Times:-refer to DHL zones country list (A zone column is added to the countries table).
Flat Charge:- Dependant on country zone and a value in DHL shipping database
Thereafter charges: Dependant on country zone and a value in DHL shipping database
Fuel surcharge: at present the fuel surcharge is 13% of the parcel value, and has to be added on at the end, this percentage changes each month, and so will needs to be editable.

Do I have enough here to apply to the recipe?

don't go by the recipe, it wont really help you in this case, except as a guide for the basics of creating filtered recordsets and using the set session value server behavior.


from you description, it sounds like you need the following columns in your table:

shipingRateID - Primary Key, Auto Number
shippingRateCountry - 2 letter country code: GB, US AU etc.
shippingBase - this will store the base rate for the country, 6.75 for GB
shippingIncrement - the increment for the country, 0.26 for GB
shippingFuelPercentage - the fuel charge, .13 for GB


on the confirm page, create a shipping rate lookup recordset.

Set the filtering options to filter the shippingRateCountry equal Form Variable 'shipping_country'

Now add 4 Set Session Value server behaviors, one for each of the columns in the shipping table except the ID:
shippingRateCountry
shippingBase
shippingIncrement
shippingFuelPercentage

In each of them, set the trigger to Any Form Post.

Set the name to be the same as the database column

Click the lightning bolt next to value and select the corresponding column from the recordset.


Now in the cart object, you can create the shipping calcualation to use the session variables created:

ShippingCost:

$_SESSION['shippingBase'] + (abs(([Weight] * [Quantity]) > 10)?floor(([Weight] * [Quantity] - 10) / 0.5) * $_SESSION['shippingIncrement']:0) + ($_SESSION['shippingFuelPercentage'] * ([Price] * [Quantity]))

then on the shipping tab, create a new shipping charge. set the trigger to:
Subtotal for any column:
ig the subtotal for the column 'total weight' is '>' a value of '0'

AND
Session Variable exists 'shippingBase'

AND
Session Variable exists 'shippingIncrement'

AND
Session Variable exists 'shippingFuelPercentage'

Calculation:
Based on column subtotal:
Subtotal of Column 'ShippingCost' 'plus' '0'

Many thanks for this solution, makes sense. One question:

I would prefer the Shipping table was simply split into 9 zones (creating 9 rows of base rates, increments etc) rather than having to add 250 countries' base rates and increments.

I've added the zone values to my countries list. When I add the filter to the lookup recordset will the "shippingRateCountry equal the Form Variable 'shipping_country' work or is there something to add?

don't change the value of the countries list, this will just cause other problems. the country list value should be the 2 letter country code.

in the recordset filter chose the contains comparison instead of equals.

Just for clarity - then the shipping rates table has 246 rows, one for each country? I've created that table but in doing so I'm thinking that will need a lot of work to update shipping costs.

How about 2 tables:

1. ShipCountryZones which list the 246 countries with columns for country 2 letter abbreviations and the zones as a key column

2. ShipZoneRates with the 9 rows for zones and columns for Zone Base rate, Zone Increment and Zone fuel surcharge

I would then INNER JOIN the look up recordset with the two tables, the main table being ShipCountryZones and the child table being ShipZoneRates.

Would that work?

yup, that would work.

We're almost there.

Shipping doesn't show unless you refresh the page - but then it looks like the calculations are correct.

One thing I did was move the look up recordset to above the session value server behaviours.

I'm guessing this is an code order or trigger issue.

Note: I'm using the long hand one off country rates table instead of the joined tables idea to get this working. I'll go back to that later.