Welcome to Vortx Community Forum, where you can ask questions and receive answers from the staff at Vortx and other members of the community.

If you had a user account on our previous forums website, you will need to register a new account here.

Learn more about...

AspDotNetStorefront
DotFeed

Affiliate Report

Okay I have a client running AspDotNetStoreFront multistore.  They would like affiliates to be able to see a report of their sales.  I assumed I could perhaps right an XML to do this but it isn't working out that easily.  I was thinking if the Affiliate logs in the AffiliateID is stored.  I was hoping to write a SQL calling on OrderNumber that matches the AffiliateID and then from Order_ShoppingCart show the Date, productID and OrderedProductPrice.  I know this won't show commissions but I figured if I can show this much I'm headed in the right direction.  I started writing the xml but it doesn't even like my sql which isn't complete.  

The code below doesn't show anything at this point.  But it throws an error when i try to view it in my browser.  You help, suggestions, comments are greatly appreciated.

Thank you in advance.

 

<?xml version="1.0" standalone="yes" ?>
 
<package version="2.1" 
         displayname="Affiliate Report" 
         debug="true" 
         includeentityhelper="true" 
         allowengine="true" >
 
    <query name="AffiliateReport" rowElementName="AffiliateOrders">
        <sql>
            <![CDATA[
                SELECT * FROM OrderNumber A with (NOLOCK)
                WHERE A.AffiliateID = @affiliateID  
            ]]>
        </sql>
        <queryparam paramname="@affiliateID" 
                    paramtype="system" 
                    requestparamname="AffiliateID" 
                    sqlDataType="int" defvalue="0" validationpattern="" />
    </query>
 
    <PackageTransform>
        <xsl:stylesheet version="1.0"
                        xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                        xmlns:aspdnsf="urn:aspdnsf"
                        exclude-result-prefixes="aspdnsf">
            <xsl:output method="html" omit-xml-declaration="yes" />
            <xsl:param name="AffiliateID" select="/root/Runtime/AffiliateID" />
            
            <xsl:template match="/">
                
            </xsl:template>
            
        </xsl:stylesheet>
    </PackageTransform>
</package>
            
            
asked Oct 30, 2013 in MultiStore by dmitsios (145 points)

1 Answer

0 votes
That's a good solution you've come up with.

Just change the query to reference "Orders" instead of "OrderNumber" like:

SELECT * FROM Orders A with (NOLOCK)  WHERE A.AffiliateID = @affiliateID

Then you can view it with http://yoursitedomain.com/x-affiliatereport.aspx.

Also, if it's throwing any weird "XML declaration must be the first node in the document" errors just make sure there are no spaces or carriage-returns before <?xml version="1.0" standalone="yes" ?>.
answered Oct 30, 2013 by Vortx Joe (550 points)
Thank you that worked!  Now I wanted to test to make sure it was getting the right AffiliateID.  I placed affiliateID in the template but it isn't showing anything.  Am I calling it wrong?
 
Thanks again!

 

 
<?xml version="1.0" standalone="yes" ?>
<package version="2.1" 
         displayname="Affiliate Report" 
         debug="true" 
         includeentityhelper="true" 
         allowengine="true" >
 
    <query name="AffiliateReport" rowElementName="AffiliateOrders">
        <sql>
            <![CDATA[
                SELECT * FROM Orders A with (NOLOCK)
                WHERE A.AffiliateID = @affiliateID  
            ]]>
        </sql>
        <queryparam paramname="@affiliateID" 
                    paramtype="system" 
                    requestparamname="AffiliateID" 
                    sqlDataType="int" defvalue="0" validationpattern="" />
    </query>
 
    <PackageTransform>
        <xsl:stylesheet version="1.0"
                        xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                        xmlns:aspdnsf="urn:aspdnsf"
                        exclude-result-prefixes="aspdnsf">
            <xsl:output method="html" omit-xml-declaration="yes" />
            <xsl:param name="AffiliateID" select="/root/Runtime/AffiliateID" />        
 
            <xsl:template match="/">
                   <AffiliateID></AffiliateID>            
            </xsl:template>
            
        </xsl:stylesheet>
    </PackageTransform>
</package>

Try something like this.  I'll highlight the differences.

I changed the paramtype being passed to the SQL query to be paramtype "request" which allows you to pass in the affiliateid on the query string, like this:

http://www.yourdomain.com/x-affiliatereport.aspx?affiliateid=2

I also put a validation pattern on it, just to make sure we just get numbers.

I took out that <xsl:param ...  /> since it doesn't seem to be needed.

I changed the query a bit with a join to get the Affiliate name.

Then I added a xsl:foreach to pull in the XML generated by the SQL query and put it on the page.

Since you have debug on, you should be able to see the actual XML generated by the SQL query if you use the e- instead of the x- URL, like http://www.youdomain.com/e-affiliatereport.aspx?affiliateid=2, and it'll be in the textarea labelled affiliatereport_store.runtime.xml.

------------------------------------------------------

<?xml version="1.0" standalone="yes" ?>
<package version="2.1" displayname="Affiliate Report" debug="true" includeentityhelper="true" allowengine="true" >

        <query name="AffiliateReport" rowElementName="AffiliateOrders">
                <sql>
                        <![CDATA[
                                select a.name AffiliateName, o.* from orders o with (NOLOCK)
                                left join affiliate a on o.affiliateid = a.affiliateid
                                where o.AffiliateID = @AffiliateID  
                        ]]>
                </sql>
                <queryparam paramname="@AffiliateID" paramtype="request"    requestparamname="affiliateid" sqlDataType="int"    defvalue="0"    validationpattern="^\d{1,10}$" />
        </query>

        <PackageTransform>
                <xsl:stylesheet version="1.0"    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:aspdnsf="urn:aspdnsf" exclude-result-prefixes="aspdnsf">
                        <xsl:output method="html" omit-xml-declaration="yes" />
                        <xsl:template match="/">
                                <xsl:for-each select="/root/AffiliateReport/AffiliateOrders">
                                        OrderNumber: <xsl:value-of select="OrderNumber" />&#160;&#160;
                                        Affiliate:  <xsl:value-of select="AffiliateName" />&#160;&#160;
                                        Total: <xsl:value-of select="OrderTotal" />
                                        <br />
                                </xsl:for-each>
                        </xsl:template>
                </xsl:stylesheet>
        </PackageTransform>
</package>

That is awesome and worked really well.  Now I have one last question.  

What do I change to make it where it gets the AffiliateID from the affiliateID login at yourdomain.com/lat_account.aspx?  I wanted to make the XML file a link for the affiliate to click on.

Thanks again!

Danny

Ooh, that gets tricker.

First, I don't think you want to pass the affiliate id on the querystring anymore, else someone will get clever and look at the other affiliates order info.  So we need to put it in a page.

I'll show you how to put it at the bottom of the lat_account.aspx page, but you could put it on its own .aspx page just as easily, and link to it in the navigation on the left of lat_account.aspx.

First, at the very bottom of lat_account.aspx we'll add a literal control at about line 309, after the ending table tag.
...
                </table>
                <asp:Literal ID="XmlPackage_AffiliateOrders" runat="server" Mode="PassThrough" />
            </asp:Panel>
    </asp:Panel>
</asp:Content>

Then in the code behind we'll call your affiliatereport.xml.config directly using the RunXmlPackage call in AppLogic.  I did this around line 167.  
...
XmlPackage_AffiliateOrders.Text = AppLogic.RunXmlPackage("affiliatereport.xml.config", base.GetParser, ThisCustomer, SkinID, String.Empty, String.Format("affiliateid={0}", AffiliateID), true, true);

AppLogic.GetButtonDisable(btnUpdate1);
AppLogic.GetButtonDisable(btnUpdate2);
...
That executes the package and gets the text and assigns it to the literal control.  That String.Format("affiliateid={0}", AffiliateID) part is then runtime parameter for AffiliateID, which was available to us in the code behind already as you noted.

That means we have to make a small change to affiliatereport.xml.config to switch it from expecting a request (querystring) param to a runtime param (passed in to the Xml package at runtime)

...
<query name="AffiliateReport" rowElementName="AffiliateOrders">
        <sql>
                <![CDATA[
                        select a.name AffiliateName, o.* from orders o with (NOLOCK)
                        left join affiliate a on o.affiliateid = a.affiliateid
                        where o.AffiliateID = @AffiliateID  
                ]]>
        </sql>
        <queryparam paramname="@AffiliateID" paramtype="runtime"    requestparamname="affiliateid" sqlDataType="int"    defvalue="0"    validationpattern="^\d{1,10}$" />
</query>
...
Hopefully that gets you what you're looking for.  You'll need to make it prettier (I probably won't be answering that forum post question - layout is not my strong-suit) since it's just hanging out there at the botton of the page now.

 

BTW, I'm sure you guys know this already and might not be an issue, but using the XmlPackage call out like that doesn't give you any security and could be used to get order data etc by changing the affiliateID...
Yup, that's why I had them change it to runtime and pulling the AffiliateID from the code behind.  We could also switch the allowengine to false, just for another level of security.
Okay I did what you told me but it shows all the affiliates.  On the file lat_account.aspx I added the following link (<a href="e-affiliateReport.aspx">Affiliate Report</a>).  Is there something I'm missing?  It shows me all the affiliate sales.  

Thank you so much!

Also an added note, your code <asp:Literal ID="XmlPackage_AffiliateOrders" runat="server" Mode="PassThrough" />  on lat_account.aspx also shows all the affiliate sales.  Once again thank you in advance for your help.

Correction to my above statement.  It is returning all orders that are attached to AffiliateID = 0.  Seems it is pulling the default value and not pulling the AffiliateID from the login.  Your assistance is greatly appreciated.  Thank you in advance.
...