Guest Posted January 25, 2005 Posted January 25, 2005 Could really use some advice on this one; Installed v3.0 a month ago and all was working fine until today. What happened was I refunded a customer and ALL of my orders statuses got zapped! (changed to "Cancelled"). This, of course, has caused quite a bit of upset with customers. The weird part is that I've done two refunds previously, and both correctly updated the paypal status for the order and changed the status of the order to cancelled (and ONLY that order). The only difference I can find is that when I view the order the PayPal status is " No PayPal Transaction Information Available ()" and there is NO HISTORY for the order. This seems odd. (The order did take place, it's in PayPal obviously, or I could not have refunded it.) Any ideas? Any advice on how to fix this? I can not simply do a search/replace because we had several hundred really cancelled orders, and we have three different "done" statuses we use (shipped, emailed, and processed). Plus, there is all the "pending" and "processing" statuses. A huge mess :'( Quote
devosc Posted January 25, 2005 Posted January 25, 2005 can you check your paypal table and see what values are there for paypal_id, they should all be unique and that column should auto-increment. Looking over the script (that I have ?) the only chance of it updating more than one order at the same time is if all the values of the paypal_id are the same (and consequently) all the values in the orders table of payment_id are the same, i.e. paypal.paypal_id == orders.payment_id. I would also suggest reviewing the ipn debug emails, for the txn_ids, they should all be unique (as specified / sent by PayPal). Quote "Any fool can know. The point is to understand." -- Albert Einstein
Guest Posted January 25, 2005 Posted January 25, 2005 can you check your paypal table and see what values are there for paypal_id, they should all be unique and that column should auto-increment.Looking over the script (that I have ?) the only chance of it updating more than one order at the same time is if all the values of the paypal_id are the same (and consequently) all the values in the orders table of payment_id are the same, i.e. paypal.paypal_id == orders.payment_id. I would also suggest reviewing the ipn debug emails, for the txn_ids, they should all be unique (as specified / sent by PayPal). <{POST_SNAPBACK}> Looked at the PayPal table and all paypal_id values are unique (only 27 as most people pay via cc on my site). Plus, no paypal orders changed status and two previous refunds went fine. More investigation reveals: 1. The refunded order did not have a status (no status history at all) 2. The refunded order showed the PayPal status as "No PayPal Transaction Information Available" Why these two are like this I can only assume means that the paypal transaction IPN never made it back to the store for whatever reason (we have this happen from time to time with our credit card orders; the "OK" message just never comes back) 3. NONE of the non-paypal statuses changed (ie, all PayPal orders had their status's unchanged) 4. ALL non-paypal orders had their status set to "Cancelled" (over 16K orders!) (Just to note: this happened within seconds after I did the refund on paypal, and no one else was doing anything in the admin area and no orders had occured for an hour before... so I am 99.9% sure that it was the refund message from paypal that caused this.) It seems that somehow, the fact that the original order never got the paypal complete message, and/or that it had no status and no status history, caused a problem. I have trouble following this mod (it's cool, but there are a lot of files!) so I am not sure where to find the code, but I can theorize that somehow the order not having a status or paypal update caused an SQL update to match all non-paypal orders. The good news is that if I can figure out how to write the SQL (I am not a php/sql guy, it's all new to me) I should be able to recover by: loop through all orders if order_status == "cancelled" and order has at least one order status and the last status is not "cancelled" then set order_status to the last status in the order status history end loop Thoughts? Where would the update status code be found if I wanted to look at it and see if I could not figure out how this happened? Anyone able to help with the SQL code needed to get me recovered? Thanks! Quote
devosc Posted January 25, 2005 Posted January 25, 2005 loop through all orders if order_status == "cancelled" and order has at least one order status and the last status is not "cancelled" then set order_status to the last status in the order status history end loop Can you explain a little more what you expect the above will do, i.e. are you saying that if any order status (of that order) is cancelled *and* if the last one is not *cancelled* then update it to the last one? What I'm wondering is if the last order status is not cancelled then why update it? We might be able to do something based upon determining whether the orders.payment != 'PayPal' *and* the *last* orders.orders_status = cancelled. This will give a list of all non paypal orders where there order status is cancelled and then we could query what their previous order status was and set it to that and delete the last order status history (the cancelled one). I have known PayPal to send incomplete (but authentic) IPN's on an individual account basis. The flow of the ipn.php logic is in the abstract: $ipn = new PayPal_IPN($_POST); This implicitly determines that the paypal.txn_id is valid, i.e. non empty and of length 17 chars, so this would mean that PayPal are refering to a particular transaction. if(!$ipn->authenticate(MODULE_PAYMENT_PAYPAL_DOMAIN) && $ipn->testMode('Off')) $ipn->dienice('500'); This means that PayPal authenticated the IPN as being sent from them if($ipn->uniqueTxnID() && $ipn->isReversal()) { //parent_txn_id is the txn_id of the original transaction $txn = $ipn->queryTxnID($ipn->key['parent_txn_id']); if(count($txn) === 1) { $ipn->insert($txn['paypal_id']); // update the order's status switch ($ipn->reversalType()) { case 'Canceled_Reversal': $order_status = MODULE_PAYMENT_PAYPAL_ORDER_STATUS_ID; break; case 'Reversed': case 'Refunded': $order_status = MODULE_PAYMENT_PAYPAL_ORDER_CANCELED_STATUS_ID; break; } $ipn->updateOrderStatus($txn['paypal_id'],$order_status); } } This means that the paypal.txn_id is *unique* and that it is of a reversal *type* (Refunded, Reversed and Canceled_Reversal). So because we earlier determined that the length of the txn_id is 17 chars and have now determined that the paypal.txn_id is unique, means that this IPN has not been previously processed. From the above: $txn = $ipn->queryTxnID($ipn->key['parent_txn_id']); if(count($txn) === 1) { Means that we have found a previous IPN where the paypal.txn_id is equal to the paypal.parent_txn_id of the current IPN, this means that for the (Refunded) IPN just received we have found the previous associated IPN. $ipn->updateOrderStatus($txn['paypal_id'],$order_status); Means that we update the osC order status to Cancelled as below (IPN.class.php): function updateOrderStatus($paypal_id,$status) { //Orders $sql_data_array = array( 'orders_status' => tep_db_input($status), 'last_modified' => 'now()' ); tep_db_perform(TABLE_ORDERS, $sql_data_array, 'update', "payment_id = '" . (int)$paypal_id . "'"); //Orders Status History $sql_query = tep_db_query("select orders_id from " . TABLE_ORDERS . " where payment_id = '" . (int)$paypal_id . "'"); $sql_result = tep_db_fetch_array($sql_query); $sql_data_array = array( 'orders_id' => $sql_result['orders_id'], 'orders_status_id' => tep_db_input($status), 'date_added' => 'now()' ); tep_db_perform(TABLE_ORDERS_STATUS_HISTORY, $sql_data_array, 'insert'); } This updates the main osc orders table where the payment_id is equal to our paypal_id. So as long as the values of paypal.paypal_id are all unique and non-zero then only *one* order should be affected. And the corresponding order_status_history is created..... Anyway see what you think of the earlier mentioned fix, e.g. where "orders.payment != 'PayPal' *and* the orders.orders_status = cancelled" Whats the id value of your Cancelled orders status ? Have a look in the table orders_status. Quote "Any fool can know. The point is to understand." -- Albert Einstein
Guest Posted January 25, 2005 Posted January 25, 2005 Can you explain a little more what you expect the above will do, i.e. are you saying that if any order status (of that order) is cancelled *and* if the last one is not *cancelled* then update it to the last one? What I'm wondering is if the last order status is not cancelled then why update it?We might be able to do something based upon determining whether the orders.payment != 'PayPal' *and* the *last* orders.orders_status = cancelled. This will give a list of all non paypal orders where there order status is cancelled and then we could query what their previous order status was and set it to that and delete the last order status history (the cancelled one). The problem field is in the "orders" table (orders_status) and I'm looking at the "orders_status_history" table to get each order's last status (as these were not changed, and seem to be correct). So, if I determe the orders where orders.payment != 'PayPal' *and* the orders.order_status == 11(Cancelled) *and* the last order_status_history for that order != cancelled then I can get the last order_status_history entry for that order and update the orders.order_status to that value. $ipn->updateOrderStatus($txn['paypal_id'],$order_status); Means that we update the osC order status to Cancelled as below (IPN.class.php): function updateOrderStatus($paypal_id,$status) { //Orders $sql_data_array = array( 'orders_status' => tep_db_input($status), 'last_modified' => 'now()' ); tep_db_perform(TABLE_ORDERS, $sql_data_array, 'update', "payment_id = '" . (int)$paypal_id . "'"); //Orders Status History $sql_query = tep_db_query("select orders_id from " . TABLE_ORDERS . " where payment_id = '" . (int)$paypal_id . "'"); $sql_result = tep_db_fetch_array($sql_query); $sql_data_array = array( 'orders_id' => $sql_result['orders_id'], 'orders_status_id' => tep_db_input($status), 'date_added' => 'now()' ); tep_db_perform(TABLE_ORDERS_STATUS_HISTORY, $sql_data_array, 'insert'); } This updates the main osc orders table where the payment_id is equal to our paypal_id. So as long as the values of paypal.paypal_id are all unique and non-zero then only *one* order should be affected. And the corresponding order_status_history is created..... Thanks you! This is where the error lies... all orders that were changed have a payment_id of 0 (zero). As does the PayPal payment that got refunded. Why it had a payment id of zero I am not sure; probably because it did not get the "I paid" IPN back correctly, yes? It seems to me that the code either needs to check for the zero condition (to alert the store owner of a problem) and/or check that there is a matching txn_id to a parent_txn_id on refund IPNs (and any other IPN that should have a matching txn_id). The order status history table didn't get messed with because the code only seems to operate on a single entity; so probably my first matching order was modified...but I can't figure out which order that would be. Should be easy enough tho. Anyway see what you think of the earlier mentioned fix, e.g. where "orders.payment != 'PayPal' *and* the orders.orders_status = cancelled"Whats the id value of your Cancelled orders status ? Have a look in the table orders_status. <{POST_SNAPBACK}> My orders_status table is as follows: 1 1 Pending 2 1 Processing 3 1 Shipped 5 1 emailed 10 1 Updated 11 1 Cancelled 12 1 On Hold Other notes; I looked in my paypal table and found: a> all transaction ID's are unique b> the previous two refunds have a matching txn_id to the parent_txn_id c> the order that caused the problem does NOT have a match for it's parent_txn_id I'm not sure of the fix here; it seems that we do not have the order_id (which is why the paypal_id is used, correct?) so we may not be able to find the matching order for these cases. If so, receiving such an IPN should cause an email to be sent to the store owner with all pertinent info (much like the debug emails, which I had turned off a week earlier, of course) and a note that they need to manually take care of the problem. Thanks for the detailed and lengthy replies; much help there! Quote
devosc Posted January 26, 2005 Posted January 26, 2005 determe the orders where orders.payment != 'PayPal' *and* the orders.order_status == 11(Cancelled) *and* the last order_status_history for that order != cancelled then I can get the last order_status_history entry for that order and update the orders.order_status to that value <{POST_SNAPBACK}> Well I managed to replicate once instance of where all order statuses in the orders table was changed to Cancelled, this was becuase the IPN received did not have a parent_txn_id, even though the IPN was a refund. This leads me to believe that PayPal may of messed up, for example for another account, they were leaving the buisiness field blank and so all IPNs were considered invalid, even though that site was previously receving IPNs correctly, and turned out to be a (temporary) glitch. Anyway..... The following script should restore your order statuses. It will do as you suggested above. I would suggest that you first *backup* your site's database. Then maybe even create a mirror site with an independent copy of your sites database, and run the following script from the admin, i.e. upload to admin directory and in the url goto the filename you named the script; I called it forum_ipn_fix.php <?php require('includes/application_top.php'); if (!get_cfg_var('safe_mode')) set_time_limit(0); $cancelledQuery = tep_db_query("select orders_id from " . TABLE_ORDERS . " where payment_method != 'PayPal' and orders_status = 11"); $cancelled = array(); while($matched = tep_db_fetch_array($cancelledQuery)) $cancelled[] = $matched['orders_id']; $orders = array(); foreach($cancelled as $orders_id) { $statusQuery = tep_db_query("select orders_id, orders_status_id from " . [CODE]TABLE_ORDERS_STATUS_HISTORY . " where orders_id = " . (int) $orders_id . " order by date_added desc limit 1"); $order = tep_db_fetch_array($statusQuery); if($order['orders_status_id'] != '11') $orders["{$order['orders_id']}"] = $order['orders_status_id']; } foreach($orders as $orders_id => $orders_status_id) tep_db_perform(TABLE_ORDERS, array('orders_status' => (int) $orders_status_id), 'update', 'orders_id = ' . (int) $orders_id ); ?> What you can do, so as to prevent this from happening again is change in catalog/ipn.php line 41 from if($ipn->uniqueTxnID() && $ipn->isReversal()) { To if($ipn->uniqueTxnID() && $ipn->isReversal() && strlen($ipn->key['parent_txn_id']) == 17) { This will ensure that the parent_txn_id is also 17 chars in length also, e.g. not blank Quote "Any fool can know. The point is to understand." -- Albert Einstein
Guest Posted January 26, 2005 Posted January 26, 2005 Well I managed to replicate once instance of where all order statuses in the orders table was changed to Cancelled, this was becuase the IPN received did not have a parent_txn_id, even though the IPN was a refund. This leads me to believe that PayPal may of messed up, for example for another account, they were leaving the buisiness field blank and so all IPNs were considered invalid, even though that site was previously receving IPNs correctly, and turned out to be a (temporary) glitch. In my case I think an internet glitch caused the IPN notice to never arrive; the order had no status and no paypal transaction assigned. No problem until the valid refund IPN arrived and could not find a match. The following script should restore your order statuses. It will do as you suggested above. I would suggest that you first *backup* your site's database. Then maybe even create a mirror site with an independent copy of your sites database, and run the following script from the admin, i.e. upload to admin directory and in the url goto the filename you named the script; I called it forum_ipn_fix.php Thanks; I'll give it a try later today. (and of course backup the DB ... I actually have a cron job setup to backup the HTML and DB every night to a seperate HD.) What you can do, so as to prevent this from happening again is change in catalog/ipn.php line 41 from if($ipn->uniqueTxnID() && $ipn->isReversal()) { To if($ipn->uniqueTxnID() && $ipn->isReversal() && strlen($ipn->key['parent_txn_id']) == 17) { This will ensure that the parent_txn_id is also 17 chars in length also, e.g. not blank <{POST_SNAPBACK}> In my case this would not have helped, as the parent_txn_id was correct. The problem was that there was no parent to match to so the incoming $paypal_id was zero and thus matched every non-paypal order in the DB. So, while it is a good to ensure the parent_txn_id is good, it would seem that the DB operation tep_db_perform(TABLE_ORDERS, $sql_data_array, 'update', "payment_id = '" . (int)$paypal_id . "'"); is overly broad. Do we ever want to update more than a single order here? I can't think of any reason that this code should. So, at the very least it seems we should add a "LIMIT 1" to the query, yes? (Not sure how to do that with the call.) Also, we should have a check at the top of this function to check for $paypal_id == 0. That can never be legal (I'd hope), and if we get one it means that at the very least we don't want to perform the DB operations, and we really should somehow notifiy the store owner that a problem occured. I'd think causing a debug email to go to the store owner (ie, not the debug email addr, and even if debug emails are off) would be enough. Thanks! PS: sorry for the long delays in responding, I have to manually check for new posts; for some reason even though I have email notifications on in my profile I am not getting any. Quote
Guest Posted January 26, 2005 Posted January 26, 2005 Well I managed to replicate once instance of where all order statuses in the orders table was changed to Cancelled, this was becuase the IPN received did not have a parent_txn_id, even though the IPN was a refund. This leads me to believe that PayPal may of messed up, for example for another account, they were leaving the buisiness field blank and so all IPNs were considered invalid, even though that site was previously receving IPNs correctly, and turned out to be a (temporary) glitch. Another possibility that could occure is that someone sends a manual payment to you for a product (ie, goes to the paypal site directly instead of through your store) and then you later refund them. This would cause the above to happen. We have also in the past refunded portions of a customer's manual paypal payment (ie, part of the shipping cost for example), and this could cause such a problem. Quote
devosc Posted January 26, 2005 Posted January 26, 2005 Change $txn = $ipn->queryTxnID($ipn->key['parent_txn_id']); if(count($txn) === 1) { To $txn = $ipn->queryTxnID($ipn->key['parent_txn_id']); if(!empty($txn)) { Quote "Any fool can know. The point is to understand." -- Albert Einstein
Guest Posted January 31, 2005 Posted January 31, 2005 OK; so the fix turned out to be much more involved that I hoped, but I think I have got my DB back in order. Along the way I created a contribution to help others who may run across similar problems. Link to the Fix Order Status Forum Hope this helps someone else. PS: the trouble I ran into involved the fact that I had some orders that were "bogus/corrupt" (no order status, no status history and not even any products in the order), plus I found that the "order by date_added desc limit 1" did not work because many orders had two statuses added at the same time (ie, within one second of each other). The solution isn't "pretty", but it works. Quote
New 
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.