Page 1 of 1
Non-selling items Report
Posted: Fri May 01, 2009 11:26 am
by 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?
Re: Non-selling items Report
Posted: Fri May 01, 2009 11:37 am
by William M
Taking this one step further gets you a list of all products and their sales for X period.
Re: Non-selling items Report
Posted: Mon May 04, 2009 9:40 am
by 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.
Re: Non-selling items Report
Posted: Mon May 04, 2009 11:08 am
by 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>
Re: Non-selling items Report
Posted: Tue May 05, 2009 6:00 am
by William_firefold
Thanks, this is plenty for me to work with. I will post the complete report code once i get it working.
Re: Non-selling items Report
Posted: Tue May 05, 2009 11:52 am
by 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>
Re: Non-selling items Report
Posted: Tue May 05, 2009 1:25 pm
by compunerdy
Works pretty good..thanks
Gets a tad slow or doesnt work if you try and do too many it months it seems.
Re: Non-selling items Report
Posted: Tue May 05, 2009 1:30 pm
by 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.