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

[Sassy Romano]

Just an appendage to Rosko's post.

In the case of a multiple order from shopping cart, Marketplace sends each line item as a separate ANS record but the TransactionID references the same Marketplace order number.

The problem is that Rosko's SQL has this:-

  UNIQUE KEY `TransactionID` (`TransactionID`)

Which will cause only the first ANS insert into the database, the subsequent ones will be dropped due to being non unique.

Just delete that line from your SQL before executing it to create the SQL table.

[Vulpinus]

I just want to say thank you Sassy for this info, and the video. You really could not have made it much easier for people to follow. And to Roscko too, for saving me having to read the wiki and make the changes myself. I read it anyway to check, but that's me.

It took all of about five minutes to do the whole thing. Shame it then took an hour to sort out the ridiculous market place reports into a format that I could use to import and populate the database with my existing transactions. Stupid marketplace!

Withing a few minutes of going live, my first new order had been added to the database. Nice! I would have given the buyer a discount for being so helpful, but he bought a freebie. Spend some money you cheapskate!

Now to write a fancy vendor.

Thanks Sassy!

 

 

[Sassy Romano]

You're welcome. I'm glad it's still useful.

 

Did you export your MP data as csv or xls? Both of those are an option if memory serves me correctly so should have been relatively trivial to import tío your SQL db.

 

My memory could be wrong though, I've used ANS since xstreet days,before LL got anywhere near it so exports from MP aren't something I've needed to do.

[Vulpinus]

CSV, I've not come across an XLS option for the purchase reports. The issue was that what I thought was the Item ID in the report, wasn't the Item ID even though it was called "Order Item ID". I didn't notice until I had imported the data a few times and wondered why it didn't seem right.

I ended up having to combine both the CSV export report, which had the buyer and recipient, and the TXN CSV export report which had the actual Item ID albeit sometimes in one field and sometimes in another, just to be extra annoying.*

Anyway, that's done now and everything seems to be working as expected.

 

*I've just spotted why that is - I had a comma in some of the item titles which has then got involved in the CSV commas in the export. Note to self: don't put commas in things that are going to be CSV'd.

[Vulpinus]

This is an update to Sassy's LSL script to push sales from in-world vendors to the database.

I've just added the hash generation (well, it's better than no security at all), escaped the string fields to get around the eaten-space issue and changed some of the field names to correspond with Roscko's php script (because I'm easily confused otherwise).

Tested and working with my database with the test data in the example.

Thanks again to Sassy for the original.

 

// SaveToANS function and example code// Modified from original as follows:// 	Hash generation using salt from marketplace//	Escape string fields where needed//	Remove surplus fields//	Change field names to match Roscko's php codestring url = "http://mywebsite/myphpans.php"; // change this to your php code URLstring salt = "1234567890abcdef"; // change this to your saltkey reqid;SaveToANS(string TransactionID, string ItemID, string ItemName, string PayerName, key PayerKey, string ReceiverName, key ReceiverKey, string MerchantName, key MerchantKey, integer PaymentGross, string InventoryName, integer PaymentFee) {	string values =		"&TransactionID="+TransactionID+		"&ItemID="+ItemID+		"&ItemName="+llEscapeURL(ItemName)+		"&PayerName="+llEscapeURL(PayerName)+		"&PayerKey="+(string)PayerKey+		"&ReceiverName="+llEscapeURL(ReceiverName)+		"&ReceiverKey="+(string)ReceiverKey+		"&MerchantName="+llEscapeURL(MerchantName)+		"&MerchantKey="+(string)MerchantKey+		"&PaymentGross="+(string)PaymentGross+		"&InventoryName="+llEscapeURL(InventoryName)+		"&PaymentFee="+(string)PaymentFee;	reqid = llHTTPRequest(url+"?"+values,[HTTP_METHOD,"POST",HTTP_MIMETYPE,"application/x-www-form-urlencoded",HTTP_CUSTOM_HEADER,"X_ANS_VERIFY_HASH",llSHA1String(values+salt)], "");}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 lItemName = "Super New Product";		string lPayerName = "A Customer";		key lPayerKey = "123456789-abcd-abcd-abcd-0123456789ab";		string lReceiverName = "The Recipient";		key lReceiverKey = "123456789-abcd-abcd-abcd-0123456789ab";		string lMerchantName = "The Seller";		key lMerchantKey = "123456789-abcd-abcd-abcd-0123456789ab";		integer lPaymentGross = 100;		string lInventoryName="Inv Name";		integer lPaymentFee = 0;		// call the function to insert the data to your database		SaveToANS(lTransactionID,lItemID,lItemName,lPayerName,lPayerKey,			lReceiverName,lReceiverKey,lMerchantName,lMerchantKey,lPaymentGross,lInventoryName,lPaymentFee);	}	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);		}	}}

 

 

[tavomtorres93]

Hi Everyone.

I have a problem with this variable:

$ myMPHash = $ _SERVER ['HTTP_X_ANS_VERIFY_HASH'] ;.

It gives me the following error: Undefined Index HTTP_X_ANS_VERIFY_HASH.

And that's why I can not do the verification with my salt code. Can someone help me with this? Maybe the name of the variable is old? I appreciate your answers. 

 

 

 

<?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 = "HERE ME SALT CODE"; // 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 = "my server";    // Your database server$myUser   = "my user";             // Your database username$myPass   = "my  pass";             // Your database pass$myDB	  = "my databasename";   // Your database name$conexion = @mysqli_connect(SERVER, USER, PASSWORD, DB_NAME)	OR die('Could not connect to Database' . mysqli_connect_error());	if (!function_exists('sql_val')) {function sql_val( $input ) {     if ( get_magic_quotes_gpc() ) {          $input = stripslashes( $input );     } //get_magic_quotes_gpc()     return ( "'" . mysqli_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 = mysqli_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 = mysqli_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: ('.mysqli_errno().') '.mysqli_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;} ?>

[NeevaBecketEmagan]

I know that I'm a little late here, but for those that are having the undefined index error:

change 'HTTP_X_ANS_VERIFY_HASH' for 'X-ANS-Verify-Hash'

and if you get new type of errors, just try this:

$headers = apache_request_headers();

$myDDHash = $headers['X-ANS-Verify-Hash'];

The rest remains the same.

I got this problem now while I was writing my PHP file for the ANS.

[dk201]

Hi All, I just have a silly question about ANS. I set up my ANS server on a vps but i don't have a domain. My original plan is to let my marketplace to report to my ANS server via a url like http://11.33.22.44:5555/ans

However, I cannot save this url on Automatic Notifications (ANS) page under my merchant account, the error message is: The URL you're trying to use apears to be invalid

Do i need a domain to replace my server ip to make ANS feature work? Or maybe i can use google form as instead of a dedicate ANS server?

Thanks in advance for help,

 

-DK

 

[NaraPhox]

If anyone else is getting an undefined result, it's because the bone heads have capitalized the header name in the documentation when the actual headers are all lower case!

I got it as: x-ans-verify-hash