<?php
defined( '_VALID_MOS' ) or die( 'Direct Access to this location is not allowed.' ); 
/**
* @version $Id: reportbasic.index.php,v 1.2 2005/01/27 19:34:03 soeren_nb Exp $
* @package mambo-phpShop
* @subpackage HTML
* Contains code from PHPShop(tm):
* 	@copyright (C) 2000 - 2004 Edikon Corporation (www.edikon.com)
*	Community: www.phpshop.org, forums.phpshop.org
* Conversion to Mambo and the rest:
* 	@copyright (C) 2004-2005 Soeren Eberhardt
*
* @license http://www.gnu.org/copyleft/gpl.html GNU/GPL
* mambo-phpShop is Free Software.
* mambo-phpShop comes with absolute no warranty.
*
* www.mambo-phpshop.net
*/
mm_showMyFileName( __FILE__ );

?>
<!-- BEGIN body -->
&nbsp;&nbsp;&nbsp;<img src="<?php echo IMAGEURL ?>ps_image/report.gif" border="0" />&nbsp;&nbsp;&nbsp;
<span class="sectionname"><? echo $PHPSHOP_LANG->_PHPSHOP_REPORTBASIC_MOD ?></span><br /><br />
    <form action="<?php echo $PHP_SELF ?>" method="POST">
    <input type="hidden" name="page" value="reportbasic.index">
    <input type="hidden" name="option" value="com_phpshop">
      <table class="box" width="100%" border="0" cellspacing="0"
      cellpadding="1">
        <tr>
          <td>
            <table class="listlabels" width="100%" border="0" cellspacing="1" cellpadding="0">
              <tr>
                <td><? echo $PHPSHOP_LANG->_PHPSHOP_VIEW ?></td>

                <td><input type="checkbox" name="show_products" value="show_products"<?php
                if (isset($show_products)) { echo ' checked="checked"'; } ?> />
                <? echo $PHPSHOP_LANG->_PHPSHOP_RB_INDIVIDUAL ?> &nbsp; &nbsp; 
                </td>
              </tr>

              <tr>
                <td colspan="2">
                  <hr noshade="noshade" size="2" color="#000000" />
                </td>
              </tr>
              <tr>
                <td><?php echo $PHPSHOP_LANG->_PHPSHOP_RB_INTERVAL_TITLE; ?></td>

                <td><input type="radio" name="interval" value="byMonth" checked />
                <?php echo $PHPSHOP_LANG->_PHPSHOP_RB_INTERVAL_MONTHLY_TITLE ?> &nbsp; &nbsp; <input type="radio"
                name="interval" value="byWeek" /> <?php echo $PHPSHOP_LANG->_PHPSHOP_RB_INTERVAL_WEEKLY_TITLE; ?> &nbsp; &nbsp;
                <input type="radio" name="interval" value="byDay" />
                <?php echo $PHPSHOP_LANG->_PHPSHOP_RB_INTERVAL_DAILY_TITLE; ?></td>
              </tr>

              <tr>
                <td colspan="2">
                  <hr noshade="noshade" size="2" color="#000000" />
                </td>
              </tr>

              <tr>
                <td><? echo $PHPSHOP_LANG->_PHPSHOP_SHOW ?></td>

                <td>
                <input type="submit" class="button" name="thisMonth" value="<?php echo $PHPSHOP_LANG->_PHPSHOP_RB_THISMONTH_BUTTON; ?>" /> &nbsp; 
                <input type="submit" class="button" name="lastMonth" value="<?php echo $PHPSHOP_LANG->_PHPSHOP_RB_LASTMONTH_BUTTON; ?>" /> &nbsp; 
                <input type="submit" class="button" name="last60" value="<?php echo $PHPSHOP_LANG->_PHPSHOP_RB_LAST60_BUTTON; ?>" /> &nbsp;
                <input type="submit" class="button" name="last90" value="<?php echo $PHPSHOP_LANG->_PHPSHOP_RB_LAST90_BUTTON; ?>" />
                </td>
              </tr>

              <tr>
                <td colspan="2">
                  <hr noshade="noshade" size="2" color="#000000" />
                </td>
              </tr>

              <tr valign="top">
                <td width="100"><?php echo $PHPSHOP_LANG->_PHPSHOP_RB_START_DATE_TITLE; ?></td>

                <td><?php
                $nh_report->make_date_popups("s",date("Ymd", mktime (0,0,0,date("m"),1,date("Y"))) );
                ?></td>
              </tr>

              <tr>
                <td width="100"><?php echo $PHPSHOP_LANG->_PHPSHOP_RB_END_DATE_TITLE; ?></td>

                <td><?php $nh_report->make_date_popups("e"); ?></td>
              </tr>

              <tr>
                <td>&nbsp;</td>

                <td><input type="submit" class="button" name="sbmt" value="<? echo $PHPSHOP_LANG->_PHPSHOP_RB_SHOW_SEL_RANGE ?>" /> </td>
              </tr>
            </table>
          </td>
        </tr>
      </table>
    </form>
<!-- begin output of report -->
<?php 
 /* assemble start date */
 if (isset($thisMonth)) {
   $start_date = mktime(0,0,0,date("n"),1,date("Y"));
   $end_date = mktime(23,59,59,date("n")+1,0,date("Y"));
 }
 else if (isset($lastMonth)) {
   $start_date = mktime(0,0,0,date("n")-1,1,date("Y"));
   $end_date = mktime(23,59,59,date("n"),0,date("Y"));
 }
 else if (isset($last60)) {
   $start_date = mktime(0,0,0,date("n"),date("j")-60,date("Y"));
   $end_date = mktime(23,59,59,date("n"),date("j"),date("Y"));
 }
 else if(isset ($last90)) {
   $start_date = mktime(0,0,0,date("n"),date("j")-90,date("Y"));
   $end_date = mktime(0,0,0,date("n"),date("j"),date("Y"));
 }
 elseif (isset($sbmt)) {
   /* start and end dates should have been given, assign accordingly */
   $start_max_day = date("j",mktime(0,0,0,$smonth+1,0,$syear));
   if (! (intval($sday) <= $start_max_day)) {
     $sday = $start_max_day;
   }
   $start_date = mktime(0,0,0,intval($smonth),intval($sday),$syear);

   $end_max_day = date("j",mktime(0,0,0,intval($smonth)+1,0,$syear));
   if (! (intval($eday) <= $end_max_day)) {
     $eday = $end_max_day;
   }
   $end_date = mktime(23,59,59,intval($emonth),intval($eday),$eyear);
 }
 else {
 /* nothing was sent to the page, so create default inputs */
   $start_date = mktime(0,0,0,date("n"),1,date("Y"));
   $end_date = mktime(23,59,59,date("n")+1,0,date("Y"));
   $interval = "byMonth";
 }

 /* get the interval and set the date line for the query */
 switch ($interval) {
    case 'byMonth':
     $query_date_line = "FROM_UNIXTIME(cdate, '%M, %Y') as order_date, ";
     $query_group_line = "GROUP BY order_date";
     break;
   case 'byWeek':
     $query_date_line .= "WEEK(FROM_UNIXTIME(cdate, '%Y-%m-%d')) as week_number, ";
     $query_date_line .= "FROM_UNIXTIME(cdate, '%M %d, %Y') as order_date, ";
     $query_group_line = "GROUP BY week_number";
     break;
   case 'byDay':
   /* query for days */
     $query_date_line = "FROM_UNIXTIME(cdate, '%M %d, %Y') as order_date, ";
     $query_group_line = "GROUP BY order_date";
     break;
   default:
     $query_date_line = '';
     $query_group_line = '';
    break;
  }
  /* better way of setting up query */
  $q  = "SELECT ";
  $r  = $q;
  $u  = $q;

  $query_between_line = "WHERE cdate BETWEEN '" . $start_date . "' AND '" . $end_date . "' ";
  if ($query_date_line) {
    $q .= $query_date_line;
  }
  $q .= "FROM_UNIXTIME(cdate, '%Y%m%d') as date_num, ";
  $q .= "COUNT(order_id) as number_of_orders, ";
  $q .= "SUM(order_subtotal) as revenue ";
  $q .= "FROM #__pshop_orders ";
  $q .= $query_between_line;
  if ($query_group_line) {
    $q .= $query_group_line;
  }
  $q .= " ORDER BY date_num ASC";

  /** setup items sold query */
  if ($query_date_line) {
    $r .= $query_date_line;
  }
  $r .= "FROM_UNIXTIME(cdate, '%Y%m%d') as date_num, ";
  $r .= "SUM(product_quantity) as items_sold ";
  $r .= "FROM #__pshop_order_item ";
  $r .= $query_between_line;
  if ($query_group_line) {
    $r .= $query_group_line;
  }
  $r .= " ORDER BY date_num ASC";

// added for v0.2 PRODUCT LISTING QUERY!
if (isset($show_products)) {
/* setup end of product listing query */
  $u .= "product_name, product_sku, ";
  if ($query_date_line) {
    $u .= str_replace ("cdate", "#__pshop_order_item.cdate", $query_date_line);
  }
  $u .= "FROM_UNIXTIME(#__pshop_order_item.cdate, '%Y%m%d') as date_num, ";
  $u .= "SUM(product_quantity) as items_sold ";
  $u .= "FROM #__pshop_order_item, #__pshop_orders, #__pshop_product ";
  $u .= str_replace ("cdate", "#__pshop_order_item.cdate", $query_between_line);
  $u .= "AND #__pshop_orders.order_id=#__pshop_order_item.order_id ";
  $u .= "AND #__pshop_order_item.product_id=#__pshop_product.product_id ";
  $u .= "GROUP BY product_sku, product_name, order_date ";
  $u .= " ORDER BY date_num, product_name ASC";
  $dbpl = new ps_DB;
  $dbpl->query($u);
}


/* setup the db and query */
  $db = new ps_DB;
  $dbis = new ps_DB;
  
  $db->query($q);
  $dbis->query($r);
 ?>
    <h4><? echo $PHPSHOP_LANG->_PHPSHOP_RB_REPORT_FOR ?><b><?php echo date("M j, Y", $start_date); ?></b> --&gt; <b><?php
    echo date("M j, Y", $end_date); ?></b></h4>

    <table class="box" width="100%" border="0" cellspacing="0" cellpadding="1">
      <tr>
        <td>
          <table bgcolor="#cccccc" width="100%" border="0" cellspacing="0"
          cellpadding="1">
            <tr>
              <td><b><? echo $PHPSHOP_LANG->_PHPSHOP_RB_DATE ?></b></td>

              <td><b><? echo $PHPSHOP_LANG->_PHPSHOP_RB_ORDERS ?></b></td>

              <td><b><? echo $PHPSHOP_LANG->_PHPSHOP_RB_TOTAL_ITEMS ?></b></td>

              <td><b><? echo $PHPSHOP_LANG->_PHPSHOP_RB_REVENUE ?></b></td>
            </tr>
<?php
  if (isset($show_products)) {
    $dbpl->next_record();
  }
  while ($db->next_record()) {
    $dbis->next_record();
    
    if ($i++ % 2) {
      $bgcolor=SEARCH_COLOR_1;
    }
    else {
      $bgcolor=SEARCH_COLOR_2;
    }
        ?> 
            <tr bgcolor=<?php echo $bgcolor ?>> 
              <td><?php $db->p("order_date"); ?></td>
    <td><?php $db->p("number_of_orders"); ?></td>
    <td><?php $dbis->p("items_sold"); ?></td>
    <td><?php 
      $db->p("revenue");
    ?>&nbsp;</td>
  </tr>
  <?php
    // BEGIN product listing
    if (isset($show_products)) {
    ?>
    <tr bgcolor="#ffffff">
      <td colspan=5 align="left"><b><? echo $PHPSHOP_LANG->_PHPSHOP_RB_PRODLIST ?></b></td>
    </tr>
    <tr bgcolor="#ffffff">
      <td>&nbsp;</td>
      <td colspan=2 align="left"><b><? echo $PHPSHOP_LANG->_PHPSHOP_PRODUCT_NAME_TITLE ?></b></td>
      <td colspan=2 align="left"><b><? echo $PHPSHOP_LANG->_PHPSHOP_CART_QUANTITY ?></b></td>
    </tr>
    <?php
      while ($dbpl->f("order_date") == $db->f("order_date")) {
        echo "<tr bgcolor=\"#ffffff\">\n";
        echo "<td>&nbsp;</td>\n";
        echo '<td colspan=2 align="left">' . $dbpl->f("product_name") . " (" . $dbpl->f("product_sku") . ")</td>\n";
        echo '<td colspan=2 align="left">' . $dbpl->f("items_sold") . "</td>\n";
        echo "</tr>\n";
        $dbpl->next_record();
      }
    ?>
    <tr><td colspan=5><hr width="85%"></td></tr>
    <?php
    }
    // END product listing

  } ?>
          </table>
        </td>
      </tr>
    </table>
<!-- end output of report -->
 
                     <!-- END body -->
