<?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_FUNCTIONS . 'events_tree.php');
  define(BOX_WIDTH1,'125');

// get initial parameters
	$date=isset($HTTP_POST_VARS['txtDate'])?tep_convert_date_raw($HTTP_POST_VARS['txtDate']):'';
	$instructor=isset($HTTP_POST_VARS['sel_instructor'])?$HTTP_POST_VARS['sel_instructor']:-1;
	$view=isset($HTTP_POST_VARS['sel_view'])?$HTTP_POST_VARS['sel_view']:3;
	$location=isset($HTTP_POST_VARS['sel_location'])?$HTTP_POST_VARS['sel_location']:-1;
	$event=isset($HTTP_POST_VARS['sel_event'])?$HTTP_POST_VARS['sel_event']:-1;
	$post_action = isset($HTTP_POST_VARS['post_action'])?$HTTP_POST_VARS['post_action']:'';

	
	if ($date==""){
		$sql =  "select date_sub('".getServerDate()."', interval 1 month) begin";
		$sql_result = tep_db_query($sql);
		$row = mysql_fetch_assoc($sql_result);
		$date = $row["begin"];
	}
	$date_begin = $date;
	$date_end = $date;
	
	$days= date('t',strtotime($date));
	
	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) { 
		$sql =  "select date_sub('$date', interval 7 day) begin";
	} else if($view==3) {
		$sql =  "select date_sub('$date', interval 1 month) begin";
	}*/
	
	if($view==2 || $view==3) {
		$sql_result = tep_db_query($sql);
		$row = mysql_fetch_assoc($sql_result);
		$date_begin = $row["begin"];
		
		//echo $date_begin;
		$date_end = $row["end"];
		//echo $date_end;
	}

	$instructors_array = array();
	$events_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 instructor names			
	$sql = "select a.admin_id admin_id, admin_firstname, admin_lastname  from events_instructors ei, admin a where ei.admin_id=a.admin_id";
	$sql_result = tep_db_query($sql);
	$num_rows = tep_db_num_rows($sql_result);
	$instructors_array[]=array("id"=>-1,"text"=>TEXT_ALL_INSTRUCTORS);
	while($row=tep_db_fetch_array($sql_result)) {
		$instructors_array[]=array("id"=>$row["admin_id"],"text"=>$row["admin_firstname"]." ".$row["admin_lastname"]);
	}
	mysql_free_result($sql_result);
		
	// get location names
	$sql = "select distinct location_id, location_name from events,events_locations where location_id=events_location order by 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["location_id"],"text"=>$row["location_name"]);
	}
	mysql_free_result($sql_result);
	// Get Events List	
	$events_option="";
	if ($login_groups_type==TEXT_INSTRUCTOR_ENTRY) $events_option=" and e.events_instructor='" . $login_id . "'";
	$sql = "select e.events_id events_id, Events_name  events_name from events e, events_description ed where e.events_id = ed.events_id and ed.language_id='" . (int)$languages_id . "'" . EVENTS_CONDITION_STATUS . $events_option . "order by events_name";
	$sql_result = tep_db_query($sql);
	$events_array[]=array("id"=>-1,"text"=>TEXT_ALL_EVENTS);
	while($row1=tep_db_fetch_array($sql_result)) {
		$events_array[]=array("id"=>$row1["events_id"],"text"=>$row1["events_name"]);
	}
	mysql_free_result($sql_result);
	
	$found_results=false;
	// build the output if needed
		$where = " ";
		if($instructor!="-1") {
			$where.= "and e.events_instructor='".$instructor."' ";
		} 
		if($location!="-1") {
			$where.= "and e.events_location='".$location."' ";
		}
		if($event!="-1") {
			$where.= "and e.events_id='".$event."' ";
		}

		// fetch the attendance details 
		$sql = "select count(*) as total_reserved,es.start_date,e.events_id events_id,es.sessions_id,ed.events_name events_name,time_format(e.events_start_time,'%h:%i %p') as start_time
		from " . TABLE_EVENTS_SESSIONS . " es, " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_EVENTS . " e, " . TABLE_EVENTS_DESCRIPTION . " ed 
		where es.sessions_id = op.products_id and e.events_id=es.events_id and products_type='E' and ed.events_id=e.events_id and es.start_date>='$date_begin' and es.start_date<='$date_end'
		and (events_type='R') ".$where." and ed.language_id='" . (int)$languages_id . "' group by events_id,es.start_date order by es.start_date,events_name,e.events_start_time ";
       //echo $sql;
		// fetch the total reserved users for the selected event
		$grand_sql = "select count(*) as event_total,es.start_date from " . TABLE_EVENTS_SESSIONS . " es, " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_EVENTS . " e, " . TABLE_EVENTS_DESCRIPTION . " ed 
		where es.sessions_id = op.products_id and e.events_id=es.events_id and products_type='E' and ed.events_id=e.events_id and es.start_date>='$date_begin' and es.start_date<='$date_end'
		and (events_type='R') ".$where . " and ed.language_id='" . (int)$languages_id . "' group by es.start_date";
		
		$grand_query=tep_db_query($grand_sql);
		$grand_array=array();
		while($grand_result=tep_db_fetch_array($grand_query)){
			$grand_array[$grand_result["start_date"]]=$grand_result["event_total"];
		}
		$sql_result=tep_db_query($sql);
		$num_rows=tep_db_num_rows($sql_result);
		if ($num_rows>0) $found_results=true;
?>
<!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="10">
			<!--<td class="pageHeading"><?php //echo HEADING_ATTENDANCE_ANALYSIS;?></td>!-->	
		</tr>
        <tr >
		  	<TD>
			<?php 	echo tep_draw_form("f",FILENAME_ATTENDANCE_ANALYSIS);
					echo tep_draw_hidden_field("post_action","yes");
			?>
			<table border="0" cellspacing="3" cellpadding="2" class="searchArea" width="100%">
				<tr>
					
					<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 '&nbsp;'.TEXT_DAY . tep_draw_separator('pixel_trans.gif',34,10) . 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 tep_draw_separator('pixel_trans.gif',30,10)  . TEXT_VIEW . '&nbsp;' . tep_draw_pull_down_menu("sel_view",$view_array,$view);
						?>
					</td>
					<td rowspan="2" valign="middle" align="right">
						<?php echo tep_image_submit('button_report_search.gif', IMAGE_SEARCH_DETAILS); ?>
					</td>



				</tr>
				<tr>
				<td nowrap="true">
					<?php echo '&nbsp;'.TEXT_LOCATION . '&nbsp;' . tep_draw_pull_down_menu("sel_location",$locations_array,$location);?>
				</td>
				<td nowrap="true">
				<?php 
					$all_events_array=array();
					$all_events_array = tep_get_events_array_single();
					if(sizeof($all_events_array)<=0) echo 'Events are not Available';
					else echo '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'.TEXT_EVENT . '&nbsp;' . tep_draw_event_select_menu('sel_event',$all_events_array,$event,' id="sel_event"');
					echo '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'.TEXT_INSTRUCTOR . '&nbsp;' . tep_draw_pull_down_menu("sel_instructor",$instructors_array,$instructor);
				?>
				</td>
				<td class="main" nowrap="true">
				</td>
			</tr>
			<tr ><td class="cell_bg_report_header" colspan="10">&nbsp;</td></tr>
			<!-- report content -->
		</table>
		</TD>
		</tr>
		</form>
		
	   <table border="0" width="100%" cellspacing="0" cellpadding="2">
			<?php
				if ($found_results) {
					$i=1;
					$total=0;
					$prev_date="";
					$total_attend="";
					$icnt=0;
					$color="#f4f7fd";
          			while($row = tep_db_fetch_array($sql_result)) {
						$sessions_id=$row["sessions_id"];
						// fetch the attended count from the attendance table
						$attend_query=tep_db_query("SELECT events_id,count(*) as total_attendees from " . TABLE_EVENTS_ATTENDEES_CONFIRM . " where sessions_id='" . $sessions_id . "' group by sessions_id ");
						$attend_result=tep_db_fetch_array($attend_query);

						$event_total=$grand_array[$row["start_date"]];
						$percent=0;
						if ($event_total>0)
							$percent=number_format(($row['total_reserved']/$event_total)*100,1);
					
					// output date heading
						if ($prev_date!=$row["start_date"]){ 
							$prev_date=$row["start_date"];
							$icnt=0;
							$color="#f4f7fd";
							if ($total>0) {
			?>
							<tr>
								<td class='dataTableContent' colspan="2" align="right"><?php echo TEXT_TOTAL_RESERVED; ?></td>
								<td class='dataTableContent' align="right"><?php echo $total;?></td>
								<td class='dataTableContent' colspan="2" align="right"><?php echo TEXT_TOTAL_ATTENDEES;?></td>
								<td class='dataTableContent' align="right"><?php echo $total_attend;?></td>
							</tr>
							<tr>
								<td><?php echo tep_draw_separator('pixel_trans.gif',1,10);?></td>
							</tr>
							<?php 
							tep_content_title_bottom();
							$total=0; $total_attend=0;}?>
							<tr>
								<td class='main' colspan="6" height="30"><b><?php tep_content_title_top(TEXT_DATE . '&nbsp;' . format_date($row["start_date"])); ?></b></td>
							</tr>
							<tr class='dataTableHeadingTitleRow'>
								<td class='dataTableHeadingTitleContent' width="350" ><?php echo TEXT_EVENT;?></td>
								<td class='dataTableHeadingTitleContent' ><?php echo TEXT_TIME;?></td>
								<td class='dataTableHeadingTitleContent' width="80" align="right"><?php echo TEXT_RESERVED;?></td>
								<td class='dataTableHeadingTitleContent' width="80" align="right"><?php echo TEXT_TOTAL;?></td>
								<td class='dataTableHeadingTitleContent' width="250">&nbsp;</td>
								<td class='dataTableHeadingTitleContent' width="80" align="right"><?php echo TEXT_ATTENDEES;?></td>
							</tr>
			<?php		} ?>
                  <tr class='dataTableRow' height="20">
					  <td class='dataTableContent' width="350"><?php echo $row['events_name'];?></td>
					  <td class='dataTableContent'><?php echo $row['start_time'];?></td>
					  <td class='dataTableContent' align="right"><?php echo $row['total_reserved'];?></td>
					  <td class='dataTableContent' align="right"><?php echo $percent;?>%</td>
					  <td class='dataTableContent' valign="middle">
					  	<table border="0" cellspacing="0" height="100%" cellpadding="0" width="<?php echo $percent;?>">
							<tr>
								<td width="100%" valign="middle">
									<img height="18" width="100%" border="0" src="images/bar_graph.gif" title="<?php echo $percent;?>%">
								</td>
							</tr>
						</table>
					  </td>
					  <td class='dataTableContent' align="right"><?php echo (int)$attend_result["total_attendees"];?></td>
                  </tr>
			<?php 
						$total_attend+=$attend_result['total_attendees'];
						$total+=$row['total_reserved'];
						$i++;
						$icnt++;
						if ($color=="#7b9ebd"){
							$color="#D0C6C4";
						} else if ($color=="#f4f7fd"){
							$color="#7b9ebd";
						} else {
							$color="#f4f7fd";
						}
					}
					if ($total>0) {
			?>
                <tr>
					<td class='dataTableContent' colspan="2" align="right"><?php echo TEXT_TOTAL_RESERVED; ?></td>
					<td class='dataTableContent' align="right"><?php echo $total;?></td>
					<td class='dataTableContent' colspan="2" align="right"><?php echo TEXT_TOTAL_ATTENDEES;?></td>
					<td class='dataTableContent' align="right"><?php echo $total_attend;?></td>
				</tr>
			<?php
			tep_content_title_bottom();
					}	
				}
				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'); ?>