Making the web work. Together.

Better Google Base in nopcommerce

Posted by Ed on 3 February 2011 | 0 Comments

Tags: , , , , , , ,

Following a read of a topic on the nopCommerce forum, and my own research into changes at Google for base/Merchant Centre, I've come to a few conclusions about the feed from nopCommerce and looking at ways to improve it.

I'm paralleling this blog with the work on a nopCommerce 1.6 site, but it should be fairly close to the requirements for earlier versions. I will then be applying it to a nopCommerce 1.8 site, which is similar in structure, but has several library changes and has introduced a lot more interfaces and other things (as .NET 4/VS2010 allows) that may confuse non-developers, so I'll do some notes on that when I get round to completing it.

The intention of this addition is to not be too fancy, make it meaningful and get it working in a few hours. You can really go to town on the features and functionality here, but all I'm really interested in doing is integrating a set of values that can be selected and applied to a category, then applied to a base feed. There will be loads of extras to add, like multiple product_types per category. There will be better places to put things and better ways to do this, but as long as it fits to nopCommerce programming style and gets info into Google base per product, that's enough for now.

It's likely that for v1.9, this will all be added to nopCommerce by default (they seem to be pretty good at adding things to new versions that I've added to older ones!) as IMHO, froogle/base is one of the most important aspects of selling products on-line.

Now, on with the show.

Get the taxonomy types download from [url=http://www.google.com/support/merchants/bin/answer.py hl=en&answer=160081/]here[/url].

I got the text version, as I'm only going to add the data as rows, like 'Arts & Entertainment > Collectibles > Sports Collectibles > Sports Trading Cards > Soccer Trading Cards'  in a single field.

Ok, lets import the data. I'm using SQLServer 2008 r2, which has a nice DTS (those were the days) style import feature. Right click on the nop db and select 'Tasks > Import Data'.

Select the file to import and just follow the wizard. I called the new table [Nop_GoogleProductTypes] but call it what you like.. If you have a problem with importing the text, just rename it as csv , open/save as excel (so you get a single column sheet) and import this. You'll also need to add an ID field to the table, and set this as the Identity field.

Ok, so we have the product_types. What is the best way to add these to the products and where is the best place to put the data

My thoughts are that this should sit at the category level. This seems the most appropriate way to make the setting specific without being a nightmare to manage. And it's probably the most logical, but since when was that important :o

If you haven't extended a class object in nopCommerce before, it's a bit scary. Once you have got familiar with the structure of the code, it's really not that bad (apart from product variant attribute combinations, but that's another story!) and is mostly uniform in structure, so you get used to it pretty quickly.

What we are going to do, is add an identifier property for the product type (the ID of the newly imported product_type) to the category class, then add a custom property which will require a new but very simple object called 'GoogleProductType' or similar, then add the update/add fields to the existing nopCommerce admin area. We don't really need to add any manager classes for the new product types, but it'll need a collection object. If we add it to an existing class folder, we'll also avoid having to add a new provider.

So with this in mind, I'm going to add this to the categories folder. It's probably the best place, as it is only related to [in myinterpretation] categories.

Navigate to Solution > Libraries > Nop.BusinessLogic > Categories, right click on the categories folder and select Add > New Item. Add a new class called 'GoogleProductType.cs'.

Change the class definition to: public partial class GoogleProductTypes : BaseEntity. For completeness, I like to add a summary and regions as per nopCommerce standards, but it won't make the program work any better, so it isn't essential you do to.

Add a class initialiser inside the class definition.


        public GoogleProductTypes()
        {

        }

Add 2 public properties, one for GoogleProductTypeID and one for GoogleProductTypeName, so your completed calss looks like:

using System;
using System.Collections.Generic;
using System.Text;

namespace NopSolutions.NopCommerce.BusinessLogic.Categories
{    
    /// <summary>
    /// Represents a GoogleProductType
    /// </summary>
    public partial class GoogleProductTypes : BaseEntity
    {
        #region Ctor
        /// <summary>
        /// Creates a new instance of the Category class
        /// </summary>
        public GoogleProductTypes()
        {

        }
        #endregion

        #region Properties

        /// <summary>
        /// Gets or sets the GoogleProductType identifier
        /// </summary>
        public int GoogleProductTypeId { get; set; }

        /// <summary>
        /// Gets or sets the GoogleProductType name
        /// </summary>
        public string GoogleProductTypeName { get; set; }


        #endregion

        #region Custom Properties

        #endregion
    }
}

Now we need a collection class. Create a new class as above, called 'GoogleProductTypeCollection.cs'.

Collection classes are pretty boring these days, so just make it look like:

using System;
using System.Collections.Generic;
using System.Text;

namespace NopSolutions.NopCommerce.BusinessLogic.Categories
{
    /// <summary>
    /// Represents a GoogleProductType collection
    /// </summary>
    public partial class GoogleProductTypeCollection : BaseEntityCollection<GoogleProductTypes>
    {

    }
}

For the interested amongst you, the magic in these collections is done by inheriting from BaseEntityCollection and passing the object type. All the list add/remove stuff that does the work is referenced directly from in there.

Now this is the bit that probably makes most people who try to extend nopCommerce give up. It's the way nopCommerce references objects of type by expressing them in several places, in a seemingly confusing manner.

What we have done so far is create a partial class (and a collection for it) which means we will be defining other bits of this class structure elsewhere. See[url= http://msdn.microsoft.com/en-us/library/wa80x488%28v=vs.80%29.aspx]this[/url] for a better explanation than I can give.

Next, we go to Nop.DataAccess > Categories and add a 'DBGoogleProductType.cs' class. This is pretty much identical to the class we added to Categories, so make sure it looks like:

using System;
using System.Collections.Generic;
using System.Text;

namespace NopSolutions.NopCommerce.DataAccess.Categories
{
    /// <summary>
    /// Represents a GoogleProductType
    /// </summary>
    public partial class DBGoogleProductType : BaseDBEntity
    {
        #region Ctor
        /// <summary>
        /// Creates a new instance of the Category class
        /// </summary>
        public DBGoogleProductType()
        {

        }
        #endregion

        #region Properties

        /// <summary>
        /// Gets or sets the GoogleProductType identifier
        /// </summary>
        public int GoogleProductTypeId { get; set; }

        /// <summary>
        /// Gets or sets the GoogleProductType name
        /// </summary>
        public string GoogleProductTypeName { get; set; }


        #endregion
    }
}

Next add a collection for this, called 'DBGoogleProductTypeCollection.cs'. It'll look like:

using System;
using System.Collections.Generic;
using System.Text;

namespace NopSolutions.NopCommerce.DataAccess.Categories
{
    /// <summary>
    /// Represents a GoogleProductType collection
    /// </summary>
    public partial class DBGoogleProductTypeCollection : BaseDBEntityCollection<DBGoogleProductType>
    {

    }
}

Next, we need to add our product type to the category. This will actually give you a good idea of how nopCommerce objects knit together, as well as allowing you to do something useful with it.

Open the Category.cs file and add:

public int GoogleProductType { get; set; }

public string GoogleProductTypeText { get; set; }

To the properties region at the end. The reason for 2 fields is that we wand to get the id of the google product_type, but also to be able to add a free-form value of our own to append.

Now add the same 2 fileds to the DBCategory.cs' file, in the same place.

Now we need  to add the 'custom' GoogleProductType object to the category. This might not seem necessary now, but when we use the category later in the actual feed, we'll be thankful we did. There is a fair bit of seemingly wierd code to add, but it's all necessary, so please bear with me.

If you have done this before, you'll know it's a bit of a wild journey through some odd places to add wierd code.

First, we need to add a method or two in the CategoryManager.css class. We need to get all the Google Product Types for adding to a drop down for selection, and also to return a single one for a category to call as a custom property.

Open DBCategoryProvider.cs and add:

public abstract DBGoogleProductType GetGoogleProductType(int GoogleProductTypeID);

...at the end of the methods already in there.

In CategoryManager.cs, add a method with the same name, like:

        public static GoogleProductType GetGoogleProductType(int GoogleProductTypeID)
        {
            var dbitem = DBProviderManager<DBCategoryProvider>.Provider.GetGoogleProductType(GoogleProductTypeID);
            var productType = DBMapping(dbitem);

// More code to go here...
        }

This will give a few errors, but we'll deal with them as we go.

Now we need to add a DBMapping to tell the program which DB field matches which object property.

So at the end of the #region Utilities section, add a dbmapping like:

        private static GoogleProductType DBMapping(DBGoogleProductType dbItem)
        {
            if (dbItem == null)
                return null;

            var item = new GoogleProductType();
            item.GoogleProductTypeId = dbItem.GoogleProductTypeId;
            item.GoogleProductTypeName = dbItem.GoogleProductTypeName;

            return item;
        }

This matches fields in the partial classes together later on when we have data from the db ready to build our object.

Next (I know, but please be patient!) we need to add the method signature from theDBCategoryProvider to the SQLCategoryProvider.cs file (Found in Nop.DataAccess.SQLServer).

        public override DBGoogleProductType GetGoogleProductType(int GoogleProductTypeID)
        {
            DBGoogleProductType item = null;
            Database db = NopSqlDataHelper.CreateConnection(_sqlConnectionString);
            DbCommand dbCommand = db.GetStoredProcCommand("Nop_GoogleProductTypeGet");
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    item = GetGoogleProductTypeFromReader(dataReader);
                }
            }

            return item;
        }

Then add a method to get the data from the reader:

        private DBGoogleProductType GetGoogleProductTypeFromReader(IDataReader dataReader)
        {
            var item = new DBGoogleProductType();
            item.GoogleProductTypeId = NopSqlDataHelper.GetInt(dataReader, "GoogleProductTypeID");
            item.GoogleProductTypeName = NopSqlDataHelper.GetString(dataReader, "GoogleProductType");
            return item;
        }



In Category.cs, add:

        public GoogleProductType GoogleProductType
        {
            get
            {
                return CategoryManager.GetGoogleProductType(this.GoogleProductTypeID);
            }
        }

Which will get the underlying object from the product type id.

Now we need to reflect this new information in the database for categories, and add fields to the presentation layer in the admin section.

So, in Nop_Categories in the database, add 2 new fields. One called something like GoogleProductTypeID, type INT, one called GoogleProductType, type NVARCHAR(255). This will allow us to add a default Google Product Type, and also append our own bits to the taxonomy.

We now need to add methods to return the GoogleProductTypes in a collection that we can add to our Category Admin page(s).

To add the methods to get a collection of GooglePRoductTypes, we need to duplicate the same process as we added for a single object, so in the CategoryManager.cs file, add a new method below the GetGoogleProductType() method we created earlier, like this:

        public static GoogleProductTypeCollection GetGoogleProductTypes()
        {
            var dbCollection = DBProviderManager<DBCategoryProvider>.Provider.GetGoogleProductTypes();
            var productTypeCategoryCollection = DBMapping(dbCollection);

            return productTypeCategoryCollection;
        }

You'll get erros, but don't worry, we'll fix them now.

In DBCategoryProvider.cs, add the following abstract method definition at the end, just after the one we added earlier.

public abstract DBGoogleProductTypeCollection GetGoogleProductTypes();

Now back to the CategoryManager class, we need to add a DBMapping for the collection, so just below the GoogleProductType DBMapping we added earlier, add:

        private static GoogleProductTypeCollection DBMapping(DBGoogleProductTypeCollection dbCollection)
        {
            if (dbCollection == null)
                return null;

            var collection = new GoogleProductTypeCollection();
            foreach (var dbItem in dbCollection)
            {
                var item = DBMapping(dbItem);
                collection.Add(item);
            }

            return collection;
        }

Ok, so far, so good. We have fixed the errors, so all the methods match correctly. We added the method definition to the DBCategoryProvider.cs file, so we now need to add the actual method to the SQLCategoryProvider.cs file to get the stuff out of the DB and into the app.

Now create another stored proc called something like Nop_GoogleProductTypesGet and then add this method at the end of the SQLCategoryProvider.cs file:
        
public override DBGoogleProductTypeCollection GetGoogleProductTypes()
        {
            var result = new DBGoogleProductTypeCollection();
            Database db = NopSqlDataHelper.CreateConnection(_sqlConnectionString);
            DbCommand dbCommand = db.GetStoredProcCommand("Nop_GoogleProductTypesGet");

            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    var item = GetGoogleProductTypeFromReader(dataReader);
                    result.Add(item);
                }
            }

            return result;
        }

(Note: I'm not adding the stored proc details here. They are pretty simple selects, so you should be able to figure out what to do with them).

Ok, time for a deep breath and a think about what we have done so far.

We've added a new table to the db for Google Product Types, added new fields to our Category object, added a new object type to represent GoogleProductTypes, made it all nopCommerce stylee and prepared some methods to retrieve and use this new info.

Ok, so let's do some actual work that people can see. (I often explain the iceberg principle, but it never makes sense to anyone!).

I'm going to add the 2 new fields to the SEO panel in the Category Details page. We could put it elsewhere, make our own panel or add it somehere odd, but the SEO panel works for me.

If you check in CategoryDetails.ascx, you'll see we need to edit CategorySEO.ascx. So open this in the editor.

In the table where the field values are listed, add 2 new rows, one for the dropdown and one for the text field. You should add all the localisation tokens etc, but I've essentially got 2 new controls. A DropDownList called ddlGoogleProductType and a textbox called txtGoogleProductType.

Now press F7 to see the codebehind.

We are interested in 3 things here. Getting the new data fields out of the DB and into the controls, filling the dropdownlist and writing data back in.

NOTE: There are references to Localised content here, but for the purpose of this document, I'm going to ignore them and just make amends to the SaveInfo() method. The localisation functionality is important to nopCommerce, but for now (as a proof of concept) I'm keeping it simple.

Now add:

                this.txtGoogleProductType.Text = category.GoogleProductTypeText;

To the BindData() method, after the other calls to fill TextBoxes.

We'll be adding the dropdownlist stuff too now. Add this method to the CategorySEO.cs file:

        protected void FillDropDown()
        {
            ddlGoogleProductType.DataSource = CategoryManager.GetGoogleProductTypes();
            ddlGoogleProductType.DataTextField = "GoogleProductTypeName";
            ddlGoogleProductType.DataValueField = "GoogleProductTypeID";
            ddlGoogleProductType.DataBind();
        }

Now add FillDropDown(); to the BindData() method. We will now 'find' the selected product type (if available).

Next, add:

                ddlGoogleProductType.Items.Insert(0, "");

...to the BindData() method. This allows us to use 0 as the type (And we could also add a default value of zero to the GoogleProductID field in Nop_Category in the database).

Now add:

                if (category.GoogleProductTypeID != null)
                {
                    if (category.GoogleProductTypeID > 0)
                    {
                        ddlGoogleProductType.Items.FindByValue(category.GoogleProductTypeID.ToString()).Selected = true;
                    }
                }

...to select the stored value. (It'll be null at the moment, because we've not added the field to the DBMapping for Category. It should be in the stored proc, because nopCommerce generally uses SELECT *.

So if we have a look, we see this uses GetCategoryByID() from the Category Manager.cs file, so we need to update the GetCategoryFromReader() method by adding:

            item.GoogleProductType = NopSqlDataHelper.GetInt(dataReader, "GoogleProductTypeID");
            item.GoogleProductTypeText = NopSqlDataHelper.GetString(dataReader, "GoogleProductType");

This will add the field values to the object.

EDIT: Some SP's aren't using SELECT *, so we need to add our new fields to them. The ones I've updated are:

[Nop_CategoryLoadAll]
[Nop_CategoryLoadByPrimaryKey]

I've just added the new field names to the selects. Namely:
,
        c.GoogleProductType,
        c.GoogleProductTypeID

Ok, now in the SaveInfo() method, add 2 new parameters to the UpdateCategory() method call, ', ddlGoogleProductType.SelectedValue, txtGoogleProductType.Text'.

Navigate to the method in CategoryManager and add the new parameters to the list, eg: ', int GoogleProductTypeID, string GoogleProductType'.

Then in the method, add them to the Provider.UpdateCategory() method, like ', GoogleProductTypeID, GoogleProductType'.

Now open DBCategoryProvider.cs and update the UpdateCategory() method by adding ', int GoogleProductTypeID, string GoogleProductType' to the parameters.

Next, we need to edit the UpdateCategory() method in SQLCategoryProvider.cs. Again, we add the 2 new parameters, like: ', int GoogleProductTypeID, string GoogleProductType'.

We then add 2 new parameters to the stored procedure parameter list and also to the stored proc itself. Add this to the UpdateCategoty() method:

            db.AddInParameter(dbCommand, "GoogleProductTypeID", DbType.Int32, GoogleProductTypeID);
            db.AddInParameter(dbCommand, "GoogleProductType", DbType.String, GoogleProductType);

Then add the 2 perameters and inserts in the stored procedure. (Nop_CategoryUpdate).

You'll also need to update the references to the UpdateCategory() method in MarkCategoryAsDeleted() and RemoveCategoryPicture(). Both of these are in the CategoryManager class. And also SaveInfo() in the CategoryInfo.ascx.cs file.

I also noticed I'd missed adding:

            item.GoogleProductTypeID = dbItem.GoogleProductType;
            item.GoogleProductTypeText = dbItem.GoogleProductTypeText;

to the Category DBMapping in CategoryManager(), so it was adding ok, but not returning. Ooops.

I also missed adding the parameter to GetGoogleProductType() in SQL CategoryProvider. Add

            db.AddInParameter(dbCommand, "GoogleProductTypeID", DbType.Int32, GoogleProductTypeID);

Under the DBCommand dbcommand = statemnt.

Ok, so now, if you run the app (and fix a few likely errors that occur!) you can add a Google Product Type to a category.

Now, [and at last, the point of all this!] we can add this information to our froogle feed.

We see a few nuances and other things we now need to think about. A product has one or many categories, so we need to add them all to this feed.

For now, I've added this to the FroogleService.cs file, just after the commented out reference to product_type:

                        string sout = "";
                        
                        foreach (ProductCategory c in product.ProductCategories)
                        {
                            if (c.Category.GoogleProductTypeID > 0)
                            {
                                if (sout.Length > 0)
                                {
                                    sout += ", ";
                                }
                                sout += "\"" + c.Category.GoogleProductType.GoogleProductTypeName + " " + c.Category.GoogleProductTypeText + "\"";
                            }
                        }

                        if (sout.Length > 0)
                        {
                            writer.WriteStartElement("g", "product_type", googleBaseNamespace);
                            writer.WriteCData(sout);
                            writer.WriteFullEndElement(); // g:brand
                        }

This basically builds a list of comma-separated values, in quotes, as per the spec, to account for multiple categories.

Hopefully, this will be helpful to someone, or at least give a leg-up when adding it to your site.

It is by no means a complete solution and in reality, it needs a lot more functionality and features before it's commercial strength.

This works ok for me, but I accept you may have problems, or I've made some [very likely] typos and omissions, so please ask if you need help.

Thanks for reading.

/ed.


Post your comment

Comments

No one has commented on this page yet.

RSS feed for comments on this page | RSS feed for all comments