| [ Index ] |
PHP Cross Reference of osCMax 2.0.4 |
[Summary view] [Print] [Text view]
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 . ":" . " " . $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 ?>
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Fri Jan 1 13:43:16 2010 | Cross-referenced by PHPXref 0.7 |