Support thread for Storing Shipped Orders

[rabbitseffort]

it would involve the $orders_history_query, change to $orders_history_shipped_query

 

--I am not exactly sure how, but maybe this can get you started--first you will need to add the table you want in your mysql--eg order_history_shipped--I think you can just copy over all the same stuff but give the new table the right name

 

I bet Jim could tell you right away

[Daemonj]

It is indeed possible but would seriously affect the operation of the store and be fairly difficult to implement and manage.

[rabbitseffort]

I agree--I dont see the need for that extra effort since the way it works now is fine for cleaning up the admin page.

 

Thanks again Jim for all your help with this--it really has been a great feature to have

Edited June 18, 2006 by rabbitseffort

[i2Paq]

I really didn't change all my FILENAME_ORDERS occurrencies to FILENAME_ORDERS2 in the orders2.php file. Now, when I made all necessary modifications to orders2.php, it's working fine. I have orders.php heavily modified with many additional contributions, so now I made my orders2.php identical to orders.php. Thanks guys. It really is a useful contribution. :thumbsup:

 

 

Ehm :blush: , made the same stuppid mistake (called my file shipped_orders.php) :-"

 

Thanks for this great contri, works fine for me and just what I was lokking for!

[rabbitseffort]

Great--I knew it would be useful--It should be built in in my opinion, sure make sit easy to see what orders are still due to process

[i2Paq]

Hi rabbitseffort,

 

I ran into a small problem today and hope you can help me with this as it is related to you contri.

It's not a BIG issue only a little inconfinient.

 

When goint into the Admin, Customers and then klick/Choose a customer, when choosing Orders it gives the following error:

 

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 'where o.orders_status != 3 and o.orders_status = s.orders_status_id and s.langua' at line 1

select count(*) as total from orders o left join orders_total ot on (o.orders_id = ot.orders_id), orders_status s where o.customers_id = '140' where o.orders_status != 3 and o.orders_status = s.orders_status_id and s.language_id = '4' and ot.class = 'ot_total'

[tep-stop]

 

Hope you can help me with this. :blush:

[rabbitseffort]

If you search the forums for the very popular 1064 error you should find the answer pretty easily---or PM Daemonj since he helps loads with this idea becoming a reality

[i2Paq]

If you search the forums for the very popular 1064 error you should find the answer pretty easily---or PM Daemonj since he helps loads with this idea becoming a reality

 

I've looked but only find limit 20, -20 related issues, not the one I'm having.

[i2Paq]

If you search the forums for the very popular 1064 error you should find the answer pretty easily---or PM Daemonj since he helps loads with this idea becoming a reality

 

If you could point me into the right direction please.

[i2Paq]

Somebody pointed me out that I should use this code:

 

if (isset($HTTP_GET_VARS['cID'])) {
  $cID = tep_db_prepare_input($HTTP_GET_VARS['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 != 3 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($HTTP_GET_VARS['status'])) {
  $status = tep_db_prepare_input($HTTP_GET_VARS['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 != 3 and 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 != 3 and 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";
}

 

It works but only shows the orders wich are just placed, not orders already deliverd or any other status.

Any help on this?

[rabbitseffort]

yes--to see the orders that are anything but pending go to your orders2.php page

[i2Paq]

yes--to see the orders that are anything but pending go to your orders2.php page

 

I'm sorry but the code provided by you and this Contri is the one that creates this problem. If I leave out the

! = 3

my orders.php works normal again.

According to PHP coders this should never have worked before and they do not beleave me when I say it did work.

[rabbitseffort]

you are the only one to report this hasnt worked right for them--not sure what to tell you

 

the 3 is the orders status id--if you want it to be 2 or 1 you can depending on the orders status number in you sql

[i2Paq]

Well, got is solved and working again with help from the guys over at the .NL forum.

 

To get this contri working again I needed to change the querie-code in orders.php to the following:

 

if (isset($HTTP_GET_VARS['cID'])) {
  $cID = tep_db_prepare_input($HTTP_GET_VARS['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($HTTP_GET_VARS['status'])) {
  $status = tep_db_prepare_input($HTTP_GET_VARS['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 != 3 and 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";

 

Hope this wil help others in case all of a sudden this contri stops working and starts generating the same error.

 

The 1064 is just a general error when using a wrong querie-statement, Daemonj mission in life regarding this 1064-error has to do with the well known limit (limit, 10,10 etc) error and nothing else.

So, nexttime someone gomes up with a (1064)-error and before you send him off to find help by someone else make sure you know what the exact problem is and then verify that it's the same error you think you know the solution to. :-"

 

Needles to say that you contribution is something that was missing in osCommerce and I'm still very thankfull that you created it :thumbsup:

[Irin]

Hi guys,

 

I wonder is it possible to add 3 order ids to the $orders_query_raw, like, in my case I want to move all my Cancelled (ID = 7) and Refunded (ID = 6) orders to a new page orders3.php and all my Delivered (ID = 3) orders in orders2.php? I'm not an advanced coder and all I came up with was the following query in orders.php which is not working indeed:

$orders_query_raw = "select o.ip_address, 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 != 3 or 6 or 7 and 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 $order";

} else {

$orders_query_raw = "select o.ip_address, 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 != 3 or 6 or 7 and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by $order";

}

Can anybody please help? Thanks in advance.

[Irin]

I figured out how to remove all that status ids from the order.php by changing the $orders_query_raw to:

$orders_query_raw = "select o.ip_address, 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 != 3 and o.orders_status != 6 and o.orders_status != 7 and o.orders_status != 15 and 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 $order";

} else {

$orders_query_raw = "select o.ip_address, 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 != 3 and o.orders_status != 6 and o.orders_status != 7 and o.orders_status != 15 and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by $order";

}

But how should I make it to show just the specific status ids in the order3.php? The query I wrote for the orders3.php below doesn't show any orders at all.

$orders_query_raw = "select o.ip_address, 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 = 6 and o.orders_status = 7 and 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 $order";

} else {

$orders_query_raw = "select o.ip_address, 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 = 6 and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by $order";

}

Does anybody have any advice on how to modify the query?

 

Thanks.

Edited September 24, 2006 by Irin

[Guest]

it would take some digging but I see no reason why it couldnt be done--if you go thru the effort will you post back here please. Thanks

i believe i have achieved it (with the help of the cancelled orders contribution): http://www.oscommerce.com/community/contri...earch,cancelled

 

which achieves basically the same functionality of this contribution, except requires a bit of rewording.

 

 

 

now my problem lies within on transferring the orders to this table.

would you happen to know how to do it?

 

basically i stripped the "cancel" code and added it to the delete / edit / packingslip menu for the customer's order, so if i click "cancel" the order is moved to this table, successfully. (which also means my "delete" button is still fully functional)

 

with thousands of orders currently existing in the database, it would take years to do them manually.

 

 

i am sure this is just a simple code, to select possibly an order that was marked "shipped" (or in my case, status "3") within the past 90 days. that way if there is a complaint regarding a newer order or the customer wishes to check their status, their order is still available as usual in the order history page of their account.

 

so basically a simple query to the database to search the order comment dates... if the order falls within the 90 day / 3 month range it runs the tep_archive_order function (to delete from table_orders and send to table_cancelled).

 

sorry for the thread hijack, but somewhat relative to the initial contribution :blush:

[dca]

Hello,

 

Nice contribution indeed. Works really well for me.

 

What I would like to do is also put away the CANCELLED orders so that the orders page is cleared of these too. So would be ok to send the cancelled orders to the shipped orders page.

 

Any idea on how to code this as well ?

 

Anyone ?

 

Thanks in advance.

 

dca

[dca]

Hello,

 

Nice contribution indeed. Works really well for me.

 

What I would like to do is also put away the CANCELLED orders so that the orders page is cleared of these too. So would be ok to send the cancelled orders to the shipped orders page.

 

Any idea on how to code this as well ?

 

Anyone ?

 

Thanks in advance.

 

dca

 

bump

[joyces]

Ok I know this is as old as the hills and maybe there is no one out there who will ever look at this but I'm going to post anyway just in case.

As previously asked I too would like to add another orders page orders3.php where I can store my cancelled orders away from the main orders page. I have successfully installed the contribution for shipped orders and reckon I can probably work out the rest but I don't know how to include the code in orders.php in addition to the code already installed for the contribution.

The clean orders.php code has been amended to

$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 != 5 and 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 != 5 and 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";

   }

The bit changed is: "where o.orders_status != 5 and o.orders_status =

s.orders_status_id"

from: "where o.orders_status = s.orders_status_id"

This change has been made twice

 

I now want the $orders_query_raw to also include the same information for order status 9 which will be sent to order3.php.

In short I will have 3 pages in Admin:

Orders (orders.php)

Shipped orders (orders2.php - created from the contribution)

Cancelled orders.(orders3.php - if someone can help me work out how to do it!!)

 

I have tried a few trial and error ways but i'm afraid the ended up as errors!!

 

Any help would be appreciated

thanks a lot

Andrea

Edited August 23, 2010 by joyces

[RobC1962]

Ok I know this is as old as the hills and maybe there is no one out there who will ever look at this but I'm going to post anyway just in case.

As previously asked I too would like to add another orders page orders3.php where I can store my cancelled orders away from the main orders page. I have successfully installed the contribution for shipped orders and reckon I can probably work out the rest but I don't know how to include the code in orders.php in addition to the code already installed for the contribution.

The clean orders.php code has been amended to

$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 != 5 and 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 != 5 and 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";

   }

The bit changed is: "where o.orders_status != 5 and o.orders_status =

s.orders_status_id"

from: "where o.orders_status = s.orders_status_id"

This change has been made twice

 

I now want the $orders_query_raw to also include the same information for order status 9 which will be sent to order3.php.

In short I will have 3 pages in Admin:

Orders (orders.php)

Shipped orders (orders2.php - created from the contribution)

Cancelled orders.(orders3.php - if someone can help me work out how to do it!!)

 

I have tried a few trial and error ways but i'm afraid the ended up as errors!!

 

Any help would be appreciated

thanks a lot

Andrea

 

 

yes - I have done this and have 4 order 'status' pages in my admin area - I did the mods a long time ago but will check the code tomorrow and pass it on to you. I recall it was very simple indeed to accomplish. Would do it tonight for you, but its been a long long long day ....

[RobC1962]

Ah - it is in a txt file on my desktop still!

 

if (isset($HTTP_GET_VARS['search']) && tep_not_null($HTTP_GET_VARS['search'])) {

$keywords = tep_db_input(tep_db_prepare_input($HTTP_GET_VARS['search']));

$search = "where o.orders_status != 3 and o.orders_status != 106 and o.orders_status != 111 and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' and o.customers_name like '%" . $keywords . "%' ";

} else {

$search = "where o.orders_status != 3 and o.orders_status != 106 and o.orders_status != 111 and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' ";

}

 

if (isset($HTTP_GET_VARS['cID'])) {

// search on order id

$cID = tep_db_prepare_input($HTTP_GET_VARS['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 ". $db_orderby . " " . $sort ;

$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($HTTP_GET_VARS['status']) && is_numeric($HTTP_GET_VARS['status']) && ($HTTP_GET_VARS['status'] > 0)) {

// search on status of order

$status = tep_db_prepare_input($HTTP_GET_VARS['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 != 3 and 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 ". $db_orderby . " " . $sort ;

} else {

 

Change your status numbers as required. (Mine are the 3, 106 and 111)

[joyces]

yes - I have done this and have 4 order 'status' pages in my admin area - I did the mods a long time ago but will check the code tomorrow and pass it on to you. I recall it was very simple indeed to accomplish. Would do it tonight for you, but its been a long long long day ....

 

Rob you are a star!!! look forward to hearing from you, your help is much appreciated!

Cheers

Andrea (Joyces)

[joyces]

Rob you are a star!!! look forward to hearing from you, your help is much appreciated!

Cheers

Andrea (Joyces)

 

Hey Rob worked like a dream. Thanks for your help

Andrea (joyces)