<?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('includes/classes/currencies.php');
  require('includes/classes/pdfTable.php');
  
  require(DIR_WS_CLASSES . 'split_page_results_report.php');
	
  $currencies=new currencies();
	 
  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());
		}
	}
	
	// pdf related
	$col_width=Array();
	$page_height=0;
	$page_width=0;
	$row_height=11;
	$font_name="Arial";
	$font_size=10;
	$pdf="";
	$header_color=array(201,201,201);
	$row_color=array(240,241,241);
	if ($post_action==""){
		$pdf_output=true;
		$post_action="screen";
	}
	
	$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']):'');
	$location = (isset($input_params['sel_location'])?$input_params['sel_location']:-1);
	$product_id = (isset($input_params['sel_product'])?$input_params['sel_product']: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 curdate() - INTERVAL 1 MONTH");
	$row = mysql_fetch_array($res);
	$start_date = $row[0]; 
}

if($end_date=="") {
	$res = tep_db_query("SELECT curdate()");		
	$row = mysql_fetch_array($res);
	$end_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>
<?php include(DIR_WS_INCLUDES."date_format_js.php")?>

<script language="JavaScript">

</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">
	<tr height="10"></tr>
	<!--<td class="pageHeading"><?php //echo HEADING_SERVICES_SALES;?></td>!-->
          <tr><TD>
<!-- --->
<?php 
} //end if(layout!=print)  
?>

<script language="JavaScript">

function doReport(mode) {
				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.post_action.value="screen";
              document.f.target="_self";
              document.f.submit();
   }
		
function f_Print() {
	document.f.layout.value="print";
	document.f.target="_blank";
	document.f.submit();

}
</script>
<FORM action="<?php echo FILENAME_SERVICES_SALES;?>" method="POST" id="f" name="f">
  <input type="hidden" name="page" value="1">
  <input type="hidden" name="layout" value="normal">
  <input type="hidden" name="post_action" value="1">
  <table cellspacing="3" cellpadding="2" class="formArea" width="100%">
  <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="txt_start_date" size="10" value="<?php echo format_date($start_date)?>">
     <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 nowrap><?php echo REPORT_END_DATE?> <INPUT type="text" name="txt_end_date" size="10" value="<?php echo format_date($end_date);?>">
     <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 nowrap>Location
  	<select name="sel_location">
  	  <option value="-1"><?php echo REPORT_ALL_LOCATIONS ?></option>
  	  <?php    
          $sql = "select distinct se.service_location as service_location ,l.service_location_name as location_name from ". TABLE_SERVICES . " se," . TABLE_SERVICES_LOCATIONS ." l where l.service_location_id=se.service_location order by l.service_location_name";
          $sql_result = tep_db_query($sql);
		  while($row=mysql_fetch_assoc($sql_result)) {
			$selected = $row["service_location"]==$location?" selected ":"";
		  	echo "<option value=\"".$row["service_location"]."\"".$selected.">".$row["location_name"]."</option>";
		  }
		  ?>
    </select>
  </tr>
<tr class="main">      
      <td><select name="sel_product">
<OPTION value="0"><?php echo REPORT_ALL_SERVICES;?> </OPTION>      
<?php
	
  $sql = "select se.service_id as products_id, service_name as products_name, service_costs as products_price 
  from services se, services_description sed where se.service_id = sed.service_id and sed.language_id='". $languages_id . "'" . SERVICES_CONDITION_STATUS .  "order by  products_name";

  $res = tep_db_query($sql);
  while($row = mysql_fetch_assoc($res)) {
?>
  <OPTION value="<?php echo "-".$row["products_id"]?>" <?php echo $row["products_id"]==abs($product_id)?"selected":"" ?>>
    <?php echo /*$row["products_id"]." ".*/$row["products_name"] ?>
  </OPTION>
<?php
  }
?>
      </select>
      </td>
      <td><label><input type="checkbox" name="chk_totals_only" <?php echo ($totals_only==true?"checked":"") ?>><?php echo REPORT_SUMMARY_ONLY ?></label></td>
      	<td align="right"><?php echo '<a href="javascript:doReport(1);">' . tep_image_button('button_report_search.gif', IMAGE_SEARCH_DETAILS) . '</a>'; ?></td>
    </tr>
	
  </tbody>
</table>
<?php

$date_clause = "(date_purchased>='".$start_date."' and date_purchased<='".$end_date."')";
$services_table = "";
//$products_clause = "";
if($location>=0) {
	$location_clause = " and (s.service_location=$location)";
	$services_table = ", services s";
} else {
	$location_clause = "";
}
  	
$product_id = abs($product_id);

if($product_id!=0) {  
  	$product_id = abs($product_id);
  	$products_clause = " and s.service_id=".$product_id." ";
} else { //all services

}

  	$product_id = abs($product_id);
	
	 
  	$sql_products = "select distinct s.service_id products_id, 'V' as product_type from orders_products op, orders o, services s, services_description sd $events_table
    where op.orders_id = o.orders_id and op.products_type='V'
    and op.products_id = s.service_id and sd.service_id = s.service_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;
	}

}
  
render_table($products_ids, $all_products);

function render_table($products_ids, $all_products) {
  global $page, $last_prod_id, $date_clause, $totals_only,$currencies;
?>
  <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;
?>
<table border="0" width="100%" cellspacing="1" cellpadding="1">
<?php
  foreach($products_ids as $product_id=>$product_type) {
  
  $sql = "select distinct service_name as products_name, service_id as products_id from services_description where service_id=".$product_id;
  	
	$res = tep_db_query($sql);
    $row = mysql_fetch_assoc($res);
    $pcount++;
?>
<tr class='dataTableHeadingRow'>
<TD colspan="10" 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>
  <TD class='dataTableHeadingContent'>Bank Transfer Payment</TD>
</tr>
<?php
 
   $sql = "select o.orders_id as orders_id, s.service_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, services s, services_description sd 
		   where op.products_type = 'V' and op.orders_id = o.orders_id and s.service_id = op.products_id and sd.service_id=s.service_id 
 		   and ".$date_clause." and s.service_id = ".$product_id." group by orders_id order by date_purchased";
	
    $result = tep_db_query($sql);
	//output to pdf
	if ($post_action=="pdf"){
			generate_pdf();
			tep_redirect(DIR_WS_CATALOG . "images/services_sales.pdf");
			return;
		}
		
	// output to excel
	if ($post_action=="excel"){
		generate_excel();
		tep_redirect(DIR_WS_CATALOG . "images/services_sales.csv");
		return;
	}

	
    $sum_quantity = 0;
    $sum_final_price = 0;
	
	
	
	while($row=tep_db_fetch_array($result)){
    //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 = "";
		$bank_transfer="";
    	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;	
			case "Bank Transfer Payment":	
			    $bank_transfer = $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=rss&type=ser');?>';">
          <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 $dat=substr($row['date_purchased'],0,10);
		  														  $tim=substr($row['date_purchased'],11,19);
																  echo format_date($dat).'&nbsp;'.$tim;	?></TD>
          <TD class='dataTableContent' style="cursor:hand"><?php echo $currencies->format($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 $currencies->format($row['final_price'])?></a></TD>
          <TD class='dataTableContent' style="cursor:hand"><?php echo $currencies->format($credit);?></TD>
          <TD class='dataTableContent' style="cursor:hand"><?php echo $currencies->format($cash);?></TD>
          <TD class='dataTableContent' style="cursor:hand"><?php echo $currencies->format($cheque);?></TD>
		  <TD class='dataTableContent' style="cursor:hand"><?php echo $currencies->format($bank_transfer);?></TD>
      </TR>
<?php
    	}
       $i++;
    }
?>
  <tr class="main">
    <TD>&nbsp;</TD>
    <TD>&nbsp;</TD>
    <TD>&nbsp;</TD>
    <TD>Total</TD>
    <TD><?php echo $sum_quantity ?></TD>
    <TD><?php echo $currencies->format($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 //-->

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

// function to create pdf details
	function generate_pdf(){
		global $result,$display_header,$found_results,$currencies,$totals_only;

		// set initial parameters
		$table=new pdfTable("A4","l");
		$table->left_margin=20;
		$table->top_margin=20;
		$table->right_margin=20;
		$table->bottom_margin=20;
		$table->pdfInit();
		
		// Add Styles for table
		$table->AddStyle("heading","color:#727272;bgcolor:#ffffff;font:Arial;size:18;style:B");
		$table->AddStyle("user","color:#000000;bgcolor:#f1f9fe;font:Arial;size:14;style:B;border-color:#7b9ebd");
		$table->AddStyle("subhead","color:#000000;bgcolor:#7b9ebd;font:Arial;size:10;style:B");
		$table->AddStyle("headrow","color:#000000;bgcolor:#C9C9C9;font:Arial;size:10;style:B");
		$table->AddStyle("row","color:#000000;bgcolor:#F0F1F1;font:Arial;size:10");
		$table->AddStyle("query","color:#000000;bgcolor:#FFFFFF;font:Arial;size:11");
		$table->AddStyle("subrow","color:#000000;bgcolor:#FFFFFF;font:Arial;size:10");
		$table->AddStyle("subrowhead","color:#000000;bgcolor:#FFFFFF;font:Arial;size:10;style:B");

		$table->headers["text"]=$display_header;
		$table->headers["style"]="query";
		$table->headers["height"]=14;
		$table->headers["width"]="100%";
		$table->headers["cols"]=5;

		// define column width
		
		$widths[0]=40;
		$widths[2]=80;
		$widths[3]=80;
		$widths[4]=80;
		$widths[5]=150;
		$widths[6]=200;
		
		$widths[1]=$table->$widths[0]+$widths[2]+$widths[3]+$widths[4]+$widths[5]+$widths[6];

		// output header details
		$cols=array();
		$cols[]=array("text"=>No,"width"=>$widths[1],"align"=>"L","style"=>"headrow","valign"=>"M");
		$cols[]=array("text"=>Client,"width"=>$widths[2],"align"=>"L","style"=>"headrow","valign"=>"M");
		$cols[]=array("text"=>SaleDate,"width"=>$widths[3],"align"=>"L","style"=>"headrow","valign"=>"M");
		$cols[]=array("text"=>UnitPrice,"width"=>$widths[4],"align"=>"L","style"=>"headrow","valign"=>"M");
		$cols[]=array("text"=>Sold,"width"=>$widths[5],"align"=>"L","style"=>"headrow","valign"=>"M");
		$cols[]=array("text"=>Total,"width"=>$widths[6],"align"=>"L","style"=>"headrow","valign"=>"M");
		$table->tableheaders["text"]=$cols;
		unset($cols);
		$i=1;
			if ($found_results){
		mysql_data_seek($result,0);
		$prev_products_id=0;
	
		// output content
		while($row = tep_db_fetch_array($result)) {
			$products_id=$row["products_id"];
		if ($prev_products_id!=$products_id) {
				$cols=array();
				$cols[]=array("text"=>$row["products_name"],"width"=>$widths[0]+$widths[1]+$widths[2]+$widths[3]+$widths[4],"align"=>"L","style"=>"subrowhead","valign"=>"M");
				$table->OutputRow($cols,15);
				unset($cols);
				$prev_products_id=$products_id;
			}	
						
			$cols=array();
			
			$cols[]=array("text"=>$i,"width"=>$widths[1],"align"=>"L","style"=>"row","valign"=>"M");
			$cols[]=array("text"=>$row['customers_name'],"width"=>$widths[2],"align"=>"L","style"=>"row","valign"=>"M");
			$cols[]=array("text"=>$row['date_purchased'],"width"=>$widths[3],"align"=>"L","style"=>"row","valign"=>"M");
			$cols[]=array("text"=>$row['products_price'],"width"=>$widths[4],"align"=>"L","style"=>"row","valign"=>"M");
			$cols[]=array("text"=>$row['products_quantity'],"width"=>$widths[5],"align"=>"L","style"=>"row","valign"=>"M");
			$table->OutputRow($cols,15);
			unset($cols);
			$i++;						
		}
		} else {
			$cols[]=array("text"=>REPORT_NO_RESULTS,"width"=>"100%","align"=>"C","style"=>"subrow","valign"=>"M");
			$table->OutputRow($cols,15);
			unset($cols);
		}
		// create pdf page
		$table->Render("services_sale.pdf");
	}
	
		function generate_excel(){
		global $result,$display_header,$found_results,$currencies,$totals_only;
		$result="";
		$i=1;
		
		$result.=No . "," . Client . "," . SaleDate . "," . UnitPrice . "," . Sold . "," . Total . "\n";
		
		if ($found_results){
		mysql_data_seek($result,0);
		$prev_services_id=0;
		while($row = tep_db_fetch_array($result)) {
			$services_id=$row["products_id"];
			if ($prev_services_id!=$services_id) {
				$result.=$row["products_name"] . "\n";
				$prev_services_id=$services_id;
			}
			//$result.=sprintf("\"%s\",\"%s\",\"%s\",\"%s\",\"%s\"\n",$row['service_name'],$row['service_stime'],$row['service_etime'],$period,$total);
			$result.=sprintf("\"%s\",\"%s\",\"%s\",\"%s\",\"%s\"\n",$i,$row['customers_name'],$row['date_purchased'],$row['products_price'],$row['products_quantity']);
			$i++;						
		}
		} else {
			$result.=REPORT_NO_RESULTS . "\n";
		}
		tep_write_text_file("services_sales.csv",$res);
	}
	
	
 ?>
