Jump to content

Setting up Marketplace to use ANS and log to an external database (tutorial)

Sassy Romano

By Sassy Romano,
in Merchants

 Share

Recommended Posts

Sassy Romano

Sassy Romano

Posted
Posted

What is ANS? - It lets Marketplace sales transactions be recorded in external databases (which can then trigger other actions)

Why should I bother reading or watching? - no reason at all, find another thread if you're not interested!

Do I need it? - No but depending on what you do, it might have use.

Why would I bother and not use use Marketplace reports? - You don't have to but by recording your sales data in your own database as it happens, you own the data and have more control.

Data consolidation - some vending systems also output ANS so with a bit of time, you can have several vending system all sending data to one location which makes finding customer purchases and the like, so much simpler.  No need to look through several systems.

What is SQL and PHP? - Google it, you don't need to know to make this work, just watch the movie!

Related content below, the tutorial uses the following scripts, copy and paste this one into a text document on your PC and call it xstreetans.php

<?php 

    $con=mysql_connect("<hostname>", "<username>", "<password>") or die(mysql_error());
    mysql_select_db("<database>",$con) or die(mysql_error());

    $Source="Xstreet";
      $Timestamp8601=$_GET['Timestamp8601'];
      $Timestamp = gmdate("Y-m-d\TH:i:s\Z",time());      
      $SaleID=$_GET['TransactionID'];
      $TransactionID=$_GET['TransactionID'];
      $ProductID=$_GET['ItemID'];
    $Region=$_GET['Region'];  
    $ProductName=$_GET['ItemName'];
    $BuyerName=$_GET['PayerName'];
    $BuyerKey=$_GET['PayerKey'];
    $ReceiverName=$_GET['ReceiverName'];
    $ReceiverKey=$_GET['ReceiverKey'];
    $SellerName=$_GET['MerchantName'];
    $SellerKey=$_GET['MerchantKey'];
    $PaymentGross=$_GET['PaymentGross'];
    $PaymentShared=$_GET['PaymentShared'];
    $PaymentCommission=$_GET['PaymentFee'];
    $Today = gmdate("Y/m/d");
    $Region = "Website";
    
   
$sql="REPLACE INTO ANS VALUES ('$Source','$Timestamp','$Today','$SaleID','$TransactionID','$ProductID','$Region','".addslashes($ProductName)."','$BuyerName','$BuyerKey','$ReceiverName','$ReceiverKey','$SellerName','$SellerKey','$PaymentGross','$PaymentShared','$PaymentCommission')";
$result=mysql_query($sql) or die(mysql_error());

mysql_close($con);

 

 

 

 

Copy and paste the following into the phpmyAdmin SQL command window as per the tutorial to create the database:-

CREATE TABLE IF NOT EXISTS `ANS` (
  `Source` text collate utf8_unicode_ci NOT NULL,
  `TimeStamp` text collate utf8_unicode_ci NOT NULL,
  `Date` text collate utf8_unicode_ci NOT NULL,
  `SaleID` int(11) NOT NULL,
  `TransactionID` int(11) NOT NULL,
  `ProductID` int(11) NOT NULL,
  `Region` text collate utf8_unicode_ci NOT NULL,
  `ProductName` text collate utf8_unicode_ci NOT NULL,
  `BuyerName` text collate utf8_unicode_ci NOT NULL,
  `BuyerKey` text collate utf8_unicode_ci NOT NULL,
  `ReceiverName` text collate utf8_unicode_ci NOT NULL,
  `ReceiverKey` text collate utf8_unicode_ci NOT NULL,
  `SellerName` text collate utf8_unicode_ci NOT NULL,
  `SellerKey` text collate utf8_unicode_ci NOT NULL,
  `PaymentGross` int(11) NOT NULL,
  `PaymentShared` int(11) NOT NULL,
  `PaymentCommission` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

 

 IMPORTANT:


You may have noticed that the above refers to xstreet but don't worry.  Please read the information and watch the tutorial as the change to Marketplace is VERY similar and the tutorial describes how to set up the web and SQL parts too which are common.

Roscko Cobalt has kindly provided updated information that is specific to Marketplace later in the thread but a quick link to it is here http://community.secondlife.com/t5/Merchants/Setting-up-Marketplace-to-use-ANS-and-log-to-an-external/m-p/2177571#M38386

  • Like 1
Link to comment
Share on other sites
Dartagan Shepherd

Dartagan Shepherd

Posted
Posted

Thanks for providing this, including the point about deploying code that works without testing, that was priceless.

Looking over the other threads about the lack of data in reporting and still wondering why both don't include the SKU.

The whole point of SKU was supposed to be able to add a lot of customized power to reporting and importing into software used for record keeping and yet it's not used, in either ANS or transactions.

Rather awkward that those running a commerce division don't seem to get the basics that any bookkeeper uses on a daily basis.

Link to comment
Share on other sites
Sassy Romano

Sassy Romano

Posted
  • Author
Posted

Here's an added snippet of LSL code.  If you have a full perm payment script in your vendors prim, a little modification of this script will allow you to have your simple vendors send their sales log information to the same database as where your Marketplace data goes.

To test this script, just rez a cube and create a new script and paste the code and save.  Touch the box and it'll enter the values into your database.

string url = "http://demoans.comze.com/xstreetans.php"; // change this URL to suit yourskey reqid;SaveToANS(string TransactionID, string ItemID, string Region, string ItemName, string PayerName, key PayerKey, string ReceiverName, key ReceiverKey, string SellerName, key SellerKey, integer PaymentGross, integer PaymentShared, integer PaymentCommission){    string values =     "Timestamp8601="+llGetTimestamp()+                        "&TransactionID="+TransactionID+                        "&ItemID="+ItemID+                        "&Region="+Region+                        "&ItemName="+ItemName+                        "&PayerName="+PayerName+                        "&PayerKey="+(string)PayerKey+                        "&ReceiverName="+ReceiverName+                        "&ReceiverKey="+(string)ReceiverKey+                        "&SellerName="+SellerName+                        "&SellerKey="+(string)SellerKey+                        "&PaymentGross="+(string)PaymentGross+                        "&PaymentShared="+(string)PaymentShared+                        "&PaymentCommission="+(string)PaymentCommission;                                            reqid = llHTTPRequest(url+"?"+values,[HTTP_METHOD,"POST",HTTP_MIMETYPE,"application/x-www-form-urlencoded"], "");}default{    touch_start(integer total_number)    {        // insert some dummy values for testing these would come from your vending or payment/splitpayment script        string lTransactionID = "12345";        string lItemID = "01";        string lRegion = "My Home";        string lItemName = "Super New Product";        string lPayerName = "Betty Boop";        key lPayerKey = "013adfce-be4f-458a-8fa8-349b6275c438";        string lReceiverName = "Johnny Blogs";        key lReceiverKey = "013adfce-be4f-458a-8fa8-349b6275c567";        string lSellerName = "Sassy Romano";        key lSellerKey = "05e01564-c7c1-4b18-8458-8333f12ef79e";        integer lPaymentGross = 100;        integer lPaymentShared = 0;        integer lPaymentCommission = 0;        // call the function to insert the data to your database        SaveToANS(lTransactionID,lItemID,lRegion,lItemName,lPayerName,lPayerKey,        lReceiverName,lReceiverKey,lSellerName,lSellerKey,lPaymentGross,lPaymentShared,lPaymentCommission);    }        http_response(key request_id, integer status, list metadata, string body)    {        if (request_id == reqid)        {            // status will be 200 if the web page replied OK.  Check for any other web errors            llOwnerSay((string)status+" "+body);        }    }}

 

Link to comment
Share on other sites
Sassy Romano

Sassy Romano

Posted
  • Author
Posted

Mikki Miles wrote:

there's just one thing not right.

for MP-sales the commision shows 0. that can't be expected behaviour?

Good catch!

I used a flavour of my PHP script that was from Apez and the variables are different.  Easy to change, edit the xstreetans.php  that you've uploaded to your web host and change the line:-

    $PaymentCommission=$_GET['PaymentCommission'];

to

    $PaymentCommission=$_GET['PaymentFee'];

Then you should see the Marketplace fee in the database PaymentCommission column.

(I'll edit the original scripts in the first post too)

Link to comment
Share on other sites
Mikki Miles

Mikki Miles

Posted
Posted

great, thank you.

hopefully you don't mind when I keep bugging you...

I try to get the ANS from Hippo to work. In the field "Region" I get "website", where it should name the region of my shop. I found that line >$Region = "Website";\< in your script, I suspect it sets that field to "website", no matter what.

Is this line required?

Link to comment
Share on other sites
Sassy Romano

Sassy Romano

Posted
  • Author
Posted

Yes region is coded in the script just to fill that field for Marketplace transactions.  For Hippo (and others) they will send the actual region where the sale occurred.  One simple way to approach this is to copy the xstreetans.php to say hippoans.php and edit the variables to suit.  Then in Hippo ANS configuration on the Hippo website, set it to use your hippoans.php script and leave the one for Marketplace alone.

This is where you need to spend a little time as the variables between all the different ANS providers differ.  They all do almost exactly the same thing, just that each needs a little nudge to fit into the same database but now that you have something working, you'll get there :)

(The Hippo site provides details about the ANS variables they send, since i'm banned from Hippo, my enthusiasm for it is limited :) )

Link to comment
Share on other sites
  • 3 months later...
Niall Braveheart

Niall Braveheart

Posted
Posted

A couple of minor tweaks are needed to the LSL example are needed to make it work with the earlier php example

 

                        "&SellerName="+SellerName+                        "&SellerKey="+(string)SellerKey+

 needs to be

 

                        "&MerchantName="+SellerName+                        "&MerchantKey="+(string)SellerKey+

 

Otherwise everything works perfectly, thanks for taking the time and effort to put this together.

Link to comment
Share on other sites
  • 3 months later...
Mikki Miles

Mikki Miles

Posted
Posted

I tried that script, it returns status 200, but then follows a bunch of warnings:

<b>Warning</b>: Unexpected character in input: '\' (ASCII=92) state=1 in <b>/home/www/XXXXXX/html/montiANS.php</b> on line <b>8</b><br />
<br />

 

for lines 8 through 13, and there's no entry in the database. What went wrong here?

Link to comment
Share on other sites
Sassy Romano

Sassy Romano

Posted
  • Author
Posted

I've just edited the original php script, please try that one.  Also, please let me know the name of the product that you purchased that it failed on, did you try it on other products too?

Also, are you trying this on the xstreetsl website or with the Marketplace website?  I haven't tested this one against MP.

Link to comment
Share on other sites
Mikki Miles

Mikki Miles

Posted
Posted

thanks, I will try that when I get home.

I didn't buy any particular product, I'm trying to set up a vendor myself, for my special needs, since hippo is failing terribly (ongoing deliver issues). I've used the information you gave here, sending ANS from hippo and MP to my database and that's working fine. Do you offer any products that might help me? I'd gladly pay for that, but on MP I didn't find any of that kind in your store

Link to comment
Share on other sites
  • 6 months later...
Darrius Gothly

Darrius Gothly

Posted
Posted

Umm .. that's very unusual .. unusual to the point of "WTF?" You may want to call llEscapeURL() on the values before sticking them into the URL, but if the spaces are getting eaten then it's something peculiar to your ISP. You might also want to replace all the spaces in the URL with plus signs. A simple trick to do that is something like:

url = llDumpList2String(llParseStringKeepNulls(url, [" "], []), "+");

That will break up the url string at spaces into an LSL List, then stick it all back into a single string with plus signs.

Link to comment
Share on other sites
  • 9 months later...
Roscko Cobalt

Roscko Cobalt

Posted
Posted

Great post Sassy! I needed to make an auto-shoutcast server setup script and thought hey "why not try ANS" :)

So I was glad to find this post to get started. I found that yours is set for Xstreet, so I decided to be nice to the community and release my updated version for the new SL marketplace.

I have also added in some functions to clean the input (for security).

This is much the same as Sassy's Tut, but when it comes to the part about hooking up your marketplace store to the php page, you will need to do this instead..

In the top of the php page the variable $myMPSalt, needs the code that is generated from your merchant account page.

To get this code :

Log into your merchant account and from the top menu choose My Marketplace>Merchant Home

then on the left menu under Store Setup click  Automatic Notifications (ANS).

You will then need to enter the exact url to the php page you have uploaded to your server.

example:  http://mysite.com/rt_mp_ans.php

When its saved you will need to copy the Notification salt code into $myMPSalt variable.

example: 

$myMPSalt = "yourNotificationSalt";

 

 Now as seen in Sassy's video, add the Sql query below.

Then you should be good to go :)

 

Heres the php page:

 

<?php // This is your ANS php page // This file name MUST be linked to your marketplace store in order for this to work!!// When you set the URL of your ANS/SLM Processor in the Merchant ANS Configuration, a Salt Code will be generated and displayed.//error_reporting(E_ALL);  //uncomment this for debugging$myMPSalt = "yourNotificationSalt"; // Salt code from MP$isValid = false;$myMPHash = $_SERVER['HTTP_X_ANS_VERIFY_HASH'];$myCalcHash = sha1($_SERVER['QUERY_STRING'] . $myMPSalt);if ($myMPHash == $myCalcHash) {    $isValid = true;}if ($isValid) {if (isset($_GET['TransactionID'])) { $myServer = "localhost";    // Your database server$myUser   = "";             // Your database username$myPass   = "";             // Your database pass$myDB	  = "";             // Your database name$dbconnect = mysql_connect($myServer, $myUser, $myPass) or die("Couldn't connect to SQL Server on $myServer " . mysql_error() );$dbselect = mysql_select_db($myDB, $dbconnect) or die("Trouble selecting the database.");if (!function_exists('sql_val')) {function sql_val( $input ) {     if ( get_magic_quotes_gpc() ) {          $input = stripslashes( $input );     } //get_magic_quotes_gpc()     return ( "'" . mysql_real_escape_string( $input ) . "'" );}} //end function not existif ( !function_exists( 'clean' ) ) {function clean( $input, $type="", $no_tags="" ) {     if ($no_tags != "") {          $input = trim(strip_tags($input));     }     if ($type != "") {          if (strlen(strstr($type,"("))>0) {               $split = explode("(", $type);               $type = $split['0'];               $limit = str_replace(")", "", $split['1']);                    if (is_numeric($limit)){                    if ( ($type == "int") && (!is_int($input)) ) {                         $input = (int)substr($input, 0, $limit);                    } else {                         $input = substr($input, 0, $limit);                    }                    }          }     }     if ( get_magic_quotes_gpc() ) {          $input = stripslashes( $input );     }      if ( @mysql_ping() != "" ) {          $input = mysql_real_escape_string( $input );     } else {          $search = array("\x00", "\n", "\r", "\\", "'", '"', "\x1a");          $replace = array("\\x00", "\\n", "\\r", "\\\\" ,"\'", '\"', "\\x1a");          $input = str_replace($search, $replace, $input);     }     return $input;} //end function} //end function not existif (!function_exists('reverb')) {function reverb($value) {     return htmlspecialchars(stripslashes($value));}}//end functin not existsif (!function_exists('print_x')) {function print_x($value) {     echo '<pre>';     print_r($value);     echo '</pre>';}}//end functin not existsdate_default_timezone_set("America/Los_Angeles");$TimeStamp = gmdate('l, F j, Y h:i:s A', time());  $TransactionID = isset($_GET['TransactionID']) ? clean($_GET['TransactionID'], "bigint(20)") : "";$ItemID = isset($_GET['ItemID']) ? clean($_GET['ItemID'], "int(10)") : "";$ItemName = isset($_GET['ItemName']) ? clean($_GET['ItemName'], "varchar(100)") : "";$PayerName = isset($_GET['PayerName']) ? clean($_GET['PayerName'], "varchar(100)") : "";$PayerKey = isset($_GET['PayerKey']) ? clean($_GET['PayerKey'], "varchar(36)") : "";$ReceiverName = isset($_GET['ReceiverName']) ? clean($_GET['ReceiverName'], "varchar(100)") : "";$ReceiverKey = isset($_GET['ReceiverKey']) ? clean($_GET['ReceiverKey'], "varchar(36)") : "";$MerchantName = isset($_GET['MerchantName']) ? clean($_GET['MerchantName'], "varchar(100)") : "";$MerchantKey = isset($_GET['MerchantKey']) ? clean($_GET['MerchantKey'], "varchar(36)") : "";$PaymentGross = isset($_GET['PaymentGross']) ? clean($_GET['PaymentGross'], "varchar(12)") : "";$InventoryName = isset($_GET['InventoryName']) ? clean($_GET['InventoryName'], "varchar(100)") : "";$PaymentFee = isset($_GET['PaymentFee']) ? clean($_GET['PaymentFee'], "varchar(12)") : "";$Date = gmdate("Y/m/d");$query = 'REPLACE INTO sl_marketplace_ans (    `TimeStamp`,    `TransactionID`,    `ItemID`,    `ItemName`,    `PayerName`,    `PayerKey`,    `ReceiverName`,    `ReceiverKey`,    `MerchantName`,    `MerchantKey`,    `PaymentGross`,    `InventoryName`,    `PaymentFee`,    `Date` ) VALUES (    '.sql_val($TimeStamp).',    '.sql_val($TransactionID).',    '.sql_val($ItemID).',    '.sql_val($ItemName).',    '.sql_val($PayerName).',    '.sql_val($PayerKey).',    '.sql_val($ReceiverName).',    '.sql_val($ReceiverKey).',    '.sql_val($MerchantName).',    '.sql_val($MerchantKey).',    '.sql_val($PaymentGross).',    '.sql_val($InventoryName).',    '.sql_val($PaymentFee).',    '.sql_val($Date).' )'; /*<!-- depending on how you want to display errors, comment or uncomment the following -->*/$result = mysql_query($query) or $db_message = '<p class="db_error"><b>A fatal MySQL error occurred while trying to save <b>'.reverb($_GET['TransactionID']).'</b> to the database.</b><br />Query: '.$query.'<br />Error: ('.mysql_errno().') '.mysql_error().'</p>';if ($result) $db_message = '<p class="db_success">Successfully saved <b>TransactionID : '.reverb($_GET['TransactionID']).'</b> to the database!!</p>';else $db_message = '<p class="db_error">Error saving <b>TransactionID : '.reverb($_GET['TransactionID']).'</b> to the database!!</p>';print_x($query);/*<!-- end of error displays -->*/if (isset($db_message)) echo $db_message;  }//end if isset TransactionID}//end if is Validelse{    //$isValid = false;	$ANS_error = '<p class="ans_fail">Hash calculation <b>Failed!</b> Check Your Salt Code!!</p>';	echo $ANS_error;}?>

 

 

And heres the SQL

 

CREATE TABLE IF NOT EXISTS `sl_marketplace_ans` (  `id` int(12) NOT NULL AUTO_INCREMENT,  `TimeStamp` text COLLATE utf8_unicode_ci NOT NULL,  `TransactionID` bigint(20) NOT NULL,  `ItemID` int(10) NOT NULL,  `ItemName` varchar(100) COLLATE utf8_unicode_ci NOT NULL,  `PayerName` varchar(100) COLLATE utf8_unicode_ci NOT NULL,  `PayerKey` varchar(36) COLLATE utf8_unicode_ci NOT NULL,  `ReceiverName` varchar(100) COLLATE utf8_unicode_ci NOT NULL,  `ReceiverKey` varchar(36) COLLATE utf8_unicode_ci NOT NULL,  `MerchantName` varchar(100) COLLATE utf8_unicode_ci NOT NULL,  `MerchantKey` varchar(36) COLLATE utf8_unicode_ci NOT NULL,  `PaymentGross` varchar(12) COLLATE utf8_unicode_ci NOT NULL,  `InventoryName` varchar(100) COLLATE utf8_unicode_ci NOT NULL,  `PaymentFee` varchar(12) COLLATE utf8_unicode_ci NOT NULL,  `Date` text COLLATE utf8_unicode_ci NOT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `TransactionID` (`TransactionID`)) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=0;

 

 I hope people find this useful. With a bit of php and lsl know how, you can do many things with this.

 

 

 

 

Link to comment
Share on other sites

Please sign in to comment

You will be able to leave a comment after signing in



Sign In Now
 Share
Go to topic listing
×
×
  • Create New...