Order status not updating event though transaction successful

[qme1ster]

Hi,

VM1.1.3 with Joomla 1.0.15

I'm testing with PayPal sandbox and am struggling to resolve this issue.
I've processed several test transactions, all of which were successful - i'm directed to the paylPal sandbox site, payment processed successfully, confirmation order emails generated - but with status: PENDING. I don't understand why the status remains as pending even though the transactions were successful.

In both the payPal buyer and seller test accounts the transactions appear as payment status: COMPLETED.

I've checked that in the payPal config in VM admin that "order status for successful transactions" is set to CONFIRMED.

Have I missed something somewhere ?

Grateful for any assistance.

Thanks in advance

Q.

[hellodave]

Different version of Joomla here but I imagine this bit it the same.
I read somewhere that paypal often returns a pending message for completed transactions.
The workaround is in your paypal config set the "Order Status for Pending Payments" to "Confirmed."

If paypal sends notify.php a message, most of the time it means everything worked fine.
I might be wrong, but if a payment fails I don't think paypal sends the message (IPN) to notify.php.

Is the code in your "payment extra" info section standard?
Because that didn't work for me, it needs a bit of tweaking.
There are a couple of good threads around here with updated code for that, Ill dig them out if you want.

[qme1ster]

Hi there hellodave, and thanks for the response.

First of all, I would have thought that if payPal is processing the payments and indeed shows them as successful in both the buyer and seller accounts, then this means that the message being sent should be "successful" rather than "pending", or are you saying, yes that's how it should be but in fact it doesn't happen that way and I should do as you suggested, set the "Order Status for Pending Payments" to "Confirmed." ? I am just a little worried about doing this if somehow it leads my client to ship stock which is not paid for. I guess maybe we need to confirm as you say, whether a message is indeed sent to notify.php if the payment fails.

My payment extra info is (I think) standard:-

Code Select Expand


<?php
$db1 = new ps_DB();
$q = "SELECT country_2_code FROM #__vm_country WHERE country_3_code='".$user->country."' ORDER BY country_2_code ASC";
$db1->query($q);

$url = "https://www.sandbox.paypal.com/cgi-bin/webscr";
$tax_total = $db->f("order_tax") + $db->f("order_shipping_tax");
$discount_total = $db->f("coupon_discount") + $db->f("order_discount");
$post_variables = Array(
"cmd" => "_ext-enter",
"redirect_cmd" => "_xclick",
"upload" => "1",
"business" => PAYPAL_EMAIL,
"receiver_email" => PAYPAL_EMAIL,
"item_name" => $VM_LANG->_('PHPSHOP_ORDER_PRINT_PO_NUMBER').": ". $db->f("order_id"),
"order_id" => $db->f("order_id"),
"invoice" => $db->f("order_number"),
"amount" => round( $db->f("order_subtotal")+$tax_total-$discount_total, 2),
"shipping" => sprintf("%.2f", $db->f("order_shipping")),
"currency_code" => $_SESSION['vendor_currency'],

"address_override" => "1",
"first_name" => $dbbt->f('first_name'),
"last_name" => $dbbt->f('last_name'),
"address1" => $dbbt->f('address_1'),
"address2" => $dbbt->f('address_2'),
"zip" => $dbbt->f('zip'),
"city" => $dbbt->f('city'),
"state" => $dbbt->f('state'),
"country" => $db1->f('country_2_code'),
"email" => $dbbt->f('user_email'),
"night_phone_b" => $dbbt->f('phone_1'),
"cpp_header_image" => $vendor_image_url,

"return" => SECUREURL ."index.php?option=com_virtuemart&page=checkout.result&order_id=".$db->f("order_id"),
"notify_url" => SECUREURL ."administrator/components/com_virtuemart/notify.php",
"cancel_return" => SECUREURL ."index.php",
"undefined_quantity" => "0",

"test_ipn" => PAYPAL_DEBUG,
"pal" => "NRUBJXESJTY24",
"no_shipping" => "1",
"no_note" => "1"
);
if( $page == "checkout.thankyou" ) {
$query_string = "?";
foreach( $post_variables as $name => $value ) {
$query_string .= $name. "=" . urlencode($value) ."&";
}
vmRedirect( $url . $query_string );
} else {
echo '<form action="'.$url.'" method="post" target="_blank">';
echo '<input type="image" name="submit" src="https://www.paypal.com/en_US/i/btn/x-click-but6.gif" alt="Click to pay with PayPal - it is fast, free and secure!" />';

foreach( $post_variables as $name => $value ) {
echo '<input type="hidden" name="'.$name.'" value="'.htmlspecialchars($value).'" />';
}
echo '</form>';

}
?>

I would be grateful if you had the time to dig out your payment info extra code if in fact you are using something different. If you are using something different, could i ask you to explain why you ended up changing from the standard ?

Thanks again.

Q.

[hellodave]

First of all, I would have thought that if payPal is processing the payments and indeed shows them as successful in both the buyer and seller accounts, then this means that the message being sent should be "successful" rather than "pending", or are you saying, yes that's how it should be but in fact it doesn't happen that way and I should do as you suggested, set the "Order Status for Pending Payments" to "Confirmed." ? I am just a little worried about doing this if somehow it leads my client to ship stock which is not paid for. I guess maybe we need to confirm as you say, whether a message is indeed sent to notify.php if the payment fails.

Yes, that is how it should be, but I believe this to be an error on paypal.
But, I only worked this stuff out a week ago so I could be wrong

Paypal sends you an email when successful payment is received, I would always check this has arrived before shipping stock.
Obviously this is a small business solution, not practical if you're selling by the lorry load.
I know it feels like a hack just to get it to work, but hey, if it works it works

I had a few problems with the standard paypal setup, product details not being sent to paypal, stock reduced and confirmation email sent before payment received, etc.
After reading a lot on the forum, particularly this thread -

Send Product Name and Attributes to Paypal

I ended up with this code -
-----WARNING-------WARNING-------WARNING-------WARNING-------
Do not use this code unedited, please try to understand the changes I have made.
They are probably not exactly what you want and may break paypal for you if used on its own.
-----WARNING-------WARNING-------WARNING-------WARNING-------

Code Select Expand

<?php
$url = "https://www.paypal.com/cgi-bin/webscr";
$order_id = $db->f("order_id");
$tax_total = $db->f("order_tax") + $db->f("order_shipping_tax");
$discount_total = $db->f("coupon_discount") + $db->f("order_discount");  

// Query for Order Items
$dboi = new ps_DB;
$q_oi = "SELECT * FROM #__vm_order_item ";
$q_oi .= "WHERE #__vm_order_item.order_id='$order_id'";
$dboi->query($q_oi);

$row_num = $dboi->num_rows();

//New code Getting Cart Items
$auth = $_SESSION['auth'];
$cart = $_SESSION['cart'];
$t_quantity = 0;
$disc_perItem = 0;
$i=1;

// Query to get User Info
$dbb = new ps_DB;
$q = "SELECT * FROM #__vm_user_info ";
$q .= "WHERE user_id ='".$my->id."' ";
$dbb->setQuery($q);
$dbb->query();

// Handle discounts and attributes
$discount_totalCP = $db->f("coupon_discount") + $db->f("order_discount");

while($dboi->next_record()) {
  $t_quantity = $t_quantity + intval($dboi->f("product_quantity"));
}

$dboi = null;
$dboi = new ps_DB;
$dboi->query($q_oi);

if($t_quantity > 0)
{
  if($discount_totalCP > 0) {
    $disc_perItem = round($discount_totalCP / $t_quantity, 2);
  }
  else {
    $disc_perItem = 0;
  }
}
else {
  $disc_perItem = 0;
}

while($dboi->next_record()) {

  $prod_attrib = $dboi->f("product_attribute");
  $supp_var['item_name_' . $i] = strip_tags("Order #". $db->f("order_id").": ". $dboi->f("order_item_name"));
  
  if($prod_attrib > ''){
    $attributes_array = explode('<br/>',$prod_attrib);
    $v = 0;
    $z = 1;
    foreach ( $attributes_array as $attributes_value){
      $attrib_name = trim(substr($attributes_value, 0, strpos($attributes_value, ':')), " ");
      $supp_var['on' . $z . '_' . $i] = $attrib_name;
//      $attrib_sel = trim(substr_replace(substr_replace($attributes_value, "", 0, strrpos($attributes_value, ':')), //"", 0, 2)); I do not want to strip all colons
      $attrib_sel = trim(substr($attributes_value, strpos($attributes_value, ':')+1));
      $supp_var['os' . $z . '_' . $i] = $attrib_sel;
      $v++;
      $z++;
      unset($attributes_value);
    }
  }

  $supp_var['item_number_' . $i] = $dboi->f("order_item_sku");
  $supp_var['quantity_' . $i] = $dboi->f("product_quantity");
  $supp_var['amount_' . $i] = round(($dboi->f("product_item_price") - $disc_perItem),2);
  $i++;
}

//Ship to Address Versus Bill To Address

//Query used to compare Bill to versus Shipp to address
$dboui = new ps_DB;
$q_oui = "SELECT * FROM #__vm_order_user_info ";
$q_oui .= "WHERE #__vm_order_user_info.order_id='$order_id' ORDER BY #__vm_order_user_info.order_info_id DESC";
$dboui->query($q_oui);

$oui_id = $dboui->f("order_info_id");

if($oui_id == $order_id){
  $first_name = $dbb->f("first_name");
  $last_name = $dbb->f("last_name");
  $address1 = $dbb->f("address_1");
  $address2 = $dbb->f("address_2");
  $city = $dbb->f("city");
  $state = $dbb->f("state");
  $address_country = $dbbt->f("country");
  $zip = $dbb->f("zip");
  $H_PhoneNumber = $dbb->f("phone_1");
}
else {
  $first_name = $dboui->f("first_name");
  $last_name = $dboui->f("last_name");
  $address1 = $dboui->f("address_1");
  $address2 = $dboui->f("address_2");
  $city = $dboui->f("city");
  $state = $dboui->f("state");
  $address_country = $dboui->f("country");
  $zip = $dboui->f("zip");
  $H_PhoneNumber = $dboui->f("phone_1");
}

// Builds array for the form
$post_variables = Array(
"cmd" => "_cart", //does not pass variables with return
//"cmd" => "_ext-enter", //will pass variables with return
"redirect_cmd" => "_xclick",
"upload" => "1",
//"page_style" => "paypal",
"business" => PAYPAL_EMAIL,
"currency_code" => $_SESSION['vendor_currency'],
"amount" => round( $db->f("order_subtotal")+$tax_total-$discount_total, 2),
"handling_cart" => "5.00",
"tax" => $tax_total,
"tax_cart" => $tax_total,
"invoice" => $db->f("order_number"),
//"myvar" => 'hello', paypal does not pass odd variables just custom
//"order_id" => $db->f("order_id"), order_id seems to be reserved in paypal
"image_url" => $vendor_image_url,
"return" => SECUREURL ."index.php?option=com_virtuemart&page=checkout.result&order_id=".$db->f("order_id"),
//"return" => SECUREURL ."paypal-return.html",
//"custom" => "option=com_virtuemart,page=checkout.result,order_id=".$order_id,
//you don't need to pass the option or page variables here use a menu link instead
"notify_url" => SECUREURL ."notify.php",
"cancel_return" => SECUREURL ."index.php",
"no_shipping" => "1",
"no_note" => "1",
"email" => $dbb->f("user_email"),
"address_override" => "1",
"first_name" => $first_name,
"last_name" => $last_name,
"address1" => $address1,
"address2" => $address2,
"city" => $city,
"state" => $state,
"country" => $address_country,
"zip" => $zip,
"night_phone_b" => $H_PhoneNumber,
"test_ipn" => PAYPAL_DEBUG,
"pal" => "NRUBJXESJTY24"
);
if( $page == "checkout.thankyou" ) {
  $query_string = "?";

  foreach( $post_variables as $name => $value ) {
    $query_string .= $name. "=" . urlencode($value) ."&";
  }

  if(is_array($supp_var) && count($supp_var)) {
    foreach($supp_var as $name => $value) {
      $query_string .= $name. "=" . urlencode($value) ."&";
    }
  }

  vmRedirect( $url . $query_string );
} 
else {
  echo '<form action="'.$url.'" method="post" target="_blank">';

  foreach( $post_variables as $name => $value ) {
    echo '<input type="hidden" name="'.$name.'" value="'.$value.'" />';
  }
  
  if(is_array($supp_var) && count($supp_var)) {
    foreach($supp_var as $name => $value) {
      echo '<input type="hidden" name="'.$name.'" value="'.$value.'" />';
    }
  }
  echo '<input type="image" name="submit" src="https://www.paypal.com/en_GB/i/btn/x-click-but6.gif" border="0" alt="Make payments with PayPal, it is fast, free, and secure!">';
  echo '</form>';
}
?>

A couple of things to note about this -

Most of the changes you will find in the thread above.

I have hard coded my shipping value to get it to paypal.
I use a flat rate so this is ok for me, for the moment....

I have added a new notify.php in the root of my website, hence the location in the code above.
The new notify.php contains this code -

Code Select Expand

<?php
include('administrator/components/com_virtuemart/notify.php');
?>

That's from another thread in this forum that I can't find at the moment.
It allows you to password protect your admin folder with .htaccess without affecting paypal.

One final tip, if you use the notify.php from the 1.1.4 RC you can use the paypal debug setting in the config.
It replaces the obsolete eliteweaver thingy with the proper paypal sandbox.

Sorry this is so rambling, and doesn't even offer a full solution.
I have not gone into all the issues here, there are too many.
I managed to get it working well enough for me, hopefully you will to.

All credit to the people that originally posted this stuff in other threads 

Even though my site is live and working, I would like to keep working on this.
It would be good if we could produce a formal solution.
There has already been a lot of discussion on this here, would be great to pull it all together and update the stickied paypal guide.
Which I've just realised is where I read about the pending thing -

IMPORTANT: PayPal seems to return "Pending" very often, although the payment will be no problem. A further investigation, why the payment is still pending is not made at this time (we'll have to improve the paypal notify script to check that).
That's why you see "An error occured while processing your transaction. The status of your order could not be updated": You obviously haven't set the Order Status for pending payments to "confirmed".

[qme1ster]

Thanks again for taking the time to help out, and for sharing your code etc....but having considered it all, I think I might be muddying the waters if I were to implement your changes, especially as the changes you made weren't specifically to address the issue I'm having.

I found this other post here regarding the order status http://forum.virtuemart.net/index.php?topic=52872.msg174360#msg174360 but once again there was no definitive explanation/ answer. I tried the changes suggested there but without luck.

I'm really just trying to get down to the bottom of why the order status does not change. It was said in the post from the link above:-

In my many hours in developing custom code for the paypal module I found that the "notify.php" code will reject the IPN from the sandbox because it's not from an approved paypal server.   

What is the reason for this and how do we resolve ? The solution put forward in that post doesn't help me as the suggested code matches what I already had as default. 

I think you're correct - we do need a definitive guide for this topic area. Too many people are experiencing these issues it seems but there's a distinct lack of official instruction.

I'd be grateful for more opinions and input from all who've had experience with this issue. Maybe then we can compile a tried and tested solution to make all of our lives easier ! Please help if you can...

Many many thanks

Q.

[hellodave]

I'm really just trying to get down to the bottom of why the order status does not change.

My understanding is that the order status does not change because paypal is returning a pending status even when payment is complete.
If this is true then do we need to complain to paypal about it?

In my many hours in developing custom code for the paypal module I found that the "notify.php" code will reject the IPN from the sandbox because it's not from an approved paypal server.   

What is the reason for this and how do we resolve ? The solution put forward in that post doesn't help me as the suggested code matches what I already had as default. 

Yeah, the code ajredding posted looks the same as default to me.
I did not have a problem with rejected IPNs from the sandbox, it worked just fine apart from this pending thing.

I would love to work all this out, really do some proper research, but my time is limited at the moment.
I'm posting here when I should be doing something else 

[qme1ster]

hellodave, you are one of the good guys I really appreciate your efforts to help.

So just out of curiosity, I tried changing the payPal configuration options, this time I set "Order Status for Pending Payments" as CONFIRMED. Then I processed another order. The PayPal transaction was successful - both the buyer and seller accounts confirm this, the status on each account was "Completed". BUT this time when directed back to the store there's an error message which states "An error occured while processing your transaction. The status of your order could not be updated". Now I'm really starting to pull my hair out !

The email went out exactly as previously, with status PENDING.

[hellodave]

I'm glad to help where I can, you get out what you put in
We are all in the same boat really.

Very odd, that error message is called on checkout.result.php when the status is not pending or confirmed.
I assume the order is in your database, what is the status set to?

[fire2]

hi hellodave,
   I was having problems with the status not updating to Confirmed in the live mode even when the payments were successful, and I tried replacing the "Payment extra info" in the PayPal method form and now PayPal is not catching the right the right amount to pay (Example, in the checkout you see you have to pay $20 and when you click on Confirm Order and it takes you to PayPal it says you have to pay $15) Why does this happen???

Do you have any clue?

[hellodave]

Did you use the code I posted above unedited?
Because that's probably not a good idea
If not, post your code.

[fire2]

what shoul I edit in that code? I just copied and pasted it, it didn't look like I had to change much...

[hellodave]

I have hard coded my shipping value to get it to paypal.
I use a flat rate so this is ok for me, for the moment....

I have added a new notify.php in the root of my website, hence the location in the code above.
The new notify.php contains this code -

Code Select Expand

<?php
include('administrator/components/com_virtuemart/notify.php');
?>

These 2 points, that code has a handling charge of 5.00 hard coded into it.
You might want to put the original line back in and see if that works for you.
So where I have -

Code Select Expand

"handling_cart" => "5.00",
I think the original is -

Code Select Expand

"shipping_1" => sprintf("%.2f", $db->f("order_shipping")),
But I have seen this used by some people -

Code Select Expand

"handling_cart" => sprintf("%.2f", $db->f("order_shipping")),

The other point is about moving notify.php, if you have not done that you need to change that back.
Where I have -

Code Select Expand

"notify_url" => SECUREURL ."notify.php",
Change it to -

Code Select Expand

"notify_url" => SECUREURL ."administrator/components/com_virtuemart/notify.php",

Oh, and change the url at the top to the sandbox version while you are testing.
Sorry for the confusion, hope I didn't miss anything.

[hellodave]

I just noticed another problem with the code I posted.
For some reason in the sql I had changed the original table names to the prefixed ones.
Where I had put -
$q_oi = "SELECT * FROM jos_vm_order_item ";
It should of been -
$q_oi = "SELECT * FROM #__vm_order_item ";
The original post has been modified with this changed back.
I'm pretty sure that with a default installation this would be no problem, but it's probably a bad thing to do

[fire2]

Thankyou very much. I actually did create a new notify.php under the root folder. But I'm still having one problem: I added a discount/fee to the PayPal payment method that is not being sent to PayPal when you confirm your order. Is any code missing or wrong?

By the way, do you know why the payment method fee/discount does not include the shipping fee? It only calculates the percentage, in my case, to the subtotal (products order) but does not add the percentage of the shipping fee to the total. I've seen this issue in many forum topics but nobody seems to have a reply to fix it.

Well, at least I want the subtotal fee to be included in the amount when you are sent to PayPal.

Thanks in advance.

[hellodave]

Ill have a look into this tomorrow, I really must do other stuff today.
For some reason, my brain is stuck on this paypal thing, I so want to get it nailed I struggle to think about anything else! I think I need help.

In the mean time, you might want to have a close look at the variables paypal accepts.
Are we sending the appropriate ones for what we want to do?

HTML Variables for Website Payments Standard