<?php

date_default_timezone_set('Europe/Ljubljana');

include_once ("_dirinfo.php");
include_once (PATH_TO_ROOT."mystery/init.php");
require_once (PATH_TO_ROOT.'_class/createQuestion/Question.php');
require_once (PATH_TO_ROOT.'_plugins/excel/PHPExcel.php');



function CreateTable(&$aObj, $aX, $aY, $dataArray) {
	
	$i = 0;
	$j = $aY;
	$y = $aY;
	$x = $aX;
	
	foreach ($dataArray['header']['data'] as $val) {
		$i++;
		$aObj->getActiveSheet()->setCellValue(chr($aX+63+$i).$j, $val);	
		$aObj->getActiveSheet()->getStyle(chr($aX+63+$i).$j)->getAlignment()->setWrapText(true);
		$aObj->getActiveSheet()->getStyle(chr($aX+63+$i).$j)->applyFromArray(
			array('fill' 	=> array(
										'type'		=> PHPExcel_Style_Fill::FILL_SOLID,
										'color'		=> array('argb' => 'FF00B0F0')
									),
				  'font'	=> array(
				  						'color'		=> array('rgb'	=>	'FFFFFF'),
				  						'bold'		=> true
				  					)
				 )
		);
	}

	foreach ($dataArray['body']['data'] as $rowArr) {
		$i = 0;
		$j++;
		foreach ($rowArr as $val) {
			$i++;
			$aObj->getActiveSheet()->setCellValue(chr($aX+63+$i).$j, $val);	
		}	
	}

	$i = 0;
	$j++;
	foreach ($dataArray['footer']['data'] as $val) {
		$i++;
		$aObj->getActiveSheet()->setCellValue(chr($aX+63+$i).$j, $val);	
		
		$aObj->getActiveSheet()->getStyle(chr($aX+63+$i).$j)->applyFromArray(
			array('fill' 	=> array(
										'type'		=> PHPExcel_Style_Fill::FILL_SOLID,
										'color'		=> array('argb' => 'FF00B0F0')
									),
				  'font'	=> array(
				  						'color'		=> array('rgb'	=>	'FFFFFF'),
				  						'bold'		=> true
				  					)
				
				 )
		);
	}
	return array($i,$j);
}

function CreateTableScale(&$aObj, $aX, $aY, $dataArray) {
	
	$i = 0;
	$j = $aY;
	$y = $aY;
	$x = $aX;
	
	foreach ($dataArray['header']['data'] as $val) {
		$i++;
		if ($i > 1) {
			$aObj->getActiveSheet()->mergeCells(chr($aX+63+$i).$j.':'.chr($aX+63+$i+1).$j);
		} else {
			$aObj->getActiveSheet()->mergeCells(chr($aX+63+$i).$j.':'.chr($aX+63+$i).($j+1));
		}
		$aObj->getActiveSheet()->setCellValue(chr($aX+63+$i).$j, $val);
		$aObj->getActiveSheet()->getStyle(chr($aX+63+$i).$j)->getAlignment()->setWrapText(true);	
		$aObj->getActiveSheet()->getStyle(chr($aX+63+$i).$j)->applyFromArray(
			array('fill' 	=> array(
										'type'		=> PHPExcel_Style_Fill::FILL_SOLID,
										'color'		=> array('argb' => 'FF00B0F0')
									),
				  'font'	=> array(
				  						'color'		=> array('rgb'	=>	'FFFFFF'),
				  						'bold'		=> true
				  					)
				 )
		);
		if ($i > 1) $i++;
	}
	$j++;
	$i = 0;
	foreach ($dataArray['header']['data'] as $val) {
		$i++;
		if ($i > 1) {
			$aObj->getActiveSheet()->setCellValue(chr($aX+63+$i).$j, "N");	
			$aObj->getActiveSheet()->getStyle(chr($aX+63+$i).$j)->applyFromArray(
				array('fill' 	=> array(
											'type'		=> PHPExcel_Style_Fill::FILL_SOLID,
											'color'		=> array('argb' => 'FF00B0F0')
										),
					  'font'	=> array(
					  						'color'		=> array('rgb'	=>	'FFFFFF'),
					  						'bold'		=> true
					  					)
					 )
			);
	
			$i++;
			$aObj->getActiveSheet()->setCellValue(chr($aX+63+$i).$j, "%");			
			$aObj->getActiveSheet()->getStyle(chr($aX+63+$i).$j)->applyFromArray(
				array('fill' 	=> array(
											'type'		=> PHPExcel_Style_Fill::FILL_SOLID,
											'color'		=> array('argb' => 'FF00B0F0')
										),
					  'font'	=> array(
					  						'color'		=> array('rgb'	=>	'FFFFFF'),
					  						'bold'		=> true
					  					)
					 )
			);
		}
	}
	

	foreach ($dataArray['body']['data'] as $rowArr) {
		$i = 0;
		$j++;
		foreach ($rowArr as $val) {
			$i++;
			$aObj->getActiveSheet()->setCellValue(chr($aX+63+$i).$j, $val);	
		}	
	}

	$i = 0;
	$j++;
	foreach ($dataArray['footer']['data'] as $val) {
		$i++;
		$aObj->getActiveSheet()->setCellValue(chr($aX+63+$i).$j, $val);	
		
		$aObj->getActiveSheet()->getStyle(chr($aX+63+$i).$j)->applyFromArray(
			array('fill' 	=> array(
										'type'		=> PHPExcel_Style_Fill::FILL_SOLID,
										'color'		=> array('argb' => 'FF00B0F0')
									),
				  'font'	=> array(
				  						'color'		=> array('rgb'	=>	'FFFFFF'),
				  						'bold'		=> true
				  					)
				
				 )
		);
	}
	return array($i,$j);
}


		
$groups = explode('_',$_GET['projTable']);
array_shift($groups);

//$cols = explode(',',$_GET['columns']);

if ($_GET['row']) {
	// because I get variable and var lab I need to cut it ...
	$tmp = explode(" ",$_GET['row'],2);
	$rowQ = $tmp[0];
	unset($tmp);
}

//get all projects for each group
$db->ChangeDatabase(DB_WARPIT_MYSTERY);


foreach ($groups as $gIndex => $gId) {
	$sql = $db->SQLexecute("SELECT a.id,b.proj_name AS idLab FROM _Groups a INNER JOIN ".DB_WARPIT_WEBCATI_BASE."._Projects b ON a.id_project = b.id WHERE a.id_sup = $gId");
	while ($row = $db->fetchRow($sql))
	{
		$grp[$gIndex][] = $row[1];
	}
	$grName[$gIndex] = $db->get('_Groups',$gId,'idLab');
}

$db->ChangeDatabase(DB_WARPIT_WEBCATI);

//we have an example project to take our questions and shit
$proj	= $grp[0][0];

foreach (explode(',',$_GET['columns']) as $val) {
	if ($val[0] == "!") {	
		$sql = " SELECT 
						
						IF(a.is_leaf = 1,a.qName,0) as x1,
						IF(b.is_leaf = 1,b.qName,0) as x2,
						IF(c.is_leaf = 1,c.qName,0) as x3,
						IF(d.is_leaf = 1,d.qName,0) as x4
						
					FROM ".DB_WARPIT_MYSTERY.".".$_GET['projTable']." as a
						
						LEFT JOIN ".DB_WARPIT_MYSTERY.".".$_GET['projTable']." as b ON a.id = b.id_sup 
						LEFT JOIN ".DB_WARPIT_MYSTERY.".".$_GET['projTable']." as c ON b.id = c.id_sup
						LEFT JOIN ".DB_WARPIT_MYSTERY.".".$_GET['projTable']." as d ON c.id = d.id_sup
						
					WHERE a.id_sup = ".substr($val,1);
		//echo $sql;
		$res = $db->SQLexecute($sql);
		while ($zad = $db->fetchAssoc($res)) {
			foreach ($zad as $valNew) if ($valNew) $cols[$valNew] = $valNew;
		}	
	} else {
		$cols[$val] = $val;
	}
}
if (!$cols) {
	echo "No data to display!";
	exit;
}



// supported questions
$supportedQuestions = array("Scale","Single response","Scale","Multiresponse","Open");

foreach ($cols as $col) {
	$qId = $db->get($proj,"q_name='$col'");
	$q = Question::createQuestionFromTable($db,$proj,$qId[0]['id']);
	//echo $q->getQType();	
	if (!in_array($q->getQType(),$supportedQuestions)) continue;
	$colAns[$col] = $q->getAnswerArray();
	$colLabels[$col] = $q->vpr_sql;
	$colNames[$col] = $col;
	$colTypes[$col] = $q->getQType();
	$colTypesStructure[$col] = $q->getStructureFields();
	if ($q->leg_sql) $colTypesLegend[$col] = $q->answerToArray($q->leg_sql);
}


$filter = $_GET['filter'];

if ($rowQ) {
	//echo "tukaj sem";
	$qId = $db->get($proj,"q_name='$rowQ'");
	$q = Question::createQuestionFromTable($db,$proj,$qId[0]['id']);
	$rowAns= $q->getAnswerArray();
	$rowLabel = $q->vpr_sql;
	$rowType = $q->getQType();
	$rowTypeStructure = $q->getStructureFields();
	$rowName = $rowTypeStructure[0];
}

$db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);




$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("Masmi")
							 ->setLastModifiedBy("Masmi")
							 ->setTitle("Export")
							 ->setSubject("Export")
							 ->setDescription("Export Total");

// -------------------------------------------------------
// FREQUENCIES
// -------------------------------------------------------
if (!$rowAns) {
	/*
	echo "<pre>";
		var_dump($grp);
	echo "</pre>";
	exit;*/
	// with this foreach we go over all groups of projects which we would like to compare
	// groups of projects are selected items in tree
	foreach ($grp as $grKey => $grProjArray ) {
		
		if ($grKey > 0) {
			$objPHPExcel->createSheet(NULL,  $grKey );
			$objPHPExcel->setActiveSheetIndex( $grKey );
			// this variable is array of x,y of last action in excel...
			// because I am adding new sheet I need also to reset this coordinates
			unset($CurrentPosInExcel);
		}
			
		$objPHPExcel->getActiveSheet()->setTitle($grName[$grKey]);
		$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setName('Verdana');
		$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);
		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(40);
		
		// #1 CommentGroup
		// this if create tables in horiyontal order so they are comparable.. 
		// if is because if we are looking only one group, then this group should be centered and if we have this div it is automaticaly aligned to the left
		// foreach with all of questions defined to show and make tables and graphs
		foreach ($colLabels as $key=>$val) {
				unset($freq,$total,$Graph,$totalPonder,$freqPonder,$scoreFactors,$GraphPonder,$dataArray);	
				
				// loading ponders...
				// here we have interesting situation... what is happening if we have 2 projects in one group and different ponders defined for each project?!?!?
				$sql = " SELECT id_answer, sc_value, q_name , id_legend 
							 		FROM ".DB_WARPIT_WEBCATI.".".$grProjArray[0]."_mystery AS a
									LEFT JOIN ".DB_WARPIT_WEBCATI.".".$grProjArray[0]." AS b ON a.id_question = b.id WHERE q_name = '".$key."'";
			 	//echo $sql;
			 	$res = $db->SQLexecute($sql);
			 	while ($zad = $db->fetchAssoc($res)) {
			 		//if ($zad['id_legend']) $scoreFactors[$zad['q_name']][$zad['q_name']."_".$zad['id_legend']][$zad['id_answer']] = abs($zad['sc_value']);
			 		//else $scoreFactors[$zad['q_name']][$zad['id_answer']] = abs($zad['sc_value']);
			 		if ($zad['id_legend']) $scoreFactors[$zad['q_name']][$zad['q_name']."_".$zad['id_legend']][$zad['id_answer']] = $zad['sc_value'];
			 		else $scoreFactors[$zad['q_name']][$zad['id_answer']] = $zad['sc_value'];
			 	}
		
			 	// ------------------------------------
			 	// TABLES AND GRAPHS
			 	// ------------------------------------
				if ($colTypes[$key] == "Scale") {
					// scale  tables and graphs
					include (PATH_TO_ROOT."mystery/_forms/graphs-tables/freq/export-scale.php");
					
				} else if ($colTypes[$key] == "Multiresponse") {
					// multirespnse  tables and graphs
					include (PATH_TO_ROOT."mystery/_forms/graphs-tables/freq/export-multiresponse.php");
					
				} else if ($colTypes[$key] == "Open") {
					// multirespnse  tables and graphs
					include (PATH_TO_ROOT."mystery/_forms/graphs-tables/freq/export-open.php");
					
				} else {
					// single response tables and graphs
					include (PATH_TO_ROOT."mystery/_forms/graphs-tables/freq/export-single.php");
				}
			}
	}
	
} 
	elseif ($_GET['splitBy'] == 1)
{
	//echo "Split by Group";
	include (PATH_TO_ROOT."mystery/_forms/graphs-tables/cross/cross-by-group.php");
	
}
else //split by row :)
{
	
	foreach ($rowAns as $rowA) {
			
		echo "<div style='float:left;width:30px'></div><div style='float:left; font-family: \"Lucida Sans Unicode\"; margin-bottom:5px'><b>";
			echo ($_GET['showVarValue'] ? $rowA[0] : '').$rowA[1];
		echo "</b></div>";		
		?>
		<div style=' clear: both;'></div>
		<div style='float:left'>
		
		<table class=reportTable cellpadding=4 cellspacing=0>
			<tr><td class="TableHeader">&nbsp;</td>
		<?php 
		//display column question names
		$totalCol = array();
		$totalTotal = 0;
		
		foreach ($cols as $cId => $col) {
			echo "<td class=\"TableHeader\" colspan=".(count( $colAns[$col]) ).">". ($_GET['showVarName'] ? "$col " : '')."{$colLabels[$col]}</td>";
		}
			echo "<td class=\"TableHeader\" >&nbsp;</td>";
		echo "</tr>
			  <tr><td class=\"TableSubHeader\" >". ($_GET['showVarName'] ? "$rowQ " : '').$rowLabel."</td>";
		//display answers
		foreach ($cols as $cId => $col) {
			$t = $colAns[$col];
			foreach ($t as $ans) {
				$display = $ans[1];
				if($_GET['showVarValue']) $display = $ans[0].' '.$display;
				echo "<td class=\"TableSubHeader\" valign=\"center\">".$display."</td>";
			}
			
		}
		echo "<td class=\"TableSubHeader\">TOTAL</td>";
		//go trough all the anwers of the row
		foreach ($grp as $grIndex => $projGroup) {
		
			echo "</tr><tr>";
			echo "<td>".$grName[$grIndex]."</td>";
			
			foreach ($colNames as $cId => $col) {
				$t = $colAns[$cId];
				$totalRow = 0;
				foreach ($t as $aInd => $ans) {
					//time to do some fucking calculations!!!
					$num = 0;
					foreach ($projGroup as $proj) {
					
					
						$sql = "SELECT count(*) FROM $proj"."_structure WHERE $rowQ = {$rowA[0]} AND $col = {$ans[0]}";
						if($filter)
							$sql.=" AND (".$filter.")";
						$numF = $db->fetchRow( $db->SQLexecute($sql) );
						$num += $numF[0];
						
					}
					$totalRow += $num;
					$totalCol[$aInd.'_'.$cId] += $num;
					$totalTotal += $num;
					echo "<td align=center>".$num."</td>";
				}
				
			}
			echo "<td align=center>".$totalRow."</td>";
		}
		// display total by columns
		echo "</tr><tr><td style=\"background: #d3e1e6; border-top: 3px solid #CECECE; text-align:right\">TOTAL</td>";
		foreach ($colNames as $cId => $col) {
				$t = $colAns[$cId];
				foreach ($t as $aInd => $ans) {
					echo "<td style=\"background: #d3e1e6; border-top: 3px solid #CECECE;\" align=\"center\">{$totalCol[$aInd.'_'.$cId]}</td>";
				}
		}
		echo "<td align=\"center\" style=\"background: #d3e1e6; border-top: 3px solid #CECECE;\">".$totalTotal."</td>";
		echo "</tr>
			</table>
			</div>
			<div style=' clear: both; height:20px'>&nbsp;</div><hr>";

	
	} // end of foreach ($rowAns as $rowA)
}

	
// Rename worksheet



// Set active sheet index to the first sheet, so Excel opens this as the first sheet
//$objPHPExcel->setActiveSheetIndex(0);


// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Export-Total.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

?>
