<?php

/*

  osCommRes, Services Online
  http://www.oscommres.com

  Copyright (c) 2005 osCommRes

  Released under the GNU General Public License
  Freeway eCommerce from ZacWare
http://www.zac-ware.com/freeway

Copyright 2007 ZacWare Pty. Ltd 
*/
// Set flag that this is a parent file
	define( '_FEXEC', 1 );
  require('includes/application_top.php');
  tep_get_last_access_file();define(BOX_WIDTH1,'125');

	// get initial parameters
	if (isset($HTTP_GET_VARS["return"]) && isset($HTTP_SESSION_VARS["rep_params"])){
		$input_params=&$HTTP_SESSION_VARS["rep_params"];
	} else {
		$input_params=&$HTTP_POST_VARS;
		if (isset($input_params["post_action"])){
			tep_set_session_value("rep_params",$HTTP_POST_VARS);
		} else {
			tep_set_session_value("rep_params",array());
		}
	}
	
	$start_date = (isset($input_params['txt_start_date'])?tep_convert_date_raw($input_params['txt_start_date']):'');
	$end_date = (isset($input_params['txt_end_date'])?tep_convert_date_raw($input_params['txt_end_date']):getServerDate());
	$subscription_id = (isset($input_params['sel_subscription'])?$input_params['sel_subscription']:0);
	$last_prod_id = (isset($input_params['last_id'])?$input_params['last_id']:'');
	$layout = (isset($input_params['layout'])?$input_params['layout']:'normal');
	$totals_only = (isset($input_params['chk_totals_only'])?true:false);

if($start_date=="") {
	$res = tep_db_query("SELECT '".getServerDate()."' - INTERVAL 1 MONTH");
	$row = mysql_fetch_array($res);
	$start_date = $row[0]; 
}
?>
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
<script language="javascript" src="includes/menu.js"></script>
<script language="javascript" src="includes/general.js"></script>
<script language="JavaScript" src="includes/date-picker.js"></script>
<script language="JavaScript">
function doSubmit() {
	strStart = document.f.txt_start_date.value.split("-");
	startDate = new Date(strStart[0], strStart[1], strStart[2]);

	strEnd = document.f.txt_end_date.value.split("-");
	endDate = new Date(strEnd[0], strEnd[1], strEnd[2]);

	if(startDate.getTime()>endDate.getTime()) {
		alert("Start date must be before end date.");
		return;
	}
	document.f.submit();
}
</script>
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF">

<div id="spiffycalendar" class="text"></div>
	<!-- header //-->
	<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
	<!-- header_eof //-->
	<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2">
<tr> 
	<!-- body_text //-->	  
	<td width=100% align=left valign="top">
	<table border="0" width="100%" cellspacing="0" cellpadding="2">
	<td class="pageHeading"><?php echo HEADING_SUBSCRIPTIONS_SALES;?></td>	
          <tr><TD>

<!-- --->
<FORM action="<?php echo FILENAME_SUBSCRIPTIONS_SALES;?>" id="f" name="f" method="post">
  <input type="hidden" name="page" value="1">
  <input type="hidden" name="post_action" value="1">

	<table cellspacing="3" cellpadding="2" class="searchArea" width="100%" border="0">
	<tbody>
		<tr>
			<td nowrap>
				<?php $_array=array('d','m','Y');  $replace_array=array('DD','MM','YYYY'); 	$date_format=str_replace($_array,$replace_array,EVENTS_DATE_FORMAT);?>
				<?php echo REPORT_START_DATE . "&nbsp;&nbsp;" . tep_draw_input_field('txt_start_date',format_date($start_date),' size="10"');?>
				<a href="javascript:show_calendar('f.txt_start_date',null,null,'<?php echo $date_format;?>');"
				onmouseover="window.status='Date Picker';return true;"
				onmouseout="window.status='';return true;"><img border="none" src="images/icon_calendar.gif"/>  
				</a>&nbsp;&nbsp;
				<?php echo REPORT_END_DATE . "&nbsp;&nbsp;" . tep_draw_input_field('txt_end_date',format_date($end_date),' size="10"');?>
				<a href="javascript:show_calendar('f.txt_end_date',null,null,'<?php echo $date_format;?>');"
				onmouseover="window.status='Date Picker';return true;"
				onmouseout="window.status='';return true;"><img border="none" src="images/icon_calendar.gif"/>  
				</a>
			</td>
			<td align="right">
				<a href="javascript:doSubmit()"><?php echo tep_image_submit('button_report_search.gif', IMAGE_SEARCH_RESERVATION); ?></a>
			</td>
		</tr>
		<tr class="main">      
			<td><?php echo TEXT_SUBSCRIPTION;?>
				<select name="sel_subscription">
					<OPTION value="0"><?php echo REPORT_ALL_SUBSCRIPTIONS;?> </OPTION>
					<?php 
					$sql = "select su.subscription_id as subscription_id, subscription_name, subscription_costs from ". TABLE_SUBSCRIPTIONS . " su,". TABLE_SUBSCRIPTIONS_DESCRIPTION . " sud 
					where su.subscription_id = sud.subscription_id and sud.language_id='" . $languages_id . "' order by  subscription_name";
					$res = tep_db_query($sql);
					while($row = mysql_fetch_assoc($res)) {?>
						<OPTION value="<?php echo $row["subscription_id"]?>" <?php echo $row["subscription_id"]==abs($subscription_id)?"selected":"" ?> >
						<?php echo $row["subscription_id"]." ".$row["subscription_name"] ?>
						</OPTION>
					<?php  
					}
					?>
				</select>
			</td>
			<td align="left">
				<label><input type="checkbox" name="chk_totals_only" <?php echo ($totals_only==true?"checked":"") ?>><?php echo REPORT_SUMMARY_ONLY?> </label>
			</td>
			<td>&nbsp;</td>
		</tr>
  </tbody>
</table>

&nbsp;
<!-- table -->
<?php
$date_clause.=" and ((date_format(o.date_purchased,'%Y-%m-%d')>='" . $start_date . "'" . " and date_format(o.date_purchased,'%Y-%m-%d')<='" . $end_date . "') OR " . " (date_format(date_add(o.date_purchased,interval su.subscription_period day),'%Y-%m-%d')>='" . $start_date . "'" .  "and date_format(date_add(o.date_purchased,interval su.subscription_period day),'%Y-%m-%d')<='" . $date_end . "'))";
if($subscription_id>0){
	$subscription_string= " and products_id= " . $subscription_id . " and op.products_type='S'";
}else {
	$subscription_string= " and op.products_type='S' ";
}
//subscriptons Query

$sql_subscriptions = "select distinct products_id, 'S' as product_type from subscriptions su,orders_products op, orders o 
	  where op.orders_id = o.orders_id and op.products_id=su.subscription_id " . $date_clause . $subscription_string;

$subscriptions_ids = array();
$all_subscriptions = array();
$prodno_on_page = 10;
$pcount = 0;
$add = true;
//die($sql_products);
$res = tep_db_query($sql_subscriptions);
while($row = mysql_fetch_assoc($res)) {
	$all_subscriptions[$row['products_id']] = $row['product_type'];
}
//ksort($products_ids);
ksort($all_subscriptions);

foreach($all_subscriptions as $key=>$val) {
	if(($key>=$last_prod_id)&&($add)) {  //visible subscriptions
		$subscriptions_ids[$key] = $val;
		$pcount++;
	}
	if($pcount == $prodno_on_page) { 
		break;
	}

}


//echo "<pre>";
//print_r($all_products);
//print_r($products_ids);
//echo "</pre>";
  
render_table($subscriptions_ids, $all_subscriptions);
function render_table($subscriptions_ids, $all_subscriptions) {
  global $page, $last_prod_id, $date_clause, $totals_only;
?>
  <table border="0">
	<tr>		
<?php   
// render page navigation
  $pages_last_ids = array();
  $pgcount = 1;
  $i=0;
  $nrrows = count($all_subscriptions);
  foreach($all_subscriptions as $prod_id=>$prod_type) {
	if($i % 10 == 0) {
		$pages_last_ids[$pgcount] = $prod_id;
?>
	<td><a href="javascript:gotoPage(<?php echo $pgcount ?>)">
		<?php echo ($page == $pgcount)?"<b>":"";echo $pgcount;echo ($page == $pgcount)?"</b>":"";?>
	</a></td>
<?php
		$pgcount++;  
	}
  	$i++;
  }

?>
 	</tr>
  </table>
<?php  
  $i=1;
  $pcount = 0;
  //print_r($products_ids);
?>
<table border="0" width="100%" cellspacing="1" cellpadding="1">
<?php
  foreach($subscriptions_ids as $subscription_id=>$subscription_type) {
  	if($subscription_type=='S') {
    	$sql = "select products_name, products_id from orders_products op where op.products_id=".$subscription_id." limit 1";
  	} 
    $res = tep_db_query($sql);
    $row = mysql_fetch_assoc($res);
    $pcount++;
?>
<tr class='dataTableHeadingRow'>
<TD colspan="9" align="left" class='dataTableHeadingContent'>
	<?php echo $subscription_id." ".$row['products_name'] ?>
</TD></tr>
<tr class='dataTableHeadingTitleRow'>
  <td class='dataTableHeadingTitleContent'>#</td>
  <TD class='dataTableHeadingTitleContent'>Client</TD>
  <TD class='dataTableHeadingTitleContent'>Sale Date</TD>
  <td class='dataTableHeadingTitleContent'>Unit Price</td>
  <TD class='dataTableHeadingTitleContent'>#Sold</TD>
  <TD class='dataTableHeadingTitleContent'>Total</TD>
  <TD class='dataTableHeadingTitleContent'>Credit</TD>
  <TD class='dataTableHeadingTitleContent'>Cash</TD>
  <TD class='dataTableHeadingTitleContent'>Cheque/MO</TD>
</tr>
<tr height="10"></tr>
<?php

    $sql = "select o.orders_id as orders_id,op.products_id as products_id,o.customers_id,concat(LTRIM(c.customers_lastname),' ',LTRIM(c.customers_firstname)) as customers_name, op.products_name as products_name, op.products_price as products_price, 
    op.products_quantity as products_quantity, payment_method, op.products_type as products_type, date_purchased, final_price 
    from subscriptions su,orders_products op, orders o,customers c where o.customers_id=c.customers_id and op.orders_id = o.orders_id ".$date_clause." and products_id='".$subscription_id."' and su.subscription_id=op.products_id order by date_purchased,customers_name";
	$res = tep_db_query($sql);
    $sum_quantity = 0;
    $sum_final_price = 0;
    //die('xxx');
    while($row = mysql_fetch_assoc($res)) {
    	$sum_quantity += $row['products_quantity'];
    	$sum_final_price += $row['final_price'];
    	$payment_method = $row['payment_method'];
    	$credit = "";
    	$cash = "";
    	$cheque = "";
    	switch($payment_method) {
    		case "Cash":
    			$cash = $row['final_price'];
    			break;
    		case "Credit Card":
    			$credit = $row['final_price'];
    			break;
    		case "Cheque/Money Order":
    			$cheque = $row['final_price'];
    			break;		
    	}
    	if($totals_only==false) {
		                                           								
    ?>
      <TR class='dataTableRow' height="20" onMouseOver="rowOverEffect(this)" onMouseOut="rowOutEffect(this)" onClick="javascript:location.href='<?php echo tep_href_link(FILENAME_ORDERS,'oID=' . $row["orders_id"] . '&action=edit&return=ss_sub');?>';">
          <TD class='dataTableContent' style="cursor:hand"><?php echo $i?></td>
          <TD class='dataTableContent' style="cursor:hand"><?php echo $row['customers_name']?></TD>
          <TD class='dataTableContent' style="cursor:hand"><?php echo format_date($row['date_purchased'])?></TD>
          <TD class='dataTableContent' style="cursor:hand"><?php echo $row['products_price']?></TD>
          <TD class='dataTableContent' style="cursor:hand"><?php echo $row['products_quantity']?></TD>
          <TD class='dataTableContent' style="cursor:hand"><?php echo $row['final_price']?></TD>
          <TD class='dataTableContent' style="cursor:hand"><?php echo $credit?></TD>
          <TD class='dataTableContent' style="cursor:hand"><?php echo $cash?></TD>
          <TD class='dataTableContent' style="cursor:hand"><?php echo $cheque?></TD>
      </TR>
<?php
    	}
       $i++;
    }

//    $sql_totals = "SELECT SUM(products_quantity) AS total_quantity, SUM(final_price) AS total_price FROM orders_products WHERE products_id=".$product_id;
//    $res_totals = tep_db_query($sql_totals);
//    $row_totals = mysql_fetch_assoc($res_totals);

?>
  <tr class="main">
    <TD>&nbsp;</TD>
    <TD>&nbsp;</TD>
    <TD>&nbsp;</TD>
    <TD>Total</TD>
    <TD><?php echo $sum_quantity ?></TD>
    <TD><?php echo $sum_final_price ?></TD>
	<td></td>
    <TD>&nbsp;</TD>

  </tr>
<?php
  }
?>
</table>

 <input type="hidden" name="last_id" value="0">

</form>

<script language="JavaScript" type="text/javascript"> 
function gotoPage(pageNr) {
	lastIds = new Array(); 
<?php
	foreach($pages_last_ids as $pg_last_id) {
?>	
	lastIds[lastIds.length] = <?php echo $pg_last_id ?>;
<?php
	}
?>
	document.f.page.value = pageNr;
	document.f.last_id.value = lastIds[pageNr-1];
	
	document.f.submit();
}
</script>  
<?php
}
?>
<br>
	  </tr>
    </table>
   </td>
  </tr>
</table>
<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>