Thread: GAN (Google Affiliate Network) & page.orderconfirmation.xml.config

Hi Guys,

I have created a stored procedure that is equivalent to:

Select * From Orders

Except that it returns one extra field called GANLink, which is the Google Affiliate Network link that has all the order info required for GAN :

See Sample Link below:

C#/VB.NET Code:

https://clickserve.cc-dt.com/link/order?vid=KXXXXX&oid=100127&amt=82.09&prdsku=MGXXXX^MCTQXXXX^MCTBXXXX^NXXXX&prdnm=MGXXXX^MCTQXXXX^MCTBXXXX^NXXXX&prdqn=1^1^1^1&prdpr=22.99^8.99^8.99^41.12&prcatid=RXXXXX^RXXXXX^RXXXXX^RXXXXX 


(I replaced some sensitive stuff with an X above, but hopefully you can understand it)


Now, my dilemma: I am decent at writing T-SQL but am just plain horrible (no I take that back... I meant.. I am just completely retarded...) when working with the XML package "page.orderconfirmation.xml.config" I simply don't know what code to use to get this link to post appropriately. XML just make my eyes hurt really badly...

Ok back to the issue:
As you can see I am pulling (At least I THINK I am pulling...) the parameter into GANLink below from my stored proc... but have no idea where to put this (or even if this line will work):

After I pull the parameter, will this line work? And where the heck do I put it?

C#/VB.NET Code:

<img src="{$GANLink}" width="1" height="1"/> 


Here is the XML I use to get the parameter GANLink:

C#/VB.NET Code:

   <query name="Orders" rowElementName="Order">
        <sql>
            <![CDATA[
                exec GetGANLink @OrderNumber
            ]]>
        </sql>
        <queryparam paramname="@OrderNumber" paramtype="runtime" requestparamname="OrderNumber" 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" indent="no"  encoding="utf-8"/>
            <xsl:param name="LocaleSetting" select="/root/Runtime/LocaleSetting" />
            <xsl:param name="WebConfigLocaleSetting" select="/root/Runtime/WebConfigLocaleSetting" />
            <xsl:param name="UseSSL"><xsl:value-of select="aspdnsf:AppConfigBool('UseSSL')" /></xsl:param>
            <xsl:param name="CacheMenus"><xsl:value-of select="aspdnsf:AppConfigBool('CacheMenus')" /></xsl:param>
            <xsl:param name="UseLiveTransactions"><xsl:value-of select="aspdnsf:AppConfigBool('UseLiveTransactions')" /></xsl:param>
            <xsl:param name="OrderNumber"><xsl:value-of select="/root/Runtime/OrderNumber" /></xsl:param>
            <xsl:param name="StoreURL"><xsl:value-of select="/root/Runtime/StoreUrl" /></xsl:param>

            <xsl:template match="/">

                <xsl:param name="PaymentMethod"><xsl:value-of select="/root/Orders/Order/PaymentMethod" /></xsl:param>
                <xsl:param name="GANLink"><xsl:value-of select="/root/Orders/Order/GANLink" /></xsl:param>
                <xsl:param name="CustomerID"><xsl:value-of select="/root/Orders/Order/CustomerID" /></xsl:param>
                <xsl:param name="PMCleaned"><xsl:value-of select="aspdnsf:CleanPaymentMethod($PaymentMethod)" /></xsl:param>
                <xsl:param name="ReceiptURL"><xsl:value-of select="$StoreURL" />receipt.aspx?ordernumber=<xsl:value-of select="$OrderNumber" /></xsl:param>
                <xsl:param name="IncludeGoogleTrackingCode"><xsl:value-of select="aspdnsf:AppConfig('IncludeGoogleTrackingCode')" /></xsl:param>
                <xsl:param name="IncludeOvertureTrackingCode"><xsl:value-of select="aspdnsf:AppConfig('IncludeOvertureTrackingCode')" /></xsl:param> 


Any help on helping me get this final issue resolved? I will post my stored proc for everyone here, if someone can help me get this working.

Thanks
A

Have a little more faith in yourself...you're pretty much spot on

Provided your query,

Code:

<query name="Orders" rowElementName="Order">
        <sql>
            <![CDATA[
                exec GetGANLink @OrderNumber
            ]]>
        </sql>
        <queryparam paramname="@OrderNumber" paramtype="runtime" requestparamname="OrderNumber" sqlDataType="int" defvalue="0"  validationpattern="" />
    </query>

is correctly pulling the value for the GANLink, then the appropriate way to create the param is exactly as you've done

Code:

<xsl:param name="GANLink"><xsl:value-of select="/root/Orders/Order/GANLink" /></xsl:param>

Your line for using the value within an img tag is also correct

Code:

<img src="{$GANLink}" width="1" height="1"/>

Now, as you've defined this param, the scope of the param will only be within the root template

Code:

<xsl:template match="/">

so you can use the img tag anywhere between the opening and closing tags of the root template anywhere after you've defined the parameters

Code:

   <query name="Orders" rowElementName="Order">
        <sql>
            <![CDATA[
                exec GetGANLink @OrderNumber
            ]]>
        </sql>
        <queryparam paramname="@OrderNumber" paramtype="runtime" requestparamname="OrderNumber" 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" indent="no"  encoding="utf-8"/>
            <xsl:param name="LocaleSetting" select="/root/Runtime/LocaleSetting" />
            <xsl:param name="WebConfigLocaleSetting" select="/root/Runtime/WebConfigLocaleSetting" />
            <xsl:param name="UseSSL"><xsl:value-of select="aspdnsf:AppConfigBool('UseSSL')" /></xsl:param>
            <xsl:param name="CacheMenus"><xsl:value-of select="aspdnsf:AppConfigBool('CacheMenus')" /></xsl:param>
            <xsl:param name="UseLiveTransactions"><xsl:value-of select="aspdnsf:AppConfigBool('UseLiveTransactions')" /></xsl:param>
            <xsl:param name="OrderNumber"><xsl:value-of select="/root/Runtime/OrderNumber" /></xsl:param>
            <xsl:param name="StoreURL"><xsl:value-of select="/root/Runtime/StoreUrl" /></xsl:param>

            <xsl:template match="/">

                <xsl:param name="PaymentMethod"><xsl:value-of select="/root/Orders/Order/PaymentMethod" /></xsl:param>
                <xsl:param name="GANLink"><xsl:value-of select="/root/Orders/Order/GANLink" /></xsl:param>
                <xsl:param name="CustomerID"><xsl:value-of select="/root/Orders/Order/CustomerID" /></xsl:param>
                <xsl:param name="PMCleaned"><xsl:value-of select="aspdnsf:CleanPaymentMethod($PaymentMethod)" /></xsl:param>
                <xsl:param name="ReceiptURL"><xsl:value-of select="$StoreURL" />receipt.aspx?ordernumber=<xsl:value-of select="$OrderNumber" /></xsl:param>
                <xsl:param name="IncludeGoogleTrackingCode"><xsl:value-of select="aspdnsf:AppConfig('IncludeGoogleTrackingCode')" /></xsl:param>
                <xsl:param name="IncludeOvertureTrackingCode"><xsl:value-of select="aspdnsf:AppConfig('IncludeOvertureTrackingCode')" /></xsl:param>

               <div align="center">
                   <!-- you could use it here -->
                   <img src="{$GANLink}" width="1" height="1"/>
                   ...
                   ...
                   ...
                   <!-- or here -->
                   <img src="{$GANLink}" width="1" height="1"/>
                   ...
                   ...
                   ...
                </div>
                <!-- or here -->
                <img src="{$GANLink}" width="1" height="1"/>
            </xsl:template>

<!-- but NOT here -->

This particular xmlpackage doesn't utilize any other templates, but as a general rule of thumb if you have a param that you'd want to utilize in multiple templates and will only have a single value then you can define it inside the xsl:stylesheet node but outside of any template nodes (like the current OrderNumber and StoreURL parameters), and you can then use the param in any of the templates defined as children of the stylesheet

Hi George,

It looks like it's working after I realized a parameter was not named right! (Doh!) Ok, I fixed it in the stored proc, instead of renaming the XML.

Thanks for letting me know I was not spinning my wheels here. IT was really driving me a little nuts. So as promised here is my stored proc.

Actually the stored proc I use requires a view, so I will also add it below. Please create the view first, otherwise the proc will give you a few errors.

Here is the view:

C#/VB.NET Code:

/*
© Copyright 2007 RevGenetics, LLC. All Rights Reserved.
First: This code is Copyrighted, but freely usable and distributable 
as long as these comments are not edited.

My Comments:
1- Yes, it's a view, simple but true.
2- Replace the text 'Fixed Category' with something you like, or join another table if you wish to extrapolate the category.
3- Create the stored proc GetGANLink (It should be available at the link below)

(see http://forums.aspdotnetstorefront.com/showthread.php?t=21077) if you forgot where you saved GetGANLink... 
*/

Create View [dbo].[vw_GANview]
as
SELECT od.[OrderNumber] as OrderID
      ,o.ordersubtotal as SubTotal
      ,od.[OrderedProductSKU] as ProductSKU
      ,od.[OrderedProductSKU] as ProductName
      ,od.[Quantity] as ProductQTY
      ,od.[OrderedProductPrice] as ProductPrice
      ,'Fixed Category' as ProdCategory
      ,od.[ShoppingCartRecID] as OrderDetailID
      
  FROM 
    [dbo].[Orders] o inner join [dbo].[Orders_ShoppingCart] od 
    on o.OrderNumber = od.OrderNumber
    inner join [dbo].[customer] c
    on o.[CustomerID] = c.[CustomerID]
    inner join [dbo].[address] BillAddr
    on c.[BillingAddressID] = BillAddr.[AddressID]
    inner join [dbo].[address] ShipAddr
    on c.[ShippingAddressID] = ShipAddr.[AddressID]

Go 


Ok... now that you created the view in your SQL database... you then create the proc below:

C#/VB.NET Code:

/*
© Copyright 2007 RevGenetics, LLC. All Rights Reserved.
First: This code is Copyrighted, but freely usable and distributable 
as long as these comments are not edited.

My Comments:
0- Yes, I know nvarchar(2000) is overkill, but its a nvarchar! not fixed, so it's great.
1- To make this work you will need the view called: vw_GANview (It should be available at the link below)
2- Replace the text 'K??????' at the end of the @GANstart variable to your GAN account number
3- work on the page.orderconfirmation.xml.config, and add the following in the apprpriate places per :

This XML was driving me nuts, so have fun:
exec GetGANLink @OrderNumber
<xsl:param name="GANLink"><xsl:value-of select="/root/Orders/Order/GANLink" /></xsl:param>
<img src="{$GANLink}" width="1" height="1"/>

Don't know where to put the XML? No problem, look at link below:
(see http://forums.aspdotnetstorefront.com/showthread.php?t=21077) for details
*/

Create Procedure [dbo].[GetGANLink]
    @OrderNumber int
As
Declare @GANstart nvarchar(2000) 
    set @GANstart = 'https://clickserve.cc-dt.com/link/order?vid=K??????'

/* All nifty stuff below here */
Declare @OrderID as nvarchar(20),
        @VendorID as nvarchar(20), 
        @SubTotal as nvarchar(20),
        @ProdSKU as nvarchar(2000),
        @ProdName as nvarchar(2000),
        @ProdQTY as nvarchar(2000),
        @ProdPrice as nvarchar(2000),
        @ProdCatID as nvarchar(2000),
        @FinalURL as nvarchar(2000),
        @OrderDetailID as nvarchar(15)
set @OrderID = @OrderNumber

Select OrderID, SubTotal, ProductSKU, ProductName, ProductQTY, ProductPrice, ProdCategory, OrderDetailID
into #tmpTable
From vw_GANview with (NOLOCK)
where orderID = @OrderID

-- Select * from #tmpTable

declare @counter int, @records int
    select @counter = count(*) from #tmpTable
    set @records = @counter

    while @counter >0
    begin

        IF @counter = @records
        Begin
        Select top 1
            @OrderID='&oid='+ltrim(rtrim(ISNULL(convert(nvarchar,OrderID),''))), 
            @SubTotal='&amt='+ltrim(rtrim(ISNULL(SubTotal,''))), 
            @ProdSKU='&prdsku='+ltrim(rtrim(ISNULL(ProductSKU,''))), 
            @ProdName='&prdnm='+ltrim(rtrim(ISNULL(ProductName,''))), 
            @ProdQTY='&prdqn='+ltrim(rtrim(ISNULL(ProductQTY,''))), 
            @ProdPrice='&prdpr='+ltrim(rtrim(ISNULL(ProductPrice,''))), 
            @ProdCatID='&prcatid='+ltrim(rtrim(ISNULL(ProdCategory,''))), 
            @OrderDetailID=OrderDetailID
            From #tmpTable
        End
            else
        Begin
        Select top 1  
            @ProdSKU=ISNULL(@ProdSKU, '')+'^'+ltrim(rtrim(ISNULL(ProductSKU,''))), 
            @ProdName=ISNULL(@ProdName, '')+'^'+ltrim(rtrim(ISNULL(ProductName,''))), 
            @ProdQTY=ISNULL(@ProdQTY, '')+'^'+ltrim(rtrim(ISNULL(ProductQTY,''))), 
            @ProdPrice=ISNULL(@ProdPrice, '')+'^'+ltrim(rtrim(ISNULL(ProductPrice,''))), 
            @ProdCatID=ISNULL(@ProdCatID, '')+'^'+ltrim(rtrim(ISNULL(ProdCategory,''))), 
            @OrderDetailID=OrderDetailID
            From #tmpTable
        End

    Delete #tmpTable where OrderDetailID = @OrderDetailID
    set @counter = @counter - 1
    End

drop table #tmpTable
/* All nifty stuff above here */

Select @FinalURL = @GANstart + @OrderID +@SubTotal+ @ProdSKU+@ProdName+@ProdQTY+@ProdPrice+@ProdCatID
--Print @GANstart + @OrderID +@SubTotal+ @ProdSKU+@ProdName+@ProdQTY+@ProdPrice+@ProdCatID

Select o.*,@FinalURL as GANLink from Orders o with (NOLOCK) where OrderNumber=@OrderNumber 


Well, this view and this proc, along with your comments George, will most definitely allow folks to easily add GAN to ADNSF...

Just replace "K??????" in the main proc to your affiliate Company ID/vid, and you guys should be set. Oh, and rename the category in the view to something you like...

Thanks Again!!