Seeking ZC gurus php help - auto order staus update script

I am tyring to set up a script to run on a cron job which automatically updates the status of my orders to shipped based on a feed that my suppliers send me.

I am currently working on the reading the feed bit to get the status but was hoping someone can help me get the code right for the order update part. My assumption is that the following needs to happen:

select * from orders where orders_status = 2; //where order status 2 is processing

while { //run a while loop for each order that is in processing status

$orders_id = result; // define which order id we are currently dealing with

do the xml thing here

$new_status = "Shipped"; //defined by pulling ht einfo from my feed from supplier

if ($new_status = Shipped) { //if the current order id is found to be Shipped status do the following:

//this is the part I need help with...

//1. update order status, pretty sure this is correct
Update orders SET orders_status = 3 WHERE orders_id = $orders_id; // where order status = 3 is Shipped

//2. insert some values into order history and set if they can be veiwed by customer or not??
INSERT into orders_status blar, blar blar;

//3. send order update email to customer, not sure how this should be?
zen_mail (.... customer whos order id = $order_id

}

} move_next

exit;

Thanks in advance

Phil

Well I worked it out for myself eventually. For those who may be interested in how you can achieve it here is what I did.

start off with:

require_once ('includes/application_top.php');

then I simply used the code from within the orders.php file under case 'update' and jigged it about. rather than including the language file from my admin directory I simply replaced all the defines with hard coded values. the only thing I couldnt get to go was the url, im guessing another file needs to be included to allow this function to work but to be honest I wasnt too bothered so just removed it.

so the order update code and sending of email was done with this:

$oID = $value->YourOrderNumber;
  $check_status = $db->Execute("select customers_name, customers_email_address, orders_status,
                                      date_purchased from orders
                                      where orders_id = '" . (int)$oID . "'");
                                      
                                      
    if ($check_status->fields['orders_status'] == 2 && $value->CurrentStatus == "OrderShipped") { // check only orders that are currently processing and need updating
  
   
                 
                            

                              $comments = "Your Order has been despatched. Thank you for your order!";
   

                 $notify_comments = '<em>The comments for your order are: </em>' . $comments . "\n\n";
                 $notify_comments_print = '<em>The comments for your order are: </em>' . $comments . "\n\n <br /><hr />";
                 echo $oID . ": " . $notify_comments_print;
                  
          // update orders and email customers
          
          $db->Execute("update orders
                        set orders_status = 3, last_modified = now()
                        where orders_id = '" . (int)$oID . "'");
                        
                        
          $db->Execute("insert into orders_status_history
                      (orders_id, orders_status_id, date_added, customer_notified, comments)
                      values ('" . (int)$oID . "',
                      3,
                      now(),
                      1,
                      '" . zen_db_input($comments)  . "')");
                                      

            //send emails
            $message =
            'Order Number: ' . $oID . "\n\n" .
            //EMAIL_TEXT_INVOICE_URL . ' ' .
            'Date Ordered: ' . zen_date_long($check_status->fields['date_purchased']) . "\n\n" .
            strip_tags($notify_comments) .
            'Your order has been updated to the following status:' . "\n" . sprintf('<strong>New status:</strong> %s' . "\n\n",  $status ) .
            'Please reply to this email if you have any questions.' . "\n";

            $html_msg['EMAIL_CUSTOMERS_NAME']    = $check_status->fields['customers_name'];
            $html_msg['EMAIL_TEXT_ORDER_NUMBER'] = 'Order Number: ' . $oID;
            $html_msg['EMAIL_TEXT_INVOICE_URL']  = '';
            $html_msg['EMAIL_TEXT_DATE_ORDERED'] = 'Date Ordered: ' . zen_date_long($check_status->fields['date_purchased']);
            $html_msg['EMAIL_TEXT_STATUS_COMMENTS'] = nl2br($notify_comments);
            $html_msg['EMAIL_TEXT_STATUS_UPDATED'] = str_replace('\n','', 'Your order has been updated to the following status:' . "\n");
            $html_msg['EMAIL_TEXT_STATUS_LABEL'] = str_replace('\n','', sprintf('<strong>New status:</strong> %s' . "\n\n",  $status));
            $html_msg['EMAIL_TEXT_NEW_STATUS'] = 'Despatched';
            $html_msg['EMAIL_TEXT_STATUS_PLEASE_REPLY'] = str_replace('\n','', 'Please reply to this email if you have any questions.' . "\n");
            $html_msg['EMAIL_PAYPAL_TRANSID'] = '';

            zen_mail($check_status->fields['customers_name'], $check_status->fields['customers_email_address'], 'Order Update #' . $oID, $message, STORE_NAME, EMAIL_FROM, $html_msg, 'order_status');
                  
            //end update order and email customer
                  

	
	} // end check if order status is currently processing