[ Index ]

PHP Cross Reference of osCMax 2.0.4

title

Body

[close]

/admin/ -> stats_monthly_sales.php (source)

   1  <?php
   2  /*

   3  $Id: stats_monthly_sales.php 3 2006-05-27 04:59:07Z user $

   4  

   5    contributed by Fritz Clapp <[email protected]>

   6  

   7  This report displays a summary of monthly or daily totals:

   8      gross income (order totals)

   9      subtotals of all orders in the selected period

  10      nontaxed sales subtotals

  11      taxed sales subtotals

  12      tax collected

  13      shipping/handling charges

  14      low order fees (if present)

  15      gift vouchers (or other addl order total component, if present)

  16  

  17  The data comes from the orders and orders_total tables, therefore this report

  18  works only for osCommerce snapshots since 2002/04/08 (including MS1 and MS2).

  19  

  20  Data is reported as of order purchase date.

  21  

  22  If an order status is chosen, the report summarizes orders with that status.

  23  

  24  Version 2.0 introduces the capability to "drill down" on any month

  25  to report the daily summary for that month.  

  26  

  27  Report rows are initially shown in newest to oldest, top to bottom, 

  28  but this order may be inverted by clicking the "Invert" control button.

  29  

  30  Version 2.1 adds a popup display that lists the various types (and their

  31  subtotals) comprising the tax values in the report rows.

  32  

  33  **NOTE:

  34  This Version 2.1.1 has columns that summarize nontaxed and taxed order subtotals.

  35  The assumptions made for this feature may not be appropriate for all stores.

  36  The taxed column summarizes subtotals for orders in which tax was charged.

  37  The nontaxed column is the subtotal for the row less the taxed column value.

  38  

  39    osCMax Power E-Commerce

  40    http://oscdox.com

  41    Copyright 2006 osCMax2004 osCommerce

  42    Released under the GNU General Public License

  43  */
  44  
  45    require ('includes/application_top.php');
  46  
  47    require (DIR_WS_CLASSES . 'currencies.php');
  48    $currencies = new currencies();
  49  //

  50  // entry for help popup window

  51  if (isset($_GET['help'])){ 
  52    echo TEXT_HELP;
  53    exit;
  54  };
  55  //

  56  // entry for bouncing csv string back as file

  57  if (isset($_POST['csv'])) {
  58  if ($HTTP_POST_VARS['saveas']) {  // rebound posted csv as save file
  59          $savename= $HTTP_POST_VARS['saveas'] . ".csv";
  60          }
  61          else $savename='unknown.csv';
  62  $csv_string = '';
  63  if ($HTTP_POST_VARS['csv']) $csv_string=$HTTP_POST_VARS['csv'];
  64    if (strlen($csv_string)>0){
  65    header("Expires: Mon, 26 Nov 1962 00:00:00 GMT");
  66    header("Last-Modified: " . gmdate('D,d M Y H:i:s') . ' GMT');
  67    header("Cache-Control: no-cache, must-revalidate");
  68    header("Pragma: no-cache");
  69    header("Content-Type: Application/octet-stream");
  70    header("Content-Disposition: attachment; filename=$savename");
  71    echo $csv_string;
  72    }
  73    else echo "CSV string empty";
  74  exit;
  75  };
  76  //

  77  // entry for popup display of tax detail

  78  // show=ot_tax 

  79  if (isset($HTTP_GET_VARS['show'])) {
  80      $ot_type = tep_db_prepare_input($HTTP_GET_VARS['show']);
  81      $sel_month = tep_db_prepare_input($HTTP_GET_VARS['month']);
  82      $sel_year = tep_db_prepare_input($HTTP_GET_VARS['year']);
  83      $sel_day = 0;
  84      if (isset($HTTP_GET_VARS['day'])) $sel_day = tep_db_prepare_input($HTTP_GET_VARS['day']);
  85      $status = '';
  86      if ($HTTP_GET_VARS['status']) $status = tep_db_prepare_input($HTTP_GET_VARS['status']);
  87      // construct query for selected detail

  88      $detail_query_raw = "select sum(round(ot.value,2)) amount, ot.title description from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) where ";
  89      if ($status<>'') $detail_query_raw .= "o.orders_status ='" . $status . "' and ";
  90      $detail_query_raw .= "ot.class = '" . $ot_type . "' and month(o.date_purchased)= '" . $sel_month . "' and year(o.date_purchased)= '" . $sel_year . "'";
  91      if ($sel_day<>0) $detail_query_raw .= " and dayofmonth(o.date_purchased) = '" . $sel_day . "'";
  92      $detail_query_raw .= " group by ot.title";
  93      $detail_query = tep_db_query($detail_query_raw);
  94      echo "<!doctype html public \"-//W3C//DTD HTML 4.01 Transitional//EN\"><html " . HTML_PARAMS . "><head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=" . CHARSET . "\">" . "<title>" . TEXT_DETAIL . "</title><link rel=\"stylesheet\" type=\"text/css\" href=\"includes/stylesheet.css\"></head><body><br><table width=\"80%\" align=center><caption align=center>";
  95      echo $sel_year . "/" . $sel_month;
  96      if ($sel_day<>0) echo "/" . $sel_day;
  97      if ($status<>'') echo "<br>" . HEADING_TITLE_STATUS . ":" . "&nbsp;" . $status;
  98      echo "</caption>";
  99   
 100      while ($detail_line = tep_db_fetch_array($detail_query)) {
 101      echo "<tr class=dataTableRow><td align=left width='75%'>" . $detail_line['description'] . "</td><td align=right>" . number_format($detail_line['amount'],2) . "</td></tr>";}
 102      echo "</table></body>";
 103  exit;
 104  };
 105  //

 106  // main entry for report display

 107  ?>
 108  <!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
 109  <html <?php echo HTML_PARAMS; ?>>
 110  <head>
 111  <meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
 112  <title><?php echo TITLE; ?></title>
 113  <link rel="stylesheet" type="text/css" href="<?php if(!$print) {
 114      echo 'includes/stylesheet.css';}
 115      else echo 'includes/printer.css'; ?>">
 116  </head>
 117  <body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF">
 118  <?php
 119  // set printer-friendly toggle

 120  (tep_db_prepare_input($HTTP_GET_VARS['print']=='yes')) ? $print=true : $print=false;
 121  // set inversion toggle

 122  (tep_db_prepare_input($HTTP_GET_VARS['invert']=='yes')) ? $invert=true : $invert=false;
 123  ?>
 124  <!-- header //-->
 125  <?php if(!$print) require (DIR_WS_INCLUDES . 'header.php'); ?>
 126  <!-- header_eof //-->
 127  
 128  <!-- body //-->
 129  <table border="0" width="100%" cellspacing="2" cellpadding="2">
 130    <tr>
 131  
 132  <?php  // suppress left column for printer-friendly version
 133      if(!$print) {?>
 134      <td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">
 135      <!-- left_navigation //-->
 136      <?php require (DIR_WS_INCLUDES . 'column_left.php'); ?>
 137      <!-- left_navigation_eof //-->
 138          </table></td>
 139  <?php    };    ?>
 140  
 141  <!-- body_text //-->
 142      <td width="100%" valign="top">
 143      <table border="0" width="100%" cellspacing="0" cellpadding="2">
 144        <tr>
 145          <td>
 146          <table border="0" width="100%" cellspacing="0" cellpadding="0">
 147  <?php if ($print) {
 148      echo "<tr><td class=\"pageHeading\">" . STORE_NAME ."</td></tr>";
 149      };
 150  ?>
 151            <tr>
 152              <td class="pageHeading">
 153              <?php echo HEADING_TITLE; ?></td>
 154  <?php 
 155  // detect whether this is monthly detail request

 156  $sel_month = 0;
 157      if ($HTTP_GET_VARS['month']&& $HTTP_GET_VARS['year']) {
 158      $sel_month = tep_db_prepare_input($HTTP_GET_VARS['month']);
 159      $sel_year = tep_db_prepare_input($HTTP_GET_VARS['year']);
 160      };
 161  // get list of orders_status names for dropdown selection

 162    $orders_statuses = array();
 163    $orders_status_array = array();
 164    $orders_status_query = tep_db_query("select orders_status_id, orders_status_name from " . TABLE_ORDERS_STATUS . " where language_id = '" . $languages_id . "'");
 165    while ($orders_status = tep_db_fetch_array($orders_status_query)) {
 166      $orders_statuses[] = array('id' => $orders_status['orders_status_id'],
 167                   'text' => $orders_status['orders_status_name']);
 168      $orders_status_array[$orders_status['orders_status_id']] = $orders_status['orders_status_name'];
 169        };
 170  // name of status selection

 171  $orders_status_text = TEXT_ALL_ORDERS;
 172  if ($HTTP_GET_VARS['status']) {
 173    $status = tep_db_prepare_input($HTTP_GET_VARS['status']);
 174    $orders_status_query = tep_db_query("select orders_status_name from " . TABLE_ORDERS_STATUS . " where language_id = '" . $languages_id . "' and orders_status_id =" . $status);
 175    while ($orders_status = tep_db_fetch_array($orders_status_query)) {
 176        $orders_status_text = $orders_status['orders_status_name'];}
 177                  };    
 178  if (!$print) { ?>
 179              <td align="right">
 180              <table border="0" width="100%" cellspacing="0" cellpadding="0">
 181                <tr><td class="smallText" align="right">
 182                  <?php echo tep_draw_form('status', FILENAME_STATS_MONTHLY_SALES, '', 'get');
 183                  // get list of orders_status names for dropdown selection

 184                    $orders_statuses = array();
 185                    $orders_status_array = array();
 186                    $orders_status_query = tep_db_query("select orders_status_id, orders_status_name from " . TABLE_ORDERS_STATUS . " where language_id = '" . $languages_id . "'");
 187                    while ($orders_status = tep_db_fetch_array($orders_status_query)) {
 188                      $orders_statuses[] = array('id' => $orders_status['orders_status_id'],
 189                       'text' => $orders_status['orders_status_name']);
 190                      $orders_status_array[$orders_status['orders_status_id']] = $orders_status['orders_status_name'];
 191                    };
 192                  echo HEADING_TITLE_STATUS . ': ' . tep_draw_pull_down_menu('status', array_merge(array(array('id' => '', 'text' => TEXT_ALL_ORDERS)), $orders_statuses), '', 'onChange="this.form.submit();"'); ?>
 193                  <input type="hidden" name="selected_box" value="reports">
 194                  <?php
 195                      if ($sel_month<>0) 
 196                      echo "<input type='hidden' name='month' value='" . $sel_month . "'><input type='hidden' name='year' value='" . $sel_year . "'>";
 197                      if ($invert) echo "<input type='hidden' name='invert' value='yes'>";
 198                  ?>
 199                  </td>
 200                </form></tr>
 201               </table>
 202               </td>
 203  <?php        }; ?>
 204  
 205  <?php if ($print) { ?>
 206              <td>
 207              </td>
 208          <tr><td>
 209                  <table>
 210                  <tr><td class="smallText"><?php echo HEADING_TITLE_REPORTED . ": "; ?></td>
 211                  <td width="8"></td>
 212                  <td class="smallText" align="left"><?php echo date(ltrim(TEXT_REPORT_DATE_FORMAT)); ?></td>
 213                  </tr>
 214                  <tr><td class="smallText" align="left">
 215                  <?php echo HEADING_TITLE_STATUS . ": ";  ?></td>
 216                  <td width="8"></td>
 217                  <td class="smallText" align="left">
 218                  <?php echo $orders_status_text;?>
 219                  </td>
 220                  </tr>
 221                  <table>
 222              </td><td></td>
 223          </tr>
 224  <?php     };     ?>
 225          </table></td>
 226        </tr>
 227  <?php if(!$print) { ?>
 228  <!--
 229  row for buttons to print, save, and help
 230  -->
 231              <tr>
 232                  <td  align="right">
 233                  <table align=right cellspacing="10"><tr>
 234                  <td align="left" class="smallText">
 235                  <?php  // back button if monthly detail
 236                  if ($sel_month<>0)     {
 237                  echo "<a href='" . $_SERVER['PHP_SELF'] . "?&selected_box=reports";
 238                  if (isset($HTTP_GET_VARS['status'])) echo "&status=" . $status;
 239                  if (isset($HTTP_GET_VARS['invert'])) echo "&invert=yes";
 240                  echo "' title='" . TEXT_BUTTON_REPORT_BACK_DESC . "'>" . TEXT_BUTTON_REPORT_BACK . "</a>";
 241                  };
 242                  ?>
 243                  </td>
 244                  <td class="smallText"><a href="<?php  
 245                  echo $_SERVER['PHP_SELF'] . "?" . $_SERVER['QUERY_STRING'] . "&print=yes";
 246                  ?>" target="print" title="<?php echo TEXT_BUTTON_REPORT_PRINT_DESC . "\">" . TEXT_BUTTON_REPORT_PRINT; ?></a>
 247                  </td>
 248                  <td class="smallText"><a href='<?php echo $_SERVER['PHP_SELF'] . "?" . ereg_replace('&invert=yes','',$_SERVER['QUERY_STRING']);
 249                  if (!$invert) echo "&invert=yes";
 250                  echo "' title= '" . TEXT_BUTTON_REPORT_INVERT_DESC . "'>" . TEXT_BUTTON_REPORT_INVERT; ?></a>
 251                  </td>
 252                  <td class="smallText"><a href="#" onClick="window.open('<?php  
 253                  echo $_SERVER['PHP_SELF'] . "?&help=yes";    ?>','help',config='height=400,width=600,scrollbars=1, resizable=1')" title="<?php echo TEXT_BUTTON_REPORT_HELP_DESC . "\">" . TEXT_BUTTON_REPORT_HELP; ?></a>
 254                  </td>
 255                  </tr></table>
 256                  </td>
 257              </tr>
 258  <?php    };    
 259  //

 260  // determine if loworder fee is enabled in configuration, include/omit the column

 261  $loworder_query_raw = "select configuration_value from " . TABLE_CONFIGURATION . " where configuration_key =" . "'MODULE_ORDER_TOTAL_LOWORDERFEE_LOW_ORDER_FEE'";
 262  $loworder = false;
 263  $loworder_query = tep_db_query($loworder_query_raw);
 264  if (tep_db_num_rows($loworder_query)>0) {
 265      $low_setting=tep_db_fetch_array($loworder_query);
 266      if ($low_setting['configuration_value']=='true') $loworder=true;
 267  };
 268  //

 269  // if there are extended class values in orders_table

 270  // create extra column so totals are comprehensively correct

 271  $class_val_subtotal = "'ot_subtotal'";
 272  $class_val_tax = "'ot_tax'";
 273  $class_val_shiphndl = "'ot_shipping'";
 274  $class_val_loworder = "'ot_loworderfee'";
 275  $class_val_total = "'ot_total'";
 276      $extra_class_query_raw = "select value from " . TABLE_ORDERS_TOTAL . " where class <> " . $class_val_subtotal . " and class <>" . $class_val_tax . " and class <>" . $class_val_shiphndl . " and class <>" . $class_val_loworder . " and class <>" . $class_val_total;
 277      $extra_class = false;
 278      $extra_class_query = tep_db_query($extra_class_query_raw);
 279      if (tep_db_num_rows($extra_class_query)>0) $extra_class = true;
 280  // start accumulator for the report content mirrored in CSV

 281  $csv_accum = '';
 282  ?>
 283        <tr>
 284          <td><table border="0" width="100%" cellspacing="0" cellpadding="2">
 285            <tr>
 286              <td valign="top">
 287              <table border="0" width='100%' cellspacing="0" cellpadding="2">
 288  <tr class="dataTableHeadingRow">
 289  <td class="dataTableHeadingContent" width='45' align='left' valign="bottom"><?php 
 290  if ($sel_month == 0) mirror_out(TABLE_HEADING_MONTH); else mirror_out(TABLE_HEADING_MONTH); ?>
 291  </td>
 292  <td class="dataTableHeadingContent" width='35' align='left' valign="bottom"><?php 
 293  if ($sel_month == 0) mirror_out(TABLE_HEADING_YEAR); else mirror_out(TABLE_HEADING_DAY); ?></td>
 294  <td class="dataTableHeadingContent" width='70' align='right' valign="bottom"><?php mirror_out(TABLE_HEADING_INCOME); ?></td>
 295  <td class="dataTableHeadingContent" width='70' align='right' valign="bottom"><?php mirror_out(TABLE_HEADING_SALES); ?></td>
 296  <td class="dataTableHeadingContent" width='70' align='right' valign="bottom"><?php mirror_out(TABLE_HEADING_NONTAXED); ?></td>
 297  <td class="dataTableHeadingContent" width='70' align='right' valign="bottom"><?php mirror_out(TABLE_HEADING_TAXED); ?></td>
 298  <td class="dataTableHeadingContent" width='70' align='right' valign="bottom"><?php mirror_out(TABLE_HEADING_TAX_COLL); ?></td>
 299  <td class="dataTableHeadingContent" width='70' align='right' valign="bottom"><?php mirror_out(TABLE_HEADING_SHIPHNDL); ?></td>
 300  <?php 
 301  if ($loworder) { ?>
 302  <td class="dataTableHeadingContent" width='70' align='right' valign="bottom"><?php mirror_out(TABLE_HEADING_LOWORDER); ?></td>
 303  <?php }; ?>
 304  <?php
 305  if ($extra_class) { ?>
 306  <td class="dataTableHeadingContent" width='70' align='right' valign="bottom"><?php mirror_out(TABLE_HEADING_OTHER); ?></td>
 307  <?php }; ?>
 308  </tr>
 309  <?php 
 310  // clear footer totals

 311      $footer_gross = 0;
 312      $footer_sales = 0;
 313      $footer_sales_nontaxed = 0;
 314      $footer_sales_taxed = 0;
 315      $footer_tax_coll = 0;
 316      $footer_shiphndl = 0;
 317      $footer_loworder = 0;
 318      $footer_other = 0;
 319  // new line for CSV

 320  $csv_accum .= "\n";
 321  // order totals, the driving force 

 322  $status = '';
 323  $sales_query_raw = "select sum(round(ot.value,2)) gross_sales, monthname(o.date_purchased) row_month, year(o.date_purchased) row_year, month(o.date_purchased) i_month, dayofmonth(o.date_purchased) row_day  from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) where ";
 324  if ($HTTP_GET_VARS['status']) {
 325    $status = tep_db_prepare_input($HTTP_GET_VARS['status']);
 326    $sales_query_raw .= "o.orders_status =" . $status . " and ";
 327      };
 328  $sales_query_raw .= "ot.class = " . $class_val_total;
 329  if ($sel_month<>0) $sales_query_raw .= " and month(o.date_purchased) = " . $sel_month;
 330  $sales_query_raw .= " group by year(o.date_purchased), month(o.date_purchased)";
 331  if ($sel_month<>0) $sales_query_raw .= ", dayofmonth(o.date_purchased)";
 332  $sales_query_raw .=  " order by o.date_purchased ";
 333  if ($invert) $sales_query_raw .= "asc"; else $sales_query_raw .= "desc";
 334  $sales_query = tep_db_query($sales_query_raw);
 335  $num_rows = tep_db_num_rows($sales_query);
 336  if ($num_rows==0) echo '<tr><td class="smalltext">' . TEXT_NOTHING_FOUND . '</td></tr>';
 337  $rows=0;
 338  //

 339  // loop here for each row reported

 340  while ($sales = tep_db_fetch_array($sales_query)) {
 341      $rows++;
 342      if ($rows>1 && $sales['row_year']<>$last_row_year) {  // emit annual footer
 343  ?>
 344  <tr class="dataTableHeadingRow">
 345  <td class="dataTableHeadingContent" align="left">
 346  <?php 
 347      if ($sales['row_year']==date("Y")) mirror_out(TABLE_FOOTER_YTD); 
 348      else 
 349          if ($sel_month==0) mirror_out(TABLE_FOOTER_YEAR);
 350          else
 351              mirror_out(strtoupper(substr($sales['row_month'],0,3)));
 352  ?>
 353  </td>
 354  <td class="dataTableHeadingContent" align="left">
 355  <?php mirror_out($last_row_year); ?></td>
 356  <td class="dataTableHeadingContent" width='70' align="right">
 357  <?php mirror_out(number_format($footer_gross,2)); ?>
 358  </td>
 359  <td class="dataTableHeadingContent" width='70' align="right">
 360  <?php mirror_out(number_format($footer_sales,2)); ?>
 361  </td>
 362  <td class="dataTableHeadingContent" width='70' align="right">
 363  <?php mirror_out(number_format($footer_sales_nontaxed,2)); ?>
 364  </td>
 365  <td class="dataTableHeadingContent" width='70' align="right">
 366  <?php mirror_out(number_format($footer_sales_taxed,2)); ?>
 367  </td>
 368  <td class="dataTableHeadingContent" width='70' align="right">
 369  <?php mirror_out(number_format($footer_tax_coll,2)); ?>
 370  </td>
 371  <td class="dataTableHeadingContent" width='70' align="right">
 372  <?php mirror_out(number_format($footer_shiphndl,2)); ?>
 373  </td>
 374  <?php if ($loworder) { ?>
 375  <td class="dataTableHeadingContent" width='70' align="right">
 376  <?php mirror_out(number_format($footer_loworder,2)); ?>
 377  </td>
 378  <?php }; ?>
 379  <?php if ($extra_class) { ?>
 380  <td class="dataTableHeadingContent" width='70' align="right">
 381  <?php mirror_out(number_format($footer_other,2)); ?>
 382  </td>
 383  <?php }; 
 384  // clear footer totals

 385  $footer_gross = 0;
 386  $footer_sales = 0;
 387  $footer_sales_nontaxed = 0;
 388  $footer_sales_taxed = 0;
 389  $footer_tax_coll = 0;
 390  $footer_shiphndl = 0;
 391  $footer_loworder = 0;
 392  $footer_other = 0;
 393  // new line for CSV

 394  $csv_accum .= "\n";
 395  ?>
 396  </tr>
 397  <?php };
 398  //

 399  // determine net sales for row

 400  $net_sales_query_raw = "select sum(round(ot.value,2)) net_sales from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) where ";
 401  if ($status<>'') $net_sales_query_raw .= "o.orders_status ='" . $status . "' and ";
 402  $net_sales_query_raw .= "ot.class = " . $class_val_subtotal . " and month(o.date_purchased)= '" . $sales['i_month'] . "' and year(o.date_purchased)= '" . $sales['row_year'] . "'";
 403  if ($sel_month<>0) $net_sales_query_raw .= " and dayofmonth(o.date_purchased) = '" . $sales['row_day'] . "'";
 404  $net_sales_query = tep_db_query($net_sales_query_raw);
 405  $net_sales_this_row = 0;
 406  if (tep_db_num_rows($net_sales_query)>0)    
 407      $net_sales_this_row = tep_db_fetch_array($net_sales_query);
 408  //

 409  // determine tax collected for row

 410  $tax_coll_query_raw = "select sum(round(ot.value,2)) tax_coll from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) where ";
 411  if ($status<>'') $tax_coll_query_raw .= "o.orders_status ='" . $status . "' and ";
 412  $tax_coll_query_raw .= "ot.class = " . $class_val_tax . " and month(o.date_purchased)= '" . $sales['i_month'] . "' and year(o.date_purchased)= '" . $sales['row_year'] . "'";
 413  if ($sel_month<>0) $tax_coll_query_raw .= " and dayofmonth(o.date_purchased) = '" . $sales['row_day'] . "'";
 414  $tax_coll_query = tep_db_query($tax_coll_query_raw);
 415  $tax_this_row = 0;
 416  if (tep_db_num_rows($tax_coll_query)>0)    
 417      $tax_this_row = tep_db_fetch_array($tax_coll_query);
 418  //

 419  // shipping and handling charges for row

 420  $shiphndl_query_raw = "select sum(round(ot.value,2)) shiphndl from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) where ";
 421  if ($status<>'') $shiphndl_query_raw .= "o.orders_status ='" . $status . "' and ";
 422  $shiphndl_query_raw .= "ot.class = " . $class_val_shiphndl . " and month(o.date_purchased)= '" . $sales['i_month'] . "' and year(o.date_purchased)= '" . $sales['row_year'] . "'";
 423  if ($sel_month<>0) $shiphndl_query_raw .= " and dayofmonth(o.date_purchased) = '" . $sales['row_day'] . "'";
 424  $shiphndl_query = tep_db_query($shiphndl_query_raw);
 425  $shiphndl_this_row = 0;
 426  if (tep_db_num_rows($shiphndl_query)>0)    
 427      $shiphndl_this_row = tep_db_fetch_array($shiphndl_query);
 428  //

 429  // low order fees for row

 430  $loworder_this_row = 0;
 431  if ($loworder) {
 432      $loworder_query_raw = "select sum(round(ot.value,2)) loworder from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) where ";
 433      if ($status<>'') $loworder_query_raw .= "o.orders_status ='" . $status . "' and ";
 434      $loworder_query_raw .= "ot.class = " . $class_val_loworder . " and month(o.date_purchased)= '" . $sales['i_month'] . "' and year(o.date_purchased)= '" . $sales['row_year'] . "'";
 435      if ($sel_month<>0) $loworder_query_raw .= " and dayofmonth(o.date_purchased) = '" . $sales['row_day'] . "'";
 436      $loworder_query = tep_db_query($loworder_query_raw);
 437      if (tep_db_num_rows($loworder_query)>0)    
 438      $loworder_this_row = tep_db_fetch_array($loworder_query);
 439  };
 440  //

 441  // additional column if extra class value in orders_total table

 442  $other_this_row = 0;
 443  if ($extra_class) { 
 444      $other_query_raw = "select sum(round(ot.value,2)) other from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) where ";
 445      if ($status<>'') $other_query_raw .= "o.orders_status ='" . $status . "' and ";
 446      $other_query_raw .= "ot.class <> " . $class_val_subtotal . " and class <> " . $class_val_tax . " and class <> " . $class_val_shiphndl . " and class <> " . $class_val_loworder . " and class <> " . $class_val_total . " and month(o.date_purchased)= '" . $sales['i_month'] . "' and year(o.date_purchased)= '" . $sales['row_year'] . "'";
 447      if ($sel_month<>0) $other_query_raw .= " and dayofmonth(o.date_purchased) = '" . $sales['row_day'] . "'";
 448      $other_query = tep_db_query($other_query_raw);
 449      if (tep_db_num_rows($other_query)>0)    
 450      $other_this_row = tep_db_fetch_array($other_query);
 451      };
 452  //

 453  // sum of order subtotals taxed

 454      $taxed_query_raw = "select sum(round(t0.value,2)) taxed_sales, monthname(o.date_purchased) row_month, month(o.date_purchased) i_month from " . TABLE_ORDERS_TOTAL . " t0, " .  TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " t1 on (o.orders_id = t1.orders_id) where t0.orders_id=t1.orders_id ";
 455      if ($status<>'') $taxed_query_raw .= " and o.orders_status ='" . $status . "'";
 456      $taxed_query_raw .= " and t0.class = " . $class_val_subtotal . " and t1.class = " . $class_val_tax  . " and t1.value>0 and month(o.date_purchased)= '" . $sales['i_month'] . "' and year(o.date_purchased)= '" . $sales['row_year'] . "'";
 457      if ($sel_month<>0) {
 458          $taxed_query_raw .= " and dayofmonth(o.date_purchased) = '" . $sales['row_day'] . "' group by o.date_purchased";
 459      } else {
 460          $taxed_query_raw .= " group by month(o.date_purchased)";
 461      };
 462      $taxed_query = tep_db_query($taxed_query_raw);
 463      $taxed_this_row = tep_db_fetch_array($taxed_query);
 464  //

 465  // sum of order subtotals not taxed

 466      $nontaxed_this_row_value = $net_sales_this_row['net_sales'] - $taxed_this_row['taxed_sales'];
 467  //

 468  // accumulate row results in footer

 469      $footer_gross += $sales['gross_sales'];
 470      $footer_sales += $net_sales_this_row['net_sales'];
 471      $footer_sales_nontaxed += $nontaxed_this_row_value;
 472      $footer_sales_taxed += $taxed_this_row['taxed_sales'];
 473      $footer_tax_coll += $tax_this_row['tax_coll'];
 474      $footer_shiphndl += $shiphndl_this_row['shiphndl'];
 475      $footer_loworder += $loworder_this_row['loworder'];
 476      if ($extra_class) $footer_other += $other_this_row['other'];
 477  ?>
 478  <tr class="dataTableRow">
 479  <td class="dataTableContent" align="left">
 480  <?php  // live link to report monthly detail
 481  if ($sel_month == 0    && !$print) {
 482      echo "<a href='" . $_SERVER['PHP_SELF'] . "?" . $_SERVER['QUERY_STRING'] . "&month=" . $sales['i_month'] . "&year=" . $sales['row_year'] . "' title='" . TEXT_BUTTON_REPORT_GET_DETAIL . "'>";
 483      }
 484  mirror_out(substr($sales['row_month'],0,3)); 
 485  if ($sel_month == 0 && !$print) echo '</a>';
 486  ?>
 487  </td>
 488  <td class="dataTableContent" align="left">
 489  <?php 
 490  if ($sel_month==0) mirror_out($sales['row_year']);
 491  else mirror_out($sales['row_day']);
 492  $last_row_year = $sales['row_year']; // save this row's year to check for annual footer

 493  ?>
 494  </td>
 495  <td class="dataTableContent" width='70' align="right"><?php mirror_out(number_format($sales['gross_sales'],2)); ?></td>
 496  <td class="dataTableContent" width='70' align="right"><?php mirror_out(number_format($net_sales_this_row['net_sales'],2)); ?></td>
 497  <td class="dataTableContent" width='70' align="right"><?php mirror_out(number_format($nontaxed_this_row_value,2)); ?></td>
 498  <td class="dataTableContent" width='70' align="right"><?php mirror_out(number_format($taxed_this_row['taxed_sales'],2)); ?></td>
 499  <td class="dataTableContent" width='70' align="right">
 500  <?php 
 501      // make this a link to the detail popup if nonzero

 502      if (!$print && ($tax_this_row['tax_coll']>0)) {
 503          echo "<a href=\"#\" onClick=\"window.open('" . $_SERVER['PHP_SELF'] . "?&show=ot_tax&year=" . $sales['row_year'] . "&month=" . $sales['i_month'];
 504          if ($sel_month<>0) echo "&day=" . $sales['row_day'];
 505          if ($status<>'') echo "&status=" . $status;
 506          echo "','detail',config='height=200,width=400,scrollbars=1, resizable=1')\" title=\"Show detail\">";
 507      };
 508      mirror_out(number_format($tax_this_row['tax_coll'],2)); 
 509      if (!$print && $tax_this_row['tax_coll']>0) echo "</a>";
 510  ?></td>
 511  <td class="dataTableContent" width='70' align="right"><?php mirror_out(number_format($shiphndl_this_row['shiphndl'],2)); ?></td>
 512  <?php if ($loworder) { ?>
 513  <td class="dataTableContent" width='70' align="right"><?php mirror_out(number_format($loworder_this_row['loworder'],2)); ?></td>
 514  <?php }; ?>
 515  <?php
 516  if ($extra_class) { ?>
 517  <td class="dataTableContent" width='70' align="right"><?php mirror_out(number_format($other_this_row['other'],2)); ?></td>
 518  <?php }; ?>
 519  </tr>
 520  <?php 
 521  // new line for CSV

 522  $csv_accum .= "\n";
 523  //

 524  //

 525  // output footer below ending row

 526  if ($rows==$num_rows){
 527  ?>
 528  <tr class="dataTableHeadingRow">
 529  <td class="dataTableHeadingContent" align="left">
 530  <?php 
 531      if ($sel_month<>0) 
 532      mirror_out(strtoupper(substr($sales['row_month'],0,3)));
 533      else
 534      {if ($sales['row_year']==date("Y")) mirror_out(TABLE_FOOTER_YTD); 
 535       else mirror_out(TABLE_FOOTER_YEAR);};
 536  ?>
 537  </td>
 538  <td class="dataTableHeadingContent" align="left">
 539  <?php mirror_out($sales['row_year']); ?></td>
 540  <td class="dataTableHeadingContent" width='70' align="right">
 541  <?php mirror_out(number_format($footer_gross,2)); ?>
 542  </td>
 543  <td class="dataTableHeadingContent" width='70' align="right">
 544  <?php mirror_out(number_format($footer_sales,2)); ?>
 545  </td>
 546  <td class="dataTableHeadingContent" width='70' align="right">
 547  <?php mirror_out(number_format($footer_sales_nontaxed,2)); ?>
 548  </td>
 549  <td class="dataTableHeadingContent" width='70' align="right">
 550  <?php mirror_out(number_format($footer_sales_taxed,2)); ?>
 551  </td>
 552  <td class="dataTableHeadingContent" width='70' align="right">
 553  <?php mirror_out(number_format($footer_tax_coll,2)); ?>
 554  </td>
 555  <td class="dataTableHeadingContent" width='70' align="right">
 556  <?php mirror_out(number_format($footer_shiphndl,2)); ?>
 557  </td>
 558  <?php if ($loworder) { ?>
 559  <td class="dataTableHeadingContent" width='70' align="right">
 560  <?php mirror_out(number_format($footer_loworder,2)); ?>
 561  </td>
 562  <?php }; ?>
 563  <?php if ($extra_class) { ?>
 564  <td class="dataTableHeadingContent" width='70' align="right">
 565  <?php mirror_out(number_format($footer_other,2)); ?>
 566  </td>
 567  <?php }; 
 568  // clear footer totals

 569  $footer_gross = 0;
 570  $footer_sales = 0;
 571  $footer_sales_nontaxed = 0;
 572  $footer_sales_taxed = 0;
 573  $footer_tax_coll = 0;
 574  $footer_shiphndl = 0;
 575  $footer_loworder = 0;
 576  $footer_other = 0;
 577  // new line for CSV

 578  $csv_accum .= "\n";
 579  ?>
 580  </tr>
 581  <?php };
 582    };
 583  // done with report body

 584  //

 585  // button for Save CSV

 586  if ($num_rows>0 && !$print) {
 587  ?>
 588  <tr>
 589                  <td class="smallText" colspan="4"><form action="<?php echo $_SERVER['PHP_SELF']; ?>" method=post><input type='hidden' name='csv' value='<?php echo $csv_accum; ?>'><input type='hidden' name='saveas' value='sales_report_<?php
 590                      //suggested file name for csv, include year and month if detail

 591                      //include status if selected, end with date and time of report

 592                  if ($sel_month<10) $sel_month_2 = "0" . $sel_month; 
 593                  else $sel_month_2 = $sel_month;
 594                  if ($sel_month<>0) echo $sel_year . $sel_month_2 . "_";
 595                  if (strpos($orders_status_text,' ')) echo substr($orders_status_text, 0, strpos($orders_status_text,' ')) . "_" . date("YmdHi"); else echo $orders_status_text . "_" . date("YmdHi"); 
 596                  ?>'><input type="submit" value="<?php echo TEXT_BUTTON_REPORT_SAVE ;?>"></form>
 597                  </td>
 598  </tr>
 599  <?php }; // end button for Save CSV ?>

 600              </table></td>
 601            </tr>
 602          </table></td>
 603        </tr>
 604      </table></td>
 605  <!-- body_text_eof //-->
 606    </tr>
 607  </table>
 608  <!-- body_eof //-->
 609  
 610  <!-- footer //-->
 611  <?php  // suppress footer for printer-friendly version
 612      if(!$print) require (DIR_WS_INCLUDES . 'footer.php'); ?>
 613  <!-- footer_eof //-->
 614  </body>
 615  </html>
 616  <?php require (DIR_WS_INCLUDES . 'application_bottom.php'); 
 617  
 618  function mirror_out ($field) {
 619      global $csv_accum;
 620      echo $field;
 621      $field = strip_tags($field);
 622      $field = ereg_replace (",","",$field);
 623      if ($csv_accum=='') $csv_accum=$field; 
 624      else 
 625      {if (strrpos($csv_accum,chr(10)) == (strlen($csv_accum)-1)) $csv_accum .= $field;
 626          else $csv_accum .= "," . $field; };
 627      return;
 628  };
 629  
 630  ?>


Generated: Fri Jan 1 13:43:16 2010 Cross-referenced by PHPXref 0.7