Thread: Edit aspdnsf_GetProducts to sort by Price?

Has anyone ever tweaked the stored proc that entity.gridwithprices.xml.config uses to sort by price? (aspdnsf_GetProducts) I am trying to get the products for our sections to sort by price from low to high. I looked at the stored proc but it's WAY beyond me.

Yes,

you must create new stored procs and modify exsisting XML packages / create new ones.

The pagging system also must be modified so you will need the source code.

E.G.

http://www.louisianalighting.com/c-12-table-lamps.aspx

Actually not a terribly difficult modification. Near the bottom of the sproc there is an order by line

Code:

ORDER BY pf.rownum

just change to

Code:

ORDER BY pv.Price ASC

As always, you'll want to make sure you have backup of the database before changing these though. You could also just create a new stored procedure called something like aspdnsf_GetProductsSortByPrice that is the exact same as aspdnsf_GetProducts (except for the ORDER BY line) and then in the xmlpackage just change

Code:

exec dbo.aspdnsf_GetProducts

to

Code:

exec dbo.aspdnsf_GetProductsSortByPrice

that way none of the other areas of the storefront that use the stored procedure are affected and you don't inadvertently break the site by changing one of the core sprocs. Doing it in this manner will also allow for an easier migration path during upgrades from version to version; as a rule of thumb custom sprocs usually are not nuked.

sorry I thought you wanted a way to select order by price / name on the page.

yes if you just want 1 query you can just modify the main proc. George is right.

Well that did work but it looks like the pagination is screwed up. The first page is ordered by price, but then on page 2, the price starts over again. Page 2 is ordered by price, but the first price is lower then the last price that showed up on page 1.

I noticed that the pagination works off of a different query.

HTML Code:

<xsl:value-of select="aspdnsf:PagingControl($BaseURL, $CurrentPage, /root/Products2/Product/pages)" disable-output-escaping="yes" />

It works off of Products2 instead of Products. So in changing the sort order in aspdnsf_GetProducts did it through off the pagination? Is there something else I need to do?

Does anyone have any ideas of why the pagination would not be accurate when i edit the stored proc to sort by price? On the first page, the products are sorted by price, but when you click to page 2, the prices start out lower then the last price on page 1. For example page one:

page 1
$50, $100, $150, $500

Page 2
$100, $150, $500

Page two should only have prices $500 and above.

???

Sorry to jump in on someone else's thread, but I have a question about the same procedure.
I am looking to create several versions of this SPROC also, to sort in a variaety of ways. (Price ASC, Price DESC, Name, Popularity, etc.). I can create the different SPROCS without much difficulty.
I haven't played around with it yet, but my initial guess is that on the page_load event of Default.aspx I will need to check the request querystrings to determine whether the customer is viewing products within my sub-categories or not, and appropriately whether or not a "Sort By:" dropdown should be displayed. Once I have it displayed, does anybody know how to alter the XmlPackage to call the appropriate SPROC based on the selected value of the dropdown? (Much like the lighting site in the above post).
I am also curious to see what the response is regarding the pagination alterations needed.
Thanks in advance.

Hi, what we want is mmm.... well first the problem:

Our search works fine:

i.e. If i try "camera 882" or "882 camera" throw the same result, as well we have diferent type of cameras like camera 910, camera 812, camera 9892, etc. they're all in the results, but the sort is the problem, are sorted by Name and my search term ("camera 882") is in the 6th place, is there a way that in the list be in the first place the results that matching more than two words.?

Editing de aspdnsf_GetProducts or the xml?


We have something like:

with a search= "camera 882"

1-camera 6543
2-camera 4356
3-camera 23
4-camera 435
5-camera 43
6-camera 882

and we want

1-camera 882
2-camera 4356
3-camera 23
4-camera 435
5-camera 43
6-camera 6543

thanks...

Was anyone able to get this work without messing up the pagination ?

Yes...and we will be adding to the next release with a bit of AJAX to smooth things along. For those of you running on older versions using SkinBase, please send a request to support for the stored procedure and xmlpackage changes (the files are for 8012...but you can modify them for older versions if necessary). Please provide your order number (we'll need to verify an existing license before we can send) and make sure to reference this forum post so you don't get charged for a support incident. The current implementation supports Name (ascending/descending), Price (Low->High/High->Low), and Popularity (based on number of looks), though could be easily extended to provide additional sorting parameters.

The trick to executing this successfully is using case statements for the order by when inserting into the @productfilter temp table and not on the final select statement in the getProducts stored procedure; this way the rows will always be in exactly the same order when selecting a sub-set of data from the temp table to create the different pages.

Good job!

It would be nice to filter the products of a single category by Manufacturer,
adding another dropdown which shows only the manufacturers of the products in that particular category.



Any suggestion?

Thanks.

See this KB article... https://support.aspdotnetstorefront....barticleid=287

Originally Posted by AspDotNetStorefront - Dan

See this KB article... https://support.aspdotnetstorefront....barticleid=287

Thanks! This is exactly what I was looking for, ADNSF rox!!

At the moment I'm having some troubles.. I read in the KB:

The AppConfigs you will need to create are:

AllowEntityPageFiltering
AllowManufacturerFiltering
AllowProductTypeFiltering

Manufacturer filtering will display a manufacturer dropdown box on category and department pages, so customers can filter that category's products by manufacturer.

I created it, setted to TRUE and resetted the cache, but when I click on a category no dropdown appears..
while if I activate AllowEntityPageFiltering it seems to works, but I'm interested in manufacturers, not departments..

Can anyone check if this is a common problem?
Thanks!

manufacturer dropdown still missing...
don't know what to do..

here's a screenshot

Originally Posted by kaezr

manufacturer dropdown still missing...

bump

no way..

If you have the source please apply this modification:
Project: AspDotNetStorefrontCore
Class: XSLTExtensionBase.cs
Method: EntityPageFilterOptions

Look for this line:

Code:

                if ((m_AllowManufacturerFiltering) &&
                    (EntityName.Equals("MANUFACTURER", StringComparison.InvariantCultureIgnoreCase)))

And change it to:

Code:

                if ((m_AllowManufacturerFiltering) ||
                    (EntityName.Equals("MANUFACTURER", StringComparison.InvariantCultureIgnoreCase)))

That's it...

Originally Posted by AspDotNetStorefront Staff - Jao

If you have the source please apply this modification

Great!! Thank you very much!

I wanted the manufacturer filter to be displayed always when a category has no subcategories, so i modified EntityPageFilterOptions this way:
(i'm not interested in other filters, so i deleted that part)

Code:

public virtual string EntityPageFilterOptions(string sEntityName, String sEntityID, String sSectionFilterID, String sCategoryFilterID, String sManufacturerFilterID, String sProductTypeFilterID)
        {
            InputValidator IV = new InputValidator("EntityPageFilterOptions");
            String EntityName = IV.ValidateString("EntityName", sEntityName);
            int EntityID = IV.ValidateInt("EntityID", sEntityID);
            int SectionFilterID = IV.ValidateInt("SectionFilterID", sSectionFilterID);
            int CategoryFilterID = IV.ValidateInt("CategoryFilterID", sCategoryFilterID);
            int ManufacturerFilterID = IV.ValidateInt("ManufacturerFilterID", sManufacturerFilterID);
            int ProductTypeFilterID = IV.ValidateInt("ProductTypeFilterID", sProductTypeFilterID);
            StringBuilder results = new StringBuilder("");

            bool m_AllowEntityPageFiltering = AppLogic.AppConfigBool("AllowEntityPageFiltering") &&
                                                (EntityName.Equals("CATEGORY", StringComparison.InvariantCultureIgnoreCase) ||
                                                 EntityName.Equals("SECTION", StringComparison.InvariantCultureIgnoreCase) ||
                                                 EntityName.Equals("MANUFACTURER", StringComparison.InvariantCultureIgnoreCase));

            bool m_AllowManufacturerFiltering = AppLogic.AppConfigBool("AllowManufacturerFiltering");
            bool m_AllowProductTypeFiltering = AppLogic.AppConfigBool("AllowProductTypeFiltering");

            string postbackurl = SE.MakeEntityLink(EntityName, EntityID, "");
            
	    if ((m_AllowManufacturerFiltering) &&
	        (EntityName.Equals("CATEGORY", StringComparison.InvariantCultureIgnoreCase)) &&
                (!AppLogic.CategoryEntityHelper.EntityHasSubs(EntityID))
               )
            {
                results.Append("<form id=\"FilterForm\" name=\"FilterForm\" method=\"GET\" action=\"" + postbackurl + "\">\n");
                String QS = CommonLogic.ServerVariables("QUERY_STRING");
                //results.Append("<a href=\"" + CommonLogic.GetThisPageName(false) + "?" + QS + CommonLogic.IIF(QS.Length == 0, "?", "&") + "resetfilters=true&entityname=" + EntityName + "&entityid=" + EntityID.ToString() + "\">RESET FILTERS</a>&nbsp;&nbsp;&nbsp;&nbsp;");
				results.Append("<br />" + AppLogic.GetString("AppConfig.ManufacturerPromptSingular", ThisCustomer.SkinID, ThisCustomer.LocaleSetting) + ": <select size=\"1\" name=\"ManufacturerFilterID\" onChange=\"document.FilterForm.submit();\">\n");
                    results.Append("<OPTION VALUE=\"0\" " + CommonLogic.IIF(ManufacturerFilterID == 0, " selected ", "") + ">" + AppLogic.GetString("searchadv.aspx.9", ThisCustomer.SkinID, ThisCustomer.LocaleSetting) + " " + AppLogic.GetString("AppConfig.ManufacturerPromptPlural", ThisCustomer.SkinID, ThisCustomer.LocaleSetting) + "</option>\n");
                    EntityHelper ManufacturerHelper = AppLogic.LookupHelper("Manufacturer");
                    String MfgSel = ManufacturerHelper.GetEntitySelectList(0, String.Empty, 0, ThisCustomer.LocaleSetting, false);
                    // mark current Section:
                    MfgSel = MfgSel.Replace("<option value=\"" + ManufacturerFilterID.ToString() + "\">", "<option value=\"" + ManufacturerFilterID.ToString() + "\" selected>");
                    results.Append(MfgSel);
                    results.Append("</select>\n");
                    results.Append("&nbsp;&nbsp;");
			
			
                results.Append("</form>");
            }
            return results.ToString();
        }

Originally Posted by AspDotNetStorefront Staff - George

Yes...and we will be adding to the next release with a bit of AJAX to smooth things along. For those of you running on older versions using SkinBase, please send a request to support for the stored procedure and xmlpackage changes (the files are for 8012...but you can modify them for older versions if necessary). Please provide your order number (we'll need to verify an existing license before we can send) and make sure to reference this forum post so you don't get charged for a support incident. The current implementation supports Name (ascending/descending), Price (Low->High/High->Low), and Popularity (based on number of looks), though could be easily extended to provide additional sorting parameters.

The trick to executing this successfully is using case statements for the order by when inserting into the @productfilter temp table and not on the final select statement in the getProducts stored procedure; this way the rows will always be in exactly the same order when selecting a sub-set of data from the temp table to create the different pages.

Hello, I downloaded the file sorting.zip and ran the query file against the database and uploaded the other two files to the xmlpackages and stringresources folders. Now when I set a category to display sortgridwithprices I get an error. I touched the web.config file, is there anything else I need to do?

*edit, nevermind.. I needed to add the strings to the existing file instead of uploading the file itself.

What's the error you're getting?

Sorry about that post. I fixed that problem.

But I have another question.. how hard would it be to add a sort by date to this? I want my customers to be able to see the latest products added first.

Not really that complicated; however, there's already a page.recentadditions.xml.config built-in. You could use that instead.

ok i made another customization...
i want to show only the manufacturers for the products shown in the selected category, not the whole list.

i don't think this is good code, but i had to make it quickly, so if you have smarter ways please let me know!

i'm still missing one thing: i want to reset the filter everytime i load a category.
how can i do it?
ok i'm not the only one with this problem
http://forums.aspdotnetstorefront.co...ad.php?t=19615




this is my code, it works, but it's quick and dirt


file: ProductCollection.cs

Code:

public class ProductCollection : IDisposable 
    {
       ...
       private List<int> m_Manufacturers = new List<int>();
       ...
    }



...



public DataSet LoadFromDB(bool IncludeAll)
     {
       ...
       m_NumProducts = m_Products.Tables[0].Rows.Count;

            foreach (DataRow r in m_Products.Tables[0].Rows)
            {
                m_Manufacturers.Add(AppLogic.GetProductManufacturerID((int)r["ProductID"]));
            }

            return m_Products;
      }



...



public List<int> Manufacturers
        {
            get
            {
                return m_Manufacturers;
            }
        }

file: XSLTExtensionBase.cs

Code:

public virtual string EntityPageFilterOptions(string sEntityName, String sEntityID, String sSectionFilterID, String sCategoryFilterID, String sManufacturerFilterID, String sProductTypeFilterID)
        {
            
             ...



                EntityHelper ManufacturerHelper = AppLogic.LookupHelper("Manufacturer");
                String MfgSel = ManufacturerHelper.GetEntitySelectList(0, String.Empty, 0, ThisCustomer.LocaleSetting, false);

                    ProductCollection p = new ProductCollection();
                    p.AddEntityFilter(EntityName, EntityID);
                    p.LoadFromDB();

                    string tmp = "", res = "";
                    int i1 = 0, i2 = 0;

                    foreach (int m in p.Manufacturers)
                    {
                        tmp = MfgSel;
                        if (tmp.Contains("<option value=\"" + m + "\"") && !res.Contains("<option value=\"" + m + "\""))
                        {
                            i1 = tmp.IndexOf("<option value=\"" + m + "\"");
                            tmp = tmp.Substring(i1, tmp.Length - i1);
                            i2 = tmp.IndexOf("</option>");
                            res += tmp.Substring(0, i2 + "</option>".Length);
                        }
                    }

                    if(!res.Equals(""))
                    {
                        MfgSel = res;

                        // mark current Section:
                        MfgSel = MfgSel.Replace("<option value=\"" + ManufacturerFilterID.ToString() + "\">", "<option value=\"" + ManufacturerFilterID.ToString() + "\" selected>");
                        results.Append(MfgSel);
                    }

                    results.Append("</select>\n");
                    results.Append("&nbsp;&nbsp;");
			
			
/* deleted a lot of code i didn't need, see my previous post */


                results.Append("</form></div>");
            }
            return results.ToString();
        }

Originally Posted by kaezr

i'm still missing one thing: i want to reset the filter everytime i load a category.
how can i do it?
ok i'm not the only one with this problem
http://forums.aspdotnetstorefront.co...ad.php?t=19615

ok i simply commented out this line

Code:

AppLogic.SetCookie("ManufacturerFilterID", m_ManufacturerFilterID.ToString(), new TimeSpan(365, 0, 0, 0, 0));

in Web\App_Code\ShowEntityPage.cs

Hi,

How do I get the "Sort By" menu to appear? I can use the other filters (manufacturers, product type etc.) but I can't get the general sort menu to appear.

I have version 8.0.1.2 with source code. Does anyone know how I activate this menu?

Cheers,
Robert

Cant seem to keep this code to work, I keep getting an error relating to
= new List<int>():
Says List is not defined in the code. Is this a custom class you added?

Thanks in advance.

Originally Posted by kaezr

ok i made another customization...

this is my code, it works, but it's quick and dirt


file: ProductCollection.cs

Code:

public class ProductCollection : IDisposable 
    {
       ...
       private List<int> m_Manufacturers = new List<int>();
       ...
    }



...



public DataSet LoadFromDB(bool IncludeAll)
     {
       ...
       m_NumProducts = m_Products.Tables[0].Rows.Count;

            foreach (DataRow r in m_Products.Tables[0].Rows)
            {
                m_Manufacturers.Add(AppLogic.GetProductManufacturerID((int)r["ProductID"]));
            }

            return m_Products;
      }



...



public List<int> Manufacturers
        {
            get
            {
                return m_Manufacturers;
            }
        }

file: XSLTExtensionBase.cs

Code:

public virtual string EntityPageFilterOptions(string sEntityName, String sEntityID, String sSectionFilterID, String sCategoryFilterID, String sManufacturerFilterID, String sProductTypeFilterID)
        {
            
             ...



                EntityHelper ManufacturerHelper = AppLogic.LookupHelper("Manufacturer");
                String MfgSel = ManufacturerHelper.GetEntitySelectList(0, String.Empty, 0, ThisCustomer.LocaleSetting, false);

                    ProductCollection p = new ProductCollection();
                    p.AddEntityFilter(EntityName, EntityID);
                    p.LoadFromDB();

                    string tmp = "", res = "";
                    int i1 = 0, i2 = 0;

                    foreach (int m in p.Manufacturers)
                    {
                        tmp = MfgSel;
                        if (tmp.Contains("<option value=\"" + m + "\"") && !res.Contains("<option value=\"" + m + "\""))
                        {
                            i1 = tmp.IndexOf("<option value=\"" + m + "\"");
                            tmp = tmp.Substring(i1, tmp.Length - i1);
                            i2 = tmp.IndexOf("</option>");
                            res += tmp.Substring(0, i2 + "</option>".Length);
                        }
                    }

                    if(!res.Equals(""))
                    {
                        MfgSel = res;

                        // mark current Section:
                        MfgSel = MfgSel.Replace("<option value=\"" + ManufacturerFilterID.ToString() + "\">", "<option value=\"" + ManufacturerFilterID.ToString() + "\" selected>");
                        results.Append(MfgSel);
                    }

                    results.Append("</select>\n");
                    results.Append("&nbsp;&nbsp;");
			
			
/* deleted a lot of code i didn't need, see my previous post */


                results.Append("</form></div>");
            }
            return results.ToString();
        }

You might have forgotten to include this one on that class' preprocessor directive:

Code:

using System.Collections.Generic;

Yep that fixed it, works great thanks!

Major issue with this.

With all of this in place our get shipping options on the checkout1 fails to work.

EDIT:
Not an problem with the code seems one of my .cs files is missing something and when ever I build throwing my shipping.

I started searching the forums yesterday (sunday night) on how to implement "sorting" of products. I read this thread and submitted a ticket to support. Shortly after that Jao responded with the files needed to get started. This is just another great example of the ASPDNSF support.

Thanks for great open source software and excellent support. Keep up the good work.

Chaz.

We received the sql and xmlpackage from support. It is for 8.0.1.2. Has anyone converted it to 8.0.0.0? What is the main difference between the 2 versions?