<?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/pdfTable.php');  
  require(DIR_WS_CLASSES . 'split_page_results_report.php');
  require(DIR_WS_FUNCTIONS . 'services_tree.php');
  define(BOX_WIDTH1,'125');

	// get initial parameters, first check to get previous settings from session if var "return" is present
	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());
		}
	}
	$date=isset($input_params['txtDate'])?tep_convert_date_raw($input_params['txtDate']):date('Y-m-d');
	$resource=isset($input_params['sel_resource'])?$input_params['sel_resource']:-1;
	$view=isset($input_params['sel_view'])?$input_params['sel_view']:1;
	$location=isset($input_params['sel_location'])?$input_params['sel_location']:-1;
	$service=isset($input_params['sel_service'])?$input_params['sel_service']:-1;
	$post_action = isset($input_params['post_action'])?$input_params['post_action']:'';
	$output_pdf=isset($input_params['chk_output_pdf'])?true:false;
	$output_excel=isset($input_params['chk_output_excel'])?true:false;
	$page = isset($input_params['page'])?$input_params['page']:1;
	
	$date_begin = $date;
	$date_end=$date;
	$days= date('t',strtotime($date));
	$search_header="";

	if ($post_action==""){
		$output_pdf=true;
		$post_action="screen";
	}	
	if($view==2) {
		$sql =  "select date_add('$date', interval -(dayofweek('$date')-1) day) begin, date_add('$date', interval (7-dayofweek('$date')) day) end";	
	} else if($view==3) {
		$sql =  "select date_add('$date', interval -(dayofmonth('$date')-1) day) begin, date_add('$date', interval ('$days'-dayofmonth('$date')) day) end";	
	}
	if($view==2 || $view==3) {
		$sql_result = tep_db_query($sql);
		$row = mysql_fetch_assoc($sql_result);
		$date_begin = $row['begin'];
		$date_end=$row['end'];
	}
	
	// create the header text for pdf output
	$display_header=TEXT_DAY .  ":&nbsp;&nbsp;" . $date . "&nbsp;&nbsp;&nbsp;&nbsp;";
	if ($view==1) {
		$display_header.=TEXT_VIEW .  ":&nbsp;&nbsp;" . TEXT_DAY . "\t";
	} else if ($view==2){
		$display_header.=TEXT_VIEW .  ":&nbsp;&nbsp;" . TEXT_WEEK . "\t";
	} else {
		$display_header.=TEXT_VIEW .  ":&nbsp;&nbsp;" . TEXT_MONTH . "\t";
	}
	if ($resource=="-1") $display_header.=TEXT_RESOURCE . ":&nbsp;&nbsp;" . TEXT_ALL_RESOURCE ."\t";
	if ($location=="-1") $display_header.=TEXT_LOCATION . ":&nbsp;&nbsp;" . TEXT_ALL_LOCATIONS  ."\t";
	if ($service=="-1") $display_header.=TEXT_SERVICE . ":&nbsp;&nbsp;" . TEXT_ALL_SERVICES  ."\t";
	
	$resources_array = array();
	$locations_array=array();

	$view_array=array(array("id"=>1,"text"=>TEXT_DAY),array("id"=>2,"text"=>TEXT_WEEK),array("id"=>3,"text"=>TEXT_MONTH));

	// get provider names
	/*$resource_option="";
	$resources_array[]=array("id"=>-1,"text"=>TEXT_ALL_PROVIDERS);
	 $sql = "select  provider_id, provider_firstname, provider_lastname  from ".TABLE_SERVICES_PROVIDERS." sep order by provider_firstname,provider_lastname";
	$sql_result = tep_db_query($sql);
	$num_rows = tep_db_num_rows($sql_result);
	
	while($row=tep_db_fetch_array($sql_result)) {
		$resources_array[]=array("id"=>$row["provider_id"],"text"=>$row["provider_firstname"]." ".$row["provider_lastname"]);
		if ($row["provider_id"]==$resource){
			$display_header.=TEXT_PROVIDER  . ":&nbsp;&nbsp;" . $row["provider_firstname"]." ".$row["provider_lastname"] . "\t";
		}
	}
	mysql_free_result($sql_result);*/
	
	// get resource names			
	$sql = "select r.resource_id resource_id, rd.resource_name  from " . TABLE_RESOURCES . " r, " . TABLE_RESOURCES_DESCRIPTION . " rd where r.resource_id=rd.resource_id order by resource_name";
	$sql_result = tep_db_query($sql);
	$num_rows = tep_db_num_rows($sql_result);
	$resources_array[]=array("id"=>-1,"text"=>TEXT_ALL_RESOURCE);
	while($row=tep_db_fetch_array($sql_result)) {
		$resources_array[]=array("id"=>$row["resource_id"],"text"=>$row["resource_name"]);
	}
	mysql_free_result($sql_result);
		
	// get location names
	$sql = "select distinct se.service_location as service_location ,l.service_location_name as location_name,l.service_location_id as location_id 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);
	$locations_array[]=array("id"=>-1,"text"=>TEXT_ALL_LOCATIONS);
	while($row=tep_db_fetch_array($sql_result)) {
		$locations_array[]=array("id"=>$row["service_location"],"text"=>$row["location_name"]);
		if ($row["location_id"]==$location){
			$display_header.=TEXT_LOCATION  . ":&nbsp;&nbsp;" . $row["location_name"] . "\t";
		}
	}
	mysql_free_result($sql_result);
	// serv	ice list	
	$sql = "select se.service_id service_id, service_name  service_name from services se, services_description sed where se.service_id = sed.service_id and sed.language_id='" . $languages_id ."'" . SERVICES_CONDITION_STATUS .  " order by service_name";
	$sql_result = tep_db_query($sql);
	while($row=tep_db_fetch_array($sql_result)) {
		if ($row["service_id"]==$service){
			$display_header.=TEXT_SERVICE  . ":&nbsp;&nbsp;" . $row["service_name"];
		}
	}
	mysql_free_result($sql_result);
	
	$found_results=false;
	// build the output if needed
		$where = " ";
		if($resource!="-1") {
			$where.= "and os.resource_id='".$resource."' ";
		} 
		if($location!="-1") {
			$where.= "and se.service_location='".$location."' ";
		}
		if($service!="-1") {
			$where.= "and se.service_id='".$service."' ";
		}
		
		// create the query to fetch the attendees details for every service
	/*	$sql = "select time_format(os.start_time,'%h:%i %p') as start_time,time_format(os.end_time,'%h:%i %p')as end_time,se.service_id service_id, concat(c.customers_firstname,' ',c.customers_lastname) as customers_name,c.customers_id,
		c.customers_email_address customers_email_address, c.customers_telephone customers_telephone, c.customers_fax,	se.provider_id, se.service_location service_location, sed.service_name service_name,
		start_date , end_date from " .  TABLE_ORDERS_SERVICES . " os, " . TABLE_CUSTOMERS . " c, "  . TABLE_SERVICES_DESCRIPTION . " sed," .	TABLE_SERVICES . " se, " . TABLE_ORDERS_PRODUCTS . " op, " .TABLE_ORDERS." o ,". TABLE_ADMIN . " a  where sed.language_id='" . $languages_id . "'
		and os.products_id = se.service_id and o.customers_id=c.customers_id and o.orders_id=os.orders_id and op.orders_id=o.orders_id and sed.service_id=se.service_id and ((os.start_date>='$date_begin' and os.start_date<='$date_end') or (os.start_date>='$date_begin' and os.end_date<='$date_end'))  ".$where." group by c.customers_id,se.service_id order by service_name,os.start_date,a.admin_firstname desc ";
		*/
		$sql = "select time_format(os.start_time,'%h:%i %p') as start_time,time_format(os.end_time,'%h:%i %p')as end_time,se.service_id service_id, concat(c.customers_firstname,' ',c.customers_lastname) as customers_name,c.customers_id,
		c.customers_email_address customers_email_address, c.customers_telephone customers_telephone, c.customers_fax, se.service_location service_location, sed.service_name service_name,
		start_date , end_date from " .  TABLE_ORDERS_SERVICES . " os, " . TABLE_CUSTOMERS . " c, "  . TABLE_SERVICES_DESCRIPTION . " sed," .	TABLE_SERVICES . " se, " . TABLE_ORDERS_PRODUCTS . " op, " .TABLE_ORDERS." o ,". TABLE_ADMIN . " a  where sed.language_id='" . $languages_id . "'
		and os.products_id = se.service_id and o.customers_id=c.customers_id and o.orders_id=os.orders_id and op.orders_id=o.orders_id and sed.service_id=se.service_id and ((os.start_date>='$date_begin' and os.start_date<='$date_end') or (os.start_date>='$date_begin' and os.end_date<='$date_end'))  ".$where." group by c.customers_id,se.service_id order by service_name,os.start_date,a.admin_firstname desc ";

		if ($post_action=="screen"){
			// split the result
			$query_split = new splitPageResultsReport($page, REPORT_MAX_ROWS_PAGE, $sql, $query_split_numrows);
		}
	
		$sql_result=tep_db_query($sql);
		$num_rows=tep_db_num_rows($sql_result);
		if ($num_rows>0) $found_results=true;
	
		$report_filename=sprintf("services_attendees_%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;
		}
		tep_delete_temp_files("services_attendees_" . $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>
<?php include(DIR_WS_INCLUDES."date_format_js.php")?>

<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();
	}
</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_SERVICES_ATTENDEES;?></td>!-->	
		</tr>
        <tr >
		  	<TD>
			<?php 	echo tep_draw_form("f",FILENAME_SERVICES_ATTENDEES);
					echo tep_draw_hidden_field("post_action","yes");
			?>
			<table border="0" cellspacing="3" cellpadding="2" class="searchArea" width="100%">
				<tr>
				    <td align="left" width="7%"><?php echo TEXT_DAY;?></td>
					<td  nowrap="true"><?php $_array=array('d','m','Y');  $replace_array=array('DD','MM','YYYY'); 	$date_format=str_replace($_array,$replace_array,EVENTS_DATE_FORMAT);?>
						<?php echo  tep_draw_input_field("txtDate",format_date($date),'maxlength="10" size="10"');?>
						<a href="javascript:show_calendar('f.txtDate',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="true">
						<?php    
							echo TEXT_RESOURCE . '&nbsp;' . tep_draw_pull_down_menu("sel_resource",$resources_array,$resource);
							echo '&nbsp;&nbsp;&nbsp;&nbsp;' . TEXT_VIEW . '&nbsp;' . tep_draw_pull_down_menu("sel_view",$view_array,$view);
						?>
					</td>
					<td valign="middle" align="right">
						<?php echo '<a href="javascript:doReport(1);">' . tep_image_button('button_report_search.gif', IMAGE_SEARCH_DETAILS) . '</a>'; ?>
					</td>
				</tr>
				<tr>
				<td nowrap="true" colspan="2">
					<?php echo TEXT_LOCATION . '&nbsp;&nbsp;' . tep_draw_pull_down_menu("sel_location",$locations_array,$location);?>
				</td>
				<td nowrap="true">


					<?php 	
						$all_service_array=array();
						$all_service_array = tep_get_service_array_single();
						if(sizeof($all_service_array)<=0) echo 'Services are not Available';
						else echo TEXT_SERVICE . '&nbsp;&nbsp;&nbsp;' . tep_draw_service_select_menu('sel_service',$all_service_array,$service,' id="sel_service"');
					?>
				</td>


				<td valign="middle" align="right">
						<?php echo '<a href="javascript:doReport(2);">' . tep_image_button('button_export_pdf.gif', IMG_EXPORT_PDF) . '</a>';?>
				</td>
				</tr>
				<tr>
				<td valign="middle" align="right" colspan="4">
					<?php echo '<a href="javascript:doReport(3);">' . tep_image_button('button_export_excel.gif', IMG_EXPORT_EXCEL) . '</a>';?>
					</td>
				</tr>

			<!-- report content -->
		</table>
		</form>
		</td>
		</tr>
		<tr ><td class="cell_bg_report_header">&nbsp;</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>
		<tr>
		<td>
	   <table border="0" width="100%" cellspacing="1" cellpadding="1">
			<tr class='dataTableHeadingTitleRow'>
				<td class='dataTableHeadingTitleContent' width="30"><?php echo TEXT_ID;?></td>
				<td class='dataTableHeadingTitleContent'><?php echo TEXT_NAME;?></td>
				<td class='dataTableHeadingTitleContent' width="100"><?php echo TEXT_PHONE;?></td>
				<td class='dataTableHeadingTitleContent' width="50"><?php echo TEXT_EMAIL;?></td>
				<td class='dataTableHeadingTitleContent' width="85"><?php echo TEXT_START;?></td>
				<td class='dataTableHeadingTitleContent' width="85"><?php echo TEXT_START_TIME;?></td>
				<td class='dataTableHeadingTitleContent' width='85'><?php echo TEXT_END_TIME;?></td>
			</tr>
			<tr height="10"></tr>
			<?php
				   $i=1;
					$prev_services_id=0;
					mysql_data_seek($sql_result,0);
          			while($row = tep_db_fetch_array($sql_result)) {
						$services_id=$row["service_id"];
						if ($prev_services_id!=$services_id) { // output service name
						?>
					<tr>
						<td class="dataTableContent" style="cursor:hand" colspan="7" height="20">
							<b><?php
								echo $row["service_name"];
								$prev_services=$services_id;
							?></b>
						</td>
					</tr>
					<?php }?>
                  <tr class='dataTableRow' height="20" onMouseOver="rowOverEffect(this)" onMouseOut="rowOutEffect(this)" onClick="javascript:location.href='customers.php?cID=<?php echo $row["customers_id"];?>&action=edit&return=rst&type=ser';">
					  <td class='dataTableContent' style="cursor:hand"><?php echo $i?></td>
					  <td class='dataTableContent' style="cursor:hand"><?php echo $row['customers_name']?></td>
					  <td class='dataTableContent' style="cursor:hand"><?php echo $row['customers_telephone']?></td>
					  <td class='dataTableContent' style="cursor:hand" align="center"> <a href="mailto:<?php echo $row['customers_email_address']?>"><img src="images/icons/mail.gif" border="0"></a></td>
					  <td class='dataTableContent' style="cursor:hand"><?php echo format_date($row['start_date']);?></td>
					  <td class='dataTableContent' style="cursor:hand"><?php echo $row['start_time'];?></td>
                      <td class='dataTableContent' style="cursor:hand"><?php echo $row['end_time'];?></td>
				  </tr>
			<?php 
						$i++;						
					}	
				}
				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 $sql_result,$display_header,$found_results,$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();
		
		// 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"]=12;
		$table->headers["width"]="100%";
		$table->headers["cols"]=3;

		// define column width
		$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->width-($widths[0]+$widths[2]+$widths[3]+$widths[4]+$widths[5]);

		if ($found_results){
		// output header details
		$cols=array();
		$cols[]=array("text"=>TEXT_ID,"width"=>$widths[0],"align"=>"L","style"=>"headrow","valign"=>"M");
		$cols[]=array("text"=>TEXT_NAME,"width"=>$widths[1],"align"=>"L","style"=>"headrow","valign"=>"M");
		$cols[]=array("text"=>TEXT_PHONE,"width"=>$widths[2],"align"=>"L","style"=>"headrow","valign"=>"M");
		$cols[]=array("text"=>TEXT_START,"width"=>$widths[3],"align"=>"L","style"=>"headrow","valign"=>"M");
		$cols[]=array("text"=>TEXT_START_TIME,"width"=>$widths[4],"align"=>"L","style"=>"headrow","valign"=>"M");
		$cols[]=array("text"=>TEXT_END_TIME,"width"=>$widths[5],"align"=>"L","style"=>"headrow","valign"=>"M");
		$table->tableheaders["text"]=$cols;
		unset($cols);
		$i=1;
		mysql_data_seek($sql_result,0);
		$prev_services_id=0;
		// output content
		while($row = tep_db_fetch_array($sql_result)) {
			$services_id=$row["service_id"];
			if ($prev_services_id!=$services_id) {
				$cols=array();
				$cols[]=array("text"=>$row["service_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;
			}
			$cols=array();
			$cols[]=array("text"=>$i,"width"=>$widths[0],"align"=>"L","style"=>"row","valign"=>"M");
			$cols[]=array("text"=>$row['customers_name'],"width"=>$widths[1],"align"=>"L","style"=>"row","valign"=>"M");
			$cols[]=array("text"=>$row['customers_telephone'],"width"=>$widths[2],"align"=>"L","style"=>"row","valign"=>"M");
			$cols[]=array("text"=>format_date($row['start_date']),"width"=>$widths[3],"align"=>"L","style"=>"row","valign"=>"M");
			$cols[]=array("text"=>$row['start_time'],"width"=>$widths[4],"align"=>"L","style"=>"row","valign"=>"M");
			$cols[]=array("text"=>$row['end_time'],"width"=>$widths[5],"align"=>"L","style"=>"row","valign"=>"M");
			$table->OutputRowMultiple($cols,15);
			unset($cols);
			$i++;						
		}
		} 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 $sql_result,$found_results,$report_filename;
		$result="";
		$i=1;
		if ($found_results){
		$result.=TEXT_ID . "," . TEXT_NAME . "," . TEXT_PHONE . "," . TEXT_START . "," . TEXT_START_TIME . "," . TEXT_END_TIME . "\n";
		mysql_data_seek($sql_result,0);
		$prev_services_id=0;
		while($row = tep_db_fetch_array($sql_result)) {
			$services_id=$row["service_id"];
			if ($prev_services_id!=$services_id) {
				$result.=$row["service_name"] . "\n";
				$prev_services_id=$services_id;
			}
			$result.=$i . "," .$row['customers_name']. "," . $row['customers_telephone'] . "," .
					format_date($row['start_date']) . "," . $row['start_time'] . "," .$row['end_time'] . "\n";
			$i++;						
		}
		} else {
			$result.=TEXT_NO_RECORDS_FOUND . "\n";
		}
		tep_write_text_file($report_filename.".csv",$result);
	}
?>