Profit Per Transaction report
Posted: Tue Jun 02, 2009 7:55 am
I am making a report, which currently shows the number of units sold per transaction in a date range. I would also like it to show the actual profit per transaction. Right now it shows the expected profit (price - cost) but does not take discounts into account.
Is there a way to match up discounts with their respective item when looking at an order?
Is there a way to match up discounts with their respective item when looking at an order?
Code: Select all
<%@ Page Language="C#" MasterPageFile="~/Admin/Admin.master" Title="Low Sales" 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();
Hashtable ht2 = new Hashtable();
Hashtable profitTable = 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))
{
profitTable[oi.ProductId]=AlwaysConvert.ToDouble(profitTable[oi.ProductId]) + (AlwaysConvert.ToDouble(oi.Price) - AlwaysConvert.ToDouble(oi.CostOfGoods));
ht[oi.ProductId] = AlwaysConvert.ToInt(ht[oi.ProductId]) + oi.Quantity;
ht2[oi.ProductId] = AlwaysConvert.ToInt(ht2[oi.ProductId]) + 1;
}else
{
profitTable.Add(oi.ProductId,(AlwaysConvert.ToDouble(oi.Price) - AlwaysConvert.ToDouble(oi.CostOfGoods)));
ht.Add(oi.ProductId, oi.Quantity);
ht2.Add(oi.ProductId, 1);
}
}
}
}
resultString +="<table cellspacing=\"0\" border=\"1\" style=\"background:#fff;\" >";
foreach (Product p in pc)
{
resultString +="<tr>";
if (ht.ContainsKey(p.ProductId)){
if(AlwaysConvert.ToInt(ht[p.ProductId]) >0){
double unitpertrans=(AlwaysConvert.ToDouble(ht[p.ProductId])/AlwaysConvert.ToDouble(ht2[p.ProductId]));
double profitpertrans=(AlwaysConvert.ToDouble(profitTable[p.ProductId])/AlwaysConvert.ToDouble(ht2[p.ProductId]));
resultString +="<td>"+ p.Name +"</td>" +"<td>"+p.Sku+"</td>"+ "<td>" + unitpertrans.ToString() + "</td>"+ "<td>" + profitpertrans.ToString()+ "</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 product sales divided by the number of orders that contain the product." SkinID="FieldHeader"></asp:Label><br /><br />
<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>