<?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();
  require(DIR_WS_CLASSES . 'split_page_results_report.php');
  require(DIR_WS_INCLUDES . 'date_format_js.php');
  require(DIR_WS_FUNCTIONS . 'subscription_tree.php');
  define(BOX_WIDTH1,'125');
  $date_offset=(defined('EVENTS_SERVER_DATE_OFFSET')?EVENTS_SERVER_DATE_OFFSET:0);	
	// get initial parameters, check the session for previous settings
	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);
			$GLOBALS["rep_params"]["post_action"]="screen";
		} else {
			tep_set_session_value("rep_params",array());
		}
	}
	$date_begin = (isset($input_params["txt_date_begin"])?tep_convert_date_raw($input_params["txt_date_begin"]):getServerDate());
	$date_end = (isset($input_params["txt_date_end"])?tep_convert_date_raw($input_params["txt_date_end"]):'');
	$subscription=(isset($input_params["cbo_subscription"])?$input_params["cbo_subscription"]:-1);
	$post_action=(isset($input_params["post_action"])?$input_params["post_action"]:'');
	$page=(isset($input_params["page"])?$input_params["page"]:1);
	$type=(isset($input_params['sub_type'])?$input_params['sub_type']:'R');
	
	if ($date_end==""){
		$res = tep_db_query("SELECT date_add('" . $date_begin . "',INTERVAL 7 day)");	
		$row = mysql_fetch_array($res);
		$date_end = $row[0];
	}
		
	$found_results=false;
	$where = " ";
	$where_status = " ";
	//$where.=" and ((date_format(date_add(o.date_purchased,interval $date_offset hour),'%Y-%m-%d')>='" . $date_begin . "'" . " and date_format(date_add(o.date_purchased,interval $date_offset hour),'%Y-%m-%d')<='" . $date_end . "') OR " . " (date_format(date_add(date_add(o.date_purchased,interval $date_offset hour),interval su.subscription_period day),'%Y-%m-%d')>='" . $date_begin . "'" .  "and date_format(date_add(date_add(o.date_purchased,interval $date_offset hour),interval su.subscription_period day),'%Y-%m-%d')<='" . $date_end . "'))";
	$where.="and os.start_date!=0 and (((date_format(os.start_date,'%Y-%m-%d')>='" . $date_begin . "') and (date_format(os.finish_date,'%Y-%m-%d')<='" . $date_end . "'))
			or ((date_format(os.start_date,'%Y-%m-%d')<='" . $date_begin . "') and (date_format(os.finish_date,'%Y-%m-%d')>='" . $date_begin . "'))
			or ((date_format(os.start_date,'%Y-%m-%d')<='" . $date_end . "') and (date_format(os.finish_date,'%Y-%m-%d')>='" . $date_end . "'))
			or ((date_format(os.start_date,'%Y-%m-%d')<='" . $date_begin . "') and (date_format(os.finish_date,'%Y-%m-%d')>='" . $date_end . "')))";
		
		
	if($subscription!="-1") {
		$where.= "and su.subscription_id='".$subscription."' ";
	}

		// create the query for fetching registered subscribers
	if($type=='R'){
	$sql = "select c.customers_id,o.orders_id, LTRIM(c.customers_firstname) as customers_firstname,LTRIM(c.customers_lastname) as customers_lastname,
		o.customers_email_address, orders_status,sud.subscription_name,os.start_date,os.finish_date as end_date,
		date_add(os.finish_date,interval -su.subscription_renewal_start+1 day) as renewal_start,
		date_add(os.finish_date,interval su.subscription_period-su.subscription_renewal_end+1 day) as renewal_end,su.subscription_type from " . TABLE_SUBSCRIPTIONS . " su, " . 
		TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS . " o, " . TABLE_SUBSCRIPTIONS_DESCRIPTION . " sud, " .
		TABLE_CUSTOMERS . " c," . TABLE_ORDERS_SUBSCRIPTIONS . " os where os.products_id=op.products_id and os.orders_id=o.orders_id and su.subscription_id=op.products_id and op.orders_id=o.orders_id 
		and products_type='S' and subscription_type!='P' and su.subscription_id=sud.subscription_id and o.customers_id=c.customers_id and sud.language_id='" . $languages_id ."' " .		
		$where. " and DATE_FORMAT(date_add(os.finish_date,interval -su.subscription_renewal_start+1 day),'%Y-%m-%d')<='" . getServerDate()."'" .
		" and DATE_FORMAT(date_add(os.finish_date,interval -su.subscription_renewal_end+1 day),'%Y-%m-%d')>='". getServerDate() . "' order by customers_lastname,customers_firstname";
	} else if($type=='N') {
		$sql = "select c.customers_id, LTRIM(c.customers_firstname) as customers_firstname,LTRIM(c.customers_lastname) as customers_lastname,
		o.customers_email_address,o.orders_id, orders_status,sud.subscription_name,os.start_date,os.finish_date as end_date,
		date_add(os.finish_date,interval -su.subscription_renewal_start+1 day) as renewal_start,
		date_add(os.finish_date,interval -su.subscription_renewal_end+1 day) as renewal_end,su.subscription_type from " . TABLE_SUBSCRIPTIONS . " su, " . 
		TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS . " o, " . TABLE_SUBSCRIPTIONS_DESCRIPTION . " sud, " .
		TABLE_CUSTOMERS . " c," . TABLE_ORDERS_SUBSCRIPTIONS . " os where os.products_id=op.products_id and o.orders_id=os.orders_id and su.subscription_id=op.products_id and op.orders_id=o.orders_id 
		and products_type='S' and subscription_type!='P' and su.subscription_id=sud.subscription_id and o.customers_id=c.customers_id and sud.language_id='" . $languages_id ."' " .
		$where . " and DATE_FORMAT(date_add(date_add(os.finish_date,interval $date_offset hour),interval -su.subscription_renewal_end+1 day),'%Y-%m-%d')<'" . getServerDate() . "' order by customers_lastname,customers_firstname";
	}
	//echo $sql;
	$sql_temp=$sql;
	
	// split the report
	$query_split = new splitPageResultsReport($page, REPORT_MAX_ROWS_PAGE, $sql_temp, $query_split_numrows);
	
	$sql_result = tep_db_query($sql);
		
	if (tep_db_num_rows($sql_result)>0) $found_results=true;
	
	//$type_array=array(array('id' => 'R','text' => TEXT_SUBSCRIPTION_RENEWAL), array('id' =>'N', 'text' => TEXT_SUBSCRIPTION_NON_RENEWAL));
	
?>
<!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 showCustomerDetails(id){
		location.href="<?php echo tep_href_link(FILENAME_ORDERS);?>"+"?oId="+id+"&return=sun&page=1&action=edit&oID=" + id;
	}
	function validate(){
		var begin_date_time = 0;
		var end_date_time = 0;
		var error_result = "";
		if(document.f.txt_date_begin.value==""){
			error_result+="* <?php echo ERR_SUBSCRIPTION_START_DATE;?>\n";
		} else if(document.f.txt_date_begin.value!=""){
			var date_error = true;
			if((document.f.txt_date_begin.value).indexOf("-")!=-1){
				date_error = isValidDate(date_format(document.f.txt_date_begin.value,'','Y-m-d'));
			} else {
				date_error = false;
			}
			if(!(date_error)){
				error_result+="* <?php echo ERR_SUBSCRIPTION_START_DATE;?>\n";
			}
		}
		if(document.f.txt_date_end.value==""){
			error_result+="* <?php echo ERR_SUBSCRIPTION_FINISH_DATE;?>\n";
		} else if(document.f.txt_date_begin.value != ""){
			var date_error = true;
			if((document.f.txt_date_end.value).indexOf("-")!=-1){
				date_error = isValidDate(date_format(document.f.txt_date_end.value,'','Y-m-d'));
			} else {
				date_error = false;
			}
			if(!(date_error)){
				error_result+="* <?php echo ERR_SUBSCRIPTION_FINISH_DATE;?>\n";
			}else{
				 begin_date_time = date_format(document.f.txt_date_begin.value,'','',true).getTime();
				 end_date_time = date_format(document.f.txt_date_end.value,'','',true).getTime();
			}
		}
		if(begin_date_time>end_date_time){
			error_result+="* <?php echo ERR_SUBSCRIPTION_BEGIN_DATE_EXCEED_END_DATE;?>\n";
		}
		if (error_result!=""){
		   alert(error_result);
		}else{
			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">
			<tr>
				<!--<td class="pageHeading"><?php //if($type=='R') echo HEADING_SUBSCRIPTION_RENEWALS; else echo HEADING_SUBSCRIPTION_NON_RENEWALS;?></td>!-->	
			<tr>
			<?php echo tep_draw_form("f",FILENAME_SUBSCRIPTION_RENEWALS); ?>
			<input type="hidden" name="post_action" value="1"/>
			<!-- form -->
			<tr >
				<td>
				<table border="0" cellspacing="3" cellpadding="2" class="searchArea" width="100%">
					<tr height="40">
						<td nowrap="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 TEXT_START_DATE . '&nbsp;' . tep_draw_input_field("txt_date_begin",format_date($date_begin),"size=10",false,'text',false);?>
							<a href="javascript:show_calendar('f.txt_date_begin',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;&nbsp;
							<?php echo TEXT_END_DATE . '&nbsp;' . tep_draw_input_field("txt_date_end",format_date($date_end),"size=10",false,'text',false);?>
							<a href="javascript:show_calendar('f.txt_date_end',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;&nbsp;
						</td>
					</tr>
						<tr height="40">
						<td nowrap="nowrap">
							<?php
								$all_subscriptions_array=array();
								$all_subscriptions_array = tep_get_subscription_array_single();
								if(sizeof($all_subscriptions_array)<=0) echo 'Subscriptions are not Available';
								else echo TEXT_SUBSCRIPTION . '&nbsp;' . tep_draw_subscription_select_menu('cbo_subscription',$all_subscriptions_array,$subscription_id,' id="cbo_subscription"');
							?>&nbsp;&nbsp;&nbsp;
							<?php //echo TEXT_TYPE . "&nbsp;&nbsp;". tep_draw_pull_down_menu('sub_type',$type_array,$type);?>
						</td>


						
						<td rowspan="2" valign="middle" align="right">
						   <a href="javascript:validate()"><?php echo tep_image_button('button_report_search.gif', IMAGE_SEARCH_SUBSCRIPTIONS); ?></a>
						</td>
					</tr>
					<!-- report content -->
				</table>
				</td>
			</tr>
			</form>
			<tr ><td class="cell_bg_report_header">&nbsp;</td></tr>
<?php 
				if ($found_results){
				?>
					<tr>
						<td>
						<table border="0" width="100%" cellspacing="0" cellpadding="2">
							<tr>
								<td class="smallText" align="left">
									<?php echo $query_split->display_links($query_split_numrows, REPORT_MAX_ROWS_PAGE, REPORT_MAX_LINKS_PAGE, $page,tep_get_report_params()); ?>&nbsp;
								</td>
							</tr>
						</table>
						</td>
					</tr>
					<tr>
						<td>
						<table border="0" width="100%" cellspacing="1" cellpadding="1">
							<tr class='dataTableHeadingTitleRow'>
								<td class='dataTableHeadingTitleContent' width="30">#</td>
								<td class='dataTableHeadingTitleContent'><?php echo TEXT_LAST_NAME;?></td>
								<td class='dataTableHeadingTitleContent'><?php echo TEXT_FIRST_NAME;?></td>
								<td class='dataTableHeadingTitleContent'>&nbsp;</td>
								<td class='dataTableHeadingTitleContent'><?php echo TEXT_SUBSCRIPTION_NAME;?></td>
								<td class='dataTableHeadingTitleContent'><?php echo TEXT_SUBSCRIPTION_TYPE;?></td>
								<td class='dataTableHeadingTitleContent' width="130"><?php echo TEXT_RENEWAL_START_DATE;?></td>
								<td class='dataTableHeadingTitleContent' width="130"><?php echo TEXT_RENEWAL_END_DATE;?></td>
							</tr>
							<tr height="10"></tr>
				<?php
					$i=1;
					$subscription_array=array('B'=>TEXT_BASIC,
											  'C'=>TEXT_CASUAL,
											  'S'=>TEXT_SEASON,
											  'P' =>TEXT_SUPPORT);
											
          			while($row = mysql_fetch_assoc($sql_result)) {
					?>
					  <tr class='dataTableRow' height="20" onMouseOver="rowOverEffect(this)" onMouseOut="rowOutEffect(this)" onClick="javascript:showCustomerDetails('<?php echo $row["orders_id"];?>');">
						  <td class='dataTableContent' style="cursor:hand"><?php echo $i?></td>
						  <td class='dataTableContent' style="cursor:hand"><?php echo $row['customers_lastname'];?></td>
						  <td class='dataTableContent' style="cursor:hand"><?php echo $row['customers_firstname'];?></td>
						  <td class='dataTableContent' style="cursor:hand" align="center"><a href="mailto:<?php echo $row['customers_email_address']?>"><img src="images/icons/mail.gif" border="0"></a></td>
						  <td class='dataTableContent' style="cursor:hand"><?php echo $row['subscription_name']?></td>
						  <td class='dataTableContent' style="cursor:hand"><?php echo $subscription_array[$row['subscription_type']]?></td>
						  <td class='dataTableContent' style="cursor:hand"><?php echo format_date($row['renewal_start']);?></td>
						  <td class='dataTableContent' style="cursor:hand"><?php echo format_date($row['renewal_end']);?></td>
					  </tr>
      			<?php 
						$i++;						
					} // for $icnt
					?>
				</table>
				</TD>
			</tr>
			<?php	} // if $found_results
					else{
						echo '<tr><td class="main" align="center">' . TEXT_NO_RECORDS_FOUND . '</td></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'); ?>

