Individual shipping costs

Version

5.5.0 or newer

Table of contents

Basic information

By using the individual conditions and calculations in the shipping module, you can set up very complex requirements and authorizations. This article contains several examples. Both options can be found in the tab "Advanced configurations" in the Shipping costs module in your Shopware backend.

An individual condition can activate or deactivate a shipping cost depending on different terms. The individual calculationignores the automatically calculated shipping costs. Only the field calculations is used for calculating the shipping cost.

In general you can use all variables of the selected tables within the functions sGetPremiumDispatches and sGetPremiumShippingcosts. The functions can be found in the file /engine/core/class/sAdmin.php. The areas are separated as follows:

  • s_order_basket -> b
  • s_articles -> a
  • s_articles_details -> d
  • s_articles_attributees -> at
  • s_core_tax -> t
  • s_user -> u
  • s_user_billingaddress -> ub
  • s_user_shippingaddress -> us

The variable of the column customergroup from the table s_user for example would look like this:

 


u.customergroup

 

Required commands

1. MAX() -> Displays the largest value of the variable, e.g. MAX(d.weight) displays the heaviest weight of all products in the basket.
2. MIN() -> Displays the smallest value of the variable, e.g. MIN(d.weight) displays the lowest weight of all products in the basket.
3. COUNT() -> Displays the number of a variable, e.g. COUNT(d.ordernumber) displays the count of different products in the basket.
4. SUM() -> Sums up all the values of a variable, e.g. SUM(s.weight*b.quantity) displays the total weight of all products in the basket.
5. IFNULL(a,b) -> Replaces a variable with another value if this variable is NULL, e.g. IFNULL(us.zipcode,ub.zipcode) checks whether there is a shipping address zip code and, if not, uses the billing address zip code.
6. IF(condition,yes,no) -> An easy if-then-else condition, e.g. IF(d.weight>5,1,0) if the weight is greater than 5, the result is 1; if not, it's 0.
7. CASE() -> Complex condition, e.g. CASE WHEN a.supplierid = 4 THEN 1 WHEN a.supplierid = 5 THEN 2 else 3 END Displays for the supplier 4 1, for the supplier 5 2 and for every other supplier 3.
8. CURDATE() -> Displays the current date, e.g. 2016-03-17

 

Setting up free text fields

Many of the provided examples require free text fields.

Since Shopware 5.2 there is an individual module under "Configuration > Free text field management", which has replaced the old one: Free text field management.

 

Own conditions

Default

For an own condition you mostly have to extend the "Extended SQL query" at "Configuration > Basic settings > Storefront > Shipping costs module". By default there already are requests for top sellers, comments and ESD-items:

MAX(a.topseller) as has_topseller, MAX(at.attr3) as has_comment, MAX(b.esdarticle) as has_esd 

In the following paragraphs we describe, how you can extend this.

 

Boolean free text fields

Please keep in mind that the request can change depending on which Shopware-Version you use. Since 5.2.0 Shopware delivers true="1" and false="0", older versions might require to check for ="true" or ="false".

 

Simple condition depending on free text field 1

 

You can extend the default-request by many further fields, for example the free text fields, in this example attr1:

 

MAX(a.topseller) as has_topseller, MAX(at.attr3) as has_comment, MAX(b.esdarticle) as has_esd, MAX(at.attr1="true") as attribute 

 

The field terms can now check for attribute or !attribute.

1. attribute activates the shipping cost if attr1 = true 
2. !attribute deactivates the shipping cost if attr1 = false

 

Complex condition depending on free text field for mixed carts

You can also check if there is a mixed cart, which means the cart contains items that fulfil the condition as well as items that do not fulfil the condition. Like this the shipping costs can be split up to three different values.

 

MAX(a.topseller) as has_topseller, MAX(at.attr3) as has_comment, MAX(b.esdarticle) as has_esd, MIN(attr1='true') as attribute, MAX(attr1='true') as mixedcart

 

  1. !attribute && !mixedcart -> no item in the cart has attr1 = true 
  2. attribute && -> only items in the cart that have attr1 = true 
  3. !attribute && mixedcart -> both types of items in the cart, at least one item has attr1 = true and at least one item which has attr1 = false

 

This would enable 3 as a combined shipping cost which calculates the normal shipping costs as well as the cost for the forwarder charge for the specific items, if you have to send the items differently.

 

Compare the item count in the cart with articles with specific attributes

When you want to compare the items in the cart with items with specific attributes to calculate, you can use the following example. We use "attr4":

At first the query for the database values:

 

COUNT(a.id) as item_count, MAX(at.attr4='1') as warehouse1, MAX(at.attr4='2') as warehouse2

 

One possible calculation could be:


// All items have warehouse1
IF(item_count=warehouse1,1,0)
 
// All items have warehouse1 or warehouse2
IF(item_count=(warehouse1+warehouse1),1,0)

 

Shipping free products

Often you want to send specific products like vouchers or small items shipping free, but just when only those products are in the cart.

To achieve this you start by extending the "Extended SQL query" at Configuration > basic settings > shipping costs module with a term like this:

 


, MIN(b.shippingfree) = 1 AS allshippingfree

 

Then you create a special shipping method "Free shipping" (or likewise) in which you enter "allshippingfree" in the "Terms" field in the "Advanced configurations" tab. It is also important that you select "Support" for the "Support items free of shipping costs" option. In the tab "Shipping costs" you now enter and empty shipping cost value for 1 to arbitrary.

In every other shipping method you enter the "!allshippingfree" as the "Term" and select "Support, but add shipping costs anyway" for the Option "Support items free of shipping costs" option.

 

Item numbers

You can also check for specific item numbers or parts of this numbers, to limit a shipping cost especially for those or exclude it for them.

 

MAX(a.topseller) as has_topseller, MAX(at.attr3) as has_comment, MAX(b.esdarticle) as has_esd,MIN(IF(d.ordernumber LIKE '%ITEM NUMBER PART%' OR b.modus <> 0,1,0)) as item

 

  1. item enables a shipping cost only if the cart contains at least one item of the defined characters in its item number. 
  2. !item disables the shipping cost if there is at least one item in the cart that includes the defined characters in its item number.

 

Categories

You can also restrict a shipping cost for items from specific categories. The categoryID can be found in the category module at System-ID.

 

MAX(a.topseller) as has_topseller, MAX(at.attr3) as has_comment, MAX(b.esdarticle) as has_esd,MIN((SELECT 1 FROM s_articles_categories WHERE articleID=a.id AND categoryID=CATEGORYID)) as category 
  1. category activates the shipping cost only if there is at least one item from the selected category in the cart.
  2. !category deactivates the shipping cost if there ist at least one item of the category in the cart.

 

Shipping weight

If you want to enable a shipping cost only up to a given maximum weight you can check the total weight. The reference value can be selected as you wish e.g. 1.05, or 3.

 

MAX(a.topseller) as has_topseller, MAX(at.attr3) as has_comment, MAX(b.esdarticle) as has_esd, SUM(b.quantity * d.weight) as totalweight
  1. totalweight < VALUE activates the shipping cost if the total weight is < than the reference value
  2. totalweight > VALUE activates the shipping cost if the total weight is > than the reference value.

 

Date

If a shipping cost, e.g. a reduction-shipping-rule, should only be available on a specific date you can use the function CURDATE(). The shipping-rule could only enable a discount for those days. For this you do not have to edit the extended SQL query, the rule can be entered in the shipping cost directly.

 


CURDATE() = "2016-03-17" || CURDATE() = "2016-03-18"

This activates the shipping cost only at the 17/03/2016 and 18/03/2016.

 

Subshops

To restrict a shipping cost for one of your subshops you can check the value b.multishopID. For this you do not have to edit the extended SQL query, the rule can be entered in the shipping cost directly.

 


b.multishopID != 1

 

This rule deactivates the shipping cost in the subshop with the id 1.

 

Customergroups

If a shipping cost should only be available for a specific customergroup you can check the value u.customergroup. For this you do not have to edit the extended SQL query, the rule can be entered in the shipping cost directly. The user values are only available after the login in the frontend, because of this the shipping cost is also only available after the login in the frontend.

 


u.customergroup = "EK" 

 

This condition only enables the shipping cost for the customergroup EK.

 

Countries

Restrict the shipping for specific countries

It can be necessary to restrict the countries not only by the default function in the tab country selection, but also with an individual condition. This can be done by using the ISO-Code or the countryID.

MAX(a.topseller) as has_topseller, MAX(at.attr3) as has_comment, MAX(b.esdarticle) as has_esd, (SELECT countryiso FROM s_core_countries WHERE id = us.countryID) AS DeliveryCountry

In this individual condition you can e.g. enter DeliveryCountry = 'AT' or DeliveryCountry = 'DE' to enable the shipping cost for those countries.

 

Restrict shipping of specific items in specific countries

If you do not want to ship an item in a specific country we would advice to use the check of a free text field combined with a check for us.countryID/ub.countryID. The free text field value is set as true for the item.

 

MAX(a.topseller) as has_topseller, MAX(at.attr3) as has_comment, MAX(b.esdarticle) as has_esd, MAX(at.attr1="true") as attribute 

 

IF(IFNULL(us.countryID,ub.countryID) != 'LÄNDERID', TRUE, !attribute) checks if the country of the user is unequal to the countryID. Is the country unequal to the countryID the shipping cost gets activated. If the country is alike the countryID the value of attribute is checked. If attribute = true the shipping cost gets deactivated, if it is false, the shipping cost gets activated.

 

Total amount

You can check the total amount of the cart if a shipping cost should only be available starting at a minimum amount. The reference value can be selected as you wish, e.g. 10.50 or 300.

 

MAX(a.topseller) as has_topseller, MAX(at.attr3) as has_comment, MAX(b.esdarticle) as has_esd, SUM(b.quantity * b.price) as totalamount 

 

  1. totalamount < VALUE activates the shipping cost only if the total amount is < than the reference value. 
  2. totalamount > VALUE activates the shipping cost only if the total amount is > than the reference value.

 

Manufacturer

To enable a shipping cost only for the items of a specific manufacturer you can use the value a.supplierID. The supplierID can be checked in the column id in the database table s_articles_suppliers.

 

MAX(a.topseller) as has_topseller, MAX(at.attr3) as has_comment, MAX(b.esdarticle) as has_esd, MAX(IF(a.supplierID = SUPPLIERID,1,0)) as manufacturer
  1. manufacturer activates the shipping cost if there is at least one item of the selected manufacturer in the cart. 
  2. !manufacturer deactivates the shipping cost if there is no item of the selected manufacturer in the cart.

 

Select the tax of the shipping cost

Sometimes you have to manually select the tax of the shipping cost. This example explains how you select always that tax, that has the larger part of the cart amount. For this you have to extend the extended SQL query and add a condition according to the summed amount for each tax rate.

 

MAX(a.topseller) as has_topseller, MAX(at.attr3) as has_comment, MAX(b.esdarticle) as has_esd,IF(SUM(if(t.tax = 7, (b.price*b.quantity) ,0))>SUM(if(t.tax = 19, (b.price*b.quantity) ,0)),1,0) as 7percent

 

Now you create two similar shipping costs, one for 7% and one for 19%. The only difference is the tax rate and the entered value for terms. For the 7% shipping cost the term value is 7 percent and for the 19% one !7percent. You eventually have to extend this condition for further tax rates.

  1. In the cart you have more items with 7% tax rate so 7percent is true and the 7% shipping cost is activated. 
  2. In the cart you have more items with 19% tax rate so 7percent is false and the 19% shipping cost is activated.

 

Individual calculations

For the individual calculation you always have to enter "Calculation" for the option "Calculate dispatch costs based on" otherwise the calculation is not used. The condition is mostly entered in the tab "shipping costs" by using the factor not by a specific shipping cost value. The factor 100 is the default to use exactly the calculated value as the shipping cost.

 

Calculation by measures

Some shipping providers calculate the costs by the measures of the items. For a calculation by length, width and height the calculation could e.g. look like this:

 


If(d.length < 12,If(d.width < 12,If(d.height < 12,4.95,6.95),If(d.height < 12,6.95,8.95)),If(d.width < 12,If(d.height < 12,6.95,8.95),If(d.height < 12,8.95,12.95)))

 

You might have to sum the values so you include the measures of all selected items:


If(SUM(d.length*b.quantity) < 12,If(SUM(d.width*b.quantity) < 12,If(SUM(d.height*b.quantity) < 12,4.95,6.95),If(SUM(d.height*b.quantity) < 12,6.95,8.95)),If(SUM(d.width*b.quantity) < 12,If(SUM(d.height*b.quantity) < 12,6.95,8.95),If(SUM(d.height*b.quantity) < 12,8.95,12.95)))

 

The calculations results for the factor 100 are the following:

 

Length, Width, Height < 12 --> 4,95€
One value > 12 --> 6,95€
Two values > 12 --> 8,95€
All values > 12 --> 12,95€

 

Shipping free based on the net value for gross value shops

Even if the gross value is displayed in the shop the shipping free amount shall be based on the net value. This can be done by using this calculation:

 


 IF((SUM(b.quantity*b.price/(t.tax+100)*100))<50,0,4.95)

 

The factor 100 in the tab shipping costs calculates 0,00€ shipping costs for a net value lower than 50,00€, all other net values get charged 4,95€ shipping costs.

 

Exclude specific items of the shipping free value

If you want to exclude the costs of specific items from the shipping free value you can do so by using a free text field. Such a calculation can be done e.g. if only items of a special branch shall be delivered shipping free for an amount of X, all other items are not considered in the calculation of the amount X.

 


IF(SUM(IF(at.attr16 != 'true',b.price*b.quantity,0))<75,4.95,0)

 

With the factor 100 in the tab shipping costs the amount of items, which have a value of false for attr16 get summed. If attr16 is true, 0 is added to the sum. In the end, the calculated sum gets checked, whether it is smaller than 75, which charges 4,95€, or bigger, which charges 0,00€ shipping costs.

 

Individual calculation depending on the total amount

If you want different shipping costs for special price limits, e.g. with a percentage discount the calculation can look like this:

 


IF(SUM(b.quantity*b.price)>600,0,IF(SUM(b.quantity*b.price)> 300,8.5/100*50,IF(SUM(b.quantity*b.price) > 100,8.5/100*80,8.50)))

 

The calculation appraises a value of the following for the factor 100:

total amount < 100,00€ no discount --> 8,50€ 
total amount > 100,00€ 20% discount --> 6,80€ 
total amount > 300,00€ 50% discount --> 4,25€ 
total amount > 600,00€ --> 0,00€ 

 

Surcharge by zip codes

This is an example for a surcharge shipping cost, which is added to the default shipping costs if a specific condition if fulfilled, in this case a zip code.

As the term you can e.g. enter his for one specific zip code:

 


IFNULL(us.zipcode,ub.zipcode) = '48624'

 

For multiple zip codes or a whole area of zip codes it could look like this:

 


IFNULL(us.zipcode,ub.zipcode) in ('48624','48683','48739','59847')
OR
IFNULL(us.zipcode,ub.zipcode) LIKE '49%' 

 

A bigger area from e.g. 42999 to 99000 could be checked like this:

 


IFNULL(us.zipcode,ub.zipcode) > '42999'
AND IFNULL(us.zipcode,ub.zipcode) < '99000'

 

The calculation depends on special factors for the different zip code areas can be done like this:

 


IF(IFNULL(us.zipcode,ub.zipcode) > '42999' AND IFNULL(us.zipcode,ub.zipcode) < '43999',SUM(d.weight*b.quantity)*2.5,IF(IFNULL(us.zipcode,ub.zipcode) > '43999' AND IFNULL(us.zipcode,ub.zipcode) < '44999',SUM(d.weight*b.quantity)*3.5,IF(IFNULL(us.zipcode,ub.zipcode) > '44999' AND IFNULL(us.zipcode,ub.zipcode) < '45999',SUM(d.weight*b.quantity)*4.5,SUM(d.weight*b.quantity)*5.5))) 

 

The calculations results for the factor 100 would be this:

Zip code between 42999 and 43999 --> quantity*weight*2,5
Zip code between 43999 and 44999 --> quantity*weight*3,5
Zip code between 44999 and 45999 --> quantity*weight*4,5
Zip code in another area --> quantity*weight*5,5

 

Calculation by category

If you want to calculate the shipping costs depending on the category of the items it can be done like this:

 

MIN((SELECT 2 FROM s_articles_categories WHERE articleID=a.id AND categoryID=X))

 

The X in the query categoryID=X has to be replaced by the id of the category. In the tab "shipping costs" enter a scaling from 0 to 1 and from 2 to arbitrary with the selected shipping costs. You also can use multiple categories e.g. like this:

 

MIN((SELECT 3 FROM s_articles_categories WHERE articleID=a.id AND categoryID=X)) + MIN((SELECT 5 FROM s_articles_categories WHERE articleID=a.id AND categoryID=X)) + MIN((SELECT 7 FROM s_articles_categories WHERE articleID=a.id AND categoryID=X))

 

The scaling could look like this:

0 to 3 (only category 1) --> 3,95€
4 to 5 (only category 2) --> 4,95€
6 to 7 (only category 3) --> 6,95€
8 to 9 (category 1 and 2) --> 8,90€
10 to 11 (category 1 and 3) --> 10,90€
12 to 14 (category 2 and 3) --> 11,90€
15 to arb. (all) --> 15,85€ 

 

Shipping costs by item

You can set up different shipping costs for the items if you use a free text field. In this example we use attr4 for the check. Please keep in mind that the free text field has to be enabled for variants if you want different shipping costs for each variant. After you configured the field you can enter the shipping costs value in the item data. If you want to enter 4,95€ for the example item you have to enter 4.95 there. The decimal character is always a dot! In the shipping cost we use the calculation on individual calculation as the base type and enter in the shipping costs tab the factor 100.

In the individual calculation in the tab "advanced configuration" you can now enter which shipping costs should be used. For this we have different possibilities:

1. SUM(at.attr4*b.quantity) sums the given values in the free text field for all items and also considers the quantity for the different positions.
2. MAX(at.attr4) uses the highest value of all possibilities in the free text field of the items.
3. SUM(IF(at.attr4=,2.95,at.attr4)*b.quantity) sums the values of the free text field and considers the quantity. If there is no value for a position in the free text field there is a fix value of 2,95€ added.

 

Weight

Individual calculation depending on weight incl. splitting into multiple packages

Some shipping providers have a maximum weight per package. If you don't want to block the shipping cost completely, but check if you can split the delivery into multiple packages you can use the following calculation. In the calculation you set for the shipping cost to be blocked if the weight is higher than the maximum weight and the deliver can not be split up. The extended SQL query looks like this, if the maximum weight per package is 30kg:

 

MAX(a.topseller) as has_topseller, MAX(at.attr3) as has_comment, MAX(b.esdarticle) as has_esd,IF(MAX(d.weight)<30,1,0) as divisible

 

By entering "divisible" in the terms you can activate the shipping cost if the maximum weight per item in the cart is lesser than 30kg.

The following calculation splits the individual positions into multiple packages, which cost 4,95€ per package.

 


IF(SUM(d.weight*b.quantity)>30,CEIL(SUM(b.quantity*d.weight)/30)*4.95,4.95)

 

Scale for items on pallets

When you want to add a fixed rate per kilo for items on pallets you can use this example. To add 50,00€ per 750kg you can use the following calculation. It is important to enter 5000 as the factor in the shipping costs tab.

 

CEIL(SUM(d.weight*b.quantity)/750) 

 

You can also add an entertainment allowance to this calculation, e.g. 10,00€.

 

CEIL(SUM(d.weight*b.quantity)/750)+10

 

Variables since Shopware 5.3.0

In Shopware 5.3 some variables have been changed, which could be important for your calculations.

To 5.3From 5.3
userIDuser_id
countryIDcountry_id
stateIDstate_id