<?php
include_once ("_dirinfo.php");

require_once (PATH_TO_ROOT."CRM/_lib/excel/PHPExcel.php");
include_once (PATH_TO_ROOT."init.php");
include_once (PATH_TO_ROOT."config.php");
include_once (PATH_TO_ROOT."CRM/class/class.checkString.php");

include_once (PATH_TO_ROOT."CRM/_CRM_config.php");
include_once (PATH_TO_ROOT."CRM/class/class.Language-Pack.php");


$LangPack = new LanguagePack($db, $aLanguage);

$sql = "SELECT * FROM ".WARPIT_CRM."._visitStatistic WHERE gender = 0";
$rez = $db->SQLexecute($sql);
while($row = $db->fetchAssoc($rez)){
	$projectId = $row['id_project'];
	
	$sql = "SELECT proj_name FROM ".DB_WARPIT_WEBCATI_BASE.".`_Projects` WHERE id = $projectId";
	$res = $db->SQLexecute($sql);
	$dat = $db->fetchAssoc($res);
	$ProjectName = $dat['proj_name'];
	
	$row['project'] = $ProjectName;
	
	$sql = "SELECT * FROM ".DB_WARPIT_WEBCATI.".`".$ProjectName."_language` WHERE `id_question` IN ( 9, 10 ) AND `id_text_type` = 3";
	$res = $db->SQLexecute($sql);
	$lang = $days = $time = array();
	while($dat = $db->fetchAssoc($res)){
		$lang[] = explode("/", $dat['lngText']);
	}
	foreach ($lang[0] as $day) {
		$tmp = explode(" ", $day);
		$days[$tmp[0]]['label'] = str_replace($tmp[0]." ", "", $day);	
	}
	foreach ($lang[1] as $t) {
		$tmp = explode(" ", $t);
		$time[$tmp[0]]['label'] = str_replace($tmp[0]." ", "", $t);
	}
	$row['day'] = $days[$row['dan']]['label'];
	$row['hour'] = $time[$row['ura']]['label'];
	$data[] = $row;
}
$abc = range("A", "Z");
$config['id'] = array("width" => 10, "title" => "#ID");
$config['id_tel'] = array("width" => 10, "title" => "#ID_TEL");
$config['firstname'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "firstname_title","Ime"));
$config['surname'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "surname_title","Priimek"));
$config['project'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "project_title","Projekt"));
$config['day'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "day_title","Dan"));
$config['hour'] = array("width" => "25", "title" => $LangPack->GetLabel("statistic_export", "hour_title","Ura"));
$config['dogodek'] = array("width" => "65", "title" => $LangPack->GetLabel("statistic_export", "event_title","Dogodek"));

$fields = array('id',
				'id_tel',
				'firstname', 
				'surname', 
				'project', 
				'dogodek',
				'day', 
				'hour');
$orderBy = array();
foreach ($fields as $key => $value) {
	$orderBy[$value] = $key; 
}
				
$orderedStatus = array();
foreach ($fields as $key => $value) {
	$orderedStatus[$orderBy[$value]] = $value;
}
ksort($orderedStatus);
$orderedStatus = array_values($orderedStatus);


$objPHPExcel = new PHPExcel();
foreach ($orderedStatus as $key => $value) {
	if($key > 25){
		$letter = $abc[intval(($key/25)-1)].$abc[intval(($key%25)-1)];
	}
	else
		$letter = $abc[$key];
	
	$objPHPExcel->getActiveSheet()->getColumnDimension($letter)->setWidth($config[$value]['width']);
	$objPHPExcel->getActiveSheet()->setCellValue($letter."1", $config[$value]['title']);	
	$objPHPExcel->getActiveSheet()->getStyle($letter."1")->getFont()->setBold(true);
}
$i=2;

foreach( $data as $key1 => $value1 )
{
	foreach ($orderedStatus as $key => $value) {
		if($key > 25){
			$letter = $abc[intval(($key/25)-1)].$abc[intval(($key%25)-1)];
		}
		else
			$letter = $abc[$key];
		
		$objPHPExcel->getActiveSheet()->setCellValue($letter.$i, $value1[$value]);
		if(isset($value1['color']))
			cellColor($letter.$i, $value1['color']);
		
	}
    $i++;
}
$fileName = $LangPack->GetLabel("statistic_export", "file_name","Statistic_Export");
$fileName .= ".xls";
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="' . $fileName . '"');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
?>
