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/Shopware/Core/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_order_billingaddress -> ob
s_order_shippingaddress -> os
The variable of the column customergroup from the table s_user for example would look like this:
u.customergroup
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
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.
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 order to apply the condition in a shipping method, insert the corresponding description (e.g. has_topseller for the topseller condition) in the tab Advanced Settings in the field Own conditions. You also have the possibility to combine several conditions.
For this purpose AND (&& or and) as well as OR links (|| or or) are available.
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
!attribute && !mixedcart -> no item in the cart has attr1 = true
attribute && -> only items in the cart that have attr1 = true
!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)
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.
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
item enables a shipping cost only if the cart contains at least one item of the defined characters in its item number.
!item disables the shipping cost if there is at least one item in the cart that includes the defined characters in its item number.
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
category activates the shipping cost only if there is at least one item from the selected category in the cart.
!category deactivates the shipping cost if there ist at least one item of the category in the cart.
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
totalweight < VALUE activates the shipping cost if the total weight is < than the reference value
totalweight > VALUE activates the shipping cost if the total weight is > than the reference value.
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.
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.
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.
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 country_ID.
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.country_ID) 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.country_ID/ub.country_ID. 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.country_ID,ub.country_ID) != 'LÄNDERID', TRUE, !attribute) checks if the country of the user is unequal to the country_ID. Is the country unequal to the country_ID the shipping cost gets activated. If the country is alike the country_ID the value of attribute is checked. If attribute = true the shipping cost gets deactivated, if it is false, the shipping cost gets activated.
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
totalamount < VALUE activates the shipping cost only if the total amount is < than the reference value.
totalamount > VALUE activates the shipping cost only if the total amount is > than the reference value.
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
manufacturer activates the shipping cost if there is at least one item of the selected manufacturer in the cart.
!manufacturer deactivates the shipping cost if there is no item of the selected manufacturer in the cart.
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.
In the cart you have more items with 7% tax rate so 7percent is true and the 7% shipping cost is activated.
In the cart you have more items with 19% tax rate so 7percent is false and the 19% shipping cost is activated.
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.
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€
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.
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.
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€
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
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€
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(ifnull(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.
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
In Shopware 5.3 some variables have been changed, which could be important for your calculations.
| To 5.3 | From 5.3 |
|---|---|
| userID | user_id |
| countryID | country_id |
| stateID | state_id |