Retrieve the customer’s last address when logging into uCommerce
Tuesday, October 26, 2010 11:31:55 AM (GMT Daylight Time, UTC+01:00)
![header[1]](http://blogs.thesitedoctor.co.uk/tim/images/64eb161c0baa_93C0/header1.jpg "header[1]")
Probably one of the most common features of an ecommerce systems is to "retrieve my details" when logging in -after all that's why you create an account with the seller isn't it?
Out of the box, uCommerce has XSLT to retrieve the customer's last x addresses but one thing it didn't do was automatically re-assign the customer's details when logging in using the built in Umbraco membership code so we need to work around it ourselves -don't worry, it's not too hard (all the code is below for you).
Background
All customer addresses are stored in the uCommerce_Address table automatically, there should be one unique address per customer however if you're on an earlier release you may find you have several copies of the same address for each customer -this is a bug that's been sorted in v1.0.5.0 so upgrade if you can.
Now you'd be forgiven for thinking that you can just select the address from the uCommerce_Address table and then assign the id to the BillingAddressId property of your purchase order however if you do that, you'll find you get the error:
The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_uCommerce_PurchaseOrder_uCommerce_OrderAddress".
The conflict occurred in database "CommsReadyCMS", table "dbo.uCommerce_OrderAddress", column 'OrderAddressId'.
The statement has been terminated.
You'll get this because there is also a second table involved -uCommerce_OrderAddress. uCommerce_OrderAddress stores the actual address used throughout the order process incase the customer changes an address in the future, the order will always have the correct address.
The Solution
Working around this isn't actually too difficult as mentioned before. The easiest solution is to create a new User Control in Visual Studio (I'll call mine login.ascx) and hook into the LoggedIn event. Once logged in, get the Umbraco member and from that, get the customer's billing address.
There's one caveat that I found with uCommerce and that's the way it gets the address. At the moment, there is a function on customer "GetAddress", this is great however if you check out the code it calls, it actually gets the customer's first address from the database -rather than the last address used. I don't think this is a bug as in most cases the first address you enter is your main address. I'll blog separately about managing a default address within the members section.
The code below however retrieves the most recently added address from the database
Login.ascx
<asp:literal runat="server" ID="litLoggedIn" />
<asp:literal runat="server" ID="litLoggedOut" />
<asp:Login runat="server" id="lgnForm" CssClass="checkout-details"
DisplayRememberMe="false" TitleText="" OnLoggedIn="lgnForm_LoggedIn"
UserNameLabelText="Email Address" />
Login.ascx.cs
protected void lgnForm_LoggedIn(object sender, EventArgs e)
{
//If the user has a basket, wire up the shipping address with their last order details
var basket = SiteContext.Current.OrderContext.GetBasket(true);
if (basket != null)
{
//Get the customers current order
var po = basket.PurchaseOrder;
//Look for a shipping address
var add = po.GetBillingAddress();
//We only need to assign the address if there isn't already one assigned to this order
if (add == null)
{
//Get the customer who's just logged in
var mem = Membership.GetUser(lgnForm.UserName);
//To be safe check that we have a member
if (mem != null)
{
//Find the customer
var customer = Customer.ForUmbracoMember(Convert.ToInt32(mem.ProviderUserKey));
if (customer != null)
{
//Get the customer's most recent address
var previousAddress = customer.Addresses.ToList().LastOrDefault(a => a.AddressName == "Billing");
//If you want to get the customer's first address just uncomment this line
//var previousAddress = customer.GetAddress("Billing");
//Populate the billing address with the address)
if (previousAddress != null)
{
OrderAddress address = new OrderAddress
{
FirstName = previousAddress.FirstName,
LastName = previousAddress.LastName,
EmailAddress = previousAddress.EmailAddress,
PhoneNumber = previousAddress.PhoneNumber,
MobilePhoneNumber = previousAddress.MobilePhoneNumber,
CompanyName = previousAddress.CompanyName,
Line1 = previousAddress.Line1,
Line2 = previousAddress.Line2,
PostalCode = previousAddress.PostalCode,
City = previousAddress.City,
State = previousAddress.State,
Attention = previousAddress.Attention,
CountryId = previousAddress.CountryId,
AddressName = "Billing",
OrderId = new int?(po.OrderId)
};
//Store the address in the database
address.Save();
//Assign the address to the purchase order
po.BillingAddressId = new int?(address.OrderAddressId);
//Save the purchase order (shopping cart)
po.Save();
}
}
}
}
}
}
Delete all UCommerce baskets older than x days
Monday, October 04, 2010 10:14:31 AM (GMT Daylight Time, UTC+01:00)
After my last UCommerce post on how to delete test orders and baskets from UCommerce, Søren suggested I extended the delete all baskets code to take into account when it was created. As my last code was relating to deleting test orders/baskets (and so would want to get rid of them all), I decided to post this one separately.
Delete all baskets older than x days
To use this, all you need to do is change the @addedBefore parameter to whatever date/time you want (or just adjust the –7 which represents seven days in the past.
--Delete all carts purchaseorders and associated data within x days
DECLARE @addedBefore smalldatetime
--By default the script deletes everything older than 7 days
SET @addedBefore = DATEADD(dd, -7, GETDATE())
BEGIN TRAN
UPDATE uCommerce_PurchaseOrder SET BillingAddressId = NULL WHERE OrderNumber IS NULL AND CreatedDate <= @addedBefore
DELETE a FROM uCommerce_Shipment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE a FROM uCommerce_OrderAddress a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE a FROM uCommerce_OrderProperty a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE a FROM uCommerce_OrderLine a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE a FROM uCommerce_Payment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE a FROM uCommerce_OrderStatusAudit a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE FROM uCommerce_PurchaseOrder WHERE OrderNumber IS NULL AND CreatedDate <= @addedBefore
--Uncomment this
--COMMIT TRAN
--And comment out this
ROLLBACK TRAN
Deleting test orders and baskets from uCommerce
Friday, October 01, 2010 12:53:43 PM (GMT Daylight Time, UTC+01:00)
Although Søren has posted a helpful post on how to delete entire purchase orders from the database here, we needed something a little less “all or nothing” so put the below together.
Delete a specific order id
--Delete purchaseorders and associated data based on order id
DECLARE @OrderNumber nvarchar(50)
SET @OrderNumber = 'TEST-40'
BEGIN TRAN
UPDATE a SET ShipmentId = NULL FROM uCommerce_OrderLine a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE OrderNumber IS NULL
UPDATE uCommerce_PurchaseOrder SET BillingAddressId = NULL WHERE OrderNumber = @OrderNumber
DELETE a FROM uCommerce_Shipment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE a FROM uCommerce_OrderAddress a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE a FROM uCommerce_OrderProperty a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE a FROM uCommerce_OrderLine a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE a FROM uCommerce_Payment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE a FROM uCommerce_OrderStatusAudit a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE FROM uCommerce_PurchaseOrder WHERE OrderNumber = @OrderNumber
--TODO: Expand this so it checks for other orders
--DELETE a FROM uCommerce_Address a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
--DELETE a FROM uCommerce_Customer a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
--Uncomment this
--COMMIT TRAN
--And comment out this
ROLLBACK TRAN
Delete all baskets
--Delete all carts purchaseorders and associated data
BEGIN TRAN
UPDATE a SET ShipmentId = NULL FROM uCommerce_OrderLine a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE OrderNumber IS NULL
UPDATE uCommerce_PurchaseOrder SET BillingAddressId = NULL WHERE OrderNumber IS NULL
DELETE a FROM uCommerce_Shipment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE a FROM uCommerce_OrderAddress a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE a FROM uCommerce_OrderProperty a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE a FROM uCommerce_OrderLine a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE a FROM uCommerce_Payment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE a FROM uCommerce_OrderStatusAudit a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE FROM uCommerce_PurchaseOrder WHERE OrderNumber IS NULL
--TODO: Expand this so it checks for other orders
--DELETE a FROM uCommerce_Address a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.NULL = @NULL
--DELETE a FROM uCommerce_Customer a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.NULL = @NULL
--Uncomment this
--COMMIT TRAN
--And comment out this
ROLLBACK TRAN
Update: At the request of Søren, I’ve altered the delete all baskets post so it allows you to delete all baskets older than a given date, see: Delete all UCommerce baskets older than x days