source: branches/1.1dev/lib/OrderStatus.inc.php

<?php
/**
 * OrderStatus.inc.php
 * Code by Strangecode :: www.strangecode.com :: This document contains copyrighted information
 */


/**
 * This function returns the number of orders with a status of $status.
 * Current valid status' are pending, confirmed, shipped, and canceled. 
 *
 * @param  string $status   the status for which to search. leave blank to
 *                          search for all.
 *
 * @return int    the number of orders with a status of $status
 */
function totalOrders($status='')
{
    if ($status != '') {
        $whereclause = 'WHERE status = ' . mysql_real_escape_string($status);
    } else {
        $whereclause = '';
    } 
    $qid = dbQuery("
        SELECT COUNT(*)
        FROM order_tbl
        $whereclause
    ");
    $num = mysql_fetch_row($qid);
    return $num[0];
}

/**
 * Updates the status of an order. Checks that the order exists first. If the status
 * is changed from an active order to an inactive one, the catalog quantities are increased
 * just as if they were "not sold" and likewise, if change from inactive to active, then
 * the catalog quantities are decreased like they've been sold. The user is emailed a
 * notice of the change if $email_user = TRUE. 
 *
 * @param  int    $order_id     the id of the order to set
 * @param  string $new_status   the status to set it to
 * @param  string $email_user   if true, send this user an email with updated status
 *
 * @return bool    true if old status is the same as new status
 *                 false if the new status is not one of the 4 valid types
 *                 true if order is valid, update is successful and user email
 *                                                    (if specified) goes out
 *                 false if order does not exist
 */
function setOrderStatus($order_id, $new_status='', $email_user=false)
{   
    global $CFG, $_SESSION;

    $qid = dbQuery("SELECT status, email, first_name, last_name FROM order_tbl WHERE order_id = " . mysql_real_escape_string($order_id));
    if (mysql_num_rows($qid) == 1) {
    /* The order exists, we contine. */
        
        $order = mysql_fetch_assoc($qid);
        $old_status = $order['status'];
        if ($old_status == $new_status) {
            return true;
        }
        
        /* Determine if the items should be removed or added to the catalog depending
         * on the type of status change. */
        if (($old_status == 'pending' || $old_status == 'canceled') && ($new_status == 'confirmed' || $new_status == 'shipped')) {
            $polarity = '-';        
        } else if (($new_status == 'pending' || $new_status == 'canceled') && ($old_status == 'confirmed' || $old_status == 'shipped')) {
            $polarity = '+';
        } else {
            $polarity = '=';
        }
        
        /* Ensure that the new status is a valid option for the database update. */
        switch ($new_status) {
        
            case 'pending':
                $db_update = 'pending';
                break;
        
            case 'canceled':
                $db_update = 'canceled';
                break;
        
            case 'confirmed':
                $db_update = 'confirmed';
                break;
        
            case 'shipped':
                $db_update = 'shipped';
                break;
        
            default:
                return false;
        }
        
        if (!actualizeOrderItems($order_id, $polarity)) {
            /* If this fails (because of too few items in stock for this order change)
             * we just say "fuck it" and cancel the order. */
            $db_update = 'canceled';
            $email_user = false;
            raiseMsg(sprintf(_("Order number <strong>%s</strong> has been canceled because there is none in stock"), $order_id), MSG_WARNING, __FILE__, __LINE__);
        }
        
        /* Otherwise we assume everything was updated okay and that 
         * we have a valid new status and so proceed updating the orders table. */
        dbQuery("UPDATE order_tbl SET status = " . mysql_real_escape_string($db_update) . " WHERE order_id = " . mysql_real_escape_string($order_id));
        
        if ($email_user == true) {
            /* email the user about the order status change */
            
            /* Query to load the details of this order. */
            $qid_order = dbQuery("SELECT * FROM order_tbl WHERE order_id = " . mysql_real_escape_string($order_id));
            $order = mysql_fetch_assoc($qid_order);

            /* Query to load the item associated with this order.
             * $qid_items is used to display a list of items ordered. */
            $qid_items = dbQuery("
                SELECT
                    oi.product_id,
                    p.title,
                    p.product_type,
                    p.retail_price,
                    oi.purchase_price,
                    oi.purchase_weight,
                    oi.qty,
                    oi.purchase_price * oi.qty AS total
                FROM order_items_tbl oi
                LEFT JOIN product_tbl p
                ON (oi.product_id = p.product_id)
                WHERE oi.order_id = " . mysql_real_escape_string($order_id) . "
            ");
            $item_num = 0;
            while ($item = mysql_fetch_object($qid_items)) {
                $product_name = getProductTitle($item->product_id);
                $item_num++;
                $subtotal += $item->total;
                $item_list .= " $product_name   " . getFormattedPrice ($item->retail_price) . " x " . $item->qty . "\n";
//              $item_list .= " $item->product_id   $product_name   " . getFormattedPrice($item->retail_price) . " x " . $item->qty . "\n";
            }
            
            /* Get the credit card info from crypt. */
            $cc = uncrypt_cc($order['crypt']);

            $var = new stdClass;
            
            $var->total_items = $item_num;
            $var->item_list = $item_list;
            $var->subtotal = getFormattedPrice($subtotal);
            $var->tax = getFormattedPrice($order['tax']);
            $var->delivery = getFormattedPrice($order['delivery']);
            $var->final_price = getFormattedPrice($order['final_price']);

            $var->email = $order['email'];
            $var->first_name = $order['first_name'];
            $var->last_name = $order['last_name'];
            $var->phone = $order['phone'];
            $var->bill_street = $order['bill_street'];
            $var->bill_city = $order['bill_city'];
            $var->bill_state = $order['bill_state'];
            $var->bill_zip = $order['bill_zip'];
            $var->bill_country = $order['bill_country'];
            $var->ship_street = $order['ship_street'];
            $var->ship_city = $order['ship_city'];
            $var->ship_state = $order['ship_state'];
            $var->ship_zip = $order['ship_zip'];
            $var->ship_country = $order['ship_country'];
            $var->notes = $order['notes'];
            $var->emaillist = !empty($order['emaillist']) ? 'yes' : 'no';
            $var->memberme = !empty($order['memberme']) ? 'yes' : 'no';
            $var->delivery_type = $order['delivery_type'];
            if ($order['payment_type'] != 'i_will_mail_my_payment' && $order['payment_type'] != 'contact_me_about_my_order') {
                $var->payment_info = "   " . strtoupper($order['payment_type']) . " credit card\n";
                $var->payment_info .= "   Cardholder: " . $cc['cc_name'] . "\n";
                $var->payment_info .= "   " . chop_ccnum($cc['cc_number']) . "\n";
                $var->payment_info .= "   Expires: " . $cc['cc_expiry'];
            } else {
                $var->payment_info = "   " . $order['payment_type'];
            }
            $var->date = $order['date'];
            $var->orderid = $order['order_id'];
            $var->newstatus = $new_status;
            $var->oldstatus = $old_status;
            
            $emailbody = wordwrap(read_template($CFG->templatedir . '/order_status_emailbody.ihtml', $var));
                            
            mail("{$order['first_name']} {$order['last_name']} <{$order['email']}>",
                 $TXT->emailsubject_order_status_change . " $var->orderid - $var->newstatus",
                 $emailbody,
                 "From: $CFG->site_name <$CFG->site_email>\r\n");
        }
        // This is a valid order
        return true;
    }
    
    return false;
}

/**
 * Increments or decrements the catalog for items in an order, based on the quantities
 * in that order. $polarity determines which way to go. Polarity values can be
 * "+" for increasing the item quantities and thus showing them in the catalog,
 * or "-" to subtract them from the catalog. 
 *
 * @param  int    $order_id   the id of the order that we change quantities for
 * @param  char   $polarity   '+' or '-' to increase or decrease inventory.
 *
 * @return bool    true if quantity acualization goes through
 *                 false if not enough inventory for an order
 *                 true if polarity is '=' (don't know when this will happen)
 *                 false if polarity is unknown.
 */
function actualizeOrderItems($order_id, $polarity='')
{  
    if ($polarity == '+' || $polarity == '-') {
        $qid = dbQuery("SELECT product_id, qty as order_qty FROM order_items_tbl WHERE order_id = " . mysql_real_escape_string($order_id));
        /* First we make sure each item is in stock in adequate quantities. */
        while ($order_item = mysql_fetch_assoc($qid)) {
            $product_id =& $order_item['product_id'];
            $qid_p = dbQuery("SELECT qty_in_stock FROM product_tbl WHERE product_id = '$product_id'");
            $product = mysql_fetch_assoc($qid_p);
            if ($polarity == '+') {
                $new_qty[$product_id] = $product['qty_in_stock'] + $order_item['order_qty'];
            } else if ($polarity == '-') {
                $new_qty[$product_id] = $product['qty_in_stock'] - $order_item['order_qty'];
            }
            if ($new_qty[$product_id] < 0) {
                $errormsg .= sprintf(_("Item <strong>%s</strong> in order number <strong>%s</strong> is no longer available in that quantity. There are %s available and the order is for %s items."), $product_id, $order_id, $product['qty_in_stock'], $order_item['order_qty']);
            } 
        }
        
        if (empty($errormsg)) {
        /* We have enough quantities, so we go ahead and make the database changes. */
            foreach ($new_qty as $product_id=>$qty) {
                dbQuery("
                    UPDATE product_tbl
                    SET qty_in_stock = '$qty'
                    WHERE product_id = '$product_id'
                ");
            }
            return true;
        } else {
        /* Not enough of something in stock, we don't touch any quantities in the database.
         * We should now cancel the order and print an alert that there are not enough items available */
            raiseMsg($errormsg, MSG_WARNING, __FILE__, __LINE__);
            return false;
        }
    } else if ($polarity == '=') {
        return true;
    } else {
        return false;
    }
}

?>