<?php
include_once ("_dirinfo.php");
include_once (PATH_TO_ROOT."mystery/init.php");


function controlGraphString($aString) {
	return addslashes($aString);
}

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<head>
  <title>Mystery Report</title>
   <style type="text/css">
	  td {
		font-family: "Lucida Sans Unicode","Lucida Grande",Sans-Serif;
		 font-size: 14px;
	    vertical-align: top;
	    border-bottom: 1px solid #CECECE;
	     }
	  .reportTable TD {
	    font-family: "Lucida Sans Unicode","Lucida Grande",Sans-Serif;
	    
	     vertical-align: top;
	    border-bottom: 0px solid #CECECE;
	    border-top: 1px solid #CECECE;
	   } 
	  .TableHeader {
		border: 1px solid #fff;
	    color: #fff;
	    font-size: 14px;
	    font-weight: bold;
	    padding: 12px 15px;
		background: #00B0F0;
		text-align:center;
		}
	   .TableSubHeader {
		border: 1px solid #fff;
	    color: #5a5a5a;
	    font-size: 14px;
	    font-weight: bold;
	    padding: 12px 15px;
		background:  #d2e7ef;
		text-align:center;
		}
	   .RowPercentage , .ColPercentage, .ColTotalPercentage, .RowTotalPercentage {
	   	  font-size: 12px;
	   }
  </style>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
   <script type="text/javascript" src="<?php echo PATH_TO_ROOT;?>_support/jquery.js"></script>
   <script type="text/javascript" src="<?php echo PATH_TO_ROOT;?>_plugins/charts/js/highcharts.js"></script>
   <script type="text/javascript" src="<?php echo PATH_TO_ROOT;?>_plugins/charts/js/modules/exporting.js"></script>
</head>
<body>

<?php 
/*
echo "<pre>";
	var_dump($_REQUEST);
echo "</pre>";
*/
date_default_timezone_set('Europe/Ljubljana');
require_once (PATH_TO_ROOT.'_class/createQuestion/Question.php');
		
$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 ($cols as $col) {
	$qId = $db->get($proj,"q_name='$col'");
	$q = Question::createQuestionFromTable($db,$proj,$qId[0]['id']);
	$colAns[$col] = $q->getAnswerArray();
	$colLabels[$col] = $q->vpr_sql;
	$colNames[$col] = $col;
	$colTypes[$col] = $q->getQType();
	$colTypesStructure[$col] = $q->getStructureFields();
	//foreach ($colTypesStructure[$col] as $key=> $var) if (!$colAns[$var]) $colAns[$var] = $colAns[$col][$key]; 
	
	if ($q->leg_sql) $colTypesLegend[$col] = $q->answerToArray($q->leg_sql);
	//echo $q->getQType();
}
	/*echo "<pre>";
		var_dump($colAns);	
	echo "</pre>";
*/
$filter = $_GET['filter'];


if ($rowQ) {
	$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();
	$freq = array();
	
	$rowName = $rowTypeStructure[0];
	
	unset($totalTotal);
	foreach ($colNames as $col) {
		
		unset($colTotalVariables);
		
		foreach ($colTypesStructure[$col] as $colName) {
			$sql = "SELECT count(*) as n, ".$rowName." as rowVariable, ".$colName." as colVariable  
						FROM ".DB_WARPIT_WEBCATI_BASE.".".$proj."_structure WHERE ".$rowName." > 0 AND ".$colName." > 0 ";
			if ($filter)  $sql .= " AND ( ".$filter." ) ";
			$sql .= " GROUP BY ".$rowName.", ".$colName;
			//echo $sql."<br>";
			$res = $db->SQLexecute($sql);
			
			while ($zad = $db->fetchAssoc($res)) {
				//echo $proj." -> ".$rowName." -> ".$zad['rowVariable']." -> ".$colName." -> ".$zad['colVariable']." ===> ".$zad['n']."<Br>";
				$freq[$proj][$rowName][$zad['rowVariable']][$colName][$zad['colVariable']] += $zad['n'];
				$rowTotal[$col][$rowName][$zad['rowVariable']] += $zad['n'];
				$colTotal[$rowName][$col][$zad['colVariable']] += $zad['n'];
				if ($colTypes[$col] == "Multiresponse" || $colTypes[$col] == "Scale") $colTotal[$rowName][$colName][$zad['colVariable']] += $zad['n'];
			}
						
			$colTotalVariables[] = $colName." > 0 ";
						
		}
		
		// for multiresponse and scale question I need to calculate extra total, because we need number of cases not items
		if ($colTypes[$col] == "Multiresponse" || $colTypes[$col] == "Scale") {
			$sql = "SELECT count(*) as n, ".$rowName." as rowVariable   
						FROM ".DB_WARPIT_WEBCATI_BASE.".".$proj."_structure WHERE ".$rowName." > 0 ";
			if ($filter)  $sql .= " AND ( ".$filter." ) ";
			$sql .= " GROUP BY ".$rowName;
			$res = $db->SQLexecute($sql);			
			while ($zad = $db->fetchAssoc($res)) $rowTotal[$col][$rowName][$zad['rowVariable']."_multi"] = $zad['n'];
		}
		
		$sql = "SELECT count(*) as n FROM ".DB_WARPIT_WEBCATI_BASE.".".$proj."_structure WHERE ".$rowName." > 0 AND ".implode(" OR ",$colTotalVariables);
		if ($filter)  $sql .= " AND ( ".$filter." ) ";
		//	echo $sql."<br>";
		$res = $db->SQLexecute($sql);
		$zad = $db->fetchAssoc($res);
		$totalTotal[$rowName][$col] = $zad['n'];			
		
		
	}
	
	
}

$db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);

/*
echo "<pre>";
	var_dump($totalTotal);
echo "</pre>";
*/
// we have only frequencies
if (!$rowAns) {
	
	foreach ($colLabels as $key=>$val) {
		unset($freq,$total,$Graph,$totalPonder,$freqPonder,$scoreFactors);
		
		
		if ($colTypes[$key] == "Scale") {
			// scale  tables and graphs
			include (PATH_TO_ROOT."mystery/_forms/graphs-tables/freq/scale.php");
			
		} else if ($colTypes[$key] == "Multiresponse") {
			// multirespnse  tables and graphs
			include (PATH_TO_ROOT."mystery/_forms/graphs-tables/freq/multiresponse.php");
			
		} else {
			// single response tables and graphs
			include (PATH_TO_ROOT."mystery/_forms/graphs-tables/freq/single.php");
		}
	}
} 
	elseif ($_GET['splitBy'] == 2)
{
	//pa zacnima...najprej table
	//echo "Split by Group";
	foreach ($grp as $grIndex => $projGroup) {
		//echo "<div style='float:left;'><div style='float:left;width:30px'></div>".$grName[$grIndex]."</div>";
		echo "<div style='float:left;width:30px'></div><div style='float:left; font-family: \"Lucida Sans Unicode\"; margin-bottom:5px'><b>";
			echo $grName[$grIndex];
		echo "</b></div>";
?>		
		<div style='clear:both; float:left'>
		<table class=reportTable cellpadding=4 cellspacing=0>
			<tr>
				<td class="TableHeader">&nbsp;</td>
		<?php 
		//display column question names
		$totalCol = array();
		$scaleCheck = 0;
		
		foreach ($colNames as $cId => $col) {
			
			if ($colTypesLegend[$col] && $colTypes[$col] == "Scale") {
				$dod = count($colTypes[$col]);
				$scaleCheck = 1;
			} else $dod = 0;
			
			echo "<td class=\"TableHeader\" colspan=".(count( $colAns[$col]) + 1 + $dod * count( $colAns[$col]) ).">". ($_GET['showVarName'] ? $col." "  : '').strip_tags($colLabels[$col])."</td>";
		}
		//echo "<td class=\"TableHeader\">&nbsp;</td>";
		echo "</tr>";
		
		echo "<tr>";
		
			echo "<td class=\"TableSubHeader\">". ($_GET['showVarName'] ? $rowQ." " : "").$rowLabel."</td>";
			//display answers
			foreach ($colNames as $col) {
				$t = $colAns[$col];
				
				if ($colTypesLegend[$col] && $colTypes[$col] == "Scale") $dod = count($colTypesLegend[$col]);
					else $dod = 0;
				
				foreach ($t as $ans) {
					$display = $ans[1];
					if($_GET['showVarValue']) $display = $ans[0].' '.$display;
						
					echo "<td class=\"TableSubHeader\"";
						if ($dod) echo " colspan=\"".$dod."\"";
					echo ">".$display."</td>";
					
				}
				echo "<td class=\"TableSubHeader\">TOTAL</td>";
			}
			
			//go trough all the anwers of the row
		echo "</tr>";
		
		if ($scaleCheck) {
			
			echo "<tr>";	
			echo "<td class=\"TableSubHeader\">&nbsp;</td>";
			//display answers
			foreach ($colNames as $col) {
				
				$t = $colAns[$col];
				foreach ($t as $ans) {
					
					if ($colTypesLegend[$col] && $colTypes[$col] == "Scale") {
						foreach ($colTypesLegend[$col] as $legends) {
							 echo "<td class=\"TableSubHeader\">".($_GET['showVarValue'] ? $legends[0]." " : "").$legends[1]."</td>"; 
						}
					} else echo "<td class=\"TableSubHeader\">&nbsp;</td>";
				}
				echo "<td class=\"TableSubHeader\">&nbsp;</td>";
			}
			
			//go trough all the anwers of the row
			echo "</tr>";
		
		}
		
		foreach ($rowAns as $rowA) {
			
			echo "<tr>";
			echo "<td>". ($_GET['showVarValue'] ? $rowA[0] : '')."{$rowA[1]}</td>";				
			foreach ($colNames  as $col) {				
					
					foreach ($colAns[$col] as $ans) {
					
						$colKey = $ans[0];
						$colVar = $col;
						
						if ($colTypes[$col] == "Multiresponse" || $colTypes[$col] == "Scale") {
							$colVar = $col."_".$colKey;
						}
							
						$tmpRowTotal = $rowTotal[$col][$rowQ][$rowA[0]];
						$tmpColTotal = $colTotal[$rowName][$col][$ans[0]];
						
						if ($colTypesLegend[$col] && $colTypes[$col] == "Scale") {
							foreach ($colTypesLegend[$col] as $legends) {
								 $num = $freq[$proj][$rowQ][$rowA[0]][$colVar][$legends[0]];
								 if (!$num) $num = 0;		
								 echo "<td align=center>";
									echo $num;
									if ($_GET['showRowPercentage']) {
										if ($tmpRowTotal) echo "<div class=\"RowPercentage\">".number_format($num/$tmpRowTotal*100,1)." %</div>"; 
											else echo "<div class=\"RowPercentage\">".number_format(0,1)." % </div>";
									}
									if ($_GET['showColPercentage']) {
										if ($tmpColTotal) echo "<div class=\"ColPercentage\">".number_format($num/$tmpColTotal*100,1)." %</div>"; 
											else echo "<div class=\"ColPercentage\">".number_format(0,1)." % </div>";
									}
								echo "</td>"; 
							}
						} else {
						
							$num = $freq[$proj][$rowQ][$rowA[0]][$colVar][$colKey];						
							if (!$num) $num = "0";
							echo "<td align=center>";
								echo $num;
								if ($_GET['showRowPercentage'] ) {
									if ($tmpRowTotal) echo "<div class=\"RowPercentage\">".number_format($num/$tmpRowTotal*100,1)." %</div>"; 
										else echo "<div class=\"RowPercentage\">".number_format(0,1)." % </div>";
								}
								if ( $_GET['showColPercentage'] ) {
									if ($tmpColTotal) echo "<div class=\"ColPercentage\">".number_format($num/$tmpColTotal*100,1)." %</div>"; 
										else echo "<div class=\"ColPercentage\">".number_format(0,1)." % </div>";
								}
							echo "</td>";
						}
					}
				if (!$rowTotal[$col][$rowQ][$rowA[0]]) $rowTotal[$col][$rowQ][$rowA[0]] = "0";
				echo "<td align=center>";
					if ($colTypes[$col] == "Multiresponse" || $colTypes[$col] == "Scale") {
							echo $rowTotal[$col][$rowQ][$rowA[0]."_multi"];
							if ($_GET['showRowPercentage']) echo "<div class=\"rowTotalPercentage\">100.0 %</div>";
							if ($totalTotal[$rowName][$col] && $_GET['showColPercentage']) echo "<div class=\"rowTotalPercentage\">".number_format($rowTotal[$col][$rowQ][$rowA[0]."_multi"]/$totalTotal[$rowName][$col]*100,1)." %</div>";
					} else {
						echo $rowTotal[$col][$rowQ][$rowA[0]];
						if ($_GET['showRowPercentage']) echo "<div class=\"rowTotalPercentage\">100.0 %</div>";
						if ($totalTotal[$rowName][$col] && $_GET['showColPercentage']) echo "<div class=\"rowTotalPercentage\">".number_format($rowTotal[$col][$rowQ][$rowA[0]]/$totalTotal[$rowName][$col]*100,1)." %</div>";
					}
				echo "</td>";				
			}
			echo "</tr>";
		}
		// display total by columns
		echo "</tr>";
		echo "<tr>";
		echo "<td align=\"right\" style=\"background: #d3e1e6; border-top: 3px solid #CECECE;\">TOTAL</td>";
		foreach ($colNames as $cId => $col) {
				$t = $colAns[$col];
				foreach ($t as $aInd => $ans) {
					
						$colVar = $col;
						if ($colTypes[$col] == "Multiresponse" || $colTypes[$col] == "Scale") {
							$colVar = $col."_".$colKey;
						}
						
						if ($colTypesLegend[$col] && $colTypes[$col] == "Scale") {
							foreach ($colTypesLegend[$col] as $legends) {
									echo "<td align=\"center\" style=\"background: #d3e1e6; border-top: 3px solid #CECECE;\">";		
										if (!$colTotal[$rowName][$col."_".$ans[0]][$legends[0]]) echo "0";
											else echo $colTotal[$rowName][$col."_".$ans[0]][$legends[0]];
											
										if ($totalTotal[$rowName][$col] && $_GET['showColPercentage']) echo "<div class=\"colTotalPercentage\">".number_format($colTotal[$rowName][$col."_".$ans[0]][$legends[0]]/$totalTotal[$rowName][$col]*100,1)." %</div>";
										if ( $_GET['showRowPercentage'] ) echo "<div class=\"rowTotalPercentage\">100.0 %</div>";
									echo "</td>";		
							}
						} else {		
							echo "<td align=\"center\" style=\"background: #d3e1e6; border-top: 3px solid #CECECE;\">";				
								if (!$colTotal[$rowName][$col][$ans[0]]) echo "0";
									else echo $colTotal[$rowName][$col][$ans[0]];
								if ($totalTotal[$rowName][$col] && $_GET['showColPercentage']) echo "<div class=\"colTotalPercentage\">".number_format($colTotal[$rowName][$col][$ans[0]] / $totalTotal[$rowName][$col]*100,1)." %</div>";
								if ( $_GET['showRowPercentage'] ) echo "<div class=\"rowTotalPercentage\">100.0 %</div>";
							echo "</td>";
						}
						
					
				}
			echo "<td style=\"background: #d3e1e6; border-top: 3px solid #CECECE;\" align=center>".$totalTotal[$rowName][$col]."</td>";	
		}
		
		echo "</tr>
			</table>
		</div>
		<div style=' clear: both; height:30px'></div>";			
	} // end foreach ($grp as $grIndex => $projGroup)
}
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)
}
?>
</body>
</html>