<?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('includes/classes/pdfTable.php');
   require('includes/classes/currencies.php');
   define(BOX_WIDTH,'125');
	$currencies=new currencies();
	// get initial parameters, try to load from the session
	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());
		}
	}

	$category=(isset($input_params["cbo_category"])?$input_params["cbo_category"]:-1);
	$date_begin=isset($input_params['txt_start_date'])?tep_convert_date_raw($input_params['txt_start_date']):'';
	$date_end=isset($input_params['txt_end_date'])?tep_convert_date_raw($input_params['txt_end_date']):'';
	$post_action = isset($input_params['post_action'])?$input_params['post_action']:'';
	$page=(isset($input_params["page"])?$input_params["page"]:1);
	$totals_only = (isset($input_params['chk_totals_only'])?true:false);
	$select=isset($input_params['check_pending'])?$input_params['check_pending']:-1;
	if ($post_action==""){
		$output_pdf=true;
		$post_action="screen";
	}	
	
	if ($date_begin==""){
		$sql =  "select date_sub(curdate(), interval 2 day) begin,curdate() as end";
		$sql_result = tep_db_query($sql);
		$row = mysql_fetch_assoc($sql_result);
		$date_begin = $row["begin"];
		$date_end = $row["end"];
	}
	// prepare the display headers to be printed in the pdf page;
	$display_header="";
	
	if ($date_end==""){
		$sql =  "select date_add('$date_begin', interval -(dayofmonth('$date_begin')-1) day) begin, date_add('$date_begin', interval (30-dayofmonth('$date_begin')) day) end";	
		$sql_result = tep_db_query($sql);
		$row = mysql_fetch_assoc($sql_result);
		$date_begin = $row["begin"];
		$date_end = $row["end"];
	}
	$ret=tep_time_diff($date_begin,$date_end);
	$total_days=(int)$ret["days"];
	// Header to be placed in pdf output
	$display_header="";
	$display_header=TEXT_START_DATE .  ":&nbsp;&nbsp;" . $date_begin . "&nbsp;&nbsp;&nbsp;&nbsp;";
	$display_header.=TEXT_END_DATE . ":&nbsp;&nbsp;" . $date_end . "\t";
	if ($category=="-1") $display_header.=TEXT_CATEGORY . ":&nbsp;&nbsp;" . TEXT_ALL_CATEGORIES  ."\t";
		
	$found_results=false;
	$where_cat="";
	if ($category!=-1){
		$where_cat.=" and se2c.services_categories_id='" . $category . "'";
		$display_header.=TEXT_CATEGORY .'&nbsp;'.tep_get_service_category_name($category,$languages_id);
	}
	//add the option start date and end date to the query
	$where_date.=" and ((os.start_date>='" . $date_begin . "' and os.start_date<='" . $date_end . "') or (os.end_date>='" . $date_begin . "' and os.end_date<='" . $date_end . "'))";
	// create the sql for report
	// fetch the available services and categories
   $results=array();
	$services=array();
	$prev_cat_id=0;
	$output_array=array();
	$category_list=array();
	$check="";
	$service_sql="SELECT se.service_id,secd.services_categories_id,secd.services_categories_name,sed.service_name,se.quantity, " .
					"se.booking_type, se.service_total_time,se.include_resource,se.quantity from " . TABLE_SERVICES  . " se," . TABLE_SERVICES_TO_SERVICES_CATEGORIES . " se2c," .
					TABLE_SERVICES_CATEGORIES_DESCRIPTION . " secd, " . TABLE_SERVICES_DESCRIPTION . " sed  where se2c.service_id=se.service_id " . $where_cat . 
					" and se.service_id=sed.service_id and se2c.services_categories_id=secd.services_categories_id and sed.language_id='" . 
					(int)$languages_id . "' and secd.language_id='" . (int)$languages_id .  "'". $check ."  order by secd.services_categories_name,sed.service_name";
					
	$service_ids="";
	$service_query=tep_db_query($service_sql);
	if (tep_db_num_rows($service_query)>0)  $found_results=true;
	$prev_cat_id=0;
	// find the difference between two dates given
	while($service_result=tep_db_fetch_array($service_query)){
		$service_id=$service_result["service_id"];
		if ($prev_cat_id!=$service_result["services_categories_id"]){
			$results[]=array("cat_name"=>$service_result["services_categories_name"],"service"=>array());
			$prev_cat_id=$service_result["services_categories_id"];
			$icnt=count($results)-1;
		}
		if (!isset($services[$service_id])){
			$services[$service_id]=array();
			if($service_result["booking_type"]=="D")
				$service_total=$total_days;
			else if($service_result["booking_type"]=="H")
				$service_total=$total_days*24;	
			
		$services[$service_id]["service_name"]=$service_result["service_name"];
		$services[$service_id]["total_time"]=$service_total;
		$services[$service_id]["service_id"]=$service_id;
		$services[$service_id]["type"]=$service_result["booking_type"];
		if($service_result['include_resource']=='N')$services[$service_id]["quantity"]=$service_result["quantity"];
		}
		$results[$icnt]["service"][]=$service_result["service_id"];
		$service_ids.="'" . $service_result["service_id"] . "',";
	
	}
	 
	$service_ids=substr($service_ids,0,-1);
	//get category list
	$view_array=array(array("id"=>1,"text"=>TEXT_DAY),array("id"=>2,"text"=>TEXT_WEEK),array("id"=>3,"text"=>TEXT_MONTH));
	$category_array=array();
	$category_array[]=array("id"=>-1,"text"=>TEXT_ALL_CATEGORIES);
		
	$category_query=tep_db_query("SELECT scd.services_categories_name,sc.services_categories_id from " . TABLE_SERVICES_CATEGORIES . " sc, " . TABLE_SERVICES_CATEGORIES_DESCRIPTION . " scd " . 
						"where sc.services_categories_id=scd.services_categories_id and scd.language_id='" . (int)$languages_id . "' order by scd.services_categories_name");
	while($category_result=tep_db_fetch_array($category_query)){
		$category_array[]=array("id"=>$category_result["services_categories_id"],"text"=>$category_result["services_categories_name"]);
	}
	$category_list[]=array('category_name'=>$result['cat_name']);
	
	for ($icnt=0;$icnt<count($results);$icnt++){
	$result=&$results[$icnt];
	
		for ($jcnt=0;$jcnt<count($result["service"]);$jcnt++) {
		//echo "true" . $service_id;
			$service=&$services[$result["service"][$jcnt]];
			
			if($service["service_id"]=='9')
			if ($service["total_time"]<=0) $service["total_time"]=1;
			if($service["total_time"])$percentage=round($service["booking_time"]/$service["total_time"]*100,2);
			$duration=TEXT_HOURS;
			if ($service["type"]=="D") $duration=' ' . TEXT_DAYS;
			$booking_type=$service["type"];
			$quantity=(($service['quantity'])?$service['quantity']:0);
			$service_time=get_total_service_booking_dates($service["service_id"],$languages_id,'total_time');
			$sales_time=get_total_service_booking_dates($service["service_id"],$languages_id,'sale_time');
			if($service_time<$sales_time) $sales_time=$service_time;
	 		$percentage="0"; 
	 		if($service_time)$percentage=number_format(($sales_time/$service_time) * 100,2);
	 		$service['service_time']=(($service_time)?$service_time:0) . ' ' .(($booking_type=='D')?TEXT_DAYS:TEXT_HOURS);
	 		$service['sale_time']=(($sales_time)?$sales_time:0) . ' ' .(($booking_type=='D')?TEXT_DAYS:TEXT_HOURS);
	 		$service['percentage']=$percentage;
	 		$output_array[]=array('type'=>'row','cat_name'=>$result['cat_name'],'service_name'=>$service['service_name'],'nbtot'=>$service['service_time'],'booking_time'=>$service['sale_time'],'percentage'=>$percentage .'%');
		
		}
   }	
   $report_filename=sprintf("service_util_%s_%s",$login_id,time());
   //output to pdf
	if ($post_action=="pdf"){
			generate_pdf();
			tep_redirect(DIR_WS_CATALOG . "images/".$report_filename.".pdf");
			return;
		}
		
	// output to excel
	if ($post_action=="excel"){
		generate_excel();
		tep_redirect(DIR_WS_CATALOG . "images/".$report_filename.".csv");
		return;
	}
	tep_delete_temp_files("service_util_" . $login_id);
?>
<!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>
</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 height="5">
				<!--<td class="pageHeading"><?php //echo HEADING_TITLE;?></td>!-->
			</tr>
			<tr>
				<td class="pageHeading"></td>
			</tr>
			<!-- --->
		<script language="JavaScript">
		/*function doSubmit() {
			startDate=date_format(document.f.txt_start_date.value,'','',true);
			endDate=date_format(document.f.txt_end_date.value,'','',true);	
			if(startDate.getTime()>endDate.getTime()) {
				alert("<?php echo REPORT_START_AFTER_END?>");
				return;
			}
			document.f.post_action.value="screen";
			document.f.submit();
		}*/
	
		function doReport(mode) {
			  if (mode==3){
					document.f.post_action.value="excel"
					document.f.target="_blank";
				} else if (mode==2){
					document.f.post_action.value="pdf";
					document.f.target="_blank";
				} else {
					startDate=date_format(document.f.txt_start_date.value,'','',true);
					endDate=date_format(document.f.txt_end_date.value,'','',true);	
					if(startDate.getTime()>endDate.getTime()) {
						alert("<?php echo REPORT_START_AFTER_END?>");
						return;
					}else {
					document.f.post_action.value="screen";
					document.f.target="_self";
					}
				}
				document.f.submit();
	         }
			</script>
			<?php 
				echo tep_draw_form("f",FILENAME_SERVICES_UTIL);
				echo tep_draw_hidden_field("post_action","screen");
			?>
			<tr class="cell_bg_report_header">
				<td>
				<table cellspacing="0" cellpadding="0" width="100%" border="0">
					<tr>
						<td>
						<table border="0" cellpadding="2" cellspacing="3"  width="100%" class="searchArea">
							<tr>
								<td align="right"><?php echo TEXT_CATEGORY;?></td>
								<td nowrap>
									<?php echo  tep_draw_pull_down_menu("cbo_category",$category_array,$category);?>
								</td>
								<td align="right"><?php echo TEXT_START_DATE;?></td>
								<td align="left">
									<?php echo tep_draw_input_field('txt_start_date',format_date($date_begin),' 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="right"><?php echo TEXT_END_DATE;?>	</td>
								<td>
									<?php echo tep_draw_input_field('txt_end_date',format_date($date_end),' 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"><?php echo '<a href="javascript:doReport(1);">' . tep_image_button('button_report_search.gif', IMAGE_SEARCH_DETAILS) . '</a>'; ?></td>
							</tr>
							<tr>
								<td align="right"><?php echo TEXT_SHOW_PENDING_STATUS;?></td>
								<td align="left"><?php echo tep_draw_checkbox_field('check_pending',1) ;?></td>
								<td align="right" colspan="5"><?php echo '<a href="javascript:doReport(2);">' . tep_image_button('button_export_pdf.gif', IMG_EXPORT_PDF) . '</a>';?></td>
							</tr>
							<tr>
								<td align="right" colspan="7"><?php echo '<a href="javascript:doReport(3);">' . tep_image_button('button_export_excel.gif', IMG_EXPORT_EXCEL) . '</a>';?></td>
							</tr>
							<tr><td colspan="5"><?php echo tep_draw_separator('pixel_trans.gif','1','3');?></td></tr>
						  </table>
						</td>
					</tr>
				 </table>
				</td>
			</tr>
			</form>
			<tr>
				<TD>
					<?php echo tep_draw_separator('pixel_trans.gif',1,5); ?>
				</TD>
			</tr>			
			<?php if ($found_results) { ?>
					<?php 
					//echos($results);
					for ($icnt=0;$icnt<count($results);$icnt++){
						$result=&$results[$icnt];
						$category_list[]=array('category_name'=>$result['cat_name']);
			?>
							<tr height="50">
								<td>
								<table border="0" cellpadding="2" cellspacing="0" width="100%" class="searchArea">
									<tr>
										<td class="tableHeading">
											<?php 
												echo $result["cat_name"];
											?>
										</td>
									</tr>
								</table>
								</td>
							</tr>
							<tr>
								<td>
								<table border="0" cellpadding="2" cellspacing="0" width="100%">
									<tr class="dataTableHeadingTitleRow">
										<td class="dataTableHeadingTitleContent"><?php echo TABLE_HEADING_SERVICE; ?></td>
										<td class="dataTableHeadingTitleContent" align="right" width="200"><?php echo TABLE_HEADING_SERVICE_TOTAL_TIME; ?></td>
										<td class="dataTableHeadingTitleContent" align="right" width="200"><?php echo TABLE_HEADING_SALE_TOTAL_TIME; ?></td>
										<td class="dataTableHeadingTitleContent" align="right" width="80">%</td>
						             </tr>
									 <tr height="10"></tr>
							    <?php 
								//echos($result["service"]);
								for ($jcnt=0;$jcnt<count($result["service"]);$jcnt++) {
								 			$service=&$services[$result["service"][$jcnt]];
								 			$service_time=$service['service_time'];
											//echo $service_time;
								 			$sales_time=$service['sale_time'];
								 			$percentage=$service['percentage'];
									 ?>
												<tr class="dataTableRow" onMouseOver="rowOverEffect(this)" onMouseOut="rowOutEffect(this)" onClick="document.location.href='<?php echo tep_href_link(FILENAME_SERVICES, 'action=new_service_preview&read=only&seID=' . $result["service"][$jcnt] . '&origin=' . FILENAME_SERVICES_UTIL   , 'NONSSL'); ?>'">
													<td class="dataTableContent"><?php echo $service['service_name']; ?></td>
													<td class="dataTableContent" align="right"><?php echo (($service_time)?$service_time:0); ?></td>
													<td class="dataTableContent" align="right"><?php echo (($sales_time)?$sales_time :0);?></td>
													<td class="dataTableContent" align="right"><?php echo $percentage .'%';  ?> </td>
												</TR>
									<?php  } // for jcnt
									 ?>
								</table>
								</td>
						</tr>
						<tr>
							<td><?php echo tep_draw_separator('pixel_trans.gif',1,10);?></td>
						</tr>
					<?php 
						} // for icnt
						
					} // if found_results
					else{
							echo '<tr><td class="main" align="center">' . TEXT_NO_RECORDS_FOUND . '</td></tr>';
					}
					?>
			</table>
			</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'); 
// function to generate pdf content
	function generate_pdf(){
		global $output_array,$display_header,$found_results,$category_list,$report_filename;
		//create pdf table class
		$table=new pdfTable("A4","l");
		//set margins
		$table->left_margin=20;
		$table->top_margin=20;
		$table->right_margin=20;
		$table->bottom_margin=20;
		$table->pdfInit();
		
		// create styles for output content
		$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");

		// add headers
		$table->headers["text"]=$display_header;
		$table->headers["style"]="query";
		$table->headers["height"]=12;
		$table->headers["width"]="100%";
		$table->headers["cols"]=3;

		// genreate coloumn widths
		$widths[0]=98;
		$widths[1]=95;
		$widths[2]=95;
		$widths[3]=95;
		$cols_head=array();
		// generate table header columns
		$cols_head[]=array("text"=>TABLE_HEADING_SERVICE,"width"=>$widths[0],"align"=>"L","style"=>"headrow","valign"=>"M");
		$cols_head[]=array("text"=>TABLE_HEADING_SERVICE_TOTAL_TIME,"width"=>$widths[1],"align"=>"L","style"=>"headrow","valign"=>"M");
		$cols_head[]=array("text"=>TABLE_HEADING_SALE_TOTAL_TIME,"width"=>$widths[2],"align"=>"L","style"=>"headrow","valign"=>"M");
		$cols_head[]=array("text"=>TEXT_PERCENTAGE,"width"=>$widths[3],"align"=>"L","style"=>"headrow","valign"=>"M");
		if ($found_results){
		// output table content columns
		$table->tableheaders["text"]=$cols_head;
		unset($cols_head); 
		$pre_catname="";
		for($icnt=0;$icnt<sizeof($output_array);$icnt++){
		  	$row=&$output_array[$icnt];
		  	$cols_head=array();
		  	if($pre_catname!=$row['cat_name']){
				$cols_head[]=array("text"=>$row['cat_name'],"width"=>$widths[0]+$widths[1]+$widths[2]+$widths[3],"align"=>"L","style"=>"subrowhead","valign"=>"M");
			   $pre_catname=$row['cat_name'];
			}
			if($cols_head) $table->OutputRow($cols_head,15);
			unset($cols_head);
			$cols=array();
			if ($row["type"]=="row"){
				$cols[]=array("text"=>$row['service_name'],"width"=>$widths[0],"align"=>"L","style"=>"row","valign"=>"M");
				$cols[]=array("text"=>$row['nbtot'],"width"=>$widths[1],"align"=>"L","style"=>"row","valign"=>"M");
				$cols[]=array("text"=>$row['booking_time'],"width"=>$widths[2],"align"=>"L","style"=>"row","valign"=>"M");
				$cols[]=array("text"=>$row["percentage"],"width"=>$widths[3],"align"=>"L","style"=>"row","valign"=>"M");
				$table->OutputRow($cols,15);
			}
			unset($cols);
		 }
		} else {
			$cols[]=array("text"=>TEXT_NO_RECORDS_FOUND,"width"=>"100%","align"=>"C","style"=>"subrow","valign"=>"M");
			$table->OutputRow($cols,15);
			unset($cols);
		}
		// output pdf file
		$table->Render($report_filename.".pdf");
	}
	
	//function to get excel format csv datas
	function generate_excel(){
		global $output_array,$found_results,$report_filename;
		if ($found_results){
		$result.=TABLE_HEADING_SERVICE. "," . TABLE_HEADING_SERVICE_TOTAL_TIME . "," .
				TABLE_HEADING_SALE_TOTAL_TIME . "," . TEXT_PERCENTAGE . "," . "\n";
		$pre_cate="";
		 for($icnt=0;$icnt<sizeof($output_array);$icnt++){
			$row=&$output_array[$icnt];
			if($pre_cate!=$row['cat_name']){
				$result.=$row['cat_name'] ."\n";
				$pre_cate=$row['cat_name'];	
			}
			if ($row["type"]=="row"){
				$result.=$row['service_name'] . "," . $row['nbtot']. "," . (($row['booking_time']>0)?$row['booking_time']:"-") . "," .
						 $row["percentage"]. "\n";
			} 
					
		 }
		} else {
			$result.=TEXT_NO_RECORDS_FOUND . "\n";
		}
		tep_write_text_file($report_filename.".csv",$result);
	}
 include(DIR_WS_INCLUDES."date_format_js.php");
 ?>