Non-selling items Report

[William_firefold]

I would like to create a report which will list all products which have sold 0 units in the last month. The current ProductBreakdownSummary only generates reports for products that DO have sales. To my knowledge, it wont generate a 0 report.

Is there a way to sort through all products on the site and make a list of everything with no sales in the past X days?

[William M]

Taking this one step further gets you a list of all products and their sales for X period.

[William_firefold]

Just to be clear, this has to be independent of the inventory system. It has to be based just on the number of sales of the item in X days.

[heinscott]

I'm not going to get into a whole bunch of coding to make an "actual" report, but...
Just something simple to get you started, here is the code for a page that will just make a simple list of all products and numbers sold during the date range. You'll have to change the dates manually in the LoadForCriteria to get the report data you want. I would incorporate some kind of date date control and grid control if I was making this for my boss.
Hope this will get you started, okay, though.

Scott

Code: Select all

<%@ Page Language="C#" MasterPageFile="~/Admin/Admin.master" Title="Run A Query" Inherits="CommerceBuilder.Web.UI.AbleCommerceAdminPage" %>
<%@ Register TagPrefix="ComponentArt" Namespace="ComponentArt.Web.UI" Assembly="ComponentArt.Web.UI" %>
<%@ Register Assembly="CommerceBuilder.Web" Namespace="CommerceBuilder.Web.UI.WebControls" TagPrefix="cb" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System" %>

<script runat="server">

protected void Page_Load(object s, EventArgs e)
{
    Hashtable ht = new Hashtable();
    ProductCollection pc = ProductDataSource.LoadForStore("Name ASC");
    foreach (Order o in OrderDataSource.LoadForCriteria("OrderDate > '1/1/2009' AND OrderDate < '2/1/2009'"))
    {
        foreach (OrderItem oi in o.Items)
        {
            if (oi.Product != null)
            {
                if (ht.ContainsKey(oi.ProductId))
                {
                    ht[oi.ProductId] = AlwaysConvert.ToInt(ht[oi.ProductId]) + oi.Quantity;
                }
                else
                {
                    ht.Add(oi.ProductId, oi.Quantity);
                }
            }
        }
    }
    foreach (Product p in pc)
    {
        if (ht.ContainsKey(p.ProductId))
            Results.Text += p.Name + " **** " + ht[p.ProductId] + "<br>";
        else
            Results.Text += p.Name + " **** 0<br>";
    }
        
}
    
</script>

<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" Runat="Server">
    <asp:Label ID="Results" runat="server"></asp:Label>
</asp:Content>

[William_firefold]

Thanks, this is plenty for me to work with. I will post the complete report code once i get it working.

[William_firefold]

Here is the code for the report.
It will show all products with sales<X between 2 dates. It uses the calender picker control to pick dates.
Not extensively tested yet, let me know if anyone finds bugs.

Code: Select all

<%@ Page Language="C#" MasterPageFile="~/Admin/Admin.master" Title="Run A Query" Inherits="CommerceBuilder.Web.UI.AbleCommerceAdminPage" %>
<%@ Register TagPrefix="ComponentArt" Namespace="ComponentArt.Web.UI" Assembly="ComponentArt.Web.UI" %>
<%@ Register Assembly="CommerceBuilder.Web" Namespace="CommerceBuilder.Web.UI.WebControls" TagPrefix="cb" %>
<%@ Register Src="~/Admin/UserControls/PickerAndCalendar.ascx" TagName="PickerAndCalendar" TagPrefix="uc1" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System" %>

<script runat="server">
    protected void ProcessButton_Click(object sender, EventArgs e)
    {
		Generate();
    }
    protected void Generate()
{
	    DateTime fromDate = PickerAndCalendar1.SelectedDate;
        DateTime toDate = PickerAndCalendar2.SelectedDate;
		Hashtable ht = new Hashtable();
		ProductCollection pc = ProductDataSource.LoadForStore("Name ASC");
		String resultString="";

foreach (Order _order in OrderDataSource.LoadForCriteria("OrderDate > ' "+fromDate.ToString("M/d/y")+" ' AND OrderDate < ' "+toDate.ToString("M/d/y")+" ' "))		//Remember the single quotes
    {
        foreach (OrderItem oi in _order.Items)
        {
            if (oi.Product != null)
            {
                if (ht.ContainsKey(oi.ProductId))
                {
                    ht[oi.ProductId] = AlwaysConvert.ToInt(ht[oi.ProductId]) + oi.Quantity;
                }
                else
                {
                    ht.Add(oi.ProductId, oi.Quantity);
                }
            }
        }
    }
	resultString +="<table cellspacing=\"0\" border=\"0\">";
    foreach (Product p in pc)
    {
		resultString +="<tr>";
		
        if (ht.ContainsKey(p.ProductId)){
			if(AlwaysConvert.ToInt(ht[p.ProductId]) <AlwaysConvert.ToInt(maxQTY.Text)){
				resultString +="<td>"+ p.Name +"</td>" +"<td>"+p.Sku+"</td>"+ "<td>" + ht[p.ProductId] + "</td>";
			}
		}else{
            resultString +="<td>"+ p.Name +"</td>" +"<td>"+p.Sku+"</td>"+ "<td>" + 0+ "</td>";
		}
    resultString+="</tr>";
	}
        resultString+="</table>";
		Results.Text=resultString;
}   
</script>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" Runat="Server"><br />
<asp:Label ID="Label0" runat="server" Text="This page will generate a list of products:" SkinID="FieldHeader"></asp:Label><br /><br />
<asp:Label ID="Label1" runat="server" Text="Selling Fewer Than X Units." SkinID="FieldHeader"></asp:Label><asp:TextBox ID="maxQTY" runat="server" Columns="15" Text="1"></asp:TextBox><br /><br />
<asp:Label ID="Label2" runat="server" Text="Between These Dates:  " SkinID="FieldHeader"></asp:Label><br /><br />
<asp:Label ID="Label3" runat="server" Text="From  " SkinID="FieldHeader"></asp:Label><uc1:PickerAndCalendar ID="PickerAndCalendar1" runat="server" /><br />
<asp:Label ID="Label4" runat="server" Text="To:  " SkinID="FieldHeader"></asp:Label><uc1:PickerAndCalendar ID="PickerAndCalendar2" runat="server" />
	<div style="margin:15px;">
		<asp:Button ID="ProcessButton" runat="server" Text="Generate Report" OnClick="ProcessButton_Click" />
	</div>
	<div style="margin:10px;">
		<asp:Label ID="Results" runat="server"></asp:Label>
	</div>
</asp:Content>

[compunerdy]

Works pretty good..thanks

Gets a tad slow or doesnt work if you try and do too many it months it seems.

[compunerdy]

Another nice report would be sales by item..

You could select a item and it would give you a 30/60/90/ALL times sales or something like that.