Profit Per Transaction report

[William_firefold]

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?

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>