[osCmax v2.5x] Migration of Customers and Orders from OSC 2.2 RC2a to osCmax 2.5 RC1

[llamma]

I started out by making a backup of my production data and using mysql diff of my 2.2rc2a database to match the structure of 2.5RC1. Udated/uploaded tables and away we go!

My users are there my orders are there (if I search by order# directly) but if I go to the orders page (admin/orders.php) I only see orders from 2004 - 2005.

I login to my test account and look for order history and get 0 results.

I look at Mysql and compare the number of records in the table and they appear to be identical, spot checking data it seems to be the same from occurance to the other. I added a test order manually and it shows up at the top of the orders list, and in order history (as it should) but where did all the 2006-2011 orders go?

Am I missing a table or something? Has anyone ever seen this before?

[peterittner]

perhaps there was a data limit on your original data export?

[pgmarshall]

Have you tried taking the query that lists the order in admin and running it directly in phpMyAdmin or similar to see why you are getting odd results.

Regards,

[llamma]

First thanks for the responses I do appreciate it!

perhaps there was a data limit on your original data export?

The data export looks good, same MB size, same # coulumns and rows, the new rows were sucessfully added prior to the export. also i can find a suspect order if I search for it by oID directly, it will then display properly. So I "think" its right.

Have you tried taking the query that lists the order in admin and running it directly in phpMyAdmin or similar to see why you are getting odd results.

I have tried to find the query, and I am pretty familiar with the orders.php file but must admit I get a bit confused as it is a pretty complex file since it produces the recent order list, the order viewer/editor, as well as orders by status results. It includes a LOT of stuff.

Anyway I have settled on this chunk of code being the part that querys and displays the orders to list.

        <td><table border="0" width="100%" cellspacing="0" cellpadding="0">
          <tr>
            <td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
              <tr class="dataTableHeadingRow">
                <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_ORDER_NUM; ?></td>
                <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_CUSTOMERS; ?></td>
                <td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_ORDER_TOTAL; ?></td>
                <td></td>
                <td class="dataTableHeadingContent" align="center"><?php echo TABLE_HEADING_DATE_PURCHASED; ?></td>
                <td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_STATUS; ?></td>
                <td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_ACTION; ?>&nbsp;</td>
              </tr>
<?php
    if (isset($_GET['cID'])) {
      $cID = tep_db_prepare_input($_GET['cID']);
//LINE CHANGED: MOD - fedex added "o.fedex_tracking"
// BOF: Orders search by customers info - Changed queries below - added " . (!is_null($search_query)?$search_query:''). "
      $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, o.fedex_tracking, 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";
// LINE CHANGED: MS2 update 501112
//  } elseif (isset($_GET['status'])) {
    } elseif (isset($_GET['status']) && is_numeric($_GET['status']) && ($_GET['status'] > 0)) {
      $status = tep_db_prepare_input($_GET['status']);
//LINE CHANGED: MOD - fedex added "o.fedex_tracking"
      $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, o.fedex_tracking, 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'  " . (!is_null($search_query)?$search_query:''). " order by o.orders_id DESC";
    } else {
//LINE CHANGED: MOD - fedex added "o.fedex_tracking"
      $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, o.fedex_tracking, 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' " . (!is_null($search_query)?$search_query:''). " order by o.orders_id DESC";
    }
// EOF: Orders search by customers info    
    $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 . '&amp;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";
      }
?>
                <td class="dataTableContent"><?php echo $orders['orders_id']; ?></td>
                <td class="dataTableContent"><?php echo '<a href="' . tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('oID', 'action')) . 'oID=' . $orders['orders_id'] . '&amp;action=edit') . '">' . tep_image(DIR_WS_ICONS . 'page_white_text.png', ICON_PREVIEW) . '</a>&nbsp;' . $orders['customers_name']; ?></td>
                <td class="dataTableContent" align="right"><?php echo strip_tags($orders['order_total']); ?></td>
                
                <?php // BOF: Orders quick viewer
                $order_viewer = "";
                $products_query = tep_db_query("SELECT orders_products_id, products_name, products_quantity, products_model FROM " . TABLE_ORDERS_PRODUCTS . " WHERE orders_id = '" . $orders['orders_id'] . "' ");
                    while($products_rows = tep_db_fetch_array($products_query)) {
                        $order_viewer .= ($products_rows["products_quantity"]) . "x ". $products_rows["products_model"]. "  " . (tep_html_noquote($products_rows["products_name"])) . "<br>";
                        $result_attributes = tep_db_query("SELECT products_options, products_options_values FROM " . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . " WHERE orders_id = '" . $orders['orders_id']. "' AND orders_products_id = '" . $products_rows["orders_products_id"] . "' ORDER BY products_options");
                        while($row_attributes = tep_db_fetch_array($result_attributes)) {
                            $order_viewer .=" - " . (tep_html_noquote($row_attributes["products_options"])) . ": " . (tep_html_noquote($row_attributes["products_options_values"])) . "<br>";
                        }
                    }            
                ?>    
                
                <td><?php echo '<span title="' . TEXT_ORDER_SUMMARY . '|' . $order_viewer . '">' . tep_image(DIR_WS_ICONS . 'page_white_find.png'); ?></span></td>
                <?php // EOF: Orders quick viewer ?>
                
                <td class="dataTableContent" align="center"><?php echo tep_datetime_short($orders['date_purchased']); ?></td>
                <td class="dataTableContent" align="right"><?php echo $orders['orders_status_name']; ?></td>
                <td class="dataTableContent" align="right"><?php if (isset($oInfo) && is_object($oInfo) && ($orders['orders_id'] == $oInfo->orders_id)) { echo tep_image(DIR_WS_ICONS . 'icon_arrow_right.gif', ''); } else { echo '<a href="' . tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('oID')) . 'oID=' . $orders['orders_id']) . '">' . tep_image(DIR_WS_ICONS . 'information.png', IMAGE_ICON_INFO) . '</a>'; } ?>&nbsp;</td>
              </tr>
<?php
    }
?>
              <tr>
                <td colspan="7"><table border="0" width="100%" cellspacing="0" cellpadding="2">
                  <tr>
                    <td class="smallText" valign="top"><?php echo $orders_split->display_count($orders_query_numrows, MAX_DISPLAY_SEARCH_RESULTS, $_GET['page'], TEXT_DISPLAY_NUMBER_OF_ORDERS); ?></td>
                    <td class="smallText" align="right"><?php echo $orders_split->display_links($orders_query_numrows, MAX_DISPLAY_SEARCH_RESULTS, MAX_DISPLAY_PAGE_LINKS, $_GET['page'], tep_get_all_get_params(array('page', 'oID', 'action'))); ?></td>
                  </tr>
                </table></td>
              </tr>
            </table></td> 

But geez thats a mess! It looks like there have been some changes for "Orders search by customers info" But at the moment I dont really understand how that is working.

Perhaps I am running into some maximums. The database includes about 100K customer records and around 95K order records.

[llamma]

Are my posts still being moderated?

[pgmarshall]

I think posts are moderated until you get to 5 posts - it is to prevent spammers posting rubbish in the forums.

As for orders - it may look a "mess" but it needs to be this complex to work!

Try this for your phpMayAdmin check:

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, o.fedex_tracking, ot.text as order_total from orders o left join orders_total ot on (o.orders_id = ot.orders_id), orders_status s where o.orders_status = s.orders_status_id and s.language_id = '1' and ot.class = 'ot_total' order by o.orders_id DESC

See what you get back from this query ... is everything there?

Regards,

Regards,

[llamma]

It produces the same results. But that showed me the way. I notice the final column hinges upon orders_total. I checked out that table and there's the problem. That table is incomplete, there are only 16,266 records instead of the 264,328 that should be there. 28mb of data so I shouldnt have hit a limit but clearly it did not come across right. However the table structure did get updated correctly according to mysqldiff so I drop the incomplete table, re-export and import and we are up and running. I see orders like I would expect in admin and have order history avaialble in my test customer account. Fixed! Thanks!