Gordon-Michael Posted March 5, 2004 Share Posted March 5, 2004 I am new to osCommerce. I am looking for instructions on how to download all orders for offline processing. I can get copies of individual orders sent to me by email and then put into Excel but with several hundred orders a day this will not work. Link to comment Share on other sites More sharing options...
cyberphool Posted March 5, 2004 Share Posted March 5, 2004 Hi, I don't have a solution to this post, but would like to add a question that may have a similar solution. I would like to know if it's possible to do a bulk product update from something like an Excel spreadsheet. I will initially set up products through the web interface, but I would like to possibly update all stock quantities and maybe even prices daily. I'm sure it's possible directly through mySQL, but has anyone implemented anything like this? Thanks, David Link to comment Share on other sites More sharing options...
flagfanatics Posted March 6, 2004 Share Posted March 6, 2004 David, That's a grrrreeeaaat question. I'd really like to get an answer as well. Let me know what you come up with. Wish I could be of more help. Good Luck to You Jacob Link to comment Share on other sites More sharing options...
ozcsys Posted March 6, 2004 Share Posted March 6, 2004 I am new to osCommerce. I am looking for instructions on how to download all orders for offline processing. I can get copies of individual orders sent to me by email and then put into Excel but with several hundred orders a day this will not work. I have something I have been using for this that I have been meaning to write up as a contribution and I will try and do it this weekend. I use it to create a csv file with all the info I need to place orders with my supplier. One of the fields is cost and I added this to my site with this contribution http://www.oscommerce.com/community/contributions,1594 anyway quick instructions you need to make a directory in the admin section called data you need to add a field called extracted in the orders table with a default of 0 you need to make a link in one of your boxes in the admin section you need to put the following info in a file called extract orders.php in your admin directory I think that is all, I will install it clean on a test site this weekend to make sure and put it together in a contribution with detailed instructions. <?php /** * extract_orders.php * * This script extracts the orders into a .csv file for download. */ $out_file = 'data/'.time().'.csv'; include_once( "includes/application_top.php" ); ?> <!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN"> <html <?php echo HTML_PARAMS; ?>> <head> <meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>"> <title><?php echo TITLE; ?></title> <link rel="stylesheet" type="text/css" href="includes/stylesheet.css"> <script language="javascript" src="includes/general.js"></script> </head> <body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF"> <? require( DIR_WS_INCLUDES . 'header.php' ); ?> <table border="0" width="100%" cellspacing="2" cellpadding="2"> <tr> <td width="<?php echo BOX_WIDTH; ?>" valign="top"> <table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft"> <? require( DIR_WS_INCLUDES . 'column_left.php' ); ?> </table> </td> <td width="100%" valign="top"> <? $fp = fopen( $out_file, 'w' ); $sth = tep_db_query( "SELECT orders.orders_id as id, products.products_model as pid, orders_products.products_name as pname, customers.customers_firstname, customers.customers_lastname, orders.customers_street_address as street_addr, orders.customers_city as city, orders.customers_state as state, orders.customers_postcode as zip, orders_products.products_quantity as qty, orders_products_attributes.products_options_values as subscr_type, products.products_cost as pprice, (orders_products.products_quantity*products.products_cost) as tcost FROM orders, customers, orders_products, products LEFT JOIN orders_products_attributes on orders_products_attributes.orders_id=orders.orders_id and orders_products_attributes.products_options='Type of Subscription' and orders_products_attributes.orders_products_id = orders_products.orders_products_id WHERE orders_products.orders_id = orders.orders_id AND products.products_id = orders_products.products_id AND customers.customers_id = orders.customers_id AND orders.exported = 0 ORDER BY orders.orders_id, orders_products.products_name" ); while( $r = tep_db_fetch_array($sth) ) { tep_db_query("UPDATE orders SET exported=1 WHERE orders_id=".$r['id']); unset($r['id']); $r['tcost'] = number_format( $r['tcost'], 2, '.', '' ); $r['pprice'] = number_format( $r['pprice'], 2, '.', '' ); $string = implode(",",$r) . "\n"; fputs( $fp, $string ); } fclose($fp); ?> <center> <font size= "6" color= "ff0000"><b>IMPORTANT MESSAGE</b></font><p></p> <font size= "5" color= "ff0000"><b>PLEASE READ BEFORE LEAVING THIS PAGE</b></font><p></p> You have now completed exporting your order. All your orders are now ready to be downloaded to your local computer. If you leave this page before doing so you will lose that information and have to manually retreive the orders. If there is even the smallest chance that you might have an order you <b>MUST</b> download before leaving this page. <p></p> <b> Export Complete</b><br> You should now download the file from <a href="<?=$out_file?>" target= "blank"><font size= "4" color= "#0000ff"><b>HERE</b></font></a> and save to your local computer.<p> You have now extracted all your new orders. <br><font size= "5" color= "ff0000">You must<b> NOW </b>Save your information to your local computer</font><br> or you will have to retreive the information manually.<br> You can do this by clicking <a href="<?=$out_file?>" target= "blank"><font size= "4" color= "#0000ff"><b>HERE</b></font></a><p> <font size= "4"> <b>Please do not leave this page before saving your order information by clicking</font> <p><a href="<?=$out_file?>" target= "blank"><font size= "5" color= "#0000ff"><b>HERE</b></font></a></p></b> <p></p> <font size= "5" color= "ff0000"><b>ONE LAST TIME DO NOT LEAVE THIS PAGE BEFORE CLICKING</b></font><br> <a href="<?=$out_file?>" target= "blank"><font size= "6" color= "#0000ff"><b>HERE</b></font></a><br> If you do not have any orders since the last time you extracted your orders you will receive a blank page. This is why you should save everytime you extract because you do not want to have a blank page when you have had customers place orders since the last time that you extracted orders. A blank page is nothing to worry about as long as you save everytime that you come to this page. </center> </td> </tr> </table> <?php require(DIR_WS_INCLUDES . 'footer.php'); ?> <br> </body> </html> <?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?> The Knowledge Base is a wonderful thing. Do you have a problem? Have you checked out Common Problems? There are many very useful osC Contributions Are you having trouble with a installed contribution? Have you checked out the support thread found Here BACKUP BACKUP BACKUP!!! You did backup, right?? Link to comment Share on other sites More sharing options...
cyberphool Posted March 7, 2004 Share Posted March 7, 2004 Thanks to ozcsys, that solution looks great for downloading a list of order. In reply to my own question on bulk updates, I think the contribution called "Easy Populate" is the solution. I haven't tried it, but am busy downloading and going through it. flagfanatics, I will post again to confirm that this contrib is doing what I was originally looking for. Here is the link: http://www.oscommerce.com/community/contributions,1838 Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.