<?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/currencies.php');
	require('includes/classes/pdfTable.php');  
	define(BOX_WIDTH,'125');
	$currencies=new currencies();
	$category=(isset($HTTP_POST_VARS["cbo_category"])?$HTTP_POST_VARS["cbo_category"]:-1);
	$post_action=(isset($HTTP_POST_VARS["post_action"])?$HTTP_POST_VARS["post_action"]:'');
	$page=(isset($HTTP_POST_VARS["page"])?$HTTP_POST_VARS["page"]:1);
	$totals_only = (isset($input_params['chk_totals_only'])?true:false);
	if ($post_action==""){
		$output_pdf=true;
		$post_action="screen";
	}	
	$found_results=false;
	if ($category=="-1") $display_header.=TEXT_CATEGORY . ":&nbsp;&nbsp;" . TEXT_ALL_CATEGORIES  ."\t";
   $where="";
	if ($category!=-1){
		$where.=" and se2c.services_categories_id='" . $category . "' ";
		$display_header.= TEXT_CATEGORY .'&nbsp;'.tep_get_service_category_name($category,$languages_id);
	}
	$service_sql="SELECT distinct se.service_id,se.service_costs,se.include_resource_price,se.include_resource,secd.services_categories_id,secd.services_categories_name,sed.service_name,se.quantity, se.booking_type, se.service_total_time from services_to_services_categories se2c,services_categories_description secd, services_description sed , services se where se2c.service_id=se.service_id and (se.service_status>=1) and date_format(se.service_date_available,'%Y-%m-%d')<=curdate() and  se.service_id=sed.service_id and se2c.services_categories_id=secd.services_categories_id " . $where .SERVICES_CONDITION_STATUS." and sed.language_id='".(int)$languages_id."' and secd.language_id='".(int)$languages_id ."' order by secd.services_categories_name,sed.service_name";
    if ($post_action=="screen"){
		$query_split = new splitPageResultsReport($page, REPORT_MAX_ROWS_PAGE, $service_sql, $query_split_numrows);
	}
	$service_query=tep_db_query($service_sql);
	// split the content to multiple pages
	if (tep_db_num_rows($service_query)>0)  $found_results=true;
	//get category list
	$category_array=array();
	$category_array[]=array("id"=>-1,"text"=>TEXT_ALL_CATEGORIES);
	$category_query=tep_db_query("SELECT  secd.services_categories_name,sec.services_categories_id from " . TABLE_SERVICES_CATEGORIES . " sec, " . TABLE_SERVICES_CATEGORIES_DESCRIPTION . " secd " . 
						"where sec.services_categories_id=secd.services_categories_id and secd.language_id='" . (int)$languages_id . "' order by secd.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"]);
	}
	$report_filename=sprintf("services_forsale_%s_%s",$login_id,time());
	// output the pdf 
	if ($post_action=="pdf"){ 
		generate_pdf();
		tep_redirect(DIR_WS_CATALOG . "images/".$report_filename.".pdf");
		return;
	}   
	// output the excel
	if ($post_action=="excel"){ 
		generate_excel();
		tep_redirect(DIR_WS_CATALOG . "images/".$report_filename.".csv");
		return;
	}
?>
<!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>
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF">
<!-- 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_SERVICES_FORSALE;?></td>!-->
			</tr>
			<tr>
				<td class="pageHeading"></td>
			</tr>
			<!-- --->
			<script language="JavaScript">
			
	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 {
			document.f.post_action.value="screen";
			document.f.target="_self";
		}
		document.f.submit();
	}
				//function doSubmit() {
					//document.f.submit();
				//}
			     
				function f_Print() {
					document.f.layout.value="print";
					document.f.target="_blank";
					document.f.submit();
				}
			</script>
			<?php 
				echo tep_draw_form("f",FILENAME_SERVICES_FORSALE);
				echo tep_draw_hidden_field("post_action","screen");
			?>
			<tr>
				<td>
				<table cellspacing="0" cellpadding="0" width="100%" border="0">
					<tr>
						<td>
						<table width="100%" border="0" cellpadding="2" cellspacing="3" class="searchArea">
							<tr class="main">
								<td nowrap>
								<?php 
									echo TEXT_CATEGORY . '&nbsp;' . tep_draw_pull_down_menu("cbo_category",$category_array,$category);
								?>
								</td>
								<td align="right">
									<?php echo '<a href="javascript:doReport(1);">' . tep_image_button('button_report_search.gif', IMAGE_SEARCH_DETAILS) . '</a>'; ?></td>
									<TD>
					<?php echo tep_draw_separator('pixel_trans.gif',1,5); ?>
				</TD>
				<td align="right">
					<?php echo '<a href="javascript:doReport(2);">' . tep_image_button('button_export_pdf.gif', IMG_EXPORT_PDF) . '</a>';?>
				</td>
				<TD>
					<?php echo tep_draw_separator('pixel_trans.gif',1,5); ?>
				</TD>
				<td align="right">
				<?php echo '<a href="javascript:doReport(3);">' . tep_image_button('button_export_excel.gif', IMG_EXPORT_EXCEL) . '</a>';?>
				</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) { ?>
					<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>
			<?php	$prev_cat_id=0;
					$prev_id=0;
					$icnt=1;
					while($row=tep_db_fetch_array($service_query)){
						$service_costs=$row["service_costs"];
						$tax_rate=tep_get_tax_rate($row["service_tax_class_id"]);
						$service_total=tep_add_tax($service_costs,$tax_rate);
						//calculate period
						if ($row["booking_type"]=="H" || $row["booking_type"]!="H"){
							$period=$row['service_total_time'];
							$quantity=$row['quantity'];
						}
						if ($prev_cat_id!=$row["services_categories_id"]) { // output category
						  if ($prev_cat_id!=0){
			?>
							</table>
							</td>
							</tr>
							<tr>
								<td><?php echo tep_draw_separator('pixel_trans.gif',1,10);?></td>
							</tr>
							<?php } ?>
							<tr height="50" class="dataTableHeadingRow">
								<td>
								<table border="0" cellpadding="2" cellspacing="0" width="100%">
									<tr>
										<td class="dataTableHeadingContent">
											<?php 
												echo $row["services_categories_name"];
											?>
										</td>
									</tr>
								</table>
								</td>
							</tr>
							<tr>
							<td>
							<table border="0" cellpadding="2" cellspacing="0" width="100%">
								<tr class='dataTableHeadingTitleRow'>
								  <TD class='dataTableHeadingTitleContent' width="650" ><?php echo TEXT_SERVICE_NAME;?></TD>
								  <TD class='dataTableHeadingTitleContent' width="250" ><?php //echo TEXT_START_TIME;?><?php echo 'Minimum Period';?></TD>
								  <TD class='dataTableHeadingTitleContent' width="100"><?php //echo TEXT_END_TIME;?><?php echo 'Quantity';?></TD>
								  <TD class='dataTableHeadingTitleContent' width="150"><?php echo 'Resource Name';?></TD>
								  <TD class='dataTableHeadingTitleContent' width="100"><?php echo 'Price';?></TD>
								</tr>
								<tr height="10"></tr>
						<?php 
							$prev_cat_id=$row["services_categories_id"];
						}   ?><?php if($row['include_resource']=="N"){ ?>
							<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=' . $row['service_id'] . '&origin=' . FILENAME_SERVICES_FORSALE  . '?page=' . $HTTP_GET_VARS['page'], 'NONSSL'); ?>'">
								<td class='dataTableContent'><?php  echo $row['service_name']; ?></td>
								<TD  class='dataTableContent'><?php echo $period;?></TD>
								<TD class='dataTableContent'><?php  echo $quantity;?></TD>
								<TD align="center" class='dataTableContent'><?php if($row['include_resource']=="Y") echo $resource_name; ?><?PHP if($row['include_resource']=="N") echo '-';?></TD>
								<td class='dataTableContent'><?PHP echo ($row['include_resource_price']=='N')?(int)$row['service_costs']:(int)$resource_price; ?></td>
							</TR>
						<?php
						   }else{ 
							$resource=$row['service_id']; 
							//echo "select distinct ssr.service_resource_id,r.resource_id,r.resource_costs,re.resource_name from services_to_services_resources ssr,resources r,resources_description re where ssr.service_id='".$resource."' and ssr.service_resource_id=r.resource_id and r.resource_id=re.resource_id"; 
							$resource_query=tep_db_query("select distinct ssr.service_resource_id,r.resource_id,r.resource_costs,re.resource_name from services_to_services_resources ssr,resources r,resources_description re where ssr.service_id='".$resource."' and ssr.service_resource_id=r.resource_id and r.resource_id=re.resource_id");  
							while($resource_result=tep_db_fetch_array($resource_query)){?>
							<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=' . $row['service_id'] . '&origin=' . FILENAME_SERVICES_FORSALE  . '?page=' . $HTTP_GET_VARS['page'], 'NONSSL'); ?>'">
							<?PHP //if($prev_id!=$row['service_id']){	?>
								<td class='dataTableContent'><?php echo $row['service_name']; ?></td>
								<TD class='dataTableContent'><?php echo $period;?></TD>
								<TD class='dataTableContent'><?php echo '-';?></TD>
								<?PHP //} else { ?>
									<!--		<TD class='dataTableContent'></TD> 
											<TD class='dataTableContent'></TD>
											<TD class='dataTableContent'><?PHP echo '-';?></TD> -->
								<?PHP //} 
								     //  $prev_id=$row['service_id']; ?>			
								<TD align="center" class='dataTableContent'><?php if($row['include_resource']=="Y") echo $resource_result['resource_name']; ?></TD>
								<td class='dataTableContent'><?PHP echo ($row['include_resource_price']=='N')?(int)$row['service_costs']: (int)$resource_result['resource_costs']; ?></td>
							</TR>					
						 <?PHP  }
							}//if resource
						}
					}
					 // if found_results
				//echo $resource;
				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 create pdf details
	function generate_pdf(){
		global $service_query,$display_header,$found_results,$currencies,$report_filename;
         
		// 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();
		
		//Date
		$current_date=Date("d-m-Y");

		
		// 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.'Date:'.$current_date;
		$table->headers["style"]="query";
		$table->headers["height"]=14;
		$table->headers["width"]="100%";
		$table->headers["cols"]=3;
	
		// define column width
		
		$widths[0]=40;
		$widths[2]=80;
		$widths[3]=80;
		$widths[4]=80;
		$widths[5]=150;
		$widths[6]=10;
		/*$widths[0]=$table->width*5/100;
		$widths[2]=$table->width*10/100;
		$widths[3]=$table->width*10/100;
		$widths[4]=$table->width*10/100;
		//$widths[5]=$table->width*10/100;*/
        
		$widths[1]=$table->$widths[0]+$widths[2]+$widths[3]+$widths[4]+$widths[5];

		// output header details
		$cols=array();
		
		if ($found_results){
		$cols[]=array("text"=>TEXT_SERVICE_NAME,"width"=>$widths[1],"align"=>"L","style"=>"headrow","valign"=>"M");
		$cols[]=array("text"=>TEXT_PERIOD,"width"=>$widths[4],"align"=>"L","style"=>"headrow","valign"=>"M");
		$cols[]=array("text"=>'Quantity',"width"=>$widths[2],"align"=>"L","style"=>"headrow","valign"=>"M");
		$cols[]=array("text"=>'Resource Name',"width"=>$widths[5],"align"=>"L","style"=>"headrow","valign"=>"M");
		$cols[]=array("text"=>TEXT_PRICE,"width"=>$widths[5],"align"=>"L","style"=>"headrow","valign"=>"M");
		$table->tableheaders["text"]=$cols;
		unset($cols);
		$i=1;
		mysql_data_seek($service_query,0);
		$prev_services_id=0;
		$prevs_id=0;
		// output content
		while($row = tep_db_fetch_array($service_query)) {
			$services_id=$row["services_categories_id"];
		if ($prev_services_id!=$services_id) {
				$cols=array();
				$cols[]=array("text"=>$row["services_categories_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_services_id=$services_id;
			}
						$period = $row["service_total_time"];
						$service_costs=$row["service_costs"];
						$tax_rate=tep_get_tax_rate($row["service_tax_class_id"]);
						$service_total=tep_add_tax($service_costs,$tax_rate);
			if($row['include_resource']=="N"){
			$cols=array();
			
			$cols[]=array("text"=>$row['service_name'],"width"=>$widths[1],"align"=>"L","style"=>"row","valign"=>"M");
		    $cols[]=array("text"=>$period,"width"=>$widths[4],"align"=>"L","style"=>"row","valign"=>"M");
			$cols[]=array("text"=>$row['quantity'],"width"=>$widths[2],"align"=>"L","style"=>"row","valign"=>"M");
			$cols[]=array("text"=>'-',"width"=>$widths[5],"align"=>"L","style"=>"row","valign"=>"M");
			$cols[]=array("text"=>(int)$row['service_costs'],"width"=>$widths[5],"align"=>"L","style"=>"row","valign"=>"M");
			$table->OutputRow($cols,15);
			unset($cols);
			
			}//if include_resource 
			else{
				$cols=array();
				$resources=$row['service_id'];
				$resources_query=tep_db_query("select ssr.service_resource_id,r.resource_id,r.resource_costs,re.resource_name from services_to_services_resources ssr,resources r,resources_description re where ssr.service_id='".$resources."' and ssr.service_resource_id=r.resource_id and r.resource_id=re.resource_id");  
				while($resources_result=tep_db_fetch_array($resources_query)){
						
						//if($prevs_id!=$row['service_id']){
						$cols[]=array("text"=>$row['service_name'],"width"=>$widths[1],"align"=>"L","style"=>"row","valign"=>"M");
		    			$cols[]=array("text"=>$period,"width"=>$widths[4],"align"=>"L","style"=>"row","valign"=>"M");
						$cols[]=array("text"=>'-',"width"=>$widths[2],"align"=>"L","style"=>"row","valign"=>"M");
						//} else{
				/*		$cols[]=array("text"=>'',"width"=>$widths[5],"align"=>"L","style"=>"row","valign"=>"M");
		    			$cols[]=array("text"=>'',"width"=>$widths[5],"align"=>"L","style"=>"row","valign"=>"M");
					    $cols[]=array("text"=>'',"width"=>$widths[4],"align"=>"L","style"=>"row","valign"=>"M");
						$cols[]=array("text"=>'',"width"=>$widths[4],"align"=>"L","style"=>"row","valign"=>"M");
						$cols[]=array("text"=>'',"width"=>$widths[6],"align"=>"L","style"=>"row","valign"=>"M");
    					$cols[]=array("text"=>'',"width"=>$widths[4],"align"=>"L","style"=>"row","valign"=>"M");
						}$prevs_id=$row['service_id'];*/	
						$cols[]=array("text"=>$resources_result['resource_name'],"width"=>$widths[5],"align"=>"L","style"=>"row","valign"=>"M");
						$cols[]=array("text"=>(($row['include_resource_price']=='N')?(int)$row['service_costs']:(int)$resource_result['resource_costs']),"width"=>$widths[5],"align"=>"L","style"=>"row","valign"=>"M");
						$table->OutputRow($cols,15);
						unset($cols);
						
			  } //inside else while
			}//else include_resource						
		  $i++; //main while
		  }
		} else {
			$cols[]=array("text"=>TEXT_NO_RECORDS_FOUND,"width"=>"100%","align"=>"C","style"=>"subrow","valign"=>"M");
			$table->OutputRow($cols,15);
			unset($cols);
		}
		// create pdf page
		$table->Render($report_filename.".pdf");
	}
	function generate_excel(){
		global $service_query,$found_results,$currencies,$report_filename;
		$result="";
		$i=1;
		if ($found_results){
		$result.=TEXT_SERVICE_NAME . ","  . TEXT_PERIOD . ", Quantity , Resource Name, Price"."\n";

		mysql_data_seek($service_query,0);
		$prev_services_id=0;
		$prevs_id=0;
		while($row = tep_db_fetch_array($service_query)) {
			$services_id=$row["services_categories_id"];
			if ($prev_services_id!=$services_id) {
				$result.=$row["services_categories_name"] . "\n";
				$prev_services_id=$services_id;
			}
						$period = $row["service_total_time"];
						$service_costs=$row["service_costs"];
						$tax_rate=tep_get_tax_rate($row["service_tax_class_id"]);
						$service_total=tep_add_tax($service_costs,$tax_rate);
						$total=$currencies->format($service_total);
			
			if($row['include_resource']=="N"){
			$result.=sprintf("\"%s\",\"%s\",\"%s\",\"%s\",\"%s\"\n",$row['service_name'],$period,$row['quantity'],'-',$row["service_costs"]);
			}
			else{
				$resources=$row['service_id'];
				$resources_query=tep_db_query("select ssr.service_resource_id,r.resource_id,r.resource_costs,re.resource_name from services_to_services_resources ssr,resources r,resources_description re where ssr.service_id='".$resources."' and ssr.service_resource_id=r.resource_id and r.resource_id=re.resource_id");  
				while($resources_result=tep_db_fetch_array($resources_query)){
					if($prevs_id!=$row['service_id'])
						$result.=sprintf("\"%s\",\"%s\",\"%s\",\"%s\",\"%s\"\n",$row['service_name'],$period,'-',$resources_result['resource_name'],(($row['include_resource_price']=='N')?(int)$row['service_costs']:(int)$resource_result['resource_costs']));
				       // $prevs_id=$row['service_id'];
					//else
					   //	$result.=sprintf("\"%s\",\"%s\",\"%s\",\"%s\",\"%s\"\n",'','','',$resources_result['resource_name'],$resources_result["resource_costs"]);
				} //inside else while
			} // else 
			$i++;						
		}//main while
		} else {
			$result.=TEXT_NO_RECORDS_FOUND . "\n";
		}
		tep_write_text_file($report_filename.".csv",$result);
	}



?>