Jump to content
Dr. Rolex

jQuery/Ajax Advanced Order Handler for osCommerce 2.3.3

Recommended Posts

Hi Jonas

 

This is a great contribution. I just have a few requests if you could show how to implement this please:

 

I need the totals to look like this:

 

Sub-total (ex TAX)

Freight (ex TAX)

Extra Cost (Ex TAX)

TAX (Calculated on above)

Total (Inc tax)

 

If i add extra fields they need to go above TAX and the TAX is to be recalculated. All items need to show without TAX and added at the end as TOTAL (inc TAX). Could you show how this could be done please?

 

Also if you are adding extra fields to the above then the TOTAL amount does not get updated. And if you send an updated invoice the TOTAL will not show the amounts of the extra fields added to it. The only way i have found to update is when i change the price of a product. Could you please look into this bug if it is.

 

If it is also possible when editing a product (say description) or any other item the original value is in the field but when you type a letter it all erases. Is it possible to have the original data and we can delete or add what we need to it otherwise at times you need to write the whole description again if you want to add something to the end of it.

 

Great Contribution. Keep up the good work.

 

Thanks :)

 

Wholesaler, are we? ;)

 

I need to sleep now, so I'll look into it more tomorrow. But it might work to replace your orders_ajax.php with the code I posted above and then make these changes to it:

 

Replace this:

$price += (float)round(((float)$products_total['final_price'] * (int)$products_total['products_quantity']) * $iva, 2);

 

With this:

//$price += (float)round(((float)$products_total['final_price'] * (int)$products_total['products_quantity']) * $iva, 2);
    $price += (float)round(((float)$products_total['final_price'] * (int)$products_total['products_quantity']), 2);

 

Replace this:

} elseif ($order_total['class'] == 'ot_total') {
    $new_value = (float)$total;
    $new_text = '<strong>' . $currencies->format(round($new_value)) . '</strong>';
    $params = array($new_text, $new_value, $order_total['orders_total_id']);
    mysqli_prepared_query("update " . TABLE_ORDERS_TOTAL . " SET text = ?, value = ? WHERE orders_total_id = ?", "sdi", $params);
  } else {

 

With this:

} elseif ($order_total['class'] == 'ot_total') {
    $new_value = (float)$total;
    $total_tax = 0;
    foreach ($taxes as $tax) {
	  $total_tax += (float)$tax['value'];
    }
    $new_value += $total_tax;
    $new_text = '<strong>' . $currencies->format(round($new_value)) . '</strong>';
    $params = array($new_text, $new_value, $order_total['orders_total_id']);
    mysqli_prepared_query("update " . TABLE_ORDERS_TOTAL . " SET text = ?, value = ? WHERE orders_total_id = ?", "sdi", $params);
  } else {

 

Replace This:

for ($i=0; $i<sizeof($taxes); $i++) {
	  if ($taxes[$i]['description'] == $others_tax[0]['tax_class_title']) {
	    $taxes[$i]['value'] += (float)$other_tax;
	    $updated = true;
	    break;
	  }
    }
    if ($updated === false) {
	  $taxes[] = array('description' => $others_tax[0]['tax_class_title'], 'value' => $other_tax);
    }
    $total += round((float)$order_total['value'], 4);
  }
   }

 

With this:

for ($i=0; $i<sizeof($taxes); $i++) {
	  if ($taxes[$i]['description'] == $others_tax[0]['tax_class_title']) {
	    $taxes[$i]['value'] += (float)$other_tax;
	    $order_total['value'] -= (float)$other_tax;
	    $order_total['text'] = $currencies->format($order_total['value']);
	    $updated = true;
	    break;
	  }
    }
    if ($updated === false) {
	  $taxes[] = array('description' => $others_tax[0]['tax_class_title'], 'value' => $other_tax);
	  $order_total['value'] -= (float)$other_tax;
	  $order_total['text'] = $currencies->format($order_total['value']);
    }
    $total += round((float)$order_total['value'], 4);
  }
   }

Share this post


Link to post
Share on other sites

Here's another modification that will result in a pretty big performance gain.

 

In ./admin/order_handler.php at line 469 replace this:

/* Orders Selection Queries */
#Sort by Search
if(isset($_GET['search']) && !empty($_GET['search'])) {
 $orderby = (isset($_GET['sortby']) && !empty($_GET['sortby']) ? (($_GET['sortby'] == 'value') ? 'ot.' : '') . $_GET['sortby'] : 'o.orders_id ') . ' ' . (isset($_GET['order']) && !empty($_GET['order']) ? $_GET['order'] : 'DESC');
 $params = array('%'.$_GET['search'].'%', '%'.$_GET['search'].'%', $_GET['search'], $_GET['search'], $languages_id);
 $typeDef = "sssii";
$orders_query_raw = "select o.orders_id, o.customers_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) left join " .	
TABLE_ORDERS_STATUS . " s on (o.orders_status=s.orders_status_id) where	
(o.customers_name like ? or o.customers_email_address like ? or o.customers_id like ? or o.orders_id = ?) and s.language_id = ?
and ot.class = 'ot_total' order by ".$orderby;
 $query_num_rows_query = array("SELECT count(*) as total FROM orders WHERE (customers_name like ? or customers_email_address like ? or customers_id like ?)", "sss", array('%'.$_GET['search'].'%', '%'.$_GET['search'].'%', '%'.$_GET['search'].'%'));
} elseif (isset($_GET['status']) && $_GET['status'] > '0' && !isset($_GET['order_by_prod_quantity'])) {
#Sort by Status
 $orderby = (isset($_GET['sortby']) && !empty($_GET['sortby']) ? (($_GET['sortby'] == 'value') ? 'ot.' : '') . $_GET['sortby'] : 'o.orders_id ') . ' ' . (isset($_GET['order']) && !empty($_GET['order']) ? $_GET['order'] : 'DESC');
 $params = array($languages_id, $_GET['status']);
 $typeDef = "ii";
$orders_query_raw = "select o.orders_id, o.customers_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 join " . TABLE_ORDERS_STATUS_HISTORY . " osh 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 o.orders_id = osh.orders_id and osh.orders_status_id = '1' and s.language_id = ? and s.orders_status_id = ? and ot.class = 'ot_total' GROUP BY o.orders_id order by ".$orderby;
 $query_num_rows_query = array("SELECT count(*) as total FROM orders WHERE orders_status = ?", "i", array($_GET['status']));
} elseif (isset($_GET['order_by_prod_quantity'])) {

#Sort by Products Quantity
 $orderby = (isset($_GET['sortby']) && !empty($_GET['sortby']) ? (($_GET['sortby'] == 'value') ? 'ot.' : '') . $_GET['sortby'] : 'o.orders_id ') . ' ' . (isset($_GET['order']) && !empty($_GET['order']) ? $_GET['order'] : 'DESC');
 $params = array($languages_id);
 $typeDef = "i";
 if(isset($_GET['status']) && is_numeric($_GET['status'])) $status = "AND o.orders_status = '" . (int)$_GET['status'] . "'";
 $orders_query_raw = "select o.orders_id, o.customers_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, sum(op.products_quantity) AS products_quantity from " . TABLE_ORDERS . " o LEFT JOIN " . TABLE_ORDERS_TOTAL . " ot ON (o.orders_id = ot.orders_id) LEFT JOIN " . TABLE_ORDERS_PRODUCTS . " op ON (o.orders_id = op.orders_id), " . TABLE_ORDERS_STATUS . " s WHERE s.language_id = ? " . (isset($status)?$status:''). " AND s.orders_status_id = o.orders_status AND ot.class = 'ot_total' GROUP BY op.orders_id ORDER BY ".$orderby;
 $query_num_rows_query = array("SELECT count(*) as total FROM orders " . (isset($status)?"WHERE orders_status = '".(int)$_GET['status']."'" : ''));
} else {
 #Default Sort
 $orderby = (isset($_GET['sortby']) && !empty($_GET['sortby']) ? (($_GET['sortby'] == 'value') ? 'ot.' : '') . $_GET['sortby'] : 'o.orders_id ') . ' ' . (isset($_GET['order']) && !empty($_GET['order']) ? $_GET['order'] : 'DESC');
 $params = array($languages_id);
 $typeDef = "i";
$orders_query_raw = "select o.orders_id, o.customers_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 = ? and ot.class = 'ot_total' order by ".$orderby;
 $query_num_rows_query = array("SELECT count(*) as total FROM orders");
}

 

With this:

/* Orders Selection Queries */
#Sort by Search
if(isset($_GET['search']) && !empty($_GET['search'])) {
 $orderby = (isset($_GET['sortby']) && !empty($_GET['sortby']) ? (($_GET['sortby'] == 'value') ? 'ot.' : '') . $_GET['sortby'] : 'o.orders_id ') . ' ' . (isset($_GET['order']) && !empty($_GET['order']) ? $_GET['order'] : 'DESC');
 $params = array('%'.$_GET['search'].'%', '%'.$_GET['search'].'%', $_GET['search'], $_GET['search'], $languages_id);
 $typeDef = "sssii";
 $orders_query_raw = "select o.orders_id, o.customers_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, osh.comments from " . TABLE_ORDERS . " o join " . TABLE_ORDERS_STATUS_HISTORY . " osh left join " .	
 TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) left join " .		
 TABLE_ORDERS_STATUS . " s on (o.orders_status=s.orders_status_id) where		
 (o.customers_name like ? or o.customers_email_address like ? or o.customers_id LIKE ? or o.orders_id = ?) AND o.orders_id = osh.orders_id AND osh.orders_status_id = '1' and s.language_id = ?
 and ot.class = 'ot_total' GROUP BY o.orders_id order by ".$orderby;
 $query_num_rows_query = array("SELECT count(*) as total FROM orders WHERE (customers_name like ? or customers_email_address like ? or customers_id like ?)", "sss", array('%'.$_GET['search'].'%', '%'.$_GET['search'].'%', '%'.$_GET['search'].'%'));
} elseif (isset($_GET['status']) && $_GET['status'] > '0' && !isset($_GET['order_by_prod_quantity'])) {
#Sort by Status
 $orderby = (isset($_GET['sortby']) && !empty($_GET['sortby']) ? (($_GET['sortby'] == 'value') ? 'ot.' : '') . $_GET['sortby'] : 'o.orders_id ') . ' ' . (isset($_GET['order']) && !empty($_GET['order']) ? $_GET['order'] : 'DESC');
 $params = array($languages_id, $_GET['status']);
 $typeDef = "ii";
$orders_query_raw = "select o.orders_id, o.customers_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, osh.comments from " . TABLE_ORDERS . " o join " . TABLE_ORDERS_STATUS_HISTORY . " osh 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 o.orders_id = osh.orders_id and osh.orders_status_id = '1' and s.language_id = ? and s.orders_status_id = ? and ot.class = 'ot_total' GROUP BY o.orders_id order by ".$orderby;
 $query_num_rows_query = array("SELECT count(*) as total FROM orders WHERE orders_status = ?", "i", array($_GET['status']));
} elseif (isset($_GET['order_by_prod_quantity'])) {

#Sort by Products Quantity
 $orderby = (isset($_GET['sortby']) && !empty($_GET['sortby']) ? (($_GET['sortby'] == 'value') ? 'ot.' : '') . $_GET['sortby'] : 'o.orders_id ') . ' ' . (isset($_GET['order']) && !empty($_GET['order']) ? $_GET['order'] : 'DESC');
 $params = array($languages_id);
 $typeDef = "i";
 if(isset($_GET['status']) && is_numeric($_GET['status'])) $status = "AND o.orders_status = '" . (int)$_GET['status'] . "'";
 $orders_query_raw = "select o.orders_id, o.customers_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, osh.comments, sum(op.products_quantity) AS products_quantity from " . TABLE_ORDERS . " o join " . TABLE_ORDERS_STATUS_HISTORY . " osh LEFT JOIN " . TABLE_ORDERS_TOTAL . " ot ON (o.orders_id = ot.orders_id) LEFT JOIN " . TABLE_ORDERS_PRODUCTS . " op ON (o.orders_id = op.orders_id), " . TABLE_ORDERS_STATUS . " s WHERE s.language_id = ? " . (isset($status)?$status:''). " AND s.orders_status_id = o.orders_status AND o.orders_id = osh.orders_id AND osh.orders_status_id = '1' AND ot.class = 'ot_total' GROUP BY op.orders_id ORDER BY ".$orderby;
 $query_num_rows_query = array("SELECT count(*) as total FROM orders " . (isset($status)?"WHERE orders_status = '".(int)$_GET['status']."'" : ''));
} else {
 #Default Sort
 $orderby = (isset($_GET['sortby']) && !empty($_GET['sortby']) ? (($_GET['sortby'] == 'value') ? 'ot.' : '') . $_GET['sortby'] : 'o.orders_id ') . ' ' . (isset($_GET['order']) && !empty($_GET['order']) ? $_GET['order'] : 'DESC');
 $params = array($languages_id);
 $typeDef = "i";
$orders_query_raw = "SELECT o.orders_id, o.customers_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, osh.comments FROM " . TABLE_ORDERS . " o JOIN " . TABLE_ORDERS_STATUS_HISTORY . " osh LEFT JOIN " . TABLE_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 = ? AND o.orders_id = osh.orders_id AND osh.orders_status_id = '1' AND ot.class = 'ot_total' GROUP BY o.orders_id ORDER BY ".$orderby;
 $query_num_rows_query = array("SELECT count(*) as total FROM orders");
}

 

Replace this code around line 526:

$link = "db_link";
global $$link;
/* prepare statement */
$sql = "SELECT comments FROM " . TABLE_ORDERS_STATUS_HISTORY . " WHERE orders_id = ? AND comments != '' ORDER BY date_added ASC LIMIT 1";
$stmt = mysqli_prepare($$link, $sql);
/* bind parameters to prepared statement */
mysqli_stmt_bind_param($stmt, 'i', $orders_id);
foreach ($orders_query as $orders) {
 // Kiss Error Debugger
 $start_time = microtime( true );
 $orders_id = $orders['orders_id'];
 mysqli_stmt_execute($stmt);
 /* bind variables to prepared statement */
 mysqli_stmt_bind_result($stmt, $comments);
 /* fetch values */
 mysqli_stmt_fetch($stmt);
 // Kiss Error Debugger
 if ( class_exists( 'KissER' ) ) {
 KissER::q( round( ( microtime( true ) - $start_time ), 4 ), $sql );
 }
 if (isset($comments)) {
 $orders['comments'] = $comments;
 } else {
 $orders['comments'] = '';
 }

 

With this:

// $link = "db_link";
// global $$link;
// /* prepare statement */
// $sql = "SELECT comments FROM " . TABLE_ORDERS_STATUS_HISTORY . " WHERE orders_id = ? AND comments != '' ORDER BY date_added ASC LIMIT 1";
// $stmt = mysqli_prepare($$link, $sql);
// /* bind parameters to prepared statement */
// mysqli_stmt_bind_param($stmt, 'i', $orders_id);
foreach ($orders_query as $orders) {
// // Kiss Error Debugger
// $start_time = microtime( true );
// $orders_id = $orders['orders_id'];
// mysqli_stmt_execute($stmt);
// /* bind variables to prepared statement */
// mysqli_stmt_bind_result($stmt, $comments);
// /* fetch values */
// mysqli_stmt_fetch($stmt);
// // Kiss Error Debugger
// if ( class_exists( 'KissER' ) ) {
//	 KissER::q( round( ( microtime( true ) - $start_time ), 4 ), $sql );
// }
// if (isset($comments)) {
//	 $orders['comments'] = $comments;
// } else {
//	 $orders['comments'] = '';
// }

 

 

Comment this code, around line 649:

mysqli_stmt_close($stmt);

 

This will reduce total queries by one per row.

 

 

Additionally, osCommerce comes without indexes configured or at least, very few.

 

To add some indexes that will increase performance for this Add-On and probably more queries, runt thi SQL code on your database.

ALTER TABLE `orders_status` ADD INDEX `orders_status_id` USING BTREE (orders_status_id);
ALTER TABLE `orders` ADD INDEX `orders_status` USING BTREE (orders_status);

Edited by Dr. Rolex

Share this post


Link to post
Share on other sites

To fix the bug that prevents new orders to be added correctly to the Order Table when the Order Table i empty, find this code in ./admin/js/order_handler.js around line 1306:

 

function messages_longpolling( last_order_number, poll_timer ){
   // Test if Polling is activated
   var input = $( "#order_by_prod_quantity" ),
    order_by_quantity = "";
   if ( input.prop( "checked" ) === true )
    order_by_quantity = "&order_by_prod_quantity=on";
   if ( polling === false ) return false;
   if ( typeof( poll_timer ) == "undefined" ) poll_timer = $( "#poll_timer" ).val();
   $.ajax({
    url: 'stream.php',
    type: 'GET',
    data: 'oID=' + last_order_number + '&poll_timer=' + poll_timer + order_by_quantity,
    global: false,
    async: true,
    cache: false,
    success: function( data ){
	    if ( running === true ) return false;
	    if ( typeof data == "object" ) {
		    if ( data.status == 'no-results' ) {
			    setTimeout( function(){ messages_longpolling( last_order_number ); },1000);
		    } else if( data.status == 'error' ){
			    setTimeout( function(){ messages_longpolling( last_order_number ); },15000);
		    }
		    return false;
	    }
	    var ordersTable = $( "#ordersTable" ).find( "tbody" ).find( ".dataTableRow:first" ),
		    newRow = $( data ).not( "#last_order_number" ),
		    last_order_number_current = window.last_order_number;

	    last_order_number = parseInt($( data ).closest( "#last_order_number" ).text(), 0);
	    if ( last_order_number_current >= last_order_number ) return false;
	    if ( ordersTable.size() === 0 ) {
		    ordersTable = $( "#ordersTable" ).find( "thead" ).eq( 1 );
		    data = "<tbody>" + data + "</tbody>";
	    }
	    $( newRow ).insertBefore( ordersTable );
	    var data_order = $( "tr." + last_order_number ),
		    customers_name = data_order.attr( "data-customers_name" ),
		    order_total = data_order.attr( "data-order_total" ),
		    count = $( "tr." + last_order_number ).size();
	    if ( count > 0 )
		    $( this ).gritter( "New Order", customers_name + " placed a new order for " + order_total );
	    setTimeout( function(){ messages_longpolling( last_order_number ); },1000);
	    return false;
    },
    error: function(){
	    setTimeout( function(){ messages_longpolling( last_order_number ); },15000);
    }
   });
   return false;
}

 

Replace with:

function messages_longpolling( last_order_number, poll_timer ){
   // Test if Polling is activated
   var input = $( "#order_by_prod_quantity" ),
    order_by_quantity = "";
   if ( input.prop( "checked" ) === true )
    order_by_quantity = "&order_by_prod_quantity=on";
   if ( polling === false || running === true ) return false;
   running = true;
   if ( typeof( poll_timer ) == "undefined" ) poll_timer = $( "#poll_timer" ).val();
   $.ajax({
    url: 'stream.php',
    type: 'GET',
    data: 'oID=' + last_order_number + '&poll_timer=' + poll_timer + order_by_quantity,
    global: false,
    async: true,
    cache: false,
    success: function( data ){
	    if ( typeof data == "object" ) {
		    running = false;
		    if ( data.status == 'no-results' ) {
			    setTimeout( function(){ messages_longpolling( last_order_number ); },1000);
		    } else if( data.status == 'error' ){
			    setTimeout( function(){ messages_longpolling( last_order_number ); },15000);
		    }
		    return false;
	    }
	    var ordersTable = $( "#ordersTable" ).find( "tbody" ).find( ".dataTableRow:first" ),
		    newRow = $( data ).not( "#last_order_number" ),
		    last_order_number_current = window.last_order_number;

	    last_order_number = parseInt($( data ).closest( "#last_order_number" ).text(), 0);

	    if ( last_order_number_current >= last_order_number ) return false;
	    if ( ordersTable.size() === 0 ) {
		    ordersTable = $( "#ordersTable" ).find( "thead" ).eq( 1 );
		    newRow = "<tbody>" + data + "</tbody>";
	    }
	    $( newRow ).insertBefore( ordersTable );
	    var data_order = $( "tr." + last_order_number ),
		    customers_name = data_order.attr( "data-customers_name" ),
		    order_total = data_order.attr( "data-order_total" ),
		    count = $( "tr." + last_order_number ).size();
	    if ( count > 0 )
		    $( this ).gritter( "New Order", customers_name + " placed a new order for " + order_total );
	    running = false;
	    setTimeout( function(){ messages_longpolling( last_order_number ); },1000);
	    return false;
    },
    error: function(){
	    setTimeout( function(){ messages_longpolling( last_order_number ); },15000);
    }
   });
   return false;
}

Share this post


Link to post
Share on other sites

There's really no need to hide the Top Navbar when at 'Edit Order' mode. It will still work the same way as usual.

 

To fix this, comment or remove these snippets of code in order_handler.js ( There should be 4 of them, I believe)

 

$.when( $( "#navigationTop" ).animate( { top: '-' + topnavHeight + 'px' } ) ).done(function() {
		 $( "#navigationTop" ).toggle();
	 });

 

 

- Moving on -

 

If you want Step Backward/Forward buttons when you edit an order, that is move one order back/forward from where you are, then in ajax_manager.php find this:

<td id="pageHeading"><h1><?php echo HEADING_TITLE; ?> <small>Rev 2</small></h1></td>
	 <td class="pageHeading" align="right"><?php echo tep_draw_separator('pixel_trans.gif', 1, HEADING_IMAGE_HEIGHT); ?></td>
	 <td class="smallText" align="right"><?php echo tep_draw_button(TOOLTIP_DUPLICATE_ORDER, 'trash', tep_href_link('ajax_handler.php', tep_get_all_get_params(array('oID', 'action', 'ajax')) . 'get_order=' . (int)$_GET['oID'] . '&cID=' . $_GET['cID'] . '&action=duplicate_order'), 'secondary', array('params' => 'class="duplicate_order"')) . '<span class="ajax_disable">' . tep_draw_button(IMAGE_DELETE, 'trash', tep_href_link("oc_batch_delete_confirm.php", 'oID=' . (int)$_GET['oID']), 'secondary', array('params' => 'class="batch_delete"')) . '</span>' . '<span class="ajax_disable">' . tep_draw_button(IMAGE_CREATE_ORDER, 'document', tep_href_link(FILENAME_CREATE_ORDER, 'Customer_nr=' . (int)$_GET['cID'])) . '</span>' . tep_draw_button(IMAGE_ORDERS_INVOICE, 'document', tep_href_link(FILENAME_ORDERS_INVOICE, 'oID=' . (int)$_GET['oID']), null, array('newwindow' => true)) . tep_draw_button(IMAGE_ORDERS_PACKINGSLIP, 'document', tep_href_link(FILENAME_ORDERS_PACKINGSLIP, 'oID=' . (int)$_GET['oID']), null, array('newwindow' => true)) . tep_draw_button(IMAGE_BACK, 'back', tep_href_link(FILENAME_ORDERS_HANDLER, tep_get_all_get_params(array('action'))), 'secondary', array('params' => 'class="ajax_button"')); ?></td>
	 </tr>
 </table></td>

 

Replace with:

<td id="pageHeading" width="30%" <?php echo ($_GET == array('ajax' => null) ? 'style="visibility:hidden;"' : ''); ?>><h1><?php echo HEADING_TITLE; ?> <small>Rev 2</small></h1></td>
	 <td class="pageHeading" align="left"><?php echo '<a class="ajax_button" style="font-size:18px;" href="' . tep_href_link(FILENAME_ORDERS_HANDLER, tep_get_all_get_params(array('oID', 'action', 'cID')) . 'oID=' . (int)($_GET['oID']-1) . '&action=edit') . '">'; ?><i class="fa fa-step-backward fa-2x"></i></a></td>
	 <td class="pageHeading" align="left"><?php echo '<a class="ajax_button" style="font-size:18px;" href="' . tep_href_link(FILENAME_ORDERS_HANDLER, tep_get_all_get_params(array('oID', 'action', 'cID')) . 'oID=' . (int)($_GET['oID']+1) . '&action=edit') . '">'; ?><i class="fa fa-step-forward fa-2x"></i></a></td>
	 <td class="smallText ajax_disable" align="right" width="40%"><?php echo tep_draw_button(TOOLTIP_DUPLICATE_ORDER, 'trash', tep_href_link('ajax_handler.php', tep_get_all_get_params(array('oID', 'action', 'ajax')) . 'get_order=' . (int)$_GET['oID'] . '&cID=' . $_GET['cID'] . '&action=duplicate_order'), 'secondary', array('params' => 'class="duplicate_order"')) . '<span class="ajax_disable">' . tep_draw_button(IMAGE_DELETE, 'trash', tep_href_link("oc_batch_delete_confirm.php", 'oID=' . (int)$_GET['oID']), 'secondary', array('params' => 'class="batch_delete"')) . '</span>' . '<span class="ajax_disable">' . tep_draw_button(IMAGE_CREATE_ORDER, 'document', tep_href_link(FILENAME_CREATE_ORDER, 'Customer_nr=' . (int)$_GET['cID'])) . '</span>' . tep_draw_button(IMAGE_ORDERS_INVOICE, 'document', tep_href_link(FILENAME_ORDERS_INVOICE, 'oID=' . (int)$_GET['oID']), null, array('newwindow' => true)) . tep_draw_button(IMAGE_ORDERS_PACKINGSLIP, 'document', tep_href_link(FILENAME_ORDERS_PACKINGSLIP, 'oID=' . (int)$_GET['oID']), null, array('newwindow' => true)) . tep_draw_button(IMAGE_BACK, 'back', tep_href_link(FILENAME_ORDERS_HANDLER, tep_get_all_get_params(array('action'))), 'secondary', array('params' => 'class="ajax_button"')); ?></td>
	 </tr>
 </table></td>

 

Find this in order_hander.php:

<td id="pageHeading" <?php echo ($_GET == array('ajax' => null) ? 'style="visibility:hidden;"' : ''); ?>><h1><?php echo HEADING_TITLE; ?> <small>Rev 2</small></h1></td>
	 <td class="pageHeading" align="right"><?php echo tep_draw_separator('pixel_trans.gif', 1, HEADING_IMAGE_HEIGHT); ?></td>
	 <td class="smallText ajax_disable" align="right"><?php echo tep_draw_button(TOOLTIP_DUPLICATE_ORDER, 'trash', tep_href_link('ajax_handler.php', tep_get_all_get_params(array('oID', 'action', 'ajax')) . 'get_order=' . (int)$_GET['oID'] . '&cID=' . $_GET['cID'] . '&action=duplicate_order'), 'secondary', array('params' => 'class="duplicate_order"')) . '<span class="ajax_disable">' . tep_draw_button(IMAGE_DELETE, 'trash', tep_href_link("oc_batch_delete_confirm.php", 'oID=' . (int)$_GET['oID']), 'secondary', array('params' => 'class="batch_delete"')) . '</span>' . tep_draw_button(IMAGE_CREATE_ORDER, 'document', tep_href_link(FILENAME_CREATE_ORDER, tep_get_all_get_params(array('action', 'oID', 'ajax')) . 'Customer_nr=' . $_GET['cID'])) . tep_draw_button(IMAGE_ORDERS_INVOICE, 'document', tep_href_link(FILENAME_ORDERS_INVOICE, 'oID=' . $_GET['oID']), null, array('newwindow' => true)) . tep_draw_button(IMAGE_ORDERS_PACKINGSLIP, 'document', tep_href_link(FILENAME_ORDERS_PACKINGSLIP, 'oID=' . $_GET['oID']), null, array('newwindow' => true)) . tep_draw_button(IMAGE_BACK, 'back', tep_href_link(FILENAME_ORDERS_HANDLER, tep_get_all_get_params(array('action'))), 'secondary', array('params' => 'class="ajax_button"')); ?></td>
	 </tr>
 </table></td>

 

Replace with:

<td id="pageHeading" width="30%" <?php echo ($_GET == array('ajax' => null) ? 'style="visibility:hidden;"' : ''); ?>><h1><?php echo HEADING_TITLE; ?> <small>Rev 2</small></h1></td>
	 <td class="pageHeading" align="left"><?php echo '<a class="ajax_button" style="font-size:18px;" href="' . tep_href_link(FILENAME_ORDERS_HANDLER, tep_get_all_get_params(array('oID', 'action', 'cID')) . 'oID=' . (int)($_GET['oID']-1) . '&action=edit') . '">'; ?><i class="fa fa-step-backward fa-2x"></i></a></td>
	 <td class="pageHeading" align="left"><?php echo '<a class="ajax_button" style="font-size:18px;" href="' . tep_href_link(FILENAME_ORDERS_HANDLER, tep_get_all_get_params(array('oID', 'action', 'cID')) . 'oID=' . (int)($_GET['oID']+1) . '&action=edit') . '">'; ?><i class="fa fa-step-forward fa-2x"></i></a></td>
	 <td class="smallText ajax_disable" align="right" width="40%"><?php echo tep_draw_button(TOOLTIP_DUPLICATE_ORDER, 'trash', tep_href_link('ajax_handler.php', tep_get_all_get_params(array('oID', 'action')) . 'get_order=' . (int)$_GET['oID'] . '&cID=' . $_GET['cID'] . '&action=duplicate_order'), 'secondary', array('params' => 'class="duplicate_order"')) . '<span class="ajax_disable">' . tep_draw_button(IMAGE_DELETE, 'trash', tep_href_link("oc_batch_delete_confirm.php", 'oID=' . (int)$_GET['oID']), 'secondary', array('params' => 'class="batch_delete"')) . '</span>' . tep_draw_button(IMAGE_CREATE_ORDER, 'document', tep_href_link(FILENAME_CREATE_ORDER, tep_get_all_get_params(array('action', 'oID')) . 'Customer_nr=' . $_GET['cID'])) . tep_draw_button(IMAGE_ORDERS_INVOICE, 'document', tep_href_link(FILENAME_ORDERS_INVOICE, 'oID=' . $_GET['oID']), null, array('newwindow' => true)) . tep_draw_button(IMAGE_ORDERS_PACKINGSLIP, 'document', tep_href_link(FILENAME_ORDERS_PACKINGSLIP, 'oID=' . $_GET['oID']), null, array('newwindow' => true)) . tep_draw_button(IMAGE_BACK, 'back', tep_href_link(FILENAME_ORDERS_HANDLER, tep_get_all_get_params(array('action'))), 'secondary', array('params' => 'class="ajax_button"')); ?></td>
	 </tr>
 </table></td>

Edited by Dr. Rolex

Share this post


Link to post
Share on other sites

To fix an issue with not being able to update the order status and add comments when at 'Edit Order' Page.

 

Find this code in order_handler.php

    </table></td>
  </tr>
  <tr>
    <td class="main"><br><b><?php echo TABLE_HEADING_COMMENTS; ?></b></td>
  </tr>
  <tr>
    <td><?php echo tep_draw_separator('pixel_trans.gif', '1', '5'); ?></td>
  </tr>
  <tr><?php echo tep_draw_form('status', FILENAME_ORDERS_HANDLER, tep_get_all_get_params(array('action')) . 'action=update_order', 'post', 'id="update_status"'); ?>
    <td class="main"><?php echo tep_draw_textarea_field('comments', 'soft', '60', '5'); ?></td>
  </tr>
  <tr>
    <td><?php echo tep_draw_separator('pixel_trans.gif', '1', '10'); ?></td>
  </tr>
  <tr>
    <td><table border="0" cellspacing="0" cellpadding="2" width="100%">
	  <tr>
	    <td><table border="0" cellspacing="0" cellpadding="2">
		  <tr>
		    <td class="main"><b><?php echo ENTRY_STATUS; ?></b> <?php echo tep_draw_pull_down_menu('status', $orders_statuses, $order->info['orders_status'], ''); ?></td>
		    <td class="smallText" valign="top" align="right"><?php echo tep_draw_button(IMAGE_UPDATE, 'disk', null, 'primary', array('params' => 'class="ajax_button"')); ?></td>
		  </tr>
		  <tr>
		    <td colspan="2"><?php echo tep_draw_separator('pixel_trans.gif', '4', '5'); ?></td>
		  </tr>
		  <tr>
		    <td class="main"><b><?php echo ENTRY_NOTIFY_CUSTOMER; ?></b> <?php echo tep_draw_checkbox_field('notify', '', true); ?></td>
		    <td class="main"><b><?php echo ENTRY_NOTIFY_COMMENTS; ?></b> <?php echo tep_draw_checkbox_field('notify_comments', '', true); ?></td>
		  </tr>
	    </table></td>

	    <td class="smallText" align="right"><?php echo tep_draw_button(IMAGE_CREATE_ORDER, 'document', tep_href_link(FILENAME_CREATE_ORDER, 'Customer=' . (int)$_GET['cID'])) . tep_draw_button(IMAGE_ORDERS_INVOICE, 'document', tep_href_link(FILENAME_ORDERS_INVOICE, 'oID=' . $_GET['oID']), 'secondary', array('params' => 'target="_blank"')) . tep_draw_button(IMAGE_ORDERS_PACKINGSLIP, 'document', tep_href_link(FILENAME_ORDERS_PACKINGSLIP, 'oID=' . $_GET['oID']), 'secondary', array('params' => 'target="_blank"')) . tep_draw_button(IMAGE_BACK, 'back', tep_href_link(FILENAME_ORDERS_HANDLER, tep_get_all_get_params(array('action'))), 'secondary', array('params' => 'target="_blank" class="ajax_button"')); ?></td>
	  </tr>
    </table></td>
  </form>
   </tr>
 </table>

 

Replace with:

    </table></td>
  </tr>
   </table>
   <?php echo tep_draw_form('status', FILENAME_ORDERS_HANDLER, tep_get_all_get_params(array('action')) . 'action=update_order', 'post', 'id="update_status"'); ?>
   <table border="0" cellpadding="2" cellspacing="0" width="100%">
  <tr>
    <td class="main"><br><b><?php echo TABLE_HEADING_COMMENTS; ?></b></td>
  </tr>
  <tr>
    <td><?php echo tep_draw_separator('pixel_trans.gif', '1', '5'); ?></td>
  </tr>
  <tr>
    <td class="main"><?php echo tep_draw_textarea_field('comments', 'soft', '60', '5'); ?></td>
  </tr>
  <tr>
    <td><?php echo tep_draw_separator('pixel_trans.gif', '1', '10'); ?></td>
  </tr>
  <tr>
    <td><table border="0" cellspacing="0" cellpadding="2" width="100%">
	  <tr>
	    <td><table border="0" cellspacing="0" cellpadding="2">
		  <tr>
		    <td class="main"><b><?php echo ENTRY_STATUS; ?></b> <?php echo tep_draw_pull_down_menu('status', $orders_statuses, $order->info['orders_status'], ''); ?></td>
		    <td class="smallText" valign="top" align="right"><?php echo tep_draw_button(IMAGE_UPDATE, 'disk', null, 'primary', array('params' => 'class="ajax_button"')); ?></td>
		  </tr>
		  <tr>
		    <td colspan="2"><?php echo tep_draw_separator('pixel_trans.gif', '4', '5'); ?></td>
		  </tr>
		  <tr>
		    <td class="main"><b><?php echo ENTRY_NOTIFY_CUSTOMER; ?></b> <?php echo tep_draw_checkbox_field('notify', '', true); ?></td>
		    <td class="main"><b><?php echo ENTRY_NOTIFY_COMMENTS; ?></b> <?php echo tep_draw_checkbox_field('notify_comments', '', true); ?></td>
		  </tr>
	    </table></td>

	    <td class="smallText" align="right"><?php echo tep_draw_button(IMAGE_CREATE_ORDER, 'document', tep_href_link(FILENAME_CREATE_ORDER, 'Customer=' . (int)$_GET['cID'])) . tep_draw_button(IMAGE_ORDERS_INVOICE, 'document', tep_href_link(FILENAME_ORDERS_INVOICE, 'oID=' . $_GET['oID']), 'secondary', array('params' => 'target="_blank"')) . tep_draw_button(IMAGE_ORDERS_PACKINGSLIP, 'document', tep_href_link(FILENAME_ORDERS_PACKINGSLIP, 'oID=' . $_GET['oID']), 'secondary', array('params' => 'target="_blank"')) . tep_draw_button(IMAGE_BACK, 'back', tep_href_link(FILENAME_ORDERS_HANDLER, tep_get_all_get_params(array('action'))), 'secondary', array('params' => 'target="_blank" class="ajax_button"')); ?></td>
	  </tr>
    </table></td>
   </tr>
 </form>

Share this post


Link to post
Share on other sites

get this error at the search code in order_handler.php :

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT o.orders_id, o.customers_id, o.customers_name, o.payment_method, o.date_p' at line 1

 

select count(*) as total SELECT o.orders_id, o.customers_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, osh.comments FROM mcyuk_orders o JOIN mcyuk_orders_status_history osh LEFT JOIN mcyuk_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 = ? AND o.orders_id = osh.orders_id AND osh.orders_status_id = '1' AND ot.class = 'ot_total' GROUP BY o.orders_id ORDER BY orders_id DESC

Share this post


Link to post
Share on other sites

get this error at the search code in order_handler.php :

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT o.orders_id, o.customers_id, o.customers_name, o.payment_method, o.date_p' at line 1

 

select count(*) as total SELECT o.orders_id, o.customers_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, osh.comments FROM mcyuk_orders o JOIN mcyuk_orders_status_history osh LEFT JOIN mcyuk_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 = ? AND o.orders_id = osh.orders_id AND osh.orders_status_id = '1' AND ot.class = 'ot_total' GROUP BY o.orders_id ORDER BY orders_id DESC

 

You have two 'SELECT' in your sql query, remove 'select count(*) as total '. After that it should work.

Share this post


Link to post
Share on other sites

There will be one more update for this Add-On by me sometime in the future.

 

I have basically remade everything so that the code will be more readable/workable. The Navbars and modals are moved to separate files, all functions/features are instead remade to Class functions and the jQuery will use a module pattern instead of functions. (Anyway, that's the plan..)

 

I have implemented the 'jTable jQuery Plugin to create AJAX based CRUD tables'. So now it's possible to choose from a wide variety of themes for the order handler and also easily configure if you want to hide certain columns, make them resizable etc..

 

I will add a 'right click menu' for those that like to edit the orders that way, instead of clicking on icons.

 

The point with this is to simplify the process of creating extensions to the Order Handler as well as improving/updating specific functions to fit one's needs. As it is in Rev 2, the code is very scattered and hard to work with.

 

jTable is pretty easy to work with and has a very well documented API. It takes JSON data and and creates customizable tables from it, so there isn't need for much jQuery/Javascript knowledge to work with it.

 

This also eliminates all that annoying html table code that take up so much space.

 

Orders can now be dropped down and edited below each row, Accordion style. The point with this feature is to remove the necessity of the 'Edit Order' page altogether resulting in that you never need to leave the page when editing/handling your orders. The original 'Edit Order' page will still be available for those who prefer to work that way.

 

Adding/Editing orders will be much more simple since the Order Handler will be using jTable's built in Create/Edit/Remove field/row function. jTable also have built in localization support

Share this post


Link to post
Share on other sites

Hi,

 

 

I was looking to install this module on a site, but ran into issues with PHP compatability. In the create_order.php file, you are using :

 

    $result = $rs->fetch_all(MYSQLI_ASSOC);  // line 89

 

which works with php 5.3 and beyond. With the 5.2 serries it has issues.

 

I switched to an older file you have there and the drop down has some strange test options to use.

 

On any selection I then get another error:

 

Fatal error: Call to undefined method mysqli_stmt::get_result() in /userweb/greavesjams/newshop/admin/create_order.php on line 191

 

Which again I am sure would be related to compatability issues.

 

Will your newest version fix compatability issues? and when might you release this?

 

Just curious if you have a time line is all.

 

thanks

 

Peter M

 

PS. it is a pretty impressive piece of work still :)


Peter McGrath

-----------------------------

See my Profile (click here) for more information and to contact me for professional osCommerce support that includes SEO development, custom development and security implementation

Share this post


Link to post
Share on other sites

Hi, I'm back after i spent some time with 2.3.3.4 getting it working properly with my mods before attempting yours again.

For the most part it is working with your v2, except for a few things I will note below.

 

First in the updates since v2 there seem to be a couple little things . . .

Post #40 I believe has an extra } at the end which causes an error with the javascript. (error is an expected token ')' missing)

Also the 2 SQL statements in #53, the second one really messes up my orders, changes order dates to 2036

 

Now to the few bugs I'm noticing.

1. If I open an order in the Quick Order Editor, then select a field like a price, the Enter New Value window opens. At this point I cannot enter a value (cannot select inside the input field). Now select OK or close the Enter New Value box, BUT NOW I can't close the Quick Order Editor window. The only way out is to refresh the browser.

 

2. If I edit an order and change a price of any item the Total goes to ZERO.

 

Thanks again for all your hard work on this.

Share this post


Link to post
Share on other sites

Create Order - create_order.php in v2

PHP Fatal error: Call to undefined method mysqli_result::fetch_all() in /admin/create_order.php on line 89

Share this post


Link to post
Share on other sites

Hi,

 

 

I was looking to install this module on a site, but ran into issues with PHP compatability. In the create_order.php file, you are using :

 

 $result = $rs->fetch_all(MYSQLI_ASSOC); // line 89

 

which works with php 5.3 and beyond. With the 5.2 serries it has issues.

 

I switched to an older file you have there and the drop down has some strange test options to use.

 

On any selection I then get another error:

 

 

 

Which again I am sure would be related to compatability issues.

 

Will your newest version fix compatability issues? and when might you release this?

 

Just curious if you have a time line is all.

 

thanks

 

Peter M

 

PS. it is a pretty impressive piece of work still :)

 

Hi Peter,

 

When it's done it's done. ;)

 

I haven't thought much about backwards compatibility, actually..

 

In addition to PHP 5.3+ the MySQL Native Driver (mysqlnd) is also required to run mysqli_stmt::get_result.

It shouldn't be too much of a hassle to include a compatibility function, if I have time I'll fix that in the next release. But why on earth are you stuck with PHP 5.2? :rolleyes:

 

Almost the entire code have been rewritten so when Rev 3 is ready I'll upload it as an independent upgrade, meaning it will not share files with Rev 2, so both Revisions can be installed simultaneously.

 

The Quick Create Order have been replaced by jTables native 'Add a record' feature with jQuery Autocomplete. This works much faster and isn't as complicated as the old variant..

 

There are many Themes and customizations available, I have included three screenshots of different ones (take notice of the new right click menu :P ).

All blue ones I noticed now, ha ha. jTable also integrates with jQuery-UI, so if someone have a modified theme for their Admin, then this Rev 3 will adjust to that style.

 

There's also the possibility to create and use new Metro UI themes.

 

It's still possible to change single values by clicking on them and also to use the old 'Edit Order' Page.

 

Orders can be expanded like an accordion. The Area under products and to the right of the order totals I plan to use for the missing comments box. After that it should be complete with all features of the regular 'Edit Order' Page.

 

You can see the new Create Order or "Add new record" as the heading says on screenshot #2. You simply optionally search for the customer on the top row to auto fill all fields or your write them yourself.

 

With jTable, you can disable columns, resize them, do whatever the h3ll you want with them actually.. It's a bit like working with Excel, except it doesn't crash (as long as you don't use IE, of course..) :P

 

jTable also have pretty nice animation/easing effects when updating orders/new orders added. No need to click on the checkboxes anymore, click anywhere on the row and it will be selected (except action icons).

 

I worked a little bit, just for fun, with Export to Excel or PDF options. But I don't really see the point in exporting orders that way so I'll probably scrap that.

 

The point of redoing everything is to make it possible to create Add-Ons for the Add-On so to speak. Extensions or whatever.

All actions will be handled in same file now as class functions, so it will be much easier to fix those that don't work as desired and of course add new ones.

 

Modals, Navbars etc.. have been moved to their own separate files to simplify working with them.

 

 

Most functions work as they should now, but their is still testing required before a, somewhat, stable release could be uploaded. Perhaps I will upload a 'Beta' version in the coming days so you can test it.

 

As a finale I was thinking on adding a slide in type of Configuration menu to simplify changing the theme or any of the many options available with jTable. I also looked at another nice jQuery plugin, where you can create your orders in Excel, Numbers or any other spreadsheet application and just select them and Drag & Drop them to the Order Handler for it to automatically insert them to your database and vice verca, but I think this will be too much work for a cool but perhaps useless feature.

 

I will add a feature at some point to be able to send push notices to logged in customers, like if I plan to close the shop in 10 minutes and send something like 'You have 10 minutes to complete your order if you want delivery tomorrow morning'.

 

 

It will probably take a while until the vultures can be fed with this new update, ha ha. But Rev2 should work well enough compared to the native system anyway.

Also, the summer has finally returned to this dark depressed location on Earth so there's less time to waste on creating those damn instructions. :trollface:

 

Good night!

post-318284-0-62239000-1401067175_thumb.png

post-318284-0-44819200-1401067180_thumb.png

post-318284-0-56555700-1401067184_thumb.png

Share this post


Link to post
Share on other sites

Hi, I'm back after i spent some time with 2.3.3.4 getting it working properly with my mods before attempting yours again.

For the most part it is working with your v2, except for a few things I will note below.

 

First in the updates since v2 there seem to be a couple little things . . .

Post #40 I believe has an extra } at the end which causes an error with the javascript. (error is an expected token ')' missing)

 

Yeah, you are right.

Correction: if you want the 'Update Status' and 'E-Mail' Radio Buttons returned to "No" position after submitting the form, then find and replace this code around line 952 in ./admin/js/order_handler.js

		 $( ".progress" ).hide();
		 if( typeof( payload ) == 'number' ) {
			 var countOrdersUpdated = payload.toString().length;
			 $( this ).gritter( "Success", countOrdersUpdated + " Orders Updated." );
			 return $( this ).ajaxLink(link + '&ajax=1', 'orderTable' );
		 } else if( payload.status == 'error' ) {
			 $( this ).gritter( "Error", payload.message );
		 }

 

To this:

		 $( "#navbar" ).find($("input[type='radio'][name='notify']")).eq( 1 ).click();
		 $( "#navbar" ).find($("input[type='radio'][name='autoupdatestatus']")).eq( 1 ).click();
		 $( ".progress" ).hide();
		 if( typeof( payload ) == 'number' ) {
			 var countOrdersUpdated = payload.toString().length;
			 $( this ).gritter( "Success", countOrdersUpdated + " Orders Updated." );
			 return $( this ).ajaxLink(link + '&ajax=1', 'orderTable' );
		 } else if( payload.status == 'error' ) {
			 $( this ).gritter( "Error", payload.message );
		 }

 

Also the 2 SQL statements in #53, the second one really messes up my orders, changes order dates to 2036

 

Indeed a strange error, I have no idea why your dates gets messed up. o:)

 

Now to the few bugs I'm noticing.

1. If I open an order in the Quick Order Editor, then select a field like a price, the Enter New Value window opens. At this point I cannot enter a value (cannot select inside the input field). Now select OK or close the Enter New Value box, BUT NOW I can't close the Quick Order Editor window. The only way out is to refresh the browser.

 

2. If I edit an order and change a price of any item the Total goes to ZERO.

 

Thanks again for all your hard work on this.

 

The Quick Order Editor never worked very good, neither the 'Create new Order Modal'.

 

That was an annoying error indeed, it's probably related to CSS. If you remove the Quick Order Editor dialog then the prompt will be writeable again.

A quick fix for this is to do this:

 

in order_handler.js, at four places, add this code before the dialog initilizes. Should be around line 373, 506, 580 and 1141, right before this code:

[...] $.fn.tdialog({

Insert this:

$( ".ajax_cart" ).hide();

 

Then again at four places add the code below, it should be right after the jqxhr.done(function() [...] is done, that is, after the closing }); for that function. Around line 404, 554, 610 and 1177; add this code:

jqxhr.always(function() {
   $( ".ajax_cart" ).show();
});

 

If you're having problem exiting the modal, press ESC. If that doesn't work, click on the modal and hit ESC again.

 

2: You have to add the values with a point (.) as a decimal and don't use spaces or any currency symbols. It has to be a pure data value. E.g.

929.0100

 

If that still doesn't work, then you need to look at the Network tab in developer tools at which parameters that are sent to the server with the request.

Edited by Dr. Rolex

Share this post


Link to post
Share on other sites

Another note worth mentioning is to take a look at the functions that you added to ./admin/includes/functions/database.php when implementing this Add-On.

 

Namely mysqli_prepared_query, gType and tep_db_prepared_perform.

 

You could replace all your current tep_db_query and tep_db_fetch_array functions for querying the database to the new ones so that your shop will use prepared statements instead of insecure, SQL Injecion prone, direct queries.

This is a good idea whenever there are variables used in the query.

 

Why is this a good idea?

Because with prepared statements, as opposed to direct querying, is completely safe from SQL injections. You don't need to think about escaping database input at all, all escaping is done for you with mysqli::prepare.

 

Simplified, a prepared statement consist of three components.

  1. The Query; simply replace all places where you would normally place a variable with a ?. E.g. this query:
    $sql = "select c.categories_image, cd.categories_name as catname from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = '" . (int)$current_category_id . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "'";
    

    Would become this:

    $sql = "select c.categories_image, cd.categories_name as catname from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = ? and c.categories_id = cd.categories_id and cd.language_id = ?";
    


     

  2. Parameter type definitions; there are 4 different Types: s = string, i = integer, d = double/float, b = binary. If you're unsure, you can almost always get away with using the s = string. So in this example we picked out two variables:
    $current_category_id
    $languages_id
    

    Both which are integers, right? They are numbers without decimals, so two integers. The type definition for this statement would thereby be "ii". If e.g. the first one would have been a string, let's say a variable with a name, e.g. 'Richard' and the second a currency value, e.g. '99.99'. Then we have a string and a double/float right? Then the type definitions would be "sd".

  3. The parameters; which in this query was
    $current_category_id
    $languages_id
    

    . Put these inside an array (doesn't matter if it's one variable or many). So in this query it would be

    array( $current_category_id, $languages_id )
    

    If there are more, just divide them with a comma.

So, moving on.. Now we have a new sql query, the type definitions and the parameters separated. So with this we can simply replace the example above (which is found in index.php at line 220), from this:

 

$image = tep_db_query("select c.categories_image, cd.categories_name as catname from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = '" . (int)$current_category_id . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "'");
$image = tep_db_fetch_array($image);

 

To this:

$sql = "select c.categories_image, cd.categories_name as catname from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = ? and c.categories_id = cd.categories_id and cd.language_id = ?";
$image = mysqli_prepared_query( $sql, "ii", array( $current_category_id, $languages_id ) );
$image = $image[0];

 

This will replace your originally unsecure query with a very much better prepared statement and also you'll start learning MySQLi so that you can become a better developer with a better store. There is of course much more that can be done with MySQLi, but I think this is a good way to start. Big kudos to Darren, the person who shared this function on php.net

 

Further on, I created the tep_db_prepared_perform function which you can simply replace your current tep_db_perform functions with to make them prepared statements.

I created a simple function called gType that automatically detects the type definitions from the query. So the tep_db_prepared_perform function will simply detect and pick out variables form the original query and format it to a prepared statement.

 

Note however, that I have only tested it on the places it's currently used for with the order handler and I can't remember if I made any tests with UPDATE statements, however, INSERT statements shouldn't be a problem.

 

You could add those functions to your catalog database.php as well and if you run into any problems, make sure to post them here. I created these functions in an attempt to automatically make all osCommerce queries prepared statements without any necessary code adjustments in addition to database.php.

Edited by Dr. Rolex

Share this post


Link to post
Share on other sites

jimlongo, on 23 May 2014 - 10:14 PM, said:

 

Also the 2 SQL statements in #53, the second one really messes up my orders, changes order dates to 2036

 

Indeed a strange error, I have no idea why your dates gets messed up. o:)

 

 

Yes it is strange. It of course doesn't affect the database dates. And the Orders page is okay, it's only the Orders box on the admin front page.

And every time I refresh the page there are different orders in the box, all dated 11-30-2036 . . . hmmmm

Share this post


Link to post
Share on other sites

Yes it is strange. It of course doesn't affect the database dates. And the Orders page is okay, it's only the Orders box on the admin front page.

And every time I refresh the page there are different orders in the box, all dated 11-30-2036 . . . hmmmm

 

 

It has something to do with this query around line 40 in /admin/includes/modules/dashboard/d_orders.php

 

$orders_query = tep_db_query("select o.orders_id, o.customers_name, greatest(o.date_purchased, ifnull(o.last_modified, 0)) as date_last_modified, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o, " . TABLE_ORDERS_TOTAL . " ot, " . TABLE_ORDERS_STATUS . " s where 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 . "' order by date_last_modified desc limit 14");

 

If I change it to take out the "greatest" function in the select section it is okay.

$orders_query = tep_db_query("select o.orders_id, o.customers_name, o.date_purchased as date_last_modified, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o, " . TABLE_ORDERS_TOTAL . " ot, " . TABLE_ORDERS_STATUS . " s where 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 . "' order by date_last_modified desc limit 14");

Share this post


Link to post
Share on other sites

It has something to do with this query around line 40 in /admin/includes/modules/dashboard/d_orders.php

 

$orders_query = tep_db_query("select o.orders_id, o.customers_name, greatest(o.date_purchased, ifnull(o.last_modified, 0)) as date_last_modified, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o, " . TABLE_ORDERS_TOTAL . " ot, " . TABLE_ORDERS_STATUS . " s where 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 . "' order by date_last_modified desc limit 14");

 

If I change it to take out the "greatest" function in the select section it is okay.

$orders_query = tep_db_query("select o.orders_id, o.customers_name, o.date_purchased as date_last_modified, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o, " . TABLE_ORDERS_TOTAL . " ot, " . TABLE_ORDERS_STATUS . " s where 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 . "' order by date_last_modified desc limit 14");

 

Now wait a minute, why would a dashboard module interfere with the order handler? They don't even run at the same page.

Share this post


Link to post
Share on other sites

Now wait a minute, why would a dashboard module interfere with the order handler? They don't even run at the same page.

 

As I said the database index you suggested adding only affects the Orders dashboard box, nothing to do with AOH.

Share this post


Link to post
Share on other sites

As I said the database index you suggested adding only affects the Orders dashboard box, nothing to do with AOH.

 

Hm.. The index doesn't work for me either anymore. Weird, because it worked when I tested it before and I actually managed to get it working again but after deleting and re-adding the index it didn't work anymore.. I don't know how I managed to screw it up. ha ha

Annoying though, since now there will be a full table scan on 'orders' when querying for new orders.

 

 

I haven't read that much about indexes yet, so I guess it I have to solve this later. This was what I got with MySQL Workbench Explain.

http://forums.oscommerce.com/uploads/monthly_05_2014/post-318284-0-27272100-1401374677_thumb.png[/img]

 

 

Hi Jonas

 

Any chance i can beta test your Rev 3 please?

 

 

Yeah, well, I had to work with some other stuff for my own shop and I have made some new database functions that automatically parse and convert all database queries to prepared statements using a regex pattern. I'm currently profiling it to see if I can optimize it some more. After that I thought I post it here on the forum if someone wants to try them.

 

It looks like it's working the way it should, so far anyway..

 

After I'm done with that, then I might upload a beta test.

Share this post


Link to post
Share on other sites

@@Dr. Rolex

I've done some database tuning in my previous career - anything I can help with ?


KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Share this post


Link to post
Share on other sites

@@Dr. Rolex

I've done some database tuning in my previous career - anything I can help with ?

 

Hello Carine!

 

How nice of you. I'm still pretty much a novice when it comes to database operations. So I'm trying to learn by "Trial and error". I can't say that I really understand indexes yet, how they work and the correct way to test and evaluate queries to optimize them.

 

Regarding the above mentioned query, where initially the database could find an index for all tables, but now I just can't get it right anymore and the strangest thing is that it works on my own production database, but after copying every index I have to another osCommerce test database, it still can't find an index for table 'orders'.

 

What I'm trying to accomplish is to get the sum of all the products from each order in one field, together with the rest of the order fields. Perhaps you can give me a hint of a better way of solving this, this is

the query:

SELECT
o.customers_id,
o.customers_name,
o.customers_company,
o.delivery_street_address,
o.delivery_suburb,
o.delivery_city,
o.delivery_postcode,
o.delivery_state,
o.delivery_state,
o.delivery_country,
o.customers_telephone,
o.customers_email_address,
o.date_purchased,
s.orders_status_name,
o.orders_id,
o.payment_method,
osh.comments,
sum(op.products_quantity) AS products_quantity,
ot.text AS order_total
FROM
orders o
 LEFT JOIN
orders_status_history osh USING (orders_id)
 LEFT JOIN
orders_total ot USING (orders_id)
 LEFT JOIN
orders_products op USING (orders_id)
 LEFT JOIN
orders_status s ON (o.orders_status = s.orders_status_id)
WHERE
s.language_id = 1
 AND osh.orders_status_id = 1
 AND ot.class = 'ot_total'
 AND osh.date_added = o.date_purchased
GROUP BY o.orders_id , op.orders_id
ORDER BY o.orders_id DESC
LIMIT 0 , 100

 

I actually got indexing "working" again, it's like it works sometimes and sometimes not. o:)

Don't know if it does more damage then good though:

 

ALTER TABLE `orders` ADD INDEX `orders_status` USING BTREE (orders_status);
ALTER TABLE `orders_status` ADD INDEX `orders_status` USING BTREE (orders_status_id);
ALTER TABLE `orders_status` ADD INDEX `language_id` USING BTREE (language_id);

Share this post


Link to post
Share on other sites

Sorry, just noticed your post, I have been sick in bed for 2 days - and now a little trouble sleeping ...

 

At first glance the LEFT in the join is not needed, I believe you will have records in each of the other tables joined.

But that doesn't really make a difference

 

There is something about index selectivity, which means that if an index contains mostly the same values, the database engine figures it might as wel do a table scan.

Some background for mysql is here

http://dev.mysql.com/doc/refman/5.5/en/myisam-index-statistics.html

 

I'll have a closer look tomorrow ie later today, it is now 5AM here, time to try sleeping again ...


KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Share this post


Link to post
Share on other sites

Hi!

 

I did a fresh install of OSC v2.3.3.4 and everything seems to work, except the create_order.php page which open totally blank.

 

I search in the previous posts but I couldn't find anything related. If anyone can help I appreciate!

 

Thanks

Share this post


Link to post
Share on other sites

Sorry, just noticed your post, I have been sick in bed for 2 days - and now a little trouble sleeping ...

 

At first glance the LEFT in the join is not needed, I believe you will have records in each of the other tables joined.

But that doesn't really make a difference

 

There is something about index selectivity, which means that if an index contains mostly the same values, the database engine figures it might as wel do a table scan.

Some background for mysql is here

http://dev.mysql.com/doc/refman/5.5/en/myisam-index-statistics.html

 

I'll have a closer look tomorrow ie later today, it is now 5AM here, time to try sleeping again ...

 

There's no rush Carine. ;)

 

In fact, replacing "LEFT JOIN" with "JOIN" reduced the query duration with about 0.3 - 0.4 seconds (from 2.4s to 2.0s). This was with caching disabled, strangely, with caching enabled again, the "LEFT JOIN" query have a "Fetch Time" of approx. 0.080 sec and the "JOIN" query have 0 sec. Why is it so?

 

 

Regarding the work on the next 'Revision' of this Add-On, which perhaps instead should be regarded as a completely new osC module since most of the code from Rev 2 is gone, is progressing a little bit every day.

 

There's so much code to rework, so it takes time. Regarding the functions, I believe they all work as expected now. I probably need to do some PHP profiling before I can be happy with the PHP part of it..

 

The new way of editing orders, 'Accordion Style', works very nicely.

Everything that's possible to do when at the 'Edit Order' page can now be done on the jTable without ever leaving the page/losing the table; add/edit orders, append comments, change status, duplicate orders, mail the customer etc.. This without trashy popup modals. :thumbsup:

 

The Mail function is now fixed so that e.g. the email_footer will be sent with the mail confirmation.

 

Available Payment / Shipping modules will be displayed as selectable dropdowns with additional option of Manual input.

 

Getting the modules to load correctly and calculate their tax/cost for new orders was a real pain the arse BTW, ugh..

 

It was also hard to get the order information to display clearly when expanded/dropped as accordion. A table inside another table can be a bit unclear.

 

Tax rates can be edited now as well.

 

There's almost 2,000 lines of jQuery code currently, so converting the jQuery functions to object literals will take time, I haven't decided yet if I'll take on that task this time.

 

For the time being I'll probably scrap the configuration menu.

 

I'll try to remake the the UPDATE and INSERT queries that are made in loops and instead build them on top of another to one single query.

 

And finally perhaps I'll add some caching features, if there's any use for it.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×