Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Matt Bliss 176 posts 234 karma points c-trib
    Jan 23, 2012 @ 11:57
    Matt Bliss
    0

    Getting value of "free shipping above price"

    I have a tea commerce installation and would like to show "free delivery on orders over x" where I pick up the x from the first shipping method and current country

    As a similar idea I would also like to show in the first basket page either "this order is eligable for free shipping" or "add items to the value of y and enjoy free shipping".

    How would I access the  information from XSLT or C# code?

  • Peter Duncanson 430 posts 1360 karma points c-trib
    Jan 23, 2012 @ 18:12
    Peter Duncanson
    0

    You can use the XSLTExtension to get the current order XML which should give you prices/totals etc so you can work out if it passes your free payment threshold.

    See here: 

    http://anders.burla.dk/umbraco/tea-commerce-xslt-extensions/

    There are C# versions of all these too which you can use if you are using Razor or backend code. 

    Hope that helps?

    Pete

  • Matt Bliss 176 posts 234 karma points c-trib
    Jan 23, 2012 @ 18:18
    Matt Bliss
    0

    Thanks Pete,

    My fault I didn't explain fully. I want to be able to get the shipping prices when the basket / order is empty.

    Matt

  • Peter Duncanson 430 posts 1360 karma points c-trib
    Jan 23, 2012 @ 18:24
    Peter Duncanson
    0

    Any good to you, not sure if there is an XSLT version but the boys are normally pretty good at having thought of this stuff:

    http://rune.gronkjaer.dk/en-US/2010/11/16/tea-commerce-javascript-api/#getShippingMethods

     

  • Matt Bliss 176 posts 234 karma points c-trib
    Jan 23, 2012 @ 18:40
    Matt Bliss
    0

    That's interesting,

    I really want it to grab this data in the back-end so would prefer XSLT or C#, but the example you found in the JavaScript library looks like it might return all the data I'm after. There's an XLST extention with the same name (http://anders.burla.dk/umbraco/tea-commerce-xslt-extensions/), so I will have a play with that and see if it actually returns more data than the example shows (possibly it returns all the data that the JavaScript function does but it is just not documented).

    Thanks,

    Matt

  • Matt Bliss 176 posts 234 karma points c-trib
    Jan 23, 2012 @ 21:24
    Matt Bliss
    0

    Thanks for you help Pete.

    After some testing I found that the XSLT helper, JavaScript function and the C# functions all work on the basis of the current order or exisitng processed orders and I want to get the default delivery cost values before an order has been started.

    The only solution I could find was to connect straight to the SQL database and extract the data that way.

    Based on my installation only having one country and being interested only in the default standard shipping the following SQL command will get me the data I need (If you have multiple countries then you will need to update this based on the current country, likewise if you want to get all shipping options not just the first one)

    SELECT     TeaCommerce_ShippingCurrency.Fee, TeaCommerce_ShippingCurrency.FreeShippingFromOrderTotalPrice
    FROM         TeaCommerce_Country INNER JOIN
                          TeaCommerce_ShippingMethod_rel_Country ON 
                          TeaCommerce_Country.StandardShipmentMethodId = TeaCommerce_ShippingMethod_rel_Country.ShippingMethodId AND 
                          TeaCommerce_Country.Id = TeaCommerce_ShippingMethod_rel_Country.CountryId INNER JOIN
                          TeaCommerce_ShippingCurrency ON TeaCommerce_ShippingMethod_rel_Country.Id = TeaCommerce_ShippingCurrency.ShippingMethod_relCountryId
    WHERE     (TeaCommerce_Country.IsDefault = 1)

    Having checked / generated this in SQL Management Studio I then created some C# code to grab the data I wanted:

                String fee = "";
                String freeOver = "";
                String query = "SELECT TeaCommerce_ShippingCurrency.Fee, TeaCommerce_ShippingCurrency.FreeShippingFromOrderTotalPrice "
                    + "FROM TeaCommerce_Country INNER JOIN TeaCommerce_ShippingMethod_rel_Country ON "
                    + "TeaCommerce_Country.StandardShipmentMethodId = TeaCommerce_ShippingMethod_rel_Country.ShippingMethodId AND "
                    + "TeaCommerce_Country.Id = TeaCommerce_ShippingMethod_rel_Country.CountryId INNER JOIN "
                    + "TeaCommerce_ShippingCurrency ON TeaCommerce_ShippingMethod_rel_Country.Id = TeaCommerce_ShippingCurrency.ShippingMethod_relCountryId "
                    + "WHERE (TeaCommerce_Country.IsDefault = 1)";
                SqlConnection connection;
                connection = new SqlConnection(GlobalSettings.DbDSN);
                connection.Open();
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    SqlDataReader reader = command.ExecuteReader();
                    if (reader.HasRows)
                    {
                        reader.Read();
                        fee += reader["Fee"];
                        freeOver += reader["FreeShippingFromOrderTotalPrice"];
                    }
                }

    This code doesn't have any exception checking on the DB IO and outputs numerical data as string values (it was just some test code) but I thought I'd post this basic solution in case anyone else wanted to do something similar.

    Anders / Rune, If I've missed the obvious and there is a better way of doing this please do tell me!

  • Peter Duncanson 430 posts 1360 karma points c-trib
    Jan 24, 2012 @ 11:29
    Peter Duncanson
    0

    Seems a fragile solution but one that works. Ideally there should be able to get access to this data without having to drop down to the DB which might change. Possible one to add to the future feature list unless like you say there is another way?

    Good detective work though, top hacking! :)

    Pete

  • Matt Bliss 176 posts 234 karma points c-trib
    Jan 24, 2012 @ 11:49
    Matt Bliss
    0

    I agree Pete, it is a fragile solution and not the most elegant either!

    As I don't want DB IO on front end page calls (it defeats the purpose of having all published data in cached XML), I hooked up the above code into a settings node on the Umbraco save event and saved these values into a couple of 'label' datatypes enabling the information to be pulled into any front end pages whenever wanted using the cached XML.

    But it does mean the the User has to first update the Shipping costs in the Tea Commerce Section and then remember to 'Save and Publish' the settings node afterwards, so I'm not really happy with the outcome. It would be good if there was an event I could hook into for the save of the shipping in TeaCommerce, but don't know how to and ideally as you say it would be best to be able to get this data with out a SQL query anyway.

    Anders / Rune, please tell me I've missed something!

  • Rune Grønkjær 1372 posts 3103 karma points
    Jan 24, 2012 @ 13:15
    Rune Grønkjær
    0

    Hi Matthew,

    You can get all countries as objects using the razor API like this:

    TeaCommerce.Razor.TeaCommerce.GetCountries()

    Then you can use Linq to select whatever you need. The TeaCommerce.Razor.TeaCommerce also contains a bunch of other usefull methods for getting stuff.

    /Rune

  • Matt Bliss 176 posts 234 karma points c-trib
    Jan 25, 2012 @ 13:31
    Matt Bliss
    0

    Thanks Rune, 

    That would be a bit more elegant solution, I'll look into that later

    Matt

Please Sign in or register to post replies

Write your reply to:

Draft