close ad
Databridge V2 with MySQLi support IS Now Available!
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

Database driven shipping

Thread began 10/05/2010 4:15 am by Badger | Last modified 11/04/2010 9:58 am by Jason Byrnes | 2645 views | 32 replies |

Badger

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

Sign in to reply to this post

sam308940

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.

Sign in to reply to this post

Badger

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

Sign in to reply to this post

Badger

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?

Sign in to reply to this post

Jason ByrnesWebAssist

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'

Sign in to reply to this post

Badger

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?

Sign in to reply to this post

Jason ByrnesWebAssist

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.

Sign in to reply to this post

Badger

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?

Sign in to reply to this post

Jason ByrnesWebAssist

yup, that would work.

Sign in to reply to this post

Badger

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.

Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...