Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

all orders cancelled


fizisition

Recommended Posts

My cart marked all orders cancelled but it does not show cancelled when you look up the customers info under the edit button.

 

1. Is there a reason my cart did this

 

2. Is there a way to change them all back to delivered without manually doing each one.

Link to comment
Share on other sites

Pat,

 

I encountered a similiar situation with a client. We had just installed the PayPal IPN and conducted a test transaction. Suddenly, he had 3 years worth of orders that had been changed to cancelled status. Needless to say but with 108,000 orders he was a bit nervous and somewhat aggravated!

 

The solution I provided for the database fix was to create a script that would query the orders table and each one that had a status that did NOT match the last recorded status in the orders_status_history table it would change it back.

 

Also, the script below will also BY DEFAULT not change anything. It will first run in preview only mode to show you what would be changed or needs to be changed. Then if you are satisfied with the preview output select the execute mode and it will change them to the last recorded order status automatically.

 

This script fixed an orders table with 108,000 records in under 30 seconds on a dual 2.4Ghz CPU, 1.5 Gb RAM, CentOS server.

 

Be sure to change the STATUS_ID definition near the top to match your desired last status ID. Not the status name...it has to be the actual ID of that status!

 

I offer no support for this script and offer it in the hopes it will be as useful to you in this situation as it was to my client.

 

Create a new file in your admin directory and name it whatever you want. For my client I called it 'fix-db.php". Save the file with the code below and call it in your browser -> yourdomain.com/admin/*yourscriptname*.php

 

Read before you do anything...it'll automatically run in preview mode UNLESS you select an execute option.

 

<?php
/*=======================================================================*\
|| #################### //-- SCRIPT INFO --// ########################## ||
|| #  Script name: Fix Order Status
|| #  Contribution: N/A - Custom solution
|| #  Version: 1.1
|| #  Date: 2 February 2005
|| # ------------------------------------------------------------------ # ||
|| #################### //-- COPYRIGHT INFO --// ######################## ||
|| #  Copyright (C) 2005 Bobby Easland            # ||
|| #  Internet moniker: Chemo         	 # ||	
|| #  Contact: [email protected]             # ||
|| #  Commercial Site: http://gigabyte-hosting.com/         # ||
|| #  GPL Dev Server: http://mesoimpact.com/     	 # ||
|| #                       # ||
|| #  This script is free software; you can redistribute it and/or     # ||
|| #  modify it under the terms of the GNU General Public License  # ||
|| #  as published by the Free Software Foundation; either version 2	# ||
|| #  of the License, or (at your option) any later version. 	 # ||
|| #               	 # ||
|| #  This script is distributed in the hope that it will be useful,	# ||
|| #  but WITHOUT ANY WARRANTY; without even the implied warranty of	# ||
|| #  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the     # ||
|| #  GNU General Public License for more details.         # ||
|| #               	 # ||
|| #  Script is intended to be used with:        # ||
|| #  osCommerce, Open Source E-Commerce Solutions         # ||
|| #  http://www.oscommerce.com              # ||
|| #  Copyright (c) 2003 osCommerce             # ||
|| ###################################################################### ||
\*========================================================================*/

// Change the status ID below to the correct one for your store
define('STATUS_ID', '3');

require('includes/application_top.php');

if ( isset($_GET['action']) ){
 switch (actons){
	 case 'reset_date':
   $date_checked = ' CHECKED';
   break;
	 case 'reset_last_status':
   $last_status_checked = ' CHECKED';
   break;
	 default:
   break;
 }
}

$execute = ( (isset($_GET['execute']) && $_GET['execute'] == 'true') ? true : false );
?>
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
<title>Fix-DB Script</title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
</head>
<body>
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->
<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2">
 <tr>
   <td width="<?php echo BOX_WIDTH; ?>" valign="top">
 <table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">
       <!-- left_navigation //-->
      	 <?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>
       <!-- left_navigation_eof //-->
    	 </table></td>
   <!-- body_text //-->
   <td width="100%" valign="top">

<!-- begin HTML form //-->
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="get">
 <input type="radio" name="action" value="reset_date"<? echo $date_checked; ?>>Reset status for date_purchased < 01/01/04<br>
 <input type="radio" name="action" value="reset_last_status"<? echo $last_status_checked; ?>>Reset status non-matching current VS last order history<br>
 <input type="radio" name="execute" value="true">Execute the action<br>
 <input type="radio" name="execute" value="view" CHECKED>View the output (no database changes)<br>
 <input type="submit" value="Do it...">	
</form>
<!-- end HTML form //-->
<?php
if ($execute) echo '<h1>Modifying database records</h1>';
else echo '<h1>NOT modifying database, viewing mode</h1>';

function check_status_match($oID, $current_status, &$foo){
 $last = tep_db_fetch_array(tep_db_query("SELECT orders_status_id as status, date_added FROM orders_status_history WHERE orders_id='".$oID."' ORDER BY date_added DESC LIMIT 1"));
 $foo = $last['status'];
 if ( $last['status'] == $current_status ) return true;
 else return false;
}

function reset_status($oID, $last_status){
 tep_db_query("UPDATE orders SET orders_status='".$last_status."' WHERE orders_id='".$oID."'");
 return true;
}

switch ( $action ){
 case 'reset_date':
	 $list = tep_db_query("SELECT orders_id as oid, orders_status as status, date_purchased FROM orders WHERE date_purchased < '2004/01/01 00:00:00' ORDER BY orders_id DESC");
	 while ($tmp = tep_db_fetch_array($list) ){
  	 echo '<b>Changing Status =></b> oID: ' . $tmp['oid'] . ', Current Status: ' . $tmp['status'] . ', Date: '. $tmp['date_purchased'] . '<br>';
  	 if ($execute) reset_status($tmp['oid'], STATUS_ID);
	 }
	 break;

 case 'reset_last_status':
	 $list = tep_db_query("SELECT orders_id as oid, orders_status as status, date_purchased FROM orders ORDER BY orders_id DESC");
	 while ($tmp = tep_db_fetch_array($list) ){
   if ( !check_status_match($tmp['oid'], $tmp['status'], $last_status) ) {
  	 echo '<b>Changing Status =></b> oID: ' . $tmp['oid'] . ', Current: ' . $tmp['status'] . ', Last: '. $last_status . '<br>';
  	 if ($execute) reset_status($tmp['oid'], $last_status);
   }
	 }
	 break;

 default:
	 echo '<h1>action not specified or invalid</h1>';
	 break;
} # end switch
?>	
</td>
   <!-- body_text_eof //-->
 </tr>
</table>
<!-- body_eof //-->
<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
<br>
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>

Bobby

Link to comment
Share on other sites

I cannot offer comment on cause of my client's root problem as there have been no further occurrences. Also, I cannot offer comment on your root problem as I have no knowledge of your setup or other factors that may have contributed to the melt down.

 

I hope the script works for you...

 

Bobby

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...