<?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_CLASSES . 'currencies.php');
  $currencies = new currencies();
  define(BOX_WIDTH1,'125');
  set_time_limit(5);
  // 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());
	 } 
   }
	$found_results=false;
	$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());
	$product_id = (isset($input_params['sel_product'])?$input_params['sel_product']:'-1');
	$totals_only = (isset($input_params['chk_totals_only'])?true:false);
	$detail_view = (isset($input_params['chk_detail_view'])?true:false);
	$sort_by_date = (isset($input_params['chk_sort_date'])?true:false);

   $page=(isset($input_params["page"])?$input_params["page"]:1);
	
    if($start_date=="") {
	 $res = tep_db_query("SELECT '".$end_date."' - INTERVAL 1 MONTH");
	 $row = mysql_fetch_array($res);
	 $start_date = $row[0];
    }
	if($product_id!=-1)
		$swhere=" and p.products_id='".$product_id."' and op.products_type='P'";
		
	if($sort_by_date)
		$where=" order by o.date_purchased desc";
	else 
		$where=	" order by o.customers_name desc";
		
	$sql="select o.customers_name,o.orders_id,count(op.orders_id) as item_count,o.customers_id,p.products_price,op.products_id,sum(op.final_price*op.products_quantity) as final_price,sum(op.products_quantity) as products_quantity,".
			"sum(op.final_price*op.products_quantity*(op.products_tax/100)) as products_tax,sum(owd_cost) as OWD, date_format(o.date_purchased,'%Y-%m-%d') date_purchased, o.payment_method, o.shipping_weight from ".TABLE_ORDERS ." o, ". TABLE_ORDERS_PRODUCTS." op, ". TABLE_PRODUCTS." p ".
									" where o.orders_id=op.orders_id and date_format(o.date_purchased,'%Y-%m-%d')>='".$start_date."' and date_format(o.date_purchased,'%Y-%m-%d')<='".$end_date."'  ".
									" and p.products_id=op.products_id ". $swhere." and op.products_type='P' and op.orders_products_status>1 and op.orders_products_status<5 group by o.orders_id ".$where;

	$products_query=tep_db_query($sql);
	
	$num_rows=tep_db_num_rows($products_query);
	if ($num_rows>0) $found_results=true;
									
	while($products_result=tep_db_fetch_array($products_query)){
		$pid=$products_result["products_id"];
		
		$order_shipping_query = tep_db_query("select value from " . TABLE_ORDERS_TOTAL . " where orders_id = '" . $products_result['orders_id'] . "' and class = 'ot_shipping'");
		$order_shipping = tep_db_fetch_array($order_shipping_query);
		
		$order_tax_query = tep_db_query("select value from " . TABLE_ORDERS_TOTAL . " where orders_id = '" . $products_result['orders_id'] . "' and class = 'ot_tax'");
		$order_tax = tep_db_fetch_array($order_tax_query);
		
		$order_total_query = tep_db_query("select value from " . TABLE_ORDERS_TOTAL . " where orders_id = '" . $products_result['orders_id'] . "' and class = 'ot_total'");
		$order_total = tep_db_fetch_array($order_total_query);
		
			$products_result_array[]=array('name' => $products_result['customers_name'] ,
										'orders_id' =>$products_result['orders_id'],
										'total' =>$order_total['value'],
										'quantity' => $products_result['products_quantity'],
										'shipping' => $order_shipping['value'],
										'item_count' =>$products_result['item_count'],
										'final_price' => $products_result['final_price'],
										'tax' => $products_result['products_tax'],
										'OWD'=>$products_result['OWD'],
										'date_purchased'=>$products_result['date_purchased'],
										'payment_method'=>$products_result['payment_method'],
										'shipping_weight'=>$products_result['shipping_weight']
										);
	}	
?>
<!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>
<?php  include(DIR_WS_INCLUDES."date_format_js.php")?>
<script language="JavaScript">
function doReport() {
		startDate=date_format(document.f.txt_start_date.value,'','y-m-d',true);
		endDate=date_format(document.f.txt_end_date.value,'','y-m-d',true);
		if(startDate>endDate) {
			alert("<?php echo REPORT_START_AFTER_END?>");
			return;
		}
	document.f.submit();
}
function dochange(val){
	if(val){
		document.f.chk_totals_only.checked=false;
	} 	
}
function dochange1(val){
	if(val){
		document.f.chk_detail_view.checked=false;
	} 	
}

</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 echo HEADING_PRODUCTS_RECONCILIATION;?></td>	</tr>
		    <tr><TD>
			  <FORM action="<?php echo FILENAME_REPORTS_PRODUCTS_RECONCILIATION;?>" 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">
					<?php $_array=array('d','m','Y');  $replace_array=array('DD','MM','YYYY'); 	$date_format=str_replace($_array,$replace_array,EVENTS_DATE_FORMAT);?>
					<tr> 
						<td  width="40%" nowrap><?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>
						</td>
						<td  align="left"  width="30%" nowrap><?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 width="15%"><?php echo tep_draw_separator("pixel_trans.gif");?> </td>
						<td align="center"><?php echo '<a href="javascript:doReport();">' . tep_image_button('button_report_search.gif', IMAGE_SEARCH_DETAILS) . '</a>'; ?></td>
					</tr>
					<tr>      
						<td colspan="4">
						<?php 
							$all_products_array = array();
							$all_products_array = tep_get_products_array_single();
							if(sizeof($all_products_array)<=0) echo 'Products Are Not Available';
							else echo TEXT_PRODUCTS . '&nbsp;' . tep_draw_products_select_menu('sel_product',$all_products_array,$product_id,' id="sel_product"').'&nbsp;&nbsp;&nbsp;';
						?>
						<?php echo tep_draw_checkbox_field('chk_totals_only',1,$totals_only,'','onClick=javascript:dochange1(this.checked)') .'  ' . TEXT_SUMMARY_ONLY . '&nbsp;&nbsp;'.tep_draw_checkbox_field('chk_detail_view',1,$detail_view,'','onClick=javascript:dochange(this.checked)') .'  ' . TEXT_DETAIL_VIEW.
														'&nbsp;&nbsp;'. tep_draw_checkbox_field('chk_sort_date',1,$sort_by_date) .'  ' . TEXT_SORT_BY_DATE;?>
						</td>
					</tr>
				</table>
			</form>
		  </td></tr>
	<tr>
		<td><?php echo tep_draw_separator('pixel_trans.gif');?></td>
	</tr>
	<?php if ($found_results) { ?>
		  <tr><td>&nbsp;</td></tr>
		  <tr><td>
				<table cellpadding="3" cellspacing="1" width="100%" border="0">
							<tr class='dataTableHeadingTitleRow'>
								<?php if($detail_view){ ?>
									<td class="dataTableHeadingTitleContent" align="center" width="50"><?php  echo TEXT_ORDERS_ID;?></td>
								<?php }?>	
								<td class='dataTableHeadingTitleContent' align="center"><?php echo TEXT_CLIENT;?></td>
								<?php if($detail_view) {?>
									<td class="dataTableHeadingTitleContent" align="center"><?php echo TEXT_PAYMENT;?></td>
								<?php }?>	
								<td class="dataTableHeadingTitleContent" align="center" width="100"><?php echo TEXT_ORDER_DATE;?></td>
								<td class="dataTableHeadingTitleContent" align="center" width="50"><?php echo TEXT_ITEMS_ORDER;?></td>
								<td class='dataTableHeadingTitleContent' align="center"><?php echo TEXT_INCOME;?></td>
								<td class='dataTableHeadingTitleContent' align="center"><?php echo TEXT_OWD;?></td>
								<?php if($detail_view){?>
									<td class="dataTableHeadingTitleContent" align="center"><?php echo TEXT_SHIPPING_WEIGHT.'<br>'.TEXT_KG;?></td>
								<?php }?>	
								<td class='dataTableHeadingTitleContent' align="center"><?php echo TEXT_SHIPPING_COST;?></td>
								<td class='dataTableHeadingTitleContent' align="center"><?php echo TEXT_TAX;?></td>
								<td class='dataTableHeadingTitleContent' align="center"><?php echo TEXT_NET_INCOME;?></td>
							</tr>
							<tr height="10"></tr>
		  <?php 
		  		$prev_pr_id=0;
				$net_total=0;
				$netl_shipping=0;
				$net_total=0;
				$net_tax=0;
				$net_income=0;
				$net_item_count=0;
				//$grand_shipping_weight=0;
				$count=count($products_result_array);
				$jcnt=0;
					$icnt=1;
					for($i=0;$i<$count;$i++){
						$pr_id=$products_result_array[$i];
					   if(!$totals_only){
					  ?>
								 <tr class="dataTableRow" onMouseOver="rowOverEffect(this)" onMouseOut="rowOutEffect(this)" onClick="javascript:location.href='<?php echo tep_href_link(FILENAME_ORDERS,'oID='.$pr_id['orders_id'].'&action=edit&return=pc');?>'">
								 <?php if($detail_view){ ?>
										<td class="dataTableContent" align="right"><?php echo $pr_id['orders_id'];?></td>
									<?php }?>	
									<td class="dataTableContent" align="left"><?php echo $pr_id['name'];?></td>
									<?php if($detail_view){ ?>
										<td class="dataTableContent" align="left"><?php echo $pr_id['payment_method'];?></td>
									<?php }?>	
									<td class="dataTableContent" align="right"><?php echo $pr_id['date_purchased'];?>	</td>
									<td class="dataTableContent" align="right"><?php echo $pr_id['item_count']?></td>
									<td class="dataTableContent" align="right"><?php echo $currencies->format($pr_id['total']);?></td>
									<td class="dataTableContent" align="right"><?php echo ($pr_id['OWD'])?$currencies->format($pr_id['OWD']):'-';?></td>
									<?php if($detail_view){?>
										<td class="dataTableContent" align="right"><?php echo $pr_id['shipping_weight'];?></td>
									<?php }?>	
									<td class="dataTableContent" align="right"><?php echo isset($pr_id['shipping'])?$currencies->format($pr_id['shipping']):'-';?></td>
									<td class="dataTableContent" align="right"><?php echo isset($pr_id['tax'])?$currencies->format($pr_id['tax']):'-';?></td>
									<td class="dataTableContent" align="right"><?php echo $currencies->format($pr_id['final_price']);?></td>
								</tr>	
					  
					  <?php $icnt++;
						$jcnt++;
							}
					  $grand_quantity+=$pr_id['quantity'];
					  $grand_total+=$pr_id['total'];
					  $grand_income+=$pr_id['final_price'];
					  $grand_tax+=$pr_id['tax'];
					  $grand_shipping+=$pr_id['shipping'];
					  $grand_item_count+=$pr_id['item_count'];
					  $grand_shipping_weight+=$pr_id['shipping_weight'];
		  		} 
	?>
					<tr>
						 <?php if($detail_view){?>
						 	<td colspan="5" align="right" class="main"><b><?php echo TEXT_TOTAL . tep_draw_separator("pixel_trans.gif");?></b></td>
						 <?php }else{ ?>	
						 	<td colspan="3" align="right" class="main"><b><?php echo TEXT_TOTAL . tep_draw_separator("pixel_trans.gif");?></b></td>
						<?php }?>	
						<td class="dataTableContent" align="right"><?php echo $currencies->format($grand_total);?></td>
						<td class="dataTableContent" align="right"><?php echo tep_draw_separator("pixel_trans.gif");?></td>
						<?php if($detail_view){?>
							<td class="dataTableContent" align="right"><?php echo $grand_shipping_weight;?></td>
						<?php }?>	
						<td class="dataTableContent" align="right"><?php echo $currencies->format($grand_shipping);?></td>
						<td class="dataTableContent" align="right"><?php echo $currencies->format($grand_tax);?></td>
						<td class="dataTableContent" align="right"><?php echo $currencies->format($grand_income);?>
					</tr>	
						  			</table>
				 </td></tr>
				 <?php 
				 	} 
					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'); ?>
