Quickly delete all products and orders from uCommerce
Saturday, May 11, 2013 11:09:59 AM (GMT Daylight Time, UTC+01:00)
Sometimes you need to blitz your uCommerce database e.g. just before launch or to remove the testing products etc.
This is a quick database clear script which will clear delete all orders and products in uCommerce.
Be careful - this will remove everything without any form of checks (this is the V3 script let me know if you need it for other versions).
BEGIN TRAN
DELETE FROM uCommerce_ProductReviewComment
DELETE FROM uCommerce_ProductReview
DELETE FROM uCommerce_OrderLineDiscountRelation
DELETE FROM uCommerce_ShipmentDiscountRelation
DELETE FROM uCommerce_Discount
UPDATE uCommerce_OrderLine SET ShipmentId = NULL
UPDATE uCommerce_PurchaseOrder SET BillingAddressId = NULL
DELETE FROM uCommerce_Shipment
DELETE FROM uCommerce_OrderAddress
DELETE FROM uCommerce_OrderProperty
DELETE FROM uCommerce_OrderLine
DELETE FROM uCommerce_PaymentProperty
DELETE FROM uCommerce_Payment
DELETE FROM uCommerce_OrderStatusAudit
DELETE FROM uCommerce_PurchaseOrder
DELETE FROM uCommerce_Address
DELETE FROM uCommerce_Customer
DELETE uCommerce_ProductRelation
DELETE uCommerce_ProductProperty
DELETE uCommerce_ProductDescriptionProperty
DELETE uCommerce_ProductDescription
DELETE uCommerce_CategoryProductRelation
DELETE uCommerce_PriceGroupPrice
DELETE FROM uCommerce_Product
-- Just double check things have gone
SELECT * FROM uCommerce_PurchaseOrder o
SELECT * FROM uCommerce_Product p
-- For safety's sake, run it in a transaction just in case you change your mind
ROLLBACK TRAN
-- When happy it works, uncomment this line and comment out the ROLLBACK
-- COMMIT TRAN
How not to do a "How did you hear about us" selector
Tuesday, March 12, 2013 9:00:00 AM (GMT Standard Time, UTC+00:00)
A frequent request we get when creating a checkout process or contact us form is to include a "How did you hear about us" select list. They generally end up looking like this one I screenshot from MoonPig:
We usually recommend against adding a "How did you hear about us" select list not only because there are more reliable ways of tracking this sort of information but because when someone is presented with a list like this we've found that the information (when completed) is usually a guess. Think about it, you go to a website, start ordering and then you're asked to remember where you first heard about them. Although you may have come from Google, you may have heard about them on TV first -which do you select (probably Google because that's the last thing you can remember but the advertising department would need to know it was TV that triggered it...).
The example from MoonPig above in my eyes is even worse as it doesn't keep it top level "TV" they try and break it down to channel. How many people are paying *that* much attention to the channel they're watching the advert on?
So what's the alternative?
The first thing to understand is that although there are better ways of finding out how people found you but there are far more inclusive. The two that we tend to recommend clients use are:
- Discount Codes
- Custom Landing Pages/Domain Names
By using these, you can track the sale/interaction back to the original source (it will of course include the odd user who's been referred but that's not an issue as you'd still want to attribute the sale to that original source).
So the next time you're thinking about adding a "How did you hear about us" select list to your site, have a think if there's a better route you can use.
How to setup passive FTP on a Windows Azure virtual machine
Wednesday, February 13, 2013 9:30:39 PM (GMT Standard Time, UTC+00:00)
This is more of a reminder for me than anything else. If you're looking for a great walkthrough on how to configure Passive FTP on a Windows Azure VM, check out the walkthrough from Ronald here -it got us up and running.
The thing that takes the time to write each time is the powershell script side of things so this time I made some notes:
- Run Get-AzurePublishSettingsFile to get your publishsettings file (save it somewhere easily accessible
- Run Import-AzurePublishSettingsFile d:\Azure.publishsettings
- Run the Get-AzureVM calls listed below (you can copy/paste in one go and powershell will work it's way through them -it can take a few minutes). If you're not sure what <ServiceName> and <Name> should be, these are the names you configured your VMs.
- To get the Service Name Run: Get-AzureVM
- To get the Name of the server run: Get-AzureVM -ServiceName '<ServiceName>' (from above)
Get-AzureVM -ServiceName '<ServiceName>' -Name '<Name>' | Add-AzureEndpoint -Name 'FTPData' -Protocol 'TCP' -LocalPort 20 -PublicPort 20 | Update-AzureVM
Get-AzureVM -ServiceName '<ServiceName>' -Name '<Name>' | Add-AzureEndpoint -Name 'FTP' -Protocol 'TCP' -LocalPort 21 -PublicPort 21 | Update-AzureVM
Get-AzureVM -ServiceName '<ServiceName>' -Name '<Name>' | Add-AzureEndpoint -Name 'FTPPassive00' -Protocol 'TCP' -LocalPort 7000 -PublicPort 7000 | Update-AzureVM
Get-AzureVM -ServiceName '<ServiceName>' -Name '<Name>' | Add-AzureEndpoint -Name 'FTPPassive01' -Protocol 'TCP' -LocalPort 7001 -PublicPort 7001 | Update-AzureVM
Get-AzureVM -ServiceName '<ServiceName>' -Name '<Name>' | Add-AzureEndpoint -Name 'FTPPassive02' -Protocol 'TCP' -LocalPort 7002 -PublicPort 7002 | Update-AzureVM
Get-AzureVM -ServiceName '<ServiceName>' -Name '<Name>' | Add-AzureEndpoint -Name 'FTPPassive03' -Protocol 'TCP' -LocalPort 7003 -PublicPort 7003 | Update-AzureVM
Get-AzureVM -ServiceName '<ServiceName>' -Name '<Name>' | Add-AzureEndpoint -Name 'FTPPassive04' -Protocol 'TCP' -LocalPort 7004 -PublicPort 7004 | Update-AzureVM
Get-AzureVM -ServiceName '<ServiceName>' -Name '<Name>' | Add-AzureEndpoint -Name 'FTPPassive05' -Protocol 'TCP' -LocalPort 7005 -PublicPort 7005 | Update-AzureVM
Get-AzureVM -ServiceName '<ServiceName>' -Name '<Name>' | Add-AzureEndpoint -Name 'FTPPassive06' -Protocol 'TCP' -LocalPort 7006 -PublicPort 7006 | Update-AzureVM
Get-AzureVM -ServiceName '<ServiceName>' -Name '<Name>' | Add-AzureEndpoint -Name 'FTPPassive07' -Protocol 'TCP' -LocalPort 7007 -PublicPort 7007 | Update-AzureVM
Get-AzureVM -ServiceName '<ServiceName>' -Name '<Name>' | Add-AzureEndpoint -Name 'FTPPassive08' -Protocol 'TCP' -LocalPort 7008 -PublicPort 7008 | Update-AzureVM
Get-AzureVM -ServiceName '<ServiceName>' -Name '<Name>' | Add-AzureEndpoint -Name 'FTPPassive09' -Protocol 'TCP' -LocalPort 7009 -PublicPort 7009 | Update-AzureVM
Get-AzureVM -ServiceName '<ServiceName>' -Name '<Name>' | Add-AzureEndpoint -Name 'FTPPassive10' -Protocol 'TCP' -LocalPort 7010 -PublicPort 7010 | Update-AzureVM
Get-AzureVM -ServiceName '<ServiceName>' -Name '<Name>' | Add-AzureEndpoint -Name 'FTPPassive11' -Protocol 'TCP' -LocalPort 7011 -PublicPort 7011 | Update-AzureVM
Get-AzureVM -ServiceName '<ServiceName>' -Name '<Name>' | Add-AzureEndpoint -Name 'FTPPassive12' -Protocol 'TCP' -LocalPort 7012 -PublicPort 7012 | Update-AzureVM
Get-AzureVM -ServiceName '<ServiceName>' -Name '<Name>' | Add-AzureEndpoint -Name 'FTPPassive13' -Protocol 'TCP' -LocalPort 7013 -PublicPort 7013 | Update-AzureVM
Get-AzureVM -ServiceName '<ServiceName>' -Name '<Name>' | Add-AzureEndpoint -Name 'FTPPassive14' -Protocol 'TCP' -LocalPort 7014 -PublicPort 7014 | Update-AzureVM
Prices including tax on uCommerce
Friday, December 14, 2012 4:03:30 PM (GMT Standard Time, UTC+00:00)
Most of our retailers price point their products to include tax i.e. a shirt that costs you £100 would include a proportion of VAT that the retailer would have to pay (at the moment about £16.67).
One frustration I've had for a while with uCommerce is that although you can opt to show prices with VAT (below), this just toggles the display and the calculations are just the same. This means the website administrator has to enter the prices excluding VAT (in this instance £83.33).
That's not only a laborious task, prone to error for the editor but as we found out on Staunton Moods the other day, can also cause rounding issues when ordering in multiples. On digging into it, the product prices are stored to 4 decimal places whereas the tax is only stored to 2 decimal places so we ended up with the following scenario:
?179.99 inc VAT @ 21% = ?147.9338842975207 exc VAT (or ?147.9339 when rounded to 4dp)
Total Without Tax ?147.9339 * 2 = ?295.8678
Tax Total ?31.07 * 2 = ?62.14
Grand Total ?295.8678 + ?62.14 = ?358.0078 (or ?358.01 when rounded)
Thankfully it's surprisingly easy to resolve in uCommerce. After a little a little playing around with IPricingService and ITaxService on simplygigabyte.co.uk (a blank demo store install) I managed to resolve the issue. The trick is not to override the ITaxService as this results in double calculations. Instead just roll out your own IPricingService like so:
namespace SimplyGigabyteCommon.Catalog
{
using System;
using UCommerce;
using UCommerce.Catalog;
using UCommerce.EntitiesV2;
public class TaxIncludedInPricePricingService : IPricingService
{
public Money GetProductPrice(Product product, PriceGroup priceGroup)
{
// Get the default pricing provider to get the product's price
var pricingService = new PricingService();
var incTax = pricingService.GetProductPrice(product, priceGroup);
// Calculate the tax part of the price
var tax = CalculateTax(priceGroup, incTax);
// To avoid rounding issues, subtract the tax value from the item's price
var excTax = incTax.Value - tax;
// Return the excluding tax part (the tax can be calculated as normal
// with the standard TaxService
return new Money(excTax, incTax.Culture, incTax.Currency);
}
private decimal CalculateTax(PriceGroup priceGroup, Money amount)
{
if (priceGroup == null)
throw new ArgumentNullException("priceGroup");
if (amount == null)
throw new ArgumentException("amount");
var incTax = amount.Value;
var taxRate = priceGroup.VATRate;
var priceTotal = 1 + taxRate;
var tax = (incTax / priceTotal) * taxRate;
return tax;
}
}
}
You'll then need to update your uCommerce configuration file to use your new IPricingService. In post v3 versions of uCommerce, the file is stored in /umbraco/ucommerce/configuration/Core.config.
Change:
<component id="PriceService" service="UCommerce.Catalog.IPricingService, UCommerce" type="UCommerce.Catalog.PricingService, UCommerce" />
To:
<component id="PriceService" service="UCommerce.Catalog.IPricingService, UCommerce" type="SimplyGigabyteCommon.Catalog.TaxIncludedInPricePricingService, SimplyGigabyteCommon" />
And that should be it -just make sure all your prices are updated to include tax in the admin area!
If you're running a pre v3 install then the logic is largely the same but instead of Money we've got PriceGroupPrice:
namespace StauntonMoods.Catalog
{
using System;
using UCommerce.Catalog;
using UCommerce.EntitiesV2;
public class TaxIncludedInPricePricingService : IPricingService
{
public PriceGroupPrice GetProductPrice(Product product, ProductCatalog catalog)
{
return this.GetProductPrice(product, catalog.PriceGroup);
}
public PriceGroupPrice GetProductPrice(Product product, PriceGroup priceGroup)
{
// Get the default pricing provider to get the product's price
var pricingService = new PricingService();
var incTax = pricingService.GetProductPrice(product, priceGroup);
// Calculate the tax part of the price
var tax = CalculateTax(priceGroup, incTax);
// To avoid rounding issues, subtract the tax value from the item's price
// you may also want to round the values here
var excTax = incTax.Price.Value - tax;
// Return the excluding tax part (the tax can be calculated as normal
// with the standard TaxService
return new PriceGroupPrice
{
Price = excTax,
PriceGroup = priceGroup,
Product = product
};
}
private decimal CalculateTax(PriceGroup priceGroup, PriceGroupPrice amount)
{
if (priceGroup == null)
throw new ArgumentNullException("priceGroup");
if (amount == null)
throw new ArgumentException("amount");
var incTax = amount.Price.Value;
var taxRate = priceGroup.VATRate;
var priceTotal = 1 + taxRate;
var tax = (incTax / priceTotal) * taxRate;
return tax;
}
}
}
Also, in pre v3 versions of uCommerce, the file is stored in /umbraco/ucommerce/configuration/Components.config.
Script to migrate an SVN repository to Git including commit history
Monday, November 26, 2012 11:30:54 AM (GMT Standard Time, UTC+00:00)
We started using source control years ago and have a load of projects still on SVN. As great as SVN was at the time, we've moved over to Git (if you've not already done so, you really should) but needed an easy way of migrating repositories over while maintaining the various commit history. Helpfully Dan put together a little script to automate the process and as Si was asking to do the same on the weekend I thought I'd share.
How To Use The Script
1. Download the SVN to Git migration script
Download bash_scripts_from_svn_to_git.zip
Extract the script in a new folder -we store the script in the GitoliteAdmin folder so we have it available if we need to migrate old repos but it can be stored anywhere you want.
2. Create a new user mapping file
This is a simple text file which includes the SVN username and the associated Git user details e.g.:
SVNUsername = Git User <email@domain.com>
username = Git User <example@domain.com>
Add each user mapping on a new line. You'll need to make sure that every user that has committed to the SVN repo has a mapping in this file.
Save the user mapping file as svn_users.txt in the same place as the SVN to Git migration script.
3. Create a temporary folder
This folder will be where the script downloads various repos to and does it's work. We've called ours "_SVN Transfer"
4. Create the Git Repo
Create an empty repository for the project on your Git server
5. Open Git Bash
Navigate to the temporary transfer folder you've just created. Assuming you called your folder "_SVN Transfer" and it was stored on D it would be:
6. Run the SVN to Git migration script
You'll need to run the migration script with three parameters e.g.:
bash_scripts_from_svn_to_git.sh <URL of the SVN Repo (including trunk)> <URL of the Git Repo> <Path of your Users Mapping File>
When running the script it'll probably look something like this:
/d/_GitoliteAdmin/tools/bash_scripts_from_svn_to_git.sh http://svn.domain.com/example/trunk ssh://git@git.domain.com/example /d/_GitoliteAdmin/tools/svn_users.txt
7. Accept any certificates
If you're running your old SVN repository over SSL then you may need to accept a certificate. If that's the case, just accept them permanently (P).
8. Wait for the migration script to complete (it can take a while)
9. Clone the remote repo
It should pull down as normal but include a full history.
Let me know how you get on.
The ultimate urlReplacing character list for Umbraco
Friday, November 09, 2012 8:52:00 AM (GMT Standard Time, UTC+00:00)
If you're not already familiar with the built in character replacing functionality for urls in Umbraco then I highly recommend you check out the umbracoSettings.config file's urlReplacing node section:
urlReplacing: List of characters which will be replaced in generated urls. This ensures that urls does not contain characters that search engines or browsers do not understand. Umbraco comes with a predefined set of characters and you can add your own
One thing I find we often forget to update is the default list of characters -which isn't that conclusive so I thought I would update our default and share it for others so without further ado, here's the list:
<urlReplacing removeDoubleDashes="true">
<char org=" ">-</char>
<char org="!"></char>
<char org="#"></char>
<char org="%">percent</char>
<char org="&">and</char>
<char org=">"></char>
<char org="<"></char>
<char org="""></char>
<char org="'"></char>
<char org="("></char>
<char org=")"></char>
<char org="*">star</char>
<char org="+">plus</char>
<char org=","></char>
<char org="."></char>
<char org="/"></char>
<char org=":"></char>
<char org=";"></char>
<char org="="></char>
<char org="?"></char>
<char org="@">at</char>
<char org="["></char>
<char org="]"></char>
<char org="^"></char>
<char org="_"></char>
<char org="`"></char>
<char org="{"></char>
<char org="}"></char>
<char org="¦"></char>
<char org="¬"></char>
<char org="ß">ss</char>
<char org="ä">ae</char>
<char org="Ä">ae</char>
<char org="å">aa</char>
<char org="æ">ae</char>
<char org="ö">oe</char>
<char org="Ö">oe</char>
<char org="ø">oe</char>
<char org="ü">ue</char>
<char org="-">-</char>
<char org="'"></char>
<char org="'"></char>
<char org="$">USD</char>
<char org="£">GBP</char>
<char org="?">EUR</char>
</urlReplacing>
I hope this is of use to someone. If you have ones that I've missed please let me know and I'll get them added. In some ways it would be nice if this was a regex rather than a character replace. Maybe that's a commit to the core I would look at one day.
Update: It would appear that my blogging engine/syntax highlighter is causing issues, the last rule should be a Euro symbol (?) and the quotes need to be encoded for XML e.g. ". Thanks @jbclarke and @greystate for spotting those
Do you know where your company’s skills are lacking?
Tuesday, October 30, 2012 9:58:51 AM (GMT Standard Time, UTC+00:00)
We're recruiting again and to work out what type of recruit we would benefit from the most, we decided to try and assess our strengths and weaknesses. It was a really useful exercise and meant that we were quickly able to identify which areas we should increase capacity in.
I am over-simplifying the skillsets a touch here, but when we were assessing what we use to create our websites, we boiled it down to:
- Design
- HTML/CSS (to be fair, this could be split into two)
- JavaScript (annotated below as jQuery)
- ASP.Net, ASP.Net MVC, PHP (or other dynamic language)
- C# / VB
- SQL (the database interaction)
We did this so we could map out the skillsets we had internally and where we would gain the most benefit in our next employee (we're hiring again if you're interested, get in touch). It turns out that our skills internally are fairly well balanced at the moment:
Looking at that however it's evident that we're a little light on the frontend side of things (HTML/CSS/JavaScript). That's not necessarily a bad thing as it's often quicker than the more codey aspects, so one developer can keep a couple of backend developers going. Another thing that I've over-simplified in this chart is the level each resource has so it may be worth factoring that in as well.
So what did we conclude? Looking at that, I'd say we're looking for someone who is a little more backend based.
How does your company's skillset look?
Track Google Analytics and Google AdWords conversions on click with jQuery
Wednesday, October 24, 2012 5:10:55 PM (GMT Daylight Time, UTC+01:00)
Today we needed to track conversions in Google Analytic and Google AdWords when a user clicked a button. There are a fair few posts around with different ways to do this but as it's something we've needed to do before so I thought I'd wrap it up into a quick jQuery plugin.
I've popped the script below for you and on github as a gist here: https://gist.github.com/3946934
Please note: To track Google Analytics, you will need to make sure that the standard Google Analytics tracking code (the new version) is included on your page.
The jQuery Plugin
/*!
* Conversion Tracker: a jQuery Plugin that tracks an event in both Google Analytics and Google AdWords
* @author: Tim Gaunt (@timgaunt)
* @url: http://blogs.thesitedoctor.co.uk/tim
* @documentation: http://blogs.thesitedoctor.co.uk/tim
* @published: 24/10/2012
* @license Creative Commons Attribution Non-Commercial Share Alike 3.0 Licence
* http://creativecommons.org/licenses/by-nc-sa/3.0/
*
* ----------------------
* Usage
* ----------------------
* $('a').trackConversion({ goalId: Your AdWords Id });
*
*/
;if(typeof jQuery != 'undefined') {
jQuery(function($) {
$.fn.extend({
trackConversion: function(options) {
var settings = $.extend({}, $.fn.trackConversion.defaults, options);
return this.each(function () {
var $$ = $(this),
o = $.metadata ? $.extend({}, settings, $$.metadata()) : settings;
$$.click(function(e){
if(getValue($$, o.trackAnalytics) && (typeof _gaq != 'undefined')){
_gaq.push(['_trackEvent', getValue($$, o.label), getValue($$, o.action), getValue($$, o.value)]);
}
if(getValue($$, o.trackAdWords) && getValue($$, o.goalId) > 0){
var randomNum = new Date().getMilliseconds();
var trackUrl = "http://www.googleadservices.com/pagead/conversion/" + getValue($$, o.goalId) + "/?random=" + randomNum + "&value=" + getValue($$, o.conversionValue) + "&label=" + getValue($$, o.label) + "&guid=ON&script=0&url=" + getValue($$, o.url);
$('<img />', {src:trackUrl, width:1, height:1 });
}
});
});
function getValue(link, value) {
return (typeof value !== "function") ? value : value(link);
}
}
});
$.fn.trackConversion.defaults = {
trackAnalytics:true,
trackAdWords:true,
url: function(){ return encodeURI(location.href); },
goalId: 0,
label: "Conversion",
action: "Click",
value: "",
conversionValue: 1
};
}(jQuery));
}