Bug in admin orders and customer's order history

[Juto]

During development of my site I have placed about 70 orders, just to debug and design.

I have then deleted all but one. Now, on the customers order history page (show all orders) the existing order do not show up, instead I got negative page indexes. The order don't show up in admin either, but can be found by search.

 

If I submit a new order, this order will be shown in admin as well as on the customers page, and the negative page indexes are gone. But, the old existing order do not show up.

 

The settings in admin do allow to show all orders.

 

I have read through a lot of threads about this issue, but none of the suggested solutions seems to be correct.

 

Since the old order can be found by search, I wonder if the queries for the customers history and for the orders in admin are wrong?

 

Any help is most appreciated.

 

Sara

[multimixer]

How did you delete the old orders, in admin or in the database directly?

[Juto]

Hi George, thanks for looking into this.

 

The delete was done using the delete action in admin. If it is of any help, I am using an upgraded RC2a.

 

In account_history we have:

 if ($orders_total > 0) {
   $history_query_raw = "select o.orders_id, o.date_purchased, o.delivery_name, o.billing_name, ot.text as order_total, s.orders_status_name from " . TABLE_ORDERS . " o, " . TABLE_ORDERS_TOTAL . " ot, " . TABLE_ORDERS_STATUS . " s where o.customers_id = '" . (int)$customer_id . "' and o.orders_id = ot.orders_id and ot.class = 'ot_total' and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and s.public_flag = '1' order by orders_id DESC";
   $history_split = new splitPageResults($history_query_raw, MAX_DISPLAY_ORDER_HISTORY);
   $history_query = tep_db_query($history_split->sql_query);

 

 

The bugg is revealed by these lines:

 <tr>
   <td>
  <table summary="" border="0" width="100%" cellspacing="0" cellpadding="2">
    <tr>
	  <td class="smallText" valign="top"><?php echo $history_split->display_count(TEXT_DISPLAY_NUMBER_OF_ORDERS); ?></td>
	  <td class="smallText" align="right"><?php echo TEXT_RESULT_PAGE . ' ' . $history_split->display_links(MAX_DISPLAY_PAGE_LINKS, tep_get_all_get_params(array('page', 'info', 'x', 'y'))); ?></td>
    </tr>
  </table>
   </td>
 </tr>

 

In admin/orders (search) we have:

<?php
   $orders_history_query = tep_db_query("select orders_status_id, date_added, customer_notified, comments from " . TABLE_ORDERS_STATUS_HISTORY . " where orders_id = '" . tep_db_input($oID) . "' order by date_added");
   if (tep_db_num_rows($orders_history_query)) {
  while ($orders_history = tep_db_fetch_array($orders_history_query)) {
    echo '		  <tr>' . "\n" .
		 '		    <td class="smallText" align="center">' . tep_datetime_short($orders_history['date_added']) . '</td>' . "\n" .
		 '		    <td class="smallText" align="center">';
    if ($orders_history['customer_notified'] == '1') {
	  echo tep_image(DIR_WS_ICONS . 'tick.gif', ICON_TICK) . "</td>\n";
    } else {
	  echo tep_image(DIR_WS_ICONS . 'cross.gif', ICON_CROSS) . "</td>\n";
    }
    echo '		    <td class="smallText">' . $orders_status_array[$orders_history['orders_status_id']] . '</td>' . "\n" .
		 '		    <td class="smallText">' . nl2br(tep_db_output($orders_history['comments'])) . ' </td>' . "\n" .
		 '		  </tr>' . "\n";
  }
   } else {
    echo '		  <tr>' . "\n" .
		 '		    <td class="smallText" colspan="5">' . TEXT_NO_ORDER_HISTORY . '</td>' . "\n" .
		 '		  </tr>' . "\n";
   }
?>

And:

<?php
   if (isset($_GET['cID'])) {
  $cID = tep_db_prepare_input($_GET['cID']);
  $orders_query_raw = "select o.orders_id, o.customers_name, o.customers_id, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.customers_id = '" . (int)$cID . "' and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by orders_id DESC";
   } elseif (isset($_GET['status']) && is_numeric($_GET['status']) && ($_GET['status'] > 0)) {
  $status = tep_db_prepare_input($_GET['status']);
  $orders_query_raw = "select o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and s.orders_status_id = '" . (int)$status . "' and ot.class = 'ot_total' order by o.orders_id DESC";
   } else {
  $orders_query_raw = "select o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by o.orders_id DESC";
   }
   $orders_split = new splitPageResults($_GET['page'], MAX_DISPLAY_SEARCH_RESULTS, $orders_query_raw, $orders_query_numrows);
   $orders_query = tep_db_query($orders_query_raw);
   while ($orders = tep_db_fetch_array($orders_query)) {
   if ((!isset($_GET['oID']) || (isset($_GET['oID']) && ($_GET['oID'] == $orders['orders_id']))) && !isset($oInfo)) {
    $oInfo = new objectInfo($orders);
  }

  if (isset($oInfo) && is_object($oInfo) && ($orders['orders_id'] == $oInfo->orders_id)) {
    echo '			  <tr id="defaultSelected" class="dataTableRowSelected" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href=\'' . tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('oID', 'action')) . 'oID=' . $oInfo->orders_id . '&action=edit') . '\'">' . "\n";
  } else {
    echo '			  <tr class="dataTableRow" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href=\'' . tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('oID')) . 'oID=' . $orders['orders_id']) . '\'">' . "\n";
  }
?>

 

 

Sara

[multimixer]

Sara, where does the problem appear, in admin or catalog, or both?

 

First thing to check is, how the data is in he DB.

 

Having just one order in there (the old, not deleted one) that appear only via search in admin, but nor regularly, take a look if the indexes in the DB are correct, as they are used by the query eg

 

$$history_query_raw = "select o.orders_id, o.date_purchased, o.delivery_name, o.billing_name, ot.text as order_total, s.orders_status_name from " . TABLE_ORDERS . " o, " . TABLE_ORDERS_TOTAL . " ot, " . TABLE_ORDERS_STATUS . " s where o.customers_id = '" . (int)$customer_id . "' and o.orders_id = ot.orders_id and ot.class = 'ot_total' and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and s.public_flag = '1' order by orders_id DESC";

 

in where clause: where o.customers_id = '" . (int)$customer_id . "' and o.orders_id = ot.orders_id and ot.class = 'ot_total' and o.orders_status = s.orders_status

 

The "seach" in admin does not check this all (looks only for: where orders_id = '" . tep_db_input($oID) . "') , that could be a reason why orders appear only via search

 

What I suspect is, that the delete didn't happen correctly, so the keys in the DB are messed up.

 

In this case, we need to look into the delete process

[Juto]

Hi George, it affect both sides.

 

I have searched the db and found that the orders_id are:

 

In table orders as:

insert into orders (orders_id, customers_id, customers_name, customers_company, customers_street_address, customers_suburb, customers_city, customers_postcode, customers_state, customers_country, customers_telephone, customers_email_address, customers_address_format_id, delivery_name, delivery_company, delivery_street_address, delivery_suburb, delivery_city, delivery_postcode, delivery_state, delivery_country, delivery_address_format_id, billing_name, billing_company, billing_street_address, billing_suburb, billing_city, billing_postcode, billing_state, billing_country, billing_address_format_id, payment_method, cc_type, cc_owner, cc_number, cc_expires, last_modified, date_purchased, orders_status, orders_date_finished, currency, currency_value) values ('39', '6', 'Sara Juto', '', 'Härnegatan 106', '', 'Ljungsbro', '59074', 'Östergötlands län', 'Sweden', '', '[email protected]', '5', 'Sara Juto', '', 'Härnegatan 106', '', 'Ljungsbro', '59074', 'Östergötlands län', 'Sweden', '5', 'Sara Juto', '', 'Härnegatan 106', '', 'Ljungsbro', '59074', 'Östergötlands län', 'Sweden', '5', 'Förskottsbetalning', '', '', '', '', NULL, '2012-02-18 16:25:07', '1', NULL, 'SEK', '1.000000');
insert into orders (orders_id, customers_id, customers_name, customers_company, customers_street_address, customers_suburb, customers_city, customers_postcode, customers_state, customers_country, customers_telephone, customers_email_address, customers_address_format_id, delivery_name, delivery_company, delivery_street_address, delivery_suburb, delivery_city, delivery_postcode, delivery_state, delivery_country, delivery_address_format_id, billing_name, billing_company, billing_street_address, billing_suburb, billing_city, billing_postcode, billing_state, billing_country, billing_address_format_id, payment_method, cc_type, cc_owner, cc_number, cc_expires, last_modified, date_purchased, orders_status, orders_date_finished, currency, currency_value) values ('68', '6', 'Sara Juto', '', 'Härnegatan 106', '', 'Ljungsbro', '59074', 'Östergötlands län', 'Sweden', '', '[email protected]', '5', 'Sara Juto', '', 'Härnegatan 106', '', 'Ljungsbro', '59074', 'Östergötlands län', 'Sweden', '5', 'Sara Juto', '', 'Härnegatan 106', '', 'Ljungsbro', '59074', 'Östergötlands län', 'Sweden', '5', 'Förskottsbetalning', '', '', '', '', NULL, '2012-05-11 10:53:42', '1', NULL, 'SEK', '1.000000');

In table orders_products as:

insert into orders_products (orders_products_id, orders_id, products_id, products_model, products_name, products_price, final_price, products_tax, products_quantity, products_stock_attributes) values ('68', '68', '62', '10-06-32-01', 'Tunika', '340.0000', '340.0000', '25.0000', '1', '');
insert into orders_products (orders_products_id, orders_id, products_id, products_model, products_name, products_price, final_price, products_tax, products_quantity, products_stock_attributes) values ('39', '0', '48', '10-04-16-01', 'Jumper', '292.0000', '292.0000', '25.0000', '1', '');

In table orders_status_history as:

insert into orders_status_history (orders_status_history_id, orders_id, orders_status_id, date_added, customer_notified, comments) values ('70', '68', '1', '2012-05-11 10:53:42', '1', '');
insert into orders_status_history (orders_status_history_id, orders_id, orders_status_id, date_added, customer_notified, comments) values ('41', '0', '1', '2012-02-18 16:25:07', '1', '');

In table orders_total as:

insert into orders_total (orders_total_id, orders_id, title, text, value, class, sort_order) values ('156', '0', 'Att betala:', '<b>465Kr</b>', '465.0000', 'ot_total', '4');
insert into orders_total (orders_total_id, orders_id, title, text, value, class, sort_order) values ('155', '0', 'varav moms:', '93Kr', '93.0000', 'ot_tax', '3');
insert into orders_total (orders_total_id, orders_id, title, text, value, class, sort_order) values ('154', '0', 'Fraktkostnad (Posten) avgift för paket:', '100Kr', '100.0000', 'ot_shipping', '2');
insert into orders_total (orders_total_id, orders_id, title, text, value, class, sort_order) values ('153', '0', 'Delsumma:', '365Kr', '365.0000', 'ot_subtotal', '1');
insert into orders_total (orders_total_id, orders_id, title, text, value, class, sort_order) values ('275', '68', 'Att betala:', '<strong>525Kr</strong>', '525.0000', 'ot_total', '4');
insert into orders_total (orders_total_id, orders_id, title, text, value, class, sort_order) values ('274', '68', 'varav moms:', '105Kr', '105.0000', 'ot_tax', '3');
insert into orders_total (orders_total_id, orders_id, title, text, value, class, sort_order) values ('273', '68', 'Fraktkostnad (Posten) avgift för paket:', '100Kr', '100.0000', 'ot_shipping', '2');
insert into orders_total (orders_total_id, orders_id, title, text, value, class, sort_order) values ('272', '68', 'Delsumma:', '425Kr', '425.0000', 'ot_subtotal', '1');

So yes, the table is corrupt.

In table orders the orders_id is 39. In orders_products, orders_status_history and in orders_total the orders_id is 0!

 

So, then, why this?

 

Sara

[multimixer]

Hi Sara

 

Now we know why the orders appear that way. The question is now, how did the DB mess happen?

 

- Either there is something wrong in the script that delete the orders. I can't really believe this, because any other orders got deleted correctly

- Or, you did something to that particular order (#39) manually inthe database. Could this be the case at some point?

 

Whatever, lets see if things work correctly right now. Make some more orders and delete them again, then make one more, are all ids correct? If yes, then you could delete order #39 manually from the DB

[Juto]

Goodmorning George. I did what you suggested and deleted the order. I haven't done anything manually with the db wrt to the "funny" order.

Also that funny order also caused errors in the order total module, like:

 

 

Date / Time: 10-05-2012 17:14:37

Error Type: [E_NOTICE] Use of undefined constant MODULE_ORDER_TOTAL_TOTAL_TITLE - assumed 'MODULE_ORDER_TOTAL_TOTAL_TITLE'

On line 18

File ........./includes/modules/order_total/ot_total.php

-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

 

 

However, I think I deleted the order via admin while I was logged in as a customer. So, a fix would be that it should be impossible to delete an order while a customer is logged in, and also there should be a "flag" in admin if the customer is logged in.

 

How could this be done?

 

Many thanks for your help.

 

Sara