<?php

require_once 'xls/PHPExcel.php'; //3rd party library + Antradar patches
include 'xls/makexls.php'; //Gyroscope commercial license

function rpt#reportkey#(){
	$rstart=preg_replace('/[^A-Za-z0-9-]/','',SGET('start'));
	$rend=preg_replace('/[^A-Za-z0-9-]/','',SGET('end'));
	$start=date2stamp($rstart);
	$end=date2stamp($rend);
	
	global $codepage;
	
	$mode=SGET('mode');
	
	global $db;

	// Report Meta
	
	$query="select * from ".TABLENAME_REPORTS." where reportkey='#reportkey#'";
	$rs=sql_prep($query,$db);
	$myrow=sql_fetch_assoc($rs);
	$reportname=$myrow['reportname'];
	$reportgroup=$myrow['reportgroup'];
			
	if ($mode!='embed'&&$mode!='xls'){
?>
<div class="section">

<?
	//// Report Header
	
?>
<div class="sectiontitle" style="margin-bottom:0;"><?echo $reportname;?></div>
<div class="infobox"><?echo $myrow['reportdesc'];?></div>
<?	
	////	
?>	
	<div class="inputrow">
		<div class="formlabel">Start Date:</div>
		<input class="inpmed" id="rpt#reportkey#start" onfocus="pickdate(this);" onkeyup="_pickdate(this);">
		<?makelookup('rpt#reportkey#start',1);?>
	</div>
	<div class="inputrow">
		<div class="formlabel">End Date:</div>
		<input class="inpmed" id="rpt#reportkey#end" onfocus="pickdate(this);" onkeyup="_pickdate(this);">
		<?makelookup('rpt#reportkey#end',1);?>
	</div>
	<div class="inputrow">
			<button onclick="
				if (!valdate(gid('rpt#reportkey#start'))||!valdate(gid('rpt#reportkey#end'))) return;
				ajxpgn('rptview_#reportkey#',document.appsettings.codepage+'?cmd=rpt#reportkey#&mode=embed&start='+gid('rpt#reportkey#start').value+'&end='+gid('rpt#reportkey#end').value);
			">Go</button>
	</div>
	
	<div id="rptview_#reportkey#" style="padding:20px 0;">
	
	</div>
		
	<?
	} else {//embed or xls
		
		$sheets=array(
			array(
				'name'=>$reportname,
				'cols'=>array(
#iterator-fields-xlscol#				
				),
				'data'=>array()
			)//sheet	
		);
		
		$query="select * from #tablename# where #datefield#<=? and #datefield#>=? order by #datefield# ";
		
		//range interception style:
		// $query="select * from #tablename# where #datefield#_start<='? and #datefield#_end>=? order by #datefield#_start";
		
		$rs=sql_prep($query,$db,array($end,$start));
		while ($myrow=sql_fetch_assoc($rs)){
			 	
#iterator-fields-myrow#	

		//post process variables here if needed before binding
		//bind date fields with Y-m-d; time fields with H:i:s
	 	//wrap colored fields as such: 'string_value' becomes array('content'=>'string_value','color'=>'color_hex')
		
	 	$row=array(
	 		#iterator-fields-xlsdata#
	 	);
	 	
	 	$color='';
	 	
	 	//set row colors
	 		 	
	 	if ($color!=''){
		 	foreach ($row as $k=>$v) if (!is_array($v)) $row[$k]=array('content'=>$v,'color'=>$color);	
	 	}
	 	
	 	
	 	array_push($sheets[0]['data'],$row);
	 	
	 	}//while
		
		//echo '<pre>'; print_r($sheets); echo '</pre>'; die();		
		
		if ($mode=='xls'){
		
			
			$xls = new PHPExcel();
			$xls->getProperties()->setCreator("Company Name via Gyroscope");
			$xls->getProperties()->setTitle($reportname);
			if ($reportgroup) $xls->getProperties()->setSubject($reportgroup);

			makexls($xls,$sheets);
			
			
			header("Content-type: application/vnd.ms-excel");
			header('Content-Disposition: attachment; filename=#reportkey#-report.xls');
			
			$writer = PHPExcel_IOFactory::createWriter($xls, 'Excel5'); // .xlsx - 'Excel2007'; .xls - 'Excel5'	
			$writer->save('php://output');
			die();
			
		} else {
	?>
		<div style="padding:10px 0;font-size:12px;"><a target=_blank href="<?echo $codepage;?>?cmd=rpt#reportkey#&start=<?echo $rstart;?>&end=<?echo $rend;?>&mode=xls">download as Excel</a></div>
	<?		
		}	
	
	?>
	<div class="stable">
	<table class="subtable" style="width:100%;">	
	<tr>
	<?foreach ($sheets[0]['cols'] as $col){?>
	<td><b><?echo $col['title'];?></b></td>
	<?}?>
	</tr>
	<?
	foreach ($sheets[0]['data'] as $row){		
	?>
	<tr>
	<?
		foreach ($row as $col){
	?>
		<td<?if (is_array($col)&&$col['color']) echo ' style="color:'.$col['color'].';"';?>><?echo is_array($col)?htmlspecialchars($col['content']):htmlspecialchars($col);?></td>
	<?
		}//col
	?>
	</tr>
	<?	
	}//foreach
	?>
	</table>
	</div>
	<?
	
	}
}