Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Incostistence between orders and items sold.


AndreD

Recommended Posts

not all orders in my webshop are used to calculate "items sold"

and more:

not all orders are taken into account when calculating "best customers"

 

I assume this happened because some orders stopped at "Preparing paypal IPN" , and then was confirmed as payed by me just by changing status.

 

Could somebody please help me to mark them as truly completed, so they are taken into consideration like the rest of the orders ?

 

Thanks.

Link to comment
Share on other sites

I do know which orders did not got registered, maybe a smart SQL query can transfer items from such order to sold items ?

 

like from ,idx_orders_products_products_id (products_id)

 

to table products ? ...I am obviously not good at this :)

Link to comment
Share on other sites

the most simple solution should be to find the piece of PHP in the Paypal Standard-plugin that processes the order when confirmed, and execute it manually with a manually set order ID.

 

could somebody please help me find that code ?

 

is it this ?

 

  if ($result == 'VERIFIED') {
if (isset($HTTP_POST_VARS['invoice']) && is_numeric($HTTP_POST_VARS['invoice']) && ($HTTP_POST_VARS['invoice'] > 0)) {
  $order_query = tep_db_query("select orders_status, currency, currency_value from " . TABLE_ORDERS . " where orders_id = '" . $HTTP_POST_VARS['invoice'] . "' and customers_id = '" . (int)$HTTP_POST_VARS['custom'] . "'");
  if (tep_db_num_rows($order_query) > 0) {
	$order = tep_db_fetch_array($order_query);

	if ($order['orders_status'] == MODULE_PAYMENT_PAYPAL_STANDARD_PREPARE_ORDER_STATUS_ID) {
	  $sql_data_array = array('orders_id' => $HTTP_POST_VARS['invoice'],
							  'orders_status_id' => MODULE_PAYMENT_PAYPAL_STANDARD_PREPARE_ORDER_STATUS_ID,
							  'date_added' => 'now()',
							  'customer_notified' => '0',
							  'comments' => '');

	  tep_db_perform(TABLE_ORDERS_STATUS_HISTORY, $sql_data_array);


	  tep_db_query("update " . TABLE_ORDERS . " set orders_status = '" . (MODULE_PAYMENT_PAYPAL_STANDARD_ORDER_STATUS_ID > 0 ? (int)MODULE_PAYMENT_PAYPAL_STANDARD_ORDER_STATUS_ID : (int)DEFAULT_ORDERS_STATUS_ID) . "', last_modified = now() where orders_id = '" . (int)$HTTP_POST_VARS['invoice'] . "'");
	}

	$total_query = tep_db_query("select value from " . TABLE_ORDERS_TOTAL . " where orders_id = '" . $HTTP_POST_VARS['invoice'] . "' and class = 'ot_total' limit 1");
	$total = tep_db_fetch_array($total_query);

	$comment_status = $HTTP_POST_VARS['payment_status'] . ' (' . ucfirst($HTTP_POST_VARS['payer_status']) . '; ' . $currencies->format($HTTP_POST_VARS['mc_gross'], false, $HTTP_POST_VARS['mc_currency']) . ')';

	if ($HTTP_POST_VARS['payment_status'] == 'Pending') {
	  $comment_status .= '; ' . $HTTP_POST_VARS['pending_reason'];
	} elseif ( ($HTTP_POST_VARS['payment_status'] == 'Reversed') || ($HTTP_POST_VARS['payment_status'] == 'Refunded') ) {
	  $comment_status .= '; ' . $HTTP_POST_VARS['reason_code'];
	}

	if ($HTTP_POST_VARS['mc_gross'] != number_format($total['value'] * $order['currency_value'], $currencies->get_decimal_places($order['currency']))) {
	  $comment_status .= '; PayPal transaction value (' . tep_output_string_protected($HTTP_POST_VARS['mc_gross']) . ') does not match order value (' . number_format($total['value'] * $order['currency_value'], $currencies->get_decimal_places($order['currency'])) . ')';
	}

	$sql_data_array = array('orders_id' => $HTTP_POST_VARS['invoice'],
							'orders_status_id' => (MODULE_PAYMENT_PAYPAL_STANDARD_ORDER_STATUS_ID > 0 ? (int)MODULE_PAYMENT_PAYPAL_STANDARD_ORDER_STATUS_ID : (int)DEFAULT_ORDERS_STATUS_ID),
							'date_added' => 'now()',
							'customer_notified' => '0',
							'comments' => 'PayPal IPN Verified [' . $comment_status . ']');

	tep_db_perform(TABLE_ORDERS_STATUS_HISTORY, $sql_data_array);
  }
}

Link to comment
Share on other sites

(damn, cannot edit posts.)

 

the previous post was wrong, the real update is done by paypal_standard.php

// Stock Update - Joao Correia
	if (STOCK_LIMITED == 'true') {
	  if (DOWNLOAD_ENABLED == 'true') {
		$stock_query_raw = "SELECT products_quantity, pad.products_attributes_filename
							FROM " . TABLE_PRODUCTS . " p
							LEFT JOIN " . TABLE_PRODUCTS_ATTRIBUTES . " pa
							ON p.products_id=pa.products_id
							LEFT JOIN " . TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD . " pad
							ON pa.products_attributes_id=pad.products_attributes_id
							WHERE p.products_id = '" . tep_get_prid($order->products[$i]['id']) . "'";
// Will work with only one option for downloadable products
// otherwise, we have to build the query dynamically with a loop
		$products_attributes = $order->products[$i]['attributes'];
		if (is_array($products_attributes)) {
		  $stock_query_raw .= " AND pa.options_id = '" . $products_attributes[0]['option_id'] . "' AND pa.options_values_id = '" . $products_attributes[0]['value_id'] . "'";
		}
		$stock_query = tep_db_query($stock_query_raw);
	  } else {
		$stock_query = tep_db_query("select products_quantity from " . TABLE_PRODUCTS . " where products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");
	  }
	  if (tep_db_num_rows($stock_query) > 0) {
		$stock_values = tep_db_fetch_array($stock_query);
// do not decrement quantities if products_attributes_filename exists
		if ((DOWNLOAD_ENABLED != 'true') || (!$stock_values['products_attributes_filename'])) {
		  $stock_left = $stock_values['products_quantity'] - $order->products[$i]['qty'];
		} else {
		  $stock_left = $stock_values['products_quantity'];
		}
		tep_db_query("update " . TABLE_PRODUCTS . " set products_quantity = '" . $stock_left . "' where products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");
		if ( ($stock_left < 1) && (STOCK_ALLOW_CHECKOUT == 'false') ) {
		  tep_db_query("update " . TABLE_PRODUCTS . " set products_status = '0' where products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");
		}
	  }
	}

// Update products_ordered (for bestsellers list)
	tep_db_query("update " . TABLE_PRODUCTS . " set products_ordered = products_ordered + " . sprintf('%d', $order->products[$i]['qty']) . " where products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");

//------insert customer choosen option to order--------
	$attributes_exist = '0';
	$products_ordered_attributes = '';
	if (isset($order->products[$i]['attributes'])) {
	  $attributes_exist = '1';
	  for ($j=0, $n2=sizeof($order->products[$i]['attributes']); $j<$n2; $j++) {
		if (DOWNLOAD_ENABLED == 'true') {
		  $attributes_query = "select popt.products_options_name, poval.products_options_values_name, pa.options_values_price, pa.price_prefix, pad.products_attributes_maxdays, pad.products_attributes_maxcount , pad.products_attributes_filename
							   from " . TABLE_PRODUCTS_OPTIONS . " popt, " . TABLE_PRODUCTS_OPTIONS_VALUES . " poval, " . TABLE_PRODUCTS_ATTRIBUTES . " pa
							   left join " . TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD . " pad
							   on pa.products_attributes_id=pad.products_attributes_id
							   where pa.products_id = '" . $order->products[$i]['id'] . "'
							   and pa.options_id = '" . $order->products[$i]['attributes'][$j]['option_id'] . "'
							   and pa.options_id = popt.products_options_id
							   and pa.options_values_id = '" . $order->products[$i]['attributes'][$j]['value_id'] . "'
							   and pa.options_values_id = poval.products_options_values_id
							   and popt.language_id = '" . $languages_id . "'
							   and poval.language_id = '" . $languages_id . "'";
		  $attributes = tep_db_query($attributes_query);
		} else {
		  $attributes = tep_db_query("select popt.products_options_name, poval.products_options_values_name, pa.options_values_price, pa.price_prefix from " . TABLE_PRODUCTS_OPTIONS . " popt, " . TABLE_PRODUCTS_OPTIONS_VALUES . " poval, " . TABLE_PRODUCTS_ATTRIBUTES . " pa where pa.products_id = '" . $order->products[$i]['id'] . "' and pa.options_id = '" . $order->products[$i]['attributes'][$j]['option_id'] . "' and pa.options_id = popt.products_options_id and pa.options_values_id = '" . $order->products[$i]['attributes'][$j]['value_id'] . "' and pa.options_values_id = poval.products_options_values_id and popt.language_id = '" . $languages_id . "' and poval.language_id = '" . $languages_id . "'");
		}
		$attributes_values = tep_db_fetch_array($attributes);

		$products_ordered_attributes .= "\n\t" . $attributes_values['products_options_name'] . ' ' . $attributes_values['products_options_values_name'];
	  }
	}
//------insert customer choosen option eof ----
	$total_weight += ($order->products[$i]['qty'] * $order->products[$i]['weight']);
	$total_tax += tep_calculate_tax($total_products_price, $products_tax) * $order->products[$i]['qty'];
	$total_cost += $total_products_price;

	$products_ordered .= $order->products[$i]['qty'] . ' x ' . $order->products[$i]['name'] . ' (' . $order->products[$i]['model'] . ') = ' . $currencies->display_price($order->products[$i]['final_price'], $order->products[$i]['tax'], $order->products[$i]['qty']) . $products_ordered_attributes . "\n";
  }

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...