During a paypal transaction on your site the received amount didn't match ...

[stinga]

G'day all,

I got an email from my site...
During a paypal transaction on your site the received amount didn't match the order total.
                   Order ID: xx.
                   Order Number: xxxxxx.
                   The amount received was: 13.41 USD.
                   It should be: 13.41 GBP.

I think I know what the problem is..
Shop base currency is USD
Paypal account is GBP.
13.41 is the correct GBP price, so the value is correct but the currency is not.
I am not sure yet where it gets those values from but I assume it is not checking the order currency.

I have had another look and realise it is to with the sending of the information to paypal.
It should have sent 13.41GBP but it sent 13.41USD, not good.

I can only assume the customer elected to show the prices in GPB which joomla took as being USD...

I will need to have a look at the code.

Update...
Looking in
administrator/components/com_virtuemart/classes/payment/ps_paypal.cfg.php

I find

"amount" => round( $db->f("order_subtotal")+$tax_total-$discount_total, 2),
"shipping" => sprintf("%.2f", $db->f("order_shipping")),
"currency_code" => $_SESSION['vendor_currency'],



I am not sure what the amount (GBP/USD) would be, but if it was in GBP and the shop in USD then this is where the problem could stem from.

Update 2
I am positive this is what the problem is, I think
"currency_code" => $_SESSION['vendor_currency'],
should be
"currency_code" => $db->f("order_currency"),

This would send 13.41GPB to paypal and not 13.41USD.

Now I just need to find a way of testing it.

(Edited by stinga: corrected $db->f("order_currency"))

[daxander]

there is also another problem.

My PayPal fee is -3.50 percent.
On 21.00 USD, this amount is 0.74

But is shows Paypal(+0.62) !!!!

And THEN when I select next..  I see a fee of 0.74
So it does show he right amount, but it is crazy.

When I finally select next and go to the Paypal site, the amount is changed again !!  But now the total amount is not 21.74, BUT 21.72.

Can somebody please tell me what the hell is going on and why Virtuemart does not give support or updates on the Paypal module ?  It is totally crap.. everybody has he same problems.

Who can help ?

[MikeUK]

@stinga, do you have any currency modifications or extensions? I have a paypal account with GBP as the primary currency but have no problem with USD payments either through sites or directly.

[kelsay]

Guys, I have the same problem.

In the backend I have set my percentage to -4.00.
When I login as a user, and buy a product of €10,00, on the checkout page I get a fee of €0,34 ?  What happened to €0,40 ?
I think the fee is based on the price minus tax, so that would be €0,334.
But still, very odd.

[MikeUK]

This isn't really what the original poster was talking about. Anyway, that sounds like exactly what it is, the fee minus tax. This may be due to something missed in the VM code, or maybe tax settings? It would be surprising more people hae not posted about this if it is only a VM problem.

Probably some concrete testing ad results would help, with a note of the tax settings used.

[stinga]

G'day MikeUK,

I don't have any currency mods.
Do you have your shop and your paypal account in GBP?
We have our shop in USD base currency in USD and our paypal account in GBP, I suspect that is the problem, I have still not got around to testing the it. It is anew site that does not do much traffic (yet)

[stinga]

G'day all,

Finally got around to doing some more testing on this.
Whatever I did, I ended up with the currency coming across as USD.
So I changed out paypal account to be USD.
But that made no difference.
I then looked a bit more and discovered that table _vm_orders holds the order amount in the currency you have selected and this is what is sent to paypal, so there is no currency conversion at that point in time.
I then found that I had used an invalid column name in my code, so I changed
"currency_code" => $db->f("currency_code"),
to
"currency_code" => $db->f("order_currency"),

I also wrote a data_dump.pl and changed
$url = "https://www.paypal.com/cgi-bin/webscr";
to
$url = "http://xxxxx/xxxxx/data_dump.pl";

Then I could see all the data that was being sent to paypal.
Now that I have changed that erroneous code I am sending the correct data to paypal.
Just need to check with paypal now.

How do other people do this? I can't believe I am the only one with this problem...

[stinga]

G'day all,

So here is my latest 'Paypal: Payment Extra Info' data...

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);

//Get all the items on the order
$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();
$t_quantity = 0;
$i=1;

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));
      $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_final_price") - $disc_perItem),2);
  $i++;
}


$url = "https://www.paypal.com/cgi-bin/webscr";
//$url = "http://www.xxx.xxx/cgi-bin/data_dump.pl";
$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" => "_cart",
"redirect_cmd" => "_ext-enter",
"upload" => "1",
"business" => PAYPAL_EMAIL,
"receiver_email" => PAYPAL_EMAIL,
"invoice" => $db->f("order_number"),
"amount" => round( $db->f("order_subtotal")+$tax_total-$discount_total, 2),
"shipping_1" => sprintf("%.2f", $db->f("order_shipping")),
"currency_code" => $db->f("order_currency"),

"address_override" => "0",
"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'),

"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?option=com_virtuemart&page=checkout.result&order_id=".$db->f("order_id"),
"undefined_quantity" => "0",

"test_ipn" => PAYPAL_DEBUG,
"pal" => "????",
"no_shipping" => "1",
"no_note" => "1"
);

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">';
echo '<input type="image" name="submit" src="https://www.paypal.com/en_US/i/btn/x-click-but6.gif" border="0" 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>';

}
?>


This changes vendor currency being sent to paypal, it send the order currency.
It also shows individual order lines instead of just 'Order 31'.

It does work, I just need to tidy it up and remove the redundant guff.

[Changed again]
I discovered that it was not taking into account the tax amount, so I changed the line...

Code Select Expand


$supp_var['amount_' . $i] = round(($dboi->f("product_item_price") - $disc_perItem),2);

to

Code Select Expand


$supp_var['amount_' . $i] = round(($dboi->f("product_final_price") - $disc_perItem),2);

product_final_price includes tax (or VAT in my case)

[hotelathome]

What a shame this code doesnt work with tax and shipping and also with discount coupons.
I am trying to find a fix for this:
http://forum.virtuemart.net/index.php?topic=58724.0
http://forum.virtuemart.net/index.php?topic=63776.0

It looks so much better in paypal with this coding, I just wish it could show the products including tax, then subtotal (including tax) minus the discount amount and then add the shipping (which includes tax) then let them know how much tax was paid:
1 x item   (inc tax) $20.00
1 x item   (inc tax) $15.00
SUB TOTAL  (inc tax) $35.00
less discount       -$10.00
shipping   (inc gst) $15.00

TOTAL      (inc GST) $40.00
TAX PAID             $ 4.00

(the tax amount is not correct but you get my drift)

[stinga]

G'day,

It would not be that hard to add, you just need to set the correct variables, I probably should have them set but have not bothered yet!

I think you would need to change

Code Select Expand


 $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_final_price") - $disc_perItem),2);
 $i++;

to

Code Select Expand


 $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("??price before tax") - $disc_perItem),2);
 $supp_var['tax_' . $i] = round(($dboi->f("??tax amount??") - $disc_perItem),2);
 $i++;


Sort off, not sure where you would want the discount calculated, but you get the idea.

After rereading you post I am not sure you are ever going to get that exact layout. I remember why I didn't add it now... This was just a payent gateway, the invoice shows the breakdown of costs etc. I have not lived in Oz since GST so I am not sure about the legalities of what need to be shown. We are small enough to be below the radar on this sort of thing and I assume the invoice we generate is good enough.

Some time later...
I think you can, thinking about it... I notice in the docs that you can just pass the full vat amount in a single line, so you should be able to get close to what you want.

[tvigar]

Thanks for all this - the call for the correct currency greatly helped... the code I know use - thanks to several contributors on this forum is compiled like this:

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();
 
//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();
 
//logic for applying discounts to multiple items
$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;
}
//query to optain product attributes
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));
      $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++;
}
 
 
//Query used to find whether to use Bill Address or Ship 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",
"upload" => "1",
"page_style" => "paypal",
"business" => PAYPAL_EMAIL,
"currency_code" => $db->f("order_currency"),
"amount" => round( $db->f("order_subtotal")+$tax_total-$discount_total, 2),
"handling_cart" => sprintf("%.2f", $db->f("order_shipping")),
"tax" => $tax_total,
"tax_cart" => $tax_total,
"invoice" => $db->f("order_number"),
"image_url" => $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",
"no_shipping" => "1",
"no_note" => "1",
"email" => $dbb->f("user_email"),
"address_override" => "0",//change this to 0 if you have - Paypal does not allow your country of residence to ship to the country you wish to - errors
"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
);
//add and send the new variables
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_US/i/btn/x-click-but6.gif" border="0" alt="Make payments with PayPal, it is fast, free, and secure!">';
//Change the above image url for different languages and countries
  echo '</form>';
}
?>

Only thing I'm still struggling with is the successful change of the status of the order from pending to confirmed once payment has been processed.

If anyone has any ideas to contribute to make the code better - please do!

Cheers!

[stinga]

G'day,

My latest PayPal extra info for 1.1.2 looks like...

Code Select Expand


<?php

$db_customer = new ps_DB();
$q = "select last_name,first_name,address_1,address_2,city,state_name,country,country_2_code,zip,phone_1,user_email from #__vm_user_info ui , #__vm_order_item oi , #__vm_country , #__vm_state where oi.user_info_id=ui.user_info_id and country_3_code = country and state_2_code=state and order_id='$order_id'";

$db_customer->query($q);
$db_customer->next_record();

//Get all the items on the order
$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();
$t_quantity = 0;
$i=1;

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));
      $supp_var['os' . $z . '_' . $i] = $attrib_sel;
      $v++;
      $z++;
      unset($attributes_value);
    }
  }

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


$url = "https://www.paypal.com/cgi-bin/webscr";
//$url = "http://www.squangle.co.uk/cgi-bin/data_dump.pl";
$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" => "_cart",
"redirect_cmd" => "_ext-enter",
"upload" => "1",
"business" => PAYPAL_EMAIL,
"receiver_email" => PAYPAL_EMAIL,
"invoice" => $db->f("order_number"),
"amount" => round( $db->f("order_subtotal")-$discount_total, 2),
"tax_cart" => $tax_total,
"shipping_1" => sprintf("%.2f", $db->f("order_shipping")),
"currency_code" => $db->f("order_currency"),

"item_name" => "test order",

"address_override" => "0",
"first_name" => $db_customer->f('first_name'),
"last_name" => $db_customer->f('last_name'),
"address1" => $db_customer->f('address_1'),
"address2" => $db_customer->f('address_2'),
"zip" => $db_customer->f('zip'),
"city" => $db_customer->f('city'),
"state" => $db_customer->f('state_name'),
"country" => $db_customer->f('country_2_code'),
"email" => $db_customer->f('user_email'),
"night_phone_b" => $db_customer->f('phone_1'),

"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?option=com_virtuemart&page=checkout.result&order_id=".$db->f("order_id"),
"undefined_quantity" => "0",

"test_ipn" => PAYPAL_DEBUG,
"no_shipping" => "1",
"no_note" => "1"
);

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">';
echo '<input type="image" name="submit" src="https://www.paypal.com/en_US/i/btn/x-click-but6.gif" border="0" 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>';

}
?>