sales_report.php

Zen Cart是真正的电子商务艺术

<?php
/*
//////////////////////////////////////////////////////////
//  SALES REPORT                                        //
//                                                      //
//  By Frank Koehl (PM: BlindSide)                      //
//                                                      //
//  Powered by Zen-Cart (www.zen-cart.com)              //
//  Portions Copyright (c) 2006 The Zen Cart Team       //
//                                                      //
//  Released under the GNU General Public License       //
//  available at www.zen-cart.com/license/2_0.txt       //
//  or see "license.txt" in the downloaded zip          //
//////////////////////////////////////////////////////////
//  DESCRIPTION: The class file acts as the engine in   //
//  the sales report.  All the data displayed is        //
//  gathered and calculated in here.  The logic tree    //
//  provides a brief summary of the main functions at   //
//  work every time a report is generated.              //
//////////////////////////////////////////////////////////
// $Id: sales_report.php 104 2006-11-12 00:12:59Z BlindSide $
*/

/*
** Logic Tree of class sales_report functions
  sales_report - establishes base class variables, initializes loop for timeframes
   |
   |_build_timeframe - initial oID query for given timeframe
       |_build_li_totals - basic totals for each timeframe
           |_build_li_orders - line item for each order in the timeframe
           |_build_li_products - line item for each product in the timeframe
       |_build_matrix - calculate detailed stats for each timeframe; non-linear display;
                        uses data from build_li_orders and build_li_products
*/

  //_TODO modularize time format code, allowing for other formats

  class sales_report {
    var $timeframe_group, $sd, $ed, $sd_raw, $ed_raw, $date_target, $date_status;
    var $payment_method, $current_status, $manufacturer, $detail_level, $output_format;
    var $timeframe, $timeframe_id, $current_date, $product_filter;

    function sales_report($timeframe, $sd, $ed, $date_target, $date_status, $payment_method, $current_status, $manufacturer, $detail_level, $output_format) {
      global $db;

      // place passed variables into class variables
      $this->timeframe_group = $timeframe;
      $this->date_target = $date_target;
      $this->date_status = $date_status;
      $this->payment_method = $payment_method;
      $this->current_status = $current_status;
      $this->manufacturer = $manufacturer;
      $this->detail_level = $detail_level;
      $this->output_format = $output_format;

      // all our calculations are done using a "raw" timestamp format, which are
      // pulled from entered date strings using the substr function (similar to zen_date_raw)
      $this->sd_raw = mktime(0, 0, 0, substr($sd, 0, 2), substr($sd, 3, 2), substr($sd, 6, 4) );
      $this->ed_raw = mktime(0, 0, 0, substr($ed, 0, 2), substr($ed, 3, 2), substr($ed, 6, 4) );

      // run a few checks on the dates
      // avoid dates before the first order
      $first = $db->Execute("select UNIX_TIMESTAMP(min(date_purchased)) as date FROM " . TABLE_ORDERS);
      $first_order = $first->fields['date'];
      $this->global_sd = mktime(0, 0, 0, date("m", $first_order), date("d", $first_order), date("Y", $first_order));
      if ($this->sd_raw < $this->global_sd) $this->sd_raw = $this->global_sd;
      if ($this->ed_raw < $this->global_sd) $this->ed_raw = $this->global_sd;

      // avoid days in the future
      $now = mktime(0, 0, 0, date("m"), date("d"), date("Y") );
      if ($this->sd_raw > $now) $this->sd_raw = $now;
      if ($this->ed_raw > $now) $this->ed_raw = $now;

      // now that the date checks are out of the way, let's begin
      $this->sd = date(DATE_FORMAT_SPIFFYCAL, $this->sd_raw);
      $this->ed = date(DATE_FORMAT_SPIFFYCAL, $this->ed_raw);
      $this->current_date = $this->sd_raw;

      $this->timeframe_id = 0;
      $this->timeframe = array();
      $this->grand_total = array('goods' => 0,
                                 'num_orders' => 0,
                                 'num_products' => 0,
                                 'shipping' => 0,
                                 'tax' => 0,
                                 'discount' => 0,
                                 'discount_qty' => 0,
                                 'gc_sold' => 0,
                                 'gc_sold_qty' => 0,
                                 'gc_used' => 0,
                                 'gc_used_qty' => 0,
                                 'grand' => 0);

      while ($this->current_date <= $this->ed_raw) {
        $this->build_timeframe();
      }

      // build matrix data if requested
      // By placing it here and adding 'matrix' to the 'if' statements
      // for building order and product line items, we have all
      // the possible data at our disposal
      if ($this->detail_level == 'matrix') {
        $this->build_matrix();
      }
    }  // END function sales_report


    //////////////////////////////////////////////////////////
    // Each time this function runs, another timeframe array
    // is built.  The variable $this->current_date acts as the
    // key, used to determine the start and end dates of this
    // particular timeframe.  All other functions are called
    // from within here to build all the requested timeframe
    // information (order line items, product line items, or
    // data matrix).
    //
    function build_timeframe() {
      global $db;
      $id = $this->timeframe_id;  // we use $id to keep arrays short, easier to read

      // $sd and $ed are local to this function, not to be confused with
      // $this->start_date and $this->end_date, entered by the user
      $sd = $this->current_date;

      switch ($this->timeframe_group) {
        case 'year':
          $ed = mktime(0, 0, 0, date("m", $sd), date("d", $sd), date("Y", $sd) + 1);
        break;
        case 'month':
          $ed = mktime(0, 0, 0, date("m", $sd) + 1, 1, date("Y", $sd));
        break;
        case 'week':
          $ed = mktime(0, 0, 0, date("m", $sd), date("d", $sd) + 7, date("Y", $sd));
        break;
        case 'day':
          $ed = mktime(0, 0, 0, date("m", $sd), date("d", $sd) + 1, date("Y", $sd));
        break;
      }

      // dial back $ed if it's beyond the user-specified end date
      // we go 1 day beyond specified end date because end date is exclusive in the query
      if ($ed > $this->ed_raw) {
        $ed = mktime(0, 0, 0, date("m", $this->ed_raw), date("d", $this->ed_raw) + 1, date("Y", $this->ed_raw));
      }

      // define the timeframe array
      $this->timeframe[$id] = array();

      // store the start date and end date for this timeframe
      // timestamp format allows us to use whatever display format we want at output
      // we subtract 1 day so that the displayed end date is the actual end date
      $this->timeframe[$id]['sd'] = $sd;
      $this->timeframe[$id]['ed'] = mktime(0, 0, 0, date("m", $ed), date("d", $ed) - 1, date("Y", $ed));

      // build the excluded products array
      $this->product_filter = "";
      $exclude_products = unserialize(EXCLUDE_PRODUCTS);
      if (is_array($exclude_products) && sizeof($exclude_products) > 0) {
        foreach($exclude_products as $pID) {
          $this->product_filter .= " and op.products_id != '" . $pID . "' \n";
        }
      }

      // build the SQL query of order numbers within the current timeframe
      $sql = "SELECT DISTINCT o.orders_id from " . TABLE_ORDERS . " o \n";
      if ($this->date_target == 'status') {
        $sql .= "LEFT JOIN " . TABLE_ORDERS_STATUS_HISTORY . " osh ON o.orders_id = osh.orders_id \n";
        $sql .= "WHERE osh.date_added >= '" . date("Y-m-d H:i:s", $sd) . "' AND osh.date_added < '" . date("Y-m-d H:i:s", $ed) . "' \n";
        $sql .= "AND osh.orders_status_id = '" . $this->date_status . "' \n";
      }
      else {
        $sql .= "WHERE o.date_purchased >= '" . date("Y-m-d H:i:s", $sd) . "' AND o.date_purchased < '" . date("Y-m-d H:i:s", $ed) . "' \n";
      }
      if ($this->payment_method) $sql .= "AND o.payment_module_code LIKE '" . $this->payment_method . "' \n";
      if ($this->current_status) $sql .= "AND o.orders_status = '" . $this->current_status . "' \n";
      $sql .= "ORDER BY o.orders_id DESC";

      // DEBUG
      //$this->sql[$id] = $sql;

      // loop through query and build the arrays for this timeframe
      $sales = $db->Execute($sql);
      // make sure we actually have orders to process
      if ($sales->RecordCount() > 0) {
        // initialize the various timeframe arrays
        // by creating them inside the RecordCount() check, we can easily
        // check for an empty timeframe with is_array() in the report
        $this->timeframe[$id]['total'] = array('goods' => 0,
                                               'num_orders' => 0,
                                               'num_products' => 0,
                                               'shipping' => 0,
                                               'tax' => 0,
                                               'discount' => 0,
                                               'discount_qty' => 0,
                                               'gc_sold' => 0,
                                               'gc_sold_qty' => 0,
                                               'gc_used' => 0,
                                               'gc_used_qty' => 0,
                                               'grand' => 0,
                                               'diff_products' => array());

        if ($this->detail_level == 'order') {
          $this->timeframe[$id]['orders'] = array();
        }
        elseif ($this->detail_level == 'product') {
          $this->timeframe[$id]['products'] = array();
        }

        while (!$sales->EOF) {
          $oID = $sales->fields['orders_id'];
          $grand_total += $this->build_li_totals($oID);
          
          if (sizeof($this->timeframe[$id]['orders']) == 0) $this->timeframe[$id]['orders'] = false;
          if (sizeof($this->timeframe[$id]['products']) == 0) $this->timeframe[$id]['products'] = false;
          $sales->MoveNext();
        }

        // calculate the total for the timeframe
        $this->timeframe[$id]['total']['grand'] = $grand_total;
        //_MATHCHECK compare this figure to total of individual orders/products

        // add values to the grand total line at the bottom of the report
        $this->grand_total['goods'] += $this->timeframe[$id]['total']['goods'];
        $this->grand_total['num_orders'] += $this->timeframe[$id]['total']['num_orders'];
        $this->grand_total['num_products'] += $this->timeframe[$id]['total']['num_products'];
        $this->grand_total['shipping'] += $this->timeframe[$id]['total']['shipping'];
        $this->grand_total['tax'] += $this->timeframe[$id]['total']['tax'];
        $this->grand_total['discount'] += $this->timeframe[$id]['total']['discount'];
        $this->grand_total['discount_qty'] += $this->timeframe[$id]['total']['discount_qty'];
        $this->grand_total['gc_sold'] += $this->timeframe[$id]['total']['gc_sold'];
        $this->grand_total['gc_sold_qty'] += $this->timeframe[$id]['total']['gc_sold_qty'];
        $this->grand_total['gc_used'] += $this->timeframe[$id]['total']['gc_used'];
        $this->grand_total['gc_used_qty'] += $this->timeframe[$id]['total']['gc_used_qty'];
        $this->grand_total['grand'] += $this->timeframe[$id]['total']['grand'];
      }

      // Since $sd is inclusive, but $ed is exclusive in our query, we need
      // only set next starting point to the current $ed
      $this->current_date = $ed;

      // increment the id number
      $this->timeframe_id++;
    }  // END function build_timeframe()


    //////////////////////////////////////////////////////////
    // build_li_totals() actually does the tallying for each
    // order found within the timeframe set and searched in
    // build_timeframe().  It calls build_li_orders() and
    // build_li_products() as needed.
    //
    function build_li_totals($oID) {
      global $db;
      $id = $this->timeframe_id;

      // if we have to filter on manufacturer, the SQL is totally different
      if ($this->manufacturer) {
        $products_sql = "select op.* from " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_PRODUCTS . " p
                         where p.products_id = op.products_id
                         and p.manufacturers_id = " . $this->manufacturer . "
                         and op.orders_id = '" . $oID . "'" . $this->product_filter;
      } else {
        $products_sql = "select op.* from " . TABLE_ORDERS_PRODUCTS . " op
                         where op.orders_id = '" . $oID . "'" . $this->product_filter;
      }
      $products = $db->Execute($products_sql);

      // these "order_" variables are local to the build_li_totals() function.  They
      // are used to determine order total, timeframe grand total, and order count
      $order_goods = 0;
      $order_tax = 0;
      $order_shipping = 0;
      $order_discount = 0;
      $order_gc_sold = 0;
      $order_gc_used = 0;

      while (!$products->EOF) {
        // assign key values to shorter variables for clarity
        $pID = $products->fields['products_id'];
        $final_price = $products->fields['final_price'];
        $quantity = $products->fields['products_quantity'];
        $tax = $products->fields['products_tax'];
        $onetime_charges = $products->fields['onetime_charges'];
        $model = zen_db_output($products->fields['products_model']);

        // do the math

        // gift certificates aren't products, so we must separate those out
        if (substr($model, 0, 4) == 'GIFT') {
          $order_gc_sold += ($final_price * $quantity);
          $this->timeframe[$id]['total']['gc_sold'] += ($final_price * $quantity);
          $this->build_li_orders($oID, 'gc_sold', ($final_price * $quantity) );

          $this->timeframe[$id]['total']['gc_sold_qty'] += $quantity;
          $this->build_li_orders($oID, 'gc_sold_qty', $quantity);

          $order_goods += $onetime_charges;
          $this->timeframe[$id]['total']['goods'] += $onetime_charges;
          $this->build_li_orders($oID, 'goods', $onetime_charges);
        }
        // otherwise calculate the worth of normal goods
        else {
          $order_goods += ( ($final_price * $quantity) + $onetime_charges );
          $this->timeframe[$id]['total']['goods'] += ( ($final_price * $quantity) + $onetime_charges );
          $this->build_li_orders($oID, 'goods', (($final_price * $quantity) + $onetime_charges) );

          $this->timeframe[$id]['total']['num_products'] += $quantity;
          $this->build_li_orders($oID, 'num_products', $quantity);
        }
        // future code - currently being pulled from orders_total table for better accuracy
        // calculate tax values
        //$this->timeframe[$id]['total']['tax'] += zen_calculate_tax($onetime_charges, $tax);
        //$this->timeframe[$id]['total']['tax'] += zen_calculate_tax(($final_price * $quantity), $tax);

        // check to see if product is unique in this timeframe
        // add to 'diff_products' array if so
        $diff_prod_total = $this->unique_count($pID, $this->timeframe[$id]['total']['diff_products']);
        if ($diff_prod_total) $this->timeframe[$id]['total']['diff_products'][] = $pID;

        $diff_prod_order = $this->unique_count($pID, $this->timeframe[$id]['orders'][$oID]['diff_products']);