Blog posts of '2012' 'February'

RSS
Shipping Director 1.03 available for NopCommerce 2.4- Monday, February 20, 2012

We've released Shipping Director 1.03 for NopCommerce 2.4.  You can download it from the Download Trial link on its product page.

We've corrected a localization problem, and added additional localization capability.  This is most useful when you want to display numeric values – many languages use different decimal and thousands separators – e.g. “,” vs. “.”.   For example, in English, [$TotalWeight].ToString() would show “6.25”, but in Spanish it would show “6,25”.

Also, you can now set up error messages, etc. for all your languages, and use the new GetLocaleString() function to get a language specific message.

This is a "special function" - it must be assigned to a String variable and must be the first word in the variable’s Expression field.  The expression cannot be complex:  it can be like GetLocaleString("..."), or GetLocaleString([someotherstringvar]), but cannot include any operators.  

As an example, you should validate that Country State Zip have been entered because the front end does not enforce it on Estimate Shipping.  Create a language resource string for each of your languages using the Resource Name like Shipping.Director.PleaseEnterCountryStateZip, and assign appropriate Values for each language.

 Order

Type

Name

Expression

Description Expression

10

String

PleaseEnterCountryStateZip

GetLocaleString("Shipping.Director.PleaseEnterCountryStateZip")

 

20

ErrorExit

Missing Country State Zip

ShippingAddress.Country = null or ShippingAddress.ZipPostalCode = null or ("US,CA".Contains(ShippingAddress.Country.TwoLetterIsoCode) and ShippingAddress.StateProvince = null)

[PleaseEnterCountryStateZip]

Also, in case there are any unforeseen problems during calculation you may want to localize the default “Sorry, we are unable to calculate your shipping.  Please contact the store."   That's already there as Resource Name:  Plugins.Shipping.Director.Error.UnableToCalculateShipping

Similarly, we’ve added a GetSetting() special function.  It can be assigned to an appropriate variable type based on the setting’s type.

We’ve also added additional debugging capability.  The $Debug variable was there previously, but was mostly for packing and rate tracing.  Now it will also show variable assignments, etc.  Just add a Boolean record with name $Debug.  By default it is false.

 Order

Type

Name

Expression

Description Expression

0

Boolean

$Debug

true

 

Check the system log for the messages.  Previously, “debugging” was best accomplished by using Error type records, so now you don't have to do extra Error records for that purpose.  Because the log view page is html, the messages will all appear concatenated.  To read it more clearly, right click the page and view source.  Scroll down a bit and you'll see each message on its own line.

And finally, we’ve moved the trial check to the end of the evaluation routine.  This way, the records will always be evaluated, and messages, etc. will be logged; you will still see the trial warning on the web page after 5 transactions, but it will still evaluate and log the messages. This will allow you to continue testing without having to reset the app or reload the plugins.

Comments (0)
SQL Example - Lookup table for Zip to Zone, and lookup table for Zone/Weight to Rate- Sunday, February 12, 2012

If you have a complex rate lookup, or if you just prefer to write your rate calculation as a SQL Server stored procedure, Shipping Director has the ability to do SQL queries and call stored procedures. 

If an expression starts with one of these keywords, then the expression is evaluated as a SQL query:

            SELECT

            ?SELECT"

            EXECUTE"

            ?EXECUTE

Note thate there are two forms of SELECT and EXECUTE; one form is prefixed with a “?”.  Without the leading “?” the query is treated as a standard SQL paramaterized query using “@”-prefixed parameters.  For example:

select Zone from ShippingZone_Origin23235 where @p1 between PrefixFrom and PrefixTo; [ZipCode3]

 This default behavior should be used when there is a concern for SQL injection.  (It is also recommended to use ErrorExit to test for for such possibilities.)

With the leading “?” the query can contain zero-based indexed placeholders (format items to be used in String.Format).  For example:

?select Zone from ShippingZone_Origin{0} where '{1}' between PrefixFrom and PrefixTo;[OriginZipCode],[ZipCode3]

This provides the ability to create truly dynamic queries.  In the above example, the table name contains a format specifier.

The result of a SQL expression needs to be a single scalar value (i.e. a String, a Decimal, etc.).  Typically the expression is assigned to a variable.

An example of using SQL queries would be to do a custom rate lookup from a set of Zone/Weight/Rate  tables in the database.  The first table would be used to lookup a Zone base on a shipping destination zip code, and the second table would use the looked up Zone and the total weight of items in the cart to lookup the rate.


ShippingZone_Origin23235

PrefixFrom

PrefixTo

Zone

120

126

4

127

127

3

128

147

4

148

163

3

164

165

4

166

172

3

ShippingZone_Rate

Weight

Zone2

Zone3

Zone4

Zone5

Zone6

Zone7

Zone8

Zone9

1

5.17

5.4

5.51

5.75

6.04

6.12

6.22

19.18

2

5.37

5.72

6.22

6.34

6.75

6.89

7.13

21.3

3

5.45

5.97

6.53

6.73

7.15

7.36

7.88

23.21

4

5.58

6.16

6.86

7.17

7.53

7.85

8.44

25.37

5

5.79

6.25

7.15

7.46

7.83

8.2

8.91

27.63

6

5.96

6.44

7.27

7.65

7.97

8.44

9.1

30.01


The first option is to use parameterized queries (“select” without the “?” prefix).  In this example, a constant Origin Zip Code is provided, but this could instead be a calculation of a specific warehouse based on what is in the cart.

SQL queries using parameterized queries

 

Order

Type

Name

Expression

 Rate

 Expression

200

String

ZipCode3

ShippingAddress.ZipPostalCode.Substring(0,3)

 

300

String

Zone

select Zone from ShippingZone_Origin23235 where @p1 between PrefixFrom and PrefixTo; [ZipCode3]

 

350

Decimal

ZoneRate

select Rate from ShippingZone_Rate where Zone = @p1 and Weight = Ceiling(@p2); [Zone],[$TotalWeight]

 

500

OptionExit

Shipping Cost

true

 [ZoneRate]


 

 

 

 

Use a stored procedure to do the lookup  (replace lines 300 and 350 above with just this line 300)

300

 

Decimal

ZoneRate

EXECUTE ShippingZone_LookupRate @p1, @p2; [ZipCode3], [$TotalWeight]

 

The second option is to use the “?” prefix so that the table name can be dynamically determined.  In this example, a constant Origin Zip Code is provided, but this could instead be a caclulation of a specific warehous based on what is in the cart. 

SQL queries using zero-based index placeholders

Order

Type

Name

Expression

 Rate

 Expression

100

String

OriginZipCode

"23235"

 

200

String

ZipCode3

ShippingAddress.ZipPostalCode.Substring(0,3)

 

300

String

Zone

?select Zone from ShippingZone_Origin{0} where '{1}' between PrefixFrom and PrefixTo;[OriginZipCode],[ZipCode3]

 

350

Decimal

ZoneRate

?select Zone{0} from ShippingZone_Rate where Weight = Ceiling({1}); [Zone],[$TotalWeight]

 

500

OptionExit

Shipping Cost

true

 [ZoneRate]

 

 

 

 

 

Use a stored procedure to do the lookup  (replace lines 300 and 350 above with just this line 300)

300

 

Decimal

ZoneRate

?EXECUTE ShippingZone_LookupRate '{0}',{1}; [ZipCode3], [$TotalWeight]


Here's an example stored procedure.  Note that the SELECT statements only return a single column (and should only return a single row)

CREATE PROCEDURE [dbo].[ShippingZone_LookupRate]
(
    @zip     VARCHAR(9),
    @weight  DECIMAL(8,4)
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @zone VARCHAR(3);
    IF @WEIGHT = 0 
      SET @weight = 1;

    select @zone = Zone from ShippingZone_Origin23235 where SUBSTRING(@zip,1,3) between PrefixFrom and PrefixTo

    IF      @zone = '2' select Zone2 from ShippingZone_Rate where Weight = Ceiling(@weight)
    ELSE IF @zone = '3' select Zone3 from ShippingZone_Rate where Weight = Ceiling(@weight)
    ELSE IF @zone = '4' select Zone4 from ShippingZone_Rate where Weight = Ceiling(@weight)
    ELSE IF @zone = '5' select Zone5 from ShippingZone_Rate where Weight = Ceiling(@weight)
    ELSE IF @zone = '6' select Zone6 from ShippingZone_Rate where Weight = Ceiling(@weight)
    ELSE IF @zone = '7' select Zone7 from ShippingZone_Rate where Weight = Ceiling(@weight)
    ELSE IF @zone = '8' select Zone8 from ShippingZone_Rate where Weight = Ceiling(@weight)
    ELSE IF @zone = '9' select Zone9 from ShippingZone_Rate where Weight = Ceiling(@weight)    
END

Tags :  SQLLookupTable
Comments (4)
Purchase Rules - Checking minimum quantities- Sunday, February 12, 2012

Besides calculating rates, Shipping Director can enforce all types of purchase rules.  For example, If you want to enforce that if items are purchased from a specific category that there be at least n of them, then you want an Error expression that looks like "if there are Any items in category A and the Quantity of those items is less than n":  

Order

Active

Type

Name

Expression

Rate Expression

Surcharge Expression

Name Expression

Description Expression

10

 

Reference

categories

ProductVariant.Product.ProductCategories

 

 

 

 

20

 

Reference

categoriesAnyA

[@categories].Any(Category.Name = "Category A")

 

 

 

 

30

 

ErrorExit

Not Enough Items from A

Items.Any([@categoriesAnyA]) and Items.Where([@categoriesAnyA]).Sum(Quantity) < 2

 

 

 

"Not Enough Items from Category A"

You would need to repeat lines 20 & 30 for each category  

(The customer won't get warning until they get to shipping calc page, or estimate shipping)

NOTE:  As of Shipping Director 1.08 for 2.80, you don't need line 10, and line 20's Reference should be: ProductVariant.Product.HasCategory("Category A")  

NOTE: For Shipping Director for 3.10, you don't need line 10, and line 20's Reference should be: Product.HasCategory("Category A")  

Tags :  MinimumQuanity
Comments (0)
Shipping Director 1.02 available for NopCommerce 2.4- Saturday, February 4, 2012

We've released Shipping Director 1.02 for both NopCommerce 2.3 and 2.4.
You can download it from the Download Trial link on its product page.

If you already have version 1.01 installed in the Plugins/Shipping.Director folder, then back it up, and then overwrite the files from what's in the downloaded .zip file.  You do not need a new License.txt file.  After you've uploaded/overwritten your files, you need to restart nopC, or reload plugins.  Either

  a) Login in as Administrator, goto Administration. Click “Restart application” (upper right), or

  b) Login in as Administrator, goto Administration > Configuration > Plugins > click the button "Reload list of Plugins"

In Configuration > Plugins,  scroll down to Shipping Director, and verify that it says version 1.02.

These are the new features:

1) Relaxed license check allows www.mydomain.com as well as mydomain.com.

2) Automatic Update capability.  If you have a prior version installed, it will detect it and upgrade settings, locale strings, etc.  To install, overwrite the files in the plugin folder with what's in the downloaded .zip file.

3) Packing - "Exclude Item Expression".  You can suppress items from being packed.  (E.g. can handle complex free shipping scenarios - unpacked items do not contribute to package  weight or dimensions)

4) $PackageCount variable.   Set after packing with the total # of packages packed.  If you are using packing, then you should check this after the Packing  type, so that you can indicate "Free Shipping" if all items got excluded.

5) $ShippingOptionsCount variable. Set after using an external shipping plugin with the total # of shipping rate options it returned.  Not typically used, but If you are doing something where you think no options will match, then you should check it and say something like "call for rate".

6) Localize Error Message "Sorry, we are unable to calculate your shipping. ..."  : Plugins.Shipping.Director.Error.UnableToCalculateShipping

Comments (0)
Example - Local Pickups and Local Delivery- Saturday, February 4, 2012

Maybe you're a small local store or restaurant, or pizza place; you want to offer local pickup at one of your two locations, or you will deliver nearby.  Here's an example using State, but could also check ShippingAddress.ZipPostalCode or ShippingAddress.City 

 

Order

Active

Type

Name

Expression

Rate Expression

Surcharge Expression

Name Expression

Description Expression

10

 

ErrorExit

No State Entered

ShippingAddress.StateProvince = null

 

 

 

"Please enter Country, State, and Zip"

20

 

ErrorExit

No Zip Entered

ShippingAddress.ZipPostalCode = null

 

 

 

"Please enter Country, State, and Zip"

50

 

String

ShippingState

ShippingAddress.StateProvince.Name

 

 

 

 

100

 

Option

In Store A Pickup

true

0

 

 

 

110

 

Option

In Store B Pickup

true

0

 

 

 

200

 

OptionExit

Local Delvery

[ShippingState] = "local 1"

10

 

 

 

210

 

OptionExit

Local Delivery

[ShippingState] = "local 2"

20

 

 

 

300

 

ErrorExit

Out of Area

 

 

 

 

"Sorry, we can’t deliver to that area"

Also, this example shows fixed Rates, but you can also calculate some percentage of the cost, or fixed rate plus percentage, etc. using

[$SubTotalWithoutDiscounts]

[$SubTotalWithDiscounts]

e.g.   Rate =  5 + ( [$SubTotalWithDiscounts] * 0.05 )

(Note that only US and Canada have StateProvince configured by default in nopCommerce, so adjust/remove the above ErrorExit if needed)


Tags :  LocalPickup
Comments (2)
Example - Free ground shipping on orders over $50, but want the customer to still have the option to pick the quicker options from Fedex at the regular price.- Friday, February 3, 2012

Use the Surcharge Expression to negate the rate if the shipping option name contains the word “Ground”.

Add new 'Shipping Director' record

Type

Option

Name

FedEx Free ground shipping only on orders over X

Expression

true

Rate Expression

Shipping.FedEx

Surcharge Expression

[$SubTotalWithDiscounts] > 50 and [$Name].Contains("Ground") ? -[$Rate] : 0

Name Expression

[$SubTotalWithDiscounts] > 50 and [$Name].Contains("Ground") ? "Free Ground Shipping" : [$Name]

Description Expression

 



Tags :  FreeShipping
Comments (5)
Example - Allow only FedEx for domestic services and only USPS for international services- Friday, February 3, 2012

Add two 'Shipping Director' records

Type

OptionExit

Name

FedEx for domestic services only

Expression

ShippingAddress.Country.Name = "United States"

Rate Expression

Shipping.Fedex

Type

OptionExit

Name

USPS for international services only

Expression

ShippingAddress.Country.Name <> "United States"

Rate Expression

Shipping.USPS


Tags :  International
Comments (0)
Shipping Director 1.02 can Exclude Items from Packing- Tuesday, January 31, 2012

(This is an old blog.  If using nopCommerce 3.x or greater, note that "ProductVariant." should be removed if you copy these expressions)

You can exclude items from packing - the Packing option now has a "Exclude Item Expression" (shows in Description Expression column).  It should evaluate as a boolean (true, false).  Items that are not packed do not contribute to the weight/dimensions of a package, therefore you can create complex free shipping scenarios like "Free shipping for product x if quantity n or more".   Note that Packing expressions are always with respect to the item as it's being packed (unlike other record types where expressions relate to the entire cart - e.g. Items).  So, if you need to count the quantity of something in the cart to determine if exclusion should apply, then you need to do it with a variable before you pack.  Then, use that variable in the Exclude Item Expression with any other item specific expression.  For example, below, we sum the quantity of a particular item and if greater than 10, the "Exclude From Packing" variable is true.  Then in packing expression, we use the variable to check if exclusion should be considered, and then check the item condition.

Note also, that we initially check for Free Shipping - no point in checking expressions, packing, etc., if all items in cart are free shipping.  (An alternate, and better although a little slower, way to check free shipping for the entire cart is to use the $IsFreeShipping variable; this uses the nopC core OrderTotalCalcService.IsFreeShipping to check all the possible cart ships free possibilities including customer roles).  Also, we check variable $PackageCount after we pack - [$PackageCount is set by the system with the # of packages that result from packing - if nothing was packed (everything was excluded), then the entire cart ships free.

Order

Type

Name

Expression

Rate Expression

Surcharge Expression

Name Expression

Description Expression

10

OptionExit

Free Shipping

Items.All(ProductVariant.IsFreeShipping)

0

 

 

 

100

Reference

productName

ProductVariant.Product.Name

 

 

 

 

110

Boolean

Exclude From Packing

Items.Where([@productName] = "my product name").Sum(Quantity) > 10

 

 

 

 

120

Packing

First Fit

true

Packing.FirstFitSingleBox

 

 

[Exclude From Packing] and [@productName] = "my product name"

130

OptionExit

Free Shipping

[$PackageCount] = 0

0

 

 

 

Tags :  Packing
Comments (0)