custom reporting feedback

Go to solution

Press Ctrl / CMD + C to copy this to your clipboard.

Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at

Nigel Wilson 945 posts 2077 karma points

Oct 10, 2012 @ 21:39
0

Custom Reporting Feedback
Hi there

Am about to launch a teacommerce install, but have one aspect causing me to scratch my head...

I need add a custom user control to the dashboard of the site to facilitate some custom reporting.

The reportnig requirements are 2 fold:

1. Download a CSV file of orders within a given date range (date pickers within the user control) and for a given region (dropdown list within the user control).

2 Create PDF files of all orders (as matched above) and download as a single ZIP file.

To facilitate the above I have 2 custom properties recorded on each order - a date completed and a region ID (node from the content tree).

For now the only way I have been able to make progress in getting some reporting working is a follows:
```
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(HttpContext.Current.Server.MapPath("~/App_Data/TeaCommerce/orders.xml"));
XmlNodeList orders = xmlDoc.SelectNodes("/orders/order[string(properties/orderInvoiced) != '' and <<<more  filtering to go in here>>>]");
foreach (XmlNode n in orders) 
{
...
}
```
Can anyone suggest a better way ?

The prime consideration is being able to check the custom date property against the selected date range.

Thanks

Nigel
Copy Link
Rune Grønkjær 1372 posts 3103 karma points

Oct 11, 2012 @ 09:28

1

Hi Nigel,

I have done both csv generation of orders and pdf generation of order information. It's pretty easy, when you know how to do it.

CSV generation

I used the Macro Service package to expose a macro as an url. It's easy to set it up to returning a csv file to the user. If you make it with an xslt macro you can use the teacommerce:GetFinalizedOrdersXml()method to get all orders and then just run through your orders. Easy as pie :)

PDF generation

For that we bought a license to ABC pdf, which is brilliant and very easy to use. I then created the pdf content as a regular page in umbraco, which is easy to debug. Then I used ABC pdf to create a pdf from the contents of that page.

Hope that helps a bit

/Rune

Copy Link
Nigel Wilson 945 posts 2077 karma points

Oct 11, 2012 @ 22:41

0

Hi Rune

Thanks for the feedback - very much appreciated.

I had madfe progress yesterdau but am still stuck on how to use XPATH with dates.

I was not aware of the Macro Service Package. I have previously done somehting similar with hidden iframes and xslt files that return a file stream output. It worked but was a bit clunky from a setup perspective.

This package looks nice and should provide a solution to the XPATH / date issue I have.

Re PDF's - we have bought a licence for PDF Creator and I intend building a wrapper to stream the output and save to disk. For this to work I only need the order ID which is easy to "grab".

Then once complete will zip the files and download. This part is "under control" - at least in my head. Once I get started on it, things may change. ;-)

Cheers again - appreciate it.

Nigel

Copy Link
Rune Grønkjær 1372 posts 3103 karma points

Oct 12, 2012 @ 08:15
0
Hi Nigel,

Dates and xpath... Heres how I get all orders in a specific date range:

<xsl:variable name="orders" select="teacommerce:GetFinalizedOrdersXml()//order [(string($fromDate) = '' or umbraco.library:DateGreaterThanOrEqual(@orderDate,$fromDate)) and (string($toDate) = '' or umbraco.library:DateGreaterThanOrEqual($toDate,@orderDate))]"/>

It takes into account that the dates may be empty.

You can also get all orders belonging to a specific member group. That will require a small xslt extension method:
```
public static XPathNodeIterator GetMembersByMemberGroup( string memberGroupName ) {
      XmlDocument xml = new XmlDocument();
      XElement root = new XElement( "members" );


      if ( !string.IsNullOrEmpty( memberGroupName ) ) {
        string[] usernames = Roles.GetUsersInRole( memberGroupName );

        foreach ( string username in usernames ) {

          MembershipUser user = Membership.GetUser( username );
          Member member = new Member( (int)user.ProviderUserKey );

          root.Add( member.ToXml( xml, true ).GetXElement() );

        }
      }

      return root.CreateNavigator().Select( "/" );

    }
```
<xsl:variable name="members" select="lange:GetMembersByMemberGroup($memberGroup)/node"/>
<xsl:variable name="ordersForMemberGroup" select="$orders [@memberId = $members/@id]" />

That was some bonus information and just for kicks, let us get all unique order lines. Unique on their product number that is:

<xsl:variable name="orderLines" select="$ordersForMemberGroup/orderLine" />
<xsl:variable name="uniqueOrderLines" select="$orderLines [not(properties/productNumber = preceding::orderLine/properties/productNumber)]" />

That will give you, and everyone else some fun ways to display Tea Commerce orders.

/Rune
Copy Link
Nigel Wilson 945 posts 2077 karma points

Oct 12, 2012 @ 08:22

0

Hi Rune

Thanks for the code - up until now I have been attempting to code in c# / API using XPATH and not getting anywhere.

So reverting to your valuable input in the above posts is what I'll be working on next.

Cheers, Nigel

Copy Link
Nigel Wilson 945 posts 2077 karma points

Oct 12, 2012 @ 20:25

0

Hi Rune

Just installed the Macro Service package and initially was not "getting it"...

But yes, the package is perfect.

As an overview - I have a user control being displayed on the dashboard of the content section - the user selects 2 dates and a region (drop down list) and then using jquery I am submitting the selections to a hidden iframe. The URL for the iframe the URL exposed by the macro.

Now coding the desired output will be an absolute cinch...

Wicked wicked wicked.. It is only 7:30am but this has absolutely made my day...

Cheers, Nigel

Copy Link
is working on a reply...

Please Sign in or register to post replies

Flag this post as spam?