<?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');  
  define(BOX_WIDTH1,'125');


$start_date = tep_convert_date_raw($_REQUEST['startDate']);
$end_date = tep_convert_date_raw($_REQUEST['endDate']);
$location_id = $_REQUEST['location'];
$categorie_id = $_REQUEST['categorie'];
$last_prod_id = $_REQUEST['last_id'];
$layout = $_REQUEST['layout'];
$totals_only = $_REQUEST['totalsOnly'];
$include_pending = $_REQUEST['includePending'];
$include_waitlist = $_REQUEST['includeWaitlist'];

if(!isset($categorie_id)) {
  $product_id = 0;
}

if(!isset($start_date)) {
	$res = tep_db_query("SELECT curdate() - INTERVAL 1 MONTH");
	$row = mysql_fetch_array($res);
	$start_date = $row[0]; 
}

if(!isset($end_date)) {
	$res = tep_db_query("SELECT curdate()");		
	$row = mysql_fetch_array($res);
	$end_date = $row[0]; 
}

if(isset($_REQUEST['totalsOnly'])) {
	$totals_only = true;
} else {
	$totals_only = false;
}

if(isset($_REQUEST['includePending'])) {
	$include_pending = true;
} else {
	$include_pending = false;
}

if(isset($_REQUEST['includeWaitlist'])) {
	$include_waitlist = true;
} else {
	$include_waitlist = false;
}


if(!$last_prod_id) {
	$last_prod_id = 0;
}
if(!isset($page)) {
	$page = 1;
}

if(!isset($location_id)) {
	$location_id=-1;
}

if(!isset($layout)) {
	$layout = "normal";
}

//if($page == 1) {
//	$last_prod_id = 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() {
	document.f.submit();
}
</script>
</head>

<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF">
<?php  if($layout!="print") { ?>
<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_OUTSTANDING_PAYMENTS;?></td>
          <tr><TD>

<!-- --->
<?php 
} //end if(layout!=print)  
?>

<script language="JavaScript">
function doSubmit() {
	strStart = document.f.startDate.value.split("-");
	startDate = new Date(strStart[0], strStart[1], strStart[2]);

	strEnd = document.f.endDate.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();
}

function f_Print() {
	document.f.layout.value="print";
	document.f.target="_blank";
	document.f.submit();

}

</script>
<FORM action="outstanding_payments.php" method="GET" id="f" name="f">
  <input type="hidden" name="page" value="1">
  <input type="hidden" name="layout" value="normal">

  <table cellspacing="3" cellpadding="2" class="formArea" width="100%" border="0">
  <tbody>
    <tr class="main"><?php $_array=array('d','m','Y');  $replace_array=array('DD','MM','YYYY'); 	$date_format=str_replace($_array,$replace_array,EVENTS_DATE_FORMAT);?>
      <td nowrap><?php echo REPORT_START_DATE?>  <INPUT type="text" name="startDate" size="10" value="<?php echo format_date($start_date)?>">
     <a href="javascript:show_calendar('f.startDate',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 nowrap><?php echo REPORT_END_DATE?> <INPUT type="text" name="endDate" size="10" value="<?php echo format_date($end_date)?>">
     <a href="javascript:show_calendar('f.endDate',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 nowrap>Location
  	<select name="location">
  	  <option value="-1"><?php echo REPORT_ALL_LOCATIONS ?></option>
  	  <?php    
          $sql = "select distinct location_id, location_name from events,events_locations where location_id=events_location order by location_name";
          $sql_result = tep_db_query($sql);
		  while($row=mysql_fetch_assoc($sql_result)) {
			$selected = $row["location_id"]==$location?" selected ":"";
		  	echo "<option value=\"".$row["location_id"]."\"".$selected.">".$row["location_name"]."</option>";
		  }
		  ?>
    </select>
  </tr>
<tr class="main">      
      <td><select name="categorie">
<OPTION value="0"><?php echo REPORT_ALL_EVENTS_CATEGORIES?> </OPTION>      
<?php
  $sql = "select * from events_categories_description";
  $res = tep_db_query($sql);
  while($row = mysql_fetch_assoc($res)) {
?>
  <OPTION value="<?php echo "-".$row["events_categories_id"]?>" <?php echo $row["events_categories_id"]==$categorie_id?"selected":"" ?>>
    <?php echo /*$row["products_id"]." ".*/$row["events_categories_name"] ?>
  </OPTION>
<?php
  }
?>
      </select>
      </td>
      <td>
      	<label><input type="checkbox" name="totalsOnly" <?php echo ($totals_only==true?"checked":"") ?>><?php echo REPORT_SUMMARY_ONLY ?></label>
      	<label><input type="checkbox" name="includePending" <?php echo ($include_pending==true?"checked":"") ?>><?php echo REPORT_INCLUDE_PENDING ?></label>
      	<label><input type="checkbox" name="includeWaitlist" <?php echo ($include_waitlist==true?"checked":"") ?>><?php echo REPORT_INCLUDE_WAITLIST ?></label>
      	
      </td>
      <td align="right"><a href="javascript:doSubmit()"><?php echo tep_image_submit('button_report_search.gif', IMAGE_SEARCH_RESERVATION); ?></a></td>
    </tr>
  </tbody>
</table>
<!--
<table border="0" width="100%">
	<tr>
		<td align="right">
			<input type="button" value="Print" onClick="f_Print()">
		</td>
	</tr>
</table>
-->
&nbsp;
<!-- table -->
<h1>TODO</h1>

<?php

$date_clause = "(date_purchased>='".$start_date."' and date_purchased<='".$end_date."')";
$events_table = "";
$products_clause = "";
if($location_id>=0) {
	$location_clause = " and (e.events_location=$location_id) and (e.events_id=es.events_id)";
	$events_table = ", events e";
} else {
	$location_clause = "";
}
  	
$product_id = abs($product_id);

if($product_id!=0) {  
  	$product_id = abs($product_id);
  	$products_clause = " and es.events_id=".$product_id." ";
} else { //all events

}

  	$product_id = abs($product_id);
  	$sql_products = "select distinct es.events_id products_id, 'E' as product_type from orders_products op, orders o, events_sessions es, events_description ed $events_table
    where op.orders_id = o.orders_id and op.products_type='E'
    and op.products_id = es.sessions_id and ed.Events_id = es.events_id and ".$date_clause.$products_clause.$location_clause;



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

ksort($all_products);
  
foreach($all_products as $key=>$val) {
	if(($key>=$last_prod_id)&&($add)) {  //visible products
		$products_ids[$key] = $val;
		$pcount++;
	}
	if($pcount == $prodno_on_page) { 
		break;
	}

}


//echo "<pre>";
//print_r($all_products);
//print_r($products_ids);
//echo "</pre>";
  
render_table($products_ids, $all_products);


function render_table($products_ids, $all_products) {
  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_products);
  foreach($all_products 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($products_ids as $product_id=>$product_type) {
  	
  	if($product_type=='P') {
    	$sql = "select products_name, products_id from orders_products op where op.products_id=".$product_id." limit 1";
  	} else {
  		$sql = "select events_name as products_name, events_id as products_id from events_description where events_id=".$product_id;
  	}
    $res = tep_db_query($sql);
    $row = mysql_fetch_assoc($res);
    $pcount++;
?>
<tr class='dataTableHeadingRow'>
<TD colspan="9" align="left" class='dataTableHeadingContent'>
	<?php echo $row['products_name'];/*$product_id." ".*/
	 ?>
</TD></tr>
<tr class='dataTableHeadingRow'>
  <td class='dataTableHeadingContent'>#</td>
  <TD class='dataTableHeadingContent'>Client</TD>
  <TD class='dataTableHeadingContent'>Sale Date</TD>
  <td class='dataTableHeadingContent'>Unit Price</td>
  <TD class='dataTableHeadingContent'>#Sold</TD>
  <TD class='dataTableHeadingContent'>Total</TD>
  <TD class='dataTableHeadingContent'>Credit</TD>
  <TD class='dataTableHeadingContent'>Cash</TD>
  <TD class='dataTableHeadingContent'>Cheque/MO</TD>
</tr>
<?php
  	if($product_type=='P') {
    $sql = "select op.products_id as products_id, 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 orders_products op, orders o where op.orders_id = o.orders_id and ".$date_clause." and products_id='".$product_id."' order by date_purchased";
  	} else {
    $sql = "select o.orders_id as orders_id, es.events_id as products_id, customers_name, op.products_name as products_name, op.products_price as products_price, 
sum(op.products_quantity) as products_quantity, payment_method, op.products_type as products_type, date_purchased, sum(final_price) as final_price 
from orders_products op, orders o, events_sessions es, events_description ed 
where op.products_type = 'E' and op.orders_id = o.orders_id and es.sessions_id = op.products_id and ed.events_id=es.events_id 
 and ".$date_clause." and es.events_id = ".$product_id." group by orders_id order by date_purchased";
    }
    $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)">
          <TD class='dataTableContent' style="cursor:hand"><?php echo $i?></td>
          <TD class='dataTableContent' style="cursor:hand"><a href="#" alt="Jump to customer"><?php echo $row['customers_name']?></a></TD>
          <TD class='dataTableContent' style="cursor:hand"><?php echo $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"><a href="#" alt="Jump to order"><?php echo $row['final_price']?></a></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
}
?>

<!-- footer //-->
<?php 
if($layout!="print") {
	require(DIR_WS_INCLUDES . 'footer.php');
} 
	
?>
	<!-- footer_eof //-->
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>
